MySQL(约束,多表查询,事务,数据库设计)

最新推荐文章于 2024-07-26 14:20:58 发布

Zephyr-

最新推荐文章于 2024-07-26 14:20:58 发布

阅读量793

点赞数 1

文章标签：数据库 mysql java

本文链接：https://blog.csdn.net/y871181189/article/details/121837839

版权

mysql高级

1.约束

1.1概念

约束是作用于表中列上的规则, 用于限制家入表上的数据

例如:我们可以给id列上加约束, 让值不能重复, 不能为null值.
约束保证了数据库中的数据的正确性,有效性,完整性.

1.2分类

非空约束: 关键词是 Not Null

保证列中的数据不能有null值.
唯一约束: 关键词是 UNIQUE

保证列中所有数据各不相同
主键约束:关键词是Primary Key

主键是一行数据的唯一标识, 要求非空且唯一.

一般我们都会给每张表添加一个主键列用来唯一标识数据.
检查约束:关键词是 Check

保证列中的值满足某一条件

例如:可以给age列添加一个范围,最低年龄设置为1,最大年龄设置为300,这样的数据才会合理.
- 注意：MySQL不支持检查约束。
  
  这样是不是就没办法保证年龄在指定的范围内了？从数据库层面不能保证，以后可以在java代码中进行限制，一样也可以实现要求。
默认约束: 关键词是 Default

保存数据时, 未指定值采用默认值.

例如:我们给english成绩列添加该约束,制定默认值为0, 这样在添加数据时如果没有给定具体的值时就会采用默认值0.
外键约束: 关键词是 Foreign Key

外键用来上两个表的数据建立连接,保证数据的一致性与完整性.

(重点!!!)

1.3 非空约束

概念:

非空约束用于保证列中所有数据不能为null值
语法
- 添加约束
  
  – 创建表示添加非空约束
```
Create table 表名 (

	列名  数据类型 Not null,
    ...

);
```
  –或者建完表后添加非空约束
  
  Alter table 表名 Modify 字段名数据类型 Not Null;
- 删除约束
  
  Alter table 表名 Modify 字段名数据类型;

1.4 唯一约束

概念

唯一约束用于保证列中所有数据各不相同

语法

添加约束

– 创建表时添加唯一约束

Create table 表名(
列名  数据类型  Unique auto_increment,
	--Auto_Increment: 当不指定值时进行自增

);
Create table 表名(
    列名 数据类型,
    ...
    --在结尾写唯一约束
    [Constraint] [约束名称] Unique(列名)
);

-- 建完表后添加唯一约束
Alter table 表名 Modify 字段名 数据类型 Unique;

删除约束

Alter table 表名 Drop Index 字段名;

1.5 主键约束

概念

主键是一行数据的唯一标识,要求非空且唯一

一张表只能有一个主键

语法

添加约束

-- 创建表时添加主键约束
Create table 表名(
    列名 数据类型 Primary Key
[Auto_Increment],
    ...
);
Create table 表名(
    列名 数据类型,
    [Constraint] [约束名称] primary key (列名)
    );

-- 建完表后添加主键约束
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

删除约束

ALTER TABLE 表名 DROP PRIMARY KEY;

1.6 默认约束

概念

保存数据时, 未指定值则采用默认值

语法

添加约束

-- 创建表时添加默认约束
Create table 表名(
	列名 数据类型 Default 默认值,
	...
);

-- 建完表后添加默认约束
ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;

删除约束

ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;

1.7 外键约束

-- 创建表时添加
create table 表名(
    列名 数据类型,
    ...
    [constraint] [外键名称] foreign key(外键列名) references 
    主表(主表列名)
    );
    
-- 建完表后添加外键约束
Alter table 表名 add constraint 外键名称 Foreign key (外键字段名称) References 主表名称(主表列名称);


-- 删除外键约束
 Alter table 表名 drop foreign key 外键名称;

例子:

-- 部门表
CREATE TABLE dept(
	id int primary key auto_increment,
	dep_name varchar(20),
	addr varchar(20)
);
-- 员工表 
CREATE TABLE emp(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dep_id int,

```
-- 添加外键 dep_id,关联 dept 表的id主键
CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)	
```

);

2.数据库设计

2.1软件的研发步骤:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-odDvhIkA-1639045185109)(C:\Users\Administrator.DESKTOP-19OD09N\Desktop\新建文件夹\1.png)]

