MySQL学习笔记 | 来自缤纷多彩的灰

MySQL学习笔记 @ WHlcj | 2022-08-03T16:43:40+08:00 | 15 分钟阅读 | 更新于 2023-09-09T14:25:58+08:00

基于MySQL的SQL学习笔记。细琐的东西特别多，因此笔记也比较多。本笔记主要是为了需要时进行查询，因此会删去一些非必要的解释及说明。

一、前置知识

mysql默认端口(port)为3306

启动,关闭程序:

1
2

net stop 程序名
net start 程序名

登录方式:

img

法2可以隐藏密码

img

一些概念:

（1）sql(Structured Query Language):sql是一套标准，用来和数据库打交道，完成和数据库的通信
（2）DB: database数据库
（3）DBMS(DataBase Management System):数据库管理系统,比如MySQL,oracle,db2…
（4）命名规范:所有标识符全部小写,单词和单词之间用下划线进行衔接
（5） SQL语句以；结尾，不区分大小写

二、MySQL基础篇

mysql常用命令:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

（1）退出:  exit  
（2）查看有哪些数据库:  show databases; 	(mysql默认自带四个数据库)  
（3）使用数据库:  use xxx;  
（4）创建数据库:  create database xxxx;  
（5）删除数据库:  drop database xxx;  
（6）查看MySQL数据库版本号:  select version();  
（7）查看当前使用的数据库:  select database();  
（8）查看某个数据库下有哪些表:  show tables;  
（9）终止一条命令输入:  \c  
（10）导入数据或批量执行脚本:  source 绝对路径（路径中不要有中文,可以直接拖拽文件到终端）  
（11）查看表中数据:  select * from 表名;  
（12）不看表的数据只看表的结构:  desc 表名;  
（13）注释规则:  两个”-”一个空格。  Eg: "-- 这是注释"

一些概念

（1）数据库最基本的单元是表(table)任何一张表都有行(row)和列(column)
       行（row）:被称为数据/记录。
       列（column）:被称为字段。
（2）每个字段都有字段名、数据类型、约束等属性
（3）SQL语句分类:DQL（数据查询语言）、DML（数据操作语言）、DDL（数据定义语言）、TCL（事务控制语言）、DCL（数据控制语言）

三、DQL语句

  1. 总结: select …from … join … on …where … group by … having… order by… limit …
  2. 顺序:
1
2
3
4
5
6
7

1. from  
2. where  
3. group by  
4. having  
5. select  
6. order by  
7. limit
  1. 给查询的列起别名: select 字段名 as 别名;（as可省略。只是将显示的结果显示为别名,原表名不变。） 若加的别名有空格，使用字符串的形式: select 字符名 as ‘别 名’;（单双引号都可以，但是单引号是标准）
  2. 简单计算:select int字段 * number from 表名;（字段可以使用数学表达式）
  3. 条件:
1
2
3
4
5
6
7

1. =,<,>,<>或!=(不等于),>=  
2. between ... and ...                （同 ... >= ... <= ... ）  
3. is null/not null                   （不能使用!=null）  
4. and,or,in（ 数据1，数据2....）      （优先级and>or）  
5. not                                （not可以取非,主要用在is或in中）  
6. like                               （like称为模糊查询,支持%或_匹配;%表示任意多个字符,_表示任意一个字符）  
查询名字中带%或者_的用转义字符’\’:	where 字段名 like ‘%\_%’
  1. 排序:（按照数字大小和字母顺序,默认升序）
1
2
3
4

（1）指定降序:  select 字符段 from 表格 order by 字符段 desc;  
（2）指定升序:  select 字符段 from 表格 order by 字符段 asc;  
（3）按照多个字段排序:  select 字符段 from 表格 order by 字符段1 asc ,字符段2 asc;  
（4）字段位置排序:  select 字符段1,字符段2,字符段3  from 表格 order by  2;（表示根据字符段2排序）

数据处理函数:

（又被称为单行处理函数）

