可兼容的多条插入sql:使用“insert….;insert….;insert….;”

insert

insert into tb_operator (id, real_name, cms_user, no, department, created_at, updated_at) values(16, 'name', 'yannis', '072', 'dept', now(), now())

where and join

select a.sn, concat_ws(' ~ ', date_format(a.ad_begin_at, '%Y-%m-%d'), date_format(a.ad_end_at, '%Y-%m-%d')), a.sum, c.title, d.name, d.channel, a.created_at, b.cms_user
from 
( 
  select * from tb_out_of_pocket 
  where ad_begin_at >= '20150101' and ad_end_at < '20150301' and is_deleted = false
) as a
left join tb_due b on a.due_id = b.id 
left join tb_payment_template c on b.payment_template_id = c.id 
left join tb_partner d on a.partner_id = d.id

Alter

alter table table_name add column rank int(11) default 2
alter table table_name modify column rank int(11) default 2 not null
alter table tb_copyright_content 
            add column price_mode varchar(255) after right_type, 
            add column minimum_price varchar(255) after right_type, 
            add column divide_rate varchar(255) after right_type

sql index:

alter table table_name add index(status)
alter table table_name add unique index(status)  !!! unique
show index from table_name

Update

update tb_body_content_track set beian_status = 1, is_deleted = false
where beian_status != 11

批量更新

update tb_test set title = case
when id = 2 then 't1'
when id = 3 then 't2'
else title
end, updated_at = now()
/* where id in (2, 3) */

结尾的case之后的else,否则将会把该表的其余部分的title置为nullwhere语句可以起到和else相同的作用。

Delete

delete from tb_test
where content_type = 1

数据库的charset

use mydatabase
select @@character_set_database

alter database mydatabase character set utf8 collate utf8_unicode_ci

SHOW FULL COLUMNS FROM table_name

ALTER TABLE t MODIFY col1 VARCHAR(50) CHARACTER SET greek