2.2数据库的设计理念

数据库设计就是利用我们所选用的DBMS(数据库管理系统)为业务系统构造出最优的数据模型.
建立数据库中的== 表结构 ==以及 == 表与表 == 之间的关联关系的过程.
有哪些表?表中有哪些字段?表与表之间有什么关系?

2.3 表关系(一对多)

一对多

例如: 部门和员工

一个部门对应多个员工,一个员工对应一个部门
实现方式

在多的一方建立外键, 指向一的一方的主键
例子

员工表与部门表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kJKO2pgT-1639045185113)(C:\Users\Administrator.DESKTOP-19OD09N\Desktop\新建文件夹\002.png)]

经分析发现,员工表属于多的一方,而部门表属于一的一方,此时我们会在员工表中添加一列(dep_id) , 指向于部门表的主键(id) :

建表语句如下:

-- 删除表
DROP TABLE IF EXISTS tb_emp;
DROP TABLE IF EXISTS tb_dept;

-- 部门表
CREATE TABLE tb_dept(
	id int primary key auto_increment,
	dep_name varchar(20),
	addr varchar(20)
);
-- 员工表 
CREATE TABLE tb_emp(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dep_id int,

	-- 添加外键 dep_id,关联 dept 表的id主键
	CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES tb_dept(id)	
);

查看表结构模型图：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-E63d5lP7-1639045185114)(C:\Users\Administrator.DESKTOP-19OD09N\Desktop\新建文件夹\003.png)]

2.4 表关系(多对多)

多对多
- 如: 商品和订单
- 一种商品对应多个订单, 一个订单包含多个商品
实现方式

== 建立第三张中间表 , 中间表至少包含两个外键, 分别关联双方的主键==
案例:

订单表与商品表 :

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CvJxVshe-1639045185115)(C:\Users\Administrator.DESKTOP-19OD09N\Desktop\新建文件夹\004.png)]

经过分析发现, 订单表和商品表都属于多的一方,此时我们需要创建一张中间表,在中间表中添加订单表的外键和商品表的外键指向两张表的主键:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kHEP7NRX-1639045185116)(C:\Users\Administrator.DESKTOP-19OD09N\Desktop\新建文件夹\005.png)]

建表语句如下：

-- 删除表
DROP TABLE IF EXISTS tb_order_goods;
DROP TABLE IF EXISTS tb_order;
DROP TABLE IF EXISTS tb_goods;

-- 订单表
CREATE TABLE tb_order(
	id int primary key auto_increment,
	payment double(10,2),
	payment_type TINYINT,
	status TINYINT
);

-- 商品表
CREATE TABLE tb_goods(
	id int primary key auto_increment,
	title varchar(100),
	price double(10,2)
);

-- 订单商品中间表
CREATE TABLE tb_order_goods(
	id int primary key auto_increment,
	order_id int,
	goods_id int,
	count int
);

-- 建完表后，添加外键
alter table tb_order_goods add CONSTRAINT fk_order_id FOREIGN key(order_id) REFERENCES tb_order(id);
alter table tb_order_goods add CONSTRAINT fk_goods_id FOREIGN key(goods_id) REFERENCES tb_goods(id);

查看表结构模型图：

2.5 表关系(一对一)

一对一
- 如:用户和用户详情
- 一对一关系多用于拆分表,将一个实体中经常使用的字段放一张表,不经常使用的字段放另一张表,用于提高查询性能
实现方式

在任意一方加入外键,关联另一方的主键,并且设置外键为唯一(UNIQUE)
案例

我们以用户表为例

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Dg7VveaR-1639045185118)(C:\Users\Administrator.DESKTOP-19OD09N\Desktop\006.png)]

而在真正实用的过场中发现, id、photo、nickname、age、gender 字段比较常用，此时就可以将这张表查分成两张表。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Iku2uAel-1639045185119)(C:\Users\Administrator.DESKTOP-19OD09N\Desktop\新建文件夹\007.png)]

建表语句如下：

create table tb_user_desc (
	id int primary key auto_increment,
	city varchar(20),
	edu varchar(10),
	income int,
	status char(2),
	des varchar(100)
);

create table tb_user (
	id int primary key auto_increment,
	photo varchar(100),
	nickname varchar(50),
	age int,
	gender char(1),
	desc_id int unique,
	-- 添加外键
	CONSTRAINT fk_user_desc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id)	
);

