跳至主要內容

MySQL

Yang大约 24 分钟

MySQL 是最广泛、普及度最高的开源关系型数据库

测试用库：链接: https://pan.baidu.com/s/1pZhP2UQzglUULBtaiD1feQopen in new window 密码: 8frq

数据库分类

关系型数据库 MyS

简称：RDBMS，是指使用了关系模型的数据库系统。关系模型中，数据是分类存放的，数据之间可以有联系

  • 主流关系型数据库
    • DB2
    • Oracle
    • MySQL
    • SQL Server

NoSQL 数据库

指数据分类存放，但数据之间没有关联关系的数据库系统

  • 是关系型数据库的一种补充

  • 应用场景

    • 秒杀库存
    • 登录信息
    • 消息通知

  • 主流 NoSQL 数据库

    • Redis
    • MemCache
    • MongoDB
    • Neo4J

MySQL 安装与配置

衍生版本

  • MySQL（Oracle）
  • Percona Server（Percona）
  • MariaDB（MariaDB）

常用命令

  • 登录：mysql -uroot -p

  • 退出登录：exit

  • 显示数据库列表：show databases;

  • 启动 MySQL 服务：sudo /usr/local/mysql/support-files/mysql.server start

  • 停止 MySQL 服务：sudo /usr/local/mysql/support-files/mysql.server stop

  • 重启 MySQL 服务：sudo /usr/local/mysql/support-files/mysql.server restart

常见问题

重设 root 密码（windows）

  • 创建一个 TXT 文件，定义修改密码的 SQL 语句
    • ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
  • mysqld --defaults-file="my.ini" --init-file="modifyPassword.txt" --console

SQL 语言

用于访问和处理数据的标准的计算机语言

语言分类

  • DML：数据操作语言
    • 添加
    • 删除
    • 修改
    • 查询
  • DCL：数据控制语言
    • 用户
    • 权限
    • 事务
  • DDL：数据定义语言
    • 逻辑库
    • 数据表
    • 视图
    • 索引

注意事项

  • 语句不区分大小写
  • 字符串区分大小写，单引号双引号都是定义字符串
  • 以英文分号结尾
  • 空白和换行没有限制，但不能破坏语法规则

注释

# 这是一段注释文字
/* 这是一段注释文字 */

逻辑库

  • 显示所有逻辑库：SHOW DATABASES;

  • 创建逻辑库：CREATE DATABASE test;

  • 删除逻辑库：DROP DATABASE test;

  • 使用逻辑库：USE test;

数据表

表：关系型数据库中用来存储数据的一个单元，一个表定义了一组数据结构

  • 显示逻辑库中的所有数据表：SHOW tables;
  • 显示数据表的具体情况：DESC student;
  • 显示创建数据表时的 SQL 语句：SHOW CREATE TABLE student;
  • 删除数据表：DROP TABLE student;
  • 查看表的索引：SHOW INDEX FROM 表名
  • 删除表的索引：DROP INDEX 索引名称 ON 表名

数据类型

数字
  • 十进制整数成二进制整数不存在精度丢失
  • 十进制浮点数无法在计算机中用二进制精确表达
    • 0.2 -> 0.2000000030
类型大小说明
TINYINT1 字节（-128 ~ 127）小整数
SMALLINIT2 字节（-2^16 ~ 2^16-1）普通整数
MEDIUMINT3 字节普通整数
INT4 字节较大整数
BIGINT8 字节大整数
FLOAT4 字节单精度浮点数
DOUBLE8 字节双精度浮点数
DECIMAL(精确数字 )------DECIMAL(10,2)
字符串
类型大小说明
CHAR1 ~ 255 字符固定长度字符串
VARCHAR1 ~ 65535 字符不固定长度字符串
TEXT1 ~ 65535 字符不确定长度字符串
MEDIUMTEXT1 ~ 1600 万 字符不确定长度字符串
LONGTEXT1 ~ 42 亿 字符不确定长度字符串
日期
类型大小说明
DATE3 字节日期
TIME3 字节时间
YEAR1 字节年份
DATETIME8 字节日期时间
TIMESTAMP4 字节时间戳
枚举类型
ENUM("男","女")
布尔类型
  • true 和 false 会被映射成 0 和 1
Boolean

约束参数

