数据库系统概论学习记录

第3章 关系数据库标准语言SQL

3.1 SQL概述

3.1.1 SQL的产生与发展

3.1.1 SQL的特点

1. 综合统一

2. 高度非过程化

   • 非关系数据模型的数据操纵语言“面向过程”，必须制定存取路径，表格数据库不用，层次和网状数据库要
   • SQL只要提出“做什么”，无须了解存取路径

3. 面向集合的操作方式

   • 非关系数据模型采用面向记录的操作方式，操作对象是一条记录

     • 一层一层找

   • SQL采用集合操作方式

     • 操作对象、查找结果可以是元组的集合

     • 一次插入、删除、更新操作的对象可以是元组的集合

4. 以同一种语法结构提供多种使用方式

   • SQL是独立的语言，能够独立地用于联机交互的使用方式
   • SQL又是嵌入式语言，能够嵌入到高级语言程序中

5. 语言简洁，易学易用

   • 数据查询 Select
   • 数据定义 Create ,Drop,Alter
   • 数据操纵 Insert,Update,Delete
   • 数据控制 Grant,Revoke

3.1.3 SQL的基本概念

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

   SQL

   视图1 视图2 外模式 应用程序看到的，可以展现出一张表

   基本表1 基本表2 基本表3 基本表4 模式 数据库里的

   存储文件1 存储文件2 内模式 硬盘里的

2. 基本表

   1. 第二章基本表叫关系，SQL数据库里，叫二维表
   2. 本身独立存在的表
   3. SQL中一个关系就对应一个基本表
   4. 一个（或多个）基本表对应一个存储文件
   5. 一个表可以带若干索引，索引就是做目录

3. 存储文件

   1. 操作系统放在计算机硬盘上的，这里放的是数据库里的数据、格式、表等一些数据库里面的信息
   2. 逻辑结构组成了关系数据库的内模式
   3. 物理结构上是任意的，对用户透明

4. 视图

   1. 外模式
   2. 从一个或几个基本表导出的表
   3. 数据库中只存放视图的定义而不存放视图对应的数据 ，只保存结构，不保存数据， 避免冗余
   4. 是一个虚表
   5. 用户可以视图上再定义视图

学生-课程数据库

1. 学生-课程模式S-T
2. 学生表：Student(Sno, Sname, Ssex, Sage, Sdept)
3. 课程表：Course(Cno, Cname, Cpno, Ccredit)
4. 学生选课表：SC(Sno, Cno, Grade)

3.3 数据定义

1. SQL的数据定义功能：模式定义、表定义、视图和索引的定义

   ​ 创建 删除 修改

   模式 Create schema Drop schema

   表 Create table Drop table Alter table

   视图 Create view Drop view

   索引 Create index Drop index Alter index

2.模式的定义和删除

1. 定义模式

   Create schema 模式名 Authorization 用户名

   eg:为用户Wang定义一个学生-课程模式S-T

   Create schema "S-T" Authorization Wang;

   为用户Wang定义一个模式S-T

   • 无模式名字，默认用户名

   • 定义模式时，可以同时定义表、视图、授权

     eg:

     Create schema Test Authorization Zhang

     Create table Tab1( COL1 SMALLINT

     ​ COL2 INT,

     ​ COL3 CHAR(20),

     ​ COL4 NUMERIC(10,3),

     ​ COL5 DECLMAL(5,2)

     ​ );

   • 执行创建模式语句必须拥有DBA权限，或者DBA授予在Create schema的权限

2. 删除模式

   Drop schema 模式名 Cascade|Restrict

   • Cascade和Restrict必须二选一
   • Cascade(级联)：删除模式的同时把该模式中所有的数据库对象全部删除
   • Restrict(限制)：如果该模式中已经定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行，只有当该模式中没有任何下属的对象时才能执行Drop schema语句。

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

