nanakon

 

2、安装mysql

3、创建数据库

创建users表

CREATE TABLE `users` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `create_time` datetime NOT NULL,
  `nickname` varchar(32) NOT NULL,
  `password` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `avatar` varchar(100) DEFAULT NULL,
  `verify` varchar(10) NOT NULL,
  `fans_count` int(10) unsigned NOT NULL,
  `follow_count` int(10) unsigned NOT NULL,
  `note_count` int(10) unsigned NOT NULL,
  `description` varchar(1000) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
)

 

修改字段名

mysql> alter table users change id user_id INT UNSIGNED  AUTO_INCREMENT;

alter table users add expires datetime null;

ALTER TABLE users ADD sns VARCHAR(20) NULL;
ALTER TABLE users ADD sns_uid VARCHAR(50) NULL;

ALTER TABLE users ADD sns_access_token VARCHAR(100) NULL;
ALTER TABLE users ADD sns_expires datetime NULL;

创建follow表

CREATE TABLE `follow` (
  `create_time` datetime NOT NULL,
  `follower_id` int(10) unsigned NOT NULL,
  `feeder_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`follower_id`,`feeder_id`),
  KEY `feeder_id` (`feeder_id`),
  CONSTRAINT `follow_ibfk_1` FOREIGN KEY (`follower_id`) REFERENCES `users` (`user_id`),
  CONSTRAINT `follow_ibfk_2` FOREIGN KEY (`feeder_id`) REFERENCES `users` (`user_id`)
)

 

创建filter表

CREATE TABLE filter
(
filter_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(filter_id),
name VARCHAR(40) NOT NULL,
tag_count INT UNSIGNED NOT NULL,
user_id INT UNSIGNED NOT NULL,FOREIGN KEY (user_id) REFERENCES users (user_id)
);

创建tag表

CREATE TABLE tag
(
tag_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(tag_id),
name VARCHAR(40) NOT NULL, 
link VARCHAR(100) NULL,
picture VARCHAR(100) NULL,
description VARCHAR(200) NULL,
privacy VARCHAR(10) NOT NULL, 
note_count INT UNSIGNED NOT NULL,
create_time DATE NOT NULL,user_id INT UNSIGNED NOT NULL,
filter_id INT UNSIGNED NULL,FOREIGN KEY (user_id) REFERENCES users (user_id),
FOREIGN KEY (filter_id) REFERENCES filter (filter_id)
);

修改

ALTER TABLE tag ADD width int(5) NULL;
ALTER TABLE tag ADD height int(5) NULL;

alter table tag change filter_id filter_id INT UNSIGNED NULL;

创建note表

CREATE TABLE note
(
note_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(note_id),
type VARCHAR(20) NOT NULL,
create_time DATE NOT NULL,
privacy VARCHAR(10) NOT NULL,
user_id INT UNSIGNED NOT NULL,
tag_id INT UNSIGNED NULL,
FOREIGN KEY (user_id) REFERENCES users (user_id),
FOREIGN KEY (tag_id) REFERENCES tag (tag_id)
);

ALTER TABLE note ADD is_feed VARCHAR(10) NOT NULL；

创建markdown

CREATE TABLE markdown
(
note_id INT UNSIGNED NOT NULL,
PRIMARY KEY(note_id),
title VARCHAR(100) NOT NULL,
content VARCHAR(10000) NOT NULL,
FOREIGN KEY (note_id) REFERENCES note (note_id)
);

 

创建word表

CREATE TABLE word
(
note_id INT UNSIGNED NOT NULL,
PRIMARY KEY(note_id),
word VARCHAR(10000) NOT NULL,
FOREIGN KEY (note_id) REFERENCES note (note_id)
);

alter table word change word_content word VARCHAR(10000) NOT NULL;

创建picture表

CREATE TABLE picture
(
note_id INT UNSIGNED NOT NULL,
PRIMARY KEY(note_id),
pictrue_content VARCHAR(100) NOT NULL,
pictrue_description VARCHAR(200) NULL,
FOREIGN KEY (note_id) REFERENCES note (note_id)
);

alter table picture change pictrue_content picture  VARCHAR(100) NOT NULL;
alter table picture change pictrue_description description VARCHAR(200) NULL;

 

创建拓扑图

CREATE TABLE `topologys` (
    `name` varchar(100) NOT NULL,
    `status` varchar(10) NOT NULL,
    `type` varchar(10) NOT NULL,
    `description` varchar(1000) NULL,
    `note_id` int unsigned NOT NULL,
    PRIMARY KEY (`note_id`),
    FOREIGN KEY (`note_id`) REFERENCES `note` (`note_id`)
)

CREATE TABLE `topology` (
    `topology_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `create_time` datetime NOT NULL,
    `name` varchar(100) NOT NULL,
    `status` varchar(10) NOT NULL,
    `type` varchar(10) NOT NULL,
    `link` varchar(100) NULL,
    `description` varchar(1000) NULL,
    `parent_id` int unsigned NULL,
    `note_id` int unsigned NOT NULL,
    PRIMARY KEY (`topology_id`),
    FOREIGN KEY (`parent_id`) REFERENCES `topology` (`topology_id`),
    FOREIGN KEY (`note_id`) REFERENCES `note` (`note_id`)
)

