关系数据库标准语言SQL

来源：互联网发布：免费预算软件编辑：程序博客网时间：2024/05/01 03:40

1、SQL概述：

SQL ，结构化查询语言，是关系数据库的标准语言，SQL是一个通用的、功能极强的关系数据库语言。

SQL集数据查询（Data　Query）、数据操纵（Data Mainipulation）、数据定义（Data Definition）和数据控制（Data Control）功能于一体。

SQL则集数据定义语言DDL，数据操纵语言DML、数据控制语言DCL的功能于一体。语言风格统一，可以独立完成数据库生命周期中的全部活动，包括：定义关系模式，插入数据，建立数据库；对数据库中的数据进行查询和更新；数据库的重构和维护；数据库安全性、完整性控制。数据操作符的统一性，查找、插入、删除、更新等每一种操作都只需一种操作符。SQL既是独立语言，又是嵌入式语言。

基本表是本身独立存在的表，在SQL中一个关系就对应一个基本表。一个（或多个）基本表对应一个存储文件，一个表可以带若干索引，索引也存放在存储文件中。

视图表是一个从一个或几个基本表导出的表。它本身不独立存储在数据库中，即数据库中只存放视图的定义而不存放视图对应视图对应的数据。

SQL动词：

数据库定义语言DDL create drop alter
数据库查询语言QL select
数据库操纵语言DML insert update delete
数据库控制语言DCL grant revoke

3.3 数据定义

SQL的数据定义功能包括模式定义，表定义、视图和索引的定义。如下图：

创建模式 create schema

删除模式 drop schema

创建表 create table

删除表 drop table

更新表/修改表 alter table

创建视图 create view

删除视图 drop view

创建索引 create index

删除索引 drop index

创建存储过程 create procedure

删除存储过程 drop procedure

修改存储过程 alter procedure

创建触发器 create trigger

删除触发器 drop trigger

修改触发器 alter trigger

3.3.1 模式的定义和删除

一、定义模式

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

如果没有指定模式名，那么模式名隐含用户名。要创建模式，调用该命令道用户必须拥有DBA权限。

例1：定义一个学生-课程模式S-T

 SQL Code 

create schema S-T authorization WANG;

例2：

 SQL Code 

create schema authorization WANG;

该语句没有指定模式名，因此模式名为用户名。

定义模式实际上定义了一个命名空间，在这个空间中可以进一步定义该模式包含的数据库对象，例如基本表、视图、索引等待。

例3：

 SQL Code 

1
2
3
4
5
6

create schema TEST authorization WANG
   create table TAB1(COL1 smallint,
                     COL2 int,
                     COL3 char(20),
                     COL4 numeric(10,3)
                     COL5 decimal(5,2));

该语句为用户WANG创建了一个模式TEST，并在其中定义了一个表TAB1

二、删除模式

drop schema <模式名> <cascade|restrict>

其中cascade和restrict两者必选其一。

cascade（级联）,表示在删除模式的同时把该模式中的所有数据对象全部一起删除。

restrict（限制），表示如果该模式中已经定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。只有当该模式中没有任何下属的对象时才能执行drop schema语句。

例4：

 SQL Code 

drop schema TEST cascade

该语句删除了模式TEST，同时该模式中已经定义了的表TAB1也被删除了。

3.3.2 基本表的定义和删除

一、定义基本表

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

SQL语言使用create table语句定义基本表，其基本格式如下：

create table <表名> （<列名><数据类型>[列级完整性约束条件],....）;

常用完整性约束

1、主码约束： PRIMARY KEY

2、唯一性约束：UNIQUE

3、非空值约束：NOTNULL

4、参照完整性约束：FOREIGN KEY

常用数据类型：（不同DBMS数据类型不完全相同）

例5：建立一个学生表Student

 SQL Code 

1
2
3
4
5
6
7

create table Student
         (Sno char(9) primary key, /*Sno 为主码*/
          Sname char(20) unique,   /*Sname 取唯一值*/
          Ssex char(2),            /*一个汉字占两个字节*/
          Sage smallint,
          Sdept char(20)
          );

例6：建立一个课程表Course

