SQL Server中的数据查询课件

1、第五章 SQL Server中的数据查询,软件工程系 许爽,1,PPT学习交流,章节脉络,2009-10-6,select语句中的数学问题,1,select语句,2,连接查询,3,嵌套查询,4,联合,5,2,PPT学习交流,5.1 SQL Server中的数学问题,一、SQL Server中的变量,2009-10-6,全局变量,SQL中 的变量,局部变量,全局变量是系统提供的,局部变量是用户自定义的,3,PPT学习交流,5.1 SQL Server中的数学问题,范围：作用于系统内部使用，不局限于某一程序,可为任 意程序随时调用 注意: 全局变量不是用户定义，是在服务器级定义的 用户只能使用预先

2、定义的全局变量 引用全局变量时，必须以标记符开头 局部变量的名称不能与全局变量的名称相同,2009-10-6,全局变量,4,PPT学习交流,5.1 SQL Server中的数学问题,作用：程序内部使用的变量数据 范围：可作为计数器计算循环执行的次数 利用局部变量可以保存数据值 局部变量标志： 使用变量的方法：先定义后赋值 定义方式：declare 变量名1 数据类型，变量名2 赋值方式：select 变量名=value（可给多变量赋值） set 变量名=value（只给一个变量赋值） 注意：输出看结果用select,2009-10-6,局部变量,5,PPT学习交流,5.1 SQL Server

3、中的数学问题,【例】创建局部变量var1、var2，并赋值，然后输出 变量的值。 DECLARE var1 char(20),var2 char(20) SET var1中国 SET var2var1+是一个伟大的国家 SELECT var1,var2 Go 回答：输出结果是什么？ 如何实现输出“中国是一个伟大的国家”,2009-10-6,6,PPT学习交流,5.1 SQL Server中的数学问题,（1）- - -：用于单行注释 （2）/*/：用于标识多行文字作注释 解释： /*：用于注释文字的开头 */：用于注释文字的结尾,2009-10-6,7,PPT学习交流,5.1 SQL Serve

4、r中的数学问题,二、SQL Server中的运算符,2009-10-6,算数运算符,赋值运算符,位运算符,关系运算符,逻辑运算符,字符串连接运算符,+，-，*，/，%，*,=,&，|,=,!等,Not,and,or,+,8,PPT学习交流,5.1 SQL Server中的数学问题,运算符：+、-、*、/、%、* 注意：+和也可用于对日期值进行算术运算 【例】求学生的年龄。 USE XSCJ SET NOCOUNT ON DECLARE startdate datetime SET startdate = getdate() SELECT startdate出生时间 AS 年龄 FROM XS,

5、2009-10-6,算数运算符,9,PPT学习交流,5.1 SQL Server中的数学问题,描述：指给局部变量赋值的SET和SELECT语句中使用的 “=”,2009-10-6,赋值运算符,10,PPT学习交流,5.1 SQL Server中的数学问题,描述：在两个表达式之间执行位操作，表达式为整型或 整型兼容的数据类型 具体的运算规则：,2009-10-6,位运算符,11,PPT学习交流,5.1 SQL Server中的数学问题,【例】在master数据库中，建立表bitop，并插入一行， 然后将a字段和 b字段列上值进行按位与运算。 USE master CREATE TABLE bit

6、op ( a int NOT NULL, b int NOT NULL) INSERT bitop VALUES (168, 73) SELECT a&b, a|b,ab FROM bitop,2009-10-6,12,PPT学习交流,5.1 SQL Server中的数学问题,描述：结果为逻辑值，TRUE、FALSE及UNKNOWN 基本操作符：=,=,!= in：检索匹配列表中的值 like：检索匹配字符样式的数据 betweenand：检索两值之间的内容 is null：检索空数据,2009-10-6,关系运算符,13,PPT学习交流,5.1 SQL Server中的数学问题,描述：对某个

