热搜：前端 vuedraggable 插件 vue

MySQL基础知识

lxf2023-05-17 01:58:27

MySQL笔记

MySQL基础

使用数据库存储数据的好处

  1. 可以实现数据的持久化存储
  2. 可以实现结构化查询，方便管理

数据库的相关概念

  1. DB：数据库，保存一组有组织的数据的容器

  2. DBMS：数据库管理系统，又称为数据库软件，用于管理DB中的数据。例如MySQL、SQL Server、Oracle等

  3. SQL：结构化查询语言，用于和DBMS通信的语言

    备注：SQL不是某个特定数据库供应商专有的语言，几乎所有的DBMS都支持SQL

MySQL服务的启动与停止

方式一：右键win选择计算机管理，找到mysql服务，右键进行mysql服务的启动与停止

方式二：net start/stop 服务名，本机安装的mysql的服务名就是"mysql"

MySQL服务端的登录与退出

登录：mysql -h主机名 -P端口号 -u用户名 -p密码，如果主机名为localhost，端口号为3306，则可以简化为mysql -u用户名 -p密码

退出：exit

备注：本机安装的mysql服务的用户名和密码都为root

MySQL的常见命令

备注：输入mysql命令之前，必须先登录进入mysql服务端

  1. 查看当前所有的数据库

show databases;

  1. 打开指定的库

    use <databaseName>;

  2. 查看当前库的所有表

    show tables;

  3. 查看其它库的所有表

    show tables {from|in} <databaseName>;

  4. 查看当前在哪个数据库

    select database();

  5. 查看表的索引信息

    show index from <tableName>;

  6. 查看表结构

    desc <tableName>;

  7. 查看mysql版本

    方式一：登录进mysql服务端，输入命令select version();

    方式二：不登录mysql服务端，输入命令mysql --version或者mysql -V

  8. 查看mysql支持的存储引擎

    show engines;

备注：

  1. database() 函数返回当前数据库的名称
  2. version() 函数返回mysql数据库的当前版本

MySQL语法规范

  1. 不区分大小写，但建议关键字大写，表名、列名小写

  2. 每条命令最好用分号结尾

  3. 每条命令根据需要，可以进行缩进或换行

  4. 注释

    单行注释：#注释内容或-- 注释内容

    多行注释：/* 注释内容 */

DQL（数据查询语言）

SQL查询语句完整的执行顺序

  1. from 从哪个表获取数据
  2. [joinType] join 连接其它表
  3. on 使用多表连接条件筛选符合条件的数据
  4. where 分组前筛选符合条件的数据
  5. group by 按照指定的列进行分组
  6. having 分组后筛选符合条件的数据
  7. select 选择需要查询的列
  8. distinct 去重
  9. union [all] 合并多个查询语句的结果
  10. order by 按照指定的列进行排序
  11. limit 限制返回的数据行数

基础查询

语法：SELECT 查询列表 FROM 表名;

特点：

  1. 查询列表可以是：表中的字段、常量值、表达式、函数
  2. 查询的结果是一个虚拟的表格

使用示例：

USE myemployees;
-- 查询表中的单个字段
SELECT last_name FROM employees;
-- 查询表中的多个字段
SELECT last_name, salary, email FROM employees;
-- 查询表中的所有字段
SELECT * FROM employees;
-- 查询常量值
SELECT 100;
SELECT 'john';
-- 查询表达式
SELECT 100%98;
-- 查询函数
SELECT VERSION();
-- 为字段起别名(方式一：使用AS)
SELECT last_name AS 姓, first_name AS 名 FROM employees;
SELECT `name` AS "#姓 名#" FROM `employees`;
-- 为字段起别名(方式二：使用空格)
SELECT last_name 姓, first_name 名 FROM employees
-- 使用DISTINCT关键字去重
SELECT DISTINCT department_id FROM employees;
-- MySQL中的加号
SELECT 100+90; #190
SELECT "100"+90; #190
SELECT "100"+"90"; #190
SELECT "abc"+100; #100
SELECT NULL+10; #Null
-- 使用CONCAT函数实现字符串拼接
SELECT CONCAT(last_name, " ", first_name) AS 姓名 FROM employees;
-- IFNULL函数的使用
SELECT commission_pct, IFNULL(commission_pct, 0) AS 奖金率 FROM employees;
-- ISNULL函数的使用
SELECT last_name, ISNULL(commission_pct) AS 是否有奖金 FROM employees;

备注：

  1. 最好在所有sql语句之前使用USE 库名;命令指定在哪个数据库中进行操作
  2. 查询常量值时，字符型和日期型的常量值必须用引号包裹起来，数值型不需要
  3. 如果表名或字段名是sql命令中的关键字，则应该用着重号 （``）将表名或字段名包裹起来
  4. 为字段起别名时，如果别名中含有特殊符号（#号、空格等），应该使用双引号（单引号也可以）将别名包裹起来
  5. MySQL中的加号（特别注意：只能作为运算符，不能作为连接符）：
    • 如果两个操作数都为数值型，则做加法运算
    • 只要其中一个操作符为字符型，则会试图将字符型转换为数值型。然后继续做加法运算（注意：如果字符型不能转换为数值型，则转换为0）
    • 只要其中一个操作数为NULL，则加法运算的结果肯定为Null

条件查询

语法：SELECT 查询列表 FROM 表名 WHERE 筛选条件;

筛选条件分类：

  1. 按条件表达式筛选

    条件运算符：> < = >= <= != <>

  2. 按逻辑表达式筛选

    逻辑运算符：&& || ! and or not

  3. 模糊查询

    like between and in is null is not null

使用示例：

USE myemployees;
-- 按条件表达式筛选
SELECT salary from employees WHERE salary>12000;
SELECT department_id FROM employees WHERE department_id<>90;
-- 按逻辑表达式筛选
SELECT IFNULL(commission_pct,0) AS commission_pct, salary FROM employees WHERE salary>=10000 AND salary<=20000;
SELECT department_id, salary FROM employees WHERE NOT(department_id>=90 AND department_id<=110) OR salary>=15000;
-- 模糊查询 like
SELECT last_name FROM employees WHERE last_name LIKE "%a%";
SELECT last_name FROM employees WHERE last_name LIKE "__n_l%";
SELECT last_name FROM employees WHERE last_name LIKE "_\_%"; #“\”为转义符号
SELECT last_name FROM employees WHERE last_name LIKE "_$_%" ESCAPE "$"; #使用ESCAPE关键字自定义转义符号
SELECT last_name, department_id FROM employees WHERE department_id LIKE "1__"; #department_id是int类型
-- 模糊查询 between and
SELECT employee_id FROM employees WHERE employee_id BETWEEN 100 and 120;
-- 模糊查询 in
SELECT job_id FROM employees WHERE job_id IN ('IT_PROG', 'AD_VP', 'AD_PRES');
-- 模糊查询 is null
SELECT last_name, commission_pct FROM employees WHERE commission_pct IS NULL;
-- 模糊查询 is not null
SELECT last_name, commission_pct FROM employees WHERE commission_pct IS NOT NULL;
-- 安全等于 <=>
SELECT last_name, commission_pct FROM employees WHERE commission_pct <=> NULL;
SELECT first_name, salary FROM employees WHERE salary<=>12000;

备注：

  1. 对于like关键字，通常和通配符搭配使用。而且既可以判读字符型，也可以判断数值型

    通配符：

    • % 表示任意多个字符，包含0个
    • _ 表示任意单个字符

  2. 对于 between and 关键字，两个临界值不能调换顺序，且查询的结果中包含临界值

  3. 对于in关键字，匹配列表中不能使用通配符

  4. 不能使用 = 和 <> 判断是否是 null，而应该使用 is null 和 is not null

  5. 安全等于（<=>）既可以判断null，也可以判断普通数值类型。但由于可读性较差，所以不推荐使用

排序查询

语法：SELECT 查询列表 FROM 表名 [WHERE 筛选条件] ORDER BY 排序列表 [ASC|DESC];

特点：

  1. asc代表升序，desc代表降序，如果不写，默认为升序
  2. order by子句可以支持单个字段、多个字段、表达式、函数、别名
  3. order by子句一般放在查询语句中所有子句的最后面，limit子句除外

使用示例：

USE myemployees;
-- 基础排序
SELECT last_name, salary FROM employees ORDER BY salary DESC;
-- 添加筛选条件
SELECT last_name, department_id, hiredate FROM employees WHERE department_id>=90 ORDER BY hiredate ASC;
-- 按表达式排序
SELECT last_name, salary*12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
-- 按别名排序
SELECT last_name, salary * 12 * (1 + IFNULL(commission_pct, 0)) 年薪 FROM employees ORDER BY 年薪 DESC;
-- 按函数排序
SELECT last_name, LENGTH(last_name) AS 字节长度 FROM employees ORDER BY LENGTH(last_name) DESC;
-- 按多个字段排序
SELECT last_name, salary, employee_id FROM employees ORDER BY salary ASC, employee_id DESC;

