mysql之路

帅过地球一半的男人

已于 2023-06-11 16:57:04 修改

阅读量366

点赞数

分类专栏：知识文章标签：数据库 mysql 教程 1024程序员节

于 2022-03-13 15:35:26 首次发布

本文链接：https://blog.csdn.net/qq_45833812/article/details/123460261

版权

知识专栏收录该内容

14 篇文章 0 订阅

订阅专栏

这并不算一个sql教程，其实我最初是通过mysql必知必会这本书进行入门的学习，学会了crud，后来面试，接触了mysql的一些更重要的内容，像索引，存储引擎等等。我一开始是打算写个教程，后面想想大可不必，网上好的教程多了去了，所以我只记录一些新的、我记不住的。视频教程我推荐B站尚硅谷最新的教程(内容是很全但是时长感人，我是根据需要再去看的)，书的话推荐《mysql是怎么运行的》，还没买，后期买了再更新说说这本书。

基础

概念和原则

sql语句可由多行子句构成，因为空格会被忽略
sql语句分为数据操作语言DML（增删改查）和数据定义语言DDL（创建/删除数据库、表、索引），当然还有DCL(数据控制语言，如权限安全设置语句)
主键不能更新

库的操作

# 添加删除
create/drop database [if not exists] 库名;
# 查看所有库
show databases;
# 查看具体库信息
show create database 库名

💡中括号可省略

表的操作

创建

# 由于表是放在某个数据库的，所以需要先指定数据库 
use 库名;
create table [if not exists] 表名(
	列名 类型 限制,
	列名 类型 限制
)ENGINE=MyISAM;
# 查看库下的表
show tables;
# 查看具体表信息
show create table 表名
# 重命名表
rename table old_name to new_name;

字段限制

auto_increment 自动增长
primary key 主键
[not] null  是否能为空
unique 唯一
default 默认值

增删改查

# 查询去重
select distinct 列名1,列名2 from 表名; #去重，放select后，作用范围是针对所有列
# 插入多行、子查询
insert 表名 (列名1,列名2) values(值1,值2),(值3,值4),(值5,值6); 
insert 表名 (列名1,列名2) select (列名1,列名2) from 表名; 
# 更新
update 表名 set 列名1=值1, 列名2=值2  where 条件
# 删除数据
delete from 表名 where 条件
# 删除整个表
drop table 表名

💡insert ignore --如果存在则忽略，不插入(针对主键等有效)，其实就是隐藏报错

where条件

代替多个or，可使用in
and or 如果同时存在，注意and优先级高
is null 用于检查null值的列
范围条件可用 between 10 and 100，即[10,100]

表连接

join等价inner join 只包含左右匹配的数据
left join 结果包含左边表的所有数据
right join 结果包含右边表的所有数据
full join 左右都包含，无论是否匹配

where实现连接

select 列名... from 表1, 表2 where 表1.列名 = 表2.列名

💡如果没有where的话，每一行会匹配整个表，结果称为笛卡尔积

查询结果拼接

union

select 语句1  union select 语句2 union select 语句3

💡各语句列的数量、类型、和顺序要求一致，注意order by 必须放最后面

⚡️union是会去重的，需要保留重复可用union all

mysql 正则

函数

字符串

拼接：concat()，字段以逗号分隔
截取字符串：substring(“1234”, 1, 3) 1表示第几个字符，3表示截取长度，故结果为123
字符串的长度：length()
替换：replace(字符串, search, replace) 出现的search全部替换成replace，跟java的replace是一样的
大小写：upper()、lower()
去空格：trim()、rtrim()、 ltrim() 只对首尾空格有作用
数值
最大最小值：max() min()，忽略null值
平均值：avg(distinct 指定列)，忽略null值，且能去重
求和：sum(distinct 指定列)，忽略null值，且能去重
返回行数：count(*) 不忽略null，无法用distinct去重，count(distinct 指定列)忽略null值，且指定列可以去重
四舍五入函数：round(列名,保留小数点位数)
绝对值：abs()，忽略null值
其它
时间：now()
版本：version()

分组过滤

group by 和 having

提升

视图操作

作用

可以理解为存储了查询语句，操作只需要对视图操作，简化复杂的sql语句。
可重用、适合大的项目和复杂的查询（视图也需要维护）、控制用户可操作的字段（安全）。