1. 定义基本表

   语句格式：

   Create table 表名

   (列名 数据类型 [列级完整性约束条件,如主键，实体完整性]

   [, 列名 数据类型 [列级完整性约束条件，如SC表Sno,Cno参照Student、Course的，参照完整性]]...

   [,表级完整性约束条件] );

【说明】

​ 如果完整性约束条件涉及该表的多个属性列，则必须定义在表级上，否则既可以定义在表级上也可以定义在列级。

• 例3.5

  建立一个“学生”表Student

  Create table Student(

  Sno char(9) Primary key,/列级完整性约束条件/

  Sname char(20) Unique,/*Sname取唯一值 */

  Ssex char(2),

  Sage Smallint,/* 短整数，2的8次方 */

  Sdept char(20)

  );

• 例3.6

  建立一个“课程”表Course

  Create table Course

  ( Cno char(4) Primary key,

  Cname char(40),

  Cpno char(4),

  Ccredit Smallint,

  Foreign key(Cpno) References Course(Cno)

  );/* Cpno是外码，被参照表是Course，被参照列是Cno，外键，要求Cno是主码*/

• 例3.7

  建立一个学生选课表SC

  Create table SC

  ( Sno char(9),

  Cno char(4),

  Grade Smallint,

  Primary Key(Sno,Cno),/* 主码由两个属性构成，必须作为表级完整性进行定义*/

  Foreign Key(Sno) Referenses Student(Sno),

  /* 表级完整性约束条件，Sno是外码，被参照表是Student*/

  Foreign Key(Cno) Referenses Course(Cno)

  );

2. 数据类型

   • char(n)
   • varchar(n)
   • int
   • smallint
   • numeric(p,d) 定点数
   • real 取决于机器精度的浮点数
   • Double Precision 取决于机器精度的双精度浮点数
   • Float(n) 浮点数，精度至少为n位数字
   • date 年-月-日
   • time HH: MM: SS

3. 模式与表

   eg:大仓库就是数据库，每一个房间就是模式，在模式下可以建立多张表

   每一个基本表都属于某一个模式，一个模式包括多个基本表

   创建基本表时，若没有指定模式，系统根据搜索路径来确实该对象所属的模式

   1. 显示当前的搜索路径

      show search path

   2. 搜索路径的默认值

      $user, Pubilc;

   3. DBA用户可以设置搜索路径

      Set search_path to "S-T",Public;

      • 若搜索路径中的模式名都不存在，系统报错
      • 若搜索路径中的存在模式,Rdbms会使用模式列表中的第一个存在的模式作为数据库对象的模式名，先归user,再Public

   4. 创建基本表

      1. 创建表时给出模式名

         Create table "S-T".Student(...);

         Create table "S-T".Course(...);

         Create table "S-T".SC(...);

      2. 在创建模式语句中同时创建表

         Create schema Test Authorization Zhang

         Create table Tab1( COL1 SMALLINT

         ​ COL2 INT,

         ​ COL3 CHAR(20),

         ​ COL4 NUMERIC(10,3),

         ​ COL5 DECLMAL(5,2)

         ​ );

      3. 设置所属模式，在创建表名中不必给出模式名

         eg:DBA用户设置搜索路径，然后定义基本表

         Set search_path to "S-T", Public;

         Create Table Student(...);

         执行结果:建立了S-T.Student基本表

      4. 修改基本表

         Alter table 表名

         [ADD[Column] <新列名> <数据类型> [完整性约束]]

         [ADD <表级完整性约束>]/* 忘记加表级完整性约束条件可以通过Alter修改*/

         [Drop [Column] <列名> [Cascade|Restrict]]

         [Drop Constraint <完整性约束名>[Restrict|Cascade]]

         [Alter Column <列名><数据类型>]；

         【说明】

         1. 表名

         2. ADD

         3. Drop Column

            1. Cascade
            2. restrict

         4. Drop Constraint子句用于删除指定的完整性约束条件

         5. Alter Conlumn 子句用于修改原有的列定义，包括修改列名和数据类型

         • 例

           向Student表增加入学时间列，数据类型为日期

           Alter table Student ADD S_entrance Date;

           【注意】不论基本表中原来是否已有数据，新增加的列一律为空值，所以不能设置主键，主键不能为空值，只能在定义表时候设置

         • 例

           将年龄的数据类型由字符型(假设原来的数据类型是字符型)改为整型

           Alter table Student Alter Column Sage int;

         • 例

           增加课程名称必须取唯一值的约束条件

           Alter table Course ADD Unique(Cname);

      5. 删除基本表

         语句格式：Drop table <表名> [Restrict | Cascade];

         • 例

           删除Student表

           Drop table Student Cascade;

           1. Cascade
           2. Restrict,可以先把关联的表删除，再删除该表,默认Restrict
           3. 基本表定义被删除，数据被删除，表上的索引、视图、触发器等一般也将被删除

         • 例

           删除Student表，若表上建有视图，选择Restrict时表不能删除，选择Cascade时可以删除表，视图也被自动删除

           Create view IS_Student

           AS

           Select Sno,Sname,Sage

           From Student

           Where Sdept='IS';

           报错：Drop table Student Restrict;

           可行：Drop table Student Cascade;

           报错：Select * From IS_Student;

