【正文】 SQL SERVER高級(jí)培訓(xùn) 主講人 :鄭維勇 數(shù)據(jù)庫(kù)的概述 ? 數(shù)據(jù)庫(kù)對(duì)象 表、視圖、存儲(chǔ)過(guò)程、觸發(fā)器、默認(rèn)值、規(guī)則、 索引、用戶。 ? SQL Server數(shù)據(jù)庫(kù)的版本 ?個(gè)人版 ?標(biāo)準(zhǔn)版 ?開發(fā)版 ?企業(yè)版 ? TransactSQL語(yǔ)言主要的 4個(gè)組成部份 ?數(shù)據(jù)定義語(yǔ)言 DDL ?數(shù)據(jù)操作語(yǔ)言 DML ?數(shù)據(jù)控制語(yǔ)言 DCL ?共它語(yǔ)言元素 TransactSQL ? 數(shù)據(jù)定義語(yǔ)言 DDL 用來(lái)創(chuàng)建數(shù)據(jù)庫(kù)、數(shù)據(jù)庫(kù)對(duì)象和定義列。包括 ： CREATE,ALTER,DROP。 ? 數(shù)據(jù)操縱語(yǔ)言 DML 用來(lái)操縱數(shù)據(jù)庫(kù)的命令。包括 ： SELECT,INSERT,UPDATE,DELET。 ? 數(shù)據(jù)控制語(yǔ)言 DCL 用來(lái)控制數(shù)據(jù)庫(kù)組件的存取許可、存取權(quán)限等的命令 包括 ： GRANT,DENY,REVOKE。 管理 SQL Server服務(wù) ? 啟動(dòng) SQL Server服務(wù) Net start mssqlserver ? 暫停 SQL Server服務(wù) Net pause mssqlserver ? 停止 SQL Server服務(wù) Net stop mssqlserver SQL Server服務(wù) SQL Server 2022的各種服務(wù)和功能說(shuō)明 服務(wù)器名稱 文件名 服務(wù)說(shuō)明 MSSQLSERVER 它負(fù)責(zé)數(shù)據(jù)存取、安全配置、事務(wù)管理等 (Binn\) SQLSERVERAGENT 負(fù)責(zé)調(diào)度定期執(zhí)行的活動(dòng)以及通知系統(tǒng)管理員服務(wù)器所發(fā)生的問(wèn)題(Binn\) Microsoft Search 提供數(shù)據(jù)庫(kù)內(nèi)全文檢索的功能 Distributed Transaction Coordinator(MSDTC) 是一個(gè)事務(wù)管理器，它允許客戶端應(yīng)用程序在一個(gè)事務(wù)中包含多個(gè)不同的數(shù)據(jù)源 SQL Server服務(wù)器的注冊(cè)與刪除 通過(guò)控制臺(tái)目錄 使用 Transactsql sp_addserver [@server=] ?server? [,[@local=] ?local?] [,[@duplicate_ok=]? duplicate_ok?] Eg sp_addserver local 配置鏈接服務(wù)器 在 SQL Server 2022中 ,允許 SQL Server用戶訪問(wèn)基于 SQL Server的服務(wù)器 以外的數(shù)據(jù)。通過(guò)配置鏈接服務(wù)器可以實(shí)現(xiàn)訪問(wèn)其他服務(wù)器上的 OLE DB 數(shù)據(jù)源。鏈接服務(wù)器是對(duì) OLE DB數(shù)據(jù)源的抽象，是 OLE DB提供程序和 連接屬性的來(lái)源。 在鏈接服務(wù)器的邊接過(guò)程中，為了使該連接有效，需要在鏈接服務(wù)器 之間創(chuàng)建登錄映射。 鏈接服務(wù)器登錄映射可以通過(guò)系統(tǒng)存儲(chǔ)過(guò)程 sp_addlinkedsrvlogin添加，相應(yīng)地， 鏈接服務(wù)器登錄映射可以通過(guò)系統(tǒng)存儲(chǔ)過(guò)程 sp_droplinkedsrvlogin刪除。 配置鏈接服務(wù)器 添加鍵接服務(wù)器 Sp_addlinkedserver [@server=] ?server? [,[@srvproduct=]? product_name?] [,[@provider=]?provider_name?] [,[@datasrc=]?data_source?] [,[@location=]?location?] [,[@provstr=]?provider_string?] [,[@catalog=]?catalog?] Eg: exec sp_addlinkedserver @server=39。exam239。, @provider=39。39。, @srvproduct=39。39。,@datasrc=39。d:\39。 sp_addlinkedserver 39。鏈接服務(wù)器名 39。, 39。產(chǎn)品名 39。, 39。39。, 39。Access數(shù)據(jù)庫(kù)路徑及文件名 39。 sp_addlinkedserver 39。Anna39。, 39。Access 202239。, 39。39。, 39。D:\39。 配置鏈接服務(wù)器 數(shù)據(jù)源 提供者名稱 Product_name Provider_name Data_source SQL Server SQL Server OLE DB Provider SQL Server SQL Server SQL Server主機(jī)網(wǎng)絡(luò)名稱 Access Microsoft OLE DB provider for Jet 任意名稱 E Access文件路徑和文件名稱 Excel Microsoft OLE DB provider for Jet 任意名稱 E Excel文件路徑和文件名稱 配置鏈接服務(wù)器 創(chuàng)建鏈接服務(wù)器登錄 通過(guò)系統(tǒng)存儲(chǔ)過(guò)程 sp_addlinkedsrvlogin可以創(chuàng)建或更新本地 SQL Server 實(shí)例上的登錄與鏈接服務(wù)器上遠(yuǎn)程登錄之間的映射。其語(yǔ)法為： Sp_addlinkedsrvlogin [@rmtsrvname=]?rmtsrvname? [,[@useself=] ?useself?] [,[@location=]?location?] [,[@rmtuser=]?rmtuser?] [,[@rmtpassword=]?rmtpassword?] 其中，參數(shù) : (1)[@rmtsrvname=] ?rmtsrvname?:是應(yīng)用登錄映射的鏈接服務(wù)器名稱． (2)[@useself=]‘ useself’ :決定用于連接到遠(yuǎn)程服務(wù)器的登錄名稱，默認(rèn) 設(shè)置為 SQL Server驗(yàn)證的登錄使用其自己的憑據(jù)以連接 到 rmtsrvname,而忽略 rmtuser和 rmtpassword參數(shù)． (3)[@locallogin=]?locallogin?:是本地服務(wù)器上的登錄 ,默認(rèn)設(shè)置為 果為 null,則指定應(yīng)用于所有連接到 rmtsrvname的本地登錄．若不為 null 則 locallogin可以是 SQL Server登錄或 windows NT用戶 (4)[@rmtuser=]?rmtuser?:當(dāng) useself為 false時(shí) ,用來(lái)連接 rmtsrvname的用戶名 , 默認(rèn)設(shè)置為 null. 配置鏈接服務(wù)器 創(chuàng)建一個(gè)映射，使得所有到本地服務(wù)器的登錄都使用其各自的用戶憑據(jù) 連接到鏈接服務(wù)器 exam Exec sp_addlinkedsrvlogin ?exam? 或 Exec sp_addlinkedsrvlogin ?exam?,?true? 使用指定的用戶和密碼將所有本地登錄連接到鏈接服務(wù)器 Exec sp_addlinkedsrvlogin ?exam?,?false?,null,?user?,?password? 創(chuàng)建一個(gè)映射，使得所有到本地服務(wù)器的登錄都使用相同的登錄 user和 密碼 password連接到鏈接服務(wù)器 exam,不使用登錄和密碼 Exec sp_addlinkedsrvlogin ?exam?,?false? 配置鏈接服務(wù)器 數(shù)據(jù)訪問(wèn) 從 SQL Server 2022中訪問(wèn) OLE DB數(shù)據(jù)源的方法有兩種：通過(guò)特殊名稱 以及通過(guò)鏈接服務(wù)器進(jìn)行數(shù)據(jù)訪問(wèn)。在通過(guò)特殊名稱訪問(wèn) OLE DB數(shù)據(jù) 源的方法中，可以通過(guò)函數(shù) openrowset和 opendatasource來(lái)進(jìn)行。其語(yǔ)法 : Openrowset (?provider_name?, {?datasource?。?user_id?。?password?|?provider_string?} ,{[catalog.][schema.]object |?query?} ) Eg: SELECT * FROM OPENROWSET(39。39。, 39。d:\39。 39。admin39。39。39。, bbs) 查詢分析器 ? 如何進(jìn)入查詢分析器？ SQL語(yǔ)言基礎(chǔ) (變量與常量 ) ?定義和使用局部變量 定義局部變量 Declare { @variable_name datatype [,….n]} Declare @sex char(2) 為局部變量賦值 為局部變量賦值一般有兩種 ,一種是使用 select語(yǔ)句，另一種是使用 Set語(yǔ)句 ． Select @variable_name=expression [from table_name[,…n] where clause] declare @bookname varchar(30) select @bookname = 39。vc39。 print @bookname GO SQL語(yǔ)言基礎(chǔ) (變量與常量 ) ?定義和使用全局變量 全局變量是 SQL SERVER系統(tǒng)內(nèi)部事先定義好的變量，不 用用戶參與定義，對(duì)用戶而言是只讀的 。 ?@@identity:返回最近一次插入的 identity列的數(shù)值 ,返回值是 numeric ?@@procid:返回當(dāng)前存儲(chǔ)過(guò)程的 ID標(biāo)識(shí) ?@@version:返回當(dāng)前 SQL Server服務(wù)器安裝日期、版本以及處理器的類型 ?@@language:返回當(dāng)前使用的語(yǔ)言名 ?@@servername:返回當(dāng)前 SQL SERVER服務(wù)器名稱 SQL語(yǔ)言基礎(chǔ) (變量與常量 ) ? 使用日期和時(shí)間常量 SQL 提供的日期 /時(shí)間數(shù)據(jù)類型可以存儲(chǔ)日期和時(shí)間的組給數(shù)據(jù)。 SQL 規(guī)定日期、時(shí)間和時(shí)間間隔的常量值被指定為字符中常量。 SQL語(yǔ)言基礎(chǔ) (數(shù)據(jù)類型 ) ? 常見的數(shù)據(jù)類型 1. 整型 (int,smallint,tinyint,bigint) 2. 浮點(diǎn)數(shù)據(jù)類型 (real,float,decimal) 3. 二進(jìn)制數(shù)據(jù)類型 (binary,varbinary) 4. 邏輯數(shù)據(jù)類型 5. 字符型數(shù)據(jù)類型 (char(n),nchar(n),varchar(n),nvarchar(n)) SQL語(yǔ)言基礎(chǔ) (復(fù)合語(yǔ)句 ) ?定義復(fù)合語(yǔ)句 (begin…end) declare @A int,@B int,@C int set @A=20 set @B=30 begin set @C=@A set @A=@B set @B=@C end print @A print @B SQL語(yǔ)言基礎(chǔ) (條件判斷語(yǔ)句 ) ?使用 if語(yǔ)句進(jìn)行條件判斷 If條件表達(dá)式 {命令行 |程序塊 } declare @x int set @x=100 if @x0 print 39。@x是一個(gè)負(fù)數(shù) 39。 新建數(shù)據(jù)庫(kù) ?Create database命令 Create database database_name [on [filespec[,…..n]] [,filegroup[,….n]] ] [log on{filespec[,….n]}] [collate collation_name] [for load|for attach] 新建數(shù)據(jù)庫(kù) create database student on ( name=39。學(xué)生管理數(shù)據(jù)庫(kù) 39。, filename=39。f:\data\39。, size=1MB, MAXSIZE=50MB, FILEGROWTH=5MB ) log on ( name=39。學(xué)生管理日志 39。, filename=39。f:\data\39。, size=1MB, MAXSIZE=50MB, FILEGROWTH=10% ) 修改數(shù)據(jù)庫(kù) ? ALTER DATABASE命令 alter database student modify file( name=39。學(xué)生管理數(shù)據(jù)庫(kù) 39。, maxsize=unlimited ) 刪除數(shù)據(jù)庫(kù) ? Drop Database命令 新建表 ? Create table 命令 Create table table_name ( column_name data_type {[null|not null] [primary key|unique] } [,………] ) 新建表 use student go create table stu_info ( stu_id int not null, stu_name varchar(10), gender bit, major varchar(20), address varchar(100) ) 修改表 (增加字段 ) ? 增加字段 Alter table table_name Add column_name data_type Eg: alter table stu_info add memo text 修改表 (增加主鍵 ) ? Alter table add primary key(column_name) Eg: alter table stu_info add primary key(stu_id) 修改表 (更改字段 ) ? 更改字段屬性 Alter table table_