特点

更新删除视图的数据会修改原表的数据，但视图的字段不一定能修改，例如非原表的字段（聚合函数的结果）
join视图的不能有插入删除操作等等

create view viewname as 查询语句; #创建
show create view viewname; #查看视图信息
drop view viewname #删除
crud #跟表的操作一样，只是把表名改为视图名

事务

事务提交的作用是将数据更改到磁盘中

显式事务

begin;  #或者 start transaction(功能更多)
# 注意begin中间出错会失效
dml语句
savepoint pointname; #保存点，回滚时可以使用rollback to pointname返回到保存点，而不是起点。
commit/rollback; #提交或中止

隐式事务
每个语句的执行可视为一个事务，具体可通过查看自动提交变量

show variables like 'autocommit'; # 默认ON，即自动提交
set autocommit = 'OFF' #设置

💡不影响ddl语句，只影响dml语句
⚡️自动提交如果关闭，它的性能会更好，能跟myIsam做比较，从事务提交的作用也可以看出来

问题

标准安装的核心目录

# 安装、配置、命令目录
/usr/lib/mysql
/usr/share/mysql/
/usr/bin

创建可远程访问的对象：链接

#创建可远程登录的用户
create user 'zone'@'%' identified by 'zone';
#授权所有权限，并可以给其他用户授权（with grant option）
grant all privileges on *.* to "zone"@"%" with grant option; 
#刷新权限
FLUSH PRIVILEGES;

账号密码问题：链接

 # 注意不同版本可能变量的命名不同，像我这里跟链接文章是不一样的，所以下面设置需要自己根据终端显示进行修改
show  variables like 'validate_password%'; 
//只校验长度
set global validate_password.policy=0; 
//长度只需要达到3即可
set validate_password.length=3;

设置只是临时的，重启后无效

客户端sqlyog连接mysql8服务器问题，创建用户后，客户端无法解析加密密码（因为mysql8密码认证插件变了），因为MySQL以前的密码认证插件是“mysql_native_password”，而mysql8使用的是“caching_sha2_password”。修改为原来的插件就行，需要用root账号去修改
```
alter user 'zone'@'%' identified with mysql_native_password BY 'zone';
```
linux下启动、查看、停止mysql8服务的命令，注意是mysqld😅
```
systemctl start/status/stop mysqld
```

查看系统信息

字符编码

show variables like 'char%';

可以通过/etc/my.cnf进行修改，不过注意修改编码对之前已经创建的数据库是无效的

mysql引擎

show engines;

索引🐟

学习视频：B站尚硅谷宋红康，内容很多，但时长太长了，感觉有点啰嗦。这里关于索引的几集如下，方便食用
128-130 索引基础
131-133 索引设计原则
115-119 B+树
120 Hash、二叉平衡树、红黑树、B树

对于主键、唯一、外键约束的字段都会自动创建索引。

分类

单值索引、复合索引、主键索引、唯一索引

查看

show index form 表名;

创建

建表时指定
```
# 语法
unique/fulltext/spatial  index  索引名(索引的列  ASC/DESC) 	invisible/visible
#索引类型，默认为普通索引						  升序/降序	    是否可见
create table tt(
	b int,
    index idx_b(b DESC),
);		
```
降序索引是mysql8之后才支持的，升降指的是B+树中对该列的排序是从小到大还是从大到小，默认都是升序。
是否可见也是mysql8的新特性，对于不确定是否删除的索引，可修改为不可见，也就是索引不可用，待确定不需要时再删除(防止删除索引导致系统出问题，同时也可用于分析索引性能–隐藏前后的比较)

建表后指定

alter table 表名  add 索引类型 index  索引名(索引的列 ASC/DESC)   invisible/visible

删除

drop index 索引名 on 表名;

什么情况适合创建索引

主键必加
频繁作为where条件
频繁作为分组和排序的列
join连接的字段可以加索引（但需要注意字段之间的类型要一致，否则触发函数强转会使索引失效）
需要去重（distinct）的字段可以加索引（该字段首先有一定的重复率，不然去重去个寂寞，通过命令行测试比较明显，因为sqlyog软件本身会优化）

索引的设计原则

