關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL

1、1第五章第五章關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL2本章目標(biāo)q掌握創(chuàng)建、修改、刪除基本表的標(biāo)準(zhǔn)SQL語(yǔ)句q掌握插入、修改、刪除基本表數(shù)據(jù)的標(biāo)準(zhǔn)SQL語(yǔ)句q掌握查詢基本表數(shù)據(jù)的標(biāo)準(zhǔn)SQL語(yǔ)句q掌握常用的SQL統(tǒng)計(jì)函數(shù)35.1 SQL概述 q SQL（Structured Query Language）是關(guān)系數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)語(yǔ)言。目前，所有的商業(yè)化的關(guān)系DBMS均支持SQL語(yǔ)言。q ISO組織公布的SQL標(biāo)準(zhǔn)：qSQL-89qSQL-92qSQL-99qSQL-2003SQL Server2005符合ANSI SQL-99的定義4SQL的主要功能SQL語(yǔ)言按照功能可以分為3大類：q數(shù)據(jù)定義語(yǔ)言DDL（Data D

2、efinition Language） 用于定義關(guān)系數(shù)據(jù)庫(kù)的模式、外模式和內(nèi)模式，以實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)基本表、視圖及索引文件的定義、修改和刪除等操作。q數(shù)據(jù)操縱語(yǔ)言DML（Data Manipulation Language） 用于完成數(shù)據(jù)查詢和數(shù)據(jù)更新操作。q數(shù)據(jù)控制語(yǔ)言DCL（Data Control Language） 用于控制對(duì)數(shù)據(jù)庫(kù)及其對(duì)象的訪問(wèn)，服務(wù)器的關(guān)閉、啟動(dòng)等操作。5SQL的特點(diǎn)q語(yǔ)言簡(jiǎn)潔，風(fēng)格統(tǒng)一，易學(xué)易懂q既是自含式語(yǔ)言，又是嵌入式語(yǔ)言qSQL能夠獨(dú)立地用于聯(lián)機(jī)交互的使用方式。q可以嵌入到如Visual C#、Basic、PowerBuild、Java、ASP、JSP、Powe

3、rBuild等這樣的前端開(kāi)發(fā)平臺(tái)上。q高度非過(guò)程化 用SQL語(yǔ)言進(jìn)行數(shù)據(jù)操作，只要提出“做什么”，而無(wú)須知道描述“怎么做”，SQL語(yǔ)句的其實(shí)現(xiàn)過(guò)程由系統(tǒng)自動(dòng)完成。6SQL對(duì)象命名約定 SQL對(duì)象包括數(shù)據(jù)庫(kù)、表、視圖、列名等。這些對(duì)象名必須符合一定規(guī)則或約定：q數(shù)據(jù)對(duì)象名是為1-30長(zhǎng)度不超過(guò)30個(gè)字符的字符串。q數(shù)據(jù)對(duì)象名應(yīng)以字母開(kāi)頭，其余字符可以由字母、數(shù)字、下劃線組成。 7SQL語(yǔ)句的書(shū)寫(xiě)準(zhǔn)則 q SQL語(yǔ)句語(yǔ)法格式中的一些約定符號(hào)：q尖括號(hào)“”中的內(nèi)容為必選項(xiàng)。q中括號(hào)“ ”中的內(nèi)容為任選項(xiàng)。q，. . .意思是“等等”，即前面的項(xiàng)可以重復(fù)。q大括號(hào)“”與豎線“|”表明此處為選擇項(xiàng)，在所

