第五天 继续SQL多表查询

SQL多表查询

#给表名别名

SELECT st.class_num,cl.class_name FROM student st,class cl

#连接查询

查询出每个学生的班级名称

#以左边的表为主表（用的最多）

SELECT st.class_num,cl.class_name FROM student st,class cl

 #以右边的表为主表

SELECT student.name,student.class_num,class.class_name FROM student RIGHT JOIN class on student.class_num=class.class_num

#以谁为主表 就主要查询谁

嵌套查询（损耗计算机性能）

-->二维表-->二维表 #先查询出一个表可以再从这个表中继续查询

#先查询里面的在查询外面的

SELECT t1.name FROM

(SELECT student.name,student.class_num,class.class_name FROM student LEFT JOIN class on student.class_num=class.class_num) t1 WHERE class_num='20201001'

#找到所有选择课程号为20201001的同学的名字

SELECT t2.name FROM

(SELECT student.name,relationship.cno FROM student LEFT JOIN relationship on student.sno = relationship.sno) t2 WHERE t2.cno = '1001'

#找到所有选择课程号 数学 的同学名字

SELECT t4.name FROM

(SELECT t3.name,scoure.gradeName FROM

(SELECT student.name,relationship.cno FROM student LEFT JOIN relationship on student.sno = relationship.sno) t3 LEFT JOIN scoure on t3.cno = scoure.cno) t4 WHERE t4.gradeName = '数学'

#查询出每个同学的班级名称

SELECT student.name,class.class_name FROM student INNER JOIN class on student.class_num=class.class_num

#查询年龄最高的同学信息

SELECT * from student ORDER BY age DESC LIMIT 1 OFFSET 0

#查询出学生中哪个年龄段学生最多统计次数

SELECT age,COUNT(age) FROM student GROUP BY age LIMIT 1 OFFSET 0

#查询出年龄超过21并且选择语文的同学名字

SELECT a1.name,a1.age,scoure.gradeName FROM

(SELECT student.name,student.age,relationship.cno FROM student INNER JOIN relationship on student.sno = relationship.sno) a1 inner JOIN scoure on a1.cno=scoure.cno

#查询软工班年龄最大的同学选择的课程名称和该同学姓名

SELECT a4.name,scoure.gradeName FROM

(SELECT a3.name,relationship.cno FROM

(SELECT a2.name,a2.sno FROM

(SELECT a1.sno,a1.age,a1.name from

(SELECT student.sno,student.name,student.age,class.class_name FROM student INNER join class on student.class_num = class.class_num) a1 WHERE a1.class_name='软件工程') a2 ORDER BY age DESC LIMIT 1 OFFSET 0) a3 INNER JOIN relationship on a3.sno = relationship.sno) a4 INNER join scoure on a4.cno =scoure.cno

#数据修改

#增加 删除 修改

#增加

INSERT 如果id列自动递增不用插入

#INSERT into 表名(列1，列2，列3，列4，列5) VALUES (值1，值2，值3，值4，值5)

INSERT into student(name,age,sex) VALUES('宋宇',18,'男')

#插入多条数据

#INSERT into 表名(列1，列2，列3，列4，列5) VALUES (值1，值2，值3，值4，值5)，(值1，值2，值3，值4，值5)，(值1，值2，值3，值4，值5)

INSERT into student(name,age,sex) VALUES('宋宇',18,'男'),('宋1宇',18,'男'),('宋2宇',18,'男')

#插入的另一种形式

#INSERT INTO 表名 SET=值,列=值,列=值,列=值,

INSERT INTO student SET name ='ssss',age=11

#数据的修改

#指定数据修改的行

#UPDATE 表名 set 列=值,列=值 WHERE

UPDATE student set name='123' WHERE id='1'

#数据的删除

#DELETE FROM 表名 WHERE 列=值 指定列删除

DELETE FROM student WHERE id='1'

#DELETE FROM 表名 一行行的删除整张表（删除整张表速度慢）

DELETE FROM

#TRUNCATE TABLE 表名; 清空表

TRUNCATE table

小练习

1.查询出每个同学的班级名称:提示，不是所有同学都有班级名称

SELECT student.name,class_name from student INNER JOIN clss on student.class_num = class.class_num

2.查询出年龄最高的同学信息:提示:limit

SELECT * from student ORDER BY age DESC LIMIT 1 OFFSET 0

3.查询出在学生当中那个年龄段的学生最多，并统计其出现次数

SELECT age,count(age) num from student GROUP BY age LIMIT 1 OFFSET 0

4.查询出年龄超过21岁，并且选择语文的同学名字

SELECT t2.name from
(SELECT t1.name,t1.age,scoure.gradeName from
（SELECT student.name,student,age relationship.cno FROM student INNER JOIN relationship on student.sno = relationship.sno） t1 inner join scoure on t1.cno = scoure.cno) t2 where t2.gradeName='语文' and age>21

5.查询软件工程班年龄最大的同学 选择的课程名称和该同学姓名

SELECT a4.name,scoure.gradeName FROM
(SELECT a3.name,relationship.cno FROM
(SELECT a2.name,a2.sno FROM
(SELECT a1.sno,a1.age,a1.name from
(SELECT student.sno,student.name,student.age,class.class_name FROM student INNER join class on student.class_num = class.class_num) a1 WHERE a1.class_name='软件工程') a2 ORDER BY age DESC LIMIT 1 OFFSET 0) a3 INNER JOIN relationship on a3.sno = relationship.sno) a4 INNER join scoure on a4.cno =scoure.cno