SQL基礎(chǔ)與常用技巧

1、SQL基礎(chǔ)知識(shí)數(shù)據(jù)庫(kù)、表介紹基本概念和查詢語(yǔ)句建數(shù)據(jù)庫(kù)、建表進(jìn)階子句、函數(shù)的應(yīng)用優(yōu)化使用技巧SQL基礎(chǔ)SQL進(jìn)階SQL技巧數(shù)據(jù)庫(kù)就是存放數(shù)據(jù)的倉(cāng)庫(kù)。是一組相關(guān)數(shù)據(jù)的集合。數(shù)據(jù)庫(kù)由表、關(guān)系和操作對(duì)象組成。表行和列行記錄（客觀存在、可被描述）列字段、屬性DBMS：操縱和管理數(shù)據(jù)庫(kù)的大型軟件，用于建立、使用和維護(hù)數(shù)據(jù)庫(kù)。常用：SYBASE、ORACLE、DB2、Mysql、SQL Server、Postgre。SQL：用于存取數(shù)據(jù)以及查詢、更新和管理DBMS數(shù)據(jù)庫(kù)的概念數(shù)據(jù)的存放數(shù)據(jù)庫(kù)管理系統(tǒng)結(jié)構(gòu)化查詢語(yǔ)言SQL是一門標(biāo)準(zhǔn)語(yǔ)言。但根據(jù)數(shù)據(jù)庫(kù)操作系統(tǒng)的不同存在一些不同版本的SQL。換句話說(shuō)，除了一些主

2、要的關(guān)鍵詞和語(yǔ)法外，各數(shù)據(jù)庫(kù)管理系統(tǒng)中都有關(guān)于SQL的擴(kuò)展語(yǔ)言。標(biāo)準(zhǔn)SQL在任何DBMS中都可以正常使用。SQL可以查詢、插入記錄?？梢愿聰?shù)據(jù)、刪除記錄?？梢詣?chuàng)建數(shù)據(jù)庫(kù)?？梢詣?chuàng)建表、存儲(chǔ)過(guò)程、視圖。為表、存儲(chǔ)過(guò)程、視圖設(shè)置權(quán)限。每張表都有一個(gè)名字。每張表都有至少一個(gè)列。表用來(lái)存放記錄，新建的表中沒(méi)有記錄，是一張空表。IdNameSexProvinceCity1趙阿土男臺(tái)灣省臺(tái)北市2錢夫人女福建省廈門市3孫小美女廣東省廣州市4李寶藏男江蘇省蘇州市如下表，表名為：Persons。該表含有Id、姓名、性別、省、市這5個(gè)列。該表包含4條記錄，就是4行數(shù)據(jù)。SQL語(yǔ)言是一種大小寫(xiě)不敏感的語(yǔ)言，書(shū)寫(xiě)SQ

