MySQL

#database

概述

引擎（Engine）：

InnoDB：支持事务，但不支持全文本搜索
MyISAM：支持全文本搜索，但不支持事务
Memory：功能同MyISAM，但存储在内存

查询

NULL：表示没有值，与空字符串不同；
Search Pattern：搜索模式，即搜索条件；

基本

SELECT prod_name FROM products;
SELECT prod_id, prod_name, prod_price FROM products;

SELECT * FROM products;

SELECT DISTINCT vend_id FROM products;    # 去掉重复行

SELECT prod_name FROM products LIMIT 5;    # 返回最多5行
SELECT prod_name FROM products LIMIT 5,5;    # 从row5开始计算最多5行
SELECT prod_name FROM products LIMIT 5 OFFSET 5;    # 同上（MySQL5）

SELECT products.prod_name FROM crashcourse.products;    # 限定列名和表名

排序

SELECT prod_name FROM products ORDER BY prod_name;
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name;    # 先用price排序，price相同的再用name排序

SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC;    # 降序（默认升序，ASC）

过滤

WHERE过滤行，HAVING过滤分组

SELECT vend_id, prod_name FROM products WHERE vend_id != 1003;    # where子句操作符

SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;    # 5~10

SELECT prod_name FROM products WHERE prod_price IS NULL;

SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id=1003 AND prod_price<=10;
SELECT prod_name, prod_price FROM products WHERE vend_id=1002 OR vend_id=1003;
SELECT prod_name, prod_price FROM products WHERE (vend_id=1002 OR vend_id=1003) AND prod_price>=10;    # AND优先级更高

SELECT prod_name, prod_price FROM products WHERE vend_id IN (1002,1003) ORDER BY prod_name;    # vend_id等于1002和1003
SELECT prod_name, prod_price FROM products WHERE vend_id NOT IN (1002,1003) ORDER BY prod_name;

SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%';    # %匹配任意多个字符（包括0个）（LIKE必须用通配符）
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil'; # _匹配1个字符

SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000';

函数

Calculated Fields：计算字段，检索出经转换、计算或格式化过的数据
Aggregate Function：聚集函数，获取表的汇总信息

# 计算字段（field）

SELECT Concat(vend_name, ' (', vend_country, ')') FROM vendors ORDER BY vend_name;    # 拼接：ACME (USA)

SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM orderitems;    # 计算字段、别名

# 函数

