解题09-11

• 找出可以自驾游的部门

连接查询：
select d.name from department d join 
  (select e.did,e.name join employee e join es 
    on e.eid=es.eid 
    where es.sid in( select sid from skill 
    where substr(descript,1,4)='驾驶小车' or substr(descript,1,4)='驾驶货车')
    j where d.did = j.did
)
相关子查询：
select dp.name from department dp 
where exists(
    select * from employee e
    where eid in(
    select eid from es 
    where sid in(
        select sid from skill
        where substr(descript,1,4)='驾驶小车' or substr(descript,1,4)='驾驶货车'
    ) )and e.did=dp.did
)

• 找出可以出国自由行的部门的员工名单

子查询：
    select * from employee e1
    where e1.did in 
    (
    select e.did from employee e 
    join es
    on e.eid = es.eid 
    join skill s
    on es.sid=s.sid and s.descript='外语'
    )
相关子查询：
    select * from employee e1 
    where exists(
    select e2.did from employee e2 
    join es
    on e2.eid = es.eid 
    join skill s
    on es.sid=s.sid and s.descript='外语' 
    where e1.did=e2.did
    )

• 找出某班 80分以上 学生人数在2人的 全班的名单

select * from student s1 
where exists(
    select * from class 
    where s1.cno in (
    select * from 
    (select cno as countno from student s2 
    where avgscore>=80 group by cno 
    having count(*) >= 2) t )
)

• 找出掌握技能在2门以上人数在2人以上的部门的 全体人员名单

逻辑有点问题：
SELECT * FROM employee e 
WHERE EXISTS(
    SELECT t.did FROM (
    SELECT e.eid,e.name,es.sid,e.did FROM employee e
    JOIN es ON es.eid = e.eid 
    GROUP BY e.eid,e.name 
    HAVING COUNT(es.sid)>=2
    ) t
    WHERE t.did IN(
    SELECT did FROM employee 
    GROUP BY did 
    HAVING COUNT(eid)>2)
    AND e.did=t.did)
参考1,看本部门是否在同时满足条件一和条件二的部门列表中：
select * from employee e 
where exists(
    SELECT t.did FROM (
        -- 查询出了哪些员工掌握了2门以上的技能*
        SELECT e.eid,e.name,es.sid,e.did FROM employee e
        JOIN es ON es.eid = e.eid 
        GROUP BY e.eid,e.name 
        HAVING COUNT(es.sid)>=2 ) t 
        -- 当前员工和掌握2门以上技能的员工是不是同一部门
        where t.did=e.did 
        group by t.did 
        having count(t.did) >=2
)
参考2,先看本部门是否满足条件一，再看是否满足条件二：
SELECT * FROM employee e
 WHERE EXISTS(
  SELECT * FROM department 
  WHERE e.did IN( -- 我的部门在不在符合要求的部门之中
   -- *** 找出那些员工掌握了2门以上的技能
   SELECT t.did FROM (
         SELECT e.eid,e.name,es.sid,e.did FROM employee e
         JOIN es ON es.eid=e.eid
         GROUP BY e.eid,e.name
         HAVING COUNT(es.sid)>=2) t
    -- ***
         GROUP BY t.did
         HAVING COUNT(t.did) >= 2)  -- 只留下有2人以上符合要求的部门
 )

---

查询效率与索引

• 查看表信息sp_help testeacher
• 找出和陈涛老师同乡的名单

explain select s.sno,s.name from teacher t join student s 
on t.area=s.area 
where t.tname='陈涛'

explain select s.sno,s.name from teacher t join student s 
on t.area=s.area and t.tname='陈涛'

explain select s.sno,s.name from student s 
where area = (
    select area from teacher 
    where tname='陈涛'
)

• 创建与删除索引

create index idx_student_sno_name_area on student(sno,name,area)
create index idx_teacher_tno_tname_area on teacher(tno,tname,area)
drop index idx_teacher_tno_tname_area on teacher
-- 删除索引sqlserver写法
drop index teacher.idx_teacher_tno_tname_area

• 快速插入数据

-- 从其他表复制一次数据
insert into teststudent select * from student
-- 再重复翻倍复制自身数据
insert into teststudent select * from teststudent

• 快速删除表

-- 比delete执行快，但其不记录日志，找不回数据
truncate table teststudent

• 创建新表同时复制表中数据

select * into testconsumer from Consumer

• 索引 就相当于字典的目录 如果连接查询所涉及的字段直接就有索引的组合，数据库直接扫描索引，
  而不会扫描表

• 索引的优点：在一定条件下可以大幅提高连接运算查询IO的时间（在索引的大小和正文大小差距比较大的
  情况下，DBMS才使用索引； 如果查询字段（ABD）索引为（BC），DBMS不会使用索引）

• 如果查询字段为ABC，索引为BC,DBMS使用索引+正文

• 如果查询的字段为BC，索引为BC，DBMS使用索引，不查正文

• 创建主键，会自动创建一个聚集（物理排序）索引，因为一张表只有一个主键只能有一个聚集索引

• 非聚集索引可以创建多个，因为 是额外产生的索引页保存逻辑排序的结果

• 修改id列不为空alter table test alter column id int not null

• 追加主键alter table test add constraint pk_test_id primary key(id)

• 创建索引就是将select 后的字段和where涉及字段和on涉及字段组合在一起创建

• 实验步骤：

1. 构建没有索引的表
2. 复制数据
3. 没有索引执行连接
4. 创建索引执行连接

• 扩展：填充因子（sqlserver：dbcc showconfig）

---

新题

• 找出考试通过率不是100% 教师的信息

解法一：
select distinct sc1.exampoint from sc sc1 
where exists(
    select score from sc sc2 
    where sc2.score<60 and sc1.exampoint=sc2.exampoint 
)
解法二：
SELECT DISTINCT sc1.`exampoint`,

  (SELECT COUNT(*) AS 'lessno' FROM sc sc2
   WHERE sc1.exampoint = sc2.exampoint AND score > 60)
   /
  (SELECT COUNT(*) AS 'countno' FROM sc sc2
  WHERE sc1.exampoint = sc2.exampoint) AS '通过率'
  
  
  ,exampoint 
  FROM sc sc1
WHERE 
  (SELECT COUNT(*) AS 'lessno' FROM sc sc2
  WHERE sc1.exampoint = sc2.exampoint AND score > 60) 
   /
  (SELECT COUNT(*) AS 'countno' FROM sc sc2
  WHERE sc1.exampoint = sc2.exampoint)  < 1

• 找出考试通过率不是100% 管理员的信息

select distinct ad.adno from admin ad 
where exists(
    select score from sc 
    where sc.score<60 and ad.adno = substr(sc.adno,4,1)
)

• 找出通过率不是100% 的管理员并显示他的通过率
• 将管理员编号中开头的0去掉（2种）