《數(shù)據(jù)庫(kù)與信息系統(tǒng)》SQL語(yǔ)言

1、SQL語(yǔ)言與可編程對(duì)象4目 錄常用SQL語(yǔ)句4.1可編程對(duì)象4.2SQL語(yǔ)言概述SQL語(yǔ)言概述SQL是結(jié)構(gòu)化查詢語(yǔ)言（Structure Query Language）的簡(jiǎn)稱，是關(guān)系數(shù)據(jù)庫(kù)操作語(yǔ)言的國(guó)際標(biāo)準(zhǔn)。MySQL所使用的SQL語(yǔ)言遵從ANSI-92國(guó)際標(biāo)準(zhǔn)。SQL語(yǔ)言的特點(diǎn)非過(guò)程化的描述性語(yǔ)言。語(yǔ)言簡(jiǎn)潔、易學(xué)易用。使用方式聯(lián)機(jī)交互獨(dú)立使用嵌入到其他宿主語(yǔ)言輸入SQL語(yǔ)句操作結(jié)果在Navicat for MySQL中打開(kāi)交互式操作窗口1.點(diǎn)擊工具欄中的“查詢”按鈕2.點(diǎn)擊“新建查詢”3.在“查詢編輯器”中輸入命令，點(diǎn)擊“運(yùn)行”。SQL語(yǔ)言的主要語(yǔ)句類別維護(hù)數(shù)據(jù)結(jié)構(gòu)。支持建立、修改或刪除數(shù)據(jù)

2、庫(kù)及數(shù)據(jù)對(duì)象操作。DDL(Data Definition Language) 數(shù)據(jù)庫(kù)定義語(yǔ)言數(shù)據(jù)訪問(wèn)。支持對(duì)數(shù)據(jù)庫(kù)中數(shù)據(jù)的查詢、插入、修改和刪除等操作。DML(Data Manipulation Language) 數(shù)據(jù)庫(kù)操縱語(yǔ)言數(shù)據(jù)庫(kù)監(jiān)視。支持對(duì)數(shù)據(jù)對(duì)象的授權(quán)、安全控制等操作。DCL(Data Control Language) 數(shù)據(jù)庫(kù)控制語(yǔ)言操作對(duì)象是數(shù)據(jù)庫(kù)或者對(duì)象（表、視圖等）操作對(duì)象是表中的數(shù)據(jù)SQL語(yǔ)言的書(shū)寫(xiě)規(guī)則不區(qū)分字母大小寫(xiě)一條語(yǔ)句可寫(xiě)在一行或多行上可用注釋增強(qiáng)語(yǔ)句可讀性 單行注釋（-）,也可以用# 多行注釋（/* */）例如：/*打開(kāi)e_learning數(shù)據(jù)庫(kù)。當(dāng)?shù)谝淮卧L問(wèn)某數(shù)

3、據(jù)庫(kù)時(shí)，需要使用USE語(yǔ)句打開(kāi)數(shù)據(jù)庫(kù)，否則其他SQL語(yǔ)句不能執(zhí)行*/USE e_learning; 【例】SELECT StudentCode, StudentName FROM student; - 查詢學(xué)生表中學(xué)生的學(xué)號(hào)和姓名語(yǔ)句中的單詞可以是關(guān)鍵詞，也可以是標(biāo)識(shí)符關(guān)鍵字標(biāo)識(shí)符語(yǔ)句以“；”結(jié)尾注意-之后必須有空格！THANK YOU!數(shù)據(jù)庫(kù)定義語(yǔ)言（DDL）簡(jiǎn)介數(shù)據(jù)庫(kù)定義語(yǔ)言DDL維護(hù)數(shù)據(jù)結(jié)構(gòu)。支持建立、修改或刪除數(shù)據(jù)庫(kù)及數(shù)據(jù)對(duì)象的操作。DATABASETABLEVIEWPROCEDURETRIGGERCREATE DROPALTER主要語(yǔ)句：（動(dòng)作+操作對(duì)象類型+對(duì)象名）創(chuàng)建刪除更改數(shù)據(jù)

