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, # 班級編號
classname varchar(10),
gradeid int, # 年級編號
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級1班', 20, '2020');
INSERT INTO two20210610.classinfo(id, classname, gradeid, beginyear) VALUES (2002, '20級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級', '軟件技術(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', '河南', '安陽', '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', '河南', '濮陽', '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', '河南', '南陽', '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)頁設(shè)計', 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', '女');
-- 子查詢 用子查詢實現(xiàn)，查詢出學(xué)生“林平之”的同班同學(xué)
1 找到林平之 的班級
2 查同班同學(xué)
找到林平之 的班級
select classid from studentinfo where studentname='林平之';
根據(jù)班級查詢同班同學(xué)信息
select * from studentinfo where classid='2001';
合并
select * from studentinfo where classid=(select classid from studentinfo where studentname='林平之');
查詢《mysql》考試成績剛好等于90分的學(xué)生名單
-- 1 需要查詢學(xué)生名單 在studentinfo
-- 2 科目是mysql 在subject表中 id
-- 3 成績?yōu)?8 在exam表中 subjectid studentid
-- 方法一
select studentname from studentinfo S inner join exam e on S.StudentID=e.studentid inner join subject on e.subjectid=subject.id where subject.subjectname='mysql' and e.exam=98;
-- 方法二
select studentname '學(xué)生姓名' from studentinfo s where
s.StudentID=(select id from exam where
exam=98 and subjectid=(select id from subject where subjectname='mysql') )
-- 更新令狐沖的網(wǎng)頁設(shè)計成績+5分
-- 找到......成績
update exam set exam = exam + 5 where subjectid = ?
-- 找到mysql課程的id
select id from subject where subjectname='網(wǎng)頁設(shè)計'
-- 根據(jù)名字找id
select id from teacher where teachername='dj'
-- 融合
update exam set exam = exam + 5 where subjectid = (select id from subject where subjectname = '網(wǎng)頁設(shè)計' and subject.teacherid = (select id from teacher where teachername='dj'
))
-- 刪除張無忌的所有考試成績
-- 1 找到張無忌地學(xué)生id
select studentid from studentinfo where studentname='張無忌'
-- 2 根據(jù)id在exam表中刪除成績
delete from exam where studentid=?
-- 合并
delete from exam where studentid=(select studentid from studentinfo where StudentName='張無忌');
-- 插入的子查詢 把查詢的結(jié)果作為內(nèi)容 插入到新的數(shù)據(jù)
insert into studentinfo (select * from studentinfo where studentid=5);
查詢《mysql》考試成績剛好等于90分的學(xué)生名單
-- 1 需要查詢學(xué)生名單 在studentinfo
-- 2 科目是mysql 在subject表中 id
-- 3 成績?yōu)?8 在exam表中 subjectid studentid
-- 方法一
select studentname from studentinfo S inner join exam e on S.StudentID=e.studentid inner join subject on e.subjectid=subject.id where subject.subjectname='mysql' and e.exam=85;
-- 方法二
select studentname '學(xué)生姓名' from studentinfo s where
s.StudentID in (select id from exam where
exam=85 and subjectid in (select id from subject where subjectname='mysql') )
-- 查詢成績中科目編號為2的考試成績中是否存在不及格的學(xué)生
-- 是否存在不及格的學(xué)生
select studentID exam from exam where exam<80
-- 參加科目編號102的學(xué)生
where SubjectID=102
-- 學(xué)生編號和成績?nèi)坎樵冿@示出來
select StudentID,exam from exam
-- 融合
select StudentID,exam from exam where SubjectID=102 and exists (select StudentID from exam where exam<80);
-- 查詢成績比科目編號為1 的這門課的所有成績都大的學(xué)生考試信息
-- 查詢。。。學(xué)生考試信息
select * from exam where where ?
-- 成績比科目編號為101的這門課程的所有成績都大
all(select exam from exam where SubjectID=1)
-- 合并
select * from exam where exam > all(select exam from exam where subjectid=101);
