MySQL Create and manipulate database recommended command summary

SQL full Name: Structured queries language (structured query Language)

4 + languages included

1, data definition language DDL data definition Language

2. Data manipulation language DML data Manipulation Language

3. DQL data Query Language Language

4, Data Control language DCL Data Control Language

Common commands in DDL:

1. Create a database

Create database db_name character set UTF8;

2. Using the database

Use db_name;

3. Create a table in the database

CREATE TABLE Tb_name (

_id int,

_name varchar,

..... (The last attribute ends without a comma)

);

4. Add Table field

ALTER TABLE tb_name add column _age int;

5. Delete table field

ALTER TABLE tb_name drop column _name;

6. Change table field

ALTER TABLE tb_name change column _id _id int;

7. Renaming a table

Alter Tb_name Tb_newname;

Cond......

Common commands in DML:

Three common command keywords: Add insert modify update remove delete

1. New data in the table

Insert into Tb_name (_id,_name) VALUES (1, ' name ');

If you do not specify a field name, all new is added, and the value in values needs to correspond to the order of the columns one by one.

2. Modify the data in the table

Update tb_name Set _name = ' NewName ' where _id =?;

3. Delete data from table (conditional delete)

Delete from tb_name where _id =?;

Cond......

Common commands in DQL:

1. Query all the data in the table

Select *from tb_name;

2. Query all data for a field

Select _name from Tb_name;

3, replace the query results display column name (only change the display effect, did not change the table field in the database, very useful)

Select _name as ' NewName ' from Tb_name;

4. Single Condition Inquiry

Select *from tb_name where _id >?

5. Compound condition Query and OR

Select *from tb_name where _id >? and/or _id <?

6, go to re-query

Select distinct _name from Tb_name;

7, sort query (the default is ascending ASC, reverse is DESC)

Select *from tb_name ORDER by _name; Ascending

Select *from tb_name ORDER by _name Desc; Reverse

8, paged query keyword limit x, y

The first parameter, x, indicates the offset of the first returned record row, and the initial record line offset is 0, not 1.

The second parameter, y, specifies the maximum number of record rows to return.

Simply put, x represents the index value, and Y represents the number of queries.

Select *from tb_name limit 2,10; Take 10 from the third, since the starting number is from 0.

9. Aggregation function

The keyword has max (max) min (minimum) AVG (average) sum (and)

Select Max (_age) as age from Tb_name

10. Sum Query

Select COUNT (*) from Tb_name

11. Group Inquiry Query The total number of students in each grade

Select COUNT (*), Stu_grade from Tb_student Group by Stu_grade;

12, grouping conditions Query group by + have to query the number of students in Grade 3 and above in total

Select COUNT (*), Stu_grade from Tb_student Group by Stu_grade have Stu_grade >4;

13, fuzzy query like query student name with ' Fang ' people

Select *from tb_student where stu_name like '% aromatic% ';

Query Last Word% word

Two characters% word%

Identity match one character ' _ ' like ' _ Fang ' Wu Fang (queryable) Wu Yuanfang (not available)

14. Inline Query

Select *from tb_boy a INNER join tb_girl b on a.men_age = B.girl_age; Match boys and girls with the same age information

15, the external query (leftist right)

Select *from Tb_boy a LEFT join Tb_girl b on a.men_age = B.girl_age;

MySQL Create and manipulate database recommended command summary