初级SQL

1. 什么是SQL？

结构化查询语言（Structured Query Language,SQL）：用于管理和操作关系数据库的标准语言

数据定义语言（Data-Definition Language,DDL）：定义和管理数据库结构
数据操纵语言（Data-Manipulation Language,DML）：查询和操作数据库中数据
数据控制语言（Data-Control Language,DCL）：控制对数据库对象的访问权限等
事务控制语言（Transaction-Control Language,TCL）：管理数据库事务

以下都是基于书本中的大学数据库模式，可以参考这个链接下载

🪧引用站外地址，不保证站点的可用性和安全性！

《数据库系统概念》中的大学模式（University Schema）

dpkt

2. DDL

2.1 DDL定义什么？

模式（Scheme）：是以下数据库对象的集合体

关系/表（Table）：表的结构，即表名、属性名
域（Domain）：每个属性可以存储的数据类型和范围
约束（Constraints）：用于确保数据的有效性和一致性
视图（Views）：用于简化复杂查询并增强安全性
索引（Indexes）：用于提高查询性能
权限（Privilege）：控制用户对数据库对象的访问权限
存储过程（Stored Procedures）：描述数据在物理存储上的组织方式
函数（Functions）：可重用的代码块，用于执行特定的操作并返回一个值

2.2 基本数据类型

标识符	描述
char(n)	指定长度n的固定长字符串
varchar(n)	指定最大长度n的可变长字符串
int	整数
numeric(p,d)	指定有p位数字，且小数点右边有d位数字
real	单浮点数
double precision	双精度浮点数
float(n)	精度至少为n的浮点数

char类型的值会在字符串末尾自动补充空格来达到固定长度
即使char和varchar类型存储相同的值，比较的结果也有可能是不同的

2.3 创建关系

通用形式（A表示属性名Attribute，D表示数据类型Data）

create table r(
  A1 D1,
  A2 D2,
  ...
  An Dn,
  <完整性约束1>，
  ...,
  <完整性约束m>
);

码约束

-- 主码约束
primary key(attribute1,attribute2)
-- 外码约束
foreign key(attribute1,attribute2) references t(attribute1,attribute2)

创建中山大学学生卡的关系

create table sysu_card(
  card_id int,
  name varchar(4) not null,
  stu_id char(8),
  college varchar(20),
  primary key (card_id),
  foreign key (stu_id) references student(stu_id)
);

查看关系

1	describe <table_name>;

2.4 对关系的操作

操作	命令	描述
删除表	`drop table r;`	不仅删除r的所有元组还删除r的模式
清空表	`delete from r;`	删除r的所有元组但保留关系
添加属性	`alter table r add A D;`	r是现有关系，A是属性名，D是属性类型
去除属性	`alter table r drop A;`	注意这里只能是drop不能是delete

3. DML-查询

形式

1
2
3

select A1,A2,...,An
from r1,r2,...,rm
where P;

含义：为from子句中指定的关系产生笛卡尔积，然后应用where子句中指定的谓词，最后筛选输出select子句中指定的属性

select：列出查询结果中所需要的属性
from：列出需要访问的关系表
where：列出作用在关系的属性上的谓词

必须以select、from、where的次序写出
where一定要仔细设计，否则将会输出是一个庞大且无意义的笛卡儿积关系

附加用法

distinct关键字：在select后插入，表示去除重复属性值
all关键字：在select后插入，表示不去除重复属性值
算术运算符：可以在select中对运算对象使用+,-,*,/运算符
逻辑连接词：可以在where中对多个谓词逻辑使用and,or,not连接词

单关系查询

-- 找出Comp. Sci.系中工资超过70000的所有老师的姓名
select name
from instructor
where dept_name = 'Comp. Sci.' and salary > 70000;

多关系查询

-- 对于大学中所有讲授课程的教师，找出他们的姓名以及他们所讲授的所有课程的ID
select name,course_id
from instructor,teaches
where instructor.ID = teaches.ID;

