数据库之五大约束，表与表的关联关系-31

最新推荐文章于 2023-04-22 13:38:14 发布

weixin_33939380

最新推荐文章于 2023-04-22 13:38:14 发布

阅读量1.1k

点赞数

文章标签：数据库数据结构与算法

原文链接：http://www.cnblogs.com/jokezl/articles/10183141.html

版权

数据库之五大约束，表与表的关联关系-31

内容:

1.约束
    not null
    default
    unique
    primary key
    auto_increment
    foreign key
2.表之间的关联关系

1.约束

 1.什么是约束
   除了数据类型以外额外添加的约束
 2.为什么要使用约束
   为了保证数据的合法性 完整性
 3.分类：
    not null
    default
    unique
    primary key
    auto_increment
    foreign key

1.not null 非空约束数据不能为空

例:学生表的姓名字段
此时没有设置not null,可以插入null,当什么也不填时,默认插入空
mysql> create table student1(id int,name char(10));

mysql> insert into student1 value ();

mysql> select * from student1;
+------+------+
| id   | name |
+------+------+
| NULL | NULL |
+------+------+


设置为not null的字段不可以插入null,当什么也不填时,设置为not null的字段什么也不插入
mysql> create table student(id int,name char(10) not null);

mysql> insert into student value(null,null);
ERROR 1048 (23000): Column 'name' cannot be null
mysql> insert into student value(null,'a');

mysql> select * from student;
+------+------+
| id   | name |
+------+------+
| NULL | a    |
+------+------+

mysql> insert into student value();

mysql> select * from student;
+------+------+
| id   | name |
+------+------+
| NULL | a    |
| NULL |      |
+------+------+

2.default 默认值约束可以指定字段的默认值

应用场景:
美柚的性别字段 默认为女
游戏 注册成功 送一万金币
例:设置用户性别默认为女
mysql> create table user (id int,name char(10),sex char(5) default 'male');

mysql> insert into user(id,name) value(1,'lmz');

mysql> select * from user;
+------+------+------+
| id   | name | sex  |
+------+------+------+
|    1 | lmz  | male |
+------+------+------+

3.unique 唯一性约束该字段的值不能重复

unique其实是一种索引,索引是一种数据结构,用于提高查询效率,可以为空
一张表中可以有多个唯一约束.
应用场景:身份证 手机号 学号
1.单列唯一约束
mysql> create table t1(idcard char(18) unique);

mysql> INSERT INTO t1 VALUE(110);

mysql> INSERT INTO t1 VALUE(110);
ERROR 1062 (23000): Duplicate entry '110' for key 'idcard'

2.多列联合唯一约束
意思： 身份证相同 并且 手机号相同 才叫做相同 
mysql> CREATE TABLE t2 (idcard char(18),phonenumber char(11),
unique(idcard,phonenumber));

mysql> INSERT INTO t2 VALUE(110,112);
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO t2 VALUE(110,110);
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO t2 VALUE(110,112);
ERROR 1062 (23000): Duplicate entry '110-112' for key 'idcard'

4.primary key ******

主键primary key是innodb存储引擎组织数据的依据，innodb称之为索引组织表，
一张表中必须有且只有一个主键,主键类型建议为int(不怕被打死可以用其它类型).
主键约束:用于唯一标识表中一条记录
       如何能做到唯一标识 该字段 只要是惟一的 并且不为空 即可
       从约束的角度来看主键约束和非空加唯一约束的作用相同(主键就是由唯一约束和非空约束组成的约束)
       那它们之间的区别是什么？
       唯一约束 是一种索引 必然存在硬盘上的某个文件中  是物理层面（实实在在存在的数据）
       primary key 是一种逻辑意义上的数据 （实际上不存在）                    
       就像 一男一女 可以组成夫妻 但是夫妻只是一种称呼 不实际存在
有主键和没有主键的区别？
   1.无法区分两个相同记录,比如班级里有两个人名字相同
   2.有主键则意味有索引,效率更高
   3.可以建立关联关系
