MySQL

基础指令

登陆MySQL：

1	mysql -u用户名 -p密码 [-h数据库服务器IP地址 -P端口号]

DDL

数据库

show databases;

select database();

use 数据库名;

create database [if not exists] 数据库名 [default charset utf8mb4];

drop database [if exists] 数据库名;

表结构

创建表：

create table 表名(
	字段1 字段类型 [约束] [comment '字段1注释'],
	......
	字段2 字段类型 [约束] [comment '字段2注释']
)[comment '表注释'];

例如：

create table user(
	id int primary key auto_increment comment 'ID,唯一标识',
	username varchar(50) not null unique comment '用户名',
    name varchar(10) not null comment '姓名',
    age int comment '年龄',
    gender char(1) default '男' comment '性别'
) comment '用户信息表';

常见的约束：

约束	描述	关键字
非空约束	限制字段不能为null	not null
唯一约束	数据唯一	unique
主键约束	主键是一行数据的唯一标识，要求非空且唯一	primary key
默认约束	未指定时使用默认值	default
外键约束	让两张表的数据建立连接，保证一致性、完整性	foreign key

数据类型：

数值类型：
- 整形分为tinyint、smallint、mediumint、int、bigint，分别占用1、2、3、4、8字节。
- 浮点数分为float、double，分别占用4、8字节，小数值decimal（不会出现精度损失）。float(5,2)中5表示整个数字长度，2表示小数位数。在类型后可以指定unsigned为无符号数。
字符串类型：
- char定长字符串：char(10)无论存储内容，均占用10个字符空间，性能比varchar略高。varchar变长字符串：varchar(10)最多占用10个字符空间，占用空间随存储内容变化。
- blob存储二进制数据
- text存储文本数据
日期类型：
- date：YYYY-MM-DD
- time：HH:MM:SS
- year：YYYY
- datetime：YYYY-MM-DD HH:MM:SS
- timestamp：YYYY-MM-DD HH:MM:SS，存储时间戳

表的查改删：

show tables; -- 查询当前数据库所有表
desc 表名; -- 查询表结构
show create table 表名; -- 查询建表语句

alter table 表名 add 字段名 字段类型 [约束] comment '注释'; -- 添加字段
alter table 表名 modify 字段名 字段类型 comment '注释'; -- 修改字段类型
alter table 表名 change 旧字段名 新字段名 新字段类型 comment '注释'; -- 修改字段名
alter table 表名 drop column 字段名; -- 删除字段

alter table 旧表名 rename to 新表名字; -- 修改表名
drop table 表名; -- 删除表

DML

数据的增删改：

添加数据：insert

-- 指定字段添加数据
insert into 表名(字段1, 字段2) values (值1, 值2), (值1, 值2);

-- 全部字段添加数据
insert into 表名 values (值1, 值2, ...), (值1, 值2, ...);

修改数据：update

1 2	-- 修改数据(注意where, 否则会对所有数据更新) update 表名 set 字段名1 = 值1, 字段名2 = 值2, ... [where 条件];

删除数据：delete

1 2	-- 删除数据(注意where, 否则会删除所有相关数据) delete from 表名 [where 条件];

注意将值设为null使用update。

DQL

数据的查询：select。

select 
	字段列表
from 
	表名列表
where
	条件列表
group by
	分组字段列表
having
	分组后条件列表
order by
	排序字段列表
limit
	分页参数

基本查询与条件查询

select 字段列表 from 表名;
select * from 表名; -- 不建议，会导致慢查询

select 字段1 [as] 别名1, 字段2 [as] '别名 2' from 表名; -- 为查询字段设置别名

select distinct 字段列表 from 表名; -- 去重查询

select 字段列表 from 表名 where 条件列表;

运算符	功能
`is null`	判断为`null`
`between min_val and max_val`	在指定范围内（包含边界）
`in (...)`	在多个值中匹配
`=`	等于
`!=`或`<>`	不等于
`like`	模糊匹配，`_`匹配单个字符，`%`匹配多个字符

分组查询

聚合函数：对列进行纵向计算，所有聚合函数不会统计null

函数
`count`	统计数量
`min`	求最小值
`max`	求最大值
`sum`	求和
`avg`	求均值

例如：

select count(id) from table1;

select count(*) from table1; -- 统计总数据量，推荐

select avg(salary) from table1;

分组查询：

1	select 分组字段/聚合函数(不能为*) from 表名 [where 条件列表] group by 分组字段名 [having 分组后过滤条件];

where对比having：

where在分组前执行，having在分组后对结果过滤
where不能对聚合函数判断