外码指在一个表中是主码另外一个表中不是主码，这样的字段我们把他定义为外码。

 SQL Code 

1
2
3
4
5
6
7
8

create table Course
         (Cno char(4) primary key, /*Cno 为主码*/
          Cname char(40),   /*Sname 取唯一值*/
          Cpno char(4),            /*一个汉字占两个字节,先修课*/
          Ccredit smallint,
          foreign key(Cpno) references Course(Cno)
          /*表级完整性约束条件，Cpno是外码，被参照表是Course，被参照列是Cno*/
          );

本例说明参照表和被参照表可以是同一个表。

例7：建立学生选课表SC

 SQL Code 

1
2
3
4
5
6
7
8
9
10
11
12

create table SC
         (Sno char(9) ,
          Cno char(4),
          Grade smallint,
          Ccredit smallint,
          primary key(Sno,Cno),
          /*主码由两个属性构成，必须作为表级完整性进行定义*/
          foreign key(Sno) references Student(Sno),
           /*表级完整性约束条件，Sno是外码，被参照表是Student*/
          foreign key(Cno) references Course(Cno)
          /*表级完整性约束条件，Cno是外码，被参照表是Course*/
          );

三、模式与表

每一个基本表都属于一个模式，一个模式包含多个基本表。当定义基本表如何定义它所属的模式呢？

方法一：在表名中显式的给出模式名：

 SQL Code 

1
2
3

create table "S-T".Student(...); /*Student所属的模式S-T*/
create table "S-T".Course(...);
create table "S-T".SC(...);

方法二：在创建模式语句中同时创建表。

方法三，设置所属的模式，这样在创建表时表名中不必给出模式名。

DBA用户设置搜索路径，例如：

 SQL Code 

set search_path to "S-T",public;

然后，定义基本表：

 SQL Code 

create table Student(...);

四、修改基本表

随着应用环境和应用需求的变化，有时需要修改已建立好的基本表，SQL语言用alter table语句修改基本表，其一般格式如下：

alter table <表名> [add <新列名> <数据类型> [完整性约束] ]

[drop <完整性约束名> ]

[alter column <列名> <数据类型> ]

其中<表名>是要修改的基本表，add子句用于增加新列和新的完整性约束条件，drop子句用于删除指定的完整性约束条件，alter column 子句用于修改原有列定义，包括修改列名和数据类型。

例8：向Student 表增加”入学时间“列，其数据类型为日期型。

 SQL Code 

alter table Student add s_entrance date;

不论基本表中原来是否已有数据，新增加的列一律为空值。

例9：将年龄的数据类型由字符型改成整数

 SQL Code 

alter table Student alter column Sage int;

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

 SQL Code 

alter table Course add unique(Cname);

五、删除基本表

当某个基本表不再需要时，可以使用drop table语句删除它。其一般格式为：

drop table <表名> [restrict|cascade];

若选择restrict：则该表的删除是有限制条件的，欲删除的基本表不能被其他表的约束所引用（如check、foreign key等约束），不能有视图、不能有触发器，不能有存储过程或函数。如果存在这些依赖该表的对象，则此对象不能被删除。

若选择cascade，该表的删除没有限制条件。在删除基本表的同时，相关的依赖对象，例如视图，将被一起删除。

缺省的是restrict

例11：删除Student表

 SQL Code 

drop table Student cascade

基本表定义一旦被删除，不仅表中的数据和此表的定义将被删除，而且此表上建立的索引，视图，触发器等有关对象议案也都被删除。因此要格外小心。

例12：若表上有视图，选择restrict 时表不能删除，cascade时可以删除表，不过视图也跟随被删除。

 SQL Code 

1
2
3
4
5
6
7
8

create view IS_Student as select Sno,Snmae,Sage from Student where Sdept='IS';
/*Student表上建立视图*/
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

不同数据库的drop table有不同的策略。

3.3.3 索引的建立与删除

建立索引是加快查询速度的有效手段，用户可以根据应用环境的需要，在基本表上建立一个或多个索引，以提供多种存取路径，加快查找速度。

