MySQL four-2: multi-table Query

First, prepare the table

New Table

Mysql> CREATE TABLE Department (

ID int,

Name varchar (20)

);

Mysql> CREATE TABLE Employee (

ID int primary KEY auto_increment,

Name varchar (20),

Sex enum (' Male ', ' female ') not null default ' male ',

Age int,

dep_id int

);

Inserting data

Mysql> INSERT INTO Department values

(200, ' technology '),

(201, ' human Resources '),

(202, ' Sales '),

(203, ' operations ');

Mysql> INSERT into employee (NAME,SEX,AGE,DEP_ID) values

(' Egon ', ' Male ', 18,200),

(' Alex ', ' female ', 48,201),

(' Wupeiqi ', ' Male ', 38,201),

(' Yuanhao ', ' female ', 28,202),

(' Liwenzhou ', ' Male ', 18,200),

(' Jingliyang ', ' female ', 18,204)

;

View table structure and data

Mysql> DESC Department;

Mysql> DESC employee;

Mysql> SELECT * from department;

Mysql> SELECT * from employee;

Second, multi-table connection query

Focus!!! Outer link syntax

Elect field list from table 1 inner| Left| Right JOIN table 2 on table 1. field = table 2. field;

1. Cross connect: No matching conditions apply. Generate Cartesian product

Mysql> select * from Employee,department;

2. Internal connection: Only matching rows are connected

Find two common parts of the table, equivalent to the use of conditions from the Cartesian product results to filter out the correct results

Select Employee.id,employee.name,employee.age,employee.sex,department.name from employee inner JOIN department on Employee.dep_id=department.id;

The above SQL is equivalent to

Select Employee.id,employee.name,employee.age,employee.sex,department.name from Employee,department where Employee.dep_id=department.id;

Note: Department does not have 204 this department, so the employee table about 204 of this staff information does not match out

3, the external link to the left connection: priority to display the left table all records

To left table, that is to find all employee information, of course, including no Department of staff; The essence is: on the basis of the internal connection to increase the left side has no results

Select Employee.id,employee.name,department.name as Depart_name from employee left join department on Employee.dep_id= Department.id;

4, the right connection of the outside Link: priority to display all records of the right table

The right table shall prevail, that is, to identify all departmental information, including those that do not have an employee; the essence is: Add the right side to the bottom of the inner link.

Select Employee.id,employee.name,department.name as Depart_name from employee right join department on Employee.dep_id= Department.id;

5, all-out connection: show left and right two tables all records

On the basis of the inner connection, add the left side without the right side and the left side without the result.

Note: MySQL does not support full out-of-band join

Emphasis: MySQL can use this method to indirectly implement a full-outer connection

SELECT * FROM employee left JOIN department on employee.dep_id = department.id Union SELECT * FROM employee RIGHT join DEP Artment on employee.dep_id = department.id;

Note the difference between Union and Union: Union will remove the same record

Three, meet the conditions of connection query

Example 1: Querying the Employee and department tables in an internal connection, and the Age field value in the Employee table must be greater than 25, that is, identify employees older than 25 years old, and the department where the employee is located

Select Employee.name,department.name from employee inner JOIN department on employee.dep_id = department.id where > 25;

Example 2: Querying the Employee and department tables in an in-connection manner and displaying them in ascending order of the age field

Select Employee.id,employee.name,employee.age,department.name from employee,department where employee.dep_id = Department.id and Age > order by age ASC;

Four sub-query

1) A subquery is a query statement that is nested within another query statement.

2) query result of inner query statement, can provide query condition for outer query statement.

3) Sub-query can contain: In, not in, any, all, EXISTS and not EXISTS and other keywords

4) can also contain comparison operators: =,! =, >, <, and so on

1. Sub-query with in keyword

Check the department name of the average age over 25 years

Select Id,name from department where ID in (select dep_id from the employee GROUP by DEP_ID have AVG (age) > 25);

View technical staff Name

Select name from the employee where dep_id in (select ID from department where name= ' technology ');

