正則表達(dá)式
字符類別
| 類別 | 描述 |
|---|---|
| \d | 任意數(shù)字（等同于[0-9]） |
| \D | 任意非數(shù)字字符（等同于[^0-9]） |
| \w | 任意字母或數(shù)字（等同于[a-zA-Z0-9]） |
| \W | 任意非字母或數(shù)字字符（等同于[^a-zA-Z0-9]） |
| \s | 任意空白字符 |
| \S | 任意非空白字符 |
重復(fù)元字符
| 元字符 | 描述 |
|---|---|
| * | 0個(gè)或多個(gè)匹配 |
| + | 1個(gè)或多個(gè)匹配（等價(jià)于{1,}） |
| ? | 0個(gè)或1個(gè)匹配（等價(jià)于{0,1}） |
| {n} | 具體的匹配次數(shù) |
| {n,} | 不少于指定的匹配次數(shù) |
| {n,m} | 匹配的范圍 |
錨元字符
| 錨 | 描述 |
|---|---|
| ^ | 文本的開頭 |
| $ | 文本的末尾 |
^有兩種用法：在字符集內(nèi)（使用[和]定義）齐莲，使用它來(lái)對(duì)字符集取反；否則星掰，將把它用于指示字符串的開頭家厌。
/* SELECT prod_name, vend_id
FROM products; */
SELECT DISTINCT vend_id -- DISTINCT 關(guān)鍵字用法
FROM products;
SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price DESC;
SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10
ORDER BY prod_name;
SELECT prod_name, prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003
ORDER BY prod_name;
SELECT prod_name, prod_price
FROM products
-- WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10
WHERE vend_id IN (1002,1003) AND prod_price >= 10 -- 可以使用IN
ORDER BY prod_name;
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '_ ton anvil%';
SELECT prod_name
FROM products
WHERE REGEXP_LIKE (prod_name, '.000') -- 正則函數(shù)
ORDER BY prod_name;
SELECT prod_name
FROM products
WHERE REGEXP_LIKE(prod_name, '1000|2000|3000')
ORDER BY prod_name;
SELECT prod_name
FROM products
WHERE REGEXP_LIKE(prod_name,'[1-5] ton')
ORDER BY prod_name;
SELECT vend_name
FROM vendors
WHERE REGEXP_LIKE(vend_name, '\.'); -- 用轉(zhuǎn)義符"\"搜索"."
SELECT prod_name
FROM products
-- WHERE REGEXP_LIKE(prod_name, '\(\d sticks?\)')
WHERE REGEXP_LIKE(prod_name, '\d{4}')
ORDER BY prod_name;
常用文本操作函數(shù)
| 函數(shù) | 描述 |
|---|---|
| Length() | 返回字符串的長(zhǎng)度 |
| Lower() | 把字符串轉(zhuǎn)換成小寫形式 |
| LPad() | 在字符串左邊填充空格 |
| LTrim() | 從字符串左邊修剪掉空白 |
| RPad() | 在字符串右邊填充空格 |
| RTrim() | 從字符串右邊修剪掉空白 |
| Soundex() | 返回字符串的SOUNDEX值 |
| SubString() | 返回字符串內(nèi)的字符 |
| Upper() | 把字符串轉(zhuǎn)換成大寫形式 |
常用的日期和時(shí)間操作函數(shù)
| 函數(shù) | 描述 |
|---|---|
| Add_Month() | 給日期添加月份（也可以減去月份） |
| Extract() | 從日期和時(shí)間中減去年果覆、月、日舰罚、時(shí)、分或秒 |
| Last_Day() | 返回月份的最后一天 |
| Months_Between() | 返回兩個(gè)月份之間的月數(shù) |
| Next_Day() | 返回指定日期后面的那一天 |
| Sysdate() | 返回當(dāng)前日期和時(shí)間 |
| To_Date() | 把字符串轉(zhuǎn)換成日期 |
常用的數(shù)值操作函數(shù)
| 函數(shù) | 描述 |
|---|---|
| Abs() | 返回?cái)?shù)字的絕對(duì)值 |
| Cos() | 返回指定角度的三角余弦值 |
| Exp() | 返回指定數(shù)字的指數(shù)值 |
| Mod() | 返回除法運(yùn)算的余數(shù) |
| Sin() | 返回指定角度的三角正弦值 |
| Sqrt() | 返回指定數(shù)字的平方根 |
| Tan() | 返回指定角度的三角正切值 |
SQL聚合函數(shù)
| 函數(shù) | 描述 |
|---|---|
| AVG() | 返回列的平均值 |
| COUNT() | 返回列中的行數(shù) |
| MAX() | 返回列的最大值 |
| MIN() | 返回列的最小值 |
| SUM() | 返回列的值匯總 |
-
可以使用COUNT()的兩種方式是：
- 使用COUNT(*)統(tǒng)計(jì)表中的行數(shù)，無(wú)論列包含的是數(shù)值還是NULL值；
- 使用COUNT(column)統(tǒng)計(jì)在特定列中具有值（忽略NULL值）的行數(shù)伙菊。
GROUP BY子句必須出現(xiàn)在WHERE子句的后面和ORDER BY子句的前面。
HAVING與WHERE的區(qū)別：
WHERE過(guò)濾發(fā)生在數(shù)據(jù)分組之前，而HAVING過(guò)濾則發(fā)生在數(shù)據(jù)分組之后财剖。這是一個(gè)重要的區(qū)別乳蓄，被WHERE子句刪除的行不會(huì)包括在分組中美侦。這可能會(huì)改變計(jì)算值捧弃，基于HAVING子句中使用的那些值嘴办，它反過(guò)來(lái)又可能影響哪些分組將會(huì)被過(guò)濾。
ORDER BY與GROUP BY的比較
| ORDER BY | GROUP BY |
|---|---|
| 對(duì)生成的輸出進(jìn)行排序 | 對(duì)行進(jìn)行分組，不過(guò)輸出可能沒(méi)有采用分組順序 |
| 可能使用任意列（甚至包括沒(méi)有選擇的列） | 只可能使用所選的列或表達(dá)式列，并且一定會(huì)使用所有選擇的列表達(dá)式 |
| 從來(lái)都不是必需的 | 如果結(jié)合使用列（或表達(dá)式）與聚合函數(shù)，則是必需的 |
SELECT子句和它們的順序
| 子句 | 描述 | 是否必需 |
|---|---|---|
| SELECT | 要返回的列或表達(dá)式 | 是 |
| FROM | 要從中檢索數(shù)據(jù)的表 | 是（Oracle） |
| WHERE | 行級(jí)過(guò)濾 | 否 |
| GROUP BY | 分組規(guī)范 | 僅當(dāng)按分組計(jì)算聚合值時(shí)是必需的 |
| HAVING | 分組級(jí)過(guò)濾 | 否 |
| ORDER BY | 輸出的排列順序 | 否 |
查詢示例
SELECT RTRIM(vend_name) || ',(' || RTRIM（vend_country） || ')'
AS vend_title
FROM vendors
ORDER BY vend_name;
SELECT prod_id, quantity, item_price,
quantity * item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005;
SELECT cust_id, order_num
FROM orders
WHERE order_date = TO_DATE('2015-02-01', 'yyyy-mm-dd');
SELECT cust_id, order_num
FROM orders
WHERE order_date>= TO_DATE('2015-02-01', 'yyyy-mm-dd')
AND order_date< TO_DATE('2015-02-02', 'yyyy-mm-dd');
SELECT cust_id, order_num
FROM orders
-- 檢索order_date在2015年和2月的所有行
WHERE Extract(Year FROM order_date) = 2015
AND Extract(Month FROM order_date) = 2;
SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;
SELECT COUNT(*) AS num_cust
FROM customers;
SELECT COUNT(cust_email) AS num_cust
FROM customers;
SELECT SUM(quantity) AS items_ordered
FROM orderitems
WHERE order_num = 20005;
SELECT SUM(item_price*quantity) AS total_price
FROM orderitems
WHERE order_num = 20005;
SELECT COUNT(*) AS num_items,
MIN(prod_price) AS price_min,
MAX(prod_price) AS price_max,
AVG(prod_price) AS price_avg
FROM products;
SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id; -- GROUP BY 聚合分組
SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2; -- HAVING用于過(guò)濾分組 等同于過(guò)濾行的 WHERE
/* 列出具有2件或更多產(chǎn)品并且價(jià)格在10以上（含10）的所有供應(yīng)商 */
SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2;
/* 檢索總價(jià)在50以上（含50）的所有訂單的訂單號(hào)和訂單總價(jià),并按訂單總價(jià)對(duì)輸出進(jìn)行排序 */
SELECT order_num, SUM(quantity * item_price) AS order_total
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity * item_price) >= 50
ORDER BY order_total;
/* 子查詢：查詢訂購(gòu)商品TNT2的所有顧客的信息鞋囊，下面有連接表方法查詢 */
SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2'));
SELECT cust_name,
cust_state,
(SELECT COUNT(*)
FROM orders
WHERE orders.cust_id = customers.cust_id) AS orders -- orders為計(jì)算字段
FROM customers
ORDER BY cust_name;
/* 表的連接瓜喇，必須使用完全限定的列名（用點(diǎn)號(hào)把表和列分隔開） */
SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;
-- 等同于下面的內(nèi)連接，WHERE更簡(jiǎn)化黍檩，INNER更清晰
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;
-- 顯示了訂單號(hào)20005中的商品
SELECT prod_name, vend_name, prod_price, quantity
FROM orderitems, products, vendors
WHERE products.vend_id = vendors.vend_id
AND orderitems.prod_id = products.prod_id
AND order_num = 20005;
/* 使用連接表：查詢訂購(gòu)商品TNT2的所有顧客的信息，上面有子查詢方法 */
-- WHERE連接表
SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num
AND prod_id = 'TNT2';
-- FROM子句始锚，INNER JOIN ON實(shí)現(xiàn)
SELECT cust_name, cust_contact
FROM customers
INNER JOIN orders ON customers.cust_id = orders.cust_id
INNER JOIN orderitems ON orderitems.order_num = orders.order_num
WHERE prod_id = 'TNT2';
表別名和列別名
列別名使用 AS:
SELECT RTrim(vend_name) || ', (' || RTrim(vend_country) || ')'
AS vend_title
FROM vendors
ORDER BY vend_name;
表別名如下：
SELECT cust_name, cust_contact
FROM customers c, orders o, orderitems oi
WHERE c.cust_id = o.cust_id
AND oi.order_num = o.order_num
AND prod_id = 'TNT2';
注意：只在查詢執(zhí)行期間使用表別名刽酱。與列別名不同，表別名永遠(yuǎn)不會(huì)返回給客戶瞧捌。
/* 查詢商品ID為DTNTR的商品的同一個(gè)供應(yīng)商制造的所有產(chǎn)品 */
-- 子查詢方法
SELECT prod_id, prod_name
FROM products
WHERE vend_id = (SELECT vend_id
FROM products
WHERE prod_id = 'DTNTR')
ORDER BY prod_id;
/* 自連接 */
-- 連接表 WHERE
SELECT p1.prod_id, p1.prod_name
FROM products p1, products p2
WHERE p1.vend_id = p2.vend_id
AND p2.prod_id = 'DTNTR'
ORDER BY prod_id;
-- 連接表 INNER JOIN ON
SELECT p1.prod_id, p1.prod_name
FROM products p1
INNER JOIN products p2 ON p1.vend_id = p2.vend_id
WHERE p2.prod_id = 'DTNTR'
ORDER BY prod_id;
/* 自然連接 */
SELECT c.*, o.order_num, o.order_date, -- c.*為customers表中所有列
oi.prod_id, oi.quantity, OI.item_price
FROM customers c, orders o, orderitems oi
WHERE c.cust_id = o.cust_id
AND oi.order_num = o.order_num
AND prod_id = 'FB';
SELECT c.*, o.order_num, o.order_date,
oi.prod_id, oi.quantity, OI.item_price
FROM customers c
INNER JOIN orders o ON c.cust_id = o.cust_id
INNER JOIN orderitems oi ON oi.order_num = o.order_num
WHERE prod_id = 'FB';
/* 外連接：檢索所有顧客及其訂單 */
SELECT c.cust_id, o.order_num
FROM customers c
INNER JOIN orders o ON c.cust_id = o.cust_id;
-- 檢索所有顧客的列表，包括那些沒(méi)有下訂單的顧客
SELECT customers.cust_id, orders.order_num
FROM customers
LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;
與將兩個(gè)表中的行相關(guān)聯(lián)的內(nèi)連接不同柱告，外連接還包括沒(méi)有相關(guān)行的行。
當(dāng)使用OUTER JOIN語(yǔ)法時(shí)墩新，必須使用RIGHT或LEFT關(guān)鍵字指定要包括所有行的表（RIGHT用于OUTER JOIN右邊的表，LEFT則用于左邊的表）缺狠。前面的示例在FROM子句中使用LEFT OUTER JOIN從左邊的表（customers表）中選擇所有的行。要從右邊的表中選擇所有的行，可以使用RIGHT OUTER JOIN。
外連接類型:
有兩種基本的外連接形式：左外連接和右外連接杈女，它們之間的唯一區(qū)別是相關(guān)聯(lián)的表的順序。換句話說(shuō)栓辜，可以把左外連接轉(zhuǎn)變成右外連接贩毕，只需在FROM或WHERE子句中顛倒表的順序即可。因此改淑，可以互換地使用兩類外連接，而要使用哪種外連接則取決于方便性辩棒。
/* 檢查所有顧客的列表以及每位顧客下的訂單數(shù)量 */
-- 內(nèi)連接
SELECT customers.cust_name,
COUNT(orders.order_num) AS num_ord
FROM customers
INNER JOIN orders ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_name;
-- 外連接
SELECT customers.cust_name,
COUNT(orders.order_num) AS num_ord
FROM customers
LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_name;
/* 組合查詢 UNION */
/* 價(jià)格在5以下的所有產(chǎn)品，包括由供應(yīng)商1001和1002制造的所有產(chǎn)品,不管價(jià)格 */
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001,1002)
ORDER BY vend_id, prod_price;
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
OR vend_id IN (1001,1002);
使用UNION ALL深纲，Oracle將不會(huì)消除重復(fù)的行哈垢。
INSERT 插入的用法
/* INSERT語(yǔ)句用法示例 */
-- 下面是依賴列順序的志衍，強(qiáng)烈不推薦
INSERT INTO Customers
VALUES(10006,
'Pep E. LaPew',
'100 Main Street',
'Los Angeles',
'CA',
'90046',
'USA',
NULL,
NULL);
-- 推薦寫法
INSERT INTO customers(cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
VALUES(10006,
'Pep E. LaPew',
'100 Main Street',
'Los Angeles',
'CA',
'90046',
'USA');
-- 使用INSERT SELECT把custnew中的所有數(shù)據(jù)導(dǎo)入到customers中
INSERT INTO custnew(cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
SELECT cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
FROM custnew;
UPDATE更新數(shù)據(jù)
-- 更新顧客10005的電子郵件
UPDATE customers
SET cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;
-- 更新多列數(shù)據(jù)
UPDATE customers
SET cust_name = 'The Fudds',
cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;
-- 更新為空值（刪除列）
UPDATE customers
SET cust_email = NULL
WHERE cust_id = 10005;
UPDATE語(yǔ)句需要結(jié)束于一個(gè)WHERE子句，它告訴Oracle要更新哪一行,否則更新所有行。
DELETE刪除數(shù)據(jù)的使用
-- 從customers表中刪除單獨(dú)一行
DELETE FROM customers
WHERE cust_id = 10006;
DELETE不接受列名或者通配符，它將刪除整行，而不是刪除列晤愧。要?jiǎng)h除特定的列帅涂，可以使用UPDATE語(yǔ)句。
永遠(yuǎn)不要執(zhí)行不帶有WHERE子句的UPDATE或DELETE，除非確實(shí)打算更新和刪除每一行。
創(chuàng)建表
---------------------------------------------
-- Create customers table
---------------------------------------------
CREATE TABLE customers
(
cust_id int NOT NULL ,
cust_name char(50) NOT NULL ,
cust_address char(50) NULL ,
cust_city char(50) NULL ,
cust_state char(5) NULL ,
cust_zip char(10) NULL ,
cust_country char(50) NULL ,
cust_contact char(50) NULL ,
cust_email char(255) NULL
);
---------------------------------------------
-- Create orderitems table
---------------------------------------------
CREATE TABLE orderitems
(
order_num int NOT NULL ,
order_item int NOT NULL ,
prod_id char(10) NOT NULL ,
quantity int DEFAULT 1 NOT NULL , -- 設(shè)置默認(rèn)值
item_price decimal(8,2) NOT NULL
);
ALTER TABLE更新表定義
-- 向表中添加一列
ALTER TABLE vendors
ADD vend_phone CHAR(20);
-- 刪除添加列
ALTER TABLE vendors
DROP COLUMN vend_phone;
-- 定義主鍵
----------------------
-- Define primary keys
----------------------
ALTER TABLE customers ADD CONSTRAINT pk_customers
PRIMARY KEY (cust_id);
ALTER TABLE orderitems ADD CONSTRAINT pk_orderitems
PRIMARY KEY (order_num, order_item);
ALTER TABLE orders ADD CONSTRAINT pk_orders
PRIMARY KEY (order_num);
ALTER TABLE products ADD CONSTRAINT pk_products
PRIMARY KEY (prod_id);
ALTER TABLE vendors ADD CONSTRAINT pk_vendors
PRIMARY KEY (vend_id);
ALTER TABLE productnotes ADD CONSTRAINT pk_productnotes
PRIMARY KEY (note_id);
-- 定義外鍵
---------------------------------------------
-- Define foreign keys
---------------------------------------------
ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num)
REFERENCES orders (order_num);
ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_products
FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id)
REFERENCES customers (cust_id);
ALTER TABLE products
ADD CONSTRAINT fk_products_vendors
FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);
ALTER TABLE productnotes
ADD CONSTRAINT fk_productnotes_products
FOREIGN KEY (prod_id) REFERENCES products (prod_id);
刪除表
-- 永久刪除整個(gè)表customers2
DROP TABLE customers2;
重命名表
ALTER TABLE customers2 RENAME TO customers;