一般来说，建立与删除索引由数据库管理员DBA或表的属主，即建立表的人，负责完成，系统在存取数据时会自动选择合适的索引作为存取路径，用户不必也不能显式的选择索引，一般自动建立以下列的索引：primary key和liniquer

一、建立索引

建立索引使用create index语句，一般格式为：

create [unique][cluster]index <索引名> on <表名>(<列名>[<次序>],<列名>[<次序>]...);

其中，<表名>是建立索引的基本表的名字，索引可以建立在该表的一列或多列上，各列名之间用逗号分隔，每个列名后面还可以用次序指定索引值的排列次序，可以选择asc（升序）或者desc（降序）,缺省是asc。

unique 表明此索引的每一个索引值只对应唯一的数据记录。

cluster 表示建立的索引是聚簇索引。所谓的聚簇索引就是索引项的顺序与表中记录的物理顺序一致的索引组织。

例13：create cluster index Stusname on Student(Sname);

将会在Student表的Sname(姓名)列上建立一个聚簇索引，而且Student表中的就将按照Sname值的升序存放。

用户可以在最经常查询的列上建立聚簇索引以提高查询效率。经常要更新的列不宜建立索引。

例14：为学生-课程数据库中的Student，Course，SC3个表建立索引，其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。

 SQL Code 

1
2
3

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

二、删除索引

索引已经建立，就有系统使用和维护，不需要用户干预。建立索引是为了减少查询操作的时间，但如果数据增删改频繁，系统会花费很多时间来维护索引，所以有时候需要删除不必要的索引。

drop index<索引名>

例15 删除Student 表中的Stusname索引。

drop index Stusname;

在RDBMS中索引一般采用B+树，hash索引来实现。B+树具有动态平衡的优点。hash索引具有查找速度快的特点。

3.4 数据查询

SQL提供了select语句进行数据库查询，该语句具有灵活的使用方式和丰富的功能，一般格式如下：

select [all | distinct] <目标列表达式>,<目标列表达式> ...

from <表名或视图名>,<表名或视图名>...

[where <条件表达式>]

[group by <列名1> [having <条件表达式>] ]

[order by <列名2> [asc | desc] ];

整个select语句的含义是，根据where 子句的条件表达式，从from子句指定的基本表或者视图中找出满足条件的元组，再按select子句中的目标列表达式，选出元组中的属性值形成结果表。

如果有group by子句，则将结果按<列名1>的值进行分组，该属性列值相等的元组为一个组，通常会在每组中作用聚集函数。如果group by子句带having短语，则只有满足指定条件的组才予以输出。

如果有order by子句，则结果表还要按<列名2>的值的升序或降序排序。

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

3.4.1 单表查询

一、选择表中的若干列

选择表中的全部列或者部分列，这就是关系代数的投影运算。

1.查询指定列

在很多情况下，用户只对表中的一部分属性列感兴趣，这时可以通过select子句的<目标列表达式>中指定要查询的属性列。

例1：查询全体学生的学号与姓名

 SQL Code 

select Sno,Sname from Student;

该语句的执行过程可以是这样的，从Student表中取出一个元组，取出该元组在属性Sno和Sname上的值，形成一个新的元组作为输出。对Student表中的所有元组做相同的处理，最后形成一个结果关系作为输出。

 SQL Code 

select Sno,Sname,Sdept from Student;

2.查询全部列

方法一：列出全部列名，方法二：使用*代替

例3：查询全体学生的详细记录

 SQL Code 

select * from Student;

3.查询经过计算的值

select子句的<目标列表达式> 不仅可以是表中的属性列，也可以是表达式。

例5：查询全体学生姓名、出生年份和所在的院系，要求用小写字母表示所有系名。

 SQL Code 

1
2

select Sname NAME,'Year of Birth:' BIRTH,2004-Sage BIRTHDAY,lower(Sdept) DEPARTMENT from Student;

结果如下：

NAME BIRTH BIRTHDAY DEPARTMENT
      ------ -------------------------------------------
      李勇  Year of Birth:    1976 cs
      刘晨  Year of Birth:    1977 cs
      王名  Year of Birth:    1978 ma
      张立  Year of Birth:    1978 is

