多表查询

创建学生表和老师表

-- 创建学生表
CREATE TABLE students1107(
    id int not null auto_increment primary key,
    s_no varchar(50) not null,    -- 学号
    s_name varchar(50) not null   -- 学生姓名
)CHARACTER SET utf8;

-- 创建老师表
CREATE Table teacher1107(
    id int not null auto_increment primary key,
    t_no VARCHAR(50) not null,   -- 老师id
    t_name VARCHAR(50) not null  -- 老师姓名
) CHARACTER SET utf8;

-- 创建课程表
CREATE table course1107 (
    id int not null auto_increment primary key,
    c_name varchar(50) not null,   -- 课程名称
    t_no VARCHAR(50) not null,     -- 老师id
    s_no VARCHAR(50) not null,     -- 学生id
    classdate date not null        -- 上课日期
)CHARACTER set utf8;

添加对应的数据

 

-- 学生表中添加数据
INSERT INTO `students1107`(`id`, `s_no`, `s_name`) VALUES (1, '460001', '张三');
INSERT INTO `students1107`(`id`, `s_no`, `s_name`) VALUES (2, '460002', '李四');
INSERT INTO `students1107`(`id`, `s_no`, `s_name`) VALUES (3, '460003', '王五');
INSERT INTO `students1107`(`id`, `s_no`, `s_name`) VALUES (4, '460004', '赵六');

-- 老师表中添加数据
INSERT INTO `teacher1107`(`id`, `t_no`, `t_name`) VALUES (1, 't001', '猫叔');
INSERT INTO `teacher1107`(`id`, `t_no`, `t_name`) VALUES (2, 't002', '铭泽');


-- 上课表中添加数据；
INSERT INTO `course1107`(`id`, `c_name`, `t_no`, `s_no`, `classdate`) VALUES (1, 'Mysql基础', 't001', '460001', '2020-11-07');
INSERT INTO `course1107`(`id`, `c_name`, `t_no`, `s_no`, `classdate`) VALUES (2, 'Mysql基础', 't001', '460002', '2020-11-07');
INSERT INTO `course1107`(`id`, `c_name`, `t_no`, `s_no`, `classdate`) VALUES (3, 'Mysql基础', 't001', '460003', '2020-11-07');
INSERT INTO `course1107`(`id`, `c_name`, `t_no`, `s_no`, `classdate`) VALUES (4, 'Mysql考试', 't002', '460001', '2020-11-06');
INSERT INTO `course1107`(`id`, `c_name`, `t_no`, `s_no`, `classdate`) VALUES (5, 'Mysql考试', 't002', '460002', '2020-11-06');
INSERT INTO `course1107`(`id`, `c_name`, `t_no`, `s_no`, `classdate`) VALUES (6, 'Mysql考试', 't002', '460004', '2020-11-06');

1. 查询2020-11-07 上课同学的姓名

根据之前学的内容，使用单表查询的方式 可以用2条sql语句完成。

-- 查询学号
SELECT s_no from course1107
where classdate = "2020-11-07"
-- 根据学号查询人员姓名
SELECT s_no,s_name from students1107
where s_no in( "460001","460002","460003")

根据以上的业务逻辑，可以使用之前用过的子查询进行处理；

-- 使用子查询的方式

SELECT s_no,s_name from students1107
WHERE s_no in (
        SELECT s_no from course1107
        where classdate = "2020-11-07"
);

-- 多表查询
SELECT students1107.s_no, students1107.s_name, course1107.classdate from students1107,course1107
where course1107.classdate = "2020-11-07"
AND course1107.s_no = students1107.s_no

-- 查询2020-11-07 上课的课程名称，学生姓名，老师姓名。
SELECT course1107.c_name,students1107.s_name,teacher1107.t_name from students1107,teacher1107,course1107
WHERE course1107.classdate = "2020-11-07"
AND students1107.s_no = course1107.s_no
AND teacher1107.t_no = course1107.t_no;

-- 查询出学员的学号，姓名，以及上课次数，根据上课次数的多少降序排序。

SELECT s.s_no,s.s_name,COUNT(c.s_no) as 上课次数 from students1107 as s, course1107 as c
WHERE s.s_no = c.s_no
GROUP BY c.s_no,s.s_name
ORDER BY 上课次数 DESC

结果作为条件来进行查询

SELECT students1107.s_no,students1107.s_name,(SELECT count(s_no) from course1107 WHERE students1107.s_no = course1107.s_no) as 上课次数
from students1107
ORDER BY 上课次数