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

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

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

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

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

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

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ù)庫中使用。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ū)分大小寫）SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2開區(qū)間還是閉區(qū)間因不同數(shù)據(jù)庫而異。不在范圍內(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)鍵字，在列或表名后面空格直接加別名。使用別名會使查詢程序更容易閱讀和書寫。給使用了函數(shù)后形成的查詢字段起別名可以縮短列寬且便于理解從多個(gè)表中取數(shù)據(jù)時(shí)使用別名表名更簡單從多個(gè)表中取數(shù)據(jù)時(shí)可能碰到同名字段，別名有助于區(qū)分字段來源主鍵（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這種寫法等同于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、行，也可寫為JOINLEFT JOIN: 即使右表中沒有匹配，也從左表返回所有的行RIGHT JOIN: 即使左表中沒有匹配，也從右表返回所有的行FULL JOIN: 只要其中一個(gè)表中存在匹配，就返回行UNION 操作符用于合并兩個(gè)或多個(gè) SELECT 語句的結(jié)果集。請注意，UNION 內(nèi)部的 SELECT 語句必須擁有相同數(shù)量的列。列也必須擁有相似的數(shù)據(jù)類型。同時(shí)，每條 SELECT 語句中的列的順序必須相同。SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2UNION ALL

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

13、name LIMIT numberOracle 語法語法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)值。如果沒有規(guī)定其他的值，那么會將默認(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 語句用于在表中創(chuàng)建索引。在不讀取整個(gè)表的情況下，索引使數(shù)據(jù)庫應(yīng)用程序可以更快地查找數(shù)據(jù)。語法：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ù)庫索引DROP INDEX index_name表DROP TABLE 表名數(shù)據(jù)庫D

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

17、可以向視圖添加 SQL 函數(shù)、WHERE 以及 JOIN 語句。視圖總是顯示最近的數(shù)據(jù)。每當(dāng)用戶查詢視圖時(shí)，數(shù)據(jù)庫引擎通過使用 SQL 語句來重建數(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ù)庫中存儲日期或日期/時(shí)間值：DATE - 格式 yyyy-MM-ddDATETIME - 格式: yyyy-MM-dd HH:mm:ssSMALLDATETIME - 格式: yyyy-MM-dd HH:mm:ssTIMESTAMP - 格式: 唯一的數(shù)字如果表中的某個(gè)列是可選的，那么我們可以在不向該列添加值的情況下插入新記錄或更新已有

19、的記錄。這意味著該字段將以 NULL 值保存。NULL 值的處理方式與其他值不同。NULL 用作未知的或不適用的值的占位符。注釋：注釋：無法比較 NULL 和 0或空字符串；它們是不等價(jià)的。無法使用比較運(yùn)算符來測試 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)換為大寫LOWER(column)將某個(gè)域轉(zhuǎn)換為小寫SUBSTR(c,start,len)從某個(gè)文本域提取字符LENGTH(column)返回某個(gè)文本域的長度，不分中英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)對某個(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 語句用于結(jié)合聚合函數(shù)，根據(jù)一個(gè)或多個(gè)列對結(jié)果集進(jìn)行分組。語法：SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;在 SQL 中增加 HAVING 子句原因是，WHERE 關(guān)鍵字無法與聚合函數(shù)一起使用。語法：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ù)庫的可操作性可使開發(fā)人員不用閱讀文檔，對表名望文生義，提高操作、維護(hù)效率表名：T_系統(tǒng)_模塊_功能_對象視圖名：V_系統(tǒng)_模塊_功能_對象存儲過程名：PRO_系統(tǒng)_模塊_功能函數(shù)名：F_系統(tǒng)_功能_對象自己新建的文件在開始部分注釋作者、新建日期、功能概述等在每個(gè)功能點(diǎn)或值得關(guān)注的地方前添加注釋增加或修改代碼要在代碼前添加注釋，包含操作、作者、日期、

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

26、個(gè)唯一的數(shù)字。有時(shí)候我們希望在每次插入新記錄時(shí)，自動地創(chuàng)建主鍵字段的值。這時(shí)可以在表中創(chuàng)建一個(gè) auto-increment字段。SQL SERVER：CREATE TABLE Persons ( Id int PRIMARY KEY DENTITY, )默認(rèn)IDENTITY開始值是 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 的序列對象，它以 1 起始且以 1 遞增。該對象緩存 10 個(gè)值以提高性能。CACHE 選項(xiàng)規(guī)定了為了提高訪問速度要存儲多少個(gè)序列值。要在 Persons 表中插入新記錄，我們必須使用 nextval 函數(shù)（該函數(shù)從 seq_person 序列中取回下一個(gè)值）：INSERT INTO Persons (Id,FirstName,LastName) VALUES (seq_person.nextval,Lars,Monsen)能