二、选择表中的若干元组

1、消除取值重复的行

两个本来不完全相同的元组，投影到指定的某些列上后，可能变成相同的行了，可以用distinct取消他们。

去掉查询的重复结果可以如下：

 SQL Code 

1
2

select distinct Sno from SC;

如果没有distinct关键字，缺省的是all，把所有的结果显示出来。

2、查询满足条件的元组

查询满足指定条件的元组可以通过where子句实现。where子句常用的查询条件如下所示：

常用的查询条件
    查询条件                      谓词
    ───────     ───────────────
    比较(比较运算符)    = 、>、>=、<、<=、<>(!=)、NOT
    确定范围           BETWEEN AND, NOT BETWEEN AND
    确定集合           IN, NOT IN
    字符匹配           LIKE, NOT LIKE
    空值               IS NULL, IS NOT NULL
    多重条件           AND, OR

①比较大小
例11 查询计算机系全体学生的名单

 SQL Code 

SELECT Sname FROM Student WHERE Sdept = '计算机系';

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

 SQL Code 

1
2
3

SELECT Sname, Sage FROM Student WHERE Sage <20;
或 SELECT Sname, Sage FROM Student WHERE NOT Sage>= 20;

例13查询考试成绩有不及格的学生的学号

 SQL Code 

SELECT DISTINCT Sno FROM Course WHERE Grade <60;

    这里使用了DISTINCT短语，当一个学生有多门课程不及格，他的学号也只列一次。

    ② 确定范围
    例14 查询年龄在20至23岁之间的学生的姓名、系别、和年龄

 SQL Code 

SELECT Sname, Sdept, Sage FROM Student WHERE Sage BETWEEN 20 AND 23;

    与BETWEEN...AND...相对的谓词是NOT BETWEEN...AND...。
    例15 查询年龄不在20至23岁之间的学生姓名、系别和年龄

 SQL Code 

SELECT Sname, Sdept, Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23;

    ③确定集合
    例16 查询信息系、数学系和计算机系的学生的姓名和性别

 SQL Code 

SELECT Sname, Ssex FROM Student　WHERE Sdept IN ('信息系', '数学系', '计算机系')；

与IN相对的谓词是NOT IN，用于查找属性值不属于指定集合的元组。
例17 查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别

 SQL Code 

1
2

SELECT Sname, Ssex 　FROM Student 　
WHERE Sdept NOT IN ('信息系', '数学系', '计算机系')；

    ④字符匹配
    谓词LIKE可以用来进行字符串的匹配。其一般语法格式如下：
   [NOT] LIKE '<匹配串>'其含义是查找指定的属性列值与<匹配串>相匹配的元组。<匹配串>可以是一个完整的字符串，也可以含有以下通配符。
   %(百分号) 代表任意长度（长度可以为0）的字符串。
   _(下横线) 代表任意单个字符。
    [ ]：匹配[ ]中的任意一个字符。
   [^]：不匹配[ ]中的任意一个字符。
    例18 查所有姓刘的学生的姓名、学号和性别

 SQL Code 

SELECT Sname, Sno, Ssex FROM Student WHERE Sname LIKE '刘%';

例19 查名字中第二字为“大”或“小”的学生的姓名和学号

 SQL Code 

SELECT Sname, Sno FROM Student WHERE Sname LIKE '__[大小]%';

例20 查询所不姓刘的学生姓名

 SQL Code 

SELECT Sname, Sno, Ssex FROM Student WHERE Sname NOT LIKE '刘%';

例21 查询学号中最后一位不是2、3、5的学生情况

 SQL Code 

SELECT * FROM Student WHERE Sno LIKE ‘%[^235]’;

⑤ 涉及空值的查询
例23 某些学生选修某门课程后没有参加考试，所以有选课记录，但没有考试成绩，下面我们来查一下缺少成绩的学生的学号和相应的课程号

 SQL Code 

SELECT Sno, Cno FROM SC WHERE Grade IS NULL;

注意这里的'IS'不能用等号（'='）代替。

例24 查所有有成绩的记录的学生学号和课程号

 SQL Code 

