• 1. 数据库系统原理与应用 第四讲
  • 2. 上节课的回顾表的创建和使用 记录的新增和删除 视图的基本操作 数据定义语言 数据操纵语言
  • 3. 建立数据库的目的是为了查询数据, 因此, 可以说数据查询是数据库的核心操作。 而SQL的核心语句, 是执行查询操作的SELECT语句。 该语句具有灵活的使用方式和丰富的功能, 其一般格式为:SELECT [ ALL | DISTINCT] 列名表 FROM 表名或视图名 [ WHERE 条件表达式] [ GROUP BY 分组表达式 [ HAVING 分组表达式 ] ] [ ORDER BY 次序表达式 [ ASC ┃DESC ] ]9.5 数据查询语言
  • 4. 根据WHERE子句的条件表达式, 从FROM子句指定的数据表或视图中, 找出满足条件的元组, 再按SELECT子句中的目标列表达式, 选出元组中的属性值形成结果表。 如果有GROUP子句, 则将结果按列名1的值进行分组, 该属性列值相等的元组为一个组。 如果GROUP子句带有HAVING短语, 则只有满足指定条件的组才予输出。 如果有ORDER子句, 则结果表还要按列名2的值升序或降序排序。含义是:SELECT语句的一般格式
  • 5. 一、投影查询9.5 数据查询语言 选择查询表的全部列或部分列,列名之间用“,”分隔。 选择全部列时可以不写列名,用“*”代替。 如果要在结果中去除重复行的话,需要在列名前加上关键字DISTINCT。
  • 6. 本例对以下购进凭证表 进行SQL查询操作。9.5 数据查询语言
  • 7. SELECT * FROM 购进凭证表选表中的所有字段显示:SELECT * FROM 表名一个最简单的SELECT查询语句9.5 数据查询语言
  • 8. SELECT 商品编号, 单价, 数量 FROM 购进凭证表●选表中的部分字段输出显示: SELECT 购进凭证表.商品编号, 购进凭证表.单价, 购进凭证表.数量 FROM 购进凭证表●字段前也可标明表名:选择若干字段(投影查询)9.5 数据查询语言
  • 9. SELECT 商品编号, 单价, 数量 AS 购进数量 FROM 购进凭证表可以为输出的字段另外取名(但不会改变原表字段名):为输出字段另外取名9.5 数据查询语言
  • 10. 二、选择查询9.5 数据查询语言 按照一定的查询条件,从表中提取符合条件的记录。 条件要放在关键字WHERE后面。 多个条件可以用逻辑运算符AND、OR、NOT来连接。
  • 11. SELECT * FROM 购进凭证表 WHERE 大类名 = ‘饼干’ 只显示购进凭证表中“饼干”大类的记录9.5 数据查询语言
  • 12. 三、排序查询9.5 数据查询语言 对从表中查询出来的结果,按一个列或多个列进行排序。 排序的列名要放在关键字ORDER BY后面。 默认排序方式为升序,关键字是ASC,也可指定为降序,关键字为DESC。
  • 13. SELECT 商品编号, 大类名, 单价, 数量 FROM 购进凭证表 ORDER BY 大类名, 单价 DESC SELECT * FROM 购进凭证表 ORDER BY 大类名 ●显示时相同大类名排在一起 ●对多字段排序, 并逐个指定升降序 输出时相同大类名排在一起, 同一大类商品, 按单价降序排序 降序默认ASC 升序9.5 数据查询语言
  • 14. 四、使用聚合函数9.5 数据查询语言 实现数据统计功能,对一组值进行计算并返回一个单一的值。 聚合函数常与关键字GROUP BY一起使用。 可以进一步利用关键字HAVING来选取满足条件的分组。
  • 15. SQL提供了如下合计函数可对字段做直列运算: 求平均值 AVG( ) 求最大值 MAX( ) 求最小值 MIN( ) 求总和 SUM( ) 统计记录条数 COUNT(*) 指定数据的标准差 STDEV( ) 总体标准差 STDEVP( )9.5 数据查询语言
  • 16. SELECT AVG( 单价 ) AS 平均价, SUM( 数量 ) AS 总数量 FROM 购进凭证表 统计该表所有记录的平均单价、总数量:9.5 数据查询语言
  • 17. 按指定字段进行分组小计 (又称分组汇总): SELECT 大类名, AVG(单价) AS 平均价, SUM(数量) AS 总数量 FROM 购进凭证表 GROUP BY 大类名9.5 数据查询语言
  • 18. SELECT SUM(数量) AS 总数量 FROM 购进凭证表全 部 记 录总数量对表中所有记录进行汇总9.5 数据查询语言
  • 19. SELECT 大类名, SUM(数量) AS 饼干数量 FROM 购进凭证表 WHERE 大类名 = ‘饼干’ GROUP BY 大类名饼干饼干的总数量对表中某一类商品记录进行汇总9.5 数据查询语言
  • 20. SELECT SUM(数量) AS 总数量 FROM 购进凭证表 WHERE 大类名 = ‘饼干’ OR 大类名 = ‘酒类’ 饼干酒类对表中某二类商品记录进行汇总9.5 数据查询语言
  • 21. SELECT 大类名, SUM(数量) AS 总数量 FROM 购进凭证表 WHERE 大类名 = ‘饼干’ OR 大类名 = ‘酒类’ GROUP BY 大类名 饼干酒类对表中某二类商品记录分组汇总这时一定要有大类名9.5 数据查询语言
  • 22. SELECT 大类名, SUM(数量) AS 大类数量 FROM 购进凭证表 GROUP BY 大类名饼干酒类饮料每类商品,各有一个总数量按类分组汇总, 每组产生一条记录::::9.5 数据查询语言
  • 23. 举例:①输出各大类的总数量与凭证数(分组小计) ②只输出汇总后数量总和大于4000的组 SELECT 大类名, SUM(数量), COUNT( * ) FROM 购进凭证表 GROUP BY 大类名 HAVING SUM(数量) > 4000分组汇总时HAVING 短语的使用9.5 数据查询语言
  • 24. HAVING 短语对 GROUP子句的限制 ●WHERE 条件 用于选择满足条件的记录, 只有符合条件的记录, 才可以参加运算 ●HAVING 条件, 用于选择满足条件的组, 只有符合条件的组, 才输出到屏幕上, 或存入新表等方向. ●要使用HAVING上述功能, 必须: ▲与分组汇总GROUP BY相配合; ▲在HAVING条件式里必须出现求汇总的集合函数, 比如 SUM(字段名). 而WHERE条件中不允许使用字段的聚合函数9.5 数据查询语言
  • 25. 五、字段间运算9.5 数据查询语言 可以通过对原始列之间的运算, 把结果作为一个新列来输出。
  • 26. SELE 商品编号, 单价 , 数量 , 单价 * 数量 AS 金额 FROM 购进凭证表 新产生的运算字段给输出字段取名为金额 目标列表达式9.5 数据查询语言
  • 27. SELECT SUM( 数量 ) AS 总数量, SUM( 单价 *数量 ) AS 总金额, COUNT(*) AS 凭证总数 FROM 购进凭证表 已知:金额=单价×数量 统计该表所有记录的总数量、总金额、凭证总数:9.5 数据查询语言
  • 28. 六、表的连接查询9.5 数据查询语言 实现若干个表数据的联合查询。 SELECT 列名1,列名2,… FROM 表1,表2,… WHERE 连接条件
  • 29. SELECT 学生表 .姓名, 成绩表.百分制 FROM 学生表 , 成绩表 WHERE 学生表.学号 =成绩表. 学号查询结果二表等值连接查询:9.5 数据查询语言
  • 30. 例:显示学生的姓名、课程名、成绩例:只显示百分制成绩不及格学生的以上字段三表连接查询:AND 成绩表. 百分制<609.5 数据查询语言SELECT 学生表.姓名, 课程表.课程名,成绩表.百分制 FROM 学生表 ,成绩表,课程表 WHERE 学生表 .学号 =成绩表. 学号 AND 成绩表. 课程编号=课程表. 课程编号
  • 31. 给表取别名SELECT 学生表.姓名, 课程表.课程名,成绩表.百分制 FROM 学生表 , 成绩表,课程表 WHERE 学生表 .学号 =成绩表. 学号 AND 成绩表. 课程编号=课程表. 课程编号取别名后取别名前SELECT S.姓名, C.课程名,SC.百分制 FROM 学生表 S , 成绩表 SC,课程表 C WHERE S .学号 =SC. 学号 AND SC. 课程编号=C. 课程编号9.5 数据查询语言
  • 32. 书上P118例9.26SELECT score.*, grade.rank FROM score, grade WHERE degree between low and upp ORDER BY grade.rank非等值联接9.5 数据查询语言查询结果
  • 33. 自连接书上P119例9.27SELECT X.* FROM score X, score Y WHERE X.cno=‘3-105’ AND X.degree>Y.degree AND Y.sno=‘109’ AND Y.cno=‘3-105’ ORDER BY X.degree DESC查询结果 输出Score表中,选修3-105课程、成绩高于学号109成绩的记录9.5 数据查询语言
  • 34. 七、子查询9.5 数据查询语言 当一个查询是另一个查询的条件时,称之为子查询。 子查询常用在WHERE子句中。 子查询前一般接关键字IN、ANY、ALL等
  • 35. 9.5 数据查询语言例:列出购进凭证表中, “001147”商品所属大类的所有凭证记录.
  • 36. ●上层查询块称为主查询, 下层查询块称为子查询 ●主查询的选择标准, 是基于子查询的结果;总是先执行子查询 ●当子查询返回唯一值时, 可用比较运算符, 比如本题用等号连接二个查询 当子查询返回一组值时, 应该用IN、ANY等连接二个查询.解决时要分为二步: 先查出该编号商品所属的大类名 再利用前面查询的结果组成主查询的条件 SELECT * FROM 购进凭证表 WHERE 大类名 = ( SELECT 大类名 FROM 购进凭证表 WHERE 商品编号 = ‘0001147’ )9.5 数据查询语言返回一个值的子查询
  • 37. SELECT * FROM 成绩表 WHERE 课程编号 IN (SELECT 课程编号 FROM 成绩表 WHERE 学号=‘97101000’ ) 第一, 先查出成绩表中该学生所选的所有课程记录; 第二, 因为子查询的结果有多条, 所以主查询的条件连接词用IN 如果要求不在返回的组中,则用 NOT IN.9.5 数据查询语言举例:列出成绩表中,选修过学号“97101000”的学生所选课程的所有成绩.返回多个值的子查询
  • 38. SELECT * FROM 购进凭证表 WHERE 单价 > ALL ( SELECT 单价 FROM 购进凭证表 WHERE 大类名 =‘饼干’ ) 如果单价只要比饼干中任一单价高就可以输出, 则用 ANY.举例:列出购进凭证表中, 单价比饼干类商品中所有单价高的凭证记录.9.5 数据查询语言
  • 39. 八、相关子查询9.5 数据查询语言 前面的例子中,每个子查询仅执行一次,返回的值构成主查询的筛选条件。 有的查询中,子查询不只执行一次,主查询在逐行判断每个待选行时,都必须以待选行中某字段的当前值,去执行一次子查询,这类子查询被称为相关查询。
  • 40. 举例:输出成绩比该课程平均成绩低的学生成绩表。 SELECT A.* FROM 成绩表 A WHERE 百分制 < (SELECT AVG(百分制) FROM 成绩表 B WHERE A.课程编号=B.课程编号)9.5 数据查询语言相关子查询
  • 41. 九、带EXISTS测试的子查询9.5 数据查询语言 EXISTS一般用在WHERE子句中,其后紧跟一个SQL子查询,从而构成一个条件,该子查询的结果集中如果不为空,则产生逻辑真值”true”,否则产生假值“false”。 关键字EXISTS前不能有列名、常量或其他表达式。 由EXISTS引入的子查询的选择列表一般由‘*’组成。
  • 42. 举例:输出所有任课教师的姓名和单位。 SELECT A.tname AS 姓名, depart AS 单位 FROM teacher A WHERE EXISTS (SELECT * FROM course B WHERE A.tno=B.tno)查询结果教师表课程表9.5 数据查询语言
  • 43. 该查询涉及表teacher和表course两个关系,在处理时,先从教师表中依次取出每个元组的教师编号值;然后用此值去检查课程表中是否有该教师编号的元组;若有,则子查询的Where条件为真,该教师元组中的姓名和单位应在结果集中。 SELECT A.tname AS 姓名, depart AS 单位 FROM teacher A WHERE EXISTS (SELECT * FROM course B WHERE A.tno=B.tno)9.5 数据查询语言
  • 44. 十、空值及其处理9.5 数据查询语言 空值NULL是指未知的值,空值不包括0、一个或多个空格组成的字符串。 实际操作时,下列情况会使一列成为NULL: 其值未知 其值不存在 列对表行不可用
  • 45. 9.5 数据查询语言 检测空值用 : 表达式 IS NULL 检测非空值用 : 表达式 IS NOT NULL举例:查询所有未参加考试的学生成绩记录。 SELECT * FROM score WHERE degree IS NULL查询结果
  • 46. 9.5 数据查询语言处理空值的函数 : ISNULL(check_expr,repl_value)举例:查询所有学生的成绩,并将空值作为0分。 SELECT sno AS ‘学号’, cno AS ‘课程号’, isnull(degree,0) AS ‘分数’ FROM score
  • 47. 一、标识符 标识符是值用来定义服务器、数据库、数据库对象和变量等的名称。 1.常规标识符 不需要使用分隔标识符进行分割的标识符。 2.分隔标识符 SQL中分隔符为[]或"",对于不符合标识符规则的标识符必须用分隔标识符进行分隔。 如:SELECT * FROM [My Table] WHERE [order]=10 9.6 T-SQL程序设计基础
  • 48. 3.使用标识符 SQL Server中的每个内容都可带有标识符,服务器、数据库和数据库对象——包括表、视图、列、索引、触发器、过程、约束、规则等等都有标识符。大多数对象要求带有标识符,但对有些对象,标识符是可选的。 一个对象的全称语法格式为: Server.database.owner.object 9.6 T-SQL程序设计基础
  • 49. 二、数据类型 1.系统数据类型 整数型:tinyint、smallint、int、bigint 小数型:decimal [ (p [ , s] ) ]、numeric [ (p [ , s] ) ] p是精度,在1到38间,s是小数位数,在0到p之间 近似数值型:float [ (n) ]、real n是科学计数法位数的位数 字符型:char [ (n) ]、varchar [ (n) ]、text n是字节数,在1到8000之间 逻辑型:bit 货币型:money、smallmoney 二进制数据型:binary[(n)]、varbinary[(n)]、image 日期时间型:datetime、smalldatetime9.6 T-SQL程序设计基础
  • 50. 2.用户定义数据类型 这种数据类型是根据基本数据类型来定义的,定义时要提供三个参数:名称、依据的系统数据类型、是否允许为空。 可以用系统存储过程sp_addtype来创建。 sp_addtype [ @typename= ] type , [ @phystype= ] system_data_type, [ , [ @nulltype = ] ‘null_type’ ] 9.6 T-SQL程序设计基础
  • 51. 在SQL Server 2005中,所有系统数据类型名称是不分大小写的。 例: EXEC sp_addtype telephone,’varchar(24)’,’NULL’ EXEC sp_addtype fax,’varchar(20)’,’NOT NULL’ 删除用户定义数据用sp_droptype存储过程 sp_droptype fax9.6 T-SQL程序设计基础
  • 52. 三、变量 1.局部变量 局部变量前面有一个@符号,用DECLARE来定义,用SET或SELECT来赋值。9.6 T-SQL程序设计基础USE school DECLARE @f float,@cn char(8),@no char(5) SET @f=85 SELECT @cn=‘3-105’ SELECT @no=sno FROM score WHERE cno=@cn AND degree>=@f ORDER BY degree DESC PRINT @no SELECT @f=(SELECT MAX(degree) FROM score WHERE degree IS NOT NULL) PRINT @f赋值定义变量把结果集中最后一个值赋给变量把子查询的结果赋给变量
  • 53. 2.全局变量 全局变量前面有两个@符号,由系统定义和维护。 @@FETCH_STATUS 返回FETCH语句游标状态 @@identity 返回最新插入的identity列值 @@VERSION 返回服务器日期、版本 全局变量用户不能声明和赋值 同一时刻同一全局变量在不同会话中的值是不同的 局部变量和全局变量名称不能相同9.6 T-SQL程序设计基础
  • 54. 四、运算符 1.算术运算符:+、-、×、/、% 2.赋值运算符:= 3.按位运算符:&、|、^ 4.比较运算符:>、<、=、!=、!<、!>等等 5.逻辑运算符:ALL、AND、ANY、EXISTS、 BETWEEN、IN、LIKE、NOT、OR、SOME 6.字符串连接运算符:+ 7.一元运算符:+、-、~9.6 T-SQL程序设计基础
  • 55. 在20至30之间 BETWEEN 20 AND 30 不在20至30之间 NOT BETWEEN 20 AND 30确定范围确定集合 字段值在集合中 IN ( ‘饼干’, ‘酒类’ ) 字段值不在集合中 NOT IN ( ‘饼干’, ‘酒类’ ) 举例: WHERE 单价 BETWEEN 20 AND 30 WHERE 单价 NOT BETWEEN 20 AND 30举例: WHERE 大类名 IN ( ‘饼干’ , ‘酒类’ )9.6 T-SQL程序设计基础
  • 56. 通配符 %、_、[ ]、 [^]举例:查所有姓李的名字为2个字的学生记录 WHERE 姓名 LIKE ‘李_’举例:查课程含“原理”的记录,比如“数据库原理”、“微机原理与应用” WHERE 课程 LIKE ‘%原理%’举例:查字符型四位编号字段中, 末位是1的记录, 如“ 9801”、“9831”, 而“981”和“ 9813”都不符合 WHERE 编号 LIKE ‘___1’9.6 T-SQL程序设计基础举例:查所有姓李和姓王的学生记录 WHERE 姓名 LIKE ‘[李,王]%’
  • 57. 五、批处理 批处理是包含一个或多个T-SQL语句的组,批处理的结束语句是GO。 注意:局部变量的作用域被限制在一个批处理中。9.6 T-SQL程序设计基础
  • 58. 批处理遵循规则: CREATE DEFAULT 、CREATE PROCEDURE 、CREATE RULE 、CREATE TRIGGER 、CREATE VIEW等语句不能在批处理中与其他语句组合使用,所有跟在其后的语句将被解释为第一个CREATE语句定义的一部分; 不能在同一个批处理中更改表结构,再引用新添加的列; 如EXECUTE语句是批处理中的第一句,则可不需要EXECUTE关键字。9.6 T-SQL程序设计基础
  • 59. 六、注释 --表示从双连字符开始一直注释到行尾 /*…*/表示该范围内所有信息都是注释 9.6 T-SQL程序设计基础
  • 60. 七、控制流语句 用于控制T-SQL语句、语句块和存储过程的执行流,包括下列语句: BEGIN…END 定义语句块 IF…ELSE 条件处理 CASE 分支 WHILE 循环 GOTO 无条件跳转 WAITFOR 延迟 BREAK 跳出循环 CONTINUE 重新开始循环 PRINT 屏幕输出9.6 T-SQL程序设计基础
  • 61. 八、函数 1.内置函数 字符串函数:LTRIM、RTRIM、LEFT、 RIGHT、 SUBSTRING、 STR、LOWER、UPPER、STUFF 日期和时间函数:GETDATE、DATEDIFF、YEAR、MONTH、DAY 数学函数:ABS、ROUND、RAND 系统函数:ISNULL、CAST、CONVER、COL_NAME9.6 T-SQL程序设计基础
  • 62. 9.6 T-SQL程序设计基础USE school DECLARE @columnname varchar(20),@i int,@columnlen int SET @i=0 WHILE @i<=5 BEGIN SET @columnname=COL_NAME(OBJECT_ID('student'),@i) SELECT @columnlen=COL_LENGTH('student',@columnname) PRINT @columnname+','+CAST(@columnlen AS char(3)) SET @i=@i+1 END
  • 63. 2. 用户定义函数 SQL Server 2005允许用户创建自己的用户定义函数,这种函数由一个或多个T-SQL语句组成。用CREATE FUNCTION语句创建,DROP FUNCTION除去。9.6 T-SQL程序设计基础提高 用户创建一个自定义函数后,SQL Server系统表sysobjects中会有一条相应的记录,其中name列就是该自定义函数的名称,type列为对象类型,“FN”表示是标量函数,“IF”表示是内联表函数,“TF”表示为表函数。
  • 64. 9.6 T-SQL程序设计基础1)标量函数:只有一个返回值调用
  • 65. 9.6 T-SQL程序设计基础2)内联表值函数:返回一个记录集,只能有一个SELECT语句调用
  • 66. 9.6 T-SQL程序设计基础3)多语句表值函数:返回一个表,但可包含多个T-SQL语句调用
  • 67. 自学要求与上课安排 ●自学与上机内容: 自学第10章 完成上机实习:做完上机练习3 ●要求: 在上机时,显示你的第2部分结果。
  • 68. (本页无文本内容)