sqlserver提高篇續集

来源:http://www.cnblogs.com/julinhuitianxia/archive/2017/05/17/6868573.html
-Advertisement-
Play Games

七．數據完整性 1.概念：數據一致性和準確性。 分類：域完整性、實體完整性、引用完整性。 解析：域完整性也叫列完整性是指一個數據集對某個列是否有效和確定是否允許為空值。實體完整性也叫行完整性 要求所有的行都有一個唯一的標示符。引用完整性保證主鍵和外鍵之間的關係總是得到維護。 實現：A聲明數據完整性和 ...


七．數據完整性

1.概念：數據一致性和準確性。

分類：域完整性、實體完整性、引用完整性。

解析：域完整性也叫列完整性是指一個數據集對某個列是否有效和確定是否允許為空值。實體完整性也叫行完整性 要求所有的行都有一個唯一的標示符。引用完整性保證主鍵和外鍵之間的關係總是得到維護。

實現：A聲明數據完整性和B過程數據完整性。  A實現包括各種約束、預設和規則。B實現方法包括觸發器和存儲過程等。

2.約束和完整性之間的關係：

完整性類型

約束類型

描述

域完整性

Default

在使用insert語句插入數據時，如果某個列的值沒有給定，則將賦予預設值

Check

對某一列值進行檢查

實體完整性

主鍵

能夠唯一鎖定一行記錄的標示符，該列不能為空。

Unique

防止出現冗餘值，並且確保創建索引，提高性能，允許為空。

引用完整性

外鍵

將一個表中的主鍵拿過來作為該表的列，那麼這一列就是外鍵。

3.管理約束：default、check、主鍵約束、unique、外鍵約束

Default:

1)create table 語句的列的屬性中 create table lin （name varchar(50),age int, gender char(2) default ‘男’）

2)alert table 語句中定義default約束： alter table lin   add constraint 約束名稱 default ‘男’ for gender

Check:

1）create table lin （name varchar(50),age int, gender char(2) default ‘男’ check (gender=’女’ or gender=’男’)）

2）alert table 語句中定義default約束： alter table lin   add constraint 約束名稱 check(gender=’女’ or gender=’男’)

主鍵約束語法:

列級約束：①constraint constraint_name primary key

②primary key

表級約束：③constraint constraint_name primary key(column_list)

④primary key(column_list)

例如：1)create table lin （id int primary key, name varchar(50),age int, gender char(2) default ‘男’ check (gender=’女’ or gender=’男’)）

2)create table lin （id int, name varchar(50), primary key(id,name),age int, gender char(2) default ‘男’ check (gender=’女’ or gender=’男’)）

Unique約束：

語法：①constraint constraint_name unique    ②unique   ③constraint constraint_name unique(column_list)④unique(column_list)

外鍵約束：

1）在create table 語句中定義外鍵約束：create table A (id int primary key,…) create table B (id int primary key, aid int foreign key references A(id))

2）在alter table 語句中定義外鍵約束：create table A (id int primary key,…) create table B (id int primary key, aid int not null)

Alter table B add constraint 約束名稱 foreign key(id) references A(id) on delete cascade

3）刪除約束：alert table B drop constraint 約束名稱

4.禁止約束: 就是不讓約束對某個列生效。

比如：Alter table with nocheck B add constraint 約束名稱 check(gender=’女’ or gender=’男’)不進行性別檢查。

八．視圖

概念：從一個或幾個基本表導出的表。資料庫中只存放視圖的定義，而不存儲視圖對應的數據。視圖是一種邏輯對象，是虛擬表。除非是索引視圖，否則視圖不占物理存儲空間。

使用視圖優點：1）集中用戶使用的數據2）掩碼資料庫的複雜性:視圖把資料庫設計的複雜性與用戶的使用方式屏蔽開了。為開發人員提供了一種資料庫的設計而不影響用戶使用的能力。3）簡化用戶許可權的管理4）方便重新組織數據為其他應用程式提供輸出數據集合。

1.創建視圖   create view view_name[with encryption]  as  select_statement

如果希望加密視圖定義文本，可以使用with encryption子句。

2.修改視圖註意：

1）不能影響兩個或兩個以上的基表。2）某些列不能修改。3）如果影響到表中那些沒有預設值的列，就可能引起錯誤。4）如果定義了with check option選項，那麼系統驗證所修改的數據。With check option 選項強制對視圖所有修改語句必須滿足定義視圖所用的select語句的標準。

