快捷導(dǎo)航

Mysql深入了解聯(lián)表查詢的特點

更新時間：2022年07月27日 10:53:29 作者：·~簡單就好

這篇文章主要給大家介紹了關(guān)于MySQL聯(lián)表查詢的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家學(xué)習(xí)或者使用MySQL具有一定的參考學(xué)習(xí)價值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧

前言

為了減少對數(shù)據(jù)庫的查詢次數(shù)，例如在互不關(guān)聯(lián)的表中為了減輕系統(tǒng)的壓力，我們可以通過union all關(guān)鍵詞將多個表查到的數(shù)據(jù)做一個聯(lián)查處理

（便于統(tǒng)計分析時使用到不同的數(shù)據(jù)而只用一次請求）

舉例：通過一條sql語句一次查詢查詢學(xué)生表中的性別為男的學(xué)生總數(shù)和教師表中的教師性別為男的教師總數(shù)

數(shù)據(jù)庫表準(zhǔn)備：

1、student表

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 '',
  `birth` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
  `sex` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
  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 ('01', '趙雷', '1990-01-01', '男');
INSERT INTO `student` VALUES ('02', '錢電', '1990-12-21', '男');
INSERT INTO `student` VALUES ('03', '孫風(fēng)', '1990-05-20', '男');
INSERT INTO `student` VALUES ('04', '李云', '1990-08-06', '男');
INSERT INTO `student` VALUES ('05', '周梅', '1991-12-01', '女');
INSERT INTO `student` VALUES ('06', '吳蘭', '1992-03-01', '女');
INSERT INTO `student` VALUES ('07', '鄭竹', '1989-07-01', '女');
INSERT INTO `student` VALUES ('08', '王菊', '1990-01-20', '女');
SET FOREIGN_KEY_CHECKS = 1;

2、teacher表

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 '',
  `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 ('01', '張三', '男');
INSERT INTO `teacher` VALUES ('02', '李四', '女');
INSERT INTO `teacher` VALUES ('03', '王五', '男');
SET FOREIGN_KEY_CHECKS = 1;

一、傳統(tǒng)方法（查詢性能不佳）

對所查詢的數(shù)據(jù)封裝成一個表，在分別對表的數(shù)據(jù)查詢展示出來。

這種方法比較簡單但是會對數(shù)據(jù)庫的查詢次數(shù)大大提高

SELECT
	t1.學(xué)生男生總數(shù),
	t2.男教師總數(shù) 
FROM
	( SELECT count( id ) AS 學(xué)生男生總數(shù) FROM student WHERE student.sex = '男' ) t1,
	( SELECT count( id ) AS 男教師總數(shù) FROM teacher WHERE teacher.sex = '男' ) t2

二、使用union all將多個表聯(lián)合成一個表查詢

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

1、此時a代表學(xué)生性別為男生的總?cè)藬?shù)，b代表教師性別為男的總?cè)藬?shù)

2、此時我們只需對a和b分別求和，就能夠查詢出男學(xué)生和男教師的總?cè)藬?shù)

	select sum(t.a) as 學(xué)生男生總數(shù),sum(t.b) as 男教師總數(shù) from
	( SELECT count(id) as a,0 as b FROM student WHERE student.sex = '男'
			union all
	SELECT 0 as a,count(id) as b FROM teacher WHERE teacher.sex = '男' ) t