查看表结构模型图：

3.多表查询

多表查询主要分为 :

连接查询

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2vweGV54-1639045185120)(C:\Users\Administrator.DESKTOP-19OD09N\Desktop\新建文件夹\001.png)]
- 内连接查询 : 相当于查询AB交集数据
- 外连接查询 :
  - 左外连接查询:查询A表所有数据(包括交集部分数据)
  - 右外连接查询:查询B表所有数据(包括交集部分数据)
子查询

3.1 内连接查询

语法

--隐式内连接

Select 字段列表 from 表1,表2,... where 条件;

--显式内连接

Select 字段列表 from 表1 [Inner] Join 表2 On 条件;

-- 案例(隐式)
SELECT
	*
FROM
	emp,
	dept
WHERE
	emp.dep_id = dept.did;

3.2 外连接查询

-- 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;

-- 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;

左外连接：相当于查询A表所有数据和交集部分数据

右外连接：相当于查询B表所有数据和交集部分数据

案例

查询emp表所有数据和对应的部门信息(左外连接)

select * from emp left join dept on emp.dep_id = dept.did;

3.3 子查询

概念

== 查询中嵌套查询, 称嵌套查询为子查询.

例如:我们要查询出工资高于猪八戒的员工信息

第一步:我们可以先查出猪八戒的工资

select salary from emp where name = '猪八戒';  
-- 猪八戒的工资为3600

第二部:查询工资高于猪八戒的员工信息

select * from  emp where salary > 3600;

这样我们便可以得到大于猪八戒的员工信息.

运用子查询的话,我们可以将第二部里面的值,用第一部的sql语句进行替换
```
select from emp where salary >(select salary from emp where name ='猪八戒') ;
```

这就是查询语句中嵌套查询语句

子查询根据查询结果不同, 作用不同
- 子查询语句结果是单行单列，子查询语句作为条件值，使用 = != > < 等进行条件判断
- 子查询语句结果是多行单列，子查询语句作为条件值，使用 in 等关键字进行条件判断
- 子查询语句结果是多行多列，子查询语句作为虚拟表

4.事务

4.1概述

数据库的事务(Transaction)是一种机制、一个操作序列,包含了

== 一组数据库操作命令==

事务把所有的命令作为一个整体一起向系统提交或撤销操作请求,

即这一组数据库命令要么同时成功,要么同时失败
事务是一个不可分割的工作逻辑单元

4.2语法

开启事务
```
START TRANSACTION;
或者
BEGIN;
```
提交事务
```
commit;
```
回滚事务
```
rollback;
```

4.3注意事项

我们在执行事务时,需要在Java中进行操作,我们需要将开启事务,与提交事务语句用try catch包裹起来,语句错误信息,在发生错误后回滚事务.

4.4 事务的四大特征

原子性（Atomicity）: 事务是不可分割的最小操作单位，要么同时成功，要么同时失败
一致性（Consistency） :事务完成时，必须使所有的数据都保持一致状态
隔离性（Isolation） :多个事务之间，操作的可见性
持久性（Durability） :事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

说明：

mysql中事务是自动提交的。

也就是说我们不添加事务执行sql语句，语句执行完毕会自动的提交事务。

可以通过下面语句查询默认提交方式：
SELECT @@autocommit;
查询到的结果是1 则表示自动提交，结果是0表示手动提交。当然也可以通过下面语句修改提交方式
set @@autocommit = 0;


- 回滚事务

```sql
rollback;

4.3注意事项

我们在执行事务时,需要在Java中进行操作,我们需要将开启事务,与提交事务语句用try catch包裹起来,语句错误信息,在发生错误后回滚事务.

4.4 事务的四大特征

原子性（Atomicity）: 事务是不可分割的最小操作单位，要么同时成功，要么同时失败
一致性（Consistency） :事务完成时，必须使所有的数据都保持一致状态
隔离性（Isolation） :多个事务之间，操作的可见性
持久性（Durability） :事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

说明：

mysql中事务是自动提交的。

也就是说我们不添加事务执行sql语句，语句执行完毕会自动的提交事务。

可以通过下面语句查询默认提交方式：
SELECT @@autocommit;
查询到的结果是1 则表示自动提交，结果是0表示手动提交。当然也可以通过下面语句修改提交方式
set @@autocommit = 0;