约束名称关键字描述
主键约束PRIMARY KEY字段值唯一，且不能为 NULL
非空约束NOT NULL字段值不能为 NULL
唯一约束UNIQUE字段值唯一，且可以为 NULL
外键约束（不推荐）FOREIGN KEY保持关联数据的逻辑性
主键约束

  • 要求字段的值在全表必须唯一，且不能为 NULL 值

  • 建议一定要使用数字类型，因为数字的检索速度会非常快

  • 如果主键是数字类型，还可以设置自动增长 AUTO_INCREMENT

非空约束
  • 要求字段的值不能为 NULL 值
  • NULL 值是没有值，而不是空字符串
  • 可以在后面添加默认值 DEFAULT "默认值"
唯一约束
  • 要求字段值如果不为 NULL，那么在全表必须唯一
外键约束
  • 不推荐使用：如果行程外键闭环，将无法删除任何一张表的记录
  • 用来保证关联数据的逻辑关系
  • 写在字表上

表操作

创建表
CREATE TABLE test(
	列名1 数据类型 [约束] [COMMENT 注释],
	列名2 数据类型 [约束] [COMMENT 注释],
	列名3 数据类型 [约束] [COMMENT 注释]
)[COMMENT=表注释];

USE test;
CREATE TABLE student(
	id INT UNSIGNED PRIMARY KEY,
	name VARCHAR(20) NOT NULL,
	sex CHAR(1) NOT NULL,
	birthday DATE NOT NULL,
	tel char(11) NOT NULL,
	remark VARCHAR(200)
);
添加字段
ALTER TABLE 表名称
ADD 列1 数据类型 [约束] [COMMENT 注释],
ADD 列2 数据类型 [约束] [COMMENT 注释],
...;
修改字段
# 不修改猎德名字
ALTER TABLE 表名称
MODIFY 字段名 数据类型 [约束] [COMMENT 注释];

# 修改列的名字
ALTER TABLE 表名称
CHANGE 原字段名 新字段名 数据类型 [约束] [COMMENT 注释];
删除字段
ALTER TABLE 表名称
DROP 列1，
DROP 列2,
...;

索引

  • 一旦数据排序之后，查找的速度就会翻倍，现实世界和程序世界都是如此
  • 设置索引后，数据库会对该索引字段排序，生成二叉树

使用原则

  • 数据量很大，而且经常被查询的数据表可以设置索引
  • 索引只添加在经常被用作检索条件的字段上面
  • 不要在大字段（字符很长）上创建索引

建表时添加

CREATE TABLE 表名称(
	...,
  INDEX [索引名称](字段)
  ...
);

# 例子
CREATE TABLE student(
	id INT UNSIGNED PRIMARY KEY,
	name VARCHAR(20) NOT NULL,
	isManage Boolean DEFAULT(FALSE),
	sex ENUM("男","女") NOT NULL,
	create_time TIMESTAMP NOT NULL,
	INDEX idx_name(name)
);

表存在时添加

# 方式一
CREATE INDEX 索引名称 ON 表名(字段);

# 方式二
ALTER TABLE 表名 ADD INDEX [索引名](字段名);

基本查询

总执行顺序：FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT

基础

基本的查询语句由 SELECT 和 From 关键字组成

  • SELECT 语句屏蔽了物理层的操作，用户不必关心数据的真实存储，教育数据库高效的查找数据
SELECT * FROM 表名;
SELECT 字段1,字段2,字段3 FROM 表名;

别名

通常情况下，SELECT 子句中使用了表达式，那么这列的名字就默认为表达式，因此需要一种对列名重命名的机制

SELECT title,price*12 AS "new_price" FROM 表名;

分页

如果结果集的记录很多，则可以使用 LIMIT 关键字限定结果集数量

SELECT ... FROM ... LIMIT 起始位置,偏移量;

# 如果 LIMIT 子句只有一个参数，他表示偏移量，起始值默认为 0
SELECT ... FROM ... LIMIT 偏移量;

排序

查询语句默认不会对结果集进行排序，如果想让结果集按照某种顺序排列，则需使用 ORDER BY 子句

  • 数字类型：按照数字大小排序
  • 字符串：按照字符集序号排序
  • 日期类型：按照日期大小排序
