1、SQL 语 句 大 全一 、 基 础1、 说 明 ： 创 建 数 据 库创 建 之 前 判 断 该 数 据 库 是 否 存 在if exists (select * from sys.databases where name=databaseName) drop database databaseNamegoCreate DATABASE database-name2、 说 明 ： 删 除 数 据 库drop database dbname3、 说 明 ： 备 份 sql server- 创 建 备 份 数 据 的 deviceUSE masterEXEC sp_addumpdevice di
2、sk, testBack, c:mssql7backupMyNwind_1.dat- 开 始 备 份BACKUP DATABASE pubs TO testBack4、 说 明 ： 创 建 新 表create table tabname(col1 type1 not null primary key,col2 type2 not null,.)根 据 已 有 的 表 创 建 新 表 ：A： create table tab_new like tab_old (使 用 旧 表 创 建 新 表 )B： create table tab_new as select col1,col2 from ta
3、b_old definition only5、 说 明 ： 删 除 新 表drop table tabname6、 说 明 ： 增 加 一 个 列Alter table tabname add column col type注 ： 列 增 加 后 不 能 删 除 。 DB2 中 列 加 上 后 数 据 类 型 也 不 能 改 变 ， 唯 一 能 改 变 的 是 增 加 varchar 类 型 的 长度 。7、 说 明 ： 添 加 主 键 ： Alter table tabname add primary key(col)说 明 ： 删 除 主 键 ： Alter table tabname d
4、rop primary key(col)8、 说 明 ： 创 建 索 引 ： create unique index idxname on tabname(col.)删 除 索 引 ： drop index idxname on tabname注 ： 索 引 是 不 可 更 改 的 ， 想 更 改 必 须 删 除 重 新 建 。9、 说 明 ： 创 建 视 图 ： create view viewname as select statement删 除 视 图 ： drop view viewname10、 说 明 ： 几 个 简 单 的 基 本 的 sql 语 句选 择 ： select *
5、from table1 where 范 围插 入 ： insert into table1(field1,field2) values(value1,value2)删 除 ： delete from table1 where 范 围更 新 ： update table1 set field1=value1 where 范 围查 找 ： select * from table1 where field1 like %value1% （ 所 有 包 含 value1这 个 模 式 的 字 符 串 ）排 序 ： select * from table1 order by field1,field2
6、desc总 数 ： select count as totalcount from table1求 和 ： select sum(field1) as sumvalue from table1平 均 ： select avg(field1) as avgvalue from table1最 大 ： select max(field1) as maxvalue from table1最 小 ： select min(field1) as minvalue from table1separator11、 说 明 ： 几 个 高 级 查 询 运 算 词A： UNION 运 算 符UNION 运 算
7、符 通 过 组 合 其 他 两 个 结 果 表 （ 例 如 TABLE1 和 TABLE2） 并 消 去 表 中 任 何 重 复 行 而 派 生 出一 个 结 果 表 。 当 ALL 随 UNION 一 起 使 用 时 （ 即 UNION ALL） ， 不 消 除 重 复 行 。 两 种 情 况 下 ， 派 生 表 的 每一 行 不 是 来 自 TABLE1 就 是 来 自 TABLE2。B： EXCEPT 运 算 符EXCEPT 运 算 符 通 过 包 括 所 有 在 TABLE1 中 但 不 在 TABLE2 中 的 行 并 消 除 所 有 重 复 行 而 派 生 出 一 个 结果 表 。
8、 当 ALL 随 EXCEPT 一 起 使 用 时 (EXCEPT ALL)， 不 消 除 重 复 行 。C： INTERSECT 运 算 符INTERSECT 运 算 符 通 过 只 包 括 TABLE1 和 TABLE2 中 都 有 的 行 并 消 除 所 有 重 复 行 而 派 生 出 一 个 结 果表 。 当 ALL 随 INTERSECT 一 起 使 用 时 (INTERSECT ALL)， 不 消 除 重 复 行 。注 ： 使 用 运 算 词 的 几 个 查 询 结 果 行 必 须 是 一 致 的 。12、 说 明 ： 使 用 外 连 接A、 left outer join：左 外
9、 连 接 （ 左 连 接 ） ： 结 果 集 既 包 括 连 接 表 的 匹 配 行 ， 也 包 括 左 连 接 表 的 所 有 行 。SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.cB： right outer join:右 外 连 接 (右 连 接 )： 结 果 集 既 包 括 连 接 表 的 匹 配 连 接 行 ， 也 包 括 右 连 接 表 的 所 有 行 。C： full outer join：全 外 连 接 ： 不 仅 包 括 符 号 连 接 表 的 匹 配 行 ， 还 包 括
10、两 个 连 接 表 中 的 所 有 记 录 。 二 、 提 升1、 说 明 ： 复 制 表 (只 复 制 结 构 ,源 表 名 ： a 新 表 名 ： b) (Access 可 用 )法 一 ： select * into b from a where 1513、 说 明 ： 一 条 sql 语 句 搞 定 数 据 库 分 页select top 10 b.* from (select top 20 主 键 字 段 ,排 序 字 段 from 表 名 order by 排 序 字 段 desc) a,表 名 b where b.主 键 字 段 = a.主 键 字 段 order by a.排
11、序 字 段14、 说 明 ： 前 10 条 记 录select top 10 * form table1 where 范 围15、 说 明 ： 选 择 在 每 一 组 b 值 相 同 的 数 据 中 对 应 的 a 最 大 的 记 录 的 所 有 信 息 (类 似 这 样 的 用 法 可 以 用 于 论坛 每 月 排 行 榜 ,每 月 热 销 产 品 分 析 ,按 科 目 成 绩 排 名 ,等 等 .)select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)16、 说 明 ：
12、 包 括 所 有 在 TableA 中 但 不 在 TableB 和 TableC 中 的 行 并 消 除 所 有 重 复 行 而 派 生 出 一 个 结 果 表(select a from tableA except (select a from tableB) except (select a from tableC)17、 说 明 ： 随 机 取 出 10 条 数 据select top 10 * from tablename order by newid()18、 说 明 ： 随 机 选 择 记 录select newid()19、 说 明 ： 删 除 重 复 记 录Delete fr
13、om tablename where id not in (select max(id) from tablename group by col1,col2,.)20、 说 明 ： 列 出 数 据 库 里 所 有 的 表 名select name from sysobjects where type=U21、 说 明 ： 列 出 表 里 的 所 有 的select name from syscolumns where id=object_id(TableName)22、 说 明 ： 列 示 type、 vender、 pcs 字 段 ， 以 type 字 段 排 列 ， case 可 以 方
14、便 地 实 现 多 重 选 择 ， 类 似 select 中 的 case。select type,sum(case vender when A then pcs else 0 end),sum(case vender when C then pcs else 0 end),sum(case 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 323、 说 明 ： 初 始 化 表 table1
15、TRUNCATE TABLE table124、 说 明 ： 选 择 从 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”全 部 不 选 ，如 ：if strWhere !=beginset strSQL = select count(*) as Total fr
16、om + tblName + where + strWhereendelsebeginset strSQL = select count(*) as Total from + tblName + end我 们 可 以 直 接 写 成set strSQL = select count(*) as Total from + tblName + where 1=1 安 定 + strWhere2、 收 缩 数 据 库-重 建 索 引DBCC REINDEXDBCC INDEXDEFRAG-收 缩 数 据 和 日 志DBCC SHRINKDBDBCC SHRINKFILE3、 压 缩 数 据 库dbc
17、c shrinkdatabase(dbname)4、 转 移 数 据 库 给 新 用 户 以 已 存 在 用 户 权 限exec sp_change_users_login update_one,newname,oldnamego5、 检 查 备 份 集RESTORE VERIFYONLY from disk=E:dvbbs.bak6、 修 复 数 据 库Alter DATABASE dvbbs SET SINGLE_USERGODBCC CHECKDB(dvbbs,repair_allow_data_loss) WITH TABLOCKGOAlter DATABASE dvbbs SET M
18、ULTI_USERGO7、 日 志 清 除SET NOCOUNT ONDECLARE LogicalFileName sysname,MaxMinutes INT,NewSize INTUSE tablename - 要 操 作 的 数 据 库 名Select LogicalFileName = tablename_log, - 日 志 文 件 名MaxMinutes = 10, - Limit on time allowed to wrap log.NewSize = 1 - 你 想 设 定 的 日 志 文 件 的 大 小 (M)- Setup / initializeDECLARE Ori
19、ginalSize intSelect OriginalSize = sizeFROM sysfilesWhere name = LogicalFileNameSelect Original Size of + db_name() + LOG is +CONVERT(VARCHAR(30),OriginalSize) + 8K pages or +CONVERT(VARCHAR(30),(OriginalSize*8/1024) + MBFROM sysfilesWhere name = LogicalFileNameCreate TABLE DummyTrans(DummyColumn ch
20、ar (8000) not null)DECLARE Counter INT,StartTime DATETIME,TruncLog VARCHAR(255)Select StartTime = GETDATE(),TruncLog = BACKUP LOG + db_name() + WITH TRUNCATE_ONLYDBCC SHRINKFILE (LogicalFileName, NewSize)EXEC (TruncLog)- Wrap the log if necessary.WHILE MaxMinutes DATEDIFF (mi, StartTime, GETDATE() -
21、 time has not expiredAND OriginalSize = (Select size FROM sysfiles Where name = LogicalFileName)AND (OriginalSize * 8 /1024) NewSizeBEGIN - Outer loop.Select Counter = 0WHILE (Counter OriginalSize / 16) AND (Counter 50000)BEGIN - updateInsert DummyTrans VALUES (Fill Log)Delete DummyTransSelect Count
22、er = Counter + 1ENDEXEC (TruncLog)ENDSelect Final Size of + db_name() + LOG is +CONVERT(VARCHAR(30),size) + 8K pages or +CONVERT(VARCHAR(30),(size*8/1024) + MBFROM sysfilesWhere name = LogicalFileNameDrop TABLE DummyTransSET NOCOUNT OFF8、 说 明 ： 更 改 某 个 表exec sp_changeobjectowner tablename,dbo9、 存 储
23、更 改 全 部 表Create PROCEDURE dbo.User_ChangeObjectOwnerBatchOldOwner as NVARCHAR(128),NewOwner as NVARCHAR(128)ASDECLARE Name as NVARCHAR(128)DECLARE Owner as NVARCHAR(128)DECLARE OwnerName as NVARCHAR(128)DECLARE curObject CURSOR FORselect Name = name,Owner = user_name(uid)from sysobjectswhere user_na
24、me(uid)=OldOwnerorder by nameOPEN curObjectFETCH NEXT FROM curObject INTO Name, OwnerWHILE(FETCH_STATUS=0)BEGINif Owner=OldOwnerbeginset OwnerName = OldOwner + . + rtrim(Name)exec sp_changeobjectowner OwnerName, NewOwnerend- select name,NewOwner,OldOwnerFETCH NEXT FROM curObject INTO Name, OwnerENDclose curObjectdeallocate curObjectGO10、 SQL SERVER 中 直 接 循 环 写 入 数 据declare i intset i=1while i30begininsert into test (userid) values(i)set i=i+1end
