关系数据库语言SQL学习笔记（大量代码版）

原创 已于 2023-06-11 14:45:35 修改 · 903 阅读 · 5 ·
本内容遵循CC 4.0 BY-SA版权协议
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
标签
#sql #学习 #笔记
于 2023-06-08 19:20:39 首次发布
SQL是用于管理关系数据库的标准语言，包括数据查询、定义、操纵和控制。它支持基本的查询操作，如SELECT，以及更复杂的子查询、连接查询、视图和索引。此外，SQL提供了数据更新功能，如INSERT、UPDATE和DELETE，以及安全性控制，如用户身份验证、权限控制和视图，确保数据库的安全。文章还介绍了如何通过CREATEVIEW和DROPVIEW来创建和删除视图，以及如何使用GRANT和REVOKE进行权限管理。

辛苦手码不易, 如若有帮助烦请不吝点赞 😃

1. 关系数据库标准语言SQL

Def: 结构化查询语言 (Structed Query Language) 是关系数据库的标准语言

Tips:

SQL于 1974年 由Boyce和Chamberlin提出, 最早叫Sequel, 并在IBM公司研制的RDBMS原型System R上实现

目前, 没有一个DBS能够支持SQL标准的所有概念和特性, 同时许多软件厂商对SQL基本命令集还进行了不同程度的扩充和修改, 以可以支持标准以外的一些功能特性

SQL的特点:

  1. 综合统一 SQL集数据定义语言, 数据操纵语言, 数据控制语言 的功能为一体, 可以独立完成数据库生命周期中的全部活动
  2. 高度非过程化 区别于非关系数据模型的数据操纵语言是"面向过程"的语言, SQL只需提出做什么, 无需关注怎么做, 故无需了解存储路径, 存储路径的选择和SQL的操作过程由系统自动完成
  3. 面向集合的操作方式 区别于非关系数据模型"面向记录"的操作方式, SQL采用集合操作方式, 操作对象, 查找结果, 一次插入, 更新, 删除的对象均可以是元组的集合
  4. 以同一种语法结构提供多种使用方式 SQL可独立使用, 也可嵌入到高级语言中(C++, java等)
  5. 语言简洁, 易学易用 SQL完成核心功能只用了 9 个动词, 如下表:
SQL功能动词
数据查询SELECT
数据定义(创, 删, 改)CREATE, DROP, ALTER
数据操纵(插入, 更新, 删除)INSERT, UPDATE, DELETE
数据控制GRANT, REVOKE

1.1 基本概念

1.支持SQL的关系数据库同样支持关系数据库的三级模式结构:

2.外模式, 包括若干视图和部分基本表

3.模式(即数据库模式), 包括若干基本表

4.内模式, 包括若干存储文件

基本表

def: 是本身独立存在的表

  1. 一个关系对应一个基本表

  2. 一个或多个基本表对应一个存储文件

  3. 一个表可以带若干索引, 索引也存放在存储文件中

存储文件

存储文件的逻辑结构组成了关系数据库的内模式

其物理结构是任意的, 对用户透明

视图

def: 从一个或几个基本表导出的表

  1. 视图本身不独立存储在数据库中, 数据库中只存放视图的定义而不存放视图对应的数据
  2. 数据仍存放在导出视图的基本表中, 因此视图是一个虚表
  3. 用户可以在视图上再定义视图

1.2 数据定义

SQL的数据定义功能: 模式定义, 表定义, 视图和索引的定义

SQL标准不提供修改模式定义和修改视图定义的操作, SQL标准也没有提供索引相关的语句, 这都是商用关系数据库提供的

模式的定义与删除

定义模式

CREATE SCHEMA <模式名> AUTHORIZATION <用户名> [<表定义语句>|<视图定义语句>|<授权定义语句>];

Instance:

为用户ZHANG创建一个模式TEST, 并且在其中定义一个表TAB1

CREATE SCHEMA TEST AUTHORIZATION ZHANG /*这里在创建模式的同时 创建了表*/
CREATE TABLE TAB1(COL1 TINYINT,
                 COL2 INT,
                 COL3 CHAR(20),
                 COL4 NUMERIC(10,3),
                 COL5 DECIMAL(5,2)
                 );

删除模式

DROP SCHEMA <模式名><CASCADE|RESTRICT>;

其中 CASCADE和RESTRICT 二者必选其一

选择了CASCADE(级联), 表示在删除模式的同时把该模式下的所有数据库对象全部删除

选择了RESRTICT(限制), 表示如果该模式中已经定义了下属的数据库对象( 如表,视图等) , 则拒绝该删除语句的执行, 只有该模式中没有任何下属的对象时, 才能执行DROP SCHEMA 语句

Instance:

DROP SCHEMA ZHANG CASCADE;

该语句删除了模式 ZHANG, 同时该模式中已经定义的表 TAB1 也被删除了.

基本表的定义, 删除, 和修改

创建了一个模式, 就建立了一个数据库的命名空间, 在这个空间中首先要定义的, 是该模式包含的数据库基本表

定义基本表

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

建立表的同时, 通常还可以定义与该表有关的完整性约束条件, 这些完整性约束条件被存入系统的数据字典中, 用户操作表中数据时, 系统会自动检查该操作是否违反了这些完整性约束条件. **如果完整性约束条件涉及表的多个属性列, 则必须定义在表级上 **

Instance:

/*建立一个学生表Student*/
CREATE TABLE Student(
	Sno CHAR(9) PRIMARY KEY, /*列级完整性约束条件, Sno是主码*/
	Sname CHAR(20) UNIQUE, /*Sname取唯一值*/
	Ssex CHAR(2),
	Sage SMALLINT,
	Sdept CHAR(20)
);

系统执行该语句后, 就在数据库中建立一个新的 空 学生表 Student, 并将有关该表的定义和约束条件存放在数据字典中

Instance:

/*建立一个课程表Course*/
CREATE TABLE Course(
	Cno CHAR(4) PRIMARY KEY,/*列级完整性约束条件, Cno是主码*/
	Cname CHAR(40) NOT NULL, /*列级完整性约束条件, Cname不能取空值*/
	Cpno cHAR(4), /*Cpno 为先修课*/
	Ccredit TINYINT,
	FOREIGN KEY (Cpno) REFERENCES Course(Cno)
		/*表级完整性约束条件, Cpno是外码, 被参照表是Course ,被参照列是 Cno*/
);/*本例说明 参照表和被参照表可以是 同一个表*/

Instance:

/*建立学生选课表SC*/
CREATE TABLE SC(
	Sno CHAR (9),
	Cno CHAR (4),
	Grade TINYINT,
	PRIMARY KEY (Sno,Cno),/*主码由两个属性构成, 必须作为表级完整性进行定义*/
	FOREIGN KEY (Sno) REFERENCES Student(Sno),
		/*表级完整性约束, Sno是外码,被参照表是Student*/
	FOREIGN KEY (Cno) REFERENCES Course(Cno)
		/*表级完整性约束, Cno是外码, 被参照表是Course*/);
数据类型

详见P83-84

主要掌握:

数据类型含义
CHAR(n)长为n的定长字符串
VARCHAR(n)长为n的可变长字符串
CLOB字符串最大对象
BLOB二进制最大对象
INT长整数 4字节
SMALLINT/ TINYINT短整数 2字节
BIGINT大整数 8字节
NUMERIC(p,d)定点数, p位数字+小数点后d位数字
FLOAT(n)精度至少为n位的浮点数
BOOLEAN逻辑布尔量
DATE日期 格式为YYYY-MM-DD
TIME时间 格式为HH:MM:SS
模式与表

每个基本表都属于某一个模式, 一个模式包含多个基本表

定义基本表时, 有三种方法定义其所属的模式:

for instance: 定义了一个学生-课程模式’S-T’, 现在要在S-T中定义 Student, Course, SC 等基本表

  1. 法一

在表名中明显地给出模式名:

CREATE TABLE "S-T".Student(...); /*Student表所属的模式是S-T*/
CREATE TABLE "S-T".Course(...); /*Course表所属的模式是S-T*/
CREATE TABLE "S-T".SC(...); /*SC表所属的模式是S-T*/
  1. 法二

在创建模式的语句中同时创建表:

CREATE SCHEMA TEST AUTHORIZATION ZHANG /*这里在创建模式的同时 创建了表*/
CREATE TABLE TAB1(COL2 SMALLINT,
                 COL2 INT,
                 COL3 CHAR(20),
                 COL4 NUMERIC(10,3),
                 COL5 DECIMAL(5,2)
                 );
  1. 法三