特点: 一个输入对应一个输出;

常见函数:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

1. 转换小写: lower
2. 转换大写: upper
3. 取字串: substr（substr（被截取的字符串,起始下标,截取的长度））
4. 字符串拼接: concat(s1,s2)
*注意,起始下标从1开始
5. 取长度: length(字符段)
6. 去空格: trim(字符段)
7. 将字符串转换成日期: str_to_date
8. 格式化日期: date_format
9. 设置千分位: format
10. 四舍五入: round(字符段,保留位数)（0为保留整数）
11. 生成随机数: rand()
*在所有数据库中，只要有null参与的数学运算，最终结果都是null,为了避免这种情况需要用到ifnull
12. 将null转换成一个具体值: ifnull(数据,被当作哪个值)
(case 字符段 when 数据面 then 语句 when..then..end..else..end)
13. 消除重复记录: distinct
Eg: select distinct 字段名 from 表格;

分组函数:

（又被称为多行处理函数）

特点:多行处理函数的特点:多行对应一个输出;

常见函数:

1
2
3
4
5

1. 计数:    count(字符段)   
2. 求和:    sum(字符段)   
3. 平均值:	avg(字符段)  
4. 最大值:  max(字符段)  
5. 最小值:  min(字符段)

注意:

1
2
3

1.分组函数在使用的时候必须先进行分组，然后才能使用  
2.如果没有对数据进行分组，整张表默认为一组。  
3.分组函数计算时自动忽略null

连接查询:

从一张表中单独查询,称为单表查询。跨表查询，多张表连接起来查询数据被称为连接查询.

  1. 根据表的连接方式分类:
1
2
3

1. 内连接: 等值连接，非等值连接,自连接  
2. 外连接: 左外连接（左连接），右外连接（右连接）  
3. 全连接

  1. 当两张表进行连接查询，没有任何条件限制的时候，最终查询结果条数，是两张表条数的乘积，这种现象被称为:笛卡尔积现象。

  2. 用法:

92语法：表的连接条件跟筛选条件都放在where后面结构不清晰

1

select 表格1.字符段1，表格2.字符段2  from 表格1，表格2... where 表格1.字符段 = 表格2.字符段

99语法：inner可省略,但是可读性更好

1

select 表格1.字符段1，表格2.字符段2  from 表格1  inner  join 表格2...on 表格1.字符段 = 表格2.字符段...where 条件
  1. 外连接:
1

表格1  left/right  outer join 表格2  on....(左边或者右边不完全匹配的也显示出来,outer可省略)
  1. 多张表连接:
1

select ... from  a  join  b  on  a和b的连接条件  join  C  on  a和c的连接条件 ...

子查询

select 语句中嵌套 select语句,被嵌套的select语句称为子查询。

语句:

1
2
3
4
5
6

select  
..select  
from   
..select  
where  
..select

Union（避免笛卡尔积）:

1
2
3

select ... from 表格... where...  
union  
select...from 表格...where...

limit:将查询结果集的一部分取出来,通常使用在分页查询中，分页作用是提高用户体验。

1
2

使用:limit length;
Limit start,length;（limit起始下标为0）

四、DML语句

插入数据insert:

1

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

一次插入多条记录:

1

insert into 表名(字符名1，字符名2....) values((值1，值2....),(值11，值22.....),......)
  • 注意: 前后数量以及数据类型一一对应。默认值为null。但凡执行成功，一定会多一条记录。只能修改，不能插入。也可以直接按顺序插入数据。

数字格式化:format(数字,’格式’);（格式: ‘$999,999’）

1

select  format(字符段,格式)  from ....

将字符串varchar类型转换成date类型: str_to_date(“字符串日期”,”日期格式”) (若日期是类似”1990-10-01”的’%y-%m-%d’的形式则会自动转换,默认情况下自动将数据库中的date类型转换为’%y-%m-%d’的格式)

mysql的日期格式(区分大小写):

1
2
3
4
5
6
7
8
9

