mysql数据库操作实例

1，添加字段

ALTER TABLE `HS_SCORE_2020`
    ADD COLUMN `STATE`  INT(11)   DEFAULT '0' COMMENT '状态，0或空未推送，1成功、2失败' AFTER `ORG_UID`;

ALTER TABLE `HS_SCORE_2020`
    ADD COLUMN `FAIL_DESC`  VARCHAR(200) NULL COMMENT '失败原因说明' COLLATE 'utf8mb4_general_ci' AFTER `STATE`;

 

2，添加索引

ALTER TABLE `HS_SCORE_2020`
    ADD INDEX `IDX_SCORE_STATE` (`STATE`);

3，根据条件查询不同表数据

            
SELECT
    hsc.HS_CORE_UID AS hsCoreUid,hsc.IDCARD AS idcard,hsc.ORG_UID AS orgUid,hsc.SOURCE AS SOURCE,
    hsc.BPITEM AS bpitem,hsc.PONINT AS ponint,
    hsc.SDATE AS sdate,hsc.CHILD_CAREID AS childCareid,
  (case

    when  hsc.BPITEM IN (13,37) 
    then
         jz.jztxm 
    ELSE 
         jc.jztxm END) AS jztxm,
  (case

    when  hsc.BPITEM IN (13,37) 
    then
         jz.chilname
    ELSE 
         jc.etxm  END) AS chrpname,
    (case

    when  hsc.BPITEM IN (13,37)  
    then
        jz.etxb
    ELSE 
       jc.etxbdm  END) AS chrsex,
       
    (case

    when  hsc.BPITEM IN (13,37) 
    then
         jz.etcsrq
    ELSE 
         jc.etcsrq  END) AS chrbirthday,
    (case

    when  hsc.BPITEM IN (13,37) 
    then
        jz.etmzmc
    ELSE 
       jc.etmzdm  END) AS chrrace,
    (case

    when  hsc.BPITEM IN (13,37) 
    then
         jz.jdrq
    ELSE 
       jc.jdrq  END) AS chrardate,
    (case

    when  hsc.BPITEM IN (13,37) 
    then
         jz.hjlx
    ELSE 
        jc.hjlx END) AS chrhouseType,
       
    (case

    when  hsc.BPITEM IN (13,37) 
    then
         jz.jzsx
    ELSE 
       jc.jzsx  END) AS chrliveType,  
    
    jc.jhrgx  AS pref, jz. fqsfzjh,jz.smsfzjh
         
    FROM
    hs_score_2020 hsc
    LEFT JOIN app_tb_yfjz_etxx jz ON hsc.CHILD_CAREID=jz.chilcode
    LEFT JOIN app_tb_eb_etxx jc ON hsc.HS_CORE_UID=jc.chilcode

 4,根据不同机构获取最近一条数据

SELECT * FROM (
SELECT pt.ORG_RECURE_UID AS orgRecureUid,pt.SDATE AS sdate,pt.FERITIN AS feritin,bm.FR AS fr,pt.DIABETES AS diabetes,bm.DBR AS dbr
FROM hs_org_ph_tally pt,hs_org_basic_msg bm
WHERE pt.ORG_RECURE_UID=bm.ORG_RECURE_UID
ORDER BY pt.ORG_RECURE_UID DESC,pt.SDATE DESC) r GROUP BY r.orgRecureUid

 5,查询重复数据

select * from app_feedback
where opinion_id in (select opinion_id from app_feedback group by opinion_id having count(*) > 1)

 

6,将表中某字段某值排第一位

  SELECT
      ahp.APP_HOME_PAGE_UID as appHomePageUid,
      ahp.MODULE_METHOD as moduleMethod,
      ahp.METHOD_EXPLAIN as methodExplain,
      ahp.LEVEL as level,
      ahpr.CLICK_NUMBER as clickNumber,
      ahp.IMAGE_URL as imageUrl,
      ahp.WEB_URL as webUrl
  FROM
      app_home_page ahp
      LEFT JOIN app_home_page_record ahpr ON ahp.APP_HOME_PAGE_UID = ahpr.APP_HOME_PAGE_UID
      AND ahpr.app_user_uid = 737204
  WHERE
      ahp.STATE =1
  ORDER BY
          ahp.APP_HOME_PAGE_UID NOT IN (1) ASC,
      ahp.SERIAL desc,
      ahpr.CLICK_NUMBER DESC,
      ahpr.UPDATE_TIME ASC,ahp.APP_HOME_PAGE_UID ASC;