SHOW DATABASES;
SHOW TABLES;
SHOW COLUMNS FROM customers;
SHOW CREATE DATABASE crashcouse;
SHOW CREATE TABLE customers;

SELECT prod_name FROM products;
SELECT prod_name, prod_price FROM products; # 检索多列
SELECT * FROM products;  # 除非确实需要表中的每个列，否则最好不要使用`*`通配符，检索不需要的列通常会降低检索和应用程序的性能。

SELECT DISTINCT vend_id FROM products;  # DISTINCT 表示只返回唯一不同的行，用法是直接放在列名前，注意不能使用DISTINCT
                                        # DISTINCT 应用于所有列而不是前置它的列。
                                        # 可以通过 LEFT JOIN + IN 的方式：

SELECT prod_name FROM products LIMIT 5;     # 返回前5行；类似 LIMIT 0,5
SELECT prod_name FROM products LIMIT 5, 10; # 返回从第5行（包括5）开始的10条数据； 注意下标从0开始。
SELECT prod_name FROM products LIMIT 10 OFFSET 5; # 返回从第5行开始的10行

SELECT prod_name FROM products ORDER BY prod_name;
SELECT prod_name FROM products ORDER BY prod_price;  # 可以用非检索的列来排序
SELECT prod_name, prod_price FROM products ORDER BY prod_price,prod_name;  # 多列检索
SELECT prod_name, prod_price FROM products ORDER BY prod_price,prod_name DESC;  # 反序 （默认是ASC——升序）
SELECT prod_name, prod_price FROM products ORDER BY prod_price DESC,prod_name;  # 注意和上面的区别；
SELECT prod_name, prod_price FROM products ORDER BY prod_price DESC,prod_name DESC;  # DESC 关键字只应用到直接位于其前面的列名
SELECT prod_name, prod_price FROM products ORDER BY prod_price DESC LIMIT 1;    # 找到最昂贵的物品；
                                                     # ORDER BY 位于 FROM 之后，LIMIT 位于 ORDER BY 之后；

SELECT prod_name, prod_price FROM products WHERE prod_price = 2.50;
SELECT prod_name, prod_price FROM products WHERE prod_price >= 2.50;
SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;
SELECT cust_id FROM customers WHERE cust_email IS NULL;

SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id = 1003 AND prod_price <= 10; # 逻辑与
SELECT vend_id, prod_id, prod_price, prod_name FROM products WHERE vend_id = 1003 OR vend_id = 1002; # 逻辑或

SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;
SELECT prod_name, prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;
# 使用圆括号没有什么坏处，它能消除歧义

SELECT prod_name,prod_price FROM products WHERE vend_id IN (1002, 1003) ORDER BY prod_name;

SELECT prod_name, prod_price FROM products WHERE vend_id NOT IN (1002, 1003) ORDER BY prod_name;

SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%'; # 检索任意以jet起头的词
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '%anvil%'; # 检索任意位置包含文本anvil的值
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil'; # 与%可以匹配任意字符不一样,
                                                                            # _ 总是匹配一个字符，不能多也不能少

SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '.000' ORDER BY prod_name; # `.`匹配任意一个字符
SELECT prod_name FROM products WHERE prod_name REGEXP BINARY 'JetPack .000'; # 默认不匹配大小写, 使用 BINARY 来区分
SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000|3000'; # 进行OR匹配
SELECT prod_name FROM products WHERE prod_name REGEXP '[123] ton'; # 匹配任意几个字符, 指定一组用[和]括起来的字符
SELECT prod_name FROM products WHERE prod_name REGEXP '1|2|3 ton'; # 区别于上面的[123] ton,这里匹配的是1, 2, 3 ton
SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] ton'; # 匹配范围
SELECT prod_name FROM products WHERE prod_name REGEXP '\\.'; # 匹配特殊字符, 需要使用转义符
                                                             # Mysql要求两个反斜杠(Mysql自己解释一个,正则表达式库解释一个)
SELECT prod_name FROM products WHERE prod_name REGEXP '[:alnum:]'; # 匹配字符类,更多参考p58
SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)'; # sticks? 匹配stick和sticks