排序查询

1	select 字段名 from 表名 order by 排序字段排序方式;

排序方式

升序(asc)，默认
降序(desc)

分页查询

1	select 字段名 from 表名 limit 起始索引, 查询记录数;

limit为mysql专用的，起始索引从0开始，如果起始索引为0则可省略。前端一般传入页码，需要(页码-1)*每页展示数计算起始索引。

多表关系

一对多：在数据库表中多的一方，添加字段，来关联一的一方的主键。

建立数据库关联：在多的一方添加外键约束关联一的一方。

-- 创建表时添加约束
create table 表名(
	字段名 数据类型,
    ...
    [constraint] [外键名称] foreign key (外键字段名) references 主表 (字段名)
);

-- 建表后添加外键
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(字段名);

例如：

1	alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

删除外键约束：

1	alter table emp drop foreign key fk_emp_dept_id;

上述物理外键容易引发死锁问题，同时不适用于分布式数据库，因此目前更常用的方案是在业务层逻辑中解决外键关联问题，即使用逻辑外键，而数据库层面无关联。

一对一：多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率。

在任意一方加入外键关联另外一方的主键，并且设置外键为unique。

多对多：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。

多表查询

1	select * from 表名1, 表名2;

此时会组合表1、表2的所有信息。

通过多表之间的关联过滤：

1	select emp.id, emp.name from emp, dept where emp.dept_id = dept.id;

连接查询

内连接：查询A、B交集部分数据

-- 隐式内连接(常用)
select 字段列表 from 表1 [as] 别名1, 表2 [as] 别名2 where 连接条件 ...;

-- 显示内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件 ...;

外连接：

左外连接：查询左表所有数据（常用）

select 字段列表 from 表1 left [outer] join 表2 on 连接条件;

-- 输出所有员工姓名以及对应的部门名称
select e.name, d.name from emp e left outer join dept on e.dept_id = d.id;

右外连接：查询右表所有数据

1	select 字段列表 from 表1 right [outer] join 表2 on 连接条件;

子查询：SQL语句嵌套select语句，称为嵌套查询。

select * from tl where column1 = (select column1 from t2 ...);

-- 标量子查询
-- 查询最早入职的员工信息
select * from emp where entry_date = (select min(entry_date) from emp);
-- 查询在员工xxx之后入职的员工信息
select * from emp where entry_date > (select entry_date from emp where name = 'xxx');

-- 列子查询
-- 查询"教研部"和"咨询部"下所有的员工信息
select * from emp where dept_id = (select id from dept where name = '教研部' or name = '咨询部');

-- 行子查询
-- 查询与员工xxx的薪资与职位相同的员工信息
select * from emp where salary = (select salary from emp where name = 'xxx') and job = (select job from where name = 'xxx');
-- **简化写法**(推荐)
select * from emp where (salary, job) = (select salary, job from emp where name = 'xxx');

-- 表子查询
-- 获取每个部门中薪资最高的员工信息(将查询项也视为一个表)
select * from emp e, (select dept_id, max(salary) from emp group by dept_id) a where e.dept_id = a.dept_id and e.salary = a.salary;

案例

-- 查询"教研部"的男性员工, 且在"2011-05-01"之后入职的员工信息
select e.* from emp e, dept d where e.dept_id = d.id and d.name = '教研部' and e.gender = 1 and e.entry_date > '2011-05-01';
select * from emp where gender = 1 and dept_id = (select id from dept where name='教研部') and entry_date > ;

-- 查询工资低于公司平均工资且性别为男的员工信息
select * from emp where gender = 1 and salary < (select avg(salary) from emp);

-- 查询部门人数超过10人的部门名称
select d.name count(*) from emp e, dept d where e.dept_id = d.id group by d.name having count(*) > 10;

-- 查询在"2010-05-01"后入职, 且薪资高于10000的"教研部"员工信息, 并根据薪资倒序排序
select e.* from emp e, dept d where d.dept_id = d.id and d.entry_date > '2010-05-01' and e.salary > 10000 and d.name = '教研部' order by e.salary desc;
select * from emp where entry_data > '2010-05-01' and salary > 10000 and dept_id =
    (select id from dept where name = "教研部") order by salary desc;
    
-- 查询工资低于本部门平均工资的员工信息
select * from emp e, (select dept_id, avg(salary) from emp group by dept_id) a where e.dept_id = a.dept_id and e.salary < a.salary;

事务管理

事务是一组操作的集合，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，只会同时成功或同时失败。

三步操作：开启事务、提交事务/回滚事务。

start transaction;

...

commit/rollback;