【MySQL】超详细入门学习

MySQL

关系型数据库（RDBMS）

• 建立在关系模型基础上，由多张相互连接的二维表组成的数据库

特点：

• 使用表存储数据，格式统一，便于维护
• 使用SQL语言操作，标准统一，使用方便

一、SQL

1.SQL分类

图形化界面工具

2.DDL

1）DDL-数据库操作

• showdatabases; :查询所有的数据库
• select database(); : 查询当前数据库是哪个
• create database [is not exists] 数据库名 [default charset 字符集] [collate 排序规则]; : 创建数据库
• drop database [is exists] 数据库名; : 删除指定数据库
• use 数据库名； ：使用指定的数据库

2）DDL-表操作

1.创建

create table 表名（字段1 字段1类型[comment 字段1注释],字段2 字段2类型[comment 字段2注释],...
)[comment 表注释];

2.查询

* 查询当前数据库中的所有表：`show tables;`
* 查询表结构： `desc 表名;`
* 查询指定表的建表语句：`show create table 表名;`

3.数据类型

数值类型：

字符串类型：

• char(10): 指定长度的话，存入的字符不管有没有10个，都会占用10个字符的空间
• varchar(10): 长度可以变，存入多少个字符就占用多少个字符的空间

日期时间类型：

表结构的创建：

4.修改

• alter table 表名 add 字段名 字段类型(长度) [comment 字段的注释]; ：给表添加字段
• alter table 表名 modify 字段名 新数据类型(长度);：修改指定字段的数据类型
• alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束];：修改字段名和字段类型
• alter table 表名 drop 字段名;：删除字段
• alter table 表名 rename 新表名;：修改表名
  

5.删除

会清空表中的数据

• drop table [if exists] 表名;：删除指定表
• truncate table 表名;：删除指定表，并重新创建该表结构

---

3.DML

DataManipulation Language（数据操作语言），用来对数据库中表的数据进行增删改查操作

• insert：插入数据
• update：修改数据
• delete：删除数据

1）插入数据

• insert into 表名 (字段1，字段2，...) values (值1，值2，...);：给指定字段添加数据
• insert into 表名 values (值1， 值2,....);：给所有字段添加数据

批量添加：

• insert into 表名 (字段1，字段2，...) values (值1，值2，...), (值1， 值2，...);
• insert into 表名 values (值1，值2，...), (值1， 值2,...);

insert into user(id, name, nickname) values (1, 'zhangsan', 'san');select * from user;insert into user values (1, 'lisi', 'si');insert into user (id, name, nickname) values (1, 'wagnwu', 'wu'), (2,'zhaoliu', 'liu');

注意：

• 插入数据时，指定的字段顺序需要与值的顺序是一一对应的
• 字符串和日期型数据应该包含在引号中
• 插入数据的大小应该在字段的规定范围内

2）修改数据

• update 表名 set 字段名1=值1， 字段名2=值2，...[where 条件];：修改指定字段的值

3）删除数据

• delete from 表名 [where 条件]; :删除满足条件的数据

• 注意：

  • delete语句的条件可以有也可以没有，如果没有条件，则会删除整张表的所有数据
  • delete语句不能删除某一个字段的值（可以使用update）

4.DQL

• Data Query Language（数据查询语言），用来查询数据库中的数据记录

• select

1）基本查询

• select 字段1,字段2,.... from 表名; :查询多个字段
• select * from 表名; ： 查询所有字段
• select 字段1 as 别名1，字段2 as 别名2，... from 表名;：起别名
• select distinct 字段1 from 表名;：去重

2）条件查询

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

3） 聚合函数

• 将一列数据作为一个整体，进行纵向计算
  语法
  select 聚合函数(字段列表）from 表名;

• 注意：null值不参与聚合函数的运算

4）分组查询

• select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];

• where与having区别

  • 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组，而having是分组之后对结果进行过滤
  • 判断条件不同：where不能对聚合函数进行判断，而having可以

• 注意：

  • 执行顺序：where > 聚合函数 > having
  • 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

5）排序查询

• select 字段列表 from 表名 order by 字段1 排序方式1，字段2 排序方式2，...
  会先根据字段1的排序方式进行排序，然后再根据字段2的排序方式进行进一步排序，以此类推。

• 排序方式：

  • ASC：升序（默认）
  • DESC：降序

