Lesson 5 Basic _ multi-table query

1. Multi-table relations

In project development, when designing database table structure, the table structure will be analyzed and designed according to the business requirements and the relationship between business modules. Because business is related to each other, there are also various connections among the table structures, which can be divided into three basic types:

1. One-to-many (many-to-one)

Case Study: Part of Employee Relationships

Relationships: one door for multiple employees, one employee for a department

Implement: foreign keys are created on more than one side, and primary keys on one side

2. Many-to-many

Case study: the relationship between students and courses

Relationships: One student can choose more than one course, or a relocated course can be chosen by more than one student

Implementation: Establish a third intermediate table that contains at least two foreign keys and associates the primary keys of both parties

1. Cases

-- Multi-table relations
-- Many-to-many
-- 1.Create Student Table
create table student(
    id int auto_increment primary key comment 'Primary key ID',
    name varchar(10) comment 'Full name',
    no varchar(10) comment 'School Number'
)comment 'Student table';
insert into student values
    (null,'Dili Reba',2000100101),
    (null,'Li Qin',2000100102),
    (null,'Korea Oba',2000100103),
    (null,'Guan Xiaotong',2000100104);
-- 2.Create Course Schedule
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,'MySQL'),
    (null,'Cocos'),
    (null,'Html');
-- 3.Create intermediate tables
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 Course Intermediate Table';
insert into student_course values
    (null,1,1),
    (null,1,2),
    (null,1,3),
    (null,2,2),
    (null,2,3),
    (null,3,4);

(2) Graphical view table relations

Right-click the middle table student_course_Diagrams_Show Visualzation

3.One-to-one

Case: Used in relation to user details

Relationships: one-to-one relationship, mostly split with the form, put the basic fields of one table in one table, other fragrance fields in another table, to enhance the operation of small green

Implementation: Add a foreign key to either party, associate the other party's primary key, and set the foreign key to be unique (UNIQUE)

1. Cases

--Multi-table relations
--One-on-one
--1.Create User Basic Information Table
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';
--2.Create User Education Information Table
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 table';
--3.Add data
insert into tb_user(id,name,age,gender,phone) values
    (null,'Luo Dayou',45,'1','18804545550'),
    (null,'Linyilian',32,'2','18804545888'),
    (null,'Zhao Chuan',41,'1','13836699888'),
    (null,'Li Zongsheng',43,'1','15845425870');

insert into tb_user_edu(id,degree,major,primaryschool,middleschool,university,userid) values
    (null,'Undergraduate','Dance','Jihong Primary School','Harbin 69 Middle','Beijing Dance Institute',1),
    (null,'master','perform','Coal Machine Primary School','Jiamusi No.1 Middle School','Beijing Film Academy',2),
    (null,'Undergraduate','vocal music','Garden Street Elementary School','Harbin 3rd Middle School','China Media University',3),
    (null,'Undergraduate','Dance','Jiamusi Xiao','Jiamusi No.1 Middle School','Tsinghua University',4);

2. Overview of multi-table queries

Overview: Querying data from long tables

Cartesian product: All combinations of sets A and B. (Invalid Cartesian product needs to be eliminated in multi-table queries)

1. Data preparation

--multi-table query
--1.Data preparation
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 'Enrollment Time',
    managerid int comment 'Direct Leadership ID',
    dept_id int comment 'department ID'
)comment 'Employee Sheet';
insert into emp(id,name,age,job,salary,entrydate,managerid,dept_id) values
    (1,'Jin Yong',66,'CEO',20000,'2000-01-01',null,5),
    (2,'Zhang Wuji',20,'project manager',12500,'2005-12-05',1,1),
    (3,'Yang Xiao',33,'Development',8400,'2000-11-03',2,1),
    (4,'Xiangr',48,'Development',11500,'2002-02-05',2,1),
    (5,'Zhao Min',22,'Development',10500,'2004-09-07',3,1),
    (6,'Xiao Zhao',19,'Trainee',6600,'2004-10-12',2,1),
    (7,'Extinction',60,'Financial Director',12500,'2002-09-12',1,3),
    (8,'Zhou Zhinuo',19,'accounting',4500,'2006-10-12',7,3),
    (9,'Ding Minjun',23,'Cashier',5200,'2009-10-12',7,3),
    (10,'Dragon King Purple',25,'Director of Marketing',12600,'2001-10-18',1,2),
    (11,'Deer Cuspid',56,'Staff member',3700,'2006-10-03',10,2),
    (12,'Hedgehog',55,'Staff member',3700,'2007-05-09',10,2),
    (13,'Duan Yu',19,'Staff member',3700,'2009-10-29',10,2),
    (14,'Zhang Sanfen',66,'Sales Director',16600,'2004-09-11',1,4),
    (15,'False Bamboo',28,'Sale',3700,'2009-10-12',14,4),
    (16,'Look crazy',35,'Trainee',6600,'2007-12-25',14,4),
    (17,'Sweeping monk',89,null,2600,'1985-10-01',1,null);
--2.Add Foreign Key
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

2. Implementation of multi-table query