设置所属的模式, 这样在创建表时表名中不必给出模式名

当用户创建基本表(其他数据对象也同样)时, 如没有指定其所属模式, 系统根据搜索路径( search path ) 来确定该对象所属的模式

搜索路径包含一组模式列表, DBMS会使用模式列表中第一个存在的模式作为数据库对象的模式名. 若路径中模式名都不存在, 则系统给出错误.

SHOW search_path; /* 显示当前的搜索路径*/

其显示结果将是(默认值):

$user, PUBLIC /*其含义是 首先搜索与用户名相同的模式名(user是用户名),若不存在,则使用PUBLIC模式

数据库管理员(DA)可以设置搜索路径:

SET search_path TO "S-T", PUBLIC;

然后定义基本表:

CREATE TABLE Student (...);

这时就建立了S-T.Student基本表, 因为RDBMS发现搜索路径中第一个模式名S-T存在,就将其作为Student表所属的模式

修改基本表

SQL语言用ALTER TABLE语句修改基本表:

ALTER TABLE <表名>
[ADD [COLUMN] <新列名><数据类型> [完整性约束]]/* 增加新的一列*/
[ADD <表级完整性约束>] /*增加表级完整性约束*/
[DROP [COLUMN]<列名> [CASCADE|RESTRICT]] 
/*CASCADE,比如删除Student中的Sno列,则SC表中的Sno也被删除*/
/*RESTRICT,若该列被其它对象引用, 则RDBMS拒绝删除该列*/
[DROP CONSTRAINT<完整性约束名> [RESTRICT|CASCADE]]/* 删除指定的约束条件*/
[ALTER COLUMN<列名><数据类型>];/* 用于修改原有列的定义*/

Instances:

/*向Student表中增加'入学时间'列*/
ALTER TABLE Student
ADD S_entrance DATE;
/*不论基本表中原来是否有数据,新增加的列一律空值*/ /*所以 建表时就确定好主码 后面再改很麻烦*/
/*将'年龄'数据类型由字符型改为整数*/
ALTER TABLLE Student 
ALTER COLUMN Sage INT;
/*增加课程名称必须取唯一值的约束条件*/
ALTER TABLE Course 
ADD uq_Cname UNIQUE(Cname);/* uq_Cname为约束名
删除基本表
DROP TABLE <表名> [RESTRICT|CASCADE];
/*若选择RESTRICT, 则欲删除的基本表不能被其他表的约束所引用, 如(CHECK, FOREIGN KEY等), 不能有视图,
不能有触发器(trigger), 不能有存储过程或函数等.*/
/*若选择CASCADE, 则该表的删除没有约束条件, 在删除基本表的同时, 相关的依赖对象, 如视图等, 都将被一起删除*/

Instance:

/*删除 Student表*/
DROP TABLE Stundet CASCADE;

基本表定义一旦被删除, 不仅表中的数据和此表的定义被删除, 而且此表上建立的索引, 触发器等对象一般也都将被删除

有的RDBMS还会同时删除在此表上建立的视图

如果欲删除的基本表被其他表所引用, 则这些表也可能被删除

Instance:

CREATE VIEW IS_Student /*在Student表上建立视图*/
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept='IS';

DROP TABLE Student RESTRICT; /*删除Student表*/
--ERROR: cannot drop table Student because other objects depend on it
/* 系统返回错误信息, 存在依赖该表的对象, 此表不能被删除*/
DROP TABLE Student CASCADE; /*删除Student表*/
--NOTICE: drop cascades to view IS_Student
/* 系统返回提示, 此表上的视图也被删除*/

SELECT * FROM IS_Student;
--ERROR: relation "IS_ Student" does not exist
索引的建立与删除

索引文件: 属性值和相应的元组指针

建立索引的目的: 加快查询速度

谁可以建立和删除索引: DA 或表的属主(即建表人)

RDBMS会自动选择合适的索引作为存储路径, 用户不必也不能显式地选择索引

DBMS一般会自动建立 PRIMARY KEY, UNIQUE 列上的索引(只有这两个列是DBMS自己建立索引)

DBMS自动完成索引的维护

DBMS自动选择是否使用索引和使用哪些索引

索引是RDBMS的内部实现技术, 属于内模式的范畴

RDBMS中索引一般采取B+树, HASH(散列索引)索引来实现

B+树索引具有动态平衡的优点, HASH索引具有查找速度快的优点, 采用哪种由具体的RDBMS来决定

CREATE INDEX 语句定义索引时, 可以定义索引是唯一索引, 非唯一索引或聚簇索引

  1. 建立索引

格式:

CREATE [UNIQUE][CLUSTER] INDEX <索引名>
/* UNIQUE 唯一索引 CLUSTER聚簇索引 两个都不写则是非唯一索引*/
ON <表名>(<列名> [<次序>][, <列名>[<次序>]]...);/*在哪个表的哪一列建立索引, 列可以排序*/

说明:

  1. UNIQUE 表明此索引每一个索引值只对应唯一的数据

Instance:

CREATE UNIQUE INDEX Stusno ON Student(Sno);/*Student表按学号升序建立唯一索引*/
CREATE UNIQUE INDEX Coucno ON Course(Cno);/*Course表按课程号升序建立唯一索引*/
CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC);/*默认升序ASC*/
/*SC表按学号升序和课程号降序建立唯一索引*/

解释一下,比如对于SC表的索引:

将这个索引理解为一张表:

学号(升序)课程号(降序)存放位置的序号
0017123
0015458
0014248
  1. CLUSTER表示要建立的索引是聚簇索引. 指索引顺序与表中记录的物理顺序一致的索引组织（是所有索引中查询速度最快的）

在最经常查询的列上，建立聚簇索引以提高查询效率

在一个基本表上最多只能建立一个聚簇索引

经常更新的列不宜建立聚簇索引

  1. 修改索引

修改索引主要是重命名:

ALTER INDEX <旧索引名> RENAME TO <新索引名>;
  1. 删除索引
DROP INDEX <索引名>;

Instance:

/* 删除Student表的Stuname索引*/
DROP INDEX Stusname;

删除索引时, 系统会同时从数据字典中删除有关该索引的描述

数据字典
  1. 数据字典是RDBMS内部的一组系统表
  2. 数据字典记录了数据库中所有的定义信息, 包括模式定义, 视图定义, 索引定义, 完整性约束定义, 各类用户对数据库的操作权限, 统计信息等
  3. RDBMS执行SQL数据定义时(查询不算,查询并没有对数据库信息产生变化, 主要是指定义, 删, 改), 实际就是更新数据字典

1.3 数据查询

SELECT语句进行数据查询操作, 其一般格式如下:

SELECT [ALL|DISTINCT] <目标列表达式> [,<目标列表达式>].../*指定要显示的属性列*/
FROM <表名或视图名> [,<表名或视图名>]|(<SELECT语句>)[AS]<别名>/*指定查询对象 表/视图*/
[WHERE <条件表达式>]/*指定查询条件*/
[GROUP BY <列名1> [HAVING <条件表达式>]]/*对查询结果分组聚集,HAVING:满足条件组才输出*/
[ORDER BY <列名2> [ASC|DESC]];/*升降序排列
单表查询

功能: 对一个表的内容进行查询

1. 选择表中的若干列

def: 选择表中的全部或部分列, 相当于关系代数中的 投影

Instance:

SELECT Sno,Sname FROM Student;/* 查询Student表中全体学生的学号和姓名

查询各列的顺序与表中的顺序可以不一致

查询全部列

法一: 在SELECE关键字后列出所有列名

法二: 如果列的显示顺序与其在表中的顺序相同, 也可简单地将目标列表达式替换为 *

Instance:

SELECT Sno, Sname, XX, XX, XX FROM Student;
/*等价于*/
SELECT * FROM Student;

查询经过计算的值

格式: SELECT后的目标列表达式可以替换为: 算数表达式/字符串常量/函数/列别名

Instance:

/*查询全体学生的姓名机器出生年份*/
SELECT Sname,2014-Sage FROM Student;/* 2014-age 即作为输出列的列名*/
/*查询全体学生的姓名, 出生年份和所在院系, 要求用小写字母表示系名*/
SELECT Sname,'Year of Birth:',2014-Sage,LOWER(Sdept)
FROM Student;/*效果如下*/
Sname‘Year of Birth:’2014-SageLOWER(Sdept)
李勇Year of Birth:1994cs

明显2014-Sage看的不爽, 故还可以给列起别名:

Instance:

