1 / 54

（一）教学内容 1. SQL 语言的特性 综合统一、高度非过程化、集合操作、自含与宿主、 SQL 结构 2. 数据库操作

第四章 结构化查询语言 SQL. （一）教学内容 1. SQL 语言的特性 综合统一、高度非过程化、集合操作、自含与宿主、 SQL 结构 2. 数据库操作 库的创建与撤消，表结构的创建、修改与撤消，表内容的插入、修改与删除，视图的创建与撤消 3. 数据库查询 单表查询、多表连接查询、多表嵌套查询、分组查询、按序查询、统计查询、谓词查询等. 了解本章所有概念的含义；掌握表、视图的创建与撤消的语句格式及功能；掌握 数据的插入、删除和修改的语句格式及功能； 熟练掌握库查询语句 SELECT 的各种格式和相应功能。. 前言.

rowan-mcconnell
Download Presentation

（一）教学内容 1. SQL 语言的特性 综合统一、高度非过程化、集合操作、自含与宿主、 SQL 结构 2. 数据库操作

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 第四章 结构化查询语言SQL （一）教学内容 1. SQL语言的特性 综合统一、高度非过程化、集合操作、自含与宿主、SQL结构 2. 数据库操作 库的创建与撤消，表结构的创建、修改与撤消，表内容的插入、修改与删除，视图的创建与撤消 3. 数据库查询 单表查询、多表连接查询、多表嵌套查询、分组查询、按序查询、统计查询、谓词查询等

  2. 了解本章所有概念的含义；掌握表、视图的创建与撤消的语句格式及功能；掌握数据的插入、删除和修改的语句格式及功能；熟练掌握库查询语句SELECT的各种格式和相应功能。了解本章所有概念的含义；掌握表、视图的创建与撤消的语句格式及功能；掌握数据的插入、删除和修改的语句格式及功能；熟练掌握库查询语句SELECT的各种格式和相应功能。

  3. 前言 SQL是structured query language 的缩写，产生于1986，1989年SQL89，1992年SQL2,1999年的SQL3,是关系数据库语言的国际标准. SQL具有数据库定义、数据操纵和数据控制等丰富功能。用户通过命令交互方式和程序执行方式使用命令能够实现相应功能。目前还有窗口界面方式 SQL的数据定义功能够定义数据库的三级模式。 SQL的数据操纵功能包括对基本表和视图的数据查询、插入、删除和修改。它是一种高度非过程化的面向集合的语言。 SQL的数据控制功能主要是对用户权限加以控制，以保证系统的安全性。哪些用户能使用哪些数据、使用数据库中的哪些表和视图、具有哪些操作功能等都是访问权限要规定的内容。

  4. SQL是结构化查询语言的缩写，它包括查询、定义、操纵、和控制四个部分，是一种功能齐全的数据库语言SQL是结构化查询语言的缩写，它包括查询、定义、操纵、和控制四个部分，是一种功能齐全的数据库语言

  5. 4.1数据库模式的建立和删除 4.1.1建立数据库模式 CREATE {SCHEMA│DATABASE } 〈数据库名〉[AUTHORIZATION] <所有者名> 语名举例： （1）creat schema xuesh authorization (2) create database 教学库 4.1.2删除数据库模式

  6. 4.2 表结构的建立、修除和删除 4.2.1 建立表结构 定义一个基本表相当于建立一个新的关系模式，SQL定义表的命令格式如下： Create table [<数据库名>.<所有者名>.]<基本表名> (<列定义） ,…..,….[,<表级完整性约束>,……,…]) 列级完整性定义： 表级完整性定义 语名举例：

  7. 列级完整性约束有六种： 1.DEFAULT:默认值约束 2.NULL/NOT NULL空值、非空值约束 3.PRIMARY KEY主码约束 4.UNIQUE单值约束 5.REFERENCES外码约束 6.CHECK检查约束 表级完整性约束有四种： PRIMARY KEY,UNIQUE,FOREIGN KEY,CHECK

  8. (1)creat 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年级<=4) )

  9. (2)creat table 课程( 课程号 char (4) primary key, 课程名 char (10) not null unique, 课程学分 int check (课程学分 >=land课程学分 <=6)

  10. (3)create table　选课（ 学生号　Char(7)， 课程号　Char(4)， 成绩　Int check（成绩>=0 and 成绩<=100), Primary key (学生号，课程号）， Foreign key（学生号） references　学生（学生号） Foreign key（课程号） references 课程（课程号）

  11. 修改表的结构 语名格式： ALTER TABLE[<数据库名>.<所用者名>.]<基本表名> 　{ADD　<列定义>,……|ADD<表级完整约束>,…… | DROP COLUMN<列名>,….|DROP<约束名>,….. 语名举例： （1）alter table 学生 add 籍贯　char (6) （2）alter table 学生 drop column 籍贯

  12. 删除表的结构 语句格式 DROP TABLE [<数据库名>.<所有者名>.]<基本表名> 语名举例： Drop table 学生1

  13. 4.3表内容的插入、修改和删除 4.3.1插入记录 单行插入INSERT[INTO][<数据库>.<所有者名>.] <基本表名>(<列名>，……)VALUES(<列值>，……) 多行插入INSERT[INTO][<数据库>.<所有者名>.] <基本表名>(<列名>，……)<SELECT子句> 语句举例 P79

  14. 4.3表内容的插入、修改和删除 4.3.2修改记录 Update[<数据库名>.<所有者名>.]<目的表名> SET <列名>=<表达式>,……[FROM<源表名>,….][WHERE<逻辑表达式>] 语名举例： （1）UPDATE 职工SET 年龄=年龄+1 （2）UPDATA 职工SET 基本工资=职工1.基本工资+职工1. 职务津贴　FROM 职工1　WHERE 职工.职工号=职工1.职工号 （3）UPDADE 职工　SET 基本工资=基本工资*1.2

  15. 4.3表内容的插入、修改和删除 4.3.3删除记录 语句格式： DELETE[FROM][<数据库名>.<>所有者名.]<目的表名> [FROM<源表名>,……][WHERE<逻辑表达式>] 语句举例： （1）DELE FROM 职工　WHERE 年龄>45 (2)delete 职工　FROM 职工1　WHERE 职工号=职工1.职工号 （3）DELETE 职工

  16. 4.4视图的建立、修改和删除 视图的定义：是在基本表之上建立的表，它的结构和内容都来自基本表，它依据基本表存在而存在。广义地说，视图可以在任何一个或多个表上建立，这些表包括基本表和逻辑表的视图在内。 在数据库三级模式结构中，基本表属于全局模式中的表，它是实表，而视图则属于局部模式中的表，它是虚表。视图的建立和删除只影响视图本身，不影响对应的基本表，而对视图内容的更新（插入、删除和修改）直接影响基本表。当视图来自多个基本表时，通常只允许对视图做适当的修改，不允许做插入和删除数据的操作。对视图做的最多的是查询

  17. 4.4.1建立视图 语名格式： CREATE VIEW<视图名>(<列名>,…..)AS<SELECT 子句> 例P83-89 CREATE 成绩视图表（学生号，姓名，课程号，课程名，成绩） AS SELECT 选课.学生号，姓名，选课.课程号，课程名，成绩 FROM 学生，课程，选课 WHERE 学生.学生号=选课.学生号AND 课程.课程号=选课.课程号 AND 专业=‘电子’

  18. CREATE VIEW RESULT VIEW(学生，姓名，性别，语文，数学，英语，物理，生物，化学，平均成绩，总成绩） AS SELECT (学生，姓名，性别，语文，数学，英语，物理，生物，化学，（语文+数学+英语+物理+生物+化学）/6, 语文+数学+英语+物理+生物+化学） FROM RESULT

  19. 4.4.2修改视图内容 语名格式：UPDATE [<数据库名><所有者名>.]<视图名> SET<列名>=<表达式>,…..[FROM<源表名>,….] WHERE<逻辑表达式>] 语名举例： UPDATE 成绩视图SET成绩=80 WHERE学生号=‘0102005’AND课程号=‘E002’

  20. 4.4.2修改视图定义 语句格式：ALTER VIEW <视图名>(<列名>,….)AS <SELECT子句> 语句举例： （1)CREATE VIEW 学生视图（学生号，姓名） AS SELE 学生号，姓名FROM学生 (2)ALTER VIEW 学生号（学生号，专业） AS SELECT 学生号，专业　FROM 学生 4.4.3删除视图 DROP VIEW<视图>语句举例：DROP VIEW 成绩视图表

  21. 4.5 SQL查询 4.5.1 SELECT语句格式 SQL—SELECT基本查询模块的结构 Select [ALL | DISTINCT]{<表达式1>[[AS]<列名1>] [， <表达式2>[[AS]<列名2>]….|*| <表别名.*|} [INTO <基本表名>] From <表名1>[[AS]<表别名1>， <表名2> [[AS]<表别名1> ，… [ Where <条件表达式>] [GROUP BY <分组列名1>[，<分组列名2>…..] [HAVING<逻辑表达式2>] [ORDER BY<排序列名1>[ASC |DESC][,<排序列名2>[ASC | DESC]……

  22. 4.5.2SELECT选项 在这一节中用到的数所库有 商品库 　　　商品表1（商品代号，分类名，单价，数量） 　　　　　　　　内容见教材P87　　　 　　　商品表2（商品代号，产地，品牌） 　　　　　　　　内容见教材P87 教学库 　　　学生（学生号，姓名，性别，专业） 　　　课程（课程号，课程名，课程学分） 　　　选课（学生号，课程号，成绩） 　　　　　内容见教材P87 ，P83　

  23. 4.5.2SELECT选项(2) 例4-1从商品库的商品表1中查询出每种商品的分类名 SELECT 分类名 FROM　商品表1 例4-2从商品库的商品表1中查询出所有商品的不同分类名 SELECT 　DISTINCT 分类名 FROM　商品表1 在SELECT语句中使用的列函数有 COUNT([ALL | DISTINCT]<列名>　 | *） MAX(<列名>） MIN (<列名>） AVG (<列名>） SUM (<列名>）

  24. 4.5.2SELECT选项(3) 例4-3从商品表1中查询出分类名为“电视机”的所有商品 SELECT *; FROM 商品表1; WHERE 分类名=‘电视机’ 例4-4从商品表1中查询出单价低于2000元的每一种商品的商品代号、分类名和单价 SELECT 商品代号，分类名，单价 FROM 商品表1 WHERE 单价<2000

  25. 4.5.2SELECT选项(4) 例4-5从商品表1中查询出每一种商品的价值 Select 商品代号，单价*数量 as价值 From　商品表1 例4-6从商品表1中查询出不同分类名的个数。 SELECT COUNT(DISTINCT 分类名）AS 分类种数 FROM 商品表1 例4-8从商品表1中查询出所有商品的最大量、最小数量、平均数量及数量总和。 SELECT MAX(数量）AS 最大数量，MIN(数量）AS 最小数量，AVG(数量）AS 平均数量，SUM（数量）AS 总和

  26. 4.5.2SELECT选项(5) 例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

  27. 4.5.3 FROM选项 例4-11从教学库中查询出每个学生选修每门课程的学生号、姓名、课程号、课程名、成绩等数据 方法（一） SELECT X.学生号,X.姓名,Y.课程号,Y.课程名,Z.成绩 FROM　学生 X,课程　Y,选课　Z WHERE X.学生号=Z.学生号　AND　Y.课程号=Z.课程号 方法（二） SELECT学生.学生号,.学生姓名,课程.课程号,课程.课程名,选课.成绩 FROM　学生,课程　,选课 WHERE学生.学生号=选课.学生号AND课程.课程号=选课.课程号

  28. 4.5.4 WHERE 选项(1) 例4-12从商品表1中查询出单价大于1500，同时数量大于等于10的商品。 SELECT 商品代号，单价，数量 FROM　商品表1 WHERE 单价>1500 AND 数量>=10 例4-13从商品库中查询出产地为南京或无锡的所有商品的商品代号、分类名、产地和品牌。 SELECT X.商品代号，分类名，产地，品牌 FROM 商品表1　AS X，商品表2 AS Y WHERE X.商品代号=Y.商品代号 AND （产地=‘商品’ OR　产地=‘无锡’）　 94

  29. 4.5.4 WHERE 选项(2) 例4-14从教学库中查询出选修至少两门课程的学生学号 SELECT DISTINCT C1.学生号 FROM　选课C1，选课C2 WHERE C1.学生号=C2.学生号 AND C1.课程号<>C2.课程号 例4-15从教学库中查询出选修了课程名为“操作系统”课程的每个学生的姓名 SELECT 姓名 FROM 学生 X，课程 Y,　选课 Z WHERW X.学生号=Z.学生号 AND Y.课程号=Z.课程号 AND 课程名=‘操作系统’ 94.95

  30. 4.5.4 WHERE 选项(3) 中间连接 FROM<表名1>INNER JOIN<表名2> ON <表名1>.<连接列名1><比较符><表名2>.<连接列名2> 左连接 FROM<表名1> LEFT JOIN<表名2> ON <表名1>.<连接列名1><比较符><表名2>.<连接列名2> 右连接 FROM<表名1> RIGHT JOIN<表名2> ON <表名1>.<连接列名1><比较符><表名2>.<连接列名2> 95

  31. 4.5.4 WHERE 选项(4) 例4-16从教学库中查询出所有学生的选课情况，要求没选修任 何课程的学生信息也要反映出来 SELECT * FROM 学生　LEFT JOIN　选课 ON　学生.学生号=选课.学生号 若要查询出所有课程被学生选修的情况: Select * From 课程　left join(选课　inner join学生 on学生.学生号=选课.学生号） on 课程.课程号=选课.课程号 若要从教学库中查询出所有电子专业的学生选课的全部情况 Select * From 课程　inner join (选课inner join 学生　on 学生.学生号=选课.学生号）on 课程.课程号=选课.课程号 Where 专业=‘电子’ 97

  32. 4.5.4 WHERE 选项(5) 用于查询语句中的专门比较式又叫判断式，它实现单值与集合数据之间的比较。 1.格式之一 <列名><比较符>ALL（<子查询>） 例4-17 select * From 商品表1　where 单价>all(select 单价　from 商品表1　where 分类名=‘洗衣机’ 例4-18　select x.*,品牌 From 商品表1　x　 inner　join　商品表2 y on x.商品代号=y.商品代号　Where<all(select　数量 from 商品表1 where 　分类名=‘洗衣机’or　分类名=‘微波炉’）

  33. 4.5.4 WHERE 选项(6) 2.格式之二 <列名><比较符>{ANY | SOME }(<子查询>) 例4-19从商品库中查询出产地与品牌为“春兰”的商品的产地相同的所有商品的商品代号、分类名、品牌、产地等属性的值。 Select x.商品代号,x.分类名,y.品牌,y.产地 From 商品表1　x innner join 商品表2　y on x.商品代号=y.商品代号 Where 产地=some(select产地 from商品表2　 where品牌=‘春兰’ 例4-20从教学库中查询出选修了课程名为“C++语言”的所有学生的姓名和成绩 Select 姓名，成绩 From　学生 x　inner 选课 y on x.学生号=y.学生号 Where　课程号=any(select 课程号 from　课程 where 课程名=‘C++语言’)

  34. 4.5.4 WHERE 选项(7) 例4-21从商品库中查询出所有商品中单价最高和最低的商品 SELECT * FROM 商品表1 WHERE 单价=any(select max(单价)from 商品表1) or单价=any(select min(单价) from 商品表1) 或者 SELECT * FROM 商品表1 WHERE 单价=any(select max(单价)from 商品表1　union select min(单价) from 商品表1)

  35. 3.格式之三 <列名>[NOT]BETWEEN<开始值>AND<结束值> 此比较式与下面的逻逻表达式等效： <列名>>= <开始值> AND <列名><= <结束值> <列名>< <开始值> OR <列名>> <结束值> 例4-22从商品表1中查询出单价在1000到2000之间的所有商品 select * From 商品表1 Where 单价　between 1000 and 2000

  36. 4.格式之四 [NOT] EXIST(<子查询>) 例4-23从教学库中查询出选修至少一门课程的所有学生 Select * From 学生 Where exists(select * from 选课　where 选课.学生号=学生.学生号） 例4-24从教学库中查询出与姓名为“王明”的学生选课至少有一门相同的所有学生

  37. Select * From 学生 x Where x.姓名<>‘王明’and exit　(select y.课程号　from　选课　y Where y.学生号=x.学生号 and y.课程号=any(select w.课程号　From 学生　z.选课　w where z.学生号=w.学生号 and z.姓名=‘王明’）） 例4-25从教学库中查询出选修了课程表中所有课程的学生 Select * From 学生 Where not exit(select * From 选课 Where 学生.学生号=选课.学生号　and 课程.课程号=选课.课程号））

  38. 5.格式之五 <列名>[NOT]IN{(<常量表>) |（<子查询>）} 例4-26从学生表中查询出专业为计算机、电气、通信的所有学生。 Select * from 学生　where 专业　in (‘计算机’，’电气’，’通信’） 例4-27从教学库中查询出选修了课程名为“操作系统”的所有学生。 Select * from 学生　where 学生号　in (select 学生号 from 选课.课程 where 选课.课程号=课程.课程号　and 课程名=‘操作系统’）

  39. 6.格式之六 <字符型列名>[NOT] LIKE <字符表达式> 例4-28从商品表1中查询出商品代号以字符串“dsj”开头的所有商品。 Select * From 商品表1 Where 商品代号　like ‘dsj%’

  40. 4.5.5GROUP BY 选项 例4-29从学生表中查询出每个专业的学生数 Select 专业　as 专业名，count(专业）学生数　 From 学生 Group by 专业 例4-30从教学中查询出每个学生号、姓名及所选课程的门数 Select x.学生号,y.姓名,count（x.学生号）选课门数 from 选课x,学生y where x.学生号=y.学生号 Group by x.学生号,y.姓名

  41. 4.5.5GROUP BY 选项(2) 例4-31从商品表1中查询出每一类（即分类名相同）商品的最高价、最低价和平均价 Select 分类名，max(单价）as 最高价,min(单价）as 最低价， Avg(单价）　as 平均价 From 商品表1 Group by 分类名

  42. 4.5.6 HAVING选项 例4-32从学生表中查询出专业的学生多于1人的专业名及人数 Select 专业as专业名 ,count（专业）学生数 From　学生 Group by 专业　having count（专业）>1 例4-33从教学库中查询出选修课程超过1门的学生 Select * From 学生 Where 学生号　in （select 学生号 from 选课 group by 学生号　having count(学生号）>1)

  43. 例4-34从教学库中查询出选课门数超过学生号为01001001学生的选课门数的所有学生例4-34从教学库中查询出选课门数超过学生号为01001001学生的选课门数的所有学生 Select * From 学生 Where 学生号　in (select 学生号 From 选课 group by 学生号　having count (学生号）> (select count(*) from 选课 where 学生号=‘0202001’））

  44. 4.5.7ORDER BY 选项 例4-35从商品表1中按升序查询出所有商品记录 Select * From 商品表1 Order by 单价 例4-36从商品表1中查询出单价比平均单价高的所有商品，并 使结果按降序排列。 Select * From 商品表1 Where 单价>all(select avg(单价）from 商品表1） Order by 单价 desc

  45. 4.5.7ORDER BY 选项(2) 例4-37从教学库中查询出所有学生的学号及所有选课的门数，按门数升序排列结果 select学生.学生号,count(学生.学生号)as选课门数 From 学生，选课 Where 学生.学生号=选课.学生号 Group by学生.学生号 Order by选课门数

  46. 4.5.7ORDER BY 选项(3) 例4-38从教学库中查询出所有学生的信息及选课的门数，按门数升序排列结果 Select学生.*,count(学生.学生号)as选课门数 from学生，选课 where学生.学生号=选课.学生号 Group by 学生.学生号，姓名，性别，专业 Order by 选课门数

  47. INTO选项 例4-39从教学库中查询出每个学生的学生号、选课门数和总成绩，把查询结果保存到学生选课门数及总成绩统计表 Select 学生.学生号，count（学生.学生号） as　选课门数 Sum （成绩）as　总成绩 Into　学生选课门数及总成绩统计表 From　学生，选课 Where 学生.学生号=选课.学生号

More Related