2021-09-29

1.索引:主键自带索引,提高查询效率

#1.1查看索引: show index from 表名
SHOW INDEX FROM emp
#1.2创建单值索引:单值索引(一个索引只包含一个字段) 复合索引 唯一索引
CREATE INDEX ename_index ON emp(ename)
#1.3创建唯一索引:一个索引只包含一个字段,索引列值不能重复
CREATE UNIQUE INDEX bindex ON emp(ename)
#1.4 创建复合索引:一个索引包含着多个字段,遵循最左特性
CREATE INDEX pindex ON emp (job,hiredate,comm)
#1.5删除索引
ALTER TABLE emp DROP INDEX pindex
#1.6使用索引:背后的故事(感觉不到使用了索引),按照索引列去查
SELECT * FROM emp WHERE ename=‘jack’
SELECT * FROM emp WHERE ename=‘jack’
#使用复合索引,遵循最左特性
SELECT * FROM emp WHERE job=‘总监’ #生效
SELECT * FROM emp WHERE job='总监’AND hiredate=‘2019-1-1’ #生效
SELECT * FROM emp WHERE hiredate=‘2019-1-1’ #失效
SELECT * FROM emp WHERE hiredate=‘2019-1-1’ AND comm=100 #失效
#1.7查询SQL的执行计划/性能(看看用没用索引)
EXPLAIN
SELECT * FROM emp WHERE ename=‘jack’
EXPLAIN
SELECT * FROM emp WHERE ename=‘jack’

2.多表联查

#方式1:笛卡尔积,通过逗号连接表名
#练习1:查询部门编号是1的部门信息和员工信息
SELECT * FROM dept,emp
#表名.字段名=表名.字段名
WHERE dept.deptno=emp.deptno#,描述两个表的关系
AND dept.deptno=1#查deptno=1的数据
#练习2:查询员工姓名叫jack的部门信息和员工信息
SELECT dept.* FROM dept,emp
WHERE dept.deptno=emp.deptno#描述了两张表的关系
AND emp.ename=‘jack’ #业务条件
#练习3:查询岗位是总监的部门信息
SELECT emp.* FROM dept,emp
WHERE dept.deptno=emp.deptno
AND emp.job = ‘总监’

#方式2:连接查询
#分为三种
#内连接 inner join:取两张表的交集
#左连接 left join:取左表的所有和右表满足条件的
#右连接 right join:取右表的所有和左表满足条件的
#练习1:列出research部门下的所有员工的信息
#笛卡尔积方式
SELECT emp.* FROM dept JOIN emp
ON dept.deptno = emp.deptno
WHERE dept.dname= ‘java开发部’
#练习2:查询岗位是总监所在的部门信息
#连接查询方式
SELECT dept.* FROM dept JOIN emp
ON dept.deptno=emp.deptno
WHERE emp.job=‘总监’;
#练习3:查询员工姓名叫jack的部门信息和员工信息
SELECT emp.* FROM dept JOIN emp
ON dept.deptno=emp.deptno
WHERE emp.ename=‘jack’
#三种连接查询:inner join /left join /right join
SELECT * FROM dept INNER JOIN emp
ON emp.deptno = dept.deptno

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

#综合练习2:查询学员李军的总得分(students/scores)
#笛卡尔积
SELECT SUM(degree)FROM students,scores
WHERE students.sno=scores.sno
AND students.sname=‘李军’
#连接查询
SELECT SUM(degree)FROM students JOIN scores
ON students.sno=scores.sno
WHERE students.sname=‘李军’

#方式3:子查询/嵌套查询:把上次的查询结果作为这次的查询条件
#练习1:查询学员李军的总得分(students/scores)
#查询学生表,根据学员名字查编号
#根据编号查得分表里的分数
SELECT sno FROM students WHERE sname=‘李军’
SELECT SUM(degree) FROM scores WHERE sno=(SELECT sno FROM students WHERE sname=‘李军’)
#子查询练习2:查询陈冰老师能讲解的课程的名称(teachers/courses)
SELECT tno FROM teachers WHERE tname=‘陈冰’
SELECT cname FROM courses WHERE tno=(SELECT tno FROM teachers WHERE tname=‘陈冰’)