写在前面：在互联网公司业务部门做分析师，一般会要求用SQL进行提数，这是必备的技能。这是一个我从入门到日常积累的帖子，内容循序渐进。第一篇是【入门】，写了我当时学习的步骤，建议控制在十天内搞定（PS:我当时基本就是上下班回家地铁上看的）；第二篇是【刷题&面试题】，一定要保证每天都要刷，即使一道也行！保持手感！第三篇是【工作中遇到的语法记录】，会不定期更新，欢迎长期交流~
第一篇：入门-总耗时十天
关卡1-了解什么是数据库和SQL，并成功安装MYSQL-耗时一天
数据库（Database)是将大量数据保存起来，通过计算机加工而成的可以进行高效访问的数据集合。用来管理数据库的计算机系统称为数据库管理系统（DBMS）。DBMS能够实现多个用户同时安全简单地操作大量数据。DBMS的分类：层次数据库、关系数据库、面向对象数据库、XML数据库、键值存储系统。这里需要重点了解的是关系数据库（RDBMS），它是采用行和列组成的二维表来管理数据库，并使用专门的SQL（Structured Query Language，结构化查询语言)对数据进行操作。比较具有代表性的RDBMS有：
Oracle Database
SQL Server
DB2
PostgreSQL
MySQL
RDBMS最常见的系统结构就是客户端/服务器类型，简而言之就是数据库将数据保存到硬盘等设备上，通过客户端向服务器发送SQL语句来实现数据库的读写操作。关系数据库通过表来管理数据，数据库中可以同时存储多个表，而表是由行和列来组成的，根据SQL语句的内容返回的数据同样必须是二维表的形式。表的列称为字段，表的行称为记录。关系数据库必须以行为单位进行数据读写，行和列交汇的为单元格，一个单元格只能输入一个数据。
SQL是用来操作关系数据库的语言，SQL语言可以分为三类：
数据定义语言DDL：用来创建或者删除存储数据用的数据库以及数据库中的表等对象。
CREATE:创建数据库和表等对象。
DROP:删除数据库和表等对象。
ALTER:修改数据库和表等对象。
数据操纵语言DML：用来查询或者变更表中的记录。
SELECT：查询。
INSERT：向表中插入新数据。
UPDATE: 更新表中的数据。
DELETE：删除表中的数据。
数据控制语言DCL：用来确认或者取消对数据库中的数据进行的变更。
COMMIT:  确认对数据库中的数据进行变更。
ROLLBACK: 取消对数据库中的数据进行变更。
GRANT：赋予用户操作权限。
REVOKE：取消用户操作权限。
作为商业分析师，如果是在业务部门，那实际工作中99%都集中在DML语言中的SELECT。
之后的关卡我们会用到MYSQL,请按照下面的链接一步一步操作，安装好服务器和客户端。
猴子：超级详细的mysql安装指南
猴子：Mysql客户端：Navicat安装教程及问题汇总
安装过程中遇到问题请利用搜索引擎解决，网上也有很多安装教程，请在一天内完成此任务。
关卡2-看完《SQL基础教程》（作者：Mick）-耗时五天
这本书非常适合初学者，简单易懂，请保证每天看1~2章，书上的例题及课后题目都可以在Navicat上练习，勤做笔记，入门SQL仅此一本书读透彻即可。以下为我看完书后按照章节选取的重要知识点，供参考。强烈建议边看书边自己梳理知识框架，特别是语法运行的逻辑及先后顺序，这对以后编写复杂代码很有用，一定要打好基础。看完一遍后根据框架再快速过一遍来巩固。（第八-九章可以略过，选择性看）
关卡3-上w3school网站查漏补缺-耗时一天
关于学习SQL的网站，我刷过SQLZOO和w3school，但网站上的题目答案不一定是最优的，需要自己去判断和辨别，可以根据个人需要在网站进行学习，不懂的知识点返回《SQL基础教程》去深入理解，会豁然开朗。
SQL 简介
关卡4-入门基础题-耗时三天
网络上流传的入门50题（两个版本都有），先自己做，主要是对之前学的基础知识的巩固和查漏补缺。
第一个版本
学生表 Student(SId,Sname,Sage,Ssex) ：
SId 学⽣生编号,Sname 学⽣生姓名,Sage 出⽣生年年⽉月,Ssex 学⽣生性别
课程表 Course(CId,Cname,TId) ：
CId 课程编号,Cname 课程名称,TId 教师编号
教师表 Teacher(TId,Tname) ：
TId 教师编号,Tname 教师姓名
成绩表 SC(SId,CId,score) ：
SId 学⽣生编号,CId 课程编号,score 分数
-- 1、查询01课程比02课程成绩高的学生的信息及课程分数
-- 书写规范：关键词全部大写、括号要缩进并换行
-- 考点1：where 一定要跟select 
-- 考点2：匹配join开始运行都是先笛卡尔集，然后根据on后的条件（可多个）重新组合后一定要用select才能取出结果
SELECT e.*,d.cid,d.score FROM
(
SELECT a.sid FROM
  (
SELECT sid,cid,score FROM sc
WHERE cid='01'
  )AS a
INNER JOIN
  (
SELECT sid,cid,score FROM sc
WHERE cid='02'
  )AS b
ON a.sid=b.sid
WHERE a.score>b.score
)AS c
LEFT JOIN 
sc AS d 
ON c.sid=d.sid
LEFT JOIN 
student AS e
ON d.sid=e.sid
-- 1.1 查询同时存在课程01和02课程的情况
SELECT d.*,c.cid,c.score FROM
(
SELECT sid,cid,score FROM sc 
WHERE cid='01'
)AS a
INNER JOIN
(
SELECT sid,cid,score From sc 
WHERE cid='02'
)AS b
ON a.sid=b.sid
LEFT JOIN sc AS c
ON a.sid=c.sid
LEFT JOIN student d 
ON c.sid=d.sid
-- 1.2 查询存在01课程但可能不存在02课程的情况（不存在时显示为null)
SELECT d.*,c.cid,c.score FROM
(
SELECT sid,cid,score FROM sc 
WHERE cid='01'
)AS a
LEFT JOIN 
(
SELECT sid,cid,score From sc 
WHERE cid='02'
)AS b
ON a.sid=b.sid
LEFT JOIN sc AS c
ON a.sid=c.sid
LEFT JOIN student d 
ON c.sid=d.sid
-- 1.3 查询不存在01课程但存在02课程的情况
SELECT e.*,d.cid,d.score FROM
(
SELECT a.sid FROM
  (
SELECT sid,cid,score FROM sc 
WHERE cid='02'
  )AS a
LEFT JOIN 
  (
SELECT sid,cid,score From sc 
WHERE cid='01'
  )AS b
ON a.sid=b.sid
WHERE b.cid is NULL
)AS c 
LEFT JOIN sc AS d
ON c.sid=d.sid	
LEFT JOIN student AS e 
ON d.sid=e.sid
-- 2.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
-- 考点：子查询和group by
SELECT a.sid,b.sname,a.score_avg FROM
(
SELECT sid,AVG(score)AS score_avg FROM
sc 
GROUP BY sid
) AS a
INNER JOIN 
student AS b
ON a.sid=b.sid
WHERE a.score_avg>=60
-- 3、查询在SC表存在成绩的学生信息 
SELECT DISTINCT a.* FROM student AS a
INNER JOIN 
sc AS b 
ON a.sid=b.sid
WHERE b.score is not null 
-- 4、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null ) 
SELECT a.sid,a.sname,
COUNT(DISTINCT b.cid)AS '选课总数',
SUM(b.score)AS '课程总成绩'
FROM student AS a
LEFT JOIN
sc AS b 
ON a.sid=b.sid
GROUP BY a.sid,a.sname
-- 另外一种是先求分数表先group by 再取对应的学生信息，用子查询
SELECT b.sid,b.sname,a.cid,a.score
FROM 
student as b
LEFT JOIN 
(
SELECT sid,
COUNT(DISTINCT cid)AS cid,
SUM(score) AS score
FROM sc 
GROUP BY sid
)AS a
ON a.sid=b.sid
-- 4.1查有成绩的学生信息
SELECT DISTINCT a.sid,b.sname,b.sage,b.ssex FROM sc AS a
INNER JOIN student AS b 
ON a.sid=b.sid
-- 5、查询李姓老师的数量
SELECT COUNT(DISTINCT tid) FROM teacher 
WHERE tname LIKE '李%'
-- 6、查询学过张三老师授课的同学信息
SELECT d.sid,c.cid,c.tname,e.sname,e.ssex,e.sage FROM
(
SELECT a.cid,a.tid,b.tname FROM course AS a
INNER JOIN teacher AS b
ON a.tid=b.tid
WHERE tname='张三'
)AS c
LEFT JOIN sc AS d
ON c.cid=d.cid
LEFT JOIN student AS e
ON d.sid=e.sid
-- 7、查询没有学全所有课程的同学信息
SELECT b.sid,c.sname,c.sage,c.ssex FROM
(
SELECT a.sid FROM 
  (
SELECT sid,COUNT(DISTINCT cid)AS count_cid FROM sc 
GROUP BY sid
  )AS a
WHERE a.count_cid <3
)AS b 
LEFT JOIN  student c
ON b.sid=c.sid
-- 所有课程如果不用3直接表示
SELECT b.sid,c.sname,c.sage,c.ssex FROM
(
SELECT a.sid FROM 
  (
SELECT sid,COUNT(DISTINCT cid)AS count_cid FROM sc 
GROUP BY sid
  )AS a
WHERE a.count_cid <
     (
SELECT COUNT(DISTINCT cid) FROM course 
     )
)AS b 
LEFT JOIN  student c
ON b.sid=c.sid
-- 8、查询至少有一门课与学号为01的同学所学相同的同学信息
SELECT DISTINCT a.sid,b.sname,b.ssex,b.sage FROM sc AS a
LEFT JOIN student b
ON a.sid=b.sid
WHERE a.cid IN
(
SELECT cid FROM sc 
WHERE sid='01'
)
-- 9查询和01号同学学习的课程完全相同的其他同学的信息
-- concat函数可以连接多个字段
SELECT concat(sid,',',cid,',',score) FROM sc
-- concat_ws()函数可以一次性指定分隔符
SELECT concat_ws(',',sid,cid,score) FROM sc
-- group_concat() 函数让名字出现一次，相同名字对应的id分别出现:语法
SELECT sid,group_concat(cid order by cid DESC separator '_') from sc
GROUP BY sid
-- 先取出01同学学习的课程，并放在一个单元格里作为一个concat_cid,取出其他sid对应的concat_cid让其相等
SELECT a.sid,b.sname,b.sage,b.ssex,group_concat(distinct a.cid order by a.sid,a.cid separator '_')AS concat_cid1
FROM sc AS a
INNER JOIN student AS b 
ON a.sid=b.sid
GROUP BY a.sid,b.sname,b.sage,b.ssex
HAVING concat_cid1=
(
SELECT concat_cid FROM 
(
SELECT sid,
group_concat(DISTINCT cid order by sid,cid separator '_')AS concat_cid
FROM sc 
WHERE sid='01'
GROUP BY sid
)AS score_01
)
--
SELECT a.sid,b.sname,b.sage,b.ssex,group_concat(distinct a.cid order by a.cid separator '_')AS concat_cid1
FROM sc AS a
INNER JOIN student AS b 
ON a.sid=b.sid
GROUP BY a.sid,b.sname,b.sage,b.ssex
-- 10、查询没有学过'张三'老师讲授的任一门课程的学生姓名
--  需要筛选出学过张三老师课程的学生，再进行排除
SELECT sname FROM student 
WHERE sid NOT IN 
(
SELECT DISTINCT b.sid FROM student AS b 
INNER JOIN sc AS c
ON b.sid=c.sid
INNER JOIN course d
ON c.cid=d.cid
INNER JOIN teacher e
ON d.tid=e.tid
WHERE tname='张三'
)
-- 11、查询两门及其以上不及格课程的同学的学号，姓名和平均成绩
SELECT b.sid,d.sname,AVG(c.score) FROM 
(
SELECT a.sid FROM 
     (  
SELECT sid,COUNT(DISTINCT cid)as count_cid FROM sc 
WHERE score<60
GROUP BY sid
     )AS a
WHERE a.count_cid>=2
)AS b
INNER JOIN sc AS c
ON b.sid=c.sid
INNER JOIN student AS d 
ON c.sid=d.sid
GROUP BY b.sid
-- 12、取出01课程分数小于60，按分数降序排列的学生信息
SELECT a.* FROM student AS a 
INNER JOIN sc AS b 
ON a.sid=b.sid
WHERE b.cid='01'
AND b.score<60
ORDER BY b.score DESC 
-- 13、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT a.*,b.cid,b.score,c.avg_score FROM student AS a
INNER JOIN sc AS b
ON a.sid=b.sid
INNER JOIN 
(
SELECT sid,AVG(score)AS avg_score
FROM sc  
GROUP BY sid
)AS c
ON b.sid=c.sid
ORDER BY avg_score DESC 
-- 14、查询各科成绩最高分、最低分、平均分：
-- 以如下形式显示：课程 ID，课程 name，最⾼高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
-- 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
-- 注意count和case when如果取的是0 ，count对于空值也是会计数的
SELECT a.cid,b.cname,
MAX(a.score),
MIN(a.score),
AVG(a.score),
COUNT(a.sid)AS '选修人数',
SUM(case when a.score>=60 then 1 else 0 end)/COUNT(a.sid)AS '及格率',
SUM(case when a.score>=70 and a.score<80 then 1 else 0 end)/COUNT(a.sid) AS '中等率',
SUM(case when a.score>=80 and a.score>=90 then 1 else 0 end)/COUNT(a.sid)AS '优良率',
SUM(case when a.score>80 then 1 else 0 end)/COUNT(a.sid)AS '优秀率'
 FROM sc AS a
