第4章關(guān)系數(shù)據(jù)庫標準語言SQL語言

1、第四章 關(guān)系數(shù)據(jù)庫標準語言SQL語言 關(guān)系運算 查詢功能 操縱功能 定義功能4.1 關(guān)系運算 4.1.1、傳統(tǒng)的集合運算 4.1.2、專門的關(guān)系運算4.1.1 傳統(tǒng)的集合運算：并、交、差 進行集合運算的兩個關(guān)系必須具有相同的結(jié)構(gòu)。對于表來說就要求兩個表的字段變量要一樣。4.1.2、專門的關(guān)系運算專門的關(guān)系運算：選擇、投影、連接一、 選擇：查找滿足條件的元組（行的角度運算）。從列的角度進行的運算查詢性別為女的記錄二、 投影指定若干屬性組成新的關(guān)系（ 從列的角度進行的操作）。查詢學號 和姓名信息三 聯(lián)接聯(lián)接分為等值聯(lián)接和自然聯(lián)接1、等值聯(lián)接：按照兩個表的公共字段對應相等把多個關(guān)系合成一個關(guān)系。（）

2、2、自然聯(lián)接：去掉重復屬性的等值聯(lián)接聯(lián)接：將兩個（多個）關(guān)系通過聯(lián)接條件橫向拼成一個新的關(guān)系；新的關(guān)系包括滿足聯(lián)接條件的所有元組。4.2查詢功能 4.2.1 SQL 概述SQL是結(jié)構(gòu)化查詢語言Structured Query Language的縮寫。它包含數(shù)據(jù)定義、數(shù)據(jù)操縱和數(shù)據(jù)控制功能，核心功能是查詢功能；是關(guān)系型數(shù)據(jù)庫的標準數(shù)據(jù)語言，現(xiàn)在所有的關(guān)系數(shù)據(jù)庫管理系統(tǒng)都支持SQL。表之間的聯(lián)系：4.2.2 簡單查詢 基于一個表的查詢格式： select from where 例1、從student表中查出所有人的年齡.Select 年齡 from student例2:檢索全體學生的全部信息Sel

3、ect 學號,姓名,性別,年齡,院系號; from studentSelect * from stedent說明：如果語句過長，則用;表示換行； *為通配符，表示全部屬性或字段例3:查詢表中年齡大于18的學號。Select 學號 from student where 年齡18說明：where表示查詢條件例4:檢索哪些系有性別為男的學生。Select 院系號 from student where 性別=“男”去重查詢：Select distinct 院系號; from student; where 性別=“男” 例5:檢索出年齡小于20且在06號或者02號學院的學號Select 學號 from

4、student；Where 年齡90;And student.學號=score.學號說明：1. student.學號=score.學號為聯(lián)接條件（主碼=外碼）2. 使用表的公共屬性時，要用關(guān)系前綴表示屬性的所屬關(guān)系。3. 公共字段對應相等要記住。例7：查詢選修計算機軟件基礎這門課的學號和成績。分析：條件一 課程號=“計算機軟件基礎” 條件二：聯(lián)接條件，course.課程號=score.課程號Select 學號,成績 from score,course; where 課程名=“計算機軟件基礎”; and course.課程號=score.課程號 例8:查詢選“數(shù)據(jù)庫”的學生 的姓名和成績分析：

5、課程=“數(shù)據(jù)庫”,用的course表 姓名用到student表，成績用到score表用到3個表，聯(lián)接字段student.學號=score.學號 score.課程號=course.課程號語句：select 姓名,成績 ;from student,score,course;Where 課程名=“數(shù)據(jù)庫”;and score.課程號=course.課程號;And student.學號=score.學號4.2.4嵌套查詢一個查詢包含另外一個查詢例9:查詢哪些系有學生得成績?yōu)?0分；聯(lián)接查詢：select 院系號 from student,score;Where 成績=80 and student.學號

