SQL Server 语句

数据库：

1、  创建数据库的语法：

if exists (select * from sysdatabases where name='DT_name')   /*判断是否存在，是则删除*/

drop database DT_name

create database database_name

on          primary  /* 指定主要文件组，其中第一个主要文件 */

(

name = data1,   /* 主数据文件的逻辑名 */

filename =‘地址/data1.mdf’; /* 主数据文件的物理 地址、名称 */

size = 10MB,    /* 初始大小*/

maxsize = 100MB,          /* 最大大小 */

filegrowth = 10%          /* 增长率 */

),

filegroup group_NAME      /* 非主要文件组 */

(

name = data2,

filename =‘地址/data2.ndf’；

size = 10MB,

maxsize = 100MB,

filegrowth = 10%

)...

log on      /*指定事务日志文件*/

(

name = data3,   /* 日志文件的逻辑名 */

filename =‘地址/data3.ldf’; /* 日志文件的物理 地址、名称 */

size = 5MB,

maxsize = 100MB,

filegrowth = 5%

)...

 

2、  修改语句：

alter database database_name +

1)  添加文件：add [log|] file (...)

2)  修改文件：modify file(name =‘文件名’，修改内容)

3)  删除文件：remove [log|] file “file_name”

4)  文件组：

j 添加：add filegroup 文件组名称 go alter database 数据库 add file (...)

k 删除：remove filegroup 文件组名称 ( 注意：除非文件组不包含任何数据文件，

否则无法删除 )

 

 

表：

1、  创建表：

create table table_name

(

列名#1   数据类型，

...

列名#n   数据类型，

)

1)   主键约束：primary key

2)   外键约束：foreign key               

j [Constraint constraint_name]

Foreign key(外键列名) references 主表名(主键列)

k 表已经存在的情况在:

ALTER TABLE table_name

ADD FOREIGN KEY (外键列名)

REFERENCES table_primary(主键列)

3)   自动增长：identity(最低值，增长值)   适用于：tinyint、smallint、int、decimal、

numeric，一个表仅允许一个列声明 identity 属性；

4)   唯一约束：unique

5)   检查约束：check([检查的表达式])

6)   默认约束：dafault([默认的值])

7)   新建约束：alter table 表名 add constraint con_name 声明；

当用此语法新建约束时，SQL SERVER会去检查目前在表内的数据是都合乎新建约束的条件，可以用 with nocheck 命令SQL SERVER不做检查；

alter table 表名 with nocheck add constraint...

with nocheck 仅对check和forign key 有效

8)   取消强制约束：alter table 表名 drop constraint 约束名

9)    

 

2、  修改表的属性：

添加列：Alter table  table_name  Add  column_name  column_type

删除列：Alter  table  table_name  Drop  COLUMN  column_name

修改列：Alter  table  table_name  Alter  COLUMN  column_name  column_type

 

3、  根据已有的表创建表：

select * into new_tableName from tableName

 

4、  删除表：

drop table table_name

 

5、  临时表：

1)    临时表的类型：

j局部临时表：#table_name（只能在创建者的会话中能够看到）；

k 2)     全局临时表：##table_name（能在所有会话中看到）；

2)    创建的方式：

j 1)     create   table    #[|##]table_name(...)

k select (col)    into     #[|##]table_name   from table

3)    临时表的存储：存储在 系统数据库 表tempdb 中；

4)    临时表的判断：

if exists (select name from tempdb..sysobjects where id = object_id('tempdb..#temp_name'))

drop table #temp_name

 

 

创建索引：

1.    创建语法：

create  [unique] [clustered] [nonclustered] index index_name on {table|view}(列 asc|desc)

创建的原则：

根据where条件建索引是极其重要的一个原则；

对于复合索引，在查询使用时，最好将条件顺序按照索引的顺序，这样效率最高；

字段数据更新频率较低，查询频率较高并存在大量重复值建议使用聚簇索引；

 

1)   聚簇索引：clustered index

2)   非聚簇索引：nonclustered index

3)   唯一索引：create unique index index_name ON table_name( 索引列) ；字段不能有空值，否则在使用时会经常出错。

4)   复合索引：最多16个字段；

 

2.    删除索引：DROP INDEX TABLE_NAME.INDEX_NAME

 

3.    问题：

 

1)    复合索引使用的目的是什么？

能形成索引覆盖,提高where语句的查询效率；

 

2)    一个复合索引是否可以代替多个单一索引？

复合索引的使用原则是第一个条件应该是复合索引的第一列,依次类推,否则复合索引不会被使用，所以正常情况下复合索引不能替代多个单一索引；

 

3)    符合索引中索引列的排序原则是什么？

