SQL Server 2005筆記總結

標籤：sql

SQL分類

DDL  data definition language

        create alter  drop

DML  data manipulation language

         insert  update  delete  select

DCL  data control language

         grant  revoke  deny

資料庫的建立和管理

檔案   資料檔案(主要資料檔案.mdf  輔助資料檔案.ndf)

          記錄檔 .ldf

檔案組 邏輯結構  包含資料檔案

建立資料庫

create database 資料庫名

on primary

(name=,filename=,size=,maxsize=,filegrowth=),

filegroup 檔案組名

(name=,filename=,size=,maxsize=,filegrowth=)

log on

(name=,filename=,size=,maxsize=,filegrowth=),

(name=,filename=,size=,maxsize=,filegrowth=)

修改

 alter database 資料庫名

|add filegroup 檔案組名

|add file() to filegroup 檔案組名

|add log file()

|modify name=新資料庫名

|modify file(name=,newname=,size=,maxsize=,filegrowth=)

|modify filegroup 檔案組名 default

|remove file

|set 資料庫選項

刪除

drop database 資料庫名

資料庫收縮

dbcc shrinkfile

dbcc shrinkdatabase

資料庫資訊查看

sp_helpdb

dbcc sqlperf

sp_spaceused

dbcc checkdb

系統資料類型

整型：bigint   int  smallint  tinyint

精確數值：decimal(p,s)  numeric(p,s)

近似數值：float real

貨幣：money  smallmoney

日期：datetime  smalldatetime

   

     --SQL2008

        date  time  datetime2  datetimeoffset

字元：char(n)  varchar(n)  varchar(max)

         nchar(n)  nvarchar(n)  nvarchar(max)

         bit  uniqueidentifier  table

         ......

表的建立

create table 表名

(列名 資料類型,

 列名 資料類型  not null,

 列名 資料類型  identity(seed,increment))

表的修改

alter table 表名

|add 列名 資料類型 屬性

|alter column 列名 新資料類型|舊資料類型 屬性

|drop column 列名 資料類型

表的刪除

drop table 表名

DML

insert

insert into 表名(列名列表) values(對應值)

注意：字元和日期資料加單引號

insert into 表名(列名列表) select語句

update

update 表名 set 列名=值 where 條件

delete

delete from 表名 where 條件

select

語句 子句 

select：結果中的列

    *

    列名

    distinct

    top(n) [percent]

    +

    函數

    算術運算子

    with ties

    列別名    

          列名 as 列別名

          列名 列別名

          列別名=列名

into：將查詢結果放入新表

from：資料來源（具體表名；CTE；derived table）

where：條件 對返回的資料行進行限定

     邏輯運算子  and  not  or

     比較子  >  <  >=   <=  <>  !=    =

     算術運算子

     between...and...：給定取值範圍，包括邊界

     in（值列表）：取給定值 

     like：字串的模糊查詢

          萬用字元： %：任意長度任一字元

                   _： 任意單個字元

                  []：取給定的任意一個

                [^] ：除了給定的字元，任意取一個

   is null：空值判斷

   exists：判斷存在性

group by：分組匯總+彙總函式

having：對分組後的資料進行篩選

order by：排序

    升序asc(預設)  降序 desc 

    可以按多個列排序  null作為最小值  

join：多表串連          

where   select 列名 from 表1,表2  where 表1.列=表2.列  and  條件

join       select 列名 from 表1 *JOIN* 表2 on 表1.列=表2.列   where 條件

內串連：[inner] join           串連欄位的值在兩張表中相同的資料

左外連接：left [outer] join    串連欄位的值在兩張表中相同的資料，串連欄位的值在左表有在右表沒有的資料

右外串連：right [outer] join   串連欄位的值在兩張表中相同的資料，串連欄位的值在右表有在左表沒有的資料

全連結：full [outer] join      串連欄位的值在兩張表中相同的資料，串連欄位的值在左表有在右表沒有的資料，串連欄位的值在右表有在左表沒有的資料

  stu                  exam

id name          id    eid   score

1  a              1    2072  100

2  b              2    2072   80

3  c              3    2072   60

4  d             null  2072   70

交叉串連

笛卡爾積  m*n

select 列名 from 表1 cross join 表2

自串連

union

行疊加

select語句  *union* select語句

union//union all//intersect//except

子查詢--可以轉換為多表

select 欄位 from 表1 where 欄位 in(select語句)

資料完整性

實體完整性：行唯一

值域完整性：對欄位的取值範圍或格式進行限制

參考完整性：維護表之間的資料一致性

實現：規則  預設  約束

約束

主鍵約束   primary key

         一張表只有一個主鍵約束，可以建立在多個列上，建立主鍵約束的欄位取值唯一，不能為NULL，建立主鍵約束會自動建立唯一叢集索引

唯一約束   unique

         一張表可以有多個唯一約束，可以建立在多個列上，建立唯一約束的欄位可以為NULL，取值唯一(包括NULL)，建立唯一約束會自動建立唯一非叢集索引

檢查約束  check

         對欄位的取值範圍或格式進行限制

預設約束  default

         如果某列定義了預設約束，插入資料時，不給值，自動填滿預設值

外鍵約束 foreign key

         被參照表的被參照列要定義主鍵約束或唯一約束，維護表之間的資料一致性

建立表的同時，建立約束************************************

 create table 表名

(列名 資料類型,

 列名 資料類型 not null,

 列名 資料類型 primary key,

 列名 資料類型 unique,

 列名 資料類型 check(運算式)

 列名 資料類型 default 預設值，

 列名 資料類型 references 被參照表(被參照列))

表建立成功後添加約束***************************************

                                                         primary key(主鍵欄位)

                                                         unique(唯一欄位)

alter table 表名 add constraint 約束名  check(運算式)

                                                         default 預設值 for 列名

                                                         foreign key(參照欄位) references 被參照表(被參照列)

視圖

分類：普通視圖   分區視圖   索引檢視表

     

資料庫中只有視圖定義，不存放資料

建立視圖

    create view 視圖名

    as

    select語句

視圖選項

   create view 視圖名

   with encryption | schemabinding

   as

   select語句

   with check option

備份還原

備份目標

     備份裝置：實現建立好，通過命令和圖形介面均可查看備份內容

     備份檔案：建立的同時建立，只能通過命令查看

故障恢複模型

大容量日誌：對大批量資料操作只記錄結果不記錄過程

簡單：不能進行記錄備份

完整：

備份類型

完整備份

backup database 資料庫名 to disk=‘‘       

backup database 資料庫名 to 備份裝置名

差異備份

backup database 資料庫名 to disk=‘‘ with differential

backup database 資料庫名 to 備份裝置名 with differential

記錄備份

backup log 資料庫名 to disk=‘‘ 

backup log 資料庫名 to 備份裝置名 

檔案和檔案組備份

backup database 資料庫名 file= to disk=‘‘ 

backup database 資料庫名 filegroup= to 備份裝置名 

尾記錄備份

backup log 資料庫名 to disk=‘‘ with no_truncate 

backup log 資料庫名 to 備份裝置名 with no_truncate 

本文出自 “技術員修鍊手冊” 部落格，請務必保留此出處http://juispan.blog.51cto.com/943137/1949879

SQL Server 2005筆記總結