MySQL表结构和键值

表结构:约束条件,修改表结构.

目录

〔1〕表结构

〔2〕修改表结构

〔3〕MySQL键值(key)

---

〔1〕表结构

• 查看表结构
  • 命令
    • desc db2.myself 查看表结构
  • 可以看到Field|Type|Null|Key|Default|Extra 即字段名|类型|空|键值|默认值|额外设置
• 约束条件
  • Null|Key|Default|Extra 为字段的约束条件
  • 作用:创建表时,对字段的约束
  • 格式:
    • create table 库名.表名(字段1 类型(宽度) [约束条件], 字段2 类型(宽度) [约束条件],.....);
  • 例如
    • create table db2.t7(name char(10) not null, age tinyint unsigned default 25, sex enum("man","woman") default "man");
• 设置字段约束条件
  • 格式:字段名 类型(宽度) 约束条件
  • 允许为空约束(默认)
    • 关键字: null
    • 例如:name char(10),
  • 不为空约束
    • 关键字: not null
    • 例如:name char(10) not null,
  • 键值约束(key)
    • 普通索引
    • 唯一索引
    • 主键
    • 外键
    • 全文索引
  • 默认值约束(default)
    • 例如:age tinyint default 25
  • 额外设置(extra)
    • 值可为auto_increment 自增

 

〔2〕修改表结构

• 语法结构
  • alter table 库名.表名 执行动作
  • 执行动作
    • 添加字段:add [after/first](可选)
    • 修改字段类型:modify [after/first](可选)
    • 修改字段名:change
    • 删除字段:drop
    • 修改表名:rename
• 添加字段
  • 默认添加在字段末尾
  • 命令格式
    • alter table 库名.表名 add 字段名 类型(宽度) 约束条件 [after 字段名 | first] //指定添加在什么位置,使用after,添加在某个字段名后面,first放在第一列
    • 例如:alter table db2.myself add id int not null first; //添加字段id,类型int,不为null,放在第一列
• 修改字段类型
  • 修改的字段类型不能与已存储的数据冲突
  • 命令格式
    • alter table 库名.表名 modify 字段名 类型(宽度) 约束条件 [after 字段名 | first] //使用after,添加在某个字段名后面,first放在第一列
    • 例如:修改age字段的类型为char(20),并调整位置在name字段后
      • alter table db2.myself modify age char(20) not null after name ;
• 修改字段名
  • 修改新的字段名的时候也可以修改字段的类型
  • 命令格式
    • alter table 库名.表名 change 原字段名 新字段名 类型(宽度) 约束条件;
    • 例如:alter table db2.myself change id num int;
• 删除字段
  • 表中有多条记录时,该字段所在的列的值都会被删除
  • 命令格式
    • alter table 库名.表名 drop 字段名;
• 修改表名
  • 命令格式
    • alter table 表名 rename 新表名;

 

〔3〕MySQL键值(key)

• 键值类型
  • 普通索引: index
  • 唯一索引: unique
  • 全文索引: fulltext
  • 主键: primary key
  • 外键: foreign key
• 索引
  • 定义:类似于书的目录,对表中的字段值进行排序
  • 类型:Btree,B+tree,hash
  • 优点
    • 创建唯一索引,保证数据表中的每一行数据的唯一性,加快数据查询速度
  • 缺点
    • 对表中数据增删改的时候,索引需要动态调整,降低数据的维护速度
    • 索引需要占物理空间
• 查看索引
  • 格式: show index from 表名\G;
• 删除索引
  • 格式: drop index 索引名 on 表名;
• index 普通索引
  • 规则
    • 一个表中可以有多个index字段
    • 字段的值允许重复,且可以赋值NULL
    • 通常把作为查询条件的字段设置为index字段
    • 表中index字段的标志是MUL
  • 建表时创建索引字段
    • 格式: index(字段名),index(字段名),......
    • 例如:
      • create table tea4(id int, name char(1), class char(11), index(id), index(name));
  • 在已有的表里创建索引
    • 格式: create index 索引名 on 表名(字段名);
    • 例如: create index xxx on t7(name);
• Primary Key 主键
  • 规则
    • 字段值不允许重复,不允许赋值NULL,空格
    • 一个表中只有个主键primary key
    • 多个字段都作为一个主键,称为复合主键,必须一起创建
    • 主键字段的标志是PRI
    • 主键通常与自增auto_increment连用
    • 通常把表中唯一标识记录的字段设置为主键
  • 作用:保证数据
  • 建表时创建主键
    • 格式: primary key(字段名)
    • 例如: create table t8(id int primary key, name char(3));
  • 在已有表中添加主键
    • 格式: alter table 表名 add primary key(字段名);
  • 删除主键
    • 格式: alter table 表名 drop primary key;
    • 删除主键时,字段如果有自增属性,必须先去掉,使用modify修改字段类型
  • 创建复合键
    • 已有表中添加复合键
      • 格式: alter table 表名 add primary key(字段名列表)
    • 建表时添加复合键
      • create table t5(id int, name char(10),status enum('yes','no'), primary key(id,name));
  • 与auto_increment
    • 主键字段值自动加1,根据上一条主键字段值加1,删除最后一条记录,新插入记录会在删除前最后一条记录的值上加1
      • create table t6(id int primary key auto_increment, name char(10));
    • 验证
      • insert into t6(name) values("luo"); //可以查询id第一条为
• Foreign 外键
  • 功能:插入记录时,字段值在另一个表字段值范围内选择.
  • 规则
    • 表存储引擎必须是innodb
    • 字段类型要一致
    • 被参照字段必须要是索引类型的一种(通常是primary key)
  • 创建外键
    • 格式
      • create table 表名(字段名列表,

                             foreign key(字段名) references 表名(字段名) //指定外键

                             on update cascade //同步更新

                             on delete cascade //同步删除

                             )engine=innodb; //指定存储引擎

• • 使用
    • 创建一个引用表,员工信息表yginfo,主键字段是yg_id,存储引擎是innodb
      • create table yginfo(yg_id int primary key auto_increment, name char(20))engine=innodb;
    • 创建一个工资表,存储引擎是innodb,创建gz_id的外键,
      • create table gzb(gz_id int, pay float(7,2), foreign key(gz_id) references yginfo(yg_id)
      • on update cascade on delete cascade)engine=innodb;
  • 删除外键
    • 查看外键名 show create table 表名 \G;
    • 格式: alter table 表名 drop foreign key 外键名;