MySQL 必知必会

主键：一列（或一组列），其值能够唯一区分表中每个行

任意两行都不具有相同的主键值
每一行都必须具有一个主键（不为 NULL）

习惯：

不更新主键列中的值
不重用主键列中的值
不在主键列中使用可能会更改的值

1SHOW DATABASES;
2SHOW TABLES;
3SHOW COLUMNS FROM klass; -- 返回当前选择的数据库内可用表的列表，可用 DESCRIBE klass; 代替
4SHOW STATUS; -- 用于显示广泛的服务器状态信息
5SHOW CREATE DATABASE; -- 显示创建特定数据库
6SHOW CREATE TABLE; -- 显示创建特定表的MySQL语句
7SHOW GRANTS; -- 用来显示授予用户（所有用户或特定用户）的安全权限
8SHOW ERRORS; -- 错误信息
9SHOW WARNINGS; -- 警告消息
10
11HELP SHOW;

1SELECT staff_id FROM staff;
2SELECT staff_id, wage FROM staff;
3SELECT * FROM staff;
4SELECT DISTINCT staff_id FROM staff; -- 去重
5SELECT * FROM staff LIMIT 5; -- 返回不超过 5 行
6SELECT * FROM staff LIMIT 5, 5; -- 下一个 5 行，LIMIT 5 OFFSET 5
7SELECT staff.staff_id FROM klass.staff;
8SELECT city FROM warehouses ORDER BY city; -- city 的字母顺序
9SELECT * FROM warehouses ORDER BY size, city; -- 先按 size 排序，再按 city 排序
10SELECT * FROM warehouses ORDER BY size DESC, city ASC; -- DESC 降序
11SELECT * FROM warehosues WHERE size <> 570;
12SELECT * FROM warehosues WHERE size BETWEEN 500 AND 600;
13SELECT * FROM order_form WHERE vendor_id IS NOT NULL; -- IS NULL
14SELECT * FROM staff WHERE warehouse_id = 'WH2' AND wage > 1230; -- AND 与 OR 中，AND 优先级更高，是用括号避免错误
15SELECT * FROM staff WHERE warehouse_id = 'WH2' OR warehouse_id = 'WH1'; -- 与使用 IN 相同
16SELECT * FROM staff WHERE warehouse_id IN ('WH2', 'WH1'); -- IN 可配合 SELECT 子句
17SELECT * FROM staff WHERE warehouse_id NOT IN ('WH2', 'WH1');
18SELECT * FROM vendor WHERE vendor_name LIKE '_通%公司';-- “_”匹配一个，“%”匹配零到多个，通配符有性能问题，必要时使用
19SELECT 'hello' REGEXP '[0-9]'; -- 可使用正则，转译用 “\\”
20SELECT Concat(warehouse_id, ': ', Trim(city)) AS warehouse_title FROM warehouses; -- 拼接字段，大多数 SQL 使用 + 或 ||，MySQL 使用 Concat，AS 用作别名
21SELECT prod_id, quantity, item_price, item_price * quantity AS expanded_price FROM orderitems WHERE order_num = 20005; -- 算数计算字段
22
23-- 函数不具有可移植性，各个 SQL 的提供不一样
24-- 文本处理函数、时间处理函数、数值处理函数
25SELECT * FROM warehouses WHERE Lower(warehouse_id) = 'wh1';
26SELECT * FROM order_form WHERE Month(`date`) = '06';
27SELECT * FROM warehouses WHERE Mod(size, 100) = 0;
28-- 聚集函数
29SELECT AVG(wage) FROM staff;
30SELECT AVG(DISTINCT wage) FROM staff; -- DISTINCT 聚集不同值
31
32-- 分组
33-- WHERE 之后 ORDER BY 之前
34-- WHERE 过滤行 HAVING 过滤分组
35-- WITH ROLLUP 获得每个分组以及每个分组汇总级别（针对每个分组）的值
36SELECT vend_id, COUNT(*) AS prod_num FROM products WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >= 2;
37
38-- 子查询
39SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name;

外键：外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系

信息不重复，从而不浪费时间和空间
如果信息变动，可以只更新表中的单个记录，相关表中的数据不用改动
由于数据无重复，显然数据是一致的，这使得处理数据更简单

1-- 联结性能消耗大
2SELECT * FROM staff, warehouses; -- 笛卡尔积
3SELECT * FROM staff, warehouses WHERE staff.warehouse_id = warehouses.warehouse_id;
4SELECT * FROM staff INNER JOIN warehouses ON staff.warehouse_id = warehouses.warehouse_id; -- ANSI SQL 首选，但性能不如上一条好（忽略引擎优化）

