数据库基础概念
数据库：数据存储的仓库
数据库管理系统：操作和管理数据库的大型软件
SQL：操作关系型数据库的编程语言，是一套标准
关系型数据库（RDBMS）
- 概念：建立在关系模型基础上，有多张相互连接的二维表组成的数据库
- 特点：
- 使用表存储数据，格式统一，便于维护
- 使用SQL语言操作，标准统一，使用方便
SQL
SQL通用语法
- SQL语句可以单行或者多行书写，以分号结尾
- SQL语句可以使用空格或者缩进来增强语句的可读性
- MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
- 注释：单行注释（ – 或者 #）；多行注释（ /* 注释内容 */ ）
SQL分类
| 分类 | 全称 | 说明 |
|---|---|---|
| DDL | Data Definition Language | 数据定义语言，用来定义数据库对象（数据库，表，字段） |
| DML | Data Manipulation Language | 数据操作语言，用来对数据库表中的数据进行增删改 |
| DQL | Data Query Language | 数据查询语言，用来查询数据库中表的记录 |
| DCL | Data Control Language | 数据控制语言，用来创建数据库用户、控制数据库的访问权限 |
DDL语句
数据库操作
-- 查询所有数据库
show databases;
-- 查询当前数据库
select database();
-- 创建数据库
create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];
-- 删除数据库
drop database [if exists] 数据库名;
-- 使用数据库
use 数据库名;
表操作
-- 创建表
create table tableName(
字段1 字段1类型[comment 字段1注释],
字段2 字段2类型[comment 字段2注释],
...
字段n 字段n类型[comment 字段n注释]
)[comment 表注释];
-- 查询当前数据库所有表
show tables;
-- 查询表结构
desc 表名;
-- 查询指定表的建表语句
show create table 表名;
数据类型
MySQL中的数据类型主要分为三类：数值类型、字符串类型、日期类型。
- 数值类型
| 类型 | 大小 | 有符号范围（signed） | 无符号范围（unsigned） | 描述 |
|---|---|---|---|---|
| tinyint | 1 byte | (-128, 127) | (0, 255) | 小整数值 |
| smallint | 2 byte | (-32768, 32767) | (0, 65535) | 大整数值 |
| mediumint | 3 byte | (-8388608, 8388607) | (0, 16777215) | 大整数值 |
| int或integer | 4 byte | (-2147483648, 2147483647) | (0, 4294967295) | 大整数值 |
| bigint | 8 byte | (-2^63, 2^63-1) | (0, 2^64-1) | 极大整数值 |
| float | 4 byte | (-3.402823466 E+38, 3.402823466351 E+38) | 0 和 (1.175494351 E-38, 3.402823466 E+38) | 单精度浮点数值 |
| double | 8 byte | (-1.7976931348623157 E+308, 1.7976931348623157 E+308) | 0 和 (2.2250738585072014 E-308, 1.7976931348623157 E+38) | 双精度浮点数值 |
| decimal | 依赖于M(精度)和D(标度)的值 | 依赖于M(精度)和D(标度)的值 | 小数值（精确定点数） |
- 字符串类型
| 类型 | 大小 | 描述 |
|---|---|---|
| char | 0-255 | 定长字符串 |
| varchar | 0-65535 | 变长字符串 |
| tinyblob | 0-255 | 二进制数据 |
| tinytext | 0-255 | 短文本字符串 |
| blob | 0-65535 | 二进制形式的长文本数据 |
| text | 0-65535 | 长文本数据 |
| mediumblob | 0-16777215 | 二进制形式的中等长度文本数据 |
| mediumtext | 0-16777215 | 中等长度文本数据 |
| longblob | 0-4294967295 | 二进制形式的极大文本数据 |
| longtext | 0-4294967295 | 极大文本数据 |
- 日期类型
| 类型 | 大小 | 范围 | 格式 | 描述 |
|---|---|---|---|---|
| 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:01 至 2038-01-19 03:14:07 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值，时间戳 |
-- 添加字段
alter table 表名 add 字段名 类型(长度) [comment 注释][约束];
-- 修改字段数据类型
alter table 表名 modify 字段名 新数据类型(长度);
-- 修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释][约束];
-- 删除字段
alter table 表名 drop 字段名;
-- 修改表名
alter table 表名 rename to 新表名;
-- 删除表
drop table [if exists] 表名;
-- 删除指定表，并重新创建该表
truncate table 表名;
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，...);
-- 修改数据
update 表名 set 字段名1 = 值1, 字段名2 = 值，...[where 条件];
-- 删除数据
delete from 表名 [where 条件];
DQL语句
-- DQL语法
select
字段列表
from
表名列表
where
条件列表
group by
分组字段列表
having
分组后条件列表
order by
排序字段列表
limit
分页参数
--------------------------------------------
-- 查询多个字段
select 字段1,字段2,字段3,... from 表名;
select * from 表名;
--说明：尽量不使用```*```，其效率低且不直观
-- 设置别名
select 字段1 [as 别名1],字段2 [as 别名2],... from 表名;
-- 去除重复记录
select distinct 字段列表 from 表名;
-- 条件查询
select 字段列表 from 表名 where 条件列表;
| 比较运算符 | 功能 |
|---|---|
| > | 大于 |
| >= | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
| = | 等于 |
| <> 或 != | 不等于 |
| between … and … | 在某个范围之内（含最小，最大值） |
| in(…) | 在in之后的列表中的值都选择，类似or |
| like 占位符 | 模糊匹配（_ 匹配单个字符，%匹配任意个字符 |
| is NULL | 是NULL |
| 逻辑运算符 | 功能 |
|---|---|
| AND 或 && | 并且（多个条件同时成立） |
| OR 或 || | 或者（多个条件任意一个成立） |
| NOT 或 ! | 非，不是 |
聚合函数
将一列数据作为一个整体，进行纵向计算
| 函数 | 功能 |
|---|---|
| count | 统计数量 |
| max | 最大值 |
| min | 最小值 |
| avg | 平均值 |
| sum | 求和 |
| 语法： |
select 聚合函数(字段列表) from 表名;
-- null值不参与所有聚合函数运算
分组查询
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后的过滤条件];
where与having区别
- 执行时间不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤
- 判断条件不同：where不能对聚合函数进行判断，而having可以
例句：
1、根据性别分组，统计男性员工和女性员工的数量
select gender,count(*) from emp group by gender;
2、根据性别分组，统计男性员工和女性员工的平均年龄
select age,avg(age) from emp group by gender;
3、查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
select workAdd,count(*) from emp where age < 45 group by workAdd having count(*) >= 3;
排序查询
select 字段列表 from 表名 order by 字段1 排序方式,字段2 排序方式, ...;
排序方式：
- ASC：升序（默认）
- DESC：降序
如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。
分页查询
select 字段列表 from 表名 limit 起始索引，查询记录数;
起始索引从 0 开始，起始索引 = （查询页码 - 1）* 每页显示记录数
分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是limit
如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10，等价于 limit 0 10
DCL语句
-- 查询用户
use mysql;
select * from user;
-- 创建用户
create user '用户名'@'主机名' identified by '密码';
-- 修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';
-- 删除用户
drop user '用户名'@'主机名';
权限控制
MySQL中定义了很多种权限，常用的如下所示：
| 权限 | 说明 |
|---|---|
| all, all privileges | 所有权限 |
| select | 查询数据权限 |
| insert | 插入数据权限 |
| update | 修改数据权限 |
| delete | 删除数据权限 |
| alter | 修改表权限 |
| drop | 删除数据库/表/视图权限 |
| create | 创建数据库/表权限 |
1、查询权限
show grants for '用户名'@'主机名';
2、授予权限
grant 权限列表 on 数据库.表名 to '用户名'@'主机名';
3、撤销权限
revoke 权限列表 on 数据库.表名 from '用户名'@'主机名';
函数
字符串函数
MySQL中内置了很多字符串函数，常用的如下：
| 函数 | 功能 |
|---|---|
| concat(s1,s2,…) | 字符串拼接，将s1,s2,… 拼接成一个字符串 |
| 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 lower('Hello');
selectt rpad('11',5,'0');
数值函数
| 函数 | 功能 |
|---|---|
| ceil(x) | 向上取整 |
| floor(x) | 向下取整 |
| mod(x,y) | 返回 x/y 的模 |
| rand() | 返回 0~1 内的随机数 |
| round(x,y) | 求参数x的四舍五入的值，保留y位小数 |
select ceil(1.1);
select floor(1.9);
日期函数
| 函数 | 功能 |
|---|---|
| curdate() | 返回当前日期 |
| curtime() | 返回当前时间 |
| now() | 返回当前日期和时间 |
| year(date) | 获取指定date的年份 |
| month(date) | 获取指定date的月份 |
| day(date) | 获取指定date的日 |
| date_add(date,interval expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间值 |
| datediff(date1,date2) | 返回起始时间date1 和 结束时间date2之间的天数 |
select now();
-- 当前日期往后推70天/月/年
select date_add(now(), INTERVAL 70 DAY);
select date_add(now(), INTERVAL 70 MONTH);
select date_add(now(), INTERVAL 70 YEAR);
流程函数
| 函数 | 功能 |
|---|---|
| if(value, t, f) | 如果value为true，则返回t，否则返回 f |
| ifnull(value1, value2) | 如果value1不为空，则返回value1，否则返回value2 |
| case when [value1] then [result] … eles [default] end | 如果value1为true，返回result，…，否则返回default默认值 |
| case [expr] when [value1] then [result] … else [default] end | 如果expr的值等于value1，返回result，…，否则返回default默认值 |
约束
约束是作用于表中字段上的规则，用于限制存储在表中的数据。其目的是保证数据库中数据的正确，有效性和完整性。
| 约束 | 描述 | 关键字 |
|---|---|---|
| 非空约束 | 限制该字段的数据不能为null | not null |
| 唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | unique |
| 主键约束 | 主键是一行数据的唯一标识，要求非空且唯一 | primary key |
| 默认约束 | 保存数据时，如果未指定该字段的值，则采用默认值 | default |
| 检查约束 | 保证字段值满足某一个条件 | check |
| 外键约束 | 用来让两张表的数据之间建立连接，保证数据的一致性和完整性 | foreign key |
示例：
create table user(
id int primary key auto_increment comment '主键，唯一且自动增长',
name varchar(10) not null unique comment '姓名，不为空且唯一',
age int check(age > 0 && age < 150) comment '年龄，大于0且小于150',
status char(1) default '1' comment '状态，没有指定值则默认为1'
) comment '用户表'
外键约束
外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。
添加外键有两种，一种是创表时添加，一种是创建后添加
-- 第一种
create table 表名(
字段名 数据类型,
...
[constraint] [外键名称] foreign key (外键字段名) references 主表(主表列名)
);
-- 第二种
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名);
删除外键
alter table 表名 drop foreign key 外键名称;
删除/更新行为
指当数据表存在外键时，主表做删除或更新操作时，从表数据的变化。
| 行为 | 说明 |
|---|---|
| no action | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新 |
| restrict | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新 |
| cascade | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录 |
| set null | 当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null |
| set default | 父表有变更时，子表将外键列设置成一个默认的值（Innodb不支持 |
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名) on update cascade on delete cascade;
多表查询
连接查询
- 内连接：相当于查询A、B交集部分数据
- 隐式内连接：
select 字段列表 from 表1,表2 where 条件; - 显式内连接：
select 字段列表 from 表1 inner join 表2 on 连接条件;
外连接：
- 左外连接，查询左表所有数据，以及两张表交集部分数据；
select 字段列表 from 表1 left outer join 表2 on 连接条件; - 右外连接，查询右表所有数据，以及两张表交集部分数据
select 字段列表 from 表1 right outer join 表2 on 连接条件;
- 左外连接，查询左表所有数据，以及两张表交集部分数据；
自连接：当前表与自身的连接查询，自连接必须使用表别名
select 字段列表 from 表A 别名A join 表A 别名B on 条件;联合查询：union、union all
对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
select 字段列表 from 表A ...
union [all]
select 字段列表 from 表B ...;
对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。
- 子查询
SQL语句中嵌套select语句，称为嵌套查询，又称为子查询。
select * from t1 where column = (select column from t2);
子查询外部的语句可以是insert、update、delete、select 中的任何一个
根据子查询结果不同，可以分为：
标量子查询：子查询结果为单个值
常用的操作符：= <> > >= <=-- 查询销售部的所有员工信息 select * from emp where dept_id = (select id from dept where name = '销售部'); -- 查询在 ‘小王’ 入职之后的员工信息 select * from emp where entrydate > (select entrydate from emp where name='小王');列子查询：子查询结果为一列（可以是多行）
常用的操作符：in、not in、any、some、all操作符 描述 in 在指定的集合范围之内，多选一 not in 不在指定的集合范围之内 any 子查询返回列表中，有任意一个满足即可 some 与any等同，使用some的地方可以使用any all 子查询返回列表的所有值都必须满足 -- 查询“销售部”和“市场部”的所有员工信息 select * from emp where dept_id in (select id from dept where name='销售部' or name='市场部'); -- 查询比财务部所有人工资都高的员工信息 select * from emp where salary > all(select salary from emp where dept_id = (select id where dept where name='财务部')); -- 查询比财务部其中任意一人工资都高的员工信息 select * from emp where salary > any(select salary from emp where dept_id = (select id where dept where name='财务部'));行子查询：子查询结果为一行
常用操作符：= 、<>、in、not in-- 查询与“小张”的薪资相同且直属领导也相同的员工信息 select * from emp where (salary,managerid) = (select salary,managerid from emp where name='小张');表子查询：子查询结果为多行多列
常用字符：in-- 查询与“小路”，“小宋”的职位和薪资相同的员工信息 select * from emp where (job,salary) in (select job, salary from emp where name='小路' or name='小宋'); -- 查询入职日期是“2020-01-01”之后的员工信息及其部门信息 select e.*, d.* from (select * from emp where entrydate > '2020-01-01') e left join dept d on e.dept_id = d.id;
根据子查询位置，可以分为：where之后、from之后、select之后
事务
事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
第一种提交事务的方式
查看/设置事务提交方式
select @@autocommit; set @@autocommit = 0; -- 0表示关闭自动提交事务，1表示开启提交事务
commit;回滚事务
rollback;示例：
set @@autocommit = 0; -- 关闭自动提交事务 update account set money = money - 100 where name = 'tom'; update account set money = money + 100 where name = 'alice'; commit; -- 没有这条指令，数据库的内容不会进行更新 rollback; -- 如果出现问题，执行这条命令可以回退到刚才的状态
第二种提交事务的方式
start transaction
update account set money = money - 100 where name = 'tom';
update account set money = money + 100 where name = 'alice';
commit; -- 没有异常就进行提交
rollback; -- 有异常就进行回滚
事务的四大特性
原子性（Atomicity）： 事务时不可分割的最小操作单元，要么全部成功，要么全部失败
一致性（Consistency）： 事务完成时，必须使所有的数据都保持一致状态
隔离性（Isolation）： 数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
持久性（Durability）： 事务一旦提交或回滚，它对数据库中的数据的改变就是永久的
并发事务问题
脏读： 一个事务读到另外一个事务还没有提交的数据
不可重复读： 一个事务先后读取同一条记录，期间可能另一个事务对数据进行修改了，导致两次读取的数据不同，称之为不可重复读。
幻读： 一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了幻影
事务隔离级别
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| Read uncommitted | √ | √ | √ |
| Read committed | × | √ | √ |
| Repeatable Read(MySQL默认) | × | × | √ |
| serializable | × | × | × |
| × 表示已经解决了问题，√ 表示没有解决该问题 |
-- 查看事务隔离级别
select @@transaction_isolation
-- 设置事务隔离级别
set [session | global] transaction isolation level [read uncommitted | read committed | repeatable read | serializable]
-- session针对当前客户端的会话窗口有效，global表示对所有客户端的会话窗口有效
事务隔离级别越高，数据越安全，但是性能越低