复合索引的使用原则是第一个条件应该是复合索引的第一列,依次类推；

 

4)    在进行哪些类型的查询时，使用复合索引会比较有效？

如果一个表中的数据在查询时有多个字段总是同时出现则这些字段就可以作为复合索引,形成索引覆盖可以提高查询的效率；

 

5)    什么情况下不适合使用复合索引？

建立索引的目的就是帮助查询,如果查寻用不到则索引就没有必要建立,另外如果数据表过大(5w以上)则有些字段(字符型长度超过(40))不适合作为索引,另外如果表是经常需要更新的也不适合做索引；

 

6)    聚集索引与非聚集索引的比较？

j 、聚集索引：

聚集索引基于数据行的键值在表内排序和存储这些数据行。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储；

 

考虑对具有以下特点的查询使用聚集索引：

l   使用运算符（如 BETWEEN、>、>=、< 和 <=）返回一系列值。

使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行物理相邻。例如，如果某个查询在一系列销售订单号间检索记录，SalesOrderNumber 列的聚集索引可快速定位包含起始销售订单号的行，然后检索表中所有连续的行，直到检索到最后的销售订单号。

l   返回大型结果集。

l   使用 JOIN 子句；一般情况下，使用该子句的是外键列。

l   使用 ORDER BY 或 GROUP BY 子句。

在 ORDER BY 或 GROUP BY 子句中指定的列的索引，可以使数据库引擎不必对数据进行排序，因为这些行已经排序。这样可以提高查询性能。

定义聚集索引键时使用的列越少越好。考虑具有下列一个或多个属性的列：

l   唯一或包含许多不重复的值（例如：雇员ID唯一标识雇员）；

l   按顺序被访问；

l   由于保证了列在表中是唯一的，所以定义为 IDENTITY；

l   经常用于对表中检索到的数据进行排序；

不适用于具有下列属性的列：

l   频繁更改的列；

l   宽键（宽键是若干列或若干大型列的组合）；

 

k、非聚集索引：

非聚集索引包含索引键值和指向表数据存储位置的行定位器，可以对表或索引视图创建多个非聚集索引。通常，设计非聚集索引是为改善经常使用的、没有建立聚集索引的查询的性能。

 

设计非聚集索引时需要注意数据库的特性：

l   更新要求较低但包含大量数据的数据库或表可以从许多非聚集索引中获益从而改善查询性能

 

考虑对具有以下属性的查询使用非聚集索引：

l   使用JOIN 或 GROUP BY 子句；

应为联接和分组操作中所涉及的列创建多个非聚集索引，为任何外键列创建一个聚集索引；

l   不返回大型结果集的查询；

创建筛选索引以覆盖从大型表中返回定义完善的行子集的查询。

l   包含经常包含在查询的搜索条件（例如返回完全匹配的 WHERE 子句）中的列；

 

 

创建视图：

1.   创建视图

CREATE VIEW [拥有者.]视图名称

[(列名#1,…,列名#n)]

[WITH ENCRYPTION]

AS SELECT 命令 [WITH CHECK OPTION]

2.   视图的限制：

只能在目前数据库下创建视图；

视图不能和reles、defaulets、trigger相关连；

不能对视图创建索引；

无法创建临时保存视图；

无法对临时保存表创建视图；

无法使用READTEXT和WRITETEXT对视图内的TEXT和IMAGE列读取或写入数据；

不能使用 ORDER BY、COMPUT或COMPUTE BY语句；

不能使用 UNION 语句；

不能使用INTO 语句；

 

3.   缺点：

性能、修改权限；

 

 

创建存储过程：

CREATE proc[edure] [拥有者.]存储过程名称[;程序编号]

[(参数#1,...,参数#1024)]

[with

    {RECOMPILE|ENCRYPTION|RECOMPILE, ENCRYPTION }

]

[FOR    REPLICATION]

AS  程序行

 

解析：

1)    相同的存储过程名称，不同的程序编号，组成一个存储过程组；

（例如：pcnam;1、pcnam;2，执行drop procedure时，组内的所有程序全部删除，但是无法单独删除组内的成员）

2)    with  RECOMPILE：存储过程每次执行时都会重新编译；

3)    with  ENCRYPTION：存储过程加密（无法查看存储过程代码）；

   

执行：

declare 声明变量

execute 存储过程名称    参数#1,...,参数#1024

 

临时保存存储过程：

#procedure_name：本区临时存储过程；只能用户自己使用，用户断开SQL SERVER 连接时，该存储过程就自动删除；

##procedure_name：全区临时存储过程；所有用户都可使用，存储过程的拥有者不能限定别人使用它，当最后一位用户断开连接时，存储过程被删除；