一.简单查询
练习:创建数据库的表
如创建course表,其余的score、student、teacher一样
1)‘新建表’-->设置表中的字段名称、字段类型等
2)点击新建记录后,即可在表中添加数据
#sql语句考察点#
INSERT INTO `<表名>` ('<字段1>','<字段2>', ...) VALUES ('<字段1的值>',''<字段2的值>', ...)
INSERT INTO `course` (`课程号`, `课程名称`, `教师号`) VALUES ('0001', '语文', '0002')
INSERT INTO `course` (`课程号`, `课程名称`, `教师号`) VALUES ('0002, '数学', '0001')
INSERT INTO `course` (`课程号`, `课程名称`, `教师号`) VALUES ('0003', '英语', '0003')
/*若插入的数据众多,用sql语句插更便捷*/
二.汇总分析
练习:汇总分析
#查询课程编号为“0002”的总成绩
#查询选了课程的学生人数
练习:分组
#查询各科成绩最高和最低的分
#查询每门课程被选修的学生数
#男生和女生人数
练习:分组结果的条件
#查询平均成绩大于60分学生的学号和平均成绩
#查询至少选修两门课程的学生学号
#查询同名同姓学生名单并统计同名人数
练习:对分组结果排序
#查询不及格的课程并按课程号从大到小排列
#查询每门课程的平均成绩,结果按平均成绩升序排列。平均成绩相同时,按课程号降序排列
#检索课程编号为“0004”且分数小于60的学生学号,结果按按分数降序排列
# 统计每门课程的学生选修人数(超过2人的课程才统计),要求输出课程号和选修人数,查询结果按人数降序排序,若人数相同,按课程号升序排序
#查询两门以上不及格课程的同学的学号及其平均成绩
三.复杂查询
练习:子查询
#查询所有课程成绩小于60分学生的学号、姓名
#查询没有学全所有课的学生的学号、姓名
练习:日期函数
#1990年出生的学生名单
【面试题类型】topN问题
例如:
如何找到每个类别下用户最喜欢的产品是哪个?
如果找到每个类别下用户点击最多的5个商品是什么?
这类问题其实就是常见的:分组取每组最大值、最小值,每组最大的N条(top N)记录。
我们可以使用分组(group by)和汇总函数得到每个组里的一个值(最大值,最小值,平均值等,但是无法得到成绩最大值所在行的数据,故需要用关联子查询。
·分组取每组最大值
#案例:按课程号分组取成绩最大值所在行的数据
·分组取每组最小值
#案例:按课程号分组取成绩最小值所在行的数据
- 每组最大的N条记录
案例:查询各科成绩前两名的记录
使用union all 将每组选出的数据合并到一起
四.多表查询
联结类型总结:
练习:左联结 left join
不在表里数据使用的联结
#查询不近视的学生
#找出所有从不订购任何东西的客户
#查询所有学生的学号、姓名、选课数、总成绩
#查询平均成绩大于80分学生的学号、姓名、平均成绩
#查询学生的选课情况:学号,姓名,课程号,课程名称--三表联结
练习:case表达式
case when<判断表达式>then<表达式>
when<判断表达式>then<表达式>
···
else<表达式>
end /*不能省略*/
#查询出每门课程的及格人数和不及格人数
# 使用分段[100-85],[85-70],[70-60],[<60]来统计各分段成绩的人数,课程号,课程名称
练习:内联结inner join
#查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名|
练习:行列互换
#使用sql实现将该表行转列为下面的表结构
五.如何提高SQL查询的效率
1.实际工作中,尽量避免使用*来获取数据
2.where子句比较符号左侧避免函数,否则导致数据库引擎进行全表扫描,从而增加运行时间
例如:where 成绩 + 5 › 90
优化方法:
where 成绩 › 90 – 5(表达式在比较符号的右侧)
3. 尽量避免使用in和not in,否则导致数据库引擎进行全表扫描,从而增加运行时间
例如: select 学号, 成绩 from 成绩表 where 学号 in (8, 9)
优化方法:
select 学号, 成绩 from 成绩表 where 学号 between 8 and 9
4. 尽量避免使用or,否则导致数据库引擎进行全表扫描,从而增加运行时间
例如:select 学号 from 成绩表 where 成绩 = 88 or 成绩 = 89
优化方法:
select 学号 from 成绩表 where 成绩 = 88
union
select 学号 from 成绩表 where 成绩 = 89
5.使用limit子句限制返回的数据行数。若前台只需要显示15行数据,而你的查询结果集返回了1万行,那么这适合最好使用limt子句来限制查询返回的数据行数。
6.始终牢记SQL语句的书写规则,避免因书写不规范导致运行报错。