mysql的sql语句

mysql替换某字符串的sql:

update 表名 set 字段=replace(字段,'替换值','替换后的值') where * 查询条件;

eg:update vbook_user set user_adr=replace(user_adr,'A','a') where user_sex=0;

 

用','隔开字段值：

select bkt.*,(select group_concat(ct.teacher_name) from bk_register bkr

left join course_teacher ct on bkr.teacher_id=ct.teacher_id

where bkr.timetable_id = bkt.timetable_id) as tids from bk_timetable bkt;

 

 

删除重复数据

delete from tableA where id not in (select id from (select min(id)

as id from tableA  group by a_id) tableB)

 

 

排序号

selectuser_id,paper_id,us.rankfrom(
selectuscore.es_id,uscore.user_id,uscore.paper_id,@rownum:=@rownum+1,if(@paper=uscore.paper_id,@rank:=@rank+1,@rank:=1)asrank,@paper:=uscore.paper_idfrom
(selectuser_id,paper_id,es_idfromexam_scorewherees_last=1groupbypaper_id,user_idorderbypaper_iddesc,scoredesc)asuscore,
(select@rownum:=0,@paper:=null,@rank:=0)asranks
)asus

  

 select(@i:=@i+1)asifromtablename,(select@i:=0)asit