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 tableteacher
(cust_idint ,company varchar(40),contact varchar(30),phone char(12))
go
alter tableteacheraddageint default30
数据控制语言(Date Control Language)语句
用于改变某个用户或角色相关的权限
grant
deny
revoke
举例说明:
useschoolDB
grant select on products to public
go
数据操作语言(Date Manipulation Language)语句
操作数据库中的数据。可以更改数据库中的数据或查询数据库中的信息
举例说明:
select*fromdbo.TStudent//查询TStudent表中的数据
insertdbo.TStudent(StudentID,Sname,sex)values('0000001901','陈英宏','男')//为TStudent表中的studentID,Sname,sex列插入相应的记录
updatedbo.TStudentsetSname='张春海',sex='男'wherestudentid='0000000569'//更新表中studentID为0000000569的姓名修改为'张春海'
deletedbo.TStudentwhereStudentID='0000000020'//删除studentID为0000000020的记录
2.Ttansact-SQL语法要素
1. 批处理
go
一个批处理命令通知SQLServer分析并运行一个批处理的所有指令
实际上不是 Transact-SQL语句,只是描述一个批处理。局部变量作用范围局限在一个批内,必须独占一行。
USE schoolDB
go
select * from dbo.TScore--从那张表查找
where mark+9<60
go
2. 表达式
1.算数运算符
+ - * / %
比较运算符 =<>>=<
!= :不等于,等同于<>
BETWEEN :指定值得包含范围(包含边界)。使用AND分隔开始值和结束值
IS[NOT]NULL :根据使用的关键字,指定是否搜索空值或非空值。如果有任何一个操作数为NULL,则包含运算符或算数运算符的表达式的计算结果为NULL
LIKE:模糊查询,与指定字符串进行模糊匹配
IN:是否在数据范围里面
字符串联运算符 + 空字符不等于空值
表达式是各种符号和对单个数据进行操作
selectmark+5fromdbo.TScorewheremark<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*fromdbo.TStudent
使用where子句指定行
selectSname,sex,Emailfromdbo.TStudentwhereSname='田育朋'
3.1.2.过滤数据
使用比较操作符 = ><>= <= <>
select*fromdbo.TScorewheremark<=60
使用字符比较符 like
% 0个或多个字符串
_ 任何单个的字符
[]在指定区域或集合内的任何单个字符
[^]不在指定区域或集合内的任何单个字符
select*fromdbo.TStudentwheresnamelike'高%'
select*fromdbo.TStudentwheresnamelike'_[明,育]_'
select*fromdbo.TStudentwheresnamelike'_[^明,育]_'
3.1.3.使用逻辑操作符
OR AND NOT使用方法
select*fromdbo.TStudentwhereSnamelike'高%'andsex='男'orStudentID='0000000112'
查找不姓高的学生
select*fromdbo.TStudentwhereSnamenotlike'高%'
3.1.4.查找在一定范围的值
select*fromdbo.TScorewheremarkbetween70and80
等价于
select*fromdbo.TScorewheremark>=70andmark<=80
不包括70 和80
尽量使用between而不使用and和比较操作符表示的表达式
如果想返回不在指定区域的行时,使用not between 。这样会降低数据查询的速度。
select*fromdbo.TScorewheremarknotbetween70and80
指定时间范围
select*fromdbo.TStudentwhereBirthdaybetween'1983-01-01'and'1984-01-01'
3.1.5.查询空值
insertdbo.TStudent(StudentID,Sname,sex)values('0000001901','陈英宏','男')
查找班级不为空的学生
select*fromdbo.TStudentwhereClassisnotnull
查找班级为空的学生
select*fromdbo.TStudentwhereClassis null
使用is not null来查询指定列中非空的行
3.2.格式化结果集
3.2.1.给数据排序
selectStudentID,subJectID,markfromdbo.TScoreorderby2,3desc
selectStudentID,subJectID,markfromdbo.TScoreorderbysubJectID,markdesc
asc升序
desc降序
3.2.2.消除重复的行
distinct
selectdistinctClassfromdbo.TStudent
3.2.3.改变字段的名字
selectStudentIDas'学号',Snameas'姓名',sexas'性别',cardIDas'×××号',Birthdayas'生日',Emailas'邮件',Classas'专业',enterTimeas'录入时间'fromdbo.Tstudent
等价于
selectStudentID'学号',Sname'姓名',sex'性别',cardID'×××号',
Birthday'生日',Email'邮件',Class'专业',enterTime'录入时间'fromdbo.TStudent
3.2.4.使用符号
符号可能是字母,数字或标识,在结果集中,他们被用作特定的值,以增加结果集的可读性。
selectStudentID'学号',Sname'姓名',sex'性别','性别'fromdbo.Tstudent
3.2.5.计算列
年龄是计算列
selectStudentIDas'学号',Snameas'姓名',sexas'性别',cardIDas'×××号',Birthdayas'生日',Emailas'邮件',Classas'专业',enterTimeas'录入时间',Datediff(yy,Birthday,getdate())as'年龄'fromdbo.TStudent
3.3.使用T-SQL语句实现多表查询
前面查询都是基于单个数据库表的查询,如果一个查询需要对多个表进行操作,就成为连接查询,连接查询的结果集或结果称为表之间的连接。
表连接的类型:
内连接、外连接、交叉连接
创建多表查询的练习环境
--创建学生表student
Createtablestudent
(
studentidint,
snamenvarchar(10),
sexnchar(1)
)
--插入学生
insertstudentvalues(1,'华荣','女')
insertstudentvalues(2,'王景正','男')
insertstudentvalues(3,'郭淑丽','女')
insertstudentvalues(4,'韩旭','女')
insertstudentvalues(5,'孟小飞','男')
--创建成绩表
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)
1.查询所有学生的成绩
selecta.*,b.*fromstudent ajoinscore bona.studentid=b.studentid
selectsname,subjectname,scorefromstudent ajoinscore bona.studentid=b.studentid
selecta.studentid,sname,subjectname,scorefromstudent ajoinscore bona.studentid=b.studentid
2.从多个表中合并数据
使用内连接
selecta.*,b.*fromstudent ajoinscore bona.studentid=b.studentid
等价于
selecta.*,b.*fromstudent ainnerjoinscore bona.studentid=b.studentid
使用外连接
左外连接
selecta.studentid,sname,subjectname,scorefromdbo.student aleftjoindbo.score bona.studentid=b.studentid
右外连接
selecta.*,b.*fromdbo.student arightjoindbo.score bona.studentid=b.studentid
练习:
1.查找不及格同学姓名和学科分数
selectsname,subjectname,scorefromdbo.student ajoindbo.score bona.studentid=b.studentidwherescore<60
自连接
查找到重名的学生
insertstudentvalues(6,'韩旭','男')
selecta.*,b.*fromdbo.student ajoindbo.student bona.sname=b.snamewherea.studentid<>b.studentid
3.4.数据分组和汇总
3.4.1.用TOP n列出前n个记录
结合Order by找出满足条件的前几条记录
1.年龄最大的前5名学生
selecttop5*fromdbo.TStudentorderbyBirthday
2.年龄最小的前5名学生
selecttop5*fromdbo.TStudentorderbyBirthdaydesc
查找网络班年龄最小的前5名学生
selecttop5*fromdbo.TstudentwhereClass='网络班'orderbyBirthdaydesc
3.4.2.使用聚集函数
可以在Select 语句中单独使用聚集函数,也可以与语句group by联合使用
除了count(*)函数,如果没有满足where子句的记录,则所有的聚集函数都将返回空值,Count(*)返回0
Count(*)Count(列)SumMin maxAvg
统计表中有多少行
selectcount(*)fromdbo.TStudent
插入一条没有班级的记录
insertdbo.TStudent(studentID,Sname,sex)values('0000001901','王敬正','男')
select count(Class) from dbo.TStudent
求平均值
selectavg(mark)fromdbo.TScore
求最大值和最小值
selectmax(mark)fromdbo.TScore
selectmin(mark)fromdbo.TScore
求总和
selectsum(mark)fromdbo.TScore
3.4.3.使用GROUP BY基础
如果使用聚集函数,则将对表中的所有记录的某个字段进行汇总,然后生成单个的值。如果想生成多个汇总值,同时使用聚集函数和group by 语句,联合使用having和group by子句能够使结果集只包含满足条件的记录。
联合使用group by子句与having子句
分组汇总
1.统计每个班有多少个学生
selectClass,count(*)fromdbo.TStudentgroupbyClass
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.插入数据
insertdbo.TStudentvalues('0000001902','李维伟','男','132302197506055634','1984-3-4','liweiwei@bdqn.com','网络班',getdate())
4.2.插入部分数据
插入学生学号 ;姓名性别其他列为空
insertdbo.TStudent(StudentID,sex,Sname)values('0000001903','男','张国强')
4.3.将查询记录插创建的新表
使用SELECT INTO创建表。
将学生表中开发班的从学生查询到一个新表
selectStudentID,Sname,sex,EmailintoTDefromdbo.TStudentwhereClass='开发班'
4.4.删除数据
删除学号是0000000020的学生
deletedbo.TStudentwhereStudentID='0000000020'
删除1982年以前出生的学生
deletedbo.TStudentwhereBirthday
更新数据
更改学生学号是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-1000wherecustomerName='张三'
updatebanksetcurrentMoney=currentMoney-1000wherecustomerName='李四'
select*frombank
执行以上命令后李四账户增加了1000,但是张三账户余额仍然是1000,这样的转账结果肯定是不可取,我们可以通过以下方法来解决。
首先转账过程就是一个事物,它需要两条UPDATE语句来完成,这两条语句是一个整体。如果其中任何一条出现错误,则整个转账业务也应该取消,两个账户的余额应该恢复到原来的状态。
通过以下语句来解决上述转账的问题
print'查看转帐事务前的余额'select*frombank
go
begintransaction
declare@errorSumint
set@errorSum=0
updatebanksetcurrentMoney=currentMoney-1000wherecustomerName='张三'
set@errorSum=@errorSum+@@ERROR
updatebanksetcurrentMoney=currentMoney+1000wherecustomerName='李四'
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