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

1、第四章 关系数据库标准语言SQL语言 关系运算 查询功能 操纵功能 定义功能4.1 关系运算 4.1.1、传统的集合运算 4.1.2、专门的关系运算4.1.1 传统的集合运算：并、交、差 进行集合运算的两个关系必须具有相同的结构。对于表来说就要求两个表的字段变量要一样。4.1.2、专门的关系运算专门的关系运算：选择、投影、连接一、 选择：查找满足条件的元组（行的角度运算）。从列的角度进行的运算查询性别为女的记录二、 投影指定若干属性组成新的关系（ 从列的角度进行的操作）。查询学号 和姓名信息三 联接联接分为等值联接和自然联接1、等值联接：按照两个表的公共字段对应相等把多个关系合成一个关系。（）

2、2、自然联接：去掉重复属性的等值联接联接：将两个（多个）关系通过联接条件横向拼成一个新的关系；新的关系包括满足联接条件的所有元组。4.2查询功能 4.2.1 SQL 概述SQL是结构化查询语言Structured Query Language的缩写。它包含数据定义、数据操纵和数据控制功能，核心功能是查询功能；是关系型数据库的标准数据语言，现在所有的关系数据库管理系统都支持SQL。表之间的联系：4.2.2 简单查询 基于一个表的查询格式： select from where 例1、从student表中查出所有人的年龄.Select 年龄 from student例2:检索全体学生的全部信息Sel

3、ect 学号,姓名,性别,年龄,院系号; from studentSelect * from stedent说明：如果语句过长，则用;表示换行； *为通配符，表示全部属性或字段例3:查询表中年龄大于18的学号。Select 学号 from student where 年龄18说明：where表示查询条件例4:检索哪些系有性别为男的学生。Select 院系号 from student where 性别=“男”去重查询：Select distinct 院系号; from student; where 性别=“男” 例5:检索出年龄小于20且在06号或者02号学院的学号Select 学号 from

4、student；Where 年龄90;And student.学号=score.学号说明：1. student.学号=score.学号为联接条件（主码=外码）2. 使用表的公共属性时，要用关系前缀表示属性的所属关系。3. 公共字段对应相等要记住。例7：查询选修计算机软件基础这门课的学号和成绩。分析：条件一 课程号=“计算机软件基础” 条件二：联接条件，course.课程号=score.课程号Select 学号,成绩 from score,course; where 课程名=“计算机软件基础”; and course.课程号=score.课程号 例8:查询选“数据库”的学生 的姓名和成绩分析：

5、课程=“数据库”,用的course表 姓名用到student表，成绩用到score表用到3个表，联接字段student.学号=score.学号 score.课程号=course.课程号语句：select 姓名,成绩 ;from student,score,course;Where 课程名=“数据库”;and score.课程号=course.课程号;And student.学号=score.学号4.2.4嵌套查询一个查询包含另外一个查询例9:查询哪些系有学生得成绩为80分；联接查询：select 院系号 from student,score;Where 成绩=80 and student.学号

6、=score.学号嵌套查询：先在score表中把成绩是80得学号找出来（内层查询）；再在student表中根据找到学号查询对应的学院号（外层查询）；（学生的学号是否在从score找到满足条件的集合里面）语句：select 院系号 from student; where 学号 in; (select 学号 from score where 成绩=80)嵌套查询说明：1.In：检查一个属性值是否属于一组值2.外层查询依赖于内层查询的结果。3.内层查询语句必须用（）括起来例10:查询和学生辛国年同岁的学生分析：先查询辛国年的年龄； 再查询表年龄=辛国年年龄的学号语句：不使用in：Select 学号

7、from student where 年龄=;(Select 年龄 from student where 姓名=“辛国年”)使用 in：Select 学号 from student where 年龄 in;(Select 年龄 from student where 姓名=“辛国年”)说明：如果子查询的结果有多个值则必须用in；如果内层查询结果只有一个值则in和=都可以例11:查询没有学生选修的课程名只用corese表和score表即可，score出现的课程号均有人选修（内层），然后看course表的课程号没有在score表中出现的记录（外层）。命令：select 课程名 from course