SELECT 'hello' REGEXP '[a-zA-Z]' # 验证字符是否符合正则

SELECT Concat(vend_name, ' (', vend_country, ')') FROM vendors ORDER BY vend_name; # 拼接字段Concatenate, 将值连接到一起构成单个值
SELECT Concat(vend_name, ' (', vend_country, ')') AS vend_title FROM vendors ORDER BY vend_name; # 别名alias
SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name; # 去除左边LTrim(), 右边RTrim(), 两边Trim()的空格

SELECT vend_name, Upper(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;
SELECT cust_name, cust_contact FROM customers WHERE Soundex(cust_contact) = Soundex('Y. Lie'); # 比较发音字符

SELECT cust_id, order_num FROM orders WHERE Date(order_date) = '2005-09-01';
SELECT cust_id, order_num FROM orders WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';
SELECT cust_id, order_num FROM orders WHERE Year(order_date) = '2005' AND Month(order_date) = '09';

SELECT AVG(prod_price) AS avg_price FROM products; # AVG函数只作用于单个列,为了获得多个列的平均值,必须使用多个AVG函数
SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id=1003;
                                                   # AVG 忽略列值为NULL的行
SELECT COUNT(*) AS num_cust FROM customers; # 对表中的所有行计数, 不论是否为NULL
SELECT COUNT(cust_email) AS num_cust FROM customers; # 对特定的列计数,忽略NULL值
SELECT MAX(prod_price) AS max_price FROM products; # 找出最大值     ,忽略NULL值
SELECT MIN(prod_price) AS min_price FROM products; # 找出最小值     ,忽略NULL值
SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005; # 指定列求和
SELECT SUM(quantity*item_price) AS total_price FROM orderitems WHERE order_num = 20005; # 合计计算值
SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 1003;
SELECT AVG(ALL prod_price) AS avg_price FROM products WHERE vend_id = 1003; # 默认AVG(ALL)
SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min, MAX(prod_price) AS price_max, AVG(prod_price) AS price_avg
FROM products;

SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;
SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >=2;
SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING orders >=2; # 第三条
SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >= 10
       GROUP BY vend_id HAVING num_prods >=2; # WHERE 和 HAVING 的区别, WHERE在数据分组前进行过滤,HAVING在数据分组后进行过滤,
                                              # WHERE 排除的行不包括在分组中
SELECT order_num, SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING ordertotal >=50;
SELECT order_num, SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING ordertotal >=50 ORDER BY ordertotal; # 对总计订单价格排序

SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
                  FROM orders
                  WHERE order_num IN (SELECT order_num
                                      FROM orderitems
                                      WHERE prod_id = 'TNT2')); # 方法1 子查询 p92
SELECT cust_name, cust_contact
FROM customers
LEFT JOIN orders ON customers.cust_id = orders.cust_id
LEFT JOIN orderitems ON orders.order_num = orderitems.order_num
WHERE orderitems.prod_id = 'TNT2'; # 方法2 外查询

