SQL statement - DDL

Learn from ：b standing Luo Hao jackfrued The teacher's online class

PS：

1. Modify the engine of the table / Changing the initial value of the self increasing constraint is in create table ` Table name ` () Added later .

   create table ` Table name `(
   
   )engine = innodb auto_increment=2 comment ' Example ';
   

constraint

Primary key constraint

primary key (` Name `),

Compound primary keys are syntactically correct , But it's very difficult to use when developing , At least two fields are required to uniquely identify a record . Generally not used .

If you choose a timetable , There are course number and student number fields , Combined as a composite primary key, records can be uniquely determined . But usually a new elective course will be created id For example, you can uniquely determine the fields of all course selection records as the primary key .

Self increasing constraint 、 Non empty constraint

Just write after the field description

`col_name` int auto_increment not null comment ' Name ',

The following three can be written when creating a table , You can also write later alter table Table name add constraint....

Unique constraint

constraint `uk_col_name` unique (`col_name`),

Similar to primary key constraint , A unique constraint can also set multiple fields , Just write a few more fields in parentheses （ Such as course selection record , A student can't choose courses repeatedly , Student number and course number cannot be repeated ）

Check constraint

Checking constraints can help check the validity of data , But relative , There must be a loss of performance .

constraint `ck_col_sex` check (`col_sex`='M' or `col_sex`='F'),

Foreign key constraints

First, add the same format as the primary key of another table to the table that needs to be added with a foreign key （ The name can be different ） Field of .

The performance of foreign keys will also be lost , To check whether the corresponding field in the corresponding table corresponds to . Many companies don't use foreign key constraints , Other methods will be used to determine whether the specific fields of the two tables correspond to .

constraint `fk_col_id` foreign key (` Field names in this table `) references ` Another table name ` (` The primary key name in this table `),

One to many hours , More than one side needs to add foreign key constraints .