Detailed explanation of basic command examples for MySQL database operations

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参数–>

相关推荐：

TP5的数据库操作

PHP使用ORM进行数据库操作

MySQL教程--通过配置文件连接数据库操作详解

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!