MySQL表操作及约束

修改表

重命名：

mysql> alter table user1 rename to user;

 新增一列

mysql> alter table user add image_path varchar(128) comment '路径' after birthday;

mysql> alter table user add image_path varchar(128) comment '路径' after birthday;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from user;
+------+--------+----------+------------+------------+
| id   | name   | password | birthday   | image_path |
+------+--------+----------+------------+------------+
|    1 | 张三   | 123456   | 2020-01-01 | NULL       |
+------+--------+----------+------------+------------+

查看建表语句

mysql> show create table user \G

修改列中的属性

mysql> alter table user modify name varchar(60);

会把之前的注释覆盖，增长对姓名没有影响。

删除一列。

alter table users drop password;

修改表名

alter table users rename to employee;

修改行的名字

mysql> alter table employee change name xingming varchar(60); --新字段需要完整
定义

 约束

真正约束字段的是数据类型，但是数据类型约束很单一，需要有一些额外的约束，更好的保证数据的合 法性，从业务逻辑角度保证数据的正确性。比如有一个字段是email，要求是唯一的。

表的约束很多，这里主要介绍如下几个： null/not null,default, comment, zerofill，primary key，auto_increment，unique key 。

 not null  非空

mysql> create table myclass(
   -> class_name varchar(20) not null,
   -> class_room varchar(10) not null);

 前两列初识化时必须有值否则会发生报错

默认约束  default

插入时不指定会使用默认设定的值。

没有指定列，也没有指定default就会报错。

mysql> desc test;
+--------+------------------+------+-----+---------+-------+
| Field  | Type             | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| name   | varchar(20)      | NO   |     | NULL    |       |
| age    | tinyint unsigned | YES  |     | 18      |       |
| gender | char(1)          | YES  |     | 男      |       |
+--------+------------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

default 和 notnull是相互补充的。

MySQL会优化default

设了notnull后MySQL就不会加default了。

列描述

列描述：comment，没有实际含义，专门用来描述字段，会根据表创建语句保存，用来给程序员或DBA 来进行了解

Create Table: CREATE TABLE `user` (
  `id` int DEFAULT NULL,
  `name` varchar(60) DEFAULT NULL,
  `password` char(32) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `image_path` varchar(128) DEFAULT NULL COMMENT '路径'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

zerofill

位数不够补0，够了不补。

mysql> select hex(3.14);
+-----------+
| hex(3.14) |
+-----------+
| 3         |
+-----------+
1 row in set (0.00 sec)

hex代表16进制。

主键

主键：primary key用来唯一的约束该字段里面的数据，不能重复，不能为空，一张表中最多只能有一个 主键；主键所在的列通常是整数类型

mysql> desc test_key;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int unsigned | NO   | PRI | NULL    |       |
| name  | varchar(20)  | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

PRI 表示主键存在。

复合主键

mysql> create table tt14(
-> id int unsigned,
-> course char(10) comment '课程代码',
-> score tinyint unsigned default 60 comment '成绩',
-> primary key(id, course) -- id和course为复合主键
-> );

唯一键

唯一键的本质和主键差不多，唯一键允许为空，而且可以多个为空，空字段不做唯一性比较。

mysql> create table student(
    -> id char(20) primary key,
    -> name varchar(32) not null ,
    -> telphone char(20) unique,
    -> qq varchar(64) unique
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> desc student;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | char(20)    | NO   | PRI | NULL    |       |
| name     | varchar(32) | NO   |     | NULL    |       |
| telphone | char(20)    | YES  | UNI | NULL    |       |
| qq       | varchar(64) | YES  | UNI | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

外键

mysql> create table student  ( id int unsigned primary key, name varchar(20) not null, telephone varchar(20) unique, class_id int, foreign key(class_id) references class(id) );
Query OK, 0 rows affected (0.11 sec)

mysql> desc student;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id        | int unsigned | NO   | PRI | NULL    |       |
| name      | varchar(20)  | NO   |     | NULL    |       |
| telephone | varchar(20)  | YES  | UNI | NULL    |       |
| class_id  | int          | YES  | MUL | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

Create Table: CREATE TABLE `student` (
  `id` int unsigned NOT NULL,
  `name` varchar(20) NOT NULL,
  `telephone` varchar(20) DEFAULT NULL,
  `class_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `telephone` (`telephone`),
  KEY `class_id` (`class_id`),
  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

CONSTRAINT、

代表外键的名字。

综合案例