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;