MySQL 学习基础篇笔记

最新推荐文章于 2022-11-26 18:09:39 发布

学习使我快乐~_~

最新推荐文章于 2022-11-26 18:09:39 发布

阅读量245

点赞数

本文链接：https://blog.youkuaiyun.com/weixin_43896363/article/details/105892972

版权

本文深入讲解SQL高级特性，包括去重查询、连接查询、分页、表复制、数据删除、事务处理、索引优化及视图创建，涵盖数据库设计三范式，助你提升数据库操作技能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

文章目录

select 语句去除重复记录
连接查询
limit（分页）
- 创建表
表的复制
删除数据
唯一性约束
表的主、外键相关
存储引擎
事务概述
事务的原理
事务的特性
事务的隔离性
索引
视图
数据库设计三范式

select 语句去除重复记录

select distinct age from table

distinct 只能出现在所有字段的最前面，是去除所有字段联合起来的去重

连接查询

什么是连接查询？

在实际开发中，大部分情况都不是从单表查询数据，一般都是多张表联合查询出最终结果

连接查询的分类

1. 内连接

内连接又分为：
1.等值连接
2.非等值连接
3.自连接

等值连接

案例：查询每个员工的部门名称，要求显示员工名和部门名

SQL92:
```
	select a.ename,d.dname
	from emp e,dept d
	where e.deptno = d.ddeptno;
```
SQL99:(常用的)
```
select e.ename,d.dname
from emp e
join dept d
on e.deptno=d.deptno
```
SQL99 的优点：语法结构更清晰一些，表的连接条件和后来的 where 条件分离了

非等值连接
最大的特点：连接条件中的关系是非等量关系

案例：找出每个员工的工资等级，要求显示员工名、工资、工资等级
```
select e.ename,e.sal,s.grade
from emp e
(inner) join salgrade s
on e.sal between s.losal and s.hisal
```

自连接
最大特点：一张表看做两张表，自己连接自己

案例：找出每个员工的上级领导，要求显示员工名和对应的领导名
```
select a.ename as '员工名', b.ename as '领导名'
from emp a
(inner) join emp b
on a.mgr=b.empno
```

内连接语法总结：

	...
	A表
	(inner) join B表 on 连接条件
	(inner) join c表 on 连接条件
	...
	where ...

可以理解为： A表先和 B 表 join 的结果再和 C 表进行 join 。下面的语法也是这样理解

2. 外连接

外连接又分为：
1.左外连接（左连接）
2.右外连接（右连接）

什么是外连接，和连接有什么区别？

内连接：
假设A和B进行连接，使用内连接的话，凡是A表和B表能够匹配的记录查询出来，这就是内连接
外连接：
假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配

左外连接
表示左边的这张表是主表
语法：

	select A表
	left (outer) join B 表 on 条件
	left (outer) join C 表 on 条件
	...
	where

右外连接
表示右边这张表是主表
语法：

	select A表
	right (outer) join B 表 on 条件
	right (outer) join C 表 on 条件
	...
	where

3. 全连接

关键字：union /union all

语句： (select colum1,colum2…columN from tableA ) union (select colum1,colum2…columN from tableB )
或 (select colum1,colum2…columN from tableA ) union all (select colum1,colum2…columN from tableB )；

union语句注意事项：

1.通过union连接的SQL它们分别单独取出的列数必须相同；
2.不要求合并的表列名称相同时，以第一个sql 表列名为准；
3.使用union 时，完全相等的行，将会被合并，由于合并比较耗时，一般不直接使用 union 进行合并，而是通常采用union all 进行合并；
4.被union 连接的sql 子句，单个子句中不用写order by ，因为不会有排序的效果。但可以对最终的结果集进行排序；

在表的连接查询方面有一种现象称为：笛卡尔积现象（笛卡尔乘积现象）

当两张表进行连接查询的时候，没有任何条件进行限制，最终查询结果条数是两张表记录条数的笛卡尔乘积

limit（分页）

limit 是 mysql 特有的，其他数据库没有，不通用（Oracle 中有一个相同的机制，叫做rownum）
limit 的作用是取结果集中的部分数据
limit 是 sql 语句最后执行的一个环节
语法机制：
limit startIndex,length

startIndex 表示起始位置，从0开始，0表示第一条数据
length 表示取几个
当 startIndex 为0的时候，可以省略不写

案例

取出工资前5名的员工（思路：降序取前5个）

select ename,sal from emp order by sal desc limit 0,5
或者：
select ename,sal from emp order by sal desc limit 5

创建表

创建表的语法格式

create table 表名{
	字段名1 数据类型，
	字段名2 数据类型，
	字段名3 数据类型，
	...
}

char 和 varchar 的区别

char的特点

char表示定长字符串，长度是固定的；
如果插入数据的长度小于char的固定长度时，则用空格填充；
因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；
对于char来说，最多能存放的字符个数为255，和编码无关