8、 where 课程号 not in;(select 课程号 from score)4.2.5 几个特殊的运算符1.Between.and 查询范围例12：查询年龄在20到22之间学生信息Select * from student where 年龄年龄Between 20 and 22注意：该运算符包含边界（年龄为20和22）2.Like 匹配查询例13：查询所有姓王的学生信息（like “王%”）Select * from student where 姓名 like “王%”说明：Like的一般形式：列名 like 字符串常用的两个通配符：%和_“%”表示任意多个字符“_”表示任意一个字符3.

9、Not 否定运算符例14:查询性别不是女的学生信息（not 性别=“女”）语句：Select * from student where not 性别=“女”等价于：Select * from student where 性别!=“女”4.2.6 排序 我们不仅可以把表的结果查询出来，而且可以对查询结构进行排序。排序的命令：order by 字段名 asc|desc例15:按院系号升序查询出学生信息。Select * from student order by 院系号 asc说明：默认是升序asc 升序 desc 降序多字段排序例16:先按照年龄升序排列，如果年龄相同则按照院系号降序排列Sele

10、ct * from student order by 年龄 asc，院系号 desc说明：可按照多字段排序4.2.7 使用集函数Sql不仅可以查询还可以以计算方式查询1.Count（）函数计数2.Sum（）求和3.Avg（）求平均值4.Max（）求最大值5.Min（）求最小值1.Count（）函数例17:统计开设课程的门数。说明：只要在course表中统计课程号的数目即可。语句：select count(课程号) from course该结果别人可能看不懂统计结果是什么，所以我们可以起别名。select count(课程号) as课程门数from course说明：as可以省略例18:统计学生

11、总人数统计学号个数即可如果姓名唯一也可以使用姓名select count(学号) as 学生人数 from student例19:统计选课的学生人数，若选多门课不累计（count（distinct学号）select count(distinct 学号) as 选课人数 from score2.sum()函数例20:求s1所选课程的总成绩；分析：条件 ：学号=“s1” 对象 ：成绩（sum（成绩）语句：select sum(成绩) as 总成绩 from score; where 学号=“s1”例21:求邓一欧所选课程总成绩姓名在student表中，选课在score表中，所以要用到多个表的联接条

12、件select sum(成绩成绩) as 课程总分课程总分 from score,student ; where 姓名姓名=邓一欧邓一欧 and score.学号学号=student.学学号号3.Avg()函数例22.求所有学生平均年龄；select avg(年龄) as 平均年龄 from student4.max()函数例23:求选修“c1”课程的最高分分析：先在score表中找选修c1的记录，然后在记录中找成绩对应最高分。select max(成绩) as 最高分 from score where 课程号=c1“说明：如果要求最小值使用min（）4.2.8 分组查询命令格式：group

13、by , . having 过滤条件例24:计算每门课程得最高分先按照课程号分组，再在分组里求最高分select max(成绩) as 最高分 from score ;group by 课程号结果看不出是哪门课的最高分说明：要选择出分组的字段select 课程号 , max(成绩) as 最高分 from score ;group by 课程号说明：1.先分组，后计算2.分组字段必须是表中存在的字段3.要把分组的字段加在select后面例25:检索出年龄大于19的男女生人数分析：先在student表中查询出年龄大于19的人数，然后再按照性别进行分组，最后求男女分组的学号的个数Select 性别

14、, count(学号) as 人数 from student ;Where 年龄19 group by 性别;说明：group by作用于where返回的结果例26:计算至少有3个人的每个系的人数首先把每个系的学生记录放在一起（按院系号分组），然后统计至少有3人的院系的人数（分完组还要检查，至少有3人的院系号才能进行统计） 语句： select count(学号) as 人数; from student; group by 院系号; having count(学号 )=3说明：having用于检查每一组例27:统计年龄大于18且至少有3人的男女生人数；1、在表中找出年龄大于18的记录。2、按照

15、性别进行分组3、用having检查每一组4、统计满足条件的人数语句：select 性别,count(学号) as 人数;from student;where 年龄18;group by 性别;having 人数=3总结：1、Group by作用where返回的结果2、where把不满足条件的元组排除3、having子语句排除不符合条件的组，且不能单独使用Where：限制元组（范围宽）Having：限制组4.2.9 涉及空值（null）的查询格式：列名 is not null例28:查询没有确定院校号的学生的学号Select 学号 from student；Where 院系号 is null注意

