SQL Server基礎文法執行個體應用（一）

一、基礎1、說明：建立資料庫

CREATE DATABASE database-name 

2、說明：刪除資料庫

DROP  DATABASE database-name

3、說明：備份資料庫

USE master-- 建立 備份資料的 deviceEXEC sp_addumpdevice 'disk', 'cc_jz', 'd:\cc_jz.dat'-- 開始 備份BACKUP DATABASE cc_jz TO cc_jz 

4、說明：建立新表

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 allselect 2,'即食麵',60,'2013-01-03' union allselect 3,'即食麵',50,'2013-01-03' union allselect 4,'礦泉水',80,'2013-01-04' union allselect 5,'即食麵',50,'2013-01-05'select * from a/*ID          品名     入庫數量        入庫時間----------- ------ ----------- -----------------------1           礦泉水    100         2013-01-02 00:00:00.0002           即食麵    60          2013-01-03 00:00:00.0003           即食麵    50          2013-01-03 00:00:00.0004           礦泉水    80          2013-01-04 00:00:00.0005           即食麵    50          2013-01-05 00:00:00.000(5 行受影響)*/

5、說明：刪除新表

drop table tabname 

6、說明：增加一個列

Alter table tabname add column col typeAlter table a add  col intselect * from a /*ID          品名     入庫數量        入庫時間                    col----------- ------ ----------- ----------------------- -----------1           礦泉水    100         2013-01-02 00:00:00.000 NULL2           即食麵    60          2013-01-03 00:00:00.000 NULL3           即食麵    50          2013-01-03 00:00:00.000 NULL4           礦泉水    80          2013-01-04 00:00:00.000 NULL5           即食麵    50          2013-01-05 00:00:00.000 NULL(5 行受影響)*/

7、說明：添加主鍵：

 Alter table tabname add primary key(col) 

說明：刪除主鍵：

Alter table tabname drop primary key(col) 

8、說明：建立索引：

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

刪除索引：

drop index idxname

註：索引是不可更改的，想更改必須刪除重建立。

9、說明：建立視圖：

create view viewname as select statement 

刪除視圖：

drop view viewname

10、說明：幾個簡單的基本的sql語句

--選擇：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。

--> 測試資料：[a]if object_id('[a]') is not null drop table [a]go create table [a]([ID] int)insert [a]select 1 union allselect 1 union allselect 2 union allselect 3 union allselect null select * from a/*(5 行受影響)ID-----------1123NULL(5 行受影響)*/--> 測試資料：[b]if object_id('[b]') is not null drop table [b]go create table [b]([ID] int)insert [b]select 1 union allselect 2 union allselect 2 union allselect 4 union allselect null select * from b/*(5 行受影響)ID-----------1224NULL(5 行受影響)*/--合并去重select * from a unionselect * from b/*ID-----------NULL1234(5 行受影響)*/--合并不去重select * from a union allselect * from b/*ID-----------1123NULL1224NULL(10 行受影響)*/

B： EXCEPT 運算子

EXCEPT 運算子通過包括所有在 TABLE1 中但不在 TABLE2 中的行並消除所有重複行而派生出一個結果表。

注意：根本沒有EXCEPT ALL 的用法；網上很多文章裡寫有EXCEPT ALL ，實際上是錯誤的。（測試SQL Server 2000 2005 2008R2 2012都不好用）

--> 測試資料：[a]if object_id('[a]') is not null drop table [a]go create table [a]([ID] int)insert [a]select 1 union allselect 1 union allselect 2 union allselect 3 union allselect null select * from a/*(5 行受影響)ID-----------1123NULL(5 行受影響)*/--> 測試資料：[b]if object_id('[b]') is not null drop table [b]go create table [b]([ID] int)insert [b]select 1 union allselect 2 union allselect 2 union allselect 4 union allselect null select * from b/*(5 行受影響)ID-----------1224NULL(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都不好用）

--> 測試資料：[a]if object_id('[a]') is not null drop table [a]go create table [a]([ID] int)insert [a]select 1 union allselect 1 union allselect 2 union allselect 3 union allselect null select * from a/*(5 行受影響)ID-----------1123NULL(5 行受影響)*/--> 測試資料：[b]if object_id('[b]') is not null drop table [b]go create table [b]([ID] int)insert [b]select 1 union allselect 2 union allselect 2 union allselect 4 union allselect null select * from b/*(5 行受影響)ID-----------1224NULL(5 行受影響)*/--取兩表相同資料並去重select * from a INTERSECT  select * from b/*ID-----------NULL12(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

--> 測試資料：[a]if object_id('[a]') is not null drop table [a]go create table [a]([ID] int)insert [a]select 1 union allselect 1 union allselect 2 union allselect 3 union allselect null select * from a/*(5 行受影響)ID-----------1123NULL(5 行受影響)*/--> 測試資料：[b]if object_id('[b]') is not null drop table [b]go create table [b]([ID] int)insert [b]select 1 union allselect 2 union allselect 2 union allselect 4 union allselect null select * from b/*(5 行受影響)ID-----------1224NULL(5 行受影響)*/select a.*,b.* from a  a LEFT  JOIN b b ON a.id= b.id/*ID          ID----------- -----------1           11           12           22           23           NULLNULL        NULL(6 行受影響)*/

B：right （outer） join:

右外串連(右串連)：結果集既包括串連表的匹配串連行，也包括右串連表的所有行。

--> 測試資料：[a]if object_id('[a]') is not null drop table [a]go create table [a]([ID] int)insert [a]select 1 union allselect 1 union allselect 2 union allselect 3 union allselect null select * from a/*(5 行受影響)ID-----------1123NULL(5 行受影響)*/--> 測試資料：[b]if object_id('[b]') is not null drop table [b]go create table [b]([ID] int)insert [b]select 1 union allselect 2 union allselect 2 union allselect 4 union allselect null select * from b/*(5 行受影響)ID-----------1224NULL(5 行受影響)*/select a.*,b.* from a  a RIGHT  JOIN b b ON a.id= b.id/*ID          ID----------- -----------1           11           12           22           2NULL        4NULL        NULL(6 行受影響)*/

C：full/cross （outer） join：

全外串連：不僅包括符號串連表的匹配行，還包括兩個串連表中的所有記錄。

--> 測試資料：[a]if object_id('[a]') is not null drop table [a]go create table [a]([ID] int)insert [a]select 1 union allselect 1 union allselect 2 union allselect 3 union allselect null select * from a/*(5 行受影響)ID-----------1123NULL(5 行受影響)*/--> 測試資料：[b]if object_id('[b]') is not null drop table [b]go create table [b]([ID] int)insert [b]select 1 union allselect 2 union allselect 2 union allselect 4 union allselect null select * from b/*(5 行受影響)ID-----------1224NULL(5 行受影響)*/select a.*,b.* from a  a FULL  JOIN b b ON a.id= b.id/*ID          ID----------- -----------1           11           12           22           23           NULLNULL        NULLNULL        4NULL        NULL(8 行受影響)*/

13、分組:Group by:
   一張表，一旦分組 完成後，查詢後只能得到組相關的資訊。
    組相關的資訊：（統計資訊） count,sum,max,min,avg  分組的標準)
    在SQLServer中分組時：不能以text,ntext,image類型的欄位作為分組依據

   在selecte統計函數中的欄位，不能和普通的欄位放在一起

14、對資料庫進行操作：

分離資料庫： sp_detach_db;

附加資料庫：sp_attach_db 後接表明，附加需要完整的路徑名

15.如何修改資料庫的名稱:

sp_renamedb 'old_name', 'new_name'