7、条件进行测试，运算结果为TRUE或 FALSE 具体的运算符：AND：遇假则假 OR：遇真则真 NOT：遇真则假，遇假则真 ALL：全为真时则真 ANY：任一为真则真 SOME：一些为真则真,2009-10-6,逻辑运算符,14,PPT学习交流,5.1 SQL Server中的数学问题,描述：通过运算符“+”实现两个字符串的联接运算 【例】多个字符串的联接。 USE XSCJ SELECT (学号+ , + SPACE(1) + 姓名) AS 学号及姓名 FROM XS WHERE SUBSTRING(学号, 1, 2) = 19,2009-10-6,字符串连接运算符,15,PPT学习交流,5

8、.1 SQL Server中的数学问题,描述：用运算符将常量,变量,函数连接起来的式子 运算符的优先级别： ()算术运算符比较运算符位运算符逻辑运算符,2009-10-6,表达式,16,PPT学习交流,5.1 SQL Server中的数学问题,2009-10-6,优先级：,17,PPT学习交流,5.1 SQL Server中的数学问题,三、SQL Server中的函数,2009-10-6,数学函数,字符串函数,时间日期函数,转换函数,系统函数,集合函数,18,PPT学习交流,5.1 SQL Server中的数学问题,ACOS：求余弦值对应的角度的弧度值 SIGN(n)：求n的符号，正，负，或0

9、 ABS(n)：求n的绝对值 CEILING(n)：返回大于等于n的最小整数 FLOOR(n)：返回小于等于n的最大整数 ROUND(n,m)：对n做四舍五入处理，保留m位 （m为整数，为正时处理小数位，为负处理整数位） SQRT(n)：求n的平方根 RAND：返回01之间的随机值,2009-10-6,数学函数,19,PPT学习交流,5.1 SQL Server中的数学问题,UPPER：小写字符串转换为大写字符串 LOWER：大写字符串转换为小写字符串 SPACE：产生指定个数的空格组成字符串 STUFF(S1,n,m,S2)：从S1中指定位置多长用S2代替 LTRIM：删除字符串前面的空格

10、RTIRM：删除字符串后面的空格 SUBSTRING（S1，n，m）：从指定位置搜索多长的字符串 LEFT：从左边开始搜索指定个数的子串 RIGHT：从右边开始搜索指定个数的子串 ASCII：字符串最左端字符的ASCII代码值 CHAR：ASCII代码值转换为字符,2009-10-6,字符串函数,20,PPT学习交流,5.1 SQL Server中的数学问题,GETDATE()：给出系统当前的日期的时间 以下时间日期函数写法：函数名(月/日/年) DAY(DATE)：从date日期和时间类型数据中提取天数 MONTH(DATE)：从date日期和时间类型数据中提取月份数 YEAR(DATE)：

11、从date日期和时间类型数据中提取年份数,2009-10-6,时间日期函数,21,PPT学习交流,5.1 SQL Server中的数学问题,DB_ID,DB_NAME：获得指定数据库的ID号或名称 USER_ID,USER_NAME：获得指定用户的ID号或名称 DATALENGTH：获得指定表达式占用的字节数,2009-10-6,系统函数,22,PPT学习交流,5.1 SQL Server中的数学问题,AVG：计算一列值的平均值 COUNT：统计一列中值的个数 MAX：求一列值中的最大值 MIN：求一列值中的最小值 SUM：计算一列值的总和,2009-10-6,集合函数,23,PPT学习交流,

12、5.2 select语句,2009-10-6,SELECT 指定要选择的列或行及其限定 INTO 新表名 FROM 指定表或视图 WHERE 指定查询条件 GROUP BY 指定分组表达式 HAVING 指定分组统计条件 ORDER BY 排序字段 ASC | DESC ,24,PPT学习交流,5.2 select语句,一、选择列的应用（select）,选择指定列,修改列标题,替换查询结果数据,计算列值,消除重复行,限制结果集数据个数,25,PPT学习交流,5.2 select语句,1、选择表中指定列：使用SELECT选择一个表中某些列， 各列名间以逗号分隔 注意：若选择所有列，用*表示 【例