语法：
    mysql> CREATE table stu(id int primary key,name char(10));

    mysql> INSERT INTO stu(name) VALUE('lmz');
    不插入id,默认插入0
    mysql> INSERT INTO stu(name) VALUE('lmz');
    ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
    mysql> select * from stu;
    +----+------+
    | id | name |
    +----+------+
    |  0 | lmz  |
    +----+------+
    mysql> INSERT INTO stu VALUE(1,'lmz');
    Query OK, 1 row affected (0.08 sec)

    mysql> INSERT INTO stu VALUE(1,'lmz');
    ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
    mysql> SELECT *FROM stu;
    +----+------+
    | id | name |
    +----+------+
    |  0 | lmz  |
    |  1 | lmz  |
    +----+------+


多列联合主键：每列相同才认为是相同
mysql> CREATE table t3(idcard char(18),phonename char(11),
primary key(idcard,phonename));

mysql> desc t3;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| idcard    | char(18) | NO   | PRI |         |       |
| phonename | char(11) | NO   | PRI |         |       |
+-----------+----------+------+-----+---------+-------+

mysql> INSERT INTO t3 VALUE(110,111);
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO t3 VALUE(110,112);
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO t3 VALUE(110,112);
ERROR 1062 (23000): Duplicate entry '110-112' for key 'PRIMARY'
mysql> SELECT * FROM t3;
+--------+-----------+
| idcard | phonename |
+--------+-----------+
| 110    | 111       |
| 110    | 112       |
+--------+-----------+

5.auto_increment:自动增长******

作用:通常搭配主键字段使用,可以自动为你的数据分配主键
如何分配的?
添加一条就自动加1  计数从1开始
注意: 自动增长 只能用于整型
语法: ******
如果主键是自动增长  你可以跳过这个字段 也可以为它插入null  都可以
mysql> CREATE table t4 (id int primary key auto_increment,name char(10));

mysql> INSERT INTO t4(name) VALUE('lmz');
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO t4 VALUE (null,'lmz');
Query OK, 1 row affected (0.13 sec)

mysql> SELECT * FROM t4;
+----+------+
| id | name |
+----+------+
|  1 | lmz  |
|  2 | lmz  |
+----+------+

修改自动增长的起始位置  **
mysql> ALTER table t4 AUTO_INCREMENT=5;

mysql> INSERT INTO t4 VALUE (null,'lmz');
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM t4;
+----+------+
| id | name |
+----+------+
|  1 | lmz  |
|  2 | lmz  |
|  5 | lmz  |
+----+------+

6.foreign key:外键******

mysql提供了 foreign key 专门用于为表和表之间 建立物理关联

思考 表里存储的是一条条的记录
     两个表之间能产生的关系有哪些?
        现有 A B两张表
        1.多对一
        2.一对一
        3.多对多

在查找表之间的关系时  要分别站在 不同表去思考
        1. 从员工出发  员工对于部门来说,多个员工对应一个部门
        2. 从部门出发  一个部门对应多个员工
        如果两个得到的关系不同,则认为这种多对一关系是单向


先创建部门表
create table dept(id int primary key auto_increment,name char(10),manager char(10));
在创建员工表
create table emp(id int primary key auto_increment,name char(10),dept_id int,
foreign key(dept_id) references dept(id));


需求: 设计  学员表 和 班级表  
    两个表多对一的关系  通过外键来进行关联
    外键加在谁身上?   加到从表上
mysql> CREATE table class(id int primary key auto_increment,name char(10));

mysql> CREATE table student(id int primary key auto_increment,name char(10),
cls_id int,foreign key(cls_id) references class(id));

mysql> INSERT INTO class VALUE (1,'脱产三期');

mysql> INSERT INTO student VALUE(1,'lmz',1);

