检索数据
SQL1 从 Customers 表中检索所有的 ID
SELECT cust_id FROM Customers;去重关键字：DISTINCT 此关键字指示MySQL只返回不同（唯一）的值。
不能部分使用DISTINCT DISTINCT关键字应用于所有列而 不仅是前置它的列。如果给出SELECT DISTINCT vend_id, prod_price，除非指定的两个列都不同，否则所有行都将被检索出来。
SELECT DISTINCT prod_id FROM OrderItems;SELECT * FROM Customers;排序检索数据
DESC 按降序排序 从高到低
SELECT cust_name
FROM Customers
ORDER BY cust_name DESC;DESC 只对order_date列的数据，先cust_id，再order_date
SELECT cust_id, order_num
FROM Orders
ORDER BY cust_id, order_date DESC;SELECT *
FROM OrderItems
ORDER BY quantity DESC, item_price DESC;SELECT vend_name
FROM Vendors
ORDER BY vend_name DESC;过滤数据
SELECT prod_id, prod_name
FROM Products
where prod_price = 9.49;SELECT prod_id, prod_name
FROM Products
where prod_price >= 9;SELECT prod_name, prod_price
FROM Products
where prod_price BETWEEN 3 AND 6
ORDER BY prod_price;SELECT DISTINCT order_num
FROM OrderItems
where quantity >= 100;高级数据过滤
MySQL允许给出多个WHERE子句。这些子句可以两种方式使用：以AND子句的方式或OR子句的方式使用；
SELECT vend_name
FROM Vendors
WHERE vend_country = "USA" AND vend_state = "CA";SELECT order_num, prod_id, quantity
FROM OrderItems
WHERE (prod_id = 'BR01' or prod_id = 'BR02' or prod_id = 'BR03') AND quantity >= 100;SQL14 返回所有价格在 3美元到 6美元之间的产品的名称和价格
SELECT prod_name, prod_price
FROM Products
WHERE prod_price >= 3 AND prod_price <= 6
ORDER BY prod_price;SELECT vend_name
FROM Vendors
WHERE vend_country = 'USA' AND vend_state = 'CA'
ORDER BY vend_name;用通配符进行过滤
LIKE操作符
%表示任何字符出现任意次数；
下划线（_）。下划线的用途与%一样，但下划线只匹配单个字符而不是多个字符；
MySQL正则表达式:REGEXP操作符
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000'
ORDER BY prod_name;为搜索两个串之一（或者为这个串，或者为另一个串），使用|
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000｜2000'
ORDER BY prod_name;只匹配特定字符串，使用一组指定的[和]括起来的字符
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[123] Ton'
ORDER BY prod_name;[123]定义一组字符，它的意思是匹配1或2或3
可使用-来定义一个范围[0-9] == [0123456789]
为匹配特殊字符，使用\\前导
匹配字符类：
匹配多个实例
SELECT prod_name
FROM products
WHERE prod_name REGEXP '\\([0-9] sticks?\\)'
ORDER BY prod_name;\\(匹配(，[0-9]匹配任意数字（这个例子中为1和5），sticks?匹配stick和sticks（s后的?使s可选，因为?匹配它前面的任何字符的0次或1次出现），\\)匹配)。没有?，匹配stick和sticks会非常困难。
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[[:digit:]]{4}'
ORDER BY prod_name;[:digit:]匹配任意数字，因而它为数字的一个集合。{4}确切地要求它前面的字符（任意数字）出现4次，所以[[:digit:]]{4}匹配连在一起的任意4位数字
定位符
^有两种用法。在集合中（用[和]定义），用它来否定该集合，否则，用来指串的开始处。
LIKE匹配整个串而REGEXP匹配子串。利用定位符，通过用^开始每个表达式，用$结束每个表达式，可以使REGEXP的作用与LIKE一样
SELECT prod_name, prod_desc
FROM Products
WHERE prod_desc REGEXP 'toy';SELECT prod_name, prod_desc
FROM Products
WHERE prod_desc NOT LIKE '%toy%'
ORDER BY prod_name;SELECT prod_name, prod_desc
FROM Products
WHERE prod_desc LIKE '%toy%' AND prod_desc LIKE '%carrots%';SELECT prod_name, prod_desc
FROM Products
WHERE prod_desc LIKE '%toy%carrots%';创建计算字段
计算字段并不是实际存在于数据库表中的，计算字段是运行在SELECT语句内创建的。
拼接字段：Concat()函数，将多个值联结到一起变成单个值
SELECT Concat(name, '(', country, ')')
FROM vendors
ORDER BY name
Concat拼接串，多个串之间用逗号进行分隔
RTrim函数，删除数据右侧多余的空格
计算字段的常见用途是对检索出的数据进行算术运算。
SELECT prod_id, quantity, item_price, quantity * item_price AS expand_price
FROM orderitems
WHERE order_num = 20005;SELECT vend_id, vend_name AS vname, vend_address AS vaddress, vend_city AS vcity
FROM Vendors
ORDER BY vnameSELECT prod_id, prod_price, prod_price * 0.9 AS sale_price
FROM Products使用数据处理函数
文本处理函数
日期和时间处理函数
日期格式必须为：yyyy--mm--dd
Date函数仅提取列的日期部分，在进行特定的查找时更加的可靠。
检索出2005年9月的所有数据
SELECT cust_id, order_num
FROM orders
WHERE Year(order_date) = 2005 AND Month(order_date) = 9;数值处理函数
SELECT cust_id, cust_name, Upper(Concat(SubString(cust_name, 1, 2), SubString(cust_city, 1, 3))) AS user_login
FROM Customers;SQL23 返回 2020 年 1 月的所有订单的订单号和订单日期
SELECT order_num, order_date
FROM Orders
WHERE Year(order_date) = 2020 AND Month(order_date) = 1
ORDER BY order_date 汇总数据
聚集函数：运行在行组上，计算和返回单个值的函数
AVG函数忽略列值为NULL的行，为了获得多个列的平均值，需要使用多个AVG函数
COUNT函数，两种使用方式，COUNT(*)对表中的所有行数进行统计，包括NULL和空值
COUNT(COLUMN)对特定列中具有值的行进行统计，忽略NULL和空值
MAX函数忽略列值为NULL的行，MIN函数忽略列值为NULL的行，SUM函数忽略列值为NULL的行
如果指定列名，则DISTINCT只能用于COUNT函数，且不能用于COUNT(*)，不能使用COUNT(DISTINCT)
SELECT SUM(quantity) AS items_ordered
FROM OrderItems;SELECT SUM(quantity) AS items_ordered
FROM OrderItems
WHERE prod_id = 'BR01';SQL26 确定 Products 表中价格不超过 10 美元的最贵产品的价格
SELECT MAX(prod_price) AS max_price
FROM Products
WHERE prod_price <= 10;分组数据
GROUP BY子句：
SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;GROUP BY 子句指示MYSQL按照vend_id排序并分组数据，导致对每个vend_id而不是整个表计算num_prods
- GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制
- 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）
- GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
- 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。
- 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
- GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前
使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总的级别（针对每个分组的值）
过滤分组
HAVING子句用于过滤分组，所有类型的WHERE子句都能够用HAVING进行替代
WHERE和HAVING子句的区别：WHERE在数据分组前过滤，HAVING在数据分组后过滤；WHERE排除的行不包括在分组中，HAVING排除的行在分组中
GROUP BY 和 ORDER BY的区别
SELECT 子句顺序
SELECT order_num, COUNT(*) AS order_lines
FROM OrderItems
GROUP BY order_num
ORDER BY order_lines;SELECT vend_id, MIN(prod_price) AS cheapest_item
FROM Products
GROUP BY vend_id
ORDER BY cheapest_item;SELECT order_num
FROM OrderItems
GROUP BY order_num
HAVING SUM(quantity) >= 100
ORDER BY order_num;SELECT order_num, SUM(item_price * quantity) AS total_price
FROM OrderItems
GROUP BY order_num
HAVING total_price > 1000
ORDER BY order_num;SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num;使用子查询
嵌套SELECT查询
SQL32 返回购买价格为 10 美元或以上产品的顾客列表
SELECT DISTINCT cust_id
FROM Orders
WHERE Orders.order_num IN (
SELECT DISTINCT order_num
FROM OrderItems
WHERE item_price >= 10);SQL33 确定哪些订单购买了 prod_id 为 BR01 的产品（一）
SELECT cust_id, order_date
FROM Orders
WHERE Orders.order_num IN
(SELECT order_num
FROM OrderItems
WHERE prod_id = "BR01")
ORDER BY order_date;SQL34 返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（一）
SELECT cust_email
FROM Customers
WHERE Customers.cust_id IN
(SELECT cust_id
FROM Orders
WHERE Orders.order_num IN
(SELECT order_num
FROM OrderItems
WHERE prod_id = 'BR01')
);SELECT cust_id, total_ordered
FROM Orders,(
SELECT order_num, SUM(item_price * quantity) AS total_ordered
FROM OrderItems
GROUP BY order_num
ORDER BY total_ordered
)AS tot
WHERE Orders.order_num = tot.order_num
ORDER BY total_ordered DESC;SQL36 从 Products 表中检索所有的产品名称以及对应的销售总数
SELECT prod_name, quant_sold
FROM Products,
(SELECT prod_id, SUM(quantity) AS quant_sold
FROM OrderItems
GROUP BY prod_id
) AS TOT
WHERE Products.prod_id = TOT.prod_id;创建联结
应该保证所有联结都有WHERE子句，应该保证WHERE子句的正确性
创建联结的规则：首先列出所有表，然后定义表之间的关系
SELECT cust_name, order_num
FROM Customers, Orders
WHERE Customers.cust_id = Orders.cust_id
ORDER BY cust_name;SELECT cust_name, OrderItems.order_num, (quantity * item_price) AS OrderTotal
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND Orders.order_num = OrderItems.order_num
ORDER BY cust_name;SQL39 确定哪些订单购买了 prod_id 为 BR01 的产品（二）
SELECT cust_id, order_date
FROM OrderItems, Orders
WHERE OrderItems.order_num = Orders.order_num
AND OrderItems.prod_id = 'BR01'
ORDER BY order_date;SQL40 返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（二）
SELECT cust_email
FROM OrderItems, Orders, Customers
WHERE OrderItems.prod_id = "BR01"
AND OrderItems.order_num = Orders.order_num
AND Orders.cust_id = Customers.cust_id;SELECT cust_name, total_price
FROM Customers,
(SELECT cust_id, total_price
FROM Orders,
(SELECT order_num, SUM(item_price * quantity) AS total_price
FROM OrderItems
GROUP BY order_num) AS TOT
WHERE Orders.order_num = TOT.order_num) AS UN1
WHERE Customers.cust_id = UN1.cust_id AND total_price >= 1000
ORDER BY total_price;创建高级联结
SELECT cust_name, order_num
FROM Customers INNER JOIN Orders
ON Customers.cust_id = Orders.cust_id
ORDER BY cust_name;SELECT cust_name, order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id
ORDER BY cust_name;SELECT prod_name, order_num
FROM Products LEFT OUTER JOIN OrderItems ON Products.prod_id = OrderItems.prod_id
ORDER BY prod_name, order_num DESC;SELECT Products.prod_name, COUNT(OrderItems.order_num) AS orders
FROM Products LEFT OUTER JOIN OrderItems ON OrderItems.prod_id = Products.prod_id
GROUP BY Products.prod_name
ORDER BY Products.prod_nameSELECT
v.vend_id vend_id,
COUNT(p.prod_id) prod_id
FROM
Vendors AS v
LEFT JOIN Products AS p ON v.vend_id = p.vend_id
GROUP BY
v.vend_id
ORDER BY
v.vend_id;
组合查询
UNION使用规则
- UNION必须由两条或两条以上的SELECT语句组成，语句之间用关 键字UNION分隔（因此，如果组合4条SELECT语句，将要使用3个 UNION关键字）
- UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）
- 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以 隐含地转换的类型（例如，不同的数值类型或不同的日期类型）
UNION从查询结果集中自动去除了重复的行，如果需要返回所有的行，使用UNION ALL。
在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后，不允许使用多条ORDER BY子句
SELECT prod_id, quantity
FROM OrderItems
WHERE quantity = 100
UNION
SELECT prod_id, quantity
FROM OrderItems
WHERE prod_id LIKE 'BNBG%'
ORDER BY prod_id;SELECT prod_id, quantity
FROM OrderItems
WHERE quantity = 100 OR prod_id LIKE 'BNBG%'
ORDER BY prod_id;SQL49 组合 Products 表中的产品名称和 Customers 表中的顾客名称
SELECT prod_name
FROM Products
UNION
SELECT cust_name AS prod_name
FROM Customers
ORDER BY prod_nameSELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state = 'MI'
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state = 'IL'
ORDER BY cust_name;全文本搜索
一般在创建表的时候启用全文本搜索，使用FULLTEXT子句定义需要进行索引的列，定义之后，MYSQL自动维护该索引，在增加，删除，更新行时索引随之自动更新。
不要在导入数据的时候使用FULLTEXT
在指定索引后，使用Match函数和Against函数进行全文本搜索，Match指定被搜索的列，Against指定要使用的搜索表达式
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');
传递给 Match()的值必须与FULLTEXT()定义中的相同。如果指定多个列，则必须列出它们（而且次序正确）
全局搜索不区分大小写
查询扩展
查询扩展用来设法放宽所返回的全文本搜索结果的范围。
在使用查询扩展时，MySQL对数据和索引进行两遍扫描来完成搜索：
- 首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有行；
- 其次，MySQL检查这些匹配行并选择所有有用的词（我们将会简要地解释MySQL如何断定什么有用，什么无用）。
- 再其次，MySQL再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词。利用查询扩展，能找出可能相关的结果，即使它们并不精确包含所查找的词。
布尔文本搜索
即使没有FULLTEXT索引，也可以进行使用。只不过这种操作非常缓慢，效率十分低下。
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE);SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);包含heavy但是排除rope开头的词
插入数据
INSERT INTO插入表中数据 INSERT SELECT 插入检索得到的数据
INSERT和INTO之间添加关键字LOW_PRIORITY，指示MySQL降低INSERT语句的优先级：INSERT LOW_PRIORITY INTO
更新和删除数据
UPDATE 更新数据
--更新单个列
UPDATE customers
SET cust_email = 'XXXX'
WHERE cust_id = 10005;
--更新多个列
UPDATE customers
SET cust_email = 'XXXX',
cust_name = 'XXXX'
WHERE cust_id = 10005; UPDATE语句中可以使用子查询，使得能用SELECT语句检索出的数据更新列数据
为即使是发生错误，也继续进行更新，可使用IGNORE关键字：UPDATE IGNORE
为了删除某个列的值，可以将该列的值设置为NULL
DELETE 删除数据
--删除单行
DELETE FROM customers
WHERE cust_id = 10005;
如果想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句，它完成相同的工作，但速度更快（TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据）。
更新和删除的指导原则
- 除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
- 保证每个表都有主键，尽可能像WHERE子句那样使用它（可以指定各主键、多个值或值的范围）。
- 在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。
- 使用强制实施引用完整性的数据库，这样MySQL将不允许删除具有与其他表相关联的数据的行。
创建和操纵表
创建表
在创建新表时，指定的表名必须不存在，否则将出错。如果要防止意外覆盖已有的表，SQL要求首先手工删除该表（请参阅后面的小节），然后再重建它，而不是简单地用创建表语句覆盖它。如果你仅想在一个表不存在时创建它，应该在表名后给出IF NOT EXISTS。这样做不检查已有表的模式是否与你打算创建 的表模式相匹配。它只是查看表名是否存在，并且仅在表名不存在时创建它。
在创建表时，如果设置了NOT NULL，那么在更新和插入时，不允许为NULL值，设置为NULL的列值可以设置为NULL
PRIMARY KEY（）进行主键的设置，可以设置单一列为主键，如果是多个列则使用以逗号分隔的列表给出各列名
使用AUTO_INCREMENT
AUTO_INCREMENT告诉MySQL，本列每当增加一行时自动增量。每次执行一个INSERT操作时，MySQL自动对该列增量（从而才有这个关键字 AUTO_INCREMENT），给该列赋予下一个可用的值。这样给每个行分配一个唯一的cust_id，从而可以用作主键值。
每个表只允许一个AUTO_INCREMENT列，而且它必须被索引。
使用last_insert_id()函数获得最后一个AUTO_INCREMENT值，然后可将其用于后续的MYSQL语句中
使用默认值DEFAULT
在创建表时，表的列值最后使用DEFAULT可以设置默认值
更新表ALTER TABLE
删除表 DROP TABLE
重命名表 RENAME TABLE name1 TO name2
重命名多个表名，多个表之间用逗号分隔
视图
视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态 检索数据的查询。
视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。 在添加或更改这些表中的数据时，视图将返回改变过的数据。
视图的作用
- 重用SQL语句
- 简化复杂的SQL操作
- 使用表的组成部分而不是整个表
- 保护数据
- 更改数据格式和表示
视图的规则和限制
视图的使用
视图的更新
不能更新的情况：
- 分组（使用GROUP BY和HAVING）；
- 联结
- 子查询
- 并
- 聚集函数(MIN, MAX, SUM)
- DISTINCT
- 导出（计算）列
使用存储过程
存储过程就是为以后的使用而保存的一条或多条MySQL语句的集合。
使用存储过程的理由
- 通过把处理封装在容易使用的单元中，简化复杂的操作
- 保证了数据的完整性，防止错误，保证数据的一致性
- 简化对变动的管理，提高安全性
- 提高性能
- 可以编写更强更灵活的代码
调用存储过程
CALL productpricing(@pricelow,
@pricehigh,
@priceaverage);执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格
创建存储过程
CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage
FROM products;
END;删除存储过程
DROP PROCEDURE productpricing;使用参数的存储过程
CREATE PROCEDURE productpricing(
OUT pl DECIMAL(8,2),
OUT ph DECIMAL(8,2),
OUT pa DECIMAL(8,2)
)
BEGIN
SELECT MIN(prod_price)
INTO pl
FROM products;
SELECT MAX(prod_price)
INTO ph
FROM products;
SELECT AVG(prod_price)
INTO pa
FROM products;
END此存储过程接受3个参数：pl存储产品最低价格，ph存储产品最高价格，pa存储产品平均价格。每个参数必须具有指定的类型，这里使用十进制值。关键字OUT指出相应的参数用来从存储过程传出一个值（返回给调用者）。MySQL支持IN（传递给存储过程）、OUT（从存储过程传出，如这里所用）和INOUT（对存储过程传入和传出）类型的参数。存储过程的代码位于BEGIN和END语句内，如前所见，它们是一系列SELECT语句，用来检索值，然后保存到相应的变量（通过指定INTO关键字）。
--调用
CALL productpricing(@pricelow,
@pricehigh,
@priceaverage);检查存储过程
SHOW CREATE PROCEDURE ordertotal;
--获得包括何时、由谁创建等详细信息的存储过程列表
SHOW PROCEDURE STATUS;
--过滤模式，只显示部分存储过程列表
SHOW PROCEDURE STATUS LIKE '';
使用游标
游标是一个存储在MySQL服务器上的数据库查询，是被SELECT语句检索出的结果。在存储了游标之后，可以根据需要滚动或者浏览其中的数据。
MySQL游标只能用于存储过程或者函数
使用游标的步骤：
- 在能够使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的SELECT语句。
- 一旦声明后，必须打开游标以供使用。这个过程用前面定义的 SELECT语句把数据实际检索出来。
- 对于填有数据的游标，根据需要取出（检索）各行。
- 在结束游标使用时，必须关闭游标。
创建游标
--DECLARE语句用来定义和命名游标，这里为ordernumbers
--存储过程处理完成后，游标就消失（因为它局限于存储过程）
CREATE PROCEDURE processorders()
BEGIN
DECLEAR ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
END--打开游标
OPEN ordernumbers;
--关闭游标
CLOSE ordernumbers;使用声明过的游标不需要再次声明，直接用OPEN打开就行。
使用游标数据
在一个游标被打开后，可以使用FETCH语句分别访问它的每一行。 FETCH指定检索什么数据（所需的列），检索出来的数据存储在什么地方。 它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行（不重复读取同一行）
使用触发器
触发器是响应以下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句）
- DELETE
- INSERT
- UPDATE
其他MySQL语句不支持触发器。
创建触发器
- 唯一的触发器名；
- 触发器关联的表；
- 触发器应该响应的活动（DELETE、INSERT或UPDATE）；
- 触发器何时执行（处理之前或之后）
保持每个数据库中的触发器名称唯一
CREATE TRIGGER newproduct AFTER INSERT ON products
FRO EACH ROW SELECT 'Product added';创建一个触发器，触发器在INERT语句之后执行（AFTER INSERT），代码对每个插入行执行（FOR EACH），每个插入的行显示‘Product added’
只有表支持触发器，视图不支持
触发器按每个表每个事件每次定义，每个表每个事件每次只允许一个触发器。每个表最多六个触发器（BEGIN和AFTER，INSERT，UPDATE，DELETE两两组合）
如果BEFORE触发器失败，则MySQL将不执行请求的操作。此外，如果BEFORE触发器或语句本身失败，MySQL 将不执行AFTER触发器（如果有的话）
--删除触发器
DROP TRIGGER newproduct;
--触发器不能更新或者覆盖，为了修改一个触发器，必须先删除，再重新创建INSERT触发器
DELETE触发器
- 在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行；
- OLD中的值全都是只读的，不能更新
UPDATE触发器
关于触发器的一些重要的点
- 与其他DBMS相比，MySQL 5中支持的触发器相当初级。未来的MySQL版本中有一些改进和增强触发器支持的计划。
- 创建触发器可能需要特殊的安全访问权限，但是，触发器的执行是自动的。如果INSERT、UPDATE或DELETE语句能够执行，则相关的触发器也能执行。
- 应该用触发器来保证数据的一致性（大小写、格式等）。在触发器中执行这种类型的处理的优点是它总是进行这种处理，而且是透明地进行，与客户机应用无关。
- 触发器的一种非常有意义的使用是创建审计跟踪。使用触发器，把更改（如果需要，甚至还有之前和之后的状态）记录到另一个表非常容易。
- 遗憾的是，MySQL触发器中不支持CALL语句。这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。
管理事务处理
事务处理可以用来维护数据库的完整性，保证成批的MySQL操作要么完全执行，要么完全不执行。事务处理是一种机制，用来管理必须成批执行的MySQL操作，以保证数据库不包含不完 整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们或者作为整体执行，或者完全不执行（除非明确指示）。如果没有错误发生，整组语句提交给（写到）数据库表。如果发生错误，则进行回退（撤销）以恢复数据库到某个已知且安全的状态。
--标识事务的开始
START TRANSACTIONSELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;ROLLBACK语句回退START TRANSACTION之后的所有语句。ROLLBACK只能在一个事务处理内使用（在执行一条START TRANSACTION命令之后）不能回退CREATE或DROP操作
COMMIT提交语句
使用保留点
为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符。这样，如果需要回退，可以回退到某个占位符。每个保留点都取标识它的唯一名字，以便在回退时，MySQL知道要回退到何处。
SAVEPOINT delete1;
ROLLBACK TO delete1;保留点在事务处理完成后自动释放，也可以用RELEASE SAVEPOINT明确的释放保留点
默认的MySQL行为是自动提交所有更改，为指示MySQL不自动提交更改，使用以下语句：
SET autocommit = 0;安全管理
MySQL用户账号和信息存储在名为mysql的MySQL数据库中，使用以下代码进行查看：
USE mysql
SELECT user from user;创建用户账号
--用户名为ben 口令为password（非必需）
CREATE USER ben IDENTIFIED BY 'password';重命名用于账号
RENAME USER ben TO bforate;删除用户账号
DROP USER bforate;查看访问权限
SHOW GRANTS FOR bforate;设置访问权限
GRANT SELECT ON crashcourse.* TO bforate给bforate用于crashcourse数据库中所有表的SELECT权限
撤销访问权限
REVOKE SELECT ON crashcourse.* TO bforateGRANT和REVOKE可在几个层次上控制访问权限：
- 整个服务器，使用GRANT ALL和REVOKE ALL；
- 整个数据库，使用ON database.*；
- 特定的表，使用ON database.table；
- 特定的列；
- 特定的存储过程；
更改口令
SET PASSWORD FRO bforate = Password('XXXX');如果不设置用户名称，则默认是当前用户
改善性能
- 首先，MySQL（与所有DBMS一样）具有特定的硬件建议。在学习和研究MySQL时，使用任何旧的计算机作为服务器都可以。但对用于生产的服务器来说，应该坚持遵循这些硬件建议。
- 一般来说，关键的生产DBMS应该运行在自己的专用服务器上。
- MySQL是用一系列的默认设置预先配置的，从这些设置开始通常是很好的。但过一段时间后你可能需要调整内存分配、缓冲区大小等。（为查看当前设置，可使用SHOW VARIABLES;和SHOW STATUS;）
- MySQL一个多用户多线程的DBMS，换言之，它经常同时执行多个任务。如果这些任务中的某一个执行缓慢，则所有请求都会执行缓慢。如果你遇到显著的性能不良，可使用SHOW PROCESSLIST 显示所有活动进程（以及它们的线程ID和执行时间）。你还可以用KILL命令终结某个特定的进程（使用这个命令需要作为管理员登录）
- 总是有不止一种方法编写同一条SELECT语句。应该试验联结、并、子查询等，找出最佳的方法。
- 使用EXPLAIN语句让MySQL解释它将如何执行一条SELECT语句。
- 一般来说，存储过程执行得比一条一条地执行其中的各条MySQL语句快。
- 应该总是使用正确的数据类型。
- 决不要检索比需求还要多的数据。换言之，不要用SELECT *（除非你真正需要每个列）。
- 有的操作（包括INSERT）支持一个可选的DELAYED关键字，如果使用它，将把控制立即返回给调用程序，并且一旦有可能就实际执行该操作。
- 在导入数据时，应该关闭自动提交。你可能还想删除索引（包括 FULLTEXT索引），然后在导入完成后再重建它们。
- 必须索引数据库表以改善数据检索的性能。确定索引什么不是一件微不足道的任务，需要分析使用的SELECT语句以找出重复的 WHERE和ORDER BY子句。如果一个简单的WHERE子句返回结果所花的时间太长，则可以断定其中使用的列（或几个列）就是需要索引的对象。
- 你的SELECT语句中有一系列复杂的OR条件吗？通过使用多条SELECT语句和连接它们的UNION语句，你能看到极大的性能改进。
- 索引改善数据检索的性能，但损害数据插入、删除和更新的性能。 如果你有一些表，它们收集数据且不经常被搜索，则在有必要之前不要索引它们。（索引可根据需要添加和删除。）
- LIKE很慢。一般来说，最好是使用FULLTEXT而不是LIKE。
- 数据库是不断变化的实体。一组优化良好的表一会儿后可能就面目全非了。由于表的使用和内容的更改，理想的优化和配置也会改变。
- 最重要的规则就是，每条规则在某些条件下都会被打破
SELECT MAX(gpa)
FROM user_profile
WHERE university = '复旦大学';SELECT count(gender) AS male_num, AVG(gpa) AS avg_gpa
FROM user_profile
WHERE gender = 'male';SELECT gender, university, COUNT(device_id) AS user_num, SUM(active_days_within_30) / COUNT(device_id) AS avg_active_day,
SUM(question_cnt) / COUNT(device_id) AS avg_question_cnt
FROM user_profile
GROUP BY gender, university;SELECT university, SUM(question_cnt) / COUNT(id) AS avg_question_cnt,
SUM(answer_cnt) / COUNT(id) AS avg_answer_cnt
FROM user_profile
GROUP BY university
HAVING avg_question_cnt < 5 OR avg_answer_cnt < 20;SELECT university, SUM(question_cnt) / COUNT(id) AS avg_question_cnt
FROM user_profile
GROUP BY university
ORDER BY avg_question_cnt;SELECT user_profile.device_id, question_id, result
FROM user_profile, question_practice_detail
WHERE user_profile.device_id = question_practice_detail.device_id AND user_profile.university = '浙江大学';SELECT university, COUNT(question_id) / COUNT(DISTINCT question_practice_detail.device_id) AS avg_answer_cnt
FROM question_practice_detail, user_profile
WHERE user_profile.device_id = question_practice_detail.device_id
GROUP BY university
ORDER BY university;SELECT CASE WHEN age < 25 OR age IS NULL THEN '25岁以下'
WHEN age >= 25 THEN '25岁及以上'
END age_cut,COUNT(*)number
FROM user_profile
GROUP BY age_cutSELECT device_id, gender,CASE WHEN age < 20 THEN '20岁以下'
WHEN age BETWEEN 20 AND 24 THEN '20-24岁'
WHEN age >= 25 THEN '25岁及以上'
WHEN age IS NULL THEN '其他'
END age_cut
FROM user_profile;SQL28 计算用户8月每天的练题数量
SELECt DAY(date), COUNT(device_id)
FROM question_practice_detail
WHERE YEAR(date) = '2021' AND MONTH(date) = '8'
GROUP BY DAY(date);
1410
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
