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

1、數(shù)據(jù)庫應用項目化教程項目一校園論壇管理數(shù)據(jù)庫1基本概念2數(shù)據(jù)庫體系結構 數(shù)據(jù)(Data)：描述客觀事物特征和特性的符號描述學生:學號- G13120026姓名- 王小麗電話- 1377456789年齡- 10照片- 數(shù)據(jù)庫(DataBase)：以一定組織方式存儲在一起的、統(tǒng)一管理的相關數(shù)據(jù)的集合學生DB描述學生:學號- G13120026姓名- 王小麗電話- 1377456789年齡- 10照片-教師輔導員教務領導問題一：數(shù)據(jù)庫技術的根本目標是解決數(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 )：應用程序DBMS數(shù)據(jù)庫DB應用程序應用程序DBA問題二：數(shù)據(jù)庫，數(shù)據(jù)庫系統(tǒng)，數(shù)據(jù)庫管理系統(tǒng)之間的關系是 A DB包含DBS和DBMS B DBMS包含DB和DBS C DBS包含DB和DBMS2.數(shù)據(jù)庫體系結構 外部體系結構 單機結構 C/S結構 B/S結構 外部體系結構： 單機結構 C/S結構 B/S結構ClientDB ServerClientClientC/S結構Server騰訊DBDBMS你有強迫癥嗎？思考C/S式的優(yōu)缺點 外部體系結構： 單機結構 C/S結構 B/S結構BrowserDB ServerWeb ServerBrowserBrows

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

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

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

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

7、學生mn課程號課程名 教師學號姓名性別年齡成績例31:M1：1M：N1 方主鍵出現(xiàn)在M方成為外鍵任意一方主鍵出現(xiàn)在另一方中，成外鍵聯(liá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ù)量書名書號單價出版社名地址郵編電話供應號供應商開戶行帳號供應商進貨商品商品號商品名售貨單價單價數(shù)量日期銷售日期數(shù)量員工客戶編號姓名權限客戶編號姓名 郵編電話1nmnp病房科室病人醫(yī)生入住從屬組成診治1mmn

9、1mm1科名工作證號病歷號病房號處方課后作業(yè)床位號地址電話姓名電話姓名職稱2.2 項目實踐-學生成績管理數(shù)據(jù)庫列名數(shù)據(jù)類型允許空學號nchar(7)NO姓名varchar(20)NO性別char(2)YES年齡intYES系varchar(20)NO學號姓名性別年齡系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大學英語李強1003計算機程序設計王

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

11、02大學英語李強1003計算機程序設計王剛1004大學體育趙蘭蘭 數(shù)據(jù)庫應用實務任務2-6 學生退學 學號姓名性別年齡系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 關系運算 投影 選擇 積 條件聯(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、關系數(shù)據(jù)庫中，將具有共同屬性的兩個關系中的元組連接到一起，構成新表的操作稱為A、選擇 B、投影 C、連接 D、掃描 2、一般情況下，當對關系R和S使用自然連接時，要求R和S至少含有一個共同的 .A 元組 B 行 C 關系 D 屬性 3、如下兩個關系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、設有如圖所示的關系R和S，計算RSABCabcbabcfd關系 RABCbdaafc 關

16、系 S5、設有關系R和S如下圖所示，計算C，D（RS） R ABCa5Ce8Fa4GSBDE4ac4eg8bAABC359230368BCD302686305R A,C(R) D3課堂作業(yè)：User表:項目三電子相冊管理系統(tǒng)數(shù)據(jù)庫class表:photo表:cp表: 任務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% )任務2.T-SQL創(chuàng)建 cp表結構?主鍵?數(shù)據(jù)?主鍵、外鍵任務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) )練習時間 創(chuàng)建保存網(wǎng)站用戶的表：user表列名數(shù)據(jù)類型允許為空賬號VarChar(10)NO密碼Varchar(10)NO任務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練習時間SQL生活中應用之微博：微博賬號注冊個人資料修改賬號登陸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任務7 T-SQL模糊查詢(1)查詢使用qq郵箱注冊的用戶信息。命 令:select *from userswhere email like %任務7 T-SQL模糊查詢(2)查詢用戶名為s或f開頭的用戶的信息。命 令:select *from userswhere username like sf%任務7 T-SQL模糊查詢(3)查詢創(chuàng)建日期在5月份的相冊

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

22、d cp.PID=photo.PIDorder by PID desc任務8 T-SQL多表查詢連接查詢(3) 查詢相冊名為“我的學?！保乃姓掌腎D,標題，描述和照片時間。按照照片時間排序，如果同一時間，按PID降序排列。命 令:select cp.PID,title,p_desc,ptimefrom photo,class,cpwhere cname=我的學校and photo.PID=cp.PIDand class.CID=cp.CIDorder by ptime,PID desc任務9 T-SQL多表查詢子查詢(1) 查詢用戶mike所創(chuàng)建的相冊名，相冊描述。命 令:select

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

24、個用戶的創(chuàng)建相冊數(shù)量，要求顯示用戶的ID和對應的房屋數(shù)目。命 令:select UID as 用戶ID,count(*) as 相冊數(shù)from classgroup by UID任務10 T-SQL統(tǒng)計查詢(3) 統(tǒng)計照片數(shù)大于等于3張的相冊，要求顯示照片ID和標題。命 令: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