MySQL
1、概述
1.1、为什么选择MySQL?
- 开放源代码,使用成本低
- 性能卓越,服务稳定
- 软件体积小,使用简单,易于维护
- 历史悠久,社区用户非常活跃,遇到问题可以寻求帮助
- 许多互联网公司再用,经过了时间的验证
1.2、Oracle 和 MySQL 区别:
Oracle 更适合大型跨国企业的使用,因为他们对费用不敏感,但是对性能要求以及安全性有更高的要求。
MySQL 由于其体积小、速度快、总体拥有成本低,可处理上千万条记录的大型数据库,尤其是开放源码这一特点,使得很多互联网公司、中小型网站选择了MySQL作为网站数据库(Facebook,Twitter,YouTube,阿里巴巴/蚂蚁金服,去哪儿,美团外卖,腾讯)。
1.3、关系型数据库(RDBMS)和非关系型数据库(非RDBMS)
1.3.1、关系型数据库(RDBMS)
- 行(row) 和 列(column) 形式存储这一系列行和列被称之为表
- 一组表组成一个库
- 现实世界实体与实体之间的联系用 关系模型 来表示
- 表与表之间的数据记录有关系,关系型数据库,就是建立在 关系模型 基础上的数据库。
- SQL 就是关系型数据库的查询语言
优势:
- 复杂查询 可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。
- 事务支持 使得对于安全性能很高的 数据访问要求 得以实现。
1.3.2、非关系型数据库(非RDBMS)
- 关系型数据库
阉割版本
, 基于键值对存储数据,不需要经过 SQL 层解析 - 减少不必要的功能, 提高性能,
性能非常高
非关系型数据库类型:
- 键值型数据库: 键值型数据库典型的使用场景是作为 内存缓存 。 Redis 是最流行的键值型数据库。(key作为查找标识,查找数据快,缺点是无法使用过滤,如果不知道数据具体位置,则需要遍历所有的key )
- 文档型数据库: 此类数据库可存放并获取文档,可以是XML、JSON等格式。(MongoDB)
- 搜索引擎数据库:
- 列式数据库:
- 图形数据库:
2、聚合函数
2.1、SELECT 的执行过程
2.1.1、查询的结构
#方式1:
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
#方式2:
SELECT ...,....,...
FROM ... JOIN ...
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
#其中:
#(1)from:从哪些表中筛选
#(2)on:关联多表查询时,去除笛卡尔积
#(3)where:从表中筛选的条件
#(4)group by:分组依据
#(5)having:在统计结果中再次筛选
#(6)order by:排序
#(7)limit:分页
2.1.2、SELECT 语句的执行顺序
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT
比如你写了一个 SQL 语句,那么它的关键字顺序和执行顺序是下面这样的:
SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
FROM player JOIN team ON player.team_id = team.team_id # 顺序 1
WHERE height > 1.80 # 顺序 2
GROUP BY player.team_id # 顺序 3
HAVING num > 2 # 顺序 4
ORDER BY num DESC # 顺序 6
LIMIT 2 # 顺序 7
2.1.3、SQL 的执行原理
SELECT 是先执行 FROM 这一步的。在这个阶段,如果是多张表联查,还会经历下面的几个步骤:
-
首先先通过 CROSS JOIN 求笛卡尔积,相当于得到虚拟表 vt(virtual table)1-1;
-
通过 ON 进行筛选,在虚拟表 vt1-1 的基础上进行筛选,得到虚拟表 vt1-2;
-
添加外部行。如果我们使用的是左连接、右链接或者全连接,就会涉及到外部行,也就是在虚拟
表 vt1-2 的基础上增加外部行,得到虚拟表 vt1-3。
当然如果我们操作的是两张以上的表,还会重复上面的步骤,直到所有表都被处理完为止。这个过程得到是我们的原始数据。
当我们拿到了查询数据表的原始数据,也就是最终的虚拟表 vt1 ,就可以在此基础上再进行 WHERE 阶段 。在这个阶段中,会根据 vt1 表的结果进行筛选过滤,得到虚拟表 vt2 。
然后进入第三步和第四步,也就是 GROUP 和 HAVING 阶段 。在这个阶段中,实际上是在虚拟表 vt2 的基础上进行分组和分组过滤,
得到中间的虚拟表 vt3 和 vt4 。
当我们完成了条件筛选部分之后,就可以筛选表中提取的字段,也就是进入到 SELECT 和 DISTINCT阶段 。
首先在 SELECT 阶段会提取想要的字段,然后在 DISTINCT 阶段过滤掉重复的行,分别得到中间的虚拟表 vt5-1 和 vt5-2 。
当我们提取了想要的字段数据之后,就可以按照指定的字段进行排序,也就是 ORDER BY 阶段 ,得到虚拟表 vt6 。
最后在 vt6 的基础上,取出指定行的记录,也就是 LIMIT 阶段 ,得到最终的结果,对应的是虚拟表vt7 。
当然我们在写 SELECT 语句的时候,不一定存在所有的关键字,相应的阶段就会省略。
同时因为 SQL 是一门类似英语的结构化查询语言,所以我们在写 SELECT 语句的时候,还要注意相应的
关键字顺序,所谓底层运行的原理,就是我们刚才讲到的执行顺序。
3、创建表
3.1、 创建方式1
必须具备:
CREATE TABLE权限
存储空间
语法格式:
CREATE TABLE [IF NOT EXISTS] 表名(
字段1, 数据类型 [约束条件] [默认值],
字段2, 数据类型 [约束条件] [默认值],
字段3, 数据类型 [约束条件] [默认值],
……
[表约束条件]
);
-
必须指定:
- 表名
- 列名(或字段名),数据类型,长度
-
可选指定:
- 约束条件
- 默认值
-
创建表举例1:
-- 创建表 CREATE TABLE emp ( -- int类型 emp_id INT, -- 最多保存20个中英文字符 emp_name VARCHAR(20), -- 总位数不超过15位 salary DOUBLE, -- 日期类型 birthday DATE );
-
创建表举例2:
CREATE TABLE dept( -- int类型,自增 deptno INT(2) AUTO_INCREMENT, dname VARCHAR(14), loc VARCHAR(13), -- 主键 PRIMARY KEY (deptno) );
UTF8每个汉字占用3个字节
3.2、SQL的分类
#SQL的分类
#DDL: 就是我们在创建表时用到的一些SQL语句。例如:CREATE、ALTER、DROP等。DDL主要是用在定义表 或者 改变表的物理结构、数据类型、表之间的链接和物理约束等初始化操作上。
# (定义数据库、表结构的语言)
DDL(data definition language):数据定义语言。CREATE \ ALTER \ DROP \ RENAME \ TRUNCATE
# DML: 就是我们经常用到的select、update、insert、delete 主要用来对数据库的数据进行的一些操作 也就是对数据的增删改查 就被称为 DML
# (操作表数据的语言)
DML(data manipulation language):数据操作语言。INSERT \ DELETE \ UPDATE \ SELECT (重中之重)
# DCL: 用来设置或者更改数据库用户角色权限等的语句
# (设置数据库用户、权限的语言)
DCL(Data Control Language):数据控制语言。COMMIT \ ROLLBACK \ SAVEPOINT \ GRANT \ REVOKE
4、约束
4.1、非空约束
作用: 限定某个字段/某列的值不允许为空
关键字: NOT NULL
特点:
- 默认,所有的类型的值都可以是NULL,包括INT、FLOAT等数据类型
- 非空约束只能出现在表对象的列上,只能某个列单独限定非空,不能组合非空
- 一个表可以有很多列都分别限定了非空
- 空字符串’'不等于NULL,0也不等于NULL
添加非空约束:
建表时:
CREATE TABLE 表名称(
字段名 数据类型,
字段名 数据类型 NOT NULL,
字段名 数据类型 NOT NULL
);
建表后:
alter table 表名称 modify 字段名 数据类型 not null;
删除非空约束:
alter table 表名称 modify 字段名 数据类型 NULL;#去掉not null,相当于修改某个非注解字段,该字段允
许为空
或
alter table 表名称 modify 字段名 数据类型;#去掉not null,相当于修改某个非注解字段,该字段允许为空
4.2、唯一约束
作用: 用来限制某个字段/某列的值不能重复
关键字: UNIQUE
特点:
- 同一个表可以有多个唯一约束。
- 唯一约束可以是某一个列的值唯一,也可以多个列组合的值唯一。
- 唯一性约束允许列值为空。
- 在创建唯一约束的时候,如果不给唯一约束命名,就默认和列名相同。
- MySQL会给唯一约束的列上默认创建一个唯一索引。
添加唯一约束:
建表时:
create table 表名称(
字段名 数据类型,
字段名 数据类型 unique,
字段名 数据类型 unique key,
字段名 数据类型
);
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] unique key(字段名)
);
建表后:
#字段列表中如果是一个字段,表示该列的值唯一。如果是两个或更多个字段,那么复合唯一,即多个字段的组合是唯
#一的
#方式1:
alter table 表名称 add unique key(字段列表);
#方式2:
alter table 表名称 modify 字段名 字段类型 unique;
删除唯一约束:
ALTER TABLE 表名 DROP INDEX 唯一约束名
4.3、PRIMARY KEY 约束
作用: 用来唯一标识表中的一行记录。
关键字: primary key
特点:
- 一个表最多只能有一个主键约束,建立主键约束可以在列级别创建,也可以在表级别上创建。
- 主键约束对应着表中的一列或者多列(复合主键)
- 如果是多列组合的复合主键约束,那么这些列都不允许为空值,并且组合的值不允许重复
- MySQL的主键名总是PRIMARY,就算自己命名了主键约束名也没用。
- 当创建主键约束时,系统默认会在所在的列或列组合上建立对应的主键索引(能够根据主键查询的,就根据主键查询,效率更高)。如果删除主键约束了,主键约束对应的索引就自动删除了。
- 需要注意的一点是,不要修改主键字段的值。因为主键是数据记录的唯一标识,如果修改了主键的值,就有可能会破坏数据的完整性。
添加主键约束:
建表时指定主键约束:
create table 表名称(
字段名 数据类型 primary key, #列级模式
字段名 数据类型,
字段名 数据类型
);
create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] primary key(字段名) #表级模式
);
建表后增加主键约束:
ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表); #字段列表可以是一个字段,也可以是多个字段,如果是多
个字段的话,是复合主键
删除主键约束:
alter table 表名称 drop primary key;
4.4、DEFAULT 约束
作用: 给某个字段/某列指定默认值,一旦设置默认值,在插入数据时,如果此字段没有显式赋值,则赋值为默
认值。
关键字: DEFAULT
给字段添加默认值:
建表时添加默认值:
create table 表名称(
字段名 数据类型 primary key,
字段名 数据类型 unique key not null,
字段名 数据类型 unique key,
字段名 数据类型 not null default 默认值,
);
create table 表名称(
字段名 数据类型 default 默认值 ,
字段名 数据类型 not null default 默认值,
字段名 数据类型 not null default 默认值,
primary key(字段名),
unique key(字段名)
);
说明:默认值约束一般不在唯一键和主键列上加
建表后添加默认值:
alter table 表名称 modify 字段名 数据类型 default 默认值;
#如果这个字段原来有非空约束,你还保留非空约束,那么在加默认值约束时,还得保留非空约束,否则非空约束就被
删除了
#同理,在给某个字段加非空约束也一样,如果这个字段原来有默认值约束,你想保留,也要在modify语句中保留默
认值约束,否则就删除了
alter table 表名称 modify 字段名 数据类型 default 默认值 not null;
删除DEFAULT约束:
alter table 表名称 modify 字段名 数据类型 ;#删除默认值约束,也不保留非空约束
alter table 表名称 modify 字段名 数据类型 not null; #删除默认值约束,保留非空约束
5、视图
5.1、创建视图
在 CREATE VIEW 语句中嵌入子查询
CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名称 [(字段列表)]
AS 查询语句
[WITH [CASCADED|LOCAL] CHECK OPTION]
精简版
CREATE VIEW 视图名称
AS 查询语句
5.2、查看视图结构
语法1:查看数据库的表对象、视图对象
SHOW TABLES;
语法2:查看视图的结构
DESC / DESCRIBE 视图名称;
语法3:查看视图的属性信息
# 查看视图信息(显示数据表的存储引擎、版本、数据行数和数据大小等)
SHOW TABLE STATUS LIKE '视图名称'\G
执行结果显示,注释Comment为VIEW,说明该表为视图,其他的信息为NULL,说明这是一个虚表。
语法4:查看视图的详细定义信息
SHOW CREATE VIEW 视图名称;
5.3、修改视图
方式1:使用CREATE OR REPLACE VIEW 子句修改视图
CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS
SELECT employee_id, first_name || ' ' || last_name, salary, department_id
FROM employees
WHERE department_id = 80;
方式2:ALTER VIEW
ALTER VIEW 视图名称
AS
查询语句
5.4、删除视图
DROP VIEW IF EXISTS 视图名称;
DROP VIEW IF EXISTS 视图名称1,视图名称2,视图名称3,...;
6、变量、流程控制和游标
6.1、变量
6.1.1、系统变量
查看系统变量:
#查看所有全局变量
SHOW GLOBAL VARIABLES;
#查看所有会话变量
SHOW SESSION VARIABLES;
#或
SHOW VARIABLES;
#查看满足条件的部分系统变量。
SHOW GLOBAL VARIABLES LIKE '%标识符%';
#查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%标识符%';
查看指定系统变量:
#查看指定的系统变量的值
SELECT @@global.变量名;
#查看指定的会话变量的值
SELECT @@session.变量名;
#或者
SELECT @@变量名;
修改系统变量值:
方式一:修改MySQL 配置文件 ,继而修改MySQL系统变量的值(该方法需要重启MySQL服务)
方式二:在MySQL服务运行期间,使用“set”命令重新设置系统变量的值
#为某个系统变量赋值
#方式1:
SET @@global.变量名=变量值;
#方式2:
SET GLOBAL 变量名=变量值;
#为某个会话变量赋值
#方式1:
SET @@session.变量名=变量值;
#方式2:
SET SESSION 变量名=变量值;
6.1.2、用户变量
6.1.2.1、会话用户变量
变量定义:
#方式1:“=”或“:=”
SET @用户变量 = 值;
SET @用户变量 := 值;
#方式2:“:=” 或 INTO关键字
SELECT @用户变量 := 表达式 [FROM 等子句];
SELECT 表达式 INTO @用户变量 [FROM 等子句];
查看用户变量值:
SELECT @用户变量
6.1.2.2、局部变量
定义:可以使用 DECLARE 语句定义一个局部变量
作用域:仅仅在定义它的 BEGIN … END 中有效
位置:只能放在 BEGIN … END 中,而且只能放在第一句
BEGIN
#声明局部变量
DECLARE 变量名1 变量数据类型 [DEFAULT 变量默认值];
DECLARE 变量名2,变量名3,... 变量数据类型 [DEFAULT 变量默认值];
#为局部变量赋值
SET 变量名1 = 值;
SELECT 值 INTO 变量名2 [FROM 子句];
#查看局部变量的值
SELECT 变量1,变量2,变量3;
END
定义变量:
DECLARE 变量名 类型 [default 值]; # 如果没有DEFAULT子句,初始值为NULL
变量赋值:
方式1:一般用于赋简单的值
SET 变量名=值;
SET 变量名:=值;
方式2:一般用于赋表中的字段值
SELECT 字段名或表达式 INTO 变量名 FROM 表;
使用变量:
SELECT 局部变量名;
6.2、定义条件和处理程序
相当于Java异常处理
6.2.1、定义条件
DECLARE 错误名称 CONDITION FOR 错误码(或错误条件)
MySQL_error_code 和 sqlstate_value 都可以表示MySQL的错误。
- MySQL_error_code是数值类型错误代码。
- sqlstate_value是长度为5的字符串类型错误代码。
例如,在ERROR 1418 (HY000)中,1418是MySQL_error_code,'HY000’是sqlstate_value。
例如,在ERROR 1142(42000)中,1142是MySQL_error_code,'42000’是sqlstate_value。
6.2.2、定义处理程序
DECLARE 处理方式 HANDLER FOR 错误类型 处理语句
-
处理方式:处理方式有3个取值:CONTINUE、EXIT、UNDO。
- CONTINUE :表示遇到错误不处理,继续执行。
- EXIT :表示遇到错误马上退出。
- UNDO :表示遇到错误后撤回之前的操作。MySQL中暂时不支持这样的操作。
-
错误类型(即条件)可以有如下取值:
- SQLSTATE ‘字符串错误码’ :表示长度为5的sqlstate_value类型的错误代码;
- MySQL_error_code :匹配数值类型错误代码;
- 错误名称 :表示DECLARE … CONDITION定义的错误条件名称。
- SQLWARNING :匹配所有以01开头的SQLSTATE错误代码;
- NOT FOUND :匹配所有以02开头的SQLSTATE错误代码;
- SQLEXCEPTION :匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码;
-
处理语句:如果出现上述条件之一,则采用对应的处理方式,并执行指定的处理语句。语句可以是
像“ SET 变量 = 值 ”这样的简单语句,也可以是使用 BEGIN … END 编写的复合语句。
#方法1:捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';
#方法2:捕获mysql_error_value
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';
#方法3:先定义条件,再调用
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'NO_SUCH_TABLE';
#方法4:使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';
#方法5:使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';
#方法6:使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';
6.3、流程控制
6.3.1、分支结构
6.3.1.1、分支结构之 IF
IF语法结构:
IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2]……
[ELSE 操作N]
END IF
6.3.1.2、分支结构之 CASE
语法结构一:
#情况一:类似于switch
CASE 表达式
WHEN 值1 THEN 结果1或语句1(如果是语句,需要加分号)
WHEN 值2 THEN 结果2或语句2(如果是语句,需要加分号)
...
ELSE 结果n或语句n(如果是语句,需要加分号)
END [case](如果是放在begin end中需要加上case,如果放在select后面不需要)
语法结构二:
#情况二:类似于多重if
CASE
WHEN 条件1 THEN 结果1或语句1(如果是语句,需要加分号)
WHEN 条件2 THEN 结果2或语句2(如果是语句,需要加分号)
...
ELSE 结果n或语句n(如果是语句,需要加分号)
END [case](如果是放在begin end中需要加上case,如果放在select后面不需要)
6.3.2、循环结构
6.3.2.1、循环结构之 LOOP
LOOP语法结构:
[loop_label:] LOOP
循环执行的语句
END LOOP [loop_label]
6.3.2.2、循环结构之 WHILE
WHILE语法结构:
[loop_label:] LOOP
循环执行的语句
END LOOP [loop_label]
6.3.2.3、循环结构之 REPEAT
REPEAT语法结构:
[repeat_label:] REPEAT
循环体的语句
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label]
6.3.3、跳转语句
6.3.3.1、跳转语句之 LEAVE
LEAVE语法结构:
# label参数表示循环的标志。LEAVE和BEGIN ... END或循环一起被使用
LEAVE 标记名
6.3.3.2、跳转语句之 ITERATE
ITERATE语法结构:
# label参数表示循环的标志。ITERATE语句必须跟在循环标志前面
ITERATE label
6.4、游标
声明游标:
DECLARE cursor_name CURSOR FOR select_statement;
打开游标:
OPEN cursor_name
使用游标:
# 游标的查询结果集中的字段数,必须跟 INTO 后面的变量数一致
FETCH cursor_name INTO var_name [, var_name] ...
关闭游标:
CLOSE cursor_name
6.5、MySQL 8.0的新特性—全局变量的持久化
方式一: 修改MYSQL配置文件
方式二:
# MySQL 8.0版本新增了 SET PERSIST 命令
# 举例:
SET PERSIST global max_connections = 1000;
7、触发器
创建触发器:
CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW
触发器执行的语句块;
说明:
-
表名 :表示触发器监控的对象。
-
BEFORE|AFTER :表示触发的时间。BEFORE 表示在事件之前触发;AFTER 表示在事件之后触发。
-
INSERT|UPDATE|DELETE :表示触发的事件。
- INSERT 表示插入记录时触发;
- UPDATE 表示更新记录时触发;
- UPDATE 表示更新记录时触发;
-
触发器执行的语句块 :可以是单条SQL语句,也可以是由BEGIN…END结构组成的复合语句块。
查看和删除触发器:
# 查看触发器
# 方式1:查看当前数据库的所有触发器的定义
SHOW TRIGGERS;
# 方式2:查看当前数据库中某个触发器的定义
SHOW CREATE TRIGGER salary_check_trigger;
# 方式3:从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息。
SELECT * FROM information_schema.TRIGGERS;
# 删除触发器
DROP TRIGGER IF EXISTS 触发器名称;