【HBU数据库】SQL总结

部分转载自本人同名博客的文章:https://blog.csdn.net/weixin_45782091/article/details/115858499

第三章SQL

数据定义

模式定义和删除

模式定义

create schema <模式名> authorization <用户名>

模式删除

DROP SCHEMA  模式名 <cascade|restrict>;

cascade|restrict二者选1 选择cascade级联则可直接删除模式以及其下数据库对象 如果是restrict （限制）则如果有下属数据库对象的时候不能直接删除

基本表定义删除和修改

定义基本表：

CREATE TABLE <表名>
(
	<列名> <数据类型>[ <列级完整性约束条件> ]
	[,<列名> <数据类型>[ <列级完整性约束条件>] ] 
	...
	[,<表级完整性约束条件> ] 
);

最开始学拽的时候 对每个<>的意思还不是很懂 现在看 就很明了了

int数据类型存储的数据多于smallint数据类型，而smallint数据类型存储的数据多于tinyint数据类型。

使用int数据类型存储数据的范围从-2 147 483 648到2 147 483 647（每个值需要四个字节的存储空间）。
varchar是可变长度字符数据，其长度不超过8kb。char是最大长度为8kb的固定长度字符数据。超过8kb的ASCII数据可以使用文本数据类型存储。

摘自百度知道

约束系列后面总结的时候会提到

数据查询

SELECT [ALL|DISTINCT] <目标列表达式>[,<目标列表达式>] …
       FROM <表名或视图名>[,<表名或视图名> ]…|(SELECT 语句)      
                   [AS]<别名>
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ];

关键字消除取值重复的行 如果没有指定DISTINCT关键词，则缺省为ALL
即如果没有指定的DISTINCT：
确定范围
谓词BETWEEN…AND 和NOT BETWEEN…AND可以用来查找属性值在（或不在）指定范围内的元组，其中BETWEEN 后是范围的下限 AND后是上限
字符匹配
用谓词LIKE

[NOT] LIKE ‘<匹配串>’ [ESCAPE ‘<转码字符>’]

其含义是查找指定的属性列值与<匹配串>相匹配的元组，<匹配出>可以是一个字符串，也可以含有通配符%和_。
% 代表任意长度
_ 代表单个字符
但如果要查询的字符串里本身包含通配符%或者_ 就需要用ESCAPE ‘<换码字符>’ 短语对通配符进行转意
查询DB_Design 课程的课程号

select cno from Course where cname like 'DB\_Design' Escape '\';

ORDER BY
在order by 子句对查询结果按照一个或者多个属性列的升序ASC或降序DESC排列 默认为升序

Select Sname,Sdept from Student where Ssex='男' 
Order by Sage DESC;

这是一个按照年龄排序的例子

聚集函数：

COUNT(*) 统计一列中值的个数
COUNT([DISTINCT|ALL] <列名>) 
计算一列值的总和SUM([DISTINCT|ALL] <列名>) 
计算一列值的平均值 AVG([DISTINCT|ALL] <列名>) 
求一列中的最大值和最小值 
MAX([DISTINCT|ALL] <列名>) 
MIN([DISTINCT|ALL] <列名>)

注意： where里面不可以用聚集函数
因为二者作用对象不同
Where 作用于基本表或视图，从中筛选满足元素的组，
而having短语作用于组 从中筛选满足条件的组
GROUP BY 子句
group by就是分组，然后查询结果中如果有聚集函数就会针对的每个组里面的元素，在组里面用聚集函数。

having语句跟在group by 之后，是针对各个组的条件。

如果分组后还要按一定的要求对这些组进行筛选，最终只输出满足指定条件的组，则可以使用HAVING短语进行筛选

Select CNO,COUNT(SNO)
FROM SC
GROUP BY CNO
HAVING COUNT(*) >=2;

连接查询

等值与非等值连接查询
依靠where里的条件来实现
笛卡尔积：

Select * from a,b;

若在等值连接中把目标列中重复的属性列去掉则为自然连接

自身连接
这里可以通过巧妙的起别名来实现：

Select first.cno,second.cpno
from Course first,course second
where first.Cpno=second.cno;

外连接
在通常的连接操作中，只有满足连接条件的元组才能作为结果输出。但有时想某些不符合条件的元组保存在结果关系里，就需要使用外连接。
左外连接是列出左边关系中满足的元组

嵌套查询

带有IN谓词的子查询

嵌套查询里 子查询结果往往是一个集合，谓词IN是嵌套查询中最常使用的谓词