• 注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

6）分页查询

• select 字段列表 from 表名 limit 起始索引，查询记录数;

• 注意：

  • 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示数：比如查询第二页的数据，每页展示10条数据：select * from 表名 limit 10, 10;
  • 分页查询是数据库的方言，不同数据库有不同的实现，MySQL是limit
  • 如果查询的是第一页数据，起始索引可以忽略，直接简写为limit 10

7）案例练习

• 1.select * from emp where age in(20,21,22, 23);
• 2.select * from emp where age = '男' and age between 20 and 40 and name like '_ _ _';
• 3.select gender, count(*) from emp where age < 60 group by gender;
• 4.select name,age from emp where age <= 35 order by age asc, entrydate desc;
• 5.select * from emp where gender = '男' and age between 20 and 40 order by age asc, entrydate desc limit 5;

注意：limit分页查询是放在最后的。

8）DQL执行顺序

• 执行顺序：
  
• 验证：
  select e.name ename, e.age eage from emp e where e.age > 15 order by eage asc;
• 1.from …
• 2.where …
• 3.select …
• 4.order by…
• 5.limit …

9）总结

5.DCL

Data Control Language（数据控制语言），用来管理数据库用户，控制数据库的访问权限。

1）用户管理

• 1.查询用户：
  use mysql;
select * from user;

• 2.创建用户：
  create user '用户名'@'主机名' identified by '密码';

• 3.修改用户密码
  alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';

• 4.删除用户
  drop user '用户名'@'主机名';

2）权限控制

• 1.查询权限
  show grants for '用户名'@'主机名';

• 2.修改权限
  grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

• 3.撤销权限
  revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

注意：

• 多个权限之间使用逗号分隔
• 授权时，数据库名和表名可以使用*进行通配，代表所有。

二、函数

指一段可以直接被另外一段程序调用的程序或者代码。

1.字符串函数

语法：
select 函数(参数);

• 练习：
  
  update emp set workno = lpad(workno, 5, '0');

2.数值函数

• 语法：
  select 函数(数值);

• 练习：
  

select lpad(round(rand() * 1000000, 0), 6, '0');

3.日期函数

select name, datadiff(curdate(), entrydata) as 'entrydays' from emp order by entrydays desc;

4.流程控制函数

• 练习：
  

selectid,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;

5.总结

三、约束

• 约束是作用于表中字段上的规则，用于限制存储在表中的数据
• 目的：保证数据库中数据的正确、有效性和完整性
• 分类：
  
  注意：
  约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

1.演示

create table user_table(id int primary key  auto_increment comment '主键',name varchar(10) not null unique  comment '姓名',age int comment 'age' check ( age > 0 and age <= 120),status char(1) default '1' comment '状态',gender char(1) comment '性别'
) comment '用户表';

*这里早期的版本需要将comment放到check前面

2.外键约束

• 外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

• 语法：
  添加外键：

3.删除和更新

当父表的数据发生变化时，子表的数据也会随之发生变化：

当父表数据删除时，子表的数据对应字段变为null

4.总结

四、多表查询

1.多表关系

• 在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系。

1）一对多

比如：

• 部门与员工的关系
• 一个部门对应多个员工，一个员工对应一个部门
• 实现：在多的一方建立外键，执行一的主键

也就是多的一方的外键可以多个数据同时指向一的某一个主键

2）多对多

比如：

• 学生与课程之间的关系
• 一个学生可以选多门课程，一门课程可以被多个学生选择
• 实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

3）一对一

• 用户与用户详细信息的关系
• 关系：一对一，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
• 实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）
• 

2.多表查询

• 从多张表中进行数据的联合查询。
• 笛卡尔积：两个集合：A集合和B集合的所有组合情况。（在多表查询时，需要消除无效的笛卡尔积）
• 在进行多表查询时，直接查询两个表的内容或者未表明限制条件就会造成笛卡尔积，使得查询的信息出现冗余。

直接查询两张表的信息：
select * from emp, dept;
就会导致两张表的信息会发生组合，出现笛卡尔积
于是我们要使用限制条件：where emp.dept_id = dept.id来使得两张表的信息相互对应：
select * from emp, dept where emp.dept_id = dept.id;

3.多表查询分类