SELECT cust_name,
       cust_state,
       (SELECT COUNT(*)
        FROM orders
        WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name; # 有必要完全限定列名, 即: table名.filed名

SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
  AND orderitems.order_num = orders.order_num
  AND prod_id = 'TNT2'; # 方法3 内部查询 p105

SELECT cust_name, cust_contact
FROM customers
INNER JOIN orders ON customers.cust_id = orders.cust_id
INNER JOIN orderitems ON orders.order_num = orderitems.order_num
WHERE orderitems.prod_id = 'TNT2'; # 方法4 内部查询 INNER JOIN p104
                                 # ANSI SQL 规范首选INNER JOIN 语法.对比WHERE子句,明确要使用联结条件, 性能更佳

SELECT p1.prod_id, p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
  AND p2.prod_id = 'DTNTR'; # 自联结  对比子查询, 有时候处理自联结远比处理子查询快的多

SELECT c.*, o.order_num, o.order_date,
       oi.prod_id, oi.quantity, oi.item_price
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
  AND oi.order_num = o.order_num
  AND prod_id = 'FB';  # 自然联结 排除多次出现, 全每个列只返回一次
                       # 系统不完成这项工作, 由你自己完成它
                       # 一般是对表使用通配符 (SELECT *), 对所有其他表的列使用明确的子集来完成

SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
 ON customers.cust_id = orders.cust_id; # 外部联结 和内部联结不同的是，外部联结还包括没有关联行的行。
                                        # 相对的还有 RIGHT OUT JOIN ，究竟使用哪一种纯粹是根据方便而定
                                        # 注意：LEFT JOIN 等同于 LEFT OUTER JOIN  —— https://stackoverflow.com/questions/406294/left-join-vs-left-outer-join-in-sql-server

SELECT customers.cust_name,
       customers.cust_id,
       COUNT(orders.order_num) AS num_ord
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id; # 带聚集函数的联结, 按客户分组；
                            # 其中 INNER JOIN 可以换成 LEFT JOIN，查找到订单为0的客户

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);

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION ALL
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001, 1002); # 使用 UNION ALL 不排除重复的行

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)
ORDER BY vend_id, prod_price; # 使用 UNION查询，只能使用一条ORDER BY 子句，必须出现在最后一条SELECT语句之后。但实际上是对所有结果排序。

SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit');

SELECT note_text, Match(note_text) Against('rabbit') AS rank
FROM productnotes; # 演示排序如何工作（等级越高，越靠前）

SELECT note_text FROM productnotes
WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);

SELECT note_text FROM productnotes WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE);
SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE);
SELECT note_text FROM productnotes WHERE Match(note_text) Against('"rabbit bait"' IN BOOLEAN MODE);
SELECT note_text FROM productnotes WHERE Match(note_text) Against('>rabbit <carrot' IN BOOLEAN MODE);
SELECT note_text FROM productnotes WHERE Match(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE);

INSERT INTO customers(cust_name,
  cust_contact,
  cust_email,
  cust_address,
  cust_city,
  cust_state,
  cust_zip,
  cust_country)
VALUES('Pep E.LaPew',
  NULL,
  NULL,
  '100 Main Street',
  'Los Angeles',
  'CA',
  '90046',
  'USA');

INSERT INTO customers(cust_name,
  cust_address,
  cust_city,
  cust_state,
  cust_zip,
  cust_country)
VALUES('Pep E. Lapwe',
'100 Main Street',
'Los Angeles',
'CA',
'90046',
'USA'),
('M. Martian',
'42 Galaxy Way',
'New York',
'NY',
'11213',
'USA'); # 一次插入多条数据：单条INSERT语句处理多个插入比使用多条INSERT语句快。

INSERT INTO customers(cust_name,
  cust_address,
  cust_city,
  cust_state,
  cust_zip,
  cust_country)
SELECT cust_name,
       cust_address,
       cust_city,
       cust_state,
       cust_zip,
       cust_country
FROM custnew; # 插入检索出的数据；
              # 不一定要求一列名匹配，但是列要互相对应：第一列对第一列，第二列对第二列。

UPDATE customers
SET cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005; # 务必指定 WHERE子句，否则更新所有行。

UPDATE customers SET cust_email = NULL WHERE cust_id = 10005; # 删除某个列的值，可设置它为NULL(前提是可以为NULL)

DELETE FROM customers
WHERE cust_id = 10006; # 务必指定 WHERE子句，否则删除所有行。

DELETE FROM customers; # 删除所有用户（逐行删除）
TRUNCATE TABLE customers; # 删除所有用户，更快的删除（删除表并重新创建表）

CREATE TABLE customers
(
  cust_id       int       NOT NULL AUTO_INCREMENT,
  cust_name     char(50)  NOT NULL,
  cust_address  char(50)  NULL,
  cust_city     char(50)  NULL,
  cust_state    char(5)   NULL,
  cust_zip      char(10)  NULL,
  cust_country  char(50)  NULL,
  cust_contact  char(50)  NULL,
  cust_email    char(50)  NULL,
  PRIMARY KEY (cust_id)
) ENGINE = InnoDB; # 适当的格式缩进以便阅读和编辑
                   # 试图给NOT NULL的列插入NULL（该列无值），将返回错误，插入失败
                   # 每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如，使它成为主键）
                   # `SELECT last_insert_id()`来获取最后一个AUTO_INCREMENT值。

