MySQL常用操作方法学习以及面试总结

最新推荐文章于 2023-10-16 11:44:53 发布
最新推荐文章于 2023-10-16 11:44:53 发布
阅读量135 收藏
点赞数
分类专栏： Mysql 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42014858/article/details/118329026
版权

MySQL字符串函数

  • DISTINCT 去重
  • CONCAT() 连接字符串
  • IFNULL(user_name,0) as 结果 # username 是字段，如果真等于null 就返回0
  • and or not 编程语言当中的与或非
  • BETWEEN AND BETWEEN 100 AND 200 在100 到200之间 还有 NOT BETWEEN AND
  • <=> 安全等于 也能够查询出来== null
  • LENGTH(字段名) 字节长度
  • UPPER() 转大写
  • LOWER() 转小写
  • substr() ;索引是从1开始的字符长度不是字节
  • instr(); 返回字符串第一次出现的索引
  • trim() 去除空格还能去除其他字符 trim(‘a’ from ‘aaaaaaaa张翠山aaaaaa’);
  • lpad(); 左填充
  • rpad(); 右填充
  • REPLACE(); 替换

常用数学函数

  • round();四舍五入
  • cell();向上取整
  • floor();向下取整
  • truncate(); 截断
  • mod 取余

日期函数

  • NOW() 返回当前系统日期
  • curdate() 不包含时间
  • curtime() 不包含日期
  • data_format(NOW(),"%y年%m月%d日")
  • dataDIFF();查看两个时间的相差，前面的减后面的

流程控制函数

if（10>5,'大','小'）;

sum(distinct list)  distinct 去重之后在求和

分组

group by 添加分组后的筛选 用group 如果针对它进行分组，要判断条件的话，要用having count(*)>2;

分组前筛选  where 原始表的数据，分组后的筛选 having

能用分组前筛选就考虑先用分组前筛选

自连接

  • 一个表中连接另外一个表中的数据 表一表二都是表一
  • inner join 连接和sql92语法中的等值连接效果是一样的，都是查询多表的交

外连接

  • 用于查询一个表中有，另外一个表没有的记录
  • 外连接查询其实是相当于2部分
  • 外连接查询的是主表中所有的记录
  • 第一部分是 相当于是交集部分，有点类似于内连接。
  • 第二部分是 相当于主表中有，但是从表中没有的，我们给从表中填充为null
  • 总结外连接查询的结果，相当于 = 内连接的结果+主表中有而从表中没有的记录
  • 左外和右外交换两个表的顺序，其实可以实现同样的效果

exists(); 返回ture 和false

联合查询

当我们需要查询多个表，但是这多个表没有连接关系，我们可以用联合查询，union
特点：联合查询，查询的列数要是一样的。
默认是第一条的字段。且顺序不要弄倒了
如果2个表有重复，它会去重。如果不想去重的话可以加上 union all

mysql 六大约束

  1. Not null （非空约束）
  2. default 默认约束
  3. 主键约束
  4. unioue; 唯一约束
  5. check 检查约束 MySQL中不支持
  6. 外键约束

主键和唯一索引的区别

  1. 主键不能为空，但是只能有一个
  2. 唯一约束可以为空，可以有多个

视图

1，它只保存了SQL逻辑，没有保存查询结果
2，应用场景
1，当你多个地方用到同样的查询结果。
2，该查询结果使用的语句特别复杂

事务

1，隐士的事务，
比如。insert ，update,delete

存储过程

1，一组预先编译好的SQL语句集合
2，减少了编译次数
3，提高代码重用性
4，简化操作

mysql 分层介绍

1，连接层，2，服务层，3，引擎层，4，存储层

索引


索引是帮助MySQL高效获取数据的数据结构
索引的目的在于提高效率，可以类比字典
什么是索引
排好序的快速查找数据结构

索引的优劣势
优势：提高数据检索的效率，降低数据库的IO成本
劣势：通过索引列对数据进行排序，降低数据库排序成本，降低了CPU的消耗

查看索引

  • show index from 表名

新建索引

建复合索引得命令
create index idx_表名_字段名首字母 on 表名（字段一，字段二，字段三）
ALTER  TABLE  `table_name`  ADD  INDEX index_name (  `column1`,  `column2`,  `column3`  )

删除索引

