【数据库基础03】关系数据库标准语言SQL

系列文章目录

第一章 数据库系统绪论
第二章 关系数据库
第三章 关系数据库标准语言SQL
第四章 数据库安全性
第五章 数据库完整性
第六章 关系数据理论—规范化
第七章数据库设计
第八章关系查询处理和查询优化
第九章数据库恢复技术
第十章并发控制

---

---

前言

  本章将具体介绍SQL概述、学生—课程数据库、数据定义、数据查询、数据更新以及视图。结构化查询语言（SQL）是关系数据库的标准语言，也是一个通用的、功能极强的关系数据库语言。

在期末的基础上将最为关键的知识点进行记录，后期查漏补缺《数据库系统概论》 王珊

---

一、SQL概述

1. SQL的特点

   SQL集数据查询、数据操纵、数据定义和数据控制于一体，其主要特点包括如下：

1. 综合统一，其集数据查询、数据定义、数据操纵和数据控制于一体。
2. 高度非过程化，SQL在进行数据操纵时对用户透明，用户只要提出“做什么”，无须指明“怎么做”，因此无须了解存取路径。存取路径的选择以及SQL的操作过程由系统自动完成。
3. 面向集合的操作方式，插入、删除、更新操作的对象可以是元组集合。
4. 以同一种语法结构提供多种使用方式。
5. 语言简洁，易学易用。

   支持SQL的关系数据库管理系统同样支持关系数据库三级模式结构。外模式包括若干视图，模式包括若干基本表，内模式包括若干存储文件。

内模式	模式	外模式
存储文件	基本表	视图

Q：什么是基本表？什么是视图？两者的区别和联系是什么？
   基本表是数据库中真实存在且存储数据的独立的表。
   视图是数据库中虚拟的表，并不真实存储数据，只存储数据的定义。
   存储方式不同（基本表，真实存储数据；视图，不存储真实数据，存储数据的定义），存在形式不同（基本表，真实存在于数据库中；视图，并不真实存在于数据库中，是一张虚拟的表）
   联系：视图是由一个或者多个基本表导出的。

二、数据定义

  关系数据库系统支持三级模式结构，其模式、外模式和内模式中的基本对象有模式、表、视图和索引。

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

  一个关系数据库管理系统的实例中可以建立多个数据库，一个数据库中可以建立多个模式，一个模式下通常包括多个表、视图和索引等数据库对象。

1.模式的定义式与删除

1. 模式定义

create schema <模式名> authorization <用户名>
  如果没有指定模式名，那么模式名隐含为用户名。

要创建模式，调用该命令的用户必须拥有数据库管理员权限，或者获得了create schema 的权限。

Q：

1. 为用户wang定义一个学生-课程模式S-T：create schema S-T authorization wang
2. 为用户zhang创建一个模式TEST，并且在其中定义一个表table：

create schema test authorization zhang
create table tab1(
		col1 smallint,
		col2 int
);

2. 删除模式

drop schema <模式名> <cascade|restrict>
  其中cascade和restrict两者必选其一。cascade为强制全部删除，restrict当有表和视图时拒绝删除语句的执行。

1. 删除模式zhang，并删除模式中定义的表tab1：drop schema zhang cascade

2.基本表的定义、删除与修改

1.定义基本表

create table <表名> (<列名><数据类型>[列级完整性约束条件]
					...
					[<表级完整性性约束>])

1. 建立一个“学生”表student。其中学号为主码，学生姓名取值唯一。

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

2. 建立一个“课程”表course,课程编号为主码，课程名称不能为空值。表级完整性约束条件,此处的参照表和被参照表是同一个表。

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

3. 建立学生选课表SC。

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

2.修改基本表

alter table <表名>
[add [column] <新列名><数据类型>[完整性约束]]
[add [表级完整性约束]]
[drop [column] <列名>[cascade|restrict]]
[drop constraint <完整性约束名> [cascade|restrict]]
[alter column <列名><数据类型>]；

