計(jì)算機(jī)基礎(chǔ)與程序設(shè)計(jì)_第9章

1、SQL Server數(shù)據(jù)庫(kù)基礎(chǔ)本章將介紹數(shù)據(jù)庫(kù)的基本概念，基本的使用方法。主要內(nèi)容創(chuàng)建用戶(hù)數(shù)據(jù)庫(kù)創(chuàng)建表創(chuàng)建多表視圖用SQL管理數(shù)據(jù)庫(kù)用SQL查詢(xún)數(shù)據(jù)庫(kù)任務(wù)1創(chuàng)建用戶(hù)數(shù)據(jù)庫(kù) 在SQL Server 2008 R2中創(chuàng)建一個(gè)用戶(hù)數(shù)據(jù)庫(kù)，數(shù)據(jù)庫(kù)名稱(chēng)為“jxgl”。學(xué)習(xí)目的：學(xué)習(xí)數(shù)據(jù)庫(kù)的基本概念；學(xué)習(xí)創(chuàng)建、修改、刪除數(shù)據(jù)庫(kù)的方法； 相關(guān)知識(shí)1數(shù)據(jù)庫(kù)的基本概念1、數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù)（Database，簡(jiǎn)稱(chēng)為DB）是長(zhǎng)期存儲(chǔ)在計(jì)算機(jī)內(nèi)、有組織的、可共享的、統(tǒng)一管理的相關(guān)數(shù)據(jù)的集合。2、數(shù)據(jù)庫(kù)管理系統(tǒng)數(shù)據(jù)庫(kù)管理系統(tǒng)（Database Management System，簡(jiǎn)稱(chēng)DBMS）是用戶(hù)與數(shù)據(jù)庫(kù)之間的一個(gè)數(shù)據(jù)管

2、理軟件，它的主要任務(wù)是對(duì)數(shù)據(jù)庫(kù)的建立、運(yùn)用和維護(hù)進(jìn)行統(tǒng)一管理、統(tǒng)一控制。即用戶(hù)不能直接接觸數(shù)據(jù)庫(kù)，而只能通過(guò)DBMS來(lái)操作數(shù)據(jù)庫(kù)。3、數(shù)據(jù)模型數(shù)據(jù)模型是指數(shù)據(jù)庫(kù)管理系統(tǒng)中數(shù)據(jù)的存儲(chǔ)結(jié)構(gòu)，是構(gòu)建數(shù)據(jù)庫(kù)的基礎(chǔ)。常見(jiàn)的有層次模型、網(wǎng)狀模型和關(guān)系模型。關(guān)系模型是一種應(yīng)用最多的數(shù)據(jù)模型。相關(guān)知識(shí)1SQL Server概述 SQL Server是Microsoft推出的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，Microsoft在2008年發(fā)布了SQL Server 2008.功能：全面的數(shù)據(jù)和用戶(hù)管理特點(diǎn)：良好的圖形用戶(hù)界面、強(qiáng)大的幫助功能SQL Server系統(tǒng)數(shù)據(jù)庫(kù)兩類(lèi)數(shù)據(jù)庫(kù)：系統(tǒng)和用戶(hù)數(shù)據(jù)庫(kù)在安裝SQL Server

3、 2008時(shí)系統(tǒng)創(chuàng)建了4個(gè)系統(tǒng)數(shù)據(jù)庫(kù)：master ：重要model：模板msdb：存儲(chǔ)計(jì)劃信息tempdb：臨時(shí)主文件擴(kuò)展名：.mdf日志文件擴(kuò)展名：.ldfSQL Server 用戶(hù)數(shù)據(jù)庫(kù)用戶(hù)數(shù)據(jù)庫(kù)至少具有兩個(gè)文件：一個(gè)數(shù)據(jù)文件和一個(gè)日志文件。SQL Server 數(shù)據(jù)庫(kù)對(duì)象1）表：是最主要的數(shù)據(jù)庫(kù)對(duì)象，用來(lái)存儲(chǔ)和錯(cuò)著數(shù)據(jù)的邏輯結(jié)構(gòu)。2）索引3）視圖：是從一個(gè)或者多個(gè)基本表中引出的表。4）約束5）默認(rèn)值6）觸發(fā)器7）存儲(chǔ)過(guò)程：是一組為了完成特定功能的SQL語(yǔ)句集合。數(shù)據(jù)庫(kù)的修改數(shù)據(jù)庫(kù)的刪除任務(wù)2創(chuàng)建表 在任務(wù)1創(chuàng)建的數(shù)據(jù)庫(kù)“jxgl”中，創(chuàng)建一個(gè)關(guān)于學(xué)生基本信息的表，表名為“xs”，表結(jié)構(gòu)

