MySQL -- 约束条件|表关系

满天繁星_

已于 2022-04-12 11:18:25 修改

阅读量94

点赞数

分类专栏：笔记文章标签：数据库开发

于 2022-04-12 11:15:15 首次发布

本文链接：https://blog.csdn.net/weixin_51081062/article/details/124117995

版权

笔记专栏收录该内容

15 篇文章 0 订阅

订阅专栏

约束条件

default

# 默认值

create database day46;
use day46;


# 补充  插入数据的时候可以指定字段
create table t1(
	id int,
    name char(16)
);
# insert into values('jason',1);  # 不写字段也可以
			# 写了就是指定字段赋值
insert into t1(name,id) values('jason',1);


create table t2(
	id int,
    name char(16),
    gender enum('male','female','others') default 'male'
);

desc t2;  # 看看结构

# 默认值如何生效
insert into t2(id,name) values(1,'jason');  # 不对gender赋值, 默认 male
insert into t2 			values(2,'egon','female');

select * from t2;  # 看看字段

unique

# 唯一

# 不指定字段, 插入值
insert into t2 			values(2,'egon','female');
# 又插入, id字段有两个都是2, 不想要这样  --> 唯一

# 单列唯一
create table t3(
	id int unique,  # 直接加unique就行了
    name char(16)
);
# 验证unique
insert into t3 values(1,'jason'),(1,'egon');
# ERROR 1062 (23000): Duplicate entry '1' for key 'id'

insert into t3 values(1,'jason'),(2,'egon');
# Query OK, 2 rows affected (0.00 sec)


# 联合唯一
"""
    ip和port
    单个都可以重复 但是加在一起必须是唯一的
"""
create table t4(
	id int,
    ip char(16),
    port int,
    unique(ip,port)  # 这样用unique
);
insert into t4 values(1,'127.0.0.1',8080);
insert into t4 values(2,'127.0.0.1',8081);
insert into t4 values(3,'127.0.0.2',8080);

insert into t4 values(4,'127.0.0.1',8080);  # 报错
# ERROR 1062 (23000): Duplicate entry '127.0.0.1       -8080' for key 'ip'

primary key

# 主键

# primary key单单从约束效果上来看primary key等价于not null + unique
# 非空且唯一！！！

create table t5(id int primary key);

# 验证: 非空, 唯一
insert into t5 values(null);  # 报错
# ERROR 1048 (23000): Column 'id' cannot be null
insert into t5 values(1),(1);  # 报错
# ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
insert into t5 values(1),(2); 
# Query OK, 2 rows affected (0.04 sec)

select * from t5;


"""
    primary key 除了有约束效果之外 它还是Innodb存储引擎组织数据的依据
    Innodb存储引擎在创建表的时候必须要有primary key[原来没弄也能建表??]
    因为它类似于书的目录 能够帮助提示查询效率并且也是建表的依据
"""

# 1 一张表中有且只有一个主键
# 如果你没有设置主键
# 那么会从上往下搜索直到遇到一个 非空且唯一的字段 将它自动升级为主键
create table t6(
	id int,
    name char(16),
    age int not null unique,
    addr char(32) not null unique
);
desc t6;  # 会看到 age的key变为了PRI

# 2 如果表中没有主键也没有其他任何的非空且唯一字段
#   那么Innodb会采用自己内部提供的一个隐藏字段作为主键
#   隐藏意味着你无法使用到它 就无法提升查询速度

# 3 一张表中通常都应该有一个主键字段 并且通常将id / uid / sid字段作为主键


# 单个字段主键
create table t7(
    id int primary key
	name char(16)
);
desc t7;
# 联合主键(多个字段联合起来作为表的主键 本质还是一个主键)
create table t8(
    ip char(16),
    port int,
    primary key(ip,port)
);
desc t8;


# 所以: 以后我们在创建表的时候id字段(唯一标识)一定要加primary key

auto_increment

# 自增

# 当编号特别多的时候 人为的去维护太麻烦
create table t9(
	id int primary key auto_increment,  # 完整
    name char(16)
);
desc t9;
insert into t9(name) values('jason'),('egon'),('kevin');
select * from t9;


