T-SQL 语言

1. T-SQL语言 Transact-SQL

2. SQL语言的类型 • SQL Structured Query Language • Transact-SQL • PL/SQL

3. SQL语言分类 • DDL • DML • DCL

4. 查询分析器 • 用途 交互设计和测试Transact-SQL语句、批处理和脚本。 • 功能 • 创建查询和其它 SQL 脚本，并针对 SQL Server 数据库执行它们。（"查询"窗口） • 由预定义脚本快速创建常用数据库对象。（模板） • 快速复制现有数据库对象。（对象浏览器脚本功能） • 在参数未知的情况下执行存储过程。（对象浏览器过程执行功能） • 调试存储过程。（T-SQL 调试程序） • 调试查询性能问题。（显示执行计划、显示服务器跟踪、显示客户统计、索引优化向导） • 在数据库内定位对象（对象搜索功能），或查看和使用对象。（对象浏览器） • 快速插入、更新或删除表中的行。（"打开表"窗口） • 为常用查询创建键盘快捷方式。（自定义查询快捷方式功能） • 向"工具"菜单添加常用命令。（自定义"工具"菜单功能）

5. 查询分析器 • 启动 两种方式： • 【开始】－>【程序】－>【查询分析器】 • 在企业管理器的工具菜单运行 • 结构 分为两个窗口，左侧为“对象浏览器”窗口(包括：对象和模板选项卡)，右侧为SQL语句输入窗口。

6. 数据操作 对数据库数据操作，一般是指对表中的数据进行操作。 • 操作类型 添加(insert)、删除(delete)、修改(update)和查询。 • 操作收到的限制或约束 • 用户权限的限制 只有系统管理员，数据库和表对象所有者及被授权的用户才可操作。 • 主外键约束的限制 • Check约束的限制 • 非空(not null)约束的限制 • 各列数据类型的限制 以下将讲解如何使用SQL语句操纵数据，以及要注意的事项

7. 添加数据 • 功能 将新一行数据添加到表或视图。 • 语句格式 insert [into] 表名|视图名 [(列列表)] values( default | null | 表达式) 说明： • 若指定列列表；则VALUES中的数值必须满足值的循序与属性列表中的排列一致。 • 若列列表名省略； values中值的循序与实际表定义中的排列一致。

8. 举例 • 向表roysched 插入数据 INSERT INTO roysched (lorange, title_id ,hirange,royalty) VALUES(0,’BU1032’,5000, 10) • 若hirange， royalty列中的值未知，则可写成： INSERT INTO roysched (lorange, title_id ,hirange,royalty) VALUES(0,’BU1032’,null, null) 或 INSERT INTO roysched (lorange, title_id) VALUES(0,’BU1032’) • 省略列列表，可写成： INSERT INTO roysched VALUES('BU1032',0,null, null)

9. 注意事项 • 注意 • 数值型数据直接书写 • 字符、字符串、日期型数据用单引号括起 • 当列值未知时，用null表示。 • 当表中某属性定义了NOT NULL约束，则该列不能取空值 。 • 必须满足实体完整性约束，也就是PK列要求当前的值是唯一的，即在表中的现存数据中改列的值不能已存在 。 • 必须满足实体参照完整性约束，也就是FK列的值在参照表中必须存在。 • 插入位置：插入的元组（一行数据），由DBMS根据排序决定其插入的位置。

10. 举例 • 简单查询 select * from 表名 将表中的所有数据行列出 • 异常举例 参照实操。 现象 分析

11. 修改数据 • 功能 修改表或视图中的一行或多行满足一定条件的某列或多列数据。 • 语句格式 UPDATE <表名> SET Column_name1=表达式1，Column_name2=表达式2，…，Column_namen=表达式n WHERE 条件 说明： • “Column_name1=表达式”指定需修改的列及修改的数值 • “WHERE 条件“指明需要修改的哪些记录（或元组），当不指明WHERE时，则修改表中的所有记录 。 • 条件表达式一般是由字段组成的逻辑运算,比较运算符包括：‘>’、‘<’、‘=’、‘<>’、‘>=’、‘<=’；逻辑运算符包括：and、or 、not()。

12. 举例 • 将雇员表(employee)中emp_id=‘A-C71970F’ 的雇员名称改为‘kkkk’ • 将职位表(jobs)中的所有职位的最低级别加一，最高级别减一 Update employee Set fname=‘kkkk’ where emp_id=‘A-C71970F’ Update jobs Set min_lvl = min_lvl +1,max_lvl=max_lvl-1

13. 举例-异常 • 参照实操 现象 分析

