1. Database Constraints

1.1 NULL constraint

When creating a table, you can specify that a column is not empty:

-- Reset student table structure 
DROP TABLE IF EXISTS student; 
CREATE TABLE student ( 
    id INT NOT NULL,    
    sn INT,    
    name VARCHAR(20),    
    qq_mail VARCHAR(20) 
);

1.2 UNIQUE: unique constraint

Specify that the sn column is unique and not duplicate:

-- Reset student table structure 
DROP TABLE IF EXISTS student; 
CREATE TABLE student (
    id INT NOT NULL,    
    sn INT UNIQUE,    
    name VARCHAR(20),    
    qq_mail VARCHAR(20) 
); 

1.3 DEFAULT: default constraint

When inserting data, the name column is empty, and the default value is unkown:

-- Reset student table structure 
DROP TABLE IF EXISTS student; 
CREATE TABLE student (
    id INT NOT NULL,    
    sn INT UNIQUE,    
    name VARCHAR(20) DEFAULT 'unkown',    
    qq_mail VARCHAR(20) 
);

1.4 PRIMARY KEY: primary key constraint

Specify the id column as the primary key:

-- Reset student table structure 
DROP TABLE IF EXISTS student; 
CREATE TABLE student (    
    id INT NOT NULL PRIMARY KEY,    
    sn INT UNIQUE,    
    name VARCHAR(20) DEFAULT 'unkown',    
    qq_mail VARCHAR(20) 
);

For integer type primary keys, auto increment is often used. When no value is given for the corresponding field of inserted data, the maximum value + 1 is used.

-- The primary key is NOT NULL and UNIQUE The combination of NOT NULL 
id INT PRIMARY KEY auto_increment,

1.5 FOREIGN KEY: foreign key constraint

Foreign key is used to associate the primary key or unique key of other tables. Syntax:

foreign key (field name) references main table (column) 

Create the class table classes with id as the primary key:

-- Create a class table with MySQL When keywords are used as fields, you need to use``To identify 
DROP TABLE IF EXISTS classes; 
CREATE TABLE classes (
    id INT PRIMARY KEY auto_increment,    
    name VARCHAR(20),    
    `desc` VARCHAR(100) 
); 

Create student table, one student corresponds to one class, and one class corresponds to multiple students. Use id as the primary key, classes [id] as the foreign key, and associate the class table id

-- Reset student table structure 
DROP TABLE IF EXISTS student; CREATE TABLE student (
    id INT PRIMARY KEY auto_increment,    
    sn INT UNIQUE,    
    name VARCHAR(20) DEFAULT 'unkown',    
    qq_mail VARCHAR(20),    
    classes_id int,    
    FOREIGN KEY (classes_id) REFERENCES classes(id) 
);

2. Table design

One-on-one

One to many

Many to many

Create Curriculum

-- Create Curriculum 
DROP TABLE IF EXISTS course; 
CREATE TABLE course (
    id INT PRIMARY KEY auto_increment,    
    name VARCHAR(20) 
);

Create student course intermediate table and examination score table

-- Create the middle table of course students: examination score table 
DROP TABLE IF EXISTS score; 
CREATE TABLE score (
    id INT PRIMARY KEY auto_increment,    
    score DECIMAL(3, 1),    
    student_id int,    
    course_id int,    
    FOREIGN KEY (student_id) REFERENCES student(id),    
    FOREIGN KEY (course_id) REFERENCES course(id) 
);

3. NEW

Insert query results

INSERT INTO table_name [(column [, column ...])] SELECT ...

Create a user table with name, email, sex of sex and mobile phone number fields. You need to copy in the existing student data. The fields that can be copied are name and qq_mail

-- Create user table 
DROP TABLE IF EXISTS test_user; 
CREATE TABLE test_user (
    id INT primary key auto_increment,    
    name VARCHAR(20) comment 'Full name',    
    age INT comment 'Age',    
    email VARCHAR(20) comment 'mailbox',    
    sex varchar(1) comment 'Gender',    
    mobile varchar(20) comment 'Cell-phone number' );
