一、多表联查

1、准备数据（此数据借用我的老师霞霞的数据）

SHOW DATABASES;
USE lab;
SHOW TABLES;
CREATE TABLE dept(
deptno INT,
dname VARCHAR(20),
loc VARCHAR(13)
);

CREATE TABLE emp(
empno INT,
ename VARCHAR(10),
job VARCHAR(10),
mgr INT,
hiredate DATETIME,
sal DOUBLE,
 comm NUMERIC(8,2),
deptno INT
);


SET NAMES gbk; #防止中文乱码
INSERT INTO dept VALUES(1,'java开发部','一区');
INSERT INTO dept VALUES(2,'research','二区');
INSERT INTO dept VALUES(1,'operations','二区');
INSERT INTO emp VALUES(100,'jack','副总',NULL,'2002-05-1',90000,NULL,1);
INSERT INTO emp VALUES(200,'tony','总监',100,'2015-02-02',10000,2000,2);
INSERT INTO emp VALUES(300,'hana','经理',200,'2017-02-02',8000,1000,2);
INSERT INTO emp VALUES(400,'leo','员工',300,'2019-02-22',3000,200.12,2);
INSERT INTO emp VALUES(500,'liu','员工',300,'2019-03-19',3500,200.58,2);


CREATE TABLE courses
(
cno VARCHAR(5) NOT NULL,
cname VARCHAR(10) NOT NULL,
tno VARCHAR(3) NOT NULL,
PRIMARY KEY (cno)
);

CREATE TABLE scores(
sno VARCHAR(3) NOT NULL,
cno VARCHAR(5) NOT NULL,
degree NUMERIC(10,1) NOT NULL,
PRIMARY KEY (sno, cno)
);
CREATE TABLE students(
sno VARCHAR(3) NOT NULL, 
sname VARCHAR(4) NOT NULL,
ssex VARCHAR(2) NOT NULL,
sbirthday DATETIME, 
class VARCHAR(5), 
PRIMARY KEY (sno)
);
CREATE TABLE teachers(
tno VARCHAR(3) NOT NULL,
tname VARCHAR(4),
tsex VARCHAR(2),
tbirthday DATETIME,
prof VARCHAR(6),
depart VARCHAR(10),
PRIMARY KEY (tno)
);

INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' ,'男' ,'1977-09-01',95033);
 
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,'1975-10-02',95031);
 
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽' ,'女' ,'1976-01-23',95033);
 
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李军' ,'男' ,'1976-02-20',95033);
 
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,'1975-02-10',95031);
 
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君' ,'男' ,'1974-06-03',95031);
 
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'易天','男','1958-12-02','副教授','计算机系');
 
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'王旭','男','1969-03-12','讲师','电子工程系');
 
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,'李萍','女','1972-05-05','助教','计算机系');
 
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,'陈冰','女','1977-08-14','助教','电子工程系');
 
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机',825);
 
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804);
 
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-166' ,'模拟电路' ,856);
 
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-106' ,'概率论' ,831);
 
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,831);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'6-166',81);

2、多表联查三种方式

1）笛卡尔积：语法select * from 表名1，表名2，表名3

2）连接查询

3）子查询

3、测试

注意：如果表数据不对，想想数据库是否设置编码格式，或者删除重新输入数据，输入时规定编码格式

ALTER TABLE 表名 CONVERT TO CHARACTER SET utf #将表和字段的编码格式都改成utf8的编码格式

###方式一：笛卡尔积，通过逗号连接表名
#dept部门表deptno部门编号，dname部门名，loc部门所在区域
#emp员工信息表deptno部门编号，comm奖金，sal工资，hiredate入职时间，job职位，ename员工姓名
#查询部门编号是1的部门信息和员工信息
#思路两张表共同字段部门编号，所以利用这点查询表
SELECT * FROM dept,emp WHERE dept.deptno=emp.deptno AND dept.deptno=2;
#查询员工姓名叫jack的部门信息
SELECT dept.* FROM dept,emp WHERE dept.deptno=emp.deptno AND  emp.ename='jack';
#查询岗位是总监所在的部门信息 
SELECT dept.* FROM dept,emp WHERE dept.deptno=emp.deptno AND job='总监';
####方式二：连接查询，分为三种
#内连接inner join：取两张表的交集
#左连接：left join：取左表的所有和右表满足条件的
#右连接：right join：取右表的所有和左表满足条件的
#查询岗位是总监所在的部门信息
SELECT dept.* FROM dept JOIN emp ON dept.deptno=emp.deptno WHERE emp.job='总监';
#查询员工姓名叫jack的部门信息  
SELECT dept.* FROM dept JOIN emp ON dept.deptno=emp.deptno WHERE emp.ename='jack';
#三种连接查询：inner join/left join/right join
#inner join   inner 可以省略取两张都满足条件后的交集
SELECT * FROM dept INNER JOIN emp ON emp.deptno=dept.deptno;




#综合练习1:查询陈冰老师能讲解的课程的名称(teachers/courses)
#笛卡尔积方式
SELECT courses.cname FROM courses,teachers WHERE teachers.tno=courses.tno AND teachers.tname='陈冰';
#连接查询方式
SELECT courses.cname FROM courses INNER JOIN teachers ON courses.tno=teachers.tno  WHERE teachers.tname='陈冰';

查询学员李军的总得分(students/scores)
#笛卡尔积方式
SELECT SUM(degree) FROM scores,students WHERE scores.sno=students.sno AND students.sname='李军';
#连接查询
SELECT SUM(degree) FROM scores JOIN students ON scores.sno=students.sno WHERE students.sname='李军';
###方式三：子查询/嵌套查询：把上次查询结果作为这次的查询条件
#练习1:查询学员李军的总得分(students/scores)
SELECT SUM(degree) FROM scores WHERE sno=(SELECT sno FROM students WHERE students.sname='李军');
#练习2:查询陈冰老师能讲解的课程的名称(teachers/courses)
SELECT cname FROM courses WHERE tno=(SELECT tno FROM teachers WHERE tname='陈冰');