九．存儲過程、觸發器和函數

先看概念，再看實例！

1.存儲過程：是一個可以重用的代碼模塊，可以高效的完成指定的操作。

  優點：1）在伺服器上已註冊。2）安全性高防止sql嵌入式攻擊3）可以模塊化設計提高設計效率4）存儲過程是一組命名代碼，允許延遲綁定。5)減少網路通信流量。

  類型：用戶自定義、擴展存儲過程、系統存儲過程。

  語法：create procedure procedure_name  parameter_name data_type,…

        [With procedure_option] as sql_statement

  創建：create procedure pro_lin  as select * from lin;

  執行：execute/exec  存儲過程名稱。

  接收結果： output

  修改：alter procedure語句。

  刪除：drop procedure語句。

  執行過程（第一次）：語法分析階段、解析階段、編譯階段、執行階段。

  查看存儲過程信息：sp_helptext、 objectdefinition元數據函數。

  隱藏定義文本：with encryption

2.觸發器：

概念：事件-條件-動作規則。當特定的系統事件對一個表的增刪改查操作發生時，對規則的條件進行檢查，如果條件成立則執行規則中的動作，否則不執行。

    分類：DML觸發器和DDL觸發器。DML觸發器分為：INSERT類型、UPDATE類型、DELETE類型。DDL觸發器分為（事件類型）：CREATE、ALTER、DROP、GRANT、DENY、REVOKE。

創建語法：DML觸發器  create trigger trigger_name on table_name_or_view_name [with encryption] {for|after|instead of}{[delete],[insert],[update]} as sql_statement

DDL觸發器   create trigger trigger_name on {all servler| database}[with encryption] {for|after}{事件類型} as sql_statement

 

    DML觸發器工作原理（揀重點說）： 當INSERT觸發器觸發時，新的紀錄增加到觸發器表中和inserted表中。當DELETE觸發器觸發時，被刪除的紀錄放在一個特殊的deleted表中。Deleted表是一個邏輯表，用來保存已經從表中刪除的紀錄。當update觸發器觸發時，表中原來的紀錄移動到deleted表中，修改過的紀錄插入到inserted表中。

3.函數

  分類：標量函數、內聯表值函數、多語句表值函數、

創建：標量函數create function function_name(@parametername_list) as begin Function_body  return 返回的語句   end

    內聯表值函數  create function function_name(@parametername_list) return TABLE as  return (查詢的語句 )

多語句表值函數  create function function_name (@parametername_list)

Returns 臨時變數 table (表的定義) as begin   functin_body  return end

4.函數練習實例：

--創建mydb資料庫，並創建 學生表（學號、姓名、性別、年齡、專業）、課程表（課程編號、課程名稱、先行課號、學分）和選課表（學號、課程號、成績）

 

create database mydb

go

use mydb

go

 

CREATE TABLE Student         

(

Sno CHAR(9),

Sname CHAR(20),

Sgender CHAR(2),

Sage SMALLINT,

Sdept  CHAR(20)

)

 

insert into Student values('200215121','李勇','男',20,'CS')

insert into Student values('200215122','劉晨','女',19,'CS')

insert into Student values('200215123','王敏','女',18,'MA')

insert into Student values('200215125','張立','男',19,'IS')

 

CREATE TABLE Course

(

Cno CHAR(4),

Cname  CHAR(40),

Cpno CHAR(4),

Ccredit SMALLINT

)

 

insert into Course values('1','資料庫','5',4)

insert into Course values('2','數學',NULL,2)

insert into Course values('3','信息系統','1',4)

insert into Course values('4','操作系統','6',3)

insert into Course values('5','數據結構','7',4)

insert into Course values('6','數據處理',NULL,2)

insert into Course values('7','PASCAL語言','6',4)

 

 

CREATE TABLE SC

(

Sno CHAR(9),

Cno CHAR(4),

Grade SMALLINT

)

 

insert into SC values('200215121','1',92)

insert into SC values('200215121','2',85)

insert into SC values('200215121','3',88)

insert into SC values('200215122','2',90)

insert into SC values('200215123','3',80)

 

--1.創建標量函數，使用該函數。當用戶輸入課程名稱時，返回這門課程的最低分

 

create function retGrade(@courceName varchar)

