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

1、數(shù)據(jù)庫應(yīng)用項(xiàng)目化教程項(xiàng)目一校園論壇管理數(shù)據(jù)庫1基本概念2數(shù)據(jù)庫體系結(jié)構(gòu) 數(shù)據(jù)(Data)：描述客觀事物特征和特性的符號(hào)描述學(xué)生:學(xué)號(hào)- G13120026姓名- 王小麗電話- 1377456789年齡- 10照片- 數(shù)據(jù)庫(DataBase)：以一定組織方式存儲(chǔ)在一起的、統(tǒng)一管理的相關(guān)數(shù)據(jù)的集合學(xué)生DB描述學(xué)生:學(xué)號(hào)- G13120026姓名- 王小麗電話- 1377456789年齡- 10照片-教師輔導(dǎo)員教務(wù)領(lǐng)導(dǎo)問題一：數(shù)據(jù)庫技術(shù)的根本目標(biāo)是解決數(shù)據(jù)的- A 存儲(chǔ)問題 B 共享問題 C 安全問題 D 保護(hù)問題 數(shù)據(jù)庫管理系統(tǒng)( DBMS )：對(duì)數(shù)據(jù)庫進(jìn)行管理的軟件數(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) 單機(jī)結(jié)構(gòu) C/S結(jié)構(gòu) B/S結(jié)構(gòu) 外部體系結(jié)構(gòu)： 單機(jī)結(jié)構(gòu) C/S結(jié)構(gòu) B/S結(jié)構(gòu)ClientDB ServerClientClientC/S結(jié)構(gòu)Server騰訊DBDBMS你有強(qiáng)迫癥嗎？思考C/S式的優(yōu)缺點(diǎn) 外部體系結(jié)構(gòu)： 單機(jī)結(jié)構(gòu) C/S結(jié)構(gòu) B/S結(jié)構(gòu)BrowserDB ServerWeb ServerBrowserBrows

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

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

