1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
| #######################################MySQL基本操作#######################################
(一)基础操作
1.时间函数
①格式化时间格式：DATE_FORMAT(createtime,'%Y%m%d HH:mm:ss')
②FROM_UNIXTIME(createtime) 10位时间戳转时间
2.字符串函数
①获取字符串长度：CHAR_LENGTH(mobile)
②字符串拼接：concat、concat_ws
③LOCATE('-','a-b') 查询某字符所在字符串的位置，从1开始
④获取表字段：select concat('"',COLUMN_NAME,'",') from information_schema.COLUMNS where table_name = 'bigmarketing_broker_profit';
select replace(GROUP_CONCAT(concat(COLUMN_NAME,',')),',,',',') from
(select distinct COLUMN_NAME from information_schema.COLUMNS where table_name = 'dimdate') t1;
（1）获取第2个以逗号为分隔符之前的所有字符。
SELECT SUBSTRING_INDEX('7654,7698,7782,7788',',',2);
（2）获取最后一个到倒数第2个以逗号分隔符之后的所有字符
SELECT SUBSTRING_INDEX('7654,7698,7782,7788',',',-2);
3.更新数据：
UPDATE Persons SET LastName='Wilson' WHERE LastName='Gates'
4.爆炸函数
需要准备一个有自增id的表：help_topic
原表与辅助表不等值连接，连接条件是 help_id<
SELECT pub.mobile,substring_index( substring_index( pub.houseIds, ',', b.help_id), ',',- 1 ) houseid
FROM ims_weixinmao_house_publiccustomer pub
JOIN help_topic b ON b.help_id < ( length( pub.houseIds ) - length( REPLACE ( pub.houseIds, ',', '' ) ) + 1 )
5.行转列函数
GROUP_CONCAT(column) //把某列的所有内容，用逗号分割放在一行中
GROUP_CONCAT(distinct column) //把某列的所有内容，用逗号分割放在一行中,并去重
GROUP_CONCAT(distinct if(column <5,column,null)) 加条件筛选，只把column < 5的值，去重拼接在一起。
GROUP_CONCAT(distinct if(phaseNumber < 5,phaseNumber,null) order by phaseNumber desc) 加条件筛选，只把column < 5的值，去重并按升序拼接在一起。
GROUP_CONCAT( Separator ';') 以分号分割
##用行转列获取表全字段
select
GROUP_CONCAT(column_name)
from information_schema.columns
where table_schema='database_name' and table_name='table1';
(二)进阶优化操作
1.给列添加普通索引(能大大加快查询和关联的速度)
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
2.联合唯一索引(索引字段是唯一的，插入时有则更新，无则插入)
alter table ods_we7.ims_weixinmao_house_annual_target
ADD UNIQUE INDEX year_house_phases_property(`personnel_name`, `target_year`);
3.存储过程
①创建存储过程
DELIMITER &
CREATE PROCEDURE ads_broker_achievement_01()
BEGIN
//sql代码
END &
②调用存储过程
CALL ads_broker_achievement00();
③删除存储过程
DROP PROCEDURE ads_broker_achievement000;
4.存储过程的使用
①定义变量
DECLARE var_name varchar(255) default null;
为变量赋值
1.常规赋值 SET var_name = 'value'
2.查询赋值 set @date1 = (select max(create_time) from table);
SELECT COUNT(*) INTO n_total FROM db_database11.tb_user;
②IF结构
IF createtime = DATE_FORMAT(now(),'%Y%m%d') then
SET var_name = '今天';
ELSE set var_name = '过去';
END IF;
③WHILE结构
WHILE a < 100 DO
SET j = j+1;
END WHILE;
④循环结构
DECLARE i INT DEFAULT 0;
REPEAT
SET i = i + 1;
……
……
UNTIL 1 >= 100
END REPEAT;
⑤更新表字段
update table1 di
set di.column1 = 1;
关联更新时，最好使用join，能更改符合条件的字段，其他不符合条件的不改变。
使用left join，不符合条件的，会置空。
COMMIT;
END
5.函数
CREATE FUNCTION `rand_num`(from_num INT ,to_num INT) RETURNS int(11) #创建函数（传入a,b返回a-b的随机数）
BEGIN
DECLARE i INT DEFAULT 0; #定义变量
SET i = FLOOR(from_num +RAND()*(to_num -from_num+1)) ; #变量赋值
RETURN i; #变量作为函数返回值
END
6.事件
①创建事件
CREATE EVENT broker_event
ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP(CURRENT_DATE,'02:00:00') #每天2点调度一次
#ON SCHEDULE EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK
#每十二个小时，从现在起三十分钟后开始，并于现在起四个星期后结束
#ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 20 second 20秒后执行，只会执行一次。
ON COMPLETION [NOT] PRESERVE #是否循环执行
COMMENT '' #添加注释
DO
call bianliang();
②删除事件
DROP event broker_event;
③查看事件开启状态
select @@event_scheduler; #查看事件是否开启
set global event_scheduler = on; #开启事件
④查看事件
SHOW EVENTS;
SHOW CREATE EVENT test;
SELECT * FROM information_schema.events;
⑤启动或关闭事件
ALTER EVENT event_user ENABLE;
ALTER EVENT event_user DISABLE;
(三)其他注意事项
①插入数据注意事项：
replace into若有与主键值或索引值相同的数据，则删除旧数据，插入新数据(若不是主键的话无意义)。
insert into table values()不推荐使用，插入数据时id主键必须设置为null,不会更新数据，扩展性差。
insert into table(col1,col2) values(v1,v2) 不必为自增主键赋值，且字段顺序可以变动。
②创建事件流程：
开启事件调度
#表字段编码格式不同，导致无法关联。
ALTER TABLE `thesis_answer_ppt` MODIFY `file_path` varchar(2048) CHARACTER SET UTF8 COLLATE utf8_general_ci NOT NULL;
|
