湖南
day04数据库
一.多表联查
1.准备表和数据
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);
二.笛卡尔积
1.概述
本质上就是把多张表，联合查询。要求，多张表用逗号隔开
2.测试
#多表联查:
#1.笛卡尔积
#练习1:查询部门表和员工表里的所有数据
SELECT * FROM dept,emp
#问题:产生了庞大的结果集,出现了重复的数据
#练习2:查询部门表和员工表里的所有数据,添加过滤条件
SELECT * FROM dept,emp
WHERE dept.deptno=emp.deptno; #描述了两个表的关系
#练习3:计算计算机导论课程所得的总分
#聚合函数(表名.字段名) 表1,表2
SELECT SUM(scores.degree) FROM courses,scores
#描述了两个表的关系(表名.字段名)
WHERE courses.cno=scores.cno
AND courses.cname='计算机导论'#真正的业务条件
#练习4:查询学员陆君的总得分 sno
SELECT SUM(scores.degree) FROM scores,students
WHERE scores.sno=students.sno#描述了两个表的关系(表名.字段名)
AND students.sname='陆君'
三.连接查询
1.概述
本质上就是把多张表，联合查询.要求，多张表用join连接
2.测试
#2.连接查询:join连接表,用on描述表关系
#练习5:统计陈冰老师能讲的课程名称
SELECT courses.cname FROM teachers JOIN courses
ON teachers.tno=courses.tno#描述了两个表的关系
WHERE teachers.tname='陈冰'#真正的业务条件
#练习6:统计操作系统课程的总得分
SELECT SUM(scores.degree) FROM courses JOIN scores
ON courses.cno=scores.cno#描述了两个表的关系
WHERE courses.cname='操作系统'#真正的业务条件
#面试题:三种连接查询的区别?inner join/left join/right join
#inner join内连接,取两个表的交集的数据
#left join左外连接,取左表的所有和右表里满足条件的数据
#right join右外连接,取右表的所有和左表里满足条件的数据
#中午,自己测试三种区别???
SELECT * FROM
dept INNER JOIN emp #内连接,取两张表的交集
ON dept.deptno=emp.deptno
SELECT * FROM
dept LEFT JOIN emp
#左连接,取左表的所有和右表满足条件的,不满足的填充null
ON dept.deptno=emp.deptno
SELECT * FROM
emp RIGHT JOIN dept
#右连接,取右表的所有和左表满足条件的,不满足的填充null
ON dept.deptno=emp.deptno
四.子查询
1.概述
又叫嵌套查询,是指,把第一次的查询结果,作为第二次的查询条件,继续发起查询语句.
2.测试
#3.子查询
#练习1:查询research部门的员工信息
#第一次:查部门表,根据部门名称查部门编号
SELECT deptno FROM dept WHERE dname='research'
#第二次:查员工表,根据部门编号查询员工信息
SELECT * FROM emp WHERE deptno=2
#子查询:
SELECT * FROM emp WHERE deptno=(
SELECT deptno FROM dept WHERE dname='research'
)
#练习2:查询tony所在的部门名称
#第1次:根据员工名字查部门编号
SELECT deptno FROM emp WHERE ename='tony'
#第2次:根据部门编号查部门名称
SELECT dname FROM dept WHERE deptno=2
#子查询:
SELECT dname FROM dept WHERE deptno=(
SELECT deptno FROM emp WHERE ename='tony'
)
#练习3:查询java开发部和research部门的员工姓名
#第1次:根据部门名称查部门编号
SELECT deptno FROM dept
WHERE dname='java开发部' OR dname='research'
#第2次:根据编号查员工姓名
SELECT ename FROM emp
#where deptno=1 or deptno=2
WHERE deptno IN(1,2) #效果同上
#子查询:第一次查到了多个结果,第二次查就要用in
SELECT ename FROM emp WHERE deptno IN(
SELECT deptno FROM dept
WHERE dname='java开发部' OR dname='research'
)
五.综合练习
1.测试
#综合练习:
#练习1:查询可以讲高等数学的老师的名字
#笛卡尔积
SELECT tname FROM teachers,courses
WHERE teachers.tno=courses.tno#描述两个表的关系
AND courses.cname='高等数学'
#连接查询
SELECT tname FROM teachers a INNER JOIN courses b
ON a.tno=b.tno#描述两个表的关系
WHERE b.cname='高等数学'
#子查询
SELECT tname FROM teachers WHERE tno=(
SELECT tno FROM courses WHERE cname='高等数学'
)
#练习2:查询学员曾华的总得分
#笛卡尔积
SELECT SUM(a.degree) FROM scores a,students b
WHERE a.sno=b.sno #描述两个表的关系
AND b.sname='曾华' #业务需求
#连接查询
SELECT SUM(a.degree) FROM scores a JOIN students b
ON a.sno=b.sno #描述两个表的关系
WHERE b.sname='曾华' #业务需求
#子查询
SELECT SUM(degree) FROM scores WHERE sno=(
SELECT sno FROM students WHERE sname='曾华'
)
#查询高于平均工资的员工信息
SELECT * FROM emp WHERE sal>(
SELECT AVG(sal) FROM emp
)
六.扩展：索引
1.概述
用来提高数据库的查询效率
分类：
- 单值索引：是指一个索引只包含着一个字段/列
- 复合索引：是指一个索引包含着多个字段/列
- 唯一索引：是一种特殊的单值索引，一个索引只包含着一个字段/列，这个字段的值要唯一
2.测试
#索引:提高查询效率,建议给经常用来查询的字段加索引
#1.查看索引(主键自带索引)
SHOW INDEX FROM students
#2.创建单值索引
#create index 索引名 on 表名(字段名)
CREATE INDEX index1 ON students(sname)
#3.使用索引(按照索引列去查)
SELECT * FROM students WHERE sname='陆君'
#4.查看SQL的执行性能/计划(只想关注你的SQL是否用到了索引)
EXPLAIN #看执行结果里的key
SELECT * FROM students WHERE sname='陆君'
#5.创建唯一索引:找到合适的列,值要唯一
#语法:create unique index 索引名 on 表名(字段名)
#create unique index index2 on students(ssex)#不让加,ssex值大量重复
CREATE UNIQUE INDEX index2 ON students(sno)
SHOW INDEX FROM students#查看索引
EXPLAIN SELECT * FROM students WHERE sno=101 #使用索引,索引失效!!
#!!6.创建复合索引:一个索引包含着多个列
CREATE INDEX index3 ON emp(ename,job,deptno)
SHOW INDEX FROM emp#查看索引
#使用索引,最左特性(查询条件里必须包含最左元素)
EXPLAIN SELECT * FROM emp WHERE ename='jack'#复合索引生效
EXPLAIN SELECT * FROM emp WHERE job='总监'#复合索引失效!
EXPLAIN SELECT * FROM emp WHERE deptno=2#复合索引失效!
EXPLAIN SELECT * FROM emp WHERE ename='jack' AND job='总监'#生效
EXPLAIN SELECT * FROM emp WHERE job='总监' AND ename='jack'#生效
EXPLAIN SELECT * FROM emp WHERE job='总监' OR ename='jack'#失效!
#删除索引
ALTER TABLE emp DROP INDEX index3
3.总结
- 优点：
大大提高了查询效率
本质上数据库会为索引列的数据进行排序，快速查询 - 缺点
本身索引是一个单独的表，也需要占空间的
索引适合查询的业务，但是，也需要同步更新修改一些新的数据，需要一定的时间 - 原则：
什么时候加索引？频繁的按照一个规则去查询的数据，就应该考虑添加索引
给谁加索引？给那些经常作为查询条件的字段添加索引
加啥索引？索引是有分类的，单值索引/唯一索引/复合索引，看情况选择不同的索引类型
如何查看SQL的执行计划/性能？explain