MySQL Common & useful statements

MySQL is the most popular relational database management system, and is one of the most commonly used database, it is necessary to master its commonly used operation statements.

Here is a summary of the MYSQP Common & useful SQL statements:

1,MySQL -u root -p command to connect to the MySQL server;

Mysqladmin -u root password "new_password" command to create the root user's password.

2, check which db:show databases are currently available;

Add db:create database mx; (MX db name)

Delete Db:drop database mx;

Use Db:use mx;

3. Create Data Tables Table

creat table table_name (colum_name data_type,colum_name data_type,.. Colum_name data_type,);

View table field: describe table_name;

4. Add columns

ALTER TABLE "table_name" Add "column_name" "data_type" [Not Null][default];

Delete Column

ALTER TABLE "table_name" drop "column_name";

5. Modify column Information

ALTER TABLE "table_name" Change "old_column_name" "New_column_name" "Data_type"

Change column name only: Data_type and original, old_column_name! = New_column_name

Change data type only: Old_column_name = = New_column_name, data_type

The column name and data type are changed.

6. Modify the table name

ALTER TABLE "table_name" rename "New_table_name";

7. View Table Data

SELECT * FROM table_name;

Select Col_name,col_name2,... from table_name;

8. Inserting data

INSERT INTO "table_name" value (value 1, value 2,...);

INSERT INTO "table_name" (column 1, column 2 ...) ) Value (value 1, value 2,...);

9. Where language

SELECT * FROM table_name where col_name operator value;

Combination conditions and, or

Where you can filter by combining multiple conditions with and and the OR operator

SELECT * FROM table_name where col1 = xxx and col2 = xx or col > xx

10. Null Judgment-Is/is not

SELECT * FROM table_name where col_name is null;

SELECT * FROM table_name where col_name are NOT null:

11, distinct (accurate)

SELECT DISTINCT col_name from table_name;

12. ORDER BY order

Sort by a single column name:

SELECT * FROM table_name [WHERE clause] ORDER by col_name [Asc/desc];

Sort by multiple columns:

SELECT * FROM table_name [WHERE clause] ORDER by Col1_name [Asc/desc], col2_name [Asc/desc] ...;

When ASC or DESC is not added, the default is ASC

13. Limit Limits

SELECT * FROM table_name [WHERE clause] [ORDER BY clause] limit [offset,] rowCount;

Offset: The starting position of the query results, the first record is actually 0

Rowcoun: Gets the number of record bars starting from the offset position

Note: Limit RowCount = Limit 0,rowcount

14. Insert into and select Use combination

INSERT into "Table name 1" Select column 1, column 2 from "Table Name 2";

INSERT into "table name 1" (Column 1, column 2) Select column 3, column 4 from "Table Name 2";

15. Updata Syntax

Modify a single column

Updata table Name set column name = XXX [WHERE clause];

Modify multiple columns

Updata table Name set column name 1 = XXX, column name 2 = XXX ... [where sentence];

16, in syntax

SELECT * FROM table name where column name in (Value1,value2 ...);

SELECT * FROM table name where column name in (select Column name from table name);

17. Between syntax

SELECT * FROM table name where column name between value 1 and value 2;

SELECT * FROM table name where column name not between value 1 and value 2;

18. Like grammar

SELECT * FROM table name where column name [not] like pattern;

Pattern: matching pattern, e.g. ' abc '%ABC ' abc% '%abc% '

'% ' is a wildcard character, which can be understood as any string

For example: '%abc ' can match ' erttsabc '

MySQL Common & useful statements