create database two20210610 default charset=utf8;

use one20210606; 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 one20210606.classinfo(id, classname, gradeid, beginyear) VALUES (2001, '20級1班', 20, '2020'); INSERT INTO one20210606.classinfo(id, classname, gradeid, beginyear) VALUES (2002, '20級2班', 20, '2020');

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

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

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

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

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

-- 子查詢 用子查詢實(shí)現(xiàn)桐款，查詢出學(xué)生“林平之”的同班同學(xué) --1 查出林平之 的班級 select classid from studentinfo where studentname='林平之'; --2 根據(jù)班級查詢同班學(xué)生信息 select * from studentinfo where chassid=2001; -- 合并 select * from studentinfo where classid=(select classid from studentinfo where studentname='林平之');

-- 查詢 《軟件技術(shù)》 考試成績剛好等于90分的學(xué)生名單 -- 1 需要查詢學(xué)生名單 再studentinfo -- 2 科目是mysql 再subject表中 id -- 3 成績是90 再exam表中subjectid

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

-- 查詢 《mysql》考試成績剛好等于85分的學(xué)生名單

根據(jù)學(xué)生id查詢學(xué)生的名單

<pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n23" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">select studentname from studentinfo where id = ?</pre>

在成績表中找到成績?yōu)?5的學(xué)生id

<pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n25" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">select studentid from exam where exam=85;</pre>

在課程表中找到mysql的課程id

<pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n27" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">select id from subject where subjectname='mysql';</pre>

-- 融合 select studentname from studentinfo where id = (select studentid from exam where exam=85 and subject = (select id from subject where subjectname='mysql') ) ; -- 更新“dj”的“mysql”成績?yōu)?5分

-- 找到....成績 +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)頁設(shè)計(jì)" -- 根據(jù)名字找id teacher select id from teacher where teachername='dj'

<pre class="md-fences md-end-block ty-contain-cm modeLoaded" spellcheck="false" lang="" cid="n30" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">-- 融合
update exam set exam = exam + 5 where subjectid = (select id from subject where subjectname = "網(wǎng)頁設(shè)計(jì)" and subject.teacherid = (select id from teacher where teachername='dj' ))</pre>

-- 刪除“岳靈珊”的所有考試成績 -- 1 在studentinfo表中找到岳靈珊的id select studentid from studentinfo where studentname="岳靈珊" -- 2 根據(jù)id在exam表中刪除成績 delect from exam where studentid=?

-- 合并 delect from exam where studentid= (select studentid from studentinfo where studentname="張無忌");

-- 插入的子查詢 把查詢結(jié)果作為條件 插入到新的數(shù)據(jù) insert into studentinfo values (select * from studentinfo where studentid=5)

-- 查詢《mysql》考試成績剛好等于85分的學(xué)生名單 in的使用 在....范圍之內(nèi)

根據(jù)學(xué)生id查詢學(xué)生的名單

select studentname from studentinfo where studentid = ?

在成績中找到成績?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 student from exam=85 and subjectid in (select id from subject where subjectname='mysql') );

-- 查詢成績表中科目編號為102的考試成績中是否存在不及格的學(xué)生崭别，如果存在不及格的學(xué)生就將參加科目編號102考試的學(xué)生編號和成績?nèi)坎樵冿@示出來 -- 是否存在不及格的學(xué)生 SELECT StudentID fromEXAM WHERE Exam<60 -- 參加科目編號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<60);

-- 查詢成績比科目編號為“1”的這門課程的所有成績都大的學(xué)生考試信息 -- 1 查詢....學(xué)生考試信息 SELECR * FROM Exam WHERE where ? -- 2 成績比科目編號為“101”的這門課程的所有成績都大 all(SELECT Exam from EXAM WHERE SubjectID=1) -- 合并 SELECT * FROM exam where exam > all(SELECT Exam from EXAM WHERE subjectid=101);