本文为自学MySQL课程记录部分笔记
课程视频出处:黑马程序员 MySQL数据库入门到精通,从mysql安装到mysql高级、mysql优化全囊括 侵权即删
笔记内容如有错误,敬请指正
SQL
SQL通用语法
- SQL语句可以单行或多行书写,以分号结尾
- SQL语句可以使用空格/缩进来增强语句的可读性
- MYSQL数据库的SQL语句不区分大小写,关键字建议使用大写
- 注释
- 单行注释:
--注释内容
或#注释内容
(MYSQL特有) - 多行注释:
/*注释内容*/
- 单行注释:
SQL分类
分类 | 全称 | 说明 |
---|---|---|
DDL | data definition language | 数据定义语言,用来定义数据库对象(数据库,表,字段) |
DML | data mainpulation language | 数据操作语言,用来对数据库表中的数据进行增删改 |
DQL | data query language | 数据查询语言,用来查询数据库中表的记录 |
DCL | data control language | 数据控制语言,用来创建数据库用户,控制数据库的访问权限 |
DDL
- DDL-数据库操作
- 查询
查询所用数据库:SHOW DATABASES;
查询当前数据库:SELECT DATABASE();
- 创建:
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
- 删除:
DROP DATABASE [IF NOT EXISTS] 数据库名;
- 使用:
USE 数据库名;
- 查询
- DDL-表操作-查询
- 查询当前数据库所有表:
SHOW TABLES;
- 查询表结构:
DESC 表名;
- 查询指定表的建表语句:
SHOW CREATE TABLE 表名;
- 查询当前数据库所有表:
- DDL-表操作-创建
CREATE TABLE 表名(
字段1 字段1类型[COMMENT 字段1注释],
字段2 字段2类型[COMMENT 字段2注释],
字段3 字段3类型[COMMENT 字段3注释],
……
字段n 字段n类型[COMMENT 字段n注释]
)[COMMENT 表注释];
- DDL-表操作-数据类型
MySQL中的数据类型有很多,主要分为三类:数值类型,字符串类型,日期时间类型
分类 | 类型 | 大小 | 有符号(SIGNED)范围 | 无符号(UNSIGNED)范围 | 描述 |
---|---|---|---|---|---|
数值类型 | TINYINT | 1 byte | [-128,127] | [0,255] | 小整数值 |
数值类型 | SMALLINT | 2 bytes | [-32768,32767] | [0,65535] | 大整数值 |
数值类型 | MEDIUMINT | 3 bytes | [-8388608,8388607] | [0,16777215] | 大整数值 |
数值类型 | INT | 4 byte | [-2147483648,2147483647] | [0,4294967295] | 大整数值 |
数值类型 | BIGINT | 8 bytes | [-263,263-1] | [0,2^64-1] | 极大整数值 |
数值类型 | FLOAT | 4 bytes | 略 | 略 | 大整数值 |
数值类型 | DOUBLE | 8 byte | 略 | 略 | 小整数值 |
- DDL-表操作-修改
- 添加字段:
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
- 修改数据类型:
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
- 修改字段名和字段类型:
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
- 删除字段:
ALTER TABLE 表名 DROP 字段名;
- 修改表名:
ALTER TABLE 表名 RENAME TO 新表名;
- 添加字段:
- DDL-表操作-删除
- 删除表:
DROP TABLE [IF EXISTS] 表名;
- 删除指定表,并重新创建该表:
TRUNCATE TABLE 表名;
- 删除表:
DML
DML语句用来对数据库中表的数据记录进行增删改操作
- DML-添加数据
- 给指定字段添加数据:
INSERT INTO 表名 (字段名1,字段名2……) VALUES(值1,值2……);
- 给全部字段添加数据:
INSERT INTO 表名 VALUES(值1,值2……);
- 批量添加数据:
INSERT INTO 表名 (字段名1,字段名2……) VALUES(值1,值2……),(值1,值2……),(值1,值2……);
INSERT INTO 表名 VALUES(值1,值2……),(值1,值2……),(值1,值2……);
- 给指定字段添加数据:
- DML-修改数据
UPDATE 表名 SET 字段名1=值1,字段名2=值2,……[WHERE 条件];
- DML-删除数据
DELETE FROM 表名 [WHERE 条件];
DQL
DQL语句用来查询数据库中表的记录
- DQL语法
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表
LIMIT
分页参数
-
DQL-基本查询
- 查询多个字段:
SELECT 字段1,字段2,字段3…… FROM 表名;
或SELECT * FROM 表名;
- 设置别名:
SELECT 字段1 [AS 别名1],字段2 [AS 别名2]…… FROM 表名;
- 去除重复记录:
SELECT DISTINCT 字段列表 FROM 表名;
- 查询多个字段:
-
DQL-条件查询
- 语法
SELECT 字段列表 FROM 表名 WHERE 条件列表;
- 条件
比较运算符 功能 > 大于 >= 大于等于 < 小于 <= 小于等于 = 等于 <>或!= 不等于 BETWEEN……AND…… 在某个范围之内(含最小值,最大值) IN() 在in之后的列表中的值,多选一 LIKE 占位符 模糊匹配( _ 匹配单个字符, % 匹配任意个字符 IS NULL 是NULL 逻辑运算符 功能 AND或&& 并且(多个条件同时成立) OR (多个条件任意一个成立) NOT或! 非,不是 - 语法
-
DQL-聚合函数
- 介绍:将一列数据作为一个整体,进行纵向计算
- 常见聚合函数
函数 功能 count 统计数量 max 最大值 min 最小值 avg 平均值 sum 求和 - 语法:
SELECT 聚合函数(字段列表) FROM 表名;
-
DQL-分组查询
- 语法:
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
- where与having的区别:
执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组,而having是分组之后对结果进行过滤
判断条件不同:where不能对聚合函数进行判断,而having可以
注意:
执行顺序:where>聚合函数>having
分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义 - 语法:
-
DQL-排序查询
- 语法
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;
- 排序方式
- ASC:升序(默认)
- DESC:降序
注意:如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序
- 语法
-
DQL-分页查询
- 语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;
注意:
- 起始索引从0开始,起始索引=(查询页码-1)*每页显示记录数
- 分页查询是数据库的方言,不同的数据库有不同的实现,NySQL中是LIMIT
- 如果查询的是第一页数据,起始索引可以省略,直接简写为limit 10
- 语法
-
DQL-执行顺序
编写顺序:
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表
LIMIT
分页参数
执行顺序:
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
SELECT
字段列表
ORDER BY
排序字段列表
LIMIT
分页参数
DCL
DCL用来管理数据库,控制数据库的访问权限
-
DCL-管理用户
- 查询用户
USE mysql;
SELECT * FROM user;
- 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
- 修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
- 删除用户
DROP USER '用户名'@'主机名';
- 查询用户
-
DCL-权限控制
MySQL中定义了很多种权限,常用有以下几种权限 说明 ALL,ALL PRIVILEGES 所有权限 SELECT 查询数据 INSERT 插入数据 UPDATA 修改数据 DELETE 删除数据 ALTER 修改表 DROP 删除数据库/表/视图 CREATE 创建数据库/表 注意:主机名可以使用通配符%
- 查询权限:
SHOW GRANTS FOR '用户名'@'主机名';
- 授予权限:
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
- 撤销权限:
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
- 查询权限:
函数
函数是指一段可以直接被另一段程序调用的程序或代码
字符串函数
MySQL中内置了很多字符串函数,常用的函数如下:
函数 | 功能 |
---|---|
CONCAT(S1,S2……Sn) | 字符串拼接,将S1,S2……Sn拼接成一个字符串 |
LOWER(str) | 将字符串str全部转为小写 |
UPPER(str) | 将字符串str全部转为大写 |
LPAD(str,n,pad) | 左填充,用字符串pad对str的左边进行填充,达到n个字符串长度 |
RPAD(str,n,pad) | 右填充,用字符串pad对str的右边进行填充,达到n个字符串长度 |
TRIM(str) | 去掉字符串头部和尾部的空格 |
SUBSTRING(str,start,len) | 返回从字符串str从start位置起的len个长度的字符串 |
使用方法:SELECT 函数(参数);
数值函数
常见数值函数如下:
函数 | 功能 |
---|---|
CEIL(x) | 向上取整 |
FLOOR(x) | 向下取整 |
MOD(x,y) | 返回x/y的模 |
RAND() | 返回0~1内的随机数 |
ROUND(x,y) | 求参数x的四舍五入值,保留y位小数 |
日期函数
常见日期函数如下:
函数 | 功能 |
---|---|
CURDATE() | 返回当前日期 |
CURTIME | 返回当前时间 |
NOW() | 返回当前日期和时间 |
YEAR(data) | 获取指定data的年份 |
MONTH(data) | 获取指定data的月份 |
DAY(data) | 获取指定data的日期 |
DAY_ADD(data,INTERVAL expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间值 |
DATEDIFF(date1,date2) | 返回起始时间date1和结束时间data2之间的天数 |
流程函数
流程函数也是很常用的一类函数,可以在SQL语句中实现条件筛选,从而提高语句的效率
函数 | 功能 |
---|---|
IF(value,t,f) | 如果value为true,则返回t,否则返回f |
IFNULL(value1,value2) | 如果value1不为空,则返回value1,否则返回value2 |
CASE WHEN [val1] THEN [res1]……ELSE [default] END | 如果val1为true,则返回res1……,否则返回default默认值 |
CASE [expr] WHEN [val1] THEN [res1]……ELSE [default] END | 如果expr的值等于val1,则返回res1……,否则返回default默认值 |
约束
- 概念:约束是作用于表中字段上的规则,用于限制存储在表中的数据
- 目的:保证数据库中数据的正确,有效性和完整性
- 分类:
约束 | 描述 | 关键字 |
---|---|---|
非空约束 | 限制该字段的数据不能为null | NOT NULL |
唯一约束 | 保证该字段的所有数据都是唯一的,不重复的 | UNIQUE |
主键约束 | 主键是一行数据的唯一标识,要求非空且唯一 | PRIMARY KEY |
默认约束 | 保存数据时,如果未指定该字段的值,则采用默认值 | DEFAULT |
检查约束(8.0.16版本之后) | 保证字段值满足某一个条件 | CHECK |
外键约束 | 用来让两张表的数据之间建立连接,保证数据的一致性和完整性 | FOREIGN KEY |
外键约束
外键用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性
-
语法
- 添加外键
CREATE TABLE 表名( 字段名 数据类型, …… [CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名) );
或者:
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);
- 删除外键:
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
-
删除/更新行为
行为 | 说明 |
---|---|
NO ACTION | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新(与RESTRICT一致) |
RESTRICT | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新(与NO ACTION一致) |
CASCADE | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则也删除/更新外键在子表中的记录 |
SET NULL | 当在父表中删除对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null(要求外键允许取null) |
SET DEFAULT | 父表有变更时,子表将外键列设置成一个默认的值(innodb不支持) |
使用方法:ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段) REFERENCES 主表名(主表字段名) ON UPDATE 行为 ON DELETE 行为;
多表查询
多表关系
项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析设计表结构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为3种:一对多(多对一),多对多,一对一
- 一对多(多对一)
- 案例:部门与员工的关系
- 关系:一个部门对应多个员工,一个员工对应一个部门
- 实现:在多的一方建立外键,指向一的一方的主键
- 多对多
- 案例:学生与课程的关系
- 关系:一个学生可以选修多门课程,一门课程也可以供多个学生选择
- 实现:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
- 一对一
- 案例:用户与用户详情的关系
- 关系:一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升操作效率
- 实现:在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)
多表查询概述
多表查询分类:
- 连接查询
内连接:相当于查询A,B交集部分数据
外连接:
左外连接:查询左表中所用数据,以及两张表交集部分数据
右外连接:查询右表所有数据,以及两张表交集部分数据
自连接:当前表与自身的连接查询,自连接必须使用表别名 - 子查询
内连接
- 隐式内连接:
SELECT 字段列表 FROM 表1,表2 WHERE 条件;
- 显式内连接:
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件……;
- 内连接查询的是两张表交集的部分
外连接
- 左外连接:
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;
相当于查询表1(左表)的所有数据包含表1和表2交集部分的数据 - 右外连接:
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;
相当于查询表1(右表)的所有数据包含表1和表2交集部分的数据
自连接
- 语法:
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件;
- 自连接查询,可以是内连接查询,也可以是外连接查询
联合查询-union,union all
对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集
SELECT 字段列表 FROM 表A……
UNION [ALL]
SELECT 字段列表 FROM 表B……;
注意:
- UNION可以对两次的查询结果去重,UNION ALL只是将两次查询的结果 合并到了一起
- 对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致
子查询
- 概念:SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询
- 例如:
SELECT * FROM table1 WHERE column1=(SELECT column1 FROM table2);
- 子查询外部语句可以是INSERT/UPDATE/DELETE/SELECT中的任何一个
- 根据子查询结果不同,可分为:
- 标量子查询(子查询结果为单个值)
- 列子查询(子查询结果为一列)
- 行子查询(子查询结果为一行)
- 表子查询(子查询结果为多行多列)
- 标量子查询
子查询返回的结果是单个值(数字,字符串,日期等),最简单的形式,这种子查询称为标量子查询
常用操作符:= , <> , > , >= , < , <= - 列子查询
子查询返回的结果是一列(可以是多行),这种子查询称为列子查询
常用的操作符:IN,NOT IN,ANY,SOME,ALL
操作符 | 描述 |
---|---|
IN | 在指定的集合范围之内,多选一 |
NOT IN | 不在指定的集合范围之内 |
ANY | 子查询返回列表中,有任意一个满足即可 |
SOME | 与ANY等同,使用SOME的地方都可以使用ANY |
ALL | 子查询返回列表的所有值都必须满足 |
- 行子查询
子查询返回的结果是一行(可以是多列),这种子查询称为行子查询
常用操作符:= , <> , IN , NOT IN - 表子查询
子查询返回的结果是多行多列,这种子查询称为表子查询
常用操作符:IN
事务
事务简介
事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败
默认MySQL的事务是自动提交的,也就是说,当执行一条DML语句,MySQL会立即隐式的提交事务
事务操作
- 查看/设置事务提交方式
SELECT @@autocommit;
查看提交方式
SET @@autocommit=0;
设置提交方式,0为手动提交,1为自动提交 - 提交事务:
COMMIT;
- 回滚事务:
ROLLBACK;
- 开启事务:
START TRANSACTION
或BEGIN;
-- 开启事务
START TRANSACTION;
-- 设置提交方式为手动提交
SET @@autocommit=0;
-- 进行事务的操作
-- 如果操作能够执行,则提交事务
commit;
-- 如果中途报错,则回滚事务,将数据复原
rollback;
事务四大特性
- 原子性:事务是不可分割的最小操作单元,要么全部成功,要么全部失败
- 一致性:事务完成时,必须要使所有的数据保持一致状态
- 隔离性:数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行
- 持久性:事务一旦提交或回滚,它对数据库中的数据的改变就是永久的
并发事务问题
问题 | 描述 |
---|---|
脏读 | 一个事务读到另外一个事务还没有提交的数据 |
不可重复读 | 一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读 |
幻读 | 一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在 |
事务隔离级别
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
read uncommitted | √ | √ | √ |
read committed | × | √ | √ |
repeatable read(MySQL默认) | × | × | √ |
serializable | × | × | × |
√ 表示可以预防出现对应问题
-- 查看事务隔离级别
select @@transaction_isolation;
-- 设置事务隔离级别
set [session global] transaction isolation level [read uncommitted|read committed|repeatable read|serializable]