4、列出的各項(xiàng)中僅需選擇一項(xiàng)。 例如： A | B | C | D 意思是在A、B、C、D中取其一。qSQL中的數(shù)據(jù)項(xiàng)分隔符為“，”；其字符串常數(shù)量的定界符用為單撇號(hào)“”表示。8SQL語(yǔ)句的書(shū)寫(xiě)準(zhǔn)則 q 在編寫(xiě)SQL語(yǔ)句時(shí)，遵從一些通常的準(zhǔn)則：qSQL語(yǔ)句對(duì)不區(qū)分字母的大小寫(xiě)不敏感。 為了提高SQL語(yǔ)句的可讀性，子句開(kāi)頭的關(guān)鍵字通常采用大寫(xiě)形式。q一個(gè)SQL語(yǔ)句可寫(xiě)成一行或多行，習(xí)慣上每個(gè)子句占用一行。q一個(gè)關(guān)鍵字不能在行與行之間分開(kāi)寫(xiě)開(kāi)在多行上，并且很少采用縮寫(xiě)形式。qSQL語(yǔ)句的結(jié)束符為分號(hào)“；”。9插入記錄插入記錄 SQL語(yǔ)言的插入語(yǔ)句為INSERT，有兩種形式：?jiǎn)涡胁迦牒投嘈胁迦?。q 插入

5、單條記錄 INSERT INTO (,) VALUES (,); 該命令將單條新記錄插入指定表中，屬性列與常量根據(jù)位置對(duì)應(yīng)。 注意： （1）屬性列的個(gè)數(shù)與常量的個(gè)數(shù)要相等，且順序一致。 （2）定義為NOT NULL的屬性列，若沒(méi)有出現(xiàn)在INTO子句中，這些列將取空值。定義為NOT NULL的屬性列且未定義默認(rèn)約束的屬性列，則必須出現(xiàn)在INTO子句后。 （3）如果INTO子句中沒(méi)有指定任何列，則VALUES子句后面的常量個(gè)數(shù)必須與基本表中列的個(gè)數(shù)相等，且類型、順序一致?！纠吭诓块T表dept中插入一條部門新記錄。 INSERT INTO dept (dept_id,dname,tel) VALU

6、ES (311,產(chǎn)品開(kāi)發(fā)部,08667864532);【例】在員工表employee中插入一條員工新記錄。 INSERT INTO employee VALUES(1311,陳淳,NULL,NULL, to_date(2004/08/15),800,200,11);10q6-14在users表插入一條新紀(jì)錄。INSERT INTO users (uid,uname,uidno.u_utid) VALUES (201008120021,陳東1);q等價(jià)于：INSERT INTO users VALUES (201008120021,陳東,310228198

7、012230046,1);11q6-15在cards表中插入一條新紀(jì)錄。INSERT INTO cardsVALUES (100008, 201008120021,200,cast(2010-10-15 12:00:00 as datetime), 1);12修改記錄 q SQL語(yǔ)言修改記錄的語(yǔ)句為UPDATE。該語(yǔ)句有3種形式：修改單條記錄、修改多條記錄以及使用子查詢修改記錄。 UPDATE SET =,=, WHERE ; SQL語(yǔ)言的修改UPDATE語(yǔ)句的功能是將指定表中符合WHERE子句條件的記錄找出，以表達(dá)式的值替代相應(yīng)屬性列的值。13修改單條記錄q6-17將UTYPES表中臨時(shí)人員

8、的借閱限量改為10.UPDATE UTYPESSET borrowmaxnumber=10Where UTNAME=臨時(shí)人員;14修改單條記錄【例】修改1311號(hào)員工的月系薪為1700。 UPDATE employee SET sal=1700 WHERE emp_id=1311 ;15修改多條記錄q6-18將UTYPES表中學(xué)生和臨時(shí)人員的借閱限量在現(xiàn)有基礎(chǔ)上加10.UPDATE UTYPESSET borrowmaxnumber=borrowmaxnumber+10Where UTNAME=臨時(shí)人員 or UTNAME=學(xué)生 ;16修改多條記錄 【例】所有員工月薪上調(diào)5%。 UPDATE

9、employee SET sal=sal*1.05 ;【例】取消編號(hào)為21號(hào)部門的的所有員工取消的崗位津貼。 UPDATE employee SET comm=0 WHERE dept_id=21 ;17刪除記錄刪除記錄 SQL語(yǔ)言刪除記錄的語(yǔ)句是DELETE。該語(yǔ)句有3種形式：刪除單條記錄、刪除多條記錄以及使用子查詢刪除。 DELETE FROM WHERE ; DELETE語(yǔ)句的功能是：先按照將指定表中符合WHERE子句中指定的條件的范圍將記錄找出來(lái)，然后進(jìn)行刪除。 18刪除單條記錄 【例】刪除員工號(hào)為1045的記錄。 DELETE FROM employee WHERE emp_id=1

