准备数据，倒入sql文件
运行sql文件 得到四张表
select * from 表名 * 代表全部
1、AS子句作为别名
select studentname as "姓名" , sex as "性别",gradeid as "班级ID" from student; 多表查询给表起别名 简化代码
2、distinct 去重
DISTINCT关键字 作用： 去掉SELECT查询返回的记录结果中重复的记录（所有返回列的值都相同），只返回一条 语法：
多条语句去重
select distinct ID,NAME from student;以 ID和NAME 为单位例如：如下不算重复1 aa1 bb
3、where条件语句
4、LIKE模糊查询
查询姓李的同学记录% 所有字符select * from student where StudentName like "李%";"_" 代表一个字符查询姓李X(名只有1个)的同学记录 select * from student where StudentName like "李_";查询名字中包含文的记录select * from student where StudentName like "%文%";查询以 姓名 明 结尾的纪录select * from student where StudentName like "%明"
查询所有姓 李 的学生所有成绩
select s.StudentNo as "'李'同学学号",r.stuResult as "'李'同学成绩" from student as s ,result as r where s.StudentNo=r.StudentNo AND s.StudentName like "李%";
查询 Java的考试成绩
select s.subjectName as java,r.stuResult as "java的成绩" from subject as s ,result as r
where s.SubjectName="java" and s.SubjectNo=r.SubjectNo;
查询考试JAVA的学生的学号和考试成绩
select r.StudentNo as "学号",r.stuResult AS "考试成绩" from result as r ,subject as swhere r.subjectNo=s.subjectNo and s.subjectName="java";
查询考试Java的学生学号，姓名，考试成绩
select r.StudentNo as "学号",st.StudentName as "姓名",r.stuResult AS "考试成绩"
from result as r ,subject as s,student as st where r.subjectNo=s.subjectNo AND r.StudentNo=st.StudentNo and s.subjectName="java";
5 查询Student表中年龄为12或性别为“女”的同学记录。
CURDATE() 函数返回当前的日期。select * from student where TIMESTAMPDIFF(YEAR,BornDate,CURDATE())=12 or Sex=0;
year(now())-year(BronDate)
TIMESTAMPDIFF函数，有参数设置，
可以精确到天（DAY）、小时（HOUR），分钟（MINUTE）和秒（SECOND），
。对于比较的两个时间，时间小的放在前面，时间大的放在后面。
5、联合查询
SELECT 字段列表 FROM 表1 INNER|LEFT|RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;
要求：从subject和grade数据表查询课程名称和所属年级名称
select s.subjectName as "课程名称" ,g.gradeName as "年级名称" from subject as s INNER JOIN grade as g
on s.gradeid=g.gradeID;JION左边的是左表 JOIN JOIN右边的是右表
6、order by排序
查询所有考试结果，并按成绩由高到低排列ORDRY BY 字段select * from result ORDER BY stuResult desc;
根据成绩升序排序，当成绩相同时根据学号降序排序主要排序和次要排序select * from result ORDER BY stuResult asc ,StudentNo DESC ;
7、LIMIT 分页查询
m对应索引，n是步长
select * from result limit 1,5; 查询起始为学号为1002 共5条记录
8、统计函数
count计数、sum求和、avg平均值、max最大值、最小值
SELECT count(*) from student; count可以不放字段名，其他的都要放字段
9、group by 分组
按照不同的课程分组，分别算出其平均分、最高分和最低分，对于平均分低于60分的不予显示select s.subjectName as "课程名" ,avg(r.stuResult) as "平均分",MAX(r.stuResult) "最高分",MIN(r.stuResult) "最低分"from subject s, result r where s.SubjectNo=r.SubjectNo GROUP BY r.SubjectNo HAVING avg(r.stuResult)>60;
10、having
普通等值写法 select subjectName,avg(stuResult),max(stuResult),min(stuResult) from subject s,result rWHERE s.subjectNo=r.subjectNOGROUP BY r.subjectnoHAVING avg(stuResult)>60;
连表方法 select subjectname,avg(stuResult),max(stuResult),min(stuResult) from subject s INNER JOIN result r on r.subjectno=s.subjectno GROUP BY r.subjectno having avg(stuResult)>60;
11、子查询
查询大二的上了什么课？select subjectName from subject where gradeid =(select gradeID from grade where gradeName="大二");
查询 课程为java且分数不小于80分的学生的学号和姓名？
gradeid是桥梁子查询方法：
SELECT StudentNo,StudentName from student where StudentNo in(select StudentNo from result where SubjectNo in(select subjectNo FROM subject where subjectName="java") and stuResult>=80 );连表查询方法：
select st.StudentNo,StudentName from student as st INNER JOIN result as r on st.StudentNo=r.StudentNoINNER JOIN subject as s ONr.SubjectNo=s.subjectNo where s.subjectName="java" and r.stuResult>=80; 等值连接 select s.studentno,studentname from student s,subject su,result rWHERE r.studentno=s.studentno and su.subjectno=r.subjectnoand subjectname='java'and stuResult>80;sql语句注意事项
(1)MYSQL 查询语句 不区分大小写
例如：
select * from table where name = "aa"
select *from table where name = "AA"
这两条的查询结果是一样的
(2)sql count(*) 和 count(col)、count(1)区别
row行，col列
count(*)和count(列)根本就是不等价的，count(*)是针对于全表的，而count(列)是针对于某一列的，如果此列值为空的话，count(列)是不会统计这一行的。也就是说count(列)会用到索引，并且会过滤掉该列为null的那行。而count(*)是全表扫描，并且只要某一条数据有一列不为null，那就会统计到这条数据。 count(1)可以理解为表里有一列数据都为1的列。count(*)，执行时会把星号翻译成字段的具体名字，效果也是一样的，不过多了一个翻译的动作，比固定值的方式效率稍微低一些。
group_concat 和 group by 是一起使用的
综合练习
course sc student teachercourse:Cid,Cname,Tidsc:Id,Sid,Cid,scorestudent:Sid,Sname,Sage,Sexteacher:Tid,Tname1查询每门课程被选修的人数select Cid,count(Sid) "人数" from sc GROUP BY Cid;select Cid,count(*) "人数" from sc GROUP BY Cid;2 查询只选修了4门课程的学生学号，和姓名SELECT s.Sid,s.Sname from student s INNER JOIN sc on sc.Sid=s.SidGROUP BY sc.Sid HAVING count(sc.Cid)=4;select s.sid,sname from student sINNER JOIN scon s.Sid=sc.SidGROUP BY s.SidHAVING count(*)=4;3 查询姓张的学生名单select Sname from student where Sname like "李%";4 查询同名同姓的学生名单，显示姓名，同名的人数select Sname,count(Sid) "人数" from student GROUP BY Sname HAVING count(Sid)>=2;select sname,count(*) from student GROUP BY snamehaving count(*)>=2;5 查询每门课程的平均成绩，按照平均成绩升序，平均成绩相同时按课程号降序；select Cname "课程名",avg(score) "平均成绩" from course c INNER JOIN sc onc.Cid=sc.Cid GROUP BY c.Cid ORDER BY avg(score),c.Cid desc;select cid,avg(score) FROM sc GROUP BY cidORDER BY avg(score) asc,cid desc;6 查询平均成绩大于80分的学生的学号，姓名和平均成绩select s.Sid "学号",Sname "姓名",avg(score) "平均成绩" from student s INNER JOIN sc ONs.Sid=sc.Sid GROUP BY s.Sid HAVING avg(score)>80;select s.sid,sname,avg(score) 'avg' FROM scINNER JOIN student son s.sid=sc.sidGROUP BY s.sidHAVING avg>80;7 查询 所有 的课程分数都低于80分的学生的姓名 ???????已解决是所有的！！！select Sname "姓名" from student s INNER JOIN sc ONs.Sid = sc.Sid GROUP BY s.Sid HAVING MAX(score)<80;select sname from sc INNER JOIN student son s.sid=sc.SidGROUP BY s.SidHAVING max(score)<80;8 查询所有同学的选课情况，显示学生的姓名和选修的课程名select Sname "姓名",Cname "课程名" from student s INNER JOIN sc ONs.Sid=sc.Sid INNER JOIN course c ON c.Cid=sc.Cid ORDER BY s.Sid;下面这种更好：select sname,group_concat(cname) from student sINNER JOIN sc on s.sid=sc.sidINNER JOIN course con c.cid=sc.cidGROUP BY s.sid group_concat 和 group by 是一起使用的
9 查询学习Java的学生人数。select count(Sid) "学习java人数" from sc INNER JOIN course on course.Cid=sc.Cid WHERE course.Cname="java";select count(*) from sc INNER JOIN course con sc.cid=c.cidWHERE cname='java';10查询sc表中的最高分的学生学号和课程号。（子查询或者排序）select s.Sid "最高分学号",sc.Cid "课程号" from student s INNER JOIN sc ONs.Sid=sc.Sid WHERE score = (SELECT MAX(score) from sc);select sid,cid,score from sc ORDER BY score DESClimit 1;11 查询成绩在80-90之间的学生学号更正去重SELECT DISTINCT Sid from sc WHERE score BETWEEN 80 AND 90;12 查询成绩在80-90之间的学生的姓名更正去重SELECT DISTINCT Sname "学生姓名" from student INNER JOIN sc on student.Sid=sc.SidWHERE score BETWEEN 80 AND 90;SELECT DISTINCT sname from student sINNER JOIN scon s.sid=sc.sidWHERE score BETWEEN 80 and 90;13 查询每个学生的平均成绩select Sname "学生姓名",avg(score) "平均成绩" from student s INNER JOIN sc ONs.Sid=sc.Sid GROUP BY sc.Sid;14 查询所有任课老师的姓名和课程名select Tname "老师姓名",Cname "课程名" from teacher t INNER JOIN course con t.Tid=c.Tid;下面的方法更好：select tname,group_concat(cname) from teacher tINNER JOIN course con c.tid=t.tidGROUP BY t.tid;15 查询最高分的学生学号，学生姓名，考试成绩 错误：select s.Sid "学号",Sname "学生姓名",score "考试成绩" from student s INNER JOIN scon s.Sid=sc.Sid where score = (SELECT MAX(score) from sc);
更正：select sname,s.sid,score from sc INNER JOIN student son s.sid=sc.sidWHERE s.sid=(select sid from sc WHERE score=(select max(score) from sc));
select sname,s.sid,GROUP_CONCAT(score) from sc INNER JOIN student son s.sid=sc.sidWHERE s.sid=(select sid from sc WHERE score=(select max(score) from sc))GROUP BY s.sid;
练习题2，重点
course:cid,cname,tidsc:sid,cid,scorestudent:sid,sname,age,sexteacher:tid,tnameselect * from course;select * from sc;select * from student;select * from teacher;1．查询平均成绩大于60分的同学的学号和平均成绩???? 要分组where只能用于存在的列 错误表达： WHERE AVG(score)>60;select s.sid,avg(score) from student s INNER JOIN scon s.sid=sc.sid HAVING group by s.sid avg(score)>60;2．查询所有同学的学号、姓名、选课数、总成绩select s.sid "学号",sname "姓名",count(cid) "选课数",sum(score)"总成绩"from student s INNER JOIN sc on s.sid=sc.sid GROUP BY sc.sid3． 查询姓“李”的老师的个数select count(*)"姓李老师个数" from teacher where tname LIKE "李%";5.查询学过“李纯”老师课的同学的学号、姓名select s.sid"学号",sname"姓名" from student s INNER JOIN sc ONs.sid=sc.sid INNER JOIN course as c on c.cid=sc.cid INNER JOINteacher t on c.tid = t.tid WHERE tname = "李纯"; 6.查询没学过“李雷”老师课的同学的学号、姓名?????????---已解决select sid,sname from student where sid not in (select sid from sc where cid in(select cid from course where tid =(select tid from teacher where tname="李雷")));7.查询“001”课程比“002”课程成绩高的所有学生的学号、姓名?????? --已解决---course:cid,cname,tidsc:sid,cid,scorestudent:sid,sname,age,sexteacher:tid,tname 法一：
select sid,sname from student where sid in(
SELECT t1.sid FROM sc t1, sc t2WHERE t1.sid=t2.sid and t1.cid = '001' AND t2.cid = '002' ANDt1.score > t2.score);法二：
select s.sid,sname from student sINNER JOIN sc a on s.sid=a.sid INNER JOIN sc bon a.sid=b.sidwherea.cid = '001' AND b.cid = '002' ANDa.score > b.score;8. 查询学过“001”并且也学过编号“003”课程的同学的学号、姓名
select s.sid"学号",sname"姓名" from student s INNER JOIN sc a ON
s.sid =a.sid INNER JOIN sc b on a.sid=b.sidWHERE a.cid="001" and b.cid="003" GROUP BY b.sid;9.查询各科成绩最高分和最低分：以如下形式显示：课程ID，课程名，最高分，最低分select c.cid"课程ID",cname"课程名",max(score)"最高分",min(score)"最低分" FROMcourse c INNER JOIN sc on c.cid=sc.cid GROUP BY sc.cid;10. 查询没有学全所有课的同学的学号、姓名select s.sid"学号",sname"姓名" from student s INNER JOIN sc ONs.sid =sc.sid GROUP BY sc.sid HAVING count(*)<(select count(*) from course);11.查询学过“李雷”老师所教的所有课的同学的学号、姓名？？？----已解决course:cid,cname,tidsc:sid,cid,scorestudent:sid,sname,age,sexteacher:tid,tnameselect sid"学号",sname"姓名" from student where sid in (select sid from sc where cid in (SELECT cid from course where tid=(select tid from teacher where tname="李雷")));12. 查询至少学过学号为“1001”同学所学的一门课的其他同学学号和姓名方法一： 要排除学号为"1001"的学号 select DISTINCT s.sid"学号",sname"姓名" from student s INNER JOIN sc ONs.sid=sc.sid where cid in(select cid from sc where sid = "1001") and s.sid<>"1001";方法三：select sid"学号",sname"姓名" from student WHERE sid in(select DISTINCT sid from sc where cid in(select cid from sc where sid = "1001")and sid<>"1001");13.按各科平均成绩从低到高和及格率的百分数从高到低顺序排序???????---已解决select cid,round(avg(score),2)t1,(sum(case when score BETWEEN 60 and 100 then 1ELSE0end)/count(*))*100 t2from sc GROUP BY cid ORDER BY t1,t2 desc;
select cid,round(avg(score),2)t1,CONCAT(round((sum(case when score BETWEEN 60 and 100 then 1ELSE0end)/count(*))*100),'%') t2from sc GROUP BY cid ORDER BY t1,t2 desc;
14.删除学习“李纯”老师课的SC表记录DELETE from sc where cid in (SELECT cid from course where tid=(select tid from teacher where tname="李纯"));
练习题三
select * from course;select * from sc;select * from student;select * from teacher;course:cid,cname,tidsc:sid,cid,scorestudent:sid,sname,age,sexteacher:tid,tname 1 查询任何一门课程成绩在70分以上的姓名、课程名和分数select sname,GROUP_CONCAT(cname),GROUP_CONCAT(score) from student s INNER JOIN sc ONs.sid=sc.sid INNER JOIN course c on c.cid=sc.cid GROUP BY sc.sid HAVING min(sc.score)>70;2 查询所有不及格的课程，并按课程号从大到小罗列课程ID和学生IDselect cid,GROUP_CONCAT(s.sid) from sc INNER JOIN student s on sc.sid=s.sidwhere score<60 GROUP BY cid ORDER BY cid desc;3 查询课程编号为003且课程成绩在80分以上的学生的学号和姓名select s.sid,sname from student s INNER JOIN sc on s.sid=sc.sidWHERE cid='003' and score>80;4 求选全了课程的学生人数select count(a.sid) from (select sid from sc GROUP BY sid HAVING COUNT(*)=(select count(*) from course)) a;5 查询选修李 老师所授课程的学生中，成绩最高的学生姓名及其成绩select sname,max(score) from student s ,teacher t,course c,scwhere t.tid=c.tid and tname like "李%" ANDc.cid=sc.cid ANDsc.sid=s.sid GROUP BY tname;6 查询各个课程及相应的选修人数select c.cid,count(*) from course c INNER JOIN sc ONc.cid=sc.cid GROUP BY sc.cid;7 查询不同课程成绩相同的学生的学号、课程号、学生成绩?????select s1.sid,s2.sid,sc1.cid,sc1.score,sc2.score from student s1,student s2,sc sc1,sc sc2 where s1.sid!=s2.sid and s1.sid=sc1.sid and s2.sid=sc2.sid and sc1.cid!=sc2.cid and sc1.score=sc2.score;8 检索至少选修两门课程的学生学号select sid from sc GROUP BY sid HAVING count(*)>=2;9 查询全部学生都选修的课程的课程号和课程名哪一个课程被所有学生多选？select c.cid,cname from course c INNER JOIN sc ONc.cid=sc.cid GROUP BY sc.cid HAVING count(*)=(select count(*) from student);
4 求选全了课程的学生人数????select count(*) from (select sid from sc GROUP BY sid HAVINGcount(*)=(select count(*) from course)) a;2 查询sc表里 所有 不及格的课程，并按照课程号从大到小罗列课程id和学生id?????select sid,cid,score from sc where score<60 ORDER BY sid desc,cid desc;
练习四
导入cdsgusTable表select * from cdsgustable;idcnameCtfTpCtfIdGenderBirthdayAddressZipMobileTelFaxEMailVersion1. 获取每个性别的人数select gender,count(*) from cdsgustable GROUP BY gender;2. 查询2012年的数据select * from cdsgustable where year(version)=2012;3. 查询2012年12月的数据select * from cdsgustable where year(version)=2012 and month(version)=12;4. 查询姓王的人数select count(*)'姓王的人数' from cdsgustable where cname like '王%'5. 查询姓李的，并且CTFTP不是OTH的人数select count(*) from cdsgustable where cname like '李%' and CtfTp<>'OTH';6. 查询现在年龄大于30岁的人数select count(a.cname) from (select cname from cdsgustable where (year(NOW())-year(Birthday))>30 GROUP BY cname) a;错误：select count(*) from cdsgustable where (year(NOW())-year(Birthday))>30;7. 根据出生年月日，转换为星期，现实出生人最多的那个星期几的英语单词DATE_FORMAT(NOW(),"%W")select DATE_FORMAT(Birthday,"%W") bir_week from cdsgustable GROUP BY bir_week HAVING count(*)=(select count(*) from cdsgustable GROUP BY DATE_FORMAT(Birthday,"%W") limit 1);附加题9：获取每年的男女性别所占的百分比，并保留两位小数（结果如下所示） 提示：保留几位小数用函数round(值,保留小数位数)select year(Birthday) year,CONCAT(round((sum(case when Gender='F' then 1ELSE0end)/count(Gender))*100,2),'%')'女',CONCAT(round((sum(case when Gender='M' then 1ELSE0end)/count(Gender))*100,2),'%')'男'from cdsgustable GROUP BY year;
select * from course;select * from sc;select * from student;select * from teacher;course:cid,cname,tidsc:Id,Sid,Cid,scorestudent:Sid,Sname,Sage,Sexteacher:Tid,Tname1. 查询不同课程成绩相同的学生的学号、课程号、学生成绩select s1.sid,s1.cid,s2.sid,s2.cid,s1.score from sc s1 INNER JOIN sc s2 on s1.cid<>s2.cid where s1.score=s2.score;2. 统计各科成绩的各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60] select sc.cid,cname,sum(case when score between 85 and 100 then 1 ELSE 0end)'[100-85]',sum(case when score between 70 and 85 then 1 ELSE 0end)'[85-70]',sum(case when score between 60 and 70 then 1 ELSE 0end)'[70-60]',sum(case when score <60 then 1 ELSE 0end)'[ <60] ' from sc INNER JOIN course c on c.cid=sc.cidGROUP BY sc.cid;3. 查询所有学生的选课情况，显示学生姓名和选修的课程名select sname,GROUP_CONCAT(cname)from student s INNER JOIN sc ONs.sid=sc.sid INNER JOIN course c on sc.cid=c.cid GROUP BY sc.sid;4. 把sc表中李浩然老师教课的成绩都改为此课程的平均成绩 ？？？？