【mysql】基础知识总结

这里写目录标题

• • 数据存储方法
  • 数据库相关概念
  • • 数据库的好处？
    • 数据库的发展历程
    • 常见的关系型数据库产品
    • 对数据库的理解
  • 搭建mysql环境
  • sql概述
  • sql分类
  • DDL
  • • DDL - 操作数据库
    • DDL-操作数据表
    • • 数据类型
      • • 数值类型
        • 字符串类型
        • 日期时间类型
      • 基本操作
      • • 创建数据表
        • 修改表
        • 删除表
  • DML
  • • 插入表数据 - insert
    • 修改表数据 - update
    • 删除表数据-delete
  • 约束
  • • • 实体完整性约束
      • • 主键约束
        • 主键自增长
        • 唯一约束
      • 域完整性约束
      • • 非空约束
        • 默认值
      • 引用完整性约束
  • DQL
  • • 单表查询
    • • 基本查询
      • • 查询所有列
        • 查询指定列
        • 完全重复的记录只出现一次
        • 列运算
        • **别名**
      • 条件查询
      • 模糊查询
      • 排序
      • 聚合函数
      • 分组查询
      • Limit子句
    • 查询语句执行顺序
    • 多表查询
    • • 合并结果集
      • 内连接
      • 外连接
      • • 左外连接
        • 右外连接
        • 全外连接
    • 子查询
    • • where后
      • from后
  • DCL
  • • 创建用户
    • 用户授权
    • 查看用户权限
    • 撤销权限
    • 删除用户
    • 修改用户密码
  • TPL
  • • 什么是事务
    • MySQL中处理事务
    • 事务原理
    • 事务特性【记忆】
    • 事务隔离级别
    • • 事务并发读问题
      • 四大隔离级别简介
      • 隔离级别常见操作
      • 隔离级别演示
      • • 读未提交(READ UNCOMMITTED
        • 读已提交(READ COMMITTED
        • 可重复读(REPEATABLE READ
        • 串行化(SERIALIZABLE)
  • 视图
  • • 特点
    • 视图的操作
    • • 创建视图
      • 使用视图
      • 修改视图
      • 删除视图
      • 注意事项
  • 扩展学习：常用函数，变量——存储过程——函数

数据存储方法

先说几个点,通过之前的学习，我们能够使用以下两种方式进行数据存储：

1. 使用变量、对象、数组、集合存储数据，数据保存在内存（RAM）中；
2. 使用文件（File）存储数据，保存在硬盘上。

数据库相关概念

数据库就是用来存储和管理数据的仓库。

数据库的好处？

1. 可以持久化存储数据
2. 方便存储和管理数据
3. 可存储大量数据；
4. 使用了统一的方式操作数据库 – SQL
5. 方便检索；

数据库的发展历程

• 无数据库，使用磁盘文件存储数据；
• 层次结构数据库：IBM公司IMS（Information Management System）定向有序的树状结构实现存储和访问；
• 网状结构数据库：美国通用电气公司IDS（Integrated Data Store），以节点形式存储和访问；
• 关系结构数据库：Oracle、DB2、MySQL、SQL Server，使用二维表格来存储数据；
• 非关系型数据库：ElasticSearch、MongoDB、Redis，多数使用哈希表，表中以键值（key-value）的方式实现特定的键和一个指针指向的特定数据。

常见的关系型数据库产品

• Oracle（神谕）：美国Oracle（甲骨文）公司，主要用在电信，金融领域，下载免费，服务需要收费；
• DB2：IBM，主要用在金融领域；
• SQL Server：微软，只能用在微软平台上；
• MySQL：瑞典MySQL AB公司开发，属于 Oracle旗下产品，分为社区版和收费版，在国内互联网公司使用广泛。

对数据库的理解

我们通常所说的数据库其实是RDBMS（Relational Database Management System，关系型数据库管理系统），其包括两个部分：

• 管理员，Manager；
• 仓库，Database。

Database包括：N张表（Table）；

Table包括两个部分：

• 表结构：定义表的列名和列类型(理解成类)；
• 表记录：一行一行的记录(理解成对象)。

搭建mysql环境

mysql环境的搭建

sql概述

SQL：Structured Query Language（结构化查询语言），其实就是定义了操作所有关系型数据库的规则,客户端使用SQL来操作数据库，可以应用到所有关系型数据库中。

SQL语言标准由ISO（国际标准化组织）发布，ISO定义了很多SQL标准（例如SQL99）对RDBMS进行统一的操作，相同的语句可以操作Oracle，MySQL。各个数据库厂商有自己的标准，类似于方言，MySQL中的limit。

SQL语法：

• SQL语句可以在单行或多行书写，以分号结尾；
• 可使用空格和缩进来增强语句的可读性；
• MySQL不区分大小写，建议大写。
• 数据库的注释：
  • 单行注释：-- 注释内容 #注释内容(mysql特有)
  • 多行注释：/* 注释内容 */

经验：通常执行对数据库的“增删改查”，简称C（Create）R（Read）U（Update）D（Delete）。

sql分类

SQL分类

• DDL(Data Definition Language)数据定义语言
  • 创建、删除、修改库与表结构 => 用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等
• DML(Data Manipulation Language)数据操作语言
  • 用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等
• TPL(Transaction Process Language)事务处理语言：用于对事务进行处理；
• DQL(Data Query Language)数据查询语言
  • 用来查询数据库中表的记录(数据)。关键字：select, where 等
• DCL(Data Control Language)数据控制语言(了解)
  • 用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1lzdD6Ls-1635561153625)(asset/img/01_mysql/image-20210929203914953.png)]

DDL

DDL - 操作数据库

自带数据库

数据库名称	描述
information_schema	信息数据库，其中保存着关于所有数据库的信息（元数据），元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。
mysql	核心数据库，主要负责存储数据库的用户、权限设置、关键字等，以及需要使用的控制和管理信息，不可以删除。
performance_schema	性能优化的数据库，MySQL 5.5版本中新增的一个性能优化的引擎。
sys	系统数据库，MySQL5.7版本中新增的可以快速的了解元数据信息的系统库，便于发现数据库的多样信息，解决性能瓶颈问题。

因为此部分的句子不常用,所以直接整理

命令	解释
show databases	查看所有的数据库
use sys	切换到想要操作的数据库,这里切换到sys数据库
select database()	查询当前使用的数据库
CREATE DATABASE mydb1;	创建名字为mydb1数据库
CREATE DATABASE mydb2 CHARACTER SET utf8;	创建数据库并设置编码格式为utf8
CREATE DATABASE IF NOT EXISTS mydb3;	如果mydb3数据库不存在，则创建；如果存在，则不创建。
SHOW CREATE DATABASE mydb1;	查看数据库的创建信息
ALTER DATABASE mydb1 CHARACTER SET gbk;	修改数据库的字符集
DROP DATABASE mydb1;	删除数据库mydb1
DROP DATABASE IF EXISTS mydb2;	如果存在数据库mydb2就删除

DDL-操作数据表

数据类型

MySQL支持多种类型，大致可以分为三类：

• 数值；
• 字符串(字符)类型；
• 日期时间。

数据类型对于我们约束数据的类型有很大的帮助。

数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
INT	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
DOUBLE	8 字节	（-1.797E+308,-2.22E-308）	(0,2.22E-308,1.797E+308)	双精度浮点数值
DOUBLE(M,D)	8个字节，M表示长度，D表示小数位数	同上，受M和D的约束 DOUBLE(5,2) -999.99-999.99	同上，受M和D的约束	双精度浮点数值
DECIMAL(M,D)	DECIMAL(M,D)	依赖于M和D的值，M最大值为65	依赖于M和D的值，M最大值为65	小数值，和钱相关，不会出现精度缺失的问题

字符串类型

类型	大小	用途
CHAR	0-255字符	定长字符串CHAR(10)10个字符
VARCHAR	0-65535 字节	变长字符串VARCHAR(10)10个字符
BLOB（binary large object）	0-65535字节	二进制形式的长文本数据
TEXT	0-65535字节	长文本数据

关于CHAR和VARCHAR：

• CHAR(255) 数据长度不足指定长度，补足到指定长度，用于身份证号，手机号，时间固定长度的内容；
• VARCHAR(65535) 数据长度不足指定长度，不补足到指定长度，用于用户名，备注不固定长度的内容；
• VARCHAR单独至少花一个字节保存数据长度，如果长度超过一个字节，就要花费两个字节。

日期时间类型

类型	大小	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	- 838 : 59 : 59 / 838:59 : 59	HH : MM : SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00 : 00:00 / 9999-12-31 23 : 59 : 59	YYYY-MM-DD HH : MM : SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00 : 00 : 00 / 2038 结束时间是第 2147483647 秒北京时间 2038-1-19 1 1 : 14 : 07，格林尼治时间 2038年1月19日 凌晨 03 : 14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

基本操作

语法：

创建数据表

CREATE TABLE [IF NOT EXISTS] 表名（
    列名 数据类型 [约束],
    列名 数据类型 [约束],
    列名 数据类型 [约束]       //最后一列的末尾不加逗号
）[CHARSET=utf8];            //可根据需要指定表的字符编码集

修改表

# 添加列
ALTER TABLE 表名 ADD (
    列名 列类型,
    列名 列类型
)

# 修改列类型(如果被修改的列已存在数据，那么新的类型可能会影响到已存在数据), 修改表中的某列时，也要写全列的名字，数据类型，约束
ALTER TABLE 表名 MODIFY 列名 列类型;   

# 修改列名, 在给定列新名称时，要指定列的类型和约束
ALTER TABLE 表名 CHANGE 原列名 新列名 列类型;

# 删除列, 删除列时，每次只能删除一列
ALTER TABLE 表名 DROP 列名;

# 修改表名称
ALTER TABLE 原表名 RENAME TO 新表名;
ALTER TABLE 原表名 RENAME 新表名; 

删除表

DROP TABLE 表名;

DML

DML(Data Manipulation Language)数据操作语言：增、删、改表记录。

注意：DML是对表的内容进行操作，DDL是对表的结构进行操作，两者有本质区别。

插入表数据 - insert

用来在表中增加数据，增加的数据可能是一行，也可能是多行，但是不可能是半行。

# 在表名后给出要插入的列名，其他没有指定的列等同与插入null值。所以插入记录总是插入一行，不可能是半行。
# 在VALUES后给出列值，值的顺序和个数必须与前面指定的列对应
INSERT INTO 表名(列名1, 列名2, ...) VALUES(列值1, 列值2, ...);

INSERT INTO stu(id, name, age, gender, addr) VALUES(1, 'zs', 10, '男', '青岛市北区');
INSERT INTO stu(name, age, gender, addr) VALUES('ls', 10, '男', '青岛市南区');
INSERT INTO stu(name, age) VALUES("ww", 20);

# 没有给出要插入的列，那么表示插入所有列
# 值的个数必须是该表列的个数
# 值的顺序，必须与表创建时给出的列的顺序相同。
INSERT INTO 表名 VALUES(列值1, 列值2, ...);

INSERT INTO stu VALUES(1, 'zs', 10, '男', '青岛市北区');
INSERT INTO stu VALUES(2, 'ls', 10, '男', '青岛市南区');

# 一次插入多条数据
INSERT INTO 表名(列名1, 列名2, ...) VALUES(列值1, 列值2, ...), (列值1, 列值2, ...), (列值1, 列值2, ...);

INSERT INTO stu(name, age) VALUES('zs', 20), ('ls', 11), ('ww', 12);

修改表数据 - update

用来修改表中已有的数据。

# WHERE条件是可选的, 如果没有条件, 就修改所有记录, 多数时候我们都加上WHERE条件
UPDATE 表名 SET 列名1=列值1, 列名2=列值2, ...[WHERE 条件]

关于条件：

• 条件必须是一个boolean类型的值或表达式；
• 运算符:=、!=、<>、>、<、>=、<=、between…and… 、in(…)、is null、not、or、and；
• 和后面学习的删除数据及DQL中的条件通用。

UPDATE stu SET addr='qd', gender='男' WHERE age >= 18 AND age <= 80;
UPDATE stu SET addr='qd', gender='男' WHERE age <> 18;
UPDATE stu SET addr='qd', gender='男' WHERE age BETWEEN 18 AND 80;
UPDATE stu SET addr='qdsnq' WHERE name='zs' OR name='ls';
UPDATE stu SET addr='qdsnq' WHERE name IN('zhangsan','lisi');
UPDATE stu SET addr='qdsnq' WHERE age IS NOT null;

删除表数据-delete

用来删除表中已有的数据。

# WHERE条件是可选的, 如果没有条件, 就删除所有记录, 多数时候我们都加上WHERE条件
DELETE FROM 表名 [WHERE 条件];

# 删除id为1的学生
DELETE FROM stu WHERE id=1;

#与DELETE不加WHERE删除整表数据不同，TRUNCATE是把表销毁，再按照原表的格式创建一张新表
TRUNCATE TABLE 

约束

用于限制加入表的数据的类型和规范，约束是添加在列上的，用来约束列的。

分类：

• 实体完整性约束；
• 域完整性约束；
• 引用完整性约束。

下文整理

约束	说明
PRIMARY KEY	主键约束
PRIMARY KEY AUTO_INCREMENT	主键、自动增长
UNIQUE	唯一约束
NOT NULL	非空约束
FOREIGN KEY	外键约束
FOREIGN KEY ON UPDATE CASCADE	外键级联更新
FOREIGN KEY ON DELETE CASCADE	外键级联删除
default	默认值

实体完整性约束

主键约束

标识表中的一行数据，是该行数据的唯一标识，特性：

• 非空；
• 唯一；
• 被引用（在外键中使用）。

当表的某一列被指定为主键后，该列就不能为空，不能有重复值出现。

# 创建表时指定主键
CREATE TABLE 表名(
    列名 数据类型 PRIMARY KEY,
    列名 数据类型,
    列名 数据类型
);

# 创建表时指定主键
CREATE TABLE 表名(
    列名 数据类型,
    列名 数据类型,
    列名 数据类型,
    PRIMARY KEY(设置为主键的列名)
);

# 修改表时指定主键
ALTER TABLE 表名 ADD PRIMARY KEY(列名);
ALTER TABLE 表名 MODIFY 列名 列类型 PRIMARY KEY;
ALTER TABLE 表名 CHANGE 原列名 新列名 列类型 PRIMARY KEY;

# 删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;

主键自增长

通常指定主键类为整型，然后设置其自动增长，这样可以保证在插入数据时主键列的唯一和非空特性 。

# 创建表时指定主键
CREATE TABLE 表名(
    列名 数据类型 PRIMARY KEY AUTO_INCREMENT,
    列名 数据类型,
    列名 数据类型
);

# 修改表时设置主键自增长
ALTER TABLE 表名 CHANGE 原列名 新列名 列类型 PRIMARY KEY AUTO_INCREMENT;
ALTER TABLE 表名 MODIFY 列名 列类型 PRIMARY KEY AUTO_INCREMENT;

# 修改表时删除主键自增长
ALTER TABLE 表名 CHANGE 原列名 新列名 列类型;

唯一约束

某些列不能设置重复的值，所以可以对列添加唯一约束。

# 创建表时指定唯一约束
CREATE TABLE 表名(
    列名 数据类型 UNIQUE,
    列名 数据类型,
    列名 数据类型
);

# 修改表时设置唯一约束
ALTER TABLE 表名 MODIFY 列名 列类型 UNIQUE;
ALTER TABLE 表名 CHANGE 原列名 新列名 列类型 UNIQUE;

# 删除唯一约束
ALTER TABLE 表名 DROP INDEX name;

域完整性约束

非空约束

此列必须有值，某些列不能设置为NULL值

# 创建表时指定非空约束
CREATE TABLE 表名(
    列名 数据类型 NOT NULL,
    列名 数据类型,
    列名 数据类型
);

# 修改表时设置非空约束
ALTER TABLE 表名 MODIFY 列名 列类型 NOT NULL;
ALTER TABLE 表名 CHANGE 原列名 新列名 列类型 NOT NULL;

# 删除非空约束
ALTER TABLE 表名 MODIFY 列名 列类型;
ALTER TABLE 表名 CHANGE 原列名 新列名 列类型;

默认值

为列赋予默认值，当新增数据不指定值时，书写DEFAULT，以指定的默认值进行填充。

# 创建表时指定默认值
CREATE TABLE 表名(
    列名 数据类型 DEFAULT 默认值,
    列名 数据类型,
    列名 数据类型
);

# 修改表时设置默认值
ALTER TABLE 表名 MODIFY 列名 列类型 DEFAULT 默认值;
ALTER TABLE 表名 CHANGE 原列名 新列名 列类型 DEFAULT 默认值;

# 删除默认值
ALTER TABLE 表名 MODIFY 列名 列类型;
ALTER TABLE 表名 CHANGE 原列名 新列名 列类型;

引用完整性约束

这里说的引用完整性约束就是指的外键约束，作用：

• 引用外部表的某个列的值；
• 新增数据时，约束此列的值必须是引用表中已经存在的值。

外键的特点：

• 外键必须是另一表的主键的值(外键要引用主键！)；
• 外键可以重复；
• 外键可以为空；
• 一张表中可以有多个外键。

# 创建表时指定外键约束
CREATE TABLE 表名(
    列名 数据类型,
    列名 数据类型,
    列名 数据类型,
    CONSTRAINT 约束名称 FOREIGN KEY(外键列名) REFERENCES 关联表(关联表的主键)
);

# 修改表时添加外键约束
ALERT TABLE 表名 ADD CONSTRAINT 约束名称 FOREIGN KEY(外键列名) REFERENCES 关联表(关联表的主键);

# 修改表时删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 约束名称;

DQL

关系结构数据库是以表格（Table）进行数据存储，表格由“行”和“列”组成。

经验：执行查询语句返回的结果集是一张虚拟表。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XqWXvZiZ-1635561153638)(asset/img/01_mysql/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDc5NzE4Mg==,size_16,color_FFFFFF,t_70.png)]