10、045 ;6-20刪除cards表中校園卡編號(hào)為100005的記錄DeleteFrom cardsWhere cid=100005;19刪除多條記錄q6-21刪除cards表中卡狀態(tài)為注銷的記錄DeleteFrom cardsWhere cstatus=3;q6-22刪除例6-16創(chuàng)建的cards_cancel表中所有記錄。DeleteFrom cards_cancel;20刪除多條記錄 【例】刪除部門號(hào)為31的所有員工記錄（不包括部門主管）。 DELETE FROM employee WHERE dept_id=31 and job部門主管;【例】刪除所有員工記錄。 DELETE FROM

11、employee ; 刪除所有記錄，是清空表中數(shù)據(jù)，因此不加WHERE子句。這類操作的執(zhí)行應(yīng)非常小心慎重。 215.4數(shù)據(jù)查詢 SELECT語(yǔ)句執(zhí)行數(shù)據(jù)庫(kù)的查詢操作，是DML語(yǔ)句中的核心部分。 SELECT語(yǔ)句的作用是讓數(shù)據(jù)庫(kù)服務(wù)器根據(jù)客用戶要求搜索出所需要的數(shù)據(jù)庫(kù)數(shù)據(jù)，并將形成得到的結(jié)果集按規(guī)定格式返回給用戶。 SELECT語(yǔ)句具有強(qiáng)大的查詢功能，能實(shí)現(xiàn)數(shù)據(jù)查詢、結(jié)果排序、分組、統(tǒng)計(jì)以及多表連接查詢等功能。22SELECT語(yǔ)句的結(jié)構(gòu) SQL語(yǔ)言提供的SELECT語(yǔ)句的一般格式：SELECT ALL|DISTINCT ，, FROM ,WHERE GROUP BY HAVING ORDER B

12、Y ASC|DESC；23選擇行和列q選擇滿足條件的若干行記錄 （1）查詢一個(gè)表的所有行 在SELECT語(yǔ)句中只要沒(méi)有WHERE子句，查詢結(jié)果就包含了所有行。6-24 查詢USERS表所有持卡人信息。 SELECT * FROM users; 【例】查詢表employee的所有員工的員工號(hào)和姓名的信息。 SELECT emp_id,ename FROM employee;2425選擇行和列（2）查詢滿足條件的某些行 查詢滿足條件的某些行， 可以通過(guò)WHERE子句來(lái)實(shí)現(xiàn)條件選擇。6-25 查詢cards表中卡內(nèi)余額超過(guò)50元的校園卡編號(hào)和卡內(nèi)余額。 SELECT cid，cbalance FRO

13、M cards WHERE cbalance 50;6-26查詢cards表中編號(hào)為100005的全部?jī)?nèi)容。 SELECT * FROM cards WHERE cid=100005;【例】查詢?cè)滦匠^(guò)1800的員工姓名和月薪信息。 SELECT ename,sal FROM employee WHERE sal 1800;【例5】查詢員工號(hào)為1001的員工姓名及部門號(hào)信息。 SELECT ename,dept_id FROM employee WHERE emp_id=1001;2627選擇滿足條件記錄的若干列 （1）查詢表的全部列【例】查詢部門表中部門編號(hào)號(hào)為11的全部?jī)?nèi)容部門信息。 SE

14、LECT dept_id,dname,tel FROM dept WHERE dept_id=11; 等價(jià)于 SELECT * FROM dept WHERE dept_id=11; 28選擇滿足條件記錄的若干列 （2）查詢表的部分列 在很多情況下，查詢結(jié)果只需要顯示部分列，這時(shí)SELECT子句的中要指定列名或表達(dá)式。 【例】查詢?nèi)w員工的員工號(hào)，姓名和參加工作時(shí)間。 SELECT emp_id,ename,workdate FROM employee ;查詢經(jīng)過(guò)計(jì)算的值。6-28查詢UTYPES表中持卡人的借閱限期，以月計(jì)算。Select utid,utname,borrowmaxdays/

