教學(xué)課件·數(shù)據(jù)庫應(yīng)用項目化教程(第二版)

1、數(shù)據(jù)庫應(yīng)用項目化教程項目一校園論壇管理數(shù)據(jù)庫1基本概念2數(shù)據(jù)庫體系結(jié)構(gòu) 數(shù)據(jù)(Data)：描述客觀事物特征和特性的符號描述學(xué)生:學(xué)號- G13120026姓名- 王小麗電話- 1377456789年齡- 10照片- 數(shù)據(jù)庫(DataBase)：以一定組織方式存儲在一起的、統(tǒng)一管理的相關(guān)數(shù)據(jù)的集合學(xué)生DB描述學(xué)生:學(xué)號- G13120026姓名- 王小麗電話- 1377456789年齡- 10照片-教師輔導(dǎo)員教務(wù)領(lǐng)導(dǎo)問題一：數(shù)據(jù)庫技術(shù)的根本目標(biāo)是解決數(shù)據(jù)的- A 存儲問題 B 共享問題 C 安全問題 D 保護問題 數(shù)據(jù)庫管理系統(tǒng)( DBMS )：對數(shù)據(jù)庫進行管理的軟件數(shù)據(jù)庫管理系統(tǒng)（DBMS）數(shù)

2、據(jù)庫（DB） 數(shù)據(jù)庫系統(tǒng)( DBS )：應(yīng)用程序DBMS數(shù)據(jù)庫DB應(yīng)用程序應(yīng)用程序DBA問題二：數(shù)據(jù)庫，數(shù)據(jù)庫系統(tǒng)，數(shù)據(jù)庫管理系統(tǒng)之間的關(guān)系是 A DB包含DBS和DBMS B DBMS包含DB和DBS C DBS包含DB和DBMS2.數(shù)據(jù)庫體系結(jié)構(gòu) 外部體系結(jié)構(gòu) 單機結(jié)構(gòu) C/S結(jié)構(gòu) B/S結(jié)構(gòu) 外部體系結(jié)構(gòu)： 單機結(jié)構(gòu) C/S結(jié)構(gòu) B/S結(jié)構(gòu)ClientDB ServerClientClientC/S結(jié)構(gòu)Server騰訊DBDBMS你有強迫癥嗎？思考C/S式的優(yōu)缺點 外部體系結(jié)構(gòu)： 單機結(jié)構(gòu) C/S結(jié)構(gòu) B/S結(jié)構(gòu)BrowserDB ServerWeb ServerBrowserBrows

3、erB/S結(jié)構(gòu)問題三：請說明以下各屬于何種體系結(jié)構(gòu)？項目二.學(xué)生成績管理數(shù)據(jù)庫項目二 學(xué)生成績管理數(shù)據(jù)庫1數(shù)據(jù)庫結(jié)構(gòu)設(shè)計2SQL Server 數(shù)據(jù)庫結(jié)構(gòu)3視圖4項目實踐數(shù)據(jù)庫結(jié)構(gòu)設(shè)計 數(shù)據(jù)庫設(shè)計步驟：現(xiàn)實世界機器世界 E-R圖數(shù)據(jù)表概念模型邏輯模型數(shù)據(jù)庫結(jié)構(gòu)設(shè)計 需求分析： 需求分析 概念結(jié)構(gòu)設(shè)計 邏輯結(jié)構(gòu)設(shè)計 物理結(jié)構(gòu)設(shè)計 DB實施階段 DB運行，維護數(shù)據(jù)庫結(jié)構(gòu)設(shè)計 概念模型設(shè)計：職工A 職工號B 姓名C 性別實體屬性數(shù)據(jù)庫結(jié)構(gòu)設(shè)計 概念模型設(shè)計：學(xué)生學(xué)號姓名年齡性別學(xué)生 學(xué)號姓名年齡性別課程課程號課程名教師課程課程號課程名教師數(shù)據(jù)庫結(jié)構(gòu)設(shè)計 概念模型設(shè)計：學(xué)生學(xué)習(xí)課程課程學(xué)生選修課程名課