• 连接查询：
  • 内连接：相当于查询A、B交集部分数据
  • 外连接：
    • 左外连接：查询左表所有数据，以及两张表交集部分数据
    • 右外连接：查询右表所有数据，以及两张表交集部分数据
  • 自连接：当前表与自身的连接查询，自连接必须使用表别名
• 子查询

1）内连接

案例：
1.查询每一个员工的姓名，及关联的部门名称（隐式内连接实现）

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

起别名简化SQL：

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

2.查询每一个员工的姓名，及关联的部门名称（显式内连接实现）

select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;

inner可以省略。

写SQL的一般写法：先select * from 表名, 然后加上筛选条件，最后将*替换为需要查询的字段名

2）外连接

案例：
1.查询emp表的所有数据和对应的部门信息（左外连接）

select e.*, d.name from emp e left outer join dept e on e.dept_id = d.id;

2.查询dept表的所有数据和对应的员工信息（右外连接）通常可以改为左外（将两张表位置替换）

select d.*, e.* from emp e right outer join dept e on e.dept_id = d.id;

3）自连接

案例：
表结构：

• 1.查询员工及其所属领导的名字
  • 将emp表看作为两张表： emp a 员工表 emp b领导表
  • 根据a 员工表中的managerid去找到b 领导表中的对应信息
  • 拿到a 的name作为员工，对应b 的id的b.name作为领导

select a.name name, b.name manager from emp a join emp b on a.managerid = b.id;

• 2.查询所有员工 emp a 及其领导的名字 emp b，如果员工没有领导也要查询出来（外连接）

select a.name name, b.name manager from emp a left join emp b on a.managerid = b.id;

4）联合查询

• 就是将多次查询的结果合并起来，形成一个新的查询结果
  

案例：

• 将薪资低于 5000的员工，和年龄大于 50 岁的员工查询出来

union all：将所有结果展示出来

select * from emp where salary < 5000
union all
select * from emp where age > 50;

union：对查询结果去重

select * from emp where salary < 5000
union
select * from emp where age > 50;

*注意：

• 对联合查询的多张表的列数必须保持一致，字段类型也需要保持一致
• union all会将全部的数据直接合并到一起，union会对合并之后的数据进行去重

5）子查询

• SQL语句中嵌套select语句，称为嵌套查询，又称为子查询
  

1.标量子查询

返回的结果是单个值（数字、字符串、日期等），这种最简单的形式，这种查询称为标量子查询。

案例：

• 1.查询’销售部‘的所有员工信息
  • a.先查询销售部部门的ID： select id from dept where name = '销售部';
  • b.根据销售部部门ID查询员工信息：select * from emp where dept_id = 4;

由于b中的dept_id是由a查询出来的，所以我们可以将4 替换成 select id from dept where name = '销售部';

于是整个语句变为：

select * from emp where dept_id = (select id from dept where name = '销售部');

这里（）里面的查询就称为标量子查询。

---

• 2.查询在’方东白之后入职的员工信息
  • a.先查询方东白的入职日期 : select entrydate from emp where name = '方东白';
  • b.根据方东白入职日期查询在这个日期之后的员工信息: select * from emp where entrydate > '2009-02-12';

嵌套标量子查询：

select * from emp where entrydate > (select entrydate from emp where name = '方东白');

2.列子查询

• 子查询返回的结果是一列（可以是多行），称为列子查询

案例：

• 1.查询”销售部“和”市场部“的所有员工信息
  * a.查询”销售部“和”市场部“的ID：select id from dept where name = '销售部' or name = '市场部';
  * b. 根据部门ID查询员工信息：select * from emp where dept_id in (2, 4);

列子查询：a返回的是一个一列两行的数据，b可以直接根据a返回的数据进行查询：

select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');

---

• 2.查询比研发部其中任意一人工资高的员工信息
  • a.查询研发部所有人的工资：select salary from emp where dept_id = (select id from dept where name = '研发部');
  • b.查询比研发部其中任意一人工资高的员工信息： select * from emp where salary > any (研发部的所有工资数据);

嵌套列子查询：
select * from emp where salary > any (select salary from emp where dept_id = (select id from dept where name = '研发部'));

3.行子查询

• 子查询返回的结果是一行（可以是多列），这种子查询称为行子查询
• 常用操作符：=、<>、IN 、NOT IN

案例：

