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