#SQL1242错误

---------------------

1. 子查询更新数据时遇到多条数据时，可以使用SUM&MIN等函数解决：如下：
   
   

   //正确的方法一对多
   UPDATE `yd_draw_prize_order` SET `adopt_update_time` = (SELECT MIN(`update_time`) FROM `yd_draw_prize_order_number` WHERE `d_p_order_id` = `yd_draw_prize_order`.`d_p_order_id`) WHERE `adopt_update_time` IS NULL
   
   //报1242错误方式一对多
   UPDATE `yd_draw_prize_order` SET `adopt_update_time` = (SELECT `update_time` FROM `yd_draw_prize_order_number` WHERE `d_p_order_id` = `yd_draw_prize_order`.`d_p_order_id`) WHERE `adopt_update_time` IS NULL

   View Code

    

2. 时间查询     简单举例： SELECT COUNT('$column') FROM `%table` WHERE `$column` BETWEEN UNIX_TIMESTAMP('2018-05-03 00:00:01') AND UNIX_TIMESTAMP('2018-05-03 23:59:59')
   更多时间查询案例；
   案例分享：

   //FROM_UNIXTIME 将时间戳转换为字符串日期;
   //UNIX_TIMESTAMP 将其他时间转换为时间戳;
   SELECT
     `yd_draw_prize_order`.`d_p_order_id`,
     yd_draw_prize_order_log.`rand_num`,
     FROM_UNIXTIME(yd_draw_prize_order.adopt_time) AS adopt_time,
     FROM_UNIXTIME(
       yd_draw_prize_order_log.draw_time
     ) AS '抽奖时间',
     yd_draw_prize_order_log.`d_p_order_activity_id`,
        yd_draw_prize_order_log.`d_p_order_activity_name`,
       yd_draw_prize_order_log.`draw_num`,
           yd_draw_prize_order_log.`winning`
   FROM
     `yd_draw_prize_order`
     INNER JOIN `yd_draw_prize_order_number`
       ON yd_draw_prize_order.d_p_order_id = yd_draw_prize_order_number.d_p_order_id
     INNER JOIN `yd_draw_prize_order_log`
       ON yd_draw_prize_order.d_p_order_id = yd_draw_prize_order_log.d_p_order_id
   WHERE `customer_phone` = '15512816085'
     AND `adopt` = 1
     AND   yd_draw_prize_order_number.`reward_num` = 04028
     AND   yd_draw_prize_order_log.draw_time > UNIX_TIMESTAMP(NOW())
      AND  yd_draw_prize_order_log.`d_p_order_activity_id` = 2
   

   案例2

   //日期子查询;
   SELECT
     *
   FROM
     `yd_extension_data_1`
   WHERE `distrbutor_id` IN
     (SELECT
       `distrbutor_id`
     FROM
       `yd_distrbutor`
     WHERE `distrbutor_provinceid` = 19)
     AND `create_time` BETWEEN '2017-01-01 00:00:01'
     AND '2018-01-01 00:00:00'
   AND `distrbutor_id` NOT IN (1,23)
   ORDER BY `create_time`
   

   　　

    