4、如課本P273表9-1所示。學(xué)習(xí)目的：學(xué)習(xí)創(chuàng)建數(shù)據(jù)表；學(xué)習(xí)表、字段、數(shù)據(jù)類(lèi)型的概念；學(xué)習(xí)數(shù)據(jù)完整性及約束的概念；學(xué)習(xí)索引的概念； 相關(guān)知識(shí)2表的概念表用于存儲(chǔ)數(shù)據(jù)庫(kù)中數(shù)據(jù)，是數(shù)據(jù)庫(kù)的基礎(chǔ)，一個(gè)數(shù)據(jù)庫(kù)通常會(huì)包含一個(gè)或者多個(gè)表。表是一個(gè)二位結(jié)構(gòu)，由行和列組成。字段：列稱(chēng)為字段，每個(gè)列都有列名和其自由的屬性。記錄：行稱(chēng)為記錄，記錄由主鍵唯一確定。表結(jié)構(gòu)：多個(gè)列即構(gòu)成了表結(jié)構(gòu)。NULL：為空值。主鍵：如果某一個(gè)字段可以唯一標(biāo)識(shí)一條記錄，可以將該字段指定為主鍵。相關(guān)知識(shí)2字段和字段類(lèi)型類(lèi)型名稱(chēng)數(shù)據(jù)類(lèi)型長(zhǎng)度/字節(jié)說(shuō)明整數(shù)型bigint整數(shù)8取值為-263263-1int整數(shù)4取值為-231231-1sma

5、llint整數(shù)2取值為-3276832767tinyint整數(shù)1取值為0255二進(jìn)制型binary(n)二進(jìn)制由用戶(hù)指定固定長(zhǎng)度的二進(jìn)制數(shù)據(jù)，最大長(zhǎng)度為8000字節(jié)varbinary(n)可變長(zhǎng)度的二進(jìn)制由用戶(hù)指定最大長(zhǎng)度為8000字節(jié)位型bit位1位僅存儲(chǔ)1或0字符型char(n)字符由用戶(hù)指定nchar(n)字符由用戶(hù)指定相關(guān)知識(shí)2date日期30001-1-19999-12-31datetime日期和時(shí)間81753-1-19999-12-31smalldatetime日期和時(shí)間41900-1-12079-12-31浮點(diǎn)型float浮點(diǎn)數(shù)8-1.79E+3081.79E+308real浮點(diǎn)

6、數(shù)4-3.40E+383.4E+38圖像型image圖像、視頻、聲音二進(jìn)制存儲(chǔ)，最大長(zhǎng)度為230-1字節(jié)貨幣型money貨幣8-263263-1類(lèi)型名稱(chēng)數(shù)據(jù)類(lèi)型長(zhǎng)度/字節(jié)說(shuō)明日期時(shí)間相關(guān)知識(shí)2數(shù)據(jù)完整性1.實(shí)體完整性2.域完整性3.參照完整性4.用戶(hù)自定義完整性約束索引1.目的：為了提高查找速度2.索引類(lèi)型：（1）聚集索引 （2）非聚集索引任務(wù)3 創(chuàng)建多表要求：在“jxgl”數(shù)據(jù)庫(kù)中，已經(jīng)創(chuàng)建了學(xué)生表“xs”，再創(chuàng)建3個(gè)表：1）教師表“js”：描述教師基本信息；2）課程表“kc”：描述課程基本信息；3）選課表“xk”：選課的情況，即哪個(gè)學(xué)生選了哪個(gè)老師的哪門(mén)課；目的：學(xué)習(xí)多表概念；學(xué)習(xí)主鍵和外

