mysql8学习笔记⑥数据库常用操作之Delete/update语句

mysql8学习笔记⑥数据库常用操作之Delete/update语句

-- 找出课程表中没有章节信息的课程

select a.course_id,a.title

from imc_course a

left join imc_chapter b on b.course_id = a.course_id

where b.course_id IS NULL

-- 删除课程表中没有章节信息的课程

delete a

from imc_course a

left join imc_chapter b on b.course_id = a.course_id

where b.course_id IS NULL

-- 删除课程方向表中重复的课程方向，

-- 保留方向ID最小的一条，并在方向名称上添加唯一索引(如果课程方向重复则不能添加唯一索引)

-- 找出重复的课程类型
select type_name,count(*)
from imc_type
group by type_name having count(*) > 1

-- 最小的type_id
select type_name,min(type_id) as min_type_id,count(*)
from imc_type
group by type_name having count(*) > 1


delete a
from imc_type a
join (
        select type_name,min(type_id) as min_type_id,count(*)
        from imc_type
        group by type_name having count(*) > 1
        ) b 
    on a.type_name=b.type_name and a.type_id > b.min_type_id

create unique index uqx_typename on imc_type(type_name);

Update使用order by和limit语句可以限制更新的数据量，当我们对某个数据表很大的业务进行更新时，比如更新100W数据，如果一次全部更新会引发主从延时、大面积阻塞，用limit 限制可以循环分批进行更新

更新示例：

-- 冻结用户“沙占”的账号

select user_nick,user_status

from imc_user

where user_nick = '沙占'

update imc_user

set user_status=0

where user_nick = '沙占'

-- 随机推荐10门课程

alter table imc_course
add is_recommand tinyint default 0 comment '是否推荐，0不推荐，1推荐';

select course_id
from imc_course
order by rand()
limit 10;


update imc_course
set is_recommand=1
order by rand()
limit 10;

select course_id,title
from imc_course
where is_recommand=1;

-- 利用课程表中的平均评分，更新课程表中课程的评分

select * from imc_classvalue;

update imc_course a 
join(
        select course_id,
                avg(content_score) as avg_content_score,
                avg(level_score) as avg_level_score,
                avg(logic_score) as avg_logic_score,
                avg(score) as avg_score
        from imc_classvalue
        group by course_id
) b on a.course_id = b.course_id
set a.content_score = b.avg_content_score,
a.level_score = b.avg_level_score,
a.logic_score = b.avg_logic_score,
a.score = b.avg_score
;

-- 每门课程的学习人数占总课程总学习人数的百分比

with tmp as(

select class_name,title,study_cnt

                                ,sum(study_cnt) over(partition by class_name) as class_total

from imc_course a

join imc_class b on b.class_id = a.class_id

)

select class_name,title,concat(study_cnt/class_total*100,'%')

from tmp

order by class_name;