4、程號教師姓名性別年齡mn學(xué)號成績成績成績 一對一 記為 1:1 一對多 記為 1:M 多對多 記為 M:N 數(shù)據(jù)庫結(jié)構(gòu)設(shè)計聯(lián)系：分析聯(lián)系,畫出E-R圖： 學(xué)校的班級有班主任，人數(shù)，專業(yè)。班長有學(xué)號和姓名。一個班級只有一個班長,班長只能負責(zé)本班的工作。 工廠里廠長需要姓名，年齡，工齡，職工除了這些數(shù)據(jù)還需要了解崗位，職務(wù)。一個廠長領(lǐng)導(dǎo)很多職工,職工只能在一個工廠里工作，聽從于廠長。分析聯(lián)系,畫出E-R圖： 教師一般有工號，姓名，年齡，職稱，課程信息有課程號，課程名，學(xué)分和課時。一位教師可以教授多門課,一門課可以由多位教師教授。 每一個研究室包含多位科研人員,每一位科研人員只屬于一個研究室.研究室

5、承當(dāng)了多個科研項目，包括負責(zé)人，項目號項目名和開工時間,多個研究室可以合作完成一個項目.每個科研項目有多位科研人員參加,每位科研人員可以參加多個科研項目.科研人員參加科研項目均有相應(yīng)的工作量。分析聯(lián)系,畫出E-R圖： 校長領(lǐng)導(dǎo)學(xué)校，學(xué)?？梢哉惺諏W(xué)生。校長有職工號、姓名和性別，學(xué)校有學(xué)校代碼、校名和地址，學(xué)生有學(xué)號、姓名和性別。一個校長只能在一個學(xué)校任職，一個學(xué)校也只能有一位校長。一個學(xué)?？梢哉惺斩嗝麑W(xué)生，一個學(xué)生只能報考一個學(xué)校。 數(shù)據(jù)庫結(jié)構(gòu)設(shè)計 邏輯結(jié)構(gòu)設(shè)計： 屬性 主鍵關(guān)系中的每一個屬性值都是不可分解的。關(guān)系中不允許出現(xiàn)相同的元組。關(guān)系中不考慮元組之間的順序。 關(guān)系 元組 S (Sno,S

6、name,Sex,Age,Dept)關(guān)系名(主鍵,屬性2,屬性3.) E-R圖 關(guān)系模式12系聘用教師1m系編號系名稱姓名性別工資教工編號旅客飛機票擁有11身份證號姓名日期航班號座位號價格 系 （系編號，系名稱）教師（教工編號，姓名，性別，工資，系編號）系聘用教師1m系編號系名稱姓名性別工資教工編號例1旅客（身份證號， 日期，姓名，航班號，座位號）飛機票（航班號，座位號，日期，價格）旅客飛機票擁有11身份證號姓名日期航班號座位號價格旅客（身份證號，姓名）飛機票（航班號，座位號，日期，價格，身份證號）例2課程（課程號，課程名，教師）學(xué)生（學(xué)號，姓名，性別，年齡）選修（學(xué)號，課程號，成績）選修課程

7、學(xué)生mn課程號課程名 教師學(xué)號姓名性別年齡成績例31:M1：1M：N1 方主鍵出現(xiàn)在M方成為外鍵任意一方主鍵出現(xiàn)在另一方中，成外鍵聯(lián)系建為一新表，其主鍵由兩個實體的主鍵復(fù)合組成 每個實體建一張表，屬性轉(zhuǎn)為表屬性，關(guān)鍵屬性為主鍵商店（商號，店名，地址）顧客（編號，姓名，電話）消費（編號，商號，日期，金額）nm消費商號店名地址日期電話編號姓名金額顧客商店商店（商店編號，商店名，地址，電話）職工（職工編號，姓名，性別，年齡，商店編號，進店日期，月薪）商店職工m1工作商店編號商店名地址電話進店日期職工編號姓名性別年齡月薪例4車間（車間號，車間名，電話，計劃量,產(chǎn)品號 ）產(chǎn)品（產(chǎn)品號，規(guī)格）或車間（車間