14. 删除数据 • 功能 删除表或视图中的一行或多行符合一定条件的数据。 • 语句格式 DELETE FROM <表名> WHERE 条件 • 说明 • DELETE字句是删除表中的行(元组)，但并不是删除整个表。 • “WHERE 条件“指明需要删除的哪些记录（或元组），当不指明WHERE时，则删除表中的所有记录 。 • 条件表达式同“数据修改”一节中的条件表达式。

15. 举例 • 删除SALES表中所有stor_id ＝’ 6380’的销售记录。 delete sales where stor_id ＝’ 6380’ • 删除titleauthor 表中的au_id=‘998-72-3567’且title_id =‘PS2106’ 的记录。 delete titleauthor where au_id=‘998-72-3567’且title_id =‘PS2106’

16. 举例-异常 • 参照实操 现象 分析

17. 查询 • 功能(与“关系运算”的关系) SELECT语句功能强大。虽然表面上看来它只用来完成关系代数运算“选择”，但实际上它也可以完成其他两种关系运算—“投影”和“连接”，SELECT语句还可以完成统计并对数据进行排序。

18. SELECT语句 • 语法格式 SELECT [ALL/DISTINCT <字段名>[,<字段名>]… FROM <表名或视图>[,<表名或视图>]… [WHERE <条件表达式>] [GROUP BY <列名1> [HAVING <条件表达式>]]… [ORDER BY <列名2> [ASC/DESC]] • 说明 • SELECT子句指明要查找的列、列表达式、*等，实现关系运算的投影操作。其中：*代表所有列 。 • FROM子句指明被查找的表或视图（VIEWS） 。 • WHERE子句说明查询的条件或多个表的连接条件。该条件表达式是由列名、函数名，运算符等组成。其中包括关系运算中的比较运算符和逻辑运算符 以下将从简到难介绍select语句的用法。

19. 单表查询 • 功能 从单个表中查询一列或多列数据。 • 查询所有列和所有行(无条件查询) select * from 表/试图名 等价于 select col1,col2,col3,…,coln from 表/试图名 • 举例 • 从titles表中显示所有书籍的所有内容 • 从authors表中显示所有作者的所有内容 select * from titles select * from authors

20. 单表查询 • 查询部分列 有选择的显示部分列信息(同关系运算中的投影) • 方法 select (列表达式1,列表达式1,…,列表达式1n) from 表/试图名 //显示结果中列的次序与选择列中排列一致 • 说明 • 列表达式可以是列名。 • 列表达式可以是常量、变量、函数及列参与的算术表达式。 • 举例 • 从表stores中查询所有商店的商店名称，地址，邮政编码。 Select stor_name , stor_address , zip From stores

21. 举例 • 查询discounts表中的每种折扣的类型和值，并使每种折扣显示时增加1。 declare @v1 varchar(15) set @v1=‘增加后的折扣：’ select ‘折扣类型： ’, discounttype , @v1 , discount+10 from discounts 等同于 select ‘折扣类型： ’, discounttype ‘增加后的折扣：’, discount+10 from discounts

22. 指定列标题 • 指定列标题 当没有特殊声明时，一般查询结果的标题为定义列时的列名。可以为显示结果定义其他标题。 • 方法 在选择列表达式中采用： • 列标题＝列名 • 列名 列标题 • 列名 AS 列标题 • 举例 显示titles表中所有书籍的书编号、书名、价格并将标题改为 书编号、书名、价格。 Select title_id as 书编号 , title as 书名 , price as 价格

23. 删除重复行 • 删除重复行 在查询时可能存在重复行，可以使用distinct选项从结果中删除重复行。 • 方法 在需要取消重复的列前使用distinct选项。 • 举例 从表SALES中，查询所有销售过书籍的商店编号 select distinct stor_id as 商店编号 from sales 从表SALES中，查询所有销售过书籍的商店编号，及戏相应数量 select distinct stor_id as 商店编号 , qty as 数量 from sales

24. 限制查询结果行数 • 限制查询结果行数 缺省时查询的结果包含满足条件的所有行，若只显示部分，可以进行适当限制。 • 方法 使用TOP 行数 选项限制查询结果的行数。 • 举例 从SALES中显示前20条销售记录 select top 20 * from sales

25. 条件查询 • 条件查询 关系运算中有选择操作，在select语句中使用条件查询实现，即实现查询满足一定条件的数据行(记录)。 • 方法 添加where字句，可采用比较、范围限定、集合判断、模式匹配、空值判断、条件连接等操作。(具体参见P143表6-1) • 格式 where 条件 注：where字句位于from字句后。 对于使用不同类型的条件，可以达到不同的查询效果，同时这些条件格式，也适用于check约束。

26. 大小比较 • 大小比较 可对数值型、字符串、日期型等数据类型进行： >、<、>=、<=、=、<>、!>、!<等运算。 • 举例 查询titles表中价格高于10元的图书名、价格、版税 select title,price, royalty from titles where price>10 查询authors表中姓为Smith的作者姓，名、电话号码，地址 select au_lname , au_fname , phone , address 查询sales表中订单号不为P2121的所有销售，包括商店编号、书编号、订单号、数量 select stor_id, title_id , ord_num , qty form sales where ord_num <>’ P2121’

27. 范围限定 • 范围限定 限定某列或列表达式的值在某种范围。(可以用比较运算和逻辑运算替代) 使用的运算符有：between…and…、not between…and…。 • 举例 查询titles表中价格高于10元小于100元的图书名、价格、版税 select title,price, royalty from titles where price between 10 and 100 查询titles表中价格不在10元~100元的图书名、价格、版税 select title,price, royalty from titles where price not between 10 and 100

28. 集合判断 • 集合判断 可以用某列或列表达式的值是否在某集合作为选择条件。 使用的运算符有：in, not in 集合的各元素用‘,’分隔。 • 举例 从表SALES中查找订单号为：‘P2121’，’P3087a’，’ 423LL922’，’ A2976’的销售记录。 select * from sales where ord_id in (‘P2121’，’P3087a’，’ 423LL922’，’ A2976’) 从表SALES中查找所有订单号不为：‘P2121’，’P3087a’，’ 423LL922’，’ A2976’的销售记录。 select * from sales where ord_id not in (‘P2121’，’P3087a’，’ 423LL922’，’ A2976’)

29. 字符串匹配 • 字符串匹配 当需要进行相似或不相似的比较时，可以采用字符串匹配运算。 匹配运算包括：like 、 not like。 比如：姓什么，地址中包含什么等 • 匹配符 单独的使用like 、 not like时，相当于=、<>，只有加上通配符，才能完成匹配运算。常用的通配符有： • ‘%’ 代表任意长度的字符串,如‘杨%’代表所有姓杨的人名称。 • ‘_’ 表示任意单个字符,如‘杨_明’代表所名称为‘杨某明’的人名称；

30. 通配符 • ‘[]’ 表示指定范围内或指定字符串中的任意单个字符。如[0-9][0-9]表示包含两个数字的任意字符串；如0757- [0-9][0-9] [0-9][0-9] [0-9][0-9] [0-9][0-9]表示佛山地区电话号码。 • ‘^’ 与‘[]’相反，表示不在指定范围内或指定字符串中的任意单个字符。

31. 字符串匹配-举例 • 举例 从表employee从查询姓名第一各字母为‘D’的所有职员记录，包括所有列。 select * from employee where fname like ‘D%’ 从student表中查询所有名为‘高声’的所有学生记录，包括所有列。 select * from student where name like ‘%高声’ 查询titles表中图书编号以B或P字母开头的图书编号、书名 查询authors表中作者编号中间项是‘56’的作者编号，姓名

32. 空值判断 • 空值判断 当要判断某列是否为NULL时，不能直接写成：列名=NULL，必须使用is null或is not null • 举例 查询titles中没有定价的图书编号、书名和价格 select title_id, title, price from titles where price is null 查询discount表中最低数量已确定的折扣类型、商店编号、最高数量和最低数量 select discounttype , stor_id , lowqty , highqty from discount where lowqty is not null

33. 多重条件查询 • 多重条件查询 当where字句存在多个条件时，可以使用and(与)、or(或)逻辑运算来连接这些条件。 使用‘()’号来改变优先级 • 举例

34. 结果排序 • 结果排序 若要将结果进行排序，可以使用order by字句。可选择升序、降序两种类型；可以是对单个列的排序，也可是多列的组合排序，还可是列表达式的排序等。 • 举例 select * from titles where price>10 order by price desc select * from titles order by type, price desc

35. 问题的提出 • 统计某类书籍的种类数 • 统计某类书籍的最高、最低价格 • 统计某种书籍销售的总数量 • 分别统计某类书籍的年销售量

36. 函数分类 • 聚集函数 • 其他函数 • 字符串函数 如：upper(char_expr) 转换为大写 • 数学函数 如：round(numeric_expr,int_expr) 四舍五入 • 日期函数 如：datepart(datepart,date_expr) 取日期一部份 • 系统函数 如：suser_name()  用户登录名

37. 聚集函数使用 • 聚集函数 SQL提供了许多库函数(聚集函数)，它使检索功能更强大。它们是按属性列（或列的表达式）进行计算的。 如在PUBS数据库中，我们经常要进行以下统计： 1.统计书类的数目 2.求所有书年销售量的总和（所有书年销售金额的总和） 3.求所有书的平均价格 4.求所有书中价格最贵的书籍 5.求所有书中价格最便宜的书籍

38. 常用函数 • 常用函数 • COUNT([DISTINCT]|[ALL]*) 说明： 统计表中元组个数一般形式：count(*) 举例： • 统计商店的个数 select count(*) from stores • 统计书籍的种类 select count(*) from titles • COUNT([DISTINCT]|[ALL]<列名>) 说明： 统计表中某列值的个数(除null外，与count(*)类似) ， 一般形式：count(列名) ；当要取消重复的值时，可采用DISTINCT。

39. 常用函数 举例： • 求图书种类 • 求订单号为‘P3087a’所销售的书籍种类数 • 求订单号为‘P3087a’所销售的书籍种类数及书籍名称 • SUM([DISTINCT]|[ALL] <列名>) 说明： 求 某列或多列的表达式之和。 一般形式： sum(<列名或列表达式>) select count(distinct type) from titles • select count(distinct title) from titles • where ord_num= ‘P3087a’

40. 常用函数 举例： • 求所有书籍年销售量的总和 • 求编号为‘PS2091’的书籍销售的数量 • 求所有书籍年销金额的总和 Select sum(ytd_sales ) from titles Select sum(qty ) from sales Where title=‘PS2091’ Select sum(ytd_sales *price ) from titles

41. 常用函数 • AVG([DISTINCT]|[ALL]<列名>) 说明： 求某列或多列的表达式平均值。 一般形式： AVG(<列名或列表达式>) 举例： • 求所有书籍的平均价格及年销售数量 • 求编号为‘7066’商店的平均销售量 Select avg(price) as ‘平均价格’，avg(ytd_sales ) as ‘年销售数量’ From titles Select avg(qty ) from sales Where stor_id= ‘7066’

42. 常用函数 • MAX([DISTINCT]|[ALL]<列名>) 说明： 求某列或多列的表达式的最大值。 一般形式 ： MAX (<列名或列表达式>) 举例： • 求书籍中价格最高的书籍价格 • 求求书籍中价格最高的书籍价格及书籍名 Select max(price) from titles

43. 常用函数 • MIN([DISTINCT]|[ALL]<列名>) 说明： 求某列或多列的表达式的最小值。 一般形式 ： MIN(<列名或列表达式>) 举例： 省略。

44. 结果分组 • 结果分组 前面介绍的聚集函数都是对表中所有行进行计算，当要对不同的类型进行分别计算时，可以采用分组的方法。 使用GROUP BY 字句对查询结果按给定的一个或属性列的不同值进行分组。分组后库函数作用于每一组，使每一组都得到一个相应的统计值。 可以显示分组的列值，其他则不允许。 举例： 求出各个类型书的最高价格、最低价格和册数。 Select type,MAX(price),MIN(price),COUNT(*) AS 书籍数 from titles GROUP BY type

45. Having字句 • Having字句 当需要使用统计结果作为条件时，采用having字句。 • 注意 • HAVING 通常与 GROUP BY 子句一起使用。 • 如果不使用 GROUP BY 子句，HAVING 的行为与 WHERE 子句一样。 • 举例 求出最高价格高于20的所有类型书的最高价格、最低价格和册数。 Select type,MAX(price),MIN(price),COUNT(*) AS书籍数 from titles GROUP BY type having MAX(price)>20

46. 结果统计 • 结果统计 当不仅要显示统计结果，而且要显示各项数据明细时，可以采用compute字句。 • 格式 在select句后 加上compute字句，并将统计函数加入该句中。 • 显示结果 分两部分：查询明细、统计结果。 • 举例 略

47. 将查询结果添加到指定表中 • 将查询结果添加到指定表中 可以将所查询的结果写入到一表中，该表会由系统根据查询的列自动创建(因此该表应在此数据库中不存在) • 举例 省略

48. 联合查询 • 联合查询 将多个查询结果集合并为一个结果集，采用union子句 • 注意事项 • 联合查询的结果集的列标题以第一个查询的列标题为准。 • 每个select子句的列表达式必须结构相同、列数相同、数据兼容。 • 当要使用order by或compute字句时，必须加到语句最后。 • 该字句会消除重复值 • 举例 select au_id ,title_id from titleauthor union select au_id,au_lname from kk

49. 连接的形式1 • 自由连接 学生表 成绩表 自由连接的结果

50. 连接的形式2 • 理想的连接 学生表 成绩表 自由连接的结果