mysql

-- cmd导出数据库
C:\Users\Administrator.97SD-20110326TW> cd C:\ProgramFiles\MySQL\MySQL Server 5.1\bin
C:\ProgramFiles\MySQL\MySQL Server 5.1\bin>mysqldump -u root -p bbs>c:/test.txt
Enter password: **

 

二 修改mysql数据库的编码

--修改bbs数据库为gbk

mysql> alter database bbs default character set gbk collate gbk_bin; 

--修改bbs数据库为utf-8

mysql> alter database bbs default character set utf8 collate utf8_bin; 

--修改表为utf-8

mysql> alter table BBS_BOARD default character set utf8 collate utf8_bin; 

 

 

create database bbs;

use bbs;

CREATE TABLE BBS_USER (
  userid int AUTO_INCREMENT NOT NULL primary key ,
  username varchar(20) NOT NULL,
  password varchar(20) DEFAULT NULL,
  email varchar(20) DEFAULT NULL,
  regtime TIMESTAMP  not null default now(),
  gender char(2),
  UNIQUE (username)
);

-- userid int AUTO_INCREMENT NOT NULL primary key ，AUTO_INCREMENT 自动增长
--Date或DateTime类型是不能使用函数作为默认值的

insert into BBS_USER (username,password,email,gender )
values ('shabao','123',NULL,'男');
insert into BBS_USER (username,password,email,gender )
values ('saboo','123',NULL,'男');

create table BBS_BOARD
(
boardid int not null AUTO_INCREMENT primary key,
boardName varchar(100),
parentId int
);

insert into BBS_BOARD(boardName ,parentId)
values ('校园生活',0);

create table BBS_TOPIC
(
topicId int not null AUTO_INCREMENT primary key,
title varchar(50) not null ,
content varchar(255) not null,
publishTime TIMESTAMP  not null default now(),
modifyTime Date default NULL,
uid int not null,
boardId int not null,
FOREIGN KEY (uid) REFERENCES BBS_USER(userid),
FOREIGN KEY (boardId) REFERENCES BBS_BOARD(boardid)
);

insert into BBS_TOPIC(title,content,uid,boardId)
values('学习是动力','..........................',1,1);

create table BBS_REPLY
(
replyId int not null AUTO_INCREMENT primary key,
title varchar(50) not null ,
content varchar(255) not null,
publishTime TIMESTAMP  not null default now(),
modifyTime Date default NULL,
uid int not null,
topicId int not null,
FOREIGN KEY (uid) REFERENCES BBS_USER(userid),
FOREIGN KEY (topicId) REFERENCES BBS_TOPIC(topicId)
);

insert into BBS_REPLY(title,content,uid,topicId)
values('这样','加油',3,1);

select * from BBS_USER;
select * from BBS_BOARD;
select * from BBS_TOPIC;
select * from BBS_REPLY;