7、鍵的概念；學(xué)習(xí)使用參照完整性；任務(wù)4 視圖要求：在任務(wù)3創(chuàng)建的“jxgl”數(shù)據(jù)庫(kù)中，按如下要求完成查詢(xún)：1）查詢(xún)所有學(xué)生的選課情況，包括學(xué)生、課程和任課教師的基本情況；2）查詢(xún)“李牧”的選課情況，包括課程和任課教師的基本情況；3）查詢(xún)“機(jī)械063”班并且性別為“男”的學(xué)生都選了哪些課程；目的：學(xué)習(xí)關(guān)系的概念、關(guān)系的相關(guān)操作；掌握一對(duì)一關(guān)系、一對(duì)多關(guān)系和多對(duì)多關(guān)系的概念；學(xué)習(xí)視圖的概念、視圖的相關(guān)操作。學(xué)習(xí)篩選條件的使用方法。相關(guān)知識(shí)4視圖的概念視圖是一種數(shù)據(jù)庫(kù)對(duì)象，它是從一個(gè)或多個(gè)表或視圖中導(dǎo)出的動(dòng)態(tài)表，即它可以從一個(gè)或多個(gè)表中的一個(gè)或者多個(gè)列提取數(shù)據(jù)，并按照行和列來(lái)顯示。數(shù)據(jù)庫(kù)關(guān)系圖在數(shù)據(jù)庫(kù)

8、技術(shù)中，有三種關(guān)系類(lèi)型，分別是一對(duì)一關(guān)系、一對(duì)多關(guān)系和多對(duì)多關(guān)系。(1)一對(duì)一關(guān)系：若建立關(guān)系的兩個(gè)表，被關(guān)聯(lián)的字段在兩個(gè)表中分別是主鍵，則建立的關(guān)系為一對(duì)一關(guān)系。 (2)一對(duì)多關(guān)系：若建立關(guān)系的兩個(gè)表，被關(guān)聯(lián)的字段僅有一個(gè)是主鍵，則建立的關(guān)系為一對(duì)一關(guān)系。 (3)多對(duì)多關(guān)系：多對(duì)多關(guān)系必須借助第三個(gè)表實(shí)現(xiàn)。如一個(gè)學(xué)生可以選修多門(mén)課程，一門(mén)課程可以被多名學(xué)生選修，學(xué)生表和課程表是多對(duì)多關(guān)系，可以借助選課表實(shí)現(xiàn)多對(duì)多關(guān)系。任務(wù)5 用SQL管理數(shù)據(jù)庫(kù)要求：在SQL Server中使用SQL語(yǔ)言創(chuàng)建一個(gè)學(xué)生選課數(shù)據(jù)庫(kù)（xsxk），要求包含如下兩個(gè)表的信息；1）學(xué)生表：包含學(xué)號(hào)、姓名、性別字段，“學(xué)號(hào)

9、”為主鍵。2）選課表：包含學(xué)號(hào)、課程編號(hào)字段，“學(xué)號(hào)+課程編號(hào)”為主鍵。3）為選課表設(shè)置外鍵“學(xué)號(hào)”；目的：學(xué)習(xí)T-SQL的基本使用方法；學(xué)習(xí)使用T-SQL語(yǔ)言創(chuàng)建數(shù)據(jù)庫(kù)的方法；學(xué)習(xí)使用T-SQL語(yǔ)言創(chuàng)建表的方法；學(xué)習(xí)使用T-SQL語(yǔ)言創(chuàng)建外鍵、主鍵的方法；學(xué)習(xí)使用T-SQL語(yǔ)言設(shè)置默認(rèn)值的方法；相關(guān)知識(shí)5SQL概述SQL是結(jié)構(gòu)化查詢(xún)語(yǔ)言（Structured Query Language）的縮寫(xiě)。SQL是一種一體化的語(yǔ)言，所有的關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)都支持SQL，其功能包括數(shù)據(jù)查詢(xún)、數(shù)據(jù)操縱、數(shù)據(jù)定義和數(shù)據(jù)控制四個(gè)部分。使用CREATE DATABASE 命令創(chuàng)建數(shù)據(jù)庫(kù)CREATE DATABA