INNER JOIN course AS b
ON a.cid=b.cid
GROUP BY a.cid,b.cname
ORDER BY '选修人数' DESC ,a.cid 
-- 15按各科的成绩进行排序，并显示排名，分数如果重复保留名次空缺
-- 保留名次空缺是指2个80，1个79分，79分排名是第三名。
-- 第一种解法：两个表自连接，通过cid相连，left join 选出A表比B表分数小的，计算比自己大的B表的个数，count+1
SELECT a.sid,a.cid,a.score,count(b.score)+1 AS ranking 
FROM sc AS a 
LEFT JOIN sc AS b 
ON a.cid=b.cid
AND a.score<b.score
GROUP BY a.cid,a.sid,a.score
ORDER BY a.cid,ranking
-- 第二种解法
-- rank() over是跳跃排序的，row_number()over排序唯一值，dense_rank连续排名。,group by 是分组，partition by 是分区
SELECT cid,sid,score,
rank () over (PARTITION BY cid ORDER BY score DESC)AS '排名'
FROM sc 
-- 16查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
SELECT a.sid,a.cid,a.score,count(a.score) AS ranking 
FROM sc AS a 
LEFT JOIN sc AS b 
ON a.cid=b.cid
AND a.score<b.score
GROUP BY a.cid,a.sid,a.score
ORDER BY a.cid,ranking
-- 第二种解法
SELECT a.sid,a.sum_score,dense_rank over(ORDER BY a.sum_score) FROM 
(
SELECT sid,sum(score)as sum_score
FROM sc GROUP BY sid
)AS a 
-- 17、统计各科成绩各分数段人数，课程编号、课程名称，100-85、85-70、70-60、60-0以及所占百分比
SELECT a.cid,b.cname,COUNT(a.sid),
SUM(case when a.score>=85 and a.score<=100 then 1 else 0 end)/COUNT(a.sid)AS '100-85占比',
SUM(case when a.score>=70 and a.score<85 then 1 else 0 end)/COUNT(a.sid)AS '85-70占比',
SUM(case when a.score>=60 and a.score<70 then 1 else 0 end)/COUNT(a.sid)AS '70-60占比',
SUM(case when a.score>=0 and a.score<60 then 1 else 0 end)/COUNT(a.sid)AS '0-60占比'
 FROM sc AS a 
