create database two20210610 default charset=utf8;

use two20210610;

CREATE TABLE `studentinfo` ( 
  `StudentID` char(10) DEFAULT NULL,
  `StudentName` varchar(20) DEFAULT NULL,
  `Gender` varchar(2) DEFAULT NULL,
  `Birthday` date DEFAULT NULL,
  `ClassID` int(4) DEFAULT NULL,
  `BeginYear` year(4) DEFAULT NULL,
  `Phone` varchar(11) DEFAULT NULL,
  `Province` varchar(20) DEFAULT NULL,
  `City` varchar(20) DEFAULT NULL,
  `Email` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table teacher (
    id int,
    teachername VARCHAR(10),
    hiredate DATE,
    gender char
);

create table classinfo (
    id int,   # 班級(jí)編號(hào)
    classname varchar(10),
    gradeid int,   # 年級(jí)編號(hào)
    beginyear varchar(10)  # 開始年份
);

create table subject (
    id int ,
    subjectname varchar(10),
    teacherid int 
); 

create table exam (
    id int,
    exam int,
    subjectid int,
    studentid int,
    remark varchar(10) # 備注
);

create table grade(
    id int ,
    gradename varchar(10),
    major varchar(20)
);

INSERT INTO `two20210610`.`classinfo`(`id`, `classname`, `gradeid`, `beginyear`) VALUES (2001, '20級(jí)1班', 20, '2020');
INSERT INTO `two20210610`.`classinfo`(`id`, `classname`, `gradeid`, `beginyear`) VALUES (2002, '20級(jí)2班', 20, '2020');

INSERT INTO `two20210610`.`exam`(`id`, `exam`, `subjectid`, `studentid`, `remark`) VALUES (1, 98, 101, 1, '無');
INSERT INTO `two20210610`.`exam`(`id`, `exam`, `subjectid`, `studentid`, `remark`) VALUES (2, 89, 102, 1, NULL);
INSERT INTO `two20210610`.`exam`(`id`, `exam`, `subjectid`, `studentid`, `remark`) VALUES (3, 79, 103, 1, '');
INSERT INTO `two20210610`.`exam`(`id`, `exam`, `subjectid`, `studentid`, `remark`) VALUES (4, 96, 104, 1, NULL);
INSERT INTO `two20210610`.`exam`(`id`, `exam`, `subjectid`, `studentid`, `remark`) VALUES (5, 85, 101, 2, NULL);
INSERT INTO `two20210610`.`exam`(`id`, `exam`, `subjectid`, `studentid`, `remark`) VALUES (6, 89, 102, 2, NULL);
INSERT INTO `two20210610`.`exam`(`id`, `exam`, `subjectid`, `studentid`, `remark`) VALUES (7, 79, 103, 2, NULL);
INSERT INTO `two20210610`.`exam`(`id`, `exam`, `subjectid`, `studentid`, `remark`) VALUES (8, 83, 104, 2, NULL);

INSERT INTO `two20210610`.`grade`(`id`, `gradename`, `major`) VALUES (20, '20級(jí)', '軟件技術(shù)');

INSERT INTO `two20210610`.`studentinfo`(`StudentID`, `StudentName`, `Gender`, `Birthday`, `ClassID`, `BeginYear`, `Phone`, `Province`, `City`, `Email`) VALUES ('1', '張無忌', '男', '2021-06-10', 2001, 2020, '13012340001', '河南', '許昌', 'zwj@qq.com');
INSERT INTO `two20210610`.`studentinfo`(`StudentID`, `StudentName`, `Gender`, `Birthday`, `ClassID`, `BeginYear`, `Phone`, `Province`, `City`, `Email`) VALUES ('2', '張鐵牛', '男', '2021-06-16', 2001, 2020, '13012340002', '河南', '許昌', 'ztn@qq.com');
INSERT INTO `two20210610`.`studentinfo`(`StudentID`, `StudentName`, `Gender`, `Birthday`, `ClassID`, `BeginYear`, `Phone`, `Province`, `City`, `Email`) VALUES ('3', '林平之', '男', '2021-06-15', 2001, 2020, '13012340003', '河南', '安陽(yáng)', 'lpz@qq.com');
INSERT INTO `two20210610`.`studentinfo`(`StudentID`, `StudentName`, `Gender`, `Birthday`, `ClassID`, `BeginYear`, `Phone`, `Province`, `City`, `Email`) VALUES ('4', '令狐沖', '男', '2021-06-08', 2002, 2019, '13012340004', '河南', '濮陽(yáng)', 'lhc@qq.com');
INSERT INTO `two20210610`.`studentinfo`(`StudentID`, `StudentName`, `Gender`, `Birthday`, `ClassID`, `BeginYear`, `Phone`, `Province`, `City`, `Email`) VALUES ('5', '岳靈珊', '女', '2021-06-02', 2002, 2019, '13012340005', '河南', '南陽(yáng)', 'yls@qq.com');

INSERT INTO `two20210610`.`subject`(`id`, `subjectname`, `teacherid`) VALUES (101, 'mysql', 201);
INSERT INTO `two20210610`.`subject`(`id`, `subjectname`, `teacherid`) VALUES (102, 'java基礎(chǔ)', 201);
INSERT INTO `two20210610`.`subject`(`id`, `subjectname`, `teacherid`) VALUES (103, '面向?qū)ο?, 202);
INSERT INTO `two20210610`.`subject`(`id`, `subjectname`, `teacherid`) VALUES (104, '網(wǎng)頁(yè)設(shè)計(jì)', 202);

INSERT INTO `two20210610`.`teacher`(`id`, `teachername`, `hiredate`, `gender`) VALUES (201, 'hys', '2021-06-04', '男');
INSERT INTO `two20210610`.`teacher`(`id`, `teachername`, `hiredate`, `gender`) VALUES (202, 'dj', '2021-06-01', '女');

select * from studentinfo;
select * from teacher;
select * from classinfo;
select * from subject;
select * from exam;
select * from grade;

-- 子查詢  用子查詢實(shí)現(xiàn)脚粟，查詢出學(xué)生“林平之”的同班同學(xué)  

# 1 找到林平之 的班級(jí)

# 2 查同班同學(xué)

select classid from studentinfo where studentname='林平之';

select * from studentinfo where classid=2001;

select * from studentinfo where classid=(select classid from studentinfo where studentname='林平之');

-- 查詢《mysql》考試成績(jī)剛到等于90的學(xué)生名單
-- 1 需要查詢學(xué)生名單 在studentinfo
-- 2 科目是mysql 在subject表中
-- 3 成績(jī)?yōu)?0 在exam表中subjectid studentid

select studentname from studentinfo inner join exam on studentinfo.StudentID=exam.studentid inner join subject on subject.id=exam.subjectid 
where subject.subjectname='mysql' and exam.exam=85;

-- 查詢《mysql》考試成績(jī)剛好等于85分的學(xué)生名單
# 根據(jù)學(xué)生id查詢學(xué)生的名單
select studentname from studentinfo where studentid = ?????
# 在成績(jī)中找到成績(jī)?yōu)?5的學(xué)生id
select studentid from exam where exam=85;
# 在課程中找的mysql的課程ID
select id from where subjectname='mysql';
-- 融合
select studentname from studentinfo where studentid =(select studentid from exam where exam=85 and subjectid = (select id from subject where subjectname='mysql'));

-- 更新"令狐沖"的"mysql"成績(jī)?yōu)?5分