drop index idx_表名_字段名首字母 on 表名

sql 检查报告

explain 加 sql 语句

执行计划包含的信息
id 
id相同执行顺序由上而下。
id不相同，如果是子查询，id的序号会递增，id越大，优先级越高，越先被执行。
id相同不同，同时存在， 按照大的先执行，其次就是顺序

select_type
SIMPLE  简单查询 其中不包含子查询或者union
PRIMAEY 最后被执行 查询中包含任何复杂的子部分，最外层查询，则被标记
SUBQUERY  在select中或者where里面包含了子查询
DERIVED　在from表中包含的子查询标记为DERIVED（衍生）MySQL会递归执行这些子查询，把结果放在临时表里。
UNION　若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为：DERIVED
UNION RESULT 从UNION表获取结果的select

 table  显示这一行的数据是关于那一张表的
 
 type  访问数据类型  从最好到最差以此如下 system>const>eq_ref>ref>range>index>ALL   一般来说，保证查询级别至少达到range，最好能达到ref。
	 eq_ref  唯一性扫描索引，对于每个索引键，表里只有一条记录相知匹配，常见于主键或者唯一索引扫描
	 ref 非唯一性索引扫描。返回匹配单独值的所有行，本质上也是一种索引访问。它返回所有匹配某个单独值的行，然而它可能找到多个符合条件的行，所以它属于查找和扫描的混合体 
	 range 只检索给定范围的行，使用索引给定范围的行
	 index index与all的区别为Index类型只遍历索引，这通常比ALL快，因为索引文件通常比数据文件小。（通俗来说，虽然ALL和Index都是读全表，单index是从索引中读取，但ALL是从硬盘中读取。）
 
 possible_keys  显示可能应用在这张表中的索引，一个或者多个。查询设计到的字段上所有的索引，但不一定被查询实际使用
 
 key 实际用到的索引，如果为null，就是没有使用索引
 
 key_len  表示索引中使用的字节数，可通过该列的计算查询中使用索引的长度，在不损失精度的情况下，长度越短越好，key_len显示的值是索引字段最大的可能长度，并非实际使用的长度，key_len 是通过表计算而得，不是通过表内检索出的
 查的条件越多，精度就越大。
 
 ref  表之间得引用
 
 rows 每张表有多少行被优化器查询  越少越好

Extra 包含不适合在其他列显示，但是非常十分重要得额外信息

1，Using filesore :mysql 会对数据使用一个外部索引排序，而不是按照表内得索引顺序进行排序  九四一声  文件排序
2，Using temporary :使用了临时表保存中间结果，MySQL在对查询结果排序是使用临时表。常见于 order by 和分组查询 group by。
3，Using index :表示相应得select 操作中使用了覆盖索引，避免访问了表得数据行，效率不错。 如果同时出现了where 表明索引被用来执行索引建值得查找，用了这个说明比较好，用到了索引查找

索引单列表优化

范围后面得索引，会用不上

多表优化索引

这是由左连接得特性决定得，left join 条件用于确定如何从右表搜索行，左边是一定都有，所以右边是我们得关键点，一定要建立索引

join语句优化

尽可能减少join语句中得循环总次数，永远用小结果驱动大的结果集
保证join语句中被驱动表上join条件字段已经被索引。

索引失效问题

最佳昨前缀法则，如果建了一个复合索引，使用的使用一定要带上第一个，如果没带上就会索引失效。
中间兄弟不能短，要按照建得索引顺序来

1，最佳左前缀法则，带头大哥不能死，中间兄弟不能断
2，尽量不要在索引列上计算
3，范围之后的索引全部失效
4，尽量使用复合索引，只访问索引的查询，索引列和查询一致，减少select*
5，在MySQL中使用不等于 或者<> 的时候无法使用索引会导致全表扫描
6，is null 或者is Not null 也不能使用索引
7，百分号like 加右边，这样不会全部扫描，加载开头或者左边会使索引失效，如果你非要使用2个百分号，请用覆盖索引
8，字符串不加单引号，会使索引失效 varchar 类型一定不能失去单引号
9，少用or ,用他来连接会导致索引失效
10，全职匹配我最爱

exists 和 in 的区别

exists 返回的使true 和 false , in 返回的是一个结果集

order by 关键字的排序优化

