MySQL数据库学习——（三）数据库设计
|
2,060
|
0
|
MySQL,其他,数据库

1392 字
|
13 分钟

数据库的设计关键在于建立各数据表之间的联系。它们之间的联系存在以下几种关系：

（1）一对一的关系；（2）一对多（多对一）的关系；（3）多对多的关系。

表关系：一对一

一对一的关系是数据库中最为常见的一种关系，示例如下：

建立它们之间的联系便是通过设置外键，设计思路是：

  • 在任意一个数据表中添加外键，另一个表的对应字段设置为主键
  • 且要将其该字段设置为唯一（UNIQUE）
# 创建数据表tb_user
CREATE TABLE tb_user(
id int primary key auto_increment,
photo varchar(32),
nickname varchar(32) default "nickname"
);

# 创建数据表tb_user_desc（对应每个用户的详细信息）
CREATE TABLE tb_user_desc (
id int,
city varchar(20)
);
# 在tb_user_desc中添加外键
ALTER TABLE tb_user_desc add CONSTRAINT ky_user_desc_user FOREIGN KEY (id) REFERENCES tb_user(id);

之后，我们使用navicat查看该一对一的结构关系

有模型结构可以看出，在这两个表之间存在外键连接，且为一对一的关系。

表关系：一对多（多对一）

建立一对多的关系，就是在存在多个对应关系的表中建立外键，而在另一个表的字段则为主键（且设为唯一（UNIQUE））。对于之前所创建的员工与部门表之间的关系就是一对多的关系。

# 创建部门表
CREATE TABLE dept(
id int primary key auto_increment,
name varchar(20) not null unique,
address varchar(20) not null
);

# 创建员工表
CREATE TABLE emp(
id int primary key auto_increment,
name varchar(20) not null unique,
age int not null,
dept_id int,
CONSTRAINT ky_emp_dept FOREIGN KEY (dept_id) REFERENCES dept(id)
);
# 建立外键连接
ALTER TABLE emp add CONSTRAINT ky_emp_dept FOREIGN KEY (dept_id) REFERENCES dept(id);

进入到navicat查看二者之间的关系，可以发现，同样在二者之间存在外键联系，且在员工表中并不唯一，而部门表中则为主键，所以属于多对一（一对多）的关系。

表关系：多对多

若想要建立两个表之间的多对多关系，则需要另外再创建一个中间表，将其中的键设置为外键，分别管理两个表的主键。

例如，对于订单和商品之间就是多对多的关系，一个订单可以包括多个商品，而一个商品有可能在多个订单中。

# 创建订单表
CREATE TABLE tb_order (
id int primary key auto_increment,
payment double not null,
payment_type varchar(20) not null,
status varchar(20) not null
);

# 创建商品表
CREATE TABLE tb_goods (
id int primary key auto_increment,
title varchar(32) not null,
price double not null
);

# 创建二者之间的关联表
CREATE TABLE tb_order_goods (
id int primary key auto_increment,
order_id int,
goods_id int,
CONSTRAINT ky_order_id FOREIGN KEY (order_id) REFERENCES tb_order(id),
CONSTRAINT ky_goods_id FOREIGN KEY (goods_id) REFERENCES tb_goods(id)
);

利用navicat查看三者之间的模型，我们可以发现tb_order_goods表通过设置外键，将二者之间的多对多关系联系起来。

多表查询

# 多表查询
# SELECT * FROM 表1 表2...;

多表查询

笛卡尔积：A,B两个集合所有元素的两两组合情况

多表查询

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

#内连接

# 内连接
## 隐式内连接
select * from emp, dept where emp.dep_id = dept.id;

## 查询emp的部分字段
select emp.name, dept.name from emp, dept where emp.dept_id = dept.id;

## 查询emp的部分字段（使用别名）
select d1.name, d2.name from emp d1, dept d2 where d1.dept_id = d2.id;

##显式内连接
select * from emp inner join dept on emp.dept_id = dept.id;

#外连接

# 外连接
## 左外连接
select * from emp left join dept on emp.dept_id = dept.id;

## 右外字段
select * from emp right join dept on emp.dept_id = dept.id;

#子查询

嵌套式地执行SQL语句，例如，若想要查询年龄大于等于张三的人员信息