-- Copy all data from student table to user table 
insert into test_user(name, email) select name, qq_mail from student;

4. query

4.1 aggregate query

4.1.1 aggregate function

Common operations such as counting the total number and calculating the parity value can be implemented by using aggregate functions, such as

COUNT

-- Count the number of students in the class 
SELECT COUNT(*) FROM student; 
SELECT COUNT(0) FROM student;
-- Statistics collected by the class qq_mail How many are there, qq_mail by NULL Data of will not be included in the result 
SELECT COUNT(qq_mail) FROM student;

SUM

-- Total score of Statistical Mathematics 
SELECT SUM(math) FROM exam_result;
-- Fail, < 60 Total score of, no result, return NULL 
SELECT SUM(math) FROM exam_result WHERE math < 60; 

AVG

--Statistical average total score 
SELECT AVG(chinese + math + english) average total score from exam [result;

MAX

-- Return to the highest score in English 
SELECT MAX(english) FROM exam_result;

MIN

-- Return > 70 Minimum math score above 
SELECT MIN(math) FROM exam_result WHERE math > 70;

4.1.2 GROUP BY clause

Use the GROUP BY clause in SELECT to group the specified columns. Need to meet: when GROUP BY is used for group query, the field specified by SELECT must be "GROUP BY field", and other fields must be included in the aggregation function if they want to appear in SELECT.

select column1, sum(column2), .. from table group by column1,column3;

Prepare test table and data: employee table, including id (primary key), name (name), role (role), salary (salary)

create table emp(
    id int primary key auto_increment,    
    name varchar(20) not null,    
    role varchar(20) not null,    
    salary numeric(11,2) 
);
    
insert into emp(name, role, salary) values 
('Jack Ma','Waiter', 1000.20), 
('pony','Play with game', 2000.99), 
('Sun WuKong','Game character', 999.11), 
('Pig incompetence','Game character', 333.5), 
('Sand monk','Game character', 700.33), 
('Old king next door','Chairman', 12000.66);

Query the maximum wage, minimum wage and average wage of each role

select role,max(salary),min(salary),avg(salary) from emp group by role;

4.1.3 HAVING

After grouping the GROUP BY clause, when it is necessary to filter the grouping result conditionally, you cannot use the WHERE statement, but you need to use HAVING

Show roles with an average wage of less than 1500 and their average wage
select role,max(salary),min(salary),avg(salary) from emp group by role 
having avg(salary)<1500; 

4.2 joint query

If the data comes from different tables, you need to query multiple tables jointly. Multi table query is to take Cartesian product for data of multiple tables:

insert into classes(name, `desc`) values 
('Computer department class 1, grade 2019', 'Learning the principles of computer C and Java Language, data structure and algorithm'), 
('Class 3, grade 2019, Chinese Department','Learning Chinese traditional literature'), 
('Automation class 5, 2019','Learned mechanical automation');

insert into student(sn, name, qq_mail, classes_id) values 
('09982','Black Whirlwind Li Kui','xuanfeng@qq.com',1), 
('00835','The Grapes',null,1), ('00391','Bai Suzhen',null,1), 
('00031','Xu Xian','xuxian@qq.com',1), 
('00054','I don't want to graduate.',null,1), 
('51234','Speak in a normal way','say@qq.com',2), 
('83223','tellme',null,2), 
('09527','Foreigners learn Chinese','foreigner@qq.com',2);

insert into course(name) values 
('Java'),('Chinese traditional culture'),('Principles of computer'),('Chinese'),('Higher order mathematics'),('English');

insert into score(score, student_id, course_id) values 
-- Black Whirlwind Li Kui 
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6), 
-- The Grapes 
(60, 2, 1),(59.5, 2, 5), 
-- Bai Suzhen 
(33, 3, 1),(68, 3, 3),(99, 3, 5), 
-- Xu Xian 
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6), 
-- I don't want to graduate. 
(81, 5, 1),(37, 5, 5), 
-- Speak in a normal way 
(56, 6, 2),(43, 6, 4),(79, 6, 6), 
-- tellme 
(80, 7, 2),(92, 7, 6);

