The field constraint of mysql is inevitable in the future. There are four main contents as follows: the primary key constraint is used for uniqueness and cannot be empty; the non empty constraint cannot be empty and can be repeated; the unique constraint can be empty but must be unique; the foreign key constraint is to make a certain association between the table and the table; of course, how to use it is also shown below, and multi table is not summarized here. If you forget or are interested in the previous knowledge---- Fuzzy / multiline / grouping / sorting / paging query of MySQL database table and explanation of MySQL data type

7 field constraints of MySQL

7.1 primary key constraint

Primary key constraint: if a primary key constraint is added to a column, the column is the primary key. The primary key is unique and cannot be empty.
The primary key is used to uniquely identify a table record (just like the ID card number is used to uniquely represent a person)
Add a primary key constraint, such as setting the id as the primary key:
(usually there is a primary key in a table)

create table stu(
    id int primary key auto_increment,
   
);

About primary key auto increment:

(1) After setting the auto increment of the primary key, and then inserting records into the table, you can directly insert a null value without assigning a value to the primary key
(2) After the primary key auto increment is set, the id can also be assigned as long as the assigned value does not conflict with the existing id value! At the bottom layer, the value of the auto increment variable will be compared with the inserted id value. If the inserted value is large, the inserted value will be added by 1 and assigned to the auto increment variable.

7.2 non empty constraints

Non empty constraint: if a non empty constraint is added to a column, the value of the column cannot be empty, but it can be repeated.
Add a non empty constraint, such as for password:

create table user(
    password varchar(50) not null,
    ...
);

7.3 unique constraints

Unique constraint: if a unique constraint is added to a column, the value of the column must be unique (that is, it cannot be repeated), but it can be empty.
Add unique constraints, such as unique constraints and non empty constraints for username:

create table user(
    username varchar(50) unique not null,
    ...
);

7.4 foreign key constraints

What's the difference between setting a foreign key and not setting a foreign key? If you do not set a foreign key, the corresponding relationship between the two tables will only be known by the developers themselves. The database itself does not know that there is a corresponding relationship between the two tables, so it will not help us maintain this relationship. It's up to the developers to maintain it.

If a foreign key is set, it is the same as informing the database that there is a certain correspondence between two tables (Dept and emp) (the dept ﹣ id column in the emp table should strictly refer to the primary key (id) in the dept table). The database knows and will help us maintain this relationship.

create table dept(
    id int,
    name varchar(50)
    ...
);

create table emp(
    id int,
    name varchar(50),
    dept_id int,
    foreign key(dept_id) references dept(id)
);

8 table relation

Common table relationships are: One to many (many to one), one to one, many to many

Conclusion:

One to many (many to one): add columns (employees and departments) on one side to save the primary key of one side as a foreign key, so as to save the corresponding relationship between two tables

One to one: add columns to any table to save the primary key of the other party as a foreign key, so as to save the corresponding relationship between the two tables

Many to many: you cannot add columns on either side to hold the primary key of the other side. At this time, you can create a third-party table, and save the primary keys of the two tables as foreign keys, so as to save the many to many correspondence.

multi-table query

Import data from db30 library into database

-- -----------------------------------
-- Establish db30 Library, dept Table, emp Table and insert record
-- -----------------------------------

-- delete db30 library(If exist)
drop database if exists db30;
-- Re create db30 library
create database db30 charset utf8;
-- Choice db30 library
use db30;

-- Delete department table, If exist
drop table if exists dept;
-- Recreate department table, Requirement id, name field
create table dept(
	id int primary key auto_increment,	-- Department number
	name varchar(20)					-- Department name
);
-- Insert record into department table
insert into dept values(null, 'Financial department');
insert into dept values(null, 'Ministry of Personnel');
insert into dept values(null, 'Ministry of science and technology');
insert into dept values(null, 'Sales Department');

-- Delete employee table, If exist
drop table if exists emp;
-- Create employee table (employee number, employee name, department number)
create table emp(
	id int primary key auto_increment,	-- Employee number
	name varchar(20),					-- Employee name
	dept_id int							-- Department number
);
-- Insert record into employee table
insert into emp values(null, 'Zhang San', 1);
insert into emp values(null, 'Li Si', 2);
insert into emp values(null, 'Lao Wang', 3);
insert into emp values(null, 'Zhao Liu', 5);

9.1 connection query

--42. Query employee information corresponding to department and department

select * from dept, emp;
The above query is called Cartesian product query: if two tables are queried at the same time, one table has M pieces of data and the other table has n pieces of data, the result of Cartesian product query is m*n pieces
There are a lot of wrong data in the result of Cartesian product query, which we usually don't use directly.
But we can get rid of the wrong data and keep the right data through where clause and condition.
Correct code:

select * from dept, emp where dept.id=emp.dept_id;

9.2 left outer connection query

-- 43.Query all departments and employees under the Department. If there are no employees under the Department, the employees are displayed as null
select * from dept left join emp on dept.id=emp.dept_id;

Left outer connection query: all records in the left table will be queried. The right table only displays the data corresponding to the left table. If the left table has no corresponding data on the right, it can correspond to null values.

-- You can also use the right outer connection to implement the above query
select * from emp right join dept on emp.dept_id=dept.id;

9.3 right external connection query

-- 44.Query the Department and all employees. If the employee does not belong to a department, the Department is displayed as null
select * from dept right join emp on dept.id=emp.dept_id;

Right outer connection query: all records in the right table will be queried. The left table only displays the data corresponding to the right table. If the right table has no corresponding data on the left, it can correspond to null values.

-- You can also use the left outer connection to implement the above query:
select * from emp left join dept on dept.id=emp.dept_id;

-- Query all departments and corresponding employees (display all departments and employees as well)
select * from emp full join dept on dept.id=emp.dept_id;

mysql It does not support all external connection query, but we can use the union(Union) query to simulate:
select * from dept left join emp on dept.id=emp.dept_id
union
select * from dept right join emp on dept.id=emp.dept_id;

9.4 sub queries

Subquery: query the execution result of one SQL statement as the condition of another SQL statement. Such a query is called subquery.
Import db40 library into database

-- -----------------------------------
-- Establish db40 Library, dept Table, emp Table and insert record
-- -----------------------------------

-- delete db40 library(If exist)
drop database if exists db40;
-- Re create db40 library
create database db40 charset utf8;
-- Choice db40 library
use db40;

-- Create department table
create table dept(				-- Create department table
	id int primary key,			-- Department number
	name varchar(50),			-- Department name
	loc varchar(50)				-- Departmental position
);

-- Create employee table
create table emp(				-- Create employee table
	id int primary key,			-- Employee number
	name varchar(50),			-- Employee name
	job varchar(50),			-- position
	topid int,					-- Directly subordinate
	hdate date,					-- Date of employment
	sal int,					-- salary
	bonus int,					-- bonus
	dept_id int,				-- Department No
	foreign key(dept_id) references dept(id)
);

-- Insert record into department table
insert into dept values ('10', 'Financial department', 'Beijing');
insert into dept values ('20', 'Design Department', 'Shanghai');
insert into dept values ('30', 'Technology Department', 'Guangzhou');
insert into dept values ('40', 'Sales Department', 'Shenzhen');

-- Insert record into employee table
insert into emp values ('1001', 'Wang Fu Guo', 'Clerk', '1007', '1980-12-17', '800', 500, '20');
insert into emp values ('1003', 'Qi Shuai', 'analyst', '1011', '1981-02-20', '1900', '300', '10');
insert into emp values ('1005', 'Wang Lan', 'Salesman', '1011', '1981-02-22', '2450', '600', '10');
insert into emp values ('1007', 'Feng Song', 'manager', '1017', '1981-04-02', '3675', 700, '20');
insert into emp values ('1009', 'Li Zheng', 'Salesman', '1011', '1981-09-28', '1250', '1400', '10');
insert into emp values ('1011', 'Chen Yao', 'manager', '1017', '1981-05-01', '3450', 400, '10');
insert into emp values ('1013', 'Zhang Yong', 'Clerk', '1011', '1981-06-09', '1250', 800, '10');
insert into emp values ('1015', 'Cheng De Shu', 'analyst', '1007', '1987-04-19', '3000', 1000, '20');
insert into emp values ('1017', 'Han Yue', 'Chairman', null, '1981-11-17', '5000', null, null);
insert into emp values ('1019', 'Liu Xiao', 'Salesman', '1011', '1981-09-08', '1500', 500, '10');
insert into emp values ('1021', 'Fan Bingbing', 'Clerk', '1007', '1987-05-23', '1100', 1000, '20');
insert into emp values ('1023', 'Zhao Zi Long', 'manager', '1017', '1981-12-03', '950', null, '30');
insert into emp values ('1025', 'Snow ya', 'analyst', '1023', '1981-12-03', '3000', 600, '30');
insert into emp values ('1027', 'Zhang bold', 'Clerk', '1023', '1982-01-23', '1300', 400, '30');

--45. List all employees whose salary is higher than that of 'Liu Xiao', display name and salary

-- query'Liu Xiao'Salary
select sal from emp where name='Liu Xiao';

-- Query salary ratio'Liu Xiao'All highly paid employees
select name, sal from emp
where sal > (select sal from emp where name='Liu Xiao');

--46. List all employees in the same position as' xueya ', showing their names and positions.

-- Find out xueya's position
select job from emp where name='Snow ya';

