DML (data manipulation language, used to operate the database contains data) insert (add), update (modified) delete (delete)
(data definition language, is used to create and delete database objects such as operation) create (create) the drop (delete) the alter (censored fields and the table name)
DQL (data query language, used to query data in the database), select (query)
DCL (data control language, is used to control access to the database component license, access permissions, etc.) grant commit rollback
Not Null attributes: Not empty
The comment "" "' number/field/description
The default 'attributes: the default
The unique key (UK) attributes: the only
AUTO_INCREMENT on the columns, identity column
PRIMARY KEY (pk) PRIMARY KEY constraints
Foreign Key (fk1 - fk) Foreign Key constraints
- create database
the CREATE DATABASE MySchool;
- show database list
Grammar: - SHOW the DATABASES;
- use database
Grammar: - USE MySchool;
- see table structure
Grammar: - desc table name - the describe table name
- delete database
Grammar: - DROP DATABASE DATABASE name;
-- delete the table name
Grammar: - DROP TABLE [IF EXISTS] the name of the TABLE; (if the exists to judge whether there is the table)
DROP TABLE IF the EXISTS student;
- modify the table name
Grammar: - the ALTER TABLE RENAME the old TABLE name (T0) the new TABLE name;
ALTER TABLE student1 RENAME demo;
- adding a field (column)
Grammar: - the ALTER TABLE TABLE name ADD field name data type [properties];
ALTER TABLE demo ADD demosex CHAR (2);
- modified field (column)
Grammar: - the ALTER TABLE TABLE name CHANGE the field name new field name data type [properties];
ALTER TABLE demo CHANGE demosex not INT (4);
-- delete the field (column)
Grammar: - the ALTER TABLE TABLE name DROP field name;
ALTER TABLE demo DROP not;
add a primary key field
Grammar: - the ALTER TABLE TABLE name ADD the CONSTRAINT of the PRIMARY KEY PRIMARY KEY TABLE name (PRIMARY KEY field);
ALTER TABLE demo ADD CONSTRAINT pk_gradeID PRIMARY KEY demo (gradeID);
add field - foreign keys
Grammar: - the ALTER TABLE TABLE name ADD CONSTRAINT FOREIGN KEY of the FOREIGN KEY (FOREIGN KEY fields) REFERENCES associated TABLE name (associated fields);
ALTER TABLE demo ADD CONSTRAINT fk_gradeID FOREIGN KEY REFERENCES (gradeID) grade (gradeID);
DML statements1. 1. Increase the data into a single data [field name written corresponding value not write a field name in order assignment]
Grammar: - INSERT INTO the table name/list (field) VALUES (list of VALUES);
2. Increase the data 2. Insert multiple data
Grammar: - INSERT INTO the new table (field list) VALUES (list 1), (2) list of VALUES,... (list of values, n);
3. Modify the data updating data record
The UPDATE table name SET field 1=1, field 2=value 2... [] WHERE clause for
4. Delete the data deleted data record
A: DELETE FROM the table name [] the WHERE condition;
2: TRUNCATE TABLE TABLE name;
5. Query query statement
SELECT & lt; Field list & gt;
The FROM & lt; The name of the table or view & gt;
[WHERE & lt; query conditions & gt;]
[GROUP BY & lt; grouping field name & gt;]
[HAVING & lt; filter condition & gt;]
[the ORDER BY & lt; sort the field name & gt; (ASC or DESC descending]
[LIMIT [] what query statement - 1, query the number of rows]; ]
Note: the TRUNCATE statement will reset after deleting columns, table structure and its fields, constraints, the index remained unchanged, execution speed is faster than the DELETE statement
6. Insert the query results into a new table way
A: this table need to create your own
INSERT INTO the new table (field 1, 2... )
The SELECT field 1, field 2...
FROM the original table;
2: the table don't need to create he will automatically create
The CREATE TABLE new TABLE (
The SELECT field 1, field 2
FROM the original table);