SELECT Sno, Cno FROM SC WHERE Grade IS NOT NULL;

    ⑥多重条件查询
    逻辑运算符AND和OR可用来联结多个查询条件。如果这两个运算符同时出现在同一个WHERE条件子句中，则AND的优先级高于OR，但用户可以用括号改变优先级。
    例25 查询计算机系年龄在20岁以下的学生姓名

 SQL Code 

SELECT Sname FROM Student WHERE Sdept='计算机系' AND Sage<20;

例12 中的IN谓词实际上是多个OR运算符的缩写，因此例12中的查询也可以用OR运算符写成如下等价形式：

 SQL Code 

1
2

SELECT Sname, Ssex FROM Student
WHERE Sdept='信息系' OR Sdept='数学系' OR Sdept='计算机系';

  3．对查询结果排序
    如果没有指定查询结果的显示顺序，DBMS将按其最方便的顺序（通常是元组在表中的先后顺序）输出查询结果。用户也可以用ORDER BY子句指定按照一个或多个属性列的升序（ASC）或降序（DESC）重新排列查询结果，其中升序ASC为缺省值。
    例26 查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列

 SQL Code 

SELECT Sno, Grade FROM SC WHERE Cno='3' ORDER BY Grade DESC;

前面已经提到，可能有些学生选修了3号课程后没有参加考试，即成绩列为空值。用ORDER BY子句对查询结果按成绩排序时，若按升序排，成绩为空值的元组将最后显示，若按降序排，成绩为空值的元组将最先显示。
例27 查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列

 SQL Code 

1
2

SELECT * FROM Student ORDER BY Sdept, Sage DESC;

4．使用集函数
    为了进一步方便用户，增强检索功能，SQL提供了许多集函数，主要包括：
    COUNT([DISTINCT|ALL] *) 统计元组个数
    COUNT([DISTINCT|ALL] <列名>) 统计一列中值的个数
    SUM([DISTINCT|ALL] <列名>) 计算一列值的总和（此列必须是数值型）
    AVG([DISTINCT|ALL] <列名>) 计算一列值的平均值（此列必须是数值型）
    MAX([DISTINCT|ALL] <列名>) 求一列值中的最大值
    MIN([DISTINCT|ALL] <列名>) 求一列值中的最小值
    如果指定DISTINCT短语，则表示在计算时要取消指定列中的重复值。如果不指定DISTINCT短语或指定ALL短语（ALL为缺省值），则表示不取消重复值。
    例29 查询学生总人数

 SQL Code 

SELECT COUNT(*) FROM Student;

例30 查询选修了课程的学生人数

 SQL Code 

SELECT COUNT(DISTINCT Sno) FROM SC;

学生每选修一门课，在SC中都有一条相应的记录，而一个学生一般都要选修多门课程，为避免重复计算学生人数，必须在COUNT函数中用DISTINCT短语。
例31 计算1号课程的学生平均成绩

 SQL Code 

SELECT AVG(Grade) FROM SC WHERE Cno='1';

例32 查询选修1号课程的学生最高分数

 SQL Code 

SELECT MAX(Grade) FROM SC WHERE Cno='1';

例33 查询选修1号课程的学生最高分、最低分及平均分

 SQL Code 

SELECT MAX(Grade)，MIN(Grade)，AVG(Grade) FROM SC WHERE Cno='1';

    5．对查询结果分组
   GROUP BY子句可以将查询结果表的各行按一列或多列取值相等的原则进行分组。　　
    对查询结果分组的目的是为了细化集函数的作用对象。如果未对查询结果分组，集函数将作用于整个查询结果，即整个查询结果只有一个函数值。否则，集函数将作用于每一个组，即每一组都有一个函数值。
    例34 查询各个课程号与相应的选课人数

 SQL Code 

SELECT Cno, COUNT(Sno) FROM SC GROUP BY Cno;

    该SELECT语句对SC表按Cno的取值进行分组，所有具有相同Cno值的元组为一组，然后对每一组作用集函数COUNT以求得该组的学生人数。查询结果为：
      Cno　 COUNT(Sno)
    ------ ----------
       1       22
       2       34
       3       44
       4       33
       5       48
    如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用HAVING短语指定筛选条件。
    例35 查询信息系选修了3门以上课程的学生的学号

 SQL Code 

