mysql高级查询

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’
);