MySQL笔记

MySQL

1.分类和书写规范

1.分类

• DDL：数据定义语言。CREATE \ ALTER \ DROP \ RENAME \ TRUNCATE

• DML：数据操作语言,INSERT \ DELETE \ UPDATE \ SELECT

• DCL：数据控制语言.COMMIT \ ROLLBACK \ SAVEPOINT \ GRANT \ REVOKE

• 在windows下大小写不敏感

• 在linux下大小写敏感

• 建议

  • 数据库名，表名，表别名，字段名，字段别名等都小写
  • SQL关键字，函数名，绑定变量都大写

2.开始

• 开始服务
  • net start mysql
• 停止服务
  • net stop mysql
• 登录
  • mysql -uroot -p
• 修改密码
  • alter user root@localhost identified by ‘root’;
• 退出
  • exit;
  • quit;
• 查看数据库
  • show databases;

3.注释

单行注释：#注释文字(MySQL特有的方式)
单行注释：-- 注释文字(--后面必须包含一个空格。)
多行注释：/* 注释文字 */

4.使用数据库

USE database_name;

5.创建表格和显示表结构

• 创建

CREATE TABLE table_name(
name1 类型,
name2 类型
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

• 显示表结构
  • Field：表示字段名称。
    Type：表示字段类型，这里 barcode、goodsname 是文本型的，price 是整数类型的。
    Null：表示该列是否可以存储NULL值。
    Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分；UNI表示该列是UNIQUE索引的一部分；MUL表示在列中某个给定值允许出现多次。
    Default：表示该列是否有默认值，如果有，那么值是多少。
    Extra：表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等。

DESCRIB 表名;

DESC 表名;

2.select基本语句

1.语法

SELECT 要查询的列/全部列就是 *
FROM 要查询的表名;

• 特定列

SELECT 列名1,;列名2
FROM 要查询的表名;

2.别名

• 空格别名

• AS(alias)

# 1
SELECT 原名 别名
FROM 表名;
# 2
SELECT 原名 AS 别名
FROM 表名;

3.去重

• DISTINCT关键字

SELECT DISTINCT 列名
FROM 表名;

4.空值null参与运算

• null遇到运算结果都为null

SELECT id, 2 * id + 1 "列名"
FROM 表名;

5.着重号 ``

• 当表名等与系统中的关键字相同时，需要使用``才能使用

6.添加常数列

SELECT '常数' AS 列名，列2
FROM 表名;

7.过滤条件 where

SELECT *
FROM 表名
WHERE 条件;//比如 id = 1  / name = 'lily';

3.运算符

1.算数运算符

• +、-、*、/、%

2.比较运算符

• =：等号运算符
  • 注意：null比较返回null
• <=>：安全等号运算符
  • 两边都是null时，返回1
• <>(!=)：不等于运算符
  • 有null返回null
• <
• <=
• 空运算符 空运算符（IS NULL或者ISNULL）判断一个值是否为NULL，如果为NULL则返回1，否则返回0。 SQL语句示例如下：

mysql> SELECT NULL IS NULL, ISNULL(NULL), ISNULL('a'), 1 IS NULL;

• 非空运算符 非空运算符（IS NOT NULL）判断一个值是否不为NULL，如果不为NULL则返回1，否则返回0。
• 最小值运算符 语法格式为：LEAST(值1，值2，…，值n)。其中，“值n”表示参数列表中有n个值。在有两个或多个参数的情况下，返回最小值。

mysql> SELECT LEAST (1,0,2), LEAST('b','a','c'), LEAST(1,NULL,2);

• 最大值运算符 语法格式为：GREATEST(值1，值2，…，值n)。其中，n表示参数列表中有n个值。当有两个或多个参数时，返回值为最大值。假如任意一个自变量为NULL，则GREATEST()的返回值为NULL。
• BETWEEN AND运算符 BETWEEN运算符使用的格式通常为SELECT D FROM TABLE WHERE C BETWEEN A AND B，此时，当C大于或等于A，并且C小于或等于B时，结果为1，否则结果为0。

SELECT 1 BETWEEN 0 AND 1, 10 BETWEEN 11 AND 12, 'b' BETWEEN 'a' AND 'c';

• IN运算符 IN运算符用于判断给定的值是否是IN列表中的一个值，如果是则返回1，否则返回0。如果给定的值为NULL，或者IN列表中存在NULL，则结果为NULL。

SELECT 'a' IN ('a','b','c'), 1 IN (2,3), NULL IN ('a','b'), 'a' IN ('a', NULL);

• NOT IN运算符 NOT IN运算符用于判断给定的值是否不是IN列表中的一个值，如果不是IN列表中的一个值，则返回1，否则返回0。

• LIKE运算符 LIKE运算符主要用来匹配字符串，通常用于模糊匹配，如果满足条件则返回1，否则返回0。如果给定的值或者匹配条件为NULL，则返回结果为NULL。

  • 通配符

  • “%”：匹配0个或多个字符。
    “_”：只能匹配一个字符。
    

• SELECT 列名
  FROM 表名
  WHERE 列名 LIKE 'S%';
  

3.逻辑运算符

• NOT或！
• AND或＆＆
• OR或｜｜
• XOR异或

4.正则表达式：REGEXP

SELECT * FROM fruits WHERE f_name REGEXP '^b';

4.排序和分页

1.排序

• 排序规则

  • 使用ORDER BY子句排序

    • ASC(ascend):升序

    • DESC(descend)：降序

    • SELECT 列名
      FROM 表名
      ORDER BY 列名 ASC/DESC;
      

• 列为一列时就按照这列来排，可以使用不在SELECT中的列来 排序

• 列为多列时

  • 第一列出现相同时，采用第二列来排序
  • 以此类推

2.分页

• 使用LIMIT实现分页

  • 格式：LIMIT (位置偏移量，)行数;

  • --前10条记录：
    SELECT * FROM 表名 LIMIT 0,10;
    或者
    SELECT * FROM 表名 LIMIT 10;
    

  • 公式：

  • SELECT * FROM table
    LIMIT(PageNo - 1)*PageSize,PageSize;
    

• 好处：约束返回结果的数量可以减少数据表的网络传输量，也可以提升查询效率。如果我们知道返回结果只有1 条，就可以使用LIMIT 1 ，告诉 SELECT 语句只需要返回一条记录即可。这样的好处就是 SELECT 不需要扫描完整的表，只需要检索到一条符合条件的记录即可返回。

• 注意：LIMIT 子句必须放在整个SELECT语句的最后！

5.多表查询

1.等值连接和非等值连接

• 等值连接

  • AND操作符

  • 区别重复列名

  • 表的别名

  • SELECT e.employee_id, e.last_name, e.department_id,d.department_id, d.location_id
    FROM employees e , departments d
    WHERE e.department_id = d.department_id;
    

  • 注意：如果使用了别名，在查询字段和过滤条件中只能使用别名；连接n个表至少需要n-1个连接条件

• 非等值连接

  • 过滤条件是BETWEEN … AND

2.自连接和非自连接

• 自连接

  • 自己连接自己的表

  • SELECT emp.name,emp.id,mgr.name
    FROM employees emp,employees mgr
    WHERE emp.manager_id = mgr.id;
    

• 非自连接:不同的表

3.内连接和外连接

• 内连接

  • 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行。

  • # 99语法
    SELECT 字段列表
    FROM A表 INNER JOIN B表
    ON 关联条件
    WHERE 等其他子句;
    

• 外连接

  • 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。

  • 左外连接：则连接条件中左边的表也称为主表，右边的表称为从表

    • #实现查询结果是A
      SELECT 字段列表
      FROM A表 LEFT JOIN B表
      ON 关联条件
      WHERE 等其他子句;
      

  • 右外连接 : 则连接条件中右边的表也称为主表，左边的表称为从表

    • FROM A表 RIGHT JOIN B表
      ON 关联条件
      WHERE 等其他子句;
      

  • 满外连接：满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。

    • FULL OUTER JOIN
      

4.UNION 和 UNION ALL

利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

• NION：UNION 操作符返回两个查询的结果集的并集，去除重复记录

• UNION ALL：不去重

• SELECT * FROM 表1
  UNION
  SELECT * FROM 表2;
  

---

6.单行函数

1.函数理解

• 操作数据对象
• 参数返回一个结果
• 只对一行进行变换
• 每行返回一个结果
• 可以嵌套
• 参数可以是一列或一个值

2.数值函数

• ABS(), SIGN(), PI(), ,

进制转换

3.字符串函数

• ASCII(), CHAR_LENGTH(), LENGTH()

4.日期函数

4.1获取日期、时间

4.2日期与时间戳转换

4.3获取月份、星期、星期数、天数等函数

SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())
FROM DUAL;

