《基本SQL語(yǔ)句》PPT課件.ppt

基本SQL語(yǔ)句,SQL語(yǔ)言概述,SQL是Structured Query Language（結(jié)構(gòu)化查詢語(yǔ)言）的縮寫，它是目前使用最廣泛的數(shù)據(jù)庫(kù)語(yǔ)言，SQL是由IBM發(fā)展起來(lái)的，后來(lái)被許多數(shù)據(jù)庫(kù)軟件公司接受而成為了業(yè)內(nèi)的一個(gè)標(biāo)準(zhǔn)。 包括數(shù)據(jù)定義、數(shù)據(jù)操縱、數(shù)據(jù)查詢和數(shù)據(jù)控制功能,SQL語(yǔ)言的分類,SQL語(yǔ)言共分為四大類： 數(shù)據(jù)定義語(yǔ)言DDL 用來(lái)創(chuàng)建數(shù)據(jù)庫(kù)中的各種對(duì)象-表、視圖、 索引、同義詞、聚簇等如： CREATE TABLE/VIEW/INDEX/SYN/CLUSTER 表 視圖 索引 同義詞 簇 數(shù)據(jù)控制語(yǔ)言DCL 用來(lái)授予或回收訪問(wèn)數(shù)據(jù)庫(kù)的某種特權(quán)，并控制 數(shù)據(jù)庫(kù)操縱事務(wù)發(fā)生的時(shí)間及效果，對(duì)數(shù)據(jù)庫(kù)實(shí)行監(jiān)視等。如：grant,rollback,commit 數(shù)據(jù)操縱語(yǔ)言DML 數(shù)據(jù)操縱語(yǔ)言DML主要有三種形式：1) 插入：INSERT 2) 更新：UPDATE 3) 刪除：DELETE 數(shù)據(jù)查詢語(yǔ)言DQL 基本結(jié)構(gòu)是由SELECT子句，F(xiàn)ROM子句，WHERE 子句組成的查詢塊：,SQL核心語(yǔ)句,DDL數(shù)據(jù)定義語(yǔ)言,建立數(shù)據(jù)庫(kù)的第一步就是定義一些基本表。SQL語(yǔ)言使用CREATE TABLE命令定義基本表。 1）定義基本表 CREATE (列級(jí)完整性約束 ,列級(jí)完整性約束. ,) 如果完整性約束涉及到改表的多個(gè)字段，則必須定義在表的級(jí)上，否則可以定義在列級(jí)上也可以定義在表級(jí)上,新建表例子,例如建立一個(gè)學(xué)員表student,包括學(xué)號(hào)sno,姓名sname,性別ssex,年齡sage,所在系sdept五個(gè)屬性組成。其中學(xué)號(hào)不能為空，值是唯一的，并且姓名的取值也唯一。 CREATE TABLE student ( sno char(5) not null unique, sname char(20) unique, ssex char(1) default 男, sage int, sdept char(15);,字段常用數(shù)據(jù)類型,定義表的各各屬性需要指明數(shù)據(jù)類型及長(zhǎng)度。不同的數(shù)據(jù)庫(kù)系統(tǒng)支持的數(shù)據(jù)類型不安全相同，常用的類型有： Char（n） 長(zhǎng)度為n的定長(zhǎng)字符串； Varchar（n）最大長(zhǎng)度為n的變長(zhǎng)字符串； INT 整形 Double 浮點(diǎn)型 DATETIME 日期型 text 可變長(zhǎng)度的Unicode數(shù)據(jù)，最大長(zhǎng)度 230 - 1 image 可變長(zhǎng)度的二進(jìn)制數(shù)據(jù)，最大長(zhǎng)度為 230 - 1,修改基本表,2）修改基本表 Alter Table ADD 完整性約束 DROP MODIFY ; ADD：增加新列和新的完整性約束條件 DROP：刪除制定的完整性條件 MODIFY：用于修改原有的列的定義，包括列名和數(shù)據(jù)類型,修改表的例子,例：向student表增加入學(xué)時(shí)間列，類型為日期型 ALTER TABLE student ADD scome DATETIME 例：將年齡的數(shù)據(jù)類型改為字符型； ALTER TABLE student MODIFY Sage char(4) 例：刪除學(xué)生姓名必須唯一值的約束 ALTER TABLE student DROP UNIQUE(sname),刪除基本表,DROP TABLE student 基本表定義一旦刪除，表中的數(shù)據(jù)、此表上建立的索引和視圖都將自動(dòng)被刪除掉。因此執(zhí)行基本表的刪除一定要格外小心。,創(chuàng)建索引,建立索引是加快查詢速度的有效手段，在基本表上建立一個(gè)或多個(gè)索引，以提供多種存取路徑，加快查詢速度。 1）建立索引 在SQL語(yǔ)言中，建立索引使用CREATE INDEX語(yǔ)句，其一般格式為： CREATE UNIQUE CLUSTER INDEX ON (,.); UNIQUE：唯一索引 CLUSTER：聚簇索引。所謂聚簇索引是指索引項(xiàng)的順序與表中記錄的物理順序一致的索引組織。由于一般表只能有一個(gè)物理順序，所以一個(gè)表上只能建立一個(gè)聚簇索引。,創(chuàng)建索引,用戶可以在最常查詢的列上建立聚簇索引以提高查詢效率，建立聚簇索引后，更新索引列數(shù)據(jù)時(shí)，往往會(huì)導(dǎo)致表中記錄的物理順序的變更，代價(jià)較大，因此對(duì)于經(jīng)常更新的列不宜建立聚簇索引。 例：建立下面的聚簇索引 Create CLUSTER INDEX stusname on student(sname),創(chuàng)建索引,例，建立下面三個(gè)索引： CREATE UNIQUE INDEX stusno ON student(sno); CREATE UNIQUE INDEX Coucon on student(cno); CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC),刪除索引,索引一旦建立，就由系統(tǒng)使用和維護(hù)它，不需用戶干預(yù)。建立索引是為了減少查詢操作的時(shí)間，但如果數(shù)據(jù)增加、刪除頻繁，系統(tǒng)會(huì)花很多時(shí)間來(lái)維護(hù)索引。這時(shí)可以刪除一些不必要的索引。 DROP INDEX 例 刪除Student 表的Stusname 索引 DROP INDEX Stusname；,使用索引,問(wèn)題一：在關(guān)系client（depart，id，name，tel）中分別標(biāo)識(shí)客戶的分區(qū)、ID、姓名和電話，假如分區(qū)固定為（武昌、漢口、漢陽(yáng)、江夏、青山、黃陂）6個(gè)分區(qū)，系統(tǒng)已對(duì)depart建立索引，查詢所有的客戶信息。 查詢一， select * from client 沒(méi)有使用任何索引列，系統(tǒng)按逐條掃描的方式返回?cái)?shù)據(jù)，執(zhí)行效率低（特別是在返回?cái)?shù)據(jù)在1w條以上時(shí)） 查詢二， select * from client where depart=武昌 or depart=漢口. 正確使用索引列depart，SQL分析器將對(duì)此SQL語(yǔ)句正確優(yōu)化，查詢效率將大大優(yōu)于前1個(gè)語(yǔ)句，在同樣訪問(wèn)的結(jié)果集情況下，這種寫法將比前一種寫法快615倍。,使用索引,問(wèn)題二：在關(guān)系student（id，name，birth）中，查詢年齡在20歲以上的所有學(xué)生信息，假設(shè)對(duì)birth建立索引。 【查詢一】select * from student where year(now)-year(birth)20 【分析】由于對(duì)索引列birth進(jìn)行了函數(shù)處理year，所以系統(tǒng)沒(méi)法對(duì)該sql語(yǔ)句進(jìn)行任何優(yōu)化，將執(zhí)行逐條掃描比較，執(zhí)行效率低。索引列的使用還有一個(gè)基本的原則，即不要對(duì)索引列進(jìn)行任何函數(shù)或者運(yùn)算符的處理。 【查詢二】select * from student where birthdateadd(year,-20,getdate() 由于直接對(duì)索引進(jìn)行比較，系統(tǒng)將能正確優(yōu)化該SQL語(yǔ)句，執(zhí)行效率高,創(chuàng)建索引基本原則,DML-數(shù)據(jù)操縱語(yǔ)言,對(duì)基本表進(jìn)行插入、修改、刪除,INSERT INTO,INSERT INTO 語(yǔ)句用于向表格中插入新的行。 語(yǔ)法 完全插入 INSERT INTO 表名稱 VALUES (值1, 值2,) 指定所要插入數(shù)據(jù)的列： INSERT INTO table_name (列1, 列2,.) VALUES (值1, 值2,),UPDATE,Update 替換表中列/字段的數(shù)值 語(yǔ)法Update table SET col = expression , . FROM fromlist Where condition 輸入 table 現(xiàn)存表的名稱 column 表 table 中列/字段的名 expression 賦予列/字段的一個(gè)有效的值或表達(dá)式 fromlist Postgres 的一個(gè)非標(biāo)準(zhǔn)的擴(kuò)展，允許別的表中的列/字段出現(xiàn)在 Where 條件里 condition,DELETE,DELETE 刪除基本表里的記錄 語(yǔ)法delete from table_name Where condition 輸入 table_name 現(xiàn)存表的名稱 condition 刪除條件,DQL-數(shù)據(jù)查詢語(yǔ)言,對(duì)基本表里的記錄進(jìn)行檢索 A.基本查詢 B.高級(jí)查詢,基本查詢語(yǔ)法,數(shù)據(jù)庫(kù)查詢是數(shù)據(jù)庫(kù)的核心操作。SQL語(yǔ)言提供了SELECT語(yǔ)句進(jìn)行數(shù)據(jù)庫(kù)的查詢，該語(yǔ)句的語(yǔ)法如下： SELECT 屬性名表或庫(kù)函數(shù) FROM 基本表 （ 或視圖 ） WHERE 條件表達(dá)式 GROUP BY 屬性名 HAVING 庫(kù)函數(shù)表達(dá)式 ORDER BY 屬性名 ASC OR DESC,基本查詢語(yǔ)法,整個(gè)SELECT語(yǔ)句的含義是，根據(jù)Where子句的條件表達(dá)式，從From子句指定的基本表或視圖中找到滿足條件的元組，再按SELECT子句中的目標(biāo)列表達(dá)式，選出元組中的屬性值形成結(jié)果表。如果有GRoup自己，則將結(jié)果按Group by后的屬性值進(jìn)行分組，該屬性列值相等的元組為1個(gè)組。通常會(huì)在每組中作用集函數(shù)。如果Group by 子句帶有Having 短語(yǔ)，則只有滿足條件的組才予輸出。如果有Order by 子句，則還需排序列的值升序或降序排序。,基本查詢,示例數(shù)據(jù)庫(kù)說(shuō)明： 以學(xué)生課程數(shù)據(jù)庫(kù)為例說(shuō)明SELECT 語(yǔ)句的各種用法： Student( Sno,Sname,Ssex,Sage,sdept,City); 學(xué)生（學(xué)號(hào)、姓名、性別、年齡、所在系、出生城市） Course (Cno,Cname); 課程（課程編號(hào)、課程名稱） SC(Sno,Cno,Score) 學(xué)生選修（學(xué)號(hào)、課程編號(hào)、成績(jī)）,基本查詢查詢表中若干列,選擇表中的若干列 1）查詢指定列 例：查詢?nèi)w學(xué)生的學(xué)號(hào)與姓名 select sno,sname from student 2）查詢?nèi)苛?例：查詢?nèi)w學(xué)生的詳細(xì)記錄 Select * from student Select sno,sname,ssex,sage,sdno from student 3)查詢經(jīng)過(guò)計(jì)算后的列 例：select sage 2 from student 4）取別名 例:select sname 姓名 from student,基本查詢過(guò)濾,1)使用distinct 關(guān)鍵字過(guò)濾重復(fù)行 例： select distinct sno From SC 2)使用where過(guò)濾條件 例：查詢所有的女學(xué)生子類 select * from student where ssex=女 例：查詢考試成績(jī)有不及格的學(xué)生的學(xué)號(hào) SELECT distinct sno From sc where score60,基本查詢where條件常用比較預(yù)算符,基本查詢排序,使用Order by 子句對(duì)查詢結(jié)果進(jìn)行升序（默認(rèn) ASC）或降序（DESC）排列，默認(rèn)為升序。 例：查詢選修了3號(hào)課程的學(xué)生的學(xué)號(hào)及成績(jī)，查詢結(jié)果按分?jǐn)?shù)的簡(jiǎn)序排列。 Select sno，score from sc where cno=3 order by score desc 例：查詢?nèi)w學(xué)生的情況，查詢結(jié)果按所在系的序號(hào)進(jìn)行升序排列，同一系中的學(xué)生按年齡降序排列 select * from student order by sdept,sage DESC,基本查詢-集函數(shù),為了進(jìn)一步方便用戶，增強(qiáng)檢索功能,SQL提供了許多集函數(shù)，主要有：,思考題：ALL和Distinct 有什么區(qū)別？,基本查詢使用集函數(shù),student(sno,sname,score),基本查詢使用集函數(shù),例：查詢學(xué)生的總?cè)藬?shù) SELECT Count(*) from student 例：查詢選修了課程的學(xué)生人數(shù) SELECT count(distinct sno) from sc; 例：計(jì)算1號(hào)課程的學(xué)生平均成績(jī) select avg(score) from sc where cno=1 例：查詢選修了1號(hào)課程的學(xué)生最高分?jǐn)?shù) select MAX(score) from sc where cno=1,基本查詢分組查詢,對(duì)查詢結(jié)果進(jìn)行分組 Group by 子句將查詢結(jié)果按某一列或多列值分組，值相等的為一組。 對(duì)查詢結(jié)果分組的目的是為了細(xì)化集函數(shù)的作用對(duì)象。如果未對(duì)查詢的結(jié)果進(jìn)行分組，集函數(shù)將作用于整個(gè)查詢結(jié)果。分組后集函數(shù)將作用于每一個(gè)組，即每一組都有一個(gè)函數(shù)值。 例：求各個(gè)課程號(hào)及相應(yīng)的選課人數(shù) select cno,count(sno) from sc group by cno; select cno, count(sno) from sc,基本查詢分組查詢,如果分組后還要求按一定的條件對(duì)這些組進(jìn)行篩選，最終只輸出滿足指定條件的組，則可以使用Having 短語(yǔ)指定篩選條件; 例：查詢選修了3門以上課程的學(xué)生學(xué)號(hào)。 select sno,count(*) from sc group by sno having count(*)=3; Where 和Having 的區(qū)別在于作用對(duì)象不同。Where子句是對(duì)分組前的元組進(jìn)行選擇。Having 作用于分組后的數(shù)據(jù)，從中選擇滿足條件的組。,基本查詢分組查詢,Having 和 Where 可以一起使用， 例：查詢考試成績(jī)80分以上至少有3門以上課程的學(xué)生學(xué)號(hào)和平均成績(jī)； 分析： 1）考試成績(jī)要在80分以上，是對(duì)分組前元組數(shù)據(jù)的選擇，使用Select； 2）至