假如你发现某物品(其ID为DTNTR)存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。下面是解决此问题的一种方法:

1SELECT prod_id, prod_name
2FROM products
3WHERE vend_id IN (SELECT vend_id
4                  FROM products
5                  WHERE prod_id = 'DTNTR');
6
7SELECT p1.prod_id, p1.prod_name
8FROM products AS p1, products AS p2
9WHERE p1.vend_id = p2.vend_id
10  AND p2.prod_id = 'DTNTR'; -- 性能好

join

1SELECT * FROM staff CROSS JOIN warehouses; -- 笛卡尔积
2SELECT * FROM staff NATURAL JOIN warehouses; -- 自然连接
3SELECT * FROM staff INNER JOIN warehouses ON staff.warehouse_id = warehouses.warehouse_id;
4SELECT * FROM staff, warehouses WHERE staff.warehouse_id = warehouses.warehouse_id; -- 内连接
5SELECT * FROM staff LEFT OUTER JOIN warehouses ON staff.warehouse_id = warehouses.warehouse_id; -- 左外连接

组合查询

1SELECT * FROM staff WHERE warehouse_id = 'WH2' OR wage >= 1230;
2
3SELECT * FROM staff WHERE warehouse_id = 'WH2'
4UNION -- 不包含重复的列
5SELECT * FROM staff WHERE wage >= 1230;
6
7SELECT * FROM staff WHERE warehouse_id = 'WH2'
8UNION ALL -- 包含重复的列
9SELECT * FROM staff WHERE wage >= 1230;
10
11SELECT * FROM staff WHERE warehouse_id = 'WH2'
12UNION
13SELECT * FROM staff WHERE wage >= 1230 ORDER BY wage; -- 是最后一个的组成部分，反对所有的结果都有效

插入

1INSERT INTO customers(
2  cust_name,
3  cust_address,
4  cust_city,
5  cust_state,
6  cust_country)
7VALUES (
8  'ahab',
9  NULL,
10  'hb',
11  'study',
12  'cn'), (
13  'lj',
14  NULL,
15  'cd',
16  'study',
17  'cn'); -- 不依赖表中列的次序
18
19INSERT INTO customers(
20  cust_id,
21  cust_name)
22SELECT cust_id, cust_name
23FROM cust_new; -- 根据列的次序进行插入

更新删除

1UPDATE customers
2SET cust_email = NULL
3WHERE cust_name = 'ahab';
4
5DELETE FROM customers
6WHERE cust_id = 123;
7-- 使用 WHERE，除非要更新删除每一行

创建

1CREATE TABLE IF NOT EXISTS customers ( -- 创建新表时，表名必须不存在，IF NOT EXISTS 只检测表名不检测模式
2  cust_id int NOT NULL AUTO_INCREMENT, -- 每个表只允许一个 AUTO_INCREMENT 列，且必须被索引，使用 SELECT last_insert_id() 确定最后一个 AUTO_INCREMENT 值
3  cust_name char(50) NOT NULL,
4  cust_address char(50) NULL DEFAULT 'cn',
5  PRIMARY KEY (cust_id)) ENGINE = InnoDB; -- InnoDB 支持事务处理，MEMORY 功能同 MyISAM 但数据存在内存，速度极快，适合创建临时表，MyISAM 性能极高，支持全文本搜索，但不支持事务处理

更新表

1ALTER TABLE customers
2ADD cust_phone char(50);
3
4ALTER TABLE customers
5DROP cust_phone char(50);
6
7ALTER TABLE customers
8ADD CONSTRAINT fk_customers_school
9FOREIGN KEY (cust_school_id) REFERENCES school (school_id),
10ADD CONSTRAINT fk_customers_city
11FOREIGN KEY (cust_city_id) REFERENCES home (city_id);

删除表

1DROP TABLE customers;

重命名

1RENAME TABLE
2  backup_customers TO customers,
3  backup_vendor TO vendor;

视图

重用SQL语句。
简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节。
使用表的组成部分而不是整个表。
保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降得很厉害

ORDER BY可以用在视图中，但如果从该视图检索数据SELECT中也含有ORDER BY，那么该视图中的ORDER BY将被覆盖。
视图不能索引，也不能有关联的触发器或默认值。
视图可以和表一起使用。例如，编写一条联结表和视图的SELECT语句。

1CREATE VIEW <viewname>
2DROP VIEW <viewname>
3CREATE OR REPLACE VIEW <viewname>
4
5CREATE VIEW orderitemsexpanded AS
6SELECT order_num,
7  prod_id,
8  item_price,
9  quantity,
10  quantity * item_price AS expanded_price
11FROM orderitems;