6、=score.學號嵌套查詢：先在score表中把成績是80得學號找出來（內(nèi)層查詢）；再在student表中根據(jù)找到學號查詢對應的學院號（外層查詢）；（學生的學號是否在從score找到滿足條件的集合里面）語句：select 院系號 from student; where 學號 in; (select 學號 from score where 成績=80)嵌套查詢說明：1.In：檢查一個屬性值是否屬于一組值2.外層查詢依賴于內(nèi)層查詢的結(jié)果。3.內(nèi)層查詢語句必須用（）括起來例10:查詢和學生辛國年同歲的學生分析：先查詢辛國年的年齡； 再查詢表年齡=辛國年年齡的學號語句：不使用in：Select 學號

7、from student where 年齡=;(Select 年齡 from student where 姓名=“辛國年”)使用 in：Select 學號 from student where 年齡 in;(Select 年齡 from student where 姓名=“辛國年”)說明：如果子查詢的結(jié)果有多個值則必須用in；如果內(nèi)層查詢結(jié)果只有一個值則in和=都可以例11:查詢沒有學生選修的課程名只用corese表和score表即可，score出現(xiàn)的課程號均有人選修（內(nèi)層），然后看course表的課程號沒有在score表中出現(xiàn)的記錄（外層）。命令：select 課程名 from course

8、 where 課程號 not in;(select 課程號 from score)4.2.5 幾個特殊的運算符1.Between.and 查詢范圍例12：查詢年齡在20到22之間學生信息Select * from student where 年齡年齡Between 20 and 22注意：該運算符包含邊界（年齡為20和22）2.Like 匹配查詢例13：查詢所有姓王的學生信息（like “王%”）Select * from student where 姓名 like “王%”說明：Like的一般形式：列名 like 字符串常用的兩個通配符：%和_“%”表示任意多個字符“_”表示任意一個字符3.

9、Not 否定運算符例14:查詢性別不是女的學生信息（not 性別=“女”）語句：Select * from student where not 性別=“女”等價于：Select * from student where 性別!=“女”4.2.6 排序 我們不僅可以把表的結(jié)果查詢出來，而且可以對查詢結(jié)構(gòu)進行排序。排序的命令：order by 字段名 asc|desc例15:按院系號升序查詢出學生信息。Select * from student order by 院系號 asc說明：默認是升序asc 升序 desc 降序多字段排序例16:先按照年齡升序排列，如果年齡相同則按照院系號降序排列Sele

10、ct * from student order by 年齡 asc，院系號 desc說明：可按照多字段排序4.2.7 使用集函數(shù)Sql不僅可以查詢還可以以計算方式查詢1.Count（）函數(shù)計數(shù)2.Sum（）求和3.Avg（）求平均值4.Max（）求最大值5.Min（）求最小值1.Count（）函數(shù)例17:統(tǒng)計開設課程的門數(shù)。說明：只要在course表中統(tǒng)計課程號的數(shù)目即可。語句：select count(課程號) from course該結(jié)果別人可能看不懂統(tǒng)計結(jié)果是什么，所以我們可以起別名。select count(課程號) as課程門數(shù)from course說明：as可以省略例18:統(tǒng)計學生

11、總?cè)藬?shù)統(tǒng)計學號個數(shù)即可如果姓名唯一也可以使用姓名select count(學號) as 學生人數(shù) from student例19:統(tǒng)計選課的學生人數(shù)，若選多門課不累計（count（distinct學號）select count(distinct 學號) as 選課人數(shù) from score2.sum()函數(shù)例20:求s1所選課程的總成績；分析：條件 ：學號=“s1” 對象 ：成績（sum（成績）語句：select sum(成績) as 總成績 from score; where 學號=“s1”例21:求鄧一歐所選課程總成績姓名在student表中，選課在score表中，所以要用到多個表的聯(lián)接條

