数据库全

aoyoudexiaoshayu

已于 2024-07-19 16:28:26 修改

阅读量1.1k

点赞数 30

文章标签：数据库 sql mysql

于 2022-10-28 15:54:50 首次发布

本文链接：https://blog.csdn.net/aoyoudexiaoshayu/article/details/127536001

版权

MYSQL=> ifnull函数

不做详解，工作中发现的注意事项

// 当获取第二个数值，想其为空时返回null
select ifnull((select salary from employee limit 1,1),null) 
//可以当所查为空时返回null
//而如下的写法无法在查询为空的时候返回null
select ifnull(salary,null) from employee order by salary desc limit 1,1

//解析
//第一个
IFNULL 函数包裹了整个子查询。这意味着即使子查询的结果是 NULL（例如，当表中没有足够的记录来满足 LIMIT 1,1 的要求时），IFNULL 也会捕获这个 NULL 并将其替换为其第二个参数，也就是 NULL。因此，最终结果将是 NULL

//第二个
IFNULL 函数是在每一行的数据上操作的。但是，ORDER BY 和 LIMIT 子句会在 IFNULL 函数执行之前对结果进行筛选。如果 LIMIT 1,1 指定的行不存在（例如，表中少于两行数据），则整个查询将不会返回任何行，而不是返回一个 NULL 值。因此，不会看到任何结果，也不会得到一个明确的 NULL

数据库操作

1、查 show database;
2、创 create database 表名;
3、用 use 表名;
4、删 drop database 表名;

表操作

1、创建表 create table 表名( 字段1 ，字段2 ，字段3）
2、复制表 create table 表名 select * from 原表名 
3、临时表 create temporary table 表名（字段1 ， 字段2 ， 字段3 ）
4、查看所有表 show tables
5、查看表结构 ① describe 表名    ② show columns in 表名
6、删表 drop table if exists 表名           注：if exists是存在时候删，不存在拉倒
7、重命名 rename table 原名 to 新名

表修改

改结构 
1、alter table 表名 add 新字段
2、alter table 表名 drop 原字段
3、alter table 表名 change 更改字段 更改后的字段信息

插入数据

1、insert into 表名(字段1 ，字段2 ，字段3 等) values (字段1信息 ， 字段2信息 ，字段3信息)，可以同时加几条
2、插入查询的数据 insert into 表1(字段1 ， 字段2 ) select 字段1 ， 字段2 from 表2

更新数据

update 表1 set 字段*=*** where 条件

删除数据

delete from 表1 where 条件

条件控制

1、where 
2、having
3、符号 ： = ，<， >， <>， in， between 左范围 and 右范围， not， and， or， linke()， is null              注：linke %任意，_一个字符

常见函数

1、字符串连接 concat
    select concat(字段1，'=>'，字段2) from 表1；
2、数字函数 avg 、 sum 、 max 、 min 、 count
3、文本处理 trim 、 locate 、 upper 、 lower 、 substring
4、时间函数 date() 、 curtime() 、 day() 、 year() 、 now()

分组查询

1、group by ：
    select * from 表1 group by 字段1        后面还可以跟上having对分组后的数据进行条件判断，order by desc/asc 升降序排序

数据库基础操作优先级

在这里插入图片描述

索引小概括

1、体系结构：连接层、服务层、引擎层、存储层
2、存储引擎简介：①show engines ②create table xx(...)engine=innodb
3、存储引擎特点：    INNODB和MyISAM：事务、外键、行级锁
4、存储引擎应用：
    ①INNODB：存储业务系统中对事务、数据完整性要求较高的核心数据
    ②MyISAM：存储业务系统中的非核心事务

索引语法

1、创建索引：CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name,... ) ;
2、查看索引：SHOW INDEX FROM table_name ;
3、删除索引：DROP INDEX index_name ON table_name ;

变量定义

1、局部变量 👉 declare
2、用户变量 👉 select @变量名
3、会话变量 👉 session 当前会话有效，仅限于本次连接
4、全局变量 👉 global 在整个生命周期有效

