mysql 连接查询

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50723
 Source Host           : localhost:3306
 Source Schema         : join-test

 Target Server Type    : MySQL
 Target Server Version : 50723
 File Encoding         : 65001

 Date: 08/02/2020 17:31:58
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `CID` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `Cname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `TID` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('01', '语文', '02');
INSERT INTO `course` VALUES ('02', '数学', '01');
INSERT INTO `course` VALUES ('03', '英语', '03');

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc`  (
  `SID` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `CID` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `score` decimal(18, 1) DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('01', '01', 80.0);
INSERT INTO `sc` VALUES ('01', '02', 90.0);
INSERT INTO `sc` VALUES ('01', '03', 99.0);
INSERT INTO `sc` VALUES ('02', '01', 70.0);
INSERT INTO `sc` VALUES ('02', '02', 60.0);
INSERT INTO `sc` VALUES ('02', '03', 80.0);
INSERT INTO `sc` VALUES ('03', '01', 80.0);
INSERT INTO `sc` VALUES ('03', '02', 80.0);
INSERT INTO `sc` VALUES ('03', '03', 80.0);
INSERT INTO `sc` VALUES ('04', '01', 50.0);
INSERT INTO `sc` VALUES ('04', '02', 30.0);
INSERT INTO `sc` VALUES ('04', '03', 20.0);
INSERT INTO `sc` VALUES ('05', '01', 76.0);
INSERT INTO `sc` VALUES ('05', '02', 87.0);
INSERT INTO `sc` VALUES ('06', '01', 31.0);
INSERT INTO `sc` VALUES ('06', '03', 34.0);
INSERT INTO `sc` VALUES ('07', '02', 89.0);
INSERT INTO `sc` VALUES ('07', '03', 98.0);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `SID` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `Sname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `Sage` datetime(0) DEFAULT NULL,
  `Ssex` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('01', '赵雷', '1990-01-01 00:00:00', '男');
INSERT INTO `student` VALUES ('02', '钱电', '1990-12-21 00:00:00', '男');
INSERT INTO `student` VALUES ('03', '孙风', '1990-05-20 00:00:00', '男');
INSERT INTO `student` VALUES ('04', '李云', '1990-08-06 00:00:00', '男');
INSERT INTO `student` VALUES ('05', '周梅', '1991-12-01 00:00:00', '女');
INSERT INTO `student` VALUES ('06', '吴兰', '1992-03-01 00:00:00', '女');
INSERT INTO `student` VALUES ('07', '郑竹', '1989-07-01 00:00:00', '女');
INSERT INTO `student` VALUES ('08', '王菊', '1990-01-20 00:00:00', '女');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `TID` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `Tname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_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;

SELECT * FROM student;
SELECT * FROM course;
SELECT * FROM sc;
SELECT * FROM teacher;

– mysql连接分为内连接、外连接（左、右）、全连接查询
– 内连接(inner join on)： 取的是交集
– 外连接(left(right) join on)： 根据是左还是右来决定，左的话会把左边的数据都取出来，右边没有的显示null,右连接亦然。
– 全连接：

– 1. 查询“001”课程比“002”课程成绩高的所有学生的学号与分数；
– FROM A,B 实际上考察的A数量*B数量

SELECT * FROM
(SELECT * FROM sc WHERE CID = 01) a,
(SELECT * FROM sc WHERE CID = 02) b
WHERE a.SID = b.SID AND a.score > b.score

– 2.查询“01”课程的所有学生的学号与分数

SELECT * FROM sc WHERE CID = 01;

– 3. 查询平均成绩大于60分的同学的学号和平均成绩
– GROUP BY: 可以根据一个或多个列对结果集进行分组

SELECT SID,AVG(score) as ags FROM sc GROUP BY SID HAVING ags>60;

– 4. 查询所有同学的学号、姓名、选课数、总成绩

SELECT SID,SUM(score) as ags FROM sc GROUP BY SID;
SELECT SID,count(CID) as ags FROM sc GROUP BY SID;
-- 方法1
SELECT s.SID,s.Sname,a.ags,b.countCID FROM student s,
(SELECT SID,SUM(score) as ags FROM sc GROUP BY SID) a,
(SELECT SID,count(CID) as countCID FROM sc GROUP BY SID) b
WHERE a.SID = b.SID AND s.SID = a.SID;
-- 方法2
SELECT
	s.SID,
	s.Sname,c.score
FROM
	student s,
	sc c 
WHERE
	s.SID = c.SID 
GROUP BY
	SID,
	Sname

– 5. 查询姓“李”的老师的个数；

		SELECT count(DISTINCT(Tname)) FROM teacher t WHERE t.Tname LIKE '李%';

– 6. 查询学过“张三”老师课的同学的学号、姓名

		SELECT * FROM teacher t WHERE Tname = '张三';
		SELECT * FROM course c WHERE c.TID = 01;
		SELECT * FROM sc s WHERE s.CID = 02; 

		-- 方法1
		SELECT d.SID,d.Sname FROM student d 
		LEFT JOIN sc s on d.SID = s.SID
		LEFT JOIN course c on c.CID = s.CID
		LEFT JOIN teacher t on t.TID = c.TID
		WHERE t.Tname = '张三';

		-- 方法2
		SELECT s.sid AS "学号", s.sname AS "姓名"
		FROM student s, sc sc, course c, teacher t
		WHERE s.sid=sc.sid AND c.cid=sc.cid AND t.tid=c.tid AND t.tname="张三"

– 7. 查询没有学过“张三”老师课的同学的学号、姓名

	SELECT * FROM student l WHERE l.SID NOT IN (SELECT d.SID FROM student d 
		LEFT JOIN sc s on d.SID = s.SID
		LEFT JOIN course c on c.CID = s.CID
		LEFT JOIN teacher t on t.TID = c.TID
		WHERE t.Tname = '张三')

– 8. 查询学过“01”并且也学过编号“02”课程的同学的学号、姓名

SELECT * from course WHERE CID IN;
SELECT * FROM sc;

-- 第一种办法
SELECT * FROM student t WHERE 
t.SID in (SELECT DISTINCT(SID) FROM sc s WHERE s.CID in(01) )
and 
t.SID in (SELECT DISTINCT(SID) FROM sc s WHERE s.CID in(02) );

– 9. 查询学过“叶平”老师所教的所有课的同学的学号、姓名；