8、號，車間名，電話）產(chǎn)品（產(chǎn)品號，規(guī)格，計劃量,車間號 ）車間產(chǎn)品生產(chǎn)11車間號車間名電話計劃量產(chǎn)品號規(guī)格儲戶存款取款存款單取款單儲戶（賬號，身份證號 ）存款單（賬號，存款單號，存款日期，金額，存款方式）取款單（賬號，取款單號，取款日期，金額，取款方式）11m1存款日期金額存款方式取款日期金額取款方式存款單號取款單號帳號身份證號例5讀者借閱圖書出版出版社mnm1借證號姓名單位借書日期還書日期數(shù)量書名書號單價出版社名地址郵編電話供應(yīng)號供應(yīng)商開戶行帳號供應(yīng)商進貨商品商品號商品名售貨單價單價數(shù)量日期銷售日期數(shù)量員工客戶編號姓名權(quán)限客戶編號姓名 郵編電話1nmnp病房科室病人醫(yī)生入住從屬組成診治1mmn

9、1mm1科名工作證號病歷號病房號處方課后作業(yè)床位號地址電話姓名電話姓名職稱2.2 項目實踐-學(xué)生成績管理數(shù)據(jù)庫列名數(shù)據(jù)類型允許空學(xué)號nchar(7)NO姓名varchar(20)NO性別char(2)YES年齡intYES系varchar(20)NO學(xué)號姓名性別年齡系4123001李慶男18計算機4123002張濤男19計算機4124001孫天昊男20電子4125001計優(yōu)然女19經(jīng)濟管理4125002羅一恒男20經(jīng)濟管理列名數(shù)據(jù)類型允許空課程號intNO課程名vachar(20)YES教師vachar(10)YES課程號課程名教師1001數(shù)據(jù)庫張?zhí)m1002大學(xué)英語李強1003計算機程序設(shè)計王

10、剛1004大學(xué)體育趙蘭蘭學(xué)生表： 課程表: 成績表: 列名數(shù)據(jù)類型允許為空學(xué) 號nchar(7)NO課程號intNO成績intYES 學(xué)號 課程號 成績4123001 1001 704123001 1003 784123002 1004 854125002 1001 804125002 1004 91任務(wù)2-4 新生注冊 學(xué)號姓名性別年齡系4123001李慶男18計算機4123002張濤男19計算機4124001孫天昊男20電子4125001計優(yōu)然女19經(jīng)濟管理4125002羅一恒男20經(jīng)濟管理4152003 蔡青 女 21 信息管理任務(wù)2-5 課程變更 課程號課程名教師1001數(shù)據(jù)庫張?zhí)m10

11、02大學(xué)英語李強1003計算機程序設(shè)計王剛1004大學(xué)體育趙蘭蘭 數(shù)據(jù)庫應(yīng)用實務(wù)任務(wù)2-6 學(xué)生退學(xué) 學(xué)號姓名性別年齡系4123001李慶男18計算機4123002張濤男19計算機4124001孫天昊男20電子4125001計優(yōu)然女19經(jīng)濟管理4125002羅一恒男20經(jīng)濟管理4152003 蔡青 女 21 信息管理融會貫通項目三電子相冊管理系統(tǒng)數(shù)據(jù)庫3.1.1 關(guān)系運算 投影 選擇 積 條件聯(lián)接 自然聯(lián)接 F例1： A B C A B Ca b c b g a d a f d a fc b d R S A Ca cd fc d 1,3(R) C Ac af dd c A B C a b c

