数据表的基本操作

本章思维导图：链接：https://pan.baidu.com/s/102dCNRnVRGRY172ttWA2GA 提取码：m5ka

4.1 创建数据表

4.1.1 创建表基本语法

• use <数据库名> ；   在创建数据表之前，先指定操作是在哪个数据库中进行
• 创建表

  create table <表名>
  (字段1   数据类型   [约束条件]  [默认值]
   字段2   数据类型   [约束条件]  [默认值]
   );
  

• show tables;查看该库中所有的表

   +---------------+
   | Tables_in_emp |
   +---------------+
   | zoo           |
   +---------------+
  

4.1.2 主键约束

主键又叫主码，表中一列或者多列的组合。要求主键列的数据唯一，不允许为空。好处是能唯一的标识表中的一条记录，可以结合外键来定义不同数据表之间的关系，且可以加快数据库查询的速度。

• 单字段主键（2种创建方法）

  mysql> create table zoo3(
      -> id int(11) primary key,
      -> name varchar(255)
      -> );
  

  mysql> create table zoo4(
      -> id int(11),
      -> name varchar(255),
      -> primary key(id)
      -> );
  

• 多字段联合主键

  mysql> create table zoo5(
  	    -> id int(11),
  	    -> name varchar(255),
  	    -> primary key (id,name)
  	    -> );
  

4.1.3 外键约束

用来在两个表的数据之间建立链接，可以是一列或者多列。主要作用是保证数据的完整性，一致性。

主表（父表）：主键所在的表。

从表（子表）：外键所在的表。

mysql> create table zoo2(
    -> id2 int(11) primary key ,
    -> name varchar(255)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> create table zoo3(
    -> id3 int(11),
    -> gender varchar(1),
    -> constraint fk_emp_zoo2 foreign key (id3) references zoo2(id2)
    -> );
Query OK, 0 rows affected (0.03 sec)

关联字段的数据类型必须匹配，int(1)和int(2)是同一类型，不同则报错。

4.1.4 非空约束

字段插入的值不能为空。

mysql> create table zoo4(
    -> id int(11),
    -> name varchar(25) not null
    -> );
Query OK, 0 rows affected (0.03 sec)

4.1.5 唯一性约束

要求该列唯一，允许为空，但只能出现一个空值。可以确保出现一列或者几列不出现重复值。

• mysql> create table zoo5(
      -> id int(11),
      -> name varchar(11) unique
      -> );
  Query OK, 0 rows affected (0.03 sec)
  

• mysql> create table zoo6(
      -> id int(11),
      -> name varchar(11),
      -> unique(name)
      -> );
  Query OK, 0 rows affected (0.03 sec)
  

unique 和primary key 区别：unique 可以设置多个字段，primary key 只能有一个。primary key 中不能有空值，但unique允许有空值存在。

4.1.6 默认约束

插入一条新的记录时没有为这个字段赋值，则默认为指定值。

mysql> create table zoo7(
    -> id int(11) default 111
    -> );
Query OK, 0 rows affected (0.03 sec)

4.1.7 设置表属性值的自动增加

auto_increment; 初始值为1，一张表中只能有一个字段使用auto_increment 约束，且该字段必须为主键的一部分。可以时任何整数类型（tinyint,smallint,int,bigint等）。

mysql> create table zoo8(
    -> id int(11) primary key auto_increment,
    -> name varchar(25)
    -> );
Query OK, 0 rows affected (0.03 sec)

4.2 查看数据表结构

• 查看表基本结构

  describe/desc   表名；
  

  +-------+---------+------+-----+---------+-------+
  | Field | Type    | Null | Key | Default | Extra |
  +-------+---------+------+-----+---------+-------+
  | id    | int(11) | YES  |     | 111     |       |
  +-------+---------+------+-----+---------+-------+	
  

  Null:表示是否可以存储Null值
  Key:是否编制索引。pri表示是主键的一部分，uni表示该列是unique索引的一部分，mul表示某给定值允许出现多次
  Default：有没有默认值，为多少
  Extra:表示可以获取的与给定列有关的附加信息，例如auto_increment

• 查看表详细结构

  show create table 表名\G
  

4.3 修改数据表

4.3.1 修改表名

alter table <旧表名> rename [to] <新表名>;

mysql> show tables;
+---------------+
| Tables_in_emp |
+---------------+
| zoo           |
| zoo2          |
| zoo3          |
| zoo4          |
+---------------+
4 rows in set (0.00 sec)

mysql> alter table zoo4 rename zoo5;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+---------------+
| Tables_in_emp |
+---------------+
| zoo           |
| zoo2          |
| zoo3          |
| zoo5          |
+---------------+
4 rows in set (0.00 sec)

4.3.2 修改字段的数据类型

alter table <表名> modify <字段名> <数据类型>
alter table zoo5 modify id int(12);

4.3.3 修改字段名

alter table <表名> change <旧字段名> <新字段名> <新数据类型>
alter table zoo change name name varchar(22);

可以仅修改数据类型，达到和modify的效果。当数据表中有数据时，不要轻易改变数据类型，因为由于不同类型数据在机器中存储方式和长度不同，修改数据类型可能会影响到原有数据。

4.3.4 添加字段

alter table <表名> add <字段名> <数据类型>[约束条件] [first| after <已存在字段名>]
alter table zoo5 add gender varchar(11) after id;
alter table zoo5 add salary int(11) not null after id;

4.3.5 删除字段

alter table <表名> drop <字段>；

mysql> alter table zoo drop name;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

4.3.6 修改字段排列位置

alter table <表名> modify <字段名> <数据类型> first|after <字段名>

mysql> alter table zoo5 modify id int(12) after gender ;

4.3.7 更改表的存储引擎

alter table <表名> engine = <引擎名>；

alter table zoo5 engine=myisam;

4.3.8 删除表的外键约束

删除外键约束后，就会解除主表和从表之间的关联关系。

alter table <表名> drop foreign key <外键约束名>;

4.4 删除表

• 删除无关联的表

   drop table [if exists] 表1，表2……；
  

• 删除有关联的表
  
  先删父表会失败，原因是破坏了表的参照完整性。
  应先删子表，再删父表。或者先取消外键约束条件，再删父表。