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