12、件select sum(成績成績) as 課程總分課程總分 from score,student ; where 姓名姓名=鄧一歐鄧一歐 and score.學號學號=student.學學號號3.Avg()函數(shù)例22.求所有學生平均年齡；select avg(年齡) as 平均年齡 from student4.max()函數(shù)例23:求選修“c1”課程的最高分分析：先在score表中找選修c1的記錄，然后在記錄中找成績對應最高分。select max(成績) as 最高分 from score where 課程號=c1“說明：如果要求最小值使用min（）4.2.8 分組查詢命令格式：group

13、by , . having 過濾條件例24:計算每門課程得最高分先按照課程號分組，再在分組里求最高分select max(成績) as 最高分 from score ;group by 課程號結(jié)果看不出是哪門課的最高分說明：要選擇出分組的字段select 課程號 , max(成績) as 最高分 from score ;group by 課程號說明：1.先分組，后計算2.分組字段必須是表中存在的字段3.要把分組的字段加在select后面例25:檢索出年齡大于19的男女生人數(shù)分析：先在student表中查詢出年齡大于19的人數(shù)，然后再按照性別進行分組，最后求男女分組的學號的個數(shù)Select 性別

14、, count(學號) as 人數(shù) from student ;Where 年齡19 group by 性別;說明：group by作用于where返回的結(jié)果例26:計算至少有3個人的每個系的人數(shù)首先把每個系的學生記錄放在一起（按院系號分組），然后統(tǒng)計至少有3人的院系的人數(shù)（分完組還要檢查，至少有3人的院系號才能進行統(tǒng)計） 語句： select count(學號) as 人數(shù); from student; group by 院系號; having count(學號 )=3說明：having用于檢查每一組例27:統(tǒng)計年齡大于18且至少有3人的男女生人數(shù)；1、在表中找出年齡大于18的記錄。2、按照

15、性別進行分組3、用having檢查每一組4、統(tǒng)計滿足條件的人數(shù)語句：select 性別,count(學號) as 人數(shù);from student;where 年齡18;group by 性別;having 人數(shù)=3總結(jié)：1、Group by作用where返回的結(jié)果2、where把不滿足條件的元組排除3、having子語句排除不符合條件的組，且不能單獨使用Where：限制元組（范圍寬）Having：限制組4.2.9 涉及空值（null）的查詢格式：列名 is not null例28:查詢沒有確定院校號的學生的學號Select 學號 from student；Where 院系號 is null注意

16、：1、“=null” 無效 2、升序排序中null值首先顯示給關(guān)系起別名：格式： 別名例：查詢學生的姓名、成績和選課的課程名聯(lián)接查詢：Select 姓名,成績,課程名 ;From student ,score,course;Where student.學號=score.學號 and; score.課程號=course.課程號等價于：Select 姓名,成績,課程名 from; student a, score b,course c; where a.學號=b.學號 ; and b.課程號=c.課程號4.2.10量詞和謂詞的使用與子查詢相關(guān)的運算符Exists或not ExistsAny|all

17、|some注意：any和some的功能相同Exists/not exists例30:查詢選“c1”課程的學生姓名語句 ：Select 姓名 from student where exists;(select 學號 from score where 課程號=“c1”; and 學號=student.學號)Exists是存在的意思，在本例中內(nèi)存查詢先找到選c1的學生學號，然后在student表中看其每條記錄能否滿足內(nèi)層查詢查出來的條件，如果滿足的話就把該記錄的姓名取出來any例31:求年齡大于“06”號系任一同學年齡的學生姓名Select 姓名 from student;Where 年齡any;(

18、select 年齡 from student where 院系號=“06”)說明：any作用：子查詢中有一行能使結(jié)果為真，就為真Some（一些）和any相同Select 姓名 from student;Where 年齡any;(select 年齡 from student where 院系號=“06”)等價于Select 姓名 from student;Where 年齡some;(select 年齡 from student where 院系號=“06”)all求年齡大于“06”號系所有同學年齡的學生姓名Select 姓名 from student;Where 年齡all;(select 年齡

19、 from student where 院系號=“06”)4.2.11 超級聯(lián)接查詢格式：Select from 表1Inner|left|right|full|join On 聯(lián)接條件作用：顯示一個表的全部記錄和另外一個表的部分記錄使用文件夾3的內(nèi)容題目：要求顯示學號、年齡、性別、課程號、成績字段聯(lián)接查詢：Select student.學號,年齡,性別,課程號,成績;From student,score;Where student.學號=score.學號一 內(nèi)部聯(lián)接inner join 只有滿足聯(lián)接條件的記錄才會出現(xiàn)在結(jié)果中Select student.學號,年齡,性別,課程號,成績;Fro

