《SQL语句大全(百度查询).doc》由会员分享，可在线阅读，更多相关《SQL语句大全(百度查询).doc（12页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、创建数据库创建之前判断该数据库是否存在 if exists (select * from sysdatabases where name=databaseName) drop database databaseName go Create DATABASE database-name 删除数据库drop database dbname 备份sql server- 创建备份数据的 device USE master EXEC sp_addumpdevice disk, testBack, c:mssql7backupMyNwind_1.dat - 开始备份 BACKUP DATABASE pub
2、s TO testBack 创建新表create table tabname(col1 type1 not null primary key,col2 type2 not null,.) 根据已有的表创建新表： A：go use 原数据库名 go select * into 目的数据库名.dbo.目的表名 from 原表名(使用旧表创建新表) B：create table tab_new as select col1,col2 from tab_old definition only 创建序列create sequence SIMON_SEQUENCE minvalue 1 - 最小值 max
3、value 999999999999999999999999999 - 最大值 start with 1 - 开始值 increment by 1 - 每次加几 cache 20; 删除新表drop table tabname 增加一个列Alter table tabname add colname coltype 删除一个列Alter table tabname drop column colname 添加主键Alter table tabname add primary key(col) 说明：删除主键：Alter table tabname drop primary key(col) 创
4、建索引create unique index idxname on tabname(col。) 删除索引：drop index idxname on tabname 注：索引是不可更改的，想更改必须删除重新建。 创建视图create view viewname as select statement 删除视图：drop view viewname 简单基本的sql语句(1) 数据记录筛选： sql=select * from 数据表 where 字段名=字段值 order by 字段名 desc sql=select * from 数据表 where 字段名 like %字段值% order
5、by 字段名 desc sql=select top 10 * from 数据表 where 字段名 order by 字段名 desc sql=select * from 数据表 where 字段名 in (值1,值2,值3) sql=select * from 数据表 where 字段名 between 值1 and 值2 (2) 更新数据记录： sql=update 数据表 set 字段名=字段值 where 条件表达式 sql=update 数据表 set 字段1=值1,字段2=值2 字段n=值n where 条件表达式 (3) 删除数据记录： sql=delete from 数据表
6、where 条件表达式 sql=delete from 数据表 (将数据表所有记录删除) (4) 添加数据记录： sql=insert into 数据表 (字段1,字段2,字段3 ) values (值1,值2,值3 ) sql=insert into 目标数据表 select * from 源数据表 (把源数据表的记录添加到目标数据表) (5) 数据记录统计函数： AVG(字段名) 得出一个表格栏平均值 COUNT(*;字段名) 对数据行数的统计或对某一栏有值的数据行数统计 MAX(字段名) 取得一个表格栏最大的值 MIN(字段名) 取得一个表格栏最小的值 SUM(字段名) 把数据栏的值相加
7、 引用以上函数的方法： sql=select sum(字段名) as 别名 from 数据表 where 条件表达式 set rs=conn.excute(sql) 用 rs(别名) 获取统计的值，其它函数运用同上。 查询去除重复值：select distinct * from table1 (5) 数据表的建立和删除： CREATE TABLE 数据表名称(字段1 类型1(长度),字段2 类型2(长度) ) 几个高级查询运算词A：UNION 运算符 UNION 运算符通过组合其他两个结果表（例如TABLE1 和TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随UNION 一
8、起使用时（即UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自TABLE1 就是来自TABLE2。 B： EXCEPT 运算符 EXCEPT 运算符通过包括所有在TABLE1 中但不在TABLE2 中的行并消除所有重复行而派生出一个结果表。当ALL 随EXCEPT 一起使用时(EXCEPT ALL)，不消除重复行。 C：INTERSECT 运算符 INTERSECT 运算符通过只包括TABLE1 和TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当ALL 随INTERSECT 一起使用时(INTERSECT ALL)，不消除重复行。 注：使用运算词的几个查询结果
9、行必须是一致的。 使用外连接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 outer join： 全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。 判断对象是否存在判断数据库是否存在if exists (select* from sy
10、sdatabases wherename= 数据库名) dropdatabase数据库名 判断表是否存在if not exists (select * from sysobjects where name = 表名 and xtype=U) begin -这里创建表 end 判断存储过程是否存在if exists (select* from sysobjects whereid = object_id(N存储过程名) and OBJECTPROPERTY(id, NIsProcedure) = 1) dropprocedure存储过程名 判断临时表是否存在if object_id(tempdb
11、.#临时表名) isnot null droptable#临时表名 判断视图是否存在-SQL Server 2000 IF EXISTS (SELECT* FROM sysviews WHEREobject_id = dbo.视图名 -SQL Server 2005 IF EXISTS (SELECT* FROM sys.views WHEREobject_id = dbo.视图名 判断函数是否存在if exists (select* from dbo.sysobjects whereid = object_id(Ndbo.函数名) and xtype in (NFN, NIF, NTF) d
12、ropfunctiondbo.函数名 获取创建信息SELECTname,id,crdate FROM sysobjects where xtype=U /* xtype 的表示参数类型，通常包括如下这些 C = CHECK约束 D = 默认值或DEFAULT约束 F = FOREIGNKEY约束 L = 日志 FN = 标量函数 IF = 内嵌表函数 P = 存储过程 PK = PRIMARYKEY约束（类型是K） RF = 复制筛选存储过程 S = 系统表 TF = 表函数 TR = U = 用户表 UQ = UNIQUE约束（类型是K） V = 视图 X = 扩展存储过程 */ 判断列是否
13、存在if exists(select* from syscolumns whereid=object_id(表名) and name=列名) altertable表名dropcolumn列名 判断列是否自增列if columnproperty(object_id(table),col,IsIdentity)=1 print 自增列 else print 不是自增列 SELECT* FROM sys.columns WHEREobject_id=OBJECT_ID(表名) AND is_identity=1 判断表中是否存在索引if exists(select* from sysindexes
14、whereid=object_id(表名) and name=索引名) print 存在 else print 不存在 查看数据库中对象SELECT* FROM sysobjects WHEREname=对象名 select * from table（所要查询的表名） where coloum(条件) 提升复制表(只复制结构，源表名：a 新表名：b) (Access可用) 法一：select * into b from a where 11 法二：select top 0 * into b from a 拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用) insert into
15、 b(x, y, z) select d,e,f from a; 跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) insert into b(x, y, z) select d,e,f from a in 具体数据库 where 条件 例子：。.from b in &Server.MapPath(.&data.mdb & where. 子查询(表名1：a 表名2：b) select a,b,c from a where a IN (select d from b 或者： select a,b,c from a where a IN (1,2,3) 显示文章最后时间selec
16、t a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b 外连接查询(表名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 在线视图查询(表名1：a select * from (Select a,b,c FROM a) T where t.a 1; between的用法between限制查询数据范围时包括了边界值
17、，not between不包括 select * from table1 where time between time1 and time2 select a,b,c, from table1 where a not between 数值1 and 数值2 in 的使用方法select * from table1 where a not in (值1，值2,值4，值6) 删除主表没有的信息两张关联表delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 四表联查问
18、题select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where . 日程安排提前五分钟SQL: select * from 日程安排 where datediff(minute,f开始时间，getdate()5 一条sql 搞定数据库页select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段= a.主键字段 orde
19、r by a.排序字段 前10条记录select top 10 * from table1 where 范围 选择排名选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜，每月热销产品分析,按科目成绩排名，等等。) select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) 派生结果表包括所有在TableA 中但不在TableB和TableC 中的行并消除所有重复行而派生出一个结果表 (select a from tableA exce
20、pt (select a from tableB) except (select a from tableC) 随机取出10条数据select top 10 * from tablename order by newid() 随机选择记录select newid() 删除重复记录Delete from tablename where id not in (select max(id) from tablename group by col1,col2,.) 列出数据库里的表名select name from sysobjects where type=U 列出表里的所有的select name
21、 from syscolumns where id=object_id(TableName) 列示排列列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似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 ve
22、nder pcs 电脑A 1 电脑A 1 B 2 光盘A 2 手机B 3 手机C 3 初始化表table1TRUNCATE TABLE table1 选择从10到15的记录select top 5 * from (select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc) table_2 order by id 数据类型转换declare numid int declare id varchar(50) set numid=2005 set id=convert(varc
23、har,numid) 通过上述语句完成数据类型Int转换成varchar，其他转换类似，可参看convert函数 技巧1=1，1=2的使用在SQL语句组合时用的较多 “where 1=1”是表示选择全部 “where 1=2”全部不选， 如： 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 我们可以直接写成 se
24、t strSQL = select count(*) as Total from + tblName + where 1=1 and + strWhere 收缩数据库-重建索引 DBCC REINDEX DBCC INDEXDEFRAG -收缩数据和日志 DBCC SHRINKDB DBCC SHRINKFILE 压缩数据库dbcc shrinkdatabase(dbname) 转移数据库给新用户以已存在用户权限 exec sp_change_users_login update_one,newname,oldname go 检查备份集RESTORE VERIFYONLY from disk=
25、E:dvbbs.bak 修复数据库Alter DATABASE dvbbs SET SINGLE_USER GO DBCC CHECKDB(dvbbs,repair_allow_data_loss) WITH TABLOCK GO Alter DATABASE dvbbs SET MULTI_USER GO 日志清除SET NOCOUNT ON DECLARE LogicalFileName sysname, MaxMinutes INT, NewSize INT USE tablename - 要操作的数据库名 Select LogicalFileName = tablename_log,
26、- 名 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 + CONVERT(VARCHAR(30),OriginalSize) + 8K pages or +
27、 CONVERT(VARCHAR(30),(OriginalSize*8/1024) + MB FROM sysfiles Where name = LogicalFileName Create TABLE DummyTrans (DummyColumn char (8000) not null) DECLARE Counter INT, StartTime DATETIME, TruncLog VARCHAR(255) Select StartTime = GETDATE(), TruncLog = BACKUP LOG + db_name() + WITH TRUNCATE_ONLY DB
28、CC 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 = LogicalFileName) AND (OriginalSize * 8 /1024) NewSize BEGIN - Outer loop. Select
29、 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 + CONVERT(VARCHAR(30),size) + 8K pages or + CONVERT(VARCHAR(30),(size
30、*8/1024) + MB FROM sysfiles Where name = LogicalFileName Drop TABLE DummyTrans SET NOCOUNT OFF 更改某个表exec sp_changeobjectowner tablename,dbo 存储更改全部表Create PROCEDURE dbo.User_ChangeObjectOwnerBatch OldOwner as NVARCHAR(128), NewOwner as NVARCHAR(128) AS DECLARE Name as NVARCHAR(128) DECLARE Owner as N
31、VARCHAR(128) DECLARE OwnerName as NVARCHAR(128) DECLARE curObject CURSOR FOR select Name = name, Owner = user_name(uid) from sysobjects where user_name(uid)=OldOwner order by name OPEN curObject FETCH NEXT FROM curObject INTO Name, Owner WHILE(FETCH_STATUS=0) BEGIN if Owner=OldOwner begin set OwnerN
32、ame = OldOwner + . + rtrim(Name) exec sp_changeobjectowner OwnerName, NewOwner end - select name,NewOwner,OldOwner FETCH NEXT FROM curObject INTO Name, Owner END close curObject deallocate curObject GO SERVER中循环写入数据declare i int set i=1 while i30 begin insert into test (userid) values(i) set i=i+1 e
33、nd在SQL Server中有5种约束：主键约束（primary key constraint）唯一性约束（unique constraint）检查约束（check constraint）缺省约束（default constraint）外部键约束（foreign key constraint）在SQL SERVER中，（1）对于基本表的约束分为列约束和表约束约束是限制用户输入到表中的数据的值的范围，一般分为列级约束与表级约束。列级约束有六种：主键Primary key、外键foreign key 、唯一 unique、检查 checck 、默认default 、非空/空值 not null/
34、null表级约束有四种：主键、外键、唯一、检查列约束是对某一个特定列的约束，包含在列定义中，直接跟在该列的其他定义之后，用空格分隔，不必指定列名；表约束与列定义相互独立，不包括在列定义中，通常用于对多个列一起进行约束，与列定义用,分隔，定义表约束时必须指出要约束的那些列的名称。完整性约束的基本语法格式为： CONSTRAINT 约束名：约束不指定名称时，系统会给定一个名称。（2）列级约束与表级约束的区别如果完整性约束涉及到该表的多个属性列，必须定义在表级上，否则既可以定义在列级也可以定义在表级。简而言之：列级约束:列级约束是行定义的一部分，只能应用于一列上。表级约束:表级约束是独立于列的定义，
35、可以应用在一个表中的多列上。（3）列级约束与表级约束在SQL中的用法（即如何在SQL中定义约束）在创建表时定义约束：CREATE TABLE table_name ( -列级约束定义|column_name AS computed_column_expression -计算列定义| -表级约束定义,.n )一个约束定义为列级约束还是表级约束？根据实际需要和设计者思路确定。如primary key，当只涉及到一列时，定义为列级约束；当涉及到多列时，则定义为表级约束。Prinmary key定义为列级约束时，相应SQL语句：Stu_id上建立的主键pk_1为列级约束CREATE TABLE stu
36、dent(Stu_id int constraint pk_1 primary key,Stu_name varchar(8),.)当定义为表级约束时，相应SQL语句：CREATE TABLE student(Stu_id int NOT NULL,Stu_id上建立的主键pk_1为的表级约束Stu_name varchar(8),Constraint pk_1 primary key (Stu_id),.)各约束具体说明：1. 主键约束PRIMARY KEY约束PRIMARY KEY约束用于定义基本表的主键，起唯一标识作用，其值不能为NULL，也不能重复，以此来保证实体的完整性。PRIMAR
37、Y KEY与UNIQUE约束类似，通过建立唯一索引来保证基本表在主键列取值的唯一性，但它们之间存在着很大的区别：在一个基本表中只能定义一个PRIMARY KEY约束，但可定义多个UNIQUE约束；对于指定为PRIMARY KEY的一个列或多个列的组合，其中任何一个列都不能出现空值，而对于UNIQUE所约束的唯一键，则允许为空。注意：不能为同一个列或一组列既定义UNIQUE约束，又定义PRIMARY KEY约束。PRIMARY KEY既可用于列约束，也可用于表约束。PRIMARY KEY用于定义列约束时，其语法格式如下：CONSTRAINT PRIMARY KEYPRIMARY KEY用于定义表
38、约束时，即将某些列的组合定义为主键，其语法格式如下： CONSTRAINT S PRIMARY KEY ()2. 唯一性(UNIQUE)约束UNIQUE约束用于指明基本表在某一列或多个列的组合上的取值必须唯一。定义了UNIQUE约束的那些列称为唯一键，系统自动为唯一键建立唯一索引，从而保证了唯一键的唯一性。唯一键允许为空，但系统为保证其唯一性，最多只可以出现一个NULL值。UNIQUE既可用于列约束，也可用于表约束。UNIQUE用于定义列约束时，其语法格式如下：CONSTRAINT UNIQUE唯一性约束用于指定一个或者多个列的组合的值具有唯一性，以防止在列中输入重复的值。当使用唯一性约束时，
39、需要考虑以下几个因素：使用唯一性约束的字段允许为空值。一个表中可以允许有多个唯一性约束。可以把唯一性约束定义在多个字段上。唯一性约束用于强制在指定字段上创建一个唯一性索引。缺省情况下，创建的索引类型为非聚簇索引。unique约束是用来确保不受主键约束列上的数据的唯一性.unique与primary key的区别在于:（1）unique约束主要用于非主键的一列或多列上要求数据唯一.（2） unique约束允许该列上存在NULL值,而主键决不允许出现.（3）可以在一个表创建多个unique约束,而在一个表上只能够设置一个主键3. 检查约束CHECK约束用来检查字段值所允许的范围，如，一个字段只能输
40、入整数，而且限定在0-100的整数，以此来保证域的完整性。CHECK既可用于列约束，也可用于表约束，其语法格式为：CONSTRAINT CHECK ()一个列级检查约束只能与限制的字段有关；一个表级检查约束只能与限制的表中字段有关。一个表中可以定义多个检查约束。每个CREATE TABLE语句中每个字段只能定义一个检查约束。在多个字段上定义检查约束，则必须将检查约束定义为表级约束。当执行INSERT语句或者UPDATE语句时，检查约束将验证数据。检查约束中不能包含子查询。4. 缺省约束使用缺省约束时，应该注意以下几点：每个字段只能定义一个缺省约束。如果定义的缺省值长于其对应字段的允许长度，那么
41、输入到表中的缺省值将被截断。不能加入到带有IDENTITY属性或者数据类型为timestamp的字段上。如果字段定义为用户定义的数据类型，而且有一个缺省绑定到这个数据类型上，则不允许该字段有缺省约束。5. 外部键约束外部键约束用于强制参照完整性，提供单个字段或者多个字段的参照完整性。 FOREIGN KEY约束指定某一个列或一组列作为外部键，其中，包含外部键的表称为从表(参照表)，包含外部键所引用的主键或唯一键的表称主表(被参照表)。系统保证从表在外部键上的取值要么是主表中某一个主键值或唯一键值，要么取空值。以此保证两个表之间的连接，确保了实体的参照完整性。FOREIGN KEY既可用于列约束
42、，也可用于表约束，其语法格式为：CONSTRAINT FOREIGN KEYREFERENCES ()当使用外部键约束时，应该考虑以下几个因素：外部键约束提供了字段参照完整性。外部键从句中的字段数目和每个字段指定的数据类型必须和REFERENCES从句中的字段相匹配。外部键约束不能自动创建索引，需要用户手动创建。用户想要修改外部键约束的数据，必须有对外部键约束所参考表的SELECT权限或者REFERENCES权限。参考同一表中的字段时，必须只使用REFERENCES子句，不能使用外部键子句。一个表中最多可以有31个外部键约束。在临时表中，不能使用外部键约束。主键和外部键的数据类型必须严格匹配。6. NULL 约束（1）NULL/NOT NULL是否允许该字段的值为NULL。NULL值不是0也不是空白，更不是填入字符串“NULL”，而是表示“不知道”、“不确定”或“没有数据”的意思。当某一字段的值一定要输入才有意义的时候，则可以设置为NOT NULL。如主键列就不允许出现空值，否则就失去了唯一标识一条记录的作用只能用于定义列约束，其语法格式如下： constraint null|not null