【mysql】基础知识总结
数据存储方法
先说几个点,通过之前的学习,我们能够使用以下两种方式进行数据存储:
- 使用变量、对象、数组、集合存储数据,数据保存在内存(RAM)中;
- 使用文件(File)存储数据,保存在硬盘上。
数据库相关概念
数据库就是用来存储和管理数据的仓库。
数据库的好处?
- 可以持久化存储数据
- 方便存储和管理数据
- 可存储大量数据;
- 使用了统一的方式操作数据库 – SQL
- 方便检索;
数据库的发展历程
- 无数据库,使用磁盘文件存储数据;
- 层次结构数据库: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环境
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 联合后的结果