20、m student inner join score;on student.學號=score.學號說明：1、聯(lián)接條件用on 2、inner可以省略二、左聯(lián)接除了滿足聯(lián)接條件的記錄能查詢出來外，第一個表中不滿足聯(lián)接條件的記錄也顯示出來Select student.學號,年齡,性別,課程號,成績;From student left join score;on student.學號=score.學號三、左聯(lián)接除了滿足聯(lián)接條件的記錄能查詢出來外，第二個表中不滿足聯(lián)接條件的記錄也顯示出來Select student.學號,年齡,性別,課程號,成績;From student right join scor

21、e;on student.學號=score.學號四、全聯(lián)接除了滿足聯(lián)接條件的記錄能查詢出來外，兩個個表中不滿足聯(lián)接條件的記錄也顯示出來Select student.學號,年齡,性別,課程號,成績;From student full join score;on student.學號=score.學號例32:查詢成績大于90的姓名、成績和課程名Join聯(lián)接多個表時，join的順序和on聯(lián)接條件的順序正好相反。Select 姓名,成績,課程名;From student join score join course;on score.課程號=course.課程號;On score.學號= studen

22、t.學號;Where 成績904.2.12 集合查詢例33:查詢“06”或“02”號系的學生的學號1、用“or”Select 學號 from student;Where 院系號=“06” or院系號=“02” 2、集合查詢：unionSelect 學號 from student;Where 院系號=“06”;Union;Select 學號 from student;Where 院系號=“02”說明：1、union對兩個select語句的結(jié)果求并集。2、union要求兩個select語句一樣，即結(jié)果的屬性（字段變量）要相同4.2.13 幾個特殊選項1、顯示部分記錄 top格式：top perce

23、nt例34:查詢年齡最小的三名同學的姓名和年齡select top 3 姓名,年齡;from student;order by 年齡說明：top必須和order by一起使用查詢年齡最大的三條學生姓名、年齡（按降序排序）select top 3 姓名,年齡;from student;order by 年齡 desc例35:查詢年齡最大的前50%學生信息（按降序排序）select * top 50 percent ;from student;order by 年齡 desc說明：數(shù)字后面使用percent表示的是百分比，不使用表示的是記錄個數(shù)4.2.13 幾個特殊選項2、查詢?nèi)ハ颍喊巡樵兊慕Y(jié)果永

24、久保存（1）into array （2）into cursor （3）into table|dhf （4）to file （5）to printer ：打?。?）into array 例35:將查詢student所有記錄存入數(shù)組aa中select * from student;into array aa問題：aa數(shù)組是什么數(shù)組？（2）into cursor例36:將查詢student所有記錄存入臨時表bb中select * from student;into cursor bb說明：臨時表只能讀，關(guān)閉時自動刪除，用于復雜查詢的中間過程（3）into table格式：into table|dhf

25、 例37:將查詢student所有記錄存入永久表cc中select * from student;into table cc（4）to fileto file 例37:將查詢student所有記錄存入臨時表dd中select * from student;to file dd說明：把結(jié)果存入文本文件4.3 操縱功能4.3.1 插入4.3.2 更新4.3.3 刪除注：以上操作針對表中的記錄進行4.3.1 插入格式一： insert into （字段1，字段2,.) values(表達式) 格式二： insert into from array 數(shù)組名格式一例38:向student表中插入記錄（“

