2.mysql 表操作

在 mysql shell 中操作后续命令. 进入方式

docker exec -it mysql mysql -u root

1. 创建表

   • 无主键

     # 切换数据库
     use test_db;
     create table tb_empl(
         id int(11) not null,
         name varchar(25),
         deptId int(11),
         salary float
     );
     # 查看已有的表
     show tables;
     # +-------------------+
     # | Tables_in_test_db |
     # +-------------------+
     # | tb_empl           |
     # +-------------------+
     

   • 唯一主键

     create table emp2(
             id int(11) primary key,
             name varchar(25),
             deptId int(11),
             salary float
     );
     # 或者
     create table emp3(
             id int(11) ,
             name varchar(25),
             deptId int(11),
             salary float,
             primary key(id)
     );
     

   • 多字段主键. 假设没有主键 id, 通过 name 和 deptId 联合起来作为主键

     create table emp4(
        name varchar(25),
        deptId int(11),
        salary float,
        primary key(name, deptId)
     );
     

   • 约束

     # 非空. not null
     # 唯一. unique. 该列唯一, 允许为空
     # 默认. default 为字段提供默认值
     # 自增. auto_increment 只能有一个字段使用. 且该字段必须为主键的一部分. 类型需为 *int.
     
     

     unique 与 primary key 的区别: 一个表中只允许一个字段声明为 primary key, 且该字段不能为空. 但是可以有多个字段声明为 unique, 且允许空值存在.

2. 查看数据表结构

   • 基本结构

     describe tb_tmpl;
     # 或者
     desc tb_tmpl;
     # +--------+-------------+------+-----+---------+-------+
     # | Field  | Type        | Null | Key | Default | Extra |
     # +--------+-------------+------+-----+---------+-------+
     # | id     | int         | NO   |     | NULL    |       |
     # | name   | varchar(25) | YES  |     | NULL    |       |
     # | deptId | int         | YES  |     | NULL    |       |
     # | salary | float       | YES  |     | NULL    |       |
     # +--------+-------------+------+-----+---------+-------+
     

   • 详细结构. 可以额外查看表

     # \G 格式化输出
     show create table tb_empl\G;
     # 结果略
     

3. 修改数据表

   1. 修改表名

      alter table tb_empl rename to tb_emp1;
      

   2. 修改字段的数据类型

      alter table tb_emp1 modify name varchar(22);
      

   3. 修改字段名

      alter table tb_emp1 change deptId deptmentId int;
      

   4. 添加字段

      # 默认在表尾添加
      alter table tb_emp1 add phone varchar(11);
      # 添加到第一列
      alter table tb_emp1 add column1 int(11) first;
      # 在指定字段之后添加
      alter table tb_emp1 add column2 int(11) after name;
      

   5. 删除字段

      alter table tb_emp1 drop column1;
      

   6. 修改字段顺序

      # 将 name 字段调整为第一个字段
      alter table tb_emp1 modify name varchar(22) first;
      # 将 name 字段调整至 column2 后面
      alter table tb_emp1 modify name varchar(22) after column2;
      

   7. 修改表的存储引擎

      alter table tb_emp1 engine=MyISAM;
      

4. 删除数据表

   # if exists 可选. 如果有就删除. 否则啥事不做
   drop table if exists emp4;
   

   实际使用中, 可能删除的表是父表. 如果直接删除会显示失败, 需要先删除与之关联的子表, 在删除父表. 或者取消外健约束, 再单独删除父表. 案例如下:

   # 新建父表
   create table tmp_dept(
     id int(11) primary key,
     name varchar(22)
   );
   # 关联子表
   create table tmp_emp(
     id int(11) primary key,
     name varchar(25),
     deptId int(11),
     salary float,
     constraint fk_emp_dept foreign key (deptId) references tmp_dept(id)
   );
   # 尝试删除父表
   drop table tmp_dept;
   # ERROR 3730 (HY000): Cannot drop table 'tmp_dept' referenced by a foreign key constraint 'fk_emp_dept' on table 'tmp_emp'.
   # 解除关联子表的外建约束
   alter table tmp_emp drop foreign key fk_emp_dept;
   # 再删除父表
   drop table tmp_dept;