函数	用法
EXTRACT(type FROM date)	返回指定日期中特定的部分，type指定返回的值

EXTRACT(type FROM date)函数中type的取值与含义：

SELECT EXTRACT(MINUTE FROM NOW()),EXTRACT( WEEK FROM NOW()),
EXTRACT( QUARTER FROM NOW()),EXTRACT( MINUTE_SECOND FROM NOW())
FROM DUAL;

4.4时间和秒钟转换的函数

例子

SELECT SEC_TO_TIME(78774);

4.5 计算日期和时间的函数

• 第一组

例子：

ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3,
DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4,
DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, #可以是负数
DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col6 #需要单引号
FROM DUAL;

• 第二组

例子：

SELECT
ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3'),DATEDIFF(NOW(),'2021-10-01'),
TIMEDIFF(NOW(),'2021-10-25 22:10:10'),FROM_DAYS(366),TO_DAYS('0000-12-25'),
LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()),12),MAKETIME(10,21,23),PERIOD_ADD(20200101010101,10)
FROM DUAL;

4.6 日期的格式化与解析

上述非GET_FORMAT 函数中fmt参数常用的格式符：

GET_FORMAT函数中date_type和format_type参数取值如下：

例子：

SELECT DATE_FORMAT(NOW(), '%H:%i:%s');
SELECT GET_FORMAT(DATE, 'USA');
SELECT DATE_FORMAT(NOW(),GET_FORMAT(DATE,'USA')),
FROM DUAL;

5.流程函数

例子：

SELECT IF(1 > 0,'正确','错误');
SELECT CASE
　　WHEN 1 > 0
　　THEN '1 > 0'
　　WHEN 2 > 0
　　THEN '2 > 0'
　　ELSE '3 > 0'
　　END;
　　
SELECT CASE 1
　　WHEN 1 THEN '我是1'
　　WHEN 2 THEN '我是2'
ELSE '你是谁'

SELECT employee_id,salary, CASE WHEN salary>=15000 THEN '高薪'
WHEN salary>=10000 THEN '潜力股'
WHEN salary>=8000 THEN '屌丝'
ELSE '草根' END "描述"
FROM employees;

6.加密解密函数

例子：

SELECT PASSWORD('mysql'), PASSWORD(NULL);

SELECT md5('123')

SELECT DECODE(ENCODE('mysql','mysql'),'mysql');

7. MySQL信息函数

MySQL中内置了一些可以查询MySQL信息的函数，这些函数主要用于帮助数据库开发或运维人员更好地
对数据库进行维护工作。

例子：

SELECT DATABASE();

SELECT USER(), CURRENT_USER(), SYSTEM_USER(),SESSION_USER();

SELECT CHARSET('ABC');

8. 其他函数

例子：

SELECT FORMAT(123.123, 2), FORMAT(123.523, 0), FORMAT(123.123, -2);

SELECT CONV(16, 10, 2), CONV(8888,10,16), CONV(NULL, 10, 2);

SELECT INET_ATON('192.168.1.100');

SELECT BENCHMARK(1, MD5('mysql'));

SELECT BENCHMARK(1000000, MD5('mysql'));

SELECT CHARSET('mysql'), CHARSET(CONVERT('mysql' USING 'utf8'));

7. 聚合函数

不能在where处使用聚合函数！ 但可以在having处使用

对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值。

7.1 AVG和SUM函数

• 数值类型

• SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
  FROM employees
  WHERE job_id LIKE '%REP%';
  