SELECT vend_name, Upper(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;

SELECT cust_name, cust_contact FROM customers WHERE Soundex(cust_contact)=Soundex('Y Lie');    # 匹配发音类似：Y Lee

SELECT cust_id, order_num FROM orders WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';

# 聚集函数（aggregate function）

SELECT AVG(prod_price) AS avg_price FROM products;  # 计算平均值（忽略列值为NULL的行）

SELECT COUNT(*) AS num_cust FROM customers;    # 统计总行数（忽略列值为NULL的行）

SELECT MAX(prod_price) AS max_price FROM products;

SELECT SUM(item_price*quantity) AS total_price FROM  orderitems WHERE order_num=20005;    # 统计总和

分组（Group）

使用GROUP BY和HAVING进行分组及过滤，用于对内容划分后再汇总

SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;    # 对每个vend_id分别统计

SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price>=10 GROUP BY vend_id HAVING COUNT(*)>=2;    # where过滤行，having过滤分组

SELECT子句顺序

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

子查询（Subquery）

嵌套在查询中的查询

Correlated Subquery：相关子查询，涉及外部查询的子查询

# 利用子查询过滤
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
FROM customers
ORDER BY cust_name;

联结（Join）

关联表查询

Foreign Key：外键，表中的一列包含另一个表的主键值
笛卡尔积：没有联结条件的表关系返回结果为笛卡尔积，即结果行数为两张表行数的乘积
Inner Join：内部联结，默认联结，仅返回关联行
Outer Join：外部联结，返回结果包括无关联行
自联结：联结同一张表（两个实例），可替代基于同一张表的子查询；

SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id=products.vend_id;    # 等值联结（equijoin）
SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products WHERE vendors.vend_id=products.vend_id;    # 效果同上，内部联结（默认）

# 自联结
SELECT prod_id, prod_name FROM products WHERE vend_id=(SELECT vend_id FROM products WHERE prod_id='DTNTR');    # 子查询
SELECT p1.prod_id, p1.prod_name FROM products AS p1, products AS p2 WHERE p1.vend_id=p2.vend_id AND p2.prod_id='DTNTR'    # 等价的自联结

# 外部联结
SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id=orders.cust_id;
SELECT customers.cust_id, orders.order_num FROM customers RIGHT OUTER JOIN orders ON customers.cust_id=orders.cust_id;

组合查询（Union）

合并多个查询结果

SELECT ...
UNION     # UNION ALL（不去除重复行）
SELECT ...
ORDER BY ...    # 对最终结果排序

全文本搜索，仅MyISAM引擎支持，创建表时用FULLTEXT(column)对指定列进行索引后才可以进行全文本搜索

Query Expansion：查询扩展，可放宽搜索范围；进行两次搜索，在第一次搜索结果上找到“可能有用的”单词，加入搜索条件后再搜索一遍；
Boolean Mode：布尔搜索，没有FULLTEXT索引也能使用

CREATE TABLE productnotes (
	note_id int NOT NULL AUTO_INCREMENT,
	prod_id char(10) NOT NULL,
	note_date datetime NOT NULL,
	note_test text NULL,
	PRIMARY KEY(note_id),
	FULLTEXT(note_text)        # 对指定列进行索引
) ENGINE=MyISAM;               # 支持全文搜索的引擎

SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit');
SELECT note_text FROM productnotes WHERE note_text LIKE '%rabbit%';    # 同上，但是全文本搜索会优先返回匹配程度高的行
SELECT note_text, Match(note_text) Against('rabbit') AS rank FROM productnotes;    # rank为匹配程度（等级值）

# 查询扩展
SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit' WITH QUERY EXPANSION);

# 布尔文本搜索
SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);    # 排除包含rope*的行
SELECT note_text FROM productnotes WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE);    # 同时包含两个
SELECT note_text FROM productnotes WHERE Match(note_text) Against('"rabbit bait"' IN BOOLEAN MODE);    # 包含"rabbit bait"

修改记录

INSERT INTO customers VALUES ('aaa', 'bbb', 'ccc');
INSERT INTO customers(cust_name, cust_city, cust_email) VALUES ('aaa','bbb','ccc');

INSERT INTO customers VALUES (...),(...);    # 插入多行

UPDATE customers SET cust_email='xxx' WHERE cust_id=10005;
UPDATE customers SET cust_name='aaa', cust_email='xxx' WHERE cust_id=10005;
UPDATE IGNORE customers SET cust_email='xxx' WHERE cust_id=10005;    # 即使某行发生错误也继续执行（不Ignore，其中一行出错则全部取消）

UPDATE customers SET cust_email='NULL';    # NULL不同于空字符串

DELETE FROM customers WHERE cust_id=10006;

表（Table）

ALERT TABLE vendors ADD vend_phone CHAR(20);
ALERT TABLE vendors DROP COLUMN vend_phone;

ALERT TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);    # 定义外键
DROP TABLE customers;

RENAME TABLE customer2 TO customers;

视图（View）

类似宏的概念，就是一个SQL查询的别名

CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email FROM customers WHERE cust_email IS NOT NULL;

SHOW CREATE VIEW viewname;    # 查看view语句
DROP VIEW viewname;
CREATE OR REPLACE VIEW ...

存储过程（Stored Procedure）

类似函数的概念，可传入参数并返回变量

