SQL练习与进阶

最新推荐文章于 2024-07-24 21:50:19 发布
最新推荐文章于 2024-07-24 21:50:19 发布
阅读量923 收藏
点赞数
分类专栏： 数据库
原文链接：https://www.jianshu.com/p/476b52ee4f1b
版权

 

                                        SQL练习与进阶

         知道和亲自写出来还是不一样的，经常后台开发，要对简单的增删改查的sql语句掌握并运用，但有时候，项目的需求需要很好的sql来进行支撑，那在需要的时候进行sql网上搜索就会显得格外的浪费时间，所以需要对sql提前装备，并查缺补漏。

一、基本sql练习

1.1 表结构（user、 user_ext）

1.2 sql 练习

（1）查询出来 user 表中 score 大于 80 的所有数据

SELECT * FROM user u WHERE u.score >80

（2）查询表 user 中字段 gender 为 '男' 的所有内容

SELECT u.* FROM user u WHERE u.gender = '男';

（3）查询表 user 中字段 students 开头为'小'字的内容

SELECT u.* FROM user u WHERE u.students LIKE '小%';

（4）查询表 user 中字段 students 开头不是为'小'字的内容

SELECT u.* FROM user u WHERE u.students NOT LIKE '小%';

（5）查询表 user 中字段 students 包含'亮'字的所有内容

SELECT u.* FROM user u WHERE u.students LIKE '%亮%';

（6）查询表 user 中字段 score 为100,79,40的所有内容

SELECT u.* FROM user u WHERE u.score in (100, 79, 40);

（7）查询表 user 中字段 score 大于95 或者 gender 为女性的所有内容

SELECT u.* FROM user u WHERE u.score > 95 OR u.gender = '女';

（8）合并查询表 user 和表 user_ext 中 id 相同的所有数据

SELECT * FROM user u, user_ext e WHERE u.id = e.id;

（9）获取表 user 中字段 score 大于 60 的内容数量

SELECT COUNT(*) FROM user u WHERE u.score > 60;

（10）获取表 user 中字段 score 的平均值

SELECT AVG(u.score) AS avg FROM user u;

（11） 获取表 user 中字段 score 的总分数

SELECT SUM(u.score) AS sum FROM user u;

（12）获取表 user 中字段 score 的最大值

SELECT MAX(u.score) AS max FROM user u;

（13）获取表 user 中字段 score 的最小值

SELECT MIN(u.score) AS min FROM user u;

（14）获取表 user_ext 中所有不同的字段 age 并设置字段别名为'年龄'

SELECT DISTINCT(u.score) AS '年龄' FROM user_ext u;

（15）获取表 user_ext 中的所有数据并且按照字段 weight 进行倒序排序

SELECT e.* FROM user_ext e ORDER BY e.weight DESC;

（16）通过左连接 获取表 user(别名t1) 和表 user_ext(别名t2) 中字段 id 相同的数据，其中字段 age 大于9，并仅返回 id、students、age、weight 这几个字段的数据

二、复杂sql练习

2.1 表结构

 

A.学生表Student   

create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');

B.课程表Course

create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

C.教师表Teacher

create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

D.成绩表 SC

create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

2.2 SQL查询

（1）查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

SELECT * FROM student s RIGHT JOIN(
SELECT t1.SId, class1, class2 FROM
 (SELECT SId, score AS class1 FROM sc WHERE CId = '01') AS t1, 
 (SELECT SId, score AS class2 FROM sc WHERE CId = '02') AS t2
where t1.SId = t2.SId AND t1.class1 > t2.class2
)r
ON r.SId = s.SId

SELECT * from (
SELECT t1.SId, class1, class2 FROM
 (SELECT SId, score AS class1 FROM sc WHERE CId = '01') AS t1, 
 (SELECT SId, score AS class2 FROM sc WHERE CId = '02') AS t2
where t1.SId = t2.SId AND t1.class1 > t2.class2
)r
LEFT JOIN student s ON s.SId = r.SId

（2）查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
这里只用根据学生ID把成绩分组，对分组中的score求平均值，最后在选取结果中AVG大于60的即可. 注意，这里必须要给计算得到的AVG结果一个alias.（AS ss）
得到学生信息的时候既可以用join也可以用一般的联合搜索

SELECT s.SId, s.Sname, r.ss from student s RIGHT JOIN(
select SId, AVG(score) as ss from sc  
    GROUP BY SId 
	HAVING AVG(score) > 60
)r
ON s.SId = r.SId

select student.SId,sname,ss from student,(
    select SId, AVG(score) as ss from sc  
    GROUP BY SId 
    HAVING AVG(score)> 60
    )r
where student.sid = r.sid;

（3）查询在 SC 表存在成绩的学生信息

