寫在前面

最近在學習數(shù)據(jù)庫，將學習概要記錄于此栖袋，學習的知識主要來源于 <<MySQL必知必會>>塘幅，下文中涉及相關(guān)的數(shù)據(jù)表均來源于書中提供的樣例表create.sql，populate.sql

• 創(chuàng)建數(shù)據(jù)庫

create database study;

• 查看數(shù)據(jù)庫

show databases;

• 使用數(shù)據(jù)庫

use study;

• 導入數(shù)據(jù)表

source create.sql;
source populate.sql;

• 查看表結(jié)構(gòu)信息

DESC tablename;

• 檢索數(shù)據(jù)

- 檢索單列
SELECT cust_name FROM customers;

- 檢索多列
SELECT cust_id,cust_name, cust_address FROM customers;

- 檢索所有列
SELECT * FROM customers;

- 檢索不同的行
SELECT DISTINCT cust_country FROM customers;

說明:
DISTINCT關(guān)鍵字是作用于所有的列，而不僅僅是前置它的列钝尸，例如SELECT DISTINCT cust_country, cust_id FROM customers; 意思是customers表中cust_country與cust_id都不同的行有哪些

- 限制結(jié)果
-- 指示返回不多于2行
SELECT cust_name FROM customers LIMIT 2;
-- 指示返回行數(shù)從行1開始的2行結(jié)果
SELECT cust_name FROM customers LIMIT 1,2;

說明:
行的下標起始從0開始珍促，行0是第一行，行1是第二行

- 限定表名/數(shù)據(jù)庫名檢索
SELECT customers.cust_name FROM customers;
SELECT customers.cust_name FROM study.customers;

- 排序數(shù)據(jù)檢索
SELECT cust_id,cust_name FROM customers ORDER BY cust_id;

- 多列排序檢索
SELECT cust_id, cust_name FROM customers ORDER BY cust_name, cust_id;

- 指定方向檢索
SELECT cust_id, cust_name FROM customers ORDER BY cust_id DESC;

說明:
默認ASC升序

- 過濾數(shù)據(jù)檢索
-- where子句操作符號
SELECT prod_name, prod_price FROM products where prod_price = 2.5;
SELECT prod_name, prod_price FROM products where prod_price BETWEEN 2.5 AND 10;
-- 空值檢索
SELECT prod_name, prod_price FROM products where prod_price IS NULL;

MySQL子句操作符

- where子句組合過濾
-- AND且操作
SELECT prod_name, prod_price FROM products where prod_price >2.5 and prod_id < 5
-- OR或操作
SELECT vend_id,prod_name FROM products WHERE vend_id = 1001 OR vend_id = 1002;

說明:
AND 和 OR 操作符組合建立一個WHERE子句,SQL在處理OR操作符前锦积，優(yōu)先處理AND操作符
SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 and prod_price >= 10;等同于
SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR (vend_id = 1003 and prod_price >= 10);

- IN操作符
SELECT vend_id,prod_name,prod_price FROM products WHERE vend_id IN (1002,1003);
-- 等同于：
SELECT vend_id,prod_name,prod_price FROM products WHERE vend_id = 1002 or vend_id = 1003;

- NOT操作符
SELECT vend_id,prod_name,prod_price FROM products WHERE vend_id NOT IN(1002,1003);

說明:
MySQL數(shù)據(jù)NOT可支持對IN, BETWEEN和EXISTS子句取反

- 通配符過濾
-- LIKE 操作符
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '%anvil%';
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil';

說明:
"%"匹配0/1/N多個字符背蟆，"_"匹配單個字符

- 正則檢索
-- 基本字符
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '1000';

-- '或'匹配
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '1000|2000';

-- 幾個字符之一匹配
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '[123] ton';
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '1|2|3 ton';
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '[^123] ton';

-- 范圍匹配
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '[1-5] ton';

--特殊字符匹配（特殊字符需要前置'\\'進行引導）
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '\\.';

-- 匹配字符類
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '[:alnum:]';

匹配字符類

- 匹配多個實例
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '.anvil';
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}';
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP "a*il";

正則表達式重復元字符

說明:
*在通配符和正則表達式中有其不一樣的地方敛纲，在通配符中*可以匹配任意的0個或多個字符载慈，而在正則表達式中他是重復之前的一個或者多個字符，不能獨立使用的寡具。比如通配符可以用*來匹配任意字符童叠，而正則表達式不行，他只匹配任意長度的前面的字符,通過使用.*匹配任意字符杜秸。

- 定位符
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP "^[0-9].*";

定位元字符

- 計算別名
SELECT prod_id, quantity,item_price, quantity * item_price AS expanded_price FROM orderitems;

• 函數(shù)

- 拼接函數(shù)
SELECT CONCAT(quantity,' : ', item_price) AS result FROM orderitems;
- 文本處理函數(shù)
SELECT vend_name, UPPER(vend_name) FROM vendors;
SELECT vend_name, LEFT(vend_name,1) FROM vendors;
SELECT vend_name, LENGTH(vend_name) FROM vendors;
SELECT vend_name, LOCATE('LT',vend_name) FROM vendors;
SELECT vend_name, LTRIM(vend_name)FROM vendors;
SELECT vend_name, SUBSTRING(vend_name FROM 1 FOR 2)FROM vendors;
SELECT vend_name, SUBSTRING(vend_name,1,2)FROM vendors;substring

