数据库系统概念笔记——第三章：SQL
数据库系统概念笔记——第三章：SQL
文章目录
第三章：SQL
3.2 SQL数据定义
3.2.1 基本类型
3.2.2 基本模式定义
3.3 SQL查询的基本结构
3.3.1 单关系查询
3.3.2 多关系查询
3.3.3 自然连接
3.4 附加的基本运算
3.4.1 更名运算
3.4.2 字符串运算
3.4.4 排列元组的显示次序
3.4.5 where子句谓词
3.5 集合运算
3.5.1 并运算
3.5.2 交，差运算
3.6 空值
3.7 聚集函数
3.7.1 基本聚集
3.7.2 分组聚集
3.7.3 having子句
3.7.4 对空值和布尔值的聚集
3.8 嵌套子查询
3.8.1 集合成员资格
3.8.2 集合的比较
3.8.3 空关系测试
3.8.6 with子句
3.9 数据库的修改
3.9.1 删除
3.9.2 插入
3.9.3 更新
第三章：SQL
3.2 SQL数据定义
3.2.1 基本类型
SQL标准中支持多种固有类型
char(n)：固定长度的字符串，用户指定长度n
varchar(n)：可变长度的字符串，用户指定最大长度n（注意在Oracle数据库中写作varchar2(n)）
int：整数类型
smallint：小整数类型
numeric(p,d)：定点数，这个数有p位数字（加上符号位），其中d位在小数点右边
real，double precision：浮点数与双精度浮点数
float(n)：精度至少为n位的浮点数
注意点：
char类型存储固定长度的字符串，如果存入字符串的长度不够，则会以空格补齐
varchar类型不会补充空格，根据用户填入的字符串长度保存
定点数numeric(3,1)可以精确存储44.5，但却不能精确存储444.5或0.32这样的数
3.2.2 基本模式定义
使用create table命令定义SQL关系
课本中的大学数据库的部分建表sql语句（MySQL）
create table classroom
(building varchar(15),
room_number varchar(7),
capacity numeric(4,0),
primary key (building, room_number)
);
create table department
(dept_name varchar(20),
building varchar(15),
budget numeric(12,2) check (budget > 0),
primary key (dept_name)
);
create table course
(course_id varchar(8),
title varchar(50),
dept_name varchar(20),
credits numeric(2,0) check (credits > 0),
primary key (course_id),
foreign key (dept_name) references department(dept_name)
on delete set null
);
create table instructor
(ID varchar(5),
name varchar(20) not null,
dept_name varchar(20),
salary numeric(8,2) check (salary > 29000),
primary key (ID),
foreign key (dept_name) references department(dept_name)
on delete set null
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
primary key(A1,A2,A3…)：表明属性A1,A2,A3…构成表的主码，主码的属性必须非空且唯一
foreign key(A1,A2,A3…) references ：声明表中这些属性的取值必须和另一个关系上某些属性的取值对应
not null：限制不能为空
check：自定义限制，括号中填限制的内容
SQL禁止破坏完整性约束的任何数据库更新
使用alert table可以为已有关系增加属性
alert table r add A D
1
A是待添加属性的名字，D是属性的数据类型
如果表中原本就有数据，则原有元组在新添加的属性上的值全部为null
补充
1.使用constraint关键字给约束命名
2.表空间（tablespace）
表空间是数据库的逻辑划分，一个表空间只能属于一个数据库，以一个数据库可以拥有多个表空间
表空间分为系统表空间和用户表空间
一个表空间可以包含多张表，一张表只能属于一个表空间
3.表的属主Owner
每个数据库对象都有自己的属主，表也有自己的属主。数据库对象默认属主是创建该对象的用户
属主拥有表的所有权限
属主为user1
4.修改表的定义alert
格式：
例：
5.删除表中的属性或约束
删除表中的约束
3.3 SQL查询的基本结构
3.3.1 单关系查询
单关系查询比较简单，举几个简单的例子即可
1.找出所有教师的名字
select name
from instructor;
1
2
2.找出所有教师的系名
select dept_name
from instructor;
1
2
3.使用distinct关键字查找教师所在的系名
select distinct dept_name
from instructor;
1
2
4.在select中使用算数表达式
select name,salary*1.1
from instructor;
1
2
5.找出所有在Comp. Sci.系且工资超过70000美元的老师的姓名
select name
from instructor
where dept_name = 'Comp. Sci.' and salary > 70000;
1
2
3
3.3.2 多关系查询
1.找出所有教师的姓名，所在系，和系所在的建筑
select name,i.dept_name,building
from instructor as i,department as d where i.dept_name = d.dept_name;
1
2
3.3.3 自然连接
1.找出教师的姓名和他们所教的课程标识
select name,course_id
from instructor natural join teaches;
1
2
2.找出教师的姓名和他们所教的课程名称（三张表自然连接）
select name,title
from instructor natural join teaches natural join course;
1
2
3.4 附加的基本运算
3.4.1 更名运算
更名运算使用as关键字
例
select T.name,S.course_id
from instructor as T,teaches as S
where T.ID = S.ID;
1
2
3
3.4.2 字符串运算
1.字符串的匹配
%：匹配任意字符
_：匹配一个字符
找出所在建筑名称中包含a的所有系名
select dept_name
from department
where building like '%a%';
1
2
3
3.4.4 排列元组的显示次序
1.order by，asc，desc
select name,salary
from instructor
order by name asc,salary desc;
1
2
3
3.4.5 where子句谓词
1.使用between and简化查询
select name
from instructor
where salary between 90000 and 100000;
1
2
3
3.5 集合运算
3.5.1 并运算
找出在2009年秋季开课，或者在2010年春季开课的课程（使用并运算）
(select course_id
from section
where semester = 'Fall' and year = 2009)
union
(select course_id
from section
where semester = 'Spring' and year = 2010);
1
2
3
4
5
6
7
3.5.2 交，差运算
MySQL中不支持交和差运算运算，使用intersect和except会报错
3.6 空值
如果算数表达式的任意输入为空，则该算数表达式(+ , - , * , /等)的结果也为空
1 < null的结果为unknown
and：true and unknown的结果为unknown，false and unknown的结果为false，unknown and unknown的结果是unknown
or：true or unknown的结果是true，false or unknown的结果是unknown，unknown or unknown的结果是unknown
not：not unknown的结果是unknown
select name
from instructor
where salary is null;
1
2
3
3.7 聚集函数
3.7.1 基本聚集
1.找出历史系教师的平均工资
select avg(salary)
from instructor
where dept_name = 'History';
1
2
3
2.找出在2010年春季教授一门课程的教师总数
select count(distinct ID)
from teaches
where semester = 'Spring' and year = 2010;
1
2
3
3.7.2 分组聚集
1.找出每个系的平均工资
select dept_name,avg(salary) as avg_salary
from instructor
group by dept_name;
1
2
3
3.7.3 having子句
1.找出每个系的平均工资且平均工资大于4000的系名
select dept_name,avg(salary) as avg_salary
from instructor
group by dept_name
having avg_salary > 42000;
1
2
3
4
3.7.4 对空值和布尔值的聚集
除了count(*)以外的所有聚集函数均忽略输入集合中的空值
3.8 嵌套子查询
3.8.1 集合成员资格
连接词in可以用于测试元组是否是集合中的成员
连接词not in用于测试元组中是否不是集合中的成员
例：找出在2009年秋季和2010年春季学期同时开课的所有课程
select distinct course_id
from section
where semester = 'Fall' and year = 2009 and course_id in
(select course_id
from section
where semester = 'Spring' and year = 2010);
1
2
3
4
5
6
例：找出在2009年秋季开课但不在2010年春季学期开课的所有课程
select distinct course_id
from section
where semester = 'Fall' and year = 2009 and course_id not in
(select course_id
from section
where semester='Spring' and year = 2010);
1
2
3
4
5
6
3.8.2 集合的比较
1.>some表示至少比某一个大
例：找出至少比Biology系的某一个教师工资要高的教师姓名
select name
from instructor
where salary >some
(select salary
from instructor
where dept_name = 'Biology');
1
2
3
4
5
6
2.SQL也允许使用<some,<=some,>=some,=some,<>some等的使用
3.与some类似，<all,<=all,>=all,=all,<>all的功能类似
找出平均工资大于所有系平均工资的系
select dept_name
from instructor
group by dept_name
having avg(salary) >=all
(select avg(salary)
from instructor
group by dept_name);
1
2
3
4
5
6
7
3.8.3 空关系测试
使用exists，当子查询非空时返回true，子查询为空时返回false
例：找出选修了Biology系开设的所有课程的学生
select S.ID,S.name
from student as S
where not exists(
(select course_id
from course
where dept_name='Biology')
except
(select T.course_id
from takes as T
where S.ID = T.ID));
1
2
3
4
5
6
7
8
9
10
exists的执行逻辑和原理要求完全掌握
3.8.6 with子句
使用with子句可以新建一张临时表
例：找出具有最大运算的系
with max_budget(value) as
(select max(budget)
from department)
select budget
from department
where department.budget = max_budget.value;
1
2
3
4
5
6
3.9 数据库的修改
3.9.1 删除
删除语句的基本语法格式
delete
from 表名
where 条件
1
2
3
没有where时默认删除整张表
3.9.2 插入
1.最基本的插入语句
insert into course
values('CS-437','Database Systems','Comp. Sci. ',4);
1
2
2.可以在insert语句中指定要插入的属性
insert into course(course_id,title,dept_name,credits)
values('CS-437','Database Systems','Comp. Sci. ',4);
1
2
3.9.3 更新
1.一般的update语句
update 表名
set xxx
where xxx;
1
2
3
2.升级的update语句
update table_name
set xxx = case
when pred1 then result1
when pred2 then result2
...
else result0
end;
1
2
3
4
5
6
7
3.例：给工资超过100000美元的教师涨3%的工资，其余教师涨5%
update instructor
set salary = case
when salary > 100000 then salary*1.03
else salary*1.05
end;
————————————————
版权声明：本文为CSDN博主「叶卡捷琳堡」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_46841376/article/details/114854398