SQL语法大全Tsql.docx
《SQL语法大全Tsql.docx》由会员分享，可在线阅读，更多相关《SQL语法大全Tsql.docx（38页珍藏版）》请在冰豆网上搜索。
SQL语法大全Tsql
T—SQL目录
一、T—SQL的组成1
1、DML（数据操作语言DataManipulationLanguage）1
2、DCL（数据控制语言DataControlLanguage）1
3、DDL（数据定义语言DataDefinitionLanguage）1
4、变量说明、流程控制、功能函数1
二、库1
1、建立库1
2、删除库2
三、表3
1、建表3
2、删表3
四、约束4
1、主键（primarykey）4
2、唯一性（uinque）4
3、默认填写（default（'……'）for）4
4、检查（check（……））4
5、外键（foreignkey（列名）references主表名（列名））4
6、删除约束4
五、通配符5
六、插入数据5
1、注意事项5
2、插入多行数据6
七、更新数据行7
八、删除数据行7
1、删除指定的行7
2、删除所有记录7
九、查询7
1、查询全部的行和列8
2、查询部分行8
3、自定义命名查询结果中的列名8
十、模糊查询10
1、LIKE10
2、ISNULL11
3、BETWEEN11
4、IN11
十一、聚合函数12
1、SUM（求和）12
2、AVG（求平均值）12
3、MAX、MIN（求最大、最小值）12
4、COUNT（计数）12
十二、分组查询13
1、单列分组查询13
2、多列分组13
3、HAVING（追加条件）13
4、条件比较顺序14
十三、多表联接查询14
1、分类14
2、多表内联结查询14
3、多表外联接查询15
4、多表交叉联接查询16
十四、数据库用户16
1、创建登录帐户16
2、创建数据库用户17
3、给用户分配权限17
4、系统内置的数据库用户17
十五、T—SQL编程18
1、变量18
2、输出语句19
3、逻辑控制语句19
十六、高级查询21
1、简单的子查询21
2、IN（NOTIN）子查询21
3、EXISTS子查询22
十七、事务22
1、使用T-SQL语句来管理事务22
2、判断某条语句执行是否出错22
3、事务必须具备ACID四个属性22
4、事务的分类23
5、事务例句23
十八、索引24
1、索引类型24
2、使用T-SQL语句创建索引24
3、索引的优缺点24
4、创建索引的指导原则25
5、索引例句25
十九、视图26
1、是么是视图26
2、视图的用途26
3、使用T-SQL语句创建视图26
4、视图例句26
二十、存储过程27
1、什么是存储过程（procedure）27
2、存储过程的优点27
3、存储过程的分类27
4、常用的系统存储过程28
5、使用T-SQL语句创建和调用存储过程28
6、处理存储过程中的错误32
二十一、触发器33
1、创建触发器的语法33
2、例句34
T—SQL
一、T—SQL的组成
1、DML（数据操作语言DataManipulationLanguage）
查询、插入、删除和修改数据库中的数据。
SELECT、INSERT、UPDATE、DELETE等；
2、DCL（数据控制语言DataControlLanguage）
用来控制存取许可、存取权限等。
GRANT、REVOKE等。
3、DDL（数据定义语言DataDefinitionLanguage）
用来建立数据库、数据库对象和定义其列。
CREATETABLE、DROPTABLE等。
4、变量说明、流程控制、功能函数
定义变量、判断、分支、循环结构等。
日期函数、数学函数、字符函数、系统函数等。
二、库
1、建立库
--判断是否存在该库,如果有则删除
USEmaster--设置当前数据库为master,以便访问sysdatabases表
GO
IFEXISTS（SELECT*FROMsysdatabasesWHEREname='stuDB'）
DROPDATABASEstuDB
--建立数据库
CREATEDATABASEstuDB
ONPRIMARY--默认就属于PRIMARY主文件组,可省略
（
NAME='stuDB_data',--主数据文件的逻辑名
FILENAME='D:
\project\stuDB_data.mdf',--主数据文件的物理名
SIZE=5mb,--主数据文件初始大小
MAXSIZE=100mb,--主数据文件增长的最大值
FILEGROWTH=15%--主数据文件的增长率
）
LOGON
（
NAME='stuDB_log',
FILENAME='D:
\project\stuDB_log.ldf',
SIZE=2mb,
FILEGROWTH=1MB
）
GO
2、删除库
USEmaster--设置当前数据库为master,以便访问sysdatabases表
GO
IFEXISTS（SELECT*FROMsysdatabasesWHEREname='stuDB'）
DROPDATABASEstuDB
EXISTS（）语句：
检测是否存在stuDB数据库,如果存在stuDB数据库,则删除
sysdatabases表在master数据库中,保存着当前系统中所有的数据库
三、表
1、建表
--判断是否存在该表,有则删除
USEstuDB--将当前数据库设置为stuDB,以便在stuDB数据库中建表
GO
IFEXISTS（SELECT*FROMsysobjectsWHEREname='stuInfo'）
DROPTABLEstuInfo
--建表
CREATETABLEstuInfo/*-创建学员信息表-*/
（
stuNameVARCHAR（20）NOTNULL,--姓名,非空（必填）
stuNoCHAR（6）NOTNULL,--学号,非空（必填）
stuAgeINTNOTNULL,--年龄,INT类型默认为4个字节
stuIDNUMERIC（18,0）,--身份证号
stuSeatSMALLINTIDENTITY（1,1）,--座位号,自动编号
stuAddressTEXT--住址,允许为空,即可选输入
）
GO
2、删表
USE库名--将当前数据库设置为stuDB,以便在stuDB数据库中建表
GO
IFEXISTS（SELECT*FROMsysobjectsWHEREname='表名'）
DROPTABLE表名
四、约束
1、主键（primarykey）
ALTERTABLEstuInfo
ADDCONSTRAINTPK_stuNoPRIMARYKEY（stuNo）
2、唯一性（uinque）
ALTERTABLEstuInfo
ADDCONSTRAINTUQ_stuIDUNIQUE（stuID）
3、默认填写（default（'……'）for）
ALTERTABLEstuInfo
ADDCONSTRAINTDF_stuAddress
DEFAULT（'地址不详'）FORstuAddress
4、检查（check（……））
ALTERTABLEstuInfo
ADDCONSTRAINTCK_stuAge
CHECK（stuAgeBETWEEN15AND40）
5、外键（foreignkey（列名）references主表名（列名））
ALTERTABLEstuMarks
ADDCONSTRAINTFK_stuNo
FOREIGNKEY（stuNo）REFERENCESstuInfo（stuNo）
6、删除约束
ALTERTABLE有约束的表名DORP约束名
五、通配符
通配符
解释
示例
'_'
一个字符
ALike'C_'
%
任意长度的字符串
BLike'CO_%'
[]
括号中所指定范围内的一个字符
CLike'9W0[1-2]'
[^]
不在括号中所指定范围内的一个字符
DLike'%[A-D][^1-2]'
六、插入数据
INSERT表名（列名）VALUES（插入的列值）
insertstuinfo（stuname,stuno,stuage,stuid,stuaddress）
values（'张三',001,20,100,'hello'）
1、注意事项
A、每次插入一行数据,不可能只插入半行或者几列数据,因此,插入的数据是否有效将按照整行的完整性的要求来检验。
B、每个数据值的数据类型、精度和小数位数必须与相应的列匹配。
C、不能为标识列指定值,因为它的数字是自动增长的。
D、如果在设计表的时候就指定了某列不允许为空,则必须插入数据。
E、插入的数据项,要求符合检查约束的要求。
F、具有缺省值的列,可以使用DEFAULT（缺省）关键字来代替插入的数值。
2、插入多行数据
（1）对象表存在
INSERTINTO<表名>（列名）
SELECT<列名>FROM<源表名>
INSERTINTOstuinfobak（stuname,stuno,stuage）
SELECTstuname,stuno,stuageFROMstuinfo
（Stuinfobak表必须在数据库中存在）
（2）对象表不存在
SELECT（列名）INTO<表名>FROM<源表名>
SELECTstuname,stuno,stuageINTOstuinfobak1FROMstuinfo
（Stuinfobak1表必须在数据库中不存在）
（3）插入新的标识列
IDENTITY（数据类型,标识种子,标识增量）
SELECTIDENTITY（数据类型,标识种子,标识增长量）AS列名
INTO新表FROM原始表
SELECTStudents.SName,Students.SAddress,Students.SEmail,
IDENTITY（int,1,1）AsStudentID
INTOTongXunLuEXFROMStudents
（4）插入多行内容
INSERTINTO<表名>（列名）
SELECT列内容UNION
SELECT列内容UNION
……
七、更新数据行
UPDATE<表名>SET<列名=更新值>
[WHERE<更新条件>]
UPDATEStudents
SETSAddress='北京女子职业技术学校家政班'
WHERESAddress='北京女子职业技术学校刺绣班'
UPDATEScores
SETScores=Scores+5
WHEREScores<=95
八、删除数据行
1、删除指定的行
DELETEFROM<表名>[WHERE<删除条件>]
注意：
DELETEFROM不会只删单个字段,要删就是整行
2、删除所有记录
TRUNCATETABLE<表名>
注意：
不能用于有外键约束引用的表。
删除后,表的结构、列、约束、索引不变。
九、查询
SELECT<列名>
FROM<表名>
[WHERE<查询条件表达式>]
[ORDERBY<排序的列名>[ASC（升）或DESC（降）]]--默认为升序
1、查询全部的行和列
SELECT*FROMStudents
2、查询部分行
SELECT<列名>
FROM<表名>
WHERE<查询条件表达式>
3、自定义命名查询结果中的列名
（1）使用AS来命名列
SELECT原表中列名AS查询结果中显示的列名
FROM<表名>
WHERE<查询条件表达式>
SELECTStunoAS学员编号,StuNameAS学员姓名,StuAddressAS学员地址
FROMstuinfo
WHERESAddresslike'地址不详'
（2）合并两列数据,以规定格式输出查询结果
SELECT原表中列名+'.'+原表中列名AS查询结果中显示的列名
FROM表名
（3）使用=来命名列
SELECT查询结果中显示的列名=原表中列名
FROM<表名>
WHERE<查询条件表达式>
SELECT查询结果中显示的列名=原表中列名+'.'+原表中列名
FROM表名
（4）查询某几列为空的行
SELECT列名A
FROM表名
WHERE条件列名BISNULL
显示结果：
B列为空的A列内容。
（5）显示结果中加入常量列
SELECT列A=SName,列B=SAddress,'常量列内容'AS常量列名称
FROMStudents
SELECT姓名=SName,地址=SAddress,'河北新龙'AS学校名称
FROMStudents
（6）限制查询结果输出的行数
SELECTTOP5列名
FROM表名
WHERE条件
显示结果：
符合条件的前五行。
（7）返回百分之多少行
SELECTTOP20PERCENT列名
FROM表名
WHERE条件
显示结果：
符合条件的行数的前20%行。
（8）升序排列（默认）ASC
SELECTStudentIDAs学员编号,（Score*0.9+5）As综合成绩
FROMScore
WHERE（Score*0.9+5）>60
ORDERBYScore
（9）降序排列DESC
SELECTAu_Lname+'.'+Au_fNameASEMP
From表AUnion
SELECTfName+'.'+LNameASEMP
From表B
ORDERBYEMPDESC
显示结果：
混合查找两张表中的列,并按格式输出到新列中,并按新列排序输出。
（10）按多列排序
SELECTStudentIDAs学员编号,ScoreAs成绩
FROM表名
WHEREScore>60
ORDERBYScore,studentID
显示结果：
先按Score排序，再按studentID排序。
十、模糊查询
1、LIKE
查询时,字段中的内容并不一定与查询内容完全匹配,只要字段中含有这些内容。
SELECTStuNameAS姓名
FROMStuinfo
WHEREstunameLIKE'徐%'
显示结果：
姓为“徐”的人的名字。
2、ISNULL
把某一字段中内容为空的记录查询出来。
SELECTStuNameAS姓名,StuAddressAS地址。
FROMStuinfo
WHEREStuAddressISNULL
显示结果：
把地址栏为空的显示出来。
3、BETWEEN
把某一字段中内容在特定范围内的记录查询出来。
SELECTStuNo,Score
FROMStumarks
WHEREScoreBETWEEN60AND80
显示结果：
把分数80>=Score>=60的显示出来。
4、IN
把某一字段中内容与所列出的查询内容列表匹配的记录查询出来。
SELECTStuNameAS学员姓名,StuAddressAs地址
FROMStuinfo
WHEREStuAddressIN（'北京','广州','上海'）
显示结果：
把地址在（'北京','广州','上海'）里的显示出来。
十一、聚合函数
1、SUM（求和）
SELECTSUM（Score）
FROMStumarks
WHERE条件
显示结果：
把符合条件的Score求和,然后显示结果。
2、AVG（求平均值）
SELECTAVG（Score）AS平均成绩
FromScore
WHEREScore>=60
显示结果：
把Score>=60的成绩求平均值,然后显示结果,显示的列名为“平均成绩”
3、MAX、MIN（求最大、最小值）
SELECTMAX（Score）AS最高分,MIN（Score）AS最低分
FromScore
WHEREScore>=60
显示结果：
把Score>=60中的最高分和最低分显示出来。
4、COUNT（计数）
SELECTCOUNT（*）AS及格人数
FromScore
WHEREScore>=60
显示结果：
把Score列中,>=60的个数统计出来,然后显示统计数目。
十二、分组查询
1、单列分组查询
SELECTCourseID,AVG（Score）AS课程平均成绩
FROMScore
GROUPBYCourseID
显示结果：
按CourseID组求Score的平均值,然后将CourseID和平均值显示出来。
2、多列分组
SELECTStudentIDAS学员编号,CourseIDAS内部测试,AVG（Score）AS平均成绩
FROMScore
GROUPBYStudentID,CourseID
显示结果：
显示所有学员的：
“学员编号”（StudentID）,“内部测试”（CourseID）,“平均成绩”（AVG（Score））。
如果同一CourseID组中出现了相同的StudentID,则显示出来的是这一CourseID组中相同的StudentID的平均成绩。
3、HAVING（追加条件）
SELECTStudentIDAS学员编号,CourseIDAS内部测试,AVG（Score）AS平均成绩
FROMScore
GROUPBYStudentID,CourseID
HAVINGCOUNT（Score）>1
显示结果：
显示补考学员的：
“学员编号”（StudentID）,“内部测试”（CourseID）,“平均成绩”（AVG（Score））。
如果同一CourseID组中的同一StudentID组中记录Score的次数,如果次数>1,则显示出来“学员编号”（StudentID）,“内部测试”（CourseID）,“平均成绩”（AVG（Score））。
4、条件比较顺序
WHERE子句从数据源中去掉不符合其搜索条件的数据。
GROUPBY子句搜集数据行到各个组中,统计函数为各个组计算统计值。
HAVING子句去掉不符合其组搜索条件的各组数据行。
WHERE———>GROUPBY———>HAVING
十三、多表联接查询
1、分类
（1）内联接（INNERJOIN）
（2）外联接
A、左外联结（LEFTJOIN）
B、右外联结（RIGHTJOIN）
C、完整外联结（FULLJOIN）
（3）交叉联接（CROSSJOIN）
2、多表内联结查询
（1）建立联接
SELECTS.SName,C.CourseID,C.Score
FromScoreASC
INNERJOINStudentsASS
ONC.StudentID=S.SCode
显示结果：
把Score表和Students表建立内联结,查询C.StudentID=S.SCode时,显示S.SName,C.CourseID,C.Score的内容。
（2）未建立联接
SELECTStudents.SName,Score.CourseID,Score.Score
FROMStudents,Score
WHEREStudents.SCode=Score.StudentID
显示结果：
同上。
（3）多表联接查询—三表联接
SELECTS.SNameAS姓名,CS.CourseNameAS课程,C.ScoreAS成绩
FROMStudentsASS
INNERJOINScoreASC
ON（S.SCode=C.StudentID）
INNERJOINCourseASCS
ON（CS.CourseID=C.CourseID）
（4）区别
建立联接的查询速度比没有建立的快得多。
3、多表外联接查询
（1）左外联接
（LEFTJOIN或LEFTOUTERJOIN）
SELECTS.SName,C.CourseID,C.Score
FromStudentsASS
LEFTJOINScoreASC
ONC.StudentID=S.SCode
显示结果：
Students为左表（leftjoin）Score为右表。
左表中有的,右表中没有的显示空值（NULL）。
（2）右外联接
（RIGHTJOIN或RIGHTOUTERJOIN）
SELECTTitles.Title_id,Titles.Title,Publishers.Pub_name
FROMtitles
RIGHTOUTERJOINPublishers
ONTitles.Pub_id=Publishers.Pub_id
显示结果：
与左外连接相反。
（3）完整外联接
（FULLJOIN或FULLOUTERJOIN）
显示结果：
左表和右表中的所有行。
当某行在另一个表中没有匹配行时,则另一个表的选择列为空值；如果有匹配行,则显示结果包括左右表中的所有列值。
4、多表交叉联接查询
（CROSSJOIN）
交叉联接返回左表中的所有行,左表中的每一行与右表中的所有行一一组合,相当于两个表“相乘”。
十四、数据库用户
1、创建登录帐户
（1）添加Windows登录帐户
EXECsp_grantlogin'jbtraining\S26301'（域名\用户名）
（2）添加SQL登录帐户
EXECsp_addlogin'zhangsan','1234'
EXEC表示调用存储过程,存储过程类似C语言的函数。
内置的系统管理员帐户sa,密码默认为空,建议修改密码
2、创建数据库用户
USE库名
GO
EXECsp_grantdbaccess'登录帐户名','数据库用户名'
其中,“数据库用户“为可选参数,默认为登录帐户,即数据库用户默认和登录帐户同名。
3、给用户分配权限
GRANT权限[ON表名]TO数据库用户
权限：
select、insert、update、delete、createtable……
4、系统内置的数据库用户
（1）dbo用户
A、表示数据库的所有者（DBOwner）
B、无法删除
升级会员 