常见函数

分类：

  1. 单行函数

    • 字符函数

      • length 获取参数值的字节个数
      • concat 字符串拼接
      • upper、lower 转换大小写
      • substr、substring 字符串截取
      • instr 返回子串第一次出现的索引，如果找不到，返回0
      • trim 去空格
      • lpad、rpad 使用指定字符左/右填充到指定长度
      • replace 字符串替换（全部替换）

    • 数学函数

      • round 四舍五入
      • ceil 向上取整，返回>=该参数的最小整数
      • floor 向下取整，返回<=该参数的最大整数
      • truncate 截断
      • mod 取余
      • rand 返回一个 [0,1) 之间的随机数

    • 日期函数

      • now 返回当前系统日期+时间
      • curdate 返回当前系统日期，不包含时间
      • curtime 返回当前系统时间，不包含日期
      • year、month、day、hour、minute、second 提取日期/时间的指定部分
      • dayname 返回指定日期的工作日名称
      • monthname 返回指定日期的月份名称
      • str_to_date 将日期格式的字符转换成指定格式的日期
      • date_format 将日期转换为字符
      • datediff 返回两个日期之间的天数

    • 其他函数

      • version 返回当前数据库服务器版本
      • database 返回当前打开的数据库
      • user 返回当前用户
      • md5 返回给定字符的md5加密结果

    • 流程控制函数

      • if(逻辑表达式, 表达式1, 表达式2)

        若逻辑表达式的值为真，则返回表达式1的值；否则，返回表达式2的值

      • case

        用法一：类似于编程语言中的switch case语句

        case 要判断的字段或表达式
when 常量1 then 值1
...
when 常量n then 值n
[else 值m]
end

        用法二：类似于编程语言中的多重if else判断

        case
when 条件1 then 值1
...
when 条件n then 值n
[else 值m]
end

  2. 分组函数（又称为统计函数、聚合函数、组函数，用于统计）

    • sum 求和
    • avg 求平均值
    • max 求最大值
    • min 求最小值
    • count 计算个数

    特点：

    1. sum、avg函数一般用于处理数值型，max、min、count函数可以处理任何类型
    2. 以上5个分组函数在计算时都会忽略null值
    3. 以上5个分组函数都可以与distinct关键字搭配使用实现去重的运算
    4. 一般使用count(*)或count(1)统计行数
    5. 和分组函数一同查询的字段要求是group by后的字段

调用：SELECT 函数名(实参列表) [FROM 表名];

使用示例：

USE myemployees;
-- length
SELECT LENGTH("张三丰hahaha"); #15
-- concat
SELECT CONCAT(last_name, '_', first_name) FROM employees;
-- upper、lower
SELECT CONCAT(UPPER(last_name), '_', LOWER(first_name)) FROM employees;
-- substr/substring 截取从指定索引后面的所有字符
SELECT SUBSTR("李莫愁爱上了陆展元", 7); #陆展元
SELECT SUBSTR("李莫愁爱上了陆展元" FROM 7); #陆展元
SELECT SUBSTRING("李莫愁爱上了陆展元", 7); #陆展元
SELECT SUBSTRING("李莫愁爱上了陆展元" FROM 7); #陆展元
-- substr/substring 截取从指定索引处指定字符长度的字符
SELECT SUBSTR("李莫愁爱上了陆展元", 1, 3); #李莫愁
SELECT SUBSTR("李莫愁爱上了陆展元" FROM 1 FOR 3); #李莫愁
SELECT SUBSTRING("李莫愁爱上了陆展元", 1, 3); #李莫愁
SELECT SUBSTRING("李莫愁爱上了陆展元" FROM 1 FOR 3); #李莫愁
-- instr
SELECT INSTR("殷六侠杨不悔爱上了殷六侠", "殷六侠"); #1
SELECT INSTR("杨不悔爱上了殷六侠", "殷八侠"); #0
-- trim
SELECT LENGTH(TRIM("   hello   ")); #5
SELECT TRIM('a' FROM 'aaa你aaa好aaa'); #你aaa好
-- lpad、rpad
SELECT LPAD("殷素素", 8, "*"); #*****殷素素
SELECT RPAD("殷素素", 8, 'ab'); #殷素素ababa
-- replace
SELECT REPLACE("张无忌爱上了周芷若，周芷若", '周芷若', '赵敏'); #张无忌爱上了赵敏，赵敏
-- round
SELECT ROUND(1.65), ROUND(1.45), ROUND(-1.65), ROUND(-1.45); #2 1 -2 -1
SELECT ROUND(1.567, 2); #1.57
-- ceil
SELECT CEIL(1.002), CEIL(1.00), CEIL(-3.2); #2 1 -3
-- floor
SELECT FLOOR(1.99), FLOOR(1.00), FLOOR(-3.2); #1 1 -4
-- truncate
SELECT TRUNCATE(1.69, 1); #1.6
-- mod
SELECT MOD(10, 3), MOD(-10, 3); #1 -1
-- rand
SELECT RAND();
-- now
SELECT NOW(); #2023-03-14 12:32:36
-- curdate
SELECT CURDATE(); #2023-03-14
-- curtime
SELECT CURTIME(); #12:32:36
-- year、month、day、hour、minute、second
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW()), HOUR(NOW()), MINUTE(NOW()), SECOND(NOW()); #2023 3 14 12 54 32
-- monthname
SELECT MONTHNAME(NOW()); #March
-- dayname
SELECT DAYNAME(NOW()); #Tuesday
-- str_to_date
SELECT STR_TO_DATE('1992-4-3', '%Y-%c-%d'); #1992-04-03
SELECT * FROM employees WHERE hiredate = STR_TO_DATE("4-3 1992", "%c-%d %Y");
-- date_format
SELECT DATE_FORMAT(NOW(), "%c月%d日 %y年"); #3月14日 23年
-- datediff
SELECT DATEDIFF('2023-3-14', '2023-3-13'), DATEDIFF('2023-3-13', '2023-3-14'); #1 -1
-- 其它函数
SELECT VERSION(); #8.0.19
SELECT DATABASE(); #myemployees
SELECT USER(); #root@localhost
SELECT MD5('xfy'); #b8582063bca37121daca9984236c501a
-- if
SELECT last_name, commission_pct, if(commission_pct IS NULL, '没奖金', '有奖金') AS 备注 FROM employees;
-- case 用法一
SELECT last_name, department_id, salary 原工资,
CASE department_id 
WHEN 30 THEN salary * 1.1 
WHEN 40 THEN salary * 1.2 
WHEN 50 THEN salary * 1.3 
ELSE salary 
END AS 新工资 
FROM employees;
-- case 用法二
SELECT last_name, salary,
CASE
WHEN salary > 20000 THEN 'A'
WHEN salary > 15000 THEN 'B'
WHEN salary > 10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;
-- 分组函数的使用
-- 基础使用
SELECT SUM(salary), AVG(salary), MAX(salary), MIN(salary), COUNT(salary) FROM employees;
-- max、max、count可以处理任意类型
SELECT MAX(last_name), MIN(last_name), MAX(hiredate), MIN(hiredate) FROM employees;
-- sum、avg、max、min、count都会忽略null值
SELECT SUM(commission_pct), AVG(commission_pct), MAX(commission_pct), MIN(commission_pct), COUNT(commission_pct) FROM employees;
-- 与DISTINCT关键字搭配使用
SELECT SUM(DISTINCT salary), COUNT(DISTINCT salary) FROM employees;
-- 使用count(*)和count(1)统计行数
SELECT COUNT(*), COUNT(1) FROM employees; #107 107
SELECT COUNT(*), COUNT(1) FROM employees WHERE commission_pct IS NOT NULL; #35 35

备注：

  1. 特别注意，SQL中的索引从 1 开始

  2. 常用格式符：

    格式符功能
    %Y四位的年份
    %y两位的年份
    %m月份（01, 02, ..., 11, 12）
    %c月份（1, 2, ..., 11, 12）
    %d日（01, 02, ..., 30, 31）
    %H小时（24小时制）（00, 01, ..., 22, 23）
    %h小时（12小时制）（00, 01, ..., 11, 12）
    %i分钟（00, 01, ..., 58, 59）
    %s秒（00, 01, ..., 58, 59）

分组查询

语法：SELECT 查询列表 FROM 表名 [WHERE 筛选条件] GROUP BY 分组列表 [ORDER BY 排序列表 [ASC|DESC]];

