《SQL语言基础》PPT课件.ppt_第1页
《SQL语言基础》PPT课件.ppt_第2页
《SQL语言基础》PPT课件.ppt_第3页
《SQL语言基础》PPT课件.ppt_第4页
《SQL语言基础》PPT课件.ppt_第5页
已阅读5页,还剩189页未读, 继续免费阅读

下载本文档

版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领

文档简介

1、数据库管理系统,授课教师 吴涛,第4章 SQL语言基础,4.1 SQL语言概述 4.2系统提供的数据类型 4.3 用户定义数据类型 4.4 T-SQL语言的一些基础知识 4.5 变量,2/34,概述,SQL(Structured Query Language)是用户操作关系数据库的通用语言。 包含数据定义、数据查询、数据操作和数据控制等与数据库有关的全部功能。 已成为关系数据库的标准语言。 所有的关系数据库管理系统都支持SQL。,3/34,4.1 SQL语言概述,4.1.1 SQL语言的发展 4.1.2 SQL语言的特点 4.1.3 SQL语言功能概述,4/34,4.1.1 SQL语言的发展,

2、1986年10月美国ANSI公布最早的SQL标准。 1989年4月,ISO提出了具备完整性特征的SQL,称为SQL-89(SQL1)。 1992年11月,ISO又公布了新的SQL标准,称为SQL-92(SQL2) (以上均为关系形式)。 1999年颁布SQL-99(SQL3),是SQL92的扩展。,5/34,4.1.2 SQL语言的特点,1. 一体化 2. 高度非过程化 3. 简洁 4. 使用方式多样,6/34,4.1.3 SQL语言功能概述,四部分:数据定义功能、数据控制功能、数据查询功能和数据操纵功能。,7/34,4.2 系统提供的数据类型,4.2.1 数值类型 4.2.2 字符串类型 4

3、.2.3 日期时间类型 4.2.4 货币类型,8/34,准确数值类型,9/34,近似数值数类型,10/34,字符串类型,普通编码字符串类型 统一字符编码字符串类型 二进制字符串类型,11/34,普通编码字符串类型,说明:如果在使用char(n)或varchar(n)类型时未指定n,则默认长度为1。如果在使用CAST和CONVERT函数时未指定n,则默认长度为30。,12/34,统一字符编码字符串类型,13/34,二进制字符串类型,14/34,日期时间类型,15/34,日期时间类型(续),16/34,货币类型,17/34,4.3 用户定义数据类型,实际上是为系统数据类型起了个别名,因此也称为别名

4、类型。 当在多个表中存储语义相同的列时,一般要求这些列的数据类型和长度应该完全一致。为避免语义相同的列在不同的地方定义不一致,可以使用用户定义的数据类型。,18/34,创建用户定义数据类型,CREATE TYPE schema_name. type_name FROM base_type ( precision , scale ) NULL | NOT NULL 例1创建一个名为telephone的数据类型,其相应的系统数据类型为:char(8),不允许空 CREATE TYPE telephone FROM CHAR(8) NOT NULL,19/34,4.4 T-SQL语言的一些基础知识,

5、语句批 一组SQL语句集合 作为一个执行单元 结束标记:GO 脚本 存储在文件中的SQL语句集合 注释 单行注释符:- 多行注释符:/* */,20/34,4.5 变量,4.5.1变量的种类 4.5.2 变量的声明与赋值,21/34,变量种类,(1)局部变量: 变量名:用户使用 (2)全局变量: 变量名:系统用于记录信息,22/34,声明局部变量,DECLARE local_variable AS data_type | = value , . n local_variable:变量名。必须以“”开头,且最多可包含128个字符。 data_type:任何系统提供的数据类型或用户定义的数据类型。