16、：1、“=null” 无效 2、升序排序中null值首先显示给关系起别名：格式： 别名例：查询学生的姓名、成绩和选课的课程名联接查询：Select 姓名,成绩,课程名 ;From student ,score,course;Where student.学号=score.学号 and; score.课程号=course.课程号等价于：Select 姓名,成绩,课程名 from; student a, score b,course c; where a.学号=b.学号 ; and b.课程号=c.课程号4.2.10量词和谓词的使用与子查询相关的运算符Exists或not ExistsAny|all

17、|some注意：any和some的功能相同Exists/not exists例30:查询选“c1”课程的学生姓名语句 ：Select 姓名 from student where exists;(select 学号 from score where 课程号=“c1”; and 学号=student.学号)Exists是存在的意思，在本例中内存查询先找到选c1的学生学号，然后在student表中看其每条记录能否满足内层查询查出来的条件，如果满足的话就把该记录的姓名取出来any例31:求年龄大于“06”号系任一同学年龄的学生姓名Select 姓名 from student;Where 年龄any;(

18、select 年龄 from student where 院系号=“06”)说明：any作用：子查询中有一行能使结果为真，就为真Some（一些）和any相同Select 姓名 from student;Where 年龄any;(select 年龄 from student where 院系号=“06”)等价于Select 姓名 from student;Where 年龄some;(select 年龄 from student where 院系号=“06”)all求年龄大于“06”号系所有同学年龄的学生姓名Select 姓名 from student;Where 年龄all;(select 年龄

19、 from student where 院系号=“06”)4.2.11 超级联接查询格式：Select from 表1Inner|left|right|full|join On 联接条件作用：显示一个表的全部记录和另外一个表的部分记录使用文件夹3的内容题目：要求显示学号、年龄、性别、课程号、成绩字段联接查询：Select student.学号,年龄,性别,课程号,成绩;From student,score;Where student.学号=score.学号一 内部联接inner join 只有满足联接条件的记录才会出现在结果中Select student.学号,年龄,性别,课程号,成绩;Fro

20、m student inner join score;on student.学号=score.学号说明：1、联接条件用on 2、inner可以省略二、左联接除了满足联接条件的记录能查询出来外，第一个表中不满足联接条件的记录也显示出来Select student.学号,年龄,性别,课程号,成绩;From student left join score;on student.学号=score.学号三、左联接除了满足联接条件的记录能查询出来外，第二个表中不满足联接条件的记录也显示出来Select student.学号,年龄,性别,课程号,成绩;From student right join scor

21、e;on student.学号=score.学号四、全联接除了满足联接条件的记录能查询出来外，两个个表中不满足联接条件的记录也显示出来Select student.学号,年龄,性别,课程号,成绩;From student full join score;on student.学号=score.学号例32:查询成绩大于90的姓名、成绩和课程名Join联接多个表时，join的顺序和on联接条件的顺序正好相反。Select 姓名,成绩,课程名;From student join score join course;on score.课程号=course.课程号;On score.学号= studen

22、t.学号;Where 成绩904.2.12 集合查询例33:查询“06”或“02”号系的学生的学号1、用“or”Select 学号 from student;Where 院系号=“06” or院系号=“02” 2、集合查询：unionSelect 学号 from student;Where 院系号=“06”;Union;Select 学号 from student;Where 院系号=“02”说明：1、union对两个select语句的结果求并集。2、union要求两个select语句一样，即结果的属性（字段变量）要相同4.2.13 几个特殊选项1、显示部分记录 top格式：top perce

23、nt例34:查询年龄最小的三名同学的姓名和年龄select top 3 姓名,年龄;from student;order by 年龄说明：top必须和order by一起使用查询年龄最大的三条学生姓名、年龄（按降序排序）select top 3 姓名,年龄;from student;order by 年龄 desc例35:查询年龄最大的前50%学生信息（按降序排序）select * top 50 percent ;from student;order by 年龄 desc说明：数字后面使用percent表示的是百分比，不使用表示的是记录个数4.2.13 几个特殊选项2、查询去向：把查询的结果永

24、久保存（1）into array （2）into cursor （3）into table|dhf （4）to file （5）to printer ：打印（1）into array 例35:将查询student所有记录存入数组aa中select * from student;into array aa问题：aa数组是什么数组？（2）into cursor例36:将查询student所有记录存入临时表bb中select * from student;into cursor bb说明：临时表只能读，关闭时自动删除，用于复杂查询的中间过程（3）into table格式：into table|dhf

