MySQL data definition statement

MySQL data definition statement is mainly to create, modify, delete the table, add, modify, delete the operation of the field

To create a table:
CREATE Table Table name (property name data type constraint condition,
Property name data type constraint condition,
Property name data type constraint condition,
Property name Data type,
);

Full constraint conditions:
PRIMARY Key Primary Key
FOREIGN Key FOREIGN Key
Not NULL non-null
Unique Uniqueness Key
Auto_increment self-increment key (MySQL feature)
Default setting defaults


1. CREATE TABLE Test1

MySQL>createtableintnotnullprimarykey  ,varchar(notnull0 rows Affected (0.00 sec)

Mysql> desctest1;+-------+-------------+------+-----+---------+-------+|Field|Type| Null | Key | Default |Extra|+-------+-------------+------+-----+---------+-------+|Id| int( One)|NO|Pri| NULL | ||Name| varchar( -)|NO| | NULL | |+-------+-------------+------+-----+---------+-------+2Rowsinch Set(0.00Sec

2. Set multi-field primary key

Mysql> Create TableTest2 (IDint  not NULL, -Nameint  not NULL, - Primary Key(Id,name)); Query OK,0Rows Affected (0.00sec) MySQL> desctest2;+-------+---------+------+-----+---------+-------+|Field|Type| Null | Key | Default |Extra|+-------+---------+------+-----+---------+-------+|Id| int( One)|NO|Pri| NULL | ||Name| int( One)|NO|Pri| NULL | |+-------+---------+------+-----+---------+-------+2Rowsinch Set(0.00Sec

3. Setting the foreign key

Mysql> Create TableTest4 (IDint Primary Key, -Nameint  not NULL, - constraintFkForeign Key(Id,name) - Referencestest3 (id,name)); Query OK,0Rows Affected (0.00Sec

4. View table structure

Mysql> desctest4;+-------+---------+------+-----+---------+-------+|Field|Type| Null | Key | Default |Extra|+-------+---------+------+-----+---------+-------+|Id| int( One)|NO|Pri| NULL | ||Name| int( One)|NO| | NULL | |+-------+---------+------+-----+---------+-------+2Rowsinch Set(0.01Sec

5. View Table Detail Syntax

Mysql>ShowCreate Tabletest4\g*************************** 1. Row***************************Table: Test4Create Table:CREATE TABLE' test4 ' (' ID ')int( One) not NULL, ' name 'int( One) not NULL,PRIMARY KEY(' id '),KEY' FK ' (' id ', ' name ')) ENGINE=MyISAMDEFAULTCHARSET=UTF81Rowinch Set(0.00Sec

6. Modify the table name

MySQL>altertable0 rows affected (0.00 sec)

7. Modify field Data type

MySQL>altertablevarchar(0 rows affected (  0.01000

8. Modify field names

MySQL>altertablevarchar(0 rows affected (  0.01000

9. Add Field

MySQL>altertableaddvarchar(0 rows Affected (0.01000

10. Put the added field in the first place

MySQL>altertableadduserint0 rows Affected (0.00000

11. Place the added field in the specified position

MySQL>altertableaddint0 rows affected (0.00   0 0 0 

12. Delete Fields

MySQL>altertabledrop0 rows affected (0.01   000

13. Modify field Position

MySQL>altertablevarchar(0 rows affected (  0.00000

13. Modify the Table engine

MySQL>altertable test1 engine='innodb'   0 rows affected (0.01000

14. Delete foreign keys

MySQL>altertabledropforeignkey0 rows Affected (0.06000

15. Delete a table

MySQL>droptable0 rows affected (0.00 sec)

16. Create a Database

MySQL>Createdatabase1 row affected (0.00 sec)

Reference book: MySQL Getting Started is simple

MySQL data definition statement