第4章 结构化查询语言—SQL

4.1 数据库模式的建立和删除

4.1.1 建立数据库模式（P73）

  create database 教学库

4.1.2 删除数据库模式（P74）

  drop database 教学库

4.2 表结构的建立、修改和删除

4.2.1 建立表结构（P74）

  语句格式：

  CREATE TABLE [数据库名.]<基本表名>

         (<列定义>,...[,<表级完整性约束>,...])

 1） 列级完整性约束

  DEFAULT<常量表达式>：默认值约束。

  NULL/NOT NULL：空值/非空值约束。

  PRIMARY KEY：主码约束。

  UNIQUE：单值约束。注明该列取值互不相同。

  REFERENCES<父表名>(<主码>)：外码约束。

注明该列为外码，并给出对应的父表及父表中被参照的主码。

  CHECK(<逻辑表达式>)：检查约束。如“性别='男'or 性别='女'”

 2）表级完整性约束在所有列定义后给出。

  PRIMARY KEY(<列名>,...)主码约束。注明一个或同时多个列为主码。

  UNIQUE：单值约束。注明一个或同时若干个列为单值。

  FOREIGN KEY(<列名>,...) REFERENCES<父表名>(<主码列名>,...)

外码约束。注明一个或同时多个列为外码，

并给出对应的父表及父表中被参照的主码中的所有列。

  CHECK(<逻辑表达式>)：检查约束。

注明每行中一个或若干个列在取值上必须满足的条件。

如，check(工龄<年龄)。

 语句举例：

  create table 学生（

      学号 char(7) primary key,

      姓名 char(6) not null unique,

      性别 char(2) not null check(性别='男'or 性别='女'),

      出生日期 datetime check(出生日期<'1993-12-31'),

      专业 char(10),

      年级 int check(年级>=1 and 年级<=4)

    )

4.2.2 修改表结构（P77）

  alter table 学生 add 籍贯 char(6) 

  alter table 学生 drop column 籍贯

4.2.3 删除表结构（P78）

  drop table 学生1

4.3 表内容的插入、修改和删除

4.3.1 插入记录（P78）

 1）插入单行记录

  create table 职工（

      职工号 char(6) primary key,

      姓名 char(8) not null,

      性别 char(2) not null,

      年龄 int,

      基本工资 float,

    )

  insert into 职工(职工号,姓名,性别,年龄,基本工资)

           values('010405','吕口品','女',28,1960)

 1）插入多行记录

  设职工 1 有多行记录。

  insert into 职工(职工号,姓名,性别,年龄,基本工资)

       select 职工号,姓名,性别,年龄,基本工资

       from 职工 1 where 性别='男'

4.3.2 修改记录（P80）

  按条件修改一个表中的一些列的值。

 1）update 职工 set 年龄=年龄+1

 2）update 职工 set 基本工资=职工1.基本工资+职工1.职务津贴

    from 职工1 where 职工.职工号=职工1.职工号

 3）update 职工 set 基本工资=基本工资*1.2

    from 职工号='010405'

4.3.3 删除记录（P81）

    删除一个表中满足条件的所有行。

 1）delete from 职工 where 年龄>45

 2）delete 职工 from 职工1 where  职工.职工号=职工1.职工号

  删除职工表中与职工表1中职工号相同的记录。

4.4 视图的建立、修改和删除

  视图对应一个，或多个基本表。

4.4.1 建立视图（P82）

  在当前数据库中根据 SELECT 子名的查询结果建立一个视图，

包括视图的结构和内容。

语句举例：教学库中建立学生，课程，选课三个基本表。

 学生基本表：学号、姓名、性别、专业。

 课程基本表：课程号、课程名、学分。

 选课基本表：学号、课程号、成绩。

 在三个基本表上建立成绩视图表

 学号、姓名、课程号、课程名、成绩。

 create view 成绩视图表(学号,姓名,课程号,课程名,成绩)

 as select 选课.学号,姓名,选课.课程号,课程名,成绩

 from 学生，课程，选课

 where 学生.学号=选课.学号 and 课程.课程号=选课.课程号

 and 专业='电子'

  数据库规范化：把关系逐步分解，视图：把分解的关系按需要连接。

