• #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%';
    • --

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

  • 相关阅读:
    MyBatis的动态SQL详解
    Mybatis实现Mapper动态代理方式
    解决-Dmaven.multiModuleProjectDirectory system property is not set. Check $M2_HOME environment variable and mvn script match.
    springmvc前台向后台传值几种方式从简单到复杂
    Maven 项目无法在Ecplise加进tomcat server
    解决-Dmaven.multiModuleProjectDirectory system property is not set. Check $M2_HOME environment variable and mvn script match.
    tomcat启动startup.bat一闪而过
    用Eclipse创建一个Maven Web项目
    使用Yeoman搭建 AngularJS 应用 (8) —— 让我们搭建一个网页应用
    使用Yeoman搭建 AngularJS 应用 (7) —— 让我们搭建一个网页应用
  • 原文地址:https://www.cnblogs.com/q1104460935/p/8900412.html
Copyright © 2020-2023  润新知