# 注意auto_increment通常只加在主键上 不能给普通字段加
create table t10(
	# id int primary key auto_increment,
    name char(16),
    cid int auto_increment
);
# ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

小结: id int primary key auto_increment

删除表中数据后主键的自增不会停止(这个机制是很好的)
delete from t9; 
desc t9;
insert into t9(name) values('jason'),('egon'),('kevin');
select * from t9;


truncate t9;  # 清空表数据并且重置主键       [truncate 截断, 截取]
desc t9;
insert into t9(name) values('jason'),('egon'),('kevin');
select * from t9;

表关系

引入 - 外键

        定义一张员工表表中有很多字段
                编号名字性别部门名字部门描述
          id, name, gender, dep_name, dep_desc
        1 a m    w    sb
        2 b f t s
          3 c f t s
          4 d m t s

该表的组织结构不是很清晰 [ 字段多不知道哪个是重点] (可忽视)
浪费硬盘空间 [部门重复] (可忽视)
数据的扩展性极差 [修改起来极其不方便] (无法忽视的)

如何优化？

将员工表拆分, 员工表和部门表?
问题又来了, 员工和部门之间没了联系
--> 员工表添加一列(内容必须来自部门表的id列中)来标识部门
--> 这一列, 就叫外键

外键--> 就是用来帮助我们建立表与表之间关系的字段 foreign key

种类

一对多 [注意: 没有多对一]
多对多
一对一
没关系

表关系判断

员工表与部门表为例
先站在员工表
思考一个员工能否对应多个部门(一条员工数据能否对应多条部门数据)
不能！！！
(不能直接得出结论一定要两张表都考虑完全)
再站在部门表
思考一个部门能否对应多个员工(一个部门数据能否对应多条员工数据)
能！！！
得出结论
员工表与部门表示单向的一对多, 所以表关系就是一对多

关键 --> 换位思考

一对多

foreign key

一对"多"表关系外键字段建在"多"的一方
在创建表的时候一定要先建被关联表(部门)
在录入数据的时候也必须先录入被关联表(部门)

SQL语句建立表关系 [员工 employee; 部门 department]

create table dep(
	id int primary key auto_increment,
    dep_name char(16),
    dep_desc char(32)
);

create table emp(
	id int primary key auto_increment,
    name char(16),
    gender enum('male','female','others') default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id)
);
desc dep; desc emp;
# MUL  非唯一索引的第一列，其中允许在列中多次出现给定值

insert into dep(dep_name,dep_desc) values('sb教学部','教书育人'),('ox外交部','多人外交'),('nb技术部','技术能力有限');
insert into emp(name,dep_id) values('jason',2),('egon',1),('tank',1),('kevin',3);
select * from emp; select * from dep;

删除与修改 [错误示范]

# 修改dep表里面的id字段
update dep set id=200 where id=2;  # 报错
# 删除dep表里面的数据
delete from dep where id=1;  # 报错
# ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`day46`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))

确实想删除

--> 先删除教学部对应的员工数据之后再删除部门

--> 操作太过繁琐

-> 真正做到数据之间有关系 更新时同步更新, 删除时同步删除

备注:

级联更新 <--> 同步更新; 级联删除 <--> 同步删除

级联（cascade）在计算机科学里指多个对象之间的映射关系，建立数据之间的级联关系提高管理效率

删除与修改 [正确做法]

drop table emp;drop table dep;  # 测试之前删除前两张表
# 测试开始
create table dep(
	id int primary key auto_increment,
    dep_name char(16),
    dep_desc char(32)
);

create table emp(
	id int primary key auto_increment,
    name char(16),
    gender enum('male','female','others') default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id) # 下边的两行属于这行
    on update cascade  # 同步更新
    on delete cascade  # 同步删除
);
desc dep; desc emp;
insert into dep(dep_name,dep_desc) values('sb教学部','教书育人'),('外交部','多人外交'),('nb技术部','技术能力有限部门');
insert into emp(name,dep_id) values('jason',2),('egon',1),('tank',1),('kevin',3);