4.4.2 修改视图内容（P84）

  按照一定条件对当前或指定数据库中的一些列值进行修改。

语句举例：

  update 成绩视图表 set 成绩=80

      where 学号='0102005 and 课程号='E002'

4.4.3 修改视图定义（P85）

语句举例：

 1）create view 学生视图（学号，姓名）

    as select 学号，姓名 from 学生

 2）alter view 学生视图（学号，专业）

    as select 学号，专业 from 学生

4.4.4 删除视图

  drop view 成绩视图表

4.5 SQL 查询

4.5.1 SELECT 语句格式（P86）

  SELECT ……投影运算，投影所需要的列。

  INTO ……建立新表

  FROM ……AS……连接和选择运算

  WHERE ……选择运算，选择所需要的行。

  GROUP BY ……分组统计

  HAVING …… 分组统计后筛选

  ORDER BY …… 排序

4.5.2 SELECT 选项

  SELECT [ALL|DISTINCT]{<表达式>[[AS]<列名>]},……

  SELECT：投影所需要的列。

  ALL：查询结果，允许出现内容重复的记录。

  DISTINCT：查询结果，只要内容不重复的记录。

  表达式计算结果存入新列名所指的字段中。

 1.商品库有两个基本表。

  商品表 1（商品代号，分类名，单价，数量）

  商品表 2（商品代号，产地，品牌）

 2.教学库有学生，课程，选课三个基本表。

 学生基本表：学号、姓名、性别、专业。

 课程基本表：课程号、课程名、学分。

 选课基本表：学号、课程号、成绩。

例 4-1 查出分类名。

  SELECT 分类名 FROM 商品表 1

例 4-2 查出不同分类名。

  SELECT DISTINCT 分类名 FROM 商品表 1

例 4-3 查出分类名为“电视机”的商品。

  select *

  from 商品表 1

  where 分类名=‘电视机’

例 4-4 查出单价<2000元的商品代号,分类名,单价。

  select 商品代号,分类名,单价

  from 商品表 1

  where 单价<2000元

例 4-5 查出每种商品价值。

  表达式计算结果存入新列名所指的字段中。

  select 商品代号,单价*数量 as 价值

  from 商品表 1

例 4-6 从商品表1 和商品表2 中查出按商品代号自然连接的结果。

  select 商品表1.*,产地,品牌

  from 商品表1,商品表2

  where 商品表1.商品代号=商品表2.商品代号

  查询结果：商品代号,分类名,单价,数量,产地,品牌

例 4-7 从商品表1 查出不同分类名的个数。

  select count(distinct 分类名) as 分类种数

  from 商品表 1

例 4-8 从商品表1 查出所有商品的

最大数量,最小数量,平均数量及数量总和。

  select max(数量) as 最大数量, min(数量) as 最小数量, 

         avg(数量) as 平均数量, sum(数量) as 总和

  from 商品表 1

例 4-9 从商品表1 查出分类名为“电视机”的

商品的种数,最高价,最低价及平均价。

  select count(*) as 种数,max(单价) as 最高价,

        min(单价) as 最低价,avg(单价) as 平均价

  from 商品表 1

  where 分类名=‘电视机’

例 4-10 从商品表1 查出所有商品的最高价值,最低价值及总价值。

  select max(单价*数量) as 最高价值,min(单价*数量) as 最低价值,

         sum(单价*数量) as 总价值

  from 商品表 1

4.5.3 FROM 选项（P92）

  FROM <表名>[[AS]<表别名>],……

  按指定表名查询，同时为该表起别名。

  例 4-11 连接教学库的学生，课程，选课三个基本表，

再选择所需要的列。

  select x.学号,x.姓名,y.课程号,y.课程名,z.成绩

  from 学生 x,课程 y,选课 z

  where x.学号=z.学号 and y.课程号=z.课程号

