MySQL常规篇之增删改查（精选）

一谈及数据库，最常规的莫过于增删改查，那么在学习完SQL server后再来学习MySQL时，发现两者SQL语句有“异曲同工之妙”，那么这“妙”在何处呢？接下来，让我们以具体的实例来瞧瞧————

基础表

-- 学生表--CREATE TABLE Student( Sid VARCHAR(16) NOT NULL COMMENT '学生编号', Sname VARCHAR(30) NOT NULL COMMENT '学生姓名', Sage date DEFAULT NULL COMMENT '出生日期', Ssex CHAR(2) DEFAULT NULL COMMENT '学生性别') ENGINE=INNODB DEFAULT CHARSET=utf8;-- 课程表--CREATE TABLE Course(    Cid VARCHAR(16) NOT NULL COMMENT '课程编号',  Cname VARCHAR(30) DEFAULT NULL COMMENT '课程名称',    Tid VARCHAR(16) NOT NULL COMMENT '教师编号') ENGINE=INNODB DEFAULT CHARSET=utf8;-- 教师表--CREATE TABLE Teacher(    Tid VARCHAR(16) NOT NULL COMMENT '教室编号',    Tname VARCHAR(30) DEFAULT NULL COMMENT '教师姓名')ENGINE=INNODB DEFAULT CHARSET=utf8;-- 成绩表--CREATE TABLE SC(    Sid VARCHAR(16) NOT NULL COMMENT '学生编号',    Cid VARCHAR(16) NOT NULL COMMENT '课程编号',    Score double(255,0) DEFAULT NULL COMMENT '学生成绩')ENGINE=INNODB DEFAULT CHARSET=utf8;

导入数据

学生表

课程表

成绩表

教师表

增删改查操作

• 1.插入一条数据到学生表中

INSERT INTO Student VALUES         ('01','Cecilia','1995-10-09','女');

• 2.批量插入数据到学生表中

INSERT INTO Student VALUES         ('01','Cecilia','1995-10-09','女'),        ('02','芷若初荨','1996-11-11','女'),        ('03','刘诗诗','1987-03-10','女'),        ('04','胡歌','1982-09-20','男'),        ('05','白小明','1994-03-23','男'),        ('06','杨梦妮','1995-07-07','女'),        ('07','安文龙','1993-10-09','男'),        ('08','周逸飞','1997-8-1','男');

• 3.单纯地创建一个备份表，表结构和原来的表结构一模一样

create table student1 SELECT * FROM student where 1=2;

• 4.将原有的表数据全部备份到备份表中

INSERT INTO student1 SELECT * FROM student;

• 5.查询”001”课程比”002”课程成绩高的学生的信息及课程分数
  注意：此处重点同一个学生的两个对应课程的成绩比较
  解决方案一：

SELECT stu.*,s1.score,s2.score FROM student stu,SC s1,SC s2 WHERE stu.sid = s1.sid AND s1.cid = '001'AND stu.sid = s2.sid AND s2.cid = '002'AND s1.score>s2.score;

解决方案二：

SELECT stu.*,s1.score,s2.score FROM student stuINNER JOIN sc s1 ON stu.sid = s1.sid AND s1.cid = '001'INNER JOIN sc s2 ON stu.sid = s2.sid AND s2.cid = '002'WHERE s1.score>s2.score;

• 6.查询”001”课程比”002”课程成绩低的学生的信息及课程分数

SELECT stu.*,s1.score,s2.score FROM student stu,SC s1,SC s2 WHERE stu.sid = s1.sid AND s1.cid = '001'AND stu.sid = s2.sid AND s2.cid = '002'AND s1.score<s2.score;

• 7.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

分析：看到此要求，首先应该想到将每个学生的平均成绩和60进行比较，并且还要依次输出，那么这时就需要将学生按照编号和姓名依次分组，确保每个学生具有唯一性

解决方案一：

SELECT  stu.sid,stu.sname,AVG(s.score) AS '平均成绩' FROM student stu,sc s WHERE stu.sid = s.sid GROUP BY s.score HAVING AVG(s.score)>=60;

解决方案二：

SELECT stu.sid,stu.sname FROM student stuINNER JOIN SC s ON stu.sid = s.sidGROUP BY s.score HAVING AVG(s.score)>=60;

