Sql Server資料庫設計進階查詢

標籤：

-------------------------------------第一章  資料庫的設計-------------------------------------

軟體開發週期：
     (1)需求分析
     (2)概要設計
     (3)詳細設計
     (4)編碼
     (5)整合測試
     (6)上線運行
     (7)後期維護

1,資料庫設計步驟：
     (1)收集資訊
     (2)標識對象(確定資料庫中有哪些表)
     (3)標識對象的屬性(確定每個表中的例)
     (4)標識對象的關係(兩個表的關係)
    4種關係:
     1對1
     1對多
     多對1
     多對多


2，E-R 實體關聯圖
     矩形   表示，實體集（即表）
     橢圓   表示，屬性（即列）
     菱形   表示，關係集（即關係）

3，資料庫的三大範式：(在實際工作中不一定遵循)
     衡量資料庫設計是否優秀的標準

     第一範式：確保每一列的資料不可再分
     第二範式：要求每個表只描述一件事
     第三範式：要求每一列與主鍵有直接關係，即確保表中每一列都直接依賴於主鍵






--------------------------------------第二章  資料庫的實現------------------------------------------
 

1，資料庫操作
     (1)使用語句建立資料庫
          create database  資料庫名
          on primary{     --設定資料庫檔案
           //邏輯名稱
           name=‘邏輯名稱‘,      --不用帶尾碼.mdf
          //設定初始大小
           size=3mb,             --sql server2014最小不可小於5mb
          //增量
          filegrowth=1mb,
          //路徑包括檔案，即實體名稱
          filename=‘路徑‘,
          //增長的最大值
          maxsize=‘100mb‘
             }
             log on{     --設定事物記錄檔
          //與上相同
          }

          例：
          create database bbsDB
          on primary
          (
          name=‘bbsDB‘,size=3mb,filegrowth=1mb,
          filename=‘E:\SQL Sever\SQL Server 進階編程\資料庫\bbsDB.mdf‘
          )
          log on
          (
          name=‘bbsDB_log‘,size=1mb,filegrowth=1mb,
          filename=‘E:\SQL Sever\SQL Server 進階編程\資料庫\bbsDB_log.ldf‘
          )






     (2)刪除資料庫
           drop database 資料庫名


     (3)判斷資料庫是否存在
          1>sysdatabases表:儲存在master資料庫中，這張表儲存了所有資料庫的資訊
            sysobjects表:資料庫的系統資料表

          例:  判斷資料庫是否存在，如果存在輸出a,否則輸出b
          if(exists(select * from sysdatabases  where name=‘students‘))
          begin
          select ‘a‘
          end
          else
          begin
          select ‘b‘
          end


2,表操作
     (1)建立表
          identity       識別欄位
          primary  key   主鍵
          not null       不為空白

          create table 表名
          (
            id  int not null primary key  identity,
            name varchar(20)  not null,
           ..................................
          )

     (2)刪除表
          drop table 表名






3,約束
     (1)主鍵約束   primary key
     (2)檢查約束   check
     (3)預設值約束  default
     (4)主外鍵約束   foreign key

     1>添加約束：

          添加主鍵約束：
               alter table 表名
               add constraint 主鍵約束名(規範"pk_"開頭)    
               primary key(列名)

          添加檢查約束：
               alter table 表名
               add constraint 檢查約束名(規範"ck_"開頭)  check(運算式);

          添加預設值約束
               alter table 表名
               add constraint 預設值約束名(規範"df_"開頭)
               default(‘預設值‘)   for 列名


          添加主外鍵約束：
               alter table 表名(外鍵表)
               add constraint 外鍵約束名(規範"fk_"開頭)
               foreign key(外鍵名)
               references   主鍵表名 (主鍵列名)

     註：
       也可同時給一個表添加多個約束


     2>刪除約束
          alter table 表名
          drip constraint 約束名





4，使用命令建立使用者和刪除使用者的6步
     (1)建立：
          第一步:建立sqlserver登入使用者
               exec addlogin ‘登入名稱‘,‘密碼‘
          第二部：建立對應資料庫的使用者
               exec grantdbaccess ‘登入名稱‘,‘使用者名稱‘
          第三步：添加使用者對指定表的操作許可權
               grant insert,upadat,select  on  表名  to  使用者名稱
     (2)刪除
          第一步：撤銷許可權
               revoke  許可權  on  表名  from  使用者名稱
          第二步:刪除資料庫使用者
               exec sp_dropuser  ‘使用者名稱‘
          第三步：刪除登入帳號
               exec sp_droplogin  ‘登入名稱‘


                


---------------------------------------------第三章 T-SQL編程-------------------------------------------------

1，局部變數(自訂變數)
     declare @變數名  資料類型     。。。。後面可定義多個

2，變數的賦值
     (1)set賦值
          set @變數=值
     (2)select賦值
          select @變數=列名 from 表名
     註：這種賦值方式只適合使用者查到的資料只有一條時，如果查詢到的結果為多條語句，則給變數賦的值是最後一條語句的值

3，輸出變數
     (1)print  變數：以文本的方式輸出
     (2)select 變數：以表元素的方式輸出


4,全域變數
     常用：
          @@error           擷取上一條語句的錯誤號碼
          @@identity       擷取最後一次產生的標識值
          @@rowcount        受上一條語句所受影響的行數
          不常用：
          @@language       當前使用的語言名稱
          @@max_connections   可以建立的同時串連的最大數目
          @@servername         本機伺服器的名稱
          @@servicname         本機電腦上的SQL服務的名稱
          @@timeticks           當前電腦上每刻度的微秒數
          @@version            當前sql server的版本號碼



