MYSQL从入门到精通---基础篇_mysql入门到精通

一、学习概况

说明：我们会从基础篇、进阶篇和运维篇三个部分去学习，这其实也是我的一次学习，可以说是我的学习笔记。

二、MYSQL概述

2.1 数据库相关概念

主流的关系型数据库管理系统

SQL是操作关系型数据库的编程语言，都是一套标准

2.2MySQL数据库

2.2.1 MySql下载

MySql下载

2.2.2 MySql启动停止

启动
net start mysql
停止
net stop mysql

2.2.3 关系型数据库(RDBMS)

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

特点：
1.使用表存储数据，格式统一，便于维护
2.使用$QL语言操作，标准统一，使用方便

三、 SQL

3.1 SQL通用语法

1.SQL语句可以单行或多行书写，以分号结尾。
2.SQL语句可以使用空格/缩进来增强语句的可读性。
3.MYSQL数据库的SQL语句不区分大小写，关键字建议使用大写。
4.注释：
单行注释：–注释内容或或#注释内容(MySQL特有)
多行注释：/注释内容/

3.2 SQL分类

3.3 DDL

3.3.1 命令行进入mysql

1.按windows+r键

mysql -u root -p

3.3.2 DDL操作数据库

比较简单，自己敲一遍就好了

查询当前处于哪一个数据库

3.3.3 DDL-表操作-创建&&查询

查询表

创建表

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(10) DEFAULT NULL COMMENT '姓名',
  `no` varchar(10) DEFAULT NULL COMMENT '学号',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COMMENT='学生表'
1
2
3
4
5
6

3.3.4 表结构数据类型

数值类型、字符串类型、日期时间类型

3.3.5 表操作–修改&&删除

添加字段

ALTER TABLE 表名 ADD 字段名 类型（长度）[COMMENT注释] [约束]；

ALTER TABLE student add nickname  VARCHAR(20) COMMENT'昵称';
1

修改字段

alter table student modify nickname VARCHAR(50) ;

ALTER TABLE student change nickname nike VARCHAR(900);

3.4 DML–增删改

添加数据

注意：

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

修改数据

注意：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

注意：

·DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
·DELETE语句不能删除某一个字段的值(可以使用URDATE)。

3.5 DQL–查

3.5.1 基本查询

3.5.2 条件查询

3.5.3 聚合函数

注意：null值不参与所有聚合函数运算。

3.5.4 分组查询

2.where与having区别

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

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

3.5.5 排序查询

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

3.5.5 分页查询

注意
起始索引从0开始，起始索引=(查询页码-1)*每页显示记录数。
分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是limit
如果查询的是第一页数据，起始索引可以省略，直接简写为limit10。

依次执行

3.6 DCL

MySQL中定义了很多种权限，但是常用的就以下几种：

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

四、函数

4.1 字符串函数

例如：由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0。比如：1号员工的工号应该为00001

update emp set workno= lpad(workno , 5 , ‘0’);

4.2 数值函数

– 向上取整
SELECT ceil(2.1);

– 向下取整
SELECT floor(2.6);

– 返回x/y的模
SELECT mod(6,4);

– 返回0-1内的随机数
select rand();

– 求参数x的四舍五入的值，保留y位小数
SELECT round(5.267,2);

通过数据库的函数，生成一个六位数的随机验证码。

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

4.3 日期函数

–案例：查询所有员工的入职天数，并根据入职天数倒序排序。

select name datediff(curdate(),entrydate) as ‘entrydays’ from emp
order by entrydays desc;

4.4 流程函数

– if
SELECT if(TRUE,‘ok’,‘error’);

– IFNULL(expr1,expr2)
SELECT IFNULL(‘ok’,‘DEFAULT’);

SELECT IFNULL(‘’,‘DEFAULT’)

SELECT IFNULL(null,‘DEFAULT’)

– case when then else end
– 需求 查询emp表员工姓名和工作地址（北京、上海==一线城市，其他是二线城市）