单表查询

基本查询

查询所有列

# 查询所有列
SELECT * FROM 表名
# 查询所有部门的信息
SELECT * FROM dept;
# 查询所有学生的信息
SELECT * FROM student;

查询指定列

# 查询指定列
SELECT 列1 [, 列2, ...列N] FROM 表名;
# 查询所有部门的名称
SELECT dname FROM dept;
# 查询所有的学生姓名和年龄
SELECT sname, age FROM stu;
# 查询所有的学生姓名，年龄和省份
SELECT sname, age, province FROM stu;

完全重复的记录只出现一次

# 查询所有的工种
SELECT job FROM emp;

# 上面查询出的结果有多条是重复的，我们需要将重复记录去除，重复的记录只出现一次
# 去重操作
# 当查询结果中的多行记录一模一样时，只显示一行
# 一般查询所有列时很少会有这种情况，但只查询一列（或几列）时，这种可能就大了
SELECT DISTINCT job FROM emp;

列运算

算数运算

# 列可以进行加、减、乘、除运算
# 查询所有员工的姓名和工资提升50%之后的工资
SELECT ename, sal*1.5 FROM emp;

# 查询所有员工的姓名和总工资（工资加奖金）
SELECT ename, sal+comm FROM emp; # 注意这个结果有问题，后面会进行处理

