MySQL建表语句大全
摘要:操作包含: 创建表、创建约束、删除约束、修改表字段和删除表
1. 创建表(含有约束)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
/**创建组表**/
create
table
t_group
(
id
int
(
11
)
auto_increment
primary
key
,
name
varchar
(
50
)
,
value
varchar
(
255
)
)
engine
=
InnoDB
default
charset
=
utf8
;
/**创建用户表**/
create
table
t_user
(
id
int
(
11
)
auto_increment
primary
key
,
name
varchar
(
50
)
unique
,
value
varchar
(
255
)
default
'0'
,
email
varchar
(
100
)
not
null
,
sex
varchar
(
1
)
check
(
user_sex
=
1
or
user_sex
=
2
)
,
group_id
int
(
11
)
,
create_time
timestamp
,
constraint
foreign
key
(
group_id
)
references
t_group
(
id
)
on
delete
cascade
)
engine
=
InnoDB
default
charset
=
utf8
;
/**查看表信息**/
desc
t_group
;
show
create
table
t_group
;
desc
t_user
;
show
create
table
t_user
;
|
自动增长: auto_increment
创建主键: primary key
唯一约束: unique
非空约束: not null
检查约束: check(user_sex=1 or user_sex=2)
创建外键: constraint foreign key(group_id) references t_group(id) on delete cascade
2. 创建表(不含约束)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
/**创建组表**/
create
table
t_group
(
id
int
(
11
)
,
name
varchar
(
50
)
,
value
varchar
(
255
)
)
engine
=
InnoDB
default
charset
=
utf8
;
/**创建用户表**/
create
table
t_user
(
id
int
(
11
)
,
name
varchar
(
50
)
,
value
varchar
(
255
)
default
'0'
,
email
varchar
(
100
)
,
sex
varchar
(
1
)
,
group_id
int
(
11
)
,
create_time
timestamp
)
engine
=
InnoDB
default
charset
=
utf8
;
/**查看表信息**/
desc
t_group
;
show
create
table
t_group
;
desc
t_user
;
show
create
table
t_user
;
|
3. 创建约束
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
/**创建主键约束**/
alter
table
t_group
add
constraint
group_id_pk
primary
key
(
id
)
;
alter
table
t_user
add
constraint
user_id_pk
primary
key
(
id
)
;
/**自动增长**/
alter
table
t_group
modify
id
int
(
11
)
auto_increment
;
alter
table
t_user
modify
id
int
(
11
)
auto_increment
;
/**创建外键约束**/
alter
table
t_user
add
constraint
user_group_id_fk
foreign
key
(
group_id
)
references
t_group
(
id
)
;
/**创建唯一约束**/
alter
table
t_group
add
constraint
group_name_uk
unique
(
name
)
;
alter
table
t_user
add
constraint
user_name_uk
unique
(
name
)
;
/**创建非空约束**/
alter
table
t_user
add
constraint
user_email_nk
check
(
email
is
not
null
)
;
/**创建检查约束**/
alter
table
t_user
add
constraint
user_gender_ck
check
(
user_sex
=
1
or
user_sex
=
2
)
;
|
4. 删除约束
1
2
3
4
5
|
/**删除唯一约束**/
alter
table
t_user
drop
unique
key
user_name_uk
;
/**删除外键约束**/
alter
table
t_user
drop
foreign
key
user_group_id_fk
;
|
5. 修改表字段
1
2
3
4
5
6
7
8
9
10
11
|
/**增加字段**/
alter
table
t_group
add
description
varchar
(
255
)
;
alter
table
t_user
add
description
varchar
(
255
)
;
/**修改字段**/
alter
table
t_group
modify
description
varchar
(
20
)
;
alter
table
t_user
modify
description
varchar
(
20
)
;
/**删除字段**/
alter
table
t_group
drop
description
;
alter
table
t_user
drop
description
;
|
6.删除表
1
2
|
drop
table
t_user
;
drop
table
t_group
;
|