SELECT Sname,'Year of Birth:',2014-Sage BIRTHYEAR,LOWER(Sdept) DEPARTMENT
FROM Student;/*效果如下*/
Sname‘Year of Birth:’BIRTHYEARDEPARTMENT
李勇Year of Birth:1994cs
2.选择表中的若干元组

1. 消除取值重复的行

Instance:

SELECT DISTINCT Sno FROM SC;/* 去掉了结果中的重复行*/
不去掉的效果去掉的效果
SnoSno
20202142662020214266
20202142662020214259
2020214259

若未加入DICTINCT, 则默认为ALL

2. 查询满足条件的元组

查询满足指定条件的元组用WHERE语句实现, 常用的查询条件如下表:

查询条件谓词
比较=,>,<,>=,<=,!=,<>,!>,!<;NOT+上述比较符号
确定范围BETWEEN AND, NOT BETWEEN AND
确定集合IN, NOT IN
字符匹配LIKE, NOT LIKE
空值IS NULL, IS NOT NULL
多重条件(逻辑运算)AND, OR ,NOT

Instance:

/*查询计算机科学系全体学生名单*/
SELECT Sname FROM Student WHERE Sdept='CS';/*WHERE语句后为查询条件*/

查询的分析方式:

三段论: 查谁? SELECT后跟谁 从哪? FROM后跟哪个表 查询条件? WHERE后跟条件

Instance(比较):

/*查询所有年龄在20岁以下的学生姓名及其年龄*/
SELECT Sname,Sage FROM Student WHERE Sage<20;
/*查询考试成绩有不及格的学生的学号*/
SELECT DISTINCT Sno FROM SC WHERE Grade<60;/*注意避免重复*/

试想执行操作时, 数据库系统的过程: 扫描全表->取出一个元组->某列是否满足条件->满足则依照要求输出

For instance, 扫描全表, 取出一个元组, 查看其Sdept列是否取值为’CS’, 若是则输出

不妨在Student表上的Sdept上建立索引, 则再次查找时, 系统将不会再扫描全表, 而是依照索引, 先行找出全部Sdept='CS’的元组, 在从中取出Sname列值形成结果关系, 这就避免了全表扫描, 从而大大加快了查询速度

Instance(BETWEEN)

/*查询所有年龄在20岁-23岁的学生姓名及年龄*/
SELECT Sname,Sage FROM Student WHERE Sage BETWEEN 20 AND 23;/* 前低后高*/

/*查询年龄不在20-23岁的学生姓名, 系别, 年龄*/
SELECT Sname,Sdept,Sage FROM Student WHERE Sage NOT BETWENNT 20 AND 23;

Instance(IN 集合)

/*查询CS系, MA系, 和IS系学生的姓名和性别*/
SELECT Sname,Ssex FROM Student WHERE Sdept IN ('CS','MA','IS');
/*查询既不是******/
SELECT Sname,Ssex FROM Student WHERE Sdept NOT IN ('CS','MA','IS');

Instance(LIKE 字符匹配)

WHERE <属性名> [NOT] LIKE "<匹配串>" [ESCAPE '换码字符']

匹配串可以是一个完整的字符串,也可以含有通配符 % _

**%**代表任意长度的字符串(可为0), 例如a%b表示所有以a开头b结尾的字符串

_ 代表任意单个字符

/*查询学号为2020214266的学生的详细情况*/
SELECT * FROM Student WHERE Sno LIKE '2020214266';
/*等价于*/
SELECT * FROM Student WHERE Sno='2020214266';
/*即 如果LIKE后不含通配符'_''%', 则可以用=来代替LIKE 或者用!= <>来代替NOT LIKE

/*查询所有姓刘的学生的姓名, 学号,性别*/
SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE '刘%';

/*查询姓 欧阳 且全名为三个字的学生的姓名*/
SELECT Sname FROM Student WHERE Sname LIKE '欧阳_';

数据库字符集为ASCII编码时, 一个汉字需要两个_ , 为GBK编码时, 一个汉字需要一个_

如果用户要查询的字符串本身含有通配符, 则需要用ESCAPE '<换码字符>'对通配符进行转义

Instance:

/*查询 DB_Design 课程的课程号和学分*/
SELECT Cno,Ccredit FROM Course WHERE Cname LIKE 'DB/_Design' ESCAPE'/;
/*ESCAPE'/'表示'/'为换码字符, 匹配串中紧跟在'/'后的'_'不再具有通配符的含义,而是转义为普通字符*/

/*查询以 DB_ 开头， 且倒数第三个字符为i 的课程的详细情况*/
SELECT * FROM Course WHERE Cname LIKE 'DB/_%i__' ESCAPE '/';