字符串连接运算

# MySQL使用CONCAT()进行连接运算, 不能用“+”连接字符串
# 查询所有员工的姓名和工资
SELECT ename, CONCAT(sal, '$') FROM emp;

转换NULL值

# 在进行列运算时，如果某一列值为NULL，那么运算之后的结果也为NULL
# 如果我们不希望最终结果为NULL，那么我们需要在运算之前对NULL值进行转换
# 查询所有员工的姓名和总工资（工资加奖金, 如果奖金为NULL则按照奖金为0进行运算）
SELECT ename, sal+IFNULL(comm, 0) FROM emp;
# IFNULL(列名, 转换值)

别名

# 对列进行运算后，查询出的结果中的列名不便于阅读，这时需要给列名起一个别名
# 格式：列名 AS 别名
# AS也可以省略，格式：列名 别名
# 查询所有员工的姓名和总工资（工资加奖金, 如果奖金为NULL则按照奖金为0进行运算）
SELECT ename AS '姓名', sal+IFNULL(comm, 0) AS '总工资' FROM emp;
SELECT ename '姓名', sal+IFNULL(comm, 0) '总工资' FROM emp;
# 除了可以给列起别名，也可以给表起别名，在多表查询中会使用到为表起别名

条件查询

与前面介绍的UPDATE和DELETE语句一样，SELECT语句也可以使用WHERE子句来控制查询出的记录。

