Previous: I'm learning database at station b (VI): regular expressions in DQL

1, Multi table relation
It can be summarized as: one to one, one to many / many to one relationship, many to many

1. One to one relationship
Example: one ID card can only correspond to one person

be careful:
(1) Add a unique foreign key in any table to point to the other party's primary key to ensure a one-to-one relationship.
(2) Generally, one-to-one relationship is rare. It is better to merge the tables that encounter one-to-one relationship.

2. One to many / many to one relationship
For example: Department and employee, one employee can only correspond to one department, and one department has multiple employees

Realization principle:
Establish a foreign key on the side with more than one, and point to the primary key of the side with one

3. Many to many relationship
For example: students and courses, a student can choose multiple courses, and a course can also be selected by many students

Third, you need to realize multiple pairs of intermediate relations with the help of multiple tables. The intermediate table contains at least two fields. Split the many to many relationship into one to many relationship. The intermediate table must have at least two foreign keys, which point to the primary keys of the original two tables respectively

2, Foreign key constraints (one to many relationships)

1. Concept
MySQL foreign key constraint is a special field of a table, which is often used together with primary key constraint. For two associated tables, the table with the primary key in the associated field is the master table (parent table), and the table with the foreign key is the slave table (child table).

2. When defining a foreign key, you need to follow the following rules
(1) The main table must already exist in the database or be the table currently being created.
(2) You must define a primary key for the primary table.
(3) The primary key cannot contain null values, but null values are allowed in foreign keys. In other words, as long as each non null value of the foreign key appears in the specified primary key, the content of the foreign key is correct.
(4) Specify the column name or combination of column names after the table name of the main table. This column or combination of columns must be the primary key or candidate key of the main table.
(5) The number of columns in the foreign key must be the same as that in the primary key of the main table.
(6) The data type of the column in the foreign key must be the same as that of the corresponding column in the primary key of the main table.

3. Example

4. Create foreign key constraints
Method 1: set when creating a table
Syntax: [constraint < foreign key name >] foreign key field name [, field name 2,...] references < main table name > primary key column 1 [, primary key column 2,...]
Operation:

#Create department table
create table if not exists dept(
  deptno varchar(20) primary key ,  -- Department number
  name varchar(20) -- Department name
);
#Create employee table and create foreign key constraints
create table if not exists emp(
  eid varchar(20) primary key , -- Employee number
  ename varchar(20), -- Employee name
  age int,  -- Employee age
  dept_id varchar(20),  -- Employee's Department
  constraint emp_fk foreign key (dept_id) references dept (deptno) –- Foreign key constraint
);

Method 2: set after creating a table
Syntax: alter table < data table name > add constraint < foreign key name > foreign key (< column name >) references < main table name > (< column name >);
Operation:

-- Create department table
create table if not exists dept2(
  deptno varchar(20) primary key ,  -- Department number
  name varchar(20) -- Department name
);
-- Create employee table
create table if not exists emp2(
  eid varchar(20) primary key , -- Employee number
  ename varchar(20), -- Employee name
  age int,  -- Employee age
  dept_id varchar(20)  -- Employee's Department
 
);
-- Create foreign key constraints
alter table emp2 add constraint dept_id_fk foreign key(dept_id) references dept2 (deptno);

5. Data operation under foreign key constraints

-- 1,Add master table data
 insert into dept values('1001','R & D department');
 insert into dept values('1002','Sales Department');
 -- 2,Add data from table
insert into emp values('7','Jiu Mozhi',50, '1003'); 
insert into emp values('8','Tong lao ',60, '1005');  -- may not
 -- 3,Delete data
 delete from dept where deptno = '1004'; -- Can delete
 delete from dept where deptno = '1001'; -- Cannot delete

be careful:
(1) You must add data to the main table first
(2) When adding data to the slave table, the value of the foreign key column cannot be written casually. It must depend on the primary key column of the primary table
(3) When the data of the master table is dependent on the slave table, it cannot be deleted, otherwise it can be deleted. The data from the table can be deleted at will.

6. Delete foreign key constraint
Syntax: alter table < table name > drop foreign key < foreign key constraint name >;
Operation:

#Delete foreign key constraint dept_id_fk
alter table emp2 drop foreign key dept_id_fk;

3, Foreign key constraints (many to many relationships)
The implementation of many to many relationship needs the help of the third intermediate table. The intermediate table contains at least two fields. Split the many to many relationship into one to many relationship. The intermediate table must have at least two foreign keys, which point to the primary keys of the original two tables respectively

1. Example:

2. Operation:

-- 1 Create student table student(Left main table)
   create table if not exists student(
    sid int primary key auto_increment,
    name varchar(20),
    age int,
    gender varchar(20)
   );
  -- 2 Create Curriculum course(Right main table)
  create table course(
   cid  int primary key auto_increment,
   cidname varchar(20)
  );
-- 3 Create intermediate table student_course/score(From table)
  create table score(
    sid int,
    cid int,
    score double
  );
  -- 4 Establish foreign key constraints(2 second)
 