returns int

begin 

end

 

select * from sc;

select * from course;

 

--2.創建內聯表值函數，當用戶輸入學分，返回所有學分為該學分的課程的信息

create function kc (@xf smallint)

returns table

as

return(select * from course where ccredit=@xf)

 

select * from kc(4);

select * from student;

 

--3.創建多語句表值函數，用戶輸入專業名稱時，返回所有該專業同學的信息

create function stu(@zy char(20))

returns @k table(sno char(20),sname char(20))

as

begin

    insert @k

    select sno,sname from student where sdept=@zy

    return

end

 

select * from stu('cs');

 

十．備份和還原

http://os.51cto.com/art/201305/392742.htm詳解SQL Server 2008數據備份與還原

1.三種資料庫恢復模式：完整恢復模式、大容量日誌記錄模式、簡單恢復模式。

2.四種備份方法：完全資料庫備份、增量資料庫備份、事務日誌備份、資料庫文件或文件組備份。

3.還原操作：restore database語句.

十一.事務

1.概念：用戶定義的一個資料庫操作序列，這些操作要麼全做，要麼全不做，是一個不可分割的工作單位。

2.特性：ACID即原子性（atomicity）、一致性（consistency）、隔離性（Isolation）、持續性（Durability）。

原子性：事務是資料庫的邏輯單元，事務中包括的諸多操作要麼全做，要麼不全做。

一致性：當事務完成時，所有的數據都必須是一致的狀態。

隔離性：一個事務的執行不會被其他的事務干擾。

持續性：一個事務一旦提交，他對資料庫中數據的改變就是永久的。

3.事務的工作原理：涉及三點：事務日誌、檢查點機制、檢查點周期性檢查事務日誌。

4.管理事務：begin transaction、begin distributed transaction、commit transaction、rollback transaction、save transaction、set implicit_transaction

一個使用實例：

use A

create table emp(

eid int,

ename varchar(50)

)

go

use A

begin transaction

insert into emp values(1,'hello')

save transaction a

insert into emp values(2,'hei')

save transaction b

insert into emp values(3,'hehe')

rollback transaction b

insert into emp values(4,'haha')

rollback transaction a

commit transaction

select * from emp;

go

5.使用鎖

鎖是防止其他事務訪問指定資源的手段，也是實現併發控制的方法。

1）.三種錯誤現象：臟讀、不可重覆讀、幻讀。

臟讀：一個事務讀到的數據正是另一個事務update更新的數據。

不可重覆讀：在一個事務內，多次讀到同一個數據卻得到不同的結果。

幻讀：一個事務讀到的數據正是另一個事務整改插入insert的數據。

2）.封鎖：實現併發控制的一個重要技術。

分類：排它鎖又稱寫鎖。若事務T對數據對象A加上X鎖，則只允許T讀取和修改A，其他任何事務都不能在對A加任何其他類型的鎖，直到T釋放A上的鎖為止。   共用鎖又叫讀鎖。若事務T對數據對象A加上S鎖，則只允許T讀取但不能修改A，其他事務只能在對A加S鎖，不能加X鎖，直到T釋放A上的S鎖為止。

3）.一級封鎖協議：事務T在修改數據R之前必須先對其加X鎖，直到事務結束才釋放。二級封鎖協議：在一級封鎖協議基礎上增加事務T讀取數據R之前必須先對起加S鎖，讀完後即可釋放S鎖。三級封鎖協議：在一級封鎖協議基礎上增加事務T讀取數據R之前必須先對起加S鎖，直到事務結束才釋放S鎖。

4.活鎖與死鎖

活鎖：一個事務T是有可能被執行的，但它卻處於可能等待狀態。（就是要死不死的）

死鎖：你等待我的資源，我也在等待你的資源，我們兩個都很強勢，不鬆口，就形成了僵持局面，不能結束，於是形成死鎖。

解除死鎖：超時法：超過了規定的等待時間，就認為發生了死鎖，系統就會選擇一個代價較小的事務，將其撤銷，釋放此事務持有的所有鎖。等待圖法：

                                   

5.併發調度的可串列性

概念：多個事務的併發執行時正確的，當且僅當其結果與按某一次序串列地執行這些事務時的結果相同。

6.兩段鎖的協議：所有事務必須分兩個階段對數據項加鎖和解鎖。