4.2.1 internal connection

Syntax:

select field from table 1 alias 1 [inner] join table 2 alias 2 on connection condition and other conditions; 
select field from table 1 alias 1, table 2 alias 2 where connection condition and other conditions;

(1) Query "Xu Xian" students' scores

select sco.score from student stu inner join score sco on stu.id=sco.student_id and stu.name='Xu Xian'; 
-- perhaps 
select sco.score from student stu, score sco where stu.id=sco.student_id and stu.name='Xu Xian';

(2) To query the total scores and personal information of all students:

-- The score table is many to one relation to the student table. The total score is inquired according to the students in the score table id To group 
SELECT
    stu.sn,    
    stu.NAME,    
    stu.qq_mail,    
    sum( sco.score ) 
FROM
    student stu    
    JOIN score sco ON stu.id = sco.student_id 
GROUP BY
    sco.student_id;

(3) To query the scores and personal information of all students:

-- All the students found out are those who have achievements, but the "foreigners learning Chinese" students didn't show up 
select * from student stu join score sco on stu.id=sco.student_id;

-- Associated query of three tables of student table, score table and course table 
SELECT
    stu.id,    
    stu.sn,    
    stu.NAME,    
    stu.qq_mail,    
    sco.score,    
    sco.course_id,    
    cou.NAME 
FROM
    student stu    
    JOIN score sco ON stu.id = sco.student_id    
    JOIN course cou ON sco.course_id = cou.id 
ORDER BY 
    stu.id; 

4.2.2 external connection

The outer connection is divided into left outer connection and right outer connection. If the table on the left side is fully displayed, we call it a left outer join; if the table on the right side is fully displayed, we call it a right outer join.
Syntax:

--Left outer connection, table 1 shows completely 
select field name from table name 1 left join table name 2 on connection condition;

--Right outer connection, table 2 shows completely 
select field from table name 1 right join table name 2 on connection condition;

Query the scores of all students and their personal information. If the student has no scores, it also needs to be displayed

-- ""Foreigners learn Chinese" students have no test results, but also show that 
select * from student stu left join score sco on stu.id=sco.student_id; 

-- The corresponding right outer connection is: 
select * from  score sco right join student stu on stu.id=sco.student_id;

-- Associated query of three tables: student table, score table and course table 
SELECT
    stu.id,    
    stu.sn,    
    stu.NAME,    
    stu.qq_mail,    
    sco.score,    
    sco.course_id,    
    cou.NAME 
FROM
    student stu
    LEFT JOIN score sco ON stu.id = sco.student_id    
    LEFT JOIN course cou ON sco.course_id = cou.id 
ORDER BY
    stu.id;

4.2.3 self connection

Self join refers to joining the same table to query itself.

Display all the scores of "principles of computer" higher than "Java"

-- First query "computer principle" and“ Java"Curriculum id 
select id,name from course where name='Java' or name='Principles of computer';

-- Query the score ratio of "computer principle" in the score table again“ Java"Good information 
SELECT
    s1.* FROM    
    score s1,    
    score s2 
WHERE    
    s1.student_id = s2.student_id 
    AND s1.score < s2.score     
    AND s1.course_id = 1     
    AND s2.course_id = 3;
    
-- It can also be used join on Statement for self join queries 
SELECT
    s1.* 
FROM
    score s1
    JOIN score s2 ON s1.student_id = s2.student_id     
    AND s1.score < s2.score     
    AND s1.course_id = 1     
    AND s2.course_id = 3;