对于那些只出现在单个关系中的属性，通常去掉关系名前缀
但是对于那些在不同关系有重名的属性，必须加上关系名前缀

4. 基本运算

4.1 as

as实现了更名运算：old-name as new-name

把长的名字替换为短的，使用起来更方便
适用于比较同一个关系中的元组情况

-- 查询至少比Biology系某一位老师工资高的所有姓名
select distinct T.name
from instructor as T,instructor as S
where T.salary > S.salary and S.dept_name = 'Biology';

as子句可以出现在select子句中，也可以出现在from子句中

4.2 like

like实现字符串的模式匹配

百分号%：匹配任意子串，如’dasi%‘匹配任意以’dasi’开头的字符串，’%dasi%'匹配任意包含’dasi’的字符串
下划线_：匹配任意字符，如’___'匹配只含三个字符的字符串，'dasi_'匹配任意前缀是dasi且长度为5的字符串

-- 找出建筑名称中包含子串'Watson'的所有系名
select dept_name
from department
where building like '%Watson%';

在MySQL中like运算符是大小写不敏感的

4.3 *

*在select子句中表示所有属性

1
2
3

-- 查询department表
select *
from department;

4.4 order by

order by实现了排序运算，可以让查询结果的元组特定排列顺序显示

name：字母顺序
desc：降序
asc：升序

-- 按照薪水降序列出整个instructor关系，若相同则按name升序
select *
from instructor
order by salary desc,name asc;

4.5 between

between...and...实现了值在区间内的谓词逻辑，还可以使用not between

-- 找出薪水在90000到100000的老师的名字
select name 
from instructor
where salary between 90000 and 100000;

行构造器：当a1=b1且a2=b2时，(a1,a2)=(b1,b2)为真（等号可以替换为其他比较符）

-- 查询'Biology'系中授课的老师名称和课程号
select name,course_id
from instructor,teaches
where (instructor.ID,dept_name) = (teaches.ID,'Biology');

5. 集合运算

5.1 union

union实现结果的并运算

-- 查询2017年秋季或2018春季开课的课程id
select course_id
from section
where semester = 'Fall' and year = 2017
union
select course_id
from section
where semester = 'Spring' and year = 2018;

union运算自动去除重复，如果要保留重复项，需要显式使用union all

5.2 intersect

intersect实现结果的交运算

-- 查询2017年秋季且2018春季开课的课程id
select course_id
from section
where semester = 'Fall' and year = 2017
intersect
select course_id
from section
where semester = 'Spring' and year = 2018;

intersect运算自动去除重复，如果要保留重复项，需要显式使用intersect all

5.3 except

except实现结果的差运算

-- 查询2017年秋季但2018春季不开课的课程id
select course_id
from section
where semester = 'Fall' and year = 2017
except
select course_id
from section
where semester = 'Spring' and year = 2018;

except运算自动去除重复，如果要保留重复项，需要显式使用except all

6. 空值

空值运算

空值的任何算术结果都是空值
空值的任何比较结果都是unknown

unknown的特殊性

unknown是sql语言中除了true和false之外的第三个逻辑值
where子句谓词如果计算出unknown，也不能加入结果中，因为它不是true！
not unknown的值还是unknown！只有true和false是相对的！
unknown既不是is null，也不是is not null！
null=null返回的是unknown不是true！

7. 聚集

7.1 基本聚集函数

聚集函数（aggregate）：以多个集合为输入，返回单个值的函数

平均值：avg
最大值：max
最小值：min
总数：sum
计数：count

-- 找出在2018年春季授课的教师总数
select count(distinct ID)
from teaches
where semester = 'Spring' and year = 2018;

-- 找出薪水比平均薪水大的薪水总和
select sum(salary) as sum_salary
from instructor
where salary > (select avg(salary) from instructor);