# ASC  升序（默认）
# DESC 降序
SELECT ... FROM ... ORDER BY 列名 [ASC|DESC]

# 示例
select * from goods ORDER BY price DESC;

# 多字段排序：使用 ORDER BY 规定首要排序条件和次要排序条件，数据库会按照首要条件排序，当首要排序内容相同时，按照次要条件对相同内容进行排序，如果所有排序条件都相同，则按主键升序排序
select * from goods ORDER BY price1 DESC, price2 ASC;

去重

假如 SELECT 语句中没有选择主键字段，则结果集中可能出现完全一致的数据，如果要去除重复的数据，可以使用 DISTINCT 关键字来实现

  • 使用 DISTINCT 的 SELECT 子句中如果查询多个字段，去除重复记录的功能会失效
  • DISTINCT 关键字只能在 SELECT 子句中使用一次，并且必须写在第一个参数前
SELECT DISTINCT 字段 FROM ...;

条件

许多时候，用户感兴趣的只是逻辑表记录中能够满足一种或某几种条件的记录，这类条件要用 WHERE 子句来实现数据的筛选

SELECT ... FROM ... WHERE 条件1 [AND|OR] 条件2 ...;
SELECT ... FROM ... WHERE (条件1 [AND|OR] 条件2) [AND|OR] 条件3 ...;

注意

  • 聚合函数不能出现在 WHERE 子句中

  • 任何数对 NULL 进行运算都返回 NULL，可以使用自带函数 IFNULL(null,0) 来避免 NULL 运算

  • 条件执行的顺序是从左到右的，应该把索引条件或者筛选记录最多的条件写在左侧

# 运算符

## 数学运算符 + - * / %

## 比较运算符
### > >= < <= = != IN
### 包含（IN）
SELECT * FROM 数据表 WHERE 字段名 IN ('前端','后端');
### 为空（IS NULL）
SELECT * FROM 数据表 WHERE 字段名 IS NULL;
### 不为空（IS NOT NULL）
SELECT * FROM 数据表 WHERE 字段名 IS NOT NULL;
### 范围（BETWEEN AND）
SELECT * FROM 数据表 WHERE amount BETWEEN 200 AND 300;
### 模糊查询（LIKE）: 百分号匹配多个字符，下划线匹配一个字符
SELECT * FROM 数据表 WHERE name LIKE "A%";
SELECT * FROM 数据表 WHERE name LIKE "_A";
### 正则表达式（REGEXP）
SELECT * FROM 数据表 WHERE name REGEXP "[a-zA-Z{4}]";

## 逻辑运算符
### 与（AND）
SELECT * FROM 数据表 WHERE age > 18 AND sex = "男";
### 或（OR）
SELECT * FROM 数据表 WHERE age < 18 OR age > 60;
### 非（NOT）
SELECT * FROM 数据表 WHERE NOT sex = '男';
### 异或（XOR）：当任意一个操作数为NULL时,返回值为NULL；对于非NULL的操作数,如果两个的逻辑真假值相异，则返回结果为1，否则为0，就是两个不能同时成立,也不能同时不成立,只成立其中一个条件
SELECT * FROM 数据表 WHERE age > 18 XOR sex = '男';

## 二进制按位运算：实质上是将参与运算的两个操作数，按对应的二进制数逐位进行逻辑运算
### &：  按位与
### |：  按位或
### ~：  按位取反
### ^：  按位异或
### <<： 左移
### >>： 右移

合并

UNION

高级查询

聚合函数

又称作汇总函数，在数据的查询分析中应用十分广泛。可以对数据求和、求最大值和最小值、求平均值等等。默认情况下，聚合函数是对全表范围内的数据做统计

平均值（AVG）

用于获取非空值的平均值，非数字数据统计结果为 0

select AVG(amount1+IFNULL(amount2,0)) from t_emp;
select AVG(amount1+IFNULL(amount2,0)) AS avg from t_emp;

求和（SUM）

SUM 函数用来求和，只能用于数字类型，字符类型的统计结果为 0，日期类型统计结果是毫秒数相加

select SUM(amount) from 数据表;
select SUM(amount) from 数据表 WHERE name IN('name1','name2');

最大值（MAX）

用于获得非空值的最大值

