数据库表数据增删改查

目录

• 新增
• 修改
• 查询
• 删除
• 注意

新增和批量新增insert

• insert into select

INSERT INTO order_desc (member_id, apply_no, content)
SELECT member_id, apply_no, content FROM order_desc2 WHERE APPLY_TYPE = 'MOBILE' GROUP BY content ORDER BY COUNT(content) DESC;

修改和批量修改update

• 单条更新

update table_name set username = null, age = null where id = '1433233';

• 批量更新

<update id="batchUpdateErrorList">
  INSERT INTO `amos_phone` (ID, TALK_TIME, TALE_END_TIME)
  VALUES
  <foreach collection="list" item="item" separator=",">
      (#{item.id},#{item.talkTime},#{item.taleEndTime})
  </foreach>
  ON DUPLICATE KEY UPDATE TALK_TIME=values(TALK_TIME), TALE_END_TIME=values(TALE_END_TIME)
</update>

查询select

1. 日期

    AND DATE_FORMAT(BEGIN_TIME, '%Y-%m-%d') <= '2019-08-01' AND DATE_FORMAT(END_TIME, '%Y-%m-%d') >= '2019-08-31'

    <!-- MyBatis Mapper.xml 大于等于小于 -->
    <where>
        <if test="name != null and name != ''">AND NAME = #{name}</if>
        <if test="beginTime != null">AND CREATE_TIME <![CDATA[ >= ]]> #{beginTime}</if>
        <if test="endTime != null">AND CREATE_TIME <![CDATA[ <= ]]> #{endTime}</if>
    </where>

    SELECT COUNT(*), run_time, (run_min * 60 + run_sec)
    FROM(
        SELECT
            APPLY_NO 'apply_no',
            COUNT(*) 'count',
            TIMEDIFF(MAX(CREATE_TIME), MIN(CREATE_TIME)) 'run_time',
            DATE_FORMAT(TIMEDIFF(MAX(CREATE_TIME), MIN(CREATE_TIME)), '%i') 'run_min',
            DATE_FORMAT(TIMEDIFF(MAX(CREATE_TIME), MIN(CREATE_TIME)), '%s') 'run_sec',
            MIN(CREATE_TIME) 'begin',
            MAX(CREATE_TIME) 'end'
        FROM `rule_result`
        GROUP BY APPLY_NO ORDER BY CREATE_TIME DESC
    ) a GROUP BY run_time ORDER BY run_time DESC;

    -- 当日
    SELECT DAY(NOW()) CURRENT_DAY;
    -- 当月 2019-09-09
    SELECT DATE(NOW()) CURRENT_MONTH;
    -- 当月 201909
    SELECT DATE_FORMAT(NOW(), '%Y%m') CURRENT_MONTH;
    -- 上个月 201908
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH),'%Y%m') LAST_MONTH;
    -- 如下两个等价SQL
    SELECT DATE_FORMAT(NOW(),'%Y-%d-%m %h:%i:%s');
    SELECT DATE_FORMAT(NOW(),'%Y-%d-%m %T');

2. MyBatis Mapper.xml foreach

    <if test="search.memberIds != null">
     AND MEMBER_ID IN (<foreach collection="search.memberIds" item="item" separator=",">#{item}</foreach>)
    </if>

3. MyBatis if condition

    "," "=" "?" "||" "or" "&&" "and" "|" "bor" "^" "xor" "&" "band"
    "==" "eq" "!=" "neq" "<" "lt">" "gt" "<=" "lte" ">=" "gte" "in"
    "not" "<<" "shl" ">>" "shr" ">>>" "ushr" "+" "-" "*" "/" "%" "."
    "(""instanceof"  "[" "]" <![CDATA[ <= ]]>

4. 分组字段合并

    SELECT
    phone.ID,
    phone.`NAME`,
    GROUP_CONCAT(business.`NAME`) BUSINESS_NAMES
    FROM `amos_phone` phone
    LEFT JOIN `amos_business` business ON phone.BUSINESS_ID REGEXP business.ID
    WHERE phone.ID = '528a926949f1a8a0bb67234f7c837fac'
    GROUP BY phone.ID;

5. HAVING

    SELECT MEMBER_ID, COUNT(MEMBER_ID) FROM contact_info GROUP BY MEMBER_ID HAVING COUNT(MEMBER_ID) > 5;

6. OR

    -- 看两个等价sql
    select * from user where condition1 or condition2 and condition3;
    select * from user where condition1 or (condition2 and condition3);
    -- 故可以得出结论：and级别高于or, 清晰起见建议使用2

7. CONCAT

    SELECT CONCAT('姓名：',"张三",'，手机号：', "13066668888") MESSAGE;

8. REGEXP

   • REGEXP 默认不区分大小写
     • 能匹配出来，结果为 1 select 'hello' REGEXP 'HelLo';
     • 不能匹配出来，结果为 0 select 'hello' REGEXP BINARY 'HelLo';
   • REGEXP 匹配多个
     • 数字、A01、A02，结果为 1 SELECT 'A0' REGEXP '[0-9]|A01|A02' RESULT;

9. MySQL、SQL Server分页相关

• MySQL

    SELECT * FROM `amos_user` ORDER BY ID DESC LIMIT 0, 10;

• SQL Server

    -- 查询前10条
    SELECT TOP 10 * FROM amos_user;
    
    -- 分页查询20条数据 [index: 20 to 40]
    
    -- 分页 by WHERE
    SELECT amos_user.*
    FROM amos_user, (SELECT TOP 40 row_number () OVER (ORDER BY CREATE_TIME) n, id FROM amos_user) temp_user
    WHERE amos_user.id = temp_user.id AND temp_user.n > 20
    
    -- 分页 by JOIN
    SELECT amos_user.*
    FROM amos_user
    INNER JOIN (SELECT TOP 40 row_number () OVER (ORDER BY username) n, id FROM amos_user) temp_user 
    ON amos_user.id = temp_user.id AND temp_user.n > 20;

10. COUNT(TYPE = 1 OR NULL) TYPE_COUNT

    COUNT(TYPE = 1 OR NULL) TYPE_COUNT ... [optional: HAVING TYPE_COUNT > 0]

删除delete

注意notice

• 字段不为空，是 IS NOT NULL，而非 != null

---

• 存储过程命名规范
  1. 业务相关以模块代码开头 order_procedure
  2. 如果区分全量和增量，在最后加标识 order_procedure order_procedure_full
  3. 全局使用，以global开头 global_order_procedure
• 存储过程内部变量命名规范
  1. 变量（以 V 开头）V_SCORE
  2. 游标（以 C 开头）C_CURSOR
  3. 内存表（以 M 开头）M_TABLE
  4. 临时表（以 T 开头）T_TABLE

---