思路：查询存在成绩即在成绩表中有该学生，所以条件是：学生表中的ID等于成绩表中的学生ID。只要求学生信息，所以学生要去重。

SELECT DISTINCT s.* FROM student s, sc c WHERE s.SId = c.SId

（4）查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
       （4.1）联合查询不会显示没选课的学生：

select student.sid, student.sname,r.coursenumber,r.scoresum
from student,
(select sc.sid, sum(sc.score) as scoresum, count(sc.cid) as coursenumber from sc 
group by sc.sid)r
where student.sid = r.sid;

       （4.2）如要显示没选课的学生(显示为NULL)，需要使用join:

select s.sid, s.sname,r.coursenumber,r.scoresum
from (
    (select student.sid,student.sname 
    from student
    )s
    left join 
    (select 
        sc.sid, sum(sc.score) as scoresum, count(sc.cid) as coursenumber
        from sc 
        group by sc.sid
    )r
   on s.sid = r.sid
);

（5）查询有成绩的学生列表（in 和exist的用法）

SELECT * FROM student s WHERE s.SId in (SELECT sc.SId FROM sc)

SELECT * FROM student s WHERE EXISTS(
SELECT sc.SId FROM sc WHERE sc.SId = s.SId);

（6）查询学过「张三」老师授课的同学的信息

SELECT s.* from student s, teacher t, sc c, course u WHERE 
t.TId = u.TId 
AND u.CId = c.CId 
AND c.SId = s.SId 
AND t.Tname = '张三';

（7）*查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select student.sid, student.sname, AVG(sc.score) from student,sc
where 
    student.sid = sc.sid and sc.score<60
group by sc.sid 
having count(*)>1;

（8）*查询各科成绩最高分、最低分和平均分：

以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select 
sc.CId ,
max(sc.score)as 最高分,
min(sc.score)as 最低分,
AVG(sc.score)as 平均分,
count(*)as 选修人数,
sum(case when sc.score>=60 then 1 else 0 end )/count(*)as 及格率,
sum(case when sc.score>=70 and sc.score<80 then 1 else 0 end )/count(*)as 中等率,
sum(case when sc.score>=80 and sc.score<90 then 1 else 0 end )/count(*)as 优良率,
sum(case when sc.score>=90 then 1 else 0 end )/count(*)as 优秀率 
from sc
GROUP BY sc.CId
ORDER BY count(*)DESC, sc.CId ASC

（9）按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
这一道题有点tricky，可以用变量，但也有更为简单的方法，即自交（左交）
用sc中的score和自己进行对比，来计算“比当前分数高的分数有几个”。

select a.cid, a.sid, a.score, count(b.score)+1 as rank
from sc as a 
left join sc as b 
on a.score<b.score and a.cid = b.cid
group by a.cid, a.sid,a.score
order by a.cid, rank ASC;

（10）统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 

select course.cname, course.cid,
sum(case when sc.score<=100 and sc.score>85 then 1 else 0 end) as "[100-85]",
sum(case when sc.score<=85 and sc.score>70 then 1 else 0 end) as "[85-70]",
sum(case when sc.score<=70 and sc.score>60 then 1 else 0 end) as "[70-60]",
sum(case when sc.score<=60 and sc.score>0 then 1 else 0 end) as "[60-0]"
from sc left join course
on sc.cid = course.cid
group by sc.cid;

欢迎订阅关注公众号（JAVA和人工智能）

                                                           获取更多免费书籍、资源、视频资料 

           

 

