超有用的sql查询语句。项目、面试都有用

简介:

超有用的sql查询语句。项目、面试都有用


create database cstp;
use cstp;
/*1:建立学生表*/
create table student (
   学号 char(3) primary key,
   姓名 char(4),
   性别 char(2),
   年龄 int,
   班级 char(5)
);
insert into student values('108','曾华','男',19,'95033');
insert into student values('105','匡明','男',20,'95031');
insert into student values('107','王丽','女',20,'95033');
insert into student values('101','李军','男',19,'95033');
insert into student values('109','王芳','女',22,'95031');
insert into student values('103','陆君','男',20,'95031');
/*2:建立教师表*/
create table teacher(
   教师号 char(3) primary key,
   姓名 char(4),
   性别 char(2),
   年龄 int ,
   级别 char(6),
   专业 char(8)
);
insert into teacher values('804','李成','男',42,'副教授','计算机系');
insert into teacher values('856','张旭','男',35,'讲师','电子工程');
insert into teacher values('825','王萍','女',28,'助教','计算机系');
insert into teacher values('831','刘冰','女',25,'助教','电子工程');
/*3:建立课程表*/
create table course (
   课程号 char(5) primary key,
   课程名 char(10),
   教师号 char(3),
   foreign key(教师号) references teacher(教师号)
);
insert into course values('3-105','计算机导论','825');
insert into course values('3-245','操作系统','804');
insert into course values('6-166','数字电路','856');
insert into course values('9-888','高等数学','831');
/*4:建立选课表*/
create table sc(
   学号 char(3),
   课程号 char(5),
   primary key(学号,课程号),
   成绩 int,
   foreign key(学号) references student(学号),
   foreign key(课程号) references course(课程号)
);
insert into sc values('103','3-245',86);
insert into sc values('105','3-245',75);
insert into sc values('109','3-245',68);
insert into sc values('103','3-105',92);
insert into sc values('105','3-105',88);
insert into sc values('109','3-105',76);
insert into sc values('101','3-105',64);
insert into sc values('107','3-105',91);
insert into sc values('108','3-105',78);
insert into sc values('101','6-166',85);
insert into sc values('107','6-166',79);
insert into sc values('108','6-166',81);
/*5:所有表内容*/
select * from student;
select * from course;
select * from teacher;
select * from sc;

所有表信息如下:

student(学生)表
经典sql 查询语句 适合初学者 - 易建锋 - yijianfeng_vip的博客
 
course(课程)表
经典sql 查询语句 适合初学者 - 易建锋 - yijianfeng_vip的博客
 
teacher(教师)表
经典sql 查询语句 适合初学者 - 易建锋 - yijianfeng_vip的博客
 
sc(选课)表
经典sql 查询语句 适合初学者 - 易建锋 - yijianfeng_vip的博客
 

