constraint

Constraint is to restrict the fields, so as to ensure the accuracy, effectiveness and integrity of the data.

Multiple constraints are separated directly by spaces

# Create a student table and set constraints for each field
create table student(
    id int primary key auto_increment,
    name varchar(10) not null unique,
    age tinyint unsigned check ( age > 0 and age <=120),
    gender char(1) default 'male'
);

# When inserting, if the id field is not inserted, it will be automatically incremented from 1. If the gender field is not inserted, it defaults to 'male'
insert into student(name, age) values ('penrose',18);

#If the "age" column of the data inserted here does not meet the constraints, this operation will not occupy the sequence
insert into student(name, age, gender) values ('cxf',120,'female');

#If the data inserted here is repeated with the data inserted in the previous one, the data insertion fails and will occupy a sequence
insert into student(name, age) values ('penrose',18);

Note:

1. Self growth can only be set for one field, which can be used with primary keys.
2. When you set a self growth constraint for a field, you do not have to insert data for it. If the sequence is inserted, the next piece of data will be added later in turn.
3. If the new data is inserted again after the data insertion fails, the sequence will be faulted. The insert failure here means that all the inserted data meet the constraints of the field, but if this record already exists in the table, the current sequence will be occupied. However, if data truncation occurs in the inserted data, the sequence will not be occupied.

Foreign key constraint

Foreign keys can connect the data between two tables to ensure the consistency and integrity of the data.
At least two tables exist when foreign keys exist.
The associated table is the parent table (the table associated with the foreign key), and the table to be associated is the child table
Once associated, the data in the main table cannot be deleted by default

Delete / update behavior of foreign keys

Note: if you want to delete the data in the main table, you need to set the delete / update behavior

#Add foreign key constraints when creating a new table
create table emp(
    id  int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment 'full name',
    age  int comment 'Age',
    job varchar(20) comment 'position',
    salary int comment 'salary',
    entrydate date comment 'Entry time',
    managerid int comment 'Direct leader ID',
    dept_id int comment 'department ID',
    constraint fk_emp_dept_id foreign key (dept_id) references dept(id)
)comment 'Employee table';

#Add foreign key constraints to existing tables
alter table Table name add constraint Foreign key name foreign key (Foreign key field name) references Parent table(Parent table field name);

#Delete foreign key constraint
alter table drop foreign key Foreign key name

#Add a foreign key to the emp table
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

#Delete foreign key
alter table emp drop foreign key fk_emp_dept_id;

#Add a foreign key constraint. By default, if the update / delete behavior of the foreign key is not set, the data in the main table cannot be deleted.
#Set cascade delete and update for master table
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on UPDATE cascade on DELETE  cascade ;

After setting the foreign key behavior, you can view the corresponding behavior through the visualization tool.

Multi table relation

Each table corresponds to some business logic, and there are mutual connections between business logic, so there are corresponding connections between tables.
There are three types of relationships between tables:

1. One to many (departments and employees, classrooms and students), one side of one is used as the main table, the more side is used as the sub table, and the foreign key is set on the more side (set the foreign key in the employee table / student table)
2. Many to many (courses and students), you usually need to create a third table, and the middle table must contain at least two foreign keys
3. One to one, mostly used for splitting single tables. Splitting here means that when creating a table, a complete table is created twice. In order to maintain a one-to-one relationship, you need to add foreign keys on either side. At the same time, in order to limit the one-to-one relationship between them, we need to set unique constraints for foreign keys.

One to many

#Create employee table (I)
create table dept(
    id int primary key auto_increment comment 'ID',
    name varchar(50) not null comment 'Department name'
)comment 'Department table';

#Create emp table (multiple)
create table emp(
    id  int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment 'full name',
    age  int comment 'Age',
    job varchar(20) comment 'position',
    salary int comment 'salary',
    entrydate date comment 'Entry time',
    managerid int comment 'Direct leader ID',
    dept_id int comment 'department ID',
    constraint fk_emp_dept_id foreign key (dept_id) references dept(id)
)comment 'Employee table';

Many to many