SELECT Sno, Sname, Sdept
    	FROM Student
   	WHERE Sdept  IN
                  (SELECT Sdept
                   FROM Student
                   WHERE Sname= '刘晨');
    

子查询的结果不依赖于父查询，所以该种查询可以被称为 不相关查询

带有比较运算符的子查询

SELECT Sno, Sname, Sdept
    	FROM Student
   	WHERE Sdept  =
                  (SELECT Sdept
                   FROM Student
                   WHERE Sname= '刘晨');

找出每个学生超过他选修课程平均成绩的课程号。

SELECT Sno, Cno
    FROM    SC  x
    WHERE Grade >=(SELECT AVG(Grade) 
		                               FROM  SC y
                                   WHERE y.Sno=x.Sno);

这里的这个子查询用到了父查询的结果，所以属于相关查询

带有ANY（SOME）或ALL谓词的子查询

使用ANY或ALL谓词时必须同时使用比较运算

查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和	年龄
值得说的是，这里的任意并非所有，而是只有一个就够了。

SELECT Sname,Sage
    FROM    Student
    WHERE Sage < ANY (SELECT  Sage
 FROM    Student
WHERE Sdept= 'CS ')
     AND Sdept <> 'CS ';

any指查询结果中的某个值，all指查询结果中的所有值

exists谓词

exists代表存在量词∃
带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
若内层查询结果非空，则外层的WHERE子句返回真值
若内层查询结果为空，则外层的WHERE子句返回假值

NOT EXISTS恰恰相反

 SELECT Sname
     FROM Student
     WHERE EXISTS
   (SELECT *
    FROM SC
   WHERE Sno=Student.Sno AND Cno= '1');

一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换
所有带IN谓词、比较运算符、ANY和ALL谓词的子查询
都能用带EXISTS谓词的子查询 等价替换

 SELECT Sno,Sname,Sdept
     FROM Student S1
      WHERE EXISTS
(SELECT *
     FROM Student S2
      WHERE S2.Sdept = S1.Sdept AND
   S2.Sname = '刘晨');

这是一个用exist代替in的例子

SQL中没有全称量词，(∀x)P = ┐(∃x(┐)P)
用EXISTS / NOT EXISTS实现逻辑蕴涵

查询至少选修了学生201215122选修的全部课程的学生号码。

不存在这样的课程y，学生201215122选修了y，而学生x没有选。

SELECT DISTINCT Sno
       FROM SC SCX
       WHERE NOT EXISTS
                     (SELECT *
                      FROM SC SCY
                      WHERE SCY.Sno = '201215122 '  AND
                                    NOT EXISTS
                                    (SELECT *
                                     FROM SC SCZ
                                     WHERE SCZ.Sno=SCX.Sno AND
                                                   SCZ.Cno=SCY.Cno));

集合查询

集合操作主要包括并操作UNION 交操作INTERSECT和差操作EXCEPT

查询计算机科学系的学生及年龄不大于19岁的学生

Select * from student
where sdept='cs' union 
select * from student
where sage<=19;

查询计算机科学系的学生与年龄不大于19岁的学生的交集

select * from student
where sdept='cs'
intersect 
select *
from student
where sage<=19;

查询既选修了课程1又选修了课程2 的学生

SELECT sno from sc
where CNO='1'
intersect
select sno 
from sc
where CNO='2';

查询计算机科学系的学生与年龄不大于19岁的学生的差集

SELECT *
FROM student 
where sdept='cs'
except
select *
from student
where sage<=19;

基于派生表的查询

子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句中，这时子查询生成的临时派生表成为主查询的查询对象。

Select sno,cno
from sc,(select sno,avg(grade) from sc group by sno)
 AS avg_sc(ave_sno,avg_grade)
where sc.sno=avg_sc.ave_sno and sc.grade>=avg_sc.avg_grade;

from子句的子查询将生成一个派生表AVG_sc,该表由avg_sno和avg_grade两个属性组成，记录每个学生的学号和平均成绩，主查询将sc表和avg_sc按照学号相同进行连接，并找出选修课成绩大于平时成绩的课程号。

如果子查询中没有聚集函数，派生表可以不指定属性列，子查询select子句后面的列明为其默认属性。

数据更新

数据更新分为三种：
向表中添加若干行数据
修改表中的数据
删除表中的若干行

插入数据

Insert
into student(sno,sname,ssex,sdept,sage)
values('201911248','蔡徐坤','男','CS','18');


INSERT
INTO student
values('201911824','布莱恩特','男','42','ba');

