数据库系统概念笔记 CH3

最新推荐文章于 2024-12-02 21:31:56 发布

原创最新推荐文章于 2024-12-02 21:31:56 发布 · 379 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#数据库系统概念笔记 #数据库理论 #SQL基本操作 #SQL

数据库专栏收录该内容

20 篇文章

订阅专栏

本文详细介绍了数据库系统概念中的SQL章节，包括SQL数据定义，如数据类型、完整性约束，以及基本的插入、删除命令。重点讲解了SQL查询的基本结构、附加运算，如集合运算、空值处理、聚集函数，还涵盖了嵌套子查询和数据库的修改操作。

数据库系统概念笔记 CH3

第三章 SQL

SQL数据定义

数据库中的关系集合必须由数据定义语言(DDL)指定给系统

SQL支持多种数据类型，包括：

char(n):固定长度为n的字符串
varchar(n):最大长度为n的可变长字符串
int:整数类型
smallint:小整数类型
numeric(p,d)：定点数，p位数字，d位在小数点后面
real,double precision：浮点数，双精度浮点数
float(n):精度至少为n的浮点数

补充：如果使用char(5)存储"abc"，则后面未使用的两位将补充空格，如果使用varchar就不会出现该情况。故如果设计字符串的比较问题，个人建议均使用varchar较为方便，可以避免一些问题。

--创建一个表  通用格式：
creat table r
    (A1,D1
     A2,D2,
     ...
     An,Dn,
     <完整性约束1>
     ...
     <完整性约束n>
     );
--例如：
create table department
	(dept_name varchar(20),
     building varchar(15),
     budget numeric(12,2),
     primary key (dept_name)   --完整性约束，将dept_name设置为主码
     );

完整性约束：

primary key(A1,A2,…An):表示属性A1,A2,…An构成了该关系的主码。主码必须非空且唯一
foreign key(A1,A2,…An) references table2 : 声明表示关系的任意元组的外码属性必须与后者的主码属性一致
not null :表示该属性不能为空值

基本的插入、删除命令：

--插入命令
insert into instructor
	values(id,name..);
--删除元组
delete from student ;--删除所有元组
delete table student ;--彻底删除，该关系将被删除
--为原有关系增加属性
alter table r add A D;
--其中，r是关系的名字， A是添加属性的名字，D是添加属性的域

--为原有关系删除属性
alter table r drop A;

SQL查询的基本结构

基本结构：select … from … where…

select子句用来列出查询结果中所需的属性
from子句是一个查询求值中需要访问的关系列表
where子句是一个作用在from子句中关系的属性上的谓词

例如:

--找出所有教师的名字
select name
from instructor;

--找出所有计算机学院的教师名字
select name
from instructor
where dept_name='Comp.Sci.'

可以通过在select后加入distinct去除重复，使用all显式声明不去重

--去重
select distinct dept_name
from instructor
--不去重
select all dept_name
from instructor

还有要注意区分笛卡尔积和自然连接：

select ...
from R1,R2,R3
--则在此处from语句查询的列表是R1,R2,R3三者的笛卡尔积形成的列表。即三者元组的所有组合

select ...
from R1 natural join R2
--则在此处是R1与R2的自然连接

一些附加的基本运算

更名运算 (用as对变量进行重命名)

select ID,name,salary/12 as monthly_salary
from instructor

select distinct T.name
from instructor as T ,instructor as S
where T.salary>S.salary and S.dept_name='Comp.Sci.'

字符串运算
- upper(s)将字符串转化为大写
- lower(s)将字符串转换为小写
- trim(s)去掉字符串后面的空格
- like语句实现字符串匹配：%表示匹配任意字串，_ 表示匹配任意一个字符
```
select name
from instructor 
where name like '%dar%'
```
使用select *显示所有属性
```
select *
from instructor
```

使用order by使得查询结果按照一定顺序显示

--按照工资从高到低排序，工资相同则按照名字升序排序
select *
from instructor
order by salary desc,name asc 
--desc表示降序，asc表示升序

where中的between运算符和元组比较