-- 找到mysql成績(jī) +5 exam id subjectid studentid
update exam set exam = exam + 5 where subjectid = ?
-- 找到mysql課程的id subject id subjectname 
select id from subject where subjectname = "網(wǎng)頁(yè)設(shè)計(jì)"
-- 根據(jù)名字找id studentinfo
select id from teacher where teachername = "dj"
-- 融合
update exam set exam = exam + 5 where subjectid = (select id from subject where subjectname = "網(wǎng)頁(yè)設(shè)計(jì)" and subject.teacherid = (select id from 
teacher where teachername='dj'
))

-- 刪除“張無忌”的所有考試成績(jī)
-- 1 在studentinfo找到岳靈珊的學(xué)生id
select studentid from studentinfo where studentname="張無忌"
-- 2 根據(jù)id在exam表中刪除成績(jī)
delete from exam where studentid = ?
-- 合并
delete from exam where studentid = (select studentid from studentinfo where studentname="張無忌");

-- 插入的子查詢  把查詢結(jié)果作為條件
insert into studentinfo(select * from studentinfo where studentid=5)

select studentname from studentinfo inner join exam on studentinfo.StudentID=exam.studentid inner join subject on subject.id=exam.subjectid 
where subject.subjectname='mysql' and exam.exam=85;



-- 查詢《mysql》考試成績(jī)剛好等于85分的學(xué)生名單
# 根據(jù)學(xué)生id查詢學(xué)生的名單
select studentname from studentinfo where studentid = ?
# 在成績(jī)中找到成績(jī)?yōu)?5的學(xué)生id
select studentid from exam where exam=85;
# 在課程中找的mysql的課程ID
select id from subject where subjectname='mysql';
-- 融合
select studentname from studentinfo where studentid in(select studentid from exam where exam=85 and subjectid in 
(select id from subject where subjectname='mysql'));


-- 查詢成績(jī)表中編號(hào)為2的考試成績(jī)中是否存在不及格的學(xué)生刨裆，如果存在不及格的學(xué)生就將參加科目編號(hào)102考試的學(xué)生編號(hào)和成績(jī)?nèi)坎樵兲巵?-- 是否存在不及格的學(xué)生
select StudentID from where exam<60
-- 參加科目編號(hào)102考試的學(xué)生
where SubjectID=102
-- 學(xué)生編號(hào)和成績(jī)?nèi)坎樵冿@示出來
select StudentID,Exam from exam
-- 融合
select StudentID,Exam from exam where SubjectID =102 and exists (select StudentID from exam where exam<60);

-- 查詢成績(jī)比科目編號(hào)為”1“的這門課程的所有成績(jī)都大的學(xué)生考試信息
-- 1 查詢····學(xué)生考試成績(jī)
select * from exam where where?
-- 2 成績(jī)比科目編號(hào)為"101"的這門課程的所有成績(jī)都大
any (select exam from exam where SubjectID=1)
-- 合并
select * from exam where exam > any (select exam from exam where SubjectID=101);