删除数据库
DROP DATABASE IF EXIST 数据库名;
结果：报错 无法执行查询
查询当前所有数据库
SHOW DATABASES;
再次报错
成功
解析：需要使用数据库，然后再删除 否则sql不知道你到底用那个库
使用数据库 use 库名 不需要use database 库名
新建一个数据库 ku1219
CREATE DATABASE ku1219;
show databases;
创建学生表、教师表、课程表、成绩表
学生表
USE ku1219;
CREATE TABLE student(
sno VARCHAR(10) PRIMARY KEY,
sname VARCHAR(20),
sage NUMERIC(2), #NUMERIC 数字型
ssex VARCHAR(5)
);
教师表
USE ku1219;
CREATE TABLE teacher(
tno VARCHAR(10) PRIMARY KEY,
tname VARCHAR(20)
);
课程表，成绩表
USE ku1219;
CREATE TABLE course(
cno VARCHAR(10),
cname VARCHAR(20),
tno VARCHAR(20),
CONSTRAINT pk_course PRIMARY KEY(cno,tno) #主键约束
);
CREATE TABLE sc(
sno VARCHAR(10),
cno VARCHAR(10),
score NUMERIC(4,2),
CONSTRAINT pk_sc PRIMARY KEY(sno,cno)
#PRIMARY KEY(cno,tno)表示是Sc表的主码。PK_sc是此主码约束名
);
插入表数据
学生表
USE ku1219;
INSERT INTO student VALUES
('s001','张三',23,'男'),
('s002','李四',23,'男'),
('s003','吴鹏',25,'男'),
('s004','琴沁',20,'女'),
('s005','王丽',20,'女'),
('s006','李波',21,'男'),
('s007','刘玉',21,'男'),
('s008','萧蓉',21,'女'),
('s009','陈萧晓',23,'女'),
('s010','陈美',22,'女');
教师表
USE ku1219;
INSERT INTO teacher VALUES ('t001', '刘阳');
INSERT INTO teacher VALUES ('t002', '谌燕');
INSERT INTO teacher VALUES ('t003', '胡明星');
SELECT * FROM teacher;
课程表
USE ku1219;
INSERT INTO course VALUES ('c001','J2SE','t002');
INSERT INTO course VALUES ('c002','Java Web','t002');
INSERT INTO course VALUES ('c003','SSH','t001');
INSERT INTO course VALUES ('c004','Oracle','t001');
INSERT INTO course VALUES ('c005','SQL SERVER 2005','t003');
INSERT INTO course VALUES ('c006','C#','t003');
INSERT INTO course VALUES ('c007','JavaScript','t002');
INSERT INTO course VALUES ('c008','DIV+CSS','t001');
INSERT INTO course VALUES ('c009','PHP','t003');
INSERT INTO course VALUES ('c010','EJB3.0','t002');
SELECT * FROM course;
成绩表
USE ku1219;
INSERT INTO sc VALUES ('s001','c001',78.9);
INSERT INTO sc VALUES ('s002','c001',80.9);
INSERT INTO sc VALUES ('s003','c001',81.9);
INSERT INTO sc VALUES ('s004','c001',60.9);
INSERT INTO sc VALUES ('s001','c002',82.9);
INSERT INTO sc VALUES ('s002','c002',72.9);
INSERT INTO sc VALUES ('s003','c002',81.9);
INSERT INTO sc VALUES ('s001','c003','59');
COMMIT;
SELECT * FROM sc;
查询“c001”课程比“c002”课程成绩高的所有学生的学号；
查询一门课程的名称和成绩
SELECT sno,cno,score FROM sc WHERE cno='c001';
将两门成绩分别展示
SELECT a.sno,a.cno,a.score,b.cno,b.score FROM
(SELECT * FROM sc a WHERE a.cno='c001') a,
(SELECT * FROM sc b WHERE b.cno='c002') b
WHERE a.sno=b.sno ;
当c001成绩大于c002
SELECT a.sno,a.cno,a.score,b.cno,b.score FROM
(SELECT * FROM sc a WHERE a.cno='c001') a,
(SELECT * FROM sc b WHERE b.cno='c002') b
WHERE a.sno=b.sno AND a.score > b.score;
查询平均成绩大于60 分的同学的学号和平均成绩；
SELECT sno,AVG(score) FROM sc
GROUP BY sno
HAVING AVG(score)>60;
查询所有同学的学号、姓名、选课数、总成绩；
#通过成绩表查询学生的学号、选课数count（）、总成绩sum（）
SELECT sno,COUNT(cno)，SUM(score) FROM sc GROUP BY sno
#通过连接，查询学生名称
SELECT s.sname,a.* FROM
(SELECT sno,COUNT(cno),SUM(score) FROM sc GROUP BY sno) a ,
student s
WHERE a.sno=s.sno;
查询姓“刘”的老师的个数
SELECT COUNT(tname) 教师个数 FROM teacher
WHERE tname LIKE '刘%';
注：count（） 后面需要跟 group by分组函数
如果只有一个值后面可以不跟，但如果不止一个，会报错
查询没学过“谌燕”老师课的同学的学号、姓名；
#查询学过‘勘验’老师课程的学生学号
SELECT DISTINCT t.tname 教师姓名,t.tno 所教课程 ,s.sno 选课学生学号
FROM sc s
JOIN course c ON s.cno=c.cno
JOIN teacher t ON c.tno=t.tno
WHERE tname='谌燕';
#查询除此之外的学生信息
SELECT * FROM student st
WHERE st.sno NOT IN
(SELECT DISTINCT sno FROM sc s JOIN course c ON s.cno=c.cno
JOIN teacher t ON c.tno=t.tno WHERE tname='谌燕')
查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名
#查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名
#查询既学过001又学过002
SELECT a.sno,a.cno,b.cno FROM
(SELECT * FROM sc a WHERE a.cno='c001') a,
(SELECT * FROM sc b WHERE b.cno='c002') b
WHERE a.sno=b.sno ;
#将学生表和信息连接起来
SELECT st.* FROM sc a
JOIN sc b ON a.sno=b.sno
JOIN student st
ON st.sno=a.sno
WHERE a.cno='c001' AND b.cno='c002' AND st.sno=a.sno;
查询学过“谌燕”老师所教的课的同学的学号、姓名；
#查询学过“谌燕”老师所教的课的同学的学号、姓名；
#学过谌燕老师课程的学生学号
SELECT DISTINCT t.tname 教师姓名,t.tno 所教课程 ,s.sno 选课学生学号
FROM sc s
JOIN course c ON s.cno=c.cno
JOIN teacher t ON c.tno=t.tno
WHERE tname='谌燕';
#并学生信息表
SELECT DISTINCT st.* FROM
student st JOIN sc s ON st.sno=s.sno
JOIN course c ON s.cno=c.cno
JOIN teacher t ON c.tno=t.tno
WHERE t.tname='谌燕'
查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名
SELECT st.sno,st.sname FROM
student st
JOIN sc a ON st.sno=a.sno
JOIN sc b ON st.sno=b.sno
WHERE a.cno='c002' AND b.cno='c001' AND a.score < b.score
查询所有课程成绩小于60 分的同学的学号、姓名；
SELECT st.*,sc.score
FROM student st
JOIN sc ON st.sno=sc.sno
JOIN course c ON sc.cno=c.cno
WHERE sc.score <60;
查询没有学全所有课的同学的学号、姓名；
#查询所有课程数
USE ku1219;
SELECT COUNT(DISTINCT cno) 总课程数 FROM course;
#并学生表
SELECT stu.sno,stu.sname,COUNT(sc.cno)
FROM student stu
LEFT JOIN sc ON stu.sno=sc.sno
GROUP BY stu.sno,stu.sname
HAVING COUNT(sc.cno)<(SELECT COUNT(DISTINCT cno)FROM course);
查询至少学过 学号为“s001”同学 所选一门课 的 其他同学 学号和姓名；
#查询s001选了哪几门课
SELECT cno,sno FROM sc
WHERE sno = 's001';
#并学生信息表
SELECT *
FROM sc
LEFT JOIN student st
ON sc.`sno`=st.`sno`
WHERE sc.sno!='s001'
AND sc.cno IN (SELECT cno FROM sc
WHERE sno = 's001');
查询至少有一门课与学号为“s001”的同学所学相同的,同学的学号和姓名
SELECT st.* FROM student st,
(SELECT DISTINCT sno FROM sc WHERE sc.sno<>'s001'
AND cno IN (SELECT cno FROM sc WHERE sno = 's001' )) b
WHERE st.sno=b.sno
删除学习“谌燕”老师课的SC 表记录
SELECT t.tname,t.tno,c.cno
FROM course c
LEFT JOIN teacher t ON c.tno=t.tno
WHERE t.tname='谌燕';
#删除表记录
DELETE FROM sc
WHERE sc.cno IN
(
SELECT cno FROM course c
LEFT JOIN teacher t ON c.tno=t.tno
WHERE t.tname='谌燕'
);
SELECT * FROM sc;
查询没有学全所有课的同学的学号、姓名；
#查询所有课程数
SELECT COUNT(DISTINCT cno) 总课程数 FROM course;
SELECT stu.sno,stu.sname,COUNT(sc.cno)
FROM student stu
LEFT JOIN sc ON stu.sno=sc.sno
GROUP BY stu.sno,stu.sname
HAVING COUNT(sc.cno)<(SELECT COUNT(DISTINCT cno)FROM course);
把“SC”表中“谌燕”老师教的课的成绩都更改为此课程的平均成绩；
#把 谭燕老师教的课程提取出来
SELECT * FROM course JOIN teacher
ON course.`tno`=teacher.`tno`
WHERE teacher.`tname`='谌燕' ;
#查询所有课程的平均成绩
SELECT * FROM sc
SELECT cno,AVG(score) FROM sc GROUP BY cno;
#查询 谭燕老师 所教课程的平均分
SELECT * FROM
(SELECT cno,AVG(score) FROM sc GROUP BY cno) a
WHERE cno IN
(SELECT cno FROM course JOIN teacher
ON course.`tno`=teacher.`tno`
WHERE teacher.`tname`='谌燕' );
更新语句 UPDATE <表名> SET <字段名=值> WHERE <筛选条件>;
？？？？？？？？？？？？？？？？？？？？
向SC 表中插入一些记录，这些记录要求符合以下条件：
没有上过编号“c002”课程的同学学号、插入“c002”号课的平均成绩；
#没有上过编号“c002”课程的同学学号
SELECT * FROM sc WHERE cno != 'c002'
#“c002”号课的平均成绩
SELECT AVG(score) FROM sc WHERE cno = 'c002'
#插入 insert
INSERT INTO sc (sno,cno,score) VALUES (???????)
insert 插入 查询语句
INSERT IGNORE INTO `表名1` ( `字段1`, `字段2` )
SELECT
`字段3` AS 字段1,
'字段4' AS 字段2
FROM `表名2` WHERE 条件
？？？？？？？
## 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
SELECT * FROM sc
SELECT cno 课程ID,MAX(score) 最高分,MIN(score) 最低分 FROM sc
GROUP BY cno;
按各科平均成绩从低到高和及格率的百分数从高到低顺序
#平均成绩
SELECT cno,AVG(score) FROM sc GROUP BY cno
#及格率
SELECT SUM(CASE WHEN score>=60 THEN 1 ELSE 0 END) 及格人数 FROM sc GROUP BY cno
SELECT SUM(CASE WHEN score>=60 THEN 1 ELSE 0 END)/COUNT(*) 及格率 FROM sc GROUP BY cno
#并
SELECT cno 课程,AVG(score) 平均分,SUM(CASE WHEN score>=60 THEN 1 ELSE 0 END)/COUNT(*) 及格率
FROM sc GROUP BY cno
ORDER BY 平均分,及格率 DESC
查询不同老师所教不同课程平均分从高到低显示
#不同老师的课程
SELECT course.tno,sc.`cno`,AVG(sc.`score`)
FROM sc JOIN course
ON sc.`cno`=course.`cno`
GROUP BY sc.cno
???为什么报错？？？？？
avg（） 和 group by???
???为什么换了内连接还是报错？？？？
去掉一个查询选项就不报错？？？？为什么？？？？course表中有tno啊
表连接出了问题，可问题是什么？？？？
SELECT course.`cname`,sc.`cno`,AVG(sc.`score`)
FROM sc,course WHERE sc.`cno`=course.`cno`
GROUP BY sc.cno,course.`cname`
分组要两个分组词？？？？否则报错？？？
## 统计各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
SELECT cno,
SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) "[100-85]",
SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) "[85-70]",
SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) "[70-60]",
SUM(CASE WHEN score <60 THEN 1 ELSE 0 END) "[ <60]"
FROM sc
GROUP BY cno
#并课程名称
SELECT sc.cno,c.cname,
SUM(CASE WHEN sc.score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) "[100-85]",
SUM(CASE WHEN sc.score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) "[85-70]",
SUM(CASE WHEN sc.score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) "[70-60]",
SUM(CASE WHEN sc.score <60 THEN 1 ELSE 0 END) "[ <60]"
FROM sc,course c WHERE sc.`cno`=c.`cno`
GROUP BY sc.cno,c.cname
查询每门课程被选修的学生数
SELECT cno,COUNT(cno) FROM sc GROUP BY cno
查询男生、女生人数
SELECT ssex,COUNT(*)FROM student GROUP BY ssex;
SELECT SUM(CASE WHEN ssex='男' THEN 1 ELSE 0 END) 男生人数,
SUM(CASE WHEN ssex='女' THEN 1 ELSE 0 END) 女生人数
FROM student
GROUP BY ssex
查询姓“张”的学生名单
like
SELECT * FROM student WHERE sname LIKE '张%'
查询同名同性学生名单，并统计同名人数
SELECT sname,COUNT(sname) FROM student GROUP BY sname HAVING COUNT(sname)>1
1998年出生的学生名单(注：Student 表中Sage 列的类型是number)
YEAR(CURDATE())-sage
SELECT * FROM student WHERE sage='25'
SELECT YEAR(CURDATE())-25
SELECT sno,sname,sage,(YEAR(CURDATE())-sage) 出生年月
FROM student
HAVING 出生年月='1998'
查询每门课程的平均成绩，结果按平均成绩升序排列；平均成绩相同时，按课程号降序排列
SELECT cno,AVG(score)
FROM sc
GROUP BY cno
ORDER BY AVG(score), cno DESC
查询平均成绩大于80 的所有学生的学号、姓名和平均成绩
SELECT sno,AVG(score) FROM sc GROUP BY sno HAVING AVG(score)>80
SELECT s.*,AVG(sc.score) 平均成绩 FROM student s,sc
WHERE s.sno=sc.`sno`
GROUP BY sc.`sno`
HAVING AVG(sc.score)>80
查询课程名称为“oracle”，且分数低于60 的学生姓名和分数
SELECT cno,cname FROM course WHERE cname='oracle'
SELECT cno,score FROM sc
WHERE cno IN (SELECT cno FROM course WHERE cname='oracle')
HAVING score<60
SELECT s.sname,sc.`score`
FROM student s JOIN sc ON s.sno=sc.`sno`
WHERE sc.cno IN (SELECT cno FROM course WHERE cname='oracle')
HAVING sc.score<60
查询所有学生的选课情况；
SELECT * FROM student s,sc,course c
WHERE s.sno=sc.`sno` AND sc.`cno`=c.cno
SELECT st.sno,st.sname,c.cname FROM student st,sc,course c
WHERE sc.sno=st.sno AND sc.cno=c.cno;
查询任何一门课程成绩在70 分以上的姓名、课程名称和分数
SELECT * FROM sc HAVING SCORE>70
SELECT * FROM course c,sc,student s
WHERE c.cno=sc.`cno` AND s.sno=sc.`sno`
HAVING sc.`score`>70
查询不及格的课程，并按课程号从大到小排列
SELECT * FROM sc WHERE score<60 ORDER BY cno DESC
SELECT sc.sno,c.cno,c.cname,sc.score FROM sc,course c
WHERE sc.cno=c.cno AND sc.score<60 ORDER BY sc.cno DESC;
查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名；
SELECT * FROM sc WHERE cno='c001' AND score>80
SELECT * FROM student
WHERE sno IN
(SELECT sno FROM sc WHERE cno='c001' AND score>80)
SELECT st.sno,st.sname,sc.score FROM sc,student st
WHERE sc.sno=st.sno AND cno='c001' AND score>80;
求选了课程的学生人数
SELECT COUNT(cno) FROM sc GROUP BY cno
SELECT COUNT(DISTINCT cno) FROM sc
查询各个课程及相应的选修人数
SELECT cno,COUNT(cno) FROM sc GROUP BY cno
查询不同课程成绩相同的学生的学号、课程号、学生成绩
SELECT *
FROM sc a,sc b
WHERE a.score=b.score
AND a.cno != b.cno
SELECT a.*
FROM sc a,sc b
WHERE a.score=b.score
AND a.cno != b.cno
检索至少选修两门课程的学生学号
SELECT sno,COUNT(sno) FROM sc GROUP BY sno HAVING COUNT(sno)>=2
SELECT sno FROM sc GROUP BY sno HAVING COUNT(sno)>1;
统计每门课程的学生选修人数（超过10 人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT cno 课程号,COUNT(cno) 人数 FROM sc
GROUP BY cno
HAVING 人数>10
ORDER BY 人数 DESC,课程号
查询两门以上不及格课程的同学的学号及其平均成绩
SELECT * FROM sc WHERE score<60
SELECT sno,AVG(score) FROM sc WHERE score<60 GROUP BY sno
SELECT sno,COUNT(sno) FROM sc WHERE score<60 GROUP BY sno HAVING COUNT(sno)>2
SELECT sno,AVG(score) FROM sc WHERE score<60 GROUP BY sno HAVING COUNT(sno)>2
???????????????????
SELECT sno,AVG(score)FROM sc
WHERE sno IN
(SELECT sno FROM sc WHERE sc.score<60
GROUP BY sno HAVING COUNT(sno)>1)
GROUP BY sno
检索“c004”课程分数小于90，按分数降序排列的同学学号
SELECT * FROM sc WHERE cno='c004' AND score<90 ORDER BY score DESC
删除“s002”同学的“c001”课程的成绩、
SELECT * FROM sc WHERE sno='s002' AND cno='c001'
DELETE FROM sc WHERE sno='s002' AND cno='c001'
INSERT INTO sc
VALUES ('s002','c001','81')
1574
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