0,oder by 排序最好不要使用 select * 本来我需要10个字段，然后*的话，给我读取了50个字段，容易把sort buffer给用满了，就容易产生多次IO，这样单路算法还不如双路算法
1，order by 子句尽量使用usiong Index 方式排序，避免使用FileSort方式排序。
2，尽可能在索引列上完成排序操作，遵照索引建得最佳左前缀原则
3，如果不在索引列上，出现filesort的话，MySQL会启用两种算法，一种是双路排序，还有一种是单路排序
4，什么是双路排序：双路排序就是扫描2次磁盘，最终的达到数据，
4，4.1之后单路排序，扫描一次，但是有一个问题，它只能取到sort_buffer缓冲区的大小, 如果一次，取不玩的话，他会取多次，反而得不偿失。
5，优化方法 增大 sort_buffer_size 参数的设置，在尝试提高 max_length_for_sort_data

总结为排序使用索引

1,MySQL是两种排序方式：文件排序或扫描有序索引排序。文件排序是using filesort ,索引排序是 usiong Index.
2,MYSQL 能为排序与查询使用相同的索引，比如是，我查询也能用到，我排序也能用到。一举两得

慢查询日志

1，默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。当然如果不是调优需要的话，一般不建议开启，因为开启慢查询日志，或多或少会带来一定的性能影响，
慢查询日志支持将日志写入到文件当中。
show variables like “%slow_query_log”; 查看是否开始，会告诉你文件路径慢查询记录在那个地方。
set global slow_query_log = 1; 执行命令开启慢查询日志。 使用这个命令开始慢查询日志只对当前数据库生效，如果MySQL重启就会失效。也就是只针对本次。
如果你要永久失效，必须修改配置文件，My.cnf

2，默认情况下慢查询日志，记录的是10S，他这个10S的话，不是大于等于，而是大于

3，可以利用MySQLdumpslow 的帮助信息，来帮助我们分析，一些访问次数，查询时间，锁定时间，平均返回记录数，等一些记录。

show profile

0,show profile 用这个命令查看结果，这么默认是关闭的，并保存最近15次的运行结果 ，你可以通过命令来开启
1,是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量

2，诊断sql show profile cpu,block,io,for query 然后加上上一步执行sql的id

3，下面几个是一定要注意的不能出现的。
create tep table。
Copying to tmp table on disk 内存临时表复制
removing tmp table
createting tmp table = 创建临时表，拷完数据，用完再删
conveting HEAP to MyISAM 查询结果太大

全局查询日志

1，永远不要在生产环境开启这个功能
2，set global general_log = 1;
3, set global log_output = “TABLE”
4,select * from mysql.general_log; 你所执行的sql,都会被存在这个表里面。

MySQL的锁机制

读锁：针对同一份数据，多个读操作可以同时进行而不会互不影响。（共享的）
写锁（排他锁）：当前写操作没有完成前，它会阻塞其他写锁，和读锁

1，表锁（偏读）read:读锁
特点: 偏向MyIsAM存储引擎，开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低。

show open tables; 查看表上加过的锁 显示出来的数据 IN_user = 1显示有锁

lock table 表名字 (read:读锁,write: 写锁); 可以连接写多个。
unlock tables; 解锁
总结：当终端一：开启锁了以后，可以查询当前表，但是不能查询其他表，也不能修改，但终端二可以查询，被锁住的表，也能查询其他表，也不能修改，如果修改了，会造成阻塞，只有等
到终端一解锁了，才会立即插入。

2，write: 写锁
如果是写锁操作，自己可以查询，更新，插入都可以，都没问题，但是不能查询其他表，终端二的可以进行其他表的查询和访问，但是不能查询别人增加写锁的表，也不能修改，如果访问了，也会增加阻塞

总结，MySQL的读写锁有什么区别：简而言之，就是读锁会阻塞写，但是不会阻塞读，而写锁则会把读和写都堵塞。

如何分析表锁定 （MYISAM 适合表锁）

可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统表锁定。
执行SQL命令：show status like ‘table%’;

table_locks_immediate 代表的是产生表级锁定的次数，表示可以立即获取锁的查询次数，没立即获取一次则加一个。
table_locks_waited 出现表级锁定争用而发生等待次数（不能立即获取锁的次数，每等待一次锁值加一）；此值如果高的话，则存在着较严重的表集锁争用情况。