4、庫(kù)表視圖存儲(chǔ)過(guò)程觸發(fā)器object_nameDDL語(yǔ)句舉例創(chuàng)建數(shù)據(jù)庫(kù)CREATE DATABASE 數(shù)據(jù)庫(kù)名 DEFAULT CHARACTER SET 字符集，DEFAULT COLLATE 校對(duì)規(guī)則;CREATE DATABASE pet; - 創(chuàng)建一個(gè)名為pet的數(shù)據(jù)庫(kù)，采用默認(rèn)的字符集和排序規(guī)則CREATE DATABASE plant CHARACTER SET utf8; /*創(chuàng)建一個(gè)名為plant的數(shù)據(jù)庫(kù)，設(shè)置其字符集為utf8 */ 表示可選項(xiàng)DDL語(yǔ)句舉例創(chuàng)建數(shù)據(jù)庫(kù)命令演示DDL語(yǔ)句舉例使用、刪除數(shù)據(jù)庫(kù)USE 數(shù)據(jù)庫(kù)名; - 打開(kāi)該數(shù)據(jù)庫(kù)作為當(dāng)前可操作的數(shù)據(jù)庫(kù)USE pet;

5、 - 將pet設(shè)定為當(dāng)前數(shù)據(jù)庫(kù)DROP DATABASE 數(shù)據(jù)庫(kù)名; - 刪除數(shù)據(jù)庫(kù)DROP DATABASE plant; - 刪除數(shù)據(jù)庫(kù)plantDDL語(yǔ)句舉例創(chuàng)建數(shù)據(jù)表CREATE TABLE 表名(字段名1 字段數(shù)據(jù)類型 字段約束,字段名2 字段數(shù)據(jù)類型 字段約束, Constraint 約束說(shuō)明);USE pet; /*將數(shù)據(jù)庫(kù)pet設(shè)定為當(dāng)前數(shù)據(jù)庫(kù)*/CREATE TABLE cat ( CatCode int(4) NOT NULL AUTO_INCREMENT, /*不可取空值，創(chuàng)建自增約束*/ CatName varchar(16) NOT NULL, /*不可取空值*/ G

6、ender char(1) NOT NULL DEFAULT 母, /*Gender默認(rèn)值為母*/ Birthday date NOT NULL, Photo varchar(30) DEFAULT NULL, Introduction text, RegTime timestamp DEFAULT CURRENT_TIMESTAMP, /*默認(rèn)當(dāng)前時(shí)間*/ PRIMARY KEY (CatCode) /*設(shè)置CatCode為主鍵*/);字段名的單引號(hào)可以去掉字段名數(shù)據(jù)類型約束CatCodeint(4)非空, 自增，主鍵CatNamevarchar(16)非空Genderchar(1)非空，默

7、認(rèn)母Birthdaydate非空Photovarchar(30)默認(rèn)為空IntroductiontextRegTimetimestamp默認(rèn)CURRENT_TIMESTAMP描述字段的每一行末尾是逗號(hào)語(yǔ)句末尾是分號(hào)DDL語(yǔ)句舉例創(chuàng)建索引CREATE UNIQUE|FULLTEXT|SPATIAL INDEX 索引名ON 數(shù)據(jù)表名(字段 ASC|DESC); - ASC升序，DESC降序CREATE UNIQUE INDEX IX_Name ON cat (CatName DESC);-默認(rèn)為升序ASC例：在cat表的CatName字段上建立唯一索引IX_Name，按名字降序排列字段名數(shù)據(jù)類型約

