MySQL学习笔记二

外键约束：foreign key

编辑数据表的默认存储引擎，MySQL配置文件，default-storage-engine=INNODB

案例：

 create table provices(
 id smallint unsigned primary key auto_increment,
 pname varchar(20) not null
 );

 create table users(
 id smallint unsigned primary key auto_increment,
 username varchar(10) not null,
 pid smallint unsigned,
 foreign key (pid) references provices(id)
 );

其中provices的id为参考列

查看索引：show indexes from provices\G;

CREATE TABLE `users1` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(10) NOT NULL,
  `pid` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pid` (`pid`),
  CONSTRAINT `users1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provices` (`id`) ON DELETE CASCADE
)

修改数据表

a.添加单列

alter table tbl_name add [column] col_name column_definition [first | after col_name]col

b.添加多列

alter table tbl_name add [column] (col_name column_definition,…)

c.删除列

alter table tbl_name drop [column] col_name

d.添加主键约束

alter table tbl_name add [constraint [symbol]] primary key [index_type] (index_col_name,…)

案例：

create table users2(
username varchar(10) not null,
pid smallint unsigned
);

添加一列： alter table users2 add id smallint unsigned;

添加主键约束： alter table users2 add constraint PK_users2_id primary key (id);

e.添加唯一约束

alter table tbl_name add [constraint [symbol]] unique [index|key] [index_name] [index_type] (index_col_name,…)

案例：

alter table users2 add unique (username);

f.添加外键约束

alter table tbl_name add [constraint [symbol]] foreign key [index_name] (index_col_name,…) reference_definition

案例：

alter table users2 add foreign key (pid) references provices (id);

g.添加/删除默认约束

alter table tbl_name alter [COLUMN] col_name {set default literal | drop default}

案例：

alter table users2 add age tinyint unsigned not null;

 alter table users2 alter age set default 15;

h.删除约束

删除主键约束：alter table tbl_name drop primary key

删除唯一约束：alter table tbl_name drop {index|key} index_name

删除外键约束：alter table tbl_name drop froeign key fk_symbol

i.修改列定义

alter table tbl_name modify [column] col_name column_definition [first|after col_name]

案例：

alter table users2 modify id smallint unsigned not null first;

j.修改列名称

alter table tbl_name change [column] old_col_name new_col_name column_definition [first|after col_name]

案例：

ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED

k.数据表更名

方法1

alter table tbl_name rename [to|as] new_tbl_name

方法2

rename table  tbl_name to new_tbl_name [,tbl_name2 to new_tbl_name2]…

案例：

alter table users2 rename users3;