View department names for less than 1 people (subqueries get someone's department ID)

Select name from department where ID not in (SELECT distinct dep_id from employee);

2. Subqueries with comparison operators

Comparison operators: =,! =, >, >=, <, <=, <>

Search for employee names and ages greater than the average age of everyone

Select Name,age from emp where > (select AVG (age) from EMP);

Search for employee names and ages greater than the average age within the department

Select T1.name,t1.age from emp T1 INNER JOIN (select DEP_ID,AVG (age) Avg_age from EMP Group by dep_id) t2 on t1.dep_id = t 2.dep_id where T1.age > t2.avg_age;

3. Sub-query with exists keyword

The EXISTS key word indicates existence. When you use the EXISTS keyword, the inner query statement does not return a record of the query. Instead, it returns a true and False value. True or FALSE, the outer query statement is queried when True is returned, and the outer query statement does not query when the return value is False

There are dept_id=203,ture in the Department table

SELECT * from the employee where exists (select ID from department where id=200);

There are dept_id=205,false in the Department table

SELECT * from the employee where exists (select ID from department where id=204);

4. Practice: Find the employee who has the latest entry in each department

Prepare the table:

Mysql>create Table Employee (

ID int not NULL unique auto_increment,

Name varchar () is not NULL,

Sex enum (' Male ', ' female ') not null default ' male ', #大部分是男的

Age int (3) unsigned NOT null default 28,

Hire_date date NOT NULL,

Post varchar (50),

Post_comment varchar (100),

Salary double (15,2),

Office int, #一个部门一个屋子

depart_id int

);

Mysql>insert into employee (name,sex,age,hire_date,post,salary,office,depart_id) values

(' Egon ', ' Male ', 18, ' 20170301 ', ' old boy ' diplomatic ambassador to Shahe Office ', 7300.33,401,1), #以下是教学部

(' Alex ', ' Male ', ' 20150302 ', ' teacher ', ' 1000000.31,401,1 '),

(' Wupeiqi ', ' Male ', Bayi, ' 20130305 ', ' teacher ', 8300,401, 1),

(' Yuanhao ', ' Male ', ' 20140701 ', ' teacher ', 3500,401, 1),

(' Liwenzhou ', ' Male ', ', ' 20121101 ', ' teacher ', 2100,401, 1),

(' Jingliyang ', ' female ', ' 20110211 ', ' teacher ', 9000,401, 1),

(' jinxin ', ' Male ', ' 19000301 ', ' teacher ', 30000,401, 1),

(' Dragon ', ' Male ', ' 20101111 ', ' teacher ', 10000,401, 1),

(' Crooked ', ' female ', ' 20150311 ', ' sale ', ' 3000.13,402,2 '), #以下是销售部门

(' Ya Ya ', ' female ', ' 20101101 ', ' sale ', ' 2000.35,402,2 '),

(' ding ', ' female ', ' 20110312 ', ' sale ', 1000.37,402,2),

(' stars ', ' female ', ' 20160513 ', ' sale ', 3000.29,402,2),

(' Princess ', ' female ', ' 20170127 ', ' sale ', ' 4000.33,402,2 '),

(' Chang ye ', ' Male ', ' 20160311 ', ' operation ', 10000.13,403,3), #以下是运营部门

(' Cheng Bite gold ', ' Male ', ' 19970312 ', ' Operation ', 20000,403, 3),

(' Cheng bites silver ', ' female ', ' 20130311 ', ' Operation ', 19000,403, 3),

(' Process bite copper ', ' male ', ' 20150411 ', ' Operation ', 18000,403, 3),

(' Cheng Bite iron ', ' female ', ' 20140512 ', ' Operation ', 17000,403, 3)

;

Answer 1: Linked list

SELECT * FROM employee as T1 INNER JOIN (

SELECT Post,max (hire_date) max_date from the employee GROUP by post) as t2 on t1.post = T2.post

WHERE t1.hire_date = t2.max_date;

Answer 2: Sub-query

Select T3.name,t3.post,t3.hire_date from employee as T3 where ID in (select (select ID from employee as T2 where t2.post=t 1.post ORDER BY hire_date desc LIMIT 1) from employee as T1 group by Post);

V. Comprehensive exercises

1, the preparation of tables, records

mysql> CREATE DATABASE db1;

mysql> use DB1;

mysql> source/root/init.sql (Import data from Init.sql file)

2. Topics

1) Check the name of all courses and the corresponding teacher's name

Select

Teacher.tname,

Course.cname

From

Teacher

Inner JOIN course on teacher.tid = course.teacher_id;

2) Find out how many men and women are in the student list.

Select Gender,count (SID) from student group by gender;

3) The name of the student who queried the physical result equal to 100

Select

Student.sname

From

Student

where

Sid in (

Select

student_id

From

Score

Inner JOIN course on score.course_id = Course.cid

WHERE

Course.cname = ' physical ' and score.num = 100);

4) The names and average scores of the students who have average scores greater than 80 points

Select

Student.sname,

T1.avg_num

From

Student

INNER JOIN (

Select

student_id

AVG (num) as Avg_num

From

Score

GROUP BY

student_id

Having

AVG (num) > 80

) as T1 on student.sid=t1.student_id;

5) Check all students ' student number, name, number of courses selected, total

SELECT

Student.sid,

Student.sname,

T1.course_num,

T1.total_num

From

Student

Left JOIN (

SELECT

STUDENT_ID,

COUNT (course_id) Course_num,

SUM (num) total_num

From

Score

GROUP by

student_id

) as T1 on student.sid = t1.student_id;

6) Query the number of teachers surnamed Li

Select count (tid) from teacher where tname like ' li% ';

7) Check the name of the student who did not report the teacher's class

Select

Student.sname

From

Student

8) Check the number of students with physics courses higher than biology course

9) Check the names of students who have not enrolled in both physical and physical courses

10) Check the name and class of students who have more than two doors (including two doors) in Hang Ke

11) Check the names of all students who have enrolled in all courses

12) Check all records of the courses taught by teacher Li Ping

13) Check the course number and course name that all students have enrolled in.

14) Check the number of times each course is elective

15) The name and student number of the students who have enrolled in a course

16) Check the results of all students and order from high to low (score to weight)

17) The average student's name and average score is more than 85.

18) Check the name of the student who failed the biological grade and the corresponding biological score

19) For all students who have enrolled in Li Ping's course, these courses (Li Ping's course, not all courses) have the highest average student name

20) Check the top two student names for each course score

21) search for different courses but with the same grade number, course number, results

22) Inquire about the names of the students who have not studied the "cotyledons" course and the name of the elective courses;

23) All the students who have enrolled in one or more of the students who took the student number 1 are enrolled in the study number and name;

24) Most students with the highest student name

MySQL four-2: multi-table Query