SQL常用语句大全.docx
《SQL常用语句大全.docx》由会员分享，可在线阅读，更多相关《SQL常用语句大全.docx（22页珍藏版）》请在冰豆网上搜索。
SQL常用语句大全
/**
所有关键字必须要大写.因为代码在执行之前会帮我们自动优化
而优化第一步就是讲所有的关键字转换成大写.
**/
IFEXISTS（SELECT*FROMsys.databasesWHEREname='sql'）
BEGIN
PRINT'sql数据库已删除!
'
DROPDATABASEsql;
END
ELSE
BEGIN
--先创建文件夹
EXECxp_cmdshell'mkdirE:
\\Database';
--创建数据库
CREATEDATABASEsql
--设置为主文件组中
ONPRIMARY
（
--逻辑名称,相当于别名.后期可以通过逻辑名称访问该数据库信息
NAME='sql_DAT',
--文件物理存储地址
FILENAME='E:
\\Database\\sql.MDF',
--默认大小,单位为KB,MB,GB
SIZE=5MB,
--最大上限
MAXSIZE=UNLIMITED,
--文件增长率
FILEGROWTH=10%
）,
（
--次数据文件
NAME='sql_DAT2',
FILENAME='E:
\\Database\\sql.NDF',
SIZE=5MB,
MAXSIZE=UNLIMITED,
FILEGROWTH=10%
）
LOGON
（
--日志文件
NAME='sql_LOG',
FILENAME='E:
\\Database\\sql.LDF',
SIZE=1MB,
MAXSIZE=UNLIMITED,
FILEGROWTH=10%
）,
（
--日志文件
NAME='sql_LOG2',
FILENAME='E:
\\Database\\sql2.LDF',
SIZE=1MB,
MAXSIZE=UNLIMITED,
FILEGROWTH=10%
）
--提示
PRINT'sql数据库已创建!
';
END
GO
--选择数据库
USEsql;
--修改数据库名称
EXECsp_renamedb'sql','new_sql';
--删除数据库
DROPDATABASEsql;
--老师表
IFEXISTS（SELECT*FROMsys.tablesWHEREname='teacher'）
BEGIN
PRINT'teacher表已删除!
';
DROPTABLEteacher;
END
ELSE
BEGIN
CREATETABLEteacher
（
--编号主键约束自动增长序列IDENTITY（起始值,递增量）
idINTPRIMARYKEYIDENTITY（1000,1）NOTNULL,
--姓名唯一约束
nameVARCHAR（100）UNIQUENOTNULL,
--性别检查约束,默认约束
sexNCHAR
（1）CHECK（sex='男'ORsex='女'）DEFAULT（'男'）,
--分数
scoreNUMERIC（7,2）
）
PRINT'teacher表已创建!
';
END
GO
--删除表
DROPTABLEteacher;
--学生表
IFEXISTS（SELECT*FROMsys.tablesWHEREname='student'）
BEGIN
PRINT'student表已删除!
';
DROPTABLEstudent;
END
ELSE
BEGIN
CREATETABLEstudent
（
idINTPRIMARYKEYIDENTITY（1000,1）NOTNULL,
nameVARCHAR（100）UNIQUENOTNULL,
sexCHAR
（2）CHECK（sexIN（'男','女'））DEFAULT（'男'）,
scoreNUMERIC（7,2）,
t_idINTFOREIGNKEYREFERENCESteacher（id）
）
PRINT'student表已创建!
';
END
GO
--第三方表
CREATETABLEt_s
（
idINTPRIMARYKEYIDENTITY（1000,1）NOTNULL,
t_idINTFOREIGNKEYREFERENCESteacher（id）,
s_idINTFOREIGNKEYREFERENCESstudent（id）
）
GO
DROPTABLEstudent;
DROPTABLEt_s;
--添加新列
ALTERTABLEstudentADDtINTNOTNULL;
--修改列类型
ALTERTABLEstudentALTERCOLUMNtCHAR;
--删除列
ALTERTABLEstudentDROPCOLUMNt;
--添加约束
ALTERTABLEstudentADDCONSTRAINTch_testCHECK（t<10）;
--删除约束
ALTERTABLEstudentDROPCONSTRAINTch_test;
--添加数据
INSERTINTOteacher（name,sex,score）VALUES（'独孤求败','男',99999）;
INSERTINTOteacher（name,score）VALUES（'东方不败',99999）;
INSERTINTOteacherVALUES（'张三疯',DEFAULT,77777）;
INSERTINTOteacherVALUES（'张老师',DEFAULT,1）;
--多行插入
INSERTINTOteacher（name,sex,score）
SELECT'黄裳','女',99999UNION
SELECT'达摩祖师','男',99999
--修改
UPDATEteacherSETname='东方不败',sex='女',score=88888WHEREname='洞房不败';
--删除
DELETEFROMteacherWHEREid=1005;
DELETEteacherWHEREid=1000;
INSERTINTOstudent（name,sex,score,t_id）
SELECT'令狐冲','男',44444,1005UNION
SELECT'风清扬','男',77777,1005UNION
SELECT'任我行','男',22222,1001UNION
SELECT'任盈盈','女',5555,1004UNION
SELECT'张无忌','男',33333,1003UNION
SELECT'郭靖','男',66666,1003UNION
SELECT'杨过','男',55555,1005UNION
SELECT'小龙女','女',8888,1004UNION
SELECT'乔峰','男',55555,1003UNION
SELECT'段誉','男',44444,1001UNION
SELECT'虚竹','男',77777,1004UNION
SELECT'xibing','男',5,null
--查询所有数据*代表所有列
SELECTid,name,sex,scoreFROMteacher;
SELECT*FROMstudent;
--条件查询
SELECT*FROMstudentWHEREsex='男'ANDscore>=10000;
--DISTINCT消除重复值包括所有列
SELECTDISTINCTname,sexFROMstudent;
--BETWEENAND范围取值
SELECT*FROMstudentWHEREnameBETWEEN'小龙女'AND'杨过';
--LIKE模糊查询
SELECT*FROMstudentWHEREnameLIKE'___';
SELECT*FROMstudentWHEREnameLIKE'%任%';
--查询Null空值
SELECT*FROMstudentWHEREt_idISNULL;
SELECT*FROMstudentWHEREt_idISNOTNULL;
--ORDERBY排序ASC（默认）升序,DESC降序
SELECT*FROMstudentORDERBYscoreDESC
SELECT*FROMstudentORDERBYscoreDESC,idASC;
SELECT*FROMstudentWHEREt_idISNOTNULLORDERBYnameDESC;
--TOP列出前几位
SELECTDISTINCTTOP10PERCENT*FROMstudentORDERBYscoreDESC;
--IN集合取值
SELECT*FROMstudentWHEREidIN（1000,1005,1010,1015）;
--Alias别名,多表查询一定要使用别名
SELECTs.idAS'编号',s.name'姓名',s.score战斗力FROMstudents
--GROUPBY分组查询,WHERE-->GROUPBY-->ORDERBY
--HAVING代表分组之后条件筛选
SELECTsex,score,COUNT（*）scFROMstudentsWHEREt_idISNOTNULLGROUPBYsex,scoreHAVINGCOUNT（sex）>=2ORDERBYscore;
--聚合函数
SELECTAVG（score）FROMstudentWHEREt_idISNOTNULL;
SELECTSUM（score）FROMstudent;
SELECTMAX（score）FROMstudent;
SELECTMIN（score）FROMstudent;
SELECTMAX（s.score）'最大值',MIN（s.score）'最小值'FROMstudents
SELECTCOUNT（*）FROMstudent;
--左外连接.以左表作为主表.显示主表中所有的记录,同时显示右表中相关联信息.如果没有则显示null
SELECTt.id,t.name,s.id,s.nameFROMteachertLEFTOUTERJOINstudentsONt.id=s.t_id;
--右连接
SELECT*FROMteachertRIGHTJOINstudentsONt.id=s.t_id;
--全连接
SELECT*FROMteachertFULLJOINstudentsONt.id=s.t_id;
--交叉连接
SELECT*FROMteachertCROSSJOINstudents;
/**
内连接INNERJOIN只会显示两边相关联信息
SELECT*FROMAINNERJOINBON这种形属于SQL1995新标准语法
SELECT*FROMA,BWHERE属于SQL1992旧标准
我们最好是使用新标准,因为每一个关键字都有它特定的作用.
而WHERE只负责筛选条件,ON才是建立关系.
新标准思路更清晰,旧标准更方便.
**/
SELECT*FROMteachertJOINstudentsONt.id=s.t_id;
SELECT*FROMteachert,studentsWHEREt.id=s.t_id;
/**
子查询
将子查询的结果作为上级查询的条件,使用方便
子查询效率比较低,不推荐使用.因为程序每次遇到SELECT就会全表检索一次
*/
SELECT*FROMteachertWHEREt.idIN（
SELECTt_idFROMstudents）;
SELECT*FROMteachert,（SELECT*FROMstudent）sWHEREt.id=s.t_id;
/**
EXISTS代替IN
EXISTS如果对方返回true可以显示对应的结果.否则不行执行
*/
SELECT*FROMteachertWHEREEXISTS
（SELECT*FROMstudentsWHEREt.id=s.t_id）
--联合查询.UNION会消除重复值.UNIONALL会显示所有值
SELECTt.sexFROMteachert
UNIONALL
SELECTs.sexFROMstudents;
CREATETABLEarea
（
--当前ID编号
idINTPRIMARYKEYNOTNULL,
--当前对象名称
nameVARCHAR（100）UNIQUENOTNULL,
--父对象编号
f_idINT
）
GO
DROPTABLEarea;
INSERTINTOarea
SELECT1,'中国',NULLUNION
SELECT10,'湖北省',1UNION
SELECT11,'湖南省',1UNION
SELECT12,'山东省',1UNION
SELECT100,'武汉市',10UNION
SELECT101,'孝感市',10UNION
SELECT102,'荆州市',10UNION
SELECT103,'长沙市',11UNION
SELECT104,'常德市',11UNION
SELECT105,'济南市',12UNION
SELECT106,'菏泽市',12
/**
自连接
*/
SELECTg.name'国家',s.name'省',m.name'市'FROMareag,areas,areamWHEREg.id=s.f_idANDs.id=m.f_id;
/**
分页
每页5条,共12条,分3页
TOP只有SQLServer,MySQL,DB2,Oracle中没有TOP关键字
那么问题来了,怎么分页?
**/
SELECTTOP5*FROMstudentWHEREidNOTIN
（SELECTTOP（（2-1）*5）idFROMstudent）;
--获取伪列
SELECTROW_NUMBER（）OVER（ORDERBYidASC）,*FROMstudent;
--伪列分页
SELECTt2.id,t2.name,t2.sex,t2.score,t2.t_idFROM
（SELECT*FROM
（SELECTROW_NUMBER（）OVER（ORDERBYidASC）r,*FROMstudent）t
WHEREt.r<=10）t2
WHEREt2.r>5;
SELECTt.id,t.name,t.sex,t.score,t.t_idFROM
（SELECTROW_NUMBER（）OVER（ORDERBYidASC）r,*FROMstudent）t
WHEREt.r<=10ANDt.r>5;
/**
用户只能定义局部变量,局部变量需要使用@符号作为前缀
全局变量是系统指定好的,用户不能自定义.一般是@@作为前缀
SET用户赋予常亮值,SELECT用于赋予变量查询值
PRINT打印数据,SELECT显示数据
*/
DECLARE@testCHAR（5）;
SET@test='ABC';
PRINT@test;
PRINTLEN（@test）;
DECLARE@nameVARCHAR（100）;
SELECT@name=（SELECTnameFROMstudentWHEREid=1000）;
PRINT'姓名为:
'+@name;
SELECT'姓名为:
'+@name;
--类型转换
DECLARE@iINT;
SET@i=1111;
PRINT'值为:
'+STR（@i）;
PRINT'值为:
'+CONVERT（VARCHAR,@i）;
--显示系统当前时间
SELECTGETDATE（）;
--常用系统常量
PRINT@@ERROR;
PRINT@@IDENTITY;
PRINT@@ROWCOUNT;
--IFELSE分支语句
DECLARE@iINT;
SET@i=2;
IF（@i=1）
BEGIN
PRINT'i=1';
END
ELSEIF（@i=2）
BEGIN
PRINT'i=2';
END
ELSE
BEGIN
PRINT'i=其他';
END
GO
--循环语句
DECLARE@iINT;
SET@i=1;
WHILE（@i<=10）
BEGIN
PRINT@i;
--条件递增
SET@i=@i+1;
END
GO
--CASE
SELECTid,name,sex,score=CASE
WHENscoreBETWEEN0AND10000THEN'0-10000'
WHENscoreBETWEEn10001AND20000THEN'10001-20000'
WHENscoreBETWEEN20001AND30000THEN'20001-30000'
ELSE'高于30000'
END
FROMstudent;
--判断学生的平均战斗力，不过不及45000则每个人都添加100.一直加到满足为止
SELECTAVG（score）FROMstudent;
DECLARE@avgNUMERIC（7,2）;
--赋予平均值
SELECT@avg=（SELECTAVG（score）FROMstudent）;
PRINT'所有学生平均战斗力为:
'+CONVERT（VARCHAR,@avg）;
WHILE（@avg<45000）
BEGIN
--循环加
UPDATEstudentSETscore=score+100;
PRINT'所有人战斗力上升100!
'
--再次获取平均值
SET@avg=@avg+100;
END
--显示最后的结果
SELECT*,score=CASE
WHENscoreBETWEEN0AND5000THEN'孩子,你需要努力!
'
WHENscoreBETWEEN5001AND10000THEN'有进步!
'
WHENscoreBETWEEN10001AND30000THEN'带节奏!
'
WHENscoreBETWEEN30001AND80000THEN'Carry!
'
ELSE'何方神圣!
'
END
FROMstudent;
/**
事务
*/
--开启事务
BEGINTRANSACTION;
DELETEFROMstudent;
--真实修改数据
--COMMITTRANSACTION;
--回滚事务
ROLLBACKTRANSACTION;
--事务转账
DECLARE@aINT,@bINT,@moneyNUMERIC（7,2）,@errorINT;
SET@a=1010;
SET@b=1008;
--转账金额
SET@money=100;
--初始化错误编号
SET@error=0;
--开启事务
BEGINTRANSACTION
--查看该用户余额是否足够
IF（（SELECTscoreFROMstudentWHEREid=@a）>=@money）
BEGIN
PRINT'-----------转账前-----------';
SELECT*FROMstudent;
PRINT'-----------开始转账-----------';
UPDATEstudentSETscore=score-@moneyWHEREid=@a;
--累计错误编码
SET@error=@error+@@ERROR;
UPDATEstudentSETscore=score+@moneyWHEREid=@b;
SET@error=@error+@@ERROR;
--判断中途是否错误
IF（@error!
=0）
BEGIN
--中途错误
PRINT'-----------转账失败-----------';
--回滚事务
ROLLBACKTRANSACTION;
END
ELSE
BEGIN
PRINT'-----------转账成功-----------';
SELECT*FROMstudent;
END
END
ELSE
BEGIN
--余额不足
PRINT'余额不足,转账失败!
';
ROLLBACKTRANSACTION;
END
GO
/**
索引提高检索速度.但是使用不当反而会降低速度
一个表中只能有一个聚集索引,而主键就是聚集索引.唯一约束是非聚集唯一索引
所以聚集索引我们可以不用管
当某些列频繁操作的时候,或者数据量比较大的时候可以建立索引.
升级会员 