MySQL的学习笔记

参考书籍： 《MySQL必知必会

一、准备（1、2为本文必需）

1

  1. 安装MySQL

    mysql 8.0.11安装教程_电脑软件-百度经验

    mysql8.0.13下载与安装图文教程 - 十么Shi - 博客园

  2. 《MySQL必知必会》.pdf

    链接: https://pan.baidu.com/s/1IW64p81V2lw8KD5-PzVjNA 提取码: 5ab7

  3. 样例表下载

    《MySQL必知必会》样例表脚本

    本文的SQL语句会用到《MySQL必知必会》的样例表，所以下载其官方的脚本文件 mysql_scripts.zip 然后解压。

  4. 安装Navicat（流行的数据库管理工具，良好的图形界面）

    Navicat Premium 12.1.20.0安装与激活

  5. Linux安装MySQL

    centOS6安装最新版MySQL8, 以及远程连接

二、数据库连接与样例表导入

  1. 数据库连接

    • mysql -u root -p 以root用户登录mysql

    • SHOW DATABASES; 显示可用数据库（源）列表

    • USE crashcourse; 进入crashcourse数据源（请先按照下面的步骤导入样例表）

    • SHOW TABLES; 进入crashcourse数据源后，显示所有表名

    • SHOW COLUMNS FROM customers; 显示customers表的所有列

    • SHOW 的其他用法，请参考《MySQL必知必会》p19

  2. 样例表导入

    mysql_scripts.zip 解压后会有 create.sql和populate.sql 两个脚本文件，按照以下步骤导入

     mysql -u root -p    ##用root登录mysql，然后输入密码
 CREATE DATABASE crashcourse;    ##新创建一个名为crashcourse（名字随意）的数据源
 USE crashcourse;    ##进入crashcourse数据源
 SOURCE create.sql路径;    ##执行create.sql脚本创建样例表
 SOURCE populate.sql路径;    ##执行populate.sql脚本填充样例表

