DAY07和day08

-- 查找比兰朗朗年龄小的学生信息
-- 1.兰朗朗的出生日期
SELECT birthday FROM student WHERE student_name='兰浪浪';
-- 2.根据日期查询剩余学生信息
SELECT * FROM student where birthday>'2003-11-24';
-- 子查询 比较运算符时，查询结果不能大于1
-- 查询的字段只能有一个
SELECT * FROM student where birthday>(
SELECT birthday FROM student WHERE student_name='兰浪浪'
);
SELECT * FROM student where grade_id=(
SELECT grade_id FROM student where student_name='咳咳咳'
)AND student_name not='咳咳咳'
-- 获取oop的编号
SELECT subject_id FROM subjects subject_name='css'
-- 根据结果进行查询
SELECT MAX(examdate)  FROM result;
SELECT MIN(examdate)  FROM result;

SELECT MAX(student_result),MIN(student_result) FROM result 
WHERE examDate=(SELECT MAX(examdate)  FROM result) 
AND subject_id=
(SELECT subject_id FROM subjects WHERE subject_name='css')
subject_id=('id')
--  查询‘张三的’css考试成绩
SELECT * FROM result WHERE student_result=(
SELECT student_name FROM student WHERE student_name='张三'
) AND student_result=(SELECT class_hour FROM subjects WHERE class_hour=student_id)

-- 联合查询（多表联查）
SELECT * FROM student s,grade g WHERE s.grade_id=g.grade_id;
SELECT s.student_no,s.student_name,g.grade_name FROM student s,grade g WHERE s.grade_id=g.grade_id;

SELECT * FROM student s,subjects sj,result r
WHERE s.stuent_

内连接：等值查询

-- 查询和学生信息及所在班级信息
SELECT * FROM student s,grade g WHERE s.grade_id=g.grade_id

SELECT * FROM student s INNER JOIN grade g
ON s.grade_id=g.grade_id;

SELECT * FROM student s,grade g INNER JOIN subjects sj
ON s.student_name=g.grade_id AND s.student_name=sj.subject_id
WHERE g.grade_name='全栈一班'

-- 查询所有学生的考试成绩
SELECT * FROM student s LEFT JOIN result r ON
s.student_no=r.student_no

SELECT * FROM result r RIGHT JOIN student s ON
s.student_no=r.student_no

SELECT sj.* FROM subjects sj LEFT JOIN result r ON
NOT sj.subject_id=r.result_id WHERE r.subject_id IS NULL

SELECT * FROM student s LEFT JOIN grade r ON
s.student_name=r.grade_name

-- 查找比张三年龄大的学生
SELECT * FROM student s1,student s2
WHERE s1.student_name='咳咳咳' AND s1.birthday>s2.birthday

SELECT * FROM student s1,student s2
WHERE s1.student_name='咳咳咳' AND s1.birthday=s2.birthday

SELECT * FROM grade s1,grade s2
WHERE s1.grade_name='java'
-- 查看学生姓名和考试成绩
SELECT s.student_name,r.student_result from student s INNER JOIN result r
on s.student_no=r.student_no

SELECT s.student_name,r.student_result from student s 
NATURAL JOIN result r