13、】查询student数据库的stud_info表中各个学生的 姓名、地址和入学成绩 解：use student SELECT name,address,mark FROM stud_info,2009-10-6,26,PPT学习交流,5.2 select语句,【例】查询stud_info表中男同学的学号、姓名和入学分 数。查stud_info表中的所有列。 解：SELECT stud_id,name,mark FROM stud_info WHERE gender=1 SELECT * FROM stud_info,2009-10-6,27,PPT学习交流,5.2 select语句,2、修改查

14、询结果中的列标题 两种方法：原字段名 as 新字段名 新字段名=原字段名 注意：当自定义列标题中有空格时，须用引号括起 【例】查询stud_info表中入学分数为560的学生学号、 姓名和出生时间,结果中各列的标题分别指定为学 号、姓名和出生日期。 解：SELECT stud_id AS 学号,name AS 姓名, birthday AS 出生日期 FROM stud_info WHERE mark=560 解：SELECT 学号=stud_id,姓名=name,出生日期=birthday FROM stud_info WHERE mark=560,2009-10-6,28,PPT学习交流,

15、5.2 select语句,【例】查询新设置的标题中包含空格的描述 解：select 学 号=stud_id,姓 名=name from stud_info where mark=560,2009-10-6,29,PPT学习交流,5.2 select语句,3、替换查询结果中的数据：所查询的某些列得到一种 概念而不是具体数据，使 用case语句 语法格式：CASE WHEN 条件1 THEN 表达式1 WHEN 条件2 THEN 表达式2 ELSE 表达式 END,2009-10-6,30,PPT学习交流,5.2 select语句,【例】查询stud_info表中各同学的学号、姓名和入学成 绩，对

16、入学成绩按以下规则进行替换：若入学分 数为空值，替换为“试学生”；若若入学分数小 于等于560分，替换为“合格生”；若入学成绩大 于560分，替换为“高分生”，列标题改为等级 解：SELECT stud_id,name, 等级= CASE WHEN mark IS NULL THEN 试学生 WHEN mark560 THEN高分生 END FROM XS,2009-10-6,31,PPT学习交流,5.2 select语句,另解： SELECT stud_id,name, 等级= CASE WHEN mark IS NULL THEN 试学生 WHEN mark=560 THEN合格生 ELS

17、E 高分生 END FROM XS,32,PPT学习交流,5.2 select语句,4、计算列值 注意：计算列值时使用算术操作符：+，-，*，/，%。其 中+，-，*，/可任意用于任何数字类型的列 （int，smallint，tinyint，decimal，numeric， float，real，money，smallmoney），而%可用于 以上除money和smallmoney以外的其它数据类型 【例】按120分计算成绩显示。 解：SELECT stud_id,course_id, 成绩=grade*1.20 FROM stud_grade,2009-10-6,33,PPT学习交流,5.2

18、 select语句,5、消除结果集中的重复行 格式：SELECT DISTINCT 列名 DISTINCT：对结果集中的重复行只选择一个 格式：SELECT ALL 列名：保留结果集中的所有行 注意：一般不指定，系统默认为all，所有行指记录而非 字段全部保留 【例】对student数据库的stud_info表选择入学成绩都 是多少分？ 解： SELECT DISTINCT mark FROM stud_info,2009-10-6,34,PPT学习交流,5.2 select语句,6、限制结果集返回行 基本格式为：TOP n TOP n PERCENT 【例】对student数据库的stud_

19、info表选择姓名、性别 和联系电话,只返回结果集的前6行 解：SELECT TOP 6 name，gender，telcode FROM stud_info 另解：select 50 percent name，gender，telcode From stud_info,2009-10-6,35,PPT学习交流,5.2 select语句,二、选择行（where条件设定）,2009-10-6,比较运算,模式匹配,范围比较,空值比较,嵌套查询,36,PPT学习交流,5.2 select语句,运用比较运算符（=、=、!=、!） 用于比较两个表达式值 格式：表达式 比较运算符 表达式 注意：比较操作是