# 修改dep表里面的id字段
update dep set id=200 where id=2;
select * from dep; select * from emp;
# 删除dep表里面的数据
delete from dep where id=1;
select * from dep; select * from emp;

多对多

用两张表来诠释多对多的关系 --> 书籍表和作者表

   在书籍表角度
       一本书可不可以有多个作者 --> 可以
   在作者表角度
       一个作者可不可以写多本书 --> 可以
   得出结论
       表关系为: 多对多

SQL语句建立表关系 [错误示范]

create table book(
	id int primary key auto_increment,
    title varchar(32),
    price int,
    author_id int,
    foreign key(author_id) references author(id)   # 要有author表
    on update cascade  # 同步更新
    on delete cascade  # 同步删除
);  # ERROR 1215 (HY000): Cannot add foreign key constraint
create table author(
	id int primary key auto_increment,
    name varchar(32),
    age int,
    book_id int,
    foreign key(book_id) references book(id)  # 要有book表
    on update cascade  # 同步更新
    on delete cascade  # 同步删除
);  # ERROR 1215 (HY000): Cannot add foreign key constraint

why can't

按照上述的方式创建都失败了...(都是被关联表, 哪个先创建都不行)
其实我们只是想记录书籍和作者的关系
针对多对多字段表关系不能在两张原有的表中创建外键
需要单独再开设一张专门来存储两张表数据之间的关系

SQL语句建立表关系基表 + 关联表

create table book(
	id int primary key auto_increment,
    title varchar(32),
    price int
);
create table author(
	id int primary key auto_increment,
    name varchar(32),
    age int
);
create table book2author(
	id int primary key auto_increment,
    book_id int,
    author_id int,
    foreign key(author_id) references author(id)  # 一个关联
    on update cascade  # 同步更新
    on delete cascade,  # 同步删除
    foreign key(book_id) references book(id)  # 又一个关联
    on update cascade  # 同步更新
    on delete cascade  # 同步删除
);

desc book; desc author; desc book2author;
insert into book(title, price) values('a',666),('b',777),('c',888),('d',999);
insert into author(name, age) values('aa', 16),('bb', 17);
insert into book2author(book_id, author_id) values(1,2),(1,3),(4,2),(2,1);
select * from book; select * from author; select * from book2author;

# 删除
delete from author where id=1;
# 查看
select * from book; select * from author; select * from book2author;
# --> book和author两张表没有直接关系, 有间接关系

一对一

用户表和用户详细信息表为例

一对一外键字段建在任意一方都可以, 推荐建在查询频率比较高的表中

与一对多外键的区别 --> 一对一的外键必须唯一 unique
create table userdetail(
	id int primary key auto_increment,
    phone int,
    addr varchar(64)
);
create table user(
	id int primary key auto_increment,
    name varchar(32),
    age int,
    userdetail_id int unique,  # 设置为唯一
    foreign key(userdetail_id) references userdetail(id)  # 关联
    on update cascade  # 同步更新
    on delete cascade  # 同步删除
);

表关系小结

判断表之间关系的方式
换位思考！！！

表关系的建立需要用到外键 foreign key
一对多
外键字段建在多的一方
多对多
自己开设第三张表存储
一对一
建在任意一方都可以推荐建在查询频率较高的表中

员工与部门

图书与作者

作者与作者详情

修改表

# MySQL对大小写是不敏感的

1 修改表名
    alter table 表名 rename 新表名;

2 增加字段
    alter table 表名 add 字段名 字段类型(宽度)  约束条件;
    alter table 表名 add 字段名 字段类型(宽度)  约束条件 first;  # 添加到最前面
    alter table 表名 add 字段名 字段类型(宽度)  约束条件 after 字段名;  # 添加到最后面

3 删除字段
    alter table 表名 drop 字段名;

4 修改字段
    alter table 表名 modify 字段名 字段类型(宽度) 约束条件;  # 改的是字段名的字段类型

    alter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件;
    # 可以使用change来达到modify的效果，只需在其后写一样的字段名

复制表

create table 表名 select * from 旧表;  #  不能复制主键 外键 ...

create table new_dep2 select * from dep where id>3;

Over !!