SELECT Sno FROM SC WHERE Sdept=‘信息系’ GROUP BY Sno HAVING COUNT(*)>3;

选修课程超过3门的信息系学生的学号，首先需要通过WHERE子句从基本表中求出信息系的学生。然后求其中每个学生选修了几门课，为此需要用GROUP BY子句按Sno进行分组，再用集函数COUNT对每一组计数。如果某一组的元组数目大于3，则表示此学生选修的课超过3门，应将他的学生号选出来。HAVING短语指定选择组的条件，只有满足条件（即元组个数>3）的组才会被选出来。
WHERE子句与HAVING短语的根本区别在于：作用对象不同，WHERE子句作用于基本表或视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组。

3.4.2 连接查询

一、内连接

　　内连接查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。内连接分三种：

　　1、等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。

　　2、不等连接： 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。

　　3、自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。
其一般格式为：

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

例33：查询每个学生及其选修课情况

学生情况存放在Student表中，学生选课情况存放在SC表中，所以本查询实际上设计Student与SC两个表。这两个表之间的联系是通过公共属性Sno实现的。

 SQL Code 

1
2

select Student.*,SC.* from Student,SC where Student.Sno=SC.Sno;
/*将Student与SC中同一个学生的元组连接起来*/

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

例34 用自然连接完成

 SQL Code 

1
2

select Student.Sno,Sno,Sname,Ssex,Sage,Sdept,Cno,Grade from Student,SC where Student.Sno=SC.Sno;
/*将Student与SC中同一个学生的元组连接起来*/

因为Sno在两个表都出现了，因此引用时必须加上表名的前缀。

二、自身连接

连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，称为表的自身连接。

例35：查询每一门课的间接先修课（即先修课的先修课）

这要将Coure表与其自身连接。

为此，要为Course表取两个别名，一个是first，另外一个是second

 SQL Code 

select first.Cno,second.Cpno from Course first,Course second where first.Cpno=second.Cno;

三、外连接

　1、left join或left outer join（左外连接）

　　左外连接：(也叫左外连接，给出两个表的配匹行，并且以左边的表为准，如果左边表有而右边表没有的行，则在右边表的相应行选择的列显示为NULL，允许左边的基准表对应右边表多条满足条件的记录)左连接就是返回左边的匹配行，不考虑右边的表是否有相应的行。

　　2、right join或right outer join（右外连接）

　　右连接：(也叫右外连接，给出两个表的配匹行，并且以右边的表为准，如果右边表有而左边表没有的行，则在右边表的相应行选择的列显示为NULL，允许右边的基准表对应左边表多条满足条件的记录)。

例36 改写例33

 SQL Code 

1
2
3
4

select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade from Student left out join SC on(Stduent.Sno=SC.Sno);
/*也可以使用using来去掉结果中的重复值：
*/
from Student left out join SC using (Sno);

四、复合条件连接

上面各个连接查询中，where子句中只有一个条件，即连接谓词。where子句中可以有多个连接条件，称为复合条件连接。

例37：查询选修2号课程且成绩在90分以上的所有学生。

 SQL Code 

1
2

select Student.Sno,Sname,from Student,SC
where Student.Sno=SC.Sno and SC.Cno='2' and SC.Grade>90; /*连接谓词限定条件*/

连接操作除了可以是两表连接，一个表与其自身连接外，还可以是两个以上的表进行连接，后者通常称为多表连接。

例38：查询每个学生的学号、姓名、选修的课程名及成绩。本查询设计3个表。

 SQL Code 

1
2

select Student.Sno,Sname,Cname,Grade from Student,SC,Course
where Student.Sno=SC.Sno and SC.Cno=Course.Cno;

3.4.3 嵌套查询

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

上层的查询块称为外层查询或者父查询，下层查询块称为内层查询或者子查询。

 SQL Code 

select Sname from Student where Sno in (select Sno from SC where Cno='2');

