高级查询
IN子查询
1 向科目表添加三条信息,用一条insert语句
INSERT INTO SUBJECT(subjectname,classHour,GradeId)
VALUES('Logic Java',220,1),('HTML',160,1),('Java OOP',230,1);
SELECT * FROM student;
DESC grade;
#2 将学生表中学号为2的学生的邮箱修改为zhangsan2@163.com,密码改为000
UPDATE student SET email = 'zhangsan2@163.com',loginPWD = '000'
WHERE studentNo = 2;
#3 将科目表中课时数大于200且年级编号为1的科目的课时减少10(修改 update)
UPDATE SUBJECT SET classHour = classHour - 10
WHERE classHour > 200 AND GradeId = 1
#4 将所有年级编号为1的学员姓名、性别、出生日期、手机号码信息保存到新表student_grade1中
第一种方法 student_grade1表格事先创建好 insert into 新表(字段…) select 字段 from 原表
INSERT INTO student_grade1(StuName,Sex,Birthday,PhoneNo)
SELECT studentName,Sex,bornDate,phone FROM student;
第二种方法 student_grade1临时创建 create table 新表(select 字段 from 原表)
DESC student;
SELECT * FROM student_grade1
SELECT * FROM result
SELECT * FROM SUBJECT
INSERT INTO result(studentNo,subjectNo,examDate,studentResult) VALUES(2,1,'2021-03-01',60),(3,1,'2021-03-01',60)
查看年龄比“李四”大的学生,要求显示这些学生的信息 包括学生姓名、性别、出生日期,手机号
首先 获取李四的出生日期
SELECT bornDate FROM student WHERE studentName = '李四';
实现
SELECT studentName AS 姓名,sex AS 性别,bornDate AS 出生日期,phone AS 手机号
FROM student
WHERE bornDate < (SELECT bornDate FROM student WHERE studentName = '李四')
第二种方法使用 DateDiff(date1,date2)
WHERE DATEDIFF(bornDate,(SELECT bornDate FROM student WHERE studentName = '李四'))<0
案例测试 SELECT datediff(‘2001-03-31’,‘2011-03-31’)
– 查询“Logic Java”课程至少一次考试刚好等于60分的学生
– 第一种方法 表连接
SELECT studentName FROM student AS stu
INNER JOIN result AS r ON r.studentNo = stu.studentNo
INNER JOIN SUBJECT AS s ON s.subjectNo = r.subjectNO
WHERE s.subjectName = 'Logic Java' AND r.studentResult
– 二种方法 子查询 注意子查询的时候条件限定 最好用in替换 =
SELECT studentName FROM student AS stu WHERE stu.studentNo IN (
SELECT r.studentNo FROM result AS r
INNER JOIN SUBJECT AS s ON s.subjectNo = r.subjectNo
WHERE r.studentResult = 60 AND s.subjectName = 'Logic Java'
);