The above query only displays the score information and is executed in a distributed way. To display student and grade information, in a single statement:

SELECT
    stu.*,    
    s1.score Java,    
    s2.score Principles of computer 
FROM    
    score s1    
    JOIN score s2 ON s1.student_id = s2.student_id    
    JOIN student stu ON s1.student_id = stu.id    
    JOIN course c1 ON s1.course_id = c1.id    
    JOIN course c2 ON s2.course_id = c2.id     
    AND s1.score < s2.score     
    AND c1.NAME = 'Java'     
    AND c2.NAME = 'Principles of computer';

4.2.4 subquery

Subquery refers to the select statement embedded in other sql statements, also known as nested query

Single line sub query: return the sub query query query of one line record and the classmate of "don't want to graduate":

select * from student where classes_id=(select classes_id from student where name='I don't want to graduate.');

Multiline subquery: returns the subquery of multiline records
Case: query the score information of "Chinese" or "English" courses

1. [NOT] IN keyword:

-- Use IN 
select * from score where course_id in (select id from course where name='Chinese' or name='English');

-- Use NOT IN 
select * from score where course_id not in (select id from course where name!='Chinese' and name!='English'); 

You can use multiple columns to include:

 -- Insert duplicate scores: score, student_id, course_id Column repetition 
 insert into score(score, student_id, course_id) values 
 -- Black Whirlwind Li Kui 
 (70.5, 1, 1),(98.5, 1, 3), 
 -- The Grapes 
 (60, 2, 1);
 
-- Query for duplicate scores 
SELECT
    * 
FROM
    score 
WHERE
    ( score, student_id, course_id ) IN ( SELECT score, student_id, course_id FROM score GROUP BY 
score, student_id, course_id HAVING 
count( 0 ) > 1 );

1. [NOT] EXISTS keyword:

 -- Use EXISTS 
 select * from score sco where exists (select sco.id from course cou 
 where (name='Chinese' or name='English') and cou.id = sco.course_id);
 
-- Use NOT EXISTS 
select * from score sco where not exists (select sco.id from course cou 
where (name!='Chinese' and name!='English') and cou.id = sco.course_id);

Use subqueries in the from clause: subquery statements appear in the from clause. Here we need to use the technique of data query to use a subquery as a temporary table.

Query all scores higher than the average score of "Class 3, grade 2019 of Chinese Department"

-- Obtain the average score of "Class 3, grade 2019, Chinese Department" as a temporary table 
SELECT
    avg( sco.score ) score 
FROM
    score sco    
    JOIN student stu ON sco.student_id = stu.id    
    JOIN classes cls ON stu.classes_id = cls.id 
WHERE
    cls.NAME = 'Class 3, grade 2019, Chinese Department';

Query the scores higher than the average score of the above temporary table

SELECT
    * 
FROM
    score sco,
    (    
    SELECT 
        avg( sco.score ) score 
    FROM    
        score sco
        JOIN student stu ON sco.student_id = stu.id 
        JOIN classes cls ON stu.classes_id = cls.id       
    WHERE 
        cls.NAME = 'Class 3, grade 2019, Chinese Department' 
    ) tmp 
WHERE
    sco.score > tmp.score;

4.2.5 consolidated query

In practical application, in order to merge the execution results of multiple select, you can use the set operator union, union all. When using union and union all, the fields in the result set of the front and back queries need to be consistent.
Union this operator is used to get the union of two result sets. When you use this operator, the duplicate rows in the result set are automatically removed.

Query the course with id less than 3 or name "English":
select * from course where id<3 
union 
select * from course where name = 'English';

--Or use or to implement 
Select * from course where id < 3 or name = 'English';

union all this operator is used to get the union of two result sets. When this operator is used, duplicate rows in the result set are not removed.

Query courses with id less than 3 or name "Java"
--You can see duplicate data Java in the result set 
select * from course where id<3 
union all 
select * from course where name = 'English'; 

1. summary