MySQL5.5.21学习教程之二(二)
ault | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(11) | YES | | NULL | |
| dname | varchar(20) | YES | | NULL | |
| loc | varchar(40) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
#alter table table_name change 旧属性名 新属性名 旧数据类型
#alter table table_name change 旧属性名 新属性名 新数据类型
alter table t_dept change loc location VARCHAR(40);
desc t_dept;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| deptno | int(11) | YES | | NULL | |
| dname | varchar(20) | YES | | NULL | |
| location | varchar(40) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
alter table t_dept modify location VARCHAR(40) first;
desc t_dept;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| location | varchar(40) | YES | | NULL | |
| deptno | int(11) | YES | | NULL | |
| dname | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
MySQL软件支持的完整性约束
NOT NULL--设置约束字段不能为空
DEFAULT--设置字段的默认值
UNIQUE KEY--约束字段的值唯一
PRIMARY KEY--约束字段为表的主键，可以作为该表记录的唯一约束
AUTO_INCREMENT--约束字段的值为自动增加
FOREIGN KEY--约束字段为表的外键
alter table t_dept modify deptno INTEGER NOT NULL;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| location | varchar(40) | YES | | NULL | |
| deptno | int(11) | NO | | NULL | |
| dname | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
alter table t_dept modify location VARCHAR(40) default 'N
WPU';
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| location | varchar(40) | YES | | NWPU | |
| deptno | int(11) | NO | | NULL | |
| dname | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
alter table t_dept modify dname VARCHAR(20) unique;
desc t_dept;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| location | varchar(40) | YES | | NWPU | |
| deptno | int(11) | NO | | NULL | |
| dname | varchar(20) | YES | UNI | NULL | |
+----------+-------------+------+-----+---------+-------+
drop table t_dept;
show tables;
如果想给字段dname上的UK约束设置一个名字，可以执行SQL语句constraint
下面是创建表t_dept的语句:
create table t_dept(
deptno INTEGER,
dname VARCHAR(20),
loc VARCHAR(40),
constraint uk_dname unique(dname)
);
在具体的设置主键约束时，必须满足主键字段的值是唯一的、非空的。
由于主键可以是单一字段，也可以是多个字段，因此分为单字段主键和多字段主键
create table t_dept(
deptno INTEGER primary key,
dname VARCHAR(20),
loc VARCHAR(40),
constraint uk_dname unique(dname)
);
设置多字段主键
create table t_dept(
deptno INTEGER,
dname VARCHAR(20),
loc VARCHAR(40),
constraint uk_dname unique(dname),
constraint pk_dname_depno primary key(deptno,dname)
);
show tables;
desc t_dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Def