%y:  两位数字年
%Y:  四位数字年
%m:  数字月
%M:  英文月
%d:  数字日
%D:  英文日
%h:  时
%i:  分
%s:  秒

将date类型转换成具有一定格式的varchar类型: date_format

date和datetime的区别:

date是短日期，只包括年月日信息。默认格式:”%Y-%m-%d”

datetime是长日期:包括年月日时分秒信息。默认格式:”%Y-%m-%d %h:%i:%s”

update

update 表名 set 字段名1=值1，字段名2=值2，字段名3=值3…where 条件;

(没有条件限制会导致数据全部更新)

delete

1

delete from 表名 where 条件;(没有条件限制会导致数据全部删除)
  1. 原理:表中数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放。
  2. 优点:支持回滚,可恢复数据
    ​ 缺点:效率低

truncate

1

truncate table 表名;
  1. 原理: 物理删除，效率较高,速度快,但是不支持回滚
  2. 不能删除单条,都不删除表,只是删除表内的数据

五、DDL语句

表的创建

1
2
3
4

create table 表名(
字段名1  数据类型 default 数据，
字段名2  数据类型 default 数据，
.....)

  1. 表名建议以t_或者tbl_开始，可读性强。表名和字段名都属于标识符

  2. 常见数据类型:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

1. varchar: 可变长度的字符串,根据实际数据长度动态分配空间
2. char: 定长字符串。优点：速度快. 缺点:空间浪费
3. int		(auto_increment)
4. bigint:  long int
5. float
6. double
7. date:短日期
8. datetime:长日期（补充:now()为获取当前时间,eg:datetime=now()）
9. clob(Character Large OBject):字符大对象,最多存储4g的字符串。超过255字符的都要采用clob来存储
10. blob(Binary Larhe OBject):专门来存储图片，声音，视频等媒体数据
  1. 快速创建表:
    ​ （1）原理:将一个查询结果当作一个表新建
    ​ （2）eg: create table 表名2 as select …. from 表1
    ​ （3）可用insert把结果插入: insert into 表名 select ….. from 表名
  2. 删除表: drop table if exists表名;
  3. 对表结构的修改alter(本文略)

约束(constraint)

在创建表的时候，我们可以给表中的字段加上一些约束，来保证这个表中的数据的完整性，有效性。

常见约束:

1
2
3
4
5

1. 非空约束: not null
2. 唯一性约束: unique 		(null不受影响)
3. 主键约束: primary key 	(PK)
4. 外键约束: foreign key 	(FK)
5. 检查约束: check		    (mysql不支持,oracle支持)

六、DLC语句

Data Control Language(数据控制语言)，用来管理数据库用户、控制数据库的访问权限

1．管理用户:

1
2
3
4
5

（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
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

（1）常用权限:
	①all/all privileges
	②select
	③insert
	④update
	⑤delete
	⑥alter
	⑦drop
	⑧create
	⑨结尾:with grant option
（2）查询权限: show grants for '用户名'@'主机名';
（3）授予权限: grant 权限列表 on 数据库.表名 to '用户名'@'主机名';
（4）Revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

七、存储引擎

存储引擎是MySQL中特有的术语。指的是一个表存储或者组织数据的方式，不同存储引擎，表的存储数据的方式不同。

1
2
3

create table 表名(
......
)ENGINE=(默认InnoDB) 	CHARSET=(默认utf8);
  1. 查看支持哪些引擎: show engines (也可加一个空格再加\G);
  2. 展示创建指令: show create table 表名

MyISAM:

  1. 它管理的表具有一下特征:
1
2
3
4
5

1) 使用三个文件表示每一个表
2) 格式文件 -- 存储表结构的定义（mytable.frm）
3) 数据文件 -- 存储表行的内容（mytable.MYD）
4) 索引文件 -- 存储表上索引（mytable.MYI）
5) 灵活的AUTO INCREMENT字段处理
  1. 特点:可被转换为压缩、只读表来节省空间