alter table score add foreign key(sid) references student(sid);
alter table score add foreign key(cid) references course(cid);
-- 5 Add data to student table
insert into student values(1,'little dragon maiden',18,'female'),(2,'A Zi',19,'female'),(3,'Zhou Zhiruo',20,'male');
-- 6 Add data to the curriculum
insert into course values(1,'language'),(2,'mathematics'),(3,'English');
-- 7 Add data to intermediate table
insert into score values(1,1,78),(1,2,75),(2,1,88);

be careful:
During modification and deletion, the intermediate slave tables can be deleted and modified at will, but the data of the master tables on both sides dependent on the slave tables cannot be deleted or modified.

4, Multi table joint query
1. Concept
Multi table query is to query two or more tables at the same time, because sometimes when users view data, the data to be displayed comes from multiple tables.

2. Classification
(1) Cross connect query
(2) Internal connection query
(3) External connection query
(4) Subquery
(5) Table autocorrelation

3. Prepare to query example data

-- Create department table
create table if not exists dept3(
  deptno varchar(20) primary key ,  -- Department number
  name varchar(20) -- Department name
);
 
-- Create employee table
create table if not exists emp3(
  eid varchar(20) primary key , -- Employee number
  ename varchar(20), -- Employee name
  age int,  -- Employee age
  dept_id varchar(20)  -- Employee's Department
);
-- to dept3 Add data to table
insert into dept3 values('1001','R & D department');
insert into dept3 values('1002','Sales Department');
insert into dept3 values('1003','Finance Department');
insert into dept3 values('1004','Ministry of Personnel');
-- to emp Add data to table
insert into emp3 values('1','Qiao Feng',20, '1001');
insert into emp3 values('2','duan yu',21, '1001');
insert into emp3 values('3','Phyllostachys pubescens',23, '1001');
insert into emp3 values('4','A Zi',18, '1001');
insert into emp3 values('5','Sweeping monk',85, '1002');
insert into emp3 values('6','Li Qiushui',33, '1002');
insert into emp3 values('7','Jiu Mozhi',50, '1002'); 
insert into emp3 values('8','Tong lao ',60, '1003');
insert into emp3 values('9','Murongbo',58, '1003');
insert into emp3 values('10','Ding Chunqiu',71, '1005');

5, Cross connect query
1. Characteristics
(1) The cross join query returns the Cartesian product of all data rows of the two connected tables
(2) Cartesian product can be understood as that each row of one table matches any row of another table
(3) If table A has m rows of data and table B has n rows of data, return m*n rows of data
(4) Cartesian product will produce a lot of redundant data, and other queries in the later stage can be conditionally filtered on the basis of this set

2. Format:
select * from table 1, table 2, table 3;

3. Operation:

select * from dept3,emp3;

result:

6, Internal connection query
1. Inner join query to find the intersection of multiple tables

2. Classification
Implicit inner connection and display inner connection

3. Format
Implicit inner connection: select * from a, B, where condition;
Display inner join: select * from A inner join B on condition;

4. Operation:

-- Query the employees of each department
select * from dept3,emp3 where dept3.deptno = emp3.dept_id;   
select * from dept3 inner join emp3 on dept3.deptno = emp3.dept_id;

-- Query the employees of R & D department and sales department
select * from dept3,emp3 where dept3.deptno = emp3.dept_id and name in( 'R & D department','Sales Department');
select * from dept3 join emp3 on dept3.deptno = emp3.dept_id and name in( 'R & D department','Sales Department');
 
-- Query the number of employees in each department,Sort in ascending order
select deptno,count(1) as total_cnt from dept3,emp3 where dept3.deptno = emp3.dept_id group by deptno order by total_cnt;
select deptno,count(1) as total_cnt from dept3 join emp3 on dept3.deptno = emp3.dept_id group by deptno order by total_cnt;

-- Query departments with 3 or more employees and sort them in descending order
select deptno,count(1) as total_cnt from dept3,emp3 where dept3.deptno = emp3.dept_id group by deptno having total_cnt >= 3 order by total_cnt desc;
select deptno,count(1) as total_cnt from dept3 join emp3 on dept3.deptno = emp3.dept_id group by deptno having total_cnt >= 3 order by total_cnt desc;

7, External connection query

1. Classification
External connections are divided into left outer join, right outer join and full outer join.

2. Format
(1) left outer join
select * from A left outer join B on condition;
(2) right outer join
select * from A right outer join B on condition;
(3) full outer join
select * from A full outer join B on condition;

3. Operation

-- Query which departments have employees and which departments have no employees
select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id;
 
-- Query which employees have corresponding departments and which do not
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;
 
 
-- use union Keyword to realize the union of left outer connection and right outer connection
select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id
union 
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;

8, Subquery

1. Concept
Subquery refers to a form of writing in which several small queries with different functions are nested in a complete query statement to complete complex queries together. Generally speaking, it is a query containing select nesting.

2. Operation

