MYSQL数据库系统第5次实验 多表连接

基于之前实验数据库teaching中的6张数据表的数据，完成以下操作：

1.连接结果为笛卡尔积
1）更新sc表的studentno取值，‘19120000111’修改为‘19123567897’；

mysql> use teaching;

mysql> update sc
    -> set studentno='19123567897'
    -> where studentno='19120000111';
    
Query OK, 1 row affected (0.87 sec)
Rows matched: 1  Changed: 1  Warnings: 0

2）查询student表和sc表的笛卡尔积，结果显示student表和sc表的所有信息，并将sc表中的studentno设置别名为sno；

mysql> select t1.*,
    -> t2.sc_no,
    -> t2.studentno as sno,
    -> t2.courseno,
    -> t2.teacherno,
    -> t2.sc_time
    -> from student t1
    -> cross join sc t2;

2.连接多表，获取共同存在的记录
1）查询有选课的学生对应的学生学号、姓名、性别和课程编号、教师编号、选课时间；

mysql> select t1.studentno,
    -> t1.sname,
    -> t1.sex,
    -> t2.courseno,
    -> t2.teacherno,
    -> t2.sc_time
    -> from student t1
    -> join sc t2
    -> on t1.studentno=t2.studentno;
+-------------+--------+------+----------+-----------+---------------------+
| studentno   | sname  | sex  | courseno | teacherno | sc_time             |
+-------------+--------+------+----------+-----------+---------------------+
| 19123567897 | 赵既白 | 女   | co1236   | t01237    | 2017-09-01 18:40:23 |
+-------------+--------+------+----------+-----------+---------------------+
1 row in set (0.00 sec)

3. 连接多表，“左表为尊”，左表记录全部保留，右表进行匹配
   1）查询所有学生对应的学生学号、姓名、性别和课程编号、教师编号、选课时间；

mysql> select t1.studentno,
    -> t1.sname,
    -> t1.sex,
    -> t2.courseno,
    -> t2.teacherno,
    -> t2.sc_time
    -> from student t1
    -> left join sc t2
    -> on t1.studentno=t2.studentno;

2）查询没有选修课的学生对应的学生学号、姓名、性别和入学成绩；

mysql> select t1.studentno,
    -> t1.sname,
    -> t1.sex,
    -> t1.entrance
    -> from student t1
    -> left join sc t2
    -> on t1.studentno=t2.studentno
    -> where t2.studentno is null;

4. 连接多表，“右表为尊”，右表记录全部保留，左表进行匹配
1）查询所有选课的学生对应的学生学号、姓名、性别和课程编号、教师编号、选课时间；

mysql>  select t2.studentno,
    -> t1.sname,
    -> t1.sex,
    ->  t2.courseno,
    -> t2.teacherno,
    -> t2.sc_time
    -> from student t1
    -> right join sc t2
    -> on t1.studentno=t2.studentno;

2）查询所有选课的学生中，没有入学成绩的学生对应的学生学号、课程编号、教师编号、选课时间；

mysql> select t2.studentno,
    -> t2.courseno,
    -> t2.teacherno,
    -> t2.sc_time
    -> from student t1
    -> right join sc t2
    -> on t1.studentno=t2.studentno
    -> where t1.entrance is null;

5. 连接多个表
1）查询“管理学院”的老师的教师工号、姓名、职称，授课对应的课程号、课程名称、课程类别；

mysql> select teacher.teacherno,teacher.tname,teacher.prof,
-> teach_course.
-> courseno,course.cname,course.type
    -> from teacher join teach_course on teacher.teacherno=teach_course.teacherno
    -> join course on teach_course.courseno=course.courseno
    -> where substring(teacher.department,1,4)='管理学院';
+-----------+--------+--------+----------+----------+------+
| teacherno | tname  | prof   | courseno | cname    | type |
+-----------+--------+--------+----------+----------+------+
| t05002    | 常杉   | 助教   | c05127   | 数据结构 | 必修 |
| t07019    | 韩既乐 | 讲师   | c08123   | 金融学   | 必修 |
| t08017    | 时观   | 副教授 | c08106   | 经济法   | 必修 |
+-----------+--------+--------+----------+----------+------+

法2：

6. 合并多个结果集
1）复制表score，创建表score01；

mysql> create table score01 as
    -> select *
    -> from score;
    
Query OK, 28 rows affected (1.31 sec)
Records: 28  Duplicates: 0  Warnings: 0

2）查询表score01中“18”级学生的全部成绩信息，表score中“19”级学生的全部成绩信息，然后将两个结果进行合并；

mysql> select *
    -> from score01
    -> where substring(studentno,1,2)='18'
    -> union
    -> select *
    -> from score
    -> where substring(studentno,1,2)='19';