varchar的特点

varchar表示可变长字符串，长度是可变的；
插入的数据是多长，就按照多长来存储；
varchar在存取方面与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法；
对于varchar来说，最多能存放的字符个数为65532

总之，结合性能角度（char更快）和节省磁盘空间角度（varchar更小），具体情况还需具体来设计数据库才是妥当的做法。

varchar(50)中50的涵义

最多存放50个字符，varchar(50)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存，因为order by col采用fixed_length计算col长度(memory引擎也一样)。在早期 MySQL 版本中， 50 代表字节数，现在代表字符数。

int(20)中20的涵义

是指显示字符的长度。20表示最大显示宽度为20，但仍占4字节存储，存储范围不变；

不影响内部存储，只是影响带 zerofill 定义的 int 时，前面补多少个 0，易于报表展示

mysql中int(10)和char(10)以及varchar(10)的区别

int(10)的10表示显示的数据的长度，不是存储数据的大小；chart(10)和varchar(10)的10表示存储数据的大小，即表示存储多少个字符。

int(10) 10位的数据长度 9999999999，占32个字节，int型4位
har(10) 10位固定字符串，不足补空格最多10个字符
varchar(10) 10位可变字符串，不足补空格最多10个字符
char(10)表示存储定长的10个字符，不足10个就用空格补齐，占用更多的存储空间
varchar(10)表示存储10个变长的字符，存储多少个就是多少个，空格也按一个字符存储，这一点是和char(10)的空格不同的，char(10)的空格表示占位不算一个字符

FLOAT和DOUBLE的区别是什么

FLOAT类型数据可以存储至多8位十进制数，并在内存中占4字节。
DOUBLE类型数据可以存储至多18位十进制数，并在内存中占8字节

表的复制

语法

create table 表名 as select语句
将查询结果当做表创建出来

将查询结果插入到一张表

insert into 表名 select语句

删除数据

语法格式

delete from 表名 where 条件

注意：没有条件全部删除

删除所有数据

delete from 表名

怎么删除大表(删库跑路专用)

truncate table 表名

drop 和 delete 和 truncate 区别

TRUNCATE TABLE 在功能上与不带 Where 子句的 Delete 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 Delete 速度快，且使用的系统和事务日志资源少。
Delete 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 Delete。
对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 Where 子句的 Delete 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

唯一性约束

列级约束： 只能应用于一列上。包含在列定义中，直接跟在该列的其它定义之后，用空格分隔；不必指定列名
表级约束： 可以应用于一列上，也可以应用在一个表中的多个列上。与列定义相互独立，不包含在列定义中；与定义用‘，’分隔；必须指出要约束的列的名称

列级约束举例

create table t_user{
	id int,
	username varchar(255) unique // 列级约束
}

表级约束举例

create table t_user{
	id int,
	usercode varchar(255),
	username varchar(255),
	unique(usercode,username) // 表级约束
};

注意：not null 只有列级约束，没有表级约束

表的主、外键相关

mysql 提供主键自增

 create table t_user{
 	id int primary key auto_increment,
 	username varchar(255)
 };

尽量不使用业务主键

外键的语法


父表：
create table t_class{
	cno int,
	cname varchar(255),
	primary key(cno)
};

子表：
create table t_student{
	sno int,
	sname varchar(255),
	classno int,
	foreign key(classno) references t_class(cno)
}

外键约束：子表中的classno 字段的值一定要是父表中cno存在的值

注意：

外键值可以为 null
外键字段引用的字段不一定是主键，只需要引用的那个字段具有唯一性（具有 unique 约束）

存储引擎

描述的是表的存储方式
不同的存储引擎有不同的表的存储方式

在创建表的时候默认指定的存储引擎是InnoDB
默认的字符集是UTF - 8

