数据库基础（期中复习版）

数据库基础（期中复习版）

一、数据定义

	创建	删除	修改
模式	create schema	drop schema
表	create table	drop table	alter table
视图	create view	drop view
索引	create index	drop index	alter index

1.1模式

create role jjj  identified by 'dboper@123'
create schema jjj authorization jqn
drop schema jjj cascade(级联)
drop user jjj

1.1.1定义

-- <模式名> = NULL,默认模式名为用户名
create schema <模式名> authorization <用户名>

-- 为用户WANG定义一个学生-课程模式S-T
create schema S-T authorization WANG
-- 为用户ZHANG创建一个模式TEST，并且在其中定义一个表TAB1
create schema TEST authorization ZHANG
create table TAB1 (col1 int,
                   col2 int)

1.1.2删除

cascade(级联)|restrict(限制)两者必选其一

drop schema <模式名> <cascade(级联)|restrict(限制)>

-- 基本表
create table jjj 
(jqn1 int,
jqn2 char(4),
jqn3 varchar)
-- 加列
alter table jjj add column jjj4 int unique
-- 加约束
alter table jjj add primary key(jjj1)
-- 删除列
alter table jjj drop column jjj2
-- 修改数据类型（varchar - int）
alter table jjj alter column jjj3 int
-- 索引
create unique index j1 on jqn(jjj1 desc)
-- 重命名
alter index j1 rename to q1
-- 删除
drop index q1

drop table jjj cascade

1.2基本表

1.2.1定义

-- 列级完整性约束条件：primary key/unique/not null/...
-- 表级完整性约束条件：foreign key...references/...
create table <表名> (<列名> <数据类型> [列级完整性约束条件]

									[, <列名> <数据类型> [列级完整性约束条件]]

									...

									[, <表级完整性约束条件>]

									)

定义一个学生表“Student”

create table Student(
    Sno char(9) primary key,
    Sname char(20) unique,
    Ssex char(2),
    Sage smallint,
    Sdept char(20)
)

建立一个课程表“Course”

create table Course(
Cno char(4) primary key,
Cname char(10) not null,
Cpno char(4),
Ccredit smallint,
foreign key(Cpno) references Course(Cno))

建立学生选课表SC

create table SC(
Sno char(9),
Cno char(4),
Grade smallint,
primary key(Sno,Cno),
foreign key(Sno) references Student(Sno),
foreign key(Cno) references Course(Cno))

1.2.2修改

alter table <表名> 
[add [column] <新列名> <数据类型> [完整性约束]]
[add <表级完整性约束条件>]
[drop [column] <列名> [cascade(级联)|restrict(限制)]]
[drop constraint <完整性约束名> [cascade(级联)|restrict(限制)]]
[alter column <列名> <数据类型>]

alter table Student add S_entrance Date;
alter table Student alter column Sage int;
alter table Course add unique(Cname);

1.2.3删除

drop table <表名> [cascade(级联)|restrict(限制)]

drop table Student cascade;

1.3索引

1.3.1建立

默认升序

-- cluster聚簇索引
-- 次序：asc升序（默认）/desc降序
create [unique][cluster] index <索引名>
on <表名> (<列名> [<次序>][,<列名> [<次序>]]...)

create unique index Stusno on Student(Sno DESC);

1.3.2修改

alter index <旧索引名> rename to <新索引名>

alter index SCno rename to SCSno;

1.3.3删除

drop index <索引名>

drop index Stusname;

1.4视图

1.4.1定义

-- with check option 表示对视图进行操作时要保证其满足视图定义中的谓语条件（即子查询中的条件表达式）
create view <视图名> [(<列名> [,<列名>]...)]
as <子查询>
[with check option]

create view BT_S(Sno,Sname,Sbirth)
as
select Sno,Sname,2014-Sage
form student

1.4.2删除

drop view <视图名> [cascade]

1.4.3查询