# 查询部门编号为20的所有员工的信息
SELECT * FROM emp WHERE deptno=20

# 查询工种为工程师的所有员工的信息
SELECT * FROM emp WHERE job='工程师'

# 查询有奖金的所有员工的信息
SELECT * FROM emp WHERE comm IS NOT NULL and comm <> 0;
SELECT * FROM emp WHERE comm IS NOT NULL and comm != 0;

模糊查询

模糊 – 不精确

"_"匹配一个任意字符，只匹配一个字符而不是多个

"%"匹配0~N个任意字符

模糊查询需要使用运算符：LIKE

# 查询姓“周”的所有员工的信息
SELECT * FROM emp WHERE ename LIKE '周%';

# 查询姓名中包含“杰”的所有员工的信息
SELECT * FROM emp WHERE ename LIKE '%杰%';

# 查询姓“周”并且姓名只有三个字的所有员工的信息
SELECT * FROM emp WHERE ename LIKE '周__';

排序

有些时候我们需要对查询出的结果进行排序

排序分成升序（ASC）和降序（DESC），可以使用多列作为排序条件

排序使用关键字ORDER BY

# 排序
# 规则：列名 升序/降序
SELECT 列1, 列2, 列3 FROM 表名 WHERE 条件 ORDER BY 规则1, 规则2,....,规则n　