3. 综合查询&&综合更新
   综合查询案例1：
   
   

   1.
   SELECT
     b.id AS '用户id',
     user_name AS '用户名',
     `active_points` AS '积分',
     COUNT(bet_state) AS '猜中数',
     GROUP_CONCAT(bet_state) AS '统计情况',
     GROUP_CONCAT(match_id) AS '赛事ID',
     b.share AS '1为己分享',
     `use_points` AS '消耗积分'
   FROM
     yd_draw_worldcup_betting AS a
     INNER JOIN yd_draw_worldcup_user AS b
   WHERE a.`worldcup_user_id` = b.`id`
     AND a.`bet_state` IN (20, 21, 22)
   
     AND b.`use_points` <> 0
   GROUP BY id;
   
   2.
   
   SELECT
     b.id AS '用户id',
     user_name AS '用户名',
     `active_points` AS '积分',
     b.share + COUNT(bet_state) AS '自定义',
       COUNT(bet_state) AS '猜中数',
         b.share AS '1为己分享',
           `use_points` AS '消耗积分'
       
   FROM
     yd_draw_worldcup_betting AS a
     INNER JOIN yd_draw_worldcup_user AS b
   WHERE a.`worldcup_user_id` = b.`id`
     AND a.`bet_state` IN (20, 21, 22)
     AND b.`use_points` = 0
   GROUP BY id;

   View Code

    

   综合简单查询更新1：
   
   

   1.
   
   UPDATE
     `yd_draw_worldcup_user`
   SET
     `active_points` = `active_points` + 1
   WHERE `id` IN
     (SELECT
       `worldcup_user_id`
     FROM
       `yd_draw_worldcup_betting`
     WHERE `match_id` = 2
       AND `bet_state` IN (20, 21, 22))

   View Code

    综合链表更新2：

   

   1.
   update yd_draw_worldcup_user as a,yd_draw_worldcup_betting as b set 
   a.active_points = 0,
   b.bet_state = 10,
   b.draw_number = ''
    where b.match_id in (1,2,3,4,5) 
    and b.worldcup_user_id = a.id;

   View Code

    

4. 判断语句：
   

   UPDATE yd_draw_worldcup_user AS a
   SET a.active_points = 
   (CASE WHEN (a.active_points + a.`share` < a.use_points) THEN 0 ELSE (a.active_points + a.`share` - a.use_points) END)
    WHERE 1;

5. 事务语句：
   

   #mysql使用事务的关键字
   #begin //打开一个事务
   #commit //提交到数据库
   #rollback //取消操作
   #savepoint //保存，部分取消，部分提交
   #alter table person type=INNODB //修改数据引擎
   
   begin; 
   update tags set tagid = 133 where docid = 1;
   SAVEPOINT tags1;
   update tags set tagid = 530 where docid =2;
   SAVEPOINT tags2;
   ROLLBACK  TO SAVEPOINT tags2;
   SELECT * from tags where docid in(1,2);
   commit;
   

   　　

6. 自定义序列号字段:
   

   SET @rownum=0;
   SELECT
     @rownum:=@rownum+1 AS '序列',
     a.<You_TableName_Field> AS '用户名称'
   FROM
     (SELECT @rownum:=0) r,<You_TableName> AS a
   WHERE 1
   ORDER BY <You_TableName>.<You_TableName_Field> ASC
   

   　　

7. mysql update You can't specify target table 'yd_qr_code' for update in FROM clause . 
   
   原：update `yd_qr_code` set winning_description =  (select a.`winning_description` from `yd_qr_code` as a where a.`qr_id` = 1) where qr_id = 2
   原理：mysql 不能在同表操作更新，我们要用一个 中间表 来让数据库认为不是同表操作；
   后：update `yd_qr_code` set winning_description = (select b.`winning_description` from (select a.`winning_description` from `yd_qr_code` as a where a.`qr_id` = 1)b) where qr_id = 2
   
   
8. 【查询】数据库有哪些表；
   

   select table_schema as database_name,
       table_name
   from information_schema.tables
   where table_type = 'BASE TABLE'
       and table_name like '%__value%'
   order by table_schema,
       table_name;

   View Code

    

 --------------------------------

权限：

• 为用户 xuguo 添加"xu_gms" 数据库操作权限：GRANT ALL PRIVILEGES ON xu_gms.* to 'xuguo'@'%';  
• 数据库文件导出： mysqldump -u username -p dbname > filename.sql
• 查看字符集：show variables like '%char%';
  修改数据字符集：

  set character_set_database=utf8;
  set character_set_server=utf8;

• 修改全局变量max_allowed_packet：global max_allowed_packet = 2*1024*1024*10 ；    查看全局变量max_allowed_packet：show VARIABLES like '%max_allowed_packet%';
• --

---------------------