25、 例37:将查询student所有记录存入永久表cc中select * from student;into table cc（4）to fileto file 例37:将查询student所有记录存入临时表dd中select * from student;to file dd说明：把结果存入文本文件4.3 操纵功能4.3.1 插入4.3.2 更新4.3.3 删除注：以上操作针对表中的记录进行4.3.1 插入格式一： insert into （字段1，字段2,.) values(表达式) 格式二： insert into from array 数组名格式一例38:向student表中插入记录（“

26、s9”，“张三”，20，“男”，“06”）insert into student; values (s9,张三,20,男,20)例39:向student表的姓名和性别字段分别插入值（“李四”,”女”）命令：insert into ;Student(姓名，性别);Values(“李四”,”女”)说明：若插入某几个字段的值，字段名需要在表名后指明。格式二例40:查询学号是”s1”的记录放在数组aa中，再插入student表中1.查询：select * from;Student where 学号=“s1”;Into array aa;2.插入：insert into student;From arr

27、ay aa小结表中的插入命令insert into和前面学过的数据库插入命令 insert 、append都可以插入记录。使用时怎么选择呢？一般题目没有要求则使用insert into命令；另外当表中建立主索引和候选索引时候，如果插入的数据是主索引和候选索引字段相同值，则使用insert、append命令插入不进去，只能使用insert into命令完成插入。4.3.2 更新格式：updata ； set =; where 例41:把学生“邓一欧”改为“邓欧”命令：update student;set 姓名=“邓欧”;where 姓名=“邓一欧”说明：使用where修改满足条件的记录等价于：u

28、se student Replace 姓名 with “邓欧” for 姓名=“邓一欧” 例42：把所有人的年龄改为20Update student Set 年龄=20说明：缺省where条件说明是对所有的记录进行修改；等价于：use student replace all 年龄 with 20例43（修改多个字段）：把学生辛国年年龄改为21，学院号改为01命令：Update student;Ste 年龄=21,院系号=“01”;Where 姓名=“辛国年”等价于：Use studentReplace 年龄with 21，院系号 with “01” for 姓名=“辛国年”说明：update可

29、以同时修改多个字段值4.3.3 删除格式：Delete from ;Where（ 条件）例44:删除student表中学号是s1的记录Delete from student where 学号=“s1”等价于：Use studentDelete for 学号=“s1”说明：delete删除是逻辑删除，使用where删除满足的条件例35:删除所有学生的选课记录Delete from score等价于：Use scoreDelete all说明：缺省where条件说明删除所有的记录（给所有的记录添加删除标记）4.4 定义功能4.4.1 基本表4.4.2 视图定义4.4.1 基本表1、定义表：crea

30、te table2、修改表结构：alter table3、删除表：drop table说明:只对表的结构进行操作1、定义表命令：create table功能：定义表的结构说明：此命令需在数据库打开时使用，否则创建的是自由表。例36:在数据库stsc中创建一个表“课程”，其结构如course表。Create table 课程;(课程号 c(2),课程名 c(20),学分 n(2)注：数据类型表示：c 字符型；n 数值型；d 日期型；例37:建立表“课程1”的同时建立主索引Create table 课程;(课程号 c(2) primary key,;课程名 c(20),学分 n(2)Primary

31、 key 表示建立主索引例38:创建“课程2”表，设置规则：学分2,信息为”学分大于2”，默认值是3Create table 课程2;(课程号 c(2),课程名 c(20),;学分 N(2) check 学分2; error “学分大于2”;Default 3)说明：check指明规则；Error指明信息表达式；Default指明默认值。二、修改表结构命令格式：Alter table add|altercheck 逻辑表达式error 信息表达式defaut 逻辑表达式drop例39：修改student表的结构，添加日期类型的出生日期字段，并设置规则为：year（出生日期）2000,信息为“出生日期大于2000年”alte table student;add 出生日期 d;check year(出生日期)2000;error 出生年份大于2000年“说明：add添加新字段例40:把student表中姓名字段的宽度由8改为10.alter table student alter 姓名 c(10)说明：Alter用来修改已经存在的字段例41:修改或定义“出生日期”的规则为：出生日期大于2001Alter table student alter 出生日期;Set check year (出生日期)2001说明：set che