# 查询所有员工信息，按照工号升序排列
SELECT * FROM emp ORDER BY empno ASC;
# 如果是升序，ASC可以省略
SELECT * FROM emp ORDER BY empno;

# 如果设置了多个排序规则，如果规则1顺序相同则按照顺序2排序, 如果规则2顺序相同则按照顺序3排序...
# 查询所有员工信息，按照工资升序排列，如果工资相同，按照工号降序排列
SELECT * FROM emp ORDER BY sal ASC, empno DESC;

聚合函数

聚合函数做某列的纵向运算, 为NULL项不参与运算.

函数	功能
COUNT	计算个数
MAX	最大值
MIN	最小值
AVG	平均值
SUM	和

# 查询公司员工个数
SELECT count(1) FROM emp;
SELECT count(*) FROM emp;

# 查询公司最高工资
SELECT MAX(sal) FROM emp;

# 查询公司最低工资
SELECT MIN(sal) FROM emp;

# 查询公司所有员工工资和
SELECT SUM(sal) FROM emp;

# 查询公司员工工资的平均值
SELECT AVG(sal) FROM emp;

分组查询

分组查询是把记录使用某一列进行分组，然后查询组信息。

分组查询查的是组信息，不能带“个人”信息。

组信息包括：

• 分组列
• 聚合函数

分组查询使用GROUP BY关键字。

# 查询每个工种的平均工资
SELECT job, AVG(sal) FROM emp GROUP BY job;

# 查询每个工种的
SELECT job, COUNT(1) FROM emp GROUP BY job;

分组查询也可以设置条件，分组后的条件使用HAVING, 分组前的条件使用WHERE

# 查询工资大于15000的员工的工种，以及工种的平均工资
SELECT job, AVG(sal) FROM emp WHERE sal>15000 GROUP BY job;

# 查询工资大于15000的员工的工种，以及工种的平均工资，只显示超过两人的工种
SELECT job, AVG(sal) FROM emp WHERE sal>15000 GROUP BY job HAVING COUNT(*)>=2;

Limit子句

多用在分页查询中

LIMIT用来限定查询结果的起始行，以及查询行数

# 语法
SELECT 列名 FROM 表名 LIMIT 起始行，查询行数;

# 查询员工表中前五名员工的所有信息
# 起始行是从 0 开始，代表了第一行
SELECT * FROM emp LIMIT 0, 5;

#查询员工表中从第4条开始，查询10行
SELECT * FROM emp LIMIT 3,10;

LIMIT多数时候用在分页查询中

起始行计算公式：（当前页-1）* 每页的记录数

# 查询员工表中第一页信息，显示五条
SELECT * FROM emp LIMIT 0, 5;

# 查询员工表中第二页信息，显示五条
SELECT * FROM emp LIMIT 5, 5;

查询语句执行顺序

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

多表查询

使用多表关联查询的原因–查询的数据分布在多个表中

合并结果集

合并结果集就是把两个select语句的查询结果合并到一起，结果集就是一个表格。

要求：被合并的两个结果：列数必须相同。

# UNION：去除重复记录
SELECT * FROM t1 UNION SELECT * FROM t2;
# UNION ALL：不去除重复记录
SELECT * FROM t1 UNION ALL SELECT * FROM t2;

内连接

# 方式1(MySQL特有，不符合SQL标准)
SELECT 列名 FROM 表1, 表2 WHERE 表1.列名 条件运算符 表2.列名 [AND 条件];
# 方式2(符合SQL标准)
SELECT 列名 FROM 表1 INNER JOIN 表2 ON 表1.列名 条件运算符 表2.列名 [WHERE 条件];

# 列出员工的姓名和部门名称
SELECT e.ename, d.dname FROM emp e, dept d WHERE e.deptno=d.deptno;
SELECT e.ename, d.dname FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;

上面的查询只能查询出拥有部门的员工和拥有员工的部门，没有部门的员工和没有员工的部门是查询不到的。如果要将所有的员工和部门都查询出来需要使用外连接。

外连接

结果集中包含主表所有数据行，如果主表的某行在从表中没有匹配行时，则从表的选择列为NULL值。

左外连接

左外连接是以左表为主表，去关联右表(从表)，结果集中包含主表所有数据行，如果主表的某行在从表中没有匹配行时，则从表的选择列为NULL值。

# 语法
SELECT 列名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 左表.列名  条件运算符 右表.列名 [WHERE 条件];

# 列出员工的姓名和部门名称, 包括没有部门的员工
SELECT e.ename, d.dname FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno;

# 列出员工的姓名和部门名称, 包括没有员工的部门
SELECT e.ename, d.dname FROM dept d LEFT JOIN emp e ON e.deptno=d.deptno;

右外连接

右外连接是以右表为主表，去关联左表(从表)，结果集中包含主表所有数据行，如果主表的某行在从表中没有匹配行时，则从表的选择列为NULL值。

# 语法
SELECT 列名 FROM 左表 RIGHT [OUTER] JOIN 右表 ON 左表.列名 条件运算符 右表.列名 [WHERE 条件];