1. 向 Student 表增加“入学时间”列，其数据类型为日期型：alter table Student add column S_entrance date;不论表中原来是否有空值，新增加的列一律为空值。
2. 将年龄的数据类型有字符型改为整数：alter table Student alter column Sage int;
3. 增加课程名称必须取唯一值的约束条件。alter table Course add unique(Cname)

3.删除基本表

drop table <表名> [restrict|cascade]

1. 删除Student表：drop table Student cascade

3.索引的建立与删除

1.建立索引

  仅在重要属性上创建索引；系统默认创建主码索引；create [unique][cluster] index <索引名> on <表名>（<列名>[次序]），其中asc为升序，desc为降序。

• unique，表明此索引的每一个索引值只对应唯一的数据记录。
• cluster，表示要建立的索引为聚簇索引。聚簇索引：索引项与表中数据记录的物理一致。

1. 为学生—课程数据库中的Student、Course和SC三个表建立索引。其中student 表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建聚簇索引。

create unique index Stusno on Student(Sno asc)
create unique index Coucno on Course(Cno asc)
create cluster index SCno on SC(Sno asc,Cno desc)

2. 修改索引

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

1. 将SC表的SCno 索引名修改为SCSno：alter index SCno rename to SCSno

3. 删除索引

drop index <索引名>

1. 删除Student表的Stusname索引：drop index Stusname

四. 数据查询

  数据查询是数据库的核心操作。SQL提供了select语句进行数据查询，该语句具有灵活的使用方式和丰富的功能。

select [all|distinct] <目标列表达式>  %% all 表示输出所有，distinct 表示去重
from <表名或视图名> 
where <条件表达式>
group by <列名1> [having <条件表达式>]  %% 对group by 的补充 例如：以系分组（having 绩点3.0以上）
order by <列名2> [asc|desc] 

  select 语句既可以完成简单的单表查询，也可以完成复杂的连接查询和嵌套查询。

1. 单表查询

  单表查询指仅涉及一个表的查询。

• 查询表中的指定列
  • 查询全体学生的学号与姓名：select Sno,Sname from Student;
  • 查询全体学生的姓名、学号、所在系：select Sname,Sno,Sdept from Sudent;
• 查询全部列
  • 查询全体学生的详细记录：select * from Student; 等价于 select Sno,Sname,Ssex,Sage,Sdept
• 查询经过计算的值
  • 查询全体学生的姓名及出生年份：select Sname,2014-Sage from Student
  • 查询全体学生的姓名、出生年份和所在的院系，要求用小写字母表示：select Sname,'Year of Birth',2014-Sage,lower(Sdept) from Student
  • 用户可以通过指定列名来改变查询结果的列标题（列名后面空格加别名）：select Sname NAME,'Year of Birth' BIRTH,2014-Sage BIRTHDAY,lower(Sdept) department from Student
• 消除取值重复的行
  • 查询选修了课程的学生学号：select distinct Sno from SC

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

• 查询满足条件的元组
  • 查询计算机科学系全体学生的名单：select Sname from Student where Sdept = 'CS
  • 查询所有年龄在20岁以下的学生姓名及其年龄：select Sname, Sage from Student Where Sage<20;
  • 查询考试成绩不及格的学生的学号：select Sno from Student where grade<60
  • 查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄：select Sname,Sdept,Sage from Student where between 20 and 23;
  • 查询年龄不在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄：select Sname,Sdept,Sage from Student where between 20 not and 23;
  • 查询计算机科学系（CS）、数学系（MA）和信息系（IS）学生的姓名和性别：select Sname,Ssex from Student where Sdept in ('CS', 'MA', 'IS');
  • 查询缺少成绩的学生的学号和相应的课程号：select Sno, Cno from SC where Grade is null;
  • 查所有有成绩的学生学号和课程号：select Sno,Cno from SC where Grade is not null;
  • 查询计算机科学系年龄在20岁以下的学生姓名：select Sname from Student where Sage<20 and Sdept='CS' ;
  • 选出选修1号课程的不及格的学生以及缺考的学生：select Sno from SC where Cno = '1' and (Grade < 60 or Grade is null);