--between运算符
select name
from instructor
where salary between 90000 and 100000

--元组比较
select name,course_id
from instructor,teaches
where (instructor.ID,dept_name)=(teaches.ID,'Biology')

集合运算

SQL中的 ∩，∪，一三种集合操作

union:对应并集∪

例子:

--为了找出在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);

union自动去重，可以用union all来表示不去重

intersect:交集运算∩

例子：

--为了找出在2009年秋季以及在2010年春季同时开课的课程
(select course_id
 from section
 where semester='Fall' and year=2009
)
intersect
(select course_id
 from section
 where semester='Spring' and year=2010);

except:差运算 - 从第一个输入中输出所有不在第二个输入中的元组

例子:

--为了找出在2009年秋季开课但是不在2010年春季开课的课程
(select course_id
 from section
 where semester='Fall' and year=2009
)
except
(select course_id
 from section
 where semester='Spring' and year=2010);

以上三种运算均默认去重，如果想不去重，需要显式地在后面加上all

空值

SQL将涉及空值任何比较运算的结果视为unknown(unknown既不是true也不是false，是新的逻辑值)

and: true and unknown 的结果为unknown,false and unknown的结果是 false
or: true or unknown结果为true , false or unknown的结果为unknown
not: not unknown的结果还是unknown

聚集函数

SQL提供了五个固有的聚集函数:

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

一些基本的聚集操作:

--求出计算机学院老师的平均工资
select avg(salary) as avg_salary  --使用as重命名
from instructor
where dept_name='Comp.Sci';

--计算course关系中的元组数
select count(*)
from course;

--找出在2010年春季学期讲授一门课的教师总数
select count(distinct ID)
from teachers
where semester='Spring ' and year='2010'

使用group by实现分组聚类:

--找出每个系的平均工资
select dept_name,avg(salary) as avg_salary
from instructor
group by dept_name  --通过部门名称进行分类

--找出每个系在2010年春季学期讲授一门课程的教师人数
select dept_name,count(distinct ID)as instr_count
from instructor natural join teaches
where semester='Spring' and year=2010
group by dept_name

having子句: 针对group by聚类后的对象进行

--选出教师平均工资超过42000美元的系
select dept_name ,avg(salary) as avg_salary
from instructor
group by dept_name
having avg(salary)>42000

嵌套子查询

嵌套子查询可以出现在任何关系可以出现的地方

--where语句中的子查询
--为了找出在2009年秋季以及在2010年春季同时开课的课程
--之前我们采用了交集的形式，现在换一种形式
(select 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)
);
--在这里也用到了in，用来测试是否是集合中的成员

--from语句中的子查询
--选出教师平均工资超过42000美元的系
select dept_name,avg_salary
from (select dept_name,avg(salary) as avg_salary
      from instructor
      group by dept_name)
where avg(salary)>42000;

with语句：提供定义临时关系的方法，这个定义只对包含with子句的查询有效

--找出具有最大预算的系
--使用with构成的关系将对之后的包含该句的查询起效
with max_budget(value) as
	(select max(budget)
     from department)
select budget
from department,max_budget
where department.budget=max_budget.value;

数据库的修改

删除：删除某些元组

--通类：
delete from r
where P;
--删除工资在12000-15000之间的教师
delete from instructor
where salary between 12000 and 15000;
--删除工资低于大学平均工资的教师
delete from instructor
where salary<(select avg(salary) from instuctor);
--删除位于Watson大楼的系工作的教师
delete from instructor
where dept_name in (select dept_name from department where building='Watson');

插入:插入新的元组

--插入新的元组
insert into course
	values('CS-437','Database Systems','Comp.Sci.',4);
insert into student
	values('3003','Green','Finance',null)

更新:update

--工资低于100000的涨薪5%,高于100000的涨薪3%
update instructor
set salary=salary*1.03
where salary>100000;
update instructor
set salary=salary*1.05
where salary<=100000
--这里的顺序是比较重要的，否则出现重复更新

--这个语句可以用case end来处理
update instructor
set salary=case
when salary<=100000 then salary*1.05
else salary*1.03
end;