# 列出员工的姓名和部门名称, 包括没有员工的部门
SELECT e.ename, d.dname FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;

# 列出员工的姓名和部门名称, 包括没有部门的员工
SELECT e.ename, d.dname FROM dept d RIGHT JOIN emp e ON e.deptno=d.deptno;

全外连接

完全连接左表和右表中所有行，当某行数据在另一个表中没有匹配时，则另一个表的选择列值为NULL。

# 语法
SELECT 列名 FROM 左表 FULL [OUTER] JOIN 右表 ON 左表.列名 条件运算符 右表.列名 [WHERE 条件]

MySQL不支持这种语法，可以使用合并结果集进行模拟全外连接。

SELECT e.ename, d.dname FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno
UNION
SELECT e.ename, d.dname FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;

子查询

子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个或两个以上SELECT，那么就是子查询语句了。

子查询出现的位置：

• WHERE后，作为条件的一部分；
• FROM后，作为被查询的一条表。

where后

# 查询工资最高的员工的信息
SELECT * FROM emp WHERE emp.sal=(最高薪资);
# 查询员工的最高薪资
SELECT MAX(sal) FROM emp;
# 综合
SELECT * FROM emp WHERE emp.sal=(SELECT MAX(sal) FROM emp);


# 查询和文员在同一部门的所有员工的信息
SELECT * FROM emp WHERE emp.deptno=(文员的部门编号);
# Tom的部门编号
SELECT DISTINCT deptno FROM emp WHERE emp.job='文员';
# 综合
SELECT * FROM emp WHERE emp.deptno IN (SELECT DISTINCT deptno FROM emp WHERE emp.job='文员');


# 查询高于30部门所有人工资的员工详细信息
SELECT * FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=30);
# 查询高于30部门任意一人工资的员工详细信息
SELECT * FROM emp WHERE sal>ANY(SELECT sal FROM emp WHERE deptno=30);
# 查询工资、部门与周杰伦完全相同的员工信息
SELECT * FROM emp WHERE (sal, deptno) IN (SELECT sal, deptno FROM emp WHERE ename='周杰伦');

from后

# 列出在每个部门名称、员工数量、平均工资
SELECT
    d.dname, e.cnt, e.avgsal
FROM
    (SELECT deptno, COUNT(*) cnt, AVG(sal) avgsal FROM emp GROUP BY deptno) e, dept d
WHERE e.deptno=d.deptno;

DCL

数据控制语言 (Data Control Language) 在SQL语言中，是一种可对数据访问权进行控制的指令，它可以控制特定用户账户对数据表、查看表、存储程序、用户自定义函数等数据库对象的控制权。

创建用户

# 语法
CREATE USER 用户名@地址 IDENTIFIED BY '密码';

# 创建用户user1，密码设置为1234
CREATE USER user1@localhost IDENTIFIED BY '1234';

用户授权

# 语法
GRANT 权限1, …, 权限n ON 数据库.* TO 用户名;

# 将mydb1下的所有表的特定权限都赋给user1
GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT ON mydb1.* TO user1@localhost;
# 将mydb1下的所有表的所有权限都赋给user1
GRANT ALL ON mydb1.* TO user1@localhost;

查看用户权限

# 语法
SHOW GRANTS FOR 用户名@主机名;

# 查看user1的权限
SHOW GRANTS FOR user1@localhost;

撤销权限

# 语法
REVOKE 权限1, … , 权限n ON 数据库.* FORM 用户名

# 撤销user1的在数据库mydb1上的部分权限
REVOKE create,alter,drop ON mydb1.* from user1@localhost;
# 撤销user1的在数据库mydb1上的所有权限
REVOKE ALL ON mydb1.* from user1@localhost;

删除用户

# 语法
DROP USER 用户名;

# 删除用户user1
DROP USER user1@localhost;

修改用户密码

# 语法
USE mysql;
UPDATE USER SET authentication_string=PASSWORD('密码') WHERE USER='用户名' and host='IP';
FLUSH PRIVILEGES;

# 修改user1的密码为user1
USE mysql;
UPDATE USER SET authentication_string=PASSWORD('user1') WHERE USER='user1' and host='localhost';
FLUSH PRIVILEGES;

TPL

什么是事务

一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

事务要处理的问题，把多个对数据库的操作绑定成一个事务，要么都成功，要么都失败。

MySQL中处理事务

MySQL中处理事务涉及三个操作：

• 开启事务
• 提交事务
• 回滚

# 开启事务
START TRANSACTION;

# 多个SQL操作

# 提交事务
COMMIT;
# 回滚
ROLLBACK;

在执行SQL语句之前，先执行START TRANSACTION，这就开启了一个事务（事务的起点），然后可以去执行多条SQL语句;

后要结束事务，COMMIT表示提交，即事务中的多条SQL语句所做出的影响会持久化到数据库中，或者ROLLBACK，表示回滚，即回滚到事务的起点，之前做的所有操作都被撤消了；

# 开启事务
START TRANSACTION;

# 多个SQL操作
# A账户转账给B账户1000元
# A账户减1000元
UPDATE account SET money=money-1000 WHERE cardnum='10001';
# B账户加1000元
UPDATE account SET money=money+1000 WHERE cardnum='10002';

# 提交事务
COMMIT;
# 回滚
ROLLBACK;

事务原理

数据库会为每一个客户端都维护一个空间独立的缓存区(回滚段)，一个事务中所有的增删改语句的执行结果都会缓存在回滚段中，只有当事务中所有SQL语句均正常结束（COMMIT），才会将回滚段中的数据同步到数据库。否则无论因为哪种原因失败，整个事务将回滚（ROLLBACK）。