10、SE 數(shù)據(jù)庫(kù)名 ON PRIMARY | FILEGROUP 文件組名 （NAME=數(shù)據(jù)文件的邏輯名， FILENAME=數(shù)據(jù)文件的物理名 ，SIZE=文件初始大小 ，MAXSIZE=增長(zhǎng)到的最大容量|UNLIMITED ，F(xiàn)ILEGROWTH=文件空間的增量 ) ，() 使用CREATE DATABASE 命令創(chuàng)建數(shù)據(jù)庫(kù) LOG ON （NAME=日志文件的邏輯名， FILENAME=日志文件的物理名 ，SIZE=文件初始大小 ，MAXSIZE=增長(zhǎng)到的最大容量|UNLIMITED ，F(xiàn)ILEGROWTH=文件空間的增量 ） ，（） 在命令格式中，用 括起來(lái)的內(nèi)容表示是可選的； ，（）表示重

11、復(fù)前面的內(nèi)容；用括起來(lái)表示在實(shí)際編寫(xiě)語(yǔ)句時(shí)，用相應(yīng)的內(nèi)容替代；用 括起來(lái)表示是必選的；類(lèi)似A|B的格式，表示A和B只能選擇一個(gè)，不能同時(shí)都選。數(shù)據(jù)庫(kù)名稱(chēng)在服務(wù)器中必須唯一，最長(zhǎng)為128個(gè)字符，并且要符合標(biāo)識(shí)符的命名規(guī)則。每個(gè)服務(wù)器管理的數(shù)據(jù)庫(kù)最多為32767個(gè)。ON：指定存放數(shù)據(jù)庫(kù)的數(shù)據(jù)文件信息。LOG ON：指明事務(wù)日志文件的相關(guān)定義。如果沒(méi)有本選項(xiàng)，則系統(tǒng)會(huì)自動(dòng)產(chǎn)生一個(gè)文件名前綴 與數(shù)據(jù)庫(kù)名相同，容量為所有數(shù)據(jù)庫(kù)文件 大小1/3的事務(wù)日志文件。T-SQL 語(yǔ)句命令的表操作create table ( 列名1 列的數(shù)據(jù)類(lèi)型及寬度等特征， 列名2 列的數(shù)據(jù)類(lèi)型及寬度等特征，）ALTER TAB

12、LE命令的語(yǔ)法如下：ALTER TABLE ADD 列約束 ，. n |WITH CHECK|WITH NOCHECK ADD ，. n |DROP COLUMN 列名|CONSTRAINT 約束名 ，. n |ALTER COLUMN 列名 新數(shù)據(jù)類(lèi)型（新數(shù)據(jù)寬度，新小數(shù)位數(shù)） | CHECK|NOCHECK CONSTRAINT ALL 約束名 ，. n ALTER TABLE 命令增加列增加列約束啟用或禁止約束新添加的外鍵或檢查約束獨(dú)立增加約束刪除列刪除約束修改列啟用或禁止約束任務(wù)6 用SQL查詢(xún)數(shù)據(jù)庫(kù)要求：使用SQL語(yǔ)句，查詢(xún)?nèi)蝿?wù)5數(shù)據(jù)庫(kù)中學(xué)生選課情況，按如下要求查詢(xún)；1）查詢(xún)所有學(xué)生

13、的選課情況；2）查詢(xún)“zhang”的選課情況；3）按“學(xué)號(hào)”分組顯示每個(gè)學(xué)生的課程數(shù)；目的：學(xué)習(xí)INSERT語(yǔ)句的使用方法；學(xué)習(xí)SQL語(yǔ)句創(chuàng)建視圖的方法；學(xué)習(xí)SQLECT語(yǔ)句的使用方法；學(xué)習(xí)UPDATE語(yǔ)句的使用方法；學(xué)習(xí)DELETE語(yǔ)句的使用方法；學(xué)習(xí)SQL中表達(dá)式的使用方法；相關(guān)知識(shí)6SQL查詢(xún)語(yǔ)法結(jié)構(gòu)查詢(xún)語(yǔ)句SELECTSELECT命令的語(yǔ)句格式：SELECT ALL / DISTINCT */字段列表FROM , WHERE條件表達(dá)式GROUP BY HAVING ORDER BY ASC/DESCApplication Logic客戶(hù)程序查詢(xún)請(qǐng)求查詢(xún)結(jié)果集SQL SERVERA B