• 8.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

SELECT stu.sid,stu.sname FROM student stuINNER JOIN sc s ON stu.sid = s.sidGROUP BY s.score HAVING AVG(s.score) <=60;

• 9.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

分析：一个学生可以选多门课程，课程的总成绩又和选的课程数目有关，因此需要首先将所有学生进行分组，再对每个学生的课程和成绩进行统计

解决方案一:

SELECT stu.sid,stu.sname,COUNT(s.cid) AS '选课总数',SUM(s.score) AS '所有课程的总成绩' FROM student stu,SC s WHERE stu.sid = s.sid GROUP BY stu.sid,stu.sname;

解决方案二：

SELECT stu.sid,stu.sname,COUNT(s.cid) AS '选课总数',SUM(s.score) AS '所有课程的总成绩' FROM student stuINNER JOIN sc s ON stu.sid = s.sid GROUP BY stu.sid,stu.sname; 

• 10.查询”李”姓老师的数量

SELECT t.tname,COUNT(t.tid) AS '姓李的老师统计数目' FROM teacher t WHERE t.tname LIKE'李%';

• 11.查询学过”王同喜”老师授课的同学的信息
  解决方案一：

SELECT stu.*,c.cname,t.tname,s.score FROM student stu,course c,sc s,teacher t WHERE stu.sid = s.sid AND s.cid = c.cid AND c.tid = t.tid AND t.tname = '王同喜';

解决方案二：

SELECT stu.*,c.cname,t.tname,s.score FROM student stuINNER JOIN sc s ON stu.sid = s.sidINNER JOIN course c ON s.cid = c.cidINNER JOIN teacher t ON c.tid = t.tidWHERE t.tname = '王同喜'; 

• 12.查询没学过”王同喜”老师授课的同学的信息

分析：此处需要先将学过王同喜老师的所有学生查询出来，然后和学生表进行求差集，即不在学过王同喜的课程的查询结果集中但是在学生表中的学生

SELECT st.* FROM student stLEFT JOIN (SELECT stu.*,c.cname,t.tname,s.score FROM student stu,course c,sc s,teacher t WHERE stu.sid = s.sid AND s.cid = c.cid AND c.tid = t.tid AND t.tid AND t.tname = '王同喜') st1 ON st.sid = st1.sid WHERE st1.sid IS NULL;

• 13.查询学过编号为”001”并且也学过编号为”002”的课程的同学的信息

分析：此处可以采用三张表内连接求交集的方式得到查询结果，即学生表、学过课程001的学生表、学过课程002的学生表

SELECT stu.* FROM student stuINNER JOIN sc s1 ON stu.sid = s1.sid  AND s1.cid = '001'INNER JOIN sc s2 ON stu.sid = s2.sid  AND s2.cid = '002'

• 14.查询学过编号为”001”但是没有学过编号为”002”的课程的同学的信息

SELECT stu.*,s1.cid FROM student stuLEFT JOIN sc s1 ON stu.sid = s1.sid  AND s1.cid = '001'LEFT JOIN sc s2 ON stu.sid = s2.sid  AND s2.cid = '002'WHERE s1.cid = '001' AND s2.cid IS NULL;

• 15.查询至少有一门课与学号为”01”的同学所学相同的同学的信息

分析：此处可以有两种解决思路，第一种是采用in，首先分别查询所有同学学的课程信息和01号同学学的课程信息，然后判断01号同学所学课程编号是否在所有课程信息结果集中；第二种就是采用内连接求交集的思路

解决方案一：

SELECT stu.*,s.cid FROM student stu,sc s WHERE stu.sid = s.sid AND s.cid IN(SELECT sc.cid FROM sc WHERE sc.sid = '01');

解决方案二：

SELECT stu.sid,stu.sname,s.cid FROM student stuINNER JOIN sc s ON stu.sid = s.sidWHERE s.cid IN(    SELECT s.cid FROM sc s WHERE s.sid = '01')

• 16.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT stu.sid,stu.sname,AVG(s.score) AS '平均成绩',COUNT(s.cid) AS '统计不及格课程的数目' FROM student stu,sc s WHERE stu.sid = s.sid AND s.score<60 GROUP BY stu.sid,stu.sname;

