数据库期中考前突击复习（施工度：95%）

【前言】刷完题，必须做总结，形成知识网络！

思维强度：结构化知识>散点知识

【第一部分】建表与索引

1.建表

①建表语句create table

②primary key

③foreign key(变量名) references被参照表名(变量名)

④主码由多个属性构成，需要使用表级完整性约束

create table 表名(

        变量名 变量类型 [primary key],

        变量名2 变量类型2,

        ...

        变量名n 变量类型n,

        (变量名,变量名2) primary key,/*主码有两个属性，表级完整性约束单放*/

        foreign key(变量名) references 被参照表名(变量名)

);

变量类型举例：smallint，varchar(50)，char(8)...

2.索引

考点剖析：索引语句 create index 索引名 on 表名（变量名 asc/desc)

create index 索引名 on 表名(变量名1 升序/降序的英文单词缩写,变量名2 升序/降序的英文单词缩写,...,变量名n 升序/降序的英文单词缩写)

例如：在 SC 表中为 Cno 和 GRADE 列创建一个复合索引，其中 Cno 列按升序排列，GRADE 列按降序排列：

CREATE INDEX idx_sc_cno_grade ON SC(Cno ASC, Grade DESC);

【第二部分】单表查询

1.select的基本句式

select 列名/*（表示全部） from 表名 where 条件表达式

条件表达式有三大类

①where子句中的判断逻辑（三值逻辑，unknown）

a.相等逻辑

sno='20210001'

grade is not null

grade is null

b.不等逻辑

b1.大于小于逻辑

sage>18

b2.像型逻辑

sname LIKE '王%'

SELECT * FROM student WHERE sname LIKE '_国_';

c.属于逻辑

cno in ('c02','c03','c04')

②where子句中的运算逻辑

a.加减乘除

b.赋值逻辑

③where子句中的多个条件表达式的组合逻辑

a.and

sage>20 and ssex='女'

b.or

2.order by

格式：order by 属性名 升序/降序

输出c01课程的成绩单，要求成绩按从高到低排序。

SELECT * FROM sc WHERE cno='c01' ORDER BY grade desc

3.group by子句与having子句的使用

①group by子句的作用

给聚集函数分类的依据。group by sno的意思就是以sno作为分组依据，进行聚集函数的计算。

聚集函数也可以不使用group by。当不使用GROUP BY时，聚集函数会作用于查询结果的所有行，返回一个单一的值，这通常被称为“整体聚集”。

如果你想找到某个表中所有记录的数量，可以使用COUNT聚集函数，而不需要分组：

SELECT COUNT(*) FROM table_name;
这个查询会返回table_name表中的总行数

同样，如果你想计算某个列值的总和，可以这样做：

SELECT SUM(column_name) FROM table_name;

这个查询会返回column_name列所有值的总和。

但是，如果你想要按某些列的值进行分组并分别计算每组的聚集值，那么就需要使用GROUP BY子句。例如：

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;

这个查询会按column_name的每个不同值分组，并返回每个组的行数。

②having子句的作用

弥补了where子句不能对聚集函数的结果做逻辑判断的缺陷

SELECT sNo, COUNT(cNo) AS courseCount FROM SC GROUP BY sNo having courseCount>3;

若将having改为where立即报错。

聚集函数常用的有：count、sum、avg、max、min

使用时格式聚合函数名(列名)

如count(cno)

avg(grade)

③子句的位置

先where 再group by跟having

4.综合考察

以上所有内容的综合使用和位置顺序

SELECT cno,COUNT(sno) as csno FROM sc WHERE '1'='1' GROUP BY cno HAVING csno>3 ORDER BY COUNT(sno) DESC, cno asc

先where再group by跟having带order by接limit与offset

【第三部分】多表查询

1.等值连接

格式：select 表.列名 from 等值连接的多个表名 where 表名1.连接属性=表名2.连接属性

SELECT s.sname,s.sno FROM sc,student AS s,department AS d, course AS c 

WHERE s.sno=sc.sno AND s.deptNo=d.deptno AND sc.cno=c.cno

2.外连接

格式：select 表.列名  from 一个表名

left outer join 另一个表名 on（表名.连接属性=另一个表明.连接属性)
实现一个表名与另一个表的等值连接，并且保持了表中的悬浮元组

【高级要求】使用外连接。

（注：外连接会保留悬浮元组，所谓悬浮元组就是连接项为空而只能悬浮的元组）

SELECT student.Sno, student.Sname,c.Cno,c.cname

FROM student   

LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno)

LEFT OUTER JOIN course AS c ON (c.Cno=sc.cno)

【第四部分】嵌套查询

1.in嵌套子查询

格式：select 表名.列名 in(子查询)

子查询就是一个查询，select的内容一般与表名.列名相同

查询没有同时选修c02，c03，c04的同学的学号。

SELECT student.sno,student.sname

FROM student

WHERE student.sno NOT IN (

    SELECT sc.sno

    FROM sc

    WHERE sc.cno = 'c02'

    AND sc.sno IN (

        SELECT sc.sno

        FROM sc

        WHERE sc.cno = 'c03'

    )

    AND sc.sno IN (

        SELECT sc.sno

        FROM sc

        WHERE sc.cno = 'c04'

    )

)

2.集合查询

INTERSECT表示交集，UNION表示并集。

集合运算如果用作子查询，需要进行集合合并操作。利用subquery进行名字的区分。

SELECT sno

FROM student

WHERE sno NOT IN (

    SELECT sno

    FROM (

        SELECT sno

        FROM sc

        WHERE cno = 'c02'

        

        INTERSECT

        

        SELECT sno

        FROM sc

        WHERE cno = 'c03'

        

        INTERSECT

        

        SELECT sno

        FROM sc

        WHERE cno = 'c04'

    ) AS subquery

);

3.exists查询（难）

检索选修了全部课程的学生的学号与姓名。

SELECT s.Sname, s.Sno

FROM student s

WHERE NOT EXISTS (

    SELECT *

    FROM course c

    WHERE NOT EXISTS (

        SELECT *

        FROM sc

        WHERE sc.Sno = s.Sno AND sc.Cno = c.Cno

    )

);