SQLServer數(shù)據(jù)庫(kù)設(shè)計(jì)和高級(jí)查詢SQLAdvanceV實(shí)用教案

1、會(huì)計(jì)學(xué)1SQL Server數(shù)據(jù)庫(kù)設(shè)計(jì)數(shù)據(jù)庫(kù)設(shè)計(jì)(shj)和高級(jí)查和高級(jí)查詢?cè)僑QL AdvanceV第一頁(yè)，共34頁(yè)。第1頁(yè)/共33頁(yè)第二頁(yè)，共34頁(yè)。第2頁(yè)/共33頁(yè)第三頁(yè)，共34頁(yè)。數(shù)據(jù)庫(kù)文件的組成：主數(shù)據(jù)文件：*.mdf次要數(shù)據(jù)文件：*.ndf日志文件：*.ldf數(shù)據(jù)庫(kù)的其他屬性：文件存放位置(wi zhi)，分配的初始空間，屬于哪個(gè)文件組文件增長(zhǎng)：可以按百分比或?qū)嶋H大小指定增長(zhǎng)速度文件容量設(shè)置：可以指定文件增長(zhǎng)的最大值或不受限第3頁(yè)/共33頁(yè)第四頁(yè)，共34頁(yè)。CREATE DATABASE 數(shù)據(jù)庫(kù)名 ON PRIMARY ( ，n ) LOG ON ( ，n )T-SQL創(chuàng)建(chu

2、ngjin)數(shù)據(jù)庫(kù)的語(yǔ)法：主文件組，可選參數(shù)，默認(rèn)第4頁(yè)/共33頁(yè)第五頁(yè)，共34頁(yè)。CREATE DATABASE stuDB ON PRIMARY -默認(rèn)就屬于(shy)PRIMARY主文件組，可省略( NAME=stuDB_data, -主數(shù)據(jù)文件的邏輯名 FILENAME=D:projectstuDB_data.mdf, -主數(shù)據(jù)文件的物理名 SIZE=5mb, -主數(shù)據(jù)文件初始大小 MAXSIZE=100mb, -主數(shù)據(jù)文件增長(zhǎng)的最大值 FILEGROWTH=15% -主數(shù)據(jù)文件的增長(zhǎng)率)LOG ON( NAME=stuDB_log, FILENAME=D:projectstuDB_l

3、og.ldf, SIZE=2mb, FILEGROWTH=1MB)GO 數(shù)據(jù)文件的具體(jt)描述日志文件的具體描述演示創(chuàng)建數(shù)據(jù)庫(kù)1：一個(gè)數(shù)據(jù)文件和一個(gè)日志文件 第5頁(yè)/共33頁(yè)第六頁(yè)，共34頁(yè)。CREATE DATABASE employees ON ( /*-主數(shù)據(jù)文件的具體描述(mio sh)-*/ NAME = employee1, FILENAME = D:projectemployee1_Data.mdf , SIZE = 10, FILEGROWTH = 10% ), ( /*-次要數(shù)據(jù)文件的具體描述(mio sh)-*/ NAME = employee2, FILENAME =

4、 D:projectemployee2_Data.ndf , SIZE = 20, MAXSIZE = 100, FILEGROWTH = 1 ) LOG ON ( /*-日志文件(wnjin)1的具體描述-*/ NAME = employeelog1, FILENAME = D:projectemployeelog1_Log.ldf , SIZE = 10, FILEGROWTH = 1 ), ( /*-日志文件(wnjin)2的具體描述-*/ NAME = employeelog2, FILENAME = D:projectemployeelog2_Log.ldf , SIZE = 10,

5、 MAXSIZE = 50, FILEGROWTH = 1 )GO 演示創(chuàng)建數(shù)據(jù)庫(kù)2：多個(gè)數(shù)據(jù)文件和多個(gè)日志文件 第6頁(yè)/共33頁(yè)第七頁(yè)，共34頁(yè)。CREATE DATABASE stuDBON (.)LOG ON(.)GO演示(ynsh)：再次運(yùn)行示例1第7頁(yè)/共33頁(yè)第八頁(yè)，共34頁(yè)。USE master -設(shè)置(shzh)當(dāng)前數(shù)據(jù)庫(kù)為master，以便訪問(wèn)sysdatabases表GOIF EXISTS（SELECT * FROM sysdatabases WHERE name =stuDB） DROP DATABASE stuDBCREATE DATABASE stuDBON ( .)

