nanakon

时间：2023-02-25 10:28:29

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

)

nanakon

修改字段名

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

)

nanakon

创建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)

);

nanakon

创建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)

);

nanakon

修改

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)

);

nanakon

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)

);

nanakon

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)

);

nanakon

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;

nanakon

创建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)

);

nanakon

创建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)

);

nanakon

创建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)

);

nanakon

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

nanakon的更多相关文章

  1. git全局忽略文件

    mac系统如何显示和隐藏文件 创建 ~/.gitignore_global .DS_Store __pycache__/ 配置选项 git config --global core.excludesf ...

随机推荐

  1. JavaScript学习笔记-函数实例

    函数实例 var p = { a:15, b:'5', f1:function(){ var self = this; console.log(self.a+self.b); f2(); functi ...

  2. django base.html

    <!DOCTYPE html> <html> <head> <title>{% block title %}默认标题{% endblock %} - 自 ...

  3. Windows性能查看器：系统的性能信息&lpar;I&sol;O，IIS最大连接数&comma;Sql&rpar; ，以及解决 asp&period;net IIS 一二百多用户并发

    在测试过程中,我们经常需要知道“系统的资源利用情况”来监测我们的测试执行情况,来查看测试环境是否有效,测试结果是否可信,或者是在无人值守时保存结果,等我们值班时再来分析. 1.在Windows环境下, ...

  4. kobox ： key&lowbar;waitqueue&period;c -v1 如何内核线程，如何使用等待队列

    平台:TQ2440 按键驱动 (1)在init中创建一个内核线程作为等待队列的处理函数,该内核线程是一个while(1)死循环,一直检測等待队列的触发条件 DECLARE_WAIT_QUEUE_HEA ...

  5. 动态规划——Frog Jump

    题目大意就是,给定一个数组,数组中数字从小到大排列,第一个元素一定是0,青蛙的初始位置就在0,后面依次从小到大排列,表示第几个石子,青蛙只有跳到最后一个石子上才算成功过河,而且青蛙第一次从0位置只能跳 ...

  6. Appium java环境搭建&lpar;Windows版&rpar;

    注意:如果初次学习appium的话,则需要你做好准备因为安装过程并不简单 1.安装appium Appium 官方网站:http://appium.io/ 安装Appium之前需要先安装node.js ...

  7. Luogu P1776 宝物筛选&lowbar;NOI导刊2010提高（02）（多重背包模版）

    传送门 多重背包板子题, 多重背包就是每种东西有好几个,可以把它拆分成一个一个的01背包 优化:二进制拆分(拆成1+2+4+8+16+...) 比如18=1+2+4+8+3,可以证明18以内的任何数都 ...

  8. MySql&period;Data&period;dll的版本

    在.Net下访问Mysql,先是用6.4.4,老有问题,也不知道哪个版本可以用,查询官网 https://dev.mysql.com/doc/connector-net/en/connector-ne ...

  9. CentOS 7系统根目录分区扩容

    说明:系统版本为 Linux version 3.10.0-327.el7.x86_64 1. 查看现有磁盘信息,可以看出根分区有45G [root@DEV-CMDB-DB02 ~]# df -h F ...

  10. 谷歌技术&quot&semi;三宝&quot&semi;之MapReduce&lpar;转）

    原文:http://blog.csdn.net/opennaive/article/details/7514146   目录 MapReduce是干啥的 例子统计词频 map函数和reduce函数 M ...