select MAX(amount) from 数据表;
select MAX(amount1+IFNULL(amount2,0)) from 数据表;
select MAX(amount1+IFNULL(amount2,0)) from 数据表 WHERE name IN('name1','name2');
select SUM(sal) AS sum,MAX(sal+IFNULL(comm,0)) AS max from t_emp;
select MAX(LENGTH(ename)) from t_emp;

最小值（MIM）

用于获取非空值的最小值

  • 代码示例如 MAX

计数（COUNT）

COUNT(*)：用于获取包含空值的记录数

COUNT(字段名)：用于获取规定字段不为空值的记录数

分组查询

GROUP BY 子句的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对每个小区域分别进行数据汇总处理

  • 基于数据匹配，查询语句中如果含有 GROUP BY 子句，那么 SELECT 子句中的内容
    • 可以包含聚合函数，或者 GROUP BY 子句的分组列
    • 其余内容均不可以出现在 SELECT 子句中

简单分组

# 根据部门编号，计算不同部门的平均底薪（底薪四舍五入为整数）
SELECT deptno, ROUND(AVG(sal)) FROM t_emp GROUP BY deptno;

逐级分组

# 逐级分组：查询每个部门里，每种职位的人员数量和平均底薪，并按部门编号升序排列
SELECT deptno,job,COUNT(*),ROUND(AVG(sal)) FROM t_emp GROUP BY deptno,job ORDER BY deptno;

汇总计算

# 对分组结果集再次做汇总计算
SELECT deptno,COUNT(*),ROUND(AVG(sal)) FROM t_emp GROUP BY deptno WITH ROLLUP;

字段拼接

GROUP_CONCAT 函数可以把分组查询中对的某个字段拼接成一个字符串

# 查询某个部门内底薪超过2000元的人数和员工姓名
SELECT deptno,GROUP_CONCAT(ename),COUNT(*) FROM t_emp WHERE sal >= 2000 GROUP BY deptno ORDER BY deptno;

Having 子句

having 子句可以在分组之后筛选数据

  • where 子句在聚合前先筛选记录，也就是说作用在 group by 和 having字句前。

  • having 子句在聚合后对组记录进行筛选，真实表中没有此数据，这些数据是通过一些函数产生的

# 查询部门平均底薪超过 2000 的部门编号
SELECT deptno FROM t_emp GROUP BY deptno HAVING AVG(sal)>=2000;

# 查询每个部门中，1982年以后入职的员工超过两个人的部门编号
SELECT deptno FROM t_emp WHERE hiredate > '1982-01-01' GROUP BY deptno HAVING COUNT(*) >= 2;

# 按照查询字段中的第一个字段进行分组
SELECT deptno,COUNT(*), FROM t_emp GROUP BY 1;
SELECT deptno,COUNT(*), FROM t_emp GROUP BY 1 HAVAING deptno IN (10,20);

表连接

  • 从多张表中提取数据，必须指定关联条件
  • 如果不定义关联条件，就会出现无条件链接，两张表的数据会交叉连接，产生笛卡尔积
# 连接员工表和部门表，查询员工的部门信息
SELECT e.empno,e.ename,d.dname FROM t_emp e JOIN t_dept d ON e.deptno = d.deptno;

内连接

是最常见的一种表连接，用于查询多张关系表符合连接条件的记录，结果集中只保留符合连接条件的记录，WHERE 和 ON 效果相同

# 语法(INNER关键字可省略)
SELECT ... FROM 表1
[INNER] JOIN 表2 ON 条件
[INNER] JOIN 表3 ON 条件
...;

# 衍生语法
SELECT ... FROM 表1 JOIN 表2 WHERE 连接条件;
SELECT ... FROM 表1,表2 WHERE 连接条件;

# 示例：根据员工表，部门表，工资等级表 查询每个员工的工号、姓名、部门名称、底薪、职位、工资等级
SELECT e.empno,e.ename,d.dname,e.sal,e.job,s.grade
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno JOIN t_salgrade s ON e.sal BETWEEN s.losal AND hisal;

# 查询与 SCOTT 部门相同对的员工有谁
SELECT e2.name FROM t_emp e1 JOIN t_emp e2 ON e1.deptno=e2.deptno WHERE e1.ename="SCOTT" AND e2.ename!="SCOTT";