InnoDB:

  1. 最大特点：支持事务，支持数据库崩溃后自动恢复机制。主要特点是非常安全!但是效率不是很高，并且不能压缩或只读节省空间。
  2. 它管理的表具有以下主要特征:
1
2
3
4
5
6
7
8

1) 每一个InnoDB表在数据库目录中以.frm格式文件表示
2) InnoDB表空间tablespace被用于存储表的内容（表空间是一个逻辑名称，表空间存储数据加索引）
3) 提供了一组用来记录事务性活动的日志文件
4) 用COMMIT(提交)、SAVEPOINT及ROLLBACK(回滚)支持事务处理
5) 提供全ACID兼容
6) 在MySQL服务器崩溃后提供自动恢复
7) 多版本（MVCC）和行级锁定
8) 支持外键及引用的完整性，包括级联删除和更新

MEMORY:

以前被称为HEAP引擎

  1. 主要特征:
1
2
3
4

1) 在数据库目录内，每个表均以.frm格式的文件表示。
2) 表数据及索引被存储在内存中。（目的就是快，查询快！）
3) 表级锁机制。
4) 不能包含TEXT或BLOB字段
  1. 优点:查询效率最高 缺点:不安全，关机后数据消失

八、TCL语句

事务

一些概念

1
2
3
4

(1) 一个事务就是一个完整的业务逻辑,只有DML语句(insert,delete,update)才会有事务这一说。本质上，一个事务其实就是批量的DML语句同时成功，或者同时失败！  
(2) 在事务的执行过程中，每一条DML的操作都会记录到“事务性活动的日志文件”中。在事务的执行过程中，我们可以提交事务，也可以回滚事务。  
(3) 提交事务:  清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。提交事务标志着，事务的结束。并且是一种全部成功的结束。  
(4) 回滚事务： 将之前所有的DML操作全部撤销，并且清空事务性活动的文件。回滚事务标志着事务的结束，并且是一种全部失败的结束。

事务的特性:

1
2
3
4

A原子性：事务是最小的工作单元，不可再分。  
C一致性：所有事务要求，在同一个事务当中，所有操作必须同时成功，或者同时失败。以保证数据的一致性。  
I隔离性：事务之间有一定的隔离。  
D持久性：事务最终结束的一个保障。事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上。

使用：

1
2
3
4

1.事务提交:  commit;  
2.事务回滚:  rollback;  
3.MySQL默认情况下默认每执行一条DML语句则提交一次;  
执行:start transaction;关闭自动提交。

隔离性

隔离级别:

MySQL默认隔离级别是三档

  1. 读未提交：read uncommitted (最低隔离级别):《没有提交就读到了》

a. 事务A可以读取到事务B未提交的数据。这样会导致脏读(Dirty Read)。但是这种隔离级别一般都是理论上的，大多数的数据库隔离级别都是二档起步！

  1. 读已提交：read committed《提交之后才能读到》

a. 事务A只能读取到事务B提交之后的数据。解决了脏读现象。这种隔离级别是比较真实的数据，每一次读到的数据都是绝对的真实。
b. 存在的问题:不可重复读取数据。

  1. 可重复读：repeatable read《提交之后也读不到，永远读取的都是刚开启事务时的数据》

a. 事务A开启之后，不管是多久，每一次在事务A中读取到的数据都是一致的。即使事务B将数据已经修改，并且提交了，事务A读取到的数据还是没有发生改变，这就是可重复读取。
b. 存在的问题:可能会出现幻影读

  1. 序列化/串行化：serializable (最高隔离级别)

a. 这种隔离级别表示事务排队，不能并发！效率最低，解决了所有问题。
b. Synchronized,线程同步(事务同步)每一次读取的数据都是最真实的，并且效率最低的。

常用指令

1
2
3

1. 使用:	set global transaction isolation level 隔离级别;设置好后先退出再重进。  
2. 查看会话级的当前隔离级别:	select @@transaction_isolation;  
3. 查看全局级的当前隔离级别:	select @@global.transaction_isolation;

