Mysql 表的操作和约束

表的创建

-- 表的创建语法格式输入：
create table 表名(
    字段名1 数据类型,
  字段名2 数据类型,
  ...
);
例如：
    $ use demo;
  $ create table t_user(
   > name varchar(255),
   > age int
   > );

Mysql 中的数据类型

mysql 常见的数据类型如下：

char 和 varchar 的选择：
char（10）：无论储存的数据有没有达到指定长度，都会分配这些空间，产生多余空间。
varchar（10）：如果存储的数据没有达到指定长度，varchar 会自动计算需要的空间，不会产生多余的空间。

在实际开发中，当某个字段数据长度不会发生改变时使用 char，比如说：性别，当一个字段的长度不固定时使用 varchar 比如：简介。

向表中插入数据

-- 插入数据语法：
    $ insert into 表名(字段1, 字段2) values (值1, 值2);
例如：
    $ insert into t_user(name, age) values ('kk', 18);
     -- 下面这种写法可以实现添加, 但是 values 必须和表的结构位置一一对应：
    $ insert into t_user values ('sk', 18);
 -- 一次插入多条数据使用逗号隔开, 直接写 values
     $ insert into 表名(字段1, 字段2) values (值1, 值2), (值1, 值2), (值1, 值2);
 例如：
     $ insert into t_user(name, age) values ('kk', 18), ('xk', 20), ('xh', 23);

在插入数据时，字段和值的数量必须相同，且数据类型必须对应, 如果忽略某些字段时，自动填充 null。

表的复制和批量插入

通过 select 把查询数据来的数据复制为一张表或插入到一张表中。

-- 表的复制语法：
    $ create table 表名 as select 查询语法;
例如：
    $ create table t_clone_emp as select * from emp;
-- 批量插入语法：
    $ insert into t_clone_emp select 查询语法;
例如：
    $ insert into t_clone_emp select * from emp;

修改表中的数据

-- 修改表中数据的语法：
    $ update 表名 set 字段1=值, 字段2=值 where 条件;
例如:
    $ update t_user set name='superKK', age = 20 where name = 'kk';

如果不加 where 条件会将表中所有数据都会修改掉！

删除表中的数据

-- 删除表中数据语法：
    $ delete from 表名 where 条件;
 例如：
     $ delete from t_user where name = 'superKK';

如果不加 where 条件会将表中所有数据都会删除掉！

截断表：

-- 当表中数据非常多时, delete 删除的时间会很长, 可以使用下面语法截断表:
    $ truncate table 表名;
例如：
    $ truncate table t_user;

注：

1. truncate 只是把表中的数据全部删除了，表的结构还存在。

2. 表被截断后无法回滚，数据彻底丢失，危险操作。

   约束

   常见的约束有：

   • 非空约束 ：not null
   • 唯一性约束：unique
   • 主键约束 ：primary key
   • 外键约束 ：foreign key

     非空约束

     非空约束是指在添加数据时不能为空： ```sql create table t_user( id int not null, — 给 id 字段添加非空约束 not null name varchar(255), age int );

— 添加时不指定 id $ insert into t_user(name, age) values(‘kk’, 18); — 报错：ERROR 1364 (HY000): Field ‘id’ doesn’t have a default value

— 指定 id $ insert into t_user(id, name, age) values(1, ‘kk’, 18);

<a name="MkF6H"></a>
### 唯一性约束
唯一性约束指字段的值不能出现重复，但是可以为 null。
```sql
create table t_user(
  id int unique, -- 给 id 字段添加唯一性约束 unique
    name varchar(255),
  age int
);
-- 添加一条数据
    $ insert into t_user(id, name, age) values(1, 'kk', 18);
-- 尝试添加重复的 id
    $ insert into t_user(id, name, age) values(1, 'xk', 18);
  -- 报错：ERROR 1062 (23000): Duplicate entry '1' for key 'id'

unique 可以给多个字段联合起来成为一个唯一性约束，此时只有两个联合重复时才会报错：

create table t_user(
  id int,
    name varchar(255),
  age int,
  unique(id, name) -- id, name 联合添加一个约束
);
-- 单条重复不会报错：
    $ insert into t_user(id, name, age) values(1, 'kk', 18);
  $ insert into t_user(id, name, age) values(1, 'xk', 18); -- name 没有重复
-- 联合重复报错：
    $ insert into t_user(id, name, age) values(1, 'kk', 18);
  -- 报错：ERROR 1062 (23000): Duplicate entry '1-kk' for key 'id'