sum和avg的输入必须是数字集
distinct需要放在聚集表达式中
聚集函数名和参数之间不应该有空格！
除了count(*)之外的所有聚集函数都忽略空值
空集的count运算值为0，其他则返回null

as：可以给聚集函数的输出自定义一个有意义的名称

7.2 group by

group by实现构造分组来将聚集函数作用在多个元组集合上

-- 找出每个系的平均工资
select dept_name,avg(salary) as avg_salary
from instructor
group by dept_name;

-- 找出每个系在2018年春季授课的教师人数
select dept_name,count(distinct instructor.ID) as instr_count
from instructor,teaches
where instructor.ID = teaches.ID and semester = 'Spring' and year = 2018
group by dept_name;

如果在select中使用聚集函数，select选择的属性，必须是group by使用的属性，否则会出错，因为聚集函数的值是单个值，如果不选择对应的属性，那么可能会造成数量上的不匹配，无法呈现出一个表

7.3 having

having实现了对分组限定条件，而不是对全部元组限定条件

-- 找出平均工资超过42000的系的教师总数和平均工资
select dept_name,count(instructor.ID) as num,avg(salary) as avg_salary
from instructor
group by dept_name
having avg(salary) > 42000;

作用顺序与含义

顺序	命令	含义
1	from	选出一个关系
2	where	将谓词作用在整个关系上
3	group by	对关系进行分组
4	having	将谓词作用在不同分组上
5	select	将聚集函数作用在不同分组上

-- 对于2017年中至少有2名学生选课的课程，查询每个课程的学生的总学分的平均值
select course_id,semester,avg(tot_cred)
from student,takes
where student.ID = takes.ID and year = 2017
group by course_id,semester
having count(student.ID) >= 2;

8. 嵌套子查询

8.1 where中嵌套

8.1.1 in和not in

in测试集合成员资格（可以充当集合交运算），not in测试集合成员资格的缺失（可以充当集合差运算）

-- 查找在2017秋季和2018春季都开课的课程ID
select distinct course_id
from section
where semester = 'Fall' and year = 2017 and course_id in( 
  select course_id
  from section
  where semester = 'Spring' and year = 2018
);

-- 查找在2017秋季开课但2018春季不开课的课程ID
select distinct course_id
from section
where semester = 'Fall' and year = 2017 and course_id not in( 
  select course_id
  from section
  where semester = 'Spring' and year = 2018
);

嵌套子循环都需要加括号！

8.1.2 some和all

some：至少比某一个要怎么怎么样

-- 找出工资至少比'Biology'系某位教师的工资要高的所有教师的姓名
select name
from instructor
where salary > some (
  select salary
  from instructor
  where dept_name = 'Biology'
);

all：比所有的都要怎么怎么样

-- 找出那些工资大于或等于所有系平均工资的老师
select name,salary
from instructor 
where salary >= all (
  select avg(salary)
  from instructor 
  group by dept_name
);

=some等价于in，但是<>some不等价于not in：<>null返回unknown，但not in null返回false
<>all等价于not in，但是=all不等价于in，这是显而易见的

8.1.3 exists和not exists

exists可以测试子查询是否存在元组，not exists可以测试子查询是否不存在元组

-- 找出在2017年秋季学期和2018年春季学期都开课的所有课程
select course_id
from section as S
where semester = 'Fall' and year = 2017 and exists (
  select *
  from section as T
  where semester = 'Spring' and year = 2018 and S.course_id = T.course_id
);

-- 找出选修了ID为'10101'的教师所讲授的课程段的学生总数
select count(distinct ID)
from takes as ta
where exists(
  select *
  from teaches as te
  where te.ID = '10101' 
  and ta.course_id = te.course_id 
  and ta.sec_id = te.sec_id 
  and ta.semester = te.semester 
  and ta.year = te.year
);

相关子查询：来自外层查询的相关名称可以用在where子句的子查询中

8.1.4 unique和not unique