7.2 MIN和MAX函数

• 任意数据类型

• SELECT MIN(hire_date), MAX(hire_date)
  FROM employees;
  

7.3 COUNT函数

• 任意数据类型，count(*)返回表中记录总数,而count(列名)不会记录为NULL的

• SELECT COUNT(*)
  FROM employees
  WHERE department_id = 50;
  

7.4 配合使用

• group by：将表中的数据分成若干组

  • SELECT department_id, AVG(salary)
    FROM employees
    GROUP BY department_id ;
    

  • 可以使用多个列分组

• WITH ROLLUP 关键字

  • 在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。
  • 注意:当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。

• HAVING：过滤分组

  • 注意：

    • 行已经被分组。
    • 使用了聚合函数。
    • 满足HAVING 子句中条件的分组将被显示。
    • HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。

  • SELECT department_id, MAX(salary)
    FROM employees
    GROUP BY department_id
    HAVING MAX(salary)>10000 ;
    

  • having与where区别

    	优点	缺点
    where	先筛选数据再关联，执行效率高	不能使用分组中的计算函数进行筛选
    having	可以使用分组中的计算函数	在最后的结果中进行筛选，执行效率低

    开发选择：普通条件用where，包含分组统计函数用到的条件用having

7.5 select执行过程

1.结构

#方式1：
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
#方式2：
SELECT ...,....,...
FROM ... JOIN ...
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
#其中：
#（1）from：从哪些表中筛选
#（2）on：关联多表查询时，去除笛卡尔积
#（3）where：从表中筛选的条件
#（4）group by：分组依据
#（5）having：在统计结果中再次筛选
#（6）order by：排序
#（7）limit：分页

• 顺序

  • 关键字得顺序

  • SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...
    

  • select语句的执行顺序

  • FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT
    

  • 在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个虚拟表，然后将这个虚拟表传入下一个步骤中作为输入。虚拟表不可见

8. 子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。

SELECT 列名
FROM 表名
WHERE 表达式 运算关系符
                (SELECT 列名
                FROM 表名);

• 子查询（内查询）在主查询之前一次执行完成
• 子查询的结果被主查寻使用
• 注意
  • 子查询要包含在括号内
  • 将子查询放在比较条件二点右侧
  • 单行操作符对应单行子查询，多行操作对应多行子查询

8.1 单行子查询

子查询输出为单行，如

WHERE job_id =
(SELECT job_id
FROM employees
WHERE employee_id = 141)
AND salary >
(SELECT salary
FROM employees
WHERE employee_id = 143);

8.2 多行子查询

子查询输出为多行

• 多行操作比较符

• 操作符	含义
  IN	等于列表中的任意一个
  ANY	需要和单行比较操作符一起使用，和子查询返回的某一个值比较，跟任意一个比较
  ALL	需要和单行比较操作符一起使用，和子查询返回的所有值比较，跟所有比较
  SOME	实际上是ANY的别名，作用相同，一般常使用ANY

• # 查询最低工资
  SELECT department_id
  FROM employees
  GROUP BY department_id
  HAVING AVG(salary) <= ALL (
  SELECT AVG(salary) avg_sal
  FROM employees
  GROUP BY department_id
  )
  

8.3 相关子查询

如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为关联子查询。

# 查询员工的id,salary,按照department_name 排序
SELECT employee_id,salary
FROM employees e
ORDER BY (
SELECT department_name
FROM departments d
WHERE e.`department_id` = d.`department_id`
);

EXISTS 与 NOT EXISTS关键字

• 关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。
• 如果在子查询中不存在满足条件的行：
  • 条件返回 FALSE
  • 继续在子查询中查找
• 如果在子查询中存在满足条件的行：
  • 不在子查询中继续查找
  • 条件返回 TRUE
• NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

# 查询公司管理者的employee_id，last_name，job_id，department_id信息
SELECT employee_id, last_name, job_id, department_id
FROM employees e1
WHERE EXISTS ( SELECT *
				FROM employees e2
				WHERE e2.manager_id = e1.employee_id);

相关更新

使用相关子查询依据一个表中的数据更新另一个表的数据。

UPDATE table1 alias1
SET column = (SELECT expression
                FROM table2 alias2
                WHERE alias1.column = alias2.column);
# 添加一列
# 1）
ALTER TABLE employees
ADD(department_name VARCHAR2(14));
# 2）
UPDATE employees e
SET department_name = (SELECT department_name
                FROM departments d
                WHERE e.department_id = d.department_id);

相关删除

使用相关子查询依据一个表中的数据删除另一个表的数据。

DELETE FROM table1 alias1
WHERE column operator (SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);

# 删除表employees中，其与emp_history表皆有的数据
DELETE FROM employees e
WHERE employee_id in
		(SELECT employee_id
        FROM emp_history
        WHERE employee_id = e.employee_id);

9. 创建和管理表

• 数据类型

• 常用的几种类型介绍

9.1 创建数据库和使用

1.创建

• 创建数据库

  • CREATE DATABASE 数据库名;
    

• 创建数据库并指定字符集

  • CREATE DATABASE 数据库名 CHARACTER SET 字符集;
    

• 判断数据库是否已经存在，不存在则创建数据库

  • CREATE DATABASE IF NOT EXISTS 数据库名;
    

2.使用

• 查看当前所有的数据库

  • SHOW DATABASES; #有一个S，代表多个数据库
    

• 查看当前正在使用的数据库

  • SELECT DATABASE(); #使用的一个 mysql 中的全局函数
    

• 查看指定库下所有的表

  • SHOW TABLES FROM 数据库名;
    

• 查看数据库的创建信息

  • SHOW CREATE DATABASE 数据库名;
    或者：
    SHOW CREATE DATABASE 数据库名\G
    

• 使用/切换数据库

  • USE 数据库名;
    

3. 修改

• 更改数据库字符集

  • ALTER DATABASE 数据库名 CHARACTER SET 字符集; #比如：gbk、utf8等
    

• 删除指定的数据库

  • DROP DATABASE IF EXISTS 数据库名;
    

9.2 表的创建和操作

1. 创建表

• 格式