从单个基本表导出，并且只是去掉了基本表的某些行和某些列，但保留了主码，称这类视图为行列子集视图。

1.4.4更新

一般的，行列子集视图是可更新的。

实际系统规定使一些可更新视图成为不允许更新的。

二、数据查询

-- 查询所有供应商的信息，用中文表头显示
SELECT sno 供应商号, sname 供应商名, stat 等级, city 所在城市
FROM S
-- 查询位于“北京”的名称包含“星”的供应商信息；
SELECT *
FROM s
WHERE city='北京' AND sname LIKE '%星%'
-- 查询供应商名中最后一个字是“丰”的供应商信息；
SELECT *
FROM s
WHERE sname LIKE '%丰'
-- 查询零件名以“螺丝”开头的零件信息；
select *
FROM p 
WHERE pname LIKE '螺丝%'
-- 查询名称含有“车”的工程项目信息；
select *
FROM j 
WHERE jname LIKE '%车%'
-- 查询名称为“螺母”、“螺栓”、“螺丝刀”的零件信息；
SELECT *
FROM p
WHERE pname='螺母' OR pname='螺栓' OR pname='螺丝刀'
-- 查询“S001”号供应商的供应情况；
SELECT *
FROM spj
WHERE sno='S001'
-- 查询“P002”号零件的总供应量；
-- 使用sum()函数进行求和
SELECT sum(qty)
FROM spj
WHERE pno='P002'
-- 分组计算每个工程项目使用每种零件的供应量；
SELECT JNO,PNO,SUM(QTY)
FROM spj
GROUP BY JNO,PNO
-- 查询供应量在300以上的供应信息；
SELECT *
FROM spj
WHERE qty>300
-- 查询供应量最低的两个供应信息；
SELECT *
FROM spj
ORDER BY qty ASC limit 2
-- 查询供应量前三名的供应商的编号；
SELECT SNO
FROM spj
ORDER BY qty DESC limit 3
-- 分组统计每个供应商供应每种零件的供应量。
SELECT SNO,PNO,SUM(QTY)
FROM spj
GROUP BY SNO,PNO

-- <目标表达式> = * 查询全部列
-- <目标表达式> 可以是表中的属性列/算术表达式/字符串常量/函数/...
-- all（默认）| distinct 消除取整重复的行
select [all|distinct] <目标表达式> [,<目标列表达式>]...
from <表名或视图名> [,<表名或视图名>...]|(<select语句>)[as]<别名>
[where <条件表达式>]
[group by <列名1> [having <条件表达式>]]
[order by <列名2> [asc|desc]]

2.1where子句

2.1.1常用的查询条件

查询条件	谓词
比较	=,>,<,>=,<=,!=,<>,!>,!<;not+上述比较运算符
确定范围	between(下限)and(上限),not between and
确定集合	in,not in
字符匹配	like,not like
空值	is null,is not null
多重条件	and,or,not

2.1.2字符匹配

如果like后面的匹配串中不含通配符，则可以用=（等于）运算符取代like谓词，用!=或<>（不等于）取代not like谓词。

[not] like '<匹配串>' [escape '<换码字符>']

• % 任意长度（可以为0）的字符串
• _ 任意单个字符

如果用户要查询的语句的字符本身就含有通配符%或_，这时就要使用escape '换码字符’短语对通配符进行转义了。

-- DB\_Design=DB_Design
escape '\'

2.1.3涉及空值的查询

is null

注意：这里的is不能用”=“代替

2.1.4多重条件查询

and优先级高于or

2.2order by子句

asc（默认升序）|desc（降序）

2.3group by子句

将查询结果按某一列或多列的值分组

select Sno
from SC
group by Sno
having count(*)>3

2.4聚簇函数

• count(*)统计元组个数
• count([distinct|all] <列名>)统计一列中值的个数
• sum([distinct|all] <列名>)统计一列值的总和（此列必须是数值型）
• avg([distinct|all] <列名>)统计一列值的平均值（此列必须是数值型）
• max([distinct|all] <列名>)求一列值中的最大值
• min([distinct|all] <列名>)求一列值中的最小值

