mysql数据库操作

就是这

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

查询

代码语言：javascript复制

# 附带条件
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;

修改

代码语言：javascript复制

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

删除

代码语言：javascript复制

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;"

创建表

普通表

代码语言：javascript复制

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;

关联表

代码语言：javascript复制

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

代码语言：javascript复制

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;

修改表

代码语言：javascript复制

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

join

0 人点赞