mysql学习笔记二

MySQL建表约束
  1. 主键约束
    它能够唯一确定一张表中的一条记录，也就是我们通过给某个字段添加主键约束，就可以使得该字段不重复且不为空。
    给id字段添加主键约束：
mysql> create table milk(
    -> id int primary key,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (1.68 sec)

mysql> describe milk;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.04 sec)

若该id字段重复或为空则会报错：

mysql> insert into milk values (1,'yili');
Query OK, 1 row affected (0.01 sec)

mysql> insert into milk values (1,'yili');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

mysql> insert into milk values (null,'mengniu');
ERROR 1048 (23000): Column 'id' cannot be null

联合主键
只要联合的主键值加起来不重复就可以，且联合主键中的每一个字段都不能为空：

mysql> create table chip(
    -> id int,
    -> name varchar(20),
    -> primary key(id,name)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into chip values (1,'le');
Query OK, 1 row affected (0.00 sec)

mysql> insert into chip values (2,'le');
Query OK, 1 row affected (0.01 sec)

mysql> insert into chip values (null,'le');
ERROR 1048 (23000): Column 'id' cannot be null
  1. 自增约束
    自增约束可以和主键约束一起搭配使用，只能修饰 int字段。自增长列不一定要和主键搭配，但是必须和key一起搭配；一个表最多一个自增长列。
mysql> create table meat(
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> num int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into meat (name,num) values ('zhangsan',60);
Query OK, 1 row affected (0.01 sec)

mysql> select * from meat;
+----+----------+------+
| id | name     | num  |
+----+----------+------+
|  1 | zhangsan |   60 |
+----+----------+------+
1 row in set (0.00 sec)

2.1 如果说我们创建表的时候，忘记创建主键了，怎么办？
使用alter修改表结构，添加约束：

mysql> create table tool(
    -> id int,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> alter table tool add primary key(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe tool;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | 0       |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)

2.2 使用modify修改字段，修改约束：

mysql> alter table tool modify id int auto_increment;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe tool;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.03 sec)

2.3 删除主键
如果是自增主键则需要两步，首先删除auto_increment，然后再删除主键约束。如果只是删除单独的主键，则直接进行第二步即可。
第一步：删除auto_increment

mysql> alter table tool modify id int;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe tool;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | 0       |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.06 sec)

第二步：删除主键约束

mysql> alter table tool drop primary key;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe tool;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | 0       |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.07 sec)
  1. 唯一约束
    约束修饰的字段的值不可以重复。unique(id,name)只要id和name组合在一起不重复就可以。
mysql> create table beer(
    -> id int,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> alter table beer add unique(name);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

mysql> describe cola;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.07 sec)

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

删除唯一约束：

mysql> alter table cola drop index name;
Query OK, 0 rows affected (1.71 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> describe cola;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (1.66 sec)

添加唯一约束：

mysql> alter table cola modify name varchar(20) unique;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe cola;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
  1. 非空约束
    修饰的字段的值不能为null。
mysql> create table tea(
    -> id int not null,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> describe tea;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.04 sec)
  1. 默认约束
    当我们插入字段值的时候，如果没有传值，就会使用默认值；若该字段传值则不会使用默认值。
mysql> create table sugar(
    -> id int,
    -> name varchar(20),
    -> num int default 60);
Query OK, 0 rows affected (0.09 sec)

mysql> describe sugar;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| num   | int(11)     | YES  |     | 60      |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.03 sec)

mysql> insert into sugar (id,name) values (1,'bang');
Query OK, 1 row affected (1.62 sec)

mysql> select * from sugar;
+------+------+------+
| id   | name | num  |
+------+------+------+
|    1 | bang |   60 |
+------+------+------+
1 row in set (0.00 sec)
  1. 外键约束
    外键：foregin key在从表(子表)中指向主表(父表)主键的字段。
    从表(子表)是含有外键的表；主表(父表)是外键指向的表。
    外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作。

6.1 一对多或者多对一，如学生和班级的关系，此时采用在多的一端的表中添加外键的方式。
主表：

mysql> create table classes(
    -> id int primary key,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.02 sec)

从表：

mysql> create table students(
    -> id int primary key,
    -> name varchar(20),
    -> class_id int,
    -> foreign key(class_id) references classes(id)
    -> );
Query OK, 0 rows affected (0.02 sec)

添加数据(首先需要添加班级，再添加学生)：

mysql> insert into classes values(1,'one');
Query OK, 1 row affected (0.01 sec)

mysql> insert into classes values(2,'two');
Query OK, 1 row affected (0.01 sec)

mysql> insert into students values(1001,'zhang',1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into students values(1003,'zhang',3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`shop`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))

6.2 多对多的关系。如学生与老师的关系，这时候需要重新建立一张中间表，中间表中两字段均为外键字段，且指向两张表的主键。
6.3 一对一的关系。如班级与班主任的关系，这时候无需新建表，方式与一对多相似甚至不用设置外键。
由此可以看出：
主表classes中没有的数据值，在从表中是不可以使用的。
主表中的记录被从表引用，主表的记录是不可以被删除的。

  1. 总结：
    添加约束：
    7.1 建表的时候就添加约束
    7.2 可以使用alter...add... 添加约束
    7.3 可以使用alter...modify... 添加约束
    删除约束：
    7.4 可以使用alter...drop... 删除约束
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容

  • MySQL学习笔记二之单表查询与多表查询
    title: MySQL学习笔记二之单表查询与多表查询tags: MySQL 数据库categories: MyS...
    codingXiaxw阅读 759评论 0赞 3
  • mysql学习笔记（二）
    约束1、约束保证数据的完整性和一致性；2、约束分为表级约束（针对2个或2个以上字段的约束）和列级约束；3、约束类型...
    dev_winner阅读 253评论 0赞 0
  • oracle基础知识
    系统用户： 1.sys system（sys权限最高） 2.sysman(操作企业管理器) 密码是安装的时候输入的...
    3hours阅读 1,602评论 0赞 0
  • mySQL笔记
    1、MySQL启动和关闭（安装及配置请参照百度经验，这里不再记录。MySQL默认端口号：3306；默认数据类型格式...
    强壮de西兰花阅读 677评论 0赞 1
  • MYSQL 指令集合
    手动不易，转发请注明出处 --Trance 数据库系统命令: (1).查看存储过程状态:show pro...
    Trance_b54c阅读 1,700评论 0赞 8