8、束CatCodeint(4)非空, 自增，主鍵CatNamevarchar(16)非空Genderchar(1)非空，默認(rèn)母Birthdaydate非空Photovarchar(30)默認(rèn)為空IntroductiontextRegTimetimestamp默認(rèn)CURRENT_TIMESTAMPcat表表名要建立的索引字段名DDL語(yǔ)句舉例刪除數(shù)據(jù)表、索引DROP TABLE 數(shù)據(jù)表名;DROP TABLE cat;/*刪除cat表*/DROP INDEX 索引名 on 數(shù)據(jù)表;DROP INDEX IX_Name on cat; /*刪除cat表IX_Name索引*/DDL語(yǔ)句舉例修改數(shù)據(jù)對(duì)象A

9、LTER 數(shù)據(jù)對(duì)象 對(duì)象名;ALTER UNIQUE INDEX IX_NameON cat (CatName ASC);Alter語(yǔ)句與Create語(yǔ)句格式完全相同，是在已經(jīng)創(chuàng)建的數(shù)據(jù)對(duì)象做修改。/*修改索引IX_Name：按姓名升序排列*/轉(zhuǎn)儲(chǔ)數(shù)據(jù)定義SQL文件生成腳本xx.sql執(zhí)行腳本在Navicat for MySQL中創(chuàng)建各種數(shù)據(jù)庫(kù)對(duì)象，實(shí)質(zhì)上是以可視化方式生成DDL語(yǔ)句，這些語(yǔ)句可以保存到一個(gè)SQL腳本文件中，以后運(yùn)行該文件可重建數(shù)據(jù)庫(kù)對(duì)象。數(shù)據(jù)庫(kù)對(duì)象數(shù)據(jù)庫(kù)對(duì)象SQL腳本生成與使用舉例例： （1） 轉(zhuǎn)儲(chǔ)student表的SQL文件student.sql； （2）創(chuàng)建一個(gè)新的數(shù)據(jù)庫(kù)

10、example； （3）在example數(shù)據(jù)庫(kù)中運(yùn)行文件student.sql建立student表。 選中表，點(diǎn)擊右鍵新建數(shù)據(jù)庫(kù)后，選中該數(shù)據(jù)庫(kù)，右鍵運(yùn)行*.sql文件轉(zhuǎn)儲(chǔ)單個(gè)表轉(zhuǎn)儲(chǔ)整個(gè)數(shù)據(jù)庫(kù)步驟類似，選中數(shù)據(jù)庫(kù)后轉(zhuǎn)儲(chǔ)數(shù)據(jù)庫(kù)定義語(yǔ)言DDL維護(hù)數(shù)據(jù)結(jié)構(gòu)。支持建立、修改或刪除數(shù)據(jù)庫(kù)及數(shù)據(jù)對(duì)象的操作。DATABASETABLEVIEWPROCEDURETRIGGERCREATE DROPALTER主要語(yǔ)句：THANK YOU!數(shù)據(jù)庫(kù)操作語(yǔ)言（DML）：數(shù)據(jù)查詢語(yǔ)句SELECT 單表查詢數(shù)據(jù)操縱語(yǔ)言DML實(shí)現(xiàn)數(shù)據(jù)訪問(wèn)。支持對(duì)數(shù)據(jù)的查詢和更新操作。常用語(yǔ)句（增刪改查）語(yǔ)句功能SELECT從表中查詢數(shù)據(jù)

11、INSERT向表中添加記錄UPDATE修改表中數(shù)據(jù)DELETE從表中刪除記錄更新查詢?cè)鰟h改查查詢語(yǔ)句SELECTSELECT 字段列表 FROM 表名 WHERE 查詢條件 GROUP BY 分組字段 HAVING 分組條件 ORDER BY 字段名 ASC/DESC ;語(yǔ)法格式主句子句從一個(gè)或多個(gè)表中查詢數(shù)據(jù)。單表查詢只需要查詢一個(gè)表。數(shù)據(jù)查詢語(yǔ)句SELECT 主句SELECT語(yǔ)句主句SELECT 字段列表 FROM 表名;查詢問(wèn)題實(shí)例1查詢表中某些字段的信息問(wèn)題：怎么知道每一位同學(xué)的姓名和性別？分析：查詢student表中”StudentName”和“Gender”這兩個(gè)字段的信息。SEL