# 查询底薪超过公司平均底薪的员工信息
SELECT e.ename FROM t_emp e JOIN (SELECT AVG(sal) avg FROM t_emp) t ON e.sal >= t.avg;

# 查询 RESEARCH 部门的人数、最高底薪、最低底薪、平均底薪、平均工龄
SELECT COUNT(*), MAX(e.sal),MIN(e.sal),ROUND(AVG(e.sal)), FLOOR(AVG(DATEDIFF(NOW(),e.hiredate)/365))
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno WHERE d.dname="RESEARCH";

# 查询每种职业的最高工资、最低工资、平均工资、最高工资等级、最低工资等级
SELECT
job,
ROUND(MAX(e.sal + IFNULL(comm,0))) max,
ROUND(MIN(e.sal + IFNULL(comm,0))) min,
ROUND(AVG(sal+IFNULL(comm,0))) avg,
MAX(s.grade) max_grade,
MIN(s.grade) min_grade
FROM t_emp e JOIN t_salgrade s
ON (e.sal + IFNULL(comm,0)) BETWEEN s.losal AND s.hisal
GROUP BY job;

# 查询每个底薪超过部门平均底薪的员工信息
SELECT e.empno,e.ename,e.sal
FROM t_emp e
JOIN (SELECT deptno,AVG(sal) avg FROM t_emp GROUP BY deptno) d
ON e.deptno=d.deptno AND e.sal >= d.avg;

外连接

不管符不符合连接条件，记录都要保留在结果集中，WHERE 和 ON 效果 不 相同

  • 左外连接：LEFT JOIN ，保留左表的所有记录去和右表链接
    • 右表无符合连接条件则出 NULL 值与左表连接
  • 右外连接：RIGHT ，保留右表的所有记录去和左表链接
    • 左表无符合连接条件则出 NULL 值与右表连接
# 左外连接
SELECT e.empno, e.ename, d.dname
FROM t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno;

# 右外连接
SELECT e.empno, e.ename, d.dname
FROM t_emp e RIGHT JOIN t_dept d ON e.deptno=d.deptno;

# 查询每个部门的名称和部门的人数
SELECT d.dname, COUNT(e.deptno)
FROM t_emp e RIGHT JOIN t_dept d
ON e.deptno=d.deptno GROUP BY d.deptno;

# 查询每个部门的名称和部门的人数？如果没有部门的员工，部门名称用 NULL 代替
(SELECT d.dname, COUNT(e.deptno)
FROM t_emp e RIGHT JOIN t_dept d ON e.deptno=d.deptno GROUP BY d.deptno)
UNION
(SELECT d.dname, COUNT(*)
FROM t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno GROUP BY d.deptno);

# 查询每名员工的编号、姓名、部门、月薪、工资等级、工龄、上司编号、上司姓名、上司部门
SELECT
e1.empno,e1.ename,d1.dname,e1.sal+IFNULL(e1.comm,0)),
s.grade,FLOOR(DATEDIFF(NOW(),e1.hiredate)/365),
e2.empno,e2.ename,d2.dname
FROM t_emp e1
LEFT JOIN t_emp e2 ON e1.mgr=e2.empno
LEFT JOIN t_dept d1 ON e1.deptno=d1.deptno
LEFT JOIN t_salgrade s ON (e1.sal+IFNULL(e1.comm,0)) BETWEEN s.losal AND s.hisal
LEFT JOIN t_dept d2 ON e2.deptno=d2.deptno;

子查询

子查询是一种查询中嵌套查询的语句

  • 按位置分类
    • WHERE 子句：每查询一条记录的时候都要执行一次，查询效率很低**（不推荐）**
    • FROM 子句，只会执行一次，查询效率很高**（推荐）**
    • SELECT 子句：每输出一条记录的时候都要执行一次，查询效率很低**（不推荐）**
  • 按结果集数量分类
    • 单行子查询：结果集只有一条记录
      • 能出现在所有位置上
    • 多行子查询：结果集有多行记录
      • 只能出现在 WHERE 和 FROM 子句中，不可以出现在 SELECT 子句中
# 查询底薪超过公司平均水平的员工信息
SELECT empno,ename,sal
FROM t_emp WHERE sal >= (SELECT AVG(sal) FROM t_emp);

