- 掌握如何为列名、表名设置别名
- 了解什么是子查询以及子查询的使用场景
- 掌握WHERE子查询的使用
- 掌握SELECT子查询的使用
1、为输出列指定别名
语法：SELECT age [AS] 'my_age' FROM table_name
作用：改变输出的名称（不会改变其输出的值）
SELECT class_name '班级', COUNT(1) AS '总人数' FROM school_student_info GROUP BY class_name;
2、为表指定别名
语法：SELECT t.age FROM table_name [AS] t
作用：方便后面的表关联查询
-- 指定列的别名
SELECT class_name '班级', COUNT(1) AS '总人数' FROM school_student_info GROUP BY class_name;
-- 指定表的别名
SELECT school_student_info .stu_no, school_student_info .stu_name FROM school_student_info;
SELECT t.* FROM school_student_info t;
SELECT t.stu_no, t.stu_name FROM school_student_info t;
3、什么是子查询？
子查询是指一个查询语句嵌套在另一个查询语句内部的查询
使用场景：
- SELECT 子查询
- FROM 子查询
- WHERE 子查询
SELECT 子查询
查询每个学生的学号、姓名、语文成绩
SELECT t1.stu_no, t1.stu_name, (SELECT score t2 FROM school_student_grade t2
WHERE t2.course_id=1 AND t2.student_id=t1.id) AS 语文 FROM school_student_info t1;
________________________________________
FROM 子查询
SELECT t.* FROM (
SELECT t1.stu_no, t1.stu_name, t1.age * 100 AS age
FROM school_student_info t1
) t WHERE t.age <= 1000;
________________________________________
WHERE 子查询
-- 查询本班语文成绩大于平均成绩的学生ID及分数
-- 方式1、使用EXISTS关键字
SELECT t1.* FROM school_student_grade t1 WHERE t1.score > (SELECT AVG(t2.score) FROM school_student_grade t2 WHERE t2.course_id = 1) AND t1.course_id = 1;
SELECT * FROM school_student_grade WHERE course_id = 1;
SELECT AVG(score) FROM school_student_grade WHERE course_id = 1;
SELECT t1.student_id, t1.score FROM school_student_grade t1 WHERE t1.course_id = 1 AND t1.score > (SELECT AVG(t2.score) FROM school_student_grade t2 WHERE t2.course_id = 1)
-- 方式2、使用IN关键字
SELECT t1.* FROM school_student_info t1 WHERE t1.id NOT IN (
SELECT t2.student_id FROM school_student_grade t2
WHERE t2.course_id = 1
);