12、ECT語(yǔ)句主句SELECT 字段列表 FROM 表名;SELECT FROMStudentName, Gender Student； 字段列表：表中字段多個(gè)字段之間用逗號(hào)分隔所有字段可用通配符“*”表示Student表中學(xué)生姓名和性別問(wèn)題：怎么知道每一位同學(xué)的所有信息？分析：查詢student表中所有字段的信息查詢問(wèn)題實(shí)例2查詢表中所有字段的信息SELECT語(yǔ)句主句SELECT 字段列表 FROM 表名;SELECT FROM*Student； 字段列表：表中字段多個(gè)字段之間用逗號(hào)分隔所有字段可用通配符“*”表示Student表中全部信息問(wèn)題：查詢課程號(hào)和課程名信息，查詢結(jié)果中的字段名分別是“

13、課程號(hào)”和“課程名”？分析：查詢course表中”CourseCode”和“CourseName”這兩個(gè)字段的信息給查詢結(jié)果的字段名取個(gè)別名查詢問(wèn)題實(shí)例3給查詢結(jié)果中的字段名取個(gè)別名SELECT語(yǔ)句主句SELECT 字段列表 FROM 表名;SELECT CourseCode AS課程號(hào), CourseName 課程名 FROM course;字段列表：表中字段“字段名 AS 別名”替換列標(biāo)題顯示用別名顯示查詢結(jié)果和列標(biāo)題問(wèn)題：根據(jù)course表信息，怎么知道有多少種不同的學(xué)科？分析：查詢course表中的SubjectCode字段？查詢問(wèn)題實(shí)例4查詢表中可能有重復(fù)值的字段SELECT語(yǔ)句主句

14、SELECT 字段列表 FROM 表名；SELECT SubjectCode FROM Course; 字段列表：字段名前加限制，優(yōu)化查詢結(jié)果：DISTINCT：相同字段值只顯示一條記錄SELECT DISTINCT SubjectCode FROM Course; 查詢Course表中學(xué)科號(hào)查詢Course表中學(xué)科號(hào)（只顯示一條）問(wèn)題：已知數(shù)據(jù)庫(kù)中courseenroll表中所有同學(xué)成績(jī)偏低5分，根據(jù)該表信息如何查詢得到正常成績(jī)（不更改表的數(shù)據(jù)）？分析：查詢courseenroll表中的Score字段，然后怎么調(diào)整呢？查詢問(wèn)題實(shí)例5查詢時(shí)針對(duì)字段值的算術(shù)操作SELECT語(yǔ)句主句SELECT

15、字段列表 FROM 表名;SELECT StudentCode, CourseCode, Score+5 AS 成績(jī), 成績(jī)調(diào)整 AS 說(shuō)明FROM courseenroll;字段列表：可新增字段(常量、表達(dá)式)查詢courseenroll表，將成績(jī)加5分顯示，并增加一列成績(jī)調(diào)整注：courseenroll表中數(shù)據(jù)未更改。問(wèn)題：如何知道課程的平均分/最高分/最低分/總分/總?cè)藬?shù)？分析：查詢courseenroll表中，對(duì)相應(yīng)字段做聚合操作（求平均值/最大值/最小值/總數(shù)/計(jì)數(shù)）？查詢問(wèn)題實(shí)例6查詢時(shí)針對(duì)字段值的聚合運(yùn)算SELECT語(yǔ)句主句SELECT 字段列表 FROM 表名；SELECT A