作业题:
1.查询选修课程'3-105'且成绩在60到80之间的所有记录。
注释:用于指定某个范围使用between and,也可以使用and连接符;
答案:
法1:select * from sc where 课程号='3-105' and 成绩 between 60 and 80;
法2:select * from sc where 课程号='3-105' and 成绩 > 60 and 成绩 < 80;
2.查询成绩为85、86或88的记录。
注释:用于制定某个集合使用 in 关键字,也可以使用 or 连接符;
答案:
法1:select * from sc where 成绩=85 or 成绩=86 or 成绩=88;
法2:select * from sc where 成绩 in(85,86,88);
3.查询'95031'班的学生人数。
注释:count(*)用于计算结果总数;
答案:
select count(班级) as 学生人数 from student where 班级='95031';
4.查询最低分大于70,且最高分小于90的学号列。
注释:having后面跟聚合函数:avg,min,max,count;having语句只能跟在:group by语句后面;
答案:
select 学号,min(成绩)as 最低分,max(成绩)as 最高分 from sc group by 学号 having min(成绩)>70 and max(成绩) < 90;
5.查询至少有5名学生选修并以3开头的课程的平均成绩。
注释:group by 语句置于where语句后面表示根据什么条件来分组;
答案:
select avg(成绩) as 平均成绩 from sc where 课程号 like '3%' group by 课程号 having count(课程号) >= 5;
6.查询平均分大于80分的学生的成绩表
注释:having后面跟聚合函数avg();
答案:
select * from sc group by 学号 having avg(成绩) > 80;
7.查询'95033'班每个学生所选课程的平均分。
注释:此题是根据 '95033'班学生的学号进行的分组,使用where语句限制group by语句的分组条件;
答案:
select 学号,avg(成绩) as 平均成绩 from sc where 学号 in (select 学号 from student where 班级 = '95033') group by 学号;
8.以选修 '3-105'为例,查询成绩高于'109'号同学的所有同学的记录。
注释:此题使用典型的嵌套查询,层层深入;
答案:
select * from student where 学号 in (select 学号 from sc where 课程号='3-105' and 成绩 >
(select 成绩 from sc where 学号='109' and 课程号='3-105'));
9.查询与学号为'108'的同学同岁的所有学生的学号、姓名和年龄。
注释:当查询的结果集返回只有一个时关键字in的作用等价于'='的作用,但是注意'='只能用与返回结果集只有一个,而 in 可以有多个结果;
答案:
法1:select 学号,姓名,年龄 from student where 年龄 = (select 年龄 from student where 学号='108');
法2:select 学号,姓名,年龄 from student where 年龄 in (select 年龄 from student where 学号='108');
10.查询'张旭'教师任课的课程号,选修其课程学生的学号和成绩。
注释:此题使用了表的连接:inner join,将多个表连接在一起组成一个新的表,标的连接的条件是必须存在相同的列;
答案:
法1:select 课程号 from course where 教师号=(select 教师号 from teacher where 姓名='张旭'); select 学号,成绩 from sc where 课程号 in(select 课程号 from course where 教师号 in 
(select 教师号 from teacher where 姓名='张旭'));
法2:select teacher.姓名 as 教师姓名,course.课程号,student.姓名 as 学生姓名,student.学号,成绩 from  teacher inner join (course inner join (sc inner join student on student.学号=sc.学号) on course.课程号=sc.课程号) on course.教师号=teacher.教师号 where  teacher.姓名='张旭';
11.查询选修其课程的学生人数多于5人的教师姓名。
注册:此题采用嵌套查询和连接表两种方法,表现出了同样的效果;
答案:
法1:select 姓名 as 教师姓名 from teacher where 教师号 in (select 教师号 from course where 课程号 in (select 课程号 from sc group by 课程号 having count(*) > 5));
法2:select 姓名 as 教师姓名 from teacher inner join (course inner join sc on course.课程号=sc.课程号) on teacher.教师号=course.教师号 group by sc.课程号 having count(*) > 5;
12.查询'计算机系'与'电子工程系'不同职称的教师的姓名和职称。
注释:不同职称意味着分组后的返回结果集只有一个;
答案:
select 姓名 as 教师姓名,级别 as 职称 from teacher group by 级别 having count(*) = 1;
13.查询选修编号为'3-105'课程且成绩至少高于选修编号为'3-245'课程的同学的课程号、学号 、成绩并按成绩从高到低次序排列。
注释:any 表示任意一个;
答案:
select * from sc where 课程号='3-105' and 成绩 > any (select 成绩 from sc where 课程号='3-245') order by 成绩 desc;
14.查询选修编号为'3-105'课程且成绩高于选修编号为'3-245'课程的同学的课程号、学号 、成绩。
注释:all 表示所有;
答案:
select * from sc where 课程号='3-105' and 成绩 > all (select 成绩 from sc where 课程号='3-245') order by 成绩 desc;
15.列出所有教师和同学的姓名 、性别 、年龄。
注释:此题将所有表连接在一起:
答案:
select student.姓名 as 学生姓名,student.性别 as 学生性别,student.年龄 as 学生年龄,
teacher.姓名 as 教师姓名,teacher.性别 as 教师性别,teacher.年龄 as 教师年龄 from
student inner join (sc inner join (course inner join teacher on course.教师号=teacher.教师号)
on sc.课程号=course.课程号) on student.学号=sc.学号;
16.查询成绩比'3-105'课程的平均成绩低的学生的成绩表。
答案:
select * from sc where 成绩 < (select avg(成绩) from sc where 课程号='3-105') and 课程号='3-105';
17.查询成绩比该课程平均成绩低的学生的成绩表。
注释:此题是所有题中最难的,下面的方法很经典,请认真琢磨,先按照课程进行分组,查出小于各门课程平均成绩的所有成绩;
答案:
select * from sc where 成绩 < any (select avg(成绩) from sc group by 课程号) order by 课程号 asc;
18.列出所有任课教师的姓名和专业。
注释:姓名和专业涉及两个表course表和teacher表,只需连接这两个表就能解决此题了;
答案:
select teacher.姓名 as 教师姓名,专业 from teacher where 教师号 in (select 教师号 from course where 课程号 in (select 课程号 from sc group by 课程号));
19.列出所有未讲课教师的姓名和专业。
注释:解答同上题;
答案:
select teacher.姓名 as 教师姓名,专业 from teacher where 教师号 not in 
(select 教师号 from course where 课程号 in (select 课程号 from sc group by 课程号));
20.列出至少有2名男生的班号。
注释:根据性别='男'这个条件来对student表进行分组就可以了;
答案:
select 班级 as 班号 from student where 性别='男' group by 班级 having count(*) >= 2;
21.查询不姓'王'的学生记录。
答案:
法1:select *  from student where 姓名 not like '王%';
法2:select * from student where 姓名 not in (select 姓名 from student where 姓名like '王%');
22.查询每门课最高分的学生的学号、课程号、成绩。
注释:此题的技巧在于根据最高分来查成绩表;
答案:
select * from sc where 成绩 in (select max(成绩) from sc group by 课程号);
23.查询与'李军'同性别并同班的同学名字。
注释:嵌套查询;
答案:
select 姓名 from student where 性别=(select 性别 from student where 姓名='李军') and 
班级=(select 班级 from student where 姓名='李军');
24.查询'男'教师及其所上的课程。
注释:先分析此题涉及的表;
答案:
select 姓名 as 教师名,课程名 as 课程 from teacher inner join course on teacher.教师号=course.教师号 where 性别='男';

