1 select * from wyl.t;
2 --将数据从t1导入t2
3 insert into t2(c1,c2) select c1,c2 from t1 where c1= xx and c2 = xx order by c1;
4 --使用to表的name来更新t1表的name
5 update t1 as a,t2 as b set a.name = b.name where a.tid = b.id;
6 --两表关联更新
7 update t_role_user as a,
8 (
9 select
10 id
11 from
12 t_user
13 where
14 departid in(
15 select
16 id
17 from
18 t_depart
19 where
20 length(org_code) = 9
21 )
22 ) as b
23 set a.roleid = '12345'
24 where
25 a.userid = b.id;
26 --自己和自己关联一更新
27 update t_depart as a,
28 (
29 select
30 id,
31 substring(org_code,1,6) org_code
32 from
33 t_depart
34 where
35 length(org_code)=8
36 and parent_depart_id is not null
37 ) as b
38 set a.parent_depart_id = b.id
39 where
40 substring(a.org_code,1,6) =
41 b.org_code
42 --两表关联删除，将删除两表中关联id并且t2表name为空的两表记录
43 delete a,b from t1 as a left join t2 as b on a.tid = b.id where b.name is null;
44 --奖统计结果插入到表
45 insert into se_stat_org (
46 record_date,
47 org_id,
48 org_name,
49 sign_cont_count,
50 sign_arri_cont_count,
51 sign_cont_money,
52 sign_arri_cont_money,
53 total_arri_cont_count,
54 total_arri_money,
55 publish_total_count,
56 project_count
57 ) select
58 *
59 from
60 (
61 select
62 '2012-06-09' record_date,
63 parent_org_id,
64 parent_org_name,
65 sum(sign_cont_count) sign_cont_count,
66 sum(sign_arri_cont_count) sign_arri_cont_count,
67 sum(sign_cont_money) sign_cont_money,
68 sum(sign_arri_cont_money) sign_arri_cont_money,
69 sum(total_arri_cont_count) total_arri_cont_count,
70 sum(total_arri_money) total_arri_money,
71 sum(publish_total_count) publish_total_count,
72 sum(project_count) project_count,
73 from se_stat_user
74 where date_format(record_date, '%y-%m-%d') = '2012-06-09'
75 group by parent_org_id
76 ) m
77
78 --三表关联更新
79 update se_stat_user a,
80 (
81 select
82 user_id,
83 sum(invest_org_count + financial_org_count + intermediary_org_count + enterprise_count) as common_count
84 from se_stat_user
85 where date_format(record_date, '%y-%m-%d') = '2012-06-09'
86 group by user_id
87 ) b,
88 (
89 select
90 user_id,
91 sum(establish_count + stock_count + merger_count + achieve_count) as project_count
92 from se_stat_user
93 where date_format(record_date, '%y-%m-%d') = '2012-06-09'
94 group by user_id
95 ) c
96 set a.common_count = b.common_count, a.project_count = c.project_count
97 where a.user_id = b.user_id
98 and a.user_id = c.user_id
99 and date_format(a.record_date, '%y-%m-%d') = '2012-06-09'
100 --带条件的关联更新
101 update se_stat_user a,
102 (
103 select
104 p.channel,
105 count(p.cont_id) as cont_count,
106 c.cust_mgr_id
107 from
108 (
109 select
110 channel,
111 cont_id
112 from sk_project
113 where project_status = 6
114 and date_format(audit_time, '%y-%m-%d') = '2012-06-11'
115 ) p
116 inner join se_contract c on p.cont_id = c.cont_id
117 group by p.channel, c.cust_mgr_id
118 ) b
119 set
120 a.stock_count = case when b.channel = 2 then b.cont_count else 0 end,
121 a.establish_count = case when b.channel = 3 then b.cont_count else 0 end,
122 a.achieve_count = case when b.channel = 4 then b.cont_count else 0 end,
123 a.brand_count = case when b.channel = 5 then b.cont_count else 0 end,
124 a.merger_count = case when b.channel = 6 then b.cont_count else 0 end
125 where
126 a.user_id = b.cust_mgr_id
127 and date_format(a.record_date, '%y-%m-%d') = '2012-06-11'
128 --加索引
129 alter table project add index index_user_id (user_id),
130 add index index_project_status (project_status);
131 --删除列
132 alter table project drop column project_status,
133 drop column expect_return,drop column currency;
134 --增加列
135 alter table project
136 add column dict_id int default null comment 'xxx' after project_site,
137 add column introduce text default null comment 'xx' after dict_id,
138 add column stage int default null comment 'xx' after id,
139 add column attach_uri varchar(8) default null comment 'xxx' after introduce;
140 --修改列，一般用modify修改数据类型，change修改列名
141 alter table project change dict_id dict_id1 int not null,
142 modify project_status tinyint not null comment 'xxx';
143 --1.总体累计统计 对员工的工资，人数进行总体累计统计
144 select
145 employee_id,
146 sum(salary) over(order by employee_id) sal,
147 count(*) over (order by employee_id) num,
148 sum(salary) over() total_sal,
149 count(*) over() total_num,
150 from hr.employees;
151 --2.分组累计统计 对各个部门中的员工工资，人数进行分组累计统计
152 select
153 department_id,
154 employee_id,
155 sum(salary) over(partition by department_id order by employee_id) sal,
156 count(*) over(partition by department_id order by employee_id) sum
157 from hr.employees;