--table Student
CREATETABLE Student(
SVARCHAR(10),
SnameVARCHAR(10),
SageDATE,
SsexVARCHAR(10));
--tableStudent: insert rows
INSERTINTO Student (S, Sname, Sage, Ssex) VALUES ('01', 'frank1', '1991-01-01','Male');
INSERTINTO Student VALUES ('02', 'frank2', '1992-07-07', 'Female');
INSERTINTO Student VALUES ('03', 'frank3', '1993-07-07', 'Male');
INSERTINTO Student VALUES ('04', 'frank4', '1994-07-07', 'Male');
--table Course
CREATETABLE Course(
CVARCHAR(10),
CnameVARCHAR(10),
TVARCHAR(10));
--tableCourse: insert rows
INSERTINTO Course VALUES ('01', 'Chinese', '01');
INSERTINTO Course VALUES ('02', 'Math', '02');
INSERTINTO Course VALUES ('03', 'English', 'TeacherC');
UPDATECourse SET T = '03' WHERE Cname = 'English';
--table Teacher
CREATETABLE Teacher(
TVARCHAR(10),
TnameVARCHAR(10));
--tableTeacher: insert rows
INSERTINTO Teacher VALUES ('01', 'TeacherA');
INSERTINTO Teacher VALUES ('02', 'TeacherB');
INSERTINTO Teacher VALUES ('03', 'TeacherC');
--table SC
CREATETABLE SC(
SVARCHAR(10),
CVARCHAR(10),
scoreINT);
--tableSC: insert rows
INSERTINTO SC VALUES ('01', '01', 81);
INSERTINTO SC VALUES ('01', '02', 41);
INSERTINTO SC VALUES ('01', '03', 71);
INSERTINTO SC VALUES ('02', '01', 82);
INSERTINTO SC VALUES ('02', '02', 42);
INSERTINTO SC VALUES ('02', '03', 72);
INSERTINTO SC VALUES ('03', '01', 83);
INSERTINTO SC VALUES ('03', '02', 73);
INSERTINTO SC VALUES ('03', '03', 63);
INSERTINTO SC VALUES ('04', '01', 81);
--1 student and score whose SC 01 > SC 02
--Onetable can be as two tables || IFNULL
Selecta.*, b.score, c.score from Student a , SC b, SC c where a.S = b.S and c.S = b.Sand b.C = '01' and c.C = '02' and b.score > IFNULL(c.score, 0);
--3 student whose average score >=60
--Groupby &#
SQL语句的经典语句50例--MySQL版本的答案
最新推荐文章于 2024-07-15 17:39:27 发布
![](https://img-home.csdnimg.cn/images/20240711042549.png)