上面这种在最后添加联合添加一个约束称之为表级约束，在字段类型后面定义的叫做列级约束。

主键约束

主键约束 primary key ，值不能为空也不能重复。

create table t_user(
  id int primary key, -- id 为主键
    name varchar(255),
  age int
);
-- 添加一条数据
    $ insert into t_user(id, name, age) values(1, 'kk', 18);
-- 不能重复
    $ insert into t_user(id, name, age) values(1, 'kk', 18); -- error
-- 不能为空
    $ insert into t_user(name, age) values('xk', 18); -- error

-- 复合主键：多个字段联合为一个主键约束
create table t_user(
  id int,
    name varchar(255),
  age int,
  primary key(id, name) -- 复合主键
);
-- 添加一条数据
    $ insert into t_user(id, name, age) values(1, 'kk', 18);
-- id 重复, 正常
    $ insert into t_user(id, name, age) values(1, 'xk', 18);
-- id 和 name 都重复, 出错
    $ insert into t_user(id, name, age) values(1, 'kk', 18); -- error
/*
* 注：此方式不推荐使用！
*/

主键约束的作用：**
表的设计三范式中要求每一张表都要有一个主键，主键就是这张表中这行数据的唯一标示，就像身份证号一样。

主键的分类：
根据主键字段的字段数量划分为：

  - 单一主键 （一个字段的主键约束）
  - 复合主键 （多个字段联合为一个主键约束，复合主键不符合设计三范式，不推荐使用）

根据主键的性质划为为：

  - 自然主键 （与业务没关联的主键，如：1、2、3）
  - 业务主键 （与业务相关联的数据作为主键，如身份证号，业务耦合太高不推荐使用）

注：一张表中只能有一个主键。

主键自增：
可以设置主键自增 auto_increment，这样后面添加数据时就可以省略主键不写，自动维护一个默认从 1 开始自增的主键。

create table t_user(
  id int primary key auto_increment, -- id 为主键, 并设置主键自增
    name varchar(255),
  age int
);
-- 添加数据
    $ insert into t_user(name, age) values ('kk', 18), ('xk', 20);
-- 查询
    $ insert into t_user(name, age) values ('kk', 18), ('xk', 20);
-- 结果如下:
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | kk   |   18 |
|  2 | xk   |   20 |
+----+------+------+

外键约束

外键约束 foreign key , 在一种业务场景下，如：设计表维护学生和班级信息，如果设计一张表会出现数据冗余的情况，因为一个班级会对应多个学生，如果在一张表中，那么每个学生就要维护一个对应班级名称，这个班级名称就冗余了，所以这时可以设计两张表，一张学生表只维护学生信息，另一张班级表维护班级信息，在学生表中有一个 classId 字段来指向班级表的班级 id，这样就减少了冗余的数据。
但是在学生表的 classId 可能会胡乱填写，导致无法和班级表对应上，这时就可以使用外键约束，让学生表中的 classId 只能是班级表中的班级 id 中的一项。

-- 外键添加方式：
    $ foreign key(字段名) references 表名(字段名) ; -- 放在添加表语句最后
例如：
-- 创建班级表
  create table t_class(
    cid int primary key,
    classname varchar(255)
  );
-- 创建学生表
    create table t_student(
      sid int,
    name varchar(255),
    classid int,
    foreign key(classid) references t_class(cid) -- 添加外键
  );
-- 测试:
    $ insert into t_class(cid, classname) values (1, 'class one');
  $ insert into t_student(sid, name, classid) values(1, 'kk', 10); -- classId 不在 t_class 的 id 中, 添加失败
  $ insert into t_student(sid, name, classid) values(1, 'kk', 1); -- success
  $ insert into t_student(sid, name) values(2, 'xk'); -- 外键值可以为 null

在外键约束中添加外键的表叫做子表，被引用的表叫做父表，如上面的 t_student 表中的 sid 字段是一个外键，t_student 就是子表，t_class 的 cid 字段被 t_student 外键引用着，t_class 就是 t_student 的父表。

在操作这两表时，有一定的顺序要求：

  - 删除数据的时候，先删除子表，再删除父表。
  - 添加数据时，先添加父表，再添加子表。
  - 创建表的时候，先创建父表，再创建子表。
  - 删除表的时候，先删除子表，再删除父表。

注：

• 被外键引用的字段必须具有唯一性约束 unique。
• 外键值可以为 null 。