行锁 (适合innoDB)

innoDB 开销大，枷锁满。会出现死锁。
innoDB 支持事务，和行级锁

事务复习

事务具有的四个属性，ACID
原子性（A）：事务是一个原子性操作单元，要么全执行，要么全都不执行。
一致性（C）：在事务开始和完成时，数据都必须保持一致状态。
隔离性（L）：数据库提供一定的隔离机制，保证事务在不受外部并发操作，影响独立环境执行。
持久性（D）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

并发事务处理带来的问题

更新丢失 两个程序员修改同一个Java文件，由于2个人都是独立文本，后面会造成覆盖。
脏读：事务A读到了事务B已经修改，但尚为提交的数据，还在这个数据基础上做了操作，此时如果B事务回滚了，A读取的数据无效，不符合一致性。
不可重复读　事务A读到了事务B已经提交修改的数据，不符合隔离性
幻读 事务A读取到了事务B提交的新增数据，不符合隔离性。

行锁变表锁

索引失效导致行锁变表锁，比如varchar 类型，我没有家双引号，他就会索引失效，行锁变成表锁

什么是间隙锁

当我们用的是范围条件而不是相等条件检索数据,InnoDB会给符合条件的已有数据记录的索引加锁

如何锁定一行，只有我执行完了，提交完了，然后其他终端修改我的数据，才能操作

select * from test where a = 8 for update (for update（锁定某一行后，其他的操作会被阻塞）)

如何分析行锁定

通过检查InnonDB_row_lock 状态变量来分析系统上行锁的争渡情况