CREATE TABLE IF NOT EXISTS customers
(cust_id       int       NOT NULL AUTO_INCREMENT); # 仅在表名不存在时创建

CREATE TABLE IF NOT EXISTS orderitems
(
  order_num   int       NOT NULL ,
  order_item  int       NOT NULL ,
  prod_id     char(10)  NOT NULL ,
  quantity    int       NOT NULL DEFAULT 1,
  item_price  DECIMAL(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE = InnoDB; # 由多个列组成的主键，以逗号分隔。
                   # 默认值不可为函数，只支持常量

ALTER TABLE vendors
ADD vend_phone CHAR(20); # 添加一个列

ALTER TABLE vendors
DROP COLUMN vend_phone; # 删除列

ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders
FOREIGN KEY (order_num) REFERENCES orders (order_num); # ALTER TABLE常见的用途是定义外键。


DROP TABLE customers2; # 删除表

RENAME TABLE customers2 TO customers; # 重命名表

RENAME TABLE backup_customers TO customers,
             backup_vendors TO vendors,
             backup_products TO products; # 对多个表重命名

CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact, prod_id
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
    AND orderitems.order_num = orders.order_num; # 创建视图

SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';    # 查询视图

CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')')
    AS vend_title
FROM vendors
ORDER BY vend_name; # 创建重新格式化的视图

SELECT * FROM vendorlocations;

CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL; # 创建过滤条件的视图

SELECT * FROM customeremaillist;

CREATE VIEW orderitemsexpanded AS
SELECT order_num,
       prod_id,
       quantity,
       item_price,
       quantity*item_price AS expanded_price
FROM orderitems;
SELECT * FROM orderitemsexpanded WHERE order_num = 20005;

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; # 存储过程的创建
     # 参数中的IN：传递数据给存储过程
     # 参数中的OUT：从存储过程传出
     # 参数中的INOUT：对存储过程传入传出

CALL productpricing(@pricelow, @pricehigh, @priceaverage); # 调用存储过程

SELECT @pricelow; # 检索结果
SELECT @pricehigh;
SELECT @priceaverage;

DROP PROCEDURE productpricing; # 删除存储过程，如果不存在会产生错误
DROP PROCEDURE IF EXISTS productpricing; # 删除存储过程

SHOW CREATE PROCEDURE productpricing; # 检查存储过程
SHOW PROCEDURE STATUS LIKE 'productpricing'; # 限制过程状态结果

DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
    SELECT Avg(prod_price) AS priceaverage
    FROM products;
END //
DELIMITER ;

-- Name: order total
-- Parameters: onumber = order number
--             taxable = 0 if not taxable, 1 if taxable
--             ototal = order total variable

CREATE PROCEDURE ordertotal(
  IN onumber INT,
  IN taxable BOOLEAN,
  OUT ototal DECIMAL(8, 2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
  -- Declare variable for total
  DECLARE total DECIMAL(8, 2);
  -- Decliare tax percentage
  DECLARE taxrate INT DEFAULT 6;

  -- Get the order total
  SELECT Sum(item_price*quantity)
  FROM orderitems
  WHERE order_num = onumber
  INTO total;

  -- Is this taxable?
  IF taxable THEN   # 不可使用 ELSEIF THEN、ELSE子句
  SELECT total+(total/100*taxrate) INTO total;
  END IF;

  -- And finally, save to out variable
  SELECT total INTO ototal;
END;

CALL ordertotal(20005, 0, @total);
SELECT @total;
CALL ordertotal(20005, 1, @total);
SELECT @total;

CREATE PROCEDURE processorders()
BEGIN
  -- Declare local variables
  DECLARE done BOOLEAN DEFAULT 0;
  DECLARE o INT;
  DECLARE t DECIMAL(8, 2);

  -- Declare the cursor
  DECLARE ordernumbers CURSOR
  FOR
  SELECT order_num FROM orders;
  -- Declare continue handler
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; # SQLSTATE '02000'是一个未找到条件。

  -- Create a table to store the results
  CREATE TABLE IF NOT EXISTS ordertotals(order_num INT, total DECIMAL(8, 2));

  -- Open the cursor
  OPEN ordernumbers;

  -- Loop through all rows
  REPEAT
    -- Get order number
    FETCH ordernumbers INTO o;

    -- Get the total for this order
    CALL ordertotal(o, 1, t);

    -- Insert order and total into ordertotals
    INSERT INTO ordertotals(order_num, total) VALUES(o, t);

    -- End of loop
  UNTIL done  END REPEAT;

  -- Close the cursor
  CLOSE ordernumbers;
END;  # 使用游标将Fetch的数据存储到表中

CALL processorders(); # 调用
SELECT * FROM ordertotals;

# http://www.cnblogs.com/duhuo/p/4655957.html
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added' INTO @msg; # 创建触发器
INSERT INTO products (prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES ('Lou', 1002, 'TNT 5', 20, 'TNT 5, yellow, pack of 100 sticks');
SELECT @msg;

DROP TRIGGER newproduct; # 删除触发器

CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO archive_orders(order_num, order_date, cust_id)
    VALUES(OLD.order_num, OLD.order_date, OLD.cust_id); # 删除前先存档
END;

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(New.vend_state); # 更新前先转换为大写（数据净化）

SELECT * FROM ordertotals;
START TRANSACTION WITH CONSISTENT SNAPSHOT ;  # 标识事务开始
DELETE FROM ordertotals;
ROLLBACK ; # 只能在一个事务处理内使用（在执行一条START TANASACTION之后）
SELECT * FROM ordertotals;

START TRANSACTION WITH CONSISTENT SNAPSHOT ;  # 标识事务开始
DELETE FROM orderitems WHERE order_num = 20007;
DELETE FROM orders WHERE order_num = 20007;
COMMIT; # 提交事务

SAVEPOINT delete1; # 保留点
ROLLBACK TO delete1; # 回滚到保留点

SET AUTOCOMMIT = 0; # 设置不自动提交

SHOW CHARACTER SET ; # 查看字符集
SHOW COLLATION ; # 查看校对, 以latin1为例，许多校对出现两次，一次区分大小写（由_cs表示）， 一次不区分（由_ci表示）

SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';

CREATE TABLE mytable
(
  columnn1 INT,
  columnn2 VARCHAR(10)
) DEFAULT CHARACTER SET hebrew
  COLLATE hebrew_general_ci; # 给表指定字符集和校对

CREATE TABLE mytable
(
  columnn1 INT,
  columnn2 VARCHAR(10),
  columnn3 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci # 给特定的列指定字符集和校对
) DEFAULT CHARACTER SET hebrew
  COLLATE hebrew_general_ci;

SELECT * FROM customers
ORDER BY lastname, firstname COLLATE latin1_general_cs;
   # 校对在对用`ORDER BY`子句检索出来的数据排序时起重要的作用（如是否忽略大小写）。

USE mysql;
SELECT user FROM user;

CREATE USER name_lou IDENTIFIED BY 'password_lou'; # 创建用户
RENAME USER name_lou TO lou; # 更改名

DROP USER lou; # 删除账号

SHOW GRANTS FOR lou; # 查看已经有的权限
GRANT SELECT ON goods.* TO lou; # 授予goods数据库的所有表的SELECT权限
REVOKE SELECT ON goods.* FROM lou; # 撤销权限

GRANT SELECT, INSERT ON goods.* TO lou; # 简化多次授权，使用逗号分隔

SET PASSWORD FOR lou = Password('new_password'); # 更改口令；
SET PASSWORD = Password('new_password'); # 更改自己的口令；

ANALYZE TABLE orders;
CHECK TABLE orders, orderitems;