读mysql必知必会有感_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;