mysql数据库更新数据库语句_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_idselect type_name,min(type_id) as min_type_id,count(*)

from imc_type

group by type_name having count(*) > 1delete a

from imc_type ajoin(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 default0 comment '是否推荐，0不推荐，1推荐';selectcourse_id

from imc_course

order by rand()

limit10;

update imc_course

set is_recommand=1order by rand()

limit10;selectcourse_id,title

from imc_course

where is_recommand=1;

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

select * from imc_classvalue;

update imc_course ajoin(selectcourse_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;