查询note

SELECT note.note_id, note.type, note.create_time, note.privacy, note.user_id, note.tag_id, 
word.word as word_content, 
picture.picture as picture_content, picture.description as picture_description,
users.nickname, users.avatar
FROM note LEFT JOIN word ON note.note_id = word.note_id 
LEFT JOIN picture ON note.note_id = picture.note_id
LEFT JOIN users ON note.user_id = users.user_id;

创建video表

CREATE TABLE video
(
note_id INT UNSIGNED NOT NULL,
PRIMARY KEY(note_id),
pageurl VARCHAR(100) NOT NULL,
swfurl VARCHAR(100) NOT NULL,
preview VARCHAR(100) NOT NULL,
description VARCHAR(200) NULL,
FOREIGN KEY (note_id) REFERENCES note (note_id)
);

创建link表

CREATE TABLE link
(
note_id INT UNSIGNED NOT NULL,
PRIMARY KEY(note_id),
link VARCHAR(100) NOT NULL,
description VARCHAR(200) NULL,
FOREIGN KEY (note_id) REFERENCES note (note_id)
);

创建card表

CREATE TABLE card
(
note_id INT UNSIGNED NOT NULL,
PRIMARY KEY(note_id),
name VARCHAR(32) NOT NULL,
picture VARCHAR(100) NOT NULL,
location VARCHAR(100) NULL,
weibo VARCHAR(100) NULL,
blog VARCHAR(100) NULL,
taobao VARCHAR(100) NULL,
facebook VARCHAR(100) NULL,
twitter VARCHAR(100) NULL,
instagram VARCHAR(100) NULL,
tumblr VARCHAR(100) NULL,
github VARCHAR(100) NULL,
link VARCHAR(100) NULL,
description VARCHAR(200) NULL,
FOREIGN KEY (note_id) REFERENCES note (note_id)
);

alter table follow change create_time create_time DATETIME NOT NULL;
alter table note change create_time create_time DATETIME NOT NULL;
alter table tag change create_time create_time DATETIME NOT NULL;
alter table users change create_time create_time DATETIME NOT NULL;

收藏

CREATE TABLE `tagfav` (
`tag_id` INT UNSIGNED NOT NULL,
`user_id` INT UNSIGNED NOT NULL,
`create_time` datetime NOT NULL,
PRIMARY KEY (`tag_id`, `user_id`),
KEY `user_id` (`user_id`),
FOREIGN KEY (`tag_id`) REFERENCES `tag` (`tag_id`),
FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
);

CREATE TABLE `notefav` (
`note_id` INT UNSIGNED NOT NULL,
`user_id` INT UNSIGNED NOT NULL,
`create_time` datetime NOT NULL,
PRIMARY KEY (`note_id`, `user_id`),
KEY `user_id` (`user_id`),
FOREIGN KEY (`note_id`) REFERENCES `note` (`note_id`),
FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
);

点赞 

CREATE TABLE `taglike` (
`tag_id` INT UNSIGNED NOT NULL,
`user_id` INT UNSIGNED NOT NULL,
`create_time` datetime NOT NULL,
PRIMARY KEY (`tag_id`, `user_id`),
KEY `user_id` (`user_id`),
FOREIGN KEY (`tag_id`) REFERENCES `tag` (`tag_id`),
FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
);

CREATE TABLE `notelike` (
`note_id` INT UNSIGNED NOT NULL,
`user_id` INT UNSIGNED NOT NULL,
`create_time` datetime NOT NULL,
PRIMARY KEY (`note_id`, `user_id`),
KEY `user_id` (`user_id`),
FOREIGN KEY (`note_id`) REFERENCES `note` (`note_id`),
FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
);

alter table tag drop FOREIGN KEY `tag_ibfk_1`;

alter table `tag` add CONSTRAINT `tag_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `user_category` (`category_id`) ;