MySQL Common statements

First, preface

Today the weather is very good, sunny day, the mood is also good. Summarize MySQL's frequently used statements, record them in essays, and share them in the same way. In the future, I will continue to update this essay as my own MySQL statement.

II. Common SQL statements

I'm going to write from the Inside Out (database to table to data)

Library Layer:

1, SHOW DATABASES;

2, CREATE DATABASE dbname;

3, DROP dababase dbname;

4, ALTER DATABASE dbname set= new character set collate= new proofreading set;

5, use dbname;

6, SHOW CREATE DATABASE dbname;

Surface:

1. Create TABLE Form:

CREATE TABLE tabname

(

Field name 1, data type [column-level constraint] [default],

Field name 1, data type [column-level constraint] [default],

...

[table-level constraints]

);

A. Using the PRIMARY KEY constraint primary key

Two places added: 1, column constraint position (PRIMARY KEY) 2, table-level constraint position ([CONSTRAINT < constraint name;] PRIMARY KEY [field name])

B. Unique constraint using uniqueness

Ibid. Two places added: 1, unique 2, [CONSTRAINT < constraint name;] UNIQUE (< field name >)

Unlike the primary key, both require uniqueness, but unique can be null and the primary key is not available. and a table in primary key can only be one, unique can exist more than one.

C, using FOREIGN KEY constraint (InnoDB) FOREIGN key

A place to add: 1, table-level constraint location Add [CONSTRAINT < foreign key name;] FOREIGN key field name 1[, field name 2,...] REFERENCES < Main Table name > Primary key column [, PRIMARY key column 2,....]

Note: There is a foreign key connection between the two tables, you cannot delete the main table directly, or you will get an error! If you have to delete the primary table, you can delete the foreign key before you delete the primary table.

D, non-null constraint NOT NULL

One to send add: 1, column constraint position not NULL

E. Using DEFAULT constraint Defaults

One location added: 1, column constraints location default defaults

F, set the property value of the table to automatically increase auto_increment

One position added: 1, column constraint position auto_increment

Note: The column at this point must be a primary key or a unique key!

Index issues: The following are normal indexes, full-text indexes, spatial indexes. Creating the constraint above is also creating the appropriate index, primary key index, unique index, and so on.

G, normal index Index/key

One position added: 1, column constraint position INDEX (< field name >);

h, full-text index (MyISAM) fulltext

One position added: 1, column constraint position fulltext KEY [' Name '] (' field name ');

I, create spatial index (MyISAM) spatial

One position added: 1, column constraint position SPATIAL KEY [' Name '] (' field name ');

2, Describe/desc tabname;

3, SHOW CREATE TABLE tabname;

4. DROP table [IF EXISTS] < table name >;

5. ALTER TABLE < table name > engine=< changed storage engine >;

6. ALTER table < old table name > RENAME [to] < new table name >;

7. ALTER table < table name > MODIFY < Field name > < data type >;

8. ALTER table < table name > change < old field name > < new field name > < new data type >;

9. ALTER table < table name > ADD < new field name > < new field type > [constraint] [first| After existing field name];

10. ALTER table < table name > MODIFY < Field 1> < data type > first| After < field 2>;

11. ALTER Table < table name >drop < field name >;

#在已有的表上创建索引

12. ALTER table < table name > ADD index [index name] (field name);

13. ALTER Table < table name > ADD PRIMARY KEY (field name);

14. ALTER Table < table name > ADD FOREIGN key (field name) REFERENCES < Main Table name > (primary key field);

15. ALTER table < table name > ADD UNIQUE KEY (field name);

16. ALTER table < table name > ADD fulltext KEY (field name);

17. ALTER Table < table name > ADD SPATIAL KEY (field name);

18. ALTER TABLE < table name > alter < FIELD name > SET default defaults;

#删除索引

19. ALTER TABLE < table name > alter < field name > DROP DEFAULT;

20. ALTER table < table name > DROP PRIMARY KEY;

21. ALTER Table < table name > DROP FOREIGN key foreign key name;

22. ALTER table < table name > DROP index name;

23, DROP INDEX index_name on tabname;

Data layer:

1. Inserting data

INSERT into TabName (column_list) VALUES (value_list);

INSERT into TabName (column_list) VALUES (Value_list1), (value_list2),...., (VALUE_LISTN);

INSERT into tabname SET field name 1= value 1, field name 2= value 2,....., field name n= value n;\

2. Update data

UPDATE tabname SET column_name1=value1,column_name2=value2,... column_namen=valuen WHERE (condition);

3. Delete data

DELETE from Tabname[where <condition>];

TRUNCATE [TABLE] tabname;

The difference between delete and truncate: Truncate deletes the entire table directly, and then creates an identical table.

4. Data query

(1), SELECT {*|< field list;}

[

From < table 1>,< table 2>,..., < table n>

WHERE < expressions >

Group BY < Group fields >

Having <expression>

Order by < sort fields >

LIMIT <num>

]

(2), SELECT [Field 1, Field 2,..., field n] from [table | view] WHERE [query condition];

(3), connection query

A, internal connection query SELECT tab1.* tab2.* from Tab1 INNER JOIN tab2 on <condition>;

b, left join query SELECT tab1.* tab2.* from tab1 left [OUTER] JOIN tab2 on <condition>;

c, right connection query SELECT tab1.* tab2.* from Tab1 R [OUTER] join TAB2 on <condition>;

(4), merge query results

SELECT column,... From TAB1

UNION [All]

SELECT column,... from TAB2;

(These are some of their own ideas, if there is insufficient or wrong place please point out)

One leaf with the wind

Statement: This blog article for the original, only represents my work in the study in a certain time to summarize the views or conclusions. When reprinted, please give the original link in the obvious position of the article page.

MySQL Common statements