-- Query the information of the oldest employee, and the display information includes employee number, employee name and employee age
select eid,ename,age from emp3 where age = (select max(age) from emp3);
 
 
-- Query the employee information of R & D department and sales department in, including employee number and employee name
select eid,ename,t.name from emp3 where dept_id in (select deptno,name from dept3 where name = 'R & D department' or name = 'Sales Department') ;
 
 
-- Query the information of employees under the age of 20 in the R & D department,Including employee number, employee name and department name
select eid,age,ename,name from (select * from dept where name = 'R & D department ')t1,(select * from emp3 where age <20)t2

3. Subquery Keywords: all, any, some, in, exists

4. Keyword all
(1) Format
select... from... Where C > all
– equivalent to:
select ...from ... where c > result1 and c > result2 and c > result3
(2) Characteristics
- ALL: if it is true compared with ALL the values returned by the subquery, it will return true
- ALL can be used in combination with =, >, > =, <, < =, < >, indicating equal to, greater than, greater than or equal to, less than, less than or equal to, and not equal to ALL the data.
- ALL means that the value in the specified column must be greater than each value in the sub query set, that is, it must be greater than the maximum value of the sub query set; If it is less than, it is less than the minimum value of the sub query set. Similarly, other comparison operators can be deduced.
(3) Operation

-- Query age is older than'1003'Employee information of all ages in the Department
select * from emp3 where age > all(select age from emp3 where dept_id = '1003');

-- Query information that does not belong to any department 
select * from emp3 where dept_id != all(select deptno from dept3); 

5. Keywords any and some
(1) Format
select... from... Where C > any (query statement)
– equivalent to:
select ...from ... where c > result1 or c > result2 or c > result3
(2) Characteristics
- ANY: if it is true compared with ANY value returned by the subquery, it returns true
- ANY can be used in combination with =, >, > =, <, < =, < >, which means equal to, greater than, greater than or equal to, less than, less than or equal to, and not equal to ANY of the data.
- indicates that the value in the specified column must be greater than any value in the sub query, that is, it must be greater than the minimum value in the sub query set. Similarly, other comparison operators can be deduced.
- the functions of home and ANY are the same. Home can be understood as an alias of ANY
(3) Operation

-- Query age is older than'1003'Employee information of any employee age in the Department
select * from emp3 where age > all(select age from emp3 where dept_id = '1003');

6. Keyword in
(1) Format
select... from... Where C in (query statement)
– equivalent to:
select ...from ... where c = result1 or c = result2 or c = result3
(2) Characteristics
- IN keyword, used to judge whether the value of a record is IN the specified set
- add not before the IN keyword to reverse the condition
(3) Operation

-- Query the employee information of R & D department and sales department, including employee number and employee name
select eid,ename,t.name from emp3 where dept_id in (select deptno from dept3 where name = 'R & D department' or name = 'Sales Department') ;

7. Keyword exists
(1) Format
select... from... Where exists (query statement)
(2) Characteristics
- if the sub query has "data result" (at least one row of data is returned), the result of the EXISTS() is "true", and the outer query is executed
- if the sub query has "no data result" (no data is returned), the result of the EXISTS() is "false", and the outer query is not executed
- the subquery after EXISTS does not return any actual data, but only returns true or false. When it returns true, the where condition holds
- note that the EXISTS keyword is more efficient than the IN keyword. Therefore, it is recommended to use the EXISTS keyword IN practical development, especially when there is a large amount of data
(3) Operation

-- Query whether the company has employees older than 60. If yes, output
select * from emp3 a where exists(select * from emp3 b where a.age > 60);
 
-- Query the employee information of the Department to which you belong
select * from emp3 a where exists(select * from dept3 b where a.dept_id = b.deptno);

8, Auto Association query
1. Concept
MySQL sometimes needs to perform association query on the table itself during information query, that is, a table is associated with itself, and a table is used as multiple tables. Note that the table must be aliased when associated.

2. Format
select field list from table 1 a, table 1 b where conditions;
perhaps
select field list from table 1 a [left] join table 1 b on condition;

3. Operation

-- Create table,And establish self correlation constraints
create table t_sanguo(
    eid int primary key ,
    ename varchar(20),
    manager_id int,
 foreign key (manager_id) references t_sanguo (eid)  -- Add self associative constraints
);
-- Add data 
insert into t_sanguo values(1,'Liu Xie',NULL);
insert into t_sanguo values(2,'Liu Bei',1);
insert into t_sanguo values(3,'Guan Yu',2);
insert into t_sanguo values(4,'Fei Zhang',2);
insert into t_sanguo values(5,'Cao Cao',1);
insert into t_sanguo values(6,'Xu Chu',5);
insert into t_sanguo values(7,'Dianwei',5);
insert into t_sanguo values(8,'Sun Quan',1);
insert into t_sanguo values(9,'Zhou Yu',8);
insert into t_sanguo values(10,'Lu Su',8);
 
-- Perform association query
-- 1.Query the information of each person in the three countries and his superior, such as:  Guan Yu, Liu Bei 
select * from t_sanguo a, t_sanguo b where a.manager_id = b.eid;