รวมคำสั่ง MYSQL ที่ใช้งานบ่อยๆ
แทนที่ข้อความในฟิลด์
แทนที่ข้อความ 3101 ด้วย 2558 ในฟิลด์ date_permit
UPDATE business SET date_permit = REPLACE(date_permit, "3101", "2558");
insert ignore สำหรับ insert ข้อมูลลงตาราง โดยไม่สนใจว่าข้อมูลจะซ้ำหรือไม่
insert ignore into user
select rtrim(`cmb`.`name`) AS `fname`,rtrim(`cmb`.`lname`) AS `lname` from `cmb` ;
inner join fnamelname b on rtrim(a.fname)=b.fname and rtrim(a.lname)=b.lname
where flag is null order by tmpid*1 asc) j on k.tmpid=j.tmpid
set k.flag='Y';
update pnd k inner join (select num from pnd where length(cwd_code)<8) j on k.num=j.num
set k.cwd_code=CONCAT("0",k.cwd_code)
update pnd k
inner join
(select num from pnd where cast(num as UNSIGNED)>=3044) j on k.num=j.num
set k.date_import='01122558'
update pnd k
inner join
(select provincename,officecode from office ) j on j.officecode=k.code
set k.province=j.provincename
where cast(k.num as UNSIGNED)>=3044
select rtrim(`cmb`.`name`) AS `fname`,rtrim(`cmb`.`lname`) AS `lname` from `cmb` ;
คำสั่ง update
update step2 k inner join (select tmpid from step2 ainner join fnamelname b on rtrim(a.fname)=b.fname and rtrim(a.lname)=b.lname
where flag is null order by tmpid*1 asc) j on k.tmpid=j.tmpid
set k.flag='Y';
update pnd k inner join (select num from pnd where length(cwd_code)<8) j on k.num=j.num
set k.cwd_code=CONCAT("0",k.cwd_code)
update pnd k
inner join
(select num from pnd where cast(num as UNSIGNED)>=3044) j on k.num=j.num
set k.date_import='01122558'
update pnd k
inner join
(select provincename,officecode from office ) j on j.officecode=k.code
set k.province=j.provincename
where cast(k.num as UNSIGNED)>=3044
คำสั่ง concat
select
tin,pin,CONCAT(substring(date,5,4),substring(date,3,2),substring(date,1,2))
as linedate
from
formsl4
where
CONCAT(substring(date,5,4),substring(date,3,2),substring(date,1,2)) > 25570910
update pnd set pin=CONCAT('0',pin) where length(triM(pin))=12 and cast(num as UNSIGNED)>=3044
คำสั่งเรียงข้อมูลตัวอักษร โดยแปลงค่าเป็นตัวเลขก่อน
select * from pnd_tmp order by cast(num as UNSIGNED ) desc
select * from pnd_tmp order by cast(num as UNSIGNED ) desc