xml文件轉義字符處理
(1)
(2)直接寫轉義后的字符
1、mysql里批量修改表內某個字段內的部分數據
UPDATE inventory_stock
SET batchno = REPLACE(batchno,'-20-201901','-50-2019')
2、ON DUPLICATE KEY UPDATE
根據主鍵判斷是新增還是修改(也可以有兩個或多個主鍵)
INSERT INTO TABLE (a,c) VALUES (1,3) ON DUPLICATE KEY UPDATE c=c+1;
UPDATE TABLE SET c=c+1 WHERE a=1;
插入多條
INSERT INTO ding_user (dinguserid, `name` ,openid,mobile) VALUES
(#{item.dinguserid},#{item.name},#{item.openid},#{item.mobile})
ON DUPLICATE KEY UPDATE
`name` =values(name),openid=values(openid),mobile=values(mobile)
INSERT INTO ding_user (dinguserid, `name` ,openid,mobile) VALUES
(#{item.dinguserid},#{item.name},#{item.openid},#{item.mobile})
ON DUPLICATE KEY UPDATE
`name`=values(name),openid=values(openid)
1 insert into inventory_list2 (3 listid,billid,billtypeid,invid,invtypeid,storeid,positionid4 ,supplierid,productdate,validnum,batchno,count,memo,makerid5 )6 values7
8 (9 uuid(),#{item.billid},#{item.billtypeid},#{item.invid},#{item.invtypeid},#{item.storeid},#{item.positionid}10 ,#{item.supplierid},#{item.productdate},#{item.validnum},#{item.batchno},#{item.count},#{item.memo},#{item.makerid}11 )12
3、mysql插入一個字段
alter table task_list add chargeuserid varchar(50) DEFAULT NULL after userid;
UPDATE task_list SET chargeuserid =userid
AFTER userId :??必須加到最后
primary?key?：若存在主鍵，語句會報錯
//???插入一個字段和刪除一個字段
alter table ding_attence ADD ?makedate datetime DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE ding_attence DROP COLUMN maketime
ALTER TABLE ding_attence ?ADD `checkType` varchar(50) NOT NULL primary?key??DEFAULT 'OnDuty' COMMENT '考勤類型OnDuty：上班；OffDuty：下班' AFTER userId;
3.修改一個字段
alter table user MODIFY new1 VARCHAR(10); //修改一個字段的類型
alter table user CHANGE new1 new4 int;//修改一個字段的名稱，此時一定要重新指定該字段的類型
4.id自動增加
`id` int(12) NOT NULL AUTO_INCREMENT
5.id重新恢復1
alter table ali.ali_product AUTO_INCREMENT=1;
//修改自增字段的初始值
ALTER TABLE base_purchare auto_increment=1
4、REGEXP進行正則匹配
SELECT userid,username FROM ?sys_user AS a
INNER JOIN sys_department AS b ON a.depid = b.depid AND b.iused = 1
WHERE a.iused =1 AND b.depcode REGEXP CONCAT('^',
(SELECT d.depcode FROM ?sys_user AS c
INNER JOIN sys_department AS d ON c.depid = d.depid WHERE c.userid = '82200e23-5f1c-11e9-98bb-4ccc6a2f102e')
)
5、insert插入多條數據
INSERT INTO wx_user
SELECT userid ,'' AS openid FROM sys_user WHERE usercode = ''
6、查詢表中一個字段是否有重復的值
SELECT??invcode,
count(t1.invcode) AS ct
FROM
base_inventory AS t1
GROUP BY
t1.invcode
HAVING
ct > 1
6、修改(替換)表中某個字段的值
update?table?set?name?replace(name,'name_','')
where?name?like?'name_%';--替換
replace(name,'name_','')
把name中出現'name_'的全部替換為''
update?table?set?name?=?substr(name,6)?where?name?like?'name_%';?--截取
mysql中的substr()函數
用法：
substr(string string,num start,num length);
string為字符串；
start為起始位置；
length為長度。
mysql中的start是從1開始的
substr(name,6) 從第六位開始到最后一位
update table?set name= name +’內容’where id = 1
包含是否為空和NUL
UPDATE import_temp_invfile
SET state = 1 ,
errmsg?= CONCAT(IF((ISNULL(errmsg) || LENGTH(trim(errmsg))<1),'編碼重復','編碼重復/'),IFNULL(errmsg,''))
WHERE invcode?IN (1);
7、多個表的刪除
DELETE m,p FROM ytd_meetings as m LEFT join ytd_meetingpic as p on m.meetingid = p.meetingid WHERE
m.meetingid in
#{item}
LETF JOIN??表示左側表肯定刪除，右側有的話就刪除
#{item}
collection="array" ?類型
open="(" ? ? ? 開頭
separator="," ? 分隔符
close=")" 結尾
8、表的插入(修改)
INSERT INTO ding_dep (id,name) VALUES
(#{item.id},#{item.name})
ON DUPLICATE KEY UPDATE
name=values(name)
如果主鍵存在，做修改操作(只是修改name字段)
不存在做插入處理
9、轉義字符的轉換
對于一些 >、
V-IF的使用
where name like concat('%',#{keyvalue},'%')
10、時間戳的轉換
1、13位時間戳轉換為“yyyy-mm-dd hh:mm:ss”
FROM_UNIXTIME(round(b.start_time / 1000,0))
10位的則不用÷1000
2、“yyyy-mm-dd hh:mm:ss”轉換為13位時間戳
SELECT??(UNIX_TIMESTAMP(NOW())*1000)
--轉換成10位的則不用*1000
SELECT UNIX_TIMESTAMP('2019-10-12 10:50:12')
11、分組語句
根據時間查詢 年-月-日
SELECT ROUND(AVG(b.temperaturevalue), 2) AS temVal
, ROUND(AVG(b.humidityvalue),2) AS humVal
, DATE_FORMAT(b.makedate,'%d') AS day
FROM sys_department a, msg_humitemp_record b
WHERE (a.depid=b.depid
AND a.depid= '3b7ddcf4-87a9-4188-8a36-b97929dcd0ee'AND year(b.makedate)= 2019AND month(b.makedate)= 10)
GROUP BY DATE_FORMAT(b.makedate,'%Y-%m-%d')
ORDER BY b.makedate ASC
11、查詢中加入一個選項
比查詢完再加入相對簡單
SELECT '' AS stateid, '全部'AS title
UNION
SELECT stateid, statename AS title
FROM base_state
WHERE statevalue= '50'ORDER BY stateid
12、Count()的三種用法
1、COUNT(a > b OR NULL)
2、SUM(if(a > b, 1, 0))
3、是2的實際寫法(2為簡寫)
SUM(CASE
WHEN a > b THEN 1
ELSE 0
END)
SELECT DATE_FORMAT(visdate, '%d') AS title, COUNT(visid) AS totalcount
, COUNT(temperature > maxtemperature
OR NULL) AS effectcount
, SUM(if(temperature > maxtemperature, 1, 0)) AS ss
, SUM(CASE
WHEN temperature > maxtemperature THEN 1
ELSE 0
END) AS aa
FROM user_visitor
WHERE DATE_FORMAT(visdate, '%Y-%m') = '2020-02'
GROUP BY DATE_FORMAT(visdate, '%d')
ORDER BY DATE_FORMAT(visdate, '%d') ASC;
parameterType="com.ws.wsweb.entity.DingUser"