静&慢&远
关注
专栏目录
MySQL大总结
耀的博客
01-23 5万+
MySQL是程序员必须要掌握的，其实语法结构相比于其他的数据结构、Java、C++来说应该算是简单的，只要多多练习，拿下它应该不成问题。本文是将我学过的面试笔试重点的知识进行归纳总结，如果你认为写的不错的话，求点赞，求收藏，感谢！！！ 一、MySQL数据库基础 1，数据库的操作 1.1、显示当前数据库 show databases; 1.2 、创建数据库 语法： CREATE DATABASE [IF NOT EXISTS] db_name [create_specif..
【面试真题】--MySQL篇(常用命令总结)
离歌的博客
09-17 846
MySQL篇(常用命令总结),基本所有MySQL要用的命令都有。
Java进阶 谈谈MySQL数据库的学习思路
liulei952413829的博客
04-13 223
文章目录Java进阶 谈谈MySQL数据库的学习思路1. 能够理解数据库的概念2. 能够安装与卸载 MySQL 数据库3. 能够启动,关闭及登录 MySQL4. 能够使用 SQL 语句操作数据库5. 能够使用 SQL 语句操作表结构6. 能够使用 SQL 语句进行数据的添加修改和删除的操作7. 能够使用 SQL 语句进行数据的简单查询操作 Java进阶 谈谈MySQL数据库的学习思路 1. 能够理解数据库的概念 网上一找就能找到一大片关于概念的知识，这里就不多作介绍了，今天主要给大家说说常见的数据库种类和M
mysql线程状态
归来仍少年
08-11 784
mysql线程状态说明 Sending data：线程正在读取和处理select语句的行，并将数据发送到客户端。在此状态期间发生的操作往往会执行大量磁盘访问，因此它通常是给定查询生命周期中运行时间最长的状态。 Sending to client：服务器正在向客户端写入数据包。5.7.8之前是Writing to net setup：线程正在开始一个alter table操作 Sorting for order：线程正在进行排序以满足order by Sorting for group：线程正在进行排序以满
MySQL取每组的前N条记录
slongzhang的博客
11-13 2910
mysql 批量获取每组的前N条数据|每组的后N条数据
mysql 分组，获取每组前几条记录数据
进击的Ace
04-01 1998
最近项目中碰到一个统计最近五周不同区域用电量报表。 上面是需要，实际转为mysql中对数据进行分组，然后再获取每组的前几条记录。 功能效果如下： 下面是表结构： 表结构和表数据sql脚本如下： DROP TABLE IF EXISTS `electrics`; CREATE TABLE `electrics` ( `eid` int(0) NOT NULL AUTO_INCREME...
mysql 分组内取前几条数据
qq_51496789的博客
11-23 4126
分组内取前几条数据，每个学科的前几名
MySQL面试题（总结最全面的面试题）
q66562636的博客
06-24 1830
数据保存在内存数据保存在文件数据保存在数据库第一范式：每个列都不可以再拆分。第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。count(*/column)：返回行数sum(column)： 返回指定列中唯一值的和max(column)：返回指定列或表达式中的数值最大值min(column)：返回指定列或表达式中的数值最小值avg(column)：返回指定列或表达式中的数值平均值date（Expressi
MySQL面试总结
linyerr的博客
09-01 483
MySQL面试总结
mysql运维基础知识面试问答题.pdf
05-16
Oracle：商业软件、功能强大、大型传统企业常用。 应用场景包括数据备份、复杂连接查询、一致性数据存储等。 非关系数据库概念及主要特点： 非关系数据库，也称作NoSQL数据库，它们不使用传统的二维表结构，适合...
mysql分组取每组前几条记录(排名) 附group by与order by的研究
01-19
–按某一字段分组取最大(小)值所在行的数据 代码如下: /* 数据如下： nameval memo a 2 a2(a的第二个值) a 1 a1–a的第一个值 a 3 a3:a的第三个值 b 1 b1–b的第一个值 b 3 b3:b的第三个值 b 2 b2b2b2b2 b 4 b4b4 b 5 b5b5b5b5b5 */ –创建表并插入数据： 代码如下: create table tb(name varchar(10),val int,memo varchar(20)) insert into tb values(‘a’, 2, ‘a2(a的第二个值)’) insert into tb
【Mysql】Sql分组查询后取每组的前N条记录
laker的博客
11-07 5725
一、背景 二、实战解析 三、总结 分组查询后取每组的前N条记录 标签 mysql 数据库 分组 TOPN 一、背景 最近，有个功能需求。系统有个发布资讯模块，要求按照卡片形式展示。如下图: 按照项目组展示卡片，每个项目组展示阅读量最多的TOP2。 需求解析：按照项目组分组，然后取每组阅读量最多的前2条。 二、实战解析 基于Mysql数据库 表定义 1、项目组：team id 主键 name ..
MySql中group by分组排序取前n条数据
向Stack Overflow的大牛学习，编写文章尽量详细。
09-14 6647
分组排序并取前n条数据，是常见的sql需求。会用到group by / order by 。但很多实现方式都不够灵活，效率也不够。本文给出笔者认为比较好的一种实现方式。
mysql 获取每组的前N项记录
LYR 的博客
06-27 388
按照 group 进行分组，每组 里面 a 和 b进行比较 ，这段逻辑是 b里面比 a大的不超过2个，就筛选出来，即筛选出最大的前2个人。找到 a2 比 a大的不超过两个的记录就筛选出来。
oracle或mysql获取分组后每组的前三条数据
上善若泪
06-01 9696
转载于：https://blog.csdn.net/Alex_81D/article/details/104901075 mysql : select a.* from ( select t1.*,(select count(*)+1 from 表 where 分组字段=t1.分组字段 and 排序字段<t1.排序字段) as group_id from 表 t1 ) a where a.group_id<=3 oracle： SELECT t.* FROM (SEL
MySQL 分组获取每组前N条记录
kangaroo的博客
03-26 1092
最近遇到一个需求，需要根据某个字段或者两个字段进行分组，然后获取每组前25条数据进行展示，网上普遍的方法前篇一律，但是普遍查询效率低，需要借助索引优化一部分，这里介绍一种新的查询方式，无索引也能快速返回。
mysql分组排序并取每组的前1条记录
最新发布
weixin_38225763的博客
10-16 1350
最后根据模块类型和供应商用户id分组查询（GROUP BY ss.ModuleType,ss.CreatorId）--这个理在使用GROUP BY是为了过滤一些重复的内容，比如：上面语句去掉GROUP BY ss.ModuleType,ss.CreatorId。row_num
Mysql 取某个字段分组的每组前n条数据
点滴技术
07-06 567
但是存在相同值时，都会展示。如果要求唯一可以参考下面的sql。原理和上面的相同，只是取的是。
Mysql分组取前几条数据
jaemy2011的博客
02-20 197
Mysql分组取前几条数据
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值