Mysql基础知识~连接查询~行列转换~explain解读~引擎简介~分区表

最新推荐文章于 2024-10-13 21:08:42 发布
最新推荐文章于 2024-10-13 21:08:42 发布
阅读量160 收藏
点赞数
分类专栏： 数据库 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_39217714/article/details/108087827
版权

准备工作

1、班级表
CREATE TABLE `t_learn_class` (
  `id` varchar(30) NOT NULL COMMENT '班级id',
  `name` varchar(30) NOT NULL COMMENT '班级名称',
  `remark` varchar(100) NOT NULL COMMENT '备注',
  `extend01` varchar(100) DEFAULT NULL COMMENT '扩展字段',
  `extend02` varchar(100) DEFAULT NULL COMMENT '扩展字段',
  `extend03` varchar(100) DEFAULT NULL COMMENT '扩展字段',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2、学生表
CREATE TABLE `t_learn_student` (
  `id` varchar(30) NOT NULL COMMENT '学号',
  `name` varchar(100) NOT NULL COMMENT '姓名',
  `gender` varchar(4) NOT NULL DEFAULT '' COMMENT '性别',
  `age` varchar(3) NOT NULL COMMENT '年龄',
  `classid` varchar(30) NOT NULL COMMENT '班级id',
  `extend01` varchar(100) DEFAULT NULL COMMENT '扩展字段',
  `extend02` varchar(100) DEFAULT NULL COMMENT '扩展字段',
  `extend03` varchar(100) DEFAULT NULL COMMENT '扩展字段',
  UNIQUE KEY `pk_id` (`id`),
  KEY `idx_classis` (`classid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3、成绩表
CREATE TABLE `t_learn_grade` (
  `studentid` varchar(30) NOT NULL COMMENT '分数表',
  `chinese` varchar(4) NOT NULL,
  `math` varchar(4) NOT NULL,
  `english` varchar(4) NOT NULL,
  `extend1` varchar(100) DEFAULT NULL,
  `extend2` varchar(100) DEFAULT NULL,
  `extend3` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`studentid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
4、班级表数据
INSERT INTO `t_learn_class` VALUES ('001', '零零一', '学前班', NULL, NULL, NULL);
INSERT INTO `t_learn_class` VALUES ('101', '一零一', '一年级一班', NULL, NULL, NULL);
INSERT INTO `t_learn_class` VALUES ('102', '一零二', '一年级二班', NULL, NULL, NULL);
INSERT INTO `t_learn_class` VALUES ('401', '四零一', '四年级一班', NULL, NULL, NULL);
INSERT INTO `t_learn_class` VALUES ('402', '四零二', '四年级二班', NULL, NULL, NULL);
5、学生表数据
INSERT INTO `t_learn_student` VALUES ('1', '张三', '1', '18', '101', NULL, NULL, NULL);
INSERT INTO `t_learn_student` VALUES ('10', '李十二', '0', '23', '102', NULL, NULL, NULL);
INSERT INTO `t_learn_student` VALUES ('11', '万十三', '1', '52', '201', NULL, NULL, NULL);
INSERT INTO `t_learn_student` VALUES ('12', '王十四', '0', '23', '201', NULL, NULL, NULL);
INSERT INTO `t_learn_student` VALUES ('13', '屈十五', '1', '46', '201', NULL, NULL, NULL);
INSERT INTO `t_learn_student` VALUES ('14', '涂十六', '0', '52', '201', NULL, NULL, NULL);
INSERT INTO `t_learn_student` VALUES ('15', '程十七', '1', '23', '201', NULL, NULL, NULL);
INSERT INTO `t_learn_student` VALUES ('16', '赵十八', '0', '42', '202', NULL, NULL, NULL);
INSERT INTO `t_learn_student` VALUES ('17', '郑十九', '1', '34', '202', NULL, NULL, NULL);
INSERT INTO `t_learn_student` VALUES ('18', '程二十', '0', '25', '202', NULL, NULL, NULL);
INSERT INTO `t_learn_student` VALUES ('19', '涂二十一', '1', '42', '202', NULL, NULL, NULL);
INSERT INTO `t_learn_student` VALUES ('2', '李四', '0', '20', '101', NULL, NULL, NULL);
INSERT INTO `t_learn_student` VALUES ('20', '孙二十二', '0', '12', '202', NULL, NULL, NULL);
INSERT INTO `t_learn_student` VALUES ('3', '王五', '1', '10', '101', NULL, NULL, NULL);
INSERT INTO `t_learn_student` VALUES ('4', '赵六', '1', '18', '101', NULL, NULL, NULL);
INSERT INTO `t_learn_student` VALUES ('5', '徐七', '0', '14', '101', NULL, NULL, NULL);
INSERT INTO `t_learn_student` VALUES ('6', '陈八', '1', '13', '102', NULL, NULL, NULL);
INSERT INTO `t_learn_student` VALUES ('7', '许九', '1', '25', '102', NULL, NULL, NULL);
INSERT INTO `t_learn_student` VALUES ('8', '程十', '0', '42', '102', NULL, NULL, NULL);
INSERT INTO `t_learn_student` VALUES ('9', '张十一', '1', '14', '102', NULL, NULL, NULL);
6、成绩表数据
INSERT INTO `t_learn_grade` VALUES ('1', '80', '74', '69', NULL, NULL, NULL);
INSERT INTO `t_learn_grade` VALUES ('2', '79', '56', '88', NULL, NULL, NULL);
INSERT INTO `t_learn_grade` VALUES ('3', '99', '78', '87', NULL, NULL, NULL);
INSERT INTO `t_learn_grade` VALUES ('4', '67', '98', '67', NULL, NULL, NULL);
INSERT INTO `t_learn_grade` VALUES ('5', '98', '67', '100', NULL, NULL, NULL);

一、连接的使用

1、内连接——inner join
	SELECT T1.ID,
		   T1.NAME,
		   T1.AGE,
		   T1.CLASSID,
		   T2.NAME
	  FROM T_LEARN_STUDENT T1
INNER JOIN T_LEARN_CLASS T2
        ON T1.CLASSID = T2.ID;

查询结果：
在这里插入图片描述

2、左外连接——left join
	SELECT T1.ID,
		   T1.NAME,
		   T1.AGE,
		   T1.CLASSID,
		   T2.NAME
	  FROM T_LEARN_STUDENT T1
LEFT JOIN T_LEARN_CLASS T2
        ON T1.CLASSID = T2.ID;

查询结果：
在这里插入图片描述

3、右外连接——right join
	SELECT T1.ID,
		   T1.NAME,
		   T1.AGE,
		   T1.CLASSID,
		   T2.NAME
	  FROM T_LEARN_STUDENT T1
RIGHT JOIN T_LEARN_CLASS T2
        ON T1.CLASSID = T2.ID;

查询结果：
在这里插入图片描述

4、全外连接——full join
	SELECT T1.ID,
		   T1.NAME,
		   T1.AGE,
		   T1.CLASSID,
		   T2.NAME
	  FROM T_LEARN_STUDENT T1
 FULL JOIN T_LEARN_CLASS T2
        ON T1.CLASSID = T2.ID;

查询报错，mysql不支持全外连接，使用UNIOIN方式实现全外连接

	SELECT T1.ID,
		   T1.NAME,
		   T1.AGE,
		   T1.CLASSID,
		   T2.NAME
	  FROM T_LEARN_STUDENT T1
 LEFT JOIN T_LEARN_CLASS T2
        ON T1.CLASSID = T2.ID
	 UNION 
	SELECT T1.ID,
		   T1.NAME,
		   T1.AGE,
		   T1.CLASSID,
		   T2.NAME
	  FROM T_LEARN_STUDENT T1
RIGHT JOIN T_LEARN_CLASS T2
        ON T1.CLASSID = T2.ID

查询结果：
在这里插入图片描述

5、交叉连接——cross join
	SELECT T1.ID,
		   T1.NAME,
		   T1.AGE,
		   T1.CLASSID,
		   T2.NAME
	  FROM T_LEARN_STUDENT T1
CROSS JOIN T_LEARN_CLASS T2

查询结果，部分数据：
在这里插入图片描述

二、行列转换

1、行转换列

统计每个班的人数

	SELECT count(*), 
		   T1.classid
	  FROM T_LEARN_STUDENT T1
  group by T1.classid

查询结果：
在这里插入图片描述

使用cross join方式
SELECT *
 FROM ( SELECT COUNT(*) '101'
          FROM T_LEARN_STUDENT T1
   	  WHERE CLASSID = '101'
      ) TT1
   	 CROSS JOIN
     ( SELECT COUNT(*) '102'
         FROM T_LEARN_STUDENT T2
        WHERE CLASSID = '102'
     ) TT2
   	 CROSS JOIN
     ( SELECT COUNT(*) '201'
         FROM T_LEARN_STUDENT T3
   	 WHERE CLASSID = '201'
     ) TT3
   	 CROSS JOIN
     ( SELECT COUNT(*) '202'
         FROM T_LEARN_STUDENT T4
   	 WHERE CLASSID = '202'
     ) TT4

转换后的结果：
在这里插入图片描述

使用case when方式
 SELECT count(case when classid = '101' then id end) '101',
		count(case when classid = '102' then id end) '102',
		count(case when classid = '201' then id end) '201',		
		count(case when classid = '202' then id end) '202'		
   FROM T_LEARN_STUDENT

转换结果：
在这里插入图片描述

2、列转换行

将成绩表的列形式数据转换成行数据
表数据如下：
在这里插入图片描述

使用union all方式
select studentid, 'chinese' subject, chinese 'grade' from t_learn_grade
union all 
select studentid, 'math' subject, math 'grade' from t_learn_grade 
union all
select studentid, 'english' subject, english 'grade' from t_learn_grade

转换后结果如下：
在这里插入图片描述

3、explain解读

查询101班的学生成绩表

select t1.name '班级名',
	     t2.name '姓名',
	     t3.chinese '语文',
	     t3.math '数学',
	     t3.english '英语'
  from t_learn_class t1
  left join t_learn_student t2 on t1.id = t2.classid
  left join t_learn_grade t3 on t3.studentid = t2.id
 where t2.classid = '101';

执行计划的使用：

explain
 select t1.name '班级名',
				t2.name '姓名',
				t3.chinese '语文',
				t3.math '数学',
				t3.english '英语'
	 from t_learn_class t1
	 left join t_learn_student t2 on t1.id = t2.classid
	 left join t_learn_grade t3 on t3.studentid = t2.id
	where t2.classid = '101';

执行计划如下：
在这里插入图片描述
select_type：三个表都是simple，是简单查询
table：分别对应是t1,t2,t3表
partitions：三张表都未使用分区
type：对t1表是const常量级别，对t2表是连接查询， 对t3表是更优的查询
possible_keys：可能使用的索引
key：实际使用的索引，t1,t3使用的是主键，t2使用的是索引
key_len：索引长度
ref：连接的级别，t1,t2是常量级别连接
rows：行数
filtered：100%
Extra：备注信息，t2表使用了索引

引擎简介

实际开发会用到的三种引擎：InnoDB，MyIsAM，MEMORY
InnoDB：使用最多的引擎，存储限制64TB（够用）。支持事务管理，在SpringBoot使用事务注解，操作的表必须是这种引擎，否则事务会失效。对表数据进行操作时，锁的是行数据，即其他的行数据，不会被锁。
MyIsAm：较常用的引擎，存储限制256TB。不支持事务管理，事务注解会失效。对表数据进行操作时，锁的是全表。
MEMORY：常用于临时表，表数据存储在内存中，数据会丢失。

分区表

工作中常常会创建日志分区表来存储日志，下面创建一个日志分区表

CREATE TABLE `t_learn_log` (
  `logid` varchar(30) NOT NULL COMMENT '日志id',
  `platform` varchar(30) NOT NULL COMMENT '对端',
  `url` varchar(500) NOT NULL COMMENT '请求url',
  `reqbody` text COMMENT '请求报文',
  `respbody` text COMMENT '返回报文',
  `createtime` datetime NOT NULL COMMENT '创建时间',
  `costtime` varchar(10) NOT NULL COMMENT '耗时',
  `result` varchar(4) NOT NULL COMMENT '结果:0为成功，1为是失败',
  `extend01` varchar(255) DEFAULT NULL COMMENT '扩展字段一',
  `extend02` varchar(255) DEFAULT NULL COMMENT '扩展字段二',
  `extend03` varchar(255) DEFAULT NULL COMMENT '扩展字段三',
  PRIMARY KEY (`logid`,`createtime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE  COLUMNS(createtime)
(PARTITION p202009 VALUES LESS THAN ('2020-10-01') ENGINE = InnoDB,
 PARTITION p202010 VALUES LESS THAN ('2020-11-01') ENGINE = InnoDB,
 PARTITION p202011 VALUES LESS THAN ('2020-12-01') ENGINE = InnoDB,
 PARTITION p202012 VALUES LESS THAN ('2021-01-01') ENGINE = InnoDB,
 PARTITION p202101 VALUES LESS THAN ('2021-02-01') ENGINE = InnoDB,
 PARTITION p202102 VALUES LESS THAN ('2021-03-01') ENGINE = InnoDB,
 PARTITION p202103 VALUES LESS THAN ('2021-04-01') ENGINE = InnoDB,
 PARTITION p202104 VALUES LESS THAN ('2021-05-01') ENGINE = InnoDB,
 PARTITION p202105 VALUES LESS THAN ('2021-06-01') ENGINE = InnoDB,
 PARTITION p202106 VALUES LESS THAN ('2021-07-01') ENGINE = InnoDB,
 PARTITION p202107 VALUES LESS THAN ('2021-08-01') ENGINE = InnoDB,
 PARTITION p202108 VALUES LESS THAN ('2021-09-01') ENGINE = InnoDB,
 PARTITION p202109 VALUES LESS THAN ('2021-10-01') ENGINE = InnoDB,
 PARTITION p202110 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);

分区表的分区字段必须是主键的一部分，可以是联合主键。

study_alltheway
关注
专栏目录
mysql多表连接查询执行过程_Mysql多表连接查询的执行细节（一）
weixin_33533460的博客
02-03 1564
先构建本篇博客的案列演示表：create table a(a1 int primary key, a2 int ,index(a2)); --双字段都有索引create table c(c1 int primary key, c2 int ,index(c2), c3 int); --双字段都有索引create table b(b1 int primary key, b2 int);--有主键...
mysql 分区表合并_MySQL 合并表、分区表
weixin_36338946的博客
01-28 1007
合并表合并表示一种早期的、简单的分区实现，在未来可能被淘汰，不推荐使用。限制：合并表和子表的字段、索引要完全相同即使子表上有主键限制，合并表中仍然会出现重复值如果删除其中一个子表，表可能还存在，带只能通过合并表访问(视操作系统而定)合并表上无法使用replace语法，无法使用自增长字段特性：一个MyISAM表可以是多个合并表的子表可以直接复制.frm、.MYI、.MYD文件实现在不同服务器间复制子...
mysql 行转列_mysql行转列问题 | 来自一位读者本周五的面试题
weixin_39968823的博客
12-05 100
罗小黑战记欢迎点击上方蓝色字体『Bella的技术轮子』关注哦～昨天在家中突然收到一位读者发来的求助信息。由于这位读者前几天和我说正在面试菜鸟，我下意识的以为是面试题，想要拒绝，因为我的原则是不帮助正在面试中的人，面试后可以讨论自己有疑惑的题。后来发现是读者周五面试的面试题，于是我便开始了安装mysql、可视化客户端、coding。面试题如下：不知正在读这篇文章的你，看到此题是否有思路，可...
oracle行转列后左连接,oracle行转列
weixin_36459367的博客
04-15 148
针对oracle数据查询的数据，行转列1.wm_concat函数：例一：select c1,c2,wm_concat(c3) from T where.... group by c1,c2查询结果自动用“,”分割例二：select c1,c2,wm_concat(c3)over(partition by ..order by..) from T where....其中，partition用来分组，...
左连接 用于 列转行的一个想法
weixin_30551947的博客
10-12 84
上图表一 上图表二 实现表二 3列变成 表一的行效果下图 实现代码： select a.LandID, a.LandType, a.FarmID, a.StatusID, a.LandCode, a.LandName, a.Comment, b.UID, c.ParaValue 'PRICE', ...
mysql实现行转列的两个方式
鱼火
08-02 4373
两个方式，用任何一个都可以实现。 1.用cross join的方式实现 select * from (select sum(a.kills) '孙悟空' from kills1 as a LEFT JOIN tssrz as b on a.user_id = b.id WHERE b.user_name = '孙悟空' GROUP BY b.user_name)
Mysql动态行转列（不使用存储过程）
qq_41840735的博客
01-14 2121
实现思路： 动态拼接执行的SQL语句，先把要执行的语句拼在变量里，之后执行。 拼接的过程就是要动态找出数据里的所有列，然后拼成SQL – 建表语句： DROP TABLE IF EXISTS student_score; CREATE TABLE student_score ( id int(8) NOT NULL, name varchar(20) DEFAULT NULL, project varchar(20) DEFAULT NULL, score int(8) DEFAULT NULL, PRIM
MySql中如何使用 explain 查询 SQL 的执行计划
12-16
explain命令是查看查询优化器如何决定执行查询的主要方法。 这个功能有局限性，并不总会说出真相，但它的输出是可以获取的最好信息，值得花时间去了解，因为可以学习到查询是如何执行的。 1、什么是MySQL执行计划  ...
mysql分区表
willplay9的博客
12-08 1068
battery_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '电芯码',`tray_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '托盘码',按照半年一个分区,修改语法,如果表中已有数据, 在分区完成后,旧数据会自动按照分区规则分配至对应的分区.
mysql左连接多个列,SQL 左外连接查询 将右表中的多行变为左表的一列或多列
weixin_34604756的博客
03-23 481
示例：--行列互转/******************************************************************************************************************************************************以学生成绩为例子，比较形象易懂整理人：中国风(Roy)日期:2008.06.06...
mysql 将查询出来的值进行转换
java-admin
11-03 3398
如下：      mysql支持if         SELECT   IF(1<2,'yes','no');     输出yes         同时，还支持case         SELECT   CASE   WHEN   1>0   THEN   "yes"   ELSE   "no"   END;     还是输出yes       select c1.*,if(c2.to...
mysql 分数表 行数据转成列数据 行列转换
mama476984957的博客
06-28 1930
今天，在群里遇到有人问关于分数表行列转换的问题。我找小兄弟拿到了数据，写了sql 做了演示。 mysql 数据库 中，我们大部分查询是基于行的，查询一行或者几行数据，取出来。但是，有时候有些标的数据设计 是把列数据变成了行数据存储，这时候我们在查询数据的时候就需要把行数据转成列数据来展示。 这就是行列的转换，一般情况下很少遇到，不过遇到了之后，对mysql不是太熟了的同学可能有些棘手。今天，我就...
MySQL中explain执行计划中额外信息字段(Extra)详解
热门推荐
poxiaonie的博客
08-31 6万+
SQL执行计划是经过优化器决策，产生的SQL在数据库内部执行的访问路径计划； 由如下语法得到：explain select col1,col2 from t1..; desc select col1,col2 from t1..;理解输出各个列的含义 id:每个select子句的标识id select_type:select语句的类型 table:当前表名 显示查询将访问的分区，如果你的查询是基于
mysql 分区 explain_Mysql explain 查看分区表
weixin_36374105的博客
01-21 322
mysql> explain select * from ClientActionTrack where startTime>'2016-08-25 00:00:00' and startTime+----+-------------+-------------------+------+---------------+------+---------+------+-------+-...
mysql 数据库字段动态扩展
时光钟摆
12-03 8485
主要有一下几种方案： 1.动态添加属性字段         当数据库中需要增加一个字段的时候，直接在数据库中增加，并修改相应的代码。         优点：操作简单，易懂         缺点：每增加一个字段都需要修改数据库表结构，修改代码。而且在一张大表进行操作的时候，还可能需要很长时间。 2.JSON格式保存         把需要扩展的字段统一放到一个字段里面。各个字段通过JSO...
mysql行列转换
zlxxxx123的博客
08-30 209
1.一维转二维 上图为成绩表中数据，现希望将数据转换为下图。 ①静态：转化为二维表后的列名及列数是确定不变的，本例中即course只有数学、语文、英语这三门课。 select s_name, max(if(course="数学",score,0)) as 数学, max(if(course='语文',score,0)) as 语文, max(if(course='英语',score,0)) a...
MySQL中表的约束
最新发布
pouop的博客
10-13 1016
表中一定要有各种约束，通过约束，让我们来插入数据库中的数据是符合预期的。约束本质是通过技术手段，倒逼程序员插入正确的数据；反过来，站在MySQL的角度来单，内部已经插进来的数据，都i是符合数据约束的数据。约束的最终目标：保证数据完整性和可预期性为了完成以上的所有目标，就需要除了数据类型自带的范围约束以外的更多约束。
MySQL查询优化：深度解析EXPLAIN
"MySQL查询优化之explain的深入解析，通过EXPLAIN关键字分析查询性能，理解MySQL查询优化器的工作原理和查询执行计划。" 在MySQL数据库管理中，查询优化是提高系统性能的关键步骤。`EXPLAIN`是MySQL提供的一种工具...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值