16、vg(Score) AS 平均分,Max (Score) AS 最高分,Min(Score) AS 最低分,Count(StudentCode) AS 總?cè)舜蜦ROM courseenroll;字段列表：可新增字段(常量、表達(dá)式)表達(dá)式中常用聚合函數(shù)函數(shù)名函數(shù)功能AVG計(jì)算某一字段的平均值COUNT統(tǒng)計(jì)某一字段的個(gè)數(shù)MAX查找某一字段的最大值MIN查找某一字段的最小值SUM計(jì)算某一字段的總和含有聚合函數(shù)的查詢結(jié)果注：courseenroll表中數(shù)據(jù)未更改。THANK YOU!數(shù)據(jù)查詢語(yǔ)句SELECT 子句問(wèn)題舉例：怎么從student表中獲取所有男同學(xué)的信息？如何按照生源地，統(tǒng)計(jì)各地學(xué)生的平均

17、成績(jī)？查詢學(xué)生的成績(jī)，并按年齡從小到大排序？分析：查詢條件分組排序未解決的其他查詢問(wèn)題查詢語(yǔ)句SELECTSELECT 字段列表 FROM 表名 WHERE 查詢條件 GROUP BY 分組字段 HAVING 分組條件 ORDER BY 字段名 ASC/DESC ;語(yǔ)法格式主句子句從一個(gè)或多個(gè)表中查詢數(shù)據(jù)。 查詢語(yǔ)句SELECTWHERE子句SELECT 字段列表 FROM 表名WHERE 查詢條件; 查詢條件：是一個(gè)關(guān)系或邏輯表達(dá)式查詢條件謂詞比較、=、=、=、(不等于)、!(不大于)、!、=、=、=、(不等于)、!=(不等于)、 !(不大于)、!= 70 AND Score=Score=7

18、0Score BETWEEN 70 AND 90等價(jià)于成績(jī)?cè)?090分之間查詢條件，跟在where字句后查詢的字段（列表），寫(xiě)在select主句中表名SELECT語(yǔ)句WHERE子句的區(qū)間查詢2SELECT 字段列表 FROM 表名WHERE 查詢條件; SELECT StudentCode, StudentName, Birthday FROM StudentWHERE Birthday NOT BETWEEN 1970-01-01 AND 2004-12-31;例如：查詢Student表中不在19702004出生的學(xué)生學(xué)號(hào)、姓名和生日。查詢條件：確定范圍NOT BETWEEN 值1 AND

19、值2不在值1和值2之間使用Year()函數(shù)Year(Birthday) NOT BETWEEN 1970 AND 2004不在19702004年之間出生SELECT語(yǔ)句WHERE子句的集合查詢SELECT 字段列表 FROM 表名WHERE 查詢條件; SELECT StudentCode, StudentName, Location FROM StudentWHERE Location IN (黑龍江,吉林,遼寧);例如：查詢student表中來(lái)自東北三省的學(xué)生，即生源地為黑龍江、吉林、遼寧的學(xué)生信息。查詢條件：IN運(yùn)算確定是否在集合中等價(jià)于關(guān)系運(yùn)算Location =黑龍江 OR Loca

20、tion =吉林 OR Location =遼寧來(lái)自東三省SELECT語(yǔ)句WHERE子句的模糊查詢SELECT 字段列表 FROM 表名WHERE 查詢條件;查詢條件：LIKE運(yùn)算確定字符串模式是否匹配例如：一個(gè)人的姓名姓“王”： LIKE 王%；姓“王”單名： LIKE 王_中間字“小”： LIKE _小%不姓“王”： LIKE 王%通配符說(shuō)明%匹配0個(gè)或多個(gè)任意字符_匹配1個(gè)任意字符 匹配集合中任意單個(gè)字符不匹配集合中的任意單個(gè)字符LIKE模式通配符SELECT語(yǔ)句WHERE子句的模糊查詢舉例SELECT 字段列表 FROM 表名WHERE 查詢條件; 查詢條件：LIKE運(yùn)算確定字符串模式

