SQL语句的查询SELECT、插入INSERT INTO、修改UPDATE、删除DELETE、子查询、建表语句、TOP字句等等
数据库 Student表 SC表 Course表 1.查询总成绩超过200分的学生，要求列出学号、总成绩。（1）代码实现：
SELECT Student.Sno, SUM(Grade) AS '总成绩' FROM Student
JOIN SC ON SC.Sno = Student.Sno
GROUP BY Student.Sno
HAVING SUM(Grade) >200
（2）运行效果：
（1）代码实现：
[1]方法一：子查询
SELECT Student.Sno, Student.Sdept FROM Student
JOIN SC ON SC.Sno = Student.Sno
WHERE SC.Cno IN (
SELECT Cno FROM SC
WHERE SC.Cno = 'c002'
)
[2]方法一：子多表连接
SELECT Student.Sno, Student.Sdept FROM Student
JOIN SC ON SC.Sno = Student.Sno
WHERE SC.Cno = 'c002'
（2）运行效果：
（1）代码实现：
SELECT Student.Sname, SC.Cno, SC.Grade FROM SC
JOIN Student ON Student.Sno = SC.Sno
WHERE SC.Grade > 80
ORDER BY SC.Grade DESC
（2）运行效果：
（1）代码实现：
SELECT Student.Sno, Student.Sname, Student.Sdept FROM Student
LEFT JOIN SC ON SC.Sno = Student.Sno
WHERE SC.Cno IS NULL
（2）运行效果：
（1）代码实现：
SELECT Cname, Semester FROM Course
WHERE Semester IN (
SELECT Semester FROM Course WHERE Cname = 'Java')
AND Cname != 'Java'
（2）运行效果：
（1）代码实现：
SELECT Sname, Sdept, Sage FROM Student
WHERE Sage IN (
SELECT Sage FROM Student WHERE Sname = '李勇')
AND Sname != '李勇'
（2）运行效果：
[1]代码实现：
SELECT DISTINCT Student.Sname, Student.Sdept FROM Student
JOIN SC ON SC.Sno = Student.Sno
WHERE SC.Sno IN(
SELECT Sno FROM SC WHERE Cno = 'c001')
[2]运行效果：
[1]代码实现：
SELECT Student.Sno, Student.Sname, SC.Cno, SC.Grade FROM Student
JOIN SC ON SC.Sno = Student.Sno
WHERE SC.Sno IN (
SELECT Sno FROM Student WHERE Sdept = '数学系')
AND SC.Grade > 80
[2]运行效果：
[1]代码实现：
SELECT TOP 1 Student.Sname FROM SC
JOIN Student ON SC.Sno = Student.Sno
WHERE SC.Sno IN (
SELECT Sno FROM Student WHERE Sdept = '计算机系')
ORDER BY SC.Grade DESC
[2]运行效果：
[1]代码实现：
SELECT TOP 1 Student.Sname, Student.Sdept, SC.Grade FROM SC
JOIN Student ON SC.Sno = Student.Sno
JOIN Course ON SC.Cno = Course.Cno
WHERE SC.Cno IN (
SELECT Cno FROM Course WHERE Cname = '数据结构')
ORDER BY SC.Grade DESC
[2]运行效果：
（1）代码实现：
SELECT DISTINCT Student.Sno, Student.Sdept FROM Student
LEFT JOIN SC ON SC.Sno = Student.Sno
WHERE SC.Cno IS NULL
OR SC.Sno NOT IN (SELECT Sno FROM SC WHERE Cno IN (SELECT Cno FROM Course WHERE Cname = 'Java'))
（2）运行效果：
（1）代码实现：
SELECT DISTINCT Student.Sname, Student.Ssex FROM Student
LEFT JOIN SC ON SC.Sno = Student.Sno
WHERE SC.Cno IS NULL
AND Student.Sdept = '计算机系'
（2）运行效果：
COL1:整型，允许空值。
COL2:字符型，长度为10，不允许空值。
COL3:字符型，长度为10，允许空值。
试写出按行插人如下数据的语句(空白处表示空值)
| COL1 | COL2 | COL3 |
|---|---|---|
| B1 | ||
| 1 | B2 | C2 |
| 2 | B3 |
（1）代码实现：
create table test_t(
COL1 int null,
COL2 varchar(10) not null,
COL3 varchar(10) null
);
insert into test_t values (null, 'B1', null),(1, 'B2', 'C2'), (2, 'B3', null);
select * from test_t
（2）运行效果：
（1）代码实现：
DELETE FROM SC WHERE Grade < 50
DELETE FROM SC WHERE Grade IS NULL
SELECT * FROM SC
（2）运行效果：
（1）代码实现：
DELETE FROM Course WHERE Cno NOT IN (SELECT Cno FROM SC)
SELECT * FROM Course
（2）运行效果：
（1）代码实现：
DELETE FROM SC
WHERE SC.Sno IN(SELECT Sno FROM StudeNt WHERE Sdept = '计算机系')
AND SC.Cno IN(SELECT Cno FROM Course WHERE Cname = 'Java')
SELECT * FROM SC
（2）运行效果：
（1）代码实现：
UPDATE Course SET Credit = Credit + 2 WHERE Semester = 2
SELECT * FROM Course
（2）运行效果：
（1）代码实现：
UPDATE Course SET Credit = 3 WHERE Cname = 'Java'
SELECT * FROM Course
（2）运行效果：
（1）代码实现：
UPDATE Student SET Sage = Sage + 1 WHERE Sdept = '计算机系'
SELECT * FROM Student
（2）运行效果：
（1）代码实现：
UPDATE SC SET Grade = Grade + 5
WHERE SC.Sno IN(SELECT Sno FROM Student WHERE Sdept = '信息系')
AND SC.Cno IN (SELECT Cno FROM Course WHERE Cname = '计算机文化学')
SELECT * FROM SC
（2）运行效果：
（1）代码实现：
SELECT Sdept, COUNT(*) AS '人数' INTO Dept_Age
FROM Student WHERE Sage >= 20
GROUP BY Sdept
SELECT * FROM Dept_Age
（2）运行效果：
（1）代码实现：
SELECT Student.Sno AS '学号', Student.Sname AS '姓名', SC.Grade AS '成绩',
CASE
WHEN SC.Grade >= 90 THEN '好'
WHEN SC.Grade BETWEEN 80 AND 89 THEN '较好'
WHEN SC.Grade BETWEEN 70 AND 79 THEN '一般'
WHEN SC.Grade BETWEEN 60 AND 69 THEN '较差'
ELSE '差'
END AS '成绩情况'
FROM SC
JOIN Student ON SC.Sno = Student.Sno
JOIN Course ON SC.Cno = Course.Cno
WHERE Student.Sdept = '计算机系' AND Course.Cname = 'Java'
（2）运行效果：
（1）代码实现：
SELECT Student.Sno AS '学号', COUNT(SC.Cno) AS '选课门数',
CASE
WHEN COUNT(SC.Cno) >= 6 THEN '多'
WHEN COUNT(SC.Cno) BETWEEN 3 AND 5 THEN '一般'
WHEN COUNT(SC.Cno) BETWEEN 1 AND 2 THEN '偏少'
WHEn COUNT(SC.Cno) = 0 THEN '未选课'
END AS '选课情况'
FROM Student
LEFT JOIN SC ON SC.Sno = Student.Sno
GROUP BY Student.Sno
（2）运行效果：
（1）代码实现：
UPDATE Course SET Credit = Credit +
CASE
WHEN Semester BETWEEN 1 AND 2 THEN 5
WHEN Semester BETWEEN 3 AND 4 THEN 3
WHEN Semester BETWEEN 5 AND 6 THEN 1
ELSE 0
END
SELECT * FROM Course
（2）运行效果：
（1）代码实现：
SELECT DISTINCT Student.Sname, SC.Cno, Course.Semester, Course.Credit
FROM SC
JOIN Student ON Student.Sno = SC.Sno
JOIN Course ON Course.Cno = SC.Cno
WHERE Student.Sname = '李勇' OR Student.Sname = '王大力'
（2）运行效果：
（1）代码实现：
SELECT DISTINCT Course.Cname, Course.Credit
FROM Course,Student
WHERE Cno IN(SELECT Cno FROM SC JOIN Student ON SC.Sno = Student.Sno WHERE Student.Sname = '李勇' EXCEPT SELECT Cno FROM SC JOIN Student ON Student.Sno = SC.Sno WHERE Student.Sname = '王大力')
AND Course.Semester = 3
（2）运行效果：
（1）代码实现：
SELECT DISTINCT Course.Cname, Course.Credit
FROM Course
WHERE Cno IN(SELECT Cno FROM SC JOIN Student ON SC.Sno = Student.Sno WHERE Student.Sname = '李勇' INTERSECT SELECT Cno FROM SC JOIN Student ON Student.Sno = SC.Sno WHERE Student.Sname = '王大力')
AND Course.Credit >= 3
（2）运行效果：
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）