unsigned id int unsigned Cancel the plus and minus signs zerofill id int(5) zerofill Fill with 0 when there are less than 5 numbers
All terminal types can be empty by default without constraints
create table h1( id int, name varchar(32), ); insert into h1(id) values(2); insert into h1(name) values('jason'); insert into h1(name.id) values('tony',2);
create table h2( id int; name varchar(16) not null ); insert into h2(id) values(1); Must report an error name Can not be empty insert into h2(name) values('jason'); insert into h2(name) values(1,''); insert into h2 values(2,null);
create table h3( id int default 888, name varchar(32) default 'no name' )； insert into h3(id) values(1); insert into h3(name) values('jason'); insert into h3 values(2,'tony');
Unique in a single column
create table h4( id int unique, name vaechar(32) unique ); insert into h4 values(1,'jason'),(2,'jason'); insert into h4 values(3,'kevin'),(4,'tony');
create table h5( id int, IP varchar(32), port int, unique(IP,port) ); insert into h5 values(1,'127.0.0.1',8080),(2,'127.0.0.1',8081),(3,'127.0.0.2',8080); insert into t5 values(4,'127.0.0.1',8080);
- From the constraint level alone, the primary key is equivalent to not null + unique (not empty and unique)
create table h6( id int primary key, name varchar(32) ); insert into h6(name) values('jason'); insert into h6 values(1,'kevin'); insert into h6 values(1,'jerry');
- The InnoDB storage engine stipulates that all tables must have one and only one primary key (primary key is an important condition for organizing data, and primary key can speed up data query)
1.When there is no primary key and no other non-null and unique fields in the table InnoDB A hidden field will be used as the primary key of the table. Hiding means that the data query based on this table can only be searched row by row, and the speed is very slow. 2.When there is no primary key in the table but there are other non-empty and unique fields, the first field will be automatically upgraded to the primary key from top to bottom create table h6( id int, age int not null unique, phone bigint not null unique, birth int not null unique, height int not null unique );
''' When we create a table, we should have a field to identify the uniqueness of the data, and this field is usually'id'(Numbering)field id nid sid pid gid uid create table uesrinfo( uid int primary key, ); '''
This constraint cannot appear alone and can only appear once in a table. It is mainly used together with the primary key. create table h8( id int primary key, name varchar(32) ); create table h9( id int primary key auto_increment, name varchar(64) ); ''' Self-increment features: Self-increment will not fall back due to data deletion If you set a larger number, it will automatically increase according to the larger number If you want to reset the primary key value of a table, you can use truncate t9; Clear the table data and reset the primary key '''
foreign key foreplay
We need an employee form id name age dep_name dep_desc 1.The meaning of the expression is not clear(Is it an employee or a department?) doesn't matter 2.Accessing data is too redundant(waste of storage space) doesn't matter 3.Data scalability is extremely poor can not be ignored
Divide the above table into two
id name age id dep_name dep_desc The above three problems are all solved, but there is no relationship between employees and departments Foreign key field: a field used to identify the relationship between data and data
judgment of relationship
Table relationship and data relationship actually have the same meaning, but there are differences in terms of knowledge
There are four types of relationships: one-to-many, many-to-many, one-to-one, and no relationship
The judgment of relationship can adopt the principle of 'empathy'
An employee table and department table as an example
First stand in the perspective of the employee table
Q: Can one employee correspond to multiple departments?
Answer: Of course not
From the perspective of the department table
Q: Can a department correspond to multiple employees?
Conclusion: One can and the other cannot, then the relationship is 'one-to-many' for the 'one-to-many' relationship foreign key field is on the 'many' side
Creation of foreign key fields
Tip: first define a table with ordinary fields, and then consider adding foreign key fields
create table emp( id not primary key auto_increment, name varchar(32), age int, dep_id int, foreign key(dep_id) references dep(id) ); create table dep( id not primary key auto_increment, dep_name varchar(32), dep_desc varchar(64) ); 1.When creating a table, you must first create the associated table 2.When entering table data, you must first enter the associated table 3.Foreign key fields cannot be modified or deleted when modifying data There are simplified operation measures for 3: cascading update and cascading delete create table emp1( id int primary key auto_increment, name varchar(32), age int, dep_id int, foreign key(dep_id) references dep1(id) on update cascade on delete cascade ); create table dep1( id int primary key auto_increment, dep_name varchar(32), dep_desc varchar(64) ); Foreign keys are actually strong coupling and do not meet the characteristics of decoupling So many times, when there are many tables in the actual project, we may not use foreign keys, but use code to establish logical relationship
Take book table and author table as an example
1. First stand in the perspective of the book table
Q: Can one author correspond to multiple volumes?
From the perspective of the author table
Q: Can one author correspond to multiple books
Conclusion: Both are ok. The relationship is 'many to many'
For 'many-to-many', it cannot be created directly in the table, and a third relational table needs to be created
create table book( id int primary key auto_increment, title varchar(32), price float(5,2), ); create table author( id int primary key auto_increment, name varchar(32), phone bigint ); create table book2author( id int primary key auto_increment, author_id int, foreign key(author_id) references author(id) on update cascade on delete cascade, book_id int, foreign key(book_id) references book(id) on update cascade on delete cascade );
Take the user table and user details table as an example
First stand in the perspective of the user table
Q: Can one user correspond to multiple user details?
From the perspective of the user details table
Q: Can one user detail correspond to multiple users?
Conclusion: both are ok Relationship is 'one to one' or no relationship
For the 'one-to-one' foreign key field, it can be built on either side, but it is recommended to be built in the table with high query frequency
create table user( id int primary key auto_increment, name varchar(32), dateil_id int unique, foreign key(datail_id) references userdetail(id) on update cascade on delete cascade ); create table userdertail( id int primary key auto_increment, phone bigint );