一,索引
–1,概述
为了提交数据的查询效率,可以给指定的列加索引
哪些列需要加索引?–常用来做查询条件的就加索引
分为几种:单值索引(一个索引只有一个列)
唯一索引(索引列的值必须唯一)
复合索引(一个索引可以包含多个列)
–2,常用SQL
–创建索引
create index 索引名 on 表名(列名)
–查看索引，主键会自动创建索引
SHOW INDEX FROM 表名
–删除索引
ALTER TABLE 表名 DROP INDEX 索引名
–练习：
#给teachers表的tname加索引,提高按照名字查询时的效率
#创建索引
create index tname_index on teachers(tname);
#查看索引
show index from teachers;
#删除索引
alter table teachers drop index tbirthday;
#添加唯一索引
desc teachers;
alter table teachers add UNIQUE(prof);#检查索引列的值必须唯一
#添加复合索引
alter table teachers add index many_index(tno,tname)
二，关联查询
–1，完成多表的关联查询,要知道查哪些表,表之间有什么关系
–2，练习
select * from dept;
select * from emp;
#笛卡尔积–是指多表联查,但是低效,因为查出来数据太多
select * from emp,dept;
#给笛卡尔积数据加过滤条件–就是指多表的关联关系
select * from emp,dept where emp.deptno = dept.deptno;#两张表的关系
select * from emp a,dept b where a.deptno = b.deptno;#使用别名
#查询students和scores表，其中sno=101的数据
select a.sno,a.sname,b.degree from students a,scores b
where a.sno=b.sno #两张表的关
and a.sno=101 #过滤条件
#查询students和scores表 --inner join/left join/right join
select * from scores a
inner join scores b
on a.sno=b.sno #表间的关联关系
where a.sno=101 #过滤结果
#查询emp和dept表,过滤dname='research'的数据
#笛卡儿积方式
select * from dept,emp
where dept.deptno=emp.deptno#表间的关联关系
and dept.dname='research';
#关联查询方式
select * from dept
inner join emp
on dept.deptno=emp.deptno#表间的关联关系
where dept.dname='research'
#子查询方式--把一次查询结果作为一张表来使用
#查询research部门的员工信息
#先查部门名称是research的部门编号
select deptno from dept where dname='research'
#再根据查到的部门编号查emp表
#select * from emp where deptno=2
select * from emp where deptno=(
select deptno from dept where dname='research'
)
select * from students inner join
scores on students.sno=scores.sno
where degree>90
#练习子查询:#查询degree>90的学生信息
#笛卡尔积方式
select * from scores,students #表名
where scores.sno=students.sno #两个表里的关联字段
and scores.degree>90 #过滤条件
#join方式
select * from scores #表名1
inner join students #表名2
on scores.sno=students.sno #两个表里的关联字段
where scores.degree>90 #过滤条件
#子查询方式
select * from students #表名1
where sno in( #过滤条件
select sno from scores where degree>90 #子查询
)
#练习子查询:#查询能讲高等数学的老师的信息
select * from teachers where tno =(
#先查courses表里老师的编号,再根据编号查teachers表
select tno from courses where cname='高等数学'
)
select * from teachers a,courses b
where a.tno=b.tno and b.cname='高等数学'
#练习子查询:#查询部门地址在二区的员工信息
select * from emp where deptno in(
select deptno from dept where loc='二区'
)
三,SQL练习题:
#查询所有记录
select * from teachers #低效
#只查询指定列
select tname,tsex from teachers #高效
#查询tno为804的记录
select * from teachers where tno=804
#模糊查询记录
select * from teachers where prof like ‘%教%’
#查询之间范围之间的所有记录
select * from teachers where tno BETWEEN 800 and 820
select * from teachers where tno>= 800 and tno<= 820
select * from teachers where tno in (804,820) #tno必须是804或者是820
# 查询满足两个条件的记录
select * from teachers where tsex=‘男’ and depart=‘计算机系’
select * from teachers where tsex=‘男’ or depart=‘计算机系’
#查询所有岗位
select distinct prof from teachers #distinct去掉重复值
#查询男老师的信息
select * from teachers where tsex=‘男’
#按tbirthday升序查询记录
select * from teachers order by tbirthday #默认是升序asc
#以tno升序、tbirthday降序查询记录
select * from teachers order by tno asc,tbirthday desc
# 查询总人数
select count(*) from teachers #低效
select count(1) from teachers #高效
# 查询各个系的人数
select depart,count(1) from teachers group by depart#非聚合列必须分组
# 查询至少有2人的岗位是啥
select prof,count(1) from teachers group by prof having count(1)>=2
# 查询记录中最年长和最年轻 #获取到出生日期数字越大年龄越小
select min(tbirthday),max(tbirthday) from teachers
# 查询大于平均年龄的记录
select * from teachers
#where里不能用聚合函数
where tbirthday < (
select avg(tbirthday) from teachers #子查询
)
#查询年龄最大的老师信息
select min(tbirthday) from teachers
#查询每个cno的最高分
select cno,max(degree) from scores group by cno
#查询每个cno的平均分
select * from scores group by cno
#查询cno='3-105’的课程信息
select * from courses where cno=‘3-105’
#查询scores表里出现过的课程信息
select * from courses where cno in (
select DISTINCT cno from scores
)
#查询没划分部门的员工信息
select * from emp where deptno is null
#查询同名的员工记录
#select * from emp where count(ename) > 1
select ename from emp GROUP BY ename having count(ename) > 1
#全部学生按出生日期排序
select * from students ORDER BY sbirthday #默认升序(年龄从大到小)
#每个班上最小年龄的学员
select class,max(sbirthday) from students GROUP BY class
#查询学生的姓名和年龄
select sname,year(now())-year(sbirthday) as age from students
#查询男教师及其所上的课程
select * from teachers a,courses b where a.tno=b.tno and a.tsex='男'
#查询得分前3名的分数信息
select * from scores order by degree desc limit 3