CREATE TABLE [IF NOT EXISTS] 表名(
字段1, 数据类型 [约束条件] [默认值],
字段2, 数据类型 [约束条件] [默认值],
字段3, 数据类型 [约束条件] [默认值],
……
[表约束条件]
);

# 例如
-- 创建表
CREATE TABLE emp (
-- int类型
emp_id INT,#如果是INT(6)则是显示宽度为6，默认是11，不推荐指定
-- 最多保存20个中英文字符
emp_name VARCHAR(20),
-- 总位数不超过15位
salary DOUBLE,
-- 日期类型
birthday DATE
);

# 例2
CREATE TABLE dept(
-- int类型，自增
deptno INT(2) AUTO_INCREMENT,
dname VARCHAR(14),
loc VARCHAR(13),
-- 主键
PRIMARY KEY (deptno)
);

• 使用 AS subquery 选项，将创建表和插入数据结合起来
• 指定的列和子查询中的列要一一对应
• 通过列名和默认值定义列

CREATE TABLE emp1 AS SELECT * FROM employees;
CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2; -- 创建的emp2是空表

CREATE TABLE dept80
AS
SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM employees
WHERE department_id = 80;

• 查看表结构

SHOW CREATE TABLE 表名\G

2. 修改表

• 追加一个列

# 格式
ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;
# 例子
ALTER TABLE dept80
ADD job_id varchar(15);

• 修改一个列

ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名2】;

# 例
ALTER TABLE dept80
MODIFY last_name VARCHAR(30);

# 例
ALTER TABLE dept80
MODIFY salary double(9,2) default 1000;

• 重命名

# 语法格式
ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;

• 删除一个列

ALTER TABLE 表名 DROP 【COLUMN】字段名

3. 重命名表

RENAME TABLE emp
TO myemp;
ALTER table dept
RENAME [TO] detail_dept; -- [TO]可以省略

4. 删除表

• 在MySQL中，当一张数据表没有与其他任何数据表形成关联关系时，可以将当前数据表直接删除。
• 数据和结构都被删除
• 所有正在运行的相关事务被提交
• 所有相关索引被删除

DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];

DROP TABLE 语句不能回滚

5. 清空

• TRUNCATE TABLE语句：
  • 删除表中所有的数据
  • 释放表的存储空间

TRUNCATE TABLE detail_dept;

DELETE FROM emp2;
#TRUNCATE TABLE emp2;
SELECT * FROM emp2;
ROLLBACK;
SELECT * FROM emp2;

TRUNCATE语句不能回滚，而使用 DELETE 语句删除数据，可以回滚

TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无事务且不触发 TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句。说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

10. 数据的增删改

1. 插入数据

• values方式添加

  • 值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。

  • INSERT INTO 表名
    VALUES (value1,value2,....);
    # 指定列插入数据
    NSERT INTO departments
    VALUES (70, 'Pub', 100, 1700);
    # 例2
    INSERT INTO departments(department_id, department_name)
    VALUES (80, 'IT');
    # 插入多行数据
    NSERT INTO table_name
    VALUES
    (value1 [,value2, …, valuen]),
    (value1 [,value2, …, valuen]),
    ……
    (value1 [,value2, …, valuen]);
    # 
    INSERT INTO emp2
    SELECT *
    FROM employees
    WHERE department_id = 90;
    

• VALUES 也可以写成 VALUE ，但是VALUES是标准写法。

2.更新数据

• 使用 UPDATE 语句更新数据。

• 使用 WHERE 子句指定需要更新的数据。

  • UPDATE employees
    SET department_id = 70
    WHERE employee_id = 113; # 如果省略 WHERE 子句，则表中的所有数据都将被更新。
    

3. 删除数据

• 语法：DELETE FROM table_name [WHERE ];

• 使用 WHERE 子句删除指定的记录。

  DELETE FROM departments
  WHERE department_name = 'Finance';# 如果省略 WHERE 子句，则表中的全部数据将被删除
  

4. MySQL8新特性：计算列

简单来说就是某一列的值是通过别的列计算得来的

在MySQL 8.0中，CREATE TABLE 和 ALTER TABLE 中都支持增加计算列。

CREATE TABLE tb1(
id INT,
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL
);

11. 数据类型

类型	类型举例
整数类型	TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT
浮点类型	FLOAT、DOUBLE
定点数类型	DECIMAL
位类型	BIT
日期时间类型	YEAR、TIME、DATE、DATETIME、TIMESTAMP
文本字符串类型	CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
枚举类型	ENUM
集合类型	SET
二进制字符串类型	BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JSON类型	JSON对象、JSON数组
空间数据类型	单值类型：GEOMETRY、POINT、LINESTRING、POLYGON； 集合类型：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION

常见类型的属性

MySQL关键字	含义
NULL	数据列可包含NULL值
NOT NULL	数据列不允许包含NULL值
DEFAULT	默认值
PRIMARY KEY	主键
AUTO_INCREMENT	自动递增，适用于整数类型
UNSIGNED	无符号
CHARACTER SET name	指定一个字符集

1.整数类型

整数类型	字节	有符号数取值范围	无符号数取值范围
TINYINT	1	-128~127	0~255
SMALLINT	2	-32768~32767	0~65535
MEDIUMINT	3	-8388608~8388607	0~16777215
INT、INTEGER	4	-2147483648~2147483647	0~4294967295
BIGINT	8	-9223372036854775808~9223372036854775807	0~18446744073709551615

• 可选属性：
  • M：表示显示宽度，M的取值范围是(0, 255)。例如，int(5)：当数据宽度小于5位的时候在数字前面需要用字符填满宽度。该项功能需要配合“ ZEROFILL ”使用，表示用“0”填满宽度，否则指定显示宽度无效。int 默认显示宽度为11.
  • UNSIGNED : 无符号类型（非负），所有的整数类型都有一个可选的属性UNSIGNED（无符号属性），无符号整数类型的最小取值为0。所以，如果需要在MySQL数据库中保存非负整数值时，可以将整数类型设置为无符号类型。
  • ZEROFILL : 0填充,（如果某列是ZEROFILL，那么MySQL会自动为当前列添加UNSIGNED属性），如果指定了ZEROFILL只是表示不够M位时，用0在左边填充，如果超过M位，只要不超过数据存储范围即可。