# 查询 FORD 和 MARTIN 两个人的同事
SELECT ename FROM t_emp WHERE deptno IN (SELECT deptno FROM t_emp WHERE ename IN("FORD","MARTIN")) AND ename NOT IN("FORD","MARTIN");
  • WHERE 子句中的多行子查询
    • 关键字
      • IN：包含
      • ALL：全部
      • ANY：至少一个
      • EXISTS：把原来在子查询之外的条件判断写到子查询内
        • 语法：SELECT ... FROM 表名 WHERE [NOT] EXISTS (子查询)
# 查询比 FROD 和 MARTIN 底薪都高的员工信息
SELECT ename FROM t_emp WHERE sal > ALL(SELECT sal FROM t_emp WHERE ename IN("FORD","MARTIN"));

# 查询比 FROD 和 MARTIN 至少一个底薪高的员工信息
SELECT ename FROM t_emp WHERE sal > ANY(SELECT sal FROM t_emp WHERE ename IN("FORD","MARTIN"));

# 查询工资等级是 3 级或 4 级的员工信息
SELECT empno,ename,sal FROM t_emp WHERE EXISTS (SELECT grade FROM t_salgrade WHERE sal BETWEEN losal AND hisal AND grade IN(3,4));

写入操作

添加(INSERT)

  • 可以在 VALUES 中写入子查询
  • IGNORE：让 INSERT 只插入数据库不存在的记录
# 语法
INSERT [IGNORE] INTO 表名(字段1,字段2,字段3) VALUES(值1,值2,值3);
INSERT [IGNORE] INTO 表名(字段1,字段2,字段3) VALUES(值1,值2,值3),(值1,值2,值3)...;

# 方言语法
INSERT [IGNORE] INTO 表名 SET 字段1=值1,字段2=值2,...;

修改(UPDATE)

# 语法
UPDATE [IGNORE] 表名
SET 字段1=值1,字段2=值2,字段3=值3,
[WHERE 修改条件1...]
[ORDER BY ...]
[LIMIT ...];

# 内连接语法
UPDATE 表1 JOIN 表2 ON 连接条件 SET 字段1=值1,字段2=值2,字段3=值3,...;
UPDATE 表1,表2 SET 字段1=值1,字段2=值2,字段3=值3 WHERE 连接条件;

# 外连接语法
UPDATE 表1 [LEFT|RIGHT] JOIN 表2 ON 连接条件 SET 字段1=值1,字段2=值2,...;


# 把每个员工的编号和上司的编号加1，用 ORDER BY 子句完成
UPDATE t_emp SET empno=empno+1,mgr=mgr+1 ORDER BY empno DESC;

# 把月收入前三名对的员工底薪减100元，用 LIMIT 子句完成
UPDATE t_emp SET sal=sal-100 ORDER BY sal+IFNULL(comm,0) DESC LIMIT 3;

# 把 10 部门中，工龄超过 20 年的员工，底薪增加 200 元
UPDATE t_emp SET sal=sal+200 WHERE deptno=10 AND FLOOR(DATEDIFF(NOW(),hiredate)/365)>20;

# 把 ALLEN 调往 RESEARCH 部门，职务调整为 ANALYST
UPDATE t_emp e JOIN t_dept d
SET e.job="ANALYST",e.deptno=d.deptno,d.loc="北京"
WHERE e.name="ALLEN" AND d.dname="RESEARCH"

# 把底薪低于公司平均底薪的员工，底薪增加 150 元
UPDATE t_emp e
JOIN (SELECT AVG(sal) AS avg FROM t_emp) t
ON e.sal < t.avg SET e.sal=e.sal+150;

# 把没有部门的员工，或者 SALES 部门低于 2000 元底薪的员工，都调往 20 部门
UPDATE t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno SET e.deptno=20 WHERE e.deptno IS NULL OR (d.dname="SALES" AND e.sal < 2000);

删除(DELETE)

# 语法
DELETE [IGNORE] FROM 表名
[WHERE 条件1,条件2,...]
[ORDER BY ...]
[LIMIT ...];

# 表内连接语法
DELETE 表1,... FROM 表1 JOIN 表2 ON 条件
[WHERE 条件1,条件2,...]
[ORDER BY ...]
[LIMIT ...];