3.3.3 索引的建立与删除

• 建立索引的目的

  加快查询速度

• 数据库管理员DBA或建立表的人

  DBMS一般会自动建立以下列上的索引：

  Primary Key;

  Unique;

• DBMS自动完成维护索引

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

• RDBMS中索引一般采用B+树、HASH索引来实现

  B+树索引具有动态平衡的优点，HASH索引具有查找速度快的特点，由具体的RDBMS决定选择哪一个

• 索引是关系数据库内部实现的技术，属于内模式

• Create index语句索引时，可以定义索引，有唯一索引、非唯一索引或聚簇索引

1. 建立索引

   Create [Unique][Cluster] index <索引名> on <表名>(<列名>[<次序>])(,<列名>[<次序>])...;

   【说明】

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

      eg:

      为学生-课程数据库中的Student、Course、SC三个表建立索引

      Create Unique index Student on Student(Sno)；

      Create Unique index Course on Course(Cno);

      Create Unique index SCno on SC(Sno ASC,Cno DESC);

   2. Cluster表示要建立的索引是聚簇索引，聚簇索引是指索引顺序与表中记录的物理顺序一致的索引组织

      eg:

      ​ 逻辑 物理

      ​ 001 1行

      ​ 002 2行

      ​ 003 3行

      在Student表的Sname列上建立一个聚簇索引

      Create Cluster index Stusname on Student(Sname);

      • 在最经常查询的列上建立聚簇索引以提高查询效率
      • 一个基本表上最多只能建立一个聚簇索引
      • 经常更新的列不适合建立聚簇索引

   3. 删除索引

      Drop index <索引名>;

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

      例

      删除Student表的Stusname索引

      Drop index Stusname;

   4. 数据字典

      • 数据字典是关系数据库管理系统内部的一组系统表
      • 数据字典记录了数据库中所有的定义信息，包括模式定义、视图定义、索引定义、完整性约束定义、各类用户对数据库的操作权限、统计信息等
      • RDBM执行SQL数据定义时，实际是更新数据字典

3.4 数据查询

语句格式

​ Select [All|Distinct] <目标表达式> [,<目标表达式>]...

​ From <表名或视图名> [,表名或视图名]...

​ [Where <条件表达式>]

​ [Group by <列名1> [Having <条件表达式>]]

​ [Order by <列名2> [ASC|DESC]];

3.4.1 单表查询

功能：对一个表的内容查询

1. 查询指定列

   eg:查询全体学生的学号与姓名

   Select Sno,Sname

   From Student;

   查询全体学生的姓名、学号、所在系

   Select Sname,Sno,Sdept

   From Student;

2. 查询全部列

   功能选出表中所有属性列

   eg:查询全体学生的详细记录

   Select Sno,Sname,Ssex,Sage,Sdept

   From Student;

   Select *

   From Student;

