Detailed explanation of basic command examples for MySQL database operations
- 小云云Original
- 2018-01-13 14:13:211550browse
This article mainly introduces the basic commands of MySQL database for the initial use of MySQL. Friends who need it can refer to it. I hope it can help everyone.
1. Create a database:
create data data _name；
Two methods to create a database in php: (mysql_create_db(),mysql_query())
$conn = mysql_connect(“localhost”,”username”,”password”) or die ( “could not connect to localhost”); mysql_create_db(“data _name”) or die (“could not create data ”); $string = “create data data _name”; mysql_query( $string) or die (mysql_error());
2. Select a database
Before creating a table, you must select the database where the table to be created is located
Selected database:
Via command line client:
use data _name
Via
php: mysql_select_db()
$conn = mysql_connect(“localhost”,”username”,”password”) or die ( “could not connect to localhost”); mysql_select_db(“test”,$conn) or die (“could not select data ”);
3. Create a table
create table table_name
For example:
create table table_name ( column_1 column_type column attributes, column_2 column_type column attributes, column_3 column_type column attributes, primary key (column_name), index index_name(column_name) )
You need to type the entire command on the command line client
Used in php, mysql_query() Function
Such as:
$conn = mysql_connect(“localhost”,”username”,”password”) or die ( “could not connect to localhost”); mysql_select_db(“test”,$conn) or die (“could not select data ”); $query = “create table my_table (col_1 int not null primary key, col_2 text )”; mysql_query($query) or die (mysql_error());
4. Create index
index index_name(indexed_column)
5. Table type
ISAM MyISAM BDB Heap
Declaration table Type syntax:
create table table_name type=table_type (col_name column attribute);
MyISAM is used by default
6. Modify the table
alter table table_name
Change the table name
alter table table_name rename new_table_name
or (in higher versions)
rename table_name to new_table_name
Add and delete columns
Add columns:
alter table table_name add column column_name colomn attributes
For example:
alter table my_table add column my_column text not null
first specifies that the inserted column is located in the first column of the table
after Put the new column after the existing column
For example:
alter table my_table add column my_next_col text not null first alter table my_table add column my_next_col text not null after my_other _column
Delete column:
alter table table_name drop column column name
Add and delete index:
alter table table_name add index index_name (column_name1,column_name2,……) alter table table_name add unique index_name (column_name) alter table table_name add primary key(my_column) alter table table_name drop index index_name
such as ：
alter table_name test10 drop primary key
Change column definition:
Use the change or modify command to change the name or attributes of the column. To change a column's name, you must also redefine the column's properties. For example:
alter table table_name change original_column_name new_column_name int not null
Note: The column attributes must be redefined! ! !
alter table table_name modify col_1 clo_1 varchar(200)
7. Enter information into the table (insert)
insert into table_name (column_1,column_2,column_3,…..) values (value1,value2,value3,……)
If you want to store a string, you need to use single quotes "'" to enclose the string, but you need to pay attention to the characters Escape
For example:
insert into table_name (text_col,int_col) value (\'hello world\',1)
The characters that need to be escaped are: single quotation mark 'double quotation mark' backslash\ percent sign % underscore_
You can use two consecutively Single quotes escape single quotes
8. Updata statement
updata table_name set col__1=vaule_1,col_1=vaule_1 where col=vaule
The where part can have any comparison operator
Such as:
table folks
id fname iname salary
1 Don Ho 25000
2 Don Corleone 800000
3 Don Juan 32000
4 Don Johnson 44500
updata folks set fname='Vito' where id=2
updata folks set fname='Vito' where fname='Don'
updata folks set salary=50000 where salary<50000
9. Delete tables and databases
drop table table_name drop data data _name
In php You can use the drop table command through the mysql_query() function
To delete a database in PHP, you need to use the mysql_drop_db() function
10. List all available tables in the database (show tables)
Note: The database must be selected before using this command
In PHP, you can use mysql_list_tables() to get the list of tables
11. View the attributes and properties of the columns Type
show columns from table_name show fields from table_name
Use mysql_field_name(), mysql_field_type(), mysql_field_len() to get similar information!
12. Basic select statement
requires the table to be selected. , and the required column names. To select all columns, use * to represent all field names
select column_1,column_2,column_3 from table_name
or
select * from table_name
Use mysql_query() to send a query to Mysql
13. Where clause
Limit the record rows returned from the query (select)
select * from table_name where user_id = 2
If you want to compare columns that store strings (char, varchar, etc.), just You need to use single quotes to enclose the strings to be compared in the where clause
For example:
select * from users where city = ‘San Francisco'
By adding and or or to the where clause, you can compare several operators at once
select * from users where userid=1 or city='San Francisco' select 8 from users where state='CA' and city='San Francisco'
Note: Null values cannot be compared with any operator in the table. For null values, you need to use the is null or is not null predicate
select * from users where zip!='1111′ or zip='1111′ or zip is null
If you want to find any value (except All records except null values) can be
select * from table_name where zip is not null
14. Use distinct
When using distinct, the Mysql engine will delete rows with the same result.
select distinct city,state from users where state='CA'
15. Use between
Use between to select values within a certain range. between can be used for numbers, dates, and text strings.
For example:
select * from users where lastchanged between 20000614000000 and 20000614235959 select * from users where lname between ‘a' and ‘m'
16. Use in/not in
If a column may return several possible values, you can use the in predicate
select * from users where state='RI' or state='NH' or state='VT' or state='MA' or state='ME'
can be rewritten as:
select * from users where state in (‘RI','NH','VY','MA','ME')
If you want to achieve the same result, but the result set is opposite, you can use the not in predicate
select * from user where state not in (‘RI','NH','VT','MA','ME')
Seventeen, use like
If you need to use wildcards, use like
select * from users where fname like ‘Dan%' %匹配零个字符 select * from users where fname like ‘J___' 匹配以J开头的任意三字母词
like in Mysql is not case-sensitive
18. order by
The order by statement can be returned in the specified query The order of the rows can be sorted by any column type. By placing asc or desc at the end, you can set the order in ascending or descending order. If not set, the default is asc
select * from users order by lname,fname
You can set it as needed Sort by any number of columns, or mix asc and desc Number of rows
Get the first 5 rows in the table:
select * from users limit 0,5 select * from users order by lname,fname limit 0,5
得到表的第二个5行：
select * from users limit 5,5
二十、group by 与聚合函数
使用group by后Mysql就能创建一个临时表，记录下符合准则的行与列的所有信息
count() 计算每个集合中的行数
select state,count(*) from users group by state
*号指示应该计算集合中的所有行
select count(*) from users
计算表中所有的行数
可以在任何函数或列名后使用单词as，然后指定一个作为别名的名称。如果需要的列名超过一个单词，就要使用单引号把文本字符串括起来
sum() 返回给定列的数目
min() 得到每个集合中的最小值
max() 得到每个集合中的最大值
avg() 返回集合的品均值
having
限制通过group by显示的行，where子句显示在group by中使用的行，having子句只限制显示的行。
二十一、连接表
在select句的from部分必须列出所有要连接的表，在where部分必须显示连接所用的字段。
select * from companies,contacts where companies.company_ID=contacts.company_ID
当对一个字段名的引用不明确时，需要使用table_name.column_name语法指定字段来自于哪个表
二十二、多表连接
在select后面添加额外的列，在from子句中添加额外的表，在where子句中添加额外的join参数–>
相关推荐：
The above is the detailed content of Detailed explanation of basic command examples for MySQL database operations. For more information, please follow other related articles on the PHP Chinese website!