############################################################
# #
# 数据库表 #
# #
############################################################
# 删除数据库
DROP database IF EXISTS sweet;
# 创建数据库
create database sweet;
# 使用数据库
use sweet;
#
# 留言表
#
DROP TABLE IF EXISTS message;
CREATE TABLE message (
mid int unsigned not null auto_increment COMMENT '留言的id',
name varchar(128) not null COMMENT '昵称',
content varchar(6666) not null COMMENT '消息内容',
pubtime timestamp default current_timestamp COMMENT '发送时间,默认值',
examine tinyint NOT NULL DEFAULT 0 COMMENT '审核程度,0未审,1已审,2屏蔽',
PRIMARY KEY (mid)
);
############################################################
# #
# 插入数据 #
# #
############################################################
insert into message(name, content) values('admin', 'one');
insert into message(name, content) values('admin', 'two');
insert into message(name, content) values('admin', 'three');
insert into message(name, content) values('admin', 'four');
insert into message(name, content) values('admin', 'five');
insert into message(name, content) values('admin', 'six');
############################################################
# #
# 查询语句 #
# #
############################################################
select * from message;
# 查询所有未被屏蔽的博客数量
select count(*) as total from message where examine<>2;
# 分页查询所有未被屏蔽的博客,1:0-3, 2:3-3 3:6-3
select * from message where examine<>2 order by mid desc limit 0,3;
select * from message where examine<>2 order by mid desc limit 3,6;
# 查询所有未审核的博客
select * from message where examine=0 order by mid asc;
# 查询所有审核通过的博客
select * from message where examine=1 order by mid asc;
# 查询所有审核屏蔽的博客
select * from message where examine=2 order by mid asc;
# 设置某个留言审核通过
update message set examine=1 where mid=1;
# 设置某个留言审核屏蔽
update message set examine=2 where mid=2;
# #
# 数据库表 #
# #
############################################################
# 删除数据库
DROP database IF EXISTS sweet;
# 创建数据库
create database sweet;
# 使用数据库
use sweet;
#
# 留言表
#
DROP TABLE IF EXISTS message;
CREATE TABLE message (
mid int unsigned not null auto_increment COMMENT '留言的id',
name varchar(128) not null COMMENT '昵称',
content varchar(6666) not null COMMENT '消息内容',
pubtime timestamp default current_timestamp COMMENT '发送时间,默认值',
examine tinyint NOT NULL DEFAULT 0 COMMENT '审核程度,0未审,1已审,2屏蔽',
PRIMARY KEY (mid)
);
############################################################
# #
# 插入数据 #
# #
############################################################
insert into message(name, content) values('admin', 'one');
insert into message(name, content) values('admin', 'two');
insert into message(name, content) values('admin', 'three');
insert into message(name, content) values('admin', 'four');
insert into message(name, content) values('admin', 'five');
insert into message(name, content) values('admin', 'six');
############################################################
# #
# 查询语句 #
# #
############################################################
select * from message;
# 查询所有未被屏蔽的博客数量
select count(*) as total from message where examine<>2;
# 分页查询所有未被屏蔽的博客,1:0-3, 2:3-3 3:6-3
select * from message where examine<>2 order by mid desc limit 0,3;
select * from message where examine<>2 order by mid desc limit 3,6;
# 查询所有未审核的博客
select * from message where examine=0 order by mid asc;
# 查询所有审核通过的博客
select * from message where examine=1 order by mid asc;
# 查询所有审核屏蔽的博客
select * from message where examine=2 order by mid asc;
# 设置某个留言审核通过
update message set examine=1 where mid=1;
# 设置某个留言审核屏蔽
update message set examine=2 where mid=2;
update message set examine=2 where mid<7;
用该实例做的网站
http://www.fengyunxiao.cn