大量增删改操作如果影响索引动态修改，可先删除索引，然后再创建。
对联合索引，使用频繁的列应放在最左侧
对于多个字段要创建索引的情况，联合索引（一颗）优于单个单个的创建索引（多颗B+树）
创建数据表时，对于能作为索引的列，如果列的类型在满足需求的前提下，越小越好，这样B+树能存更多数据，说白了就是字段大小的设计要合理
使用字符前缀作为索引，而不是整个串

什么情况不适合创建索引

数据量较小的表
经常更新的字段
重复率高的字段
where中使用不到的字段
字段值是无序

sql优化

以下sql测试版本大多使用5.7.16

mysql四层架构

连接层：提供与客户端连接的服务
服务层：提供给用户各种接口（select等）+sql优化器(query optimizer)
引擎层：提供数据的存储方式（innodb等）
存储层：存储数据

explain性能分析

1.使用explain sql来判断索引是否用到，是否用的正确。

2.常用分析字段
在这里插入图片描述
type：优化一般能达到ref和range即算可以
key_len：可用于判断复合索引是否被完全使用，其中计算长度会受编码影响，且为null的索引字段会多用一个字节来标识
其它的比较常用的会在索引优化中提到

索引优化

Extra字段

单索引排序和where条件字段尽量一致，否则会出现using filesort，也就是是否需要额外的排序操作，例如第三条语句

create table t1(a int, b int, index ia(a), index ib(b));
explain select a from t1 where a = 1 order by a;
explain select * from t1 where a = 1 order by a;
explain select * from t1 where a = 1 order by b;

其中，第一条语句Extra字段显示using index，是代表效果比较好的意思，因为他能实现索引覆盖，也就是结果直接从索引中获取。

复合索引的话只要遵循最左前缀原则即可

create table t2(a int, b int, c int, index iabc(a,b,c));
# 要特别注意这里a=1
explain select * from t2 where a = 1 order by a;
explain select * from t2 where a = 1 order by b;
explain select * from t2 where a = 1 order by c;
# 更直观
explain select * from t2 order by a,b;
explain select * from t2 order by a,c;

using temporary，代表效果不好的意思，因为他使用了临时表，一般出现在group by上，规则类似using filesort

create table if not exists t1 (a int, b int, index ia(a), index ib(b));
explain select * from t2 where a = 1 group by a;
explain select * from t2 where a = 1 group by b;

impossible where，当where条件永真的时候会出现

explain select * from t2 where a = 1 and a =2;

using where
需要将数据加载到内存执行where，而无法在引擎层就解决

explain select * from t2 where b = 1;

连接查询

小表驱动大表原则，索引加在表大的连接字段上，一般加在右表，因为连接时基本小表在左边，可类比for循环为什么循环次数小的放外层比内层性能高。

create table tea (tid int, age int, index idx_tid(tid));
create table stu (id int, tid int, index idx_id(id));
explain select stu.id, tea.age from tea left join stu on tea.tid = stu.tid where id =1;


create table tea (tid int, age int);
create table stu (id int, tid int, index idx_id(id), index idx_tid(tid));
explain select stu.id, tea.age from tea left join stu on tea.tid = stu.tid where id =1;

索引失效

更多内容

涉及索引列参与计算、函数使用、类型转换等，都可能导致索引失效。

💡例如where id + 1 = 10会导致索引失效。可以先计算好id = 9或者在右边计算id = 10 - 1；连接查询时，如果两个字段类型不一样，则会触发函数发生强转，包括你where条件查询的类型也要规范，例如字段是字符串类型，那么查询匹配时字符串就该加引号，否则会发生强转（例如int转varchar），但是int类型加上引号反而不会（特殊）

模糊匹配，以“%”开头的LIKE语句
联合索引不符合最左侧原则
否定查询会让索引性能变差(type)，像!=、is not null

💡in not in 对5版本索引基本失效(type=index)，8的type=range；or对5版本基本失效，但不同字段对那个版本都失效，除非使用联合索引

create table t4(a int, b int, index ia(a), index ib(b));
explain select * from t4 where a in (1,2); 
explain select * from t4 where a not in (1,2);
explain select * from t4 where a = 1 or a =2;
# 不同字段，想要不失效，可以使用联合索引
explain select * from t4 where a = 1 or b =2;
create table t4(a int, b int, index iab(a,b));

