DBA02 - 约束条件 修改表结构 MySQL键值

DBA DAY02

一 、字段约束条件

1.1  约束条件 （限制如何给字段赋值的）

约束条件:
NULL      是否允许给字段赋null值  
               YES    默认允许赋null值    
               NO      不允许赋null值
key         键值类型：普通索引   唯一索引  全文索引  主键  外 键
Default   默认值 作用：当不被字段赋值时，使用默认值给字段赋值
              不设置默认值是  系统定义的是null为default值   
Extra      额外设置  ，字段是否设置为自动增加,默认没有自动增长功能

二、修改表结构

2.1 修改表结构的命令

• 基本用法
– ALTER TABLE 表名 执行动作 ;
Add        添加字段
Modify    修改字段类型
Change  修改字段名
Drop       删除字段
Rename 修改表名

2.1.1 添加新字段      add   字段名    类型(宽度)  [ 约束条件]

• 基本用法
– ALTER TABLE 表名
– ADD 字段名 类型 ( 宽度 ) 约束条件 ;
可加 AFTER 字段名 ;
或者 FIRST;

alter  table   t1   add   name   char(15)  first ;
alter  table   t1   add   addrhome   varchar(50)  default  “beijing”;
alter  table   t1   add   sex   enum(“boy”,”girl”) not null  default “boy”  after   name;

2.1.2 删除已有字段  drop   字段名

• 基本用法
– ALTER TABLE 表名
– drop 字段名 ;

alter table 表名 drop 字段名，drop 字段名;

mysql> alter  table   studb.t1   drop sex  ,   drop name;

2.1.3 修改已有字段的类型宽度及约束条件

• 基本用法
 修改时不能与已经存储的数据矛盾的话不允许修改
– ALTER TABLE 表名
– modify 字段名 类型 ( 宽度 ) 约束条件 ;
可加 AFTER 字段名 ;
或者 FIRST;    

modify只能修改Type,Null,Default

 alter table 表名 modify   字段名    类型(宽度)  [ 约束条件]

mysql> alter table   t1  modify level smallint(6) not null;

mysql> alter table  t1  modify  addrhome char(10) default “beijing”;

mysql> alter table t1 modify age tinyint(4) first;

2.1.4 修改字段名change

• 基本用法
– ALTER TABLE 表名
– change 源字段名 新字段名 类型 ( 宽度 ) 约束条件;

change只能修改Field

 

alter  table  表名  change   源字段名   新字段名   类型(宽度)  [ 约束条件]

mysql> alter table  t1  change addrhome  home char(10) default “beijing”;

2.1.5 修改表名 rename

• 基本用法
– ALTER TABLE 表名
– Rename 新表名;

alter  table  源表名  rename   [to]   新表名；

mysql> alter table  t1   rename    stu;

三、mysql键值

3.1 MySQL索引概述

3.1.1 索引介绍

• 索引是什么?
– 索引是对记录集的多个字段进行排序的方法。
– 类似于书的目录
– 索引类型包括 :Btree 、 B+tree 、 hash索引优缺点

3.1.2 索引优缺点

• 索引优点
– 通过创建唯一性索引,可以保证数据库表中每一行数
据的唯一性
– 可以加快数据的检索速度
• 索引缺点
– 当对表中的数据进行增加、删除和修改的时候,索引
也要动态的维护,降低了数据的维护速度
– 索引需要占物理空间

3.2 MySQL 键值类型

3.2.1 键值类型

• INDEX :普通索引
• UNIQUE :唯一索引
• FULLTEXT :全文索引
• PRIMARY KEY :主键
• FOREIGN KEY :外键

3.1.1  INDEX普通索引  (作用：给字段排序的)

• 使用说明
– 一个表中可以有多个 INDEX 字段
– 字段的值允许有重复,切可以赋 NULL 值
– 经常把做查询条件的字段设置为 INDEX 字段
– INDEX 字段的 KEY 标志是 MUL

• 创建索引

1.在已有表创建
create   index   索引名  on   表名（字段名）；
create  index    aaa     on    studb.t2(age);
desc   studb.t2

2.建表时创建
create  table  表名（
字段列表，
index(字段名)，
index(字段名)，
）;

mysql> create table t2(name char(10),sex enum(“boy”,”girl”),weight int,index(name),index(weight));

• 查看索引

show create  table 表名;

show   index  from   表名\G;

• 删除索引

drop   index   索引名  on   表名；
mysql> drop index name on t2;

3.1.2主键 primary   key （作用:控制如何给字段赋值的   不能为空且不能重复）

• 注意事项
– 一个表中只能有一个 primary key 字段       （普通主键）
– 对应的字段值不允许有重复,且不允许赋 NULL 值
– 如果有多个字段都作为 PRIMARY KEY ,称为复合主
键,必须一起创建。                                        （复合主键）
– 主键字段的 KEY 标志是 PRI
– 通常与 AUTO_INCREMENT 连用             （主键+auto_increment）
– 经常把表中能够唯一标识记录的字段设置为主键字段 [ 记录编号字段 ]

一、普通主键

• 创建主键

1.建表的时候指定主键字段
create  table  表名（
字段列表，
primary  key(字段名)
）；
2.在已有的表中设置 PRIMARY KEY 字段
– ALTER TABLE 表名 ADD PRIMARY KEY( 字段名 );

mysql> alter table game1 add primary key(id);

• 删除主键

1.移除表中的 PRIMARY KEY 字段
– ALTER TABLE 表名 DROP PRIMARY KEY;    //移除主键前,如果有自增属性,必须先去掉