6、但不能是text、ntext或image。,23/34,给变量赋值,SET local_variable = expression | local_variable += | -= | *= | /= | %= | := column_name AS computed_column_expression,36/38,基本表定义(续), := column_name COLLATE collation_name NULL | NOT NULL CONSTRAINT constraint_name DEFAULT constant_expression | IDENTITY ( seed ,inc

7、rement ) ,37/38,示例:创建学生表,CREATE TABLE Student ( Sno CHAR(7) PRIMARY KEY, Sname NCHAR(5)NOT NULL, SID CHAR(18)UNIQUE, Ssex NCHAR(1)DEFAULT 男, Sage TINYINTCHECK(Sage=15 AND Sage=45), Sdept NVARCHAR(20) ),38/38,创建课程表,CREATE TABLE Course ( Cno CHAR(6) PRIMARY KEY, Cname NVARCHAR(20)NOT NULL, Credit NUME

8、RIC(3,1)CHECK(Credit0), Semester TINYINT ),39/38,创建选课表,CREATE TABLE SC ( Sno CHAR(7) NOT NULL, Cno CHAR(6) NOT NULL, Grade TINYINT, PRIMARY KEY (Sno, Cno), FOREIGN KEY (Sno) REFERENCES Student(Sno), FOREIGN KEY (Cno) REFERENCES Course(Cno) ),40/38,示例:创建有计算列的表,CREATE TABLE CompTable ( low int, high i

9、nt, myavg AS (low + high)/2 ),41/38,示例:创建包含标识列的表,标识列的种子值为1,增量值也为1。 CREATE TABLE IDTable ( SID INT IDENTITY(1,1) NOT NULL, Name VARCHAR(20) ),42/38,说明,一般情况下,在插入数据时不能为标识列提供值。标识列的值是系统自动生成的。 如果确实要为标识列提供值,则必须将表的IDENTITY_INSERT属性设置为ON(默认时该属性的值为OFF)。 SET IDENTITY_INSERT database_name. schema_name . table O

10、N | OFF ,43/38,修改表结构,可以使用ALTER TABLE语句实现。 ALTER TABLE语句可以对已定义的表进行添加列、删除列、修改列定义等操作,也可以进行添加和删除约束的操作。,44/38,ALTER TABLE database_name.schema_name.| schema_name . table_name ALTER COLUMN column_name type_schema_name. type_name ( precision , scale | max ) NULL | NOT NULL | ADD | | ,.n | DROP CONSTRAINT c

11、onstraint_name | COLUMN column_name ,.n ; ,45/38,示例,例2为SC表添加“修课类别”列,此列的定义为:Type NCHAR(1),允许空。 ALTER TABLE SC ADD Type NCHAR(1) NULL 例3将新添加的Type列的数据类型改为NCHAR(2)。 ALTER TABLE SC ALTER COLUMN Type NCHAR(2),46/38,示例,例4为Type列添加限定取值范围为必修、重修、选修的约束。 ALTER TABLE SC ADD CHECK(Type IN (必修, 重修, 选修) ) 例5删除SC表的“T

12、ype”列。 ALTER TABLE SC DROP COLUMN Type,47/38,删除表,DROP TABLE , 例6删除test表。 DROP TABLE test 注意:如果被删除的表中有其他表对它的外键引用约束,则必须先删除外键所在的表,然后再删除被引用的表。,48/38,5.2 分区表,分区表是把数据按某种标准划分成区域存储在不同的文件组中,使用分区可以快速而有效地管理和访问数据子集,从而使大型表或索引更易于管理。 合理的使用分区会在很大程度上提高数据库的性能。 分区表是将表中的数据按水平方式划分成不同的子集,这些数据子集存储在数据库的一个或多个文件组中。,49/38,比较适

13、于进行分区的情况,表中数据量大; 该表包含(或将包含)以多种不同方式使用的大量数据。 数据是分段的,比如数据以年为分隔。 对表的常规维护操作只针对表的一个数据子集。,50/38,分区表特点,分区表是从物理上将一个大表分成几个小表,但从逻辑上来看,还是一个大表。 对用户而言,所面对的依然是一个大表,他们不需要考虑操作的年份对应的小表,用户只要将记录插入到大表逻辑表中就可以了,数据库管理系统会自动将数据放置到它对应的那个物理小表中。,51/38,创建分区表步骤,1创建分区函数。告诉SQL Server以什么方式对表进行分区。 2创建分区方案。将分区函数生成的分区映射到文件组中去。分区函数的作用是告

14、诉SQL Server如何将数据进行分区,而分区方案的作用则是告诉SQL Server将已分区的数据放在哪个文件组中。 3使用分区方案创建表。 说明:在创建分区表之前,最好先创建数据库文件组。,52/38,1. 创建分区函数,CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type ) AS RANGE LEFT | RIGHT FOR VALUES ( boundary_value , n ),53/38,示例,例1在 int 列上创建左侧分区函数。下列分区函数将表分为四个分区。 CREATE PARTI

