cgb2111-day04

文章目录

一,多表联查

–1,准备表和数据

createtable courses
(
cno varchar(5)notnull,
cname varchar(10)notnull,
tno varchar(3)notnull,primarykey(cno));createtable scores
(
sno varchar(3)notnull,
cno varchar(5)notnull,
degree numeric(10,1)notnull,primarykey(sno, cno));createtable students
(
sno varchar(3)notnull,
sname varchar(4)notnull,
ssex varchar(2)notnull,
sbirthday datetime,
class varchar(5),primarykey(sno));createtable teachers
(
tno varchar(3)notnull,
tname varchar(4),
tsex varchar(2),
tbirthday datetime,
prof varchar(6),
depart varchar(10),primarykey(tno));INSERTINTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS)VALUES(108,'曾华','男','1977-09-01',95033);INSERTINTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS)VALUES(105,'匡明','男','1975-10-02',95031);INSERTINTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS)VALUES(107,'王丽','女','1976-01-23',95033);INSERTINTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS)VALUES(101,'李军','男','1976-02-20',95033);INSERTINTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS)VALUES(109,'王芳','女','1975-02-10',95031);INSERTINTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS)VALUES(103,'陆君','男','1974-06-03',95031);INSERTINTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)VALUES(804,'易天','男','1958-12-02','副教授','计算机系');INSERTINTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)VALUES(856,'王旭','男','1969-03-12','讲师','电子工程系');INSERTINTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)VALUES(825,'李萍','女','1972-05-05','助教','计算机系');INSERTINTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)VALUES(831,'陈冰','女','1977-08-14','助教','电子工程系');INSERTINTO COURSES(CNO,CNAME,TNO)VALUES('3-105','计算机导论',825);INSERTINTO COURSES(CNO,CNAME,TNO)VALUES('3-245','操作系统',804);INSERTINTO COURSES(CNO,CNAME,TNO)VALUES('6-166','模拟电路',856);INSERTINTO COURSES(CNO,CNAME,TNO)VALUES('6-106','概率论',831);INSERTINTO COURSES(CNO,CNAME,TNO)VALUES('9-888','高等数学',831);INSERTINTO SCORES(SNO,CNO,DEGREE)VALUES(103,'3-245',86);INSERTINTO SCORES(SNO,CNO,DEGREE)VALUES(105,'3-245',75);INSERTINTO SCORES(SNO,CNO,DEGREE)VALUES(109,'3-245',68);INSERTINTO SCORES(SNO,CNO,DEGREE)VALUES(103,'3-105',92);INSERTINTO SCORES(SNO,CNO,DEGREE)VALUES(105,'3-105',88);INSERTINTO SCORES(SNO,CNO,DEGREE)VALUES(109,'3-105',76);INSERTINTO SCORES(SNO,CNO,DEGREE)VALUES(101,'3-105',64);INSERTINTO SCORES(SNO,CNO,DEGREE)VALUES(107,'3-105',91);INSERTINTO SCORES(SNO,CNO,DEGREE)VALUES(108,'3-105',78);INSERTINTO SCORES(SNO,CNO,DEGREE)VALUES(101,'6-166',85);INSERTINTO SCORES(SNO,CNO,DEGREE)VALUES(107,'6-106',79);INSERTINTO 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,表2SELECTSUM(scores.degree)FROM courses,scores
#描述了两个表的关系(表名.字段名)WHERE courses.cno=scores.cno
AND courses.cname='计算机导论'#真正的业务条件#练习4:查询学员陆君的总得分 snoSELECTSUM(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:统计操作系统课程的总得分SELECTSUM(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 INNERJOIN emp #内连接,取两张表的交集ON dept.deptno=emp.deptno
SELECT*FROM 
dept LEFTJOIN emp 
#左连接,取左表的所有和右表满足条件的,不满足的填充nullON dept.deptno=emp.deptno
SELECT*FROM 
emp RIGHTJOIN dept
#右连接,取右表的所有和左表满足条件的,不满足的填充nullON 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=2WHERE deptno IN(1,2)#效果同上#子查询:第一次查到了多个结果,第二次查就要用inSELECT 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 INNERJOIN courses b
ON a.tno=b.tno#描述两个表的关系WHERE b.cname='高等数学'#子查询SELECT tname FROM teachers WHERE tno=(SELECT tno FROM courses WHERE cname='高等数学')#练习2:查询学员曾华的总得分#笛卡尔积SELECTSUM(a.degree)FROM scores a,students b
WHERE a.sno=b.sno  #描述两个表的关系AND  b.sname='曾华'#业务需求#连接查询SELECTSUM(a.degree)FROM scores a JOIN students b
ON a.sno=b.sno  #描述两个表的关系WHERE  b.sname='曾华'#业务需求#子查询SELECTSUM(degree)FROM scores WHERE sno=(SELECT sno FROM students WHERE sname='曾华')#查询高于平均工资的员工信息SELECT*FROM emp WHERE sal>(SELECTAVG(sal)FROM emp
)

