MySQL的表结构和键值

MySQL表结构

约束条件：

作用是限制如何给字段赋值

约束条件就是查看表结构后面四列就是他的限制

• null：是否允许赋空值

  • 是否允许为空null ，默认允许 null

  • 不允许为空： not null

• key：键值类型

  • 不定义时没有键值

• default：设置默认值，缺省为NULL

  • 当我们插入新数据时，如果不赋值，就会使用默认值
  • default 值（要与字段类型匹配）

• extra：额外设置

  • 默认都没有额外设置

设置约束条件

• null 允许为空
• not null 不允许为空
• key 键值类型
• default 设置默认值，缺省为null
• extra 额外设置

例：

mysql> create table name1(
    -> name char(10) not null default "",
    -> age tinyint unsigned default 19,
    -> sex enum("w","m") not null default "w"
    -> );
#查看表结构
desc db1.name1;
#添加一个名字为bob，其他使用默认
insert into db1.name1 (name)values("bob");
#正常插入
insert into db1.name1 values("tom",23,"m");
select * from db1.name1;
#赋值变量为空
insert into db1.name1 values(null , null , null );
#给字符加入双引号，默认为普通字符，sex不允许为空
insert into db1.name1 values("null" , null , null );
insert into db1.name1 values("null" , null , "w" );

修改表结构

当表创建的不合理，我们就可以修改表结构

使用命令：alter table 库名.表名 执行动作 ;

执行动作：

• 添加新字段 add

  • add 字段名 类型 [约束条件];

  • add 字段名 类型 [约束条件] after 字段名; 添加到某个字段后面

  • add 字段名 类型 [约束条件] first； 添加到开头

  • 约束条件不写时，使用默认约束

    #下面操作三种添加方式
    #直接添加，约束条件为MySQL默认约束
    alter table db1.name1 add email varchar(50);
    desc name1;
    select * from name1;
    #设置约束条件，并把它放在age之后
    alter table name1 add likes set("eat","game") not null default "eat" after age;
    desc name1;
    select * from name1;
    #设置约束条件，把他放在第一列
    alter table name1 add class char(7) default "2021" first;
    desc name1;
    select * from name1;
    

    

  

• 修改字段类型：modify

  • 基本用法：修改的字段类型不能与已存储的数据冲突

  • alter table 库名.表名

    modify 字段名 类型(宽度) 约束条件

    [ after 字段名 | first ] ;

    #先查看表结构
    desc name1;
    #把age 调整到class之后，其余不要发生变化，否则就会出现初始化问题
    alter table db1.name1 modify age tinyint unsigned default 19 after class;
    desc name1;
    #把name的类型调整为varchar，其余不变
    alter table db1.name1 modify name varchar(15) not null default "" ;
    desc name1;
    

    

• 修改字段名：change

  • 基本用法：也可以用来修改字段类型

  • alter table 库名.表名

    change 源字段名 新字段名 类型(宽度)

    约束条件 ;

    desc name1;
    #把email修改为mail，并且把类型和宽度修改为varchar(20)
    alter table db1.name1 change email mail varchar(20);
    desc name1;
    

    

• 删除字段: drop

  • alter table 库名.表名

    drop 字段名 ;

    desc name1;
    alter table db1.name1 drop mail;
    select * from db1.name1;
    

    

• 修改表名: rename

  • alter table 库名.表名

    rename 新表名 ;

     select * from db1.name1;
     alter table db1.name1 rename name;
     select * from name;
    

    

MySQL键值

key键值类型：

根据数据存储要求，选择键值

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

索引

索引是什么？

• 类似于书的目录
• 对表中字段值进行排序
• 索引类型包括：Btree（二叉树）、B+tree、hash

索引优缺点

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

index普通索引

使用规则

• 一个表中可以有多个index字段
• 字段的值允许重复，且可以赋NULL值
• 通常把作为查询条件的字段设置为index字段
• index字段标志为MUL

创建索引

1. 建表时创建索引

   • create table 库.表(

     字段列表 ,

     index(字段名),

     index(字段名)

     ) ;

2. 在已有表里创建索引

   • create index 索引名 on 表名(字段名);

3. 查看索引

   • 基本查看 desc 表名; key列
   • 详细查询 show index from 表名 ; \G 竖着查看，默认是一行

4. 删除索引

   • drop index 索引名 on 库名.表名 ;

mysql> create table t0(
    -> name char(5),
    -> age int,
    -> class char(7),
    -> email varchar(10),
    -> index(name),
    -> index(age)
    -> );
desc t0;
show index  from db1.t0 \G;

在key列中可以看到我们的索引创建成功

drop index name on db1.t0;
#查看是否删除成功
show index from db1.t0 \G;

成功删除index name

索引将排序信息存放在数据库的表文件中。开始查询时，调用索引的排序信息，进行查询，表中的数据并没有发生改变