Instance(NULL涉及空值的查询）

/*查询缺少成绩的学生的学号和相应的课程号*/
SELECT Sno,Cno FROM SC WHERE Grade IS NULL;/*这里的IS 不能用=代替*/

Instance(多重条件查询)

逻辑运算符AND OR可用来连接多个查询条件, AND的优先级高于OR, 且可以用()改变优先级

/*查询CS系中学生年龄在20岁以下的学生姓名*/
SELECT Sname FROM Student WHERE Sage<20 AND Sdept='CS';
3. ORDER BY子句(排序)

可以使用ORDER BY语句对查询结果按照一个或多个属性列的升序 ASC或降序 DESC排列, 默认值为升序

Instance

/*查询选修了三号课程的学生的学号及其成绩 查询结果按分数的降序排列*/
SELECT Sno,Grade FROM SC WHERE Cno='3' ORDER BY Grade DESC;
/*对于空值的排布,依照具体系统实现来决定*/

/*查询全体学生情况, 查询结果按所在系的系号升序排列, 同一系中的学生按照年龄降序排列*/
SELECT * FROM Student ORDER BY Sdept,Sage DESC;
4. 聚集函数

为了进一步方便用户, 增强检索功能, SQL提供了许多聚集函数,主要有:

函数功能
COUNT(*)统计元组个数
COUNT([DISTINCT|ALL] <列名>)计算一列中值的个数
SUM([DISTINCT|ALL] <列名>)计算一列值的总和(此列必须为数值型)
AVG([DISTINCT|ALL] <列名>)计算一列值的平均值(此列必须为数值型)
MAX([DISTINCT|ALL] <列名>)求一列值中的最大值
MIN([DISTINCT|ALL] <列名>)求一列值中的最小值

如果指定DISTINCT短语, 则表示计算时要取消指定列中的重复值, 默认为ALL

Instance:

/*查询学生总人数*/
SELECT COUNT(*) FROM Student;

/*查询选修了课程的学生人数*/
SELECT COUNT(DISTINCT Sno) FROM SC;

/*计算选修1号课程的学生平均成绩*/
SELECT AVG(Grade) FROM SC WHERE Cno='1';

/*查询选秀1号课程的学生的最高分数*/
SELECT MAX(Grade) FROM SC WHERE Cno='1';

/*查询学生2020214266选修课程的总学分数*/
SELECT SUM(Ccredit) FROM SC,Course
WHERE Sno='2020214266' AND SC.Cno=Course.Cno;/*这里相当于SC表和Course表做了一个等值连接

WHERE函数中 不能使用聚集函数作为条件表达式, 聚集函数只能作用于SELECT子句和GROUP BY 中的HAVING子句

WHERE AVG(Grade)>90 /*是不被允许的*/
5. GROUP BY语句(分组语句)

GROUP BY子句将查询结果按某一列或多列的值分组, 值相等的为一组

说明

  1. 目的: 对查询结果分组的目的是为了细化聚集函数的作用对象
  2. 如果未对查询结果进行分组, 聚集函数将作用于整个查询结果(最大值\平均值的作用是对象是整个查询结果)
  3. 分组后, 聚集函数将作用于每一个组, 即每一个组都有一个函数值

Instance:

/* 求各个课程号及相应的选课人数*/
/*思路 求每个课程号的对应课程的人数*/
SELECT Cno, COUNT(Sno) FROM SC GROUP BY Cno;

!! 注意 !! 使用GROUP BY子句后, SELECT语句后只能出现两类数据, 即GROUP BY的对象列 和 聚集函数, 其他的数据对象将报错

执行结果:

CnoCOUNT(Sno)
122
233

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

Instance:

/*查询选修了三门以上课程的学生学号*/
SELECT Sno FROM SC GROUP BY Sno
HAVING COUNT(*)>3;

这里先用GROUP BY子句按照Sno进行分组, 再用聚集函数COUNT对每一组计数, HAVING短语给出了筛选的条件, 只有满足条件的组才会被选出来

WHERE子句与HAVING短语的区别是 作用对象不同, WHERE子句作用于基本表或视图, 从中选择满足条件的元组, 而HAVING短语作用于组(GROUP), 从中选择满足条件的组

Instance:

/*查询平均成绩>或>=90分的学生学号和成绩*/
/*正确示例*/
SELECT Sname,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=90;
/*错误示例*/
SELECT Sname,AVG(Grade)
FROM SC
WHERE AVG(Grade)>=90/* WHERE子句不能用 聚集函数 作为条件表达式*/
GROUP BY Sno;
连接查询 —查询涉及到两个表或多个表

连接查询是关系数据库中 最主要的 查询

#####1.等值与非等值查询

连接查询的WHERE子句中, 用来连接两个表的条件称为 连接条件 或 连接谓词 其一般格式为:

/*格式一:*/
[<表名1>.] <列名1><比较运算符> [<表名2>.] <列名2>
/*比较运算符主要有 = > < <= >= != <>
/*格式二:*/
[<表名1>.] <列名1> BETWEEN [<表名2>.] <列名2> AND [<表名2>.] <列名3>

/*当连接运算符为 = 时, 称为 等值连接, 使用其他运算符均为 非等值连接*/

连接运算符中的列名称为 连接字段

连接条件中的 各连接字段必须是可比的 但名字不必相同

Instance:

/*查询每个学生及其选修课程的情况*/
SELECT Student.*,SC.*
FROM Student,SC
WHERE Student.Sno=SC.Sno; /*即 把Student表和SC表中同一学生的元组连接起来*/

查询结果如下表：

Student.SnoSnameSsexSageSdeptSC.SnoCnoGrade
2020214266ZGK男20CS20202142660408
2020214266ZGK男20CS20202142661855
2020214259WB男21CS20202142590408

!! 注意 !! 这张大表在数据库中并不实际存在, 只是在查询运算的时候生成这一张虚表, 运算结束后即删除

连接操作的一种执行方法: 嵌套循环思想

a. 首先在表Student中找到第一个元组, 然后从头开始扫描表SC, 逐一查找与Student第一个元组的Sno相等的SC的元组(满足WHERE条件), 找到后就将Student表的第一个元组与该元组连接起来, 形成结果表中的一个元组

b. SC表全部查找完毕后, 再找Student表中的第二个元组, 然后再从头扫描SC, 逐一查找满足条件的元组,并连接,形成结果表中的第一个元组

c. 重复上述操作, 直到Student表中的全部元组都处理完毕为止

显然, 如果在SC表的Sno上建立索引的话, 就不用每次都全盘扫描SC表了, 而是根据Sno的值通过索引找到相应的SC元组. 用索引查询SC中满足条件的元组一般会比全盘扫描快.

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

Instance(自然连接)

/*对上例用自然连接完成*/
/*告诉SELECT 要显示哪些列即可*/
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade /*即 只显示Student表的Sno*/
/*要注意的是, 其他属性由于在Student表与SC表中均是唯一的, 故不需加表名前缀*/
/* 当然 为了程序的方便阅读, 建议都加上表名前缀*/
FROM Student,SC
WHERE Student.Sno=SC.Sno;

一条SQL语句可以同时完成选择查询和连接查询, 这时WHERE子句是由连接谓词和选择谓词所组成的复合条件

Instance:

/*查询选修2号课程且成绩在90分以上的所有学生的学号和姓名*/
SELECT Sno,Sname FROM Student,SC
WHERE Student.Sno=SC.Sno AND/*连接谓词*/
SC.Cno='2' AND SC.Grade>90; /*其他限定条件 AND连接*/
2. 自身连接

一个表自己可以与自己相连接------出现问题: 那怎么区分哪个列对应哪个表呢?

说明:

  1. 需要给表起别名以示区别
  2. 由于所有属性名均是同名属性, 故列前需要加上别名前缀

Instance:

/*查询每一门课的间接先修课(即 先修课的先修课)
/* 分析 在Course表中只有一门课的先修课, 即直接先修课信息, 故要得到间接先修课信息, 必须先找到此门课的先修课, 在查询此先修课的先修课, 这就需要将Course表与其自身连接*/
SELECT FIRST.Cno,SECOND.Cpno  /*显示 |一门课|间接先修课| */
FROM Course FIRST,Course SECOND /*在FROM中定义别名*/
WHERE FIRST.Cpno=SECOND.Cno;    /*自身连接形成大表*/
3. 外连接

普通连接与外连接的区别:

普通连接操作只输出满足连接条件的元组

外连接操作以指定表为连接主体, 将主体表中不满足连接条件的元组一并输出 (参考关系代数)

外连接分为:

/*左外连接: 列出左边关系中所有的元组*/
LEFT OUTER JOIN SC ON
/*右外连接: 列出右边关系中所有的元组*/
RIGHT OUTER JOIN SC ON

For Instance:

/* 查询每个学生 及其选修课程的情况*/
/* 这里就需要显示出NULL, 这表明该学生没有选修这门课*/
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno);
/*这里 Student是主体(左), 它们存在于Student表中,但他们还没选课,故在SC表中没有他们的信息 输出结果参照P102*/
/*也可以使用USING来去掉结果中的重复值*/
FROM Student LEFT OUTER JOIN SC USING(Sno);
4. 多表连接

Namely 将两个以上的表连接

Instance:

/*查询每个学生的学号,姓名,选修的课程名和成绩*/
SELECT Student.Sno,Student.Sname,Course.Cname,SC.Grade
FROM Student,SC,Course 
WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;

RDB在执行多表连接时, 通常是先进行两个表的连接操作, 再将其连接结果与第三个表进行连接

比如本例: 先将Student表跟SC表进行连接, 得到每个学生的学号, 姓名,所选课程号和相应的成绩, 然后再将其与Course表进行连接, 最终得到结果

嵌套查询

一个SELECT-FROM-WHERE语句称为一个 查询块

嵌套查询def: 是指将一个查询块**(子查询)嵌套在另一个查询块(父查询)**的WHERE子句或HAVING短语条件中的查询

States:

  1. 子查询中不能使用ORDER BY子句 Namely子查询中不能排序
  2. 层层嵌套方式反映了SQL语言的 结构化 的特征
  3. 有些嵌套查询可以用连接运算代替
  4. 外层查询(父查询) 内层查询(子查询)

Instance:

/*查询选修了2号课程的学生姓名*/
SELECT Sname  /*外层查询或父查询*/
FROM Student
WHERE Sno IN
		(SELECT Sno /*内层查询或子查询*/
        FROM SC
        WHERE Cno='2')
1. 带有IN谓词的子查询

在嵌套查询中, 子查询的结果往往是个集合, 用IN谓词来表示父查询的条件在子查询结果的集合中

For Instance:

/* 查询与"刘晨"在同一个系中学习的学生*/
/*方法一 分布查询 namely先查询"刘晨"所在系名, 再查询该系的所有学生*/
SELECT Sdept FROM Student WHERE Sname='刘晨'; 
/*假设得到结果为CS*/
SELECT * FROM Student WHERE Sdept='CS';/*得出结果*/

/*方法二 嵌套查询 将分步查询第一步的查询嵌套到第二步查询的条件中*/
SELECT * FROM Student
WHERE Sdept IN
	(SELECT Sdept FROM Student WHERE Sname='刘晨');/*IN()表示 子查询的结果 是个集合
	
/*方法三 使用自身连接来实现*/
SELECT FIRST.* FROM Student FIRST,Student SECOND
WHERE FIRST.Sdept=SECOND.Sdept AND SECOND.Sname='刘晨';

此例中, 子查询的查询条件不依赖于父查询, 称为 不相关子查询

一种求解方法是, 由里向外处理, 即先执行子查询, 子查询的结果作用于父查询的查询条件

由此查询实现的多种方法可见: 实现同一个查询请求可以有多种方法

不同方法的执行效率有差别—> 数据库性能调优技术

Instance:

Recipe: 查询文字描述在前的是小辈, 在嵌套的最里面 代码实现思路要从里往外剥

/*查询选修了课程名为"信息系统"的学生学号和姓名*/
SELECT Sno,Sname FROM Student/* 3. 通过返回的Sno, 在Student表中找到学生学号和姓名*/
WHERE Sno IN
	(SELECT Sno FROM SC /*2. 通过返回的Cno, 在SC表中找到该Cno对应的Sno*/
    WHERE Cno IN
    	(SELECT Cno FROM Course/* 1. 先在Course表中找出满足Cname='信息系统'的Cno*/
        WHERE Cname='信息系统'));
        