20、关系运算，返回逻辑值true或false,2009-10-6,比较运算,37,PPT学习交流,5.2 select语句,【例】查询student数据库stud_info表中入学成绩大于 560的女同学的情况 解：SELECT * FROM stud_info WHERE gender=0 and mark560,2009-10-6,38,PPT学习交流,5.2 select语句,LIKE谓词：指出一个字符串是否与指定的字符串相匹配 格式：表达式 like|not like 表达式 注意：like进行模式匹配时，使用通配符进行模糊查询,2009-10-6,模式匹配,39,PPT学习交流,5.2

21、select语句,【例】查询student数据库stud_info表中家住北京的学 生情况。查询1986年出生的张姓同学的家庭住址 解：select * from stud_info where address like 北京% select address from stud_info where year(birthday)=1986 and name like 张%,2009-10-6,40,PPT学习交流,5.2 select语句,【例】查询student数据库中学号尾数为1或3的学生信息 解：select * from stud_info where right(stud_id,1)

22、 like13 select * from stud_info where stud_id like%13,41,PPT学习交流,5.2 select语句,关键字：BETWEEN和IN BETWEEN ：要查询的条件是某个值的范围 格式：表达式 NOT BETWEEN 表达式1 AND 表达式2 解释：表示某个值或表达式在between和and所设定的范 围内,2009-10-6,范围比较,42,PPT学习交流,5.2 select语句,【例】查询student数据库stud_info表中不在1986年出 生的学生情况。 解：select * from stud_info where year

23、(birthday)!=1986 select * from stud_info where birthday not between 1986-1-1 and 1986-12-31,43,PPT学习交流,5.2 select语句,IN：指定一个值表，列出所有可能值，看是否匹配 格式：表达式 in(值1，值2，值3，) 说明：in关键字既可指定范围，也可以表示子查询,44,PPT学习交流,5.2 select语句,【例】查询student数据库stud_info表中北京、上海、 湖南学生的情况。 解：select * from stud_info where address like 北京%

24、or address like 上海% or address like 湖南% select * from stud_info where substring(address,1,2) in (北京,上海,湖南) select * from stud_info -where address like 北京,上海,湖南%,45,PPT学习交流,5.2 select语句,需要判定一个表达式的值是否为空值时，用IS NULL表示 格式为：表达式 IS NOT NULL 注意：当不使用NOT时，若表达式表达式的值为空值，返 回TRUE，否则返回FALSE；使用NOT时，结果反之。 【例】查询stude

25、nt数据库中入学分数不定的学生情况。 解：SELECT * FROM stud_info WHERE mark IS NULL,空值比较,46,PPT学习交流,5.2 select语句,嵌套查询,in子查询,比较子查询,exists子查询,一个select-from-where 中，使用另一个查询结果作 为条件的一部分，称为子查询 （嵌套查询） 处理方式：方法由里向外 注意：子查询结果不显示出来， 子查询的表可为父查询没 有选取的表,47,PPT学习交流,5.2 select语句,in子查询,IN子查询：判断一个给定值是否在子查询结果集中 格式为：父查询 NOT IN (子查询) 注意：IN和

26、NOT IN子查询只能返回一列数据。 in子查询先执行子查询部分，再执行外查询部分 父查询和子查询操作的字段必须为同一个,48,PPT学习交流,5.2 select语句,【例】student数据库中有描述课程情况表lesson_info 和描述学生成绩表的表stud_grade，要查找选修 了课程号为0401020201的课程的学生的情况 解：SELECT * FROM student WHERE stud_id IN ( SELECT stud_id FROM stud_grade WHERE course_id=0401020201),49,PPT学习交流,5.2 select语句,【例】

27、查找未选修计算机专业英语的学生的情况。 解：SELECT * FROM stud_info WHERE stud_id NOT IN (SELECT stud_id FROM stud_grade WHERE course_id IN (SELECT course_id FROM lesson_info WHERE course_name=计算机专业英语) ),50,PPT学习交流,5.2 select语句,比较子查询：表达式的值与子查询的结果进行比较运算 格式：表达式 比较符 ALL|SOME|ANY(嵌套查询) 比较符： | = | != | | ! ALL：指定表达式要与子查询结果集中的