5、承當(dāng)了多個(gè)科研項(xiàng)目，包括負(fù)責(zé)人，項(xiàng)目號(hào)項(xiàng)目名和開工時(shí)間,多個(gè)研究室可以合作完成一個(gè)項(xiàng)目.每個(gè)科研項(xiàng)目有多位科研人員參加,每位科研人員可以參加多個(gè)科研項(xiàng)目.科研人員參加科研項(xiàng)目均有相應(yīng)的工作量。分析聯(lián)系,畫出E-R圖： 校長領(lǐng)導(dǎo)學(xué)校，學(xué)校可以招收學(xué)生。校長有職工號(hào)、姓名和性別，學(xué)校有學(xué)校代碼、校名和地址，學(xué)生有學(xué)號(hào)、姓名和性別。一個(gè)校長只能在一個(gè)學(xué)校任職，一個(gè)學(xué)校也只能有一位校長。一個(gè)學(xué)?？梢哉惺斩嗝麑W(xué)生，一個(gè)學(xué)生只能報(bào)考一個(gè)學(xué)校。 數(shù)據(jù)庫結(jié)構(gòu)設(shè)計(jì) 邏輯結(jié)構(gòu)設(shè)計(jì)： 屬性 主鍵關(guān)系中的每一個(gè)屬性值都是不可分解的。關(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系編號(hào)系名稱姓名性別工資教工編號(hào)旅客飛機(jī)票擁有11身份證號(hào)姓名日期航班號(hào)座位號(hào)價(jià)格 系 （系編號(hào)，系名稱）教師（教工編號(hào)，姓名，性別，工資，系編號(hào)）系聘用教師1m系編號(hào)系名稱姓名性別工資教工編號(hào)例1旅客（身份證號(hào)， 日期，姓名，航班號(hào)，座位號(hào)）飛機(jī)票（航班號(hào)，座位號(hào)，日期，價(jià)格）旅客飛機(jī)票擁有11身份證號(hào)姓名日期航班號(hào)座位號(hào)價(jià)格旅客（身份證號(hào)，姓名）飛機(jī)票（航班號(hào)，座位號(hào)，日期，價(jià)格，身份證號(hào)）例2課程（課程號(hào)，課程名，教師）學(xué)生（學(xué)號(hào)，姓名，性別，年齡）選修（學(xué)號(hào)，課程號(hào)，成績）選修課程

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

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

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

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

11、02大學(xué)英語李強(qiáng)1003計(jì)算機(jī)程序設(shè)計(jì)王剛1004大學(xué)體育趙蘭蘭 數(shù)據(jù)庫應(yīng)用實(shí)務(wù)任務(wù)2-6 學(xué)生退學(xué) 學(xué)號(hào)姓名性別年齡系4123001李慶男18計(jì)算機(jī)4123002張濤男19計(jì)算機(jī)4124001孫天昊男20電子4125001計(jì)優(yōu)然女19經(jīng)濟(jì)管理4125002羅一恒男20經(jīng)濟(jì)管理4152003 蔡青 女 21 信息管理融會(huì)貫通項(xiàng)目三電子相冊管理系統(tǒng)數(shù)據(jù)庫3.1.1 關(guān)系運(yù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 計(jì)算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 計(jì)算RS A B C1 5 72 8 97 0 2RC D8 17 52 47 0S單元測驗(yàn)

15、1、關(guān)系數(shù)據(jù)庫中，將具有共同屬性的兩個(gè)關(guān)系中的元組連接到一起，構(gòu)成新表的操作稱為A、選擇 B、投影 C、連接 D、掃描 2、一般情況下，當(dāng)對(duì)關(guān)系R和S使用自然連接時(shí)，要求R和S至少含有一個(gè)共同的 .A 元組 B 行 C 關(guān)系 D 屬性 3、如下兩個(gè)關(guān)系R1和R2，它們進(jìn)行 運(yùn)算后得到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，計(jì)算RSABCabcbabcfd關(guān)系 RABCbdaafc 關(guān)

16、系 S5、設(shè)有關(guān)系R和S如下圖所示，計(jì)算C，D（RS） R ABCa5Ce8Fa4GSBDE4ac4eg8bAABC359230368BCD302686305R A,C(R) D3課堂作業(yè)：User表:項(xiàng)目三電子相冊管理系統(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ù)類型 列級(jí)約束， 列名2 數(shù)據(jù)類型 列級(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í)時(shí)間 創(chuàng)建保存網(wǎng)站用戶的表：user表列名數(shù)據(jù)類型允許為空賬號(hào)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表完成兩個(gè)用戶注冊帳號(hào)密碼aaa123bbb456列名數(shù)據(jù)類型允許為空賬號(hào)VarChar(10)NO密碼Varchar(10)NO練習(xí)時(shí)間SQL生活中應(yīng)用之微博：微博賬號(hào)注冊個(gè)人資料修改賬號(hào)登陸T-SQL查詢語句Select 屬性From 表Where 條件T-SQL簡單查詢(1) 查詢男性用戶的UID，姓名，性別和qq號(hào)。命 令:selec

20、t UID,username,sex,qqfrom userswhere sex=男T-SQL簡單查詢(2) 查詢101用戶所建相冊的相冊名和創(chuàng)建時(shí)間。命 令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號(hào)相冊的所有照片的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)題，描述和照片時(shí)間。按照照片時(shí)間排序，如果同一時(shí)間，按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)計(jì)查詢(1)統(tǒng)計(jì)103號(hào)用戶所創(chuàng)建的相冊數(shù)量。命 令:select count(*) from class where UID=101任務(wù)10 T-SQL統(tǒng)計(jì)查詢(2)統(tǒng)計(jì)每

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

25、束FOREIGN KEY（外鍵）約束UNIQUE（唯一）約束CHECK（檢查）約束DEFAULT（默認(rèn)值）約束存儲(chǔ)過程1 存儲(chǔ)過程的創(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 存儲(chǔ)過程2 存儲(chǔ)過程的修改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）事件時(shí)執(zhí)行。觸發(fā)器的創(chuàng)建CREATE TRIGGER trigger_nameONtable|viewWITH ENCRYPTIONFOR|AFTER|INSTEAD OFDELETE,INSERT,UPDATE ASSQL語句n2觸發(fā)器的修改ALT