21、是否匹配SELECT StudentCode, StudentName FROM studentWHERE StudentName LIKE 劉%;例如：查詢student表中不姓劉的學(xué)生的學(xué)號(hào)和姓名。例如：查詢course表中課程名中包含“技術(shù)”兩個(gè)字的課程號(hào)及課程名。 SELECT CourseCode, CourseName FROM courseWHERE CourseName LIKE %技術(shù)%;名稱包含“技術(shù)”的課程不姓劉的學(xué)生SELECT StudentCode, StudentName FROM studentWHERE StudentName NOT LIKE 劉%;查詢語(yǔ)句

22、SELECTGROUP BY子句SELECT 字段列表 FROM 表名GROUP BY 分組字段 HAVING 分組條件 ; 分類匯總。即按分組字段把具有相同值的記錄匯總計(jì)算合并成一條記錄。用于分組的字段包含分組字段、匯總計(jì)算字段SELECT CourseCode, Count(StudentCode) AS 選課人數(shù), AVG(Score) AS 平均分 FROM courseenrollGROUP BY CourseCode例如：統(tǒng)計(jì)選課表中每門(mén)課程的選課人數(shù)和平均分。HAVING AVG(Score) =80; 或：HAVING 平均分=80;只顯示平均80分以上的課程。;表名分組字段分

23、組條件查詢的字段（列表）注意：HAVING子句只能跟在GROUP BY子句后，不能單獨(dú)使用。SELECT后查詢的字段列表中必須包含分組字段。比如這里的CourseCode字段。查詢語(yǔ)句SELECTGROUP BY子句與WHERE的區(qū)別SELECT 字段列表 FROM 表名GROUP BY 分組字段 HAVING 分組條件 ; WHERE子句和HAVING字句執(zhí)行機(jī)制的區(qū)別：WHERE子句：先篩選再匯總HAVING子句：先匯總再篩選SELECT CourseCode, COUNT(StudentCode) AS 選課人數(shù), AVG(Score) AS 平均分FROM courseenrollWH

24、ERE CourseCode=C001;例如：查詢課程號(hào)為C001的選課人數(shù)。SELECT CourseCode, COUNT(StudentCode) AS 選課人數(shù), AVG(Score) AS 平均分FROM courseenrollGROUP BY CourseCodeHAVING CourseCode=C001;先篩選課程號(hào)，再匯總被選出的課程的選課人數(shù)先匯總每門(mén)課程的選課人數(shù)，再根據(jù)課程號(hào)篩選如果在篩選條件中包含匯總結(jié)果，那么用WHERE子句無(wú)法實(shí)現(xiàn)，因?yàn)閃HERE子句中不能包含聚合函數(shù)，這時(shí)只能用HAVING子句.例如： HAVING COUNT(StudentCode)3查詢語(yǔ)

25、句SELECTGROUP BY子句中有多個(gè)分組字段SELECT 字段列表 FROM 表名GROUP BY 分組字段 HAVING 分組條件 ; GROUP BY后有多個(gè)分組字段，則表示多次分組。SELECT Location, Gender, COUNT(StudentCode) AS 學(xué)生人數(shù)FROM StudentGROUP BY Location, Gender;例如：統(tǒng)計(jì)各生源地男、女學(xué)生的人數(shù)。分組字段查詢的字段（列表）查詢語(yǔ)句SELECT使用GROUP BY子句的注意事項(xiàng)SELECT 字段列表 FROM 表名GROUP BY 分組字段 HAVING 分組條件 ; 注意：分組字段必須出現(xiàn)在SELECT后的字段列表中（可以是字段，也可在聚合函數(shù)中?。〩AVING字句必須和GROUP BY字句一起使用，不能單獨(dú)使用。HAVING字句和WHERE字句的實(shí)現(xiàn)機(jī)制的差異查詢語(yǔ)句SELECTORDER BY子句SELECT 字段列表 FROM 表名ORDER BY 字段 ASC/DESC;按特定字段值為查詢結(jié)果排序。排序依據(jù)SELECT S