26、s9”，“張三”，20，“男”，“06”）insert into student; values (s9,張三,20,男,20)例39:向student表的姓名和性別字段分別插入值（“李四”,”女”）命令：insert into ;Student(姓名，性別);Values(“李四”,”女”)說明：若插入某幾個字段的值，字段名需要在表名后指明。格式二例40:查詢學號是”s1”的記錄放在數(shù)組aa中，再插入student表中1.查詢：select * from;Student where 學號=“s1”;Into array aa;2.插入：insert into student;From arr

27、ay aa小結(jié)表中的插入命令insert into和前面學過的數(shù)據(jù)庫插入命令 insert 、append都可以插入記錄。使用時怎么選擇呢？一般題目沒有要求則使用insert into命令；另外當表中建立主索引和候選索引時候，如果插入的數(shù)據(jù)是主索引和候選索引字段相同值，則使用insert、append命令插入不進去，只能使用insert into命令完成插入。4.3.2 更新格式：updata ； set =; where 例41:把學生“鄧一歐”改為“鄧歐”命令：update student;set 姓名=“鄧歐”;where 姓名=“鄧一歐”說明：使用where修改滿足條件的記錄等價于：u

28、se student Replace 姓名 with “鄧歐” for 姓名=“鄧一歐” 例42：把所有人的年齡改為20Update student Set 年齡=20說明：缺省where條件說明是對所有的記錄進行修改；等價于：use student replace all 年齡 with 20例43（修改多個字段）：把學生辛國年年齡改為21，學院號改為01命令：Update student;Ste 年齡=21,院系號=“01”;Where 姓名=“辛國年”等價于：Use studentReplace 年齡with 21，院系號 with “01” for 姓名=“辛國年”說明：update可

29、以同時修改多個字段值4.3.3 刪除格式：Delete from ;Where（ 條件）例44:刪除student表中學號是s1的記錄Delete from student where 學號=“s1”等價于：Use studentDelete for 學號=“s1”說明：delete刪除是邏輯刪除，使用where刪除滿足的條件例35:刪除所有學生的選課記錄Delete from score等價于：Use scoreDelete all說明：缺省where條件說明刪除所有的記錄（給所有的記錄添加刪除標記）4.4 定義功能4.4.1 基本表4.4.2 視圖定義4.4.1 基本表1、定義表：crea

30、te table2、修改表結(jié)構(gòu)：alter table3、刪除表：drop table說明:只對表的結(jié)構(gòu)進行操作1、定義表命令：create table功能：定義表的結(jié)構(gòu)說明：此命令需在數(shù)據(jù)庫打開時使用，否則創(chuàng)建的是自由表。例36:在數(shù)據(jù)庫stsc中創(chuàng)建一個表“課程”，其結(jié)構(gòu)如course表。Create table 課程;(課程號 c(2),課程名 c(20),學分 n(2)注：數(shù)據(jù)類型表示：c 字符型；n 數(shù)值型；d 日期型；例37:建立表“課程1”的同時建立主索引Create table 課程;(課程號 c(2) primary key,;課程名 c(20),學分 n(2)Primary

31、 key 表示建立主索引例38:創(chuàng)建“課程2”表，設置規(guī)則：學分2,信息為”學分大于2”，默認值是3Create table 課程2;(課程號 c(2),課程名 c(20),;學分 N(2) check 學分2; error “學分大于2”;Default 3)說明：check指明規(guī)則；Error指明信息表達式；Default指明默認值。二、修改表結(jié)構(gòu)命令格式：Alter table add|altercheck 邏輯表達式error 信息表達式defaut 邏輯表達式drop例39：修改student表的結(jié)構(gòu)，添加日期類型的出生日期字段，并設置規(guī)則為：year（出生日期）2000,信息為“出生日期大于2000年”alte table student;add 出生日期 d;check year(出生日期)2000;error 出生年份大于2000年“說明：add添加新字段例40:把student表中姓名字段的寬度由8改為10.alter table student alter 姓名 c(10)說明：Alter用來修改已經(jīng)存在的字段例41:修改或定義“出生日期”的規(guī)則為：出生日期大于2001Alter table student alter 出生日期;Set check year (出生日期)2001說明：set che