4.5.4 WHERE 选项（P93）

  WHERE <逻辑表达式>

  指定源表之间的连接条件和对记录的筛选条件，选择所需要的行。

  例 4-12 查询出单价>1500 and 数量>=10

  select 商品代号，单价，数量

  from 商品表1

  where 单价>1500 and 数量>=10

  例 4-13 查询出产地='南京' or 产地='无锡'

  select x.商品代号，分类名，产地，品牌

  from 商品表1 as x, 商品表2 as y

  where x.商品代号=y.商品代号

  and (产地='南京' or 产地='无锡')

  例 4-14 查询出选课至少两门的学号

  select distinct c1.学号

  from 选课 c1,选课 c2

  where c1.学号=c2.学号 and c1.课程号=c2.课程号

  例 4-15 查询出选修“操作系统”的学生。

  select 姓名

  from 学生 x,课程 y,选课 z

  where x.学号=z.学号 and y.课程号=z.课程号

    and 课程名=‘操作系统’

  例 4-16 查询出学生选课情况

  左表：连接中的第 1 个表，

  右表：连接中的第 2 个表，

  中间表：连接形成的新表。

  中间连接（FROM … INNER JOIN … ON）：连接形成中间表。

  左连接（FROM … LEFT JOIN … ON）：

连接形成的中间表中，还包含左表中没有形成连接的元组。

  右连接（FROM … RIGHT JOIN … ON）：

连接形成的中间表中，还包含右表中没有形成连接的元组。

  用于查询语句中的专门比较式的六种格式：

 1.格式之一

  <列名><比较符>ALL(<子查询>)

  要求<子查询>的查询结果中每一个值都满足所给的比较条件。

  例 4-17 

  select *

  from 商品表1

  where 单价>all

 (select 单价 from 商品表1 where 分类名='洗衣机')

 2.格式之二

  <列名><比较符>{ANY|SOME}(<子查询>)

  要求<子查询>的查询结果中任一个值满足所给的比较条件。

  例 4-19 

  select x.商品代号，x.分类名，y.产地，y.品牌

  from 商品表1 x inner join 商品表2 y

    on x.商品代号=y.商品代号

  where 产地=some(select 产地 from 商品表1 where 品牌='春兰'）

 3.格式之三

  <列名>[NOT] BETWEEN<开始值>AND<结束值>

  要求列的当前值在<开始值>和<结束值>之间。

  例 4-22 单价在 1000 到 2000 元之间。

  select *

  from 商品表1

  where 单价 between 1000 and 2000

 4.格式之四

  [NOT] EXISTS（<子查询>）

  要求<子查询>的查询结果中至少存在着一个元组。

  例 4-23 查询出选修至少一门课程的学生。

  select *

  from 学生

  where exists(select * from 选课 where 选课.学号=学生.学号)

 5.格式之五

  <列名>[NOT][IN]{(<常量表>)|(<子查询>)}

  要求列的当前值在常量表所给定的值之内或在子查询结果之中。

  例 4-26 查询出专业为计算机,电气,通信的学生。

  select *

  from 学生

  where 专业 in('计算机','电气','通信')

 6.格式之六

  <字符型列名>[NOT] LIKE<字符表达式>

  要求列的当前值与<字符表达式>的值相匹配。

  <字符表达式>中

 （_）表示能和任何一个字符匹配。

 （%）表示能和任意多个字符匹配。

  例 4-28 查询出商品代号 "dsj" 开头的商品。

  select *

  from 商品表1

  where 商品代号 like 'dsj%'

4.5.5 GROUP BY 选项（P104）

  GROUP BY <分组列名>

  对列值相同的进行分组和统计。

  例 4-29 查询出每个专业的学生人数。

  select 专业 as 专业名, count(专业) as 学生数

  from 学生

  group by 专业

4.5.6 HAVING 选项（P105）

  HAVING <逻辑表达式>

  从分组统计中筛选部分统计结果。

  例 4-32 查询出专业的学生数多于 1 人的专业名及人数。

  select 专业 as 专业名, count(专业) as 学生数

  from 学生

  group by 专业

  having count(专业)>1

4.5.7 ORDER BY 选项（P106）

  ORDER BY <排序列名>[ASC|DESC]

  按排序列名进行升序或降序排列。

  例 4-35 按单价升序查询商品。

  select *

  from 商品表1

  order by 单价

4.5.8 INTO 选项（P108）

  INTO <基本表名>

  根据查询结果建立新表。

  例 2-39 查询学生选课门数，统计总成绩。

  select x.学号,count(x.学号) as 选课门数,sum(成绩) as 总成绩

  into 学生选课门数及总成绩统计表

  from 学生 x,选课 z

  where x.学号=z.学号

第5章 数据库应用系统设计

5.1 数据库应用系统开发过程概述（P114）

  数据库应用系统开发过程的六个阶段：

  需求分析、概念设计、逻辑设计、物理设计、机器实现、运行维护。

5.1.1 需求分析阶段（P114）

  需求分析阶段：画出数据流图，建立数据字典和编写需求说明书。

  数据流图：描述实际业务管理系统工作流程的简图。

  数据字典：对系统工作流程中数据和处理的描述。

  需求说明书：系统总体设计方案，划分系统边界。

确定计算机和人工完成范围。

5.1.2 概念设计阶段（P115）

  单方的主码对应多方的外码。

5.1.3 逻辑设计和物理设计阶段（P116）

  物理设计阶段：关系数据库系统能够自动

把用户设计好的数据库全局模式转换为内模式。

  逻辑设计阶段：关系数据库中的各个外模式都是在基本表上。

  购物系统中建立三个基本表：

  cteate table 商品表（

        商品代号 char(8) primary key,

        名称 char(8) not null,

        品牌 char(6),

        体积 char(14),

        颜色 char(6),

        单价 float check(单价>0) not null,

        数量 int check (数量>=0) default 0

   ）

  cteate table 销售表（

        商品代号 char(8) not null,

        名称 char(8) not null,

        品牌 char(6),

        体积 char(14),

        颜色 char(6),

        单价 float check(单价>0) not null,

        数量 int check (数量>=0) default 0

        foreign key （商品代号) references 商品表（商品代号)

   ）

  表（商品代号、单价、数量、）

  cteate table 收款表（

        商品代号 char(8) not null,

        单价 float check(单价>0) not null,

        数量 int check (数量>=0) default 0

        总价 float check (总价>=0) not null,

        foreign key （商品代号) references 商品表（商品代号)

   ）

  顾客购买商品的商品代号和购买数量分别存入变量 @x 和 @y 中。

  向销售表插入记录

  insert into 销售表（商品代号,名称,品牌,体积,颜色,单价,数量)

       select 商品代号,名称,品牌,体积,颜色,单价,@y

       from 商品表

       where 商品代号 = @x

