數(shù)據(jù)庫基礎(chǔ)創(chuàng)建數(shù)據(jù)庫.ppt

第二章,數(shù)據(jù)庫的實現(xiàn),2,目標(biāo),掌握建庫的SQL語句 掌握建表的SQL語句,3,回顧數(shù)據(jù)庫的基礎(chǔ)知識,數(shù)據(jù)庫文件的組成： 主數(shù)據(jù)文件：*.mdf 次要數(shù)據(jù)文件：*.ndf 日志文件：*.ldf 數(shù)據(jù)庫的其他屬性： 文件存放位置，分配的初始空間，屬于哪個文件組 文件增長：可以按百分比或?qū)嶋H大小指定增長速度 文件容量設(shè)置：可以指定文件增長的最大值或不受限,4,創(chuàng)建數(shù)據(jù)庫,CREATE DATABASE 數(shù)據(jù)庫名 ON PRIMARY ( ，n ) LOG ON ( ，n ),T-SQL創(chuàng)建數(shù)據(jù)庫的語法：,主文件組，可選參數(shù)，默認(rèn),5,創(chuàng)建數(shù)據(jù)庫示例 1,CREATE 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ù)文件增長的最大值 FILEGROWTH=15% -主數(shù)據(jù)文件的增長率 ) LOG ON ( NAME=stuDB_log, FILENAME=D:projectstuDB_log.ldf, SIZE=2mb, FILEGROWTH=1MB ) GO,數(shù)據(jù)文件的具體描述,日志文件的具體描述,演示創(chuàng)建數(shù)據(jù)庫1：一個數(shù)據(jù)文件和一個日志文件,6,創(chuàng)建數(shù)據(jù)庫示例 2,CREATE DATABASE employees ON ( /*-主數(shù)據(jù)文件的具體描述-*/ NAME = employee1, FILENAME = D:projectemployee1_Data.mdf , SIZE = 10, FILEGROWTH = 10% ), ( /*-次要數(shù)據(jù)文件的具體描述-*/ 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:projectemployeelog2_Log.ldf , SIZE = 10, MAXSIZE = 50, FILEGROWTH = 1 ) GO,演示創(chuàng)建數(shù)據(jù)庫2：多個數(shù)據(jù)文件和多個日志文件,7,刪除數(shù)據(jù)庫,如果SQL Server中已存在數(shù)據(jù)庫stuDB，運(yùn)行下列語句，會出現(xiàn)什么問題？,CREATE DATABASE stuDB ON ( . ) LOG ON ( . ) GO,演示：再次運(yùn)行示例1,如何知道服務(wù)器上已有的數(shù)據(jù)庫,8,USE master -設(shè)置當(dāng)前數(shù)據(jù)庫為master，以便訪問sysdatabases表 GO IF EXISTS（SELECT * FROM sysdatabases WHERE name =stuDB） DROP DATABASE stuDB CREATE DATABASE stuDB ON ( . ) LOG ON ( ) GO,刪除數(shù)據(jù)庫,刪除數(shù)據(jù)庫的語法：,DROP DATABASE 數(shù)據(jù)庫名,EXISTS（）語句： 檢測是否存在stuDB數(shù)據(jù)庫 如果存在stuDB數(shù)據(jù)庫，則刪除,9,修改數(shù)據(jù)庫,修改數(shù)據(jù)庫的語法,ALTER DATABASE database ADD FILE ,.n TO FILEGROUP filegroup_name | ADD LOG FILE ,.n | REMOVE FILE logical_file_name | ADD FILEGROUP filegroup_name | REMOVE FILEGROUP filegroup_name | MODIFY FILE | MODIFY NAME = new_dbname | MODIFY FILEGROUP filegroup_name filegroup_property | NAME = new_filegroup_name | SET ,.n WITH | COLLATE ,10,修改數(shù)據(jù)庫示例,【例】擴(kuò)大主要數(shù)據(jù)文件的大小到12MB，擴(kuò)大事務(wù)日志文件的大小到4MB 。 ALTER DATABASE stuDB MODIFY FILE (NAME= stuDB_data, SIZE=12MB),11,回顧表的基礎(chǔ)知識,建表的基本步驟： 確定表中有哪些列 確定每列的數(shù)據(jù)類型 給表添加各種約束 創(chuàng)建各表之間的關(guān)系 SQL Server中的數(shù)據(jù)類型,整型數(shù)據(jù)：int smallint 浮點數(shù)據(jù)：float numeric ,字符數(shù)據(jù) 固定長度：char(6) 可變長度: varchar(10) unicode編碼：nchar(10) ,布爾數(shù)據(jù)（真/假） bit: 1/ 0,貨幣數(shù)據(jù) money,日期數(shù)據(jù) datetime,12,SQL Server的數(shù)據(jù)類型,13,創(chuàng)建表,建表的語法,CREATE TABLE 表名 ( 字段1 數(shù)據(jù)類型 列的特征， 字段2 數(shù)據(jù)類型 列的特征， . ),列的特征： 包括該列是是否為空（NULL）、是否是標(biāo)識列（自動編號）、是否有默認(rèn)值、是否為主鍵等。,14,建表示例 1,USE stuDB -將當(dāng)前數(shù)據(jù)庫設(shè)置為stuDB GO CREATE TABLE stuInfo /*-創(chuàng)建學(xué)員信息表-*/ ( stuName VARCHAR(20) NOT NULL , -姓名，非空（必填） stuNo CHAR(6) NOT NULL, -學(xué)號，非空（必填） stuAge INT NOT NULL, -年齡，INT類型默認(rèn)為4個字節(jié) stuID NUMERIC(18,0), -身份證號 stuSeat SMALLINT IDENTITY (1,1), -座位號，自動編號 stuAddress TEXT -住址，允許為空，即可選輸入 ) GO,NUMERIC (18,0) 代表18位數(shù)字，小數(shù)位數(shù)為0,演示：創(chuàng)建學(xué)員信息表stuInfo,IDENTITY（起始值，遞增量）,15,建表示例 2,CREATE TABLE stuMarks ( ExamNo CHAR(7) NOT NULL, -考號 stuNo CHAR(6) NOT NULL, -學(xué)號 writtenExam INT NOT NULL, -筆試成績 LabExam INT NOT NULL -機(jī)試成績 ) GO,演示：創(chuàng)建學(xué)員成績表 stuMarks,16,刪除表,如果當(dāng)前數(shù)據(jù)庫中已存在stuInfo表，再次創(chuàng)建時系統(tǒng)將提示出錯 。如何解決呢？,17,刪除表,刪除表的語法：,DROP TABLE 表名,USE stuDB -將當(dāng)前數(shù)據(jù)庫設(shè)置為stuDB ，以便在stuDB數(shù)據(jù)庫中建表 GO IF EXISTS（SELECT * FROM sysobjects WHERE name=stuInfo ） DROP TABLE stuInfo CREATE TABLE stuInfo /*-創(chuàng)建學(xué)員信息表-*/ ( . ) GO,18,SQL Server的約束,約束的目的：確保表中數(shù)據(jù)的完整型 常用的約束類型： 主鍵約束（Primary Key Constraint）：要求主鍵列數(shù)據(jù)唯一，并且不允許為空 唯一約束（Unique Constraint）：要求該列唯一，允許為空，但只能出現(xiàn)一個空值。 檢查約束（Check Constraint）：某列取值范圍限制、格式限制等，如有關(guān)年齡的約束 默認(rèn)約束（Default Constraint）：某列的默認(rèn)值，如我們的男性學(xué)員較多，性別默認(rèn)為“男” 外鍵約束（Foreign Key Constraint）：用于兩表間建立關(guān)系，需要指定引用主表的那列,19,添加約束,添加約束的語法：,ALTER TABLE 表名 ADD CONSTRAINT 約束名 約束類型 具體的約束說明,約束名的取名規(guī)則推薦采用：約束類型_約束字段 主鍵（Primary Key）約束：如 PK_stuNo 唯一（Unique Key）約束：如 UQ_stuID 默認(rèn)（Default Key）約束：如 DF_stuAddress 檢查（Check Key）約束：如 CK_stuAge 外鍵(Foreign Key)約束：如 FK_stuNo,20,添加約束示例,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 stuAddress ALTER 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é)員信息表stuInfo添加約束,添加主鍵約束（stuNo作為主鍵）,添加唯一約束（因為每人的身份證號全國唯一）,添加默認(rèn)約束（如果地址不填，默認(rèn)為“地址不詳”）,添加檢查check約束，要求年齡只能在1540歲之間,添加外鍵約束（主表stuInfo和從表stuMarks建立關(guān)系，關(guān)聯(lián)字段為stuNo）,21,添加約束示例,演示：查看約束結(jié)果,22,刪除約束,如果錯誤地添加了約束，我們還可以刪除約束 刪除約束的語法,ALTER TABLE 表名 DROP CONSTRAINT 約束名,例如：刪除stuInfo表中地址默認(rèn)約束 ALTER TABLE stuInfo DROP CONSTRAINT DF_stuAddress,23,表的數(shù)據(jù)操作,向表中插入數(shù)據(jù)語法,INSERT INTO table_name column_list VALUES(column_list的值),例： 向表 stuInfo 增加一條記錄 Insert into stuInfo(stuName, stuNo) values(張大力，20100101) Insert stuInfo values(張大力,2010,20,12312,河北省),24,用UPDATE語句修改表數(shù)據(jù),UPDATE table_name SET column_name = 要修改的值 WHERE 條件,例： 修改表stuInfo 中的張大力的年齡為22 Update stuInfo set stuAge=22 where stuName=張大力,25,用DELETE語句刪除表中數(shù)據(jù),DELETE FROM tablename WHERE 條件,例： 刪除表stuInfo中的所有數(shù)據(jù) Delete from stuInfo 刪除表stuInfo中張大力的記錄 delete from stuInfo where stuName=張大力,26,思考,標(biāo)識列允許為字符數(shù)據(jù)類型嗎？ 如果標(biāo)識列A的初始值為1，增長量為2，則輸入三行數(shù)據(jù)以后，再刪除兩行，下次再輸入數(shù)據(jù)行的時候，標(biāo)識值從多少開始？,27,關(guān)系圖,演示關(guān)系圖,28,SQL Server的安全模型,29,登錄方式回顧,登錄驗證有兩種方式： SQL身份驗證：適合于非windows平臺的用戶或Internet用戶， 需要提供帳戶和密碼 Windows身份驗證：適合于windows平臺用戶，不需要提供密碼，和windows集成驗證 登錄帳戶相應(yīng)有兩種：SQL 帳戶和Windows帳戶,30,創(chuàng)建登錄,添加 Windows登錄帳戶 EXEC sp_grantlogin jbtrainingS26301 添加 SQL登錄帳戶 EXEC sp_addlogin zhangsan, 1234,演示創(chuàng)建登錄,EXEC表示調(diào)用存儲過程， 存儲過程類似C語言的函數(shù),內(nèi)置的系統(tǒng)管理員 帳戶sa 密碼默認(rèn)為空，建議修改密碼,域名用戶名,用戶名,密碼,31,創(chuàng)建登錄,32,創(chuàng)建數(shù)據(jù)庫用戶,創(chuàng)建數(shù)據(jù)庫用戶需要調(diào)用系統(tǒng)存儲過程sp_grantdbaccess，其用法為：,EXEC sp_grantdbaccess 登錄帳戶名，數(shù)據(jù)庫用戶名 其中，“數(shù)據(jù)庫用戶“為可選參數(shù)，默認(rèn)為登錄帳戶， 即數(shù)據(jù)庫用戶默認(rèn)和登錄帳戶同名。,USE stuDB GO EXEC sp_grantdbaccess jbtrainingS26301, S26301DBUser EXEC sp_grantdbaccess zhangsan, zhangsanDBUser,在stuDB數(shù)據(jù)庫中添加兩個用戶,33,創(chuàng)建數(shù)據(jù)庫用戶,34,dbo 用戶 表示數(shù)據(jù)庫的所有者（DB Owner） 無法刪除 dbo 用戶，此用戶始終出現(xiàn)在每個數(shù)據(jù)庫中 guest 用戶 適用于沒有數(shù)據(jù)庫用戶的登錄帳號訪問 每個數(shù)據(jù)庫可有也可刪除,系統(tǒng)內(nèi)置的數(shù)據(jù)庫用戶,35,向數(shù)據(jù)庫用戶授權(quán),delete FROM Employees,SELECT * FROM Employees,36,權(quán)限的類型 2-1,授權(quán)的語法為： GRANT 權(quán)限 ON 表名 TO 數(shù)據(jù)庫用戶,USE stuDB GO /*-為zhangsanDBUser分配對表stuInfo的select, insert, update權(quán)限-*/ GRANT select, insert, update ON stuInfo TO zhangsanDBUser /*-為S26301DBUser分配建表的權(quán)限-*/ GRANT create table TO S26301DBUser,37,權(quán)限的類型 2-2,沒有授予delete權(quán)限,38,總結(jié) 2-1,數(shù)據(jù)庫的物理實現(xiàn)一般包括： 創(chuàng)建數(shù)據(jù)庫 創(chuàng)建表 添加各種約束 創(chuàng)建數(shù)據(jù)庫的登錄帳戶并授權(quán) 創(chuàng)建數(shù)據(jù)庫或表時一般需要預(yù)先檢測是否存在該對象 數(shù)據(jù)庫從master系統(tǒng)數(shù)據(jù)庫的sysdatabases表中查詢 表從該數(shù)據(jù)庫的系統(tǒng)表sysobjects表中查詢,39,總結(jié) 2-2,訪問SQL Server某個數(shù)據(jù)庫中的某個表，需要三層驗證： 是否是SQL Server的登錄帳戶 是否是該數(shù)據(jù)庫的用戶 是否有足夠的權(quán)限訪問該表,