注意：

  1. 查询列表比较特殊，要求是分组函数和group by后的字段

  2. 分组查询中的筛选条件分为两类：

    筛选条件关键字位置数据源
    分组前筛选条件wheregroup by子句的前面原始表
    分组后筛选条件havinggroup by子句的后面分组后的结果集

    备注：

    • where子句中不能使用分组函数，所以分组函数作为条件肯定是放在having子句中
    • 既能使用分组前筛选，又能使用分组后筛选的（例如group by后的字段），优先使用分组前筛选（效率更高）

  3. 在MySQL中，where子句不能使用字段别名，group by子句、having子句、order by子句可以使用字段别名

  4. group by子句中的分组列表也可以是多个字段（各字段之间无顺序要求）和表达式（用的较少）

  5. 根据多个字段进行分组时（这里以2个为例：字段a和字段b），对于row1和row2，满足条件row1.a=row2.a and row1.b=row2.b时，row1和row2会被划分到同一组；满足条件row1.a<>row2.a or row1.b<>row2.b时，row1和row2会被划分到不同的组

使用示例：

USE myemployees;
-- 简单使用
SELECT job_id, MAX(salary) FROM employees GROUP BY job_id;
SELECT location_id, COUNT(*) FROM departments GROUP BY location_id;
-- 添加分组前的筛选条件
SELECT department_id, AVG(salary) FROM employees WHERE email LIKE "%a%" GROUP BY department_id;
SELECT manager_id, MAX(salary) FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id;
-- 添加分组后的筛选条件
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) > 2;
SELECT job_id, MAX(salary) AS 最高工资 FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING 最高工资 > 12000;
SELECT manager_id, MIN(salary) AS 最低工资 FROM employees WHERE manager_id > 102 GROUP BY manager_id HAVING 最低工资 > 5000;
-- 按函数或表达式分组
SELECT LENGTH(last_name) len_name, COUNT(*) 员工个数 FROM employees GROUP BY len_name HAVING 员工个数 > 5;
-- 按多个字段分组
SELECT department_id, job_id, AVG(salary) FROM employees GROUP BY department_id, job_id;
-- 添加排序
SELECT department_id, job_id, AVG(salary) 平均工资 FROM employees WHERE NOT department_id <=> NULL GROUP BY department_id, job_id HAVING 平均工资 > 10000 ORDER BY 平均工资 DESC;

连接查询

笛卡尔乘积：当查询多个表时，没有添加有效的连接条件，导致多个表的所有行实现完全连接

分类：

  1. 按功能分类：

    • 内连接（☆）
      • 等值连接
      • 非等值连接
      • 自连接
    • 外连接
      • 左外连接（☆）
      • 右外连接（☆）
      • 全外连接
    • 交叉连接

  2. 按年代分类：

    • sql92 标准：仅仅支持内连接

    • sql99 标准【推荐】：支持内连接+外连接（左外、右外）+交叉连接

sql92标准

特点：

  1. 为了防止多个表之间的字段名重复，通常会为表起别名
  2. from子句后面的多个表无顺序要求
  3. n表连接至少需要n-1个连接条件
  4. 等值连接的结果集是多表的交集部分

使用示例：

USE myemployees;
-- 等值连接
-- 1. 基本使用
SELECT last_name, department_name
FROM employees, departments 
WHERE employees.department_id = departments.department_id;
-- 2. 为表起别名（起别名后，就不能再使用原始的表名去限制字段了，例如employee.last_name这种写法是错误的）
SELECT e.last_name, e.job_id, j.job_title 
FROM employees AS e, jobs j 
WHERE e.job_id = j.job_id;
-- 3. 添加筛选条件
SELECT d.department_name, l.city
FROM departments d, locations l
WHERE d.location_id = l.location_id AND l.city LIKE "_o%";
-- 4. 添加分组
SELECT city, COUNT(*) AS 部门个数
FROM locations l, departments d
WHERE l.location_id = d.location_id
GROUP BY city;
-- 5. 添加排序
SELECT job_title, COUNT(*) AS 员工个数
FROM jobs j, employees e
WHERE j.job_id = e.job_id
GROUP BY job_title
ORDER BY 员工个数 DESC;
-- 6. 三表连接
SELECT last_name, department_name, city
FROM employees AS e, departments AS d, locations AS l
WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND city LIKE "s%"
ORDER BY department_name DESC;
-- 非等值连接
SELECT salary, grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal
ORDER BY grade_level;
-- 自连接
SELECT e1.last_name 员工名, e2.last_name 领导名
FROM employees AS e1, employees AS e2
WHERE e1.manager_id = e2.employee_id;

sql99标准

语法：

SELECT 查询列表
FROM 表1 [[AS] 别名]
连接类型 JOIN 表2 [[AS] 别名] ON 连接条件
...
连接类型 JOIN 表n [[AS] 别名] ON 连接条件
[WHERE 分组前筛选条件]
[GROUP BY 分组列表]
[HAVING 分组后筛选条件]
[ORDER BY 排序列表];

备注：各种连接方式的连接类型如下表：

连接方式连接类型（关键字）
内连接[inner]
左外连接left [outer]
右外连接right [outer]
全外连接full [outer]
交叉连接[cross]

分类：

  1. 内连接：

    特点：

    1. inner 可以省略
    2. 和 sql92 语法中的等值连接一样，查询结果都是多表的交集部分

    使用示例：

    USE myemployees;
-- 等值连接
-- 1. 基本使用
SELECT last_name, department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;
-- 2. 添加筛选
SELECT last_name, job_title
FROM employees AS e
INNER JOIN jobs AS j
ON e.job_id = j.job_id
WHERE last_name LIKE "%e%";
-- 3. 添加分组
SELECT city, COUNT(*)
FROM locations l
INNER JOIN departments d ON l.location_id = d.location_id
GROUP BY city
HAVING COUNT(*) > 3;
-- 4. 添加排序
SELECT department_name, COUNT(*)
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY department_name
HAVING COUNT(*) > 3
ORDER BY COUNT(*) DESC;
-- 5. 三表连接
SELECT last_name, department_name, job_title
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN jobs j ON e.job_id = j.job_id
ORDER BY department_name DESC;
-- 非等值连接
SELECT grade_level, COUNT(*)
FROM employees e
INNER JOIN job_grades j ON e.salary BETWEEN j.lowest_sal AND j.highest_sal
GROUP BY grade_level
HAVING COUNT(*) > 20
ORDER BY grade_level DESC;
-- 自连接
SELECT e1.last_name AS 姓名, e2.last_name AS 上级姓名
FROM employees e1
INNER JOIN employees e2 ON e1.manager_id = e2.employee_id
WHERE e1.last_name LIKE "%k%";

  2. 外连接：

    特点：

    1. outer 可以省略
    2. 外连接的查询结果为主表中的所有记录。根据连接条件，如果从表中有和主表匹配的，则显示匹配的值；如果从表中没有和主表匹配的，则显示 null
    3. 对于左外连接，左边表是主表；对于右外连接，右边表是主表；对于全外连接（mysql不支持），左边表和右边表都是主表
    4. 对于左外连接和右外连接，通过交换两个表的顺序，可以实现同样的效果

    使用示例：

    USE girls;
-- 查询没有男朋友的女神名（左外连接）
SELECT b.name
FROM beauty b
LEFT JOIN boys bo ON b.boyfriend_id = bo.id
WHERE bo.id IS NULL;
-- 查询没有男朋友的女神名（右外连接）
SELECT b.name
FROM boys bo
RIGHT JOIN beauty b ON b.boyfriend_id = bo.id
WHERE bo.id IS NULL;
USE myemployees;
-- 查询没有员工的部门名（左外连接）
SELECT department_name
FROM departments AS d
LEFT OUTER JOIN employees AS e ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;
-- 查询没有员工的部门名（右外连接）
SELECT department_name
FROM employees AS e
RIGHT OUTER JOIN departments AS d ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;

  3. 交叉连接：

    特点：

    1. cross 可以省略
    2. 没有连接条件，查询结果为多表的笛卡尔乘积

    使用示例：

    USE girls;
SELECT * FROM beauty CROSS JOIN boys;
SELECT * FROM beauty JOIN boys;

子查询

概念：出现在其它语句内部的select语句，称为子查询或内查询；内部嵌套其它select语句的查询，称为主查询或外查询

分类：

  1. 按结果集的行列数分类：
    1. 标量子查询（结果集为一行一列）
    2. 列子查询（结果集为多行一列）
    3. 行子查询（结果集为一行多列）
    4. 表子查询（结果集为多行多列）
  2. 按出现的位置分类：
    1. 出现在select后面
    2. 出现在from后面
    3. 出现在where或having后面
    4. 出现在exists后面（相关子查询）

备注：子查询要放在小括号内

出现在select后面

特点：仅仅支持标量子查询

USE myemployees;
SELECT
	d.*, (SELECT COUNT(*) FROM employees e WHERE e.department_id = d.department_id)
FROM
	departments d;

出现在from后面

特点：支持表子查询