14、C D E F G SELECT * FROM tblstudent查詢(xún)產(chǎn)生一個(gè)虛擬表，看到的是表形式顯示的結(jié)果，但結(jié)果并不真正存儲(chǔ)，每次執(zhí)行查詢(xún)只是現(xiàn)從數(shù)據(jù)表中提取數(shù)據(jù)，并按照表的形式顯示出來(lái)什么是查詢(xún)？查詢(xún)?nèi)康男泻土蠸ELECT * FROM tblstudent 或SELECT stud_id,stud_name,stud_sex,birth,enroll_date, identity_id,class_id FROM tblstudent查詢(xún)部分列SELECT stud_id,stud_name FROM tblstudentSELECT stud_name, year(getdat

15、e() - year(birth) FROM tblstudent數(shù)據(jù)查詢(xún)-基礎(chǔ)查詢(xún)經(jīng)過(guò)計(jì)算值消除取值重復(fù)的行SELECT distinct stud_id FROM tblscoreSELECT top 3 * FROM tblstudentSELECT top 3 percent * FROM tblstudent 比較大?。ê?jiǎn)單查詢(xún)）（含別名的使用）select Stud_name 姓名，年齡=year(getdate()-year(birth) from tblStudent where year(getdate()-year(birth) 20數(shù)據(jù)查詢(xún)-distinct和top n等

16、使用top n返回前n條記錄確定范圍（簡(jiǎn)單查詢(xún)）select Stud_name,class_id, year(getdate()-year(birth)from tblStudent where year(getdate()-year(birth) BETWEEN 20 AND 23 SELECT Stud_id 學(xué)號(hào),stud_name 姓名,stud_sex 性別FROM tblstudentWHERE class_id in (310220,340431,320531)字符串匹配（LIKE 或NOT LIKE）SELECT Stud_name FROM tblStudentWHERE

17、Stud_name LIKE 諸葛_ 數(shù)據(jù)查詢(xún)-in和like使用謂詞in或not in空值的查詢(xún)SELECT * FROM tblscore WHERE score IS NULL使用常量列SELECT 學(xué)號(hào)=stud_id,課程號(hào)=course_id,3 AS 學(xué)期,成績(jī)=scoreFROM tblstudent思考：非空值怎么查？數(shù)據(jù)查詢(xún)-空值SELECT * FROM tblscore WHERE score IS not NULL注：不能書(shū)寫(xiě)成：not is null多重條件查詢(xún)SELECT Stud_name 姓名,年齡=year(getdate()-year(birth) FRO

18、M tblStudent WHERE class_id= 310220 AND year(getdate()-year(birth)20 and year(getdate()-year(birth)=60SELECT COUNT (*) as 及格人數(shù) FROM Score WHERE Score=60數(shù)據(jù)查詢(xún)-聚合函數(shù)分組查詢(xún)語(yǔ)法結(jié)構(gòu)Select distinct , . n From where Group by Having 數(shù)據(jù)查詢(xún)-分組在學(xué)生成績(jī)表tblscore中（1）查詢(xún)各個(gè)課程號(hào)及相應(yīng)的選課人數(shù)。（2）查詢(xún)每個(gè)學(xué)生選修的課程門(mén)數(shù)。（3）查詢(xún)選修了3門(mén)以上課程的學(xué)生學(xué)號(hào)。（4）查詢(xún)有3門(mén)以上課程是90分以上的學(xué)生的學(xué)號(hào)及（90分以上的）課程數(shù)數(shù)據(jù)查詢(xún)-分組（1）select course_id,count(Stud_id) from tblscore group by course_id （2）select stud_id,count(course_id) from tblscore group by