What are the characteristics of Mysql joint table query-Mysql Tutorial-php.cn

What are the characteristics of Mysql joint table query

WBOY

Release： 2023-05-29 11:28:05

forward

1710 people have browsed it

Preface

In order to reduce the number of queries to the database, for example, in order to reduce the pressure on the system in unrelated tables, we can use the union all keyword to make a union of data found in multiple tables. Query processing

(facilitates the use of different data in statistical analysis with only one request)

Example: Query the total number of male students in the student table through a SQL statement in one query The total number of teachers whose gender is male in the teacher table

What are the characteristics of Mysql joint table query

Database table preparation:

1, student table

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT &#39;&#39;,
  `birth` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT &#39;&#39;,
  `sex` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT &#39;&#39;,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (&#39;01&#39;, &#39;赵雷&#39;, &#39;1990-01-01&#39;, &#39;男&#39;);
INSERT INTO `student` VALUES (&#39;02&#39;, &#39;钱电&#39;, &#39;1990-12-21&#39;, &#39;男&#39;);
INSERT INTO `student` VALUES (&#39;03&#39;, &#39;孙风&#39;, &#39;1990-05-20&#39;, &#39;男&#39;);
INSERT INTO `student` VALUES (&#39;04&#39;, &#39;李云&#39;, &#39;1990-08-06&#39;, &#39;男&#39;);
INSERT INTO `student` VALUES (&#39;05&#39;, &#39;周梅&#39;, &#39;1991-12-01&#39;, &#39;女&#39;);
INSERT INTO `student` VALUES (&#39;06&#39;, &#39;吴兰&#39;, &#39;1992-03-01&#39;, &#39;女&#39;);
INSERT INTO `student` VALUES (&#39;07&#39;, &#39;郑竹&#39;, &#39;1989-07-01&#39;, &#39;女&#39;);
INSERT INTO `student` VALUES (&#39;08&#39;, &#39;王菊&#39;, &#39;1990-01-20&#39;, &#39;女&#39;);
SET FOREIGN_KEY_CHECKS = 1;

Copy after login

2. Teacher table

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT &#39;&#39;,
  `sex` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (&#39;01&#39;, &#39;张三&#39;, &#39;男&#39;);
INSERT INTO `teacher` VALUES (&#39;02&#39;, &#39;李四&#39;, &#39;女&#39;);
INSERT INTO `teacher` VALUES (&#39;03&#39;, &#39;王五&#39;, &#39;男&#39;);
SET FOREIGN_KEY_CHECKS = 1;

Copy after login

1. Traditional method (poor query performance)

Encapsulate the queried data into a table, and query the data in the table separately Show it.

This method is relatively simple but will greatly increase the number of database queries

SELECT
	t1.学生男生总数,
	t2.男教师总数 
FROM
	( SELECT count( id ) AS 学生男生总数 FROM student WHERE student.sex = &#39;男&#39; ) t1,
	( SELECT count( id ) AS 男教师总数 FROM teacher WHERE teacher.sex = &#39;男&#39; ) t2

Copy after login

What are the characteristics of Mysql joint table query

2. Use union all to combine multiple tables into one table query

	select t.*  from
	( SELECT count(id) as a,0 as b FROM student WHERE student.sex = &#39;男&#39;
			union all
	SELECT 0 as a,count(id) as b FROM teacher WHERE teacher.sex = &#39;男&#39; ) t

Copy after login

1. At this time, a represents the total number of students whose gender is male, and b represents the total number of teachers whose gender is male.

What are the characteristics of Mysql joint table query

2. At this time, we Just sum up a and b separately to find out the total number of male students and male teachers

	select sum(t.a) as 学生男生总数,sum(t.b) as 男教师总数 from
	( SELECT count(id) as a,0 as b FROM student WHERE student.sex = &#39;男&#39;
			union all
	SELECT 0 as a,count(id) as b FROM teacher WHERE teacher.sex = &#39;男&#39; ) t

Copy after login

What are the characteristics of Mysql joint table query

The above is the detailed content of What are the characteristics of Mysql joint table query. For more information, please follow other related articles on the PHP Chinese website!