數(shù)據(jù)庫(kù)的實(shí)現(xiàn)

1、ACCP V4.0第二章第二章數(shù)據(jù)庫(kù)的實(shí)現(xiàn)ACCP V4.02回顧q在需求分析階段，設(shè)計(jì)數(shù)據(jù)庫(kù)的一般步驟為？q實(shí)體的映射關(guān)系有哪些？q請(qǐng)簡(jiǎn)要說(shuō)明三大范式的含義？ACCP V4.03目標(biāo)q 掌握建庫(kù)的SQL語(yǔ)句q 掌握建表的SQL語(yǔ)句q 掌握加約束的SQL語(yǔ)句q 掌握創(chuàng)建安全帳戶(hù)的SQL語(yǔ)句ACCP V4.04回顧數(shù)據(jù)庫(kù)的基礎(chǔ)知識(shí)q數(shù)據(jù)庫(kù)文件的組成：q主數(shù)據(jù)文件：*.mdfq次要數(shù)據(jù)文件：*.ndfq日志文件：*.ldfq數(shù)據(jù)庫(kù)的其他屬性：q文件存放位置，分配的初始空間，屬于哪個(gè)文件組q文件增長(zhǎng)：可以按百分比或?qū)嶋H大小指定增長(zhǎng)速度q文件容量設(shè)置：可以指定文件增長(zhǎng)的最大值或不受限ACCP V4.0

2、5創(chuàng)建數(shù)據(jù)庫(kù) CREATE DATABASE 數(shù)據(jù)庫(kù)名 ON PRIMARY ( ，n ) LOG ON ( ，n )T-SQL創(chuàng)建數(shù)據(jù)庫(kù)的語(yǔ)法：主文件組，可選參數(shù)，默認(rèn)ACCP V4.06創(chuàng)建數(shù)據(jù)庫(kù)示例 1CREATE DATABASE stuDB ON PRIMARY -默認(rèn)就屬于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ù)

