SQLServer基础语法实例应用（一）

网络整理 - 08-18

一、基础 1、说明：创建数据库 ?

1 CREATE DATABASE database-name

2、说明：删除数据库

1 DROP DATABASE database-name

3、说明：备份数据库

1 2 3 4 5 USE master -- 创建备份数据的 device EXEC sp_addumpdevice 'disk', 'cc_jz', 'd:\cc_jz.dat' -- 开始备份 BACKUP DATABASE cc_jz TO cc_jz

4、说明：创建新表

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) --> 测试数据：[a] if object_id('[a]') is not null drop table [a] go create table [a]([ID] int,[品名] varchar(6),[入库数量] int,[入库时间] datetime) insert [a] select 1,'矿泉水',100,'2013-01-02' union all select 2,'方便面',60,'2013-01-03' union all select 3,'方便面',50,'2013-01-03' union all select 4,'矿泉水',80,'2013-01-04' union all select 5,'方便面',50,'2013-01-05' select * from a /* ID 品名入库数量入库时间 ----------- ------ ----------- ----------------------- 1 矿泉水 100 2013-01-02 00:00:00.000 2 方便面 60 2013-01-03 00:00:00.000 3 方便面 50 2013-01-03 00:00:00.000 4 矿泉水 80 2013-01-04 00:00:00.000 5 方便面 50 2013-01-05 00:00:00.000 (5 行受影响) */

5、说明：删除新表

1 drop table tabname

6、说明：增加一个列

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 Alter table tabname add column col type Alter table a add col int select * from a /* ID 品名入库数量入库时间 col ----------- ------ ----------- ----------------------- ----------- 1 矿泉水 100 2013-01-02 00:00:00.000 NULL 2 方便面 60 2013-01-03 00:00:00.000 NULL 3 方便面 50 2013-01-03 00:00:00.000 NULL 4 矿泉水 80 2013-01-04 00:00:00.000 NULL 5 方便面 50 2013-01-05 00:00:00.000 NULL (5 行受影响) */

7、说明：添加主键：

1 Alter table tabname add primary key(col)

说明：删除主键：

1 Alter table tabname drop primary key(col)

8、说明：创建索引：

1 create [unique] index idxname on tabname(col….)

删除索引：

1 drop index idxname

注：索引是不可更改的，想更改必须删除重新建。

9、说明：创建视图：

1 create view viewname as select statement

删除视图：

1 drop view viewname

10、说明：几个简单的基本的sql语句 ?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 --选择： select * from table1 --插入： 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%' --排序： select * from table1 order by field1,field2 [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 table1

11、说明：几个高级查询运算词

A： UNION/UNION ALL 运算符

UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。

当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 --> 测试数据：[a] if object_id('[a]') is not null drop table [a] go create table [a]([ID] int) insert [a] select 1 union all select 1 union all select 2 union all select 3 union all select null select * from a /* (5 行受影响) ID ----------- 1 1 2 3 NULL (5 行受影响) */ --> 测试数据：[b] if object_id('[b]') is not null drop table [b] go create table [b]([ID] int) insert [b] select 1 union all select 2 union all select 2 union all select 4 union all select null select * from b /* (5 行受影响) ID ----------- 1 2 2 4 NULL (5 行受影响) */ --合并去重 select * from a union select * from b /* ID ----------- NULL 1 2 3 4 (5 行受影响) */ --合并不去重 select * from a union all select * from b /* ID ----------- 1 1 2 3 NULL 1 2 2 4 NULL (10 行受影响) */

B： EXCEPT 运算符

EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。

注意：根本没有EXCEPT ALL 的用法；网上很多文章里写有EXCEPT ALL ，实际上是错误的。（测试SQL Server 2000 2005 2008R2 2012都不好用）

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 --> 测试数据：[a] if object_id('[a]') is not null drop table [a] go create table [a]([ID] int) insert [a] select 1 union all select 1 union all select 2 union all select 3 union all select null select * from a /* (5 行受影响) ID ----------- 1 1 2 3 NULL (5 行受影响) */ --> 测试数据：[b] if object_id('[b]') is not null drop table [b] go create table [b]([ID] int) insert [b] select 1 union all select 2 union all select 2 union all select 4 union all select null select * from b /* (5 行受影响) ID ----------- 1 2 2 4 NULL (5 行受影响) */ --取两表不同数据并去重 select * from a EXCEPT select * from b /* ID ----------- 3 (1 行受影响) */