7.五種事務隔離等級：①read uncommitted會發生臟讀②read committed可能產生不可重覆讀和幻讀數據 ③repeatable read 可能發生幻讀④snapshot指定任何讀取數據都將是已經存在的數據⑤ serializable等級最高，可以自己設置等待訪問時間。

十二.自動化管理任務

  1. 自動完成任務：一類是執行正常調度的任務；二類識別和回應可能遇到的問題的任務。這些任務是依靠自動化組件完成的。
  2. 自動化組件包括：Windows EventLog、MSSQLServer、SQLServer代理。
  3. 警報：用來回應sqlserver系統發生的事件。警報由事件觸發，觸發的結果可以是執行作業，也可以是同志操作員。

 

 

                            圖1 作業、警報、事件之間的關係

  1. 定義作業的用戶不一定是作業的所有者。註意這裡是所有者是login賬戶，不是user賬戶。

      2.警報的執行過程：

     

終於總結完了。。。沒有失信於昨天。另外有需要的自己拿著用！


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • ionic2 使用 cordova 打包成安卓apk
    準備工作： 1.下載java sdk 配置好java 環境 2.下載android sdk 跟 android studio 並配置好環境 1.查看平臺支持，添加android平臺 首先命令提示符進入到所在項目文件夾下 查看系統所支持 的平臺 因為我已經配置好環境了，所以在這裡我們能看到 andro ...
  • Node.js~ioredis處理耗時請求時連接數瀑增
    回到目錄 關於redis連接數過高的解釋 對於node.js開發環境里，使用傳統的redis或者使用ioredis都是不錯的選擇，而在處理大數據請求程中，偶爾出現了連接池（ redis服務端的最大可用連接數，預設為1萬）不夠用的情況，一般的提示如下： 在redis-cli上輸入info命令也可以進行 ...
  • SQL SERVER大話存儲結構（3）_數據行的行結構
    一行數據是如何來存儲的呢？ 變長列與定長列，NULL與NOT NULL，實際是如何整理存放到 8k的數據頁上呢？ 對錶格進行增減列，修改長度，添加預設值等DDL SQL，對行存儲結構又會有怎麼樣的影響呢？ 什麼是大對象，什麼是行溢出，存儲引擎是如何處理它們呢？ 如果轉載，請註明博文來源： www.c ...
  • MySQL:日期類型
    1. datetime（年月日時分秒） 格式：‘YYY-MM-DD HH:MM:SS’。 占用：8位元組 範圍：1000-01-01 00：00：00 到 9999-12-31 23：59：59。 tip:可以接收任意分隔符的日期，主要是判斷日期是否正確，是否在正確範圍內。 但是，不通用的分隔符可讀性 ...
  • mongoDB學習手記2--建庫、刪庫、插入、更新
    上一篇 講了在windows系統下的安裝和啟動，本文主要講怎麼建庫、刪庫、插入、更新 在講之前我們說一下mongoDB的一些基本概念，我們對比關係型資料庫能更直觀的理解 1.我們首先要啟動mongoDB伺服器，怎麼啟動看上一篇 2.打開shell，我們到mongoDB的安裝目錄下找到bin文件夾，進 ...
  • SQL Server on Ubuntu——Ubuntu上的SQL Server（全截圖）
    本文從零開始一步一步介紹如何在Ubuntu上搭建SQL Server 2017，包括安裝系統、安裝SQL等相關步驟和方法（僅供測試學習之用，基礎篇）。 一． 創建Ubuntu系統（Create Ubuntu System） 1. 前提準備 由於本文主要研究SQL Server 2017在Linux上 ...
  • MySQL中EXPLAIN解釋命令詳解
    MySQL中的explain命令顯示了mysql如何使用索引來處理select語句以及連接表。explain顯示的信息可以幫助選擇更好的索引和寫出更優化的查詢語句。 1.EXPLAIN的使用方法：在select語句前加上explain就可以了。 如：explain select surname,fi ...
  • MySQL5.7多源複製
    MySQL5.7開始支持多源複製，也就是多主一從的複製架構： 使用多源複製的考慮： 1、災備作用：將各個庫彙總在一起，就算是其他庫都掛了（整個機房都無法連接了），還有最後一個救命稻草； 2、備份：直接在這個從庫中做備份，不影響線上的資料庫； 3、減少成本：不需要每個庫都做一個實例，也減少了DBA的維 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類