九、索引

索引实在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引。同时索引是各种数据库进行优化的重要手段，优化的时候优先考虑的因素就是索引。

  1. 一些概念:
1
2
3
4
5
6
7
8

1. MySQL的两种查询方式: 全表扫描,根据索引检索;
2. 索引的实现原理:	以字段为B-Tree,找到后转化为物理地址。
3. 在任何数据库中主键上都会自动添加索引对象。在MySQL中，一个字段上如果有unique约束的话，也会自动创建索引对象。
4. 在任何数据库中，任何一张表的任何一条记录在硬盘的存储上都有一个硬盘的物理存储编号。
5. 在MySQL当中，索引是一个单独的对象，不同的存储引擎以不同的形式存在:
  在MySAM存储引擎中，索引存储在一个.MYI文件中。
  在InnoDB存储引擎中索引存储在一个逻辑名称叫做tablespace的当中。
  在MEMORY存储引擎当中，索引被存储在内存当中。不管索引存储在哪里，索引在MySQL当中都是一个树的形式存在。
  1. 使用索引的条件:
1
2
3

1.数据量庞大
2.该字段经常出现在where后面，以条件的形式存在。
3.该字段很少进行DML操作
  1. 常用指令:
1
2
3
4

1.添加索引:	create index 表名_字段名1_字段名2..._index on 表名(字段名1,字段名2...);
2.删除索引:drop index 表名_字段名_index on 表名;
3.查看是否使用了索引: explain select * from 表名 where 字段名 = 值;
Type = ref则使用了索引。若type=all,或rows=总长度，则未使用索引。
  1. 索引失效：
1
2
3
4
5

① 模糊查询中以%开始。
② 使用or。如果使用or那么要求or两边的条件字段都要有索引，才会走索引。
③ 最左原则。使用复合索引的时候，没有使用左侧的列查找，索引失效。
④ where中索引列参加了运算。
⑤ where中索引列使用了函数。
  1. 索引的分类：
1
2
3
4
5

① 单一索引：一个字段上添加索引。
② 复合索引：两个或者多个字段上添加索引。
③ 主键索引：主键上添加索引。
④ 唯一性索引：具有unique约束的字段上添加索引。
*唯一性较弱的字段上添加索引用处不大。

十、视图、DBA命令

站在不同的角度去看待同一份数据。我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致原表被操作。

视图

  1. 创建视图对象:create view 表名_view as select * from 表名;
  2. 删除视图对象:drop view 表名_view;
  3. 视图的作用:
1
2
3
4

1.简化操作
2.增强数据安全性
3.避免数据冗余
4.提高数据的逻辑独立性

DBA命令(部分)

  1. 数据导出:
1

mysqldump 数据库名>绝对路径\数据库名.sql -u用户名 -p密码(在windows的dos命令窗口中输入，命令结尾没有;)
  1. 导出指定的表:
1

mysqldump 数据库名 表名>绝对路径\数据库名.sql -u用户名 -p密码
  1. 数据导入:
1
2

source 绝对路径\数据库名.sql  
   (先要登录到MySQL数据库服务器上，创建数据库，使用数据库，然后再导入)

十一、数据库设计三范式

数据库表的涉及依据。教你怎么进行数据库表的设计,可以避免表中数据的冗余，空间的浪费。

1
2
3

第一范式:要求任何一张表必须有主键，每一个字段原子性不可再分。
第二范式:建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。
第三范式:建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。

口诀

多对多，三张表，两个外键;
一对多，两张表，多的表加外键;
一对一，外键唯一;

avatar

WHlcj的个人博客：仍想在这颗心脏跳动的时间内守护你！

cccc cocoapods git github Hackathon hugo iOS JDBC linux MacOS MySQL python ubuntu wwdc Xcode 仿造品 大数据 字符画 实习 生活随想 词云 随机验证码 面试