注意：all（默认）| distinct 消除取整重复的行

• 只能用于select子句和group by中的having子句。
• 不能用于where子句。
• count遇到空值仍然计数。

2.5连接查询

2.5.1等值与非等值连接查询

2.5.1.1where子句

-- 运算符为“=”时，等值连接
[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>

[<表名1>.]<列名1> between [<表名2>.]<列名2> and [<表名2>.]<列名3>

注意：属性名前加上表名前缀，是为了避免混淆。

2.5.2自身连接

注意取别名

-- first/second为别名
select first.Cno,second.Cpno
from Course first,Course second
where first.Cpno=second.Cno

2.5.3外连接

select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
from Student,SC
where Studenr.Sno=SC.Sno

-- 左连接
-- 使用using去除结果中的重复值
select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
from Student left outer join sc on (Studenr.Sno=SC.Sno)
from Student left outer join sc using Sno

2.5.4多表连接

2.6嵌套查询

将一个查询块嵌套在另一个查询块的where子句或having短句的条件中的查询

2.6.1带有in谓词的子查询

select Sno,Sname,Sdept
from Student
where Sdept in
	(select Sdept
     from Student
     where Sname='刘晨')

2.6.2带有比较运算符的子查询

当用户能确切知道内层查询返回的是单个值（一个字段而不是一个集合）时，可以用比较运算符。

此时可以用"="代替in。

select Sno,Sname,Sdept
from Student
where Sdept =
	(select Sdept
     from Student
     where Sname='刘晨')

2.6.3不相关子查询

select Sno,Sname,Sdept
from Student
where Sdept in
	(select Sdept
    from Student
    where Sname='刘晨')

2.6.4相关子查询

select Sno,Cno
from SC x
where Grade >= (select avg(Grade)
				from SC y
				where y.Sno=x.Sno)

2.6.5带有any(some)或all谓词的子查询

select Sname,Sage
from Student
where Sage < any (select Sage
				from Student
				where Sdept = 'cs')

子查询返回单值时可以使用比较运算符，但返回多值时要用any（有的系统用some）或all谓词修饰符-必须同时使用比较运算符。

	=	<>或!=	<	<=	>	>=
any	in	–	<max	<=max	>min	>=min
all	–	not in	<min	<=min	>max	>=max

2.6.6带有exists谓词的子查询

带有exists谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。

-- 若内层查询结果非空，则外层的where子句返回直值，否则返回假值。
select Sname
from Student
where exists
	(select *
	from SC
	where Sno=Student.Sno and Cno='1')

2.7集合查询

并操作union，交操作intersect和差操作except

2.8基于派生表的查询

通过from子句生成派生表时，as关键字可以省略，但必须为派生关系指定一个别名。

2.9空值的处理

空值与另一个值（包括另一个空值）的比较结果为unknown。

三、数据更新

3.1插入

3.1.1元组

insert
into <表名> [(<属性列1>[,<属性列2>]...)]
values (<常量1> [,<常量2>]...)

注意：字符串常量要用单引号（英文符号）括起来。

into子句只指出表名，不指出属性名，默认新元组在表的所有属性列上都指定值（空值要指定NULL），属性列的次序与create table中的次序相同。

insert
into Student(Sno,Sname,Ssex,Sdept,Sage)
values('201225128','陈东','男','IS',18)

insert
into SC
values('201225128','1',null)

3.1.2子查询结果

insert
into <表名> [(<属性列1>[,<属性列2>]...)]
子查询

insert
into Dept_age(Sdept,Avg_age)
select Sdept,avg(Sage)
from Student
group by Sdept;

3.2修改

update <表名>
set <列名>=<表达式> [,<列名>=<表达式>]...
[where <条件>]

3.2.1修改一个元组的值

update Student
set Sage=22
where Sno='201215121'

update Student
set Sage=22
where Sno="20120213"

3.2.2修改多个元组的值

update Student
set Sage=Sage+1

3.2.3带子查询的修改语句

update SC
set Grade=0
where Sno in
	(select Sno
	from Student
	where Sdept='CS')

3.3删除

删除的是表中的数据，而不是关于表的定义。

delete
from <表名>
[where <条件>]

四、数据库安全性

4.1自主存取控制方法（DAC）

4.1.1授权：授予与收回

对象类型	对象	操作类型
数据库模式	模式	create schema
	基本表	create table,alter table
	视图	create view
	索引	create index
数据	基本表和视图	select,insert,update,delete,references,all privileges
	属性列	select,insert,update,references,all privileges

4.1.2创建数据库模式的权限

create user<username> [with][DBA|RESOURCE|CONNECT]

4.1.3grant

grant <权限>[,<权限>]...
on <对象类型> <对象名>[,<对象类型> <对象名>]...
to <用户>[,<用户>]...
-- 获得某种权限的用户可以把这种权限授予给其他用户，不允许循环授权
[with grant option]

4.1.4revoke

revoke <权限>[,<权限>]...
on <对象类型> <对象名>[,<对象类型> <对象名>]...
from <用户>[,<用户>]...[cascade|restrict]

4.1.5数据库角色

4.1.5.1创建

create role <角色名>

4.1.5.2授权

grant <权限>[,<权限>]...
on <对象类型> <对象名>[,<对象类型> <对象名>]...
to <角色>[,<角色>]...

4.1.5.3将一个角色授予其他的角色或用户

grant <角色1>[,<角色2>]...
to <角色3>[,<角色4>]...
[with grant option]

4.1.5.4收回

revoke <权限>[,<权限>]...
on <对象类型> <对象名>[,<对象类型> <对象名>]...
from <角色>[,<角色>]...

create role r1;
grant select,update,insert
on Student
to r1;
grant r1 
to 王牌，赵零;
revoke r1
from 王牌;
grant delete
on table Student
to r1;

4.2强制存取控制方法（MAC）

主体：系统中的活动实体，用户、进程等-----许可证级别

客体：系统中的被动实体，文件、基本表、索引、视图等-----密级

敏感度标记：绝密(TS)>=机密(S)>=可信©>=公开§

规则：

• 仅当主体许可证级别>=客体的密级时，该主体才能读取相应的客体
• 仅当主体许可证级别<=客体的密级时，该主体才能写相应的客体

五、数据库完整性

5.1实体完整性

• primary key

5.2参照完整性

• foreign key
• references

1. 拒绝执行（默认）

   no action

2. 级联

   cascade

3. 设置为空值

   set-null

5.3用户定义完整性

• not null
• unique
• check

5.4完整性约束命名子句

constraint <完整性约束条件名> <完整性约束条件>

create table Student
	(Sno numeric(6)
		constraint C1 check (Sno between 90000 and 99999),
	Sname char(20)
		constraint C2 not null,
	Sage numeric(3)
		constraint C3 check (Sage<30),
	Ssex char(2)
		constraint C4 check (Ssex in('男','女')),
		constraint StudentKey primary key(Sno));

5.4.1删除完整性约束条件

drop constraint <完整性约束条件名>

alter table drop constraint C3;

5.4.2增加完整性约束条件

add constraint <完整性约束条件名>

alter table add constraint C3 check (Sage<40);

5.5触发器

5.5.1定义

create trigger <触发器名>
{before|after} <触发事件> on <表名>
referencing new|old row as <变量>
for each{row|statement}
[when <触发条件>] <触发动作体>

5.5.2激活

before-sql-after

5.5.3删除

drop trigger <触发器名> on <表名>

六、关系运算

6.1选择

6.2投影

取消重复行

6.3连接

笛卡尔积+选择

6.4除

采用象集定义