#Create student table (multiple)
create table student(
    id int auto_increment primary key comment 'Primary key ID',
    name varchar(10) comment 'full name',
    no varchar(10) comment 'Student number'
) comment 'Student list';
insert into student values (null, 'Daisy', '2000100101'),(null, 'Thankson ', '2000100102'),(null, 'Yin Tianzheng', '2000100103'),(null, 'Xiangr ', '2000100104');

#Create curriculum (multiple)
create table course(
    id int auto_increment primary key comment 'Primary key ID',
    name varchar(10) comment 'Course name'
) comment 'Class Schedule Card';
insert into course values (null, 'Java'), (null, 'PHP'), (null , 'MySQL') , (null, 'Hadoop');

#Create students_ Curriculum (middle table)
create table student_course(
    id int auto_increment comment 'Primary key' primary key,
    studentid int not null comment 'student ID',
    courseid  int not null comment 'curriculum ID',
    constraint fk_courseid foreign key (courseid) references course (id),
    constraint fk_studentid foreign key (studentid) references student (id)
)comment 'Student intermediate schedule';

one-on-one

#Original tb_user and tb_user_ The fields in edu are all together, but they are split to improve query efficiency
create table tb_user(
    id int auto_increment primary key comment 'Primary key ID',
    name varchar(10) comment 'full name',
    age int comment 'Age',
    gender char(1) comment '1: male , 2: female',
    phone char(11) comment 'cell-phone number'
) comment 'User basic information table';

#In order to establish a relationship between the two tables, you need to set a foreign key in a table for association. At the same time, the two tables are one-to-one key, so you need to set the foreign key to a unique constraint
create table tb_user_edu(
    id int auto_increment primary key comment 'Primary key ID',
    degree varchar(20) comment 'education',
    major varchar(50) comment 'major',
    primaryschool varchar(50) comment 'primary school',
    middleschool varchar(50) comment 'middle school',
    university varchar(50) comment 'university',
    userid int unique comment 'user ID',
    constraint fk_userid foreign key (userid) references tb_user(id)
) comment 'User education information form';


insert into tb_user(id, name, age, gender, phone) values
        (null,'Huang Bo',45,'1','18800001111'),
        (null,'Bingbing',35,'2','18800002222'),
        (null,'Code cloud',55,'1','18800008888'),
        (null,'Robin Li',50,'1','18800009999');

insert into tb_user_edu(id, degree, major, primaryschool, middleschool, university, userid) values
        (null,'undergraduate','dance','Jing'an District No.1 Primary School','Jing'an District No.1 Middle School','Beijing Dance Academy',1),
        (null,'master','perform','Chaoyang first primary school','Chaoyang District No.1 Middle School','Beijing Film Academy',2),
        (null,'undergraduate','English','Hangzhou No.1 Primary School','Hangzhou No.1 Middle School','Hangzhou Normal University ',3),
        (null,'undergraduate','applied mathematics','Yangquan No.1 Primary School','Yangquan No.1 Middle School','Tsinghua University',4);

multi-table query

summary

Cartesian product

Simple understanding is the product of two sets

Note: because Cartesian product will appear in multi table query, the most important operation is to eliminate redundant Cartesian product

select * from emp,dept;

multi-table query

join on

Inner connection

Inner join, which queries the intersecting parts of two sets

#Implicit inner connection (excluding keyword join on)
select Field list from Table 1,Table 2 where condition;

#Show inner connection (including keyword join on)
select Field list from Table 1 [inner] join Table 2 on Connection conditions;

#Query employee's name and department
#Implementation of implicit inner connection
select emp.name,dept.name from emp,dept where emp.dept_id = dept.id;

#Show internal connections
select emp.name,dept.name from emp inner join dept on emp.dept_id = dept.id;

External connection

The left (right) outer connection is used to query the data in the left (right) table and the intersection of the two tables, but the left connection is mostly used because the left connection can replace the right connection.

#Query all data and corresponding department information in emp table
select e.*,d.name from emp e left join dept d on e.dept_id = d.id;

#Query all data and corresponding employee names in dept
select d.*,e.name from dept d left join emp e on d.id = e.dept_id;