TINYINT ：一般用于枚举数据，比如系统设定取值范围很小且固定的场景。
SMALLINT ：可以用于较小范围的统计数据，比如统计工厂的固定资产库存数量等。
MEDIUMINT ：用于较大整数的计算，比如车站每日的客流量等。
INT、INTEGER ：取值范围足够大，一般情况下不用考虑超限问题，用得最多。比如商品编号。
BIGINT ：只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等。

2. 浮点数类型

浮点数和定点数类型的特点是可以处理小数，你可以把整数看成小数的一个特例。因此，浮点数和定点数的使用场景，比整数大多了。 MySQL支持的浮点数类型，分别是 FLOAT、DOUBLE、REAL。

• FLOAT 表示单精度浮点数；
• DOUBLE 表示双精度浮点数；

类型	有符号数取值范围	无符号数取值范围	占用字节数
float			4
double			8

REAL默认就是 DOUBLE。如果你把 SQL 模式设定为启用“ REAL_AS_FLOAT ”，那 么，MySQL 就认为REAL 是 FLOAT。如果要启用“REAL_AS_FLOAT”，可以通过以下 SQL 语句实现：

SET sql_mode = “REAL_AS_FLOAT”;

MySQL 存储浮点数的格式为： 符号(S) 、尾数(M) 和 阶码(E) 。因此，无论有没有符号，MySQL 的浮点数都会存储表示符号的部分。因此， 所谓的无符号数取值范围，其实就是有符号数取值范围大于等于零的部分。

因为浮点数是不准确的，所以我们要避免使用“=”来判断两个数是否相等。

3.定点数类型

MySQL中的定点数类型只有 DECIMAL 一种类型。

类型	占用字节数	范围
DECIMAL(M,D),DEC,NUMERIC	M+2字节	有效范围由M和D决定

使用 DECIMAL(M,D) 的方式表示高精度小数。其中，M被称为精度，D被称为标度。0<=M<=65，0<=D<=30，D<M。例如，定义DECIMAL（5,2）的类型，表示该列取值范围是-999.99~999.99。

• DECIMAL(M,D)的最大取值范围与DOUBLE类型一样，但是有效的数据范围是由M和D决定的。
• 定点数在MySQL内部是以字符串的形式进行存储，这就决定了它一定是精准的。
• 当DECIMAL类型不指定精度和标度时，其默认为DECIMAL(10,0)。
• 浮点数vs定点数
  • 浮点数相对于定点数的优点是在长度一定的情况下，浮点类型取值范围大，但是不精准，适用于需要取值范围大，又可以容忍微小误差的科学计算场景（比如计算化学、分子建模、流体动力学等）
  • 定点数类型取值范围相对小，但是精准，没有误差，适合于对精度要求极高的场景 （比如涉及金额计算的场景）

4. 位类型

BIT类型中存储的是二进制值，类似010110。

二进制字符串类型	长度	长度范围	占用空间
BIT(M)	M	1<=M<=64	约为(M+7)/8个字节

BIT类型，如果没有指定(M)，默认是1位。这个1位，表示只能存1位的二进制值。这里(M)是表示二进制的位数，位数最小值为1，最大值为64。

CREATE TABLE test_bit1(
f1 BIT,
f2 BIT(5),
f3 BIT(64)
);
INSERT INTO test_bit1(f1)
VALUES(1);

5. 日期与时间类型

主要有：YEAR类型、TIME类型、DATE类型、DATETIME类型和TIMESTAMP类型

• YEAR 类型通常用来表示年
• DATE 类型通常用来表示年、月、日
• TIME 类型通常用来表示时、分、秒
• DATETIME 类型通常用来表示年、月、日、时、分、秒
• TIMESTAMP 类型通常用来表示带时区的年、月、日、时、分、秒、

类型	含义	占用字节	格式	最小值	最大值
YEAR	年	1	YYYY或YY	1901	2155
TIME	时间	3	HH:MM:SS	-838:59:59	838:59:59
DATE	日期	3	YYYY-MM-DD	1000-01-01	9999-12-03
DATETIME	日期 时间	8	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00	9999-12-31 23:59:59
TIMESTAMP	日期 时间	4	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:00 UTC	2038-01-19 03:14:07UTC

TIMESTAMP和DATETIME的区别：

• TIMESTAMP存储空间比较小，表示的日期时间范围也比较小
• 底层存储方式不同，TIMESTAMP底层存储的是毫秒值，距离1970-1-1 0:0:0 0毫秒的毫秒值。
• 两个日期比较大小或日期计算时，TIMESTAMP更方便、更快。
• TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同，显示不同的结果。而DATETIME则只能反映出插入时当地 的时区，其他时区的人查看数据必然会有误差的。

6. 文本字符串类型

MySQL中，文本字符串总体上分为CHAR 、VARCHAR 、TINYTEXT 、TEXT 、MEDIUMTEXT 、LONGTEXT 、ENUM 、SET 等类型。

文本字符串类型	值的长度	长度范围	占用字节
CHAR(M)	M	0,255	M
VACHAER(M)	M	0,65535	M+1
TINYTEXT	L	0,255	L+2
TEXT	L	0,65535	L+2
MEDIUMTEXT	L	0,167777215	L+3
LONGTEXT	L	0,4294967295	L+4
ENUM	L	0,65535	1 OR 2
SET	L	0,64	1,2,3,4 OR 8

• CHAR类型：
  • CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M)，则表示长度默认是1个字符。
  • 如果保存时，数据的实际长度比CHAR类型声明的长度小，则会在右侧填充空格以达到指定的长度。当MySQL检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格。
  • 定义CHAR类型字段时，声明的字段长度即为CHAR类型字段所占的存储空间的字节数。
• VARCHAR类型：
  • VARCHAR(M) 定义时， 必须指定长度M，否则报错。
  • MySQL4.0版本以下，varchar(20)：指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节） ；MySQL5.0版本以上，varchar(20)：指的是20字符。
  • 检索VARCHAR类型的字段数据时，会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节。