-- Seeking and'Snow ya'Employees in the same position
select name, job from emp
where job=(select job from emp where name='Snow ya');

--47. List the employee information whose salary is higher than that of all employees in the 'Technology Department' (known Department No. 30), and display the employee name, salary and department name.

-- Seeking the highest salary of technical department
select max(sal) from emp where dept_id=30;

-- Connect query employee table and department table
select emp.name,sal,dept.name from emp, dept
where emp.dept_id=dept.id
and sal>(select max(sal) from emp where dept_id=30); -- Inaccurate
---------------------------------------------------------
select emp.name,sal,dept.name from emp left join dept
on emp.dept_id=dept.id
where sal>(select max(sal) from emp where dept_id=30);

9.5 multi table query

--48. List the employees in the design department, assuming they do not know the department number of the design department, and display the Department name and employee name

-- Associated query two tables
select dept.name, emp.name from emp,dept
where dept.id=emp.dept_id;

-- Find out the employees in the training department
select dept.name, emp.name from emp,dept
where dept.id=emp.dept_id and dept.name='Design Department';

--49. (self inquiry) list all employees and their direct superiors, display employee name, superior number, superior name

/* Treat emp table as both employee table and superior table E1 (employee table) E2 (superior table)
 Columns queried: e1.name, e2.id, e2.name
 Table queried: emp e1,emp e2
 Connection condition: e1.topid=e2.id
*/

select e1.name, e2.id, e2.name
from emp e1,emp e2
where e1.topid=e2.id;
 
select e1.name, e2.id, e2.name
from emp e1 left join emp e2
on e1.topid=e2.id; -- Left lateral,Query all employees and their corresponding superiors

--50. List all positions with minimum salary greater than 1500, display position and minimum salary of the position

-- Find out the minimum wage of each position
select job,min(sal) from emp group by job;

-- Find out the position with minimum wage greater than 1500
select job,min(sal) from emp group by job having min(sal)>1500;
-- Filter usage before grouping where，Filter usage after grouping having

--51. List the number of employees working in each department and the average wage. Display department number, number of employees and average salary.

-- Group employee tables by department (employees with the same department are a group)
select dept_id Department number, count(*) Department number, avg(sal) Average salary
from emp group by dept_id;

--52. Find out the Department with at least one employee, and display the department number, department name, Department location and department number.

-- Connect query department table and employee table
select d.id, d.name, d.loc from dept d, emp e
where emp.dept_id=dept.id;

-- Group by department and count the number of people in each department
select d.id, d.name, d.loc, count(*)
from dept d, emp e
where e.dept_id=d.id
group by d.name;

--53. List the number, name and department name of all employees whose employment date is earlier than the direct superior.

/*
Columns queried: e1.id, e1.name, d.name
 Table queried: EMP E1 (employee table), Dept D (department table) EMP E2 (superior table)
Connection conditions: e1.dept_id=d.id, e1.topid=e2.id
 Screening conditions: E1. Hdate < E2. Hdate
 */

select e1.id, e1.name, d.name
from emp e1,dept d,emp e2
where e1.dept_id=d.id and e1.topid=e2.id
and e1.hdate<e2.hdate;

--54. List the employee information with the highest salary in each department, and display the department number, the highest salary and the employee name

-- Find the highest salary of each department, display the department number and the highest salary
select dept_id,max(sal) from emp group by dept_id;

-- Use the above query results as a table and emp Table for connection query
select  e.dept_id dept_id, t.max_sal sal, e.name name
from emp e, (select dept_id,max(sal) max_sal from emp group by dept_id) t
where e.sal=t.max_sal and e.dept_id=t.dept_id;

-- Query the highest information of employees without department, display department number, highest salary, employee name
select dept_id,sal,name from emp where
sal=(select max(sal) from emp where dept_id is null)
and dept_id is null;

select  e.dept_id dept_id, t.max_sal sal, e.name name
from emp e, (select dept_id,max(sal) max_sal from emp group by dept_id) t
where e.sal=t.max_sal and e.dept_id=t.dept_id
union
select dept_id,sal,name from emp where
sal=(select max(sal) from emp where dept_id is null)
and dept_id is null;

The difference between where and having in sql statements

Same point: where and having are used to filter the records in the table

Difference:
(1)where is to filter the records in the table before grouping. In addition, column aliases (but table aliases) and multi row functions (count/max/min/sum/avg) cannot be used in where
(2)having is to filter the records in the table after grouping. You can use column alias, table alias and multi row function in having.
(3) Generally speaking, having can replace where, but not vice versa (but in some cases, it can't be replaced: records need to be filtered before grouping, and data need to be filtered after grouping. At this time, having can't be used to replace where)

This detailed explanation of the database is also clear through three blog posts. If you have any questions, you can find me in the comment area!!!