• 字符匹配，[not] like '<匹配串>' [escape '<换码字符>']
  • 其含义是查找指定的属性列值与<匹配度>相匹配的元组。<匹配串>可是一个完整的字符串，也可以是含有通配符%和_。
  • %（百分号）代表任意长度（长度可以为0）的字符串。例如a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab等都满足该匹配串。
  • _（下划线）代表任意单个字符，例如a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等。
  • 查询学号为201215121的学生的详细情况：select * from Student where Sno='201215121';或select * from Student where Sno like '201215121';
  • 查询所有姓刘的学生姓名、学号和性别：select Sname, Sno, Ssex from Student where Sname like '刘%';
  • 查询姓“欧阳”且全名为三个汉字的学生的姓名：select Sname from Student where Sname like '欧阳_';
  • 查询名字中第二个字为“阳”的学生的姓名和学号：select Sname, Sno from Student where Sname like '_阳%'；
  • 查询DB_Design课程的课程号和学分：select Cno,Ccredit from Course where Cname like 'DB\_Design' escape '\';
  • 查询以“DB_”开头，且倒数第三个字符为 i 的课程的详细情况：select * from Course where Cname Like 'DB\_%i__' escape '\';
• order by 子句：用户可以用order by 子句对查询结果按照一个或多个属性列的升序（ASC）或降序（DESC）排列，默认值为升序。
  • 查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列，默认值为升序：select Sno, Grade from SC where Cno = '3' order by Grade desc;
  • 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列(前后优先级)：select * from Student order by Sdept Asc, Sage desc

count(*)	统计元组个数
count([distinct|all] <列名>)	统计一列中值的个数
sum([distinct|all] <列名>)	统计一列的总和
avg([distinct|all] <列名>)	计算一列值的平均值
max([distinct|all] <列名>)	求一列值中的最大值
min([distinct|all] <列名>)	求一列值中的最小值

  如果指定distinct短语，则表示在计算时要取消指定列中的重复值。

• 聚集函数
  • 查询学生总人数：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';
  • 查询学生201215012选修课程的总学分数：select sum(Ccredit) from SC, Course where SC.Cno=Course.Cno and Sno = '201215012';

聚簇函数只能用于select子句和group by中的having子句，where子句中是不能用聚簇函数作为条件表达式的。

• group by 子句：该子句将查询结果按某一列或多列的值分组，即每一组都有一个函数值，group by 必须为selecct中的元素。
  • 求各个课程号及相应的选课人数：select Cno, count(Sno) from SC group by Cno ;
  • 查询选修了三门以上课程的学生学号：select Sno from SC group by Sno having count(*)>3;
  • 查询平均成绩大于等于90分的学生学号和平均成绩：select Sno,avg(Grade) from SC group by Sno having avg(Grade)>=90;

2. 连接查询

  一个查询同时涉及两个以上的表，则称之为连接查询。(当多个表有同一属性名时，引用时要加上前缀)

• 等值与非等值连接查询：使用连接运算法=时称为等值连接。使用其他运算符称为非等值连接。
  • 查询每个学生及其选修课程的情况：select Student.*, SC.* from Student, SC where Student.Sno = SC.Sno;
  • 查询选修2号课程且成绩在90分以上的所有学生的学号和姓名：select Student.Sno,Sname from Student, SC where Student.Sno = SC.Sno and SC.Cno='2' and SC.Grade>90;
• 自身连接，连接操作不仅可以再两个表之间进行，也可以是一个表与其自己进行连接，称为表的自身连接。
  • 查询每一门的间接先修课（即先修课的先修课），自身连接时要为连接表取两个别名，一个是first，另一个是second。：select first.Cno,second.Cno from Course first,Course second where first.Cpno=second.Cno;