SQL性能分析

1、查看访问频次：show global status like 'Com_______'
2、慢查询日志：先配置日查询日志的开启和限时时间，记得重启；在 /var/lib/mysql/localhost-slow.log 中查看超时操作的日志
3、profile 详情：开启profilie后，
    ①用 show profiles 可以查看当前会话中的所有sql操作用时；
    ②用 show profile for query id(上面查出来的id)  可以看本次sql在各处用时
    ③用 show profile cpu for query id(上面查出来的id) 可以查看该sql语句cpu耗时
4、explain 计划：explain/desc 获取 mysql 如何执行 select语句的信息  直接在原 select语句 前添加 explain/desc 即可

在这里插入图片描述

索引失效

1、最左前缀法则————使用联合索引时，最左侧的字段必须要存在，否则无法使用索引，如果三个字段abc的联合索引中
   跳过了b，那么c的索引也不会使用；与编写sql的先后顺序无关
2、当范围查询————如果使用<、>，编写sql的右侧索引失效，可以尽可能使用≥、≤，这不会造成索引失效
3、索引列运算————在索引列上使用列操作会无法使用索引，比如a字段（int）设置了索引，在a字段查询时，使用a*2=b这种就会无法使用索引
4、引号————尽可能在需要引号的地方使用上引号，部分字段省略时会无法使用索引
5、模糊搜索————尾部模糊匹配时，索引正常使用；头部模糊匹配时，索引直接失效；
6、or连接————or连接的条件中，前包含a列，后包含b列，如果ab有一个没索引，那么在本次sql语句都无法使用索引

指定索引

当一个字段拥有单独索引和在联合索引中时，可以使用sql提示优化数据库
1、建议使用什么索引————use index
   eg：explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';
2、忽略指定索引————ignore index
   eg：explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';
3、强制使用索引————force index
   explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';

前缀索引

1、创建————为tb_user表的email字段，建立长度为5的前缀索引
   create index idx_email_5 on tb_user(email(5));
2、取长度————不重复的索引值和总数据量比值越接近1越好，通过下述方式查看
   select count(distinct substring(email,1,5))/count(*) from tb_user;

索引设计原则

1、数据量大，查询频繁的表建立索引
2、常作为 查询条件——where、排序——order by、分组——group by 的字段建立索引
3、区分度越高，使用索引的效率越高
4、字符串等较长字段可以用前缀索引
5、尽量使用联合索引，因为可以覆盖索引，节省空间、避免回表、提升效率
6、索引并非越多越好
7、如果索引列无null，建表时就应该not null约束该字段，有利于索引查询字段

SQL优化

加载数据，用本地加载方式

1、连接mysql时，加上参数 --local-infile
   mysql –-local-infile -u root -p
2、设置其全局变量为1，开启本地加载文件导入数据的方式
   set global local_infile=1
3、加载数据
   load data local infile '/root/sqll.log' into table tb_user fields terminated by ',' lines terminated by '\n'

插入数据使用顺序插入

主键顺序插入可以避免出现表分裂的情况
1、顺序插入
在这里插入图片描述
2、主键乱序插入，导致表分裂

order by优化

Using index（根据有序索引查找）性能高，而Using filesort（根据表索引或者全表扫描，在排序缓冲区
sortbuffer 中完成排序操作）性能低，我们在优化排序操作时，尽量要优化为 Using index。做法就是给字段添加进入索引中
调用的时候也要符合 最左前缀原则
建立索引的时候没有指定顺序就都是默认升序，调用的时候要么全升要么全降，否则无法using index

group by优化

与order by优化方式基本一致

limit 优化

一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查
询形式进行优化。
例如说：先覆盖索引查询符合条件的主键，然后自表连接该临时表，再通过主键索引查询出来

explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;】

存储过程（可以理解为一个方法、函数）

1、封装，复用
2、可以接受参数，返回数据
3、减少网络交互

基本语法

