sql常用语句总结.docx
《sql常用语句总结.docx》由会员分享，可在线阅读，更多相关《sql常用语句总结.docx（22页珍藏版）》请在冰豆网上搜索。
sql常用语句总结
SQL常用语句总结
MSSQL里没有括号时，运算进行的次序将是先乘后除再模后加减
减号（-）也有两种用途：
1.作为负号使用2.从某一列中减去另一列
andornot
如果一个where子句中同时出现这三个操作符
最先评估not然后是and然后是or
coalesce哪个不为空用哪个
coalesce（i.ProductID,d.ProductID）
@@rowcount
返回上一条语句影响的行数
SQL判断某列中是否包含中文字符或者英文字符
select*from表名where某列like'%[吖-座]%'
select*from表名where某列like'%[a-z]%'
--数据操作，中英文对照
select--从数据库表中检索数据行和列
insert--向数据库表添加新数据行
delete--从数据库表中删除数据行
update--更新数据库表中的数据
--数据定义
createtable--创建一个数据库表
droptable--从数据库中删除表
altertable--修改数据库表结构
createview--创建一个视图
dropview--从数据库中删除视图
createindex--为数据库表创建一个索引
dropindex--从数据库中删除索引
createproceduer--创建一个存储过程
dropproceduer--从数据库中删除存储过程
createtrigger--创建一个触发器
droptrigger--从数据库中删除触发器
createschema--向数据库添加一个新模式
dropschema--从数据库中删除一个模式
createdomain--创建一个数据值域
alterdomain--改变域定义
dropdomain--从数据库中删除一个域
--数据控制
grant--授予用户访问权限
deny--拒绝用户访问
revoke--解除用户访问权限
--事务控制
commit--结束当前事务
rollback--中止当前事务
settransaction--定义当前事务数据访问特征
--程序化SQL
declare--为查询设定游标
explan--为查询描述数据访问计划
open--检索查询结果打开一个游标
fetch--检索一行查询结果
close--关闭游标
prepare--为动态执行准备SQL语句
execute--动态地执行SQL语句
describe--描述准备好的查询
------------------SQL中插入数据的技巧-----------------
插入少量数据时可以用：
insertinto表名（列名1，列名2...）values（值1，值2...）
插入大量数据时可以用：
insertinto要复制的表名select*from源表名
或：
insertinto要复制的表名（列名1，列名2...）select（列名1，列名2...）from源表名
insertselect语句要求你遵循如下规则：
SELECT语句不能从被插入数据的表中选择行
INSERTINTO中的列数必须与SELECT语句返回的列数相等
INSERTINTO中的数据类型要与SELECT语句返回的数据类型相同
insertselect语句的另外一个用处对表进行备份：
select*into临时表名from源表名
----------SQL中truncatetable和delete和drop的区别----------
truncatetable在功能上与不带Where子句的Delete语句相同,二者均删除表中的全部行
但truncatetable比Delete速度快，且使用的系统和事务日志资源少。
Delete语句每次删除一行，并在事务日志中为所删除的每行记录一项。
truncatetable通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
truncatetable删除表中的所有行，但表结构及其列、约束、索引等保持不变。
新行标识所用的计数值重置为该列的种子。
如果想保留标识计数值，请改用Delete。
对于由FOREIGNKEY约束引用的表，不能使用truncatetable，而应使用不带Where子句的Delete语句。
由于truncatetable不记录在日志中，所以它不能激活触发器。
truncatetable不能用于参与了索引视图的表。
truncate,delete,drop的异同点：
注意:
这里说的delete是指不带where子句的delete语句
相同点:
truncate和不带where子句的delete,以及drop都会删除表内的数据
不同点:
1.truncate和delete只删除数据不删除表的结构（定义）
drop语句将删除表的结构被依赖的约束（constrain）,触发器（trigger）,索引（index）;
依赖于该表的存储过程/函数将保留,但是变为invalid状态.
2.delete语句是dml,这个操作会放到rollbacksegement中,事务提交之后才生效;
如果有相应的trigger,执行的时候将被触发.
truncate,drop是ddl,操作立即生效,原数据不放到rollbacksegment中,不能回滚.操作不触发trigger.
3.delete语句不影响表所占用的extent,高水线（highw2atermark）保持原位置不动
显然drop语句将表所占用的空间全部释放
truncate语句缺省情况下将空间释放到minextents个extent,除非使用reusestorage;
truncate会将高水线复位（回到最开始）.
4.速度,一般来说:
drop>truncate>delete
5.安全性:
小心使用drop和truncate,尤其没有备份的时候.否则哭都来不及
使用上：
想删除部分数据行用delete,注意带上where子句.回滚段要足够大.
想删除表,当然用drop
想保留表而将所有数据删除.如果和事务无关,用truncate即可.
如果和事务有关,或者想触发trigger,还是用delete.
如果是整理表内部的碎片,可以用truncate跟上reusestroage,再重新导入/插入数据
---------------------------好句收藏--------------------------
1.UNION将返回两个查询的结果并去除其中的重复部分
SELECTNAMEFROMSOFTBALL
UNION
SELECTNAMEFROMFOOTBALL
distinct（去掉完全重复的记录）
selectdistinct*from表名
withties（并列显示完全重复的记录）
selectTop10withties*from表名
2.UNIONALL不去掉重复的记录
SELECTNAMEFROMSOFTBALL
UNIONALL
SELECTNAMEFROMFOOTBALL
3.INTERSECT返回两个表中共有的行
SELECT*FROMFOOTBALL
INTERSECT
SELECT*FROMSOFTBALL
4.MINUS（相减）
返回的记录是存在于第一个表中但不存在于第二个表中的记录
SELECT*FROMFOOTBALL
MINUS
SELECT*FROMSOFTBALL
5.in（满足括号里任意一个条件即可）
SELECT*FROMFRIENDSWHERESTATEIN（'CA','CO','LA'）
6.BEWTEEN（满足区间）
SELECT*FROMPRICEWHEREWHOLESALEBETWEEN0.25AND0.75
7.连接（||）
可以将两个字符串连接起来
SELECT（NAMEa||NAMEb）as新列名FROMFRIENDS
格式化连接
SELECT（NAMEa||','||NAMEb）as新列名FROMFRIENDS--在连接的数据中间加上自定义字符串
8.STARTINGWITH（它的作用与like相似,用之前要测试sql解释器是否支持此语法）
SELECT列名1，列名2...FROM表名WHERE列名STARTINGWITH（'Ca'）
ORDERBY（排序）
升序：
SELECT*FROM表名ORDERBY列名
降序：
SELECT*FROM表名ORDERBY列名DESC
技巧：
假如你已经知道了你想要进行排序的列是表中的第一列的话，那么你可以用ORDER
BY1来代替输入列的名字
GROUPBY（分组查询）
SELECT列名FROM表名GROUPBY列名
HAVING（对你需要进行分组的数据进行限制）
SELECT列名1，AVG（列名2）FROM表名GROUPBY列名HAVINGAVG（列名3）>66
---表的不等值联合
不等值联合则是在WHERE子句中使用除了等号以外的其它比较运算符
例句：
SELECTO.NAME,O.PARTNUM,P.PARTNUMFROMORDERSasO,PARTasPWHEREO.PARTNUM>P.PARTNUM
---表的自我联合
WHEREF.PARTNUM=S.PARTNUMANDF.DESCRIPTION<>S.DESCRIPTION
------------------------日期函数--------------------------
VARIANCE（返回某一列数值的方差）
例句：
SELECTVARIANCE（列名）AS新列名FROM表名--列必须为int或double等数值类型
STDDEV（返回某一列数值的标准差）
例句：
SELECTSTDDEV（列名）AS新列名FROM表名--列必须为int或double等数值类型
ADD_MONTHS（该函数的功能是将给定的日期增加一个月）
例句：
SELECTADD_MONTHS（ENDDATE,1）AS新列名FROM表名--ENDDATE为datetime类型
ADD_MONTHS（ENDDATE,1）可以使用于where条件
LAST_DAY（可以返回指定月份的最后一天是几号）
例句：
SELECTLAST_DAY（ENDDATE）AS新列名FROM表名
DISTINCT（得到唯一的结果,就是去掉重复的结果）
例句：
SELECTDISTINCT列名FROM表名
MONTHS_BETWEEN（得到给定的两个日期中有多少个月）
例句1：
SELECTMONTHS_BETWEEN（ENDDATE,STARTDATE）AS新列名FROM表名
例句2：
SELECT*FROM表名WHEREMONTHS_BETWEEN（DATETIME1,DATETIME2）>0
SYSDATE（将返回系统的日期和时间）
例句：
SELECTDISTINCTSYSDATEFROM表名
----------------------数学函数--------------------------
ABS（）函数返回给定数字的绝对值
CEIL（）返回与给定参数相等或比给定参数在的最小整数
FLOOR（）返回与给定参数相等或比给定参数在的最大整数
MOD（A,B）返回A与B相除后的余数
SIGN（）如果参数的值为负数返回-1,如果参数的值为正数返回1,如果参数为零返回零
SQRT（）该函数返回参数的平方根,由于负数是不能开平方的所以不能将该函数应用于负数
-----------------------字符函数---------------------------
CHR（）该函数返回与所给数值参数等当的在ASCLL码字符,返回的字符取决于数据库所依赖的字符集
例句：
SELECTCHR（列名）FROM表名
CONCAT（）与||符号相同，表示将两个字符串连接起来
例句：
SELECTCONCAT（列名1,列名2）FROM表名
INITCAP（）该函数将参数的第一个字母变为大写,此外其它的字母则转换成小写
例句：
SELECTINITCAP（列名）as新列名FROM表名
LOWER（）将参数全部转换为小写字母
UPPER（）将参数全部转换为大写字母
LENGTH（）将返回指定字符串的长度
----------------------转换函数----------------------
TO_CHAR（）将一个数字转换为字符型
TO_NUMBER（）将一个字符串型数字转换为数值型
---其它函数
GREATEST（）将会返回在字母表中最靠后的字符开头的字符串,函数是返回几个表达式中最大的；
例句：
SELECTGREATEST（‘ALPHA’,’BRAVO’,’FOXTROT’,’DELTA’,’FP’）FROM表名
LEAST（）函数是返回几个表达式中最小的！
例句：
SELECTDISTINCTLEAST（34,567,3,45,1090）FROM表名
USER（）函数将返回当前使用数据库的用户的名字
例句：
SELECTDISTINCTUSERFROM表名
-------------------条件语句后用的关键字---------------
EXISTS
从子查询中返回的行数至少有一行时，EXIST返回为true。
返回为空时，EXIST返回为false。
例句：
SELECTNAMEFROMORDERS
WHEREEXISTS（SELECT*FROMORDERSWHERENAME='MOSTLYHARMLESS'）
ANY/SOMEANY与SOME具有同样的功能
ANY与子查询中的每一行与主查询进行比较,并对子查询中的每一行返回一个TRUE值
区别：
IN只相当于多个等号的作用,IN不能用于大于或小于的判断。
而ANY和SOME则可以使用其它的比较运算符如大于或小于。
例句：
SELECTNAMEFROMORDERSWHERENAME>ANY
（SELECTNAMEFROMORDERSWHERENAME='JACKSBIKE'）
ALL关键字的作用在于子查询中的所有结果均满足条件时它才会返回TRUE，ALL常起双重否定的作用。
例句：
SELECTNAMEFROMORDERSWHERENAME<>ALL
（SELECTNAMEFROMORDERSWHERENAME='JACKSBIKE'）
-------------------局部变量和全局变量--------------------
局部变量必须以“@”开头，而且必须先用DECLARE命令说明后才可使用。
语法：
DECLARE@变量名变量类型
局部变量赋值必须使用SELECT或SET命令来设定变量的值
语法：
SELECT@局部变量=变量值
SET@局部变量=变量值
全局变量不是由用户的程序定义的，它们是在服务器级定应义的。
只能使用预先说明及定义的变局变量。
引用全局变量时，必须以“@@”开头。
局部变量的名称不能与全局变量的名称相同、否则会在应用中出错
----------------------事务-视图-索引------------------
事务是一种机制，用以维护数据库的完整性。
事务有4个属性：
原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）以及持久性（Durability），也称作事务的ACID属性。
原子性：
事务内的所有工作要么全部完成，要么全部不完成，不存在只有一部分完成的情况。
一致性：
事务内的然后操作都不能违反数据库的然后约束或规则，事务完成时有内部数据结构都必须是正确的。
隔离性：
事务直接是相互隔离的，如果有两个事务对同一个数据库进行操作，比如读取表数据。
任何一个事务看到的所有内容要么是其他事务完成之前的状态，要么是其他事务完成之后的状态。
一个事务不可能遇到另一个事务的中间状态。
持久性：
事务完成之后，它对数据库系统的影响是持久的，即使是系统错误，重新启动系统后，该事务的结果依然存在。
事务的模式
a、显示事务
显示事务就是用户使用T-SQL明确的定义事务的开始（begintransaction）和提交（committransaction）或回滚事务（rollbacktransaction）
b、自动提交事务
自动提交事务是一种能够自动执行并能自动回滚事务，这种方式是T-SQL的默认事务方式。
例如在删除一个表记录的时候，如果这条记录有主外键关系的时候，删除就会受主外键约束的影响，那么这个删除就会取消。
可以设置事务进入隐式方式：
setimplicit_transactionon;
c、隐式事务
隐式事务是指当事务提交或回滚后，SQLServer自动开始事务。
因此，隐式事务不需要使用begintransaction显示开始，
只需直接失业提交事务或回滚事务的T-SQL语句即可。
使用时，需要设置setimplicit_transactionon语句，将隐式事务模式打开，下一个语句会启动一个新的事物，再下一个语句又将启动一个新事务。
开始事务：
begintransaction
提交事务：
committransaction
回滚事务：
rollbacktransaction
创建视图：
createview视图名as
删除视图：
dropview试图名
视图定义中的select语句中不能包括下列：
1.orderby子句，除非select语句的选择列有top子句
2.into关键字
3.引用临时表或变量
创建索引：
createunique【clustered|nonclustered】index索引名on表名（列名）【withfillfactor=x】
unique可选，指定唯一索引
clustered,nonclustered可选，指定是聚集索引或非聚集索引
fillfactor可选，表示填充因子，指定一个0-100的值，该值指示索引页填满的空间所占的百分比
删除索引：
dropindex表名.索引名
下面的表总结了何时使用聚集索引或非聚集索引（很重要）。
动作描述使用聚集索引使用非聚集索引
外键列应应
主键列应应
列经常被分组排序（orderby）应应
返回某范围内的数据应不应
小数目的不同值应不应
大数目的不同值不应应
频繁更新的列不应应
频繁修改索引列不应应
一个或极少不同值不应不应
-------------------------操作数据库----------------------
SQL分类：
DDL类型包括数据库、表的创建，修改，删除，声明—数据定义语言（CREATE，ALTER，DROP，DECLARE）
DML类型包括数据表中记录的查询，删除，修改，插入—数据操纵语言（SELECT，DELETE，UPDATE，INSERT）
DCL类型包括数据库用户赋权，废除用户访问权限，提交当前事务，中止当前事务—数据控制语言（GRANT，REVOKE，COMMIT，ROLLBACK）
首先,简要介绍基础语句：
1、说明：
创建数据库
CREATEDATABASEdb1（db1代表数据库，可自命名）
onprimary--默认属于primary主文件组，可省略
（
--数据文件的具体描述
name='MySchool_data',--主数据文件的逻辑名称
filename='D:
\project\MySchool_data.mdf',--主数据文件的物理名称
size=5MB,--主数据文件的初始大小
maxsize=100MB,--主数据文件增长的最大值
filegrowth=15%--主数据文件的增长率
）
logon
（
--日记文件的具体描述，各参数含义同上
name='MySchool_log',
filename='D:
\project\MySchool_data.ldf',
size=2MB,
filegrowth=1MB
）
2、说明：
删除数据库
dropdatabasedb1（db1代表数据库，可自命名）
3、说明：
备份sqlserver
---创建备份数据的device
USEmaster
EXECsp_addumpdevice'disk','testBack','c:
\mssql7backup\MyNwind_1.dat'
---开始备份
BACKUPDATABASEpubsTOtestBack
4、说明：
创建新表
createtabletb1
（
Idintnotnullprimarykey,--设置为主键
oneintidentity（1,1）,--设为标识列
namevarcharnotnull,--非空
phonenvarchar（100）,--可以为空
...
）
根据已有的表创建新表：
A：
createtabletab_newliketab_old（使用旧表创建新表）
B：
createtabletab_newasselectcol1,col2…fromtab_olddefinitiononly
5、说明：
删除新表：
droptabletb1
useMySchool--将当前数据库设置为MySchool
ifexists（select*fromMySchoolwherename='Student'）--exist是查询语句，检测某个查询是否存在
droptableStudent
6、说明：
增加一个列：
Altertable表名add字段名字段类型字段说明/约束
添加带主键及约束的语法：
altertable表名
addconstraint约束名约束类型具体的约束说明
--添加
升级会员 