3. 查询经过计算的值

   功能：选出表中指定的属性列，经过计算后输出

   • 算术表达式

     eg:查询全体学生的姓名及其出生年份

     Select Sname,2004-Sage

     From Student;

   • 字符串表达式

   • 函数

     eg:查询全体学生、出生年份和所有系，要求用小写字母表示所有系名

     Select Sname,'Year of Birth: ',2004-Sage,Lower(Sdept)

     From Student;

   • 列别名

     eg:

     Select Sname as NAME,'Year of Birth:' BIRTH,2004-Sage BIRTHDAY,Lower(Sdept) Department

     From Student;

4. 选择表中的若干元组

   1. 取消重复的行

      默认用All

      eg:查询选修了课程的学生姓名

      ​ Select Sno

      ​ From SC;

      等价于

      ​ Select ALL Sno

      ​ From SC;

      使用Distinct消除重复的行
      
      	Select Distinct Sno
      
      	From SC;
      

   2. 查询满足条件的元组

      • 使用where子句

      • 查询条件

        比较大小

        !=和<>不等于

        !>

        !<

        eg:

        1. 查询计算机科学系全体学生的名字

           Select Sname

           From Student

           Where Sdept='CS';

        2. 查询所有年龄在20岁以下的学生姓名及其年龄

           Select Sname,Sage

           From Student

           Where Sage<=20;

        3. 查询考试成绩有不及格的学生的学号

           Select Distinct Sno

           From SC

           Where Grade<60;

        确定范围：Between and,Not Between and

        eg:

        1. 查询年龄在20~23岁（包括20和23）之间的学生的姓名、系别和年龄

           Select Sname,Sdept,Sage

           From Student

           Where Sage Between 20 and 23;

        2. 查询年龄不在20~23岁（包括20和23）之间的学生的姓名、系别和年龄

           Select Sname,Sdept,Sage

           From Student

           Where Sage not Between 20 and 23;

        确定集合：in ,not in

        eg：in('SC','IS')

        1. 查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别

           Select Sname,Ssex

           From Student

           Where Sdept in('SC','MA','CS');

        2. 查询不是信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别

           Select Sname,Ssex

           From Student

           Where Sdept not in('IS','MA','CS');

        字符匹配：Like,Not Like

        [Not] Like '<匹配串>' [ESCAPE'<换码字符>']

        eg:

        %：任意长度

        _:一个字符

        1. 查询学号为200215121的学生的详细情况

           Select *

           From Student

           Where Sno Like '200215121';

           等价于

           Select *

           From Student

           Where Sno='200215121';

           记得这里是有区别的，每一个与运算，判断是否正确，具体忘记了，好像是效率的区别

        2. 查询所有姓刘学生的姓名、学号和性别

           Select Sname,Sno,Ssex

           From Student

           Where Sname Like '刘%';

        3. 查询姓”欧阳“且全名为三个汉字的学生的姓名

           Select Sname

           From Student

           Where Sname like '欧阳_';

        4. 查询名字中第二个字为”阳“字的学生的姓名和学号

           Select Sname,Sno

           From Student

           Where Sname like '_阳%';

        5. 查询所有不姓刘的学生姓名、学号和性别

           Select Sname,Sno,Ssex

           From Student

           Where Sname not like '刘%';

           使用换码字符将通配符转义为普通字符

        6. 查询DB_Design课程的课程号和学分

           Select Cno，Ccredit

           From Course

           Where Cname like 'DB\ _Design' Escape '';

        7. 查询以“DB_”开头，且倒数第三个字符为i的课程的详细情况

           Select *

           From Course

           where Cname like 'DB\ _%i _ _'

           Escape '';

           【说明】Escape的''表示“\”为换码字符

        空值：IS NULL，IS NOT NULL

        IS不能用“=”代替

        1. 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩，查询缺少成绩学生的学号和相应的课程号

           Select Sno,Cno

           From SC

           Where Grade is NULL;

        2. 查所有有成绩的学生学号和课程号

           Select Sno,Cno

           From SC

           Where Grade is not NULL;

        多重条件：AND，OR，NOT

        and的优先级高于or，可以加括号改变优先级，可以与between...and... in互用

        1. 查询计算机系年龄在20岁以下的学生姓名

           Select Sname

           From Student

           Where Sage<20 and Sdept='CS';

        2. 查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别

           Select Sname,Ssex

           From Student

           Where Sdept='IS' or Sdept='MA' and Sdept='CS' !!!!!

           或者

           Where Sdept in('IS','MA','CS');

   3. Order by子句

      作用：可以按照一个或多个属性列排序

      升序：ASC,默认

      降序：DESC

      当排序列含空值时：空值默认为最大值

      ASC：空值在最后显示,最下面

      DESC：空值的元组最先显示，最上面

      1. 查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排序

         Select Sno,Grade

         From SC

         Where Cno='3'

         Order by Grade DESC;

      2. 查询全体学生情况，查询结果按所在系的系号升序排序，同一系中的学生按年龄降序排序

         Select *

         From Student

         Order by Sdept,Sage DESC;

   4. 聚合函数

      Count([Distinct | All] *) 计算元组个数

      Count([Distinct | All] <列名>) 统计一列中值的个数

      Sum([Distinct | All] <列名>) 计算一列值的总和（数值型）

      Avg([Distinct | ALL] <列名> )计算一列值的平均值（数值型）

      Max([Distinct | ALL] <列名>) 计算一列值的最大值

      Min([Distinct | All] <列名>) 计算一列值的最小值

      1. 查询学生总人数

         Select Count(*)

         From Student;

      2. 查询选修了课程的学生人数

         Select Count(Distinct Sno)

         From SC;

      3. 查询学生200215012选修课程的总学分数

         Select Sum(Ccredit)

         From Course,SC

         Where Sno='200215012' and SC.Cno=Course.Cno;

      4. 计算1号课程的学生平均成绩

         Select Avg(Grade)

         From SC

         Where Cno='1';

      5. 查询选修1号课程的学生最高分数

         Select Max(Grade)

         From SC

         Where Cno='1';

   5. Group by子句

      【作用】按指定的一列或多列值分组，值相等的为一组，来细化聚集函数的作用对象

      ​ 未对查询结果分组，聚集函数将作用与整个查询结果

      ​ 对查询结果分组后，聚集函数将分别作用于每一个组

      1. 求各个课程号及相应的选课人数

         Select Cno,Count(Sno)

         From SC

         Group by Cno;

         结果：

         Cno Count(Sno)

         1 22

         2 34

         3 44

         4 33

         5 48

         Group by子句分组后，可以使用Having短语指定筛选条件

      2. 查询选修了3门以上的课程的学生学号

         Select Sno

         From SC

         Group by Sno having count(*)>3;

         Having短语与Where子句的区别：

         1. 作用对象不同：where子句作用于基表或视图，从中选择满足条件的元组

            ​ Having短语作用于组，从中选择满足条件的组

         2. Where子句中是不能用聚集函数作为条件表达式的

      3. 查询平均成绩大于等于90分的学生学号和平均成绩

         Select Sno,Avg(Grade)

         From SC

         Group by Sno Having Avg(Grade)>=90;