常用處理函數(shù)

例： SELECT order_num FROM orders where DATE(order_date) = '2005-09-01';

說明:
數(shù)值處理函數(shù)
ABS()/COS()/EXP()/MOD()/PI()/RAND()/SIN()/SQRT()/TAN()
SELECT ABS(order_num) from orders ;
聚集函數(shù)
AVG()/COUNT()/MAX()/MIN()/SUM()

• 分組

- 創(chuàng)建分組
SELECT vend_id,COUNT(*) FROM products GROUP BY vend_id;
- 過濾分組
SELECT vend_id,COUNT(*) FROM products GROUP BY vend_id HAVING COUNT(*) >= 2;
- 分組和排序
SELECT vend_id,COUNT(*) AS num FROM products GROUP BY vend_id ORDER BY num;

說明:
1.SELECT 子句書寫順序總結(jié)(SELECT->FROM->WHERE->GROUP BY->HAVING->ORDER BY->LIMIT)
2.執(zhí)行順序：FROM->WHERE->GROUP BY->聚集函數(shù)計算->HAVING->SELECT->ORDER BY

• 子查詢

- 子查詢
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 od FROM customers ORDER BY cust_name;

• 聯(lián)結(jié)表

- 創(chuàng)建聯(lián)結(jié)表
SELECT vend_name, prod_name FROM vendors, products WHERE vendors.vend_id = products.vend_id;

- 內(nèi)部聯(lián)結(jié)
SELECT vend_name, prod_name FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;

- 自聯(lián)結(jié)
SELECT p1.prod_id, p1.prod_name FROM products AS p1, products AS p2 WHERE p1.vend_id = p2.vend_id;

- 外部聯(lián)結(jié)
SELECT customers.cust_id, orders.order_num FROM customers RIGHT OUTER JOIN orders ON orders.cust_id = customers.cust_id;
SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON orders.cust_id = customers.cust_id;

說明:
與內(nèi)部聯(lián)結(jié)關(guān)聯(lián)兩個表中的行不同的是膨蛮，外部聯(lián)結(jié)還包括沒有關(guān)聯(lián)行的行敞葛。左外部聯(lián)結(jié)、右外部聯(lián)結(jié)之間的差別是所關(guān)聯(lián)的表的順序不一樣

• 組合查詢

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);

說明:
UNION 從查詢結(jié)果集中自動去除重復的行氨肌，如果想返回所有匹配行，可使用 UNION ALL.

• 插入數(shù)據(jù)

-- 插入完整的行
INSERT INTO
customers
(
    cust_name,
    cust_city,
    cust_state,
    cust_zip,
    cust_country
)
VALUES('Jack','100 street','Los Angeles','CA','9004','USA');

--插入多行
INSERT INTO
customers
(
    cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country
)
VALUES('Jack','100 street','Los Angeles','CA','9004','USA')
VALUES('mao','100 street','Los Angeles','CA','9004','USA');

--插入檢索出的數(shù)據(jù)
INSERT INTO
customers
(
    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 customers;

• 更新與刪除數(shù)據(jù)

UPDATE customers SET cust_email = 'elmer@fudd.com' WHERE cust_id = 10005;
UPDATE customers SET cust_email = 'elmer@fudd.com',cust_name = 'The Fudds' WHERE cust_id = 10005;
UPDATE customers SET cust_email = NULL WHERE cust_id = 10005;
DELETE FROM customers WHERE cust_id = 10006;
TRUNCATE TABLE customers;

• 創(chuàng)建表

CREATE TABLE customers
(
  cust_id      int       NOT NULL AUTO_INCREMENT,
  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 ,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

說明:
1.NULL 不是空串, NULL 值是沒有值贩虾，它不是空串捧灰， ' '空串是一個有效的值
2.AUTO_INCREMENT,本列每增加一行時自動增量
3.PRIMARY KEY 指定表的主鍵，用于唯一標識表中每個行的列蔽午，主鍵不允許NULL值得列
4.ENGINE：InnoDB(可靠的事務(wù)處理引擎)/MyISAM(性能高抽莱，但不支持事務(wù)處理)/MEMORY(數(shù)據(jù)存儲在內(nèi)存，不在磁盤中，速度快斟叼，一般適用于臨時表)

• 更新表

ALTER TABLE vendors ADD vend_phone CHAR(20);

• 刪除表

DROP TABLE customers;

• 重命名表

RENAME TABLE customers TO customers2;
RENAME TABLE backup_customers TO customers, backup_vendors TO vendors;

• 視圖

創(chuàng)建視圖: CREATE VIEW
查看視圖：SHOW CREATE VIEW viewname;
刪除視圖：DROP VIEW viewname;

具體事例：
- 創(chuàng)建視圖
CREATE VIEW productcustomers AS SELECT cust_name, cust_contact, prod_id
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num;

- 查詢視圖
SELECT cust_name, cust_contact FROM productcustomers WHERE prod_id = 'TNT2';