SELECT
name,
(CASE workadress
WHEN ‘北京’ THEN
‘一线城市’
WHEN ‘上海’ THEN
‘一线城市’
ELSE
‘二线城市’ END) as ‘工作地址’
from emp;

五、约束

1.概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。
2.目的：保证数据库中数据的正确、有效性和完整性。
3.分类：

注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

六、多表查询

6.1 表的联系

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

一对多(多对一)
多对多
一对一

6.2 多表查询

概述：指从多张表中查询数据
笛卡尔积：笛卡尔乘积是指在数学中，两个集合A集合和B集合的所有组合情况。（在多表查询时，需要消除无效的笛卡尔积）

多表查询分类

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

6.3 内连接

隐式内连接

select 字段列表 from 表1,表2 where 条件;

显示内连接

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

查询每一个员工的姓名及关联的部门的名称

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

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

6.4 外连接

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

SELECT e.* , d.name from emp e left outer JOIN dept d on  e.dept_id = d.id
1

– 2.查询dept表的所有的数据，和对应的员工信息（右外连接）

SELECT d.*, e.* from emp e right  JOIN dept d on  e.dept_id = d.id

SELECT * from emp e right  JOIN dept d on  e.dept_id = d.id
1
2
3

6.5 自连接

自然连接语法

select 字段列表 from 表a 别名a join 表a 别名b on 条件;

– 自连接
– 1.查询员工及其领导所属的名字

SELECT a.name , b.name from emp a ,emp b WHERE a.managerid = b.id;
1

– 2.查询所有员工emp及其领导的名字emp，如果员工没有领导也需要查询出来

SELECT a.name ,b.name from emp a left JOIN emp b on a.managerid = b.id 
1

6.6 联合查询

– 1.将薪资低于5000的员工，和年龄大于50岁的员工全部查询出来。

SELECT * from emp WHERE salary < 5000
UNION
SELECT * from emp where age > 50
1
2
3

SELECT * from emp WHERE salary < 5000
UNION ALL
SELECT * from emp where age > 50
1
2
3

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

6.7 子查询

概念：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。
SELECT FROM t1 WHERE column1 =(SELECT column1 FROM t2);
子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个。
根据子查询结果不同，分为：

标量子查询（子查询结果为单个值）
列子查询（子查询结果为一列)
行子查询（子查询结果为一行）
表子查询（子查询结架为多行多列)

根据子查询位置，分为：WHERE之后、FROM之后、SELECT之后。

标量子查询
子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式，这种子查询成为标量子查询。
常用的操作符：= <> > >= <=

– 标量子查询
– 1.查询“销售部”的所有员工信息
a.查询销售部的部门ID

SELECT id from dept where name = ‘销售部’;

b.查询ID=4的所有员工信息

SELECT * from emp where dept_id = 4;

SELECT * from emp where dept_id = (SELECT id from dept where name =
‘销售部’);

列子查询
子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。
常用的操作符：IN、NOT IN、ANY、SOME、ALL

– 列子查询
– 1。查询“销售部” 和 “市场部” 的所有员工信息
– a.查询销售部和市场部的ID

SELECT id from dept where name=‘销售部’ or name = ‘市场部’;

– b.查询ID=2和4 的所有员工的信息

SELECT * from emp WHERE dept_id in (2,4);

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 FROM dept where name=‘财务部’));

行子查询
子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。
常用的操作符：=、<>、IN、NOTIN
– 行子查询
– 1.查询“张三丰”的薪资及直属领导相同的员工信息
– a.查询张无忌的薪资和直属领导

SELECT salary,managerid from emp where name =‘张三丰’;

b.查询薪资和直属领导分别是14000和1的相同员工信息

SELECT * from emp where (salary,managerid) = (14000,1)

SELECT * from emp where (salary,managerid) = (SELECT salary,managerid from emp where name =‘张三丰’)