USE myemployees;
SELECT
	a.*, b.grade_level
FROM
	(SELECT department_id, AVG(salary) AS 平均工资 FROM employees GROUP BY department_id) AS a
	INNER JOIN job_grades AS b ON a.平均工资 BETWEEN b.lowest_sal AND b.highest_sal;

出现在where或having后面

特点：

  1. 支持标量子查询、列子查询和行子查询

  2. 单行子查询一般搭配单行操作符（>、<、>=、<=、=、<>）使用；多行子查询一般搭配多行操作符（见下表）使用

    多行操作符含义
    in / not in等于 / 不等于子查询返回列表中的任意一个
    any / some和子查询返回列表中的某一个值比较
    all和子查询返回列表中的所有值比较

支持的子查询：

  1. 标量子查询（也称为单行子查询）（☆）

    USE myemployees;
-- 案例一
SELECT
	last_name, salary
FROM
	employees
WHERE
	salary > (SELECT salary FROM employees WHERE last_name = 'Abel');
-- 案例二
SELECT
	last_name, job_id, salary
FROM
	employees
WHERE
	job_id = (SELECT job_id FROM employees WHERE employee_id = 141)
	AND	salary > (SELECT salary FROM employees WHERE employee_id = 143);
-- 案例三
SELECT
	last_name, job_id, salary
FROM
	employees
WHERE
	salary = (SELECT MIN(salary) FROM employees);
-- 案例四
SELECT
	department_id, MIN(salary)
FROM
	employees
GROUP BY
	department_id
HAVING
	MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 50);

  2. 列子查询（也称为多行子查询）（☆）

    USE myemployees;
-- 案例一（第一种写法）
SELECT
	last_name
FROM
	employees
WHERE
	department_id in (SELECT department_id FROM departments WHERE location_id in (1400, 1700));
-- 案例一（第二种写法）
SELECT
	last_name
FROM
	employees
WHERE
	department_id = ANY (SELECT department_id FROM departments WHERE location_id in (1400, 1700));
-- 案例二（第一种写法）
SELECT
	employee_id, last_name, job_id, salary
FROM
	employees
