MySQL with 语句的使用

文章目录

1. 1. 插入
2. 2. 更新

插入

需求：给年龄小于18岁的用户加上学生角色，实现SQL如下

insert into s_user_role (user_id,role_id)
with a as (select * from s_user u where u.age<18 ),
b as (select * from s_role sr where sr.role_name='学生')
select user_id,role_id from a,b

更新

需求：给管理员这个角色的用户年龄加1

with a as (select u.user_id,u.username,u.age from s_user u,s_user_role sur ,s_role sr where
u.user_id=sur.user_id and sur.role_id=sr.role_id and sr.role_name='管理员'
)
update s_user set age = age+1 where user_id in (select user_id from a)