5,邏輯控制語句
     if(條件)
     begin

     end
     else
     begin
     end



     例：
     declare @username varchar(10)
     set @username=‘許超‘
     if(@username=‘超‘)
     begin
     print ‘呵呵‘
     end
     else
     begin
     print ‘嗚嗚。。。‘
     end





6,while迴圈語句
     while
     begin

     end

     例：
     declare @i int
     set @i=0;
     while(@i<10)
     begin
     print @i
     set @[email protected]+1
     end


7，case ...end 多分支結構
     case
      when  條件1   then   結果1
      when  條件2   then   結果2

     else
       其他情況
     end
 
     註：case...end  不能單獨使用必須嵌套在sel語句中使用



8，批出理   
     關鍵字:go


---------------------------------------第四章 進階查詢-------------------------------------------

1,子查詢的特點：
     (1)當子查詢返回的值只有一個時可以使用比較子,當子查詢的值不只一個時要用in 或not in
     (2)子查詢中查詢的列只能是一列
     (3)子查詢中的類型要和條件的類型一致
     (4)子查詢可以應用在 insert,update,select,delete  語句中
     (5)如果子查詢中使用了order by 就必須包含top關鍵字

2,簡單子查詢
     例:select * from stuinfo where scoreid=(select id from stuscore where score=60)

3,in和not in 子查詢
     例：select * from stuinfo where scoreid in(select id from stuscore where score>60)
     例：select * from stuinfo where scoreid not in(select id from stuscore where score>60)

4，exists和not exists
     檢查查詢的資料是否存在，存在返回true,不存在返回false
     用法:
          exists(語句)
          not exists(語句)



                        

-------------------------------------------第五章 事物、索引和視圖-------------------------------------


1,事物
     (1)事物作為一個整體，要麼都執行，要麼都不執行。
          1>開始事物
               begin tran   (或begin transaction)

          2>提交事物
               commit tran
          3>復原事物
               rollback tran

     (2)事物的屬性:
               原子性:是一個整體，不可再分，要麼都執行，要麼都不執行
               一致性:事物開始之前和事物結束之後，資料的狀態保持一致
               隔離性:對同一個資料的操作，事物之間是彼此隔離的，要麼A先執行，要麼B先執行
               永久性:事物對資料的影響是永久的


2，索引
     (1)分類:
          1>叢集索引:(主鍵索引屬於叢集索引)
               特點：
               *每個表只能有一個叢集索引
               *索引頁的順序和資料頁的資料是一致的
               *建立主鍵會自動建立叢集索引
               *當查詢範圍值時特別有效

          2>非叢集索引
               每個表最多可健249個，因為沒個表最多可以有249個列
               選擇索引列的標準：
               *頻繁作為查詢條件的列
               *唯一值較多的列
               *不經常被更新的列
     (2)使用SQL語句建立索引
          非叢集索引：
               create index  索引名稱  on  表名(列名1，列名2，.....)
          叢集索引:
               create clustered index  索引名稱   on    表名(列名1，列名2，.....)



3，視圖
     建立視圖：
          create view 名稱(一般以vi_開頭)
          as
          內容

     使用視圖:
          select * from  視圖名



--------------------------------------------第6章 預存程序------------------------------------------------


(一)，預存程序
1,預存程序的優點：
     (1)執行速度更快
     (2)允許模組化程式設計
     (3)提高系統安全性
     (4)減少網路流通量

2,常見的系統預存程序

     sp_database            顯示當前系統中的所有資料庫資訊
     sp_helpdb              顯示所有或指定資料庫的詳細資料
     sp_renamedb            修改資料庫名稱             例：sp_renamedb  ‘舊名‘,‘新名‘   
     sp_tables                顯示當前資料庫下所有表的資訊         例：sp_tables 或 sp_tables ‘表名‘
     sp_columns              顯示某個表列的資訊        例:sp_columes ‘表名‘
     sp_help                查看某個表的所有資訊        例：sp_help  或 sp_help ‘表名‘
     sp_helpconstraint      查看某個表的約束         例：sp_helpconstraint ‘表名‘
     sp_helpindex             查看某個表的索引            例:sp_helpindex ‘表名‘
     sp_stored_procedures      顯示當前資料庫下的所有預存程序    例:sp_stored_procedures
     sp_password            修改當前登入使用者的密碼        例:sp_password ‘舊密碼‘,‘新密碼‘
     sp_helptext            顯示預設值、未加密的預存程序、使用者定義的預存程序、觸發器或試圖的實際文本        例:sp_helptext


3,自訂預存程序(一般以proc_開頭)
     文法:
          create procedure  名稱
          參數1,參數2,......輸出參數 output
          as
          內容


(二),觸發器
     在執行增刪改查時會自動觸發
1，優點：
     (1)觸發器會自動執行
     (2)可以對錶進行級聯修改
     (3)可以實現強制限制式

2，兩個重要的表
     inserted表:存在記憶體中的暫存資料表，存放最新插入的資料，修改後的資料在inserted表中
     deleted表:臨時儲存了刪除的記錄，存放修改前的資料

3，觸發器的使用
     插入觸發器:insert
     修改觸發器:update

     例:
     插入的觸發器：
          create trigger  觸發器名  on  表名
          for insert
          as
          declare @id int,@count int
          select @id=scoreid from inserted
          update stuscore set score=20 where [email protected]

     修改的觸發器：
          create trigger 觸發器名 on 表名
          for update
          as
          declare @id int
          select @id=scoreid from deleted
          update stuscore set score=50 where [email protected]



     刪除存在的觸發器:
          drop trigger 觸發器名

Sql Server資料庫設計進階查詢