• 17.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT stu.sid,stu.sname,SUM(s.score) AS '课程总成绩',AVG(s.score) AS '平均成绩' FROM student stu,course c,sc s WHERE stu.sid = s.sid AND s.cid = c.cid GROUP BY stu.sid,stu.sname ORDER BY SUM(s.score) DESC,AVG(s.score) DESC;

• 18.按照单科成绩排序

SELECT stu.sid,stu.sname,c.cname,s.score FROM student stu,course c,sc s WHERE stu.sid = s.sid AND s.cid = c.cid GROUP BY stu.sid,stu.sname ORDER BY s.score DESC;

• 19.按照课程总成绩排序

SELECT stu.sid,stu.sname,SUM(s.score) AS '课程总成绩' FROM student stu,course c,sc s WHERE stu.sid = s.sid AND s.cid = c.cid GROUP BY c.cid,c.cname ORDER BY SUM(s.score) DESC;

• 20.查询学生的总成绩并进行排名

SELECT stu.sid,stu.sname,SUM(s.score) AS '课程总成绩' FROM student stu,course c,sc s WHERE stu.sid = s.sid AND s.cid = c.cid GROUP BY c.cid,c.cname ORDER BY SUM(s.score) DESC;

• 21.查询不同老师所教不同课程平均分从高到低显示

SELECT t.tid,t.tname,c.cname,AVG(s.score) AS '该科平均分' FROM student stu,teacher t,course c,sc s WHERE stu.sid = s.sid AND s.cid = c.cid AND c.tid = t.tid GROUP BY t.tid,t.tname ORDER BY AVG(s.score) DESC;

• 22.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

SELECT stu.*,SUM(s.score) AS '课程总成绩' FROM student stu,course c,sc s WHERE stu.sid = s.sid AND s.cid = c.cid GROUP BY stu.sid,stu.sname ORDER BY SUM(s.score) DESC LIMIT 1,2 ;

• 23.查询学生平均成绩并排序

SELECT stu.sid,stu.sname,AVG(s.score) AS '平均成绩' FROM student stu,sc s WHERE stu.sid = s.sid GROUP BY stu.sid,stu.sname ORDER BY AVG(s.score);

• 24.查询各科成绩前三名的记录

SELECT stu.*,c.cname,s.score FROM student stu,course c,sc s WHERE stu.sid = s.sid AND s.cid = c.cid GROUP BY c.cid,c.cname ORDER BY s.score DESC LIMIT 0,3;

• 25.查询每门课程被选修的学生数

SELECT c.cid,c.cname,COUNT(s.sid) AS '选修的学生统计个数' FROM student stu,course c,sc s WHERE stu.sid = s.sid AND s.cid = c.cid GROUP BY c.cid,c.cname;

• 26.查询出只有两门课程的全部学生的学号和姓名

SELECT stu.sid,stu.sname,c.cid,c.cname FROM student stu,course c,sc s WHERE stu.sid = s.sid AND s.cid = c.cid AND COUNT(stu.sid) = 2;

• 27.查询男生、女生人数

分析： 此处就简单的Case When…Then END的使用

SELECT COUNT(case when stu.ssex = '男' THEN stu.ssex END) AS'男生人数',COUNT(case when stu.ssex = '女' THEN stu.ssex END) AS'女生人数' FROM student stu;

• 28.查询名字中含有”凤”字的学生信息

SELECT * FROM student stu WHERE sname LIKE '%凤%';

• 29.查询同名同姓学生名单，并统计同名人数

分析：此处关键点在于统计名字出现的次数大于1，说明同名的人数

SELECT stu.sid,stu.sname,COUNT(stu.sname) AS '统计同名的学生人数' FROM student stu GROUP BY stu.sname HAVING count(stu.sname)>1;

• 30.查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime）

  分析：此处的关键点在于将其年份取出来进行与1990比较就行了

SELECT sid,sname,ssex,sage AS'年龄' FROM student WHERE YEAR(sage) = '1990';

• 31.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号

SELECT c.cid,cname,AVG(s.score) AS '该门课程平均成绩' FROM course c,sc s WHERE s.cid = c.cid GROUP BY c.cid,c.cname ORDER BY AVG(s.score) DESC,c.cid;