# 表外内连接语法
DELETE 表1,... FROM 表1 [LEFT|RIGHT] JOIN 表2 ON 条件
[WHERE 条件1,条件2,...]
[ORDER BY ...]
[LIMIT ...];

# 删除 10 部门中，工龄超过 20 年的员工记录
DELETE FROM t_emp WHERE deptno=10 AND FLOOR(DATEDIFF(NOW(),hiredate)/365)>=20;

# 删除 20 部门中，工资最高的员工记录
DELETE FROM t_emp WHERE deptno=20 ORDER BY (sal+IFNULL(comm,0)) DESC LIMIT 1;

# 删除 SALES 部门和该部门的全部员工记录
DELETE e,d FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno WHERE d.dname="SALES";

# 删除每个低于部门平均底薪的员工记录
DELETE e FROM t_emp e JOIN (SELECT deptno,AVG(sal) AS sal FROM t_emp GROUP BY deptno) t ON e.deptno=t.deptno AND e.sal < t.sal;

# 删除员工 KING 和他的下属的员工记录，用表连接实现
DELETE e
FROM t_emp e JOIN
(SELECT empno FROM t_emp WHERE ename="KING") t
ON e.mgr=t.empno OR e.empno=t.empno;

# 删除 SALES 部门的员工，以及没有部门的员工
DELETE e
FROM t_emp e LEFT JOIN t_dept d
ON e.deptno=d.deptno WHERE d.dname="SALES" OR e.deptno IS NULL;
  • 快速删除数据表全部记录
    • DELETE 语句是在事务机制下删除记录，删除记录之前，先把将要删除的记录保存到日志文件里，然后再删除记录
    • TRUNCATE 语句在事务机制之外删除记录，速度远超 DELETE 语句
    • 语法：TRUNCATE TABLE 表名;

基本函数

数字函数

函数功能用例
ABS绝对值ABS(-100)
ROUND四舍五入ROUND(4.62)
FLOOR强制舍位到最近的整数FLOOR(9.9)
CEIL强制进位到最近的整数CEIL(3.2)
POWER幂函数POWER(2,3)
LOG对数函数LOG(7,3)
LN对数函数（已 e 为底）LN(10)
函数功能用例
SQRT开平方SQRT(9)
PI圆周率PI()
SIN三角函数SIN(1)
COS三角函数COS(1)
TAN三角函数TAN(1)
COT三角函数COT(1)
RADIANS角度转弧度RADIANS(30)
DEGREES角度转弧度DEGREES(1)

字符函数