3.4.2 连接查询

1. 等值与非等值连接查询

   连接查询的Where子句中用来连接两个表的条件称为连接条件或连接谓词

   格式1

   [<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>

   格式2

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

   比较运算符为“=”称为等值连接

   其他称为非等值连接

   连接谓词中的列名称为连接字段，各连接字段类型必须是可比的，但名字不必是相同的

   1. 查询每一个学生及其选修课程的情况

      Select Student.*, SC. *

      From Student,SC

      Where Student.Sno=SC.Sno;

      结果：

      Student.Sno...SC.Sno...

      连接操作的一种执行方法：嵌套循环法

      1. 首先在表1找到第一个元组，扫描表2，找到元组拼接
      2. 第二元组。。。
      3. 重复找完

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

   2. 对上例子自然连接

      Select Student.Sno,Sname,Ssex,Sdept,Cno,Grade

      From Student

      Where Student.Sno=SC.Sno;

2. 自身连接

   一个表与其自己连接

   1. 需要给表起别名以示区别

   2. 由于所有属性名都是同名属性，因此必须使用别名前缀

   3. 查询每一门课的间接先修课（先修课的先修课）

      为Course表取两个别名，一个是First,另一个是Second

      Select First.Cno,Second.Cpno

      From Course First,Course Second

      Where First.Cpno=Second.Cno;

3. 外连接

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

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

   外连接操作以指定表为连接主体，将主体表中不符合条件的元组一并输出

   左外连接：

   Left outer join SC on(Student.Sno=SC.Sno)

   右外连接

   Right outer join SC on()

4. 多表连接

   两个表以上的连接

   Select Student.Sno,Sname,Cname,Grade

   From Student,SC,Course

   Where Student.Sno=Sc.Sno and Sc.Cno=Course.Cno;

3.4.3 嵌套查询

Select Sname

From Student

Where Sno in(

​ Select Sno

​ From SC

​ Where Cno='2');

子查询的Select语句中不能使用Order by子句

Order by子句只能对最终查询结果排序

1. in谓词子查询、

   1. 查询与‘刘晨’在同一个系学习的学生

      Select Sno,Sname,Sdept

      From Student

      Where Sdept in(

      ​ Select Sdept

      ​ From Student

      ​ Where Sname='刘晨'

      )；

2. 比较运算符子查询

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

   Select Sno,Cno

   From SC x

   where Grade>=(Select Avg(Grade)

   ​ From SC y

   ​ Where y.Sno=x.Sno);

3. any或all谓词的子查询

   查询非计算机科学系中比计算机科学系的任意一个学生年龄小的学生姓名和年龄

   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<All(

   ​ Select Sage

   ​ From Student

   ​ Where Sdept='CS')

   And Sdept<>'CS';

4. Exists谓词的子查询

   查询所有选修了1号课程的学生姓名

   Select Sname

   From Student

   Where Exists

   ( Select *

   ​ From SC

   ​ Where Sno=Student.Sno and Cno='1'

   );

3.4.4 集合查询

并Union

交intersect

差except

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

   Select *

   From Student

   Where Sdept='CS'

   Union

   Select *

   From Student

   Where Sage<=19;

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

   Intersect

3.5 数据更新

3.5.1 插入数据

1. 插入元组

   Insert

   Into

   Values

   eg

   Insert

   Into Student(Sno,Sname,Ssex,Sdept,Sage)

   Values('201215128','陈冬'，'男'，'IS',18);

   Insert

   Into Student

   Values('201215126'，'张'，'男'，18，'CS')；

   insert

   into SC

   values('222','1',NULL);

2. 插入子查询结果

   对每一个系，求学生的平均年龄，并把结果存入数据库

   Creat table Dept_age(

   ​ Sdept char(15)

   ​ Avg_age smallint

   ​ );

   Insert

   Into Dept_age(Sdept,Avg_age)

   Select Sdept,Avg(Sage)

   From Student

   Group by Sdept;

3.5.2 修改数据

1. 修改某一个元组的值

   Update Student

   Set Sage=22

   Where Sno='201215121';

2. 修改多个元组的值

   Update Student

   Set Sage=Sage+1;

3. 带子查询的修改语句

   计算机科学系全体学生的成绩置零

   Update SC

   Set Grade=0

   Where Sno in(

   ​ Select Sno

   ​ From Student

   ​ Where Sdept='CS'

   );

3.5.3 删除数据

1. 删除某一个元组

   Delete

   From Student

   Where Sno='20222';

2. 删除多个元组的值

   Delete

   From SC;

3. 带子查询的删除语句

   Delete

   From SC

   Where Sno in(select Sno

   ​ From Student

   ​ where Sdept='CS'

   ​ );