二、复合主键

创建复合主键的使用：多个字段一起做主键，插入记录时，只要做主键字段的值不同时重复，就可以插入记录。
不同时重复，就可以插入记录。

mysql> create  table studb.t23( 
    -> clientip   char(15),
    -> port smallint(2),
    -> status  enum(“allow”,”deny”),
    -> primary  key(clientip,port)
    -> );
mysql> desc   studb.t23

insert into  studb.t23  values(“1.1.1.1”,22,”allow”);  可以
insert into  studb.t23  values(“1.1.1.1”,22,”deny”)；报错
insert into  studb.t23  values(“2.1.1.1”,22,”deny”)；可以

三、主键+auto_increment

主键primary  key  通常和auto_increment连用。
要使用auto_increment的字段必须满足是主键且是数值类型

auto_increment可以让字段的值自动增长  i++

mysql> create table t1(id int(2) primary key auto_increment,name char(15) not null,age tinyint(2) unsigned default 19,pay float(7,2) default 26800);

mysql> insert into t1 (name,age,pay) values (“lijun”,22,18000);  //成功
mysql> insert into t1 (name,age,pay) values (“mayun”,22,18000);  //成功
mysql> insert into t1 (id,name,age,pay) values (2,”mayun”,22,18000);  //失败
mysql> insert into t1 (id,name,age,pay) values (5,”mayun”,22,18000);  //成功
mysql> insert into t1 (id,name,age,pay) values (null,”majiang”,22,18000); //成功

四、删除主键  

mysql> alter table 表  modify id int(2) not null;  //先去除auto_increment,才可以删除主键。因为auto_increment依赖于primary key

mysql>  alter  table   表   drop    primary  key；

mysql> alter table game1 drop primary key;

3.1.3 外 键(作用 限制如何给字段赋值的)

• 什么是外键?
– 让当前表字段的值在另一个表中字段值的范围内选择
• 使用外键的条件
– 表的存储引擎必须是 innodb
– 字段类型要一致
– 被参照字段必须要是索引类型的一种 (primary key)

• 创建外键

1.建表的时候指定外键

create   table   表（
字段名列表，
foreign  key(字段名)   references  表名（字段名） 
on  update cascade    on  delete  cascade
）engine=innodb;

2.在已有表创建外键  
mysql> alter  table  gztab  add  foreign  key(gz_id)   references  yginfo
(yg_id)  on  update cascade    on  delete  cascade;

案例1：
1.创建员工表
mysql> create table yginfo(yg_id int(2) primary key auto_increment,name char(15))engine innodb;

2.往员工信息表中插入数据

mysql> insert into yginfo (name) values(“bob”);

mysql> insert into yginfo (name) values(“jack”);

mysql> insert into yginfo (name) values(“lucy”);

select  *  from  yginfo;

3.创建工资表 
mysql> create table gztab(gz_id int(2),pay float(7,2),foreign key(gz_id) references  yginfo(yg_id) on delete cascade on update cascade)engine=innodb;

desc gztab;

4.往工资表里面插入数据

mysql> insert into gztab values(1,50000);   //成功

mysql> insert into gztab values(2,40000);   //成功

mysql> insert into gztab values(3,60000);   //成功

mysql> insert into gztab values(4,70000);   //存储失败，超出了参考表的yg_id范围

select  * from gztab;

mysql> update yginfo set yg_id=7 where yg_id=2;
select  * from yginfo;
select  * from gztab;   //工资表会同步更新已经修改的参考值

• 查看外键

mysql> show create table yginfo;  （其中 gztab_ibfk_1就是外键名称）

删除yginfo的数据，gztab里面的数据也会随之删除

mysql> delete from yginfo;

当再次插入数据时，被primary key和auto_increment修饰的字段。这个字段是多少开始，是由AUTO_INCREMENT这个变量决定的，并不是从1开始的

mysql> insert into yginfo (name) values(“bob”);

mysql> insert into yginfo (name) values(“lucy”);

mysql> insert into yginfo (name) values(“lijun”);

给gztab赋值时，因为gztab表的字段gz_id仅仅是forigen.所以它是可以重复也可以为空的

mysql> insert into gztab values(4,5000);

mysql> insert into gztab values(5,5000);

mysql> insert into gztab values(6,5000);

mysql> insert into gztab values(4,5000);

mysql> insert into gztab values(null,5000);   //null没有超出  yginfo表中id_info的4，5，6的范围。

为了避免重复发工资和给null发工资，我们可以给gztab表中的gz_id再设置为主键。让gz_id既要满足外键的约束也要满足自身的主键才能存入。

mysql> delete from gztab;

为gztab表的gz_id字段设置主键

mysql> alter table gztab add primary key(gz_id);

mysql> insert into gztab values(4,5000);

mysql> insert into gztab values(4,5000);    //主键gz_id不允许重复，报错

mysql> insert into gztab values(null,5000); //主键gz_id不能为空，报错

mysql> insert into gztab values(5,5000);  //成功

mysql> insert into gztab values(6,5000);  //成功

mysql> insert into gztab values(7,5000);  //超出了参考索引(主键)的约束范围，报错

mysql> select * from gztab;

• 删除外键   

mysql> show create table gztab;   //查看外键名称

alter  table  表名  drop  foreign key  外键名;
alter  table  bjb  drop  foreign key  gztab_ibfk_1; 

删除外键之后就可以添加不是参考范围内的值了。