大猪 & 大兔

就是这

http://7392072.blog.51cto.com/7382072/1325176

查询

# 附带条件
select * from qiushi_gif where source=20 and act_status<>7 order by id desc limit 10;
# 多重条件
select * from qiushi_gif where source=25 and created_at>'2017-04-16 17:50:42' and (act_status <> 7 or status='damage');
# 查询数量
select count(id) from qiushi_gif where source=20 and act_status<>7;

修改

update qiushi_gif set status='publish' where id=XX;

删除

delete from qiushi_gif where id = xx;
# 删库跑路
cat databases.txt | xargs -I XXX mysql -uroot -h10.0.8.45 -p'password' --default-character-set=utf8 -e "drop database XXX;"

创建表

普通表

create table USER(
    id int not null primary key auto_increment,
    name char(20) not null default '',
    age int(4) not null default 0,
    is_admin int not null default 0,
    created_time datetime not null default now()
    );
# 在远古时代也许是这样
create table news(
    id bigint(20) not null primary key auto_increment,
    title varchar(120) not null default '',
    content text not null default '',
    author varchar(100) not null default '',
    clicks int not null default 0,
    status int(8) not null default 0,
    category varchar(100) not null default '',
    created_time timestamp not null default CURRENT_TIMESTAMP
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;

关联表

create table ARTICLE(
    id int not null primary key auto_increment,
    article_name char(20) not null default '',
    uid int not null default 0,
    article_pages int not null default 0,
    created_time datetime not null default now()
    );
alter table article add constraint `user_article` foreign key (uid) references user(id) on delete cascade on update cascade;

级联

left join

select news.id, news.title, news.created_time, pics.id as pid ,pics.url from news left join pics on (news.id = pics.nid) where news.created_time < %s and pics.status=3 limit %s;

修改表

alter table MyClass add passtest int(4) default '0';