# 一般情况
select age from emp where name = '张三';
select * from emp where age >= 20;

# 子查询
select * from emp where age >= (select age from emp where name = '张三');

# 单行单列：作为条件之，使用><=!=进行判断
select 字段列表 from 表 where 字段名 = (子查询);

# 多行多列：作为条件值，使用in等关键字进行判断
select 字段列表 from 表 where 字段名 in (子查询);

# 多行多列：作为虚拟表
select 字段列表 from 表 where 字段名 = (子查询);

事务操作

关系型数据库最重要的一个特征就是事务操作，那么什么是事务操作呢？可以通过一个案例对其有初步的了解。

例如，现有一个张三，有1500元钱，一个李四，有500元钱。现在，张三要给李四转账500元，则就要在数据库中，给张三-500元，而给李四+500元。

然而，如果如果数据库在进行操作的时候，中间因为某种原因，发生故障，张三减了500元，而李四还没有加上500元钱，则这500元钱不就不翼而飞了吗？这显然不能接受！

因此，在一个数据库的事务操作中，就可以避免这种情况的出现：

先创建一个示例数据库user_bank

# 创建数据库
create table user_bank (
id int primary key auto_increment,
username varchar(20) not null,
account double not null
);

# 插入两条数据
insert into user_bank(username, account) values('张三', 1500);
insert into user_bank(username, account) values('李四', 500);

# 执行普通操作
# 张三金额-500
update user_bank set account = account - 500 where username='张三';
# 创造人为错误
出错了。。。
# 李四金额+500
update user_bank set account = account + 500 where username='李四';

这时候，我们可以发现张三金额少了500，但是李四并没有加500，这500元钱因为出错而不易二分，这是绝对不能够允许的！那么，下面，我们利用事务来完成这一过程，看看在事务中若出现错误会发生什么情况。

首先，我们现将其重置为原来的状态：张三：1500，李四：500。

事务操作的基本用法如下：

# 开启事务
BEGIN;

# 数据库操作
# ...

# 一旦出错就回滚事务
ROLLBACK；

# 而能够完成事务就提交
COMMIT;

## 开启事务操作
BEGIN;
# 张三金额-500
update user_bank set account = account - 500 where username='张三';
# 创造人为错误
出错了。。。
# 一旦出现错误就需要回归事务
ROLLBACK;
# 李四金额+500
update user_bank set account = account + 500 where username='李四';

可以发现，当时事务操作出现错误的时候，可以通过ROLLBACK回滚到开始之前的状态。

## 开启事务操作
BEGIN;
# 张三金额-500
update user_bank set account = account - 500 where username='张三';

# 李四金额+500
update user_bank set account = account + 500 where username='李四';

# 而能够完成事务就提交
COMMIT;

这时候，就可以看到，成功完成转账的事务操作了！！

事务存在的特征：

  • 原子性（Atomicity）：事务是不可分割的最小单位
  • 一致性（Consistency）：事务完成后，所有数据必须保持统一的状态
  • 隔离性（Isolation）：多个事务之间，操作的可见性
  • 持久性（Durability）：事务一旦提交或回滚，对数据库的改变是永久的

数据库的自动提交特性

可通过设置@@autocommit的属性进行修改：

# 查看@@autocommit
select @@autocommit;

# 开启自动提交
set autocommit=1;

#关闭自动提交
set autocommit=0;
MySQL数据库数据库设计
暂无评论

发送评论 编辑评论 


				

			

						

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
							
													

					

				

			

			
			

				

					
				

			

				

											

							
							
						

																				
					
											
						

	

		
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
（╯‵□′）╯︵┴─┴
￣﹃￣
(/ω＼)
∠( ᐛ 」∠)＿
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ｀)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ(￣∇￣o)
ヾ(´･ ･｀｡)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò｡)
Σ(っ °Д °;)っ
( ,,´･ω･)ﾉ"(´っω･｀｡)
╮(╯▽╰)╭
o(*////▽////*)q
＞﹏＜
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
	

	

		
颜文字
Emoji
小恐龙
花!
	


									

			

			
			
		

	






上一篇
下一篇 

					
				

			

		

		
									
			
			
			
		
		
	


            

                

                
                
隐藏

                
变装