INNER JOIN course AS b 
ON a.cid=b.cid
GROUP BY a.cid,b.cname
-- 18、查询各科成绩前三名的记录
SELECT a.sid,a.cid,a.score,count(b.score)+1 AS ranking 
FROM sc AS a 
LEFT JOIN sc AS b 
ON a.cid=b.cid
AND a.score<b.score
GROUP BY a.cid,a.sid,a.score
HAVING ranking<=3
ORDER BY a.cid,ranking
-- 19、查询每门课被选修的学生数
SELECT cid,COUNT(sid) FROM sc 
GROUP BY cid
-- 20、查询出只选修两门课程的学生学号和姓名
SELECT a.*,b.sname FROM 
(
SELECT sid,COUNT(cid)as count_cid FROM sc 
GROUP BY sid
)AS a 
LEFT join student b 
ON a.sid=b.sid
WHERE a.count_cid=2
-- 21、查询男生女生人数
SELECT 
ssex,COUNT(sid)
FROM 
student 
GROUP BY ssex
-- 22、查询名字中含有风字的学生信息
SELECT * FROM student 
WHERE sname LIKE '%风%'
-- 23、查询同名同姓学生名单，并统计同名人数
SELECT c.sname,COUNT(c.sid)
FROM 
(
SELECT a.sname,a.sid from	 student AS a 
INNER JOIN student AS b 
ON a.sname=b.sname
AND a.sid<>b.sid
)AS c 
GROUP BY c.sname
-- 另一种解法
SELECT sname,COUNT(sid)AS count_sid FROM student 
GROUP BY sname 
HAVING count_sid>1
-- 24、查询1990年出生的学生名单
SELECT * FROM student 
WHERE sage LIKE '%1990%'
-- 另一种解法
SELECT * FROM student 
WHERE YEAR(sage)='1990'
-- 25、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排序
SELECT cid,AVG(score)AS avg_score FROM sc 
GROUP BY cid
ORDER BY avg_score DESC ,cid
-- 26、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
SELECT a.sid,b.sname,AVG(a.score)AS avg_score FROM 
sc AS a 
INNER JOIN student  AS b 
ON a.sid=b.sid
GROUP BY sid 
HAVING avg_score>85
-- 27、查询课程名称为数学，且分数低于60的学生姓名和分数
SELECT a.sname,b.score FROM student AS a 
INNER JOIN sc AS b 
ON a.sid=b.sid
INNER JOIN course AS c 
ON b.cid=c.cid
WHERE c.cname='数学'
AND b.score<60
-- 28、查询所有学生的课程及分数情况(存在学生没成绩，没选课的情况)
SELECT a.sid,b.cid,b.score FROM student AS a
LEFT JOIN sc AS b 
ON a.sid=b.sid
-- 29、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
SELECT a.sname,b.cid,b.score FROM sc AS b
LEFT JOIN student AS a 
ON a.sid=b.sid
WHERE b.score>70
-- 30、查询存在不及格的课程
SELECT cid,sid,score FROM sc 
WHERE score<60
-- 31、查询课程编号为01且课程成绩在80分及以上的学生的学号和姓名
SELECT b.sid,b.sname,a.cid,a.score FROM sc AS a 
LEFT JOIN student AS b 
ON a.sid=b.sid
WHERE a.cid='01'
AND a.score>80
-- 32、求每门课程的学生人数
SELECT cid,COUNT(sid) FROM sc
GROUP BY cid
-- 33、成绩不重复的情况下，查询选修张三老师所授课程的学生中，成绩最高的学生信息以及成绩
-- limit是从表排序第一条开始取的，就算成绩相同也只取一条
SELECT a.sid,a.score,d.sname,d.ssex,d.sage FROM sc AS  a
INNER JOIN student AS d
ON a.sid=d.sid
INNER JOIN course AS b
ON a.cid=b.cid
INNER JOIN teacher c 
ON b.tid=c.tid
WHERE tname='张三'
ORDER BY a.score DESC 
LIMIT 1
-- 34、成绩有重复的情况下，查询选修张三老师所授课程的学生中，成绩最高的学生信息及其成绩
-- 查出最高的这个分数，然后限制得这个分数得所有学生,但仍然要限制课程是张三交的 
SELECT f.sid,q.sname,q.ssex,q.sage,f.cid,f.score FROM sc AS f
INNER JOIN student AS q
ON f.sid=q.sid
INNER JOIN course AS w
ON f.cid=w.cid
INNER JOIN teacher AS y
ON w.tid=y.tid
WHERE y.tname='张三'
AND  f.score IN 
(
SELECT MAX(a.score)AS max_score FROM sc AS  a
INNER JOIN student AS d
ON a.sid=d.sid
INNER JOIN course AS b
ON a.cid=b.cid
INNER JOIN teacher c 
ON b.tid=c.tid
WHERE tname='张三'
)
-- 35、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT distinct a.sid,a.cid,a.score FROM sc AS a 
INNER JOIN sc AS b 
ON a.sid=b.sid
AND a.score=b.score
AND a.cid<>b.cid
-- 36、未完成查询每门功课成绩最好的前两名
SELECT cid,count(sid) FROM sc
GROUP BY cid
-- 37、统计每门课程的学生选修人数(超过五人才统计）
SELECT cid,COUNT(sid) FROM sc
GROUP BY cid
HAVING COUNT(sid)>=5
-- 38、取出至少选修两门课的学生学号
SELECT sid,COUNT(cid) FROM sc
GROUP BY sid
HAVING COUNT(cid)>=2
-- 39、查询选修了全部课程的学生信息
-- having这个条件的用法
SELECT a.sid,a.sname,a.ssex,a.sage,COUNT(b.cid)AS count_cid FROM student AS a
INNER JOIN sc AS b
ON a.sid=b.sid
GROUP BY a.sid,a.sname,a.ssex,a.sage
HAVING count_cid =(SELECT count(cid) FROM course) 
-- 40、查询学生的年龄，只按年份来
SELECT sid,sname,YEAR(curdate())-YEAR(sage) FROM student
-- 41、按照出生日期来算，当前月<出生年月的月日则年龄减一
-- 时间差函数：
SELECT sid,sname,TIMESTAMPDIFF(YEAR,sage,NOW()) FROM student 
-- 42、查出本周过生日的学生
SELECT sid,sname FROM student 
WHERE week(sage)=week(curdate())
-- 43、查询下周过生日的学生
SELECT sid,sname FROM student 
WHERE week(sage)=week(curdate())+1
-- 44、查询本月过生日的学生
SELECT sid,sname FROM student 
WHERE MONTH(sage)=MONTH(curdate())
-- 45、查询下月过生日的学生
SELECT sid,sname FROM student
WHERE MONTH(sage)=MONTH(curdate())+1
第二个版本
首先创建数据：四个表
表student2-字段：s_id(K) ，s_name，s_birth ，s_sex
表course2-字段：c_id(k)，c_name，t_id
表teacher2-字段：t_id(k)，t_name
表score2-字段：s_id(k)，c_id，s_score
1 .查询"01"课程比"02"课程成绩高的学生的信息及课程分数 
考点：镶嵌表的自连接
select a.*,b.c_id,b.s_score from student2 a,score2 b where a.s_id=b.s_id
and a.s_id in (select a.s_id from score2 a,score2 b where a.s_id=b.s_id and 
a.c_id='01' and b.c_id='02' and a.s_score>b.s_score)
2.查询平均成绩大于60分的学生的学号和平均成绩
考点：按照score2表需要对s_id单个学生的各科平均成绩排序
select s_id,avg(s_score) from score2  group by s_id HAVING avg(s_score)>60 order by s_id
3.查询所有学生的学号、姓名、选课数、总成绩
考点：两个表的连接,通过s_id,聚合函数count,sum
select a.s_id,a.s_name,count(b.c_id),sum(b.s_score)
from student2 a,score2 b where a.s_id=b.s_id group by a.s_id
4.查询姓“猴”的老师的个数
考点：模糊查询，用like
select t_name,count(t_id) from teacher2 where t_name like '猴%'
5.查询没学过“猴子”老师课的学生的学号、姓名
考点：子查询not in (查询学过猴子老师的学生的学号、姓名)
select a.s_id,a.s_name from student2 a 
where a.s_id not in (select b.s_id from score2 b,course2 c,teacher2 d 
where b.c_id=c.c_id and c.t_id=d.t_id and d.t_name='猴子')
6.查询学过“猴子”老师所教的所有课的同学的学号、姓名
考点：学生要学过老师教的所有的课，count(学生学过的课)=count(老师教的课），老师教的c_id=学生学的c_id
select a.s_id,a.s_name from student2 a where a.s_id in 
(select b.s_id from score2 b,course2 c,teacher2 d 
where b.c_id=c.c_id and c.t_id=d.t_id and d.t_name='猴子' group by a.s_id 
HAVING count(c.c_id)=(select count(c.c_id) from course2 c,teacher2 d
where c.t_id=d.t_id and d.t_name='猴子' ))
7.查询学过编号为“001”的课程并且也学过编号为“002”的课程的学生的学号、姓名
考点：exist存在,后面接子查询where exists（select.),第二种更加简单，用in (子查询）
select a.s_id,a.s_name,b.c_id from student2 as a ,score2 as b where a.s_id=b.s_id
and b.c_id='01' and EXISTS (select c.c_id from score2 as c where b.s_id=c.s_id
and c.c_id='02')
select a.s_id,a.s_name,b.c_id,b.s_score from student2 a,score2 b where a.s_id=b.s_id
and a.s_id in (select a.s_id from score2 a,score2 b where a.s_id=b.s_id and 
a.c_id='01' and b.c_id='02' )
8.查询课程编号为“02”的总成绩
select c_id,sum(s_score) from score2 where c_id='02'
9.查询所有课程成绩小于60分的学生的学号、姓名
select a.s_id,a.s_name from student2 as a ,score2 as b where a.s_id=b.s_id
and b.s_score < 60 
10.查询没有学全所有课的学生的学号、姓名
考点：count（学生）<count(c_id)
select a.s_id,a.s_name from student2 as a ,score2 as b where a.s_id=b.s_id group by a.s_id having(count(c_id))<(select count(c_id) from course2)
11.查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名
考点:c_id in (c_id=01)
select a.s_id,a.s_name from student2 as a,score2 as b where a.s_id=b.s_id
and b.c_id in (select c_id from score2 where s_id='01')
12.查询和“1001”号同学所学课程完全相同的其他同学的学号
考点：要满足in，count(c_id)也要满足
select s_id from score2  where c_id in 
(select c_id from score2 where s_id='01') group by s_id 
HAVING COUNT(c_id)=(select count(*) from score2 where s_id='01')
13.把“SCORE”表中“猴子”老师教的课的成绩都更改为此课程的平均成绩
考点：更改update表 set 字段=(select) where 
update score2 as a set a.s_score=(select avg(b.s_score) from score2
as b where a.c_id=b.c_id ) where a.c_id in (select c_id from course2
as c INNER JOIN teacher2 as d on c.t_id=d.t_id where t_name='猴子') 
14.查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名
select a.s_id,a.s_name from student2 as a INNER JOIN score2 as b on 
a.s_id=b.s_id where c_id in (select c_id from score2 where s_id='02')
group by s_id having count(c_id) =(select count(*) from score2 where s_id='02')
17.按平均成绩从高到低显示所有学生的“数据库”（c_id='004'）、“企业管理”（c_id='001'）、“英语”（c_id='006'）三门的课程成绩，按如下形式显示：学生ID，数据库，企业管理，英语，有效课程数，有效平均分
考点：select (select a from A) from B,当A和B是相等的也需要区分
select s_id as 学生ID,
(select s_score from score2 as a where a.s_id=b.s_id and c_id='04') as 数据库,
(select s_score from score2 as a where a.s_id=b.s_id and c_id='01') as 企业管理,
(select s_score from score2 as a where a.s_id=b.s_id and c_id='06') as 英语,
count(*) as 有效课程数,avg(s_score) as 有效平均分 from score2 as b group by s_id ORDER BY avg(s_score) desc
18.查询各科成绩最高和最低的分： 以如下的形式显示：课程ID，最高分，最低分
select c_id as 课程ID,max(s_score) as 最高分,min(s_score) as 最低分 from score2
group by c_id
19.按各科平均成绩从低到高和及格率的百分数从高到低排列，以如下形式显示：
考点：sum(case when 条件s_score>60 then 结果 1 else 0 end)/cpunt(*)
select a.c_id as 课程号,a.c_name as 课程名,avg(b.s_score)as 平均成绩,
sum(case when s_score>=60 then 1 else 0 end)/count(*) as 及格百分比
from course2 as a INNER JOIN score2 as b on a.c_id=b.c_id group by a.c_id 
order by sum(case when s_score>=60 then 1 else 0 end)/count(*) DESC 
21.查询不同老师所教不同课程平均分从高到低显示
select a.t_name,b.c_id,avg(b.s_score)from score2 as b,course2 as c,teacher2 
as a where a.t_id=c.t_id and c.c_id=b.c_id group by b.c_id
order by avg(b.s_score) desc
23.使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称
考点:分段用case when,前面可以接聚合函数
select course2.c_id as 课程ID,course2.c_name as 课程名称,
sum(case when s_score between 85 and 100 then 1 else 0 end )as '85-100',
sum(case when s_score between 70 and 85 then 1 else 0 end )as '70-85',
sum(case when s_score between 60 and 70 then 1 else 0 end )as '60-70',
sum(case when s_score <60 then 1 else 0 end )as '60-'
from score2,course2 where score2.c_id=course2.c_id 
group by score2.c_id
24.查询学生平均成绩及其名次
考点：一、排序有三种函数，rank/dense_rank/row_number三种，具体用法如下：rank()over(partition by  order by) 排序为1,1,1,4；dense_rank() over (partition by  order by)排序为1,1,1,2;row_number()over (partition by order by)排序为1,2,3,4;二、order by 和where后面不能接聚合函数,from 后面加子查询，先找出学生的学号和对应的平均成绩，并对平均成绩进行命名a，之后才用order by;三、有聚合函数，考虑要用到group by 四、row_number()over 可以不加partition by ,加了的话是对内部再次进行排序。
select  a.s_id,a.amt,rownumber()over (order by a.amt desc) from (select s_id,avg(s_score) as amt from score2 group by s_id)as a
25.查询各科成绩前三名的记录（不考虑成绩并列情况）
考点：运行的顺序，where 和select是并列的，先运行from后面的子查询（先写出按照各科成绩排名的结果，没有聚合函数一般不用group by，用order by可以实现；这里需要按各科内排序用partition by；）命名为a ，再在外面写select * from ()as a where a.字段
select * from(
select c_id,s_score,row_number()over(partition by c_id order by s_score desc)as ti
 FROM  score2) a
where a.ti<=3
26.查询每门课程被选修的学生数
select c_id,count(s_id) from score2 group by c_id
27.查询出只选修了一门课程的全部学生的学号和姓名
select a.s_id,b.s_name,count(a.c_id)from student2 as b,score2 as a where a.s_id=b.s_id
group by a.s_id having count(a.c_id)=1
28.查询男生、女生人数
select count(s_sex) as 男生人数 from student2 where s_sex='男'
group by s_sex
select count(s_sex) as 女生人数 from student2 where s_sex='女'
group by s_sex
29.查询姓“张”的学生名单
select * from student2 where s_name like '张%'
30.查询同名同性学生名单并统计同名人数
select *,count(s_name) from student2 group by s_name
having count(s_name)>1
31.1981年出生的学生名单（注：Student表中s_birth列的类型是datetime）
考点：trunc(字段）=当前日期，只适用于oracle，trunc(字段,'mm')返回当月第一天，trunc(字段,'yy'）返回当年第一天；
select * from student2 where trunc(s_birth,'yyyy')='1981-01-01'
32.查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select a.s_id,a.s_name,avg(b.s_score)from student2 a,score2 b where a.s_id=
b.s_id group by a.s_id having avg(b.s_score)>85
33.查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
select avg(s_score) from score2 group by c_id order by avg(s_score),c_id DESC
34.查询课程名称为“数据库”且分数低于60的学生姓名和分数
select a.s_name,b.s_score from student2 as a,score2 as b,course2 as c
where a.s_id=b.s_id and b.c_id=c.c_id and c.c_name='数据库' and b.s_score<60
35.查询所有学生的选课情况
select a.s_id,a.s_name,c.c_name from student2 a,score2 b,course2 c
where a.s_id=b.s_id and b.c_id=c.c_id
36.查询任何一门课程成绩在70分以上的姓名、课程名称和分数
select a.s_name,c.c_name,b.s_score from student2 a,score2 b,course2 c
where a.s_id=b.s_id and b.c_id=c.c_id and b.s_score>70 group by a.s_id
37.查询不及格的课程并按课程号从大到小排列
select c_id from score2 where s_score<60 order by c_id
38.查询课程编号为003且课程成绩在80分以上的学生的学号和姓名
select a.s_id,a.s_name from student2 a ,score2 b where a.s_id=b.s_id
and b.s_score>80 and b.c_id='03'
39.查询选了课程的学生人数
select count(s_id) from score2
40.查询选修“猴子”老师所授课程的学生中成绩最高的学生姓名及其成绩
select a.s_name,max(b.s_score) from student2 as a,score2 as b,course2
as c,teacher2 as d
where a.s_id=b.s_id and b.c_id=c.c_id and c.t_id=d.t_id
and d.t_name='猴子' 
41.查询各个课程及相应的选修人数
select count(s_id) from score2 group by c_id
42.查询有2门不同课程成绩相同的学生的学号、课程号、学生成绩
select a.s_id,a.c_id,b.s_score from score2 as a ,score2 as b
where a.s_id=b.s_id and a.s_score=b.s_score and a.c_id<>b.c_id
43.查询每门课程成绩最好的前两名
select * from (select s_id,c_id,s_score,row_number()over(partition by c_id order by s_score desc)as ti from score2 ) as a where a.ti<=2
44.统计每门课程的学生选修人数(超过10人的课程才统计)。要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序
select c_id,count(s_id) from score2 group by c_id having count(s_id)>10
order by s_id desc,c_id asc
45.查询至少选修两门课程的学生学号
select s_id from score2 group by s_id having count(c_id)>=2
46.查询全部学生都选修的课程的课程号和课程名
select c_id,c_name from course2 where c_id in (select c_id from score2 group by c_id)
47.查询没学过“猴子”老师讲授的任一门课程的学生姓名
select s_name from student2 where s_id not in 
(select s_id from course2,teacher2,score2 where course2.t_id=teacher2.t_id
and score2.c_id=course2.c_id and t_name='猴子')
48.查询两门以上不及格课程的同学的学号及其平均成绩
select s_id,avg(s_score) from score2 where s_score< 60 group by s_id 
having count(c_id)>2
49.检索课程编号为“004”且分数小于60的学生学号，结果按按分数降序排列
select s_id from score2 where c_id='04' and s_score<60 order by s_score DESC
50.删除学生编号为“002”的课程编号为“001”的成绩
delete from score2 where s_id='02' and c_id='01'

2016-08-12 11:20齐明杰 客户经理
1、如果一行程序太长，能不能换行？
VB的程序代码是允许换行书写的，只要在每次换行的最后一个字符加上换行字符“_”就可以了。例如：
Sub PicMove()
Frm.Picture2.Left = Frm.Picture1.Left + _ ’加上换行符
Frm.Picture1.Width
End Sub
2、 如何在设计的时候清空存在的图片？
用鼠标点中该图片，在属性窗口中选中Picture属性，按Del键便可清空图片。
3、 Visual Basic 如何注释一段较长程序代码？
VB注释程序代码的符号是“注释：”，只要在某行程序前面加上“注释：”，就可以注释该行程序。但如果程序代码很长的时候，一行一行地注释令人觉得难以忍受。VB本身提供了这个功能，在主菜单“视图”选项的“工具栏”下，选中Edit，VB的界面会出现一排工具按钮，其中的手形图标按钮后的两个按钮用于“设置注释块”和“解除注释块”。
4、怎么实现鼠标一移上去就出现小提示窗口的功能？
VB 里每个控件都有ToolTipText属性，只要加上一行程序就可以了。
例如：Label1.ToolTipText = "这是提示！"。
5、 如何获得当前软件的运行磁盘目录和命令行参数？
VB里面有个系统对象叫App。App.Path就是当前软件的运行目录。而命令行参数存放在一个系统变量里面，叫Command。程序语句如下：
Label1.Caption=App.Path
Label2.Caption=Command$
6、我想换掉鼠标显示的形状，怎么做？
VB提供的系统控件一般都有MousePointer和MouseIcon属性。我们可以寻找自己喜欢的*.ICO,*.CUR文件，实现的程序如下：
Screen.MousePointer= 99 ’用户鼠标类型
Screen.MouseIcon=LoadPicture("C:\ABC\1.ICO") ’读取鼠标的图标文件
7、如何设置程序的错误出口？
On Error语句用于程序的错误出口处理。一般的处理方法有两种：
1) 遇到错误跳转到某一行程序去执行，On Error GoTo someline。
例如：
On Error GoTo ERR_LINE
...
Label1.Caption=“正确执行”
ERR_LINE:
...
Label1.Caption=“出错了！”
2) 遇到错误之后忽略当前错误，继续执行，On Error Resume Next。
例如：
On Error Resume Next
...
Label1.Caption=“不管对不对都要执行”
...
8、怎样获得键盘输入和判断敲键的Asc川害贬轿撞计鳖袭搏陋ii值？
把窗体的KeyPreview属性设置成True，然后在Form_KeyPress 事件里编写程序代码如下：
Private Sub Form_KeyPress(KeyAscii As Integer)
Me.Caption = Str(KeyAscii) ‘取得键盘输入的字符
...
End Sub
9、我希望窗体一运行就在屏幕的中央，怎么实现？
VB的系统对象Screen记录了当前显示模式的高度和宽度，可以利用这个值来设......余下全文>>
热心网友

VB.NET LABEL 如何实现自动换行
 yrx1983 LV12 2013-06-26 
不限制长度。一堆感叹号的时候怎么解！
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

添加以下代码，判断焦点位于datagridview或者datagridview处于可编辑状态时，通过重载PRocesscmdkey方法来拦截Return按键，回车时，Return TRUE 。  

 Protected Overrides Function ProcessCmdKey(ByRef msg As Message, ByVal keyData As Keys) As Boolean
    If Me.bspdList.Enabled = True Then
        If keyData = Keys.Return Then
            If bspdList.RowCount = 0 Then
                Return MyBase.ProcessCmdKey(msg, keyData)
            End If
            Call xcmdKeyF1_Click(Nothing, Nothing)
            Return True
        End If
    Else
        Return MyBase.ProcessCmdKey(msg, keyData)
    End If
End Function