2022年经典SQL语句大全.docx

精选学习资料 - - - - - - - - - 一、基础1、说明：创建数据库 CREATE DATABASE database-name 2、说明：删除数据库 drop database dbname 3、说明：备份 sql server - 创建 备份数据的 device USE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backupMyNwind_1.dat' - 开头 备份 BACKUP DATABASE pubs TO testBack 4、说明：创建新表 create table tabnamecol1 type1 not null primary key,col2 type2 not null,.5、说明：删除新表 drop table tabname 6、说明：增加一个列 Alter table tabname add column col type 注：列增加后将不能删除； DB2中列加上后数据类型也不能转变,唯独能转变的 是增加 varchar 类型的长度；7、说明：添加主键 ： Alter table tabname add primary keycol 说明：删除主键 ： Alter table tabname drop primary keycol 8、说明：创建索引 ：create unique index idxname on tabnamecol . 删除索引 ：drop index idxname 注：索引是不行更换的,想更换必需删除重新建；9、说明：创建视图 ：create view viewname as select statement 删除视图 ：drop view viewname sql 语句 10、说明：几个简洁的基本的 挑选： select * from table1 where 范畴插入： insert into table1field1,field2 valuesvalue1,value2 删除： delete from table1 where 范畴范畴-like的语更新 ：update table1 set field1=value1 where 查找 ：select * from table1 where field1 like %value1%法很精妙,查资料 . 排序 ：select * from table1 order by field1,field2 desc 总数 ：select count as totalcount from table1 求和 ：select sumfield1 as sumvalue from table1 平均 ：select avgfield1 as avgvalue from table1 最大 ：select maxfield1 as maxvalue from table1 最小 ：select minfield1 as minvalue from table1 11、说明：几个高级查询运算词1 名师归纳总结 - - - - - - -第 1 页,共 18 页精选学习资料 - - - - - - - - - A： UNION 运算符UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表；当 ALL 随 UNION 一起使用时（即 UNION AL L）,不排除重复行；两种情形下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2；B： EXCEPT 运算符 EXCEPT运算符通过包括全部在 TABLE1 中但不在 TABLE2 中的行并排除全部重 复行而派生出一个结果表； 当 ALL 随 EXCEPT 一起使用时 EXCEPT ALL,不消 除重复行；C： INTERSECT 运算符 INTERSECT运算符通过只包括 TABLE1 和 TABLE2 中都有的行并排除全部重复行而派生出一个结果表；当 不排除重复行；ALL 随 INTERSECT 一起使用时 INTERSECT ALL,注： 使用运算词的几个查询结果行必需是一样的；12、说明：使用外连接A、left （outer ） join：左外连接（左连接） ：结果集几包括连接表的匹配行,也包括左连接表的全部行；SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c B：right （outer ） join: 右外连接 右连接 ：结果集既包括连接表的匹配连接行,行；C：full/cross （outer ） join：也包括右连接表的全部全外连接：不仅包括符号连接表的匹配行,仍包括两个连接表中的全部记录；12、分组 :Group by: 一张表,一旦分组完成后,查询后只能得到组相关的信息；组相关的信息： （统计信息） count,sum,max,min,avg 分组的标准 据在 SQLServer 中分组时：不能以 text , ntext , image 类型的字段作为分组依在 selecte 统计函数 中的字段,不能和 一般 的字段放在一起；13、对数据库进行操作：分别数据库 ： sp_detach_db; 需要完整的路径名 14. 如何修改数据库的名称 :附加数据库 ：sp_attach_db 后接说明,附加sp_renamedb 'old_name', 'new_name' 二、提升1、说明：复制表 只复制结构 , 源表名： a 新表名： b Access 可用 法一： select * into b from a where 1<>1（仅用于 SQlServer ）名师归纳总结 - - - - - - -第 2 页,共 18 页精选学习资料 - - - - - - - - - 法二： select top 0 * into b from a 2、说明：拷贝表 拷贝数据 , 源表名： a 目标表名： b Access 可用 insert into ba, b, c select d,e,f from b; 3、说明：跨数据库之间表的拷贝 详细数据使用肯定路径 Access 可用 insert in to ba, b, c select d,e,f from b in 详细数据库 where 条件例子： .from b in '"&Server.MapPath"."&"data.mdb" &"' where. 4、说明：子查询 表名 1：a 表名 2：b select a,b,c from a where a IN select d from b from a where a IN 1,2,3 5、说明：显示文章、提交人和最终回复时间或者 : select a,b,c select a.title,a.username,b.adddate from table a,select maxadddate adddate from table where table.title=a.title b 6、说明：外连接查询 表名 1：a 表名 2：b select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c 7、说明：在线视图查询 表名 1：a select * from SELECT a,b,c FROM a T where t.a > 1; 8、说明： between 的用法 ,between 限制查询数据范畴时包括了边界值 ,not bet ween不包括 select * from table1 where time between time1 and time2 select a,b,c, from table1 where a not between 9、说明： in 的使用方法数值 1 and 数值 2 select * from table1 where a not in 值 1 , 值 2 , 值 4 , 值 6 10、说明：两张关联表,删除主表中已经在副表中没有的信息 delete from table1 where not exists select * from table2 where tabl e1.field1=table2.field1 11、说明：四表联查问题：select * from a left inner join b on a.a=b.b right inner join c on a.开头时间 ,getdata=c.c inner join d on a.a=d.d where . 12、说明：日程支配提前五分钟提示SQL: select * from 日程支配 where datediff'minute',fe>5 3 名师归纳总结 - - - - - - -第 3 页,共 18 页精选学习资料 - - - - - - - - - 13、说明：一条 sql 语句搞定数据库分页select top 10 b.* from select top 20 主键字段 ,排序字段 from 表名 order by 排序字段 desc a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段详细实现：关于数据库分页： declare start int,end int sql nvarchar600 set sql=select top+str end-start+1+ +from T where rid not inselect top+str str-1+ Rid from T where Rid>-1 exec sp_executesql sql 留意：在 top 后不能直接跟一个变量,所以在实际应用中只有这样的进行特别的处理； Rid 为一个标识列,假如top 后仍有详细的字段,这样做是特别有好处的；由于这样可以防止 top 的字段假如是规律索引的,查询的结果后实际表中的不一样（ 规律索引中的数据有可能和数据表中的不一样,而查询时假如处在索引就第一查询索引 ）14、说明：前 10 条记录select top 10 * form table1 where 范畴15、说明：挑选在每一组 b 值相同的数据中对应的 a 最大的记录的全部信息 类似这样的用法可以用于论坛每月排行榜 等等 . , 每月热销产品分析 , 按科目成果排名 ,select a,b,c from tablename ta where a=select maxa from tablename tb where tb.b=ta.b 16、说明：包括全部在TableA 中但不在TableB 和 TableC 中的行并排除全部重复行而派生出一个结果表 select a from tableA except select a from tableB except select a from tableC 17、说明：随机取出10 条数据newidselect top 10 * from tablename order by 18、说明：随机挑选记录 select newid 19、说明：删除重复记录 1, delete from tablename where id not in select maxid from tablena me group by col1,col2,. 2 ,select distinct * into temp from tablename delete from tablename insert into tablename select * from temp 评判： 这种操作牵连大量的数据的移动,这种做法不适合大容量但数据操作 3, 例如：在一个外部表中导入数据,由于某些缘由第一次只导入了一部分,但4 名师归纳总结 - - - - - - -第 4 页,共 18 页精选学习资料 - - - - - - - - - 很难判定详细位置,这样只有在下一次全部导入,这样也就产生好多重复的字段,怎样删除重复字段alter table tablename - 添加一个自增列add column_b int identity1,1 delete from tablename where column_b not in select maxcolumn_b from tablename group by column1,column2,. alter table tablename drop column column_b20、说明：列出数据库里全部的表名select name from sysobjects where type='U' / U 代表用户21、说明：列出表里的全部的列名select name from syscolumns where id=object_id'TableName'22、说明：列示 type 、vender 、pcs 字段,以 type 字段排列, case 可以便利地实现多重挑选,类似select 中的 case；select type,sumcase vender when 'A' then pcs else 0 end,sumcase vender when 'C' then pcs else 0 end,sumcase vender when 'B' then pcs else 0 end FROM tablename group by type 显示结果：type vender pcs 电脑 A 1 电脑 A 1 光盘 B 2 光盘 A 2 手机 B 3 手机 C 3 23、说明：初始化表 table1 TRUNCATE TABLE table1 24、说明：挑选从 10 到 15 的记录select top 5 * from select top 15 * from table order by id asc table_别名 order by id desc 三、技巧1、1=1,1=2 的使用,在 SQL语句组合时用的较多“ where 1=1”是表示挑选全部 如：“ where 1=2” 全部不选,名师归纳总结 - - - - - - -第 5 页,共 18 页精选学习资料 - - - - - - - - - if strWhere .='' begin set strSQL = 'select count* as Total from ' + tblName + ' where ' + strWhere end else begin set strSQL = 'select count* as Total from ' + tblName + '' end 我们可以直接写成错误！未找到目录项；set strSQL = 'select count* as Total from ' + tblName + ' where 1=1 安定'+ strWhere 2、收缩数据库-重建索引 DBCC DBREINDEX "HumanResources.Employee", PK_Employee_EmployeeID,80;DBCC INDEXDEFRAG -收缩数据和日志DBCC SHRINKDB DBCC SHRINKFILE3、压缩数据库 dbcc shrinkdatabasedbname 4、转移数据库给新用户以已存在用户权限 exec sp_change_users_login 'update_one','newname','oldname' go 5、检查备份集 RESTORE VERIFYONLY from disk='E:dvbbs.bak' 6、修复数据库 ALTER DATABASE dvbbs SET SINGLE_USER GO DBCC CHECKDB'dvbbs',repair_allow_data_loss WITH TABLOCK GO ALTER DATABASE dvbbs SET MULTI_USER GO 7、日志清除 SET NOCOUNT ON DECLARE LogicalFileName sysname, MaxMinutes INT, NewSize INT 6 名师归纳总结 - - - - - - -第 6 页,共 18 页精选学习资料 - - - - - - - - - USE tablename - 要操作的数据库名 SELECT LogicalFileName = 'tablename_log', - 日志文件名 MaxMinutes = 10, - Limit on time allowed to wrap log. NewSize = 1 - 你想设定的日志文件的大小M Setup / initialize DECLARE OriginalSize int SELECT OriginalSize = size FROM sysfiles WHERE name = LogicalFileName SELECT 'Original Size of ' + db_name + ' LOG is ' + CONVERTVARCHAR30,OriginalSize + ' 8K pages or ' + CONVERTVARCHAR30,OriginalSize*8/1024 + 'MB' FROM sysfiles WHERE name = LogicalFileName CREATE TABLE DummyTrans DummyColumn char 8000 not null DECLARE Counter INT, StartTime DATETIME, TruncLog VARCHAR255 SELECT StartTime = GETDATE, TruncLog = 'BACKUP LOG ' + db_name + ' WITH TRUNCATE_ONLY' DBCC SHRINKFILE LogicalFileName, NewSize EXEC TruncLog - Wrap the log if necessary. WHILE MaxMinutes > DATEDIFF mi, StartTime, GETDATE - time has not expired AND OriginalSize = SELECT size FROM sysfiles WHERE name = Logical FileName AND OriginalSize * 8 /1024 > NewSize BEGIN - Outer loop. SELECT Counter = 0 WHILE Counter < OriginalSize / 16 AND Counter < 50000 BEGIN - update INSERT DummyTrans VALUES 'Fill Log' DELETE DummyTrans SELECT Counter = Counter + 1 END EXEC TruncLog END SELECT 'Final Size of ' + db_name + ' LOG is ' + CONVERTVARCHAR30,size + ' 8K pages or ' + 7 名师归纳总结 - - - - - - -第 7 页,共 18 页精选学习资料 - - - - - - - - - CONVERTVARCHAR30,size*8/1024 + 'MB' FROM sysfiles WHERE name = LogicalFileName DROP TABLE DummyTrans SET NOCOUNT OFF 8、说明：更换某个表 exec sp_changeobjectowner 'tablename','dbo' 9、储备更换全部表CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch OldOwner as NVARCHAR128, NewOwner as NVARCHAR128 AS DECLARE Name as NVARCHAR128 DECLARE Owner as NVARCHAR128 DECLARE OwnerName as NVARCHAR128 DECLARE curObject CURSOR FOR select 'Name' = name, 'Owner' = user_nameuid from sysobjects where user_nameuid=OldOwner order by name OPEN curObject FETCH NEXT FROM curObject INTO Name, Owner WHILEFETCH_STATUS=0 BEGIN if Owner=OldOwner begin set OwnerName = OldOwner + '.' + rtrimName exec sp_changeobjectowner OwnerName, NewOwner end - select name,NewOwner,OldOwner FETCH NEXT FROM curObject INTO Name, Owner END close curObject deallocate curObject GO8 名师归纳总结 - - - - - - -第 8 页,共 18 页精选学习资料 - - - - - - - - - 10、 SQL SERVER中直接循环写入数据declare i int set i=1 while i<30 begin insert into test userid valuesi set i=i+1 end 案例：有如下表,要求就裱中全部沒有及格的成績,在每次增長0.1 的基礎上,使他們剛好及格:Name score Zhangshan8060 Lishi 59Wangwu 50Songquan69whileselect minscore from tb_table<begin update tb_table set score =score* 1.01 where score <60 if select minscore from tb_table >60 break else continue end 数据开发 -经典1. 按姓氏笔画排序 : Select * From TableName Order By CustomerName Collate Chinese_PRC_Str oke_ci_as / 从少到多2. 数据库加密 : select encrypt' 原始密码 ' 9 名师归纳总结 - - - - - - -第 9 页,共 18 页精选学习资料 - - - - - - - - - select pwdencrypt' 原始密码 ' select pwdcompare'原始密码 ','加密后密码 ' = 1-相同；否就不相同 encrypt'原始密码 ' 原始密码 ' 加密后密码 ' = 1-相同；否就不相同select pwdencrypt'select pwdcompare'原始密码 ','3. 取回表中字段 : declare list varchar1000, sql nvarchar1000 select list=list+ id=b.id and a.name=set sql= 'select '','+b.name from sysobjects a,syscolumns b where a.' 表 A' +rightlist,lenlist-1+' from 表 A' exec sql 4. 查看硬盘分区 : EXEC master.xp_fixeddrives 5. 比较 A,B 表是否相等 : if select checksum_aggbinary_checksum* from A = select checksum_aggbinary_checksum* from B print ' 相等 ' else print ' 不相等 ' 6. 杀掉全部的大事探察器进程 : DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIMspid FROM m aster.dbo.sysprocesses WHERE program_name IN'SQL profiler',N'SQL 大事探查器 ' EXEC sp_msforeach_worker '.'7.记录搜寻 : 开头到 N 条记录 Select Top N * From 表- N 到 M 条记录 要有主索引 ID Select Top M-N * From 表 Where ID in Select Top M ID From 表 Order by ID Desc - N 到结尾记录 Select Top N * From 表 Order by ID Desc 案例例如 1：一张表有一万多条记录,表的第一个字段 RecID 是自增长字段,写一个 SQL语句,找出表的第31 到第 40 个记录； select top 10 recid from A where recid not inselect top 30 recid from A 10 名师归纳总结 - - - - - - -第 10 页,共 18 页精选学习资料 - - - - - - - - - 分析：假如这样写会产生某些问题,假如recid在表中存在规律索引；select top 10 recid from A where 是从索引中查找,而后面的 select top 30 recid from A 就在数据表中查找,这样由于索引中的次序有可能和数据表中的不一样,这样就导致查询到的不是原来的欲得到的数据；解决方案1, 用 order by select top 30 recid from A order by ricid 假如该字段不是自增长,就会显现问题2, 在那个子查询中也加条件：select top 30 recid from A where recid>-1例 2：查询表中的最终以条记录,并不知道这个表共有多少数据,以及表结构；from set s = 'select top 1 * from T where pid not in select top ' + str count-1 + ' pid T' print sexec sp_executesqls9：猎取当前数据库中的全部用户表select Name from sysobjects where xtype='u' and status>=0 10：猎取某一个表的全部字段select name from syscolumns where id= object_id ' 表名' select name from syscolumns where id in select id from sysobjects where type = 'u' and name = ' 表名 ' 两种方式的成效相同11：查看与某一个表相关的视图、储备过程、函数select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '% 表名%' 12：查看当前数据库中全部储备过程select name as 储备过程名称 from sysobjects where xtype='P' 13：查询用户创建的全部数据库select * from master.sysdatabases D where sid not inselect sid from master. 或者syslogins where name='sa' select dbid, name AS DB_NAME from master.s x01 14：查询某一个表的字段和数据类型ysdatabases where sid <> 0select column_name,data_type from information_schema.columns where table_name = ' 表名 ' 15：不同服务器数据库之间的数据操作- 创建链接服务器11 名师归纳总结 - - - - - - -第 11 页,共 18 页精选学习资料 - - - - - - - - - exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip 地址 ' exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用户名 ', '密码 ' - 查询示例 select * from ITSV. 数据库名 .dbo. 表名- 导入示例select * into 表 from ITSV.数据库名 .dbo. 表名- 以后不再使用时删除链接服务器 exec sp_dropserver 'ITSV ', 'droplogins ' - 连接远程 / 局域网数据 openrowset/openquery/opendatasource -1 、openrowset - 查询示例select * from openrowset 'SQLOLEDB ', 'sql服务器名 ' '用户名 ' '密码 ',数据库名 .dbo. 表名 - 生成本地表select * into 表 from openrowset 'SQLOLEDB ', 'sql服务器名 ' '用户名 ' '密码 ',数据库名 .dbo. 表名 - 把本地表导入远程表insert openrowset 'SQLOLEDB ', 'sql服务器名 ' '用户名 ' '密码 ',数据库名 .dbo. 表名 select *from 本地表- 更新本地表 update b set b. 列 A=a.列 A 12 名师归纳总结 - - - - - - -第 12 页,共 18 页精选学习资料 - - - - - - - - - from openrowset