mysql> SELECT * FROM student;
+----+------+--------+
| id | name | cls_id |
+----+------+--------+
|  1 | lmz  |      1 |
+----+------+--------+
1 row in set (0.00 sec)

mysql> SELECT * FROM class;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 脱产三期     |
+----+--------------+
mysql> drop table class;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint 
fails
mysql> drop table student;
Query OK, 0 rows affected (0.20 sec)

mysql> drop table class;
Query OK, 0 rows affected (0.12 sec)
总结: 外键的作用  表与表之间建立联系
添加外键约束时: 产生的限制
              被关联的表需要先被创建
              主表先插入数据,从表才可以继续插入数据
              需要先删除从表,才可以删除主表
              在更改班级编号时,需先保证没有学生关联到此班级
简单的说 外键指的是主表的主键
外键加上以后 主表中的数据删除和更新时,都受到限制.解决的方案是为外键添加级联操作

2.表之间的关联关系

1.级联操作

级联操作:指的是就是同步更新和删除
语法:在创建外键时  在后面添加 on update cascade   同步更新
                          on delete cascade   同步删除

实例:
   对主表的id进行更新,从表的外键也会跟着更新
   删除某条主表记录,从表外键对应的数据也会跟着删除

mysql> CREATE table class (id int primary key auto_increment,name char(10));

mysql> CREATE table student(id int primary key auto_increment,
name char(10),
cls_id int,
foreign key (cls_id) references class (id) on update cascade on delete cascade);

mysql> INSERT INTO class VALUE(1,'脱产三期');

mysql> INSERT INTO student VALUE(1,'lmz',1);

mysql> UPDATE class SET id='2' where name='脱产三期';

mysql> SELECT * FROM class;
+----+--------------+
| id | name         |
+----+--------------+
|  2 | 脱产三期     |
+----+--------------+

mysql> SELECT * FROM student;
+----+------+--------+
| id | name | cls_id |
+----+------+--------+
|  1 | lmz  |      2 |
+----+------+--------+

mysql> DELETE FROM class where id='2';

mysql> SELECT * FROM student;
Empty set (0.00 sec)

2.多对多关系的处理

mysql> CREATE table student(id int primary key auto_increment,name char(10));

mysql> CREATE table teacher(id int primary key auto_increment,name char(10));

mysql> CREATE table s_t(s_id int,t_id int,foreign key(s_id) references student(id),
foreign key(t_id) references teacher(id),primary key(s_id,t_id));

mysql> INSERT INTO student VALUE(1,'lmz');

mysql> INSERT INTO student(name) VALUE('lmg');

mysql> INSERT INTO student(name) VALUE('lbz');

mysql> INSERT INTO teacher(name) VALUE('egon');

mysql> INSERT INTO teacher(name) VALUE('lxx');

mysql> INSERT INTO s_t VALUE(1,1);

mysql> INSERT INTO s_t VALUE(2,1);

mysql> INSERT INTO s_t VALUE(3,2);

mysql> SELECT * FROM student;
+----+------+
| id | name |
+----+------+
|  1 | lmz  |
|  2 | lmg  |
|  3 | lbz  |
+----+------+

mysql> SELECT * FROM teacher;
+----+------+
| id | name |
+----+------+
|  1 | egon |
|  2 | lxx  |
+----+------+

mysql> SELECT * FROM s_t;
+------+------+
| s_id | t_id |
+------+------+
|    1 |    1 |
|    2 |    1 |
|    3 |    2 |
+------+------+

posted on 2018-12-27 09:30 漫天飞雪世情难却阅读( ...) 评论( ...) 编辑收藏

转载于:https://www.cnblogs.com/jokezl/articles/10183141.html

weixin_33939380

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库之五大约束，表与表的关联关系-31

数据库之五大约束，表与表的关联关系-31 内容:1.约束 not null default unique primary key auto_increment foreign key2.表之间的关联关系 1.约束 1.什么是约束除了数据类型以外额外添加的约束 2.为什么要使用约束为了保...
复制链接

扫一扫