SQL语句

1、查询某表中某列不重复值

SELECT DISTINCT c.ChargeKindID,k.`Name`  from carinoutrec c LEFT JOIN chargekind k on c.ChargeKindID=k.ID

2.查询某个字段包含字符串

SELECT * from carinoutrec WHERE Memo LIKE '%无入场记录%';

3.记录不存在插入，存在更新

INSERT INTO card_issue(card_ID,card_no,card_type) VALUES('{0}', '{0}',0) ON DUPLICATE KEY UPDATE `card_ID`='{0}'

4.查询两个时间时间差大于1天的记录

SELECT *  from  carinoutrec WHERE  TIMESTAMPDIFF(DAY,InTime,OutTime)>=1

5.数据库中某个字段为长字符串，从中获取某个字段（user_id）的值。

{"card_type":"F0","card_id":"20180701064708","user_id":"458","user_name":"苏Y87MV0","mch_id":"458","mch_name":"自助缴费机","pos_id":"458","platform_id":"0920","platform_name":"南京"}

select *,SUBSTR(pay_obj,locate('user_id',pay_obj)+10,locate('user_name',pay_obj)-3-locate('user_id',pay_obj)-10) as deviceid 
 from  CarCenterCharge  where  pay_obj  LIKE '%自助缴费机%' 。

6.mysql创建存储过程

         string sqlPro = @"DROP PROCEDURE IF EXISTS `CheckHour`;
                                #go
                                CREATE PROCEDURE `CheckHour`(IN `checkDate` datetime,IN `nHour` INT)
                                BEGIN
                                    #Routine body goes here...
                                DECLARE Done INT DEFAULT 0;
                                DECLARE $ncount,$parkid int;
                                declare cr CURSOR for ( select COUNT(*) as nCount ,ParkID as parkID from space where RateState=1 and parkid in (select ID from park ) GROUP BY ParkID );
                                DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
                                open cr;
                                    fetch next from cr  into $ncount,$Parkid;
                                    REPEAT
                                            IF NOT Done THEN
                                      insert into checkDaily(checkDate,nHour,nCount,parkid,Editflag) values(checkDate,nHour,$ncount,$parkid,NOW());
                                            END IF;
                                    fetch next from cr  into $ncount,$Parkid;
                                     UNTIL Done END REPEAT;
                                 /* 关闭游标 */
                                        CLOSE cr;
                                END";
                dbHelper.ExecuteNonQuery(sqlPro);

7查询某列重复的行 select t.AIR_CODE4 from DATA_AIRPORT t group by t.AIR_CODE4 having COUNT(*)>1