MySQL 基础篇
数据库相关概念
- 数据库（DataBase [DB] ）：存储数据的仓库，数据是有组织的进行存储
- 数据库管理系统（Data Management System [DBMS] ）：操作和管理数据库的大型软件
- SQL（Structured Query Language [SQL])：操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准
关系型数据库
建立在关系模型基础上，由多张相互连接的二维表组成的是数据库
特点：
- 使用表存储数据，格式统一，便于维护
- 使用 SQL 语言操作，标准统一，使用方便
SQL
SQL 通用语法
- SQL 语句可以单行或多行书写，以分号结束
- SQL 语法可以使用空格/缩进来增强语句的可读性
- MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写
- 注释
- 单行注释：– 注释内容 或 # 注释内容 (MySQL 特有)
- 多行注释：/* 注释内容 */
SQL 分类
代表关键字
DQL： SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
DDL: CREATE, ALTER, DROP
DML: INSERT, UPDATE, DELETE
DCL: GRANT, REVOKE
TPL（数据事务管理语言 [Transaction Processing Language]) 确保被 DML 语句影响的表的所有行为及时更新: BEGIN, TRANSACTION, COMMIT, ROLLBACK。
CCL（指针控制语言 [Cursor Control Language]) 用于对一个或多个表的独行操作: DECLARE, FETCH INTO, UPDATE WHERE CURRENT
DDL
数据库操作
查看所有数据库
SHOW DATABASES;
查看当前数据库
SELECT DATABASE();
创建数据库
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则]
删除数据库
DROP DATABASE [IF EXISTS] 数据库名
使用数据库
USE 数据库名
表操作-查询
查询当前数据库所有表：
SHOW TABLES;
查询表结构
DESC 表名；
查询指定表多的建表语句
SHOW CREATE TABLE 表名
表操作-创建
CREATE TABLE 表名 (
字段1 数据类型 [COMMENT 注释],
字段2 数据类型 [COMMENT 注释],
字段3 数据类型 [COMMENT 注释],
....
字段n 数据类型 [COMMENT 注释]
) [COMMENT 表注释];
表操作-数据类型
数值类型
精度就是多少个数字，标度就是小数点后几位
比如：123.12 标度精度是5，标度是 2
float：4 个字节，单精度（最多 5 位小数）
double：8 个字节， 双精度（最多 16 位小数）
decimal(m, d)【有效数字最多65， 小数位最多30】
无符号就是后面 + UNSIGNED
字符串类型
char 长度是0~255个字符
固定长度字符串，在定义时指定字符串列长。当保存时，在右侧填充空格以达到指定的长度。m表示列的长度，范围是 0～255 个字符。
例如，char(4) 定义了一个固定长度的字符串列，包含的字符个数最大为 4。当插入的字符长度大于4，则报错（除非超过4个长度之后都是空格字符，则空格字符会自动被删除用来保证插入的成功）。varchar：长度是0~16383个字符
长度可变的字符串。varchar 的最大实际长度由最长的行的大小和使用的字符集确定，而实际占用的空间为字符串的实际长度加 1。
例如，varchar(50) 定义了一个最大长度为 50 的字符串，如果插入的字符串只有 10 个字符，则实际存储的字符串为 10 个字符和一个字符串结束字符。varchar在值保存和检索时尾部的空格仍保留。char 性能高一点
日期时间类型
表操作-修改
添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释][约束]
修改字段类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度)
修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释][约束]
修改表名
ALTER TABLE 表名 RENAME TO 表名
表操作-删除
删除字段
ALTER TABLE 表名 DROP 字段名
删除表
DROP TABLE [IF EXISTS] 表名
删除指定表，并重新创建该表
TRUNCATE TABLE 表名
DML
DML-添加数据
给指定字段添加数据
INSERT INTO 表名 (字段名, 字段名2, ...) VALUES (值1, 值2, ...);
给全部字段添加数据
INSERT INTO 表名 VALUES (值1, 值2, ...);
批量添加字段
INSERT INTO 表名 (字段名, 字段名2, ...) VALUES (值1, 值2,...),(值1, 值2,...);
INSERT INTO 表名 VALUES (值1, 值2,...),(值1, 值2,...),(值1, 值2,...);
字符串和日期类型数据应该包含在引号中
DML-修改数据
UPDATE 表名 SET 字段1 = 值1， 字段2 = 值2, ...[WHERE 条件]
没有 WHERE 就会修改整张表的数据
DML-删除数据
DELETE FROM 表名 [WHERE 条件]
没有 WHERE 会删除整张表的数据
DELETE 语句不能删除某一个字段的值（可以使用 UPDATE)
DQL
DQL-基本查询
查询多个字段
SELECT 字段1, 字段2, 字段3... FROM 表名
SELECT * FROM 表名;
设置别名
SELECT 字段 [AS 别名], 字段2[AS 别名2]... FROM 表名
去除重复记录
SELECT DISTINCT 字段列表 FROM 表名
DQL-条件查询
SELECT 字段列表 FROM 表名 WHERE 条件列表;
is not null 就是不是 NULL
DQL-聚合函数
SELECT 聚合函数(字段列表) FROM 表名;
所有null值不参与聚合函数运算
DQL-分组查询
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤的条件]
where 与 having 区别
- 执行实际不同：where 是分组之前进行过滤，不满足 where 条件，不参与分组。而 havcing 是分组之后对结果进行过滤
- where 不能对聚合函数进行判断，而 having 可以
有 group by , select 后面只能出现聚合函数，和要分组的字段，其他不允许
DQL-排序查询
升序: ASC (默认值)
降序：DESC
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2...
如果是多字段排序，当第一个字段相同才根据第二个字段进行排序
DQL-分页查询
SELECT 字段列表 FROM 表名 LIMIT 起始起始索引,查询记录数
- 起始索引从 0 开始，起始索引 = （查询页码 -1）* 每页显示记录数
- 分页查询是数据库的方言，不同数据库有不同实现，MySQl 是 LIMIT
- 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10
DQL 执行顺序
DCL
DLC-管理用户
查询用户
USE mysql
SELECT * FROM user;
创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY 密码;
删除用户
DROP USER '用户名'@'主机名';
可以在任意主机/ip地址访问 用 %
DLC-权限控制
查询权限
SHOW GRANT FOR '用户名'@'主机名';
授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
撤销权限
REVOKE 权限列表 ON 数据库名 表名 FROM '用户名'@'主机名'
多个权限之间，使用逗号分隔
授权时，数据库名和表名可以使用 * 进行通配，代表所有
函数
字符串函数
-- hellomysql
select concat('hello','mysql') 拼接后;
-- hello
select lower('HELLO') 转小写;
-- HELLO
select upper('hello') 转大写;
-- ---01
select lpad('01', 5, '-');
-- 01---
select rpad('01', 5, '-');
-- hello world
select trim(' hello world ');
-- Hello
-- 下标从1开始
select substring('Hello MySQL', 1, 5);
数值函数
-- 2 向上取
select ceil(1.1);
-- 1 向下取
select floor(1.9);
-- 3 % 4 = 3
select mod(3, 4);
-- 0-1 之间随机数
select rand();
-- round 从小数的后一位四舍五入
select round(2.5447, 3);
日期函数
-- 返回当前日期: 2024-07-30
select curdate();
-- 返回当前时间: 12:04:52
select curtime();
-- 返回当前日期和时间: 2024-07-30 12:04:52
select now();
-- 获取指定 date 年份: 2024
select year(now());
-- 获取指定 date 月份: 7
select month(now());
-- 获取指定date的日期 30
select day(now());
-- 返回一个日期/往后推多少时间 2030-07-30 12:08:40
select date_add(now(), INTERVAL 6 YEAR);
-- 返回date1 和 date2 之间的天数 返回-1 前面日期-后面日期
select datediff(curdate(), '2024-7-31');
流程控制函数
-- true 返回 ok。
select if(true, 'ok', 'error');
-- 第一个值不为空，返回第一个值否则返回第二个：1
select ifnull(1, 2);
-- 查询 emp 的员工姓名和工作地址（北京/上海 ---> 一线, 其他 ----> 二线)
select
name,
(case workaddress
when '北京' then '一线城市'
when '上海' then '一线城市'
else '二线城市'
end) as '工作地址'
from emp;
-- 统计班级学院成绩
-- 85以上 优秀
-- 60以上 及格
-- 60以下 不及格
select
id,
name,
(case when math >= 85 then '优秀' when math >= 60 then '及格' else '不及格' end) as '数学',
(case when english >= 85 then '优秀' when english >= 60 then '及格' else '不及格' end) as '英语',
(case when chinese >= 85 then '优秀' when chinese >= 60 then '及格' else '不及格' end) as '语文'
from score;
约束
约束的描述信息，在information_schema 库中的 table_constraints表中
列级约束
直接在字段后面是列级约束
create table test (
id int 约束1 约束2.....,
name varchar(20) 约束1 约束2....
);
表级约束
定义完，再约束是表记约束。比如联合不能重复的时候就要用到
create table test (
id int,
name varchar(20),
//这里表示 id 和 name联合不能重复
unique(id, name)
);
例子
create table user (
id int primary key auto_increment comment 'ID唯一标识',
name varchar(10) not null unique comment '姓名',
age int check ( age > 0 && age <= 120 ) comment '年龄',
status char(1) default 1 comment '状态',
gender char(1) comment '性别'
) comment '用户表';
insert into user(name, age, status, gender) values ('Tom1', 19, '1','男'), ('Tom2', 25, '0', '男');
插入数据可以不用，写id字段，他会自动自增
多个约束之间不用逗号
外键约束
语法
-- 添加外键到 emp 表 别名为 fk_emp_dept_id 关联到父表dept 的 id
ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY REFERENCES dept(id)
-- 删除外键
ALTER TABLE emp DROP FOREIGN KEY fk_emp_dept_id
-- 表中的外键
create table user (
id int primary key auto_increment comment 'ID唯一标识',
name varchar(10) not null unique comment '姓名',
age int check ( age > 0 && age <= 120 ) comment '年龄',
status char(1) default 1 comment '状态',
gender char(1) comment '性别',
uid int,
constraint fk_user_dept_id foreign key (uid) references dept(id)
) comment '用户表';
只要外键关联了记录，父表直接删除会报错，保证了数据的一致性和完整性
constraint 是起别名，foreign key…references 是外键
删除/更新行为
on update 是在更新时怎样，delete 是删除
ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE;
多表查询
笛卡尔积现象
用关联的字段消除笛卡尔积现象
-- dept 的 id 字段是 emp 的 dept_id 的外键
select * from emp , dept where emp.dept_id = dept.id;
内连接
SELECT emp.name, emp.dept_id FROM emp, dept WHERE emp.dept_id = dept.id;
SELECT emp.name, dept.id FROM emp INNER JOIN dept ON emp.dept_id = dept.id;
如果给表起了别名，就不能再通过表名限定字段了
外连接
外连接是除了满足条件的记录查询出来，再将其中一张表的记录查询出来，另一张表如果没有与之匹配的记录，自动模拟出 NULL 与其匹配
自连接
看成两张表找关联的字段
联合查询
把多次查询的结果合并起来，形成新的查询结果
把 all 去掉自动去重
多张表的字段数量和类型必须保持一致
子查询
标量子查询
子查询返回的结果是单个值。
-- 查询 "方东白" 入职之后的员工信息
select * from emp where datediff(entrydate, (select entrydate from emp where name = '方东白') ) > 0;
select entrydate from emp where name = '方东白'
列子查询
子查询返回的结果是一列（可以是多行）
常用操作符：IN, NOT IN, ANY, SOME, ALL
行子查询
子查询返回的结果是一行（可以是多行）
常用操作符：=, <>, IN, NOT, IN
select managerid, salary from emp where name = '张无忌';
--查询与张无忌薪资和直属领导相同的员工信息
select * from emp where (managerid, salary) = (select managerid, salary from emp where name = '张无忌');
表子查询
子查询返回的结果是多行多列，一张表
常用操作符 ： IN
经常当临时表。或者 in
select * from emp where (job, salary) in (select job, salary from emp where name in ('鹿杖客', '宋远桥'));
job salary 这个组合要么满足下面，要么满足上面
事务
事物就是一个最小的工作单元，在数据库中，事物表示一件完整的事
例如：一个业务的完成可能需要多条DML语句共同配合才能完成，例如转账业务，需要执行两条DML语句，先更新张三账户的余额，再更新李四账户的余额，为了保证转账业务不出现问题，就必须保证要么同时成功，要么同时失败，怎么保证同时成功或者同时失败呢？就需要使用事务机制。
默认 mysql 事务在执行一条 DML 语句时会自动提交
事务操作
第一种方式：设置autocommit 为 0 手动提交
第二种方式手动开启事务
事务四大性质
**
**
事务只针对DML语句有效：因为只有这三个语句是改变表中数据的。
- insert
- delete
- update
并发事务引发的问题
脏读
一个事务读取了，另一个事务未提交的数据。就是读取到了另一个事务中的脏数据。在这种情况下，如果事物回滚或者修改了这些数据，那么读取这些脏数据的事务所处理的数据就是不准确的。
不可重复读
一个事务内，多次读取同一个数据行。得到的结果可能不一样。这是由于其他事务对数据进行了修改操作。导致数据的不一致性
幻读
事务执行过程中，前后两次相同的查询条件得到的结果不一，可能会变多或变少
事务隔离级别
- 隔离级别从低到高：读未提交 < 读提交 < 读可重复读 < 串行化
- 现象的严重程度从低到高：幻读 < 不可重复读 < 脏读
mysql 默认是 重复读，Oracal 默认是 读提交
语法
select @@transaction_isolation -查看当前会话隔离级别
select @@gobal.transaction_isolation -查看全局的隔离级别
会话级：set session transaction isolation level 事务隔离级别
全局级：set global transaction isolation level 事务隔离级别
事务隔离级别:
1.read uncommitted
2.read committed
3.repeatable read
4.serializable
读未提交（READ UNCOMMITTED)
A事务与B事务，A事务可以读取到B事务未提交的数据。这是最低的隔离级别。几乎两个事务之间没有隔离。这种隔离级别是一种理论层面的，在实际的数据库产品中，没有从这个级别起步的。
读提交 （READ COMMITTED)
A事务与B事务，A事务可以读取到B事务提交之后的数据。Oracle数据库默认的就是这种隔离级别。
可重复读 (REPECTABLE READ)
A事务和B事务，A事务开启后，读取了某一条记录，然后B事务对这条记录进行修改并提交，A事务读取到的还是修改前的数据。这种隔离级别称为可重复读。MySQL数据库默认就是这种隔离级别。
串行化 (SERIALIZABLE)
这种隔离级别最高，避免了所有的问题，缺点是效率低，因为这种隔离级别会导致事务排队处理，不支持并发。
解决幻读
可重复读，能很大程度上避免了幻读问题，但是并不能完全解决。
快照读解决幻读
普通的 select 语句都是采用快照读。(类似就是把那个时间段的表数据，保留下来。)
原理：
由 MVCC(多版本并发控制) 实现，实现的方式是开始事务后会创建一个 Read View 对象，后续查询语句利用整个 Read View，通过这个 Read View 就可以在 undo log 版本链找到快照查询执行时候的那个版本的数据。所以事务过程中每次快照查询数据都是一样的，即使中途有其他事务插入了新记录，是查询不出来这条数据的。避免了幻读
当前读解决幻读
当前读包括：update、delete、insert、select…for update。就是 DML 加上 for update。单独执行 DML 语句也会隐式执行当前读，
原理：
select…for update加的锁叫做：next-key lock。我们可以称其为：间隙锁 + 记录锁。间隙锁用来保证在锁定的范围内不允许insert操作。记录锁用来保证在锁定的范围内不允许delete和update操作。提交后锁失效
278
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
