SQL复习

SQL

---

1. SQL语句

1.1 DDL

• CREATE、DROP、ALTER
• 关系模式、属性域、约束

1.2 DML

SELECT、INSERT、UPDATE、DELETE

1.3 DCL

GRANT、REVOKE

---

2. 数据类型

2.1 字符型：

char(n),
varchar(n)

2.2 数值型：

byte
int, smallint
numeric(p,d)
real, double precision
float(n)

2.3 时间型：

date, time, timestamp, interval

2.4 大文件：

blob, clob （适于图片、视频、CAD 文件等大数据对象，查询时通常返回指针（地址）而非对象本身)

等等

---

3. 运算符

---

3.1 + - * / %

运算符	描述
+ 加法	把运算符两边的值相加
- 减法	左操作数减去右操作数
* 乘法	把运算符两边的值相乘
/ 除法	左操作数除以右操作数
% 取模	左操作数除以右操作数后得到的余数

3.2 比较运算符

运算符	描述
==	检查两个操作数的值是否相等，如果相等则条件为真
=	检查左操作数的值是否不大于右操作数的值，如果是则条件为真
!=	检查两个操作数的值是否相等，如果不相等则条件为真。
<>	检查两个操作数的值是否相等，如果不相等则条件为真
>	检查左操作数的值是否大于右操作数的值，如果是则条件为真
<	检查左操作数的值是否小于右操作数的值，如果是则条件为真
>=	检查左操作数的值是否大于等于右操作数的值，如果是则条件为真
<=	检查左操作数的值是否小于等于右操作数的值，如果是则条件为真
!<	检查左操作数的值是否不小于右操作数的值，如果是则条件为真
!>	检查左操作数的值是否不大于右操作数的值，如果是则条件为真

3.3 位运算符

运算符	描述
&	如果同时存在于两个操作数中，二进制 AND 运算符复制一位到结果中
||	如果存在于任一操作数中，二进制 OR 运算符复制一位到结果中
~	二进制补码运算符是一元运算符，具有"翻转"位效应
<<	二进制左移运算符。左操作数的值向左移动右操作数指定的位数
>>	二进制右移运算符。左操作数的值向右移动右操作数指定的位数

3.4 逻辑运算符

运算符	描述
AND	AND 运算符允许在一个 SQL 语句的 WHERE 子句中的多个条件的存在。
BETWEEN	BETWEEN 运算符用于在给定最小值和最大值范围内的一系列值中搜索值。
EXISTS	EXISTS 运算符子查询中条件的真假，真则返回主查询，否则不返回。
IN	IN运算符用于把某个值与一系列指定列表的值进行比较。（是否在一个集合里面）
NOT IN	IN 运算符的对立面，用于把某个值与不在一系列指定列表的值进行比较。
LIKE	LIKE运算符用于把某个值与使用通配符运算符的相似值进行比较。
GLOB	GLOB 运算符用于把某个值与使用通配符运算符的相似值进行比较。GLOB 与 LIKE 不同之处在于，它是大小写敏感的。
NOT	NOT运算符是所用的逻辑运算符的对立面。比如 NOT EXISTS、NOT BETWEEN、NOT IN，等等。它是否定运算符。
OR	OR运算符用于结合一个 SQL 语句的 WHERE 子句中的多个条件。
IS NULL	NULL 运算符用于把某个值与 NULL 值进行比较。
IS	IS 运算符与 = 相似。
IS NOT	IS NOT 运算符与 != 相似。
||	连接两个不同的字符串，得到一个新的字符串。
UNIQUE	UNIQUE运算符搜索指定表中的每一行，确保唯一性（无重复）。

3.5 check约束

CHECK 约束用于限制列中的值的范围。
如果对单个列定义 CHECK 约束，那么该列只允许特定的值。
如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。

3.6 ALL、ANY、SOME

All： 对所有数据都满足条件，整个条件才成立，例如：5大于所有返回的id