primary key主键

使用规则

• 字段值不允许重复，且不允许赋NULL值
• 一个表中只能有一个primary key 字段
• 多个字段都作为主键，称为复合主键，必须一起创建
• 主键字段的标志为PRI
• 主键通常与auto_increment（自增长）连用
• 通常把表中唯一标识记录的字段设置为主键

​ 【记录编号字段】

创建主键

• 建表时创建主键

  • primary key(字段名)

    mysql> create table t9(
        -> name char(5) primary key,
        -> age int
        -> );
        desc t9;
        insert into t9 values("bob",19);
        #因为主键不允许重复，所以添加失败
         insert into t9 values("bob",20);
         select * from t9;
    

    

• 在已有的表中创建主键

  • alter table 库.表 add primary key(字段名);

  #创建主键的字段不能为空，字段其中有空值，创建失败
  select name from db1.name where name is null;
  alter table db1.name add primary key(name); 
  

  

复合主键

表中多列一起做主键，插入记录时多列的值不允许同时重复

只要所有主键不同时重复，就能顺利写入数据

• 创建复合主键

  • 在已有的表中添加复合主键

    alter table 表名 add primary key(字段名列表);

  • create table 库名.表名(

    字段列表 ,

    primary key(字段名列表)

    )；

     create table db2.t0(
         class char(5), 
         name char(5), 
         money enum("no","yes") ,
         primary key(class,name) 
       );
     desc db2.t0;
     #测试
     insert into  db2.t0 values ("0601","bob","yes");
     #这条因为两个主键冲突所以不能写入
     insert into  db2.t0 values ("0601","bob","no");
     #单条主键冲突也能顺利写入数据
     insert into  db2.t0 values ("0602","bob","no");
    

    

• 与auto_increment连用

  自增长必须为主键

   create table db2.t1(
      id int auto_increment, 
       name char(5), 
      age int,
       primary key(id,name) 
     );
     desc t1;
     insert into db2.t1 (name,age) values("bob",19);
     insert into db2.t1 (name,age) values("tom",29);
     insert into db2.t1 (name,age) values("lcuy",22);
     select * from t1;
     #id也可以自己赋值，自己赋值就不会使用自增长，
     insert into db2.t1  values(9,"bai",22);
     #下一次自增长，会从id最大值来进行自增长
     insert into db2.t1 (name,age) values("jary",22);
  

  

 delete from db2.t1; #清空表数据，
   insert into db2.t1 (name,age) values("le",22);
   #自增长会从10开始累加，会记录他上次累加到哪
   select * from t1;
   #当我们给id赋空值时，自增加也会触发
  insert into db2.t1  values(null,"lisi",22);  
  select * from t1;

删除主键

alter table db2.t0 drop primary key;

foreign key外键

外键功能：插入记录时，字符值在另一个表字段值范围内选择

使用规则：

• 表存储引擎必须是innodb
• 字段类型要一致
• 被参照字段必须要是索引类型的一种（primary key）

创建外键：

例：
create table 库.表 (
字段名列表，
foreign key（字段名）references 表名(字段名)   指定外键
on update cascade  同步更新
on delete cascade  同步删除
)engine=innodb;  指定存储引擎

#先建立一张表，假如这是员工表
mysql> create table t2(
    -> id int primary key auto_increment,
    -> name char(10),
    -> sex enum("boy","girl")
    -> )engine=innodb;
 insert into t2 (name,sex) values ("bob","boy");
 insert into t2 (name,sex) values ("lucy","girl");
 select * from t2;
 #在建立一张工资表，工资表的外键来源是员工表
 create table gzb(
  gzid int,
 pay float(7,2),
 foreign key(gzid) references t2(id)
 on update cascade
 on delete cascade
 )engine=innodb;
 desc gzb;
 #从key列可以看出，外键的key值为MUL
 show create table gzb;  #查看表的详细信息，可以看出我们刚才建立的外键

验证外键功能

desc gzb;
insert into gzb values(1,100);
insert into gzb values(2,100);
#失败添加，因为外键约束，t2表中没有编号为3的人
insert into gzb values(3,100);

验证外键的同步更新功能

select * from t2;
select * from gzb;
update t2 set id=8 where id=2;   #更新id
select * from t2;
select * from gzb;   #gzb表也会更新

验证外键的同步删除功能

select * from t2;
select * from gzb;
delete from t2  where id=8;   #删除id
select * from t2;
select * from gzb;    #gzb表也会更新

优化：

desc gzb;  
#因为id我们并没有给他设置特殊约束，所以这里的id可以为空，可以重复，当然这在现实中不合理，所以，我们给id加一个主键，就能解决上述问题
alter table gzb add primary key(gzid);
insert into gzb values(null,100);
insert into gzb values(1,1000);

删除外键：

alter table gzb drop foreign key 外键名称;
查看外键名
show create table gzb;