1 说明:此文件包含了blog数据库中建立所有的表的Mysql语句.
2
3 在sql语句中注意“约束的概念":
4 1.实体完整性约束(主键--唯一且非空) primary key()
5 违约处理:No action(拒绝执行)
6
7 2.参照完整性约束(外键约束)foregin key() references tableName(filedName) [on delete|update casecade | no action]
8 违约处理:级联更新或拒绝执行
9
10 3.用户自定义完整性约束(not null,unique,check短语)
11 违约处理:拒绝执行
12
13 //添加列语法
14 //【alter table blog_article add columName type constraint】
15 //添加约束例子
16 //【alter table blog_article add CONSTRAINT foreign key(category_Name) references blog_category(category_Name) on delete cascade on update cascade】
17
18
19 问题:如何让相册，相片，文章公用一个评论表?
20
21 create database blog;
22
23 create table blog_user
24 (
25 user_Name char(15) not null check(user_Name !=''),
26 user_Password char(15) not null,
27 user_emial varchar(20) not null unique,
28 primary key(user_Name)
29
30 )engine=innodb default charset=utf8 auto_increment=1;
31
32
33
34
35 create table blog_category
36 (
37 category_Name char(18) not null check(category_Name!=''),
38 category_Date datetime not null,
39 primary key(category_Name)
40 )engine=innod default charset=utf8 auto_increment=1;
41
42
43
44
45 create table blog_article
46 (
47 article_Id int unsigned not null auto_increment,
48 article_title varchar(20) not null unique,
49 article_content longtext not null,
50 article_date datetime not null,
51 article_readTime int unsigned not null default 0,
52 user_Name char(15) not null,
53 category_Name char(18) not null,
54 primary key(article_Id),
55 foreign key(user_Name) references blog_user(user_Name) on delete cascade on update cascade,
56 foreign key(category_Name) references blog_category(category_Name) on delete cascade on update cascade
57 )engine=innodb default charset=utf8 auto_increment=1;
58
59
60
61
62
63
64 CREATE TABLE blog_comment (
65 comment_Id int(10) unsigned NOT NULL AUTO_INCREMENT,
66 comment_Content varchar(90) NOT NULL,
67 comment_Date datetime NOT NULL,
68 article_Id int(10) unsigned NOT NULL,
69 user_Name char(15) NOT NULL,
70 PRIMARY KEY (comment_Id),
71 foreign key(article_Id) references blog_article(article_Id) on delete cascade on update cascade,
72 foreign key(user_Name) references blog_user(user_Name) on delete cascade on update cascade
73 )engine=innodb default charset=utf8 auto_increment=1;
74
75
76
77 create table blog_photoAlbum
78 (
79 photoAlbum_Name char(20) not null check(photoAlbum_Name!=''),
80 photoAlbum_Date datetime not null,
81 primary key(photoAlbum_Name)
82 )engine=innodb default charset=utf8;
83
84
85
86
87 create table blog_photograph
88 (
89 photograph_Name varchar(20) not null check(photograph_Name!=''),
90 photograph_Date datetime not null,
91 photoAlbum_Name char(20) not null,
92 photoURL varchar(90) not null,
93 foreign key(photoAlbum_Name) references blog_photoAlbum(photoAlbum_Name) on delete cascade on update cascade
94 )engine=innodb default charset=utf8;