类型	特点	空间	时间	适用场景
char	固定长度	浪费空间	效率高	存储不大，速度快
varchar	可变	节省	低	非

• TEXT类型

  • 由于实际存储的长度不确定，MySQL 不允许 TEXT 类型的字段做主键。遇到这种情况，你只能采用CHAR(M)，或者 VARCHAR(M)。

  • 经验：TEXT文本类型，可以存比较大的文本段，搜索速度稍慢，因此如果不是特别大的内容，建议使用CHAR，VARCHAR来代替。还有TEXT类型不用加默认值，加了也没用。而且text和blob类型的数据删除后容易导致“空洞”，使得文件碎片比较多，所以频繁使用的表不建议包含TEXT类型字段，建议单独分出去，单独用一个表。

7. ENUM枚举类型

文本字符串类型	长度	长度范围	占用的存储空间
ENUM	L	1 <= L <= 65535	1或2个字节

• 当ENUM类型包含1～255个成员时，需要1个字节的存储空间；
• 当ENUM类型包含256～65535个成员时，需要2个字节的存储空间。
• ENUM类型的成员个数的上限为65535个。

CREATE TABLE test_enum(
season ENUM('春','夏','秋','冬','unknow')
);

8.SET类型

当SET类型包含的成员个数不同时，其所占用的存储空间也是不同的，具体如下：

成员个数范围（L表示实际成员个数）	占用的存储空间
1 <= L <= 8	1个字节
9 <= L <= 16	2
17 <= L <= 24	3
。。。。	。。。。

CREATE TABLE test_set(
s SET ('A', 'B', 'C')
);

INSERT INTO test_set (s) VALUES ('A'), ('A,B');
#插入重复的SET类型成员时，MySQL会自动删除重复的成员
INSERT INTO test_set (s) VALUES ('A,B,C,A');
#向SET类型的字段插入SET成员中不存在的值时，MySQL会抛出错误。
INSERT INTO test_set (s) VALUES ('A,B,C,D');
SELECT *
FROM test_set;

12.约束

可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后通过 ALTER TABLE 语句规定约束。

1.NOT NULL 非空约束，规定某个字段不能为空

限定某个字段/某列的值不允许为空

• 特点
  • 默认，所有的类型的值都可以是NULL，包括INT、FLOAT等数据类型
  • 非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空
  • 一个表可以有很多列都分别限定了非空
  • 空字符串’'不等于NULL，0也不等于NULL

# 建表时添加约束
CREATE TABLE 表名称(
字段名 数据类型,
字段名 数据类型 NOT NULL,
字段名 数据类型 NOT NULL
);
# 建表后添加约束
alter table 表名称 modify 字段名 数据类型 not null;
# 删除约束
alter table 表名称 modify 字段名 数据类型 NULL;#去掉not null，相当于修改某个非注解字段，该字段允许为空
# 或
alter table 表名称 modify 字段名 数据类型;#去掉not null，相当于修改某个非注解字段，该字段允许为空

2.UNIQUE 唯一约束，规定某个字段在整个表中是唯一的

• 特点
  • 同一个表可以有多个唯一约束。
  • 唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。
  • 唯一性约束允许列值为空。
  • 在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。
  • MySQL会给唯一约束的列上默认创建一个唯一索引。
• 添加唯一约束

create table 表名称(
字段名 数据类型,
字段名 数据类型 unique,
字段名 数据类型 unique key,
字段名 数据类型
);
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] unique key(字段名)
);
#建表后添加唯一约束
#方式1：
alter table 表名称 add unique key(字段列表);
#方式2：
alter table 表名称 modify 字段名 字段类型 unique;
#删除唯一约束
ALTER TABLE USER
DROP INDEX uk_name_pwd;

#补充
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名'; #查看都有哪些约束
show index from 表名称; # 查看表的索引

• 关于复合唯一约束
  • 字段列表中写的是多个字段名，多个字段名用逗号分隔，表示那么是复合唯一，即多个字段的组合是唯一的

3.PRIMARY KEY 主键(非空且唯一)约束

用来唯一标识表中的一行记录。

• 特点：
  • 主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值。
  • 一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建。
  • 主键约束对应着表中的一列或者多列（复合主键）
  • 如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
  • MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用。
  • 当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据主键查询的，就根据主键查询，效率更高）。如果删除主键约束了，主键约束对应的索引就自动删除了。
  • 需要注意的一点是，不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性。

# 添加主键约束
create table 表名称(
字段名 数据类型,
字段名 数据类型 primary key,
字段名 数据类型,
);
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] primary key(字段名) #表级模式
);

# 复合主键
create table student_course(
sid int,
cid int,
score int,
primary key(sid,cid) #复合主键
);

# 删除主键
alter table 表名称 drop primary key;

4.AUTO_INCREMENT 自增列

• 特点和要求
  （1）一个表最多只能有一个自增长列
  （2）当需要产生唯一标识符或顺序值时，可设置自增长
  （3）自增长列约束的列必须是键列（主键列，唯一键列）
  （4）自增约束的列的数据类型必须是整数类型
  （5）如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值。

# 1.建表时
create table 表名称(
    字段名 数据类型 primary key auto_increment,
    字段名 数据类型 unique key not null,
    字段名 数据类型 unique key,
    字段名 数据类型 not null default 默认值,
);
create table 表名称(
    字段名 数据类型 default 默认值 ,
    字段名 数据类型 unique key auto_increment,
    字段名 数据类型 not null default 默认值,,
    primary key(字段名)
);

# 2.建表后
alter table 表名称 modify 字段名 数据类型 auto_increment;

# 3.删除
alter table 表名称 modify 字段名 数据类型; #去掉auto_increment相当于删除

5.FOREIGN KEY 外键约束

限定某个表的某个字段的引用完整性。
比如：员工表的员工所在部门的选择，必须在部门表能找到对应的部分。