• 查询与”张无忌“的薪资及直属领导相同的员工信息
  • a.查询”张无忌“的薪资及其直属领导：select salary, managerid from emp where name = '张无忌';
  • b.查询与”张无忌“的薪资及直属领导相同的员工信息：select * from emp where (salary, managerid) = (12500, 1);
    嵌套行子查询：

select * from emp where (salary, managerid) = (select salary, managerid from emp where name = '张无忌');

4.表子查询

• 子查询返回的结果是多行多列，这种子查询称为表子查询
• 常用操作符：IN

案例：

• 1. 查询与”鹿杖客“，”宋远桥“的职位和薪资相同的员工信息

  • a.查询”鹿杖客“，”宋远桥“的职位和薪资：select job, salary from emp where name = '鹿杖客" or name = '宋远桥';
    

  • b.查询与”鹿杖客“，”宋远桥“的职位和薪资相同的员工信息： select * from emp where (job, salary) in ();
    

嵌套表子查询：

select * from emp where (job, salary) in (select job, salary from emp where name = '鹿杖客" or name = '宋远桥');

---

• 2.查询入职日期是“2006-01-01”之后的员工信息，及其部门信息
  • a.入职日期是“2006-01-01”之后的员工信息：select * from emp where entrydate > '2006-01-01';
  • b.查询这部分员工对应的部门信息：

此时查出来的是一张子表，我们需要进一步联合这张表去查询另外一张表中的部门信息。

于是将子查询的结果作为一张表去和另外一张表进行联合查询：

select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id;

4.练习

• 1.隐式内连接：select e.name, e.age, e.job, d.name from emp e, dept d where e.dept_id = d.id;
• 2.带有筛选条件的隐式内连接：select e.name, e.age, e.job, d.name from emp e inner join dept d on e.dept_id = d.id where e.age < 30;
• 3.隐式内连接去重：select distinct d.id, d.name from emp e, dept d where e.dept_id = d.id;
• 4.带有筛选条件的左外连接select * from emp e left join dept d on e.dept_id = d.id where e.age > 40;
• 5.范围筛选的多表联查：select e.*, s.grade from emp e, salgrade s where e.salary >= s.losal and e.salary <= s.hisal;
• 6.三表联查，至少需要两个连接条件，连接条件之间直接使用and连接
  
  select e.*, s.grade from emp e, dept d, salgrade s where ( e.dept_id = d.id ) and ( e.salary between s.losla and s.hisal ) and d.name = '研发部';

• 7.使用函数以及多表联查：select avg(e.salary) from emp e, dept d where e.dept_id = d.id and d.name = '研发部';
• 8.嵌套标量子查询：select * from emp e where salary > (select salary from emp where name = '灭绝');
• 9.嵌套标量子查询：select * from emp where salary > (select avg(salary) from emp);
• 10.嵌套列子查询：select * from emp e2 where e2.salary < (select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id);
• 11.select d.id, d.name, ( select count(*) from emp e where e.dept_id = d.id) '人数' from dept d;
• 12.三表联查，要慎重选择联查条件，有效消除笛卡尔积：select s.name, s.no, c.name from student s. student_course sc, course c where s.id = sc.studentid and sc.courseid = c.id;

5.小结

五、事务

• 事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统i提交或撤销操作请求，即这些操作要么同时成功要么同时失败。

最常见的案例举例：

银行转账问题：

• 1.张三向李四转账1000元
• 2.在查询到张三余额大于1000时，会将张三余额减1000
• 3.将李四的账号余额+1000

但是，如果在第2步执行完之后，系统抛出异常，导致整个流程中断了，就会发生：

• 张三的账户少了1000，但是李四的账户并没有增加
  
  那么我们就需要把这个流程放到一个事务中：
• 当李四账户余额+1000之后，会提交整个事务，完成转账操作
• 当中间某个流程停止了，就会回滚事务，将张三账户原本修改的余额还原回事务开启之前的状态

1.事务的开启方式

1）设置为手动提交

业务模拟：
MySQL中默认的事务提交为自动提交。
set @@autocommit = 0; 将事务提交设置为手动提交
在执行完所有流程之后，通过commit手动提交该事务

create table account(id int primary key auto_increment comment '主键id',name varchar(10) comment '姓名',money int comment '余额'
) comment '账户表';insert into account (id, name, money) values (null, '张三', 2000), (null, '李四', 2000);update account set money = 2000 where name = '张三' or name = '李四';select @@autocommit;set @@autocommit = 0; -- 设置为手动提交-- 转账操作-- 1.查询张三账户余额select * from account where name = '张三';-- 2.将张三账户减去1000update account set money = money - 1000 where name = '张三';-- 3.将李四账户余额+1000update account set money = money + 1000 where name = '李四';-- 提交事务
commit;
-- 回滚事务
rollbac;

• 当所有流程都执行正常，就提交事务：commit;
• 如果有抛出异常，就回滚事务：rollback;

2）手动开启事务

还原为自动提交，通过手动开启事务：
start transaction 或者 begin;

-- 开启事务
start transaction ;-- 转账操作-- 1.查询张三账户余额select * from account where name = '张三';-- 2.将张三账户减去1000update account set money = money - 1000 where name = '张三';-- 3.将李四账户余额+1000update account set money = money + 1000 where name = '李四';commit;rollback ;

2.事务的特征（ACID）

1）原子性

atomicity:事务是不可分割的最小操作单元，要么全部成功，要么全部失败

2）一致性

consistency：事务完成时，必须使所有的数据都保持一致状态

3）隔离性

isolation：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行

4）持久性

durability：事务一旦提交或者回滚，它对数据库中的数据的修改是永久的

3.并发事务问题

1.脏读

• 一个事务读取到另外一个事务还没有提交的数据

事务A修改了id=1的数据但是还未提交时，事务B读取到了事务A还未提交的数据，

2.不可重复读

• 一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读

当事务A先读取到id=1的数据时，事务B修改了id=1的数据，事务A再去读取id=1的数据时，导致两次读取的数据不一致

3.幻读

• 一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这条数据已经存在，好像出现了一个幻影

4.事务隔离级别

• 查看事务隔离级别：select @@transaction_isolation;
• 设置事务的隔离级别：set [session | global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable};

1）解决脏读

• read uncommitted:

在read uncommitted的并发事务下，事务A可以读取到事务B中还未提交的数据，此时事务B回滚，数据保持不变，但是事务A读取到的是修改的数据，这就会导致产生脏读

• read committed
read committed在并发事务下，事务A不会访问到事务B中还未提交的数据，访问的还是原本的数据，只有当事务B提交之后，才会正确访问到修改到内存的数据。
  

2）解决不可重复读

read committed中，在一个事务中，对同一条数据的查询查到的数据是不一致的，这就产生了不可重复读

将事务隔离级别切换为repeatable read;：之后会发现，当在一个事务开启对同一条前后不一致的数据进行查询时，得到的数据是一致的， 只有当事务提交之后，再查询该数据才是修改之后的值，这就解决了不可重复读的问题

3）解决幻读

repeatable read中，并发事务时，首先是事务A先查询id=3的数据，发现并没有，但是这时事务B突然插入了id=3的数据并提交了，那么当事务A先是查询到没有id=3的数据准备插入一条id=3的数据时，显示当前的主键已经存在了，但是在事务A中重新验证时，还是没有查到id=3的记录，这条记录就像幽影一样，这就是幻读

在切换为serializable后，发生并发事务时，事务A在查询没有id=5的数据时，如果事务B去插入一条数据（插入id=5的数据），会发现事务B的任务被阻塞了，只有当事务A提交了之后（比如这里插入一个id=5的数据，并提交），事务A提交之后事务B的任务才会继续执行，此时再尝试插入就会报错（主键id=5的数据已经存在），这就解决了幻读的问题。

---

注意：事务隔离级别越高，数据越安全，但是性能越低

5.总结

• 事务简介：事务是一组操作的集合，要么全部成功，要么全部失败

• 事务操作：

  • 开启事务： start transaction;
  • 提交/回滚事务：commit; / rollback;

• 事务四大特性：

  • 原子性
  • 一致性
  • 隔离性
  • 持久性

• 并发事务问题：

  • 脏读
  • 不可重复读
  • 幻读

• 事务隔离级别：

  • read uncommitted（读未提交）
  • read committed（读已提交）
  • repeatable read（可重复读）: MySQL默认
  • serializable（串行化）

六、总结

*注：上述内容以及图片均来自B站黑马程序员的视频学习笔记，仅作为学习交流，不作为商业用途，如有侵权，联系删除。