说了表名没有说列名 插入的数据就要按照表的属性列一一对应
如果说了列名，就按照列名去对应。

插入数据的时候要注意约束的存在
插入子查询
子查询不仅可以嵌套在SELECT语句中用以构造父查询的条件 也可以嵌套在INSERT语句中用以生成要插入的批量数据

INSERT
INTO dept_age(sdept,age)
select sdept,avg(sage)
from student 
group by sdept;

修改数据

UPDATE  <表名>
SET  <列名>=<表达式>[,<列名>=<表达式>]… 
[WHERE <条件>];

删除数据

DELETE FROM     <表名> 
[WHERE <条件>];

视图

视图与基本表不同，是从一个或几个基本表导出来的表，是虚表。

CREATE  VIEW 
             <视图名>  [(<列名>  [,<列名>]…)]
       AS  <子查询>
       [WITH  CHECK  OPTION];
/*WITH CHECK OPTION
对视图进行更新操作时自动添加子查询中的条件
建立信息系学生的视图。*/

 CREATE VIEW Kun2_Student
        AS 
        SELECT Sno,Sname,Sage
        FROM  Student
        WHERE  Sdept= 'IS'
        WITH CHECK OPTION;

视图不仅可以建立在一个基本表上，还可以建立在多个基本表上。
甚至也可以建立在视图上
也可以用聚集函数和Group by子句
删除视图

DROP  VIEW  <视图名>[CASCADE];

数据库安全性

授权：授予与收回

GRANT <权限>[,<权限>]... 
       ON <对象类型> <对象名>[,<对象类型> <对象名>]…
       TO <用户>[,<用户>]...
       [WITH GRANT OPTION];

with grant option 子句的作用是：
有with grant option的用户可以把权限继续授予下去
无该子句的用户则不能继续传递权限。
T-SQL

 GRANT   SELECT 
  ON   Student 
  TO   U1;

Grant all PRIVILIGES#所有权限
on Student,course
to u2,u3;

收回

REVOKE <权限>[,<权限>]... 
    ON <对象类型> <对象名>[,<对象类型><对象名>]…
    FROM <用户>[,<用户>]...[CASCADE | RESTRICT];

数据库角色

角色是权限的集合。可以为一组具有相同权限的用户创建一个角色。

CREATE  ROLE  <角色名> 

GRANT  <权限>[,<权限>]… 
 ON <对象类型>对象名  
 TO <角色>[,<角色>]…
将一个角色授予其他的角色或用户
GRANT  <角色1>[,<角色2>]…
TO  <角色3>[,<用户1>]… 
[WITH ADMIN OPTION]

指定了WITH ADMIN OPTION则获得某种权限的角色或用户还可以把这种权限授予其他角色

敏感度

对于主体和客体，DBMS为它们每个实例（值）指派一个敏感度标记：
绝密（Top Secret，TS）
机密（Secret，S）
可信（Confidential，C）
公开（Public，P）
TS>=S>=C>=P
强制存取控制规则
（1）仅当主体的许可证级别大于或等于客体的密级时，该主体才能读相应的客体
（2）仅当主体的许可证级别小于或等于客体的密级时，该主体才能写相应的客体

视图机制

视图机制是把要保密的数据对无权存取的用户隐藏起来，来对数据提供一定程度的安全保护：

CREATE VIEW CS_Student
    AS 
    SELECT  *
    FROM   Student
    WHERE  Sdept='CS';
 GRANT  SELECT
     ON  CS_Student  
     TO 王平;
     
GRANT ALL PRIVILIGES
     ON  CS_Student  
     TO  张明;
这里王平只有计算机专业的查看权限

审计

审计日志（Audit Log）：
将用户对数据库的所有操作记录在上面（很费空间）
AUDIT语句：设置审计功能
NOAUDIT语句：取消审计功能

完整性

实体完整性

create table student1
(

sno char(9) primary key,
sname char(20) not null,
ssex char(2),
sage smallint,
sdept char(20)
//列级定义主码

);
//或者
create table student1
(

sno char(9) 
sname char(20) not null,
ssex char(2),
sage smallint,
sdept char(20),
primary key(sno)
//表级定义主码
);

实例完整性检查和违约处理
用primary key短语定义了关系的主码后 每当用户对基本表插入一条记录或对主码列进行更新操作时
关系数据库会对实体完整性规则自动进行检查：

1.检查主码值是否唯一，如果不唯一则拒绝插入或修改
2.检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改
来保证实体完整性