--Multi-table Query and Elimination of Cartesian Product
select * from emp,dept where emp.dept_id=dept.id;

3. Internal connection

The inner join query is the intersection of two tables

1. Implicit inner connection

SELECT Field List FROM Table 1, Table 2 WHERE Conditions...

2. Display internal connections

SELECT Field List FROM Table 1[INNER] JOIN Table 2 ON Join Conditions...;

3. Cases

--multi-table query_Interior Connection Demo
--1.Query employee name and associated department name(Implicit Inner Connection)
--Table structure:emp,dept
--Connection Conditions:emp.dept_id=dept.id
select emp.name,dept.name from emp,dept where emp.dept_id=dept.id;
--2.Query employee name and associated department name(Explicit inner connection)
--Table structure:emp,dept
--Connection Conditions:emp.dept_id=dept.id
select e.name,d.name from emp e inner join dept d on e.dept_id = d.id;

4. External Connection

1. Left outer connection

Query Table 1 (left table) All data contains data from the intersection of tables 1 and 2

SELECT Field List FROM Table 1 LEFT [OUTER] JOIN Table 2 ON Conditions...

2. Right Outer Connection

Query Table 2 (Right Table) All data contains data from the intersection of tables 1 and 2

SELECT Field List FROM Table 1 RIGHT [OUTER] JOIN Table 2 ON Conditions...

3. Cases

--multi-table query_External Connection Demo
--1.query emp Table all data and corresponding department name(Left Outer Connection)
--Table structure:emp,dept
--Connection Conditions:emp.dept_id=dept.id
select e.*,d.name from emp e left join dept d on d.id = e.dept_id;
--2.query dept All data of the table and corresponding employee information(Right Outer Connection)
--Table structure:emp,dept
--Connection Conditions:emp.dept_id=dept.id
select d.*,e.* from emp e right join dept d on d.id = e.dept_id;

5. Self-connection

1. Concepts

A self-joined query can be either an inner joined query or an outer joined query.

2. Grammar

SELECT Field List FROM Table A Alias A JOIN Table A Alias B ON Condition...;

3. Cases

--multi-table query_Self-Connecting Demo
--1.Query the names of employees and their leaders
--Table structure:emp
--Connection Conditions:a.managerid=b.id;
select a.name,b.name from emp a,emp b where a.managerid=b.id;
--2.Query the names of employees and their leaders,Without leadership,Also need to be queried
--Table structure:emp a,emp b
--Connection Conditions:--Connection Conditions:a.managerid=b.id;
select a.name 'staff',b.name 'Leader' from emp a left join emp b on a.managerid=b.id;

Six. Joint Query

1. Concepts

Combine the results of multiple queries to form a new set of query results.

2. Grammar

SELECT Field List FROM Table A...

UNION [ALL]

SELECT Field List FROM Table B...;

3. Cases

--multi-table query_Joint Query Demo
--1.Query employees with salaries below 5000 and age over 50.
--union all--Show all without removing duplicate data
select * from emp where salary < 5000
union all
select * from emp where age >50;
--union--All Displays Remove Duplicate Data
select * from emp where salary < 5000
union
select * from emp where age >50;

Note: The number of columns in the long table of a federated query must be maintained and the field type must be consistent.

7. Subquery

1. Concepts

Nested SELECT statements in SQL statements

2. Grammar

SELECT * FROM t1 WHERE column1=(SELECT column1 FROM t2);

The statement outside the subquery can be either INSERT/UPDATE/DELETE/SELECT.

3. Classification

(1) Scalar quantum query (subquery result is a single value)

The result returned by the subquery is a single value (number, string, date, etc.), in its simplest form

Common operators: = <> > >= < <=

--multi-table query_Subquery Demo
--scalar subquery
--1.Query all employees in sales
select * from emp where dept_id=(select id from dept where name='Sales Department');
--2.Query all employee information after Qiao Feng's entry
select * from emp where entrydate > (select entrydate from emp where name= 'Look crazy');

(2) Column subquery (subquery results are one column)

Subquery returns a single column (which can be multiple rows)

Common operators: IN,NOT IN,ANY,SOME,ALL

Operator	describe
IN	Select more than one within the specified set
NOT IN	Not within the set
ANY	Subquery returns a list, any one of which is satisfied
SOME	As with ANY, ANY can be used wherever SOME is used
ALL	All values of the subquery return list must be satisfied

--multi-table query_Subquery Demo
--Column Subquery
--1.Query all employees in sales and marketing
select * from emp where dept_id in (select id from dept where name='Sales Department' or name='Marketing Department');
--2.Query employee information that is higher than everyone in the Finance Department
select * from emp where salary > all (select salary from emp where dept_id=(select id from dept where name='Finance Department'));
--3.Query information about employees who are paid more than either of them in the R&D Department
select * from emp where salary > any (select salary from emp where dept_id=(select id from dept where name='R&D Department'));

(3) Row subquery (the result of subquery is one row)

Subquery returns one row (can have multiple columns)

Common operators: =, <>, IN, NOT IN

--multi-table query_Subquery Demo
--Row subquery
--1.Query the same employee information as Zhang Wuji's salary and direct leadership
select * from emp where (salary,managerid)=(select salary,managerid from emp where name='Zhang Wuji');

