sql实际操作练习

最新推荐文章于 2023-07-04 10:16:44 发布

司*琦

最新推荐文章于 2023-07-04 10:16:44 发布

阅读量1.1k

点赞数

本文链接：https://blog.csdn.net/qq_30774825/article/details/105183619

版权

创建数据库,表,以及插入表格内容

show databases;
create database Students default charset "utf8";  #创建数据库
use students   #使用数据库
show tables;
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' , '女');

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');

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

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);

操作题目

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

#不包含有几门不及格
select a.*,b.Sname from(select SId, avg(score) from sc where score <60 group by SId having count(CId) >1 )  as a inner join student as b on a.SId =b.SId;
#包含有几门不及格
select a.* ,b.Sname from (select SId,count(CId),avg(score) from SC where score < 60 group by SId having count(CId)>1)as a inner join Student as b on a.SId =b.SId;

检索" 01 "课程分数小于 60，按分数降序排列的学生信息

语法

降序:order by .. desc
升序:order by .. asc

select a.*,b.score from (select SId,score from sc where score <60 and CId="01") as b inner join student as a on a.SId=b.SId order by score desc;

按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

重点:

功能：将group by产生的同一个分组中的值连接起来，返回一个字符串结果
语法:group_concat( [DISTINCT] 要连接的字段 [Order BY 排序字段 ASC/DESC] [Separator ‘分隔符’] )

#不显示具体学科名,单表查询
 select SId,group_concat(CId,':',score),avg(score) from SC group by SId order by avg(score) desc;
 #显示具体学科名,连表查询
 select b.SId,group_concat(a.Cname,':',b.score),avg(b.score) from SC as b inner join Course as a on a.CId = b.CId group by b.SId order by avg(b.score) desc;

查询 1990 年出生的学生名单

select * from Student where Sage > '1990-01-01 00:00:00' and Sage < '1991-01-01 00:00:00';

查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

select CId,avg(score) from SC group by CId order by avg(score) desc ,CId asc;

select CId,group_concat(score),avg(score) from SC group by CId order by avg(score) desc ,CId asc;

select c.Cname,c.CId,group_concat(c.score),avg(c.score) from (select b.Cname,a.CId,a.score from sc as a inner join course as b on a.CId=b.CId) as c group by c.Cname;

在这里插入图片描述

查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

select a.SId,a.Sname,b.avg from (select SId,avg(score) as avg from sc group by SId having avg(score)>=85) as b inner join student as a on a.SId =b.SId;

如果报错:

ERROR 1055 (42000): Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'students.sc.score' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

在这里插入图片描述
解决方法:
mysql8以前的:

set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

mysql8之后

set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

原因: MySQL8取消了NO_AUTO_CREATE_USER:
如果出现:

ERROR 1231 (42000): Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'

在这里插入图片描述
则删除里面的NO_AUTO_CREATE_USER

查询各科成绩前三名的记录

注意:

SUBSTRING_INDEX (str,delim,count)，str:要处理的字符串、delim:分隔符、count:计数

select CId ,substring_index(group_concat(score order by score desc),",",3) as score from sc group by CId;
select * from SC s where 3 > (select count(c.score) from SC as c where s.score < c.score and s.CId =c.CId) order by s.CId ,s.score desc ;

上面两种写法得到的结果不一样,第一种是确定只取前三名或者说只取前三个,而下面一种写法则是取前三名,如果第三名和第四名成绩一样,则第三名/第四名都显示

查询下周过生日的学生(超纲)

select SId,Sname,Sage from student where week(now())+1 = week(Sage)

查询出生在6月的学生

select SId,Sname,Sage from student where month(Sage)= 6;

MySQL Week函数介绍:

WEEK(date, mode);
date是要获取周数的日期。
mode是一个可选参数，用于确定周数计算的逻辑。它允许您指定本周是从星期一还是星期日开始，返回的周数应在0-52之间或0-53之间(用一年天数除以7)。

如果忽略mode参数，默认情况下WEEK函数将使用default_week_format系统变量的值。在我们的服务器中，default_week_format的默认值为0，下表格说明了mode参数如何影响WEEK函数：
在这里插入图片描述

WEEK函数返回一个周数，遵循ISO 8601:1988

查询名字中含有「雷」字的学生信息

select * from student where Sname like "%雷%";

查询姓赵的所有学生信息

 select * from student where Sname like "赵%";#所有以赵开头的学生信息
 select * from student where Sname like "赵_"; #只能查到赵后面接一个字符的名字

查询各科成绩最高分、最低分和平均分

#显示学科名称.连表查询
select a.Cname,b.max_score,b.min_score,b.avg_score from(select CId,max(score) as max_score,min(score) as min_score,avg(score) as avg_score from sc group by CId) as b inner join course as a where a.CId=b.CId;  
#不显示学科名称,只显示学科编号,单表查询
select CId,max(score),min(score),avg(score) from sc group by CId;

按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

select *, rank() over(partition by Cid order by score desc) from sc;

在这里插入图片描述

按各科成绩进行排序，并显示排名， score 重复时合并名次(1,1,2,3,4…)

select *, (dense_rank() over(partition by Cid order by score desc)) as r from sc;

在这里插入图片描述

查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数

select * from (select * from SC where CId = 01) as a inner join (select * from SC where CId = 02) as b on a.SId =b.SId where a.score > b.score and a.SId =b.SId;