MySQL 必知必会用例
where
-
where必须放在order by之前。 - 在建表时，可以指定其中的列是否可以不包含值。在一个列不包含值时，称其为
Null, 它与 0, 空字符串不同。通过where is NULL可以查询出Null值的列。
通配符
-
%通配符搜索是区分大小写，匹配除NULL之外的0个，1个或多个字符。 -
_只匹配单个字符。
使用正则进行搜索
在 where 字句中使用 REGEXP 替换 LIKE。 可以使用 REGEXP BINARY 来区分大小写。
日期
MySQL 支持的日期类型
| 日期时间类型 | 占用空间 | 日期格式 | 最小值 | 最大值 | 零值表示 |
|---|---|---|---|---|---|
| DATETIME | 8 bytes | YYYY-MM-DD HH:MM:SS |
1000-01-01 00:00:00 |
9999-12-31 23:59:59 |
0000-00-00 00:00:00 |
| TIMESTAMP | 4 bytes | 时间戳 | 19700101080001 |
2038 年的某个时刻 | 00000000000000 |
| DATE | 4 bytes | YYYY-MM-DD |
1000-01-01 |
9999-12-31 |
0000-00-00 |
| TIME | 3 bytes | HH:MM:SS |
-838:59:59 |
838:59:59 |
00:00:00 |
| YEAR | 1 bytes | YYYY |
1901 | 2155 | 0000 |
- MySQL 使用的日期格式
yyyy-mm-dd，不管是插入或更新表值还是用WHERE子句进行过滤都必须使用这个格式。 - 如果要的是日期，请使用Date()，如果一个字段类型是
DATETIME，在WHERE过滤时WHERE Date(order_date) = '2010-01-01' - 如果是过滤一个时间范围。
- 使用
BETWEEN，WHERE Date(order_date) BETWEEN '2015-09-01' AND '2015-09-30' WHERE Year(order_date) = 2015 AND Month(order_data) = 9
- 使用
分组
SELECT 字句顺序表
| 子句 | 说明 | 是否必须使用 |
|---|---|---|
| SELECT | 要返回的列或者表达式 | 是 |
| FROM | 从中检索数据的表 | 仅在从表选择数据时使用 |
| WHERE | 行级过滤 | 否 |
| GROUP BY | 分组说明 | 仅在按组计算聚合时使用 |
| HAVING | 组级别过滤 | 否 |
| ORDER BY | 输出排序 | 否 |
| LIMIT | 要检索的行数 | 否 |
GROUP BY 子句的一些规范：
-
GROUP BY必须在WHERE之后ORDER BY之前。 - 除聚合函数外，
SELECT语句中的每个列都必须在GROUP BY子句中给出。 - 如果分组中列具有 NULL，则 NULL 将作为一个分组返回。
使用 HAVING 对分组结果过滤，原来对行结果过滤的语法均可用。
SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP_BY cust_id
HAVING COUNT(*) >= 2;
子查询
子查询即嵌套在其他查询中的查询。子句查询总是从内向外处理。
-- 查询出商品是 TNT2 的订单号
SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2';
-- 根据订单号查询出客户ID
SELECT cust_id
FROM orders
WHERE order_num IN (20005, 20007);
-- 使用子查询完成上面两个步骤, 子句查询总是从内向外处理
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;
orders 是一个计算字段，它由圆括号中的子查询建立。该子查询对检索出的每个客户执行一次。
联结表
SQL 最强大的功能之一就是能在数据检索查询的执行中联结（join）表。有效地使用联结之前，必须先了解关系表以及关系数据库设计的一些基础知识。
关系表的设计就是要保证把信息分解成多个表，一类数据一个表。各表通过某些常用的值（即关系设计中的关系（relational）互相关联）。
外键（foreign key）为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。
创建联结
规定要联结的所有表以及它们如何关联即可。请看下面例子：
SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;
内部联结
目前为止所用的联结称为等值联结（equijoin），它基于两个表之间的相等测试。这种联结也称为内部联结。对于这种联结可以使用稍微不同的语法来明确指定联结类型。下面的语句将返回与前面例子一样的结果：
ANSI SQL 规范首选 INNER JOIN 语法。
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;
联结多个表
MySQL 在运行时关联指定的每个表以处理联结。这种处理可能是非常耗资源的，因此应该仔细，不要联结不必要的表。
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;
组合查询 UNION
MySQL 允许执行多个查询（多条 SELECT 语句），并将结果作为单个结果集返回。这些组合查询统称为并或复合查询。多条 SELECT 语句必须返回类似结构的数据。
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5;
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001, 1002);
-- 将上面的两个查询使用 UNION 合并成一个查询返回
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);
插入数据
插入多行
-- 插入多行
INSERT INTO customers(cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
VALUES(
'Pep E. LaPew',
'100 Main Street',
'Los Angeles',
'CA',
'90046',
'USA'
),(
'Pep E. LaPewz',
'100 Main Street',
'Los Angeles',
'CA',
'90046',
'USA'
);
插入搜索出的数据
-- insert select
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 custnew;
删除
删除是删除内容，而不是表
DELETE FROM customers
WHERE cust_id = 10006;
更快的删除 如果想从表中删除所有的行，不要使用 DELETE，可以使用 TRUNCATE TABLE 语句，它的速度更快（TRUNCATE 实际删除原来的表并新建一张表，而不是逐行删除）
AUTO_INCREMENT
如果在使用 AUTO_INCREMENT 列时获取到这个值呢？可以使用 last_insert_id() 函数获取这个值，此语句返回最后一个 AUTO_INCREMENT 的值。
存储过程
可以把存储过程看成一个函数
查看存储过程的详情。
-- 查看存储过程的详情
SHOW PROCEDURE STATUS;
一般存储过程并不显示结果，而是把结果返回给你指定的变量。
-- 存储过程, DELIMITER 指定结束符
DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
SELECT AVG(DISTINCT prod_price) AS priceaverage
FROM products;
END //
DELIMITER ;
-- 调用存储过程
CALL productpricing();
-- 删除存储过程
DROP PROCEDURE IF EXISTS productpricing;
带参数的存储过程
DELIMITER //
CREATE PROCEDURE ordertotal(
-- 关键字 IN 代表输入的参数
IN onumber INT,
-- 关键字 OUT 代表输出的参数
OUT ototal DECIMAL(8,2)
)
BEGIN
SELECT SUM(item_price*quantity)
FROM orderitems
WHERE order_num = onumber
-- INTO 表示保存到相应的变量
INTO ototal;
END //
DELIMITER ;
-- 所有 MySQL 的变量必须是@开头
CALL ordertotal(20005, @total);
SELECT @total;
事务处理
用来维护数据库的完整性，它保证成批的 MySQL 操作要么完全执行，要么完全不执行。如果执行的过程中发生错误，则进行回退以恢复到某个已知的且安全的状态。
使用事物和事物处理时，有几个术语需要知道：
- 事物（transaction）指一组 SQL 语句。
- 回退（rollback）指撤销指定 SQL 语句的过程。
- 提交（commit）指将未存储的 SQL 语句的结果写入数据库表。
- 保留点（savepoint）指事务处理中设置的临时占位符，你可以对它发布回退（与回退整个事务处理不同）。
事务可以用来管理 INSERT，UPDATE，DELETE (DML语句)。你不能回退 CREATE，DROP (DDL语句)，SELECT。
使用 START TRANSACTION 表示事物开始。
-- 事物
SELECT * FROM customers;
START TRANSACTION;
DELETE FROM customers;
SELECT * FROM customers;
ROLLBACK;
SELECT * FROM customers;
COMMIT 一般来说执行了 SQL 就会立即执行，这是因为包含了隐含提交（implicit commit），即提交（写或保存）操作是自动进行的。但是在事物中，提交不会隐含进行，必须明确指定。当 COMMIT 或 ROLLBACK 执行后，事物会自动关闭。
START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;
使用保留点 SAVEPOINT 来可以指定回滚的位置
START TRANSACTION;
DELETE FROM customers WHERE cust_id = 10006;
-- 回退至此，执行到 DELETE 语句为止
SAVEPOINT delete1;
DELETE FROM customers WHERE cust_id = 10007;
ROLLBACK TO delete1;
