mysql-sql-练习题-1

环境

Windows cmd（普通用户/管理员）

mysql -uroot -p

mysql版本，模式（可自定义）

select version(),@@global.sql_mode;

注释

MySQL和Hive通用注释：–

建表 5张

建库

-- 创建数据库 指定编码格式
create database mysql_exercise charset = utf8;

学生表

create table student(
s_id varchar(10),
  s_name varchar(20),
  s_age date,
s_sex varchar(10));

insert into student
values  
  ('01','赵雷','1990-01-01','男'),
  ('02','钱电','1990-12-21','男'),
  ('03','孙风','1990-05-20','男'),
  ('04','李云','1990-08-06','男'),
  ('05','周梅','1991-12-01','女'),
  ('06','吴兰','1992-03-01','女'),
  ('07','郑竹','1989-07-01','女'),
  ('08','王菊','1990-01-20','女');

课程表

create table course(
c_id varchar(10),
  c_name varchar(20),
  t_id varchar(10));

insert into course
values  
  ('01' , '语文' , '02'),
  ('02' , '数学' , '01'),
  ('03' , '英语' , '03');

教师表

create table teacher(
t_id varchar(10),
t_name varchar(20));

insert into teacher
values  
  ('01' , '张三'),
  ('02' , '李四'),	
  ('03' , '王五');

分数表

create table score(
s_id varchar(10),
c_id varchar(10),
score varchar(10));

insert into score 
values 
  ('01' , '01' , 80),
  ('01' , '02' , 90),
  ('01' , '03' , 99),
  ('02' , '01' , 70),
  ('02' , '02' , 60),
  ('02' , '03' , 80),
  ('03' , '01' , 80),
  ('03' , '02' , 80),
  ('03' , '03' , 80),
  ('04' , '01' , 50),
  ('04' , '02' , 30),
  ('04' , '03' , 20),
  ('05' , '01' , 76),
  ('05' , '02' , 87),
  ('06' , '01' , 31),
  ('06' , '03' , 34),
  ('07' , '02' , 89),
  ('07' , '03' , 98);

总表

-- 查询语句建表
create table total(
  select 
    a.s_id,a.s_name,a.s_age,a.s_sex,b.c_id,ifnull(b.score,0) score,c.c_name,c.t_id,d.t_name
  from student a
  left join score b on a.s_id = b.s_id
  left join course c on b.c_id = c.c_id
  left join teacher d on c.t_id = d.t_id);

语法书写顺序

两门及以上，及格同学学号，平均成绩，格式：平均成绩降序，课程ID升序，结果显示第2-4行

select
  s_id,count(1) count_pass, -- 及格科数
  cast(avg(score) as decimal(5,2)) score_avg -- 平均成绩
from score
where score >= 60 -- 优先行级过滤，减少数据量
group  by s_id -- 分组（右侧括号图）
having count(1) >= 2 -- 组内过滤
order by any_value(score_avg) desc,any_value(c_id) -- 多字段排序；非分组字段满足语法模式
limit 1,3 -- 限制输出行数：从第1+1行开始，输出3行
;

in

单字段 in 单结果集
多字段 in 多结果集

学过/没学过

学过课程"01"，没学过课程"02"的同学

select s_id,c_id,score
from score
where
  s_id in (select s_id from score where c_id = '01') -- 学过课程01的同学
  and
  s_id not in (select s_id from score where c_id = '02') -- 没学过课程02的同学
;

完全相同

和同学"01"学习课程完全相同的同学

select s_id,group_concat(c_id) c_id
from score
where c_id in (select c_id from score where s_id = '01') -- 至少一门相同
group by s_id
having count(1) = (select count(1) from score where s_id = '01') -- 门数相同
;