六,扩展:索引

–1,概述

用来提高数据库的查询效率.
分类:
1,单值索引: 是指一个索引只包含着一个字段/列
2,复合索引: 是指一个索引包含着多个字段/列
3,唯一索引: 是一种特殊的单值索引,一个索引只包含着一个字段/列,这个字段的值要唯一

–2,测试

#索引:提高查询效率,建议给经常用来查询的字段加索引#1.查看索引(主键自带索引)SHOWINDEXFROM students
#2.创建单值索引#create index 索引名 on 表名(字段名)CREATEINDEX index1 ON students(sname)#3.使用索引(按照索引列去查)SELECT*FROM students WHERE sname='陆君'#4.查看SQL的执行性能/计划(只想关注你的SQL是否用到了索引)EXPLAIN#看执行结果里的keySELECT*FROM students WHERE sname='陆君'#5.创建唯一索引:找到合适的列,值要唯一#语法:create unique index 索引名 on 表名(字段名)#create unique index index2 on students(ssex)#不让加,ssex值大量重复CREATEUNIQUEINDEX index2 ON students(sno)SHOWINDEXFROM students#查看索引EXPLAINSELECT*FROM students WHERE sno=101#使用索引,索引失效!!#!!6.创建复合索引:一个索引包含着多个列CREATEINDEX index3 ON emp(ename,job,deptno)SHOWINDEXFROM emp#查看索引#使用索引,最左特性(查询条件里必须包含最左元素)EXPLAINSELECT*FROM emp WHERE ename='jack'#复合索引生效EXPLAINSELECT*FROM emp WHERE job='总监'#复合索引失效!EXPLAINSELECT*FROM emp WHERE deptno=2#复合索引失效!EXPLAINSELECT*FROM emp WHERE ename='jack'AND job='总监'#生效EXPLAINSELECT*FROM emp WHERE job='总监'AND ename='jack'#生效EXPLAINSELECT*FROM emp WHERE job='总监'OR ename='jack'#失效!#删除索引ALTERTABLE emp DROPINDEX index3

–3,总结

1,优点:
大大提高了查询效率
本质上数据库会为索引列的数据进行排序,快速查询
2,缺点:
本身索引是一个单独的表,也需要占空间的
索引适合查询的业务,但是,也需要同步更新修改一些新的数据,需要一定的时间
3,原则:
什么时候加索引? 频繁的按照一个规则去查询的数据,就应该考虑添加索引
给谁加索引? 给那些经常作为查询条件的字段添加索引
加啥索引? 索引是有分类的, 单值索引 / 唯一索引 / 复合索引,看情况选择不同的索引类型
如何查看SQL的执行计划/性能? explain

作业

用三种多表联查的方式(笛卡尔积/连接查询/子查询),完成以下的练习:
练习1:查询research部门的所有员工姓名和工资
练习2:查询jack所在的部门信息
练习3:查询总监的部门信息
练习4:查询李军的平均分
练习5:查询陈冰能讲的课程名