cgb2106-day04

电玩女神 2021-09-11 03:18 200阅读 0赞

### 文章目录 ###

*   *  一,索引
     *   *  \--1,概述
         *  \--2,测试
     *  二,关联查询
     *   *  \--1,测试
         *  \--2,练习
     *  三,SQL的练习题
     *  作业

## 一,索引 ##

### –1,概述 ###

为了提高数据库的查询效率,可以使用索引.  
给哪些字段设计索引? 查询需要量比较大. 字段的值比较大的时候…  
分类:  
单值索引(一个索引只包含一个列) create index 索引名 on 表名(字段名)  
唯一索引(索引列的值不能重复) alter table 表名 add unique(字段名)  
复合索引(一个索引包含多个列) alter table 表名 add index 索引名(字段名,1,1,1)  
好处: 大大的提高了SQL的查询效率  
坏处: 索引本身也是一张表,表里的数据和真正的表里的数据是重复的,浪费了空间.

### –2,测试 ###

#索引的分类:单值/唯一/复合
    #1.创建 单值索引:一个索引包含了一个列
    CREATE INDEX job_index ON emp(job)
    #创建 唯一索引
    #alter table emp add unique(deptno)报错,deptno的值重复啦
    ALTER TABLE emp ADD UNIQUE(ename)
    #创建 复合索引:最左特性
    ALTER TABLE emp ADD INDEX many_index(ename,job,hiredate)
    
    #2.查看索引
    SHOW INDEX FROM emp
    
    #3.使用索引
    EXPLAIN 
    #用来观察SQL的执行计划,主要看有没有用索引
    #(观察两列的结果:key和possible_keys)
    SELECT * FROM emp WHERE job='经理'#背后会用job索引
    
    EXPLAIN
    SELECT * FROM emp WHERE empno=100#背后会用主键索引
    
    EXPLAIN
    SELECT * FROM emp WHERE ename='jack'#背后会用唯一索引
    SELECT * FROM emp WHERE ename='jack' AND job='经理'#复合索引生效
    SELECT * FROM emp WHERE hiredate='2010-1-1'#复合索引失效
    SELECT * FROM emp WHERE job='经理' AND hiredate='2010-1-1'#复合索引失效

## 二,关联查询 ##

### –1,测试 ###

#多表联查:一张表已经无法满足业务需求,需要联合查询多张表
    #方式1:笛卡尔积
    SELECT * FROM dept,emp #查出了所有数据
    SELECT * FROM dept,emp
         #表名.字段名 
    WHERE dept.deptno=emp.deptno#两张表的关联关系
    AND dept.dname='accounting'
    
    #方式2:连接查询join
       #inner join/left join/right join
    #工作中,常用的是: 小表 left join 大表,小表驱动大表
    SELECT * FROM emp 
    #inner join dept #两边都满足的交集
    LEFT JOIN dept #左边的所有和右边满足了的
    #right JOIN dept #右边的所有和左边满足了的
    ON dept.deptno=emp.deptno #描述字段
    WHERE dept.dname='accounting' #具体过滤条件
    
    #方式3:子查询:把上次的查询结果作为条件再次查询
    #1.根据部门名称查部门编号dept
    SELECT deptno FROM dept WHERE dname='accounting'
    #2.把查到的编号作为条件,查员工信息emp
    SELECT * FROM emp WHERE deptno=1
    #查询部门名称是accounting的所有信息
    SELECT * FROM emp WHERE deptno=(
      SELECT deptno FROM dept WHERE dname='accounting'
    )

### –2,练习 ###

#练习1:查询办公地址在一区的员工信息
    #子查询:根据地址查编号,再根据编号查员工
    SELECT * FROM emp WHERE deptno=(
     SELECT deptno FROM dept WHERE loc='一区'
    )
    #笛卡尔积
    SELECT * FROM dept,emp WHERE 
    dept.`deptno`=emp.`deptno`
    AND dept.`loc`='一区'
    #join
    SELECT * FROM dept LEFT JOIN emp  
    ON dept.`deptno`=emp.`deptno`
    WHERE dept.`loc`='一区'
    #练习2:查询办公地址在二区的员工信息
    #子查询:先根据地址查编号,再根据编号查员工信息
    SELECT deptno FROM dept WHERE loc='二区'# 2 3
    SELECT * FROM emp WHERE deptno IN (2,3) #in查询
    SELECT * FROM emp WHERE deptno IN (
     SELECT deptno FROM dept WHERE loc='二区'
    )
    #笛卡尔积
    SELECT * FROM dept a, emp b
    WHERE a.deptno=b.deptno
    AND a.loc='二区'
    #join
    SELECT * FROM dept a INNER JOIN emp b 
    ON a.deptno=b.deptno
    WHERE a.loc='二区'

## 三,SQL的练习题 ##

SELECT * FROM emp #低效
    SELECT empno,ename,job,sal FROM emp #高效
    SELECT * FROM emp WHERE empno=100
    SELECT * FROM emp WHERE job LIKE '总%'#高效
    SELECT * FROM emp WHERE job LIKE '_总'#_通配一个字符
    SELECT * FROM emp WHERE job LIKE '%总%'#%通配n个字符
    SELECT * FROM emp WHERE empno>100 AND empno<300
    SELECT * FROM emp WHERE empno BETWEEN 100 AND 300#包含
    SELECT * FROM emp 
    #where ename='rose' and job='副总' #并且关系
    #where ename='jack' or job='员工' #或者关系
    WHERE ename='jack' OR ename='rose' #或者关系
    #WHERE ename in('jack','rose')
    
    #查询>45岁的人的名字
    SELECT sname FROM students 
    #where里不能用聚合函数max min sum avg count
    WHERE YEAR(NOW())-YEAR(sbirthday) > 45
    
    #按照年龄排序
    SELECT *,YEAR(NOW())-YEAR(sbirthday) AS age 
    FROM students
    ORDER BY age
    #查询年龄最大的
    #desc limit 1
    SELECT sname,MIN(sbirthday) FROM students
    GROUP BY sname
    #查询高于平均工资的员工信息
    SELECT * FROM emp WHERE sal > (
      SELECT AVG(sal) FROM emp
    )
    #查询部门地址在二区的员工信息
    SELECT deptno FROM dept WHERE loc='二区'
    SELECT * FROM emp WHERE deptno IN (2,3)
    SELECT * FROM emp WHERE deptno IN (
      SELECT deptno FROM dept WHERE loc='二区'
    )
    
    #笛卡尔积/join/子查询
    #练习1:查询李军的总得分
    #练习2:查询易天负责的课程名称

## 作业 ##

#笛卡尔积/join/子查询
    #练习1:查询李军的总得分
    #练习2:查询易天负责的课程名称