一、表架构
- student(sid,sname,sage,ssex) 学生表
- course(cid,cname,tid) 课程表
- sc(sid,cid,score) 成绩表
- teacher(tid,tname) 教师表
二 、建表sql语句
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cid` int(11) NOT NULL COMMENT '课程号',
`cname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '课程名',
`tid` int(11) NULL DEFAULT NULL COMMENT '老师号',
PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (3001, '英语', 1);
INSERT INTO `course` VALUES (3002, '数学', 2);
INSERT INTO `course` VALUES (3003, '物理', 3);
INSERT INTO `course` VALUES (3004, '语文', 4);
-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
`sid` int(11) NOT NULL COMMENT '学生号',
`cid` int(11) NOT NULL COMMENT '课程号',
`score` int(11) NULL DEFAULT NULL COMMENT '成绩'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES (101, 3001, 90);
INSERT INTO `sc` VALUES (102, 3001, 85);
INSERT INTO `sc` VALUES (103, 3001, 76);
INSERT INTO `sc` VALUES (104, 3002, 87);
INSERT INTO `sc` VALUES (105, 3001, 92);
INSERT INTO `sc` VALUES (101, 3002, 81);
INSERT INTO `sc` VALUES (102, 3002, 93);
INSERT INTO `sc` VALUES (103, 3002, 73);
INSERT INTO `sc` VALUES (104, 3002, 65);
INSERT INTO `sc` VALUES (105, 3002, 96);
INSERT INTO `sc` VALUES (101, 3003, 85);
INSERT INTO `sc` VALUES (102, 3003, 76);
INSERT INTO `sc` VALUES (103, 3003, 63);
INSERT INTO `sc` VALUES (104, 3003, 59);
INSERT INTO `sc` VALUES (105, 3003, 56);
INSERT INTO `sc` VALUES (101, 3004, 100);
INSERT INTO `sc` VALUES (102, 3004, 83);
INSERT INTO `sc` VALUES (103, 3004, 75);
INSERT INTO `sc` VALUES (104, 3004, 69);
INSERT INTO `sc` VALUES (105, 3004, 50);
INSERT INTO `sc` VALUES (106, 3001, 60);
INSERT INTO `sc` VALUES (106, 3001, 60);
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(11) NOT NULL COMMENT '学生号',
`sname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '姓名',
`sage` int(11) NULL DEFAULT NULL COMMENT '年龄',
`ssex` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '性别',
PRIMARY KEY (`sid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (101, '王明', 18, '女');
INSERT INTO `student` VALUES (102, '王天', 19, '男');
INSERT INTO `student` VALUES (103, '张三', 18, '男');
INSERT INTO `student` VALUES (104, '李四', 19, '女');
INSERT INTO `student` VALUES (105, '王五', 20, '男');
INSERT INTO `student` VALUES (107, '万源', 17, '男');
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` int(11) NOT NULL COMMENT '老师号',
`tname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '老师名称',
PRIMARY KEY (`tid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (1, '王老师');
INSERT INTO `teacher` VALUES (2, '李老师');
INSERT INTO `teacher` VALUES (3, '赵老师');
INSERT INTO `teacher` VALUES (4, '宋老师');
SET FOREIGN_KEY_CHECKS = 1;
三、查询
1、多表查询语法
select 字段1,字段2... from 表1,表2... [where 条件]
- 查询学生表和成绩表所有信息
select * from student,sc where student.sid = sc.sid
注意: 多表查询时,一定要找到两个表中相互关联的字段,并且作为条件使用
2、多表链接查询
多表连接查询语法(重点)
SELECT 字段列表
FROM 表1 INNER|LEFT|RIGHT JOIN 表2
ON 表1.字段 = 表2.字段;
①内连接查询 (只显示符合条件的数据)
- 查询学生表和成绩表所有信息
select * from student inner join sc on student.sid = sc.sid;
内连接查询与多表联合查询的效果是一样的.
②左外连接查询 (左边表中的数据优先全部显示)
- 查询学生表和成绩表所有信息
select * from student left join sc on student.sid = sc.sid;
学生表中的数据全部都显示,而成绩表中的数据符合条件的才会显示,不符合条件的会以 null 进行填充
③右外连接查询 (右边表中的数据优先全部显示)
- 查询学生表和成绩表所有信息
select * from student right join sc on student.sid = sc.sid;
正好与[左外连接相反]
④全连接查询(显示左右表中全部数据)
全连接查询：是在内连接的基础上增加 左右两边没有显示的数据
注意: mysql并不支持全连接 full JOIN 关键字
注意: 但是mysql 提供了 UNION 关键字.使用 UNION 可以间接实现 full JOIN 功能
select * from student left join sc on student.sid = sc.sid
UNION
select * from student right join sc on student.sid = sc.sid;
注意: UNION 和 UNION ALL 的区别:UNION 会去掉重复的数据,而 UNION ALL 则直接显示结果
3、三表查询
①查询所有人的英语成绩
语法：
select 表1.字段,表2.字段,表3.字段 from 表1 join 表2 on 表1.关联字段 = 表2.关联字段 join 表3 on 表2.关联字段 = 表3.关联字段 where....;
select student.sname,course.cname,sc.score from student join sc on student.sid = sc.sid join course on sc.cid = course.cid where course.cname = "英语";
4、其它查询
1、查询“3001”课程比“3003”课程成绩高的所有学生的学号与分数；
SELECT a.sid,a.score FROM (SELECT sid,score FROM sc WHERE cid="3001") a,
(SELECT sid,score FROM sc WHERE cid="3003") b
WHERE a.score>b.score AND a.sid=b.sid
2、查询平均成绩大于60分的同学的学号和平均成绩
SELECT sid,AVG(score)
FROM sc
GROUP BY sid HAVING AVG(score)>60
3、查询所有同学的学号、姓名、选课数、总成绩
select s.sid as 学号,s.sname as 姓名,count(sc.cid) as 选课数,SUM(sc.score) as 总成绩
from student s INNER JOIN sc sc
on s.sid=sc.sid
GROUP BY s.sid
4、查询姓“李”的老师的个数；
select count(distinct(Tname))
from teacher
where tname like '李%';
5、查询学过“王老师”课的同学的学号、姓名
SELECT s.sid AS 学号,s.sname AS 姓名
FROM student s, sc sc, course c, teacher t
WHERE s.sid = sc.sid AND sc.cid = c.cid AND c.tid = t.tid AND tname = "王老师"
6、查询没有学过“王老师”课的同学的学号、姓名
SELECT s.sid, s.sname
FROM student s
WHERE s.sid NOT IN (
SELECT s.sid
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="王老师")