CREATE PROCEDURE productpricing()
BEGIN
    SELECT Avg(prod_price) AS priceaverage FROM products;
END;

CALL productpricing();

# 使用参数

CREATE PROCEDURE ordertotal(
    IN onumber INT,
    OUT ototal DECIMAL(8,2)
)
BEGIN
    SELECT Sum(item_price*quantity) FROM orderitems WHERE order_item=onumber
    INTO otal;
END;

CALL ordertotal(20005, @total);	    # 不返回数据，仅返回变量供后面使用

SELECT @total;

 

DROP PROCEDURE productpricing;

SHOW CREATE PROCEDURE ordertotal;
SHOW PROCEDURE STATUS;

游标（Cursor）

只能用于PROCEDURE，可对搜索结果逐行FETCH后进行处理

CREATE PROCEDURE processorders()
BEGIN
    DECLARE o INT;

    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
    
    OPEN ordernumbers;

    FETCH ordernumbers INTO o;

    CLOSE ordernumbers;
END;

触发器（Trigger）

当INSERT,UPDATE,DELETE发生时，可自动执行SQL语句

可在事件AFTER或BEFORE触发，每个表每个事件只能有一个Trigger，因此每个表最多只有6个Trigger

CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added';

DROP TRIGGER newproduct;

CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num;

CREATE TRIGGER deleteorder BEFORE DELETE ON orders FOR EACH ROW
BEGIN
    INSERT INTO archive_orders(order_num, order_date, cust_id) VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END;

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors FOR EACH ROW SET NEW.vend_state=Upper(NEW.vend_state);

事务（Transaction）

为维护数据完整性，成批操作要么完全执行，要么完全不执行

transaction：一组SQL语句
rollback：撤销执行
commit：将未存储的SQL语句结果写入数据库
savepoint：保留点，作用类似Label，可roolback到指定savepoint，否则roolback到最开始
autocommit：是否自动提交变更——无论有没有commit；针对每个连接，而非数据库

START TRANSACTION;
SAVEPOINT delete1;
...
ROLLBACK TO delete1;
...
COMMIT

SET autocommit=0;

字符集

collation：校对，即字符排序规则；比如区分大小写、重音、假名、全半角等等

_ci大小写不敏感，_cs大小写敏感，_bin二进制

设置位置：Client, Connection, Database, Results, Server, System

SHOW CHARACTER SET;
SHOW COLLATION;
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';

CREATE TABLE mytable(
    column1 INT,
    column2 VARCHAR(10),
    column3 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci
) DEFAULT CHARACTER SET hebrew
  COLLATE hebrew_general_ci;
  
SELECT * FROM customers ORDER BY lastname, firstname COLLATE latin1_general_cs;

ALERT TABLE db_allot CONVERT TO CHARACTER SET latin1 COLLATE latin1_bin;

账户

user@host：user@%表示不管主机名
database.table：*.*表示所有

CREATE USER ben IDENTIFIED BY 'p@ssw0rd';

RENAME USER ben TO forta;

DROP USER forta;

SHOW GRANTS FOR forta;    # 显示“USAGE”表示没有任何权限

GRANT SELECT ON crashcourse.* TO forta;    # db.table

REVOKE SELECT ON crashcourse.* FROM forta;

SET PASSWORD FOR forta=Password('p@ssw0rd');
SET PASSWORD=Password('p@ssw0rd');    # 当前用户

维护

# 导出
mysqldump -u USER -p DB > FILE    # 导出整个数据库
mysqldump -u USER -p DB TABLE > FILE    # 导出表
mysqlhotcopy --user=USER --password=PASSWORD -q DB DIR

# 导入
source FILE;    # 控制台导入
mysql -u USER -p DB < FILE

FLUSH TABLES;    # 将缓存写入磁盘

# 检查
ANALYZE TABLE orders;
CHECK TABLE orders, orderitems;

参考

  • Ben Forta. 《MySQL必知必会》
← Previous Next →