函数功能用例
LOWER转换为小写函数LOWER(ename)
UPPER转换为大写函数UPPER(ename)
LENGTH字符数量LENGTH(ename)
CONCAT连接字符串CONCAT(sal,"$","%,"&")
INSTR字符串出现的位置INSTR(ename,"A")
INSERT(原字符串,要插入的位置,要覆盖的字符个数,用来进行插入或覆盖的字符串)插入/替换字符串INSERT("您好",1,0,"先生")
REPLACE(原字符串,要被替换的字符,用来替换的字符)替换字符REPLACR("你好先生","先生","女士")
SUBSTR(原字符串,开始位置,结束位置)截取字符串SUBSTR("你好世界",3,4)
SUBSTRING(原字符串,开始位置,截取长度)截取字符串SUBSTRING("你好世界",3,4)
LPAD左侧填充字符串LPAD("HELLO",10,"*")
RPAD右侧填充字符串RPAD("HELLO",10,"*")
TRIM去除守卫空格TRIM(" 你好先生 ")

日期函数

  • MySQL 数据库中，两个日期不能直接加减，日期也不能与数字加减
函数描述
NOW()获取系统日期和时间，格式 yyyy-MM-dd hh:mm:ss
CURDATE()获取当前系统日期，格式 yyyy-MM-dd
CURTIME()获取当前系统时间，格式 hh:mm:ss
DATE_FORMAT(日期,表达式)格式化日期字符串，日期占位符参考下一个表格
DATE_ADD(日期,INTERVAL 偏移量 时间单位)日期偏移计算，时间单位非常灵活，如：MONTH,DAY，MINUTE
DATEDIFF(日期,日期)计算两个日期相差的天数
``
日期占位符作用
%Y年份
%m月份
%d日期
%w星期（数字）
%W星期（名称）
%j本间第几天
%U本年第几周
%H小时（24）
%h小时（12）
%i分钟
%s秒数
%r时间（12）
%T时间（24）

条件函数

函数描述
IFNULL(字段名,值)判断第一个参数是否为空，不为空则返回第一个参数，为空则返回第二个参数
IF(表达式,值1,值2)判断表达式真假，为真则返回第一个参数，为假则返回第二个参数
CASE WHEN THEN ELSE END条件判断语句，实例代码在下方
CASE
	WHEN 表达式 THEN 值1
	WHEN 表达式 THEN 值2
	...
	ELSE 值N
END

事务机制

事务是一种可靠的、一致的方式，访问和操作数据库的程序单元

由一个或多个 SQL 语句组成的整体，要么全部执行成功，要么全部执行失败

事务依赖与数据库实现，MySQL 通过事务区违数据缓冲地带

  • 数据的写入直接操作数据文件是非常危险的，遇到突发状况（停电、死机等）会造成数据紊乱

  • RDBMS（关系型数据库管理系统） = SQL 语句 + 事务（ACID）

  • 默认情况下，MySQL 执行每条 SQL 语句都会自动开启和提交事务

  • 为了让多条 SQL 语句纳入一个事务之下，可以手动管理事务

START TRANSACTION;
SQL语句
[COMMIT|ROLLBACK];

日志

利用 日志 来实现间接写入

  • MySQL 总共有 5 种日志文件，其中只有 redo 日志和 undo 日志与事务有关

特性（ACID）

指数据库管理系统（DBMS）在写入或更新资料的过程中，为保证事务（transaction）是正确可靠的，所必须具备的四个特性

  • 原子性：一个事务的所有操作要么全部完成，要么全部失败。事务执行后不允许停留在中间的某个状态
  • 一致性：不管在任何给定的时间、并发事务有多少，事务必须保证运行结果的一致性
  • 隔离性：要求事务不受其他并发事务的影响，如同在给定的时间内，该事务是数据库唯一运行的事务
  • 持久性：事务一旦提交，结果便是永久性的。即便发生宕机，仍然可以依靠食物日志完成数据的持久化

隔离级别

序号隔离级别功能
1read uncommitted读取为提交数据
2read committed读取已提交数据
3repeatable read （默认级别）重复读取
4serializable序列化

READ UNCOMMITTED

表示可以读取其他事务未提交的数据

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

READ COMMITTED

表示只能读取到其他事物提交的数据

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

REPEATABLE READ

表示事务在执行过程中反复读取数据，得到的结果是一致的，不受其他事务影响

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SERIALIZABLE

由于事务并发执行所带来的各种问题，前三种隔离级别只适用在某些业务场景中，但是序列化的隔离性，让事务逐一执行，可以避免一切业务场景上的问题，但并发性会下降，所以很少使用

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

数据导出与导入

数据导出

导出的纯粹是业务数据

  • 导出分类
    • SQL 文档
    • 文本文档
  • mysqldump 用来把业务数据导出成 SQL 文件，其中也包括了表结构
# 导出 SQL 文件，no-data 表示只导出表结构
mysqldump -uroot -p [no-data] 逻辑库 > 文件路径

# 导入 SQL 文件，source 命令用于导入 SQL 文件。包括创建数据表，写入记录等等
mysql> USE demo;
mysql> SOURCE test.sql;
  • 数据备份，备份的是数据文件、日志文件、索引文件等等
    • 全量备份
    • 增量备份

数据库范式

  • 构造数据库必须遵循一定的规则，这种规则就是范式
  • 目前关系型数据库有 6 种范式，一般情况下，只满足第三范式即可

第一范式

是数据库的基本要求，不满足这一点就不是关系数据库

  • 数据表的每一列都是不可分割的基本数据项
  • 同一列中不能有多个值，也不能存在重复的属性

第二范式

数据表中的每条记录必须是唯一的。为了实现区分，通常要为表加上一个列用来存储唯一标识，这个唯一属性列被称作 主键列

第三范式

  • 每列都与主键有直接关系，不存在传递依赖
  • 依照第三范式，数据可以拆分保存到不同的数据表，保持彼此关联
上次编辑于:
贡献者: sunzhenyang
今日事，今日毕！
Copyright © 2024 Yang