5.1.4 机器实现阶段（P119）

5.1.5 运行维护阶段

5.2 数据库应用系统设计应用举例

5.2.1 需求分析（P120）

  旅店管理的数据表：旅客登记表，客房表，客房空闲表，消费卡表，

住宿表，住宿费表，伙食费表，娱乐费表。

  旅店管理的处理要求：给旅客建立消费卡，向消费卡追加资金，

给旅客登记住房，伙食消费，娱乐消费，结算处理。

5.2.2 概念设计（P122）

5.2.3 逻辑设计和物理设计（P122）

  用 SQL 语言定义基本表和视图的结构

  基本表：客房表、住宿表、消费卡表、旅客登记表。

  视图：客房空闲表、住宿费表、伙食费表、娱乐费表、催补款表。

5.2.4 机器实现（P124）

 1）给旅客建立消费卡

   inser into 消费卡表（卡号，资金总额，余额）

       values(@a,@x,@x)

   变量 @a 已经保存消费卡卡号，

   变量 @x 已经保存旅客的预交金额。

 8）结算处理（P127）

  第一步修改客房表，变量 @a 中保存着退房者的卡号。

    update 客房表

       set 已用=0

       from 住宿表

       where 客房表.房序号=住宿表.房序号 and 住宿表.卡号= @a

  第二步删除住宿表中的卡号值等于 @a 的值的所有记录。

    delete from 住宿表

    where 卡号= @a

  第三步退款并从消费卡表中删除对应记录。

    delete from 消费卡表

    where 卡号= @a

  第四步从旅客登记表中删除卡号值等于 @a 的旅客记录。

    delete from 旅客登记表

    where 卡号= @a