1、create procedure 存储过程名（参数）
   begin
      --sql语句
   end；
2、call 名称（参数）；
3、select * from information_schema.routines where routine_schema='xxx'  #查询指定数据库的存储过程以及状态信息
   select create procedure 存储过程名称； #查询某个存储过程的定义
4、drop procedure [ if exists ] 存储过程名称 ；

变量

1、系统变量（ global—全局变量、 session—会话变量）

1、show [ session | global ] variables ;     #查找所有的系统变量
2、show [ session | global ] variables like '...'     #like模糊匹配查找变量
3、select @@[ session | global ] 系统变量名 ；    #查看指定变量的值
4、set [ session | global ] 系统变量=值 ；    #设置系统变量方法1
5、set @@[ session | global ] 系统变量=值 ；     #设置系统变量方法2

2、用户变量

1、set @名称=值    # 赋值方法1   等号“=”可以用 “：=”替换
2、select 字段名 into 名称 from 表名 ；    #赋值方法2

3、局部变量

1、声明——declare 变量名 变量类型 [ default ... ]
2、赋值——set 变量名 = 值；
   或者  select 字段名 into 变量名 from 表名 ...；

参数

在这里插入图片描述

if

if 条件1 then
   --执行xxx
elseif 条件2 then
   --执行xxx
else
   ...
end if；

case

用法1
在这里插入图片描述
用法2

while

在这里插入图片描述

repeat （实际就是Java中的do while）

在这里插入图片描述

loop

在这里插入图片描述
关键用法是
标记：loop
中间一段过程
end loop 标记；（结束）

游标

1、 declare 游标名称 cursor for 查询语句； #创建
2、 open 游标名称； #打开游标
3、 fetch 游标名称 into 变量； #获取游标记录
4、 close 游标名称； #关闭游标

数据库的传播性质

传播行为分为两种：分为支持事物的传播和不支持事物的传播

1、PROPAGATION_REQUIRED：（支持事物）支持当前事务，如果当前存在事务，就加入该事务，如果当前没有事务，就创建一个新事务，该设置是最常用的设置。
2、PROPAGATION_SUPPORTS：（支持事物）支持当前事务，如果当前存在事务，就加入该事务，如果当前不存在事务，就以非事务执行。
3、PROPAGATION_MANDATORY：（支持事物）支持当前事务，如果当前存在事务，就加入该事务，如果当前不存在事务，就抛出异常。
4、PROPAGATION_REQUIRES_NEW：（支持事物）创建新事务，无论当前存不存在事务，都创建新事务。
5、PROPAGATION_NOT_SUPPORTED：（不支持事物）以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。
6、PROPAGATION_NEVER：（不支持事物）以非事务方式执行，如果当前存在事务，则抛出异常。
7、PROPAGATION_NESTED：（不支持事物）如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则执行与PROPAGATION_REQUIRED类似的操作。

已混淆点：spring的事务是什么？与数据库的事务是否一样
本质上其实是同一个概念,spring的事务是对数据库的事务的封装,最后本质的实现还是在数据库,假如数据库不支持事务的话,spring的事务是没有作用的.数据库的事务说简单就只有开启,回滚和关闭,spring对数据库事务的包装,原理就是拿一个数据连接,根据spring的事务配置,操作这个数据连接对数据库进行事务开启,回滚或关闭操作

未完待续，后续补充

小知识

1、MySQL基础———— 语句末尾的";"可以使用"\G"替换，然后查询到的横向表格纵向展示
2、索引———— 同一条sql，执行计划可能不一样；在查询时MySQL会评估是索引快还是全表扫描快，依此选择使用什么方法查询
3、MySQL基础———— mysql字段中的数据字符起始由 1 开始，例如substring中剪切位置
4、MyISAM 引擎———— 把一个表的总行数存在表盘，如果是直接读取 count(*)时效率很高，但是带条件的时候效率也慢
   InnoDB 引擎———— 执行count时，是把数据一行一行的读取出来
5、InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁升级为表锁 。