java—Web—MySql(高级查询)

需求：
代码：

USE test;

CREATE TABLE person(
number INT(4) AUTO_INCREMENT COMMENT'序号' PRIMARY KEY,
NAME VARCHAR(50) NOT NULL COMMENT'姓名',
sex CHAR(2) COMMENT'性别',
bornDate DATETIME COMMENT'出生日期'
)CHARSET='utf8'

#将表名修改为tb_person
ALTER TABLE person RENAME TO  tb_person;
#删除出生日期字段
ALTER TABLE tb_person DROP bornDate;
#添加出生日期字段，数据类型为DATE类型
ALTER TABLE tb_person ADD bornDate DATE ;
#修改序号字段名（number）为id，类型为BIGINT类型
ALTER TABLE tb_person CHANGE number id INT(4) AUTO_INCREMENT COMMENT'序号';

实现效果：
需求：
代码：

CREATE DATABASE demo;
USE demo;
CREATE TABLE SUBJECT(
accountNum INT(4) COMMENT'科目编号',
sccountName VARCHAR(30) COMMENT'科目名',
classHour INT(4) COMMENT'课时数',
termNum INT(4) COMMENT'学期编号'
)CHARSET='utf8'
#添加数据
INSERT INTO SUBJECT(accountNum,sccountName,classHour,termNum) 
VALUES(1,'Logic Java',220,1),(2,'HTML',160,1),(3,'Java OOP',230,2);

实现效果：
需求：

代码：

USE myschool;

UPDATE student SET email='syu2000@163.com',loginPwd=000 WHERE studentNo=3;

UPDATE SUBJECT SET classHour=classHour-10 WHERE gradeID=1 AND classHour>200;

CREATE TABLE student_gradel(SELECT studentName, sex,bornDate,phone FROM student WHERE gradeID=1);

实现效果：

需求：
实现代码：

SELECT studentNo,studentResult FROM result WHERE examDate='2020-10-13'
ORDER BY studentResult DESC LIMIT 5

SELECT studentName,FLOOR(DATEDIFF(NOW(),bornDate)/365) FROM student 
WHERE sex='女'
ORDER BY bornDate DESC LIMIT 1,7; 

SELECT COUNT(*),borndate FROM student GROUP BY YEAR(BORNDATE)
HAVING COUNT(*)>=2 

SELECT MAX(studentresult),MIN(studentresult),MAX(studentresult)
FROM result WHERE examDate='2016-02-17'; 

需求：
实现代码：

FROM result
WHERE subjectNo=
(SELECT subjectNo FROM SUBJECT 
 WHERE subjectName=Logic Java ) AND
examDate=(SELECT MAX(`examDate`) FROM result 
WHERE subjectNo=(SELECT subjectNo FROM SUBJECT 
WHERE subjectName='Logic Java'));

需求：

实现代码：

SELECT subjectName FROM SUBJECT WHERE gradeId IN (
SELECT gradeId FROM grade WHERE gradeName='S1')

实现效果：
需求：
实现代码：

SELECT studentName FROM `student` 
WHERE studentNo NOT IN (
      SELECT studentNo FROM `result` 
      WHERE subjectNo = (
          SELECT subjectNo FROM `subject` 
          WHERE subjectName='HTML'
       )
      AND examDate = (
          SELECT MAX(examDate) FROM result 
          WHERE subjectNo =(
              SELECT subjectNo FROM `subject` 
              WHERE subjectName='HTML' ) 
       ))