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`) ;

最新文章

  1. @SuppressWarnings注解的用法
  2. c语言实现面向对象OOC
  3. [转载]C#获取进程的主窗口句柄
  4. SpringMVC 常用注解
  5. 使用javascript生成当前博文地址的二维码图片
  6. order by group by
  7. 团队作业4——第一次项目冲刺（Alpha版本）11.16
  8. CSS有哪些引入方式，link和@import的区别
  9. Centos 执行top命令详细解读
  10. Java使用SFTP和FTP两种连接方式实现对服务器的上传下载 【我改】
  11. Eclipse远程调试Tomcat
  12. WIP 003 - Create page with a tablewalker
  13. iOS NSURLSession VS NSURLConnection
  14. 攻防对抗中常用的windows命令（渗透测试和应急响应）
  15. 第五章 二叉树（b）树的表示
  16. linux下pcf8563驱动时钟使用
  17. 接口测试之基础篇--http协议
  18. Hibernate入门篇<1>hibernate.cfg.xml学习小结
  19. AE常用代码（标注要素、AE中画带箭头的线、如何获得投影坐标、参考坐标、投影方式、FeatureCount注意事项）
  20. web相关基础知识2

热门文章

  1. ZOJ3554 A Miser Boss(dp)
  2. Simulate a seven-sided die using only five-sided
  3. uva 11324
  4. LoaderManager使用详解（一）---没有Loader之前的世界
  5. 2014多校第十场1004 || HDU 4974 A simple water problem
  6. poj 2068 Nim 博弈论
  7. Linux目录结构【转】
  8. Struts2 Convention插件的使用（2）return视图以及jsp的关系
  9. MongoDB (五) MongoDB 数据库操作
  10. iOS 限制UITextField输入字符