MySQL 表结构与键值

一、约束条件

null 允许为空，默认设置
not null 不允许为空
key 索引类型
default 设置默认值,缺省为null
            
+-----------------------------------------------------------------+
| Field |Type | NULL | KEY | Default | Extra             |
+-----------------------------------------------------------------+

二、表结构

语法：
alter table 表名 add(添加字段) modify(改字段类型) change(改字段名) drop(删除字段) rename(改表名);

1.添加字段
alter tanle 表名 add 字段名 类型（宽度） 约束条件 可加after(在字段后新增) 字段名 或first(在最前面新增字段)；
2.修改字段类型
alter table 表名 modify 字段名 类型（宽度）约束条件 可加after 字段名 或first;
3.修改字段名
alter table 表名 change 源字段名 新字段名 类型(宽度)"加新类型和约束条件时，也可修改字段类型"   约束条件;
4.删除字段
alter table 表名 drop 字段名"表中有很多记录时,此字段的所有值都会被删除";
5.修改表名
alter table 表名 rename 新表名(表对应的文件名,也会改变);

 
三、键值

介绍
1.索引：对记录集的多个字段进行排序(排序后表记录不发生变化,会生成一个排序文件,排序表(目录)直接告诉直接去第几行找(节省资源不用每行找)),类型Btree,B+tree,hash
索引优点：唯一性索引，每一行数据的唯一性
缺点：当对表增删改时，索引也要动态的维护,降低数据维护速度，且占物理空间.

2.键值类型

index 普通索引
unique 唯一索引
fulltext 全文索引
primary key 主键
foreign key 外键

 

• 2.1)Index普通索引

一个表可以多个index字段
字段值允许有重复，且可以赋null值
经常把做查询条件字段设置为index字段
index字段的key标志为MUL

mysql>create mysql.t3(
->id int primary key auto_increment,
->name char(10) not null,
->age int ,
->sex enum("boy","girl") default "boy",
->index(age),index(name) #可以多个INDEX
->);

mysql>desc mysql.t3;

+-------+--------------------+------+-----+---------+----------------+
| Field | Type               | Null | Key | Default | Extra          |
+-------+--------------------+------+-----+---------+----------------+
| id    | int(11)            | NO   | PRI | NULL    | auto_increment |
| name  | char(10)       | NO   | MUL | NULL    |                |
| age   | int(11)            | YES  | MUL | NULL    |                |
| sex   | enum('boy','girl') | YES  |     | boy     |                |
+-------+--------------------+------+-----+---------+----------------+

在已有表设置index字段：create index 索引名 on 表名(表段名);
删除指定表的索引字段：drop index 索引名 on 表名;
例：drop index name on mysql.t3;

查看表索引信息：show index from 表名\G;

• 2.2）primary key主键

.插入限制：一个表只能一个primary key字段，对应字段不能重复且不能赋NULL值
.如多个字段作为primary key称为复合主键，必须一起创建。
.主键字段KEY标志为PRI
.通常与auto_increment(自动增长) 连用
.常用来记录编号字段(唯一标识记录的字段)

建表时指定
mysql>create table mysql.t5(
->id int auto_increment,
->name char(10),
->age int not null,
->primary key(id)
->);

mysql>desc mysql.t5;

在已有的表设置primary key值字段
alter table 表名 add primary key(字段名);

移除priamry key 字段(如有自增属性必须去掉)
alter table 表名 drop primary key;

mysql>alter table mysql.t5 modify id int not null;(重赋属性,没加自增=删除自增)
mysql>alter table mysql.t5 drop primary key;

 

• 2.3)foreign key外键

.让当前表字段的值在另一个表中字段值的范围内选择
.条件：表引擎必须是InnoDB，字段类型要一致，被参照字段必须是索引类型的一种(priamry key)

foreign key(表A的字段名)  references 表B(字段名)
on update  cascade(同步更新) on delete cascade(同步删除);

 

删除外键字段

格式：alter table 表名 drop foreign key 约束名;

添加外键
mysql>alter table gz add foreign key(gz_id字段名) references yg表(yg_id字段名)   on update cascade on delete cascade;

mysql> create table yg(
->yg_id int primary auto_increment,
->name char(15),
->engine=innodb;
mysql>insert into yg(name) values("Bob");
多建几个方便测试

mysql>create table gz(
->gz_id int not null,
->gz float(7,2) default 20000,
->foreign key(gz_id) references yg(yg_id)
->on update cascade on delete cascade
->)engine=innodb;

mysql>update yg set yg_id=8 where yg_id=4;
mysql>insert into gz values(8,8000);
#gz_id要与yg_id一致且不能重复、空值

mysql>show create table gz\G
*************************** 1. row ***************************
       Table: gz
Create Table: CREATE TABLE `gz` (
  `gz_id` int(11) NOT NULL,
  `gz` float(7,2) DEFAULT '20000.00',
  PRIMARY KEY (`gz_id`),
  CONSTRAINT `gz_ibfk_1`(外键约束名称,删除时需要) FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> alter table gz drop foreign key gz_ibfk_1;

空 键值 默认值 额外值
primary key 要符合使用规则(不能重复，不能NULL)
有自动增长性的字段作主键，不允许删除
foreign key (被参考字段必须是key值中的一种primary key)
被参考的表、表的字段不允许被删除