850 likes | 1.14k Views
T-SQL 语言. Transact-SQL. SQL 语言的类型. SQL Structured Query Language Transact-SQL PL/SQL. SQL 语言分类. DDL DML DCL. 查询分析器. 用途 交互设计和测试 Transact-SQL 语句、批处理和脚本。 功能 创建查询和其它 SQL 脚本,并针对 SQL Server 数据库执行它们。("查询"窗口) 由预定义脚本快速创建常用数据库对象。(模板) 快速复制现有数据库对象。(对象浏览器脚本功能)
E N D
T-SQL语言 Transact-SQL
SQL语言的类型 • SQL Structured Query Language • Transact-SQL • PL/SQL
SQL语言分类 • DDL • DML • DCL
查询分析器 • 用途 交互设计和测试Transact-SQL语句、批处理和脚本。 • 功能 • 创建查询和其它 SQL 脚本,并针对 SQL Server 数据库执行它们。("查询"窗口) • 由预定义脚本快速创建常用数据库对象。(模板) • 快速复制现有数据库对象。(对象浏览器脚本功能) • 在参数未知的情况下执行存储过程。(对象浏览器过程执行功能) • 调试存储过程。(T-SQL 调试程序) • 调试查询性能问题。(显示执行计划、显示服务器跟踪、显示客户统计、索引优化向导) • 在数据库内定位对象(对象搜索功能),或查看和使用对象。(对象浏览器) • 快速插入、更新或删除表中的行。("打开表"窗口) • 为常用查询创建键盘快捷方式。(自定义查询快捷方式功能) • 向"工具"菜单添加常用命令。(自定义"工具"菜单功能)
查询分析器 • 启动 两种方式: • 【开始】->【程序】->【查询分析器】 • 在企业管理器的工具菜单运行 • 结构 分为两个窗口,左侧为“对象浏览器”窗口(包括:对象和模板选项卡),右侧为SQL语句输入窗口。
数据操作 对数据库数据操作,一般是指对表中的数据进行操作。 • 操作类型 添加(insert)、删除(delete)、修改(update)和查询。 • 操作收到的限制或约束 • 用户权限的限制 只有系统管理员,数据库和表对象所有者及被授权的用户才可操作。 • 主外键约束的限制 • Check约束的限制 • 非空(not null)约束的限制 • 各列数据类型的限制 以下将讲解如何使用SQL语句操纵数据,以及要注意的事项
添加数据 • 功能 将新一行数据添加到表或视图。 • 语句格式 insert [into] 表名|视图名 [(列列表)] values( default | null | 表达式) 说明: • 若指定列列表;则VALUES中的数值必须满足值的循序与属性列表中的排列一致。 • 若列列表名省略; values中值的循序与实际表定义中的排列一致。
举例 • 向表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)
注意事项 • 注意 • 数值型数据直接书写 • 字符、字符串、日期型数据用单引号括起 • 当列值未知时,用null表示。 • 当表中某属性定义了NOT NULL约束,则该列不能取空值 。 • 必须满足实体完整性约束,也就是PK列要求当前的值是唯一的,即在表中的现存数据中改列的值不能已存在 。 • 必须满足实体参照完整性约束,也就是FK列的值在参照表中必须存在。 • 插入位置:插入的元组(一行数据),由DBMS根据排序决定其插入的位置。
举例 • 简单查询 select * from 表名 将表中的所有数据行列出 • 异常举例 参照实操。 现象 分析
修改数据 • 功能 修改表或视图中的一行或多行满足一定条件的某列或多列数据。 • 语句格式 UPDATE <表名> SET Column_name1=表达式1,Column_name2=表达式2,…,Column_namen=表达式n WHERE 条件 说明: • “Column_name1=表达式”指定需修改的列及修改的数值 • “WHERE 条件“指明需要修改的哪些记录(或元组),当不指明WHERE时,则修改表中的所有记录 。 • 条件表达式一般是由字段组成的逻辑运算,比较运算符包括:‘>’、‘<’、‘=’、‘<>’、‘>=’、‘<=’;逻辑运算符包括:and、or 、not()。
举例 • 将雇员表(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
举例-异常 • 参照实操 现象 分析
删除数据 • 功能 删除表或视图中的一行或多行符合一定条件的数据。 • 语句格式 DELETE FROM <表名> WHERE 条件 • 说明 • DELETE字句是删除表中的行(元组),但并不是删除整个表。 • “WHERE 条件“指明需要删除的哪些记录(或元组),当不指明WHERE时,则删除表中的所有记录 。 • 条件表达式同“数据修改”一节中的条件表达式。
举例 • 删除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’
举例-异常 • 参照实操 现象 分析
查询 • 功能(与“关系运算”的关系) SELECT语句功能强大。虽然表面上看来它只用来完成关系代数运算“选择”,但实际上它也可以完成其他两种关系运算—“投影”和“连接”,SELECT语句还可以完成统计并对数据进行排序。
SELECT语句 • 语法格式 SELECT [ALL/DISTINCT <字段名>[,<字段名>]… FROM <表名或视图>[,<表名或视图>]… [WHERE <条件表达式>] [GROUP BY <列名1> [HAVING <条件表达式>]]… [ORDER BY <列名2> [ASC/DESC]] • 说明 • SELECT子句指明要查找的列、列表达式、*等,实现关系运算的投影操作。其中:*代表所有列 。 • FROM子句指明被查找的表或视图(VIEWS) 。 • WHERE子句说明查询的条件或多个表的连接条件。该条件表达式是由列名、函数名,运算符等组成。其中包括关系运算中的比较运算符和逻辑运算符 以下将从简到难介绍select语句的用法。
单表查询 • 功能 从单个表中查询一列或多列数据。 • 查询所有列和所有行(无条件查询) select * from 表/试图名 等价于 select col1,col2,col3,…,coln from 表/试图名 • 举例 • 从titles表中显示所有书籍的所有内容 • 从authors表中显示所有作者的所有内容 select * from titles select * from authors
单表查询 • 查询部分列 有选择的显示部分列信息(同关系运算中的投影) • 方法 select (列表达式1,列表达式1,…,列表达式1n) from 表/试图名 //显示结果中列的次序与选择列中排列一致 • 说明 • 列表达式可以是列名。 • 列表达式可以是常量、变量、函数及列参与的算术表达式。 • 举例 • 从表stores中查询所有商店的商店名称,地址,邮政编码。 Select stor_name , stor_address , zip From stores
举例 • 查询discounts表中的每种折扣的类型和值,并使每种折扣显示时增加1。 declare @v1 varchar(15) set @v1=‘增加后的折扣:’ select ‘折扣类型: ’, discounttype , @v1 , discount+10 from discounts 等同于 select ‘折扣类型: ’, discounttype ‘增加后的折扣:’, discount+10 from discounts
指定列标题 • 指定列标题 当没有特殊声明时,一般查询结果的标题为定义列时的列名。可以为显示结果定义其他标题。 • 方法 在选择列表达式中采用: • 列标题=列名 • 列名 列标题 • 列名 AS 列标题 • 举例 显示titles表中所有书籍的书编号、书名、价格并将标题改为 书编号、书名、价格。 Select title_id as 书编号 , title as 书名 , price as 价格
删除重复行 • 删除重复行 在查询时可能存在重复行,可以使用distinct选项从结果中删除重复行。 • 方法 在需要取消重复的列前使用distinct选项。 • 举例 从表SALES中,查询所有销售过书籍的商店编号 select distinct stor_id as 商店编号 from sales 从表SALES中,查询所有销售过书籍的商店编号,及戏相应数量 select distinct stor_id as 商店编号 , qty as 数量 from sales
限制查询结果行数 • 限制查询结果行数 缺省时查询的结果包含满足条件的所有行,若只显示部分,可以进行适当限制。 • 方法 使用TOP 行数 选项限制查询结果的行数。 • 举例 从SALES中显示前20条销售记录 select top 20 * from sales
条件查询 • 条件查询 关系运算中有选择操作,在select语句中使用条件查询实现,即实现查询满足一定条件的数据行(记录)。 • 方法 添加where字句,可采用比较、范围限定、集合判断、模式匹配、空值判断、条件连接等操作。(具体参见P143表6-1) • 格式 where 条件 注:where字句位于from字句后。 对于使用不同类型的条件,可以达到不同的查询效果,同时这些条件格式,也适用于check约束。
大小比较 • 大小比较 可对数值型、字符串、日期型等数据类型进行: >、<、>=、<=、=、<>、!>、!<等运算。 • 举例 查询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’
范围限定 • 范围限定 限定某列或列表达式的值在某种范围。(可以用比较运算和逻辑运算替代) 使用的运算符有: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
集合判断 • 集合判断 可以用某列或列表达式的值是否在某集合作为选择条件。 使用的运算符有: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’)
字符串匹配 • 字符串匹配 当需要进行相似或不相似的比较时,可以采用字符串匹配运算。 匹配运算包括:like 、 not like。 比如:姓什么,地址中包含什么等 • 匹配符 单独的使用like 、 not like时,相当于=、<>,只有加上通配符,才能完成匹配运算。常用的通配符有: • ‘%’ 代表任意长度的字符串,如‘杨%’代表所有姓杨的人名称。 • ‘_’ 表示任意单个字符,如‘杨_明’代表所名称为‘杨某明’的人名称;
通配符 • ‘[]’ 表示指定范围内或指定字符串中的任意单个字符。如[0-9][0-9]表示包含两个数字的任意字符串;如0757- [0-9][0-9] [0-9][0-9] [0-9][0-9] [0-9][0-9]表示佛山地区电话号码。 • ‘^’ 与‘[]’相反,表示不在指定范围内或指定字符串中的任意单个字符。
字符串匹配-举例 • 举例 从表employee从查询姓名第一各字母为‘D’的所有职员记录,包括所有列。 select * from employee where fname like ‘D%’ 从student表中查询所有名为‘高声’的所有学生记录,包括所有列。 select * from student where name like ‘%高声’ 查询titles表中图书编号以B或P字母开头的图书编号、书名 查询authors表中作者编号中间项是‘56’的作者编号,姓名
空值判断 • 空值判断 当要判断某列是否为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
多重条件查询 • 多重条件查询 当where字句存在多个条件时,可以使用and(与)、or(或)逻辑运算来连接这些条件。 使用‘()’号来改变优先级 • 举例
结果排序 • 结果排序 若要将结果进行排序,可以使用order by字句。可选择升序、降序两种类型;可以是对单个列的排序,也可是多列的组合排序,还可是列表达式的排序等。 • 举例 select * from titles where price>10 order by price desc select * from titles order by type, price desc
问题的提出 • 统计某类书籍的种类数 • 统计某类书籍的最高、最低价格 • 统计某种书籍销售的总数量 • 分别统计某类书籍的年销售量
函数分类 • 聚集函数 • 其他函数 • 字符串函数 如:upper(char_expr) 转换为大写 • 数学函数 如:round(numeric_expr,int_expr) 四舍五入 • 日期函数 如:datepart(datepart,date_expr) 取日期一部份 • 系统函数 如:suser_name() 用户登录名
聚集函数使用 • 聚集函数 SQL提供了许多库函数(聚集函数),它使检索功能更强大。它们是按属性列(或列的表达式)进行计算的。 如在PUBS数据库中,我们经常要进行以下统计: 1.统计书类的数目 2.求所有书年销售量的总和(所有书年销售金额的总和) 3.求所有书的平均价格 4.求所有书中价格最贵的书籍 5.求所有书中价格最便宜的书籍
常用函数 • 常用函数 • COUNT([DISTINCT]|[ALL]*) 说明: 统计表中元组个数一般形式:count(*) 举例: • 统计商店的个数 select count(*) from stores • 统计书籍的种类 select count(*) from titles • COUNT([DISTINCT]|[ALL]<列名>) 说明: 统计表中某列值的个数(除null外,与count(*)类似) , 一般形式:count(列名) ;当要取消重复的值时,可采用DISTINCT。
常用函数 举例: • 求图书种类 • 求订单号为‘P3087a’所销售的书籍种类数 • 求订单号为‘P3087a’所销售的书籍种类数及书籍名称 • SUM([DISTINCT]|[ALL] <列名>) 说明: 求 某列或多列的表达式之和。 一般形式: sum(<列名或列表达式>) select count(distinct type) from titles • select count(distinct title) from titles • where ord_num= ‘P3087a’
常用函数 举例: • 求所有书籍年销售量的总和 • 求编号为‘PS2091’的书籍销售的数量 • 求所有书籍年销金额的总和 Select sum(ytd_sales ) from titles Select sum(qty ) from sales Where title=‘PS2091’ Select sum(ytd_sales *price ) from titles
常用函数 • AVG([DISTINCT]|[ALL]<列名>) 说明: 求某列或多列的表达式平均值。 一般形式: AVG(<列名或列表达式>) 举例: • 求所有书籍的平均价格及年销售数量 • 求编号为‘7066’商店的平均销售量 Select avg(price) as ‘平均价格’,avg(ytd_sales ) as ‘年销售数量’ From titles Select avg(qty ) from sales Where stor_id= ‘7066’
常用函数 • MAX([DISTINCT]|[ALL]<列名>) 说明: 求某列或多列的表达式的最大值。 一般形式 : MAX (<列名或列表达式>) 举例: • 求书籍中价格最高的书籍价格 • 求求书籍中价格最高的书籍价格及书籍名 Select max(price) from titles
常用函数 • MIN([DISTINCT]|[ALL]<列名>) 说明: 求某列或多列的表达式的最小值。 一般形式 : MIN(<列名或列表达式>) 举例: 省略。
结果分组 • 结果分组 前面介绍的聚集函数都是对表中所有行进行计算,当要对不同的类型进行分别计算时,可以采用分组的方法。 使用GROUP BY 字句对查询结果按给定的一个或属性列的不同值进行分组。分组后库函数作用于每一组,使每一组都得到一个相应的统计值。 可以显示分组的列值,其他则不允许。 举例: 求出各个类型书的最高价格、最低价格和册数。 Select type,MAX(price),MIN(price),COUNT(*) AS 书籍数 from titles GROUP BY type
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
结果统计 • 结果统计 当不仅要显示统计结果,而且要显示各项数据明细时,可以采用compute字句。 • 格式 在select句后 加上compute字句,并将统计函数加入该句中。 • 显示结果 分两部分:查询明细、统计结果。 • 举例 略
将查询结果添加到指定表中 • 将查询结果添加到指定表中 可以将所查询的结果写入到一表中,该表会由系统根据查询的列自动创建(因此该表应在此数据库中不存在) • 举例 省略
联合查询 • 联合查询 将多个查询结果集合并为一个结果集,采用union子句 • 注意事项 • 联合查询的结果集的列标题以第一个查询的列标题为准。 • 每个select子句的列表达式必须结构相同、列数相同、数据兼容。 • 当要使用order by或compute字句时,必须加到语句最后。 • 该字句会消除重复值 • 举例 select au_id ,title_id from titleauthor union select au_id,au_lname from kk
连接的形式1 • 自由连接 学生表 成绩表 自由连接的结果
连接的形式2 • 理想的连接 学生表 成绩表 自由连接的结果