数据插入优化

如果是导入大量数据，可以使用load data 代替插入insert

其它

数值类型

int 4byte
float 4byte
double 8byte

char(m) varchar(m)

mysql5之后，m为多少，代表能存放多少个字符，不管中文还是英文，都视为1个字符，超出则截取
char详情：链接

m为0代表空串，char不写默认为1，varchar必须写
varchar还使用1~2字节用于记录字符串的实际长度，不算在m里面

varchar最大长度等更多问题：链接
例如设置字符集为utf8的表，varchar(65535/3 - 2) 为最大长度，直接写65535，报错，当然还包括其它原因

is NULL is NOT NULL

用于测试NULL值对于包含可能null值的字段，计算时需加上
IFNULL(字段，返回值)来设置计算结果，例如设返回值为0
则该字段为NULL时，该函数返回所设定的值0，否则结果都是NULL
用isnull()来代替is null

废弃待整理
1.mysql行列转换
行转列
—场景：报表统计、汇总显示
法一：多个子查询进行cross join(生成两张表的笛卡尔集)
法二：case语句
case when 指定条件 then 符合条件的结果 end
列转行
----场景：属性拆分、ETL数据处理
法一：使用序列化表
用到的函数：
mysql length()函数用于获取字符串的长度
select length(‘name’);
语法：replace(object,search,replace)
语义：把object对象中出现的的search全部替换成replace。
select SUBSTRING_INDEX(‘strlist’,‘,’,1),
– 截取字符串中第一个’,'前的内容
substring(参数1，参数2，参数3)，
参数1表示需要截取的字符串
参数2表示从字符串的那个位置开始截取
参数3表示要截取多少位，如果不写，表示截取从参数2指定的位置开始剩下的全部字符。
法二：使用union实现
如何生成唯一序列号
删除重复数据
—查询重复数据–group by + having
—删除重复数据(保留按要求的数据)
derived
delete新用法：delete table_name from table_name与其他表关联的条件
--------------delete from table_name 表列限定条件
mysql
1.注释：
从两个连续横杠 – 序列到行尾。
在MySQL中， - （两个连续横杠）注释样式要求第二个破折号
后跟至少一个空格或控制字符（例如空格，制表符，换行符等）。
此语法与标准SQL注释语法略有不同。
2.DATE：为使查询简单且更易维护，就不要在日期中使用时间部分
~EXTRACT(unit from date)函数返回日期时间(date)的单独部分(unit)
~DATE_ADD(date,INTERVAL expr type)向日期(date)添加指定的时间间隔expr
~DATE_SUB(date,INTERVAL expr type) 向日期减去指定的时间间隔
unit/type的值：
microsecond、second、minute、hour、day、week
month、 quarter(季)、 year、
second_microsecond、
minute_microsecond、minute_secondminute_microsecond、minute_second
hour_microsecond、hour_second、hour_minute
day_…
year_month
datediff(date1,date2)返回两个日期之间的天数
6.删除的表不能用别名
7.同一语句，你不能select出一些数据后再update/delete
需要使用别名再select一次(放入一个结果集)

JDBC

JDBC (Java DataBase Connection) 是通过JAVA访问数据库
sql拼接规则：mysql语句中对于用单引号的’串’
在java中的拼接技巧如下：
①只有字符 “‘字符串’”
②变量构成的串 “'”+变量+“'”
③字符变量混合 “'字符串”+变量+“'”
/*
拼接sql语句时，注意变量或浮点(如果写在""里面，浮点后面不能加f)
*/

简单入门练习

帅过地球一半的男人

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql之路

这并不算一个sql教程，其实我最初是通过mysql必知必会这本书进行入门的学习，学会了crud，后来面试，接触了mysql的一些更重要的内容，像索引，存储引擎等等。我一开始是打算写个教程，后面想想大可不必，网上好的教程多了去了，所以我只记录一些新的、我记不住的。视频教程我推荐B站尚硅谷最新的教程(内容是很全但是时长感人，我是根据需要再去看的)，书的话推荐《mysql是怎么运行的》，还没买，后期买了再更新说说这本书。
复制链接

扫一扫