• 32.查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

SELECT stu.sid,stu.sname,AVG(s.score) AS '平均成绩' FROM student stu,sc s WHERE stu.sid = s.sid AND '平均成绩' >= 85 ORDER BY stu.sid; 

• 33.查询课程名称为”高等数学”，且分数低于60的学生姓名和分数

SELECT stu.sid,stu.sname,s.score FROM student stu,sc s,course c WHERE stu.sid = s.sid AND s.cid = c.cid AND c.cname = '高等数学' AND s.score<60;

• 34.查询所有学生的课程及分数情况

SELECT stu.*,c.cid,c.cname,s.score FROM student stu,sc s,course c WHERE stu.sid = s.sid AND s.cid = c.cid ORDER BY stu.sid,stu.sname;

• 35.查询任何一门课程成绩在70分以上的姓名、课程名称和分数

SELECT stu.*,c.cid,c.cname,s.score FROM student stu,sc s,course c WHERE stu.sid = s.sid AND s.cid = c.cid AND s.score>=70 ORDER BY stu.sid,stu.sname;

• 36.查询不及格的课程

SELECT c.cid,c.cname,s.score FROM student stu,sc s,course c WHERE stu.sid = s.sid AND s.cid = c.cid AND s.score<60 ORDER BY c.cid,c.cname;

• 37.查询课程编号为001且课程成绩在80分以上的学生的学号和姓名

SELECT stu.sid,stu.sname,c.cid,c.cname,s.score FROM student stu,sc s,course c WHERE stu.sid = s.sid AND s.cid = c.cid AND c.cid = '001' AND s.score>80;

• 38.求每门课程的学生人数

SELECT c.cid,c.cname,COUNT(stu.sid) AS '该门课程的学生人数' FROM student stu,sc s,course c WHERE stu.sid = s.sid AND s.cid = c.cid GROUP BY c.cid,c.cname;

• 39.查询选修”余华平”老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT stu.sid,stu.sname,c.cname,t.tname,MAX(s.score) AS '最高分' FROM student stu,teacher t,course c,sc s WHERE stu.sid = s.sid AND s.cid = c.cid AND c.tid = t.tid AND t.tname = '余华平';

• 40.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

SELECT DISTINCT s1.*,s2.sid,s2.cid FROM sc s1,sc s2 WHERE s1.score = s2.score AND s1.cid <> s2.cid;

• 41.查询每门功课成绩最好的前两名

分析：首先看到需求，“前2名”大家很容易想到使用“top 2”，但是在这里，一个学生可能会选修多门课程，一门课程的前两名有可能会和另一门课程的前两名发生重复，那么这样情况下就不能单纯地使用先排序再取前两名而得到，需要先对选择同一门课程的同学们进行分区，然后对每个区进行排序再取前2名，在这里就需要使用到窗口函数over和Partition by（注意：Mysql不支持ROW_number()函数，一般在Oracle中出现！）

SELECT * FROM(SELECT *,ROW_number() over(PARTITION by cid ORDER BY score DESC) AS od FROM SC)t1 WHERE od<=2;

• 42.统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT s.cid,c.cname,COUNT(s.sid) AS '选修人数' FROM sc s,course c WHERE s.cid = c.cid GROUP BY c.cid,c.cname HAVING COUNT(s.sid)>=5 ORDER BY COUNT(s.sid) DESC,s.cid; 

• 43.检索至少选修两门课程的学生学号

SELECT stu.sid,stu.sname,COUNT(s.cid) AS '统计选修课程数目' from student stu,sc s WHERE stu.sid = s.sid GROUP BY stu.sid,stu.sname HAVING COUNT(s.cid)>2;

• 44.查询选修了全部课程的学生信息

分析：此处主要是看选修的课程总数有多少，那么需要通过查询语句得到

SELECT stu.*,c.cid,c.cname FROM student stu,sc s,course c WHERE stu.sid = s.sid AND s.cid = c.cid GROUP BY stu.sid,stu.sname HAVING COUNT(c.cid) = (SELECT COUNT(c1.cid) FROM course c1) ORDER BY stu.sid;

• 45.查询各学生的年龄

SELECT sid,sname,Year(CURDATE())-YEAR(sage) AS'年龄' FROM student;