| SQL Server语句 | ||
| 序号 | 功能 | 语句 |
| 1 | 创建数据库(创建之前判断该数据库是否存在) |
if exists (select * from sysdatabases where name=‘databaseName‘) drop database databaseName go Create DATABASE databasename |
| 2 | 删除数据库 | drop database databasename |
| 3 | 备份数据库 |
USE master EXEC sp_addumpdevice ‘disk‘, ‘testBack‘, ‘c:\mssql7backup\MyNwind_1.dat‘ BACKUP DATABASE pubs TO testBack |
| 4 | 创建新表 | create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) |
| 5 | 根据已有表创建新表 |
1、use 原数据库名 go select * into 目的数据库名.dbo.目的表名 from 原表名(使用旧表创建新表) 2、create table tab_new as select col1,col2… from tab_old definition only |
| 6 | 创建序列 |
create sequence SIMON_SEQUENCE minvalue 1 -- 最小值 maxvalue 999999999999999999999999999 -- 最大值 start with 1 -- 开始值 increment by 1 -- 每次加几 cache 20; |
| 7 | 删除新表 | drop table tabname |
| 8 | 增加一个列 |
Alter table tabname add colname coltype alter table tablename add column_b int identity(1,1) |
| 9 | 删除一个列 | Alter table tabname drop column colname |
| 10 | 修改一个列 |
ALTER TABLE 表名 ALTER COLUMN 字段名 varchar(30) NOT NULL DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。 |
| 11 | 添加主键 | Alter table tabname add primary key(col) |
| 12 | 删除主键 | Alter table tabname drop primary key(col) |
| 13 | 创建索引 | create [unique] index idxname on tabname(col…。) |
| 14 | 删除索引 | drop index idxname on tabname |
| 15 | 创建视图 | create view viewname as select statement |
| 16 | 删除视图 | drop view viewname |
| 17 | 选择数据记录 |
sql="select * from 数据表 where 字段名=字段值 order by 字段名 [desc]" sql="select * from 数据表 where 字段名 like ‘%字段值%‘ order by 字段名 [desc]" sql="select top 10 * from 数据表 where 字段名=字段值 order by 字段名 [desc]" sql="select top 10 * from 数据表 order by 字段名 [desc]" sql="select * from 数据表 where 字段名 in (‘值1‘,‘值2‘,‘值3‘)" sql="select * from 数据表 where 字段名 between 值1 and 值2" 注：like中"%"匹配0个或多个字符；like中"_"匹配一个字符 |
| 18 | 更新数据记录 |
sql="update 数据表 set 字段名=字段值 where 条件表达式" sql="update 数据表 set 字段1=值1,字段2=值2 …… 字段n=值n where 条件表达式" |
| 19 | 删除数据记录 |
sql="delete from 数据表 where 条件表达式" sql="delete from 数据表" (将数据表所有记录删除) |
| 20 | 添加数据记录 |
sql="insert into 数据表 (字段1,字段2,字段3 …) values (值1,值2,值3 …)" sql="insert into 目标数据表 select * from 源数据表" (把源数据表的记录添加到目标数据表) |
| 21 | 数据记录统计函数 |
AVG(字段名) 得出一个表格栏平均值 COUNT(*;字段名) 对数据行数的统计或对某一栏有值的数据行数统计 MAX(字段名) 取得一个表格栏最大的值 MIN(字段名) 取得一个表格栏最小的值 SUM(字段名) 把数据栏的值相加 引用以上函数的方法： sql="select sum(字段名) as 别名 from 数据表 where 条件表达式" set rs=conn.excute(sql) 用 rs("别名") 获取统计的值，其它函数运用同上。 |
| 22 | 查询去除重复值 | select distinct * from table1 |
| 23 | 查询数据库中含有同一这字段的表 |
select name from sysobjects where xtype = ‘u‘ and id in(select id from syscolumns where name = ‘s3‘) |
| 24 | 只复制表结构 |
select * into a from b where 1<>1 select top 0 * into b from a |
| 25 | 复制内容 |
set identity_insert aa ON insert into aa(Customer_ID, ID_Type, ID_Number) select Customer_ID, ID_Type, ID_Number from TCustomer; set identity_insert aa OFF |
| 26 | UNION 运算符(使用运算词的几个查询结果行必须是一致的) | UNION 运算符通过组合其他两个结果表（例如TABLE1 和TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随UNION 一起使用时（即UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自TABLE1 就是来自TABLE2。 |
| 27 | EXCEPT 运算符 | EXCEPT 运算符通过包括所有在TABLE1 中但不在TABLE2 中的行并消除所有重复行而派生出一个结果表。当ALL 随EXCEPT 一起使用时(EXCEPT ALL)，不消除重复行。 |
| 28 | INTERSECT 运算符 | INTERSECT 运算符通过只包括TABLE1 和TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当ALL 随INTERSECT 一起使用时(INTERSECT ALL)，不消除重复行。 |
| 29 | left (outer) join | 左外连接（左连接）：结果集既包括连接表的匹配行，也包括左连接表的所有行。 |
| 30 | right (outer) join | 右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。 |
| 31 | full/cross (outer) join | 全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。 |
| 32 | 判断临时表是否存在 |
if object_id(‘tempdb..#临时表名‘) isnot null drop table#临时表名 |
| 33 | 判断列是否自增列 |
if columnproperty(object_id(‘table‘),‘col‘,‘IsIdentity‘)=1 print ‘自增列‘ else print ‘不是自增列‘ SELECT* FROM sys.columns WHERE object_id=OBJECT_ID(‘表名‘) AND is_identity=1 |
| 34 | 判断表中是否存在索引 |
if exists(select * from sysindexes whereid=object_id(‘表名‘) and name=‘索引名‘) print ‘存在‘ else print ‘不存在 |
| 35 | between |
between为查询某字段的指定范围，限制查询数据范围时包括了边界值 not between不包括边界值 |
| 36 | 删除主表没有的信息 |
两张关联表delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1) |
| 37 | 随机取出10条数据 | select top 10 * from tablename order by newid() |
| 38 | 初始化表 | TRUNCATE TABLE table1 |
| 39 | 1=1，1=2的使用 | where 1=1”是表示选择全部；“where 1=2”全部不选 |
| 40 | 收缩数据库 |
重建索引： DBCC REINDEX DBCC INDEXDEFRAG 收缩数据和日志： DBCC SHRINKDB DBCC SHRINKFILE |
| 41 | 压缩数据库 | dbcc shrinkdatabase(dbname) |
| 42 | 转移数据库给新用户以已存在用户权限 |
exec sp_change_users_login ‘update_one‘,‘newname‘,‘oldname‘ go |
| 43 | 检查备份集 | RESTORE VERIFYONLY from disk=‘E:\dvbbs.bak‘ |
| 44 | 修复数据库 |
Alter DATABASE [dvbbs] SET SINGLE_USER GO DBCC CHECKDB(‘dvbbs‘,repair_allow_data_loss) WITH TABLOCK GO Alter DATABASE [dvbbs] SET MULTI_USER GO |
| 45 | 分组:Group by |
一张表，一旦分组 完成后，查询后只能得到组相关的信息。 组相关的信息：（统计信息） count,sum,max,min,avg 分组的标准) 在SQLServer中分组时：不能以text,ntext,image类型的字段作为分组依据 在selecte统计函数中的字段，不能和普通的字段放在一起； |
| 46 | 修改数据库名称 | sp_renamedb ‘old_name‘, ‘new_name‘ |
| 47 | 在线视图查询 | select * from (SELECT a,b,c FROM a) T where t.a > 1; |
| 48 | 更改某个表 | exec sp_changeobjectowner ‘tablename‘,‘dbo‘ |
| 49 | 按姓氏笔画排序 | Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as //从少到多 |
| 50 | 数据库加密 |
select encrypt(‘原始密码‘) select pwdencrypt(‘原始密码‘) select pwdcompare(‘原始密码‘,‘加密后密码‘) = 1--相同；否则不相同 select pwdencrypt(‘原始密码‘) select pwdcompare(‘原始密码‘,‘加密后密码‘) = 1--相同；否则不相同 |
| 51 | 查看硬盘分区 | EXEC master..xp_fixeddrives |
| 52 | 比较A,B表是否相等 |
if (select checksum_agg(binary_checksum(*)) from A) =(select checksum_agg(binary_checksum(*)) from B) print ‘相等‘ else print ‘不相等‘ |
| 53 | 杀掉所有的事件探察器进程 |
DECLARE hcforeach CURSOR GLOBAL FOR SELECT ‘kill ‘+RTRIM(spid) FROM master.dbo.sysprocesses WHERE program_name IN(‘SQL profiler‘,N‘SQL 事件探查器‘) EXEC sp_msforeach_worker ‘?‘ |
| 54 | N到M条记录(要有主索引ID) | Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID Desc |
| 55 | 查看与某一个表相关的视图、存储过程、函数 | select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like ‘%表名%‘ |
| 56 | 不同服务器数据库之间的数据操作 |
//--创建链接服务器 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 ‘ |
| 57 | 连接远程/局域网数据(openrowset) |
//--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 from openrowset( ‘SQLOLEDB ‘, ‘sql服务器名 ‘; ‘用户名 ‘; ‘密码 ‘,数据库名.dbo.表名)as a inner join 本地表 b on a.column1=b.column1 |
| 58 | 连接远程/局域网数据(openquery) |
//--openquery用法需要创建一个连接 --首先创建一个连接创建链接服务器 exec sp_addlinkedserver ‘ITSV ‘, ‘ ‘, ‘SQLOLEDB ‘, ‘远程服务器名或ip地址 ‘ --查询 select * FROM openquery(ITSV, ‘SELECT * FROM 数据库.dbo.表名 ‘) --把本地表导入远程表 insert openquery(ITSV, ‘SELECT * FROM 数据库.dbo.表名 ‘) select * from 本地表 --更新本地表 update b set b.列B=a.列B FROM openquery(ITSV, ‘SELECT * FROM 数据库.dbo.表名 ‘) as a inner join 本地表 b on a.列A=b.列A |
| 59 | 连接远程/局域网数据(opendatasource) |
//--3、opendatasource/openrowset SELECT * FROM opendatasource( ‘SQLOLEDB ‘, ‘Data Source=ip/ServerName;User ID=登陆名;Password=密码 ‘ ).test.dbo.roy_ta --把本地表导入远程表 insert opendatasource( ‘SQLOLEDB ‘, ‘Data Source=ip/ServerName;User ID=登陆名;Password=密码 ‘).数据库.dbo.表名 select * from 本地表 |
| 60 | 自定义数据类型 | EXEC sp_addtype birthday, datetime, ‘NULL‘ |