参照完整性

关系模型的参照完整性在create table中用foreign key短语定义那些列为外码，用peferences短语指明这些外码参照哪些表的主码

create table sc3(

	sno char(9) not null,
	cno char(4) not null,
	grade smallint,
	primary key(sno,cno),
	foreign key(sno) references student1(sno),
	foreign key(cno) references student1(cno)
	);

被参照表（例如Student）	参照表(例如SC）	违约处理
可能破坏参照完整性	←插入元组	拒绝
可能破坏参照完整性	←修改外码值	拒绝
–	–	–
删除元祖->	可能破坏参照完整性	拒绝/级联删除/设置为空值
修改主码值	可能破坏参照完整性	拒绝/级联删除/设置为空值

参照完整性检查和违约处理
一般地 当对参照表和被参照表的操作违反参照完整性时，系统选用默认策略，即拒绝执行。如果想让系统采用其他策略则必须在创建参照表时显式的加以说明

用户定义完整性

在Create teble中定义属性的同时，可以根据应用要求定义属性上的约束条件 即属性值限制
包括

列值非空 not null

列值唯一 unique

检查列值是否满足一个条件表达式 check短语

不允许取空值
在定义sc表时，说明sno cno grade属性不能取空值

create table sc
( sno char(9) not null,
	cno char(4) not null,
	grade smallint not null,
	primary key(sno,cno)/* 在表级定义实体完整性，隐含sno cno不允许取空值，在列级不允许取空值的定义可以不写*/
	);


create table dept
(
	deptno numeric(2),
	dname char(9) unique not null,
	location char(10),
	primary key(deptno)

);

create table Student
(Sno char(9) primary key,
 Sname char(20) not null,
 Ssex char(2) check (Ssex in('男','女')), 
 Sage smallint,
 Sdept char(20)
);

完整性约束命名子句

constraint<完整性约束条件名><完整性约束条件>
完整性约束条件名包括：not null, unique, primary key, foreign key, check短语等

CREATE TABLE TEACHER
(Eno NUMERIC(4) PRIMARY KEY,
 Ename CHAR(10),
 Job CHAR(8),
 Sal NUMERIC(7,2),
 Deduct NUMERIC(7,2),
 Deptno NUMERIC(2),
 CONSTRAINT TEACHERFKey FOREIGN KEY (Deptno) 		  
 REFERENCES DEPT(Deptno),
CONSTRAINT C1 CHECK (Sal + Deduct >= 3000) 
)

使用ALTER TABLE语句修改表中的完整性限制

ALTER TABLE Student
	DROP CONSTRAINT C1;
ALTER TABLE Student
	ADD CONSTRAINT C1 CHECK (Sno BETWEEN 900000 AND 999999);
ALTER TABLE Student 
	DROP CONSTRAINT C3;
ALTER TABLE Student
ADD CONSTRAINT C3 CHECK(Sage<40);

断言

SQL中，可以使用 CREATE ASSERTION语句，通过声明性断言来指定更具一般性的约束。断言创建以后，任何对断言中所涉及的关系的操作都会触发DBMS对断言的检查，任何使断言不为真值的操作都会被拒绝执行

CREATE ASSERTION<断言名><CHECK 子句>
DROP ASSERTION <断言名>;

触发器

create trigger <触发器名>
{before|after} <触发事件> on <表名> --指明所在表，激活的时刻
referencing new|old row as <变量> --指出引用的变量
for each{row|statement} --触发器类型，行级和语句级
[when<触发条件>] <触发动作>

有一些需要注意的点
触发器只能定义在表上，不能定义在视图上
同一模式下，触发器名必须是唯一的，并且触发器名和表名必须在同一模式下

触发器类型
行级触发器（FOR EACH ROW）
语句级触发器（FOR EACH STATEMENT）
激活触发器
一个数据表上可能定义了多个触发器，遵循如下的执行顺序:
（1） 执行该表上的BEFORE触发器;
（2） 激活触发器的SQL语句;
（3） 执行该表上的AFTER触发器。
删除触发器

DROP TRIGGER <触发器名> ON <表名>;

存储过程和函数

CREATE OR REPLACE PROCEDURE 过程名([参数1,参数2,...]) AS <过程化SQL块>；

修改存储过程

 ALTER PROCEDURE 过程名1  RENAME TO 过程名2;

删除存储过程

 DROP  PROCEDURE 过程名()；

这里的详细内容我觉得还是应该多去看看之前做过的几道题之类的 才能透彻的理解。