子查询的select语句中不能使用order by子句，order by子句只能对最终查询结果排序。

一、带有in谓词的子查询。

例39：查询与“刘晨”在同一个系学习的学生。

解法一：

 SQL Code 

1
2

select Sno,Sname,Sdept from Student where Sdept in
(select Sdept from Student where Sname='刘晨');

解法二：自身连接

 SQL Code 

1
2
3

select S1.Sno,S1.Sname,S1.Sdept from Student S1 Student S2
where S1.Sdept=S2.Sdept and S2.Sname='刘晨';

例40 查询选修了课程名为“信息系统”的学生学号和姓名。

 SQL Code 

1
2
3

select Sno,Sname from Student
where Sno in (select Sno from SC where Cno in
select Cno from Course where Cname='信息系统');

首先在Course关系中找出’信息系统的课程号‘，然后再SC关系中找出选修了这个课程的学生学号，最后在Student里面取出学号和姓名。

同样可以用连接查询来实现这个功能。

 SQL Code 

1
2

select Student.Sno,Sname from Student,SC,Course
where Student.Sno=SC.Sno and SC.Cno=Course.Cno and Course.Cname='信息系统';

二、带有比较运算符的子查询。

带有比较运算符的子查询是指父查询与子查询之间的连接。

 SQL Code 

1
2
3

select Sno,Sname,Sdept from Student
where Sdept=(select Sdept from Student where Sname='刘晨');

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

 SQL Code 

1
2

select Sno,Cno from SC x where Grade >=
(select avg(Grade) from SC y where y.Sno=x.Sno);

三、带有any(some)或者all谓词的子查询。

>ANY：大于子查询结果中的某个值

>ALL：大于子查询结果中的所有值

<ANY：小于子查询结果中的某个值

<ALL：小于子查询结果中的所有值

>=ANY：大于等于子查询结果中的某个值

<=ALL：大于等于子查询结果中的所有值

<=ANY：小于等于子查询结果中的某个值

<=ALL：小于等于子查旬结果中的所有值

=ANY：等于子查询结果中的某个值

=ALL：等于子查询结果中的所有值（通常没有实际意义）

!=(或<>)ANY：不等于子查询结果中的某个值

!=(或<>)ALL：不等于子查询结果中的任何一个值

例42 查询其他系中比计算机科学系某一学生年龄小的学生姓名和年龄。

 SQL Code 

1
2
3
4
5
6
7
8

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';

例43 查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。

 SQL Code 

1
2
3
4
5
6
7
8

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查询效率要高。

表3.5 ANY（或SOME），ALL谓词与聚集函数、IN谓词的等价转换关系

<>或!=

ANY

<MAX

<=MAX

>MIN

>= MIN

ALL

NOT IN

<MIN

<= MIN

>MAX

>= MAX

四、带有exists谓词的子查询

exists代表存在的意思。带有exists谓词的子查询不返回任何数据，只产生逻辑真值‘true’，或者‘false’.

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

本查询设计Student和SC表。可以在Student中依次取每个元组的Sno值，用此值去检查SC表。若SC表中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno=‘1’，则取此Student.Sname送人结果表。

 SQL Code 

1
2
3

select Sname from Student
where exists(select * from SC
where Sno=Student.Sno and Cno='1');

例45 查询没有选修1号课程的学生姓名。

 SQL Code 

1
2
3

select Sname from Student
where not exists(select * from SC
where Sno=Student.Sno and Cno='1');

例46 查询选修了全部课程的学生姓名。

利用exists/not exists来实现带全程量词的查询。

 SQL Code 

1
2
3

select Sname from Student
where not exists (select *from Course where not exists
(select * from SC where Sno=Student.Sno and Cno=Course.Cno));

3.4.4 集合查询

集合操作包括并操作union、交操作intersect和差操作except。

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

 SQL Code 

1
2

select * from Student where Sdept='CS'
union select * from Student where Sage<=19;

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

 SQL Code 

1
2

select * from Student where Sdept='CS'
intersect select * from Student where Sage<=19;

例子51：查询即选修了课程1又选修了课程2的学生。

 SQL Code 

