sql技巧(增册改查)

select * from wyl.t;
--将数据从t1导入t2
insert into t2(c1,c2) select c1,c2 from t1 where c1= xx and c2 = xx order by c1;
--使用to表的name来更新t1表的name
update t1 as a,t2 as b set a.name = b.name where a.tid = b.id;
--两表关联更新
update t_role_user as a,
(
   select 
   id
   from
   t_user
   where
   departid in(
      select
      id 
      from
      t_depart
      where
      length(org_code) = 9
   )
) as b 
set a.roleid = '12345'
where
 a.userid = b.id;
--自己和自己关联一更新
update t_depart as a,
(
   select
   id,
   substring(org_code,1,6) org_code
   from
   t_depart
   where
   length(org_code)=8
   and parent_depart_id is not null
) as b
set a.parent_depart_id = b.id
where
substring(a.org_code,1,6) = 
b.org_code
--两表关联删除，将删除两表中关联id并且t2表name为空的两表记录
delete a,b from t1 as a left join t2 as b on a.tid = b.id where b.name is null;
--奖统计结果插入到表
insert into se_stat_org (
    record_date,
    org_id,
    org_name,
    sign_cont_count,
    sign_arri_cont_count,
    sign_cont_money,
    sign_arri_cont_money,
    total_arri_cont_count,
    total_arri_money,
    publish_total_count,
    project_count
) select
*
from
(
    select
    '2012-06-09' record_date,
    parent_org_id,
    parent_org_name,
    sum(sign_cont_count) sign_cont_count,
    sum(sign_arri_cont_count) sign_arri_cont_count,
    sum(sign_cont_money) sign_cont_money,
    sum(sign_arri_cont_money) sign_arri_cont_money,
    sum(total_arri_cont_count) total_arri_cont_count,
    sum(total_arri_money) total_arri_money,
    sum(publish_total_count) publish_total_count,
    sum(project_count) project_count,
    from se_stat_user
    where date_format(record_date, '%y-%m-%d') = '2012-06-09'
    group by parent_org_id
) m

--三表关联更新
update se_stat_user a,
(
    select
    user_id,
    sum(invest_org_count + financial_org_count + intermediary_org_count + enterprise_count) as common_count
    from se_stat_user
    where date_format(record_date, '%y-%m-%d') = '2012-06-09'
    group by user_id
) b,
(
    select
    user_id,
    sum(establish_count + stock_count + merger_count + achieve_count) as project_count
    from se_stat_user
    where date_format(record_date, '%y-%m-%d') = '2012-06-09'
    group by user_id
) c
set a.common_count = b.common_count, a.project_count = c.project_count
 where a.user_id = b.user_id
   and a.user_id = c.user_id
   and date_format(a.record_date, '%y-%m-%d') = '2012-06-09'
--带条件的关联更新
update se_stat_user a,
(
    select
    p.channel,
    count(p.cont_id) as cont_count,
    c.cust_mgr_id
    from
    (
        select
        channel,
        cont_id
        from sk_project
        where project_status = 6
        and date_format(audit_time, '%y-%m-%d') = '2012-06-11'
    ) p
    inner join se_contract c on p.cont_id = c.cont_id
    group by p.channel, c.cust_mgr_id
) b
set
    a.stock_count = case when b.channel = 2 then b.cont_count else 0 end,
    a.establish_count = case when b.channel = 3 then b.cont_count else 0 end,
    a.achieve_count = case when b.channel = 4 then b.cont_count else 0 end, 
    a.brand_count = case when b.channel = 5 then b.cont_count else 0 end,
    a.merger_count = case when b.channel = 6 then b.cont_count else 0 end
where
  a.user_id = b.cust_mgr_id
    and date_format(a.record_date, '%y-%m-%d') = '2012-06-11'
--加索引
alter table project add index index_user_id (user_id),
add index index_project_status (project_status);
--删除列
alter table project drop column project_status,
drop column expect_return,drop column currency;
--增加列
alter table project 
add column dict_id int default null comment 'xxx' after project_site,
add column introduce text default null comment 'xx' after dict_id,
add column stage int default null comment 'xx' after id,
add column attach_uri varchar(8) default null comment 'xxx' after introduce;
--修改列，一般用modify修改数据类型，change修改列名
alter table project change dict_id dict_id1 int not null,
modify project_status tinyint not null comment 'xxx';
--1.总体累计统计 对员工的工资，人数进行总体累计统计
select 
    employee_id,
    sum(salary) over(order by employee_id) sal,
    count(*) over (order by employee_id) num,
    sum(salary) over() total_sal,
    count(*) over() total_num,
  from hr.employees;
--2.分组累计统计 对各个部门中的员工工资，人数进行分组累计统计
select 
      department_id,
      employee_id,
      sum(salary) over(partition by department_id order by employee_id) sal,
      count(*) over(partition by department_id order by employee_id) sum
  from hr.employees;