(4) Table subquery (the result of subquery is multiple rows and columns)

Subquery returns multiple rows and columns

Common operators:IN

--multi-table query_Subquery Demo
--Table Subquery
--1.Queries and Rotifers,Employee information for Hepeng with the same position and salary
select * from emp where (job,salary) in (select job,salary from emp where name='Deer Cuspid' or name='Hedgehog');
--2.Query entry date is 2007-01-01 Subsequent Employee Information and Departmental Information
select * from (select * from emp where entrydate>'2007-01-01') e left join dept d on e.dept_id=d.id;

8. Multi-table Query Cases

1. Data preparation: emp table, dept table, add salgrade table

--multi-table query_Case Demonstration
--Create salary scale salgrade
create table salgrade(
    grade int,
    losal int,
    hisal int
)comment 'PayLevels';
insert into salgrade values
    (1,0,3000),
    (2,3001,5000),
    (3,5001,8000),
    (4,8001,10000),
    (5,10001,15000),
    (6,15001,20000),
    (7,20001,25000),
    (8,25001,30000);

2. Cases

--multi-table query_Case Demonstration
--1.Query employee's name,Age,Position department information(Implicit Inner Connection)
--surface:emp,dept
--Connection Conditions:emp.dept_id=dept.id
select e.name,e.age,e.job,d.name from emp e ,dept d where e.dept_id=d.id;
--2.Query the names of employees younger than 30,Age,position,Department Information(Explicit inner connection)
--surface:emp,dept
--Connection Conditions:emp.dept_id=dept.id
select e.name,e.age,e.job,d.name from emp e inner join dept d on e.dept_id = d.id where e.age<30;
--3.Query departments with employees ID,Department Name
select distinct d.id, d.name from emp e,dept d where e.dept_id=d.id;
--4.Query all employees older than 40,And all its department names,Explicit if no department is assigned
select e.name,d.name from emp e left join dept d on e.dept_id=d.id where e.age>40;
--5.Query salary ratings for all employees
--surface:emp,salgrade
--Connection Conditions: emp.salary >= salagrade.losal and emp.salary <= salgrade.hisal
select e.* , s.grade from emp e,salgrade s where e.salary >=s.losal and e.salary <= s.hisal;
select e.* , s.grade from emp e,salgrade s where e.salary between s.losal and s.hisal;
--6.Query all R&D staff information and salary levels
--surface:emp,salgrade,dept
--Connection Conditions:emp.salary between salgrade.losal and salgrade.hisal ,emp.dept_id=dept.id
--query criteria:dept.name='R&D Department'
select e.*,s.grade from emp e,salgrade s,dept d where (e.salary between s.losal and s.hisal) and e.dept_id=d.id and d.name='R&D Department';
--7.Query the average salary of R&D employees
select avg(e.salary) from emp e,dept d where e.dept_id=d.id and d.name='R&D Department';
--8.Query information about employees whose wages are higher than extinction
select * from emp where emp.salary>(select salary from emp where name='Extinction');
--9.Query employee information above average salary
select * from emp where salary>(select avg(salary) from emp);
--10.Query employee information below the average salary of the Department
select * from emp e2 where e2.salary<(select avg(e1.salary) from emp e1 where e1.dept_id=e2.dept_id );
--11.Query all department information,Number of employees in the military Statistics Department
select d.id,d.name,(select count(*) from emp e where e.dept_id=d.id) 'Number of employees'from dept d;
--12.Query all students about their course selections,Show the student name,School Number,Course Name
--surface:student,course,student_course
--Connection Conditions:student.id=student_course.studentid,course.id=student_course.courseid
select * from student s,student_course sc,course c where s.id=sc.studentid and sc.courseid=c.id;

3. Code Formatting

Right-click the selected snippet_Reformat Code

Keywords: MySQL TypeScript Game Development UI

Added by joeiscoolone on Mon, 07 Mar 2022 19:47:01 +0200

Programming VIP

Lesson 5 Basic _ multi-table query

Lesson 5 Basic _ multi-table query

1. Multi-table relations

1. One-to-many (many-to-one)

2. Many-to-many

1. Cases

(2) Graphical view table relations

3.One-to-one

1. Cases

2. Overview of multi-table queries

1. Data preparation

2. Implementation of multi-table query

3. Internal connection

1. Implicit inner connection

2. Display internal connections

3. Cases

4. External Connection

1. Left outer connection

2. Right Outer Connection

3. Cases

5. Self-connection

1. Concepts

2. Grammar

3. Cases

Six. Joint Query

1. Concepts

2. Grammar

3. Cases

7. Subquery

1. Concepts

2. Grammar

3. Classification

(1) Scalar quantum query (subquery result is a single value)

(2) Column subquery (subquery results are one column)

(3) Row subquery (the result of subquery is one row)

(4) Table subquery (the result of subquery is multiple rows and columns)

8. Multi-table Query Cases

1. Data preparation: emp table, dept table, add salgrade table

2. Cases

3. Code Formatting

Popular Keywords