select *
from #A
where 5>All(select id from #A)

Any： 只要有一条数据满足条件，整个条件成立，例如：3大于1，2

select *
from #A
where 3>any(select id from #A)

Some： 和Any一样

About check
About operator

---

4. 实例

4.1 create table

create table student(
	stu_id varchar(20),//  primary key
	stu_name varchar(20) not null,
	stu_join time,
	stu_level int,
	stu_other varchar(1024),
	primary key (stu_id),//设置主键
	check( stu_level between (2000,2100))//检查stu_level 是否符合标准
)

create table takes(
	ID varchar(5),
	stu_id varchar(20),
	course_id varchar(20),
	grade float,
	primary key (ID),
	foreign key (stu_id) references student,//外键
	foreign key (course_id) references course on delete cascade
	// on delete cascade 是添加删除的级联 
	// 主外键关系中,级联删除,即删除主表数据会自动删除从表数据
	
)

on update 和 on delete 后面可以跟的词语有四个
分别是 ： no action ， set null ， set default ，cascade
no action ： 不做任何操作，
set null ： 在外键表中将相应字段设置为null
set default ： 设置为默认值
cascade ： 级联操作

---

4.2 SELECT

select stu_id,stu_name		// 需要查询的属性列
from student as STU			// 查询哪一个表，创建别名
where stu_level = "2019" and stu_name = "Alvin" 	// 查询条件（会用到上面的运算符）
order by stu_id 			//order by id desc

as : 给student表取别名 STU
order by id ：依据id排序（默认增序，降序需要在后面添加 desc：order by id desc）

4.3 UNION、INTERSECT、EXCEPT

(select course_id from section where sem = ‘Fall’ and year = 2009)
union
(select course_id from section where sem = ‘Spring’ and year = 2010)

(select course_id from section where sem = ‘Fall’ and year = 2009)
intersect
(select course_id from section where sem = ‘Spring’ and year = 2010)

(select course_id from section where sem = ‘Fall’ and year = 2009)
except
(select course_id from section where sem = ‘Spring’ and year = 2010)

4.4 聚集查询

select sum(salary), avg(salary),count(*)
form instructor

聚集和Group By

select dept_name ,avg(salary)// 聚集函数的使用
from instructor
group by dept_name			// 按照部门名称分组
having avg(salary)> 50000	// having 子句去掉不符合条件的组。

where：数据库中常用的是where关键字，用于在初始表中筛选查询。它是一个约束声明，用于约束数据，在返回结果集之前起作用。
where 是用于在初始表中筛选查询，having 用于group by 结果分组中查询
having 子句中的每一个元素也必须出现在select列表中
having 语句可以使用聚合函数，而where不使用。

---

4.5 嵌套查询

4.5.1 子查询作为判断条件

//查询 2010春季和2009秋季开的课程
select distinct course_id
from section
where semester = ’Fall’ and year= 2009 and
	course_id in (			//嵌套部分的 select语句
		select course_id
		from section
		where semester = ’Spring’ and year= 2010);

select name
from instructor
where salary > some (// 嵌套查询中只要有一个满足这个 > 条件就行
		select salary
		from instructor
		where dept_name = ’Biology’);

//查询在 查询在2009年秋季和2010年春季均开设的课程
select course_id
from section as S
where semester = ’Fall’ and year= 2009 and
		exists (//子查询中条件的真假，真则返回主查询，否则不返回
			select * from section as T
			where semester = ’Spring’ and year= 2010
			and S.course_id= T.course_id);

//查询选修了Biology 系开设的所有课程的学生
select distinct S.ID, S.name
from student as S
where not exists ( 
			(select course_id
			from course
			where dept_name = ’Biology’)
			except // 类似于集合操作的 A - (A ∩ B)
			(select T.course_id
			from takes as T
			where S.ID = T.ID));

在表中，可能会包含重复值。这并不成问题，不过，有时您也许希望仅仅列出不同（distinct）的值。关键词 distinct用于返回唯一不同的值。

4.5.2 子查询作为数据表

select dept_name,avg_salary// avg_salary 是子查询生成的表中的属性
from (
		select dept_name, avg(salary) as avg_salary// as 设置别名
		from instructor
		group by dept_name
		)
where avg_salary > 50000;

4.6 INSERT

// 直接插入数据
insert 
into course
values 
(’CS-437’, ’Database Systems’, ’Comp. Sci.’, 4);

// 指定列名及被插入的值
INSERT 
INTO table_name 
(column1,column2,column3,...)//自定义指定列名称
VALUES 
(value1,value2,value3,...);

//将instructor的信息导入到student表中去
insert into student
select ID, name, dept_name, 0
from instructor;

4.7 DELETE

delete 
from instructor
where 
dept_name = "计算机学院" and name = "哈哈"

// 嵌入了子查询的delete语句
delete from instructor
where dept_name in (
		select dept_name//嵌入了子查询
		from department
		where building = ’Watson’);

delete from instructor
where salary < 
			(select avg (salary) //使用且仅仅使用了聚集函数，返回一行
			from instructor);

4.8 UPDATE

update instructor
set salary = salary * 2
where salary > 10000

update student as S
set tot_cred = 
	(select sum(credits)
	from takes natural join course// takes 和 course的自然连接
	where 
		S.ID= takes.ID and
		takes.grade <> ’F’ and
		takes.grade is not null);

4.9 Grant & Revoke

with admin option和with grant option的区别与用法

GRANT ALL PRIVILIGES 	// 赋予 ALL PRIVILIGES
ON Course 				// 基于 Course 表
TO U1, U2				// 给用户 U1, U2	
WITH GRANT OPTION;		// 权限传递权限

/*
1. 如果带了 with grant option 
那么用户U1, U2可以将ALL PRIVILIGES权限传递给其他用户( 如U3)
grant ALL PRIVILIGES on Course to U3
2. 如果没带with grant option
  那么用户U1, U2不能给U3授权
*/
GRANT INSERT ON Student TO U3;
GRANT SELECT ON Instructor TO PUBLIC;//权限给所有人
GRANT UPDATE ON Deptment TO U4;
GRANT SELECT ON department TO Amit WITH GRANT OPTION;// Amit 具有此权限的传递权力

REVOKE SELECT 		// 删除select权限
ON department 		// 基于department表
FROM Amit, Satoshi 	// 用户 Amit, Satoshi
CASCADE;			// 级联：级联删除的Amit, Satoshi赋予其他用户department表的select权限

REVOKE SELECT 
ON department 
FROM Amit, Satoshi 
RESTRICT;			

4.10 ROLE

角色：一个权限的集合；

一个公司普通员工，被老板升为经理后，他拥有了作为经理的权力
可以将经理看作一个角色，对于公司经理具有更多的权力，公司员工作为用户

create role teach_assis//创建角色
grant select on takes to teach_assis;//将takes的select权限赋予角色

grant teach_assis to ALVIN;//将角色赋予用户ALVIN

4.11 VIEW

4.11.1 普通视图

将SQL的查询转化为视图

create view faculty 
as
	select ID, name, dept_name
	from instructor

create view dept_total_salary(dept_name, total_salary) 
as
		select dept_name, sum (salary)
		from instructor
		group by dept_name;

create view physics_fall_2009_watson 
as
	(select course_id, room_number
	from 
		(select course.course_id, building, room_number
		from course, section
		where course.course_id = section.course_id
			and course.dept_name = ’Physics’
			and section.semester = ’Fall’
			and section.year = ’2009’)
	where building= ’Watson’);

4.11.2 物化视图

1. 创建一个表用来存储视图数据
2. 若基表被修改，物化视图中的数据需要 自动或
   人工更新

CREATE MATERIALIZED VIEW XX
REFRESH [[fast | complete | force]
[on demand | commit]
[start with date]
[next date]
[with {primary key | rowid}]
]
[ENABLE | DISABLE] QUERY REWRITE

4.12 存储过程

存储过程(Storage Procedure)：

• 过程化结构 过程化结构
• 存储过程一经定义，即被存储在数据库中 存储在数据库中
• 类似函数，应用程序可调用

定义：

//dept_count_proc 名称
create procedure dept_count_proc (in dept_name varchar(20),//in : 输入
									out d_count integer)//out : 输出
begin//类似C++的 {
	select count(*) into d_count
	from instructor
	where instructor.dept_name = dept_count_proc.dept_name
end//类似C++的 }

调用：

declare d_count integer;					//声明字段
call dept_count_proc( ‘Physics’, d_count);	// 过程调用

4.13 触发器Trigger

触发器是表进行插入（insert）、更新（update）或者删除（delet）的时候自动执行的特殊存储过程。

在SQL中触发器可以分为DML触发器和DDL触发器，其中会为响应多种数据定义语言（DDL）语句而触发，这些语句主要是 create，alter，drop 开头的语句。

• 触发器是一条语句，当对 数据库修改时 ，它自动被系统执行
• 触发器模型：事件 触发器模型：事件 — 条件 — 动作模型
• 触发器一经定义，将被存储在数据库中
• 触发器机制有利于警示或满足特定条件时自行 执行某项任务。执行某项任务

语法：

CREATE TRIGGER trigger_name 
ON {table_name | view_name} 
{FOR | After | Instead of } [ insert, update,delete ]
AS           
    sql_statement  

OR

create trigger 触发器名称
on 要监测的表名称
[with encryption]加密
for 要监测的动作 [insert，update，delete]
as
T-SQL 语句
go

触发器