6、LOG ON( )GODROP DATABASE 數(shù)據(jù)庫(kù)名 EXISTS（）語(yǔ)句：檢測(cè)是否存在stuDB數(shù)據(jù)庫(kù)如果存在stuDB數(shù)據(jù)庫(kù)，則刪除 第8頁(yè)/共33頁(yè)第九頁(yè)，共34頁(yè)。建表的基本步驟：確定表中有哪些列確定每列的數(shù)據(jù)類型給表添加(tin ji)各種約束創(chuàng)建各表之間的關(guān)系SQL Server中的數(shù)據(jù)類型 整型數(shù)據(jù)(shj)：int smallint 浮點(diǎn)數(shù)據(jù)(shj)：float numeric字符數(shù)據(jù)固定長(zhǎng)度：char(6)可變長(zhǎng)度: varchar(10) unicode編碼：nchar(10) 布爾數(shù)據(jù)（真/假） bit: 1/ 0 貨幣數(shù)據(jù) money日期數(shù)據(jù) datetime第

7、9頁(yè)/共33頁(yè)第十頁(yè)，共34頁(yè)。CREATE TABLE 表名 ( 字段1 數(shù)據(jù)類型 列的特征(tzhng)， 字段2 數(shù)據(jù)類型 列的特征(tzhng)， .)列的特征：包括該列是是否為空（NULL）、是否是標(biāo)識(shí)列（自動(dòng)編號(hào)）、是否有默認(rèn)值、是否為主鍵等。 第10頁(yè)/共33頁(yè)第十一頁(yè)，共34頁(yè)。USE stuDB -將當(dāng)前數(shù)據(jù)庫(kù)設(shè)置為stuDB GOCREATE TABLE stuInfo /*-創(chuàng)建學(xué)員信息(xnx)表-*/( stuName VARCHAR(20) NOT NULL , -姓名，非空（必填） stuNo CHAR(6) NOT NULL, -學(xué)號(hào)，非空（必填） stuAge

8、 INT NOT NULL, -年齡，INT類型默認(rèn)為4個(gè)字節(jié) stuID NUMERIC(18,0), -身份證號(hào) stuSeat SMALLINT IDENTITY (1,1), -座位號(hào)，自動(dòng)編號(hào) stuAddress TEXT -住址，允許為空，即可選輸入) GONUMERIC (18,0)代表(dibio)18位數(shù)字，小數(shù)位數(shù)為0演示：創(chuàng)建學(xué)員信息表stuInfoIDENTITY（起始值，遞增量） 第11頁(yè)/共33頁(yè)第十二頁(yè)，共34頁(yè)。CREATE TABLE stuMarks( ExamNo CHAR(7) NOT NULL, -考號(hào) stuNo CHAR(6) NOT NULL,

9、 -學(xué)號(hào) writtenExam INT NOT NULL, -筆試(bsh)成績(jī) LabExam INT NOT NULL -機(jī)試成績(jī))GO演示：創(chuàng)建(chungjin)學(xué)員成績(jī)表 stuMarks第12頁(yè)/共33頁(yè)第十三頁(yè)，共34頁(yè)。第13頁(yè)/共33頁(yè)第十四頁(yè)，共34頁(yè)。DROP TABLE 表名USE stuDB -將當(dāng)前數(shù)據(jù)庫(kù)設(shè)置(shzh)為stuDB ，以便在stuDB數(shù)據(jù)庫(kù)中建表GOIF EXISTS（SELECT * FROM sysobjects WHERE name=stuInfo ） DROP TABLE stuInfoCREATE TABLE stuInfo /*-創(chuàng)建

10、學(xué)員信息表-*/(.) GO第14頁(yè)/共33頁(yè)第十五頁(yè)，共34頁(yè)。某列取值范圍限制、格式限制等，如有關(guān)年齡的約束n默認(rèn)約束（Default Constraint）：某列的默認(rèn)值，如我們的男性學(xué)員較多，性別默認(rèn)為“男”n外鍵約束（Foreign Key Constraint）：用于兩表間建立關(guān)系，需要指定引用主表的那列第15頁(yè)/共33頁(yè)第十六頁(yè)，共34頁(yè)。ALTER TABLE 表名 ADD CONSTRAINT 約束名 約束類型(lixng) 具體的約束說(shuō)明約束名的取名規(guī)則推薦采用：約束類型_約束字段主鍵（Primary Key）約束：如 PK_stuNo唯一（Unique Key）約束：如

