Mysql语句

xml文件转义字符处理

（1）<![CDATA[         ]]>

（2）直接写转义后的字符

1、mysql里批量修改表内某个字段内的部分数据

UPDATE inventory_stock

SET batchno = REPLACE(batchno,'-20-201901','-50-2019')

2、ON DUPLICATE KEY UPDATE

根据主键判断是新增还是修改（也可以有两个或多个主键  ON DUPLICATE KEY UPDATE 的values要跟数据库字段名一致）

 

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 id="update" parameterType="com.ws.wsweb.entity.DingUser">

INSERT INTO ding_user (dinguserid, `name` ,openid,mobile) VALUES

<foreach collection="list" item="item" index="index"  separator=",">

(#{item.dinguserid},#{item.name},#{item.openid},#{item.mobile})

</foreach>

ON DUPLICATE KEY UPDATE

`name` =values(name),openid=values(openid),mobile=values(mobile)

 

</insert>

INSERT INTO ding_user (dinguserid, `name` ,openid,mobile) VALUES

<foreach collection="list" item="item" index="index"  separator=",">

(#{item.dinguserid},#{item.name},#{item.openid},#{item.mobile})

</foreach>

ON DUPLICATE KEY UPDATE

`name`=values(name),openid=values(openid)

 

 1  　　　　insert into inventory_list
 2             (
 3                 listid,billid,billtypeid,invid,invtypeid,storeid,positionid
 4                 ,supplierid,productdate,validnum,batchno,count,memo,makerid
 5             )
 6         values
 7         <foreach collection="list" item="item" index="index" separator="," >  
 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         </foreach>


INSERT INTO table_name (column1, column2, column3, ...)SELECT column1, column2, column3, ...FROM source_table_name WHERE condition;

 

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
<foreach item="item" collection="array" open="(" separator="," close=")">
#{item}
</foreach>

LETF JOIN     表示左侧表肯定删除，右侧有的话就删除

<foreach item="item" collection="array" open="(" separator="," close=")">
#{item}
</foreach>

collection="array"   类型

open="("           　　开头

separator=","     　　分隔符

close=")"　　　　结尾

 8、表的插入（修改）

INSERT INTO ding_dep (id,name) VALUES
<foreach collection="list" item="item" index="index" separator=",">
(#{item.id},#{item.name})
</foreach>
ON DUPLICATE KEY UPDATE
name=values(name)

如果主键存在，做修改操作（只是修改name字段）

不存在做插入处理

 select 插入没有VALUES

insert into produce_bill_process_device (ppdid, billid,nodeid,deviceid)
    ( SELECT
        md5(concat(#{billid},nodeid,deviceid)) AS ppdid,
        #{billid},
        nodeid,
        deviceid
            FROM
            base_product_process_device
        WHERE
            productid = #{productid}
    )
    on duplicate key update
    billid=values(billid)

9、转义字符的转换  

对于一些 >、<等符号

 <![CDATA[     ]]>

V-IF的使用

 <if test="keyvalue != null and keyvalue != ''">
　　where name like concat('%',#{keyvalue},'%')
</if>

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) = 2019
    AND 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"


 


 


INSERT INTO resource_device_property_checktype ( id, deviceid, checktypeid, checkstate, isphoto )



SELECT
UUID( ),
'9034ad82111a60ebf64260678662074f',
checktypeid,
1,
ifnull( isphoto, 0 ) 
FROM
	base_devicechecktype 
WHERE
	CONCAT( ",", '037aae005dda1b089848f53cbde7bc15,1', "," ) LIKE CONCAT( "%,", checktypeid, ",%" );


 


 


 


13、MYSQL设置权限和刷新


set sql_mode='ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,PIPES_AS_CONCAT,ANSI_QUOTES'


		
		flush privileges;


 


14、 联合查询


 




<!-- 工控信息列表 -->
	<select id="getPCList" resultMap="PCSystemList">
		select 
		    a.deviceid as pcid,
		    a.deviceno as pcno,
		    a.iused,
		    a.makedate,
		    a.supplierid,
		    b.suppliername,
		    dh.housename
		from base_device as a
		LEFT JOIN base_supplier as b on a.supplierid = b.supplierid
		left join base_device_house dh on dh.deviceid=a.deviceid
		where 1=1
		and a.iused = 1
		and dh.iused = 1
		<if test="keyvalue != null and keyvalue != ''">
			and a.deviceno like concat('%',#{keyvalue},'%')
		</if>
		<if test="supplierid != null and supplierid != ''">
			and a.supplierid = #{supplierid}
		</if>
		ORDER BY a.makedate DESC
	</select>


	<!-- 工控/系统的关系Map -->
	<resultMap id="PCSystemList" type="Map">
		<id column="pcid" property="pcid" />
		<collection property="systemList" javaType="java.util.ArrayList" ofType="Map" column="pcid" select="getSystemItemList">
		</collection>
	</resultMap>


	<!-- 查询系统运行状况 -->
	<select id="getSystemItemList" resultType="Map">
		SELECT a.sysid, a.sysno, a.deviceid as pcid, b.sysname, s.statename, a.errmsg
		FROM base_device_system a
		INNER JOIN base_system b ON a.sysno = b.sysno
		Left JOIN base_state s ON s.stateid = a.stateid
		WHERE a.deviceid = #{pcid}
	</select>








　


 


 


15、 CASE和WHEN


 


 update pp_order_task_node set
            status = CASE WHEN start_node = 1 THEN #{create} ELSE #{start} END
        where task_id = #{taskId}


16修改自增数


ALTER TABLE tb_stock_transfer_apply_sub AUTO_INCREMENT=910







            

            
posted @ 
2019-07-24 10:25 
Barry_Song 
阅读(334) 
评论(0) 
编辑 
收藏 
举报