JAVA和人工智能
关注
专栏目录
SQL进阶练习题
mengtongxch的博客
06-21 175
建表语句： CREATE TABLE Greatests (key CHAR(1) PRIMARY KEY, x INTEGER NOT NULL, y INTEGER NOT NULL, z INTEGER NOT NULL);
SQL进阶练习一
YouriL的博客
03-21 196
SQL进阶练习一 **题目一 select s.sname from s join sc on s.son=sc.son join c on sc.sno=c.sno where c.cteacher <>"小易" select sname, avg(score) from s join sc on s.sno=sc.sno where count(case when sc.s...
sql高级进阶
12-06
sql高级进阶sql高级进阶sql高级进阶sql高级进阶sql高级进阶
SQL进阶及查询练习
SSR_J_Study的博客
08-11 707
SQL进阶及查询练习1.mysql编码问题2.mysql备份与恢复数据库3.约束3.1 主键约束（唯一标识）3.2 主键自增长3.3 非空约束3.4 唯一约束3.5 概念模型3.5.1 对象模型3.5.2 关系模型3.6 外键约束3.7 数据库一对一关系3.8 数据库多对多关系4.多表查询4.1 合并结果集4.2 连接查询4.2.1 内连接4.2.2 外连接4.3 子查询 1.mysql编码问题 ...
1-5练习题-SQL进阶教程
Tracy785的博客
08-21 276
1-5 外连接的用法 ●练习题1-5-1 ：先连接还是先聚合 在“在交叉表里制作嵌套式表侧栏”部分里，我们通过聚合将DATA视图和MASTER视图转换为一对一的关系之后进行了连接操作。采用这种做法时，代码的确比较好理解，但是这就需要创建两个临时视图，性能并不是很好。请想办法改善一下代码，尽量减少临时视图 select MASTER.age_class age_class,MASTER.sex_...
数据分析+hive基础及进阶SQL练习题+面试
最新发布
08-12
（1）HiveSQL基础SQL练习50题 （2）HiveSQL业务强化练习30题 （3）Hive进阶六大场景题目分析 1）求单月访问次数和总访问次数 2）HiveSQL影评分析案例 3）学生课程成绩 4）求每一年最大气温的那一天+温度 5）求学生...
SQL进阶：综合练习题-10道经典
UNknowmer的博客
09-27 815
进行测试前，需要先下载建表语句和插数脚本。脚本文件名前面的序号表示用到该数据集的题目序号，例如1-7market data.sql表示第1题和第7题用到了该数据，可以在建好库之后，在mysql使用source+文件地址 执行sql脚本，但是下载的文件名有空格，会报错路径无法找到，我删除空格规避错误。source的时候速度会有点慢，可以先set sql_log_bin=off;关闭二进制日志和关闭事务自动提交，然后用事务的方式提交，start transaction;souce 文件.sql;
50道SQL进阶练习题，非常实用
SnakeMonster
10-28 2303
这里写目录标题建表语句添加数据表关系参考答案1.查询" 01 “课程⽐” 02 "课程成绩⾼的学⽣的信息及课程分数2.查询同时存在" 01 “课程和” 02 "课程的情况3.查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )4.查询不存在" 01 “课程但存在” 02 "课程的情况5.查询平均成绩⼤于等于 60 分的同学的学⽣编号和学⽣姓名和平均成绩6.查询在 SC 表存在成绩的学⽣信息7.查询所有同学的学⽣编号、学⽣姓名、选课总数、所有课程的总成绩(没成绩的显示为 n
SQL强化练习工具
12-26
SQL强化练习工具，新手可用，专门用于SQL命令的练习、进阶……
牛客网SQL训练4—SQL进阶挑战
毛媛媛
12-27 1480
牛客网刷题4—SQL进阶挑战
SQL进阶练习题26-30
巧克力人生的博客
02-25 445
#SQL进阶练习题26-30 大背景和建表、插入语句就不啰嗦了，参考第一篇。 四张表概要： 学生表 student(sid,sname,sage,ssex) --sid 学生编号,sname 学生姓名,sage 出生年月,ssex 学生性别 课程表 course(cid,cname,tid) --cid 课程编号,cname 课程名称,tid 教师编号 教师表 teacher(tid,tname...
MySQl经典50题，全部都会做就是大佬了！
程序员小章的博客
09-14 3430
MySQL经典50题，全部都会做就是大佬了！ 文章目录MySQL经典50题，全部都会做就是大佬了！创建表结构建表语句添加数据表关系题目参考答案1.查询" 01 "课程⽐" 02 "课程成绩⾼的学⽣的信息及课程分数2.查询同时存在" 01 "课程和" 02 "课程的情况3.查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )4.查询不存在" 01 "课程但存在" 02 "课程的情况5.查询平均成绩⼤于等于 60 分的同学的学⽣编号和学⽣姓名和平均成绩6.查询在 SC 表存在
1-10练习题-SQL进阶教程
Tracy785的博客
08-29 365
练习题1-10-1 ：单重集合与多重集合的一般化 在“单重集合与多重集合”部分，我们只针对material一个字段检查了集合中是否存在重复值。这次我们考虑一下推而广之，针对多个字段检查是否存在重复 我们在原来的表中加上“原产国”这样一个字段，作为测试数据 请从表中查出材料和原产国两个字段都重复的生产地 select center from Materials2 group by center ...
【SQL】超全！以练代学，练完这篇，搞定MySQL语句！（进阶篇）
Liu_y_xin的博客
07-24 1018
以练代学，练完这篇，搞定MySQL语句！进阶篇，包括索引、视图、函数、存储过程、触发器等知识点
SQL高级进阶
zhangmary的博客
04-15 1024
l  限定返回的行数TOPSELECT TOP number | percent 列名FROM 表名SELECT TOP 2 * FROMPersons                                      SELECT TOP 50 PERCENT * FROMPersonsl  LIKE在WHERE字句中搜索制定模式 通配符等(%可以定义通配符，模式中缺少的字母)SELEC...
SQL 进阶50题
Nina的博客
06-06 1020
【代码】SQL 进阶50题。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值