12、c b d2=b (R) 、A,C(R) ：、C,A(R) :1、B=b (R) :例2： A B C A B C a b c b g a d a f d a f c b d R S RS:R.A R.B R.C S.A S.B S.C a b c b g a a b c d a f d a f b g a d a f d a f c b d b g a c b d d a f A B C A B C 3 6 7 3 4 52 5 7 7 3 37 3 34 4 3 R S 1、 3,2(S) 、B5(R) 、RS思考：23(R)和23(R)?4、s.A(R.C=S.B (R S)例A B C

13、 D E1 2 3 3 14 5 6 6 27 8 9 R S RS A B C D E 1 2 3 3 1 1 2 3 6 2 4 5 6 3 1 4 5 6 6 2 7 8 9 3 1 7 8 9 6 224( RS) A B C D E 1 2 3 3 1 1 2 3 6 2 4 5 6 6 2R S 即 RS 21 BDA B C D E 1 2 3 3 1 1 2 3 6 2 4 5 6 6 2條件連接求：24( RS) R SA B C D E1 2 3 3 14 5 6 6 27 8 9 R S21 1 2 A B C D E 1 2 3 3 1 4 5 6 6 2 A B C

14、B C Da b c b c dd b c b c eb b f a d bc a d R S 例4R SA B C Da b c da b c ed b c dd b c ec a d b（）記為R S 自然連接 RSR S21RSR S23(R) A B C1 5 72 8 97 0 27 1 2例3-2 計算RS A B C1 5 72 8 97 0 2A B4 16 5RS例3-3 (a) R S (b) R S 122=2A B C1 5 72 8 97 0 2D E4 16 5RS例3-4 計算RS A B C1 5 72 8 97 0 2RC D8 17 52 47 0S單元測驗

15、1、關(guān)系數(shù)據(jù)庫中，將具有共同屬性的兩個關(guān)系中的元組連接到一起，構(gòu)成新表的操作稱為A、選擇 B、投影 C、連接 D、掃描 2、一般情況下，當(dāng)對關(guān)系R和S使用自然連接時，要求R和S至少含有一個共同的 .A 元組 B 行 C 關(guān)系 D 屬性 3、如下兩個關(guān)系R1和R2，它們進行 運算后得到R3A 選擇 B 選擇連接 C 笛卡兒積 D 自然連接 R1 R2A B CA 1 XC 2 YD 1 YB D E1 M I2 N J5 M K R3A B C D EA 1 X M ID 1 Y M IC 2 Y N J4、設(shè)有如圖所示的關(guān)系R和S，計算RSABCabcbabcfd關(guān)系 RABCbdaafc 關(guān)

16、系 S5、設(shè)有關(guān)系R和S如下圖所示，計算C，D（RS） R ABCa5Ce8Fa4GSBDE4ac4eg8bAABC359230368BCD302686305R A,C(R) D3課堂作業(yè)：User表:項目三電子相冊管理系統(tǒng)數(shù)據(jù)庫class表:photo表:cp表: 任務(wù)1. T-SQL創(chuàng)建數(shù)據(jù)庫Create Database 數(shù)據(jù)庫名ON ( Name=數(shù)據(jù)文件名, Filename=“文件路徑名”, Size=大小, Maxsize=大小, Filegrowth=大小, )Log ON( 同上) 要求是數(shù)據(jù)庫名為mypic，指定數(shù)據(jù)文件位于D:pmDb文件夾中。初始容量為5MB，最大容量為

17、10MB，文件增量為10%。T-SQL“文件路徑名”格式：盤符文件夾.數(shù)據(jù)庫名.mdfcreate database mypicon( name=mypic, filename=”d:pmDbmypic.mdf”, size=5, maxsize=10, filegrowth=10% )任務(wù)2.T-SQL創(chuàng)建 cp表結(jié)構(gòu)?主鍵?數(shù)據(jù)?主鍵、外鍵任務(wù)2.T-SQL創(chuàng)建Create Table 表名 ( 列名1 數(shù)據(jù)類型 列級約束， 列名2 數(shù)據(jù)類型 列級約束， primary key(主鍵)， Foreign key(外鍵) references 表(主鍵)） USE mypicCREATE T

18、ABLE cp(CID int,PID int,ptime date not null,PRIMARY KEY(CID,PID),FOREIGN KEY(CID) references class(CID),FOREIGN KEY(PID) references photo(PID) )練習(xí)時間 創(chuàng)建保存網(wǎng)站用戶的表：user表列名數(shù)據(jù)類型允許為空賬號VarChar(10)NO密碼Varchar(10)NO任務(wù)3.T-SQL完成cp表數(shù)據(jù)的添加Insert into 表名(屬性列表)Values (元組值)INSERT INTO cp(CID,PID,ptime)VALUES (1,1001,

19、2017-6-1)CIDPIDptime 1 10012017-6-1 2 10012017-6-1 110022017-68-21210042017-810-11410062015-10-11為user表完成兩個用戶注冊帳號密碼aaa123bbb456列名數(shù)據(jù)類型允許為空賬號VarChar(10)NO密碼Varchar(10)NO練習(xí)時間SQL生活中應(yīng)用之微博：微博賬號注冊個人資料修改賬號登陸T-SQL查詢語句Select 屬性From 表Where 條件T-SQL簡單查詢(1) 查詢男性用戶的UID，姓名，性別和qq號。命 令:selec

20、t UID,username,sex,qqfrom userswhere sex=男T-SQL簡單查詢(2) 查詢101用戶所建相冊的相冊名和創(chuàng)建時間。命 令select cname,ctimefrom classwhere UID=101任務(wù)7 T-SQL模糊查詢(1)查詢使用qq郵箱注冊的用戶信息。命 令:select *from userswhere email like %任務(wù)7 T-SQL模糊查詢(2)查詢用戶名為s或f開頭的用戶的信息。命 令:select *from userswhere username like sf%任務(wù)7 T-SQL模糊查詢(3)查詢創(chuàng)建日期在5月份的相冊

21、信息。命 令:select *from classwhere ctime like _05%任務(wù)8 T-SQL多表查詢連接查詢(1)查詢用戶simon所創(chuàng)建的相冊ID，相冊名稱和相冊描述。命 令:select CID,cname,c_descfrom users,classwhere username=simonand users.UID=class.UID任務(wù)8 T-SQL多表查詢連接查詢(2) 查詢2號相冊的所有照片的ID，標(biāo)題和存放地址。要求顯示結(jié)果按照照片的ID從大到小排序。命 令:select photo.PID,title,picfrom cp,photowhere CID=2an

22、d cp.PID=photo.PIDorder by PID desc任務(wù)8 T-SQL多表查詢連接查詢(3) 查詢相冊名為“我的學(xué)校”，它的所有照片的ID,標(biāo)題，描述和照片時間。按照照片時間排序，如果同一時間，按PID降序排列。命 令:select cp.PID,title,p_desc,ptimefrom photo,class,cpwhere cname=我的學(xué)校and photo.PID=cp.PIDand class.CID=cp.CIDorder by ptime,PID desc任務(wù)9 T-SQL多表查詢子查詢(1) 查詢用戶mike所創(chuàng)建的相冊名，相冊描述。命 令:select

23、 cname,c_descfrom classwhere UID in ( select UID from users where username=mike )任務(wù)9 T-SQL多表查詢子查詢(2) 查詢還沒有創(chuàng)建相冊的用戶ID,用戶名。命 令:select UID,usernamefrom userswhere UID not in( select distinct UID from class)任務(wù)10 T-SQL統(tǒng)計查詢(1)統(tǒng)計103號用戶所創(chuàng)建的相冊數(shù)量。命 令:select count(*) from class where UID=101任務(wù)10 T-SQL統(tǒng)計查詢(2)統(tǒng)計每

24、個用戶的創(chuàng)建相冊數(shù)量，要求顯示用戶的ID和對應(yīng)的房屋數(shù)目。命 令:select UID as 用戶ID,count(*) as 相冊數(shù)from classgroup by UID任務(wù)10 T-SQL統(tǒng)計查詢(3) 統(tǒng)計照片數(shù)大于等于3張的相冊，要求顯示照片ID和標(biāo)題。命 令:select cp.CID,count(*)from cpgroup by CIDhaving count(*)=3項目四.商品進銷存管理數(shù)據(jù)庫數(shù)據(jù)完整性數(shù)據(jù)完整性主要分為實體完整性域完整性參照完整性用戶定義完整性約束SQL Server中提供了6中類型的約束：NOT NULL（非空）約束PRIMARY KEY （主鍵）約

25、束FOREIGN KEY（外鍵）約束UNIQUE（唯一）約束CHECK（檢查）約束DEFAULT（默認值）約束存儲過程1 存儲過程的創(chuàng)建CREATE PROC EDURE procedure_name ; number parameter data_type VARYING = default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATION AS sql_statement .n 存儲過程2 存儲過程的修改ALTER PROC EDURE procedure_name ; numbe

26、r parameter data_type VARYING = default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATION AS sql_statement .n 4.2.4觸發(fā)器在SQL Server 2012中觸發(fā)器分為DML和DDL觸發(fā)器兩種，分別在數(shù)據(jù)庫中發(fā)生數(shù)據(jù)操作（INSERT、UPDATE和DLETE）和數(shù)據(jù)定義（CREATE、ALTER、DROP）事件時執(zhí)行。觸發(fā)器的創(chuàng)建CREATE TRIGGER trigger_nameONtable|viewWITH ENCRYPTIONFOR|AFTER|INSTEAD OFDELETE,INSERT,UPDATE ASSQL語句n2觸發(fā)器的修改ALT