注意：在MySQL当中可以使用 ` （飘号）括起来的，但是最后别用，不通用

完整的建表语句

	create table `t_x`(
	`id` int(11) default null
	) ENGINE = InnoDB DEFAULT=utf8

什么是存储引擎

存储引擎这个名字只在 mysql 中存在（Oracle 中有对应的机制，不过Oracle中没有特殊的名字，就是“表的存储方式”）

MySQL支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式。每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎

常见的存储引擎

MyISAM
是MySQL最常用的存储引擎，但是不支持事务

使用三个文件表示每个表
1. 格式文件 – 存储表结构的定义（mytable.frm）
2. 数据文件 – 存储行的内容（mytable.MYD）
3. 索引文件 – 存储表中索引（mytable.MYI）

优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索速率

InnoDB

优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障，是最安全的存储引擎
表的结构存储在 xxx.frm 文件中
数据存储在tablespace 这样的表空间中（逻辑概念），无法被压缩，无法转换成只读。
这种InnoDB 存储引擎在MySQL数据库崩溃之后提供自动恢复机制
InnoDB 支持级联删除和级联更新

MEMORY

缺点：不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中的
优点：查询速度最快
以前叫作 HEAP 引擎

事务概述

什么是事务

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。

事务最经典也经常被拿出来说例子就是转账了。

假如小明要给小红转账1000元，这个转账会涉及到两个关键操作就是：将小明的余额减少1000元，将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩溃，导致小明余额减少而小红的余额没有增加，这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败。

和事务相关的语句只有DML语句

insert 、delete 、update
因为这三个语句都是和数据库表中的“数据”相关的，事务的存在就是为了保证数据的完整性、安全性

事务的原理

回滚事务不会同步到硬盘
提交事务和回滚事务都会清除历史记录（缓存）

假设一个事，需要先执行一条 insert ，再执行一条 update ，最后执行一条 delete 才算完成这件事

原理：

开启一个事务（开始）
执行 insert 语句 (这个执行成功后，把这个执行记录到数据库操作历史中（缓存），并不会向文件中保存一条数据，不会真正修改硬盘上的数据)
执行 update 语句（也是保存在历史记录）
执行 delete 语句（也是保存在历史记录中）
提交事务或者回滚事务（结束）

最后一起提交或者回滚，就实现了事务的原子性，要么同时成功，要么同时失败

事务的特性

事务包括四大特性：

原子性（Atomicity）：一个事物中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在啊执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没执行过一样
一致性（Consistency）：在事务开始之前和事务结束之后，数据库的完整性没有被破坏
隔离性（Isolation）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据不一致。事务隔离分为不同的级别，包括读未提交，读已提交，可重复读和可串行化
持久性（Durability）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失

事务的隔离性

理论上隔离级别包括4个：

第一级别：读未提交（read uncommitted）
对方事务还没有提交，我们当前事务可以读取到对方未提交的数据
存在问题：脏读

第二级别：读已提交（READ-COMMITTED）
对方事务提交之后的数据我方可以读取到
这种隔离级别解决了脏读现象
存在问题：不可重复读

第三级别：可重复读（REPEATABLE-READ）
解决了不可重复读的问题
存在问题：幻读

第四级别：序列化读/串行读（SERIALIZABLE）
解决了所有问题
存在问题：效率低，需要事务排队

Oracle 数据库默认的级别是：读已提交
MySQL 数据库默认的界别是可重复读
MySQL中的数据是自动提交的

什么是脏读？幻读？不可重复读？

脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。
不可重复读(Non-repeatable read) ： 在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。
幻读(Phantom Read)： 在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

索引

什么是索引？有什么用？

索引就相当于一本书的目录，通过目录可以快速找到对应的资源
在数据库方面，查询一张表有两种检索方式：

全表扫描
根据索引检索（效率很高）

索引为什么可以提高检索效率

因为缩小的扫描的范围

索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护。是有维护成本的。比如，表中的数据经常被修改，这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护

添加索引是给某一个字段，或者说某些索引添加字段

怎么创建索引对象？怎么删除索引对象？

创建索引对象：

	create index 索引名称 on 表名（字段名）;

删除索引对象：

	drop index 索引名称 on 表名（字段名）;

什么时候考虑给字段添加索引？（满足什么条件）

数据量庞大（根据客户的需求，根据线上的环境）
该字段很少的DML操作（因为字段进行修改操作，索引也需要维护）
该字段经常出现在where 字句中（说明经常根据那个字段进行查询）

注意：
主键和具有unique 约束的字段自动会添加索引。根据主键查询效率高。尽量根据主键检索

索引底层采用的数据结构是B+树

索引的实现原理

通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的

select ename from emp where ename=“SSS”;
通过索引转换为：
select ename from emp where ename=“0x3”

索引的分类

单一索引：给单个字段添加索引
复合索引：给多个字段联合起来添加1个索引
主键索引：主键上会自动添加索引
唯一索引：有unique 约束的字段上会自动添加索引

索引什么时候失效

模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的

视图

什么是视图

站在不同的角度去看数据（同一张表的数据，通过不同的角度看待）

怎么创建视图？怎么删除视图

create view myview as select empno,ename from emp;
drop view myview;

注意：只有DQL 语句才能以视图的方式创建出来，对视图增删改会影响原表的数据，可以对视图进行 CRUD 操作

视图的作用

为了提高复杂SQL语句的复用性和表操作的安全性
视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，Java 程序员可以对视图对象进行CRUD

数据库设计三范式

第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分
第二范式：建立在第一范式的基础上，所有非主键字段完全依赖主键，不能产生部分依赖
第三范式：建立在第二范式的基础上，所有非主键字段直接依赖主键，不能产生传递依赖

参考博文：https://blog.youkuaiyun.com/ThinkWon/article/details/104778621/