表子查询
子查询返回的结果是多行多列，这种子查询称为表子查询。
常用的操作符：IN
– 表子查询
– a.查询鹿杖客和宋远桥的职位和薪资相同的员工信息
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

七、事务

7.1 事务简介

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

默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。

7.2 事务的操作

7.3 事务的四大特性

·原子性(Atomicity):事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
·一致性(Consistency):事务完成时，必须使所有的数据都保持一致状态。
·隔离性(Isolation):数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
·持久性(Durability):事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

7.4 并发事务问题

7.5 事务隔离级别

7.6 Read uncommitted（读未提交）

提供了事务建最小限度的隔离。顾名思义，就是一个事务可以读取另一个未提交事务的数据。

示例：小明去商店买衣服，付款的时候，小明正常付款，钱已经打到商店老板账户，但是小明发起的事务还没有提交。就在这时，商店老板查看自己账户，发现钱已到账，于是小明正常离开。小明在走出商店后，马上回滚差点提交的事务，撤销了本次交易曹邹。

结果：小明未付钱买到了衣服，商店老板实际未收到小明的付款。

分析：商店老板查看自己的资金账户，这个时候看到的是小明还没有提交事务的付款。这就是脏读。

注意：处于该隔离级别的事务A与B，如果事务A使用事务B不提交的变化作为计算的基础，然后哪些未提交的变化被事务A撤销，这就导致了大量的数据错误变化。

7.7 Read committed （读已提交）

处于Read committed （读已提交）级别的事务可以看到其他事务对数据的修改。也就是说，在事务处理期间，如果其他事务修改了相应的表，那么同一个事务的同一sql在其他事务执行前后返回的是不同的结果。一个事务要等另一个事务提交后才能读取数据。

示例：小明卡里有1000元，准备与几个朋友聚餐消费，消费1000元，当他买单时（事务开启），收费系统检测到他卡里有1000元。就在检测完毕的时候，小明女朋友发现小明有私房钱，全部转走并提交。当收费系统准备扣款时，再检查小明卡里的金额，发现已经没钱了，付款不成功。小明此时就会很纳闷，明明有钱的呀，钱呢？

分析：该示例中同一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。该隔离级别可以解决脏读问题。

7.8 Repeatable read （重复读）

在开始读取数据（事务开启）时，不再允许修改操作

示例：还是小明有1000元，准备跟朋友聚餐消费这个场景，当他买单（事务开启）时，收费系统检测到他卡里有1000元，这个时候，他的女朋友不能转出金额。接下来，收费系统就可以扣款成功了，小明醉醺醺的回家，准备跪脱衣板。

分析：重复读可以解决不可重复读的问题，这句话有些别扭，大家可以仔细品一下。

写到这里，大家可能会产生疑问，什么情况下产生幻读呢？

示例来了：

小明在公司上班，女朋友告诉他，拿着他的卡去逛街消费。花了一千元，然后小明去查看他银行卡的消费记录（事务开启），看到确实是花了一千元。就在这个时候，小明女朋友又花三千元买了一些化妆品和衣服，即新增了一些消费记录。当小明打印自己银行卡消费记录单的时候（女朋友事务提交），发现花了四千元，似乎出现了幻觉，小明很心疼。这就是幻读

扩展：当我们开启一个事务以后，有如下的程序操作

第一步：更新A表id=1的记录

第二步：查询A表id=1的记录

第三步：使用第二步的查询结果作为依据继续业务逻辑

第四步：提交事务

问题来了：同一个事务中，事务未提交前，第二步的查询结果是第一步执行前的结果还是第一步执行后的结果呢？

答案：事务隔离级别是针对不通事务的，同一事务中的未提交的更新，在后续是可以查询到的。

7.9 Serializable （序列化）

数据库事务的最高隔离级别。在此级别下，事务串行执行。可以避免脏读、不可重复读、幻读等读现象。但是效率低下，耗费数据库性能，不推荐使用。