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;
