Mysql常用语句1

   1. date_format(caldate,'%Y-%m-%d')
   2. 截取字符串：left,right
   3. 截断表：TRUNCATE table sproject_tab;
   4. 追加字符串：update tk_main_question set keyword=CONCAT(keyword,',海南模拟') 
   5. 替换：update table set name=replace(name,'aa','bb') 
   6. 子查询+limit:
        update tk_main_question set keyword='海南' where id in 
        (select * from (select id from tk_main_question where  item_type=1 and category=1 and `status`=2 order by id limit 620) tp )
    7.存在插入：
        into sdors_tab(project_id,dors_code,dors_name,dors_email) select {$project_id},{$doctor['id']},'{$doctor['name']}','{$doctor['email']}' from dual where not EXISTS (select id from sdors_tab where dors_email='{$doctor['email']}');
    8.取差值：
        TO_DAYS(project_endtime)-TO_DAYS(project_starttime)
    9.数据表自身信息：INFORMATION_SCHEMA.COLUMNS
    10.日期查询：
        update exam_paper set `status`=2 where  date_format(add_time,'%Y-%m-%d')='2015-10-08';
    11.批量插入：
        insert into sdors_tab(project_id,dors_code,dors_name,dors_email) select 27,id,name,email from query_info_doctor where email<>'' and doctor_profession1<>'传染科' order by id desc LIMIT 80; 
    12.去重：
        SELECT id,name,email,count(DISTINCT email) from query_info_doctor where email<>'' {$cond} GROUP BY email;
    13.查找字符串：
        SELECT  * from yn_kh where locate('E',身份证号)=0;
    14.分组统计：
        select *,count(准考证号) as num from yn_pp where 医师姓名<>'' GROUP BY 准考证号 having num=1;
    15.子查询+Limit
        select * from (select id from tk_main_question 
        where `status`=4 and item_type=1 and category=5 order by id desc limit 16000) as tab1 order by rand() limit 200;
    16.Mysql查询追加序号：
        select @rownum:=@rownum+1 as sort,id from (SELECT @rownum:=0) r,exam_main_question where pid='384' order by sort；
    17.两表批量更新：
        update exam_main_question INNER JOIN (
        select @rownum:=@rownum+1 as sort,id from (SELECT @rownum:=0) r,exam_main_question where pid='384' order by sort) b 
        on exam_main_question.id=b.id set exam_main_question.sort=b.sort
    18.随机取数：
        select * from tk_main_question where `status`=4 and edit_uid=22 and category=1 order by RAND() LIMIT 400;
    19.取数更新：
        update tk_main_question set STATUS='1' where id in(
        select id from (
        select id from (
        select * from tk_main_question where `status`=4 and  category=1 and (edit_uid=22 or edit_uid=19) order by id desc LIMIT 16000) tab1
        where specialty_code=105 and edit_uid=19 order by id desc LIMIT 300) tab2)
    20.两表更新：
        update yn_score p right JOIN yn_err on yn_err.`准考证号`=p.`准考证号` set p.分数=yn_err.`核查` ;
    21.随机更新：
        update tk_main_question set status='6' where  item_type=2 and category=5 and edit_uid in (19,22) and `status`<>2 order by RAND() LIMIT 87;
    22.MySQL注释：
        /**/
    23.字符串包含：
    select t.*,d.`name` as dor_name,s.`name` as tem_name,d.full_hospital from template_items t
                left join doctors d on d.id=t.doctor_id
                left join templates s on s.id=t.template_id where t.doctor_id=182 and find_in_set('1',tag_id) order by id desc  limit 1; 
    24.一对多联查:
        select patients.*,t.tags as tag_name from patients 
              left join (select patient_id,GROUP_CONCAT(name) as tags from patient_tags LEFT JOIN tags on  tags.id=patient_tags.tag_id GROUP BY patient_id) t
                    on patients.id=t.patient_id ORDER BY patients.id des
    25.多表更新：
        update tk_option INNER JOIN tk_main_question on tk_main_question.serial_code=tk_option.ques_serial_code
        set tk_option.main_qid=tk_main_question.id
        where tk_main_question.source is not null and tk_main_question.item_type=1;
        update tk_main_question INNER JOIN tk_option on tk_main_question.serial_code=tk_option.ques_serial_code
        set tk_main_question.answer=tk_option.id where tk_main_question.source is not null and tk_main_question.item_type=1 and tk_option.sort=tk_option.true_answer
    26.创建表：
        drop table if exists Sexport_temp; 
        CREATE TABLE `export_temp` (
          `answer` varchar(200) DEFAULT NULL,
          `ques_serial_code` varchar(200) DEFAULT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;