MySQL必知必会读书笔记

MySQL 必知必会用例

where

1. where 必须放在 order by 之前。
2. 在建表时，可以指定其中的列是否可以不包含值。在一个列不包含值时，称其为 Null , 它与 0, 空字符串不同。通过 where is NULL 可以查询出 Null 值的列。

通配符

1. % 通配符搜索是区分大小写，匹配除 NULL 之外的0个，1个或多个字符。
2. _ 只匹配单个字符。

使用正则进行搜索

在 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

1. MySQL 使用的日期格式 yyyy-mm-dd，不管是插入或更新表值还是用 WHERE 子句进行过滤都必须使用这个格式。
2. 如果要的是日期，请使用Date()，如果一个字段类型是 DATETIME，在 WHERE 过滤时 WHERE Date(order_date) = '2010-01-01'
3. 如果是过滤一个时间范围。
   1. 使用 BETWEEN，WHERE Date(order_date) BETWEEN '2015-09-01' AND '2015-09-30'
   2. WHERE Year(order_date) = 2015 AND Month(order_data) = 9

分组

SELECT 字句顺序表

子句	说明	是否必须使用
SELECT	要返回的列或者表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚合时使用
HAVING	组级别过滤	否
ORDER BY	输出排序	否
LIMIT	要检索的行数	否

GROUP BY 子句的一些规范：

1. GROUP BY 必须在 WHERE 之后 ORDER BY 之前。
2. 除聚合函数外，SELECT 语句中的每个列都必须在 GROUP BY 子句中给出。
3. 如果分组中列具有 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 操作要么完全执行，要么完全不执行。如果执行的过程中发生错误，则进行回退以恢复到某个已知的且安全的状态。

使用事物和事物处理时，有几个术语需要知道：

1. 事物（transaction）指一组 SQL 语句。
2. 回退（rollback）指撤销指定 SQL 语句的过程。
3. 提交（commit）指将未存储的 SQL 语句的结果写入数据库表。
4. 保留点（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;