怎么把网站提交给搜索引擎,网站留言板模板,江诗丹顿手表网站,网页毕业设计说明书xml文件转义字符处理(1)(2)直接写转义后的字符1、mysql里批量修改表内某个字段内的部分数据UPDATE inventory_stockSET batchno REPLACE(batchno,-20-201901,-50-2019)2、ON DUPLICATE KEY UPDATE根据主键判断是新增还是修改(也可以有两个或多个主键)INSERT INTO TABLE (a,c) …xml文件转义字符处理(1)(2)直接写转义后的字符1、mysql里批量修改表内某个字段内的部分数据UPDATE inventory_stockSET batchno REPLACE(batchno,-20-201901,-50-2019)2、ON DUPLICATE KEY UPDATE根据主键判断是新增还是修改(也可以有两个或多个主键)INSERT INTO TABLE (a,c) VALUES (1,3) ON DUPLICATE KEY UPDATE cc1;UPDATE TABLE SET cc1 WHERE a1;插入多条INSERT INTO ding_user (dinguserid, name ,openid,mobile) VALUES(#{item.dinguserid},#{item.name},#{item.openid},#{item.mobile})ON DUPLICATE KEY UPDATEname values(name),openidvalues(openid),mobilevalues(mobile)INSERT INTO ding_user (dinguserid, name ,openid,mobile) VALUES(#{item.dinguserid},#{item.name},#{item.openid},#{item.mobile})ON DUPLICATE KEY UPDATEnamevalues(name),openidvalues(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 useridAFTER userId : 必须加到最后primary key 若存在主键语句会报错// 插入一个字段和删除一个字段alter table ding_attence ADD makedate datetime DEFAULT CURRENT_TIMESTAMP;ALTER TABLE ding_attence DROP COLUMN maketimeALTER 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_INCREMENT5.id重新恢复1alter table ali.ali_product AUTO_INCREMENT1;//修改自增字段的初始值ALTER TABLE base_purchare auto_increment14、REGEXP进行正则匹配SELECT userid,username FROM sys_user AS aINNER JOIN sys_department AS b ON a.depid b.depid AND b.iused 1WHERE a.iused 1 AND b.depcode REGEXP CONCAT(^,(SELECT d.depcode FROM sys_user AS cINNER JOIN sys_department AS d ON c.depid d.depid WHERE c.userid 82200e23-5f1c-11e9-98bb-4ccc6a2f102e))5、insert插入多条数据INSERT INTO wx_userSELECT userid , AS openid FROM sys_user WHERE usercode 6、查询表中一个字段是否有重复的值SELECT invcode,count(t1.invcode) AS ctFROMbase_inventory AS t1GROUP BYt1.invcodeHAVINGct 16、修改(替换)表中某个字段的值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包含是否为空和NULUPDATE import_temp_invfileSET 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 WHEREm.meetingid in#{item}LETF JOIN 表示左侧表肯定删除右侧有的话就删除#{item}collectionarray 类型open( 开头separator, 分隔符close) 结尾8、表的插入(修改)INSERT INTO ding_dep (id,name) VALUES(#{item.id},#{item.name})ON DUPLICATE KEY UPDATEnamevalues(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位的则不用÷10002、“yyyy-mm-dd hh:mm:ss”转换为13位时间戳SELECT (UNIX_TIMESTAMP(NOW())*1000)--转换成10位的则不用*1000SELECT 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 dayFROM sys_department a, msg_humitemp_record bWHERE (a.depidb.depidAND a.depid 3b7ddcf4-87a9-4188-8a36-b97929dcd0eeAND year(b.makedate) 2019AND month(b.makedate) 10)GROUP BY DATE_FORMAT(b.makedate,%Y-%m-%d)ORDER BY b.makedate ASC11、查询中加入一个选项比查询完再加入相对简单SELECT AS stateid, 全部AS titleUNIONSELECT stateid, statename AS titleFROM base_stateWHERE statevalue 50ORDER BY stateid12、Count()的三种用法1、COUNT(a b OR NULL)2、SUM(if(a b, 1, 0))3、是2的实际写法(2为简写)SUM(CASEWHEN a b THEN 1ELSE 0END)SELECT DATE_FORMAT(visdate, %d) AS title, COUNT(visid) AS totalcount, COUNT(temperature maxtemperatureOR NULL) AS effectcount, SUM(if(temperature maxtemperature, 1, 0)) AS ss, SUM(CASEWHEN temperature maxtemperature THEN 1ELSE 0END) AS aaFROM user_visitorWHERE DATE_FORMAT(visdate, %Y-%m) 2020-02GROUP BY DATE_FORMAT(visdate, %d)ORDER BY DATE_FORMAT(visdate, %d) ASC;parameterTypecom.ws.wsweb.entity.DingUser
