mysql总结 by 刘彬
2020-04-09 本文已影响0人
彬彬刘
2020年4月6日加入数据蛙，第一部分内容是mysql，虽然SQL是我最熟悉的工具，但是怀着把技能学扎实的想法，按耐下心中的急躁，2天时间撸完了这部分的学习，还是有不少收获的，补充了除查询外的各种操作语句和概念知识，学会了新的技巧——变量在排序上的应用，对得起自己的认真对待
接下来的python基础的学习，结合SQL这部分的经验，python部分我将尝试直接从练习题入手，遇到不熟悉的知识再补视频，节约时间争取1天结束这部分的巩固
mysql总结
1 mysql介绍
1.1RDBS
1.2MYSQL
1.2.1数据类型
1.2.2约束
1.2.3数据库设计
1.3SQL
2 mysql使用
2.1数据库操作
-- 一、mysql 数据库的操作
-- 连接数据库
mysql -uroot -p密码
-- 不显示密码
mysql -uroot -p
输入密码
-- 退出数据库
quit/exit
-- sql语句最后需要有分号;结尾
-- 显示数据库版本 version
select version();
-- 显示时间
select now();
-- 查看当前使用的数据库
select database();
-- 查看所有数据库
show databases;
-- 创建数据库
-- create database 数据库名 charset=utf8;
create database test_01;
create database test_01 charset=utf8;
-- 查看创建数据库的语句
-- show create database ....
show create database test_01;
-- 使用数据库
-- use 数据库的名字
use test_01;
-- 删除数据库
-- drop database 数据库名;
drop database test_01;
-- 二、数据表的操作
-- 查看当前数据库中所有表
show tables;
-- 创建表
-- int unsigned 无符号整形
-- auto_increment 表示自动增长
-- not null 表示不能为空
-- primary key 表示主键
-- default 默认值
-- create table 数据表名字 (字段 类型 约束[, 字段 类型 约束]);
create table xxx (
id int unsigned primary key auto_increment not null,
name varchar(20) not null
);
-- 查看表结构
-- desc 数据表的名字;
desc xxx;
-- 创建 classes 表(id、name)
create table classes (
id int unsigned primary key not null auto_increment,
name varchar(20) not null
);
-- 创建 students 表(id、name、age、high (decimal)、gender (enum)、cls_id)
create table students (
id int unsigned primary key not null auto_increment,
name varchar(20) not null,
age int unsigned,
high decimal(5,2),
gender enum("男","女","中性","保密") default "保密",
cls_id int unsigned
);
-- 查看表的创建语句
-- show create table 表名字;
show create table xxx;
--表字段的操作
-- 修改表-添加字段 mascot (吉祥物)
-- alter table 表名 add 列名 类型;
alter table classes add chongwu varchar(20) default "一辆宝马车";
-- 修改表-修改字段：不重命名版
-- alter table 表名 modify 列名 类型及约束;
alter table classes modify chongwu varchar(20) default "一栋房子";
-- 修改表-修改字段：重命名版
-- alter table 表名 change 原名 新名 类型及约束;
alter table classes change chongwu mascot varchar(20) default "一个美梦";
-- 修改表-删除字段
-- alter table 表名 drop 列名;
alter table classes drop mascot;
-- 删除表
-- drop table 表名;
-- drop database 数据库;
drop table xxx;
drop database xxx;
-- 三、数据的操作，增删改查(curd)
-- 增加
-- 全列插入
-- insert [into] 表名 values(...)
-- 主键字段 可以用 0 null default 来占位
-- 向classes表中插入 一个班级
insert into classes values(1,'zhansan');
-- 向students表插入 一个学生信息，auto_increment 如果需要默认自增，可以填0，null，default
insert into students values(1,'list',18,178,'男',001);
insert into students values(default,'wangwu',17,170,'男',001);
insert into students values(default,'zhaoqi',19,160,2,002);
insert into students values(default,'zhaoba',120,160,default,003);
-- 部分插入
-- insert into 表名(列1,...) values(值1,...)
insert into students(name) values('老李');
-- 多行插入
insert into students values(0,'老刘',40,160,'男',003),(0,'老王',20,180,default,003);
-- 修改
-- update 表名 set 列1=值1,列2=值2... where 条件;
-- 全部修改
update students set gender='中性';
-- 按条件修改
update students set gender='女' where id=2;
-- 按条件修改多个值
update students set gender='男' where cls_id=3;
-- 查询基本使用
-- 查询所有列
-- select * from 表名;
select * from students;
---定条件查询
select * from students where cls_id=1;
-- 查询指定列
-- select 列1,列2,... from 表名;
select name,gender from students where cls_id=1;
-- 可以使用as为列或表指定别名
-- select 字段[as 别名] , 字段[as 别名] from 数据表 where ....;
select name as '姓名',gender as '性别' from students;
-- 字段的顺序
select gender as '性别',name as '姓名' from students;
-- 删除
-- 物理删除
-- delete from 表名 where 条件
delete from students where id=1;
-- 逻辑删除
-- 用一个字段来表示 这条信息是否已经不能再使用了
-- 给students表添加一个 is_delete 字段 bit 类型
alter table students add is_delete bit default 0;
update students set is_delete=1 where id=3;
-- 数据库备份与恢复
-- mysqldump –uroot –p 数据库名 > python.sql;
-- mysql -uroot –p 新数据库名 < python.sql;
2.2查询
连接
子查询
函数
- 聚合函数
- 时间函数
分页
变量
排序
2.3索引
2.4视图
2.5事务
3 mysql练习
3.1销售记录数据练习
-- 第一步：建表
CREATE TABLE test6_orderinfo(
orderid INT PRIMARY KEY NOT NULL ,
userid INT,
isPaid VARCHAR(10),
price FLOAT,
# float decimal
-- 是一个浮点型 一个是定点型
-- 都可以设置（m,d）总长度和小数点后面的长度，如果不设置float会根据实际数据入库，decimal默认（10，0）将没有小数，
-- 在进行sum等计算是float会出现很多位小数的情况decimal不会
paidTime VARCHAR(30));
CREATE TABLE test6_userinfo(
userid INT PRIMARY KEY,
sex VARCHAR(10),
birth DATE);
# 第二步，导入数据
-- C:\Users\liubi\Downloads\order_info_utf.csv
-- C:\Users\liubi\Downloads\user_info_utf.csv
#\要改成/才能被识别 而且地址不能有中文
LOAD DATA LOCAL INFILE 'c:/Users/liubi/Downloads/order_info_utf.csv' INTO TABLE test6_orderinfo FIELDS TERMINATED BY ','
LOAD DATA LOCAL INFILE 'c:/Users/liubi/Downloads/user_info_utf.csv' INTO TABLE test6_userinfo FIELDS TERMINATED BY ','
;
#做日期处理才能使用日期函数
UPDATE `datafrog`.`test6_orderinfo` a SET a.`paidTime` = REPLACE(a.`paidTime`,'/','-');
UPDATE `datafrog`.`test6_orderinfo` a SET a.`paidTime` = REPLACE(a.`paidTime`,'\r',''); # 后面STR_TO_DATE报错 包含换行符\r
#SELECT * FROM `datafrog`.`test6_orderinfo` a WHERE a.`paidTime` ='\r'
UPDATE `datafrog`.`test6_orderinfo` a SET a.`paidTime` = STR_TO_DATE(a.`paidTime`,'%Y-%m-%d %H:%i') WHERE a.`paidTime` <>'';
-- 统计不同月份的下单人数
SELECT LEFT(a.`paidTime`,7),COUNT(DISTINCT a.`userid`)
FROM `datafrog`.`test6_orderinfo` a
WHERE a.`isPaid` = '已支付'
GROUP BY LEFT(a.`paidTime`,7)
;
-- 统计用户3月份的回购率和复购率
#回购
SELECT w1.month,COUNT(w1.month),COUNT(w2.month),COUNT(w2.month)/COUNT(w1.month)
FROM (
SELECT a1.`userid`,DATE_FORMAT(a1.`paidTime`,'%Y-%m-01') AS MONTH
FROM `datafrog`.`test6_orderinfo` a1
WHERE a1.`isPaid` = '已支付'
GROUP BY a1.`userid`,DATE_FORMAT(a1.`paidTime`,'%Y-%m-01')
) w1
LEFT JOIN (
SELECT a2.`userid`,DATE_FORMAT(a2.`paidTime`,'%Y-%m-01') AS MONTH
FROM `datafrog`.`test6_orderinfo` a2
WHERE a2.`isPaid` = '已支付'
GROUP BY a2.`userid`,DATE_FORMAT(a2.`paidTime`,'%Y-%m-01')
)w2 ON w1.userid = w2.userid AND w1.month = DATE_SUB(w2.month,INTERVAL 1 MONTH)
GROUP BY w1.month
;
#复购
SELECT MONTH,COUNT(IF(w.paid_count>1,1,NULL)),COUNT(IF(w.paid_count>1,1,NULL))/COUNT(w.`userid`)
FROM (
SELECT LEFT(a.`paidTime`,7) AS MONTH ,a.`userid`,COUNT(*) paid_count
FROM `datafrog`.`test6_orderinfo` a
WHERE a.`isPaid` = '已支付'
GROUP BY LEFT(a.`paidTime`,7) ,a.`userid`
) w
GROUP BY MONTH
;
-- 统计男女用户的消费频次是否有差异
SELECT a.`sex`,SUM(w.paid_count)/COUNT(a.`userid`),AVG(paid_count)
FROM `datafrog`.`test6_userinfo` a
INNER JOIN (
SELECT b.`userid`,COUNT(*) paid_count
FROM `datafrog`.`test6_orderinfo` b
WHERE b.`isPaid` = '已支付'
GROUP BY b.`userid`
) w ON w.userid = a.`userid`
WHERE a.`sex` <> ''
GROUP BY a.`sex`
;
-- 统计多次消费的用户，第一次和最后一次的消费间隔是多久
SELECT b.`userid`,MAX(b.`paidTime`),MIN(b.`paidTime`),DATEDIFF(MAX(b.`paidTime`),MIN(b.`paidTime`))
FROM `datafrog`.`test6_orderinfo` b
WHERE b.`isPaid` = '已支付'
GROUP BY b.`userid` #having MAX(b.`paidTime`)<>MIN(b.`paidTime`)
SELECT DATEDIFF('2019-01-01',NOW())
;
-- 统计不同年龄段用户的消费金额是否有差异
SELECT CASE WHEN YEAR(NOW())-YEAR(a.`birth`) < 10 THEN '[0-10)'
WHEN YEAR(NOW())-YEAR(a.`birth`) < 20 THEN '[10-20)'
WHEN YEAR(NOW())-YEAR(a.`birth`) < 30 THEN '[20-30)'
WHEN YEAR(NOW())-YEAR(a.`birth`) < 40 THEN '[30-40)'
WHEN YEAR(NOW())-YEAR(a.`birth`) < 50 THEN '[40-50)'
WHEN YEAR(NOW())-YEAR(a.`birth`) < 60 THEN '[50-60)'
WHEN YEAR(NOW())-YEAR(a.`birth`) < 70 THEN '[60-70)'
WHEN YEAR(NOW())-YEAR(a.`birth`) < 80 THEN '[70-80)'
WHEN YEAR(NOW())-YEAR(a.`birth`) < 90 THEN '[80-90)'
ELSE'[90-100)' END age,
SUM(w.price),AVG(price)
FROM `datafrog`.`test6_userinfo` a
INNER JOIN (
SELECT b.`userid`,SUM(b.`price`) price
FROM `datafrog`.`test6_orderinfo` b
WHERE b.`isPaid` = '已支付'
GROUP BY b.`userid`
) w ON w.userid = a.`userid`
WHERE a.`birth` > '1900-01-01'
GROUP BY (CASE WHEN YEAR(NOW())-YEAR(a.`birth`) < 10 THEN '[0-10)'
WHEN YEAR(NOW())-YEAR(a.`birth`) < 20 THEN '[10-20)'
WHEN YEAR(NOW())-YEAR(a.`birth`) < 30 THEN '[20-30)'
WHEN YEAR(NOW())-YEAR(a.`birth`) < 40 THEN '[30-40)'
WHEN YEAR(NOW())-YEAR(a.`birth`) < 50 THEN '[40-50)'
WHEN YEAR(NOW())-YEAR(a.`birth`) < 60 THEN '[50-60)'
WHEN YEAR(NOW())-YEAR(a.`birth`) < 70 THEN '[60-70)'
WHEN YEAR(NOW())-YEAR(a.`birth`) < 80 THEN '[70-80)'
WHEN YEAR(NOW())-YEAR(a.`birth`) < 90 THEN '[80-90)'
ELSE'[90-100)' END)
;
-- 统计消费的二八法则，消费的top20%用户，贡献了多少额度
SELECT COUNT(w.userid),SUM(price)
FROM (
SELECT b.`userid`,SUM(b.`price`) price
FROM `datafrog`.`test6_orderinfo` b
WHERE b.`isPaid` = '已支付'
GROUP BY b.`userid`
ORDER BY price DESC
#limit 17130#(select count(distinct b.`userid`)* 0.2 from `datafrog`.`test6_orderinfo` b where b.`isPaid` = '已支付')
) w
;
3.2学生成绩45题练习
# 数据准备
CREATE TABLE `datafrog`.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-05-20' , '男');
INSERT INTO Student VALUES('04' , '李云' , '1990-08-06' , '男');
INSERT INTO Student VALUES('05' , '周梅' , '1991-12-01' , '女');
INSERT INTO Student VALUES('06' , '吴兰' , '1992-03-01' , '女');
INSERT INTO Student VALUES('07' , '郑竹' , '1989-07-01' , '女');
INSERT INTO Student VALUES('09' , '张三' , '2017-12-20' , '女');
INSERT INTO Student VALUES('10' , '李四' , '2017-12-25' , '女');
INSERT INTO Student VALUES('11' , '李四' , '2017-12-30' , '女');
INSERT INTO Student VALUES('12' , '赵六' , '2017-01-01' , '女');
INSERT INTO Student VALUES('13' , '孙七' , '2018-01-01' , '女');
CREATE TABLE `datafrog`.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 `datafrog`.Teacher(TId VARCHAR(10),Tname VARCHAR(10));
INSERT INTO Teacher VALUES('01' , '张三');
INSERT INTO Teacher VALUES('02' , '李四');
INSERT INTO Teacher VALUES('03' , '王五');
CREATE TABLE `datafrog`.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);
-- 练习题目https://www.jianshu.com/p/acacd6f4baf6
-- 1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
SELECT d.*,c.`CId`,c.`score`
FROM sc c
LEFT JOIN `student` d ON d.`SId` = c.`SId`
WHERE c.`SId` IN (
SELECT a.`SId`
FROM sc a
INNER JOIN sc b ON b.`SId` = a.`SId` AND b.`CId` = '02' AND b.`score` < a.`score`
WHERE a.`CId` = '01');
-- 1.1 查询同时存在" 01 "课程和" 02 "课程的情况
SELECT *
FROM sc a
WHERE a.`SId` IN (
SELECT a.`SId`
FROM sc a
INNER JOIN sc b ON b.`SId` = a.`SId` AND b.`CId` = '02'
WHERE a.`CId` = '01'
)
;
-- 1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null ) 1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
-- 2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT a.*,w.avg_s
FROM `student` a
INNER JOIN (
SELECT b.`SId`,AVG(b.`score`) avg_s
FROM sc b
GROUP BY b.`SId` HAVING avg_s>= 60
) w ON w.SId =a.SId
;
-- 3.查询在 SC 表存在成绩的学生信息
SELECT *
FROM `student` a
WHERE a.`SId` IN (SELECT DISTINCT b.`SId` FROM sc b )
;
-- 4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECT a.`SId`,a.`Sname`,COUNT(DISTINCT b.`CId`) class,SUM(b.`score`) score
FROM `student` a
LEFT JOIN sc b ON b.`SId` = a.`SId`
GROUP BY a.`SId`
-- 4.1 查有成绩的学生信息
SELECT a.*
FROM `student` a
INNER JOIN sc b ON b.`SId` = a.`SId`
GROUP BY a.`SId`
-- 5.查询「李」姓老师的数量
SELECT COUNT(*)
FROM `student` a
WHERE a.`Sname` LIKE '李%';
-- 6.查询学过「张三」老师授课的同学的信息
SELECT d.*
FROM `teacher` a
INNER JOIN `course` b ON b.`TId` = a.`TId`
INNER JOIN sc c ON c.`CId` = b.`CId`
INNER JOIN `student` d ON d.`SId` = c.`SId`
WHERE a.`Tname` = '张三'
;
-- 7.查询没有学全所有课程的同学的信息
SELECT *
FROM `student` a
WHERE a.`SId` IN (
SELECT b.`SId`
FROM sc b
GROUP BY b.`SId` HAVING COUNT(DISTINCT b.`CId`)<3
)
;
-- 8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
SELECT c.*
FROM (
SELECT *
FROM sc a
WHERE a.`SId` = '01'
) w
INNER JOIN `sc` b ON b.`CId` = w.CId AND b.`SId` <> '01'
LEFT JOIN `student` c ON c.`SId` = b.`SId`
GROUP BY c.`SId`
-- 9.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
SELECT c.*
FROM `student` c
WHERE c.`SId` IN (
SELECT b.`SId`
FROM sc a
LEFT JOIN `sc` b ON b.`CId` = a.CId AND b.`SId` <> '01'
WHERE a.`SId` = '01'
GROUP BY b.`SId` HAVING COUNT(DISTINCT b.`CId`)=3
) ;
-- 10.查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT c.*
FROM `student` c
WHERE c.`SId` NOT IN (
SELECT a.`SId`
FROM sc a
INNER JOIN `course` b ON b.`CId` =a.`CId`
INNER JOIN `teacher` d ON d.`TId` = b.`TId` AND d.`Tname` = '张三'
);
-- 11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT a.`SId`,b.`Sname`,AVG(a.`score`) avg_s
FROM sc a
INNER JOIN `student` b ON b.`SId` = a.`SId`
WHERE a.`SId` IN (
SELECT w.`SId`
FROM sc w
WHERE w.`score` < 60
GROUP BY w.`SId` HAVING COUNT(DISTINCT w.`CId`)>1
)
GROUP BY a.`SId`
;
-- 12.检索" 01 "课程分数小于 60，按分数降序排列的学生信息
SELECT a.*,b.`score`
FROM `student` a
INNER JOIN sc b ON b.`SId` = a.`SId` AND b.`score` <60 AND b.`CId` = '01'
ORDER BY b.`score` DESC ;
-- 13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT a.*,w.avg_s
FROM sc a
LEFT JOIN (
SELECT a.`SId`,AVG(a.`score`) avg_s
FROM sc a
GROUP BY a.`SId`
) w ON w.SId = a.SId
ORDER BY w.avg_s DESC
;
-- 14.查询各科成绩最高分、最低分和平均分：
-- 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT a.`CId`,b.Cname,MAX(a.score) max_s,MIN(a.score) min_s,AVG(a.score)avg_s,COUNT(DISTINCT a.SId)people,
COUNT(CASE WHEN a.`score` >= 90 THEN 1 ELSE NULL END) /COUNT(DISTINCT a.SId) '优秀率',
COUNT(CASE WHEN a.`score` >= 80 THEN 1 ELSE NULL END)/COUNT(DISTINCT a.SId) '优良率',
COUNT(CASE WHEN a.`score` >= 70 THEN 1 ELSE NULL END)/COUNT(DISTINCT a.SId) '中等率',
COUNT(CASE WHEN a.`score` >= 60 THEN 1 ELSE NULL END)/COUNT(DISTINCT a.SId) '及格率'
FROM `sc` a
LEFT JOIN `course` b ON b.CId = a.CId
GROUP BY a.`CId`
ORDER BY people DESC, a.`CId` ASC
-- 15.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
SELECT w.*,
@rank:=@rank+1 AS rank
FROM sc w,(SELECT @rank:= 0) b
ORDER BY w.score DESC
-- 15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
SELECT w.*,
@rank:=IF(@s = w.score,@rank,@rank+1) AS rank,
@s := w.score
FROM sc w,(SELECT @rank:= 0,@s:=0) b
ORDER BY w.score DESC
-- 16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺
SELECT w.*,
@rank:=@rank+1 AS rank
FROM (
SELECT a.`SId`,SUM(a.`score`) total
FROM sc a
GROUP BY a.`SId`
ORDER BY total DESC
) w,(SELECT @rank:= 0) b
;
-- 16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
SELECT w.*,
@rank:=IF(@s = w.total,@rank,@rank+1) AS rank,
@s := w.total
FROM (
SELECT a.`SId`,SUM(a.`score`) total
FROM sc a
GROUP BY a.`SId`
ORDER BY total DESC
) w,(SELECT @rank:= 0,@s:=0) b
;
-- 17.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
SELECT a.`CId`,
COUNT(CASE WHEN a.`score` >= 0 AND a.`score` < 60 THEN 1 ELSE NULL END) '[60-0]人数',
COUNT(CASE WHEN a.`score` >= 0 AND a.`score` < 60 THEN 1 ELSE NULL END) /COUNT(*) '[60-0]占比',
COUNT(CASE WHEN a.`score` >= 60 AND a.`score` < 70 THEN 1 ELSE NULL END) '[70-60]人数',
COUNT(CASE WHEN a.`score` >= 60 AND a.`score` < 70 THEN 1 ELSE NULL END) /COUNT(*) '[70-60]占比',
COUNT(CASE WHEN a.`score` >= 70 AND a.`score` < 85 THEN 1 ELSE NULL END) '[85-70]人数',
COUNT(CASE WHEN a.`score` >= 70 AND a.`score` < 85 THEN 1 ELSE NULL END) /COUNT(*) '[85-70]占比',
COUNT(CASE WHEN a.`score` >= 85 AND a.`score` < 100 THEN 1 ELSE NULL END) '[100-85]人数',
COUNT(CASE WHEN a.`score` >= 85 AND a.`score` < 100 THEN 1 ELSE NULL END) /COUNT(*) '[100-85]占比'
FROM `sc` a
GROUP BY a.`CId`
-- 18.查询各科成绩前三名的记录
SELECT m.*
FROM (
SELECT
a.*,
@rank:= IF(@c=a.CId,@rank+1,1) AS rank ,
@c:= a.CId
FROM sc a ,(SELECT @rank:=0,@c:= NULL) b
ORDER BY a.`CId`,a.`score` DESC
) m
WHERE m.rank <= 3;
SELECT m.*
FROM (
SELECT
a.*,
(SELECT COUNT(DISTINCT b.score) FROM sc b WHERE b.CId = a.`CId` AND b.score >= a.`score`) AS rank
FROM sc a
ORDER BY a.`CId`,a.`score` DESC
) m
WHERE m.rank <= 3
-- 19.查询每门课程被选修的学生数
SELECT a.`CId`,COUNT(DISTINCT a.`SId`)
FROM `sc` a
GROUP BY a.`CId`;
-- 20.查询出只选修两门课程的学生学号和姓名
SELECT b.`SId`,b.`Sname`
FROM`student` b
WHERE b.`SId` IN (
SELECT a.`SId`
FROM sc a
GROUP BY a.`SId` HAVING COUNT(DISTINCT a.`CId`)=2
)
-- 21.查询男生、女生人数
SELECT a.`Ssex`,COUNT(*)
FROM `student` a
GROUP BY a.`Ssex`;
-- 22.查询名字中含有「风」字的学生信息
SELECT *
FROM `student` a
WHERE a.`Sname` LIKE '%风%';
-- 23.查询同名同性学生名单，并统计同名人数
SELECT a.`Sname`,COUNT(*)
FROM `student` a
GROUP BY a.`Sname` HAVING COUNT(*)>1;
-- 24.查询 1990 年出生的学生名单
SELECT *
FROM `student` a
WHERE YEAR(a.`Sage`) = 1990;
-- 25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT a.`CId`,AVG(a.`score`) a_score
FROM `sc` a
GROUP BY a.`CId`
ORDER BY a_score DESC,a.CId ASC;
-- 26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
SELECT a.*,w.avg_s
FROM `student` a
INNER JOIN (
SELECT b.`SId`,AVG(b.`score`) avg_s
FROM sc b
GROUP BY b.`SId` HAVING avg_s>= 85
) w ON w.SId =a.SId
;
-- 27.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
SELECT c.`Sname`,b.`score`
FROM `course` a
INNER JOIN sc b ON b.`CId` = a.`CId` AND b.`score` <60
INNER JOIN `student` c ON c.`SId` = b.`SId`
WHERE a.`Cname` = '数学';
-- 28.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
SELECT *
FROM `student` a
LEFT JOIN sc b ON b.`SId` = a.`SId`
-- 29.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
SELECT a.`Sname`,d.`Cname`,b.`score`
FROM `student` a
INNER JOIN sc b ON b.`SId` = a.`SId`
LEFT JOIN `course` d ON d.`CId` = b.`CId`
WHERE NOT EXISTS (
SELECT *
FROM sc c
WHERE c.`score` < 70 AND c.`SId` = a.`SId`
);
-- 30.查询不及格的课程
SELECT *
FROM sc c
WHERE c.`score` < 60
;
-- 31.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
SELECT a.`SId`,a.`Sname`
FROM `student` a
INNER JOIN sc b ON b.`SId` = a.`SId` AND b.`CId` ='01' AND b.`score`>= 80
-- 32.求每门课程的学生人数
SELECT a.`CId`,COUNT(DISTINCT a.`SId`)
FROM `sc` a
GROUP BY a.`CId`;
-- 33.假设成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT d.*,c.`score`
FROM `teacher` a
INNER JOIN `course` b ON b.`TId` = a.`TId`
INNER JOIN sc c ON c.`CId` = b.`CId`
INNER JOIN `student` d ON d.`SId` = c.`SId`
INNER JOIN (
SELECT MAX(c.`score`) score
FROM `teacher` a
INNER JOIN `course` b ON b.`TId` = a.`TId`
INNER JOIN sc c ON c.`CId` = b.`CId`
WHERE a.`Tname` = '张三'
) w ON w.score = c.`score`
WHERE a.`Tname` = '张三'
-- 34.假设成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT d.*,c.`score`
FROM `teacher` a
INNER JOIN `course` b ON b.`TId` = a.`TId`
INNER JOIN sc c ON c.`CId` = b.`CId`
INNER JOIN `student` d ON d.`SId` = c.`SId`
WHERE a.`Tname` = '张三'
ORDER BY c.`score` DESC LIMIT 1
-- 35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT *
FROM `sc` a
INNER JOIN `sc` b ON b.`score` = a.`score` AND b.`CId` <> a.`CId`
GROUP BY a.`SId`,a.`CId`,a.`score`
-- 36.查询每门功成绩最好的前两名
SELECT a.`CId`,a.`SId`,
a.`score`,@rank := IF(@class =a.CId, @rank + 1 ,1)AS rank,
@class := a.CId
FROM `sc` AS a
INNER JOIN (SELECT @rank := 0, @class:=0) r
LEFT JOIN `student` s ON s.`SId` = a.`SId`
LEFT JOIN `course` c ON c.`CId` = a.`CId`
ORDER BY a.`CId`,a.`score` DESC
;
SELECT m.*,
@rank := IF(@class =m.CId, @rank + 1 ,1)AS rank,
@class := m.CId
FROM (
SELECT w.`CId`,c.`Cname`,w.`SId`,s.`Sname`,w.`score`
FROM `sc` w
INNER JOIN `student` s ON s.`SId` = w.`SId`
LEFT JOIN `course` c ON c.`CId` = w.`CId`
) m ,(SELECT @rank := 0,@class:=0) r
ORDER BY m.`CId`,m.`score` DESC
;
-- 37.统计每门课程的学生选修人数（超过 5 人的课程才统计）。
SELECT b.`CId`,COUNT(DISTINCT b.`SId`)
FROM `datafrog`.sc b
GROUP BY b.`CId` HAVING COUNT(DISTINCT b.`SId`)>5
;
-- 38.检索至少选修两门课程的学生学号
SELECT b.SId
FROM `datafrog`.sc b
GROUP BY b.`SId` HAVING COUNT(DISTINCT b.`CId`)>=3
;
-- 39.查询选修了全部课程的学生信息
SELECT *
FROM `datafrog`.`student` a
WHERE a.`SId` IN (
SELECT b.SId
FROM `datafrog`.sc b
GROUP BY b.`SId` HAVING COUNT(DISTINCT b.`CId`)=3)
-- 40.查询各学生的年龄，只按年份来算
SELECT a.*,YEAR(NOW())-YEAR(a.`Sage`) AS age
FROM `datafrog`.`student` a
;
-- 41.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
SELECT a.*,TIMESTAMPDIFF(YEAR,a.`Sage`,NOW())AS age
FROM `datafrog`.`student` a
-- 42.查询本周过生日的学生
SELECT a.*,WEEK(a.`Sage`)
FROM `datafrog`.`student` a
WHERE WEEK(a.`Sage`) = WEEK(NOW())
;
-- 43.查询下周过生日的学生
SELECT *
FROM `datafrog`.`student` a
WHERE WEEK(a.`Sage`) - WEEK(NOW()) =1
-- 44.查询本月过生日的学生
SELECT *
FROM `datafrog`.`student` a
WHERE MONTH(a.`Sage`) = MONTH(NOW())
;
-- 45.查询下月过生日的学生
SELECT *
FROM `datafrog`.`student` a
WHERE MONTH(a.`Sage`) - MONTH(NOW()) = 1
;
3.3lettcode练习
- 体育馆人流高峰期计算
CREATE TABLE `datafrog`.test (
id INT NOT NULL UNIQUE PRIMARY KEY,
visit_date DATE NOT NULL ,
people INT NOT NULL
)
INSERT INTO `datafrog`.test VALUES
(1,'2017-01-01',10),
(2,'2017-01-02',109),
(3,'2017-01-03',150),
(4,'2017-01-04',99),
(5,'2017-01-05',145),
(6,'2017-01-06',1455),
(7,'2017-01-07',199),
(8,'2017-01-08',188)
SELECT id, visit_date, people
FROM (
SELECT r1.*, @flag := IF((r1.countt >= 3 OR @flag = 1) AND r1.countt != 0, 1, 0) AS flag
FROM (
SELECT s.*, @count := IF(s.people >= 100, @count + 1, 0) AS `countt`
SELECT *
FROM test s, (SELECT @count := 0) b
) r1, (SELECT @flag := 0) c
ORDER BY id DESC
) result
WHERE flag = 1 ORDER BY id;