一般，应该将视图用于检索(SELECT语句) 而不用于更新(INSERT、UPDATE和DELETE)。

存储过程

简单安全高性能

1DELIMITER // -- 使用 // 结束
2
3CREATE PROCEDURE Avg_wage()
4BEGIN
5  SELECT Avg(wage)
6  FROM staff;
7END //
8
9DELIMITER ; -- 改回使用 ;
10
11CALL Avg_wage(); -- 调用
12
13DROP PROCEDURE IF EXISTS Avg_wage;

1CREATE PROCEDURE show_sum_of_bigger_than_wage(
2  IN num INT,
3  OUT result INT)
4BEGIN
5  SELECT Sum(wage) -- 结果只能有一个数据
6  FROM staff
7  WHERE wage >= num
8  INTO result;
9END;
10
11CALL show_bigger_than_wage(1230, @sum_wages);
12SELECT @sum_wages;

1CREATE PROCEDURE ordertotal(
2  IN onumber INT,
3  IN taxable BOOLEAN,
4  OUT ototal DECIMAL(8, 2))
5COMMENT 'Obtain order total, optionally adding tax'
6BEGIN
7  DECLEAR total DECIMAL(8, 2);
8  DECLEAR taxrate INT DEFAULT 6;
9
10  SELECT Sum(item_price * quantity)
11  FROM orderitems
12  WHERE order_num = onumber
13  INTO total;
14
15  IF taxable THEN
16    SELECT total + (total / 100 * taxrate) INTO total;
17  END IF;
18
19  SELECT total INTO ototal;
20END;
21
22CALL ordertotal(20005, 0, @total) -- 0 == false, !0 == true

游标

MySQL 中只能用于存储过程和函数

1CREATE PROCEDURE processorders()
2BEGIN
3  DECLEAR done BOOLEAN DEFAULT 0;
4  DECLEAR o INT;
5  DECLEAR t DECIMAL(8, 2);
6
7  DECLEAR ordernumbers CURSOR
8  FOR
9  SELECT order_num FROM orders;
10
11  DECLEAR CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; -- 当 order_num 为 02000 时，设置 done = 1
12
13  CREATE TABLE IF NOT EXISTS ordertotals(
14    order_num INT,
15    total DECIMAL(8, 2));
16
17  OPEN ordernumbers;
18
19  REPEAT
20    FETCH ordernumbers INTO o;
21    CALL ordertotal(o, 1, t);
22    INSERT INTO ordertotals(order_num, total)
23    VALUES (o, t);
24  UNTIL done END REPEAT;
25
26  CLOSE ordernumbers;
27END;

触发器

DELETE、INSERT、UPDATE

只有表支持，视图、临时表都不支持

1-- BEFORE 用于数据验证与净化
2CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num -- AUTO_INCRMENT 之后确定新生成的值，INSERT 时有一个 NEW 虚表
3
4CREATE TRIGGER deleteorder BEFORE DELETE ON orders
5FOR EACH ROW
6BEGIN
7  INSERT INTO archive_orders(order_num, order_date, cust_id)
8  VALUES (OLD.order_num, OLD.order_date, OLD.cust_id); -- DELETE 时有一个 OLD 虚表
9END; -- 可使用 BEGIN END 存储多条语句
10
11-- UPDATE 时有 NEW、OLD 虚表
12-- 所有操作中，NEW 可以更新，OLD 只读

事务

事务(transaction)指一组SQL语句;
回退(rollback)指撤销指定SQL语句的过程;
提交(commit)指将未存储的SQL语句结果写入数据库表;
保留点(savepoint)指事务处理中设置的临时占位符(place-holder)，你可以对它发布回退(与回退整个事务处理不同)。

只能回退 UPDATE、DELETE、INSERT，不能回退 CREATE、DROP、SELECT

一般使用隐式提交，事务中只能使用 COMMIT

1START TRANSACTION;
2DELETE FROM orders WHERE order_num = 12000;
3DELETE FROM orders WHERE order_num = 12000; -- 执行出错，不会提交，操作会自动撤回
4COMMIT;
5
6-- 保留点
7SAVEPOINT delete1;
8ROLLBACK TO delete1;

安全管理

1CREATE USER ben IDENTIFIED BY 'p@$$wOrd';
2RENAME USER ben TO bufer;
3DROP USER bufer;
4SHOW GRANTS FOR ben;
5GRANTS SELECT ON klass FOR bufer;
6SET PASSWORD FOR bufer = Password('n3w p@$$wOrd'); -- 不置顶 user 时是更改自己的密码

维护

1FLUSH TABLES; -- 刷新，保证数据写入磁盘