1
2

select Sno from SC where Cno='1'
intersect select Sno from SC where Cno='2';

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

 SQL Code 

1
2

select * from Student where Sdept='CS'
except select * from Student where Sage<=19;

3.5 数据更新

3.5.1 插入数据

insert语句有两种形式，一种是插入一个元组，另一种是插入子查询的结果，后者可以掺入多个元组。

一、插入元组

例1：将一个新学生元组（学号，姓名，性别，所在系，年龄）插入到Student表中。没有的属性指明NULL。

 SQL Code 

1
2
3

insert into Student(Sno,Sname,Sdept,Sage)
values('200215128','陈冬','男','IS',18);
/*字符串常数要用单引号括起来*/

二、插入子查询结果

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

首先在数据库中建立一个新表，其中一个列存放系名，另一列存放相应的学生平均年龄。

 SQL Code 

create table Dept_age(Sdept char(15),Avg_age smallint);

然后对Student按照系分组求平均年龄，再把系名和平均年龄存入新表中。

 SQL Code 

1
2

insert into Dept_age(Sdept,Avg_age)
select Sdept,avg(Sage) from Student group by Sdept;

3.5.2 修改数据

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

update <表名>

set <列名> = <表达式> ，...

[where <条件>];

其功能是修改制定表中满足where子句条件的元组。其中set子句给出<表达式>的值用于取代相应的属性列表值。如果省略where子句，则表示要修改表中的所有元组。

一、修改某一个元组的值。

例5：将学生200215121的年龄改成22岁。

 SQL Code 

update Student set Sage=22 where Sno='200215121';

二、修改多个元组的值。

例6：将所有学生的年龄增加1岁。

 SQL Code 

update Student set Sage=Sage+1;

三、带子查询的修改语句。

例7：将计算机科学系全体学生的成绩置为零。

 SQL Code 

1
2

update SC set Grade=0 where 'CS'=
(select Sdept from Student where Student.Sno=SC.Sno);

3.5.3 删除数据

删除语句的一般格式为：

delete from <表名> [where <条件>];

delete语句的功能是从指定表中删除满足where子句条件的所有元组。如果省略where子句，表示删除表中全部元组，但表的定义仍在字典中。也就是说，delete语句删除的是表中的数据，而不是关于表的定义。

一、删除某一个元组。

例8：删除学号为200215128的学生记录。

 SQL Code 

1
2

delete from Student where Sno='200215128';

二、删除多个元组的值

例9：删除所有的学生选课记录。

 SQL Code 

1
2

delete from SC;

三、带有子查询的删除语句。

例10 删除计算机科学系所有学生的选课记录。

 SQL Code 

1
2
3

delete from SC where 'CS'=
(select Sdept from Student where Student.Sno=SC.Sno);

3.6 视图

视图是从一个或者几个基本表导出的表。它和基本表不同，是一个虚表。数据库中值存放视图的定义，而不存放视图对应的数据。

视图一经定义，就可以和基本表一样被查询、被删除。也可以在一个视图上再定义新的视图，但对视图的更新操作则有一定的限制。

3.6.1 定义视图

一、建立视图

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

 SQL Code 

1
2
3

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

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

 SQL Code 

1
2
3

create view IS_Student as
select Sno,Sname,Sage from Student
where Sdept='IS' with check option;

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

 SQL Code 

1
2

create view S_G(Sno,Gavg) as
select Sno,avg(Grade) from SC group by Sno;

二、删除视图

 SQL Code 

1
2

drop view BT_S;
drop view IS_S1;

级联删除：

 SQL Code 

drop view IS_S1 cascade;

3.6.2 查询视图

视图定义后，用户就可以像对基本表一样对视图进行查询。

3.6.3 更新视图

更新视图是指通过视图来插入(insert)、删除(delete)和修改(update)数据。

为了防止用户通过视图对数据进行增加、删除、修改时，有意无意的对不属于视图范围内的基本表数据进行操作，可以在定义视图时加上with check option子句。这样在视图上增删查改数据时，会检查是否满足条件。

和基本表的类似。

0 0