3、據(jù)文件的增長(zhǎng)率)LOG ON( NAME=stuDB_log, FILENAME=D:projectstuDB_log.ldf, SIZE=2mb, FILEGROWTH=1MB)GO 數(shù)據(jù)文件的具體描述日志文件的具體描述演示創(chuàng)建數(shù)據(jù)庫(kù)1：一個(gè)數(shù)據(jù)文件和一個(gè)日志文件 ACCP V4.07創(chuàng)建數(shù)據(jù)庫(kù)示例 2CREATE DATABASE employees ON ( /*-主數(shù)據(jù)文件的具體描述-*/ NAME = employee1, FILENAME = D:projectemployee1_Data.mdf , SIZE = 10, FILEGROWTH = 10% ), ( /*-次要數(shù)據(jù)

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

5、log2_Log.ldf , SIZE = 10, MAXSIZE = 50, FILEGROWTH = 1 )GO 演示創(chuàng)建數(shù)據(jù)庫(kù)2：多個(gè)數(shù)據(jù)文件和多個(gè)日志文件 ACCP V4.08刪除數(shù)據(jù)庫(kù)q 如果SQL Server中已存在數(shù)據(jù)庫(kù)stuDB，運(yùn)行下列語(yǔ)句，會(huì)出現(xiàn)什么問(wèn)題？CREATE DATABASE stuDBON (.)LOG ON(.)GO演示：再次運(yùn)行示例1ACCP V4.09USE master -設(shè)置當(dāng)前數(shù)據(jù)庫(kù)為master，以便訪(fǎng)問(wèn)sysdatabases表GOIF EXISTS（SELECT * FROM sysdatabases WHERE name =stuDB） D

6、ROP DATABASE stuDBCREATE DATABASE stuDBON ( .)LOG ON( )GO刪除數(shù)據(jù)庫(kù)q刪除數(shù)據(jù)庫(kù)的語(yǔ)法：DROP DATABASE 數(shù)據(jù)庫(kù)名 EXISTS（）語(yǔ)句：檢測(cè)是否存在stuDB數(shù)據(jù)庫(kù)如果存在stuDB數(shù)據(jù)庫(kù)，則刪除 ACCP V4.010回顧表的基礎(chǔ)知識(shí) q 建表的基本步驟：q確定表中有哪些列q確定每列的數(shù)據(jù)類(lèi)型q給表添加各種約束q創(chuàng)建各表之間的關(guān)系q SQL Server中的數(shù)據(jù)類(lèi)型 整型數(shù)據(jù)：int smallint 浮點(diǎn)數(shù)據(jù)：float numeric字符數(shù)據(jù)固定長(zhǎng)度：char(6)可變長(zhǎng)度: varchar(10) unicode編碼：

7、nchar(10) 布爾數(shù)據(jù)（真/假） bit: 1/ 0 貨幣數(shù)據(jù) money日期數(shù)據(jù) datetimeACCP V4.011創(chuàng)建表q建表的語(yǔ)法CREATE TABLE 表名 ( 字段1 數(shù)據(jù)類(lèi)型 列的特征， 字段2 數(shù)據(jù)類(lèi)型 列的特征， .)列的特征：包括該列是是否為空（NULL）、是否是標(biāo)識(shí)列（自動(dòng)編號(hào)）、是否有默認(rèn)值、是否為主鍵等。 ACCP V4.012建表示例 1USE stuDB -將當(dāng)前數(shù)據(jù)庫(kù)設(shè)置為stuDB GOCREATE TABLE stuInfo /*-創(chuàng)建學(xué)員信息表-*/( stuName VARCHAR(20) NOT NULL , -姓名，非空（必填） stuNo

8、 CHAR(6) NOT NULL, -學(xué)號(hào)，非空（必填） stuAge INT NOT NULL, -年齡，INT類(lèi)型默認(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)代表18位數(shù)字，小數(shù)位數(shù)為0演示：創(chuàng)建學(xué)員信息表stuInfoIDENTITY（起始值，遞增量） ACCP V4.013建表示例 2CREATE TABLE stuMarks( ExamNo CHAR(7) NOT NULL,

9、-考號(hào) stuNo CHAR(6) NOT NULL, -學(xué)號(hào) writtenExam INT NOT NULL, -筆試成績(jī) LabExam INT NOT NULL -機(jī)試成績(jī))GO演示：創(chuàng)建學(xué)員成績(jī)表 stuMarksACCP V4.014刪除表q 如果當(dāng)前數(shù)據(jù)庫(kù)中已存在stuInfo表，再次創(chuàng)建時(shí)系統(tǒng)將提示出錯(cuò) 。如何解決呢？ACCP V4.015刪除表q刪除表的語(yǔ)法：DROP TABLE 表名USE stuDB -將當(dāng)前數(shù)據(jù)庫(kù)設(shè)置為stuDB ，以便在stuDB數(shù)據(jù)庫(kù)中建表GOIF EXISTS（SELECT * FROM sysobjects WHERE name=stuInfo

10、） DROP TABLE stuInfoCREATE TABLE stuInfo /*-創(chuàng)建學(xué)員信息表-*/(.) GOACCP V4.016回顧SQL Server的約束q 約束的目的：確保表中數(shù)據(jù)的完整型q 常用的約束類(lèi)型：q主鍵約束（Primary Key Constraint）：要求主鍵列數(shù)據(jù)唯一，并且不允許為空q唯一約束（Unique Constraint）：要求該列唯一，允許為空，但只能出現(xiàn)一個(gè)空值。q檢查約束（Check Constraint）：某列取值范圍限制、格式限制等，如有關(guān)年齡的約束q默認(rèn)約束（Default Constraint）：某列的默認(rèn)值，如我們的男性學(xué)員較多，性

11、別默認(rèn)為“男”q外鍵約束（Foreign Key Constraint）：用于兩表間建立關(guān)系，需要指定引用主表的那列ACCP V4.017添加約束 q 添加約束的語(yǔ)法：ALTER TABLE 表名 ADD CONSTRAINT 約束名 約束類(lèi)型 具體的約束說(shuō)明q 約束名的取名規(guī)則推薦采用：約束類(lèi)型_約束字段q主鍵（Primary Key）約束：如 PK_stuNoq唯一（Unique Key）約束：如 UQ_stuIDq默認(rèn)（Default Key）約束：如 DF_stuAddressq檢查（Check Key）約束：如 CK_stuAgeq外鍵(Foreign Key)約束：如 FK_stu

12、No ACCP V4.018添加約束示例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 (地址不詳) FOR stuAddressALTER TABLE stuInfo ADD CONSTRAINT CK_stuAge CHECK(stuAge BETWEEN 15 AND 40)ALTER

13、 TABLE stuMarks ADD CONSTRAINT FK_stuNo FOREIGN KEY(stuNo) REFERENCES stuInfo(stuNo)GO演示：給學(xué)員信息表stuInfo添加約束添加主鍵約束（stuNo作為主鍵）添加唯一約束（因?yàn)槊咳说纳矸葑C號(hào)全國(guó)唯一）添加默認(rèn)約束（如果地址不填，默認(rèn)為“地址不詳”）添加檢查check約束，要求年齡只能在1540歲之間添加外鍵約束（主表stuInfo和從表stuMarks建立關(guān)系，關(guān)聯(lián)字段為stuNo）ACCP V4.019添加約束示例演示：查看約束結(jié)果ACCP V4.020刪除約束q如果錯(cuò)誤地添加了約束，我們還可以刪除約束

14、q刪除約束的語(yǔ)法ALTER TABLE 表名 DROP CONSTRAINT 約束名 例如：刪除stuInfo表中地址默認(rèn)約束ALTER TABLE stuInfo DROP CONSTRAINT DF_stuAddressACCP V4.021回顧SQL Server的安全模型數(shù)據(jù)庫(kù) 1數(shù)據(jù)庫(kù) 2數(shù)據(jù)庫(kù) 3表 1表 2表 1表 2表 1表 2增刪改查增刪改查增刪改查增刪改查增刪改查增刪改查數(shù)據(jù)庫(kù)用戶(hù)數(shù)據(jù)庫(kù)用戶(hù)數(shù)據(jù)庫(kù)用戶(hù)登錄帳號(hào)登錄帳號(hào)SQLserver三層安全管理ACCP V4.022登錄方式回顧q 登錄驗(yàn)證有兩種方式：qSQL身份驗(yàn)證：適合于非windows平臺(tái)的用戶(hù)或Internet用戶(hù)

15、， 需要提供帳戶(hù)和密碼qWindows身份驗(yàn)證：適合于windows平臺(tái)用戶(hù)，不需要提供密碼，和windows集成驗(yàn)證q 登錄帳戶(hù)相應(yīng)有兩種：SQL 帳戶(hù)和Windows帳戶(hù) ACCP V4.023創(chuàng)建登錄q添加 Windows登錄帳戶(hù) EXEC sp_grantlogin jbtrainingS26301 q添加 SQL登錄帳戶(hù) EXEC sp_addlogin zhangsan, 1234演示創(chuàng)建登錄EXEC表示調(diào)用存儲(chǔ)過(guò)程，存儲(chǔ)過(guò)程類(lèi)似C語(yǔ)言的函數(shù)內(nèi)置的系統(tǒng)管理員 帳戶(hù)sa密碼默認(rèn)為空，建議修改密碼 域名用戶(hù)名用戶(hù)名,密碼ACCP V4.024創(chuàng)建登錄ACCP V4.025創(chuàng)建數(shù)據(jù)庫(kù)用戶(hù)

16、q 創(chuàng)建數(shù)據(jù)庫(kù)用戶(hù)需要調(diào)用系統(tǒng)存儲(chǔ)過(guò)程sp_grantdbaccess，其用法為：EXEC sp_grantdbaccess 登錄帳戶(hù)名，數(shù)據(jù)庫(kù)用戶(hù)名 其中，“數(shù)據(jù)庫(kù)用戶(hù)“為可選參數(shù)，默認(rèn)為登錄帳戶(hù)，即數(shù)據(jù)庫(kù)用戶(hù)默認(rèn)和登錄帳戶(hù)同名。USE stuDBGOEXEC sp_grantdbaccess jbtrainingS26301, S26301DBUser EXEC sp_grantdbaccess zhangsan, zhangsanDBUser在stuDB數(shù)據(jù)庫(kù)中添加兩個(gè)用戶(hù)ACCP V4.026創(chuàng)建數(shù)據(jù)庫(kù)用戶(hù)ACCP V4.027qdbo 用戶(hù)q表示數(shù)據(jù)庫(kù)的所有者（DB Owner）q無(wú)

17、法刪除 dbo 用戶(hù)，此用戶(hù)始終出現(xiàn)在每個(gè)數(shù)據(jù)庫(kù)中 qguest 用戶(hù)q適用于沒(méi)有數(shù)據(jù)庫(kù)用戶(hù)的登錄帳號(hào)訪(fǎng)問(wèn)q每個(gè)數(shù)據(jù)庫(kù)可有也可刪除系統(tǒng)內(nèi)置的數(shù)據(jù)庫(kù)用戶(hù)ACCP V4.028向數(shù)據(jù)庫(kù)用戶(hù)授權(quán) EmployeesEmployeeID123LastNameDavolioFullerLeverling FirstName NancyAndrewJanetReportsTo22 .delete FROM EmployeesSELECT * FROM EmployeesACCP V4.029權(quán)限的類(lèi)型 2-1q 授權(quán)的語(yǔ)法為：q GRANT 權(quán)限 ON 表名 TO 數(shù)據(jù)庫(kù)用戶(hù)USE stuDBGO/*-為zhangsanDBUser分配對(duì)表stuInfo的select, insert, update權(quán)限-*/GRANT select, insert, update ON stuInfo TO zhangsanDBUser /*-為S26301DBUser分配建表的權(quán)限-*/GRANT create table TO S263