3. 嵌套查询

  在SQL中，一个select-from-where语句称为一个查询块，将一个查询快嵌套在另一个查询块的where子句或having短语的条件中的查询称为嵌套查询。

• 查询选修2号课程且成绩在90分以上的所有学生的学号和姓名：select Sno, Sname from Student where Sno in (select Sno from SC where Cno='2' adn Grade>90);
• 带有in谓词的子查询。在嵌套查询中，子查询的结果往往是一个集合，所以谓词in是嵌套查询中最经常使用的谓词。
  • 查询与“刘晨”在同一个系学习的学生：select Sname from Student where Sdept in (select Sdept from Student where Sname='刘晨')；
• 带有比较运算符的子查询。如果说内查询的结果是一个值，则=可以代替in；
  • 查询与“刘晨”在同一个系学习的学生：select Sname from Student where Sdept = (select Sdept from Student where Sname='刘晨')；
• 带有exists谓词的子查询，不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。(与连接查询有类似之处，将连接放在exists中)
  • 查询所有选修了1号课程的学生姓名，select Sname from Student where exists (select * from SC where Cno='1');

4. select语句的一般格式

select [all|distinct]<目标列表达式>  select count(distinc Sno)
from <表名或视图名>
[where <条件表达式>]
[group by <列名1> [having <条件表达式>]] 
[order by <列名2> [asc|desc]];

五.数据更新（增删改）

  数据更新操作有三种：向表中添加若干行、修改表中的数据和删除表中的若干行。

1.插入数据

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

• 插入元组
    into子句中没有出现的属性列，新元组这些列上将取空值。其中，在表定义时说明了not null 的属性列不能取空值，否则会出错。

1. 将一个新学生元组（学号：201212128，姓名：陈东，性别：男，所在系：IS，年龄：18岁）插入到Student表中【指定属性名】：insert into Student(Sno,Sname, Ssex, Sdept, Sage) values ('201215128', '陈冬','男','IS',18)
2. 将学生张成民的信息插入到Student表中【不指定属性名】：insert into Student values ('201215128', '陈冬','男','IS',18)
3. 插入一条选课记录（‘201215128’，‘1’）：insert into SC(Sno, Cno) values(‘201215128’，‘1’)

• 插入子查询结果

insert
into <表名>[<属性列1>]
子查询；

%子查询结果与插入属性列对应。

1. 对每一个系，求学生的平均年龄，并把结果存入数据库：insert into X(Sdept, Avg_age) select Sdept, age(age) from Student group by Sdept

2.修改数据

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

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

• 修改某一个元组的值
  • 将学生201215121的年龄改为22岁：update Student set Sage=22 where Sno='201215121';
• 修改多个元组的值
  • 将所有学生的年龄增加1岁：update Student set Sage= Sage+1;
• 带子查询的修改语句
  • 将计算机科学系全体学生的成绩置零：update SC set Grade=0 where Sno in (select Sno from SC where Sdept='CS');

3.删除数据

delete
from <表名>
[where <条件>] %% 删除所有满足where 条件的元组

• 删除某一个元组的值。
  • 删除学号为201215128的学生记录：delect from Student where Sno='201215128';
• 删除多个元组的值
  • 删除所有学生选课记录：delete from SC;
• 带子查询的删除语句
  • 删除计算机科学系所有学生的选课记录：delete from SC where Sno in (select Sno from SC where Sdept='CS');

六. 视图

  视图是一个或几个基本表导出的表。视图，删改查。

1.定义视图

1.建立视图

create view <视图名>
as <子查询>
[with check option];

  其中，子查询可以是任意select语句，是否可以含有order by 子句和distinct 短语，则取决于具体系统的实现。

1. 建立信息系学生的视图。

create view is_student
as
select Sno,Sname,Sage
from Student
where Sdept='IS';

  省略的is_student的列名，隐含了由子查询中select子句中的三个列名组成。create view 语句的结果只是把视图的定义存入数据字典，并不执行其中的select语句。

