约束、多表关系及查询、范式、事务的概念与应用

最新推荐文章于 2024-07-22 15:12:37 发布

naLingling

最新推荐文章于 2024-07-22 15:12:37 发布

阅读量86

点赞数

分类专栏：数据库文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/m0_46305892/article/details/107868596

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

约束

概念：对表中的数据进行限定，保证数据的正确性、有效性和完整性

分类：

主键约束：primary key，非空且唯一，一张表只能有一个字段为主键（主键就是表中记录的唯一标识）

在创建表时，添加主键约束：

create table stu(
      id int primary key, --给id添加主键约束
      name varchar(20)
);

删除主键：

alter table stu drop primary key;

创建完表后，添加主键：

alter table stu modify id int primary key;

自动增长：如果某一列是数值类型的，使用auto-increment 可以完成数值的自动增长

在创建表时，添加主键约束，并完成自动增长：

create table stu(
      id int primary key auto-increment, --给id添加主键约束
      name varchar(20)
);

删除自动增长：

alter table stu modify id int;

添加自动增长：

alter table stu modify id int auto-increment;

非空约束：not null

创建表时添加约束：

create table stu(
      id int,
      name varchar(20) not null  --name为非空
);

创建表完后，添加非空约束：

alter table stu modify name varchar(20) not null;

删除name的非空列表：

alter table stu modify name varchar(20);

唯一约束：unique，值不能重复

创建表时，添加唯一约束（在mysql中，唯一约束限定的列的值可以有多个null）：

create table stu(
      id int,
      phone_number varchar(20) unique --添加了唯一约束
);

删除唯一约束：

alter table stu drop index phone_number;

在创建表后，添加唯一约束：

alter table stu modify phone_number varchar(20) unique;

外键约束：foreign key，让表与表产生关系，从而保证数据的正确性

在创建表时，可以添加外键：

create table 表名(
    .....
    外键列
    constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
);

删除外键：

alter table 表名 drop foreign key 外键名称;

创建表之后，添加外键

alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称);

添加级联操作（级联更新：on update cascade；级联删除：on delete cascade）：

alter table 表名 add constraint 外键名称
   foreign key (外键字段名称) references 主表名称(主表列名称) on update cascade on delete cascade;

多表之间的关系与实现方式

一对多（多对一）：在多的一方建立外键，指向一的一方的主键，如部门和员工
多对多：需要借助第三张中间表，中间表至少包含连个字段，这两个字段作为第三张表的外键，分别指向两张表的主键，如学生和课程
一对一（了解）：在任意一方添加唯一外键指向另一方的主键，如人和身份证

范式

概念：设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。目前有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式（4NF）、第五范式（5NF，又称完美范式）

多表查询

笛尔卡积：有两个集合A、B，取这两个集合的所有组成情况。要完成多表查询，需要消除无用的数据

分类

内连接查询：从哪些表中查询数据，条件是什么，查询哪些字段

隐式内连接：使用where条件消除无用数据
显式内连接：select 字段列表 from 表名1 (inner) join 表名2 on 条件；

外连接查询

左外连接：select 字段列表 from 表1 left (outer) join 表2 on 条件；查询的是左表所有数据以及其交集的部分
右外连接：select 字段列表 from 表1 right (outer) join 表2 on 条件；查询的是右表所有数据以及其交集的部分

子查询：查询中嵌套查询，称嵌套查询为子查询

--查询工资最高的员工信息

select max(salary) from emp;  --1.查询最高的工资是多少
select * from emp where emp.'salary'=9000; --2.查询员工信息，并且工资等于9000的

--一条sql就完成以上操作
select * from emp where emp.'salary'=(select max(salary) from emp;

子查询的结果是单行单列的：子查询可以作为条件，使用运算符>、>=、<、<=、=去判断

--查询员工工资小于平均工资的人
select * from emp where emp.salary < (select avg(salary) from emp;

子查询的结果是多行但列的：子查询可以作为条件，使用运算符in来判断

--查询‘财务部’和‘市场部’所有的员工信息
select id from dept where name='财务部' or name='市场部';
select * from emp where dept_id=3 or dept_id=2;
--子查询
select * from emp where dept_id in (select id from dept where name='财务部' or name='市场部');

子查询的结果是多行多列的：子查询可以作为一张虚拟表参与查询

--查询员工入职日期是2011-11-11日之后的员工信息和部门信息
--子查询
select * from dept t1,(select * from emp where emp.'join_date' > '2011-11-11) t2 where t1.id=t2.dept_id;
--普通内连接
select * from emp t1,dept t2 where t1.'dept_id'=t2.'id' and t1.'join_date' > '2011-11-11;