MySQL database notes Day4

Posted by doctor_james on Thu, 04 Nov 2021 08:58:17 +0100

Table 6 constraints

Non NULL constraint not null

DROP TABLE IF EXISTS tb_user; #If the table exists, delete it. If you use it with caution, you will lose data
 
CREATE TABLE tb_user(
 
id INT AUTO_INCREMENT,
 
NAME VARCHAR(30) UNIQUE NOT NULL,
 
age INT,
 
phone VARCHAR(20) UNIQUE NOT NULL,
 
email VARCHAR(30) UNIQUE NOT NULL,
 
PRIMARY KEY (id)
 
);
 
DESC tb_user;
 
#The id is a self incrementing primary key, and the null value is invalid. The database will automatically replace it with the next id value
 
#age can be set to null because the run is null
 
INSERT INTO tb_user (id,age) VALUES(NULL,NULL);

unique constraint

The Name field creates a unique constraint. When inserting data, the database will check. If the inserted values are the same, an error will be reported:

DROP TABLE IF EXISTS tb_user; #If the table exists, delete it. If you use it with caution, you will lose data
 
CREATE TABLE tb_user(
 
id INT,
 
NAME VARCHAR(30) UNIQUE NOT NULL,
 
phone VARCHAR(20) UNIQUE NOT NULL,
 
email VARCHAR(30) UNIQUE NOT NULL,
 
PRIMARY KEY (id)
 
);
 
DESC tb_user;
 
INSERT INTO tb_user (id,NAME) VALUES(1,'tony');
 
INSERT INTO tb_user (id,NAME) VALUES(2,'tony');
 
Error executing the above statement:
 
Query : INSERT INTO tb_user (id,NAME) VALUES(2,'tony')
 
Error Code : 1062
 
Duplicate entry 'tony' for key 'name'
 
Presentation table structure:
 
DESC tb_user;

Primary key constraint primary key

A primary key is the unique identification of a record. It is unique and cannot be repeated

DROP TABLE IF EXISTS tb_user; #If the table exists, delete it. If you use it with caution, you will lose data
 
CREATE TABLE tb_user(
 
id INT,
 
NAME VARCHAR(30),
 
PRIMARY KEY (id)
 
);
 
INSERT INTO tb_user (id,NAME) VALUES(1,'tony');
 
INSERT INTO tb_user (id,NAME) VALUES(1,'hellen');
 
An error will be reported if the second sentence is inserted:
 
Query : INSERT INTO tb_user (id,NAME) VALUES(1,'hellen')
 
Error Code : 1062
 
Duplicate entry '1' for key 'PRIMARY'
 
Prompt: the value of primary key 1 already exists and is duplicated

foreign key constraint

DROP TABLE IF EXISTS tb_user_address; #If the table exists, delete it. If you use it with caution, you will lose data
 
DROP TABLE IF EXISTS tb_user; #If the table exists, delete it. If you use it with caution, you will lose data
 
CREATE TABLE tb_user (
 
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, #Auto increment primary key
 
NAME VARCHAR(50) NOT NULL UNIQUE, #Non empty, unique index
 
sex CHAR(2) DEFAULT 'male', #Default value
 
phone CHAR(18),
 
age INT,
 
CHECK (age>0 AND age<=200),
 
);
 
CREATE TABLE tb_user_address (
 
user_id INT PRIMARY KEY NOT NULL,
 
address VARCHAR(200),
 
foreign key(user_id) REFERENCES tb_user(id)
 
);
 
DESC tb_user;
 
tb_user_address in user_id Field entry tb_user If the primary key value of the table does not exist, an error will be reported
tb_user_address in user_id Field entry tb_user If the primary key value of the table does not exist, an error will be reported

Check constraint check

Rarely used, just understand. If the age exceeds 200, an error will be reported

DROP TABLE IF EXISTS tb_user; #If the table exists, delete it. If you use it with caution, you will lose data
 
CREATE TABLE tb_user (
 
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, #Auto increment primary key
 
NAME VARCHAR(50) NOT NULL UNIQUE, #Non empty, unique index
 
sex CHAR(2) DEFAULT 'male', #Default value
 
phone CHAR(18),
 
age INT,
 
CHECK (age>0 AND age<=200),
 
createdTime DATE DEFAULT NOW()
 
);
 
DESC tb_user;

Table association

Table represents an entity in life, such as department table dept and employee table emp. Table association represents the relationship between tables, such as department and employee, commodity and commodity classification, teacher and student, classroom and student.

At the same time, we should also know that tables are not all related. They form their own small circle. For example, it may not matter if the goods and the details of the goods are circled, and the departments and employees are circled. For example, the goods have nothing to do with the employees, and the goods have nothing to do with the students.

Below, we discuss the relationship between tables, which can be divided into four types:

one to one QQ and QQ email, employee and employee number
one to many is the most common, departments and employees, users and orders
many to one many to many in turn, employees and departments, orders and users
many to many teachers and students, teachers and courses

Multi table associated query join

Cartesian product

Multi table query refers to a query based on two or more tables. In practical applications, querying a single table may not meet your needs. For example, the employee table emp displays not only deptno, but also the Department name, and the Department name dname is in the dept table.

SELECT * FROM dept,emp #format

The above method of querying two tables is called Cartesian product, also known as direct product. Generally, Cartesian product has no practical business significance, but multi table queries are formed into Cartesian product, and then the data is filtered.

It is worth noting that in the actual development, multi table joint query is used as little as possible. This is the fundamental reason. In the query process, a large result set is built in memory, and then the data is filtered. The construction process and the memory resources used, including the judgment during filtering, are both resource-consuming and time-consuming.

This is why the Alibaba specification prohibits associated queries on more than three tables

Three kinds of join

inner join

SELECT d.dname,e.ename,e.job
 
FROM emp e INNER JOIN dept d
 
ON e.deptno=d.deptno
 
WHERE d.dname='research'

Left (outer) join

SELECT d.dname,e.ename,e.job
 
FROM emp e  left join  dept d
 
ON e.deptno=d.deptno
 
WHERE d.dname='research'

right join

SELECT d.dname,e.ename,e.job
 
FROM emp e right join dept d
 
ON e.deptno=d.deptno
 
WHERE d.dname='research'

What is the difference between inner join, left join and right join?

Only those with records on both sides of the INNER JOIN are displayed, and the others are removed
The data in the table on the left of the LEFT JOIN appears, and there is no data on the right, which is filled with NULL
The data in the right table of RIGHT JOIN appears, and no data on the left is filled with NULL

Topics: Database MySQL

Programmer Think