• 特点
  • 从表的外键列，必须引用/参考主表的主键或唯一约束的列
  • 在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名（例如student_ibfk_1;），也可以指定外键约束名。
  • 创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表
  • 删表时，先删从表（或先删除外键约束），再删除主表
  • 当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据
  • 在“从表”中指定外键约束，并且一个表可以建立多个外键约束
  • 从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。如果类型不一样，创建子表时，就会出现错误“ERROR 1005 (HY000): Can’t create
  • 删除外键约束后，必须手动删除对应的索引

# 1.建表
create table 主表名称(
    字段1 数据类型 primary key,
    字段2 数据类型
);
create table 从表名称(
    字段1 数据类型 primary key,
    字段2 数据类型,
    [CONSTRAINT <外键约束名称>] FOREIGN KEY（从表的某个字段) references 主表名(被参考字段)
);
#(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样
#(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样
-- FOREIGN KEY: 在表级指定子表中的列
-- REFERENCES: 标示在父表中的列

# 例子
create table dept( #主表
    did int primary key, #部门编号
    dname varchar(50) #部门名称
);
create table emp(#从表
    eid int primary key, #员工编号
    ename varchar(5), #员工姓名
    deptid int, #员工所在的部门
    foreign key (deptid) references dept(did) #在从表中指定外键约束
    #emp表的deptid和和dept表的did的数据类型一致，意义都是表示部门的编号
);
说明：
（1）主表dept必须先创建成功，然后才能创建emp表，指定外键成功。
（2）删除表时，先删除从表emp，再删除主表dept

# 2.建表后
ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用
字段) [on update xx][on delete xx];
# 例子
ALTER TABLE emp1
ADD [CONSTRAINT emp_dept_id_fk] FOREIGN KEY(dept_id) REFERENCES dept(dept_id);

# 3.删除外键约束
# (1)第一步先查看约束名和删除外键约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';#查看某个表的约束名
ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;
#（2）第二步查看索引名和删除索引。（注意，只能手动删除）
SHOW INDEX FROM 表名称; #查看某个表的索引名
ALTER TABLE 从表名 DROP INDEX 索引名;

6.CHECK 检查约束

检查某个字段的值是否符号xx要求，一般指的是值的范围

CREATE TABLE temp(
id INT AUTO_INCREMENT,
NAME VARCHAR(20),
age INT CHECK(age > 20),
PRIMARY KEY(id)
);

7.DEFAULT 默认值约束

给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值。

# 1.建表时
create table 表名称(
    字段名 数据类型 primary key,
    字段名 数据类型 unique key not null,
    字段名 数据类型 unique key,
    字段名 数据类型 not null default 默认值,
);
说明：默认值约束一般不在唯一键和主键列上加

# 2.删除
alter table 表名称 modify 字段名 数据类型 ;#删除默认值约束，也不保留非空约束
alter table 表名称 modify 字段名 数据类型 not null; #删除默认值约束，保留非空约束

13.视图

• 创建视图

# 在 CREATE VIEW 语句中嵌入子查询
CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名称 [(字段列表)]
AS 查询语句
[WITH [CASCADED|LOCAL] CHECK OPTION]

# 精简版
CREATE VIEW 视图名称
AS 查询语句

# 单表
CREATE VIEW empvu80
AS
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;

# 多表
CREATE VIEW empview
AS
SELECT employee_id emp_id,last_name NAME,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id;

# 例如
CREATE VIEW emp_depart
AS
SELECT CONCAT(last_name,'(',department_name,')') AS emp_dept
FROM employees e JOIN departments d
WHERE e.department_id = d.department_id

• 查看视图

语法1：查看数据库的表对象、视图对象
SHOW TABLES;
语法2：查看视图的结构
DESC / DESCRIBE 视图名称;
语法3：查看视图的属性信息
# 查看视图信息（显示数据表的存储引擎、版本、数据行数和数据大小等）
SHOW TABLE STATUS LIKE '视图名称'\G
执行结果显示，注释Comment为VIEW，说明该表为视图，其他的信息为NULL，说明这是一个虚表。
语法4：查看视图的详细定义信息
SHOW CREATE VIEW 视图名称;

• 要使视图可更新，视图中的行和底层基本表中的行之间必须存在一对一的关系。另外当视图定义出现如下情况时，视图不支持更新操作：
  • 在定义视图的时候指定了“ALGORITHM = TEMPTABLE”，视图将不支持INSERT和DELETE操作；
  • 视图中不包含基表中所有被定义为非空又未指定默认值的列，视图将不支持INSERT操作；
  • 在定义视图的SELECT语句中使用了JOIN联合查询，视图将不支持INSERT和DELETE操作；
  • 在定义视图的SELECT语句后的字段列表中使用了数学表达式或子查询，视图将不支持INSERT，也不支持UPDATE使用了数学表达式、子查询的字段值；
  • 在定义视图的SELECT语句后的字段列表中使用DISTINCT 、聚合函数、GROUP BY 、HAVING 、UNION 等，视图将不支持INSERT、UPDATE、DELETE；
  • 在定义视图的SELECT语句中包含了子查询，而子查询中引用了FROM后面的表，视图将不支持INSERT、UPDATE、DELETE；
  • 视图定义基于一个不可更新视图；
  • 常量视图。
• 修改视图

方式1：使用CREATE OR REPLACE VIEW 子句修改视图
CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS
SELECT employee_id, first_name || ' ' || last_name, salary, department_id
FROM employees
WHERE department_id = 80;

方式2：ALTER VIEW
ALTER VIEW 视图名称
AS
查询语句

• 删除视图

删除视图只是删除视图的定义，并不会删除基表的数据。
删除视图的语法是：

DROP VIEW IF EXISTS 视图名称;

# 举例
DROP VIEW empvu80;

• 总结
  • 操作简单
  • 减少数据冗余
  • 数据安全
  • 适应灵活多变的需求
  • 能够分解复杂的查询逻辑

14.存储过程与函数

1. 创建存储过程

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
	存储过程体
END

• 参数前面的符号的意思：
  • IN ：当前参数为输入参数，也就是表示入参；存储过程只是读取这个参数的值。如果没有定义参数种类， 默认就是 IN ，表示输入参数。
  • OUT ：当前参数为输出参数，也就是表示出参；执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。
  • INOUT ：当前参数既可以为输入参数，也可以为输出参数。