28、每个值比较 SOME：比较时有些值为真为真 ANY：比较时有任何一个值为真即为真,比较子查询,51,PPT学习交流,5.2 select语句,【例】查找课程号0401010101的成绩不低于课程号 0401010106的最低成绩的学生的学号。 解：SELECT stud_id FROM stud_grade WHERE course_id=0401010101 AND grade ! ANY (SELECT grade FROM stud_grade WHERE course_id=0401010106),52,PPT学习交流,5.2 select语句,【例】查询年龄最大的学生 解：selec

29、t min（birthday） from stud_info Select * from stud_info Where birthday=all(select birthday Froms stud_info),53,PPT学习交流,5.2 select语句,【例】查找与“钱昆”同年同月同日生的学生姓名 select * from stud_info where birthday=(select birthday from stud_info where name=钱昆) and name !=钱昆,54,PPT学习交流,5.2 select语句,【例】查找同年同月同日生的人 select

30、* from stud_info where birthday in (select birthday from stud_info group by birthday having(count(birthday)1) order by birthday asc,55,PPT学习交流,5.2 select语句,测试子查询的结果是否为空表 注意：子查询结果集不为空，则EXISTS为TRUE，结果集 为空，返回FALSE。NOT EXISTS返回值与EXIST相 反EXIST子查询先执行外查询再多次执行子查询 格式为：NOT EXISTS (嵌套查询),exists子查询,56,PPT学习交流,5

31、.2 select语句,【例】查找选修206号课程的学生姓名。 解：SELECT name FROM stud_info WHERE EXISTS (SELECT * FROM stud_grade WHERE stud_id=stud_grade.stud_id AND course_id=0401010101）,2009-10-6,57,PPT学习交流,5.2 select语句,【例】查找选修了全部课程的学生的姓名 （本例即查找没有一门功课不选修的学生） 解：SELECT 姓名 FROM XS WHERE NOT EXISTS (SELECT * FROM KC WHERE NOT EXI

32、STS (SELECT * FROM XS_KC WHERE 学号=XS.学号 AND 课程号=KC.课程号) ) 解法：无全称量词，使用双重否定代表肯定,58,PPT学习交流,5.2 select语句,在查询中将表设置别名，进行区分和简便写法之用 方式： Select * from stud_info a或 from stud_info as a 注意：当设置别名后，原表中的字段若要加表名.作前 缀，只能用别名.，而不能使用原表名,from子句,59,PPT学习交流,5.2 select语句,【例】查找至少选修了001102号同学所选修的全部课程 的同学的学号。 解：SELECT DISTI

33、NCT 学号 FROM XS_KC AS a1 WHERE NOT EXISTS ( SELECT * FROM XS_KC AS a2 WHERE a2.学号 = 001102 AND NOT EXISTS ( SELECT * FROM XS_KC AS a3 WHERE a3.学号= a1.学号AND a3.课程号 = a2.课程号) ),60,PPT学习交流,5.4 聚合函数,聚合函数：计算表中的数据，返回单个计算结果,max,min,sum,avg,count,61,PPT学习交流,5.3 连接查询,用于求表达式中所有值项总和与平均值 语法格式：SUM/AVG(ALL|DISTINC

34、T字段名) 【例】求选修0401010101课程的学生的平均成绩。 解：SELECT AVG(成绩) AS 平均成绩 FROM stud_grade WHERE course_id=0401010101,sum和avg,62,PPT学习交流,5.3 连接查询,MAX和MIN用于求表达式中所有值项最大 值与最小值 语法格式：MAX/MIN(ALL|DISTINCTexpression) 【例】求选修0401010101课程的学生的最高分和最低分 解：SELECT MAX(成绩) AS 最高分,MIN(成绩)AS 最低分 FROM stud_grade WHERE course_id=040101

35、0101,sum和avg,63,PPT学习交流,5.3 连接查询,连接谓词：select中的where子句使用比较运算符给出连 接条件对表进行连接 注意（1）连接的两个字段必须相同 （2）比较操作符可以是,=,!=,!。 当比较符为“=”就是等值连接，若是在目标 列中去除相同的字段名，则为自然连接 （3）若选择的字段在各个表中是唯一的，则可以省 略字段前面的表名,连接查询,64,PPT学习交流,5.3 连接查询,【例】查找student数据库每个学生的情况以及选修的课 程情况。 解：SELECT stud_info.* , stud_grade.* FROM stud_info,stud_gr

36、ade WHERE stud_info.stud_id=stud_grade.stud_id 【例】自然连接查询 解：SELECT stud_info.* , stud_grade.course_id, stud_grade.成绩 FROM stud_info,stud_grade WHERE stud_info.stud_id=stud_grade.stud_id,65,PPT学习交流,5.3 连接查询,【例】查找选修了206课程且成绩在80分以上的学生姓名 及成绩。 解：SELECT name,grade FROM XS , XS_KC WHERE stud_info.stud_id=st

37、ud_grade.stud_id AND course_id=0401010101 and grade=80 【例】查找选修了“计算机基础”课程且成绩在80分以 上的学生学号、姓名、课程名及成绩。 解：SELECT XS.stud_id,name,course_name,grade from stud_info,stud_grade,lesson_info WHERE stud_info.stud_id=stud_grade.stud_id and lesson_info.course_id=stud_grade.course_id AND course_name=计算机基础AND grade

38、=80,66,PPT学习交流,5.3 连接查询,注意：子查询和连接的区别与联系 （1）相同处：两者可能都要涉及两个或多个表 （2）不同处： A、连接可以含两个或多个表中数据；而带子查询的 select语句的结果只能来自一个表，子查询的结果是 用来作为选择结果数据进行参照的 B、子查询将一个复杂问题分成各个步骤进行操作；连接 的执行速度快,67,PPT学习交流,5.3 连接查询,2、以join关键字指定的连接（用from子句） 格式： 表名inner|left|right|fullouter|cross join 表名 On 相关字段相等,68,PPT学习交流,5.3 连接查询,（1）内连接：按

39、照on指定的连接条件合并两个表，返回 满足条件的记录（行） 【例】查找student数据库每个学生的情况以及选修的课 程情况。 解：SELECT xs.*,xs_kc.* FROM stud_info INNER JOIN stud_grade ON stud_info.stud_id=stud_grade.stud_id 【例】查找选修0401010101课程且成绩在80分以上的学 生姓名及成绩。 解：SELECT name,grade FROM stud_info JOIN stud_grade ON stud_info.stud_id =stud_grade.stud_id WHERE

40、course_id=0401010101 AND grade=80,69,PPT学习交流,5.3 连接查询,【例】用FROM的JOIN关键字查询：查找选修了“计算机 基础”课程且成绩在80分以上的学生学号、姓 名、课程名及成绩。 解：SELECT name,course_name,grade FROM stud_info JOIN stud_grade JOIN lesson_info ON stud_grade.course_id=lesson_info.course_id ON stud_info.stud_id = stud_grade.stud_id WHERE course_name

41、 = 计算机基础 AND grade=80 注意：内连接是系统默认的，可以省略inner关键字 使用内连接之后也可以使用where来指定条件 多表连接时采用中间表放中间，就近原则,70,PPT学习交流,5.3 连接查询,（2）外连接 左外连接（LEFT OUTER JOIN）：结果表中除了包括满足 连接条件的行外，还包括左表的所有行 右外连接（RIGHT OUTER JOIN）：结果表中除了包括满 足连接条件的行外，还包括右表的所有行 完全外连接（FULL OUTER JOIN）：结果表中除了包括满 足连接条件的行外，还包括两个表的所有行 注意：三个外连接中的outer可省 外连接只能对两个表

42、进行查询,71,PPT学习交流,5.3 连接查询,【例】查找所有学生情况，及他们选修的课程号，若学 生未选修任何课，也要包括其情况。 解：SELECT stud_info.* , course_id FROM stud_info LEFT OUTER JOIN stud_grade stud_info.stud_id=stud_grade.stud_id 【例】查找被选修了的课程的选修情况和所有开设的课 程名。 解：SELECT stud_grade.* , course_name FROM stud_info RIGHT JOIN lesson_info ON stud_grade.course_id= lesson_info.course_id,72,PPT学习交流,5.3 连