|
第三章 T-SQL查询语句 1. tansact-SQL编程语言美国国家标准协会（ANSI）和国际标准组织（ISO）为 SQL定义了标准，微软通过用Transact-SQL和ANSI—SQL兼容，Transact-SQL还包含了几种能够增强性能的扩展。
T-SQL的组成：
Ø 数据定义语言（Date Definition Language）语句简称DDL语句 DDL语句用来建立数据库，数据库对象（create,alter,drop） createobject_name alterobject_name dropobject_name 举例说明DDL语句的使用: useschoolDB create table teacher (cust_idint ,company varchar(40),contact varchar(30),phone char(12)) go alter table teacher add ageint default 30 Ø 数据控制语言（Date Control Language）语句 用于改变某个用户或角色相关的权限 grant deny revoke 举例说明： useschoolDB grant select on products to public go Ø 数据操作语言（Date Manipulation Language）语句
操作数据库中的数据。可以更改数据库中的数据或查询数据库中的信息 举例说明： select*from dbo.TStudent //查询TStudent表中的数据
insert dbo.TStudent (StudentID,Sname,sex)values('0000001901','陈英宏','男')//为TStudent表中的studentID,Sname,sex列插入相应的记录
update dbo.TStudent set Sname='张春海',sex='男'where studentid='0000000569'//更新表中studentID为0000000569的姓名修改为'张春海' delete dbo.TStudent where StudentID='0000000020'//删除studentID为0000000020的记录 2. Ttansact-SQL语法要素1. 批处理 go 一个批处理命令通知SQLServer分析并运行一个批处理的所有指令 实际上不是 Transact-SQL语句，只是描述一个批处理。局部变量作用范围局限在一个批内，必须独占一行。 USE schoolDB go select * from dbo.TScore--从那张表查找 where mark<60 go 2. 表达式 1. 算数运算符 + - * / % 比较运算符 = <>>= != :不等于，等同于<> BETWEEN :指定值得包含范围（包含边界）。使用AND分隔开始值和结束值 IS[NOT]NULL :根据使用的关键字，指定是否搜索空值或非空值。如果有任何一个操作数为NULL，则包含运算符或算数运算符的表达式的计算结果为NULL LIKE:模糊查询，与指定字符串进行模糊匹配 IN:是否在数据范围里面 字符串联运算符 + 空字符不等于空值 表达式是各种符号和对单个数据进行操作 select mark+5 from dbo.TScore where mark<60 2. 逻辑运算符 and or not NOT:和其他操作符一起使用，取反的操作 AND:组合两个条件，并在两个条件都为TRUE时取值为TRUE OR:组合两个条件，并在两个条件之一为TRUE时取值为TRUE 3. 通配符 '_' ：表示任何单个字符 sname LIKE '_cc' 查找以cc结尾的所有三个字母名字 % ：任意长度的字符串 sname LIKE '%CC%'查找所有包含cc的名字 []：括号中所指定范围内的一个字符例如sname LIKE '[c-p]ion'将查找Ion结尾且以介于 c 与p之间的任何单个字符开始的名字 通配符经常与LIKE关键字一起配合使用完成模糊查询，可以使用LIKE和通配符来完成对表的一些特殊约束。 3. 数据查询3.1. 使用select语句查询数据3.1.1. 指定列查询查询所有行 select*from dbo.TStudent 使用where子句指定行 select Sname,sex,Email from dbo.TStudent where Sname='田育朋'
3.1.2. 过滤数据使用比较操作符 = ><>= <= <> select*from dbo.TScore where mark<=60 使用字符比较符 like % 0个或多个字符串 _ 任何单个的字符 []在指定区域或集合内的任何单个字符 [^]不在指定区域或集合内的任何单个字符 select*from dbo.TStudent where sname like'高%' select*from dbo.TStudent where sname like'_[明,育]_' select*from dbo.TStudent where sname like'_[^明,育]_' 3.1.3. 使用逻辑操作符OR AND NOT使用方法 select*from dbo.TStudent where Sname like'高%'and sex='男'or StudentID='0000000112' 查找不姓高的学生 select*from dbo.TStudent where Sname notlike'高%' 3.1.4. 查找在一定范围的值select*from dbo.TScore where mark between 70 and 80 等价于 select*from dbo.TScore where mark>=70 and mark<=80 不包括70 和80 尽量使用between而不使用and和比较操作符表示的表达式 如果想返回不在指定区域的行时，使用not between 。这样会降低数据查询的速度。 select*from dbo.TScore where mark notbetween 70 and 80 指定时间范围 select*from dbo.TStudent where Birthday between'1983-01-01'and'1984-01-01' 3.1.5. 查询空值insert dbo.TStudent (StudentID,Sname,sex)values('0000001901','陈英宏','男') 查找班级不为空的学生 select*from dbo.TStudent where Class isnotnull 查找班级为空的学生 select*from dbo.TStudent where Classis null 使用is not null来查询指定列中非空的行 3.2. 格式化结果集3.2.1. 给数据排序select StudentID,subJectID,mark from dbo.TScore orderby 2,3 desc select StudentID,subJectID,mark from dbo.TScore orderby subJectID,mark desc asc升序 desc降序 3.2.2. 消除重复的行distinct selectdistinct Class from dbo.TStudent 3.2.3. 改变字段的名字select StudentID as'学号',Sname as'姓名',sex as'性别',cardID as'身份证号',Birthday as'生日',Email as'邮件', Class as'专业',enterTime as'录入时间'from dbo.Tstudent 等价于 select StudentID '学号',Sname '姓名',sex '性别',cardID '身份证号', Birthday '生日',Email '邮件', Class '专业',enterTime '录入时间'from dbo.TStudent 3.2.4. 使用符号符号可能是字母，数字或标识，在结果集中，他们被用作特定的值，以增加结果集的可读性。 selectStudentID'学号',Sname'姓名',sex'性别','性别'fromdbo.Tstudent 3.2.5. 计算列年龄是计算列 select StudentID as'学号',Sname as'姓名',sex as'性别',cardID as'身份证号',Birthday as'生日',Email as'邮件', Class as'专业',enterTime as'录入时间',Datediff(yy,Birthday,getdate())as'年龄'from dbo.TStudent 3.3. 使用T-SQL语句实现多表查询前面查询都是基于单个数据库表的查询，如果一个查询需要对多个表进行操作，就成为连接查询，连接查询的结果集或结果称为表之间的连接。 表连接的类型： 内连接、外连接、交叉连接 创建多表查询的练习环境 --创建学生表 student Createtable student ( studentid int, sname nvarchar(10), sex nchar(1) ) --插入学生 insert student values(1,'华荣','女') insert student values(2,'王景正','男') insert student values(3,'郭淑丽','女') insert student values(4,'韩旭','女') insert student values(5,'孟小飞','男') --创建成绩表 createtable score ( studentid int, subjectname nvarchar(20), score decimal ) --插入成绩 insert score values(1,'英语',89) insert score values(1,'数学',59) insert score values(2,'英语',79) insert score values(2,'数学',86) insert score values(3,'英语',57) insert score values(3,'数学',67) insert score values(6,'英语',88) insert score values(6,'数学',83) 1． 查询所有学生的成绩 select a.*,b.*from student a join score b on a.studentid=b.studentid
select sname,subjectname,score from student a join score b on a.studentid=b.studentid
select a.studentid,sname,subjectname,score from student a join score b on a.studentid=b.studentid 2． 从多个表中合并数据 使用内连接 select a.*,b.*from student a join score b on a.studentid=b.studentid 等价于 select a.*,b.*from student a innerjoin score b on a.studentid=b.studentid
使用外连接 左外连接
select a.studentid,sname,subjectname,score from dbo.student a leftjoin dbo.score b on a.studentid=b.studentid
右外连接 select a.*,b.*from dbo.student a rightjoin dbo.score b on a.studentid=b.studentid 练习: 1．查找不及格同学姓名和学科分数 select sname,subjectname,score from dbo.student a join dbo.score b on a.studentid=b.studentid where score<60 自连接 查找到重名的学生 insert student values(6,'韩旭','男') select a.*,b.*from dbo.student a join dbo.student b on a.sname=b.sname where a.studentid<>b.studentid 3.4. 数据分组和汇总3.4.1. 用TOP n列出前n个记录结合Order by找出满足条件的前几条记录 1． 年龄最大的前5名学生 selecttop 5 *fromdbo.TStudentorderbyBirthday 2． 年龄最小的前5名学生 selecttop 5 *fromdbo.TStudentorderbyBirthdaydesc 查找网络班年龄最小的前5名学生 selecttop 5 *fromdbo.TstudentwhereClass='网络班'orderbyBirthdaydesc
3.4.2. 使用聚集函数可以在Select 语句中单独使用聚集函数，也可以与语句group by联合使用 除了count（*）函数，如果没有满足where子句的记录，则所有的聚集函数都将返回空值，Count（*）返回0 Count(*)Count（列）Sum Min max Avg 统计表中有多少行 selectcount(*)from dbo.TStudent 插入一条没有班级的记录 insert dbo.TStudent (studentID,Sname,sex)values('0000001901','王敬正','男') select count(Class) from dbo.TStudent 求平均值 selectavg(mark)from dbo.TScore 求最大值和最小值 selectmax(mark)from dbo.TScore selectmin(mark)from dbo.TScore 求总和 selectsum(mark)from dbo.TScore 3.4.3. 使用GROUP BY基础如果使用聚集函数，则将对表中的所有记录的某个字段进行汇总，然后生成单个的值。如果想生成多个汇总值，同时使用聚集函数和group by 语句，联合使用having和group by子句能够使结果集只包含满足条件的记录。 联合使用group by子句与having子句 分组汇总 1. 统计每个班有多少个学生 select Class,count(*)from dbo.TStudent groupby Class 2. 统计男生女生数量 select sex,count(*) from dbo.TStudent group by sex 3. 统计每科平均分 selectsubJectName,avg(mark)fromdbo.TSubjectajoindbo.TScorebona.subJectID=b.subJectID groupbysubJectName 4. 统计每个学生的平均分 selecta.StudentID,avg(mark)fromdbo.TStudentajoindbo.TScorebona.StudentID=b.StudentID groupbya.StudentIDorderbyavg(mark) 5. 查找平均分大于95的学生 selecta.StudentID,avg(mark)fromdbo.TStudentajoindbo.TScorebona.StudentID=b.StudentID groupbya.StudentIDhavingavg(mark)>95 Having相当于条件
4. 数据修改4.1. 插入数据 insert dbo.TStudent values('0000001902','李维伟','男','132302197506055634','1984-3-4','liweiwei@bdqn.com','网络班',getdate()) 4.2. 插入部分数据 插入学生学号 ;姓名性别其他列为空 insert dbo.TStudent (StudentID,sex,Sname)values('0000001903','男','张国强') 4.3. 将查询记录插创建的新表 使用SELECT INTO创建表。 将学生表中开发班的从学生查询到一个新表 select StudentID,Sname,sex,Email into TDe from dbo.TStudent where Class='开发班' 4.4. 删除数据 删除学号是0000000020的学生 delete dbo.TStudent where StudentID='0000000020' 删除1982年以前出生的学生 deletedbo.TStudentwhereBirthday<'1982-1-1'andclass='网络班' 更新数据 更改学生学号是0000000569的学生姓名为张海明性别改成男 updatedbo.TStudentsetSname='张海明',sex='男'wherestudentid='0000000569' 5. 事物的概念执行事物的语法： 开始事物： BEGIN TRANSACTION 提交事物： COMMIT TRANSACTION 回滚撤销事物：ROLLBACK TRANSACTION 事物应用案例 创建表 CREATETABLEdbo.bank ( customerNamechar(10)NOTNULL, currentMoneymoneyNOTNULL ) 为bank表的currentMoneymoney创建check约束，currentMoney的账户余额不能少于1元
插入记录 insertintobank(customerName,currentMoney)values ('张三',1000)
insertintobank(customerName,currentMoney)values ('李四',1)
select*frombank
updatebanksetcurrentMoney=currentMoney-1000 wherecustomerName='张三'
updatebanksetcurrentMoney=currentMoney+1000 wherecustomerName='李四'
select*frombank 执行以上命令后李四账户增加了1000，但是张三账户余额仍然是1000，这样的转账结果肯定是不可取，我们可以通过以下方法来解决。 首先转账过程就是一个事物，它需要两条UPDATE语句来完成，这两条语句是一个整体。如果其中任何一条出现错误，则整个转账业务也应该取消，两个账户的余额应该恢复到原来的状态。 通过以下语句来解决上述转账的问题
print'查看转帐事务前的余额'select*frombank go begintransaction
declare @errorSumint set@errorSum=0 updatebanksetcurrentMoney=currentMoney-1000 wherecustomerName='张三' set @errorSum=@errorSum+@@ERROR updatebanksetcurrentMoney=currentMoney+1000 wherecustomerName='李四' set@errorSum=@errorSum+@@ERROR print'查看转帐事务前的余额'select*frombank
if@errorSum<>0 begin print'交易失败，回滚事务' rollbacktransaction end else begin print'交易成功，提交事务，写入硬盘，永久的保存' committransaction end go print'查看转帐事务前的余额'select*frombank
在事物处理过程中使用@@ERROR全局变量来，检查判断当前T-SQL语句执行是否有错误。如果有错误则返回非零值，而@errorSum变量用来累计两个UPDATE命令执行之后@@ERROR的值，只要其中一条UPDATE语句有错误@errorSum的值将不再为零。
如果将转账余额修改成500
6. SQL-server锁的概念共享锁：用于读取资源所加的锁。拥有共享锁的资源不能被修改。共享锁在默认情况下是读取了资源马上被释放。 排他锁：和其他锁都不兼容，包括其他排他锁，排它锁用于修改数据，当资源上加了排他锁时，其他请求读取或修改这个资源的事物都会被阻塞，直到排他锁被释放为止。 更新锁：是共享锁和排它锁的结合，用于更新数据，更新数据时首先需要找到被更新的数据，此时可以理解为被查找的数据上了共享锁。当找到需要修改的数据时，需要对被修改的资源上排他锁。 sql server 通过更新锁来避免死锁的问题，因为共享锁和共享锁是兼容的，通过更新锁和共享锁兼容，使得更新查找时并不影响数据查找，而更新锁和更新锁之间是不兼容的，从而减少了死锁的可能性。 举例说明以上描述的锁： 6.1. 创建练习环境---创建表 Createtablestudent ( studentidint, snamenvarchar(10), sexnchar(1) ）
--插入学生 insertstudentvalues (1,'华荣','女') insertstudentvalues (2,'王景正','男') insertstudentvalues (3,'郭淑丽','女') insertstudentvalues (4,'韩旭','女') insertstudentvalues (5,'孟小飞','男') 6.2. 排他锁1.新建两个连接 在第一个连接中执行以下语句 begintran updatedbo.studentsetsname='王景正'wherestudentid=1 waitfordelay'00:00:30' committran 在第二个连接中执行以下语句 begintran select*fromdbo.studentwherestudentid=1 committran 若同时执行上述两个语句,则select查询必须等待update执行完毕才能执行既要等待30秒
6.3. 共享锁在第一个连接中执行以下语句 begintran select*fromdbo.studentwith (holdlock) wheresex='女' waitfordelay'00:00:30' committran 在第二个连接中执行以下语句 begintran selectstudentid,snamefromdbo.studentwheresex='女' updatedbo.studentsetsname='韩旭'wheresex='女' committran 若同时执行上述两个语句,则第二个连接中的select查询可以执行,而UPDATE必须等待地一个事物释放 共享锁转为排它锁后才能执行,既要等待30秒.
6.4. 死锁创建score表 createtablescore ( studentidint, subjectnamenvarchar(20), scoredecimal ) --插入成绩 insertscorevalues (1,'英语',89) insertscorevalues (1,'数学',59) insertscorevalues (2,'英语',79) insertscorevalues (2,'数学',86) insertscorevalues (3,'英语',57) insertscorevalues (3,'数学',67) insertscorevalues (6,'英语',88) insertscorevalues (6,'数学',83) 在第一个连接中执行以下语句 begintran updatedbo.studentsetsname='华荣'wheresex='女' waitfordelay'00:00:30' updatedbo.scoresetscore=60 wherestudentid=2 committran 在第二个连接中执行以下语句 begintran updatedbo.scoresetscore=60 wherestudentid=2 waitfordelay'00:00:30' updatedbo.studentsetsname='华荣'wheresex='女' committran 同时执行，系统会出现死锁，并终止进程。
在第一个连接的查询窗口的截图
在第二个连接的新建查询中的截图。
6.5. 更新锁更新锁 在第一个连接中执行以下语句 begintran select*fromdbo.studentwith (updlock)wheresname='华荣' waitfordelay'00:00:30' updatedbo.studentsetsname='白楠楠' committran
select*fromsys.dm_tran_locks 在第二个连接中 begintran select*fromdbo.studentwith (updlock)wheresname='华荣' waitfordelay'00:00:30' updatedbo.studentsetsname='白楠楠' committran 在第二个连接中如果同样加更新锁，则会阻塞当前查询，直到连接释放更新锁。 如果不加更新锁则直接可以读取更新锁的记录。 ,有时候我需要控制某条记录在我读取后就不许再进行更新,那么我就可以将所有要处理当前记录的查询都加上更新锁,以防止查询后被其它事务修改.将事务的影响降低到最小。
|
|
|