事务特性【记忆】

原子性（Atomicity）：事务中所有操作作为一个整体，是不可再分割的原子单位。事务中所有操作要么全部执行成功，要么全部执行失败，如果操作失败则不能对数据库有任何影响。。

一致性（Consistency）：事务执行后，数据库状态与其它业务规则保持一致。如转账业务，无论事务执行成功与否，参与转账的两个账号余额之和应该是不变的。

隔离性（Isolation）：隔离性是指在并发操作中，不同事务之间应该隔离开来，使每个并发中的事务不会相互干扰。

即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。

持久性（Durability）：一旦事务提交成功，事务中所有的数据操作都必须被持久化到数据库中，即使提交事务后，数据库马上崩溃，在数据库重启时，也必须能保证通过某种机制恢复数据。

事务隔离级别

事务并发读问题

在现代关系型数据库中，事务机制是非常重要的，假如在多个事务并发操作数据库时，如果没有有效的机制进行避免就会导致出现脏读，不可重复读，幻读。

脏读：

• 在事务A执行过程中，事务A对数据资源进行了修改，事务B读取了事务A修改后的数据；
• 由于某些原因，事务A并没有完成提交，发生了回滚操作，则事务B读取的数据就是脏数据。
• 这种读取到另一个事务未提交的数据的现象就是脏读。

不可重复读：

• 事务B读取了两次数据资源，在这两次读取的过程中事务A修改了数据，导致事务B在这两次读取出来的数据不一致；
• 这种在同一个事务中，前后两次读取的数据不一致的现象就是不可重复读。

幻读：

• 幻读是针对数据**插入（INSERT）**操作来说的。
• 事务A查询某条数据不存在，事务B插入该条数据并提交；
• A再次查询该数据仍然不存在，但是无法插入成功，让用户感觉很魔幻，感觉出现了幻觉，这就叫幻读。

四大隔离级别简介

为了解决以上的问题，主流的关系型数据库都会提供四种事务的隔离级别。事务隔离级别从低到高分别是：

• 读未提交(READ UNCOMMITTED)
• 读已提交(READ COMMITTED)
• 可重复读(REPEATABLE READ)
• 串行化(SERIALIZABLE)

事务隔离级别等级越高，越能保证数据的一致性和完整性，但是执行效率也越低。所以在设置数据库的事务隔离级别时需要做一下权衡，MySQL默认是可重复读（REPEATABLE READ）的级别。

下表展示了四种隔离级别对并发读问题的解决程度。

隔离级别	脏读	不可重复读	幻读
读未提交(READ UNCOMMITTED)	可能	可能	可能
读已提交(READ COMMITTED)	不可能	可能	可能
可重复读(REPEATABLE READ)	不可能	不可能	可能
串行化(SERIALIZABLE)	不可能	不可能	不可能

隔离级别常见操作

查看事务隔离级别

select @@tx_isolation;
show variables like 'tx_isolation';

修改隔离级别

set [作用域] transaction isolation level [事务隔离级别]
# 作用域可以是SESSION或GLOBAL，SESSION只针对当前回话窗口，GLOBAL是全局的
# 隔离级别是read uncommitted、read committed、repeatable read、serializable这四种，不区分大小写
set [session | global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable}

# 案例，设置全局隔离级别为read uncommitted
set global transaction isolation level read uncommitted

设置全局隔离级别完成后，只对之后新起的SESSION才起作用，对已经启动SESSION无效。如果用Shell客户端那就要重新连接MySQL，如果用Navicat那就要创建新的查询窗口。

隔离级别演示

准备工作

USE test;

DROP TABLE IF EXISTS `tb_test`;
CREATE TABLE `tb_test`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ;

INSERT INTO `tb_test` VALUES (1, 1);
INSERT INTO `tb_test` VALUES (2, 2);
INSERT INTO `tb_test` VALUES (3, 3);

