MySQL进阶（七）多表查询和子查询及视图

MySQL进阶（七）多表查询和子查询及视图表“students”中存放了学生基本信息和所选课的代号，表“courses”中存放了课代号及课程名，表tutors保存了老师的序号、名字（Tname）、性别和年龄信息。
students如下：
courses表如下：tutors表如下：SELECT CID2 FROM students WHERE CID2 IS NOT NULL; SELECT DISTINCT CID2 FROM students WHERE CID2 IS NOT NULL; #去重SELECT Cname FROM courses WHERE CID NOT IN (SELECT DISTINCT CID2 FROM students WHERE CID2 IS NOT NULL;) #挑选出courses表中没有被students中的CID2学习的课程的课程名称；SELECT Tname FROM tutors WHERE TID NOT IN (SELECT DISTINCT TID FROM courses); #挑选出没有教授任何课程的老师SELECT CID1 FROM students GROUP BY CID1 HAVING COUNT(CID1) >= 2;SELECT Cname FROM courses WHERE CID IN (SELECT CID1 FROM students GROUP BY CID1 HAVING COUNT(CID1) >= 2;) #找出students表中CID1有两个或两个以上同学学习了的同一个门课程的课程名称；SELECT t.Tname,c.Cname FROM tutors AS t LEFT JOIN courses AS c ON t.TID=c.TID; #显示每一位老师及其所教授的课程；没有教授的课程的保持为NULL；SELECT t.Tname,c.Cname FROM tutors AS t RIGHT JOIN courses AS c ON t.TID=c.TID; #显示每一个课程及其相关的老师，没有老师教授的课程将其老师显示为空；SELECT Name,Cname,Tname FROM students,courses,tutors WHERE students.CID1=courses.CID AND courses.TID=tutors.TID; #显示每位同学CID1课程的课程名及其讲授了相关课程的老师的姓名；视图: 存储下来的SELECT语句；
 基于基表的查询结果；
 CREATE VIEW：创建视图
    DROP VIEW：删除视图
 物化视图：SELECT
HELP CREATE VIEW;
CREATE VIEW sct AS SELECT Name,Cname,Tname FROM students,courses,tutors WHERE students.CID1=courses.CID AND courses.TID=tutors.TID; SHOW TABLES;SELECT * FROM sct;查询结果和执行SELECT Name,Cname,Tname FROM students,courses,tutors WHERE students.CID1=courses.CID AND courses.TID=tutors.TID; 是一样的SHOW CREATE VIEW sctG #即可知道创建视图过程SHOW CREATE TABLE coursesG #即可知道创建表的过程q
mysql -e 'CREATE DATABASE edb;'
mysql -e 'SHOW DATABASES;'
mysql
SHOW DATABASES;
mysql -e 'SELECT * FROM jiaowu.students;'mysql -e "INSERT INTO jiaowu.students (Name,Age,Gender,CID1,CID2,TID) VALUES ('stu1',23,'F',4,1,6);"
欢迎各位指出不足共同交流进步