2. 建立信息系学生的视图，并要求进行修改和插入操作是任需保证该视图只有信息系的学生。

create view is_student
as 
select Sno,Sname,Sage
from Student
where Sdept='CS'
with check option

  加上了with check option 子句，以后对该视图进行插入、修改和删除操作时，关系数据库管理系统会自动加上Sdept=‘IS’的条件。如果不加则不能对视图进行相应操作。
  若一个视图从基本表导出的，并且只是去除了基本表的某些行或某些列，保留了主码，则称为这类视图为行列子集视图。

3. 建立信息系选修了1号课程的学生的视图（包括学号、姓名、成绩）。

create view is_s1(Sno,Sname,Grade)
as
select Student.Sno,Sname,Grade
from Student,SC
where Student.Sno=SC.Sno and SC.Sdept = 'IS' and SC.Sdept ='1';

4. 建立信息系选修了1号课程且成绩在90分以上的学生的视图。

create view is_s2
as
select Sno,Sname,Grade
from is_s1
where Grade >= 90;

  可以在一个已经定义好的视图上建立新视图。

5. 定义一个反映学生出生年份的视图。

create view is_s3(Sno,Sname,birth)
as
select Sno,Sname,2023-Sage
from Student;

6. 将学生的学号及平均成绩定义为一个视图。

create view is_s4(Sno,avgS)
as
select Sno,avg(Grade)
from SC
group by Sno;

7. 将Student表中所有女生记录定义为一个视图。

create view is_s5(sno,name,sex,age,dept)
as
select *
from Student
where Ssex='女';

2.删除视图

  视图删除后视图的定义将从数据字典中删除。

• 删除视图is_s1和is_s2：

drop view is_s1; /*拒绝执行*/
drop view is_s2; /*成功执行*/

drop view is_s1 cascade;/*删除了视图is_s1和由它导出的所有视图*/

2.查询视图

  视图定义后，用户就可以像对基本表一样对视图进行查询。从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换为等价的对基本表的查询，然后再执行修正了的查询。这一转化过程称为视图消解。

1. 在信息系学生的视图中找出年龄小于20岁的学生。

select *
from is_s1
where Sage < 20;

2. 在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩。

select Sno,avg(Grade)
from S_G
group by Sno
having avg(Grade)>=90;

3.更新（改、增、删）视图

  由于视图是不实际存储数据的虚表，因此对视图的更新最终要转换为对基本表的更新。

1. 将信息系学生视图is_student中学号为‘201215122’的学生姓名改为“刘成”。

update is_student
set Sname="刘成"
where Sno='201215122';

系统转化后的更新语句为：
update Student
set Sname="刘成"
where Sdept='IS' and Sno='201215122';

2. 向信息系学生视图is_student中插入一个新的学生记录，其中学号为’1’，姓名为“张三”，年龄为20岁。

insert 
into is_stident
values('1', '张三', 20);

系统转化后的更新语句为：
insert
into Student(Sno,Sname,Sage,Sdept)       /*转换后要加入视图的约束，即为信息系“IS”*/
values('1','张三',20,'IS');

3. 删除信息系学生视图is_student 中学号为‘1’的记录。

delete 
from is_student
where Sno='1';

系统转化后的更新语句为：
delete
from Student
where Sno='1' and Sdept='IS';

4.视图的作用

  视图最终是定义在基本表之上的，对视图的一切操作最终也要转换为对基本表的操作。

1. 视图能够简化用户的操作：视图机制使用户可以将注意力集中在所关心的数据上。
2. 视图使用户能以多种角度看待同一数据。
3. 视图对重构数据库提供了一定程度的逻辑独立性：对于重构数据库，其逻辑结构改变了，但应用程序不必修改，因为新建立的视图定义为用户原来的关系，使用户的外模式保持不变，用户的应用程序通过视图仍然能够查找数据。
4. 视图能够对机密数据提供安全保护。
5. 适当利用视图可以更清晰地表达查询。

---

题目总结