WHERE
	salary < ANY (SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG')
	AND job_id <> 'IT_PROG';
-- 案例二（第二种写法）
SELECT
	employee_id, last_name, job_id, salary
FROM
	employees
WHERE
	salary < (SELECT MAX(salary) FROM employees WHERE job_id = 'IT_PROG')
	AND job_id <> 'IT_PROG';
-- 案例三（第一种写法）
SELECT
	employee_id, last_name, job_id, salary
FROM
	employees
WHERE
	salary < ALL (SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG')
	AND job_id <> 'IT_PROG';
-- 案例三（第二种写法）
SELECT
	employee_id, last_name, job_id, salary
FROM
	employees
WHERE
	salary < (SELECT MIN(salary) FROM employees WHERE job_id = 'IT_PROG')
	AND job_id <> 'IT_PROG';

  3. 行子查询

    USE myemployees;
SELECT
	*
FROM
	employees
WHERE
	(employee_id, salary) = (SELECT MIN(employee_id), MAX(salary) FROM employees);

出现在exists后面（相关子查询）

特点：支持表子查询

USE myemployees;
SELECT EXISTS (select * FROM employees WHERE salary = 24000); #1
SELECT EXISTS (select * FROM employees WHERE salary = 30000); #0
-- 查询有员工的部门名（写法一）
SELECT
	department_name
FROM
	departments d
WHERE
	EXISTS (SELECT * FROM employees e WHERE e.department_id = d.department_id);
-- 查询有员工的部门名（写法二）
SELECT
	department_name
FROM
	departments
WHERE
	department_id IN (SELECT department_id FROM employees);
USE girls;
-- 查询没有女朋友的男神名（写法一）
SELECT
	boyName
FROM
	boys bo
WHERE
	NOT EXISTS (SELECT * FROM beauty be WHERE bo.id = be.boyfriend_id);
-- 查询没有女朋友的男神名（写法二）
SELECT
	boyName
FROM
	boys
WHERE
	id NOT IN (SELECT boyfriend_id FROM beauty);

分页查询

应用场景：当要显示的数据一页显示不全时，就需要使用分页查询提交 sql 请求

语法：

SELECT [DISTINCT] 查询列表
[FROM 表1
[joinType] JOIN 表2
ON 连接条件
WHERE 分组前筛选条件
GROUP BY 分组列表
HAVING 分组后筛选条件
ORDER BY 排序列表]
LIMIT offset, size;
-- offset为要显示条目的起始索引（特别注意：这里的起始索引从1开始，和函数中的起始索引不同），size为要显示的条目个数

备注：

  1. limit 语句应该放在查询语句的最后面
  2. 若起始索引为0，则 offset 可以省略
  3. 要显示的页数为pageNum，每页的条目数为pageSize，则有这样的 sql 公式：limit (pageNum-1)*pageSize, pageSize

使用示例：

USE myemployees;
SELECT * FROM employees LIMIT 5;
SELECT * FROM employees LIMIT 10, 15;
SELECT * FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary DESC LIMIT 10;

union联合查询

作用：合并多条查询语句的结果

语法：

查询语句1
UNION 查询语句2
...
UNION 查询语句n;

特点：

  1. 多条查询语句的查询列数必须相同且查询的字段应该一一对应
  2. 查询结果中每一列的字段名以第一条查询语句为准
  3. 使用 union 关键字合并多条查询语句的结果时，默认会进行去重处理。若不想去重，则应该使用 union all 关键字

使用示例：

USE myemployees;
SELECT last_name a, email b, department_id c FROM employees WHERE email LIKE '%a%'
UNION
SELECT last_name d, email e, department_id f FROM employees WHERE department_id > 90;
USE demo1;
SELECT `username`, `password` FROM `user` WHERE `status` = 0
UNION ALL
SELECT `username`, `password` FROM `user2` WHERE `status` = 2;

DML（数据操作语言）

插入

语法：

  1. INSERT INTO tableName (col1, col2, ..., coln) VALUES (val1, val2, ..., valn);
  2. INSERT INTO tableName SET col1 = val1, col2 = val2, ..., coln = valn;

特点：

  1. 插入值的类型要与表中各个字段的类型一致或兼容
  2. 插入的列数与值的个数必须一致
  3. 对于不能为null的列，必须插入值；对于可以为null的列，不插入值有两种写法，见代码示例
  4. (col1, col2, ..., coln)的顺序可以调换，但要与(val1, val2, ..., valn)保持一一对应
  5. 可以省略列名，默认所有列，而且列的顺序与表中列的顺序一致
  6. 第一种插入方式支持插入多行，第二种不支持
  7. 第一种插入方式支持子查询，第二种不支持

使用示例：

USE girls;
-- 第一种插入方式
-- 为可以为null的列不插入值（方式一）
INSERT INTO beauty (id, name, sex, borndate, phone, photo, boyfriend_id)
VALUES (13, '唐艺昕', '女', '1990-4-23', '15652785965', NULL, 2);
-- 为可以为null的列不插入值（方式二）
INSERT INTO beauty (id, name, phone)
VALUES (14, '金星', '110');
-- 可以调换插入的顺序
INSERT INTO beauty (name, sex, id, phone) VALUES ('蒋欣', '女', 15, '120');
-- 可以省略列名
INSERT INTO beauty VALUES (16, '关晓彤', '女', '1994-5-18', '13968741528', NULL, 2);
-- 支持插入多行
INSERT INTO beauty
VALUES (18, '关晓彤2', '女', '1994-5-18', '13968741528', NULL, 2),
(19, '关晓彤3', '女', '1994-5-18', '13968741528', NULL, 2);
-- 支持子查询
INSERT INTO beauty (id, name, phone) (SELECT 20, '宋茜', '12306');
INSERT INTO beauty (id, name, phone) SELECT 21, '古力娜扎', '15652729608';
-- 第二种插入方式
INSERT INTO beauty SET id = 17, name = '刘涛', phone = '119';

修改

分类：

  1. 修改单表的记录（☆）

    语法：UPDATE tableName SET col1 = val1, col2 = val2, ..., coln = valn WHERE 筛选条件;

  2. 修改多表的记录

    语法：

    1. sql92标准：

      UPDATE 表1 别名, 表2 别名
SET col1 = val1, col2 = val2, ..., coln = valn
WHERE 连接条件 AND 筛选条件;

    2. sql99标准：

      UPDATE 表1 别名
连接类型 JOIN 表2 别名 ON 连接条件
SET col1 = val1, col2 = val2, ..., coln = valn
WHERE 筛选条件;

使用示例：

USE girls;
UPDATE boys SET boyName = '张飞' WHERE id = 2;
UPDATE beauty SET phone = 1234567, boyfriend_id = 3 WHERE name LIKE '唐%';
UPDATE beauty be, boys bo
SET phone = 114
WHERE be.boyfriend_id = bo.id AND boyName = '张无忌';
UPDATE boys bo RIGHT JOIN beauty be ON be.boyfriend_id = bo.id
SET boyfriend_id = 2
WHERE bo.id IS NULL;

删除

方式一（delete）：

  1. 单表删除（☆）

    语法：DELETE FROM tableName WHERE 筛选条件 [[ORDER BY 排序列表 [ASC|DESC]] LIMIT 条目数];

  2. 多表删除

    语法：

    1. sql92标准：

      DELETE aliasList
FROM 表1 别名1, 表2 别名2
WHERE 连接条件 AND 筛选条件;

    2. sql99标准：

      DELETE aliasList
FROM 表1 别名1
连接类型 JOIN 表2 别名2 ON 连接条件
WHERE 筛选条件;

    备注：aliasList 为别名列表，表示进行多表删除时，要删除哪些表的记录，多个别名用逗号隔开

使用示例：

USE girls;
DELETE FROM beauty WHERE phone LIKE "%9";
DELETE be
FROM beauty be INNER JOIN boys bo ON be.boyfriend_id = bo.id
WHERE bo.boyName = '张无忌';
DELETE be, bo
FROM beauty be INNER JOIN boys bo ON be.boyfriend_id = bo.id
WHERE bo.boyName = '黄晓明';
DELETE FROM beauty WHERE boyfriend_id = 2 LIMIT 2;
DELETE FROM beauty WHERE boyfriend_id = 2 ORDER BY id DESC LIMIT 1;

方式二（truncate）：

语法：TRUNCATE TABLE tableName;

使用示例：

USE girls;
-- 清空boys表
TRUNCATE TABLE boys;

备注：delete 与 truncate 的对比：

  1. delete可以通过where关键字添加筛选条件来指定删除哪些记录行，而truncate不可以。truncate就是将表清空
  2. 清空表时，对于truncate table tableName和delete from tableName，前者的效率更高
  3. 如果要删除的表中有自增长列。那么使用delete删除后，插入数据的自增长列的值从断点开始；而使用truncate删除后，插入数据的自增长列的值从1开始
  4. delete删除有返回值（返回受到影响的记录行数），而truncate删除没有返回值
  5. delete删除可以回滚，而truncate删除不能回滚

DDL（数据定义语言）

库的管理

-- 创建数据库
CREATE DATABASE books;
-- 如果数据库不存在，则创建库
CREATE DATABASE IF NOT EXISTS books;
-- 创建库时指定字符集
CREATE DATABASE IF NOT EXISTS books CHARACTER SET utf8;
-- 更改数据库的字符集
ALTER DATABASE books CHARACTER SET gbk;
-- 删除数据库
DROP DATABASE books;
-- 如果数据库存在，则删除库
DROP DATABASE IF EXISTS books;

表的管理

  1. 表的创建

    语法：

    CREATE TABLE [IF NOT EXISTS] tableName(
    列名 列类型[(长度) 列级约束],
    列名 列类型[(长度) 列级约束],
    ......
    列名 列类型[(长度) 列级约束][,
    表级约束,
    ......
    表级约束]
);

    使用示例：

    -- 创建表
CREATE TABLE book(
	id INT,
	bName VARCHAR(20),
	price DOUBLE,
	authorId INT,
	publishDate DATETIME
);
-- 如果表不存在，则创建表
CREATE TABLE IF NOT EXISTS author(
	id INT,
	au_name VARCHAR(20),
	nation VARCHAR(10)
);

  2. 表的修改

    语法：

    -- 修改列名
ALTER TABLE 表名 CHANGE [COLUMN] 原列名 新列名 新列类型;
-- 修改列类型或约束
ALTER TABLE 表名 MODIFY [COLUMN] 列名 列类型 [约束];
-- 添加新列
ALTER TABLE 表名 ADD [COLUMN] 列名 列类型 [FIRST|AFTER 字段名];
-- 删除列
ALTER TABLE 表名 DROP [COLUMN] 列名;
-- 修改表名
ALTER TABLE 旧表名 RENAME [TO] 新表名;

    使用示例：

    ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME;
ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP NOT NULL;
ALTER TABLE author ADD COLUMN annual DOUBLE;
ALTER TABLE book ADD COLUMN t1 INT FIRST;
ALTER TABLE book ADD COLUMN t2 INT AFTER id;
ALTER TABLE author DROP COLUMN annual;
ALTER TABLE author RENAME TO book_author;

  3. 表的删除

    语法：

    DROP TABLE [IF EXISTS] 表名;

    使用示例：

    -- 删除表
DROP TABLE book_author;
-- 如果表存在，则删除表
DROP TABLE IF EXISTS book_author;

  4. 表的复制

    -- 仅仅复制表的结构（所有字段）
CREATE TABLE copy LIKE author;
-- 复制表的结构+全部数据
CREATE TABLE copy2
SELECT * FROM author;
-- 复制表的结构+部分数据
CREATE TABLE copy3
SELECT id, au_name FROM author WHERE nation = '中国';
-- 仅仅复制表的部分字段
CREATE TABLE copy4
SELECT id, au_name FROM author WHERE 0;

常见数据类型

数值型

  1. 整型

    tinyint、smallint、mediumint、int/integer、bigint

    特点：

    • 默认是有符号的（可以插入负数），可以使用 unsigned 关键字设置为无符号的（不能插入负数）
    • 可以在类型后面添加 zerofill 关键字，表示当数字长度 < 显示长度（在创建表时可以为类型加上小括号来指定长度，如果不写，会有默认长度）时，在数字左边用 0 填充。值得注意的是：当添加 zerofill 关键字时，mysql 还会自动加上 unsigned 关键字

  2. 浮点型和定点型

    float、double、decimal/dec

    特点：

    • 可以在类型后面加上(M,D)来指定精度。M 代表整数部分的长度 + 小数部分的长度，D 代表小数部分的长度
    • 如果不写(M,D)，对于 float 和 double，会按实际的精度来显示；而对于 decimal/dec，M 默认是 10，D 默认是 0
    • 对于精度要求较高的运算（例如货币运算），推荐使用 decimal/dec

字符型

  1. 非二进制（字符）型

    类型名称含义
    CHAR[(M)]固定长度的非二进制串
    VARCHAR(M)可变长度的非二进制串
    TINYTEXT非常小型的非二进制串
    TEXT小型非二进制串
    MEDIUMTEXT中等大小的非二进制串
    LONGTEXT大型非二进制串

  2. 二进制（字节）型

    类型名称含义
    BINARY[(M)]固定长度的二进制串
    VARBINARY(M)可变长度的二进制串
    TINYBLOB非常小型的BLOB（二进制大对象）
    BLOB小型BLOB
    MEDIUMBLOB中型BLOB
    LONGBLOB大型BLOB

特点：

  1. M 表示最大长度，非二进制串以字符为单位，二进制串以字节为单位。对于 char 和 binary，M 是可选的，默认为 1；而对于 varchar 和 varbinary，M 是必需的
  2. char 和 binary 都是固定长度的类型，MySQL 将为每个值分配同样数量的存储空间，并且会对那些长度不足最大长度的值进行补齐：
    • 对于 char 类型，使用空格补齐
    • 对于 binary 类型，使用 0x00 补齐
  3. char 与 varchar 的主要区别：
    • char 是一种长度固定的类型，而 varchar 是一种长度可变的类型（这一点同样适用于 binary 与 varbinary）
    • 对于 char 列，尾部空格会被移除，而对于 varchar 列，尾部空格会被保留

日期/时间型

类型名称含义占用空间格式
DATE日期3个字节YYYY-mm-dd
TIME时间3个字节HH:ii:ss
YEAR年份1个字节YYYY
DATETIME日期+时间8个字节YYYY-mm-dd HH:ii:ss
TIMESTAMP时间戳4个字节YYYY-mm-dd HH:ii:ss

datetime 与 timestamp 的区别：datetime 存储的时间是相对于本地时区的，而 timestamp 存储的时间是相对于 UTC（世界标准时间）的

其它类型

  1. enum枚举类型

    CREATE TABLE IF NOT EXISTS test(
	a ENUM('a', 'b', 'c')
);
-- 赋给 enum 列的值只能是值列表中的一个成员
INSERT INTO test VALUES ('a'), ('b'), ('c');
-- 插入'A'会转换为'a'
INSERT INTO test VALUES ('A');

  2. set集合类型

    CREATE TABLE IF NOT EXISTS test(
	a SET('a', 'b', 'c')
);
-- 赋给 set 列的值可以包含任意多个值列表中的成员（可以为空，也可以是全体成员）
INSERT INTO test VALUES (''), ('a'), ('b'), ('c'), ('a,b'), ('a,c'), ('b,c'), ('a,b,c');
-- 插入'A,B,C'会转换为'a,b,c'
INSERT INTO test VALUES ('A,B,C');

常见约束

根据约束的类型分类：

  1. 主键约束（Primary Key）

    用于唯一标识每一条记录，被主键约束所限制字段的值必须唯一，而且不能为空

  2. 唯一约束（Unique）

    用于唯一标识每一条记录，被唯一约束所限制字段的值必须唯一，但是可以为空

  3. 非空约束（Not Null）

    被非空约束所限制字段的值不能为空

  4. 默认约束（Default）

    用于在插入数据时为某个列提供默认值

  5. 外键约束（Foreign Key）

    当前表（从表）中被外键约束所限制字段的值必须与另一个表（主表）中某字段的值匹配

  6. 检查约束（Check）

    用于规定列中允许的值的范围或条件。如果不符合约束条件，将无法插入或更新数据

根据约束的添加位置分类：

  1. 列级约束

    支持主键约束、唯一约束、非空约束、默认约束、检查约束，不支持外键约束

  2. 表级约束

    支持主键约束、唯一约束、检查约束、外键约束，不支持非空约束和默认约束

根据约束的添加/删除时间点分类：

  1. 创建表时添加约束：

    使用示例：

    CREATE TABLE major(
	id INT PRIMARY KEY,
	majorName VARCHAR(20)
);
-- 添加列级约束
CREATE TABLE student(
	id INT PRIMARY KEY,
	stuName VARCHAR(20) NOT NULL,
	gender CHAR(1) CHECK(gender = '男' OR gender = '女'),
	seat INT UNIQUE,
	age INT DEFAULT 18,
	majorId INT
);
-- 添加表级约束
CREATE TABLE student(
	id INT,
	stuName VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorId INT,
	CONSTRAINT pk PRIMARY KEY(id),
	CONSTRAINT ck CHECK(gender = '男' OR gender = '女'),
	CONSTRAINT uq UNIQUE(seat),
    -- 注意这里的命名，由于一个表中的外键列可能有多个，因此在命名时最好表达清楚该列联系的是哪两个表
	CONSTRAINT fk_student_major FOREIGN KEY(majorId) REFERENCES major(id)
    -- 注意：上述4个表级约束的 'constraint 约束名' 都可以省略
);
-- 通用写法
CREATE TABLE student(
	id INT PRIMARY KEY,
	stuName VARCHAR(20) NOT NULL,
	gender CHAR(1) CHECK(gender = '男' OR gender = '女'),
	seat INT UNIQUE,
	age INT DEFAULT 18,
	majorId INT,
    CONSTRAINT fk_student_major FOREIGN KEY(majorId) REFERENCES major(id)
);

  2. 修改表时添加约束：

    语法：

    • 添加列级约束

      alter table 表名 modify [column] 字段名 字段类型 新约束;

    • 添加表级约束

      alter table 表名 add [constraint 约束名] 约束类型(字段名) [外键的引用];

    使用示例：

    -- 添加主键约束
ALTER TABLE student MODIFY COLUMN id INT PRIMARY KEY;
ALTER TABLE student ADD PRIMARY KEY(id);
-- 添加唯一约束
ALTER TABLE student MODIFY seat INT UNIQUE;
ALTER TABLE student ADD UNIQUE(seat);
-- 添加外键约束
ALTER TABLE student ADD CONSTRAINT fk_student_major FOREIGN KEY(majorId) REFERENCES major(id);
-- 添加非空约束
ALTER TABLE student MODIFY COLUMN stuName VARCHAR(20) NOT NULL;
-- 添加默认约束
ALTER TABLE student MODIFY age INT DEFAULT 18;
-- 添加检查约束
ALTER TABLE student ADD CHECK(gender = '男' OR gender = '女');

  3. 修改表时删除约束：

    使用示例：

    -- 删除主键约束
ALTER TABLE student DROP PRIMARY KEY;
-- 删除唯一约束
ALTER TABLE student DROP INDEX uq; #uq是唯一约束的名称，如果没有设置名称，则使用默认名称（可以通过 "show index from 表名" 命令查询到）
-- 删除外键约束
ALTER TABLE student DROP FOREIGN KEY fk_student_major; #fk_student_major是外键约束的名称，如果没有设置名称，则使用默认名称
-- 删除非空约束
ALTER TABLE student MODIFY COLUMN stuName VARCHAR(20);
ALTER TABLE student MODIFY COLUMN stuName VARCHAR(20) NULL;
-- 删除默认约束
ALTER TABLE student MODIFY age INT;
-- 删除检查约束
ALTER TABLE student DROP CHECK ck; #ck是检查约束的名称

备注：

  1. 主键和唯一键支持写法：constraint 约束名 primary key|unique(字段1,...,字段n)，即将多个字段组合成一个主键或一个唯一键（不推荐这样使用）

  2. 创建表时， 表中的字段可以添加多个列级约束。多个列级约束之间需要用空格隔开，无顺序要求

  3. 列级约束不能设置约束名，表级约束可以设置约束名，但对主键无效（主键的约束名始终为 PRIMARY）

  4. 关于外键的一些注意点：

    1. 从表外键列和主表关联列的类型应该一致或兼容，名称无要求
    2. 主表关联列必须是一个key（primary key、unique、foreign key），一般是 primary key
    3. 插入数据时，应该先插入主表的数据，再插入从表的数据；删除数据时，应该先删除从表的数据，再删除主表的数据

  5. 级联删除和级联置空

    1. 级联删除

      ALTER TABLE student add CONSTRAINT fk_stu_major FOREIGN KEY (majorId) REFERENCES major(id) ON DELETE CASCADE;
DELETE FROM major WHERE id = 3;
-- 将major表（主表）中id为3的记录行删除后，student表（从表）中majorId为3的记录行也将被删除

    2. 级联置空

      ALTER TABLE student add CONSTRAINT fk_stu_major FOREIGN KEY (majorId) REFERENCES major(id) ON DELETE SET NULL;
DELETE FROM major WHERE id = 2;
-- 将major表（主表）中id为2的记录行删除后，student表（从表）中majorId为2的记录行的majorId字段会被置空

自增长列

SHOW VARIABLES LIKE '%auto_increment%';
-- 设置自增长列的步长
SET auto_increment_increment = 3;
-- 设置自增长列的初始值
SET auto_increment_offset = 2;
-- 创建表时添加自增长列
CREATE TABLE demo_identity(
	id int PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20)
);
-- 修改表时添加自增长列
ALTER TABLE demo_identity MODIFY COLUMN id INT AUTO_INCREMENT;
-- 修改表时删除自增长列
ALTER TABLE demo_identity MODIFY COLUMN id INT;

关于自增长列的一些疑问：

  1. q：自增长列必须和主键搭配吗？

    a：不一定，但要求是一个key（primary key、unique、foreign key）

  2. q：一个表可以有几个自增长列？

    a：至多一个

  3. q：自增长列的类型有什么要求？

    a：只能是数值型

TCL（事务控制语言）

相关概念

事务：

事务是由一个或多个SQL语句组成的执行单元。在这个单元中，每个SQL语句是相互依赖的，整个执行单元是一个不可分割的整体。如果单元中某条SQL语句执行失败或产生错误，整个单元将会回滚，所有受到影响的数据将返回到事务开始以前的状态；如果单元中的所有SQL语句均执行成功，则事务被顺序执行

支持事务的存储引擎：

在MySQL中用的最多的存储引擎有：InnoDB、MyISAM、MEMORY等。其中InnoDB支持事务，而MyISAM、MEMORY等不支持事务

事务的ACID属性：

  1. 原子性（Atomicity）：事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生
  2. 一致性（Consistency）：事务的执行结果必须使数据库从一个一致性状态转变到另一个一致性状态
  3. 隔离性（Isolation）：事务的执行是相互隔离的，一个事务的执行不应该被其它事务干扰
  4. 持久性（Durability）：一旦事务被提交，它对数据库中数据的改变就是永久性的，接下来的其它操作和数据库故障不应该对其有任何影响

事务的分类：

  1. 隐式（自动）事务：事务没有明显的开启和结束的标记，比如insert、update、delete语句
  2. 显式事务：事务有明显的开启和结束的标记

事务的创建

创建步骤：

  1. 开启事务

    SET autocommit = 0; #必须先关闭自动提交模式
START TRANSACTION; #这句sql命令可选

  2. 编写事务中的SQL语句（select、insert、update、delete语句）

  3. 提交事务或回滚事务（二选一）

    COMMIT;
ROLLBACK;

关于系统变量 autocommit 的值：

  • ON（默认）：自动提交模式开启，这意味着每条SQL语句都是一个独立的事务，会自动提交
  • OFF：自动提交模式关闭，这意味着只有进行了commit（提交事务）或rollback（回滚事务）才会结束当前事务，开启一个新的事务

事务创建代码示例：

SHOW VARIABLES LIKE "autocommit"; #结果为on（即1）
DROP TABLE IF EXISTS account;
CREATE TABLE account(
	id INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(20),
	balance DOUBLE
);
INSERT INTO account (username, balance) VALUES ('张无忌', 1000), ('赵敏', 1000);
-- 开启事务
SET autocommit = 0;
START TRANSACTION;
-- 编写sql语句
UPDATE account SET balance = 1000 WHERE username = '张无忌';
UPDATE account SET balance = 1000 WHERE username = '赵敏';
-- 提交事务
-- COMMIT;
-- 回滚事务
ROLLBACK;

演示 savepoint 代码示例：

SET autocommit = 0;
DELETE FROM account WHERE id = 1;
-- 设置保存点
SAVEPOINT a;
DELETE FROM account WHERE id = 3;
-- 回滚到保存点
ROLLBACK TO a;
-- 最终只有id为1的记录行被删除

数据库隔离级别

引言：对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题：

  1. 脏读（读取未提交数据）

    例如：对于两个事务T1和T2，T1读取了已经被T2更新但还没有提交的字段，之后，若T2回滚，T1读取的内容就是临时且无效的

  2. 不可重复读（前后多次读取，数据内容不一致）

    例如：对于两个事务T1和T2，T1读取了一个字段，然后T2更新了该字段，之后，T1再次读取同一字段时，值就不同了

  3. 幻读（前后多次读取，数据总量不一致）

    例如：对于两个事务T1和T2，T1从一个表中读取了一些记录行，然后T2在该表中插入了一些新的行，之后，如果T1再次读取同一个表，就会多出几行

概念：

一个事务与其它事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别，不同隔离级别对应不同的干扰程度。隔离级别越高，数据一致性就越好，但并发性越弱

MySQL支持4种隔离级别：

隔离级别描述
READ UNCOMMITTED（读未提交数据）允许事务读取未被其它事务提交的变更。脏读、不可重复读和幻读的问题都会出现
READ COMMITTED（读已提交数据）只允许事务读取已经被其它事务提交的变更。可以避免脏读，但不可重复读和幻读问题仍然可能出现
REPEATABLE READ（可重复读，默认）确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其它事务对这个字段进行更新。可以避免脏读和不可重复读，但幻读的问题仍然存在
SERIALIZABLE（串行化）确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其它事务对该表执行插入、更新和删除操作。所有并发问题都可以避免，但性能十分低下

相关命令：

  1. 查看当前的隔离级别

    select @@transaction_isolation;

  2. 设置当前MySQL连接的隔离级别

    set session transaction isolation level {read uncommitted|read committed|repeatable read|serializable};

  3. 设置数据库系统的全局隔离级别

    set global transaction isolation level {read uncommitted|read committed|repeatable read|serializable};

视图

相关概念

视图：

视图是一种虚拟表。它由 select 语句定义，是一个可查询的对象，本身不包含数据。其数据来自于定义视图的查询语句中所使用的表，并且这些数据是在使用视图时动态生成的。也就是说视图只保存 sql 逻辑，而不保存查询结果。在创建视图之后，可以像普通表一样，使用 select、insert、update、delete 语句对其进行操作，但实际上这些操作都是针对底层的数据表进行的

使用视图的好处：

  1. 复用和简化 sql 查询，提高查询的可维护性和可读性
  2. 保护数据，限制用户对底层数据表的访问权限，只允许他们操作特定行或列，提高安全性

创建、修改、删除和查看视图

创建视图：

  1. 语法：

    create [or replace] view <viewName> as 查询语句;

  2. 使用示例：

    USE myemployees;
-- 创建视图
CREATE VIEW myView AS
SELECT e.last_name, d.department_name, j.job_title
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN jobs j ON e.job_id = j.job_id;
-- 使用视图
SELECT * FROM myView WHERE last_name LIKE "%a%";

修改视图：

  1. 语法：

    alter view <viewName> as 查询语句;

  2. 使用示例：

    USE myemployees;
ALTER VIEW myView AS
SELECT job_id, AVG(salary)
FROM employees
GROUP BY job_id;

删除视图：

  1. 语法：

    drop view <viewName1>, <viewName2>, ...;

  2. 使用示例：

    USE myemployees;
DROP VIEW myView1, myView2;

查看视图：

  1. 语法：

    desc <viewName>; 或者 show create view <viewName>;

  2. 使用示例：

    USE myemployees;
DESC myView;
SHOW CREATE VIEW myView;

视图的更新

MySQL 中的视图是从一个或多个基本表中派生出来的虚拟表，并不实际存储数据。因此，对视图的更新操作会涉及到基本表的更新。在 MySQL 中，可以对视图进行以下类型的更新：

  1. 使用 insert into 语句向视图中插入行
  2. 使用 update 语句修改视图中某些列的值
  3. 使用 delete from 语句从视图中删除行

使用示例：

USE myemployees;
-- 创建视图
CREATE OR REPLACE VIEW myView AS
SELECT last_name, email
FROM employees;
-- 更新视图
INSERT INTO myView VALUES ('张飞', 'zf@qq.com');
UPDATE myView SET last_name = '张无忌' WHERE last_name = '张飞';
DELETE FROM myView WHERE last_name = '张无忌';

需要注意的是，有些类型的视图是不允许被更新的：

  1. 查询语句中包含这些关键字：distinct、group by、having、union、union all
  2. 查询语句中使用了分组函数、子查询或连接查询
  3. 常量视图
  4. 查询语句的 from 子句中包含了不能被更新的视图
  5. ...etc

总之，在 MySQL 中，不允许被更新的视图通常具有复杂的结构，包含了多个表和各种子查询，因此需要谨慎使用和修改。在需要修改这些类型的视图时，通常需要先对基本表进行更新，然后再重新创建视图

变量

系统变量

  • 全局变量

    作用域：针对所有的会话（连接）有效，但不能跨重启。MySQL服务每次启动时，都会为所有的全局变量赋初始值

    语法：

    1. 查看所有的全局变量

      show global variables;

    2. 查看满足条件的部分全局变量

      show global variables like 模糊查询条件;

    3. 查看指定的全局变量的值

      select @@global.全局变量名;

    4. 为某个全局变量赋值

      set @@global.全局变量名 = 值; 或者 set global 全局变量名 = 值;

  • 会话变量

    作用域：仅仅针对当前会话（连接）有效

    语法：

    1. 查看所有的会话变量

      show [session] variables;

    2. 查看满足条件的部分会话变量

      show [session] variables likes 模糊查询条件;

    3. 查看指定的会话变量的值

      select @@[session].会话变量名;

    4. 为某个会话变量赋值

      set @@[session].会话变量名 = 值; 或者 set [session] 会话变量名 = 值;

自定义变量

  • 用户变量

    作用域：当前整个会话（连接）

    语法：

    1. 声明用户变量（必须赋初始值）

      • set @用户变量名 [:]= 值;
      • select @用户变量名 := 值;

    2. 修改用户变量

      • set @用户变量名 [:]= 值;
      • select @用户变量名 := 值;
      • select 字段 into @用户变量名 from 表;

    3. 查看用户变量的值

      select @用户变量名;

    使用示例：

    -- 声明用户变量（必须赋值）
SET @a = 1;
SET @b := 2;
SELECT @c := 3;
-- 查看用户变量的值
SELECT @a, @b, @c; #1 2 3
-- 修改用户变量
SET @a = 'a';
SELECT @b := 'b';
SELECT COUNT(*) INTO @c FROM myemployees.departments;
-- 再次查看用户变量的值
SELECT @a, @b, @c; #a b 27

    备注：对于用户变量，变量的类型由值的类型决定

  • 局部变量

    作用域：当前的存储过程或函数

    语法：

    1. 声明局部变量（可以不赋初始值）

      declare 局部变量名 类型 [default 值];

    2. 修改局部变量

      • set 局部变量名 [:]= 值;
      • select @局部变量名 := 值;
      • select 字段 into 局部变量名 from 表;

    3. 查看局部变量的值

      select 局部变量名;

    使用示例：

    DELIMITER $
CREATE PROCEDURE my_sum()
BEGIN
	DECLARE a INT DEFAULT 1;
	DECLARE b INT DEFAULT 2;
	DECLARE c INT;
	SET c = a + b;
	SELECT c;
END $
DELIMITER ;
CALL my_sum();

    备注：对于局部变量，变量的类型由声明时所使用的数据类型决定

存储过程和函数

存储过程

定义：

MySQL 存储过程是一组 SQL 语句集合，可以被预先编译和存储在 MySQL 服务器上

创建存储过程：

create procedure 存储过程名(参数列表)
begin
	存储过程主体，可以包含多条SQL语句和控制语句
end;

调用存储过程：

call 存储过程名(实参列表);

查看存储过程：

show create procedure 存储过程名;

删除存储过程：

drop procedure 存储过程名;

备注：

  1. 如果存储过程主体只有一条 SQL 语句，则 begin 和 end 可以省略
  2. 存储过程主体中每条 SQL 语句的结尾必须加分号，而存储过程自身的结束标记可以使用 delimiter 结束标记 语句进行自定义
  3. 参数列表中每个参数都包含参数模式、参数名和参数类型三个部分。其中参数模式又分为：
    1. in：输入参数，表示调用者向存储过程传入值
    2. out：输出参数，表示存储过程向调用者返回值
    3. inout：输入输出参数，表示调用者既可以向存储过程传入值，存储过程也可以向调用者返回值
  4. 和视图不同，删除存储过程时，一次只能删除一个

使用示例：

USE girls;
-- 修改结束标记为 $
DELIMITER $
-- 创建存储过程1——判断用户是否登录成功
CREATE PROCEDURE myp1(IN username VARCHAR(10), IN password VARCHAR(10))
BEGIN
	DECLARE result CHAR(4);
	SELECT IF(COUNT(*) = 0, '登录失败', '登录成功') INTO result
	FROM admin
	WHERE admin.username = username AND admin.password = password; #字段名和变量名重复时，需要使用表名对字段进行限定
	SELECT result;
END $
-- 创建存储过程2——根据女神名，查找对应的男神名和魅力值
CREATE PROCEDURE myp2(IN beautyName VARCHAR(50), OUT boyName VARCHAR(20), OUT userCP INT)
BEGIN
	SELECT IFNULL(bo.boyName, '无对应男神名'), IFNULL(bo.userCP, 0) INTO boyName, userCP #使用 select into 对多个变量赋值
	FROM boys bo RIGHT OUTER JOIN beauty be ON bo.id = be.boyfriend_id
	WHERE be.name = beautyName;
END $
-- 创建存储过程3——传入两个值，将这两个值翻倍并返回
CREATE PROCEDURE myp3(INOUT a INT, INOUT b INT)
BEGIN
	SET a = a * 2;
	SET b = b * 2;
END $
-- 修改结束标记为 ;
DELIMITER ;
-- 调用存储过程1
CALL myp1('john', '8888');
-- 调用存储过程2
SET @boyName = '';
SET @userCP = 0;
CALL myp2('王语嫣', @boyName, @userCP);
SELECT @boyName, @userCP;
-- 调用存储过程3
SET @a = 2;
SET @b = 3;
CALL myp3(@a, @b);
SELECT @a, @b;
-- 查看存储过程
SHOW CREATE PROCEDURE myp1;
SHOW CREATE PROCEDURE myp2;
SHOW CREATE PROCEDURE myp3;
-- 删除存储过程
DROP PROCEDURE myp1;
DROP PROCEDURE myp2;
DROP PROCEDURE myp3;

函数

定义：

MySQL 函数是一种可重用的代码块，用于执行特定的任务。函数可以接收参数，处理数据，并返回一个值或结果集

创建函数：

create function 函数名(参数列表) returns 返回类型
begin
	函数体
end;

调用函数：

select 函数名(实参列表);

查看函数：

show create function 函数名;

删除函数：

drop function 函数名;

备注：

  1. 如果函数体只有一条语句，则 begin 和 end 可以省略
  2. 函数体中一定有一条 return 语句
  3. 和存储过程不同，存储过程可以有0个或多个返回值，而函数有且仅有一个返回值
  4. 和存储过程不同，参数列表中每个参数只包含参数名和参数类型两个部分
  5. 和存储过程一样，可以使用 delimiter 结束标记 语句自定义函数本身的结束标记
  6. 和存储过程一样，删除函数时，一次也只能删除一个

使用示例：

USE myemployees;
-- 创建函数
DELIMITER $
CREATE FUNCTION myf(name VARCHAR(20)) RETURNS DOUBLE(10, 2)
BEGIN
	DECLARE salary DOUBLE(10, 2);
	SELECT e.salary INTO salary FROM employees e WHERE last_name = name;
	RETURN salary;
END $
DELIMITER ;
-- 调用函数
SELECT myf('Kochhar');
-- 查看函数
SHOW CREATE FUNCTION myf;
-- 删除函数
DROP FUNCTION myf;

流程控制结构

注意：下面介绍的分支结构和循环结构都只能用在存储过程或函数中

分支结构

if 结构：

if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
elseif 条件n then 语句n;
[else 语句m;]
end if;

case 结构：

  1. 类似于编程语言中的 switch case

    case 表达式
when 常量1 then 语句1;
...
when 常量n then 语句n;
[else 语句m;]
end case;

  2. 类似于编程语言中的多重 if else

    case
when 条件1 then 语句1;
...
when 条件n then 语句n;
[else 语句m;]
end case;

使用示例：

USE student;
DELIMITER $
-- if结构
CREATE FUNCTION test_if(score INT) RETURNS CHAR(1)
BEGIN
	DECLARE result CHAR(1);
	IF score >= 90 AND score <= 100 THEN SET result = 'A';
	ELSEIF score >= 80 THEN SET result = 'B';
	ELSEIF score >= 60 THEN SET result = 'C';
	ELSE SET result = 'D';
	END IF;
	RETURN result;
END $
-- case结构1
CREATE PROCEDURE test_case1(IN score INT)
BEGIN
	CASE
	WHEN score >= 90 AND score <= 100 THEN SELECT 'A';
	WHEN score >= 80 THEN SELECT 'B';
	WHEN score >= 60 THEN SELECT 'C';
	ELSE SELECT 'D';
	END CASE;
END $
-- case结构2
CREATE FUNCTION test_case2(name VARCHAR(20)) RETURNS VARCHAR(10)
BEGIN
	DECLARE result CHAR(1);
	SELECT sex INTO result FROM student WHERE studentname = name;
	CASE result
	WHEN '男' THEN RETURN 'male';
	WHEN '女' THEN RETURN 'female';
	END CASE;
END $
DELIMITER ;
SELECT test_if(53);
CALL test_case1(88);
SELECT test_case2('殷素素');

循环结构

whlie 循环：

 while 循环条件 do
	循环体;
end while [标签];

loop 循环：

 loop
	循环体;
end loop [标签];

repeat 循环：

 repeat
	循环体;
until 循环结束的条件
end repeat [标签];

备注：

在循环结构中可以使用iterate 标签和leave 标签进行跳转。前者的作用类似于编程语言中的 continue 关键字，而后者的作用类似于编程语言中的 breek 关键字

使用示例：

USE girls;
DELIMITER $
-- while循环
CREATE PROCEDURE test_while(IN n INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	WHILE i < n DO
		INSERT INTO admin (username, password) VALUES (CONCAT('tom', i), 'pwd_tom');
		SET i = i + 1;
	END WHILE;
END $
-- loop循环（类似于编程语言中的死循环）
CREATE PROCEDURE test_loop(IN n INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	myloop: LOOP
		INSERT INTO admin (username, password) VALUES (CONCAT('jack', i), 'pwd_jack');
		SET i = i + 1;
		IF i < n THEN ITERATE myloop;
		ELSE LEAVE myloop;
		END IF;
	END LOOP myloop;
END $
-- repeat循环（类似于编程语言中的 do while 循环）
CREATE PROCEDURE test_repeat(IN n INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	myrepeat: REPEAT
		SET i = i + 1;
		IF MOD(i, 2) = 1 THEN INSERT INTO admin (username, password) VALUES (CONCAT('rose', i), 'pwd_rose');
		ELSE ITERATE myrepeat;
		END IF;
	UNTIL i = n
	END REPEAT myrepeat;
END $
DELIMITER ;
CALL test_while(5);
CALL test_loop(5);
CALL test_repeat(5);
本网站是一个以CSS、JavaScript、Vue、HTML为核心的前端开发技术网站。我们致力于为广大前端开发者提供专业、全面、实用的前端开发知识和技术支持。 在本网站中，您可以学习到最新的前端开发技术，了解前端开发的最新趋势和最佳实践。我们提供丰富的教程和案例，让您可以快速掌握前端开发的核心技术和流程。 本网站还提供一系列实用的工具和插件，帮助您更加高效地进行前端开发工作。我们提供的工具和插件都经过精心设计和优化，可以帮助您节省时间和精力，提升开发效率。 除此之外，本网站还拥有一个活跃的社区，您可以在社区中与其他前端开发者交流技术、分享经验、解决问题。我们相信，社区的力量可以帮助您更好地成长和进步。 在本网站中，您可以找到您需要的一切前端开发资源，让您成为一名更加优秀的前端开发者。欢迎您加入我们的大家庭，一起探索前端开发的无限可能!

本文是由"秘密武器开发者中心"本站原创整理，所有内容的版权归原作者所有。

相关专题