15、30From userstypes;29q查詢時(shí)在中使用字符串常量，結(jié)果會(huì)更清晰。Select utid 類型編號(hào),utname 類型名稱,borrowmaxdays/30 借閱限期(月)From userstypes;如果要把顯示結(jié)果的重復(fù)行去掉用DISTINCT命令。6-30Select DISTINCT botitleFrom books;3031選擇滿足條件記錄的若干列 （3）查詢經(jīng)過(guò)計(jì)算的值 SELECT子句的不僅可以是基本表的屬性，也可以是表達(dá)式，包括算術(shù)表達(dá)式、字符串常量和函數(shù)等。 【例】查詢?nèi)w員工的姓名及年薪。 SELECT ename,sal*12 FROM employe

16、e ; 【例】上例查詢?nèi)绻谥惺褂米址Ａ?，結(jié)果會(huì)更清晰。 SELECT ename 姓名,sal*12 年薪 FROM employee ;查詢結(jié)果如下：ename sal*12 - - 吳偉 16380王斌 18900岳玲 13860徐歡 10080陳淳 21420查詢結(jié)果如下：姓名 年薪 - - 吳偉 16380王斌 18900岳玲 13860徐歡 10080陳淳 2142032模糊查詢 SQL語(yǔ)言提供了使用運(yùn)算符LIKE以及通配符“%”（百分號(hào)）和“_”（下劃線），用來(lái)實(shí)現(xiàn)模糊查詢。 q “%”代表任意長(zhǎng)度（長(zhǎng)度可以為0）的字符串。q “_”代表任意單個(gè)字符。q6-31查詢所有姓李的

17、持卡人的姓名。Select unameFrom usersWhere uname like李%;q6-32查詢姓名第2個(gè)字為立的員工姓名。Select unameFrom usersWhere uname like_立%;3334模糊查詢 【例】查詢所有姓張員工的姓名。 SELECT ename FROM employee WHERE ename LIKE 張% ;【例】查找姓名第2個(gè)字為“偉”的員工姓名。 SELECT ename FROM employee WHERE ename LIKE _偉%;35消除重復(fù)行 基本表中不相同的行，經(jīng)過(guò)對(duì)某些指定列進(jìn)行投影運(yùn)算后，可能會(huì)變成完全相同的行，

18、顯示結(jié)果不直觀，這時(shí)需要用DISTINCT選項(xiàng)消除重復(fù)的行。 【例】查詢表employee中的所有職位（job）。 由于很多員工都屬于同一職位，屬性列job的值會(huì)出現(xiàn)重復(fù)，因此要使用選項(xiàng)DISTINCT。 SELECT DISTINCT job FROM employee ;36SQL的統(tǒng)計(jì)函數(shù) SQL語(yǔ)言提供了許多統(tǒng)計(jì)函數(shù)，主要的統(tǒng)計(jì)有：統(tǒng)計(jì)函數(shù)統(tǒng)計(jì)函數(shù)語(yǔ)義語(yǔ)義COUNT(DISTINCT|ALL*)統(tǒng)計(jì)表的記錄個(gè)數(shù)統(tǒng)計(jì)表的記錄個(gè)數(shù)COUNT(DISTINCT|ALL)統(tǒng)計(jì)一列中值不為統(tǒng)計(jì)一列中值不為NULL值的值的個(gè)數(shù)個(gè)數(shù)SUM(DISTINCT|ALL)計(jì)算一列值的總和（此列必須計(jì)算一列值的總和（此列必須為數(shù)值型）為數(shù)值型）AVG(DISTINCT|ALL)計(jì)算一列值的平均值（此列必計(jì)算一列值的平均值（此列必須為數(shù)值型）須為數(shù)值型）MAX(DISTINCT|ALL)給出一列值中的最大值給出一列值中的最大值MIN(DISTINCT|ALL)給出一列值中的最小值給出一列值中的最小值6-33 統(tǒng)計(jì)持卡人總?cè)藬?shù)。 SELECT COUNT（*） FROM users ; 也可以寫(xiě)成： SELECT COUNT（UID） FROM users ;q統(tǒng)計(jì)USERS表中類