• 形参类型可以是 MySQL数据库中的任意类型。
• characteristics 表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下：

LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

• • LANGUAGE SQL ：说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。
  • [NOT] DETERMINISTIC ：指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC。
  • { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } ：指明子程序使
    用SQL语句的限制。
    • CONTAINS SQL表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句；
    • NO SQL表示当前存储过程的子程序中不包含任何SQL语句；
    • READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句；
    • MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句。
    • 默认情况下，系统会指定为CONTAINS SQL。
  • SQL SECURITY { DEFINER | INVOKER } ：执行当前存储过程的权限，即指明哪些用户能够执
    行当前存储过程。
    • DEFINER 表示只有当前存储过程的创建者或者定义者才能执行当前存储过程；
    • INVOKER 表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。

• 存储过程体中可以有多条 SQL 语句，如果仅仅一条SQL 语句，则可以省略 BEGIN 和 END

1. BEGIN…END：BEGIN…END 中间包含了多个语句，每个语句都以（;）号为结束符。
2. DECLARE：DECLARE 用来声明变量，使用的位置在于 BEGIN…END 语句中间，而且需要在其他语句使用之前进
行变量的声明。
3. SET：赋值语句，用于对变量进行赋值。
4. SELECT… INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。

• 需要设置新的结束标记

DELIMITER 新的结束标记

因为MySQL默认的语句结束符号为分号‘;’。为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符。

比如：“DELIMITER //”语句的作用是将MySQL的结束符设置为//，并以“END //”结束存储过程。存储过程定义完毕之后再使用“DELIMITER ;”恢复默认结束符。DELIMITER也可以指定其他符号作为结束符。

当使用DELIMITER命令时，应该避免使用反斜杠（‘\’）字符，因为反斜线是MySQL的转义字符。

# 举例1：创建存储过程select_all_data()，查看 emps 表的所有数据
DELIMITER $
CREATE PROCEDURE select_all_data()
BEGIN
	SELECT * FROM emps;
END $
DELIMITER ;

2. 调用存储过程

• 格式

# 1、调用in模式的参数：
CALL sp1('值');

# 2、调用out模式的参数：
SET @name;
CALL sp1(@name);
SELECT @name;

# 3、调用inout模式的参数：
SET @name=值;
CALL sp1(@name);
SELECT @name;

3. 存储函数

• 语法

CREATE FUNCTION 函数名(参数名 参数类型,...)
RETURNS 返回值类型
[characteristics ...]
BEGIN
	函数体 #函数体中肯定有 RETURN 语句
END

# 调用
SELECT 函数名(实参列表)

# 例子
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
BEGIN
RETURN (SELECT email FROM employees WHERE last_name = 'Abel');
END //
DELIMITER ;
#调用
SELECT email_by_name();

• 对比

	关键字	调用语法	返回值	应用场景
存储过程	PROCEDURE	CALL 存储过程()	理解为有0个或多个	一般用于更新
存储函数	FUNCTION	SELECT 函数()	只能是一个	一般用于查询结果为一个值并返回时

4. 存储过程和函数的查看、修改、删除

• 查看

# 1. 使用SHOW CREATE语句查看存储过程和函数的创建信息
SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名
SHOW CREATE FUNCTION test_db.CountProc \G

# 2. 使用SHOW STATUS语句查看存储过程和函数的状态信息
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
这个语句返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。

# 3. 从information_schema.Routines表中查看存储过程和函数的信息
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='存储过程或函数的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];

• 修改

ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名 [characteristic ...]

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

• • CONTAINS SQL ，表示子程序包含SQL语句，但不包含读或写数据的语句。
  • NO SQL ，表示子程序中不包含SQL语句。
  • READS SQL DATA ，表示子程序中包含读数据的语句。
  • MODIFIES SQL DATA ，表示子程序中包含写数据的语句。
  • SQL SECURITY { DEFINER | INVOKER } ，指明谁有权限来执行。
    • DEFINER ，表示只有定义者自己才能够执行。
    • INVOKER ，表示调用者可以执行。
  • COMMENT ‘string’ ，表示注释信息
• 删除

# 删除存储过程和函数，可以使用DROP语句，其语法结构如下
DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名

15.

CREATE FUNCTION 函数名(参数名 参数类型,...)
RETURNS 返回值类型
[characteristics ...]
BEGIN
	函数体 #函数体中肯定有 RETURN 语句
END

# 调用
SELECT 函数名(实参列表)

# 例子
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
BEGIN
RETURN (SELECT email FROM employees WHERE last_name = 'Abel');
END //
DELIMITER ;
#调用
SELECT email_by_name();

• 对比

	关键字	调用语法	返回值	应用场景
存储过程	PROCEDURE	CALL 存储过程()	理解为有0个或多个	一般用于更新
存储函数	FUNCTION	SELECT 函数()	只能是一个	一般用于查询结果为一个值并返回时

4. 存储过程和函数的查看、修改、删除

• 查看

# 1. 使用SHOW CREATE语句查看存储过程和函数的创建信息
SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名
SHOW CREATE FUNCTION test_db.CountProc \G

# 2. 使用SHOW STATUS语句查看存储过程和函数的状态信息
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
这个语句返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。

# 3. 从information_schema.Routines表中查看存储过程和函数的信息
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='存储过程或函数的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];

• 修改

ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名 [characteristic ...]

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

• • CONTAINS SQL ，表示子程序包含SQL语句，但不包含读或写数据的语句。
  • NO SQL ，表示子程序中不包含SQL语句。
  • READS SQL DATA ，表示子程序中包含读数据的语句。
  • MODIFIES SQL DATA ，表示子程序中包含写数据的语句。
  • SQL SECURITY { DEFINER | INVOKER } ，指明谁有权限来执行。
    • DEFINER ，表示只有定义者自己才能够执行。
    • INVOKER ，表示调用者可以执行。
  • COMMENT ‘string’ ，表示注释信息
• 删除

# 删除存储过程和函数，可以使用DROP语句，其语法结构如下
DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名

大部分笔记来自尚硅谷的笔记