mysql之路

这并不算一个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 列名... from1,2 where1.列名 =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
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值