读未提交(READ UNCOMMITTED

任何事务对数据的修改都会第一时间暴露给其他事务，即使事务还没有提交。

# 设置全局隔离级别为read uncommitted
set global transaction isolation level read uncommitted；

重新启动两个窗口连接MySQL，分别代表A、B两个事务。两个事务分别用A、B代替。

A：启动事务，此时数据为初始状态

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set, 1 warning (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tb_test;
+----+------+
| id | num  |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
3 rows in set (0.00 sec)

B：启动事务，更新数据，但不提交

mysql> update tb_test set num=10 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tb_test;
+----+------+
| id | num  |
+----+------+
|  1 |   10 |
|  2 |    2 |
|  3 |    3 |
+----+------+
3 rows in set (0.00 sec)

A：再次读取数据，发现数据已经被修改了，这就是所谓的“脏读”

mysql> select * from tb_test;
+----+------+
| id | num  |
+----+------+
|  1 |   10 |
|  2 |    2 |
|  3 |    3 |
+----+------+
3 rows in set (0.00 sec)

B：回滚事务

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

A：再次读数据，发现数据变回初始状态

mysql> select * from tb_test;
+----+------+
| id | num  |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
3 rows in set (0.00 sec)

经过上面的实验可以得出结论，事务B更新了一条记录，但是没有提交，此时事务A可以查询出未提交记录。造成脏读现象。读未提交(READ UNCOMMITTED)是最低的隔离级别。

读已提交(READ COMMITTED

既然读未提交没办法解决脏数据问题，那么就有了读提交。读提交就是一个事务只能读到其他事务已经提交过的数据，也就是其他事务调用commit命令之后的数据。那脏数据问题迎刃而解了。

# 设置全局隔离级别为read committed
set global transaction isolation level read committed;

A：启动事务，此时数据为初始状态

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set, 1 warning (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tb_test;
+----+------+
| id | num  |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
3 rows in set (0.00 sec)

B：启动事务，更新数据，但不提交

mysql> update tb_test set num=10 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tb_test;
+----+------+
| id | num  |
+----+------+
|  1 |   10 |
|  2 |    2 |
|  3 |    3 |
+----+------+
3 rows in set (0.00 sec)

A：再次读取数据，发现还是之前不去到的数据，说明在读已提交(READ COMMITTED)级别下解决了脏读的问题

mysql> select * from tb_test;
+----+------+
| id | num  |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
3 rows in set (0.00 sec)

B：提交

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

A：再次读取数据，发现数据已经被修改了

mysql> select * from tb_test;
+----+------+
| id | num  |
+----+------+
|  1 |   10 |
|  2 |    2 |
|  3 |    3 |
+----+------+
3 rows in set (0.00 sec)

这就出现了一个问题，在同一事务中(本例中的事务A)，事务的不同时刻同样的查询条件，查询出来的记录内容是不一样的，事务B的提交影响了事务A的查询结果，这就是不可重复读，也就是读已提交隔离级别。

每个SELECT语句都有自己的一份快照，而不是一个事务一份，所以在不同的时刻，查询出来的数据可能是不一致的。

读已提交解决了脏读的问题，但是无法做到可重复读。

可重复读(REPEATABLE READ

可重复是对比不可重复而言的，上面说不可重复读是指同一事物不同时刻读到的数据值可能不一致。而可重复读是指，事务不会读到其他事务对已有数据的修改，及时其他事务已提交，也就是说，事务开始时读到的已有数据是什么，在事务提交前的任意时刻，这些数据的值都是一样的。但是，对于其他事务新插入的数据是读取不到，也无法插入相同的数据，这也就引发了幻读问题。

# 设置全局隔离级别为repeatable read
set global transaction isolation level repeatable read;

A：启动事务，此时数据为初始状态

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set, 1 warning (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tb_test;
+----+------+
| id | num  |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
3 rows in set (0.00 sec)

B：开启事务，修改数据并提交

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update tb_test set num=10 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

A：查询数据，发现和之前的查询结果相同。

mysql> select * from tb_test;
+----+------+
| id | num  |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
3 rows in set (0.00 sec)

以上可以看到，可重复读(REPEATABLE READ)隔离级别解决了不可重复读问题，但是目前存在幻读的问题。

将数据库表恢复成之前的状态，分别开启两个事务。

A：启动事务，此时数据为初始状态

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set, 1 warning (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tb_test;
+----+------+
| id | num  |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
3 rows in set (0.00 sec)

B：开启事务，添加数据，提交

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tb_test(num) values(4);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

A：查询，看不到新添加的数据，插入主键为4的数据，失败

mysql> select * from tb_test;
+----+------+
| id | num  |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
3 rows in set (0.00 sec)

mysql> insert into tb_test(id, num) values(4,4);
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'

串行化(SERIALIZABLE)

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。这种隔离级别很少使用

# 设置全局隔离级别为serializable
set global transaction isolation level serializable;

视图

视图，虚拟表，从一个表或多个表中查询出来的表，作用和真实表一样，包含一系列带有行和列的数据。视图中，用户可以使用SELECT语句查询数据，也可以使用INSERT，UPDATE，DELETE修改记录，视图可以使用户操作方便，并保障数据库系统安全。

特点

• 优点
• 简单化，数据所见即所得。
• 安全性，用户只能查询或修改他们所能见到得到的数据。
• 逻辑独立性，可以屏蔽真实表结构变化带来的影响。
• 缺点
• 性能相对较差，简单的查询也会变得稍显复杂。
• 修改不方便，特变是复杂的聚合视图基本无法修改。

视图的操作

创建视图

# 语法
CREATE VIEW 视图名 AS 查询语句;

# 创建emp_info视图，通过视图从emp表中查询员工编号、姓名、总工资
CREATE VIEW emp_info
AS
SELECT empno '编号', ename '姓名', sal+IFNULL(comm, 0) '总工资' FROM emp;

使用视图

# 查询编号为1003的员工信息
SELECT * FROM emp_info WHERE 编号=1003;

修改视图

# 语法 方式1
CREATE OR REPLACE VIEW 视图名 AS 查询语句;
# 语法 方式2
ALTER VIEW 视图名 AS 查询语句;

CREATE OR REPLACE VIEW emp_info
AS
SELECT empno '编号', ename '姓名', sal '基本工资' FROM emp;

ALTER VIEW emp_info
AS
SELECT empno '编号', ename '姓名', sal '基本工资' FROM emp;

删除视图

删除视图不会影响原表

# 语法
DROP VIEW 视图名;

DROP VIEW emp_info;

注意事项

视图不会独立存储数据，原表发生改变，视图也发生改变。没有优化任何查询性能。

如果视图包含以下结构中的一种，则视图不可更新

• 聚合函数的结果
• DISTINCT 去重后的结果
• GROUP BY 分组后的结果
• HAVING 筛选过滤后的结果
• UNION、UNION ALL 联合后的结果

扩展学习：常用函数，变量——存储过程——函数

常用函数

【mysql】扩展-变量_存储过程_函数