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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

16、使用top n返回前n條記錄確定范圍（簡單查詢）select Stud_name,class_id, year(getdate()-year(birth)from tblStudent where year(getdate()-year(birth) BETWEEN 20 AND 23 SELECT Stud_id 學號,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ù)查詢-in和like使用謂詞in或not in空值的查詢SELECT * FROM tblscore WHERE score IS NULL使用常量列SELECT 學號=stud_id,課程號=course_id,3 AS 學期,成績=scoreFROM tblstudent思考：非空值怎么查？數(shù)據(jù)查詢-空值SELECT * FROM tblscore WHERE score IS not NULL注：不能書寫成：not is null多重條件查詢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ù)查詢-聚合函數(shù)分組查詢語法結(jié)構(gòu)Select distinct , . n From where Group by Having 數(shù)據(jù)查詢-分組在學生成績表tblscore中（1）查詢各個課程號及相應(yīng)的選課人數(shù)。（2）查詢每個學生選修的課程門數(shù)。（3）查詢選修了3門以上課程的學生學號。（4）查詢有3門以上課程是90分以上的學生的學號及（90分以上的）課程數(shù)數(shù)據(jù)查詢-分組（1）select course_id,count(Stud_id) from tblscore group by course_id （2）select stud_id,count(course_id) from tblscore group by