25.查








      本文转自yjflinchong 51CTO博客,原文链接:http://blog.51cto.com/yjflinchong/1165017,如需转载请自行联系原作者




相关文章
|
18天前
|
SQL 缓存 监控
大厂面试高频:4 大性能优化策略(数据库、SQL、JVM等)
本文详细解析了数据库、缓存、异步处理和Web性能优化四大策略,系统性能优化必知必备,大厂面试高频。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
大厂面试高频:4 大性能优化策略(数据库、SQL、JVM等)
|
16天前
|
SQL 监控 关系型数据库
SQL语句当前及历史信息查询-performance schema的使用
本文介绍了如何使用MySQL的Performance Schema来获取SQL语句的当前和历史执行信息。Performance Schema默认在MySQL 8.0中启用,可以通过查询相关表来获取详细的SQL执行信息,包括当前执行的SQL、历史执行记录和统计汇总信息,从而快速定位和解决性能瓶颈。
|
27天前
|
SQL 存储 缓存
如何优化SQL查询性能?
【10月更文挑战第28天】如何优化SQL查询性能?
87 10
|
21天前
|
SQL 关系型数据库 MySQL
|
2月前
|
SQL 数据库 开发者
功能发布-自定义SQL查询
本期主要为大家介绍ClkLog九月上线的新功能-自定义SQL查询。
|
2月前
|
SQL 关系型数据库 MySQL
Go语言项目高效对接SQL数据库:实践技巧与方法
在Go语言项目中,与SQL数据库进行对接是一项基础且重要的任务
79 11
|
2月前
|
SQL 移动开发 Oracle
SQL语句实现查询连续六天数据的方法与技巧
在数据库查询中,有时需要筛选出符合特定时间连续性条件的数据记录
|
2月前
|
SQL Java 数据库连接
如何使用`DriverManager.getConnection()`连接数据库,并利用`PreparedStatement`执行参数化查询,有效防止SQL注入。
【10月更文挑战第6天】在代码与逻辑交织的世界中,我从一名数据库新手出发,通过不断探索与实践,最终成为熟练掌握JDBC的开发者。这段旅程充满挑战与惊喜,从建立数据库连接到执行SQL语句,再到理解事务管理和批处理等高级功能,每一步都让我对JDBC有了更深的认识。示例代码展示了如何使用`DriverManager.getConnection()`连接数据库,并利用`PreparedStatement`执行参数化查询,有效防止SQL注入。
107 5
|
2月前
|
SQL 数据挖掘 数据库
SQL查询每秒的数据:技巧、方法与性能优化
id="">SQL查询功能详解 SQL(Structured Query Language,结构化查询语言)是一种专门用于与数据库进行沟通和操作的语言
|
2月前
|
SQL 移动开发 大数据
SQL语句查询连续六天满足条件的记录
在数据库管理和数据分析中,我们经常需要查询符合特定时间范围内连续几天的记录