exists可以测试子查询是否存在重复元组，not exists可以测试子查询是否不存在重复元组

-- 找出在2017年最少开设两次的所有课程
select T.course_id
from course as T
where not unique (
  select R.course_id
  from course as R
  where T.course_id = R.course_id and R.year = 2017
);

如果元组的一个属性为空，哪怕存在重复元组，unique的测试结果也有可能为真！
unique尚未被广泛使用，在MySQL中会报错

8.2 from中嵌套

任何select-from-where表达式返回的都是关系，因此可以作为from子句中的关系，同时可以使用as子句对结果关系的属性重新命名

-- 找出系平均工资超过42000美元的那些系的教师平均工资
select dept_name,avg_salary
from (
  select dept_name,avg(salary)
  from instructor
  group by dept_name
) as dept_avg(dept_name,avg_salary)
where avg_salary > 42000;

子查询结果必须被命名，即使此名称之后不会被使用

8.3 with

with...as...提供了一种定义临时关系的方式

-- 找出工资总额大于所有系平均工资总额的所有系
with 
  dept_total(dept_name,value) as (
    select dept_name,sum(salary)
    from instructor
    group by dept_name
  ), -- 先得到所有系的工资总额
  dept_total_avg(value) as (
    select avg(value)
    from dept_total
  )  -- 再得到所有系的工资总额的平均
select dept_name
from dept_total,dept_total_avg
where dept_total.value > dept_total_avg.value;

8.4 标量子查询

只返回包含单个属性的元组

-- 列出所有的系以及每个系中的教师总数
select dept_name,(
  select count(*)
  from instructor
  where department.dept_name = instructor.dept_name
) as num
from department;

9. 修改三板斧

9.1 delete

只能删除整个元组，无法只删除某些属性上的值

-- 删除属于Finance系的教师
delete from instructor
where dept_name = 'Finance';

-- 删除工资低于20000的教师
delete from instructor
where salary < 20000;

-- 删除在Watson楼的系中工作的教师
delete from instructor
where dept_name in (
  select dept_name
  from department
  where buildng = 'Watson'
);

-- 删除工资低于大学平均工资的教师
delete from instructor
where salary < (
  select avg(salary)
  from instructor
);

一条delete命令只能作用于一个关系

9.2 insert

可以插入指定元组，也可以插入查询语句生成的元组集合

-- 往课程中插入指定数据（要求按照属性排列顺序）
insert into course 
values ('CS-437','Database Systems','Comp. Sci.',4);

-- 往课程中插入指定属性和数据
insert into course(course_id,title,dept_name, credits)
values ('CS-437','Database Systems','Comp. Sci.',4);

-- 插入查询语句生成的元组集合：让Music系每 个修满144学时的学生成为Music系的教师
insert into instructor
  select ID,name,dept_name,18000
  from student
  where dept_name = 'Music' and tot_cred > 144;

-- 只给出了模式中某些属性的值，其余属性将被赋空值，用如null表示
insert into student(ID,name,dept_name,tot_cred)
values ('0227', 'dasi', 'Rap', null);

9.3 update

在不改变一个元组所有值的情况下改变其某个属性的值

-- 所有教师的工资将增长5%
update instructor
set salary = salary * 1.05;

-- 给Music系的老师涨1000工资
update instructor
set salary = salary + 1000
where dept_name = 'Music';

-- 给工资低于平均值的教师涨20%的工资
update instructor
set salary = salary * 1.2
where salary < (
  select avg(salary)
  from instructor
);

case：满足第一个的 $pred_i$ 则返回对应的 $result_i$ ，如果没有一个谓词可满足则返回 $result_0$

-- 给工资低于80000的教师涨10%，超过80000但低于100000美元的教师涨5%的工资，而给其余教师涨3%
update instructor
set salary = case
  when salary < 80000 then salary * 1.1
  when salary between 80000 and 100000 then salary * 1.05
  else salary * 1.03
end;