3、L的時(shí)候理論上無(wú)需在乎大小寫(xiě)。在每一段完整SQL語(yǔ)句后增加分號(hào)（；）某些數(shù)據(jù)庫(kù)會(huì)要求在每句SQL后加上分號(hào)，某些不需要。SQL單行注釋是 （2個(gè)-）SQL多行注釋是 /*/但在實(shí)際開(kāi)發(fā)中可以用：大寫(xiě)表名SQL關(guān)鍵字大寫(xiě)或首字母大寫(xiě)SELECT、FROM、WHERE等關(guān)鍵字前換行每句SQL后都以分號(hào)做結(jié)尾每句SQL均增加注釋等方式增加SQL代碼的可讀性。數(shù)據(jù)操作語(yǔ)言DMLlSELECT從數(shù)據(jù)庫(kù)表中獲取數(shù)據(jù)lUPDATE更新數(shù)據(jù)庫(kù)中表中的數(shù)據(jù)lDELETE從數(shù)據(jù)庫(kù)表中刪除數(shù)據(jù)lINSERT INTO向數(shù)據(jù)庫(kù)表中插入數(shù)據(jù)數(shù)據(jù)定義語(yǔ)言DDLCREATE DATABASE創(chuàng)建新數(shù)據(jù)庫(kù)ALTER DATA

4、BASE修改數(shù)據(jù)庫(kù)CREATE TABLE創(chuàng)建新表ALTER TABLE變更（改變）數(shù)據(jù)庫(kù)表DROP TABLE刪除表CREATE INDEX創(chuàng)建索引（搜索鍵）DROP INDEX刪除索引SELECT單列：SELECT 列名 FROM 表名多列：SELECT 列名,列名, FROM 表名所有列：SELECT * FROM 表名DISTINCTSELECT DISTINCT 列名 FROM 表名返回不重復(fù)的結(jié)果集WHERE用于規(guī)定選擇的標(biāo)準(zhǔn)（返回結(jié)果集的條件）SELECT 列名 FROM 表名 WHERE 列 運(yùn)算符 值運(yùn)算符：=、(!=)、=、=、between、like值若為字符串格式，則應(yīng)

5、該加單引號(hào) 為空判斷獨(dú)有的寫(xiě)法is null、is not nullAND 和 OR運(yùn)算符在WHERE子句中將多個(gè)條件連接起來(lái)AND并且OR 或者AND比OR擁有更高的優(yōu)先級(jí)使用時(shí)注意配合括號(hào)ORDER BY 語(yǔ)句用于根據(jù)指定的列對(duì)結(jié)果集進(jìn)行排序。默認(rèn)升序升序?yàn)锳SC降序?yàn)镈ESCINSERT INTO語(yǔ)句用于向表格中插入新的行。INSERT INTO 表名稱 VALUES (值1, 值2,.) -所有列INSERT INTO table_name (列1, 列2,.) VALUES (值1, 值2,.) -選擇列Update 語(yǔ)句用于修改表中的數(shù)據(jù)。語(yǔ)法：UPDATE 表名SET 列名 =

6、新值 , 列名 = 新值WHERE 列名 = 某值;DELETE 語(yǔ)句用于刪除表中的行。DELETE FROM 表名WHERE 列名 = 值;刪除部分?jǐn)?shù)據(jù)使用delete時(shí)，最好先使用select查詢，檢查一下結(jié)果集是不是要?jiǎng)h除的數(shù)據(jù)，沒(méi)有問(wèn)題再刪LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式?！? 可用于定義通配符（模式中缺少的文字）。SELECT * FROM Persons WHERE Name LIKE 趙%SELECT * FROM Persons WHERE Name LIKE %美SELECT * FROM Persons WHERE Name LIKE %夫%語(yǔ)法

7、：SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern通配符通配符含義含義%替代一個(gè)或多個(gè)字符_僅替代一個(gè)字符charlist字符列中的任何單一字符charlist或者!charlist不在字符列中的任何單一字符SELECT * FROM Persons WHERE Name LIKE 趙錢孫%SELECT * FROM Persons WHERE Name LIKE !趙錢孫%在ORACLE中只有%和_這兩種通配符。 通配符在mySQL等數(shù)據(jù)庫(kù)中使用。IN 操作符在 WHERE 子句中規(guī)定多個(gè)值。SELECT

8、 column_name(s) FROM table_name WHERE column_name IN (value1,value2,.)不在規(guī)定多個(gè)值之中的使用NOT ININ 操作符在 WHERE選取介于兩個(gè)值之間的數(shù)據(jù)范圍。這些值可以是數(shù)值、文本或者日期。（文本只支持字母且區(qū)分大小寫(xiě)）SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2開(kāi)區(qū)間還是閉區(qū)間因不同數(shù)據(jù)庫(kù)而異。不在范圍內(nèi)使用NOT BETWEEN可以為列名或表名指定別名表：SELECT column_name(s) F

9、ROM table_name AS alias_name列：SELECT column_name AS alias_name FROM table_name在ORACLE中可以省略AS關(guān)鍵字，在列或表名后面空格直接加別名。使用別名會(huì)使查詢程序更容易閱讀和書(shū)寫(xiě)。給使用了函數(shù)后形成的查詢字段起別名可以縮短列寬且便于理解從多個(gè)表中取數(shù)據(jù)時(shí)使用別名表名更簡(jiǎn)單從多個(gè)表中取數(shù)據(jù)時(shí)可能碰到同名字段，別名有助于區(qū)分字段來(lái)源主鍵（Primary Key）是一個(gè)列，在這個(gè)列中的每一行的值都是唯一的。在表中，每個(gè)主鍵的值都是唯一的。這樣做的目的是在不重復(fù)每個(gè)表中的所有數(shù)據(jù)的情況下，把表間的數(shù)據(jù)交叉捆綁在一起。主鍵P

10、rimary Key外鍵Foreign Keyselect , lo.order_nofrom LESSON_ORDERS lo, LESSON_PERSONS lpwhere lo.person_id = lp.id這種寫(xiě)法等同于INNER JOIN 為了得到完整的結(jié)果，需要從兩個(gè)或更多的表中獲取結(jié)果。這時(shí)就需要執(zhí)行 join。select , lo.order_nofrom LESSON_ORDERS lo inner join LESSON_PERSONS lpon lo.person_id = lp.idINNER JOIN: 如果表中有至少一個(gè)匹配，則返回

11、行，也可寫(xiě)為JOINLEFT JOIN: 即使右表中沒(méi)有匹配，也從左表返回所有的行RIGHT JOIN: 即使左表中沒(méi)有匹配，也從右表返回所有的行FULL JOIN: 只要其中一個(gè)表中存在匹配，就返回行UNION 操作符用于合并兩個(gè)或多個(gè) SELECT 語(yǔ)句的結(jié)果集。請(qǐng)注意，UNION 內(nèi)部的 SELECT 語(yǔ)句必須擁有相同數(shù)量的列。列也必須擁有相似的數(shù)據(jù)類型。同時(shí)，每條 SELECT 語(yǔ)句中的列的順序必須相同。SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2UNION ALL

12、操作符用法和意義與UNION相同。區(qū)別在于UNION ALL 命令會(huì)列出所有的值。而UNION 操作符選取不同的值。結(jié)果集中的列名總是等于 UNION 中第一個(gè) SELECT 語(yǔ)句中的列名。SQL Statement 1 UNION ALL SQL Statement 2TOP 子句用于規(guī)定要返回的記錄的數(shù)目。注意：并非所有的數(shù)據(jù)庫(kù)系統(tǒng)都支持 TOP 子句。SQL Server 的語(yǔ)法：的語(yǔ)法：SELECT TOP number | percent column_name(s) FROM table_nameMySQL 語(yǔ)法語(yǔ)法SELECT column_name(s) FROM table_

13、name LIMIT numberOracle 語(yǔ)法語(yǔ)法SELECT column_name(s) FROM table_name WHERE ROWNUM = number例子：M：SELECT * FROM Persons LIMIT 5O：SELECT * FROM Persons WHERE ROWNUM 0 AND City=杭州)單獨(dú)定義：ALTER TABLE Persons ADD CHECK (Id0);撤銷：ALTER TABLE Persons DROP CONSTRAINT chk_PersonDEFAULT 約束用于向列中插入默認(rèn)值。如果沒(méi)有規(guī)定其他的值，那么會(huì)將默認(rèn)

14、值添加到所有的新記錄。在定義數(shù)據(jù)類型后增加DEFAULT 值，也可以使用函數(shù)。如DEFAULT 北京市 DEFAULT GETDATE()單獨(dú)定義：ALTER TABLE Persons ALTER COLUMN CitySET DEFAULT 北京市撤銷：ALTER TABLE Persons ALTER COLUMN City DROP DEFAULTCREATE INDEX 語(yǔ)句用于在表中創(chuàng)建索引。在不讀取整個(gè)表的情況下，索引使數(shù)據(jù)庫(kù)應(yīng)用程序可以更快地查找數(shù)據(jù)。語(yǔ)法：CREATE INDEX index_name ON table_name (column_name);在表上創(chuàng)建一個(gè)唯一

15、的索引。唯一的索引意味著兩個(gè)行不能擁有相同的索引值。CREATE UNIQUE INDEX index_name ON table_name (column_name);創(chuàng)建CREATE INDEX PersonIndex ON Person (LastName) 保留字CREATE INDEX PersonIndex ON Person (LastName DESC) 多列CREATE INDEX PersonIndex ON Person (LastName, FirstName)DROP刪除索引、表和數(shù)據(jù)庫(kù)索引DROP INDEX index_name表DROP TABLE 表名數(shù)據(jù)庫(kù)D

16、ROP DATABASE 數(shù)據(jù)庫(kù)名ALTER TABLE 語(yǔ)句用于在已有的表中添加、修改或刪除列。增：ALTER TABLE table_name ADD column_name datatype刪：ALTER TABLE table_name DROP COLUMN column_name要改變表中列的數(shù)據(jù)類型，使用下列語(yǔ)法：ALTER TABLE table_name ALTER COLUMN column_name datatype在 SQL 中，視圖是基于 SQL 語(yǔ)句的結(jié)果集的可視化的表。視圖包含行和列，就像一個(gè)真實(shí)的表。視圖中的字段就是來(lái)自一個(gè)或多個(gè)數(shù)據(jù)庫(kù)中的真實(shí)的表中的字段。我們

17、可以向視圖添加 SQL 函數(shù)、WHERE 以及 JOIN 語(yǔ)句。視圖總是顯示最近的數(shù)據(jù)。每當(dāng)用戶查詢視圖時(shí)，數(shù)據(jù)庫(kù)引擎通過(guò)使用 SQL 語(yǔ)句來(lái)重建數(shù)據(jù)。創(chuàng)建視圖：CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition更新視圖：CREATE OR REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition刪除視圖：DROP VIEW view_name函數(shù)函數(shù)描述描述GETDATE()返回當(dāng)前日期

18、和時(shí)間DATEPART()返回日期/時(shí)間的單獨(dú)部分DATEADD()在日期中添加或減去指定的時(shí)間間隔DATEDIFF()返回兩個(gè)日期之間的時(shí)間CONVERT()用不同的格式顯示日期/時(shí)間SQL Server內(nèi)建日期函數(shù)SQL Server 使用下列數(shù)據(jù)類型在數(shù)據(jù)庫(kù)中存儲(chǔ)日期或日期/時(shí)間值：DATE - 格式 yyyy-MM-ddDATETIME - 格式: yyyy-MM-dd HH:mm:ssSMALLDATETIME - 格式: yyyy-MM-dd HH:mm:ssTIMESTAMP - 格式: 唯一的數(shù)字如果表中的某個(gè)列是可選的，那么我們可以在不向該列添加值的情況下插入新記錄或更新已有

19、的記錄。這意味著該字段將以 NULL 值保存。NULL 值的處理方式與其他值不同。NULL 用作未知的或不適用的值的占位符。注釋：注釋：無(wú)法比較 NULL 和 0或空字符串；它們是不等價(jià)的。無(wú)法使用比較運(yùn)算符來(lái)測(cè)試 NULL 值，比如 =, , 或者 。我們必須使用 IS NULL 和 IS NOT NULL 操作符。Null函數(shù)SQL SERVER：SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0) FROM ProductsORACLE：SELECT order_no,Unit_Price*(unit_pric

20、e+NVL(order_num,0) FROM LESSON_ORDERS_BAK函數(shù)函數(shù)描述描述AVG(column)返回某列的平均值COUNT(column)返回某列的行數(shù)（不包括NULL值）COUNT(*)返回被選行數(shù)COUNT(DISTINCT column)返回相異結(jié)果的數(shù)目MAX(column)返回某列的最高值MIN(column)返回某列的最低值SUM(column)返回某列的總和聚合函數(shù)（聚合函數(shù)（Aggregate functions）Aggregate 函數(shù)的操作面向一系列的值，并返回一個(gè)單一的值。標(biāo)量函數(shù)的操作面向某個(gè)單一的值，并返回基于輸入值的一個(gè)單一的值。函數(shù)函數(shù)描述

21、描述UPPER(column)將某個(gè)域轉(zhuǎn)換為大寫(xiě)LOWER(column)將某個(gè)域轉(zhuǎn)換為小寫(xiě)SUBSTR(c,start,len)從某個(gè)文本域提取字符LENGTH(column)返回某個(gè)文本域的長(zhǎng)度，不分中英LENGTHB(column)返回某個(gè)文本域的字符數(shù)，1個(gè)中文算2個(gè)字符INSTR(c,char)返回在某個(gè)文本域中指定字符的數(shù)值位置，不分中英INSTRB(c,char)返回在某個(gè)文本域中指定字符的數(shù)值位置，1個(gè)中文算2個(gè)字符ROUND(c,decimals)對(duì)某個(gè)數(shù)值域進(jìn)行指定小數(shù)位數(shù)的四舍五入MOD(x,y)返回除法操作的余數(shù)TO_CHAR(d,format)將日期轉(zhuǎn)換為指定格式的文

22、本TO_DATE(c,format)將指定格式的文本轉(zhuǎn)換為日期GROUP BY 語(yǔ)句用于結(jié)合聚合函數(shù)，根據(jù)一個(gè)或多個(gè)列對(duì)結(jié)果集進(jìn)行分組。語(yǔ)法：SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;在 SQL 中增加 HAVING 子句原因是，WHERE 關(guān)鍵字無(wú)法與聚合函數(shù)一起使用。語(yǔ)法：SELECT column_name, aggregate_function(column_name) FROM tab

23、le_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value好的命名規(guī)則增加數(shù)據(jù)庫(kù)的可操作性可使開(kāi)發(fā)人員不用閱讀文檔，對(duì)表名望文生義，提高操作、維護(hù)效率表名：T_系統(tǒng)_模塊_功能_對(duì)象視圖名：V_系統(tǒng)_模塊_功能_對(duì)象存儲(chǔ)過(guò)程名：PRO_系統(tǒng)_模塊_功能函數(shù)名：F_系統(tǒng)_功能_對(duì)象自己新建的文件在開(kāi)始部分注釋作者、新建日期、功能概述等在每個(gè)功能點(diǎn)或值得關(guān)注的地方前添加注釋增加或修改代碼要在代碼前添加注釋，包含操作、作者、日期、

24、功能概述等將常用查詢條件或業(yè)務(wù)主鍵作為索引會(huì)提高查詢效率。用戶無(wú)法看到索引，它們只能被用來(lái)加速搜索/查詢。更新一個(gè)包含索引的表需要比更新一個(gè)沒(méi)有索引的表更多的時(shí)間，這是由于索引本身也需要更新。但查詢時(shí)不要使用： 、!=這樣的運(yùn)算符is null、is not null相關(guān)函數(shù)，如trunc建議：使用num or num來(lái)代替 num把需要索引的列設(shè)為not null可查詢卻不可以增刪改的列ROWID是行的存儲(chǔ)地址，物理主鍵，快速定位一條記錄ROWNUM是結(jié)果集中的行號(hào)，主要用于限制查詢返回的行數(shù)對(duì)記錄條數(shù)使用COUNT函數(shù)時(shí)Count(*)的速度略快于count(1)若對(duì)主鍵（ID）建立了索引

25、，則count(ID)還是最快的使用DECODE函數(shù)可以避免重復(fù)掃描相同記錄或重復(fù)連接相同的表select count(*) from LESSON_PERSONS where sex = 男;select count(*) from LESSON_PERSONS where sex = 女;select count(decode(sex,男,m,null) as count_male,sum(decode(sex,女,1,0) as count_female from LESSON_PERSONSAuto-increment 會(huì)在新記錄插入表會(huì)在新記錄插入表中時(shí)生成一個(gè)唯一的數(shù)字。中時(shí)生成一

26、個(gè)唯一的數(shù)字。有時(shí)候我們希望在每次插入新記錄時(shí)，自動(dòng)地創(chuàng)建主鍵字段的值。這時(shí)可以在表中創(chuàng)建一個(gè) auto-increment字段。SQL SERVER：CREATE TABLE Persons ( Id int PRIMARY KEY DENTITY, )默認(rèn)IDENTITY開(kāi)始值是 1每行遞增 1。要規(guī)定 Id 列以 20 起始且遞增 10，把 identity 改為 IDENTITY(20,10)INSERT時(shí)可直接忽略這個(gè)字段。ORACLE：CREATE SEQUENCE seq_person MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10上面的代碼創(chuàng)建名為 seq_person 的序列對(duì)象，它以 1 起始且以 1 遞增。該對(duì)象緩存 10 個(gè)值以提高性能。CACHE 選項(xiàng)規(guī)定了為了提高訪問(wèn)速度要存儲(chǔ)多少個(gè)序列值。要在 Persons 表中插入新記錄，我們必須使用 nextval 函數(shù)（該函數(shù)從 seq_person 序列中取回下一個(gè)值）：INSERT INTO Persons (Id,FirstName,LastName) VALUES (seq_person.nextval,Lars,Monsen)能