/* 本例同样可以用连接查询来实现*/
SELECT Student.Sno,Student.Sname
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno AND Course.Cname='信息系统';

Tips: 有些嵌套查询可以用连接查询代替, 有些则不能

商用数据库的连接查询不够完善, 能用连接查询尽量使用连接查询

States:

以上两例均为不相关子查询, namely 子查询的查询条件不依赖于父查询

如果 子查询的查询条件依赖于父查询 , 则这类子查询称为 相关子查询, 整个查询语句被称为 嵌套查询

2. 带有比较运算符的子查询

当能确切地知道内层查询返回 单值 时, 可用比较运算符代替 IN

Instance:

/* 查询与"刘晨"在一个系的学生*/
SELECT * 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);/* 这里 子查询需要调用父查询的信息 故SC都起了别名*/
/*此例为 相关子查询*/
3. 带有ANY(SOME)或ALL谓词的子查询

子查询返回 单值 时可以用比较运算符, 但返回多值时要用 ANY(有的系统用SOME) 或 ALL 谓词修饰符, 同时伴以比较运算符

符号语义
>ANY大于子查询结果中的某个值
>ALL大于~的所有值
<ANY小于~
<ALL小于~
>=ANY~
>=ALL~
<=ANY~
<=ALL~
=ANY等于~的某个值
=ALL等于~所有值(通常无意义)
!=或<>ANY不等于~某个值
!=或<>ALL不等于~中的任何一个值

Instance:

/*查询非CS系中比CS系任意一个学生年龄小的学生姓名和年龄*/
SELECT Sname,Sage
FROM Student WHERE Sage<ANY
	(SELECT Sage FROM Student WHERE Sdept='CS')
AND Sdept!='CS'; /*这是父查询的条件*/

用聚集函数实现上例:

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

Instance:

/*查询非CS系中比CS系所有年龄都小的学生姓名和年龄*/
SELECT Sname,Sage
FROM Student WHERE Sage<ALL
	(SELECT Sage FROM Student WHERE Sdept='CS')
AND Sdept!='CS';

同样给出聚集函数的实现:

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

事实上, 使用聚集函数进行查询的效率通常比直接使用ANY或ALL要高, 其对应表详见 P109

4. 带EXISTS谓词的子查询

EXISTS代表存在量词"反E"

带有 EXISTS 谓词的子查询不返回任何数据,只产生逻辑 true 或 false

可以用EXISTS来判断 S=R 等等 是否成立

Instance:

/*查询所有选修了1号课程的学生姓名*/
SELECT Sname FROM Student WHERE Student.Sno IN
	(SELECT SC.Sno FROM SC WHERE SC.Cno='1');
	
/*用EXISTS*/
SELECT Sname FROM Student
WHERE EXISTS
	(SELECT * FROM SC WHERE Sno=Student.Sno AND Cno='1');

简述EXISTS方法的实现过程:

将Student表中的每一个Sname-Sno带入到最内层的查询, 当满足最内层条件时, 返回真, namely输出Sname, 若不符合, 则返回假, namely不输出

**TIPS: ** 由EXISTS引出的子查询, 其目标列表达式通常用 ***** , 因为EXISTS只返回真假, 带目标列名其实无意义

同理, 若使用NOT EXISTS, 则若满足(条件), 返回假, 不输出, 不满足时返回真, 输出

Instance:

/*查询所有没有选修1号课程的学生姓名*/
SELECT Sname FROM Student
WHERE NOT EXISTS
	(SELECT * FROM SC WHERE Sno=Student.Sno AND Cno='1');

TIPS:

  1. 有一些带EXIST或NOT EXIST谓词的子查询不能被其他形式的子查询等价代换
  2. 但所有带IN谓词, 比较运算符, ANY 和ALL 为此的子查询,都能用EXIST或 NOTEXIST替换

Instance:

/*查询与"刘晨"在同一个系学习的学生*/
SELECT Sno,Sname FROM Student
WHERE Sdept=(SELECT Sdept FROM Student 
            WHERE Sname='刘晨');
            
/*用EXIST*/
SELECT Sno,Sname,Sdept
FROM Student S1
WHERE EXISTS
	(SELECT * FROM Student S2
    WHERE S2.Sdept=S1.Sdept AND
    S2.Sname='刘晨');
集合查询

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

参加集合操作的各查询结果的列数必须相同; 对应项的数据类型也应相同

/*查询CS系的而学生及年龄不大于19岁的学生*/
/*法一*/
SELECT * FROM Student WHERE Sdept='CS'
UNION SELECT * FROM Student WHERE Sage<=19;
/*UNION: 将多个查询结果合并起来, 系统自动去掉重复元组
  UNION ALL: 将多个查询结果合并起来时, 保留重复元组*/
/*法二*/
SELECT DISTINCT * FROM Student WHERE Sdept='CS'OR Sage<=19;

/*查询CS系学生与年龄不大于19岁学生的交集*/
SELECT * FROM Student WHERE Sdept='CS'
INTERSECT 
SELECT * FROM Student WHERE Sage<=19;
/*其实相当于查询CS系中年龄不不大于19岁的学生*/
SELECT * FROM Student WHERE
Sdept='CS' AND Sage<=19;
/*查询CS系的学生与年龄不大于19岁的学生的差集*/
SELECT * FROM Student WHERE Sdept='CS'
EXCEPT 
SELECT * FROM Student WHERE Sage<=19;
基于派生表的查询

**子查询不仅可以出现在WHERE语句中, 还可以出现在FROM子句中, **

这时子查询生成的临时 派生表 成为主查询的查询对象

Instance:

/*找出每个学生超过他自己选修课程的平均成绩的课程号*/
SELECT Sno,Cno FROM SC x
WHERE Grade>=(SELECT AVG(Grade)
                FROM SC y
                WHERE y.Sno=x.Sno);
/*基于派生表法:*/
SELECT Sno,Cno
FROM SC,(SELECT Sno, Avg(Grade) FROM SC GROUP BY Sno)
		AS Avg_sc(avg_sno,avg_grade)/*将派生的表起一个别名用AS, 表中属性也起新的名字*/
WHERE SC.Sno = Avg_sc.avg_sno and SC.Grade >= Avg_sc.avg_grade;

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

Instance:

/*查询所有选修了1号课程的学生姓名*/
SELECT Sname FROM Student,(SELECT Sno FROM SC WHERE Cno ='1') AS SC1
WHERE Student.Sno=SC1.Cno;
/*注意: 通过FROM子句生成派生表时, AS关键字可以省略,但必须为派生关系指定一个别名*/

1.4 数据更新

def: 数据更新的操作有三种: 向表中添加若干行数据, 修改表中的数据, 删除表中的若干行数据

相应地, 在SQL中有三种对应语句 插入语句 INSERT 修改语句 UPDATE 删除语句DELETE

插入数据
1. 插入元组

格式:

INSERT INTO <表名>,[(<属性列1>[,<属性列2>]...)]
VALUES (<常量1> [,<常量2>]...)

Instance:

/*将一个新学生元组插入到Student表中*/
INSERT INTO Student (Sno,Sname,Ssex,Sdept,Sage)
VALUES('2020214266','ZGK','男','IS',18);

要注意的是, INTO子句中没有出现的属性列, 新元组在这些列上将取空值

如果在表定义 时说明了该属性列NOT NULL, namely不能去空值, 则会报错

如果INTO子句中没有指明任何属性列名, 则新插入的元组必须在每个属性列上均有值

2. 插入子查询结果

子查询不仅可以嵌套在 SELECT语句中用以构造父查询地条件, 也可以嵌套在INSERT语句中用以生成要插入的批量数据

格式:

INSERT
INTO <表名> [(<属性列1> [,<属性列2>...])
          子查询;

Instance:

/*对每一个系, 求学生的平均年龄, 并把结果存入数据库*/
/*首先在数据库中建立一个新表, 其中一列存放系名, 另一列存放相应的学生平均年龄*/
CREATE TABLE Dept_age
(Sdept CHAR(15)
Avg_age SMALLINT);
/*然后对Student表按系分组求年龄, 再把系名和平均年龄存入新表中*/
INSERT INTO Dept_age(Sdept,Avg_age)
SELECT Sdept,AVG(Sage)
FROM Student
GROUP BY Sdept;
修改数据

修改操作又称为更新操作, 其语句的一般格式为:

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

其功能是 修改指定表中满足WHERE子句条件的元组, 其中SET子句给出<表达式>的值用于取代相应的属性列值

如果省略WHERE子句, 则表示要修改表中的所有元组

1. 修改某一个元组的值

Instance:

/*将学生201215121的年龄改为22岁*/
UPDATE Student
SET Sage=22
WHERE Sno='201215121';
2. 修改多个元组的值

Instance:

/*将所有学生的年龄增加1岁*/
UPDATE Student
SET Sage=Sage+1;
3.带子查询的修改语句

Instance:

/*将计算机科学与技术系全体学生的成绩记零*/
UPDATE SC
SET Grade=0
WHERE Sno IN
	(SELECT Sno
    FROM Student
    WHERE Sdept='CS');
删除数据

删除语句的一般格式为:

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

DELETE语句 的功能是从指定表中删除满足WHERE子句条件的所有元组

如若省略WHERE子句则表示删除表中全部元组

区别于DROP是删除定义, DELETE只是删除表中的数据, 而表的定义仍在字典中

1. 删除某一个元组的值
/*删除学号为2020201266的学生记录*/
DELETE/*DELETE删除的是一行*/
FROM Student
WHERE Sno='55555'
2. 删除多个元组的值
/*删除所有的学生选课记录*/
DELETE
FROM SC;
3. 带子查询的删除语句
/*删除计算机科学系所有学生的选课记录*/
DELETE 
FROM SC
WHERE Sno IN
	(SELECT Sno
	FROM Student
	WHERE Sdept='CS')

1.5 空值的处理

def: 所谓空值就是 不知道 或 不存在 或 无意义 的值, 其含有不确定性

空值的产生有: 1. 插入元组时产生; 2. 修改为NULL; 3.外连接也会产生空值

空值的判断可以用 IS NULL 或 IS NOT NULL来进行

Instance:

/*从Student表中找出漏填了的学生信息*/
SELECT * FROM Student
WHERE Sname IS NULL OR Ssex IS NULL OR Sage IS NULL OR Sdept IS NULL;

空值的约束条件:

对属性定义或域定义中有NOT NULL约束条件的不能取NULL, 加了UNIQUE限制的属性值不能取NULL, 码属性不能取NULL

空值的算术运算见P120

1.6 视图

视图的特点:

  1. 视图是虚表, 是从一个或几个基本表(或视图)中导出的表
  2. 数据库中只存放视图的定义, 而不存放视图的数据, 数据仍在原来的基本表中(一旦基本表中的数据发生变化, 从视图中查询的数据也发生变化)

基于视图的操作

查询, 删除, 受限更新, 定义基于该视图的新视图

定义视图
1. 建立视图

格式:

CREATE VIEW <视图名> [(<列名> [,<列名>]...)]
AS <子查询>
[WITH CHECK OPTION];

说明

  1. 组成视图的属性列名, 可以 全部省略或全部指定
  2. 子查询中不允许含有ORDERD BY和DISTINCT短语, 因为这些短语都是对原基本表的操作
  3. RDBMS执行CREATE VIEW语句时只是把视图定义存入数据字典, 并不执行其中的SELECT语句
  4. 在对视图进行查询时, 按视图的定义从基本表中将视图查出

Instance:

/*建立信息系学生的视图, 并要求进行修改和插入操作时, 仍需保证该视图仅有信息系学生*/
CREATA VIEW IS_Student
AS 
SELECT Sno, Sname,Sage
FROM Student
WHERE Sdept='IS'
WITH CHECK OPTION;/*若不加此句, 则没有这个限制, 加上则以后对该视图进行插删改时, 自动加上Sdept='IS'的条件

若一个视图是从单个基本表导出的, 并且只是去掉了基本表的某些行或列, 但保留了主码, 则称这类视图为:

行列子集视图 IS_Student就是一个行列子集视图

/*建立信息系选修了1号课程的学生的视图(包括学号, 姓名, 成绩)*/
CREATE VIEW IS_S1(Sno,Sname,Grade)
AS
SELECT Sno,Sname,Grade
FROM Student,SC
WHERE Sdept='IS' AND
		Student.Sno=SC.Sno AND
		SC.Cno=1;

视图也可以建立在多个基本表或一个或多个已定义好的视图上, 或基于基本表和视图

Instance:

/*建立信息系选修了1号课程且成绩在90分以上的学生的视图*/
CREATE VIEW IS_S2
AS
SELECT Sno,Sname,Grade
FROM IS_S1
WHERE Grade>=90;/*这个视图便是建立在IS_S1上的*/

定义基本表时, 为减少数据库中的冗余数据, 表中只存放基本数据, 由基本数据经过各种计算派生出的数据不存储

**由于视图中的数据不实际存储, 所以定义视图时可以根据应用的需要设置一些派生属性列, 这些派生属性由于在基本表中不存在, 也称他们为 虚拟列 **

带虚拟列的视图也称为 带表达式的视图

/*定义一个反映学生出生年份的视图*/
CREATE VIEW BT_S(Sno,,Sname,Sbirth)
AS SELECT Sno,Sname,2023-Sage FROM Student;

这里BT_S是一个带表达式的视图, 视图中的出生年份是通过计算得到的

还可以用带有聚集函数和GROUP BY子句的查询来定义视图, 这种视图叫做 分组视图

/*将学生的学号及平均成绩定义为一个视图*/
CREATE VIEW S_G(Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno;
/*由于AS语句中SELECT语句的目标列平均成绩是通过作用聚集函数得到的, 故GREATE VIEW中必须明确定义组成S_G视图的各个属性列名, S_G是一个分组视图*/

/*将Student表中所有女生记录定义为一个视图*/
CREATE VIEW F_Student(F_sno,name,sex,age,dept)
AS
SELECT * FROM Student WHERE Ssex='女';
/*这里 视图F_Student是由子查询'SELECT *'建立的, 如果修改了Student表的结构, 则表与视图的映像关系就会被破坏, 视图就无法正常工作, 为避免上述问题,最好在修改基本表后删除由该基本表导出的视图, 然后重建这个视图*/
2. 删除视图

删除视图语句格式:

DROP VIEW <视图名> [CASCADE];

说明:

  1. 该语句从数据字典上删除指定的视图定义
  2. 如果该视图还导出了其他视图, 则使用CASCADE级联删除, 则会将该视图和由该视图导出的其他视图一并删除
  3. 删除基本表时, 由该基本表导出的视图均无法使用, 但这些视图的定义仍在数据字典中, 并未清除, 需要显式地使用DROP VIEW语句予以删除
/*删除视图 BT_S和IS_S1*/
DROP VIEW BT_S;  /*成功执行*/
DROP VIEW IS_S1; /*拒绝执行*/
/*IS_S1表还导出了IS_S2表, 要想删除S1, 需要使用CASCADE级联删除*/
DROP VIEW IS_S1 CASCADE;/*将S1和由S1导出地视图全部删除*/
查询视图

RDBMS实现 视图查询的方法 视图消解法

Step1. 进行有效性检查(涉及的表/视图是否存在)

Step2. 若存在, 从数据字典中取出视图的定义, 把定义中的子查询和用户的查询结合起来, 转换成等价的对基本表的 查询

Step3. 执行修正了的查询

这一转换的过程 称为视图消解 view resolusion

Instance:

/*在信息系学生的视图中, 找出年龄小于20岁的学生*/
SELECT Sno,Sage
FROM IS_Student
WHERE Sage<20;
/*转换后的查询语句为*/
SELECT Sno,Sage
FROM Student
WHERE Sdept='IS'AND Sage<20;

大多数情况下, 视图查询的转换是直接了当的:

/*查询选修了1号课程的信息系学生*/
SELECT IS_Student.Sno,SC
FROM IS_Student,SC
WHERE IS_Student.Sno=SC.Sno AND SC.Cno='1';

但在有些情况下, 视图消解法不能生成正确查询:

/*在SG视图中查询平均成绩在90分以上的学生学号和平均成绩*/
SELECT*
FROM S_G
WHERE Gavg>=90;
/*定义S_G视图的子查询为*/
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno;
/*如果将本例中的查询语句和子查询直接结合转换, 将形成下列语句:*/
SELECT Sno,AVG(Grade)
FROM SC
WHERE AVG(Grade)>=90
GROUP BY Sno;
/*WHERE子句中不能出现聚合函数, 故语法错误*/
/*正确的转换应该是:*/
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=90;

多数RDBS对行列子集视图的查询均能进行正确转换, 但对非行列子集视图就不一定了

定义视图的查询与基于派生表的查询有不同:

视图一旦定义, 其定义将永久保留在数据字典中, 之后的所有查询都可以直接引用该视图

派生表只是在语句执行时临时定义, 语句执行后该派生表即删除

更新视图

def: 更新视图是指通过视图来 插入INSERT 删除DELETE 和修改UPDATE 数据

因为视图是不实际存储数据的虚表, 因此对视图的更新最重要转换为对基本表的更新

为防止在更新视图时出错, 定义视图时要加上 CHECK OPTION 子句

Instance:

/*将信息系学生视图IS_Student中学号为555的学生姓名改为'刘晨'*/
UPDATE IS_Student
SET Sname='刘晨'
WHERE Sno='555';
/*转换后的更新语句为*/
UPDATE Student
SET Sname='刘晨'
WHERE Sno='555'AND Sdept='IS';

/*向信息系学生视图中插入一个新的学生记录*/
INSERT 
INTO IS_Student
VALUES('666','ZGK',20);
/*转换为对基本表的更新*/
INSERT 
INTO Student(Sno,Sname,Sage,Sdept)
VALUES(XXX,'IS');

/*删除信息系学生视图中学号为'666'的学生*/
DELETE
FROM IS_Student
WHERE Sno='666';
/*转换为对基本表的删除*/
DELETE
FROM Student
WHERE Sno='666'AND Sdept='IS';

在RDB中, 并不是所有视图都是可更新的, 因为有些视图的更新 不能唯一地有意义地转换成相对应基本表的更新

Instance:

/*对视图S_G中学号为'555'的学生的平均成绩改为90分*/
/*建立视图S_G*/
CREATE VIEW S_G(Sno,Gavg)AS
SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno;
/*更新视图S_G*/
UPDATE S_G SET Gavg=90 WHERE Sno='555';
/*这个视图的更新是无法转换成对基本表的更新的, 因为平均成绩是通过计算得出的,系统无法修改各科成绩, 使得平均成绩为90*/
/*目前,各个RDBS一般只允许对行列子集视图进行更新*/
视图的作用
  1. 视图能够简化用户的操作
  2. 视图使用户能以多种角度看待同一数据
  3. 视图对重构数据库提供了一定程度的逻辑独立性
  4. 视图能够对机密数据提供安全保护
  5. 适当地利用视图可以更清晰地表达查询

2. 数据库安全性

2.1 数据库安全性概述

def: 数据库的安全性是指保护数据库以防止不合法使用所造成的数据泄露、更改或破坏

系统安全性是否有效是数据库系统的主要技术指标之一

1. 数据库的不安全因素
  1. 非授权用户对数据库的恶意存取与破坏—用户身份鉴别, 存取控制和视图技术
  2. 数据库中重要或敏感的数据被泄露—强制存取控制, 数据加密存储和加密传输
  3. 安全环境的脆弱性—建立可信的计算机系统的概念和标准(安全标准)

####2. 安全标准简介

CC 通用准则------1999年被ISO采用为国际标准, 2001年被我国采用为国家标准

TCSEC 1985年美国国防部颁布, 又称桔皮书, CC已基本取代TCSEC

2.2 数据库安全性控制

计算机系统中安全措施是 一级一级层层设置

数据库安全性主要包括: 用户身份验证, 多层存取控制, 审计, 视图, 数据加密等安全技术

1. 用户身份鉴别

用户身份鉴别是DBMS提供的 最外层安全保护措施

每个用户在系统中都有一个用户标识, 每个用户标识由 用户名user name 和用户号 UID两部分组成

UID在系统的生命周期内是唯一的

用户身份鉴别的方法:

  1. 静态口令鉴别----用户自己设定(密码)
  2. 动态口令鉴别----口令动态变化, 每次鉴别时要用新口令登录, 一次一密(手机验证码)
  3. 生物特征鉴别----指纹, 掌纹, 虹膜
  4. 智能卡鉴别----智能卡是一种不可复制的硬件
2. 存取控制

存取控制机制主要包括 **定义用户权限 和 合法权限检查 **两部分

定义用户权限 和 合法权限检查 机制一起组成了DBMS的存取控制子系统

3. 自主存取控制方法

用户权限由两个要素组成: 数据库对象 和 操作类型

在DBS中, 定义存储权限称为 授权

在RDBS中, 存取控制的对象不仅有数据本身(基本表, 属性列上的数据), 还有数据库模式(数据库, 基本表, 视图和索引的创建等)

在给用户授予列INSERT权限时, 一定要包含主码的INSERT权限, 否则用户的插入动作会因为主码为空而被拒绝

4. 授权: 授予与收回
授予 GRANT

def: 对指定操作对象的指定操作权限授予指定的用户

格式:

GRANT <权限>[,<权限>]...
ON <对象类型> <对象名> [,<对象类型> <对象名>]...
TO <用户>[,<用户>]...
[WITH GRANT OPTION];/*若指定该语句, 则获得权限的用户可以把这个权限再授予其他的用户*/

发出该语句的可以是数据库管理员DA, 也可以是数据库对象创建者(主owner), 还可以是已拥有该权限的用户

接受权限的用户可以是一个或多个具体用户, 也可以是PUBLIC, 即全体用户

SQL标准允许具有WITH GRANT OPTION的用户把相应权限或其子集传递授予其他用户, 但不允许循环授权

Instances:

/*把查询Student表的权限授给用户U1*/
GRANT SELECT
ON TABLE Student
TO U1;
/*把对Student表和Course表的全部操作权限授予用户U2和U3*/
GRANT ALL PRIVILEGES
ON Student,Course
TO U2,U3;
/*把对表SC的查询权限授予所有用户*/
GRANT SELECT ON TABLE SC 
TO PUBLIC;
/*把查询Student表和修改学生学号的权限授予用户U4*/
GRANT UPDATE(Sno),SELECT
ON TABLE Student TO U4;/*对属性列授权时 必须指出相应的属性列名*/
/*把对表SC的INSERT权限授予U5用户, 并允许将此权限再授予其他人*/
GRANT INSERT ON SC TO U5
WITH GRANT OPTION;
收回 REVOKE

格式:

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

授予的权限可以由DA或其他授权者用REVOKE语句收回

Instances:

/*把用户U4修改学生号的权限收回*/
REVOKE UPDATE(Sno)
ON TABLE Student
FROM U4
/*收回所有用户对表SC的查询权限*/
REVOKE SELECT
ON TABLE SC
FROM PUBLIC;
/*把用户U5对SC表的插入权限收回*/
REVOKE INSERT
ON TABLE SC
FROM U5 CASCADE;/*CASCADE级联收回, 收回了U6和U7的INSTER权限
创建数据库模式的权限

数据库管理员DA在创建用户时实现 对创建数据库模式的权限

CREATE USER 语句格式

CREATE USER <user name> [WITH][DBA|RESOURCE|CONNECT];

说明:

  1. 只有系统的超级用户才有权创建一个新的数据库用户
  2. 新创建的数据库用户有三种权限: CONNECT RESOURCE DBA
  3. 命令中若没有指出哪种权限, 则默认CONNECT: 用户不能创建新用户, 不能创建模式, 不能创建基本表, 只能登陆数据库, 由数据库管理员或其他用户授予权限后才能在权限范围内进行操作
  4. RESOURCE: 能创建基本表和视图, 成为所创建对象的属主(属主可以GRANT权限给别人)
  5. DBA: 拥有DBA权限的是系统中的超级用户
5. 数据库角色

def: 数据库角色是指被命名的一组与数据库操作相关的权限, 角色是权限的集合

可以为一组具有相同权限的用户创建一个角色, 简化授权的流程

SQL中首先用CREATE ROLE语句创建角色, 然后用GRANT语句授权, 用REVOKE收回

Instance:

/*创建一个角色R1*/
CREATE ROLE R1;
/*使用GRANT语句, 使R1拥有Student表的SELECT, UPDATE, INSERT权限*/
GRANT SELECT,UPDATE,INSERT
ON TABLE Student
TO R1;
/*将这个角色授予王平,张明,赵玲*/
GRANT R1
TO 王平,张明,赵明;
/*收回王平的权限*/
REVOKE R1
FROM 王平;
/*角色权限的修改*/
GRANT DELETE 
ON TABLE Student
TO R1;
REVOKE SELECT
ON TABLE Student
FROM R1;
6. 强制存取控制方法

作用:保证更高程度的安全性, 用户不能直接感知或进行控制

适用于对数据有严格而固定密级分类的部门, 如军事部门

其他见P148

数据库系统概论第五版（第 3 章 关系数据库标准语言SQL）笔记
m0_61481871的博客
03-22 5268
目录 1. SQL概述 （1）什么是SQL? （2）SQL的特点 ① 综合统一 ② 高度非过程化 ③ 面向集合的操作方式 ④ 以同一种语法结构提供多种使用方式 ⑤ 语言简洁，易学易用 （3） SQL的基本概念 基本表 存储文件 视图 2. 学生-课程数据库 3. 数据定义 （1）数据定义概览 （2）模式的定义与删除—SCHEMA ① 定义模式 ② 删除模式 （3）基本表的定义、删除与修改—TABLE ① 定义基本表的标准格式 ② 数据类型 ③ 修改基本表 ④ 删
SQL数据库知识点总结归纳
爱吃Java的羊的博客
12-03 1454
当interval作为函数时，它被当作一个比较函数，即interval（），如interval（4,0,1,2,3,4,5,6） ，则在函数中，第一个数4作为被比较数，后面的0,1,2,3,4,5,6为比较数，然后将后面的数字依次与4进行比较，返回小于等于4的个数，所以上述结果为5，注意只有将4后面的数字从大到小进行排序，interval函数才能正常使用，若排序混乱，也可以使用但会影响最终结果。同真实的表一样，视图包含一系列带有名称的列和行数据，但是，视图并不在数据库中以存储的数据值集形式存在。
手把手教你用ComfyUI玩转OOTDiffusion：零代码实现1024x768高清虚拟换衣
最新发布
weixin_30786657的博客
04-28 1688
本文详细介绍了如何使用ComfyUI和OOTDiffusion实现零代码高清虚拟换衣，分辨率可达1024x768。从环境配置、插件安装到核心工作流搭建，再到参数调优和高级应用场景，手把手教你掌握AI虚拟试穿技术，提升电商和服装设计领域的工作效率。
《数据库系统概论》| 第三章  关系数据库标准语言SQL 知识梳理 （二、数据查询） 知识梳理
12 26 25 的博客
01-12 7785
  数据查询   目录   数据查询 3.4.1 单表查询 选择表中的若干列 选择表中的若干元组 指定DISTINCT关键词，去掉表中重复的行 ORDER BY子句 聚集函数 GROUP BY子句 (WHERE子句中是不能用聚集函数作为条件表达式） ​ 3.4.2 连接查询 等值与非等值连接查询 自身连接 外连接 3.4.3 嵌套查询 带有IN谓词的子查询 带...
笔记——数据库系统概述——关系数据库标准语言SQL
Greenxiaojing的博客
02-18 383
数据库系统概述考研笔记
数据库系统概论复习笔记(三)关系数据库标准语言SQL
饮tom的博客
05-31 1266
教材：数据库系统概论（第五版） 出版社：高等教育出版社 思维导图(考试部分) 第三章 关系数据库标准语言SQL SQL：结构化查询语言，是关系数据库的标准语言 3.1 SQL概述 SQL的特点 综合统一 高度非过程化(必须指定存取路径) 面向集合的操作方式 以同一种语法结构提供两种使用方法 语言简洁，易学易用 SQL支持关系数据库三级模式结构 3.3 数据定义 数据库（有的系统称为目录）→ ...
【MySQL】——关系数据库标准语言SQL（大纲）
shsjssnn的博客
01-20 2545
关系数据库标准语言SQL（大纲）
【SQL server】玩转SQL server数据库：第三章 关系数据库标准语言SQL（二）数据查询
朝花夕拾
03-04 1万+
本文是“【SQL server】玩转SQL server数据库”系列的第三章，深入探讨了关系数据库标准语言SQL的数据查询。通过学习本文，读者将了解SQL语言中的数据查询操作，包括SELECT语句的基本结构、条件筛选、排序和限制结果集等内容，进一步掌握SQL Server数据库的查询技巧与应用。
SQL与关系数据库编程习题详解及实践
weixin_42113456的博客
07-28 608
SQL（Structured Query Language）是一种特殊的声明式编程语言，专门用于管理关系型数据库管理系统（RDBMS）。SQL的应用覆盖了数据的定义、查询、更新和访问控制等多个方面，是数据库管理和数据操作不可或缺的工具。在当今的IT行业中，SQL不仅是数据库管理员的基础，也是程序员和数据分析师的必备技能。数据操纵语言（DML）是SQL的重要组成部分，它允许用户插入、更新、删除和查询数据库中的数据。
在关系数据库中保存大量XML文档
weixin_30666401的博客
08-06 146
当在关系数据库中保存XML文档的时候，由于采用方法上的原因，尤其是对于大量的文档，你一定碰到过很多问题。这一篇文章将提出与大量XML文档相关的特定的问题，然后给出解决这些问题的方法。数量非常大的文档的潜在问题 什么东西使得一个XML文档变得非常巨大？一个XML文档变得巨大的其中一个迹象是文档启动的时候就出现问题。现在让我们讨论其中的潜在问题。 首先是有关原始空间(raw space)的观点。出...
【数据库】第三章 关系数据库标准语言—SQL 笔记
@宁兰 的博客
02-13 2400
结构化查询语言 结构化查询语言 Structured Query Language，简称SQL，含数据查询、数据定义、数据操纵、数据控制。 大型数据库： Sybase  SQL Server  Oracle  DB2  MySQL 小型数据库： FoxPro  Access  SQLite SQL的基本概念 基本表（Base Table）：一个关系对应一个...
全世界最长的统计SQL语句，可拆分使用
暴风城基地
06-09 602
全世界最长的统计SQL语句，可拆分使用
数据库 之带子查询的操作--插入子查询结果、带子查询的修改语句、带子查询的删除语句
倚树探星的博客
02-11 6068
语句格式 INSERT INTO <表名> [(<属性列1> [,<属性列2>… )] 子查询; 【子查询要求】 SELECT子句目标列必须与INTO子句匹配 ，包括 值的个数 值的类型 例子：对每一个系，求学生的平均年龄，并把结果存入数据库 INSERT INTO Dept_age(Sdept,Avg_age) SELECT S...
sqldef：一款免费的数据库变更管理工具
Tony.Dong的专栏
03-13 1034
sqldef 是一个用于管理数据库模式结构的工具，可以用于比较两个数据库中的表结构差异并且生成可执行的 DDL 脚本。
终极数据库架构管理指南：sqldef 让变更更简单
gitblog_00068的博客
01-18 462
在当今数据驱动的世界中，数据库架构管理是每个开发者和DBA必须掌握的技能。**sqldef** 作为一款强大的幂等性数据库架构管理工具，能够帮助您轻松应对MySQL、PostgreSQL等多种数据库的变更需求，让架构管理变得前所未有的简单和可靠。 ## 🚀 什么是 sqldef？ sqldef 是一款开源的数据库架构管理工具，专门为需要处理复杂数据库变更的团队设计。它支持多种主流数据库，包括
关系数据库标准语言SQL学习笔记
m0_73686459的博客
03-18 1443
本文对基础的sql语句进行汇总
解决ERROR: cannot drop schema sc_base_1 because other objects depend on it.模式级联删除
蓝紫的博客
07-16 4370
Java资深小白，不足之处，或者有任何错误欢迎指出。 --蓝紫 错误：删除模式失败，因为模式与其他表之间有依赖关系。 解决：级联删除模式，DROP SCHEMA 模式名 CASCADE; ｀（∩_∩）′规则的生活，没太多颜色 ...
2021-12-21 数据库系统原理复习提纲（一）
weixin_51187533的博客
01-03 1177
第一章 绪论 四个基本概念 数据(Data) 对事物的描述，数据库中存储的基本对象 数据的定义 —— 描述事物的符号记录 数据的种类 —— 文字、图形、图象、声音 数据的特点 —— 数据与其语义是不可分的 数据库(Database) 长期储存在计算机内、有组织的、可共享的大量数据集合。 数据按一定的数据模型组织、描述和储存。 特征： 1）可为各种用户共享 2）冗余度较小 3）数据独立性较高 4）易扩展 三个重要的基本特点：数据库数据具有永久储存，有组织，可共享三个基本特点。 数据库管理系统(DB
MySQL带子查询的更新和删除（自改自删）
tttzzzqqq2018的博客
07-25 644
MySQL 带子查询的更新和删除 （自改自删）
PostgreSQL中 drop table指令出现ERROR: cannot drop table userinfo because other objects depend on it
热门推荐
程序员日常记录
03-15 1万+
PostgreSQL中 drop table指令出现ERROR: cannot drop table userinfo because other objects depend on it。blue=# drop table userinfo; //删除table指令 //错误信息提示 ERROR: cannot drop table userinfo because other objects
评论 1
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Autumn_begins

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值