11、UQ_stuID默認(rèn)（Default Key）約束：如 DF_stuAddress檢查（Check Key）約束：如 CK_stuAge外鍵(Foreign Key)約束：如 FK_stuNo 第16頁(yè)/共33頁(yè)第十七頁(yè)，共34頁(yè)。ALTER TABLE stuInfo ADD CONSTRAINT PK_stuNo PRIMARY KEY (stuNo)ALTER TABLE stuInfo ADD CONSTRAINT UQ_stuID UNIQUE (stuID)ALTER TABLE stuInfo ADD CONSTRAINT DF_stuAddress DEFAULT (地址(dz

12、h)不詳) FOR stuAddressALTER TABLE stuInfo ADD CONSTRAINT CK_stuAge CHECK(stuAge BETWEEN 15 AND 40)ALTER TABLE stuMarks ADD CONSTRAINT FK_stuNo FOREIGN KEY(stuNo) REFERENCES stuInfo(stuNo)GO演示：給學(xué)員信息(xnx)表stuInfo添加約束添加主鍵約束（stuNo作為主鍵）添加唯一約束（因?yàn)槊咳说纳矸葑C號(hào)全國(guó)唯一）添加默認(rèn)約束（如果地址不填，默認(rèn)為“地址不詳”）添加檢查check約束，要求年齡只能在1540歲之間

13、添加外鍵約束（主表stuInfo和從表stuMarks建立關(guān)系，關(guān)聯(lián)字段為stuNo）第17頁(yè)/共33頁(yè)第十八頁(yè)，共34頁(yè)。演示(ynsh)：查看約束結(jié)果第18頁(yè)/共33頁(yè)第十九頁(yè)，共34頁(yè)。ALTER TABLE 表名 DROP CONSTRAINT 約束(yush)名 例如：刪除stuInfo表中地址默認(rèn)約束ALTER TABLE stuInfo DROP CONSTRAINT DF_stuAddress第19頁(yè)/共33頁(yè)第二十頁(yè)，共34頁(yè)。數(shù)據(jù)庫(kù) 1數(shù)據(jù)庫(kù) 2數(shù)據(jù)庫(kù) 3表 1表 2表 1表 2表 1表 2增刪改查增刪改查增刪改查增刪改查增刪改查增刪改查數(shù)據(jù)庫(kù)用戶數(shù)據(jù)庫(kù)用戶數(shù)據(jù)庫(kù)用戶登錄帳

14、號(hào)SQLserver三層安全管理第20頁(yè)/共33頁(yè)第二十一頁(yè)，共34頁(yè)。第21頁(yè)/共33頁(yè)第二十二頁(yè)，共34頁(yè)。演示創(chuàng)建(chungjin)登錄EXEC表示調(diào)用存儲(chǔ)過(guò)程，存儲(chǔ)過(guò)程類似C語(yǔ)言的函數(shù)內(nèi)置的系統(tǒng)管理員 帳戶sa密碼默認(rèn)為空，建議修改密碼 域名用戶名用戶名,密碼第22頁(yè)/共33頁(yè)第二十三頁(yè)，共34頁(yè)。第23頁(yè)/共33頁(yè)第二十四頁(yè)，共34頁(yè)。EXEC sp_grantdbaccess 登錄帳戶名，數(shù)據(jù)庫(kù)用戶名 其中，“數(shù)據(jù)庫(kù)用戶“為可選參數(shù)，默認(rèn)(mrn)為登錄帳戶，即數(shù)據(jù)庫(kù)用戶默認(rèn)(mrn)和登錄帳戶同名。USE stuDBGOEXEC sp_grantdbaccess jbtrain

15、ingS26301, S26301DBUser EXEC sp_grantdbaccess zhangsan, zhangsanDBUser在stuDB數(shù)據(jù)庫(kù)中添加兩個(gè)用戶第24頁(yè)/共33頁(yè)第二十五頁(yè)，共34頁(yè)。第25頁(yè)/共33頁(yè)第二十六頁(yè)，共34頁(yè)。系統(tǒng)(xtng)內(nèi)置的數(shù)據(jù)庫(kù)用戶(shnch)第26頁(yè)/共33頁(yè)第二十七頁(yè)，共34頁(yè)。EmployeesEmployeeID123LastNameDavolioFullerLeverling FirstName NancyAndrewJanetReportsTo22 .delete FROM EmployeesSELECT * FROM Employees第27頁(yè)/共33頁(yè)第二十八頁(yè)，共34頁(yè)。USE stuDBGO/*-為zhangsanDBUser分配(fnpi)對(duì)表stuInfo的select, insert, update權(quán)限-*/GRANT select, insert, update ON stuInfo TO zhangsanDBUser /*-為S26301DBUs