15、TION FUNCTION myRangePF1(int) AS RANGE LEFT FOR VALUES (1,100,1000);,54/38,示例,例2在int列上创建右侧分区函数。 CREATE PARTITION FUNCTION myRangePF2(int) AS RANGE RIGHT FOR VALUES (1, 100, 1000),55/38,示例,例3在datetime列上创建右侧分区函数。将表分成12个分区,每个分区对应一个月的值 CREATE PARTITION FUNCTION myDateRangePF1(datetime) AS RANGE RIGHT FO

16、R VALUES ( 20110201, 20110301, 20110401, 20110501, 20110601, 20110701, 20110801, 20110901, 20111001, 20111101, 20111201);,56/38,示例,例3在字符列上创建右侧分区函数。下列分区函数将表分为四个分区。 CREATE PARTITION FUNCTION myRangePF3(char(20) AS RANGE RIGHT FOR VALUES (EX, RXE, XR);,57/38,2. 创建分区方案,CREATE PARTITION SCHEME partition_

17、scheme_name AS PARTITION partition_function_name ALL TO ( file_group_name | PRIMARY ,.n ) ; ,58/38,示例,例4.先创建一个分区函数,该函数将表分为四个分区。然后创建一个分区方案,在其中指定拥有这四个分区中每一个分区的文件组 CREATE PARTITION FUNCTION myRangePF1(int) AS RANGE LEFT FOR VALUES (1, 100, 1000); GO CREATE PARTITION SCHEME myRangePS1 AS PARTITION myRan

18、gePF1 TO (test1fg, test2fg, test3fg, test4fg);,59/38,例4的分区情况,在某表上对分区依据列col1使用分区函数myRangePF1后,对该表的分区进行分配的情况:,60/38,示例,例5. 创建将多个分区映射到同一个文件组的分区方案。如果要将所有分区都映射到同一个文件组,可使用ALL关键字。但如果是将多个(不是全部)分区映射到同一个文件组,则必须分别列出文件组名称 CREATE PARTITION FUNCTION myRangePF2(int) AS RANGE LEFT FOR VALUES (1, 100, 1000); GO CREA

19、TE PARTITION SCHEME myRangePS2 AS PARTITION myRangePF2 TO ( test1fg, test1fg, test1fg, test2fg ),61/38,例5分区情况,在某表上对分区依据列col1使用分区函数myRangePF2后,对该表的分区进行分配的情况,62/38,示例,例6. 创建将所有分区映射到同一个文件组的分区方案。 CREATE PARTITION FUNCTION myRangePF3(int) AS RANGE LEFT FOR VALUES (1, 100, 1000); GO CREATE PARTITION SCHEM

20、E myRangePS3 AS PARTITION myRangePF3 ALL TO ( test1fg ),63/38,示例,例7.创建指定“NEXT USED”文件组的分区方案。本示例所创建的分区方案列出的文件组数超过了关联的分区函数所创建的分区数。 CREATE PARTITION FUNCTION myRangePF4(int) AS RANGE LEFT FOR VALUES (1, 100, 1000); GO CREATE PARTITION SCHEME myRangePS4 AS PARTITION myRangePF4 TO (test1fg, test2fg, test

21、3fg, test4fg, test5fg) 执行该语句时系统返回以下消息: 分区方案myRangePS4已成功创建。test5fg在分区方案myRangePS4中标记为下次使用的文件组。,64/38,示例,例8. 本示例首先创建一个分区函数,将数据分为四个分区。然后创建一个分区方案,最后创建使用该分区方案的表。本示例假定数据库中已经存在文件组。 CREATE PARTITION FUNCTION myRangePF1(int) AS RANGE LEFT FOR VALUES (1, 100, 1000); GO CREATE PARTITION SCHEME myRangePS1 AS P

22、ARTITION myRangePF1 TO (test1fg, test2fg, test3fg, test4fg); GO CREATE TABLE PartitionTable ( col1 int, col2 char(10) ) ON myRangePS1 (col1);,65/38,第6章 数据操作语言,6.1 数据查询语句 6.2 数据更改功能,66,6.1 数据查询语句,6.1.1 查询语句基本结构 6.1.2 单表查询 6.1.3 多表连接查询 6.1.4 使用TOP限制结果集 6.1.5 将查询结果保存到新表中,67/130,6.1.1 查询语句基本结构,SELECT -

23、需要哪些列 FROM - 来自于哪些表 WHERE - 根据什么条件 GROUP BY HAVING ORDER BY ,68/130,部分能够包含的内容,SELECT ALL | DISTINCT TOP expression PERCENT WITH TIES := * | table_name | view_name | table_alias .* | table_name | view_name | table_alias . column_name | $IDENTITY | expression AS column_alias | column_alias = expressio

24、n ,.n ,69/130,6.1.2 单表查询,1.选择表中若干列 2.选择表中的若干元组 3.对查询结果排序 4.使用聚合函数统计数据 5.对数据进行分组统计,70/130,1.选择表中若干列:查询指定列,在SELECT子句的中指定要查询的属性。 例1. 查询全体学生的学号与姓名。 SELECT Sno,Sname FROM Student,71/130,示例,例2.查询全体学生的姓名、学号和所在系。 SELECT Sname, Sno, Dept FROM Student 说明:查询列表中的列顺序与表中列定义的顺序无关。,72/130,查询全部列,如果要查询表中的全部列,可以使用两种方法

25、: 在中列出所有的列名; 如果列的显示顺序与其在表中定义的顺序相同,则可以简单地在中写星号“*”。,73/130,示例,例3 查询全体学生的详细记录。 SELECT Sno, Sname, Sex, Sage, Dept FROM Student 等价于: SELECT * FROM Student,74/130,查询经过计算的列,SELECT子句中的可以是表中存在的属性列,也可以是表达式、常量或者函数。 例4 查询全体学生的姓名及其出生年份。 SELECT Sname, year(getdate() - year(Birthdate) FROM Student getdate():得到系统的

26、当前日期和时间 year():得到日期数据中年的部分,75/130,示例,例5. 含字符串常量的列。 SELECT Sname, 出生年份, year(getdate() FROM Student,76/130,指定列别名, 列名 | 表达式 AS 列别名 或 列别名 列名 | 表达式 例: SELECT Sname, year(getdate() - year(Birthdate) AS 年龄 FROM Student,77/130,指定列别名后的查询结果,SELECT Sname, year(getdate() - year(Birthdate) AS 年龄 FROM Student,78

27、/130,用DISTINCT去掉结果中的重复行,SELECT DISTINCT Sno FROM SC,Sno,DISTINCT Sno,79/130,注意,SELECT语句不会自动去掉结果中的重复行,如果要求结果中不出现行,必须要明确地指出 DISTINCT是确保检索后的每一行是唯一的,这种唯一性是相对于其他行来说的。,80/130,2. 选择表中的若干元组,查询满足条件的元组可通过 WHERE子句实现。,81/130,常用的查询条件,82,(1)比较大小,例7查询计算机系全体学生。 SELECT Sname FROM Student WHERE Dept = 计算机系 例8查询所有年龄20

28、岁以下的学生的姓名及年龄。 SELECT Sname, year(getdate() - year(Birthdate) AS 年龄 FROM Student WHERE year(getdate()-year(Birth)20,83/130,示例,例9.查询成绩不及格学生的学号。 SELECT DISTINCT Sno FROM SC WHERE Grade 60,84/130,(2)确定范围,BETWEENAND NOT BETWEENAND 作用:查找属性值在或不在指定范围内的元组。 说明: BETWEEN后是范围的下限(低值) AND后是范围的上限(高值),85/130,示例,例10查

29、询考试成绩在8090之间的学生学号、课程号和成绩。,SELECT Sno, Cno, Grade FROM SC WHERE Grade BETWEEN 80 AND 90,等价于: SELECT Sno, Cno, Grade FROM SC WHERE Grade =80 AND Grade =90,86/130,示例,例11查询考试成绩不在8090之间的学生学号、课程号和成绩。,SELECT Sno, Cno, Grade FROM SC WHERE Grade NOT BETWEEN 80 AND 90,等价于: SELECT Sno, Cno, Grade FROM SC WHERE

30、 Grade 90,87/130,示例:日期比较,例12. 设有图书表(titles),其中包含书号(title_id)、类型(type)、价格(price)和出版日期(pubdate)列,查询1991年6月出版的图书信息: SELECT title_id, type, price, pubdate FROM titles WHERE pubdate BETWEEN 1991/6/1 AND 1991/6/30,88/130,(3)确定集合(IN),作用:用来查找属性值属于指定集合的元组。 格式: 列名 NOT IN (常量1, 常量2, ),89/130,示例,SELECT Sname, S

31、ex FROM Student WHERE Dept IN( 信息管理系, 通信工程系, 计算机系),例13查询信息管理系、通信工程系和计算机系学生的姓名和性别。,等价于: SELECT Sname, Sex FROM Student WHERE Dept 信息管理系, OR Dept 通信工程系, OR Dept 计算机系,90/130,示例,例14.查询信息管理系、通信工程系和计算机系三个系之外的其他系学生的姓名和性别。 SELECT Sname, Sex FROM Student WHERE Dept NOT IN ( 信息管理系, 通信工程系, 计算机系) 等价于: SELECT Sn

32、ame, Sex FROM Student WHERE Dept!= 信息管理系 AND Dept!= 通信工程系 AND Dept!= 计算机系,91/130,(4)字符串匹配(LIKE),列名 NOT LIKE ESCAPE 匹配串中可包含如下通配符: %(百分号):匹配0个或多个字符。 _(下划线):匹配一个字符。 :匹配方括号中的任何一个字符。 :不匹配方括号中的任何一个字符。 若要比较的字符是连续的,则可以用连字符“-”表达,例如,要匹配b、c、d、e中的任何一个字符,则可以表示为:b-e,92/130,示例,例15.查询姓“张”的学生详细信息。 SELECT * FROM Stud

33、ent WHERE Sname LIKE 张%,93/130,示例,例16.查询姓“张”、姓“李”和姓“刘”的学生的详细信息。 SELECT * FROM Student WHERE Sname LIKE 张李刘%,94/130,示例,例17.查询名字的第2个字为“小”或“大”的学生的姓名和学号。 SELECT Sname, Sno FROM Student WHERE Sname LIKE _小大% 例18查询所有不姓“刘”的学生姓名。 SELECT Sname FROM Student WHERE Sname NOT LIKE 刘%,95/130,示例,例19.在Student表中查询学号

34、的最后一位不是2、3、5的学生信息。 SELECT * FROM Student WHERE Sno LIKE %235,96/130,转义字符,如果要查找的字符串正好含有通配符,比如下划线或百分号,就需要用ESCAPE来说明。 ESCAPE 转义字符 其中“转义字符”是任何一个有效的字符,在匹配串中也包含这个字符,表明位于该字符后面的那个字符将被视为普通字符,而不是通配符。,97/130,示例,查找field1字段中包含字符串“30%”的记录: WHERE field1 LIKE %30!% ESCAPE ! 查找field1字段中包含下划线(_)的记录: WHERE field1 LIKE

35、 %!_% ESCAPE !,98/130,(5)涉及空值的查询,空值是未确定的值或其值尚不知道。 例如,学生选课,在开学初学生只有选课记录,没有修课成绩,这时成绩成绩一项的值就是空值。 判断列取值为空的语句格式为: 列名IS NULL 判断列取值不为空的语句格式为: 列名 IS NOT NULL,99/130,示例,例20.查询还没有考试的学生的学号和相应的课程号。 SELECT Sno, Cno FROM SC WHERE Grade IS NULL,100/130,注意,空值不是一个确定的值,所以不可以用等于或不等于来比较或衡量; 空值只能说是空值(IS NULL)或不是空值(IS NO

36、T NULL)。,101/130,(6)多重条件查询,当需要多个查询条件时,可以在WHERE子句中使用逻辑运算符AND和OR来组成多条件查询。 例21.查询计算机系男生的姓名。 SELECT Sname FROM Student WHERE Dept = 计算机系 AND Sex = 男,102/130,示例,例22.查询C002和C003课程中考试成绩在8090的学生的学号、课程号和成绩。 SELECT Sno, Cno, Grade FROM SC WHERE Cno IN( C002, C003) AND Grade BETWEEN 80 AND 90 注:OR的优先级小于AND,要改变

37、运算的顺序可以通过加括号的方式实现。 SELECT Sno, Cno, Grade FROM SC WHERE (Cno = C001 OR Cno = C002) AND Grade BETWEEN 80 AND 90,103/130,3. 对查询结果排序,可用ORDER BY子句对查询结果进行排序。 ORDER BY ASC | DESC , 说明:按进行升序(ASC)或降序(DESC)排序。 当用多个列排序时,这些列在该子句中出现的顺序决定了对结果集进行排序的方式。,104/130,示例,例23.查询修了“C002”课程的学生的学号及成绩,查询结果按成绩降序排列。 SELECT Sno,

38、 Grade FROM SC WHERE Cno = C002 ORDER BY Grade DESC,105/130,示例,例24.查询全体学生详细信息,结果按系名升序排列,同一个系的学生按出生日期降序排列。 SELECT * FROM Student ORDER BY Dept ASC, Birthdate DESC,106/130,例24执行结果,ORDER BY Dept, Birthdate DESC,107/130,4使用聚合函数汇总数据,也称为集合函数或聚合函数, 其作用是对一组值进行计算并返回一个统计结果。,108/130,聚合函数,COUNT(*):统计表中元组的个数。 CO

39、UNT(DISTINCT ):统计列值个数 SUM():计算列值的和值(必须是数值型列)。 AVG():计算列值的平均值(必须是数值型列)。 MAX():得到列值的最大值。 MIN():得到列值的最小值。 除COUNT(*)外,其他函数在计算过程中均忽略NULL值。,109/130,示例,例25. 统计学生总人数。 SELECT COUNT(*) FROM Student,10,110/130,示例,例26.统计选修了课程的学生人数。 SELECT COUNT(DISTINCT Sno) FROM SC,4,111/130,示例,例. 计算学号为“0811101”的学生的考试总成绩。 SELE

40、CT SUM(Grade) FROM SC WHERE Sno = 0811101,322,112/130,示例,例28.计算“0831103”学生的平均成绩。 SELECT AVG(Grade) FROM SC WHERE Sno = 0831103,71,113/130,示例,例28.查询“C001”课程考试成绩的最高分和最低分。 SELECT MAX(Grade) 最高分, MIN(Grade) 最低分 FROM SC WHERE Cno = C001,96,50,114/130,注意,聚合函数不能出现在WHERE子句中。 例如,查询学分最高的课程名,如下写法是错误的: SELECT C

41、name FROM Course WHERE Credit = MAX(Credit),115/130,5对数据进行分组统计,作用:可以控制计算的级别:对全表还是对一组。 目的:细化聚合函数的作用对象。 分组语句的一般形式: GROUP BY HAVING ,116/130,使用GROUP BY子句,例29.统计每门课程的选课人数,列出课程号和选课人数。 SELECT Cno, COUNT(Sno) FROM SC GROUP BY Cno 对查询结果按Cno的值分组,所有具有相同Cno值的元组为一组,然后再对每一组使用COUNT计算,求出每组的学生人数。,117/130,示例,例30.统计每

42、个学生的选课门数和平均成绩。 SELECT Sno 学号, COUNT(*) 选课门数, AVG(Grade) 平均成绩 FROM SC GROUP BY Sno,118/130,注意,GROUP BY子句中的分组依据列必须是表中存在的列名,不能使用AS子句指派的列别名。 带有GROUP BY 子句的SELECT语句的查询列表中只能出现分组依据列和统计函数,因为分组后每个组只返回一行结果。,119/130,示例,例31. 带WHERE子句的分组。统计每个系的女生人数。 SELECT Dept, Count(*) 女生人数 FROM Student WHERE Sex = 女 GROUP BY

43、Dept,120/130,示例,例32 按多个列分组。统计每个系的男生人数和女生人数以及男生的最大年龄和女生的最大年龄。结果按系名的升序排序。 SELECT Dept, Sex, Count(*) 人数, Max(Sage) 最大年龄 FROM Student GROUP BY Dept, Sex ORDER BY Dept,121/130,使用HAVING子句,HAVING用于对分组自身进行限制,它有点象WHERE子句,但它用于组而不是对单个记录。,122/130,示例,例33. 查询选课门数超过3门的学生的学号和选课门数。 SELECT Sno, Count(*) 选课门数 FROM SC

44、 GROUP BY Sno HAVING COUNT(*) 3 处理过程为:先执行GROUP BY子句对SC表数据按Sno进行分组,然后再用统计函数COUNT分别对每一组进行统计,最后筛选出统计结果满足大于3的组。,123/130,示例,例34. 查询选课门数大于等于4门的学生的平均成绩和选课门数。 SELECT Sno, AVG(Grade) 平均成绩, COUNT(*) 选课门数 FROM SC GROUP BY Sno HAVING COUNT(*) = 4,124/130,一些说明,WHERE子句用来筛选FROM子句中指定的数据源所产生的行数据。 GROUP BY子句用来对经WHERE

45、子句筛选后的结果数据进行分组。 HAVING子句用来对分组后的统计结果再进行筛选。,125/130,一些说明,可以在分组操作之前应用的筛选条件,在WHERE子句中指定更有效。 在HAVING子句中指定的筛选条件应该是那些必须在执行分组操作之后应用的筛选条件。 将所有应该在分组之前进行的筛选条件放在WHERE子句中而不是HAVING子句中。,126/130,示例,例35. 查询计算机系和信息管理系每个系的学生人数。 SELECT Dept, COUNT(*) FROM Student GROUP BY Dept HAVING Dept in (计算机系, 信息管理系) SELECT Dept,

46、COUNT (*) FROM Student WHERE Dept in ( 计算机系, 信息管理系) GROUP BY Dept,X,127/130,示例,例36查询每个系的男生人数。 SELECT Dept, COUNT(*) FROM Student WHERE Sex = 男 GROUP BY Dept 该查询语句不能写成: SELECT Dept, COUNT(*) FROM Student GROUP BY Dept HAVING Sex = 男,128/130,6.1.3 多表连接查询,若一个查询同时涉及两个或两个以上的表,则称之为连接查询。 连接查询是关系数据库中最主要的查询,

47、主要包括: 内连接 外连接:左外连接、右外连接,129/130,内连接,是一种最常用的连接类型。使用内连接时,如果两个表的相关字段满足连接条件,则从这两个表中提取数据并组合成新的记录。 在非ANSI标准的实现中,连接操作是在WHERE子句中执行的theta连接 在ANSI SQL-92中,连接是在JOIN子句中执行的ANSI连接。 我们介绍ANSI方式的连接格式: FROM 表1 INNER JOIN 表2 ON ,130/130,连接基础知识,连接查询中用于连接两个表的条件称为连接条件或连接谓词。 一般格式为: =,必须是可比列,131/130,执行连接操作的大致过程,首先取表1中的第1个元

48、组,然后从头开始扫描表2,逐一查找满足连接条件的元组, 找到后就将表1中的第1个元组与该元组拼接起来,形成结果表中的一个元组。 表2全部查找完毕后,再取表1中的第2个元组,然后再从头开始扫描表2, 重复这个过程,直到表1中的全部元组都处理完毕为止。,132/130,示例,SELECT * FROM Student INNER JOIN SC ON Student.Sno=SC.Sno,133/130,去掉重复列,SELECT Student.Sno, Sname, Sex, Sage, Dept, Cno, Grade FROM Student JOIN SC ON Student.Sno =

49、 SC.Sno,134/130,示例,例38. 查询计算机系学生的修课情况,要求列出学生的名字、所修课的课程号和成绩。 SELECT Sname, Cno, Grade FROM Student JOIN SC ON Student.Sno = SC.Sno WHERE Dept = 计算机系,135/130,指定列别名, AS 为表指定别名可以简化表的书写。 SELECT Sname, Cno, Grade FROM Student S JOIN SC ON S.Sno = SC.Sno WHERE Dept = 计算机系 注意:当为表指定了别名时,在查询语句中的其他地方,所有用到表名的地方

50、都要使用别名,而不能再使用原表名。,136/130,示例,例39. 查询“信息管理系”修了“计算机文化学” 的学生姓名和成绩。 SELECT Sname, Grade FROM Student s JOIN SC ON s.Sno = SC. Sno JOIN Course c ON c.Cno = SC.Cno WHERE Dept = 信息管理系 AND Cname = 计算机文化学,137/130,示例,例40. 查询所有选修了Java课程的学生情况,列出学生姓名和所在系。 SELECT Sname, Dept FROM Student S JOIN SC ON S.Sno = SC.

51、Sno JOIN Course C ON C.Cno = SC.cno WHERE Cname = Java,138/130,示例,例41.有分组的多表连接查询。统计每个系的学生的考试平均成绩。 SELECT Dept, AVG(grade) as AverageGrade FROM student S JOIN SC ON S.Sno = SC.Sno GROUP BY Dept,139/130,示例,例42. 有分组和行选择条件的多表连接查询。统计计算机系学生每门课程的选课人数、平均成绩、最高成绩和最低成绩。 SELECT Cno, COUNT(*) AS Total, AVG(Grade

52、) as AvgGrade, MAX(Grade) as MaxGrade, MIN(Grade) as MinGrade FROM Student S JOIN SC ON S.Sno = SC.Sno WHERE Dept = 计算机系 GROUP BY Cno,140/130,自连接,是特殊的内连接。 相互连接的表物理上为同一张表。 必须为两个表取别名,使之在逻辑上成为两个表。 FROM 表1 AS T1 - 在内存中生成“T1” JOIN 表1 AS T2 - 在内存中生成“T2”,141/130,示例,例43. 查询与刘晨在同一个系学习的学生的姓名和所在的系。 SELECT S2.S

53、name, S2.Dept FROM Student S1 JOIN Student S2 ON S1.Dept = S2.Dept WHERE S1.Sname = 刘晨 AND S2.Sname != 刘晨,142/130,示例,例44. 查询与“数据结构”在同一个学期开设的课程的课程名和开课学期 SELECT C1.Cname, C1.Semester FROM Course C1 JOIN Course C2 ON C1.Semester = C2.Semester WHERE C2.Cname = 数据结构,143/130,示例,例45. 查询至少被两个学生选的课程的课程号。 SEL

54、ECT DISTINCT a.Cno FROM SC a JOIN SC b ON a.Cno = b.Cno AND a.Sno != b.Sno 或者: SELECT Cno FROM SC GROUP BY Cno HAVING COUNT(*) 1,144/130,外连接,外连接会返回FROM子句中提到的至少一个表的所有行,只要这些行符合任何WHERE或HAVING搜索条件。 左外连接 FROM 表1 LEFT OUTER JOIN 表2 ON 右外连接 FROM 表1 RIGHT OUTER JOIN 表2 ON ,145/130,内连接与外连接示意图,A与B的左外连接结果: A +

55、 C,A与B的右外连接结果: B+ C,A与B的内连接结果: C,146/130,示例,例46 查询全体学生的选课情况,包括选修了课程的学生和没有选修课程的学生。 SELECT Student.Sno, Sname, Cno, Grade FROM Student LEFT JOIN SC ON Student.Sno = SC.Sno 或: SELECT Student.Sno, Sname, Cno, Grade FROM SC RIGHT JOIN Student ON Student.Sno = SC.Sno,147/130,例46执行部分结果示例,148/130,示例,例47. 查询

56、没人选的课程的课程名。 SELECT Cname FROM Course C LEFT JOIN SC ON C.Cno = SC.Cno WHERE SC.Cno IS NULL,149/130,示例,例48. 查询计算机系没有选课的学生,列出学生姓名和性别。 SELECT Sname,Dept,Cno,grade FROM Student S LEFT JOIN SC ON S.Sno = SC.Sno WHERE Dept = 计算机系 AND SC.Sno IS NULL,150/130,示例,例49. 统计计算机系每个学生的选课门数,包括没有选课的学生。 SELECT S.Sno A

57、S 学号, COUNT(SC.Cno) AS 选课门数 FROM Student S LEFT JOIN SC ON S.Sno = SC.Sno WHERE Dept = 计算机系 GROUP BY S.Sno,151/130,示例,例50. 查询信息管理系选课门数少于3门的学生的学号和选课门数,包括没有选课的学生。查询结果按选课门数递增排序。 SELECT S.Sno AS 学号,COUNT(SC.Cno) AS 门数 FROM Student S LEFT JOIN SC ON S.Sno = SC.Sno WHERE Dept = 信息管理系 GROUP BY S.Sno HAVING

58、 COUNT(SC.Cno) 3 ORDER BY COUNT(SC.Cno) ASC,152/130,说明,当外连接与内连接混合使用时,对侧的概念的理解就非常重要。 所有查询语句中JOIN左侧或之前的所有表只作为一个表(这个表有可能是多个连接操作产生的结果表),同样,所有查询语句中JOIN右侧或之后的表也只作为一个表。,153/130,示例:设有三张表,154/130,查询1,查询全部供应商的VendorName及其在Vendors和VendorAddress两个表中的VendorID。 SELECT v.VendorID, v.VendorName, va.VendorID FROM Ve

59、ndors v LEFT OUTER JOIN VendorAddress va ON v.VendorID = va.VendorID,155/130,查询2,查询全部供应商的VendorName及其Address。由于并不要求返回全部的地址,因此对Address表可以采用内连接 SELECT v.VendorName, a.Address FROM Vendors v LEFT OUTER JOIN VendorAddress va ON v.VendorID = va.VendorID JOIN Address a ON va.AddressID = a.AddressID,丢失了Vendor3,156/130,修改查询2,(1)添加一个外连接操作 SELECT v.VendorName, a.Address FROM Vendors v LEFT OUTER JOIN VendorAd

温馨提示

  • 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
  • 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
  • 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
  • 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
  • 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
  • 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
  • 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

评论

0/150

提交评论