C： INTERSECT 运算符

INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。

注意：根本没有INTERSECT ALL 的用法；网上很多文章里写有INTERSECT ALL ，实际上是错误的。（测试SQL Server 2000 2005 2008R2 2012都不好用）

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 --> 测试数据：[a] if object_id('[a]') is not null drop table [a] go create table [a]([ID] int) insert [a] select 1 union all select 1 union all select 2 union all select 3 union all select null select * from a /* (5 行受影响) ID ----------- 1 1 2 3 NULL (5 行受影响) */ --> 测试数据：[b] if object_id('[b]') is not null drop table [b] go create table [b]([ID] int) insert [b] select 1 union all select 2 union all select 2 union all select 4 union all select null select * from b /* (5 行受影响) ID ----------- 1 2 2 4 NULL (5 行受影响) */ --取两表相同数据并去重 select * from a INTERSECT select * from b /* ID ----------- NULL 1 2 (3 行受影响) */

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 --> 测试数据：[a] if object_id('[a]') is not null drop table [a] go create table [a]([ID] int) insert [a] select 1 union all select 1 union all select 2 union all select 3 union all select null select * from a /* (5 行受影响) ID ----------- 1 1 2 3 NULL (5 行受影响) */ --> 测试数据：[b] if object_id('[b]') is not null drop table [b] go create table [b]([ID] int) insert [b] select 1 union all select 2 union all select 2 union all select 4 union all select null select * from b /* (5 行受影响) ID ----------- 1 2 2 4 NULL (5 行受影响) */ select a.*,b.* from a a LEFT JOIN b b ON a.id= b.id /* ID ID ----------- ----------- 1 1 1 1 2 2 2 2 3 NULL NULL NULL (6 行受影响) */

B：right （outer） join:

右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 --> 测试数据：[a] if object_id('[a]') is not null drop table [a] go create table [a]([ID] int) insert [a] select 1 union all select 1 union all select 2 union all select 3 union all select null select * from a /* (5 行受影响) ID ----------- 1 1 2 3 NULL (5 行受影响) */ --> 测试数据：[b] if object_id('[b]') is not null drop table [b] go create table [b]([ID] int) insert [b] select 1 union all select 2 union all select 2 union all select 4 union all select null select * from b /* (5 行受影响) ID ----------- 1 2 2 4 NULL (5 行受影响) */ select a.*,b.* from a a RIGHT JOIN b b ON a.id= b.id /* ID ID ----------- ----------- 1 1 1 1 2 2 2 2 NULL 4 NULL NULL (6 行受影响) */

C：full/cross （outer） join：

全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 --> 测试数据：[a] if object_id('[a]') is not null drop table [a] go create table [a]([ID] int) insert [a] select 1 union all select 1 union all select 2 union all select 3 union all select null select * from a /* (5 行受影响) ID ----------- 1 1 2 3 NULL (5 行受影响) */ --> 测试数据：[b] if object_id('[b]') is not null drop table [b] go create table [b]([ID] int) insert [b] select 1 union all select 2 union all select 2 union all select 4 union all select null select * from b /* (5 行受影响) ID ----------- 1 2 2 4 NULL (5 行受影响) */ select a.*,b.* from a a FULL JOIN b b ON a.id= b.id /* ID ID ----------- ----------- 1 1 1 1 2 2 2 2 3 NULL NULL NULL NULL 4 NULL NULL (8 行受影响) */

13、分组:Group by:
一张表，一旦分组完成后，查询后只能得到组相关的信息。
组相关的信息：（统计信息） count,sum,max,min,avg 分组的标准)
在SQLServer中分组时：不能以text,ntext,image类型的字段作为分组依据

在selecte统计函数中的字段，不能和普通的字段放在一起

14、对数据库进行操作：

分离数据库： sp_detach_db;

附加数据库：sp_attach_db 后接表明，附加需要完整的路径名

15.如何修改数据库的名称:

1 sp_renamedb 'old_name', 'new_name'