三、SQL语言基础

  1. SQL语言分类

    • DQL（Data Query Language）数据库查询语言： SELECT 查询数据

    • DML（Data Manipulation Language）数据库操纵语言： INSERT 插入数据、UPDATE 更新数据 、 DELETE 删除数据

    • DDL（Data Definition Language）数据库定义语言： CREATE 创建表等、 ALTER 更新表等、 DROP 删除表等、 RENAME 重命名表等、 TRUNCATE 重建表

    • DCL（Data Control Language）数据库控制语言：用户管理： GRANT 、 REVOKE ，事务： COMMIT 、 ROLLBACK 、 SAVEPOINT

  2. DQL（查询）

    • 检索数据

        ## 1
  SELECT prod_name
  FROM products;
  ## 2
  SELECT prod_id, prod_name, prod_price
  FROM products;
  ## 3 检索所有列
  SELECT *
  FROM products;
  ## 4 不显示重复内容的行
  SELECT DISTINCT vend_id
  FROM products;
  ## 5 显示不多于5行
  SELECT prod_name
  FROM products
  LIMIT 5;
  ## 6 显示第五行开始的5行
  SELECT prod_name
  FROM products
  LIMIT 5,5;    ##第一个为开始位置，第二个为检索的行数
  ## 7 限定表名，即表名.列名的形式，后面特殊情况会用到
  SELECT products.prod_name
  FROM products;

    • 排序检索（ORDER BY）

        ## 1
  SELECT prod_name
  FROM products
  ORDER BY prod_name;
  ## 2
  SELECT prod_id, prod_price, prod_name
  FROM products
  ORDER BY prod_price, prode_name;    ##先根据prod_price排序再根据prode_name排序
  ## 3 指定排序方向
  SELECT prod_id, prod_price, prod_name
  FROM products
  ORDER BY prod_price DESC;    ##降序DESC，升序ASC（默认升序，不用写）
  ## 4 最高价格 ———— ORDER BY 加 LIMIT
  SELECT prod_price
  FROM products
  ORDER BY prod_price DESC
  LIMIT 1;

    • 过滤数据（WHERE）

        ## 1
  SELECT prod_name, prod_price
  FROM products
  WHERE prod_price = 2.50;
  ##### 附表：WHERE子句操作符 #####
  ##      =         等于        ##
  ##      <>        不等于      ##
  ##      !=        不等于      ##
  ##      <         小于        ##
  ##      <=        小等于      ##
  ##      >         大于        ##
  ##      >=        大等于      ##
  ##      BETWEEN   两个值之间  ##
  ###############################
  ## 2 使用BETWEEN操作符
  SELECT prod_name,prod_price
  FROM products
  WHERE prod_price BETWEEN 5 AND 10;    ##大于等于5，小于等于10
  ## 3 空值检查
  SELECT prod_name
  FROM products
  WHERE prod_price IS NULL;

    • 数据过滤（AND OR IN）

        ## 1 AND操作符
  SELECT prod_id, prod_price, prod_name
  FROM products
  WHERE vend_id = 1003 AND prod_price <= 10;
  ## 2 OR操作符
  SELECT prod_name, prod_price,
  FROM products
  WHERE vend_id = 1002 OR vend_id = 1003;
  ## 3 计算次序：先计算AND再计算OR，可用括号改变次序，先计算括号里的
  SELECT prod_name, prod_price,
  FROM products
  WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;
  ## 4 IN 操作符
  SELECT prod_name, prod_price
  FROM products
  WHERE vend_id IN (1002, 1003)    ##等效于 vend_id = 1002 OR vend_id = 1003
  ORDER BY prod_name;

    • 通配符过滤（LIKE）

        ## 1 '%'通配符
  SELECT prod_id, prod_name
  FROM products
  WHERE prod_name LIKE 'jet%';    ##任何字符出现任意次数包括0次
  ## 2 '_'通配符
  SELECT prod_id, prod_name
  FROM products
  WHERE prod_name LIKE '_ ton anvil';    ##任何字符出现1次

    • 正则表达式（REGEXP）

        ## 1 使用
  SELECT prod_name
  FROM products
  WHERE prod_name REGEXP '.000'
  ORDER BY prod_name;

      正则表达式相关知识：https://blog.csdn.net/antony1776/article/details/83062899

    • 拼接字段（多数DBMS使用+或||实现拼接，而MySQL使用Concat()函数）

        ## 1
  SELECT Concat(vend_name, '(', vend_country, ')')
  FROM vendors
  ORDER BY vend_name;
  ## 2 去掉多余空格Trim()左右，RTrim()右，LTrim()左
  SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')')
  FROM vendors
  ORDER BY vend_name;
  ## 3 使用别名
  SELECT Concat(vend_name, '(', vend_country, ')') AS vend_title
  FROM vendors
  ORDER BY vend_name;
  ## 4 计算并使用别名
  SELECT prod_id, quantity*item_price AS expanded_price
  FROM orderitems
  WHERE order_num = 20005;

    • 函数

        ###################### 附表：文本处理函数 ######################
  ##   Left()                  返回串左边的字符                 ##
  ##   Right()                 返回串右边的字符                 ##
  ##   Length()                返回串的长度                     ##
  ##   Soundex()               返回串的SOUNDEX值，比较发音      ##
  ##   Upper()                 将串转换为大写                   ##
  ##   Lower()                 将串转换为小写                   ##
  ##   LTrim()                 去掉串左边空格                   ##
  ##   RTrim()                 去掉串右边空格                   ##
  ##   SubString()             返回子串                         ##
  ##   Locate(SubStr,Str)      返回Str在SubStr第一次出现的位置   ##
  ##   Locate(SubStr,Str,pos)  返回Str在SubStr第pos次出现的位置  ##
  ###############################################################
  ## 1
  SELECT cust_name, cust_contact
  FROM customers
  WHERE Soundex(cust_contact) = Soundex('Y Lie')
  #################### 附表：时间日期处理函数 ####################
  ##   AddDate()                增加一个日期（天、周等）        ##
  ##   AddTime()                增加一个时间（时、分等）        ##
  ##   CurDate()                返回当前日期                   ##
  ##   CurTime()                返回当前时间                   ##
  ##   Date()                   返回时间日期的日期部分          ##
  ##   DateDiff()               计算两个日期之差               ##
  ##   Date_Add()               高度灵活的日期运算函数          ##
  ##   Date_Format()            返回一个格式化的日期或时间串    ##
  ##   Day()                    返回一个日期的天数部分          ##
  ##   DayOfWeek()              返回一个日期对应的星期几        ##
  ##   Month()                  返回一个日期的月份部分          ##
  ##   Year()                   返回一个日期的年份部分          ##
  ##   Time()                   返回一个时间日期的时间部分      ##
  ##   Hour()                   返回一个时间的小时部分          ##
  ##   Minute()                 返回一个时间的分钟部分          ##
  ##   Second()                 返回一个时间的秒部分            ##
  ##   Now()                    返回当前日期和时间              ##
  ##############################################################
  ## 2 时间日期标准格式：yyyy-mm-dd hh:mm:ss
  SELECT cust_id, cust_num
  FROM orders
  WHERE Date(order_date) = '2005-09-01';
  ## 3 查询某月（2005年9月）订单
  SELECT cust_id, cust_num
  FROM orders
  WHERE Year(order_date) = 2005 AND Month(order_date) = 9;    ##不推荐使用：WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30'
  ###################### 附表：数值处理函数 ######################
  ##   Abs()                 返回一个数的绝对值                 ##
  ##   Cos()                 返回一个角度的余弦                 ##
  ##   Exp()                 返回一个数的指数值                 ##
  ##   a Mod b               返回a除b的余数                     ##
  ##   a DIV b               返回a除b的除数                     ##
  ##   Pi()                  返回圆周率                        ##
  ##   Rand()                返回一个随机数                     ##
  ##   Sin()                 返回一个角度的正弦                 ##
  ##   Sqrt()                返回一个数的平凡根                 ##
  ##   Tan()                 返回一个角度的正切                 ##
  ###############################################################

    • 聚集函数汇总数据

        ######################## 附表：聚集函数 ########################
  ##   AVG()                 返回某列的平均值                   ##
  ##   COUNT()               返回某列的行数                     ##
  ##   MAX()                 返回某列的最大值                   ##
  ##   MIN()                 返回某列的最小值                   ##
  ##   SUM()                 返回某列值之和                     ##
  ###############################################################
  ## 1 AVG()
  ## 全部平均值
  SELECT AVG(prod_price) AS avg_price
  FROM products;
  ## 像同行只留一个
  SELECT AVG(DISTINCT prod_price) AS avg_price
  FROM products;
  ## 2 COUNT()
  SELECT COUNT(*) AS num_cust
      ## COUNT(*) 返回customers表的行数，包括NULL值
      ## COUNT(column) 返回column列的行数，不包括NULL值
  FROM customers;
  ## 3 SUM()
  SELECT SUM(item_price*quantity) AS total_price
  FROM orderItems
  WHERE order_num = 20005;
  ## 4 组合聚集函数
  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;

    • 分组数据（GROUP BY）

        ## 1
  SELECT vend_id, COUNT(*) AS num_prods
  FROM products
  GROUP BY vend_id;
  ## 2
  SELECT vend_id, COUNT(*) AS num_prods
  FROM products
  GROUP BY vend_id WITH ROLLUP;    ##在最后一排加上所有为一组计算的值
  ## 3 HAVING
  SELECT vend_id, COUNT(*) AS num_prods
  FROM products
  WHERE prod_price >= 10
  GROUP BY vend_id
  HAVING COUNT(*) >= 2
  ## SELECT所有子句顺序
  ## 1.SELECT
  ## 2.FROM
  ## 3.WHERE
  ## 4.GROUP BY
  ## 5.HAVING
  ## 6.ORDER BY
  ## 7.LIMIT

    • 子查询

        ## 1
  SELECT cust_name, cust_concat
  FROM customers
  WHERE cust_id IN (
      SELECT cust_id
      FROM orders
      WHERE order_num IN (
          SELECT order_num
          FROM orderitems
          WHERE 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;

    • 联结表（联结：每个表有一个主键列和多个外键列，外键列分别对用其他表的主键列）

        ## SQL 92：使用 WHERE 完成联结查询，没有则是笛卡尔积，简单方便
  ## 1 等值联结又称为内部联结
  SELECT vend_name, prod_name, prod_price
  FROM vendors, products
  WHERE vendors.vend_id = products.vend_id
  ORDER BY vend_name, prod_name;
  ## 2 自联结
  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';
  ## SQL 99（推荐）：在 FROM 之后只需一个数据表，多个数据表使用 XXX JOIN 连接，连接条件不再放在 WHERE 子句之后，而是提供专门的连接条件子句，可读性更强
  ## 3 内部联结（INNER JOIN）与自然联结差不多
  SELECT vend_name, prod_name, prod_price
  FROM vendors INNER JOIN products
  ON vendors.vend_id = products.vend_id;
  ## 4 自然联结（NATURAL JOIN）
  SELECT vend_name, prod_name, prod_price
  FROM vendors NATURAL JOIN products;    ##自动将两表同名列作为连接条件
  ## 5 USING子句联结
  SELECT vend_name, prod_name, prod_price
  FROM vendors JOIN products
  USING(vend_id);    ##多个同名列时，选择指定列作为连接条件
  ## 6 ON子句联结（可以等值、非等值联结）
  SELECT vend_name, prod_name, prod_price
  FROM vendors JOIN products
  ON vendors.vend_id = products.vend_id;
  ## 7 交叉联结（CROSS JOIN）笛卡尔积
  SELECT vend_name, prod_name, prod_price
  FROM vendors CROSS JOIN products;
  ## 8 外部联结（LEFT/RIGHT/FULL [OUTER] JOIN）OUTER可以不写
  ## 应用情况：检索所有客户订单，包括没有订单的客户
  ## 左联结：LEFT [OUTER] JOIN，显示所有左边客户表，右边订单表没有则显示null
  ## 右联结：RIGHT [OUTER] JOIN，与左联结相反
  ## 全连接（MySQL不支持）：FULL [OUTER] JOIN，左右联结结合
  SELECT customers.cust_id, orders.order_num
  FROM customers LEFT OUTER JOIN orders
  ON customers.cust_id = orders.cust_id;

      推荐使用新规范，可读性更强，但是书中没有将 ANSI SQL 99 规范讲清楚，当时也学得不明白，推荐参考如下链接：https://www.cnblogs.com/geaozhang/p/6753190.html

    • 组合查询（UNION）

        ## 1 将两个查询语句连在一起
  SELECT vend_id, prod_id, prod_price
  FROM products
  WHERE prod_price <= 5
  UNION    ## UNION 默认取消重复的行，UNION ALL 则不取消
  SELECT vend_id, prod_id, prod_price
  FROM products
  WHERE vend_id IN (1001,1002);

    • 全文本搜索（暂不介绍，请参考《MySQL必知必会》p161）

        ## MySQL常用引擎为 MyISAM 和 InnoDB（常用）
  ## MyISAM：支持全文本搜索，不支持事务
  ## InnoDB：支持事务，不支持全文本搜索

  3. DML（插入、更新、删除）

    • 插入数据 INSERT

        ## 1 默认插入新行
  INSERT INTO customers
  VALUES(
      NULL,
      'Pep',
      '100 Main',
      'Los',
      'CA',
      '90046',
      'USA',
      NULL,
      NULL
  );    ##根据表的列来对应赋值，第一个cust_id为主键且自动增量，所以为NULL
  ## 2 写出指定列插入新行
  INSERT INTO customers(
      cust_name,
      cust_address,
      cust_city,
      cust_state,
      cust_zip,
      cust_country,
      cust_contact,
      cust_email
  )VALUES(
      'Pep',
      '100 Main',
      'Los',
      'CA',
      '90046',
      'USA',
      NULL,
      NULL
  );    ##明确写出列名再对应赋值，主键为自动增量可以不写
  ## 3 插入多行
  INSERT INTO customers(
      cust_name,
      cust_address,
      cust_city,
      cust_state,
      cust_zip,
      cust_country,
  )VALUES(
      'Pep',
      '100 Main',
      'Los',
      'CA',
      '90046',
      'USA',
  ),(
      'Martian',
      '42 Main',
      'New York',
      'NY',
      '11213',
      'USA',
  );
  ## 4 插入检索的数据
  INSERT INTO customers(
      cust_id,
      cust_name,
      cust_address,
      cust_city,
      cust_state,
      cust_zip,
      cust_country
  )SELECT cust_id,
          cust_name,
          cust_address,
          cust_city,
          cust_state,
          cust_zip,
          cust_country
  FROM custnew;    ##不要求列名匹配，但推荐列名相同

    • 更新数据 UPDATE

        ## 1 更新指定行的指定列
  UPDATE customers    ##UPDATE IGNORE 强制更新，慎用
  SET cust_name = 'The Fudds',
      cust_email = 'elmer@fudd.com'
  WHERE cust_id = 10005;    ##省略WHERE子句，将更新所有行

    • 删除数据 DELETE

        ## 1 删除特定行
  DELETE FROM customers
  WHERE cust_id = 10006;    ##省略 WHERE 子句则删除所有行
  ## 2 更快地删除所有行
  TRUNCATE customers;    ## DDL语句，表示删除原来的表再重建这个表，更高效

  4. DDL（表、视图、索引、存储过程、游标、触发器）

    关键字：CREATE 创建表等、 ALTER 更新表等、 DROP 删除表等、 RENAME 重命名表等、 TRUNCATE 重建表（重建表前面已讲，不再赘述）

    • 表

        ## 1 创建表
  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(50)    NULL,
      cust_zip       CHAR(50)    NULL,
      cust_country   CHAR(50)    NULL   DEFAULT China,
      cust_contact   CHAR(50)    NULL,
      cust_email     CHAR(255)   NULL,
      PRIMARY KEY (cust_id)
  ) ENGINE = InnoDB;
  ## (1) 表名后添加 IF NOT EXISTS ，仅当表不存在时创建它
  ## (2) int、char(50) 为MySQL数据类型，后面有详解
  ## (3) NOT NULL、NULL 分别表示不能为空和能为空
  ## (4) AUTO_INCREMENT 表示自动增量，无需给值，一般为主键值
  SELECT last_insert_id()    ##可获得最后一次 AUTO_INCREMENT 的值
  ## (5) DEFAULT 设置默认值
  ## (6) PRIMARY KEY 表示设置主键
  ## (7) ENGINE 设置数据库引擎，推荐 InnoDB
        ## 2 更新表
  ## (1) 添加一个列
  ALTER TABLE vendors
  ADD vend_phone CHAR(20);
  ## (2) 删除一个列
  ALTER TABLE vendors
  DROP COLUMN vend_phone;
  ## (3) 定义外键
  ALTER TABLE orderitems
  ADD CONSTRAINT fk_orderitems_orders
  FOREIGN KEY (order_num) REFERENCES orders (order_num);
        ## 3 删除表
  DROP TABLE customers2;
  ## 4 重命名表
  RENAME TABLE customers2 TO customers;

      附表1：串数据类型

      数据类型 说明
      CHAR 1~255定长字符串，长度必须创建时指定，否则默认CHAR(1)
      VARCHAR 长度可变字符串，创建时指定n，可存储0到n的变长串，其中n<=255
      SET 接受最多64个串组成的一个预定义集合的零个或多个串
      ENUM 接受最多64K个串组成的一个预定义集合的某个串
      TEXT 最大64K的变长文本
      TINYTEXT 最大255字节的长文本
      MEDIUMTEXT 最大16K的变长文本
      LONGTEXT 最大4GB的变长文本

      附表2：数值数据类型（默认有符号，加 UNSIGNED 变为无符号）

      数据类型 说明
      BIT 位字段，1~64位，MySQL5之前等价与TINYINT
      INT（或INTEGER） 整数，4个字节
      TINYINT 整数，1个字节
      SMALLINT 整数，2个字节
      MEDIUMINT 整数，3个字节
      REAL 浮点数，4个字节
      FLOAT 单精度浮点数
      DOUBLE 双精度浮点数
      DECIMAL（或DEC） 精度可变浮点数
      BOOLEAN（或BOOL） 布尔类型
      MONEY MySQL没有专门存储货币的数据类型，书中推荐使用DECIMAL(8,2)

      附表3：日期时间数据类型

      数据类型 说明
      DATE 日期，格式：YYYY-MM-DD
      TIME 时间，格式：HH:MM:SS
      DATETIME DATE与TIME的结合
      TIMESTAMP 与DATETIME相同，范围较小
      YEAR 2位数表示范围70(1970)69(2069)，4位数表示范围19012155

      附表4：二进制数据

      数据类型 说明
      BLOB 最大长度64KB
      TINYBLOB 最大长度255字节
      MEDIUMBLOB 最大长度16MB
      LONGBLOB 最大长度4GB

    • 视图（虚拟的表）

      当使用联结查询多个表时，查询语句会很复杂。这时可以使用视图，视图将这个复杂的查询语句命名为一个视图。查询的时候只需要查询视图就行了，使用视图甚至可以提前将数据进行拼接或过滤操作。

      注意：1.视图必须唯一名；2.视图可以嵌套其他视图检索出来的数据；3.视图可以使用 ORDER BY 但是会被查询语句中的 ORDER BY 覆盖；4.视图不能有索引、触发器

        ## 1 创建视图
  CREATE VIEW productscustomers AS
  SELECT cust_name, cust_concact, prod_id
  FROM customers, orders, orderitems
  WHERE customers.cust_id = orders.cust_id AND orderirems.order_num = orders.order_num;
  ## 2 强制不允许改变视图的数据，因为视图不存储数据，不推荐直接改变视图的数据
  WITH CHECK OPTION    ##创建时，在末尾加上这条语句
  ## 3 查看创建视图的语句
  SHOW CREATE VIEW viewname;
  ## 4 删除视图
  DROP VIEW viewname;
  ## 5 更新视图，书中说MySQL有许多限制比较麻烦，建议重建视图
  CREATE OR REPLACE VIEW ...;

    • 索引（从属于表，加速表的查询）

      书中没讲索引，如有错误请指出

      自动创建、删除索引：

      表上定义主键、外键、唯一约束时，系统会自动创建索引，MySQL使用 information_schema 数据源的 STATISTICS 表来保存；当表被删除时，该表上的索引自动删除

      手动创建、删除索引：

        ## 1 创建
  CREATE INDEX emp_last_name_idx
  ON employess(first_name, last_name);
  ## 2 删除
  DROP INDEX emp_last_name_idx
  ON employess;

    • 存储过程（数据库的函数）

        ## 1 创建
  CREATE PROCEDURE productpricing()    ##括号中根据需要写参数
  BEGIN
      SELECT Avg(prod_price) AS priceaverage
      FROM products;
  END;
  ## 2 上面写法会在命令行中出错改成如下
  DELIMITER //    ##除\符号外，可使用任何字符，这个使用的是//
  CREATE PROCEDURE productpricing()
  BEGIN
      SELECT AVG(prod_price) AS priceaverage
      FROM products;
  END //
  DELIMEIER;
  ## 3 执行存储过程
  CALL productpricing();
  ## 4 使用OUT参数
  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;
  ## 5 执行存储过程，获得参数值
  CALL productpricing(
      @pricelow,
      @pricehigh,
      @priceaverage
  );
  SELECT @pricelow, @pricehigh, @priceaverage;
  ## 6 使用IN参数
  CREATE PROCEDURE ordertotal(
      IN  onumber INT,
      OUT ototal  DECIMAL(8, 2)
  ) BEGIN
      SELECT SUM(item_price*quantity)
      FROM orderitems
      WHERE order_num = onumber
      INTO ototal;
  END;
  ## 7 执行存储过程，送入参数
  CALL ordertotal(20005, @total);
  SELECT @total;
  ## 8 建立智能存储过程，参考《MySQL必知必会》p171，里面使用了IF、ELSEIF、ELSE、THEN等用法
  ## 9 查看创建存储过程的语句
  SHOW CREATE PROCEDURE ordertotal;

    • 游标（MySQL只能在存储过程中使用）

        ## 1 创建游标
  CREATE PROCEDURE processorders()
  BEGIN
      DECLARE ordernumbers CURSOR
      FOR
      SELECT order_num FROM orders;
  END;
  ## 2 打开、关闭和使用游标
  CREATE PROCEDURE processorders()
  BEGIN
      --局部变量
      DECLARE o INT;
      --创建游标
      DECLARE ordernumbers CURSOR
      FOR
      SELECT order_num FROM orders;
      --打开游标
      OPEN ordernumbers;
      --使用，不作任何操作
      FETCH ordernumbers INTO o;
      --关闭游标
      CLOSE ordernumbers;
  END;
  ## 3 剩余内容请看《MySQL必知必会》p178，太多了

    • 触发器（MySQL5之后才支持）

      作用：在其关联的表被DELETE、INSERT、UPDATE操作的前后(BEFORE、AFTER)，执行一些固定的操作

        ## 1 创建 INSERT 触发器（其他跟这个一样）
  CREATE TRIGGER newproduct AFTER INSERT ON products
  FOR EACH ROW SELECT 'Product added';    ##每次插入就会显示'Product added'
  ## 2 删除触发器
  DROP TRIGGER newproduct;

  5. DCL（事务处，用户管理单独讲）

    • 标识事务开始

        ## 1 事务开始
  START TRANSACTION
  ## 2 使用 ROLLBACK 或 COMMIT 后事务自动关闭

    • 使用ROLLBACK

        ## 1 ROLLBACK只能回退DML语句
  SELECT * FROM ordertotals;
  START TRANSACTION
  DELETE FROM ordertotals;
  SELECT * FROM ordertotals;
  ROLLBACK;
  SELECT * FROM ordertotals;
  ## 2 设置保留点，并退回保留点
  SAVEPOINT delete1;
  ROLLBACK TO delete1;

    • 使用COMMIT

        ## 1 一般情况MySQL默认自动隐含提交，但事务处理块中不会隐含提交
  START TRANSACTION
  DELETE FROM orderitems WHERE order_num = 20010;
  DELETE FROM orders WHERE order_num = 20010;
  COMMIT;
  ## 2 更改MySQL默认自动隐含提交行为
  SET autocommit = 0;

四、管理用户

推荐链接：https://www.cnblogs.com/bethal/p/5512755.html

  1. 获取用户列表

     ## MySQL用户信息保存在 mysql 数据源的 user 表中
 use mysql;
 SELECT user FROM user;

  2. 创建删除用户

     ## 1 创建账户并设置密码
 CREATE USER ben IDENTIFIED BY 'p@$$word';
 ## 2 重命名
 RENAME USER ben TO bforta;
 ## 3 删除用户
 DROP USER bforta;

  3. 更改密码

     ## 1 书中只适用于老版本
 SET PASSWORD FOR bforta = Password('hello');
 ## 2 新版本没有PASSWORD这个字段，PASSWORD字段改成了authentication_string
 SET authentication_string FOR bforta = Password('hello');
 ## 具体情况以及更多方法自行百度

  4. 设置权限

     ## 1 查看用户所有权限
 SHOW GRANTS FOR bforta;
 ## 2 设置用户对crashcourse所有表的只读权限
 GRANT SELECT ON crashcourse.* TO bforta;
 ## 3 撤销用户对crashcourse所有表的只读权限
 REVOKE SELECT ON crashcourse.* TO bforta;