AI 助理
备案控制台
开发者社区 数据库 文章 正文

MySQL超详细学习教程，2023年硬核学习路线（三）

2023-05-23 194
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS PostgreSQL，高可用系列 2核4GB
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
简介： MySQL超详细学习教程，2023年硬核学习路线（三）

15. 外键约束


当我们添加了外键以后，就在数据库层面建立了两张表的关系。


目的：外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性


添加约束：


CREATE TABLE 表名(
  列名 数据类型,
  …
  [CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名)
);


建完表后添加外键约束：


ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);


删除约束：


ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;


16. 约束的案例练习


首先，我们使用以下的案例来练习约束：


-- 删除stu表
drop table if exists stu;
-- 创建stu表
CREATE TABLE stu (
id int primary key, -- 编号 主键
name varchar(10)  not null unique, -- 姓名 非空，唯一
age int not null, -- 年龄 非空
gender varchar(5) not null, -- 性别 非空
math double(5,2) not null, -- 数学成绩  非空
english double(5,2) default 0 -- 英语成绩 默认为0
);

2.1.png


验证主键约束，其特点是非空且唯一，先添加一条数据：


insert into stu(id,name,age,gender,math,english) values(1,'小张',23,'男',66,78);


添加第二条数据时，尝试 id 添加为空值：


insert into stu(id,name,age,gender,math,english) values (null,'小李',20,'女',98,87);

2.2.png



尝试 id 添加为重复的值：


insert into stu(id,name,age,gender,math,english) values (1,'小陈',55,'男',56,77);

2.3.png


我们已经验证了主键约束，当我们添加不合法的数据时，添加失败。


验证非空约束：


当我们添加以下的数据时：


insert into stu(id,name,age,gender,math,english) values (2,NULL,20,'女',76,65);

2.3.png


验证唯一约束：


当我们添加以下的数据时：


insert into stu(id,name,age,gender,math,english) values 
(5,'小张',20,'男',86,NULL);

2.4.png


验证默认约束：


当我们添加以下的数据时：


insert into stu(id,name,age,gender,math) values (6,'小赵',23,'男',99);

2.5.png


验证外键约束：


我们使用一下的案例来验证外键约束：


-- 删除表
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
-- 部门表
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)
);


此时，我们在员工表中添加了外键，相当于在数据库层面上建立了两张表的关系，此时如果员工表中有员工 a ，他属于 1 号部门，那么我们想要删除部门表中的 1 号部门就会删除失败，因为员工 a 是属于 1 号部门的，此时两张表建立了关系。


2.6.png


添加数据：


-- 添加 2 个部门
insert into dept(dep_name,addr) values
('研发部','西安'),('销售部', '成都');
-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO emp (NAME, age, dep_id) VALUES
('张三', 20, 1),
('李四', 20, 2);


此时想要是删除销售部门时，发现删除失败。


2.7.png


17. 数据库设计


17.1 概念


数据库设计是软件研发过程中很重要的一个环节，在学习数据库设计之前，要先了解软件研发的步骤。


一个成熟的软件，从想法的产生到成功上线，要经历需求调研及分析，设计，编码，测试和部署等过程，在开始阶段产品经理会根据客户的需求设计产品原型，而设计一般有架构师和开发工程师完成，这其中就包括数据库设计，还有软件结构设计，接口设计等，接着就是由开发工程师来编写代码，由测试工程师来测试，接着就可以部署上线。


2.8.png


数据库设计就是根据业务系统的具体需求，结合我们所选用的 DBMS，为这个业务系统构造出最优的数据存储模型。通俗的说就是建立数据库中的表结构以及表与表之间的关联关系的过程，分析有哪些表，哪些字段等。


数据库设计分为以下几个步骤：


  • 需求分析
  • 逻辑分析
  • 物理分析
  • 维护设计


17.2 表的关系


表和表之间的关系有一对一的关系，主要用于表的拆分，利于把常用数据和不常用数据分开存储，提高查询的效率。一对多或者多对一的关系，例如部门表和员工表的关系，多对多的关系，例如商品和订单的关系。


一对多的关系中，在表示多的一方建立外键，指向另一方的主键，示例：


-- 删除表
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)
);


查看两表结构模型：


2.9.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.10.png


一对一的关系中，在任意一方加入外键，关联另一方主键，并且设置外键为唯一(UNIQUE)，例如用户和用户详情。


示例：


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)
);


2.11.png


18. 多表查询


前面学习查询操作时，一直是从一张表中查询数据，而数据库的多表查询操作也是非常重要的一部分。多表查询就是一次性从多张表中查询出需要的数据。


在MySQL中多表查询分为连接查询和子查询，而连接查询又分为内连接查询和外连接查询，外连接查询的方式有左外连接查询和右外连接查询。下面对多表查询做一些基础的练习！


下面是演示多表查询使用的 sql:


DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
# 创建部门表
CREATE TABLE dept(
  did INT PRIMARY KEY AUTO_INCREMENT,
  dname VARCHAR(20)
);
# 创建员工表
CREATE TABLE emp (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(10),
  gender CHAR(1), -- 性别
  salary DOUBLE, -- 工资
  dep_id INT,
  FOREIGN KEY (dep_id) REFERENCES dept(did) -- 外键，关联部门表(部门表的主键)
);
-- 添加部门数据
INSERT INTO dept (dNAME) VALUES ('研发部'),('财务部'),('销售部');
-- 添加员工数据
INSERT INTO emp(NAME,gender,salary,dep_id) VALUES
('小张','男',3000,1),
('小李','女',3600,2),
('小王','男',9000,2),
('小美','女',5000,null);


执行 sql ，创建表：


2.12.png


18.1 内连接查询


内连接查询分为隐式内连接查询和显式内连接查询，表示查询 A 集合和 B 集合的交集。


2.13.png


语法：


-- 隐式内连接查询
SELECT 字段列表 FROM 表1,表2… WHERE 条件;
-- 显示内连接查询
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;


隐式内连接查询示例：


select * from emp,dept where dep_id=did;


结果：


2.14.png


显式内连接查询示例：


select * from emp inner join dept on dep_id=did;


结果：


2.15.png


18.2 外连接查询


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


2.16.png


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


2.17.png


语法：


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


左外连接查询示例：


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


结果：


2.18.png


右外连接查询示例：


select * from emp right outer join dept on dep_id=did;


结果：


2.19.png


18.3 子查询


子查询是指查询中嵌套有查询，以下的几种情况通常使用嵌套查询：


子查询语句结果是单行单列，子查询语句作为条件值，使用 = != > < 等进行条件判断，例如查询员工中工资大于小李的员工信息：


select * from emp where salary >(select salary from emp where name='小李');


结果：


2.20.png


子查询语句结果是多行单列，子查询语句作为条件值，使用 in 等关键字进行条件判断，例如查询研发部和财务部员工信息：


select * from emp where dep_id in (select did from dept where dname='研发部' or dname= '财务部');


结果：


2.21.png


子查询语句结果是多行多列，子查询语句作为虚拟表，例如查询员工中工资大于3000的员工的信息和部门信息：


select * from (select * from emp where salary>3000) t1, dept where t1.dep_id=dept.did;

2.22.png


19. 事务


19.1 概念


数据库的事务是一种机制，包含了一组数据库操作命令。事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败。


例如在进行转账操作时，张三转账给李四 500 元，首先会验证张三账户余额是否充足，如果是，则张三账户减少 500，李四账户增加 500 。如果在这过程中，由于某种原因出现错误，李四的账户没有增加 500 ，那么张三账户减少 500 的操作也不能完成。也就是要保证在这个过程中张三和李四账户余额总和要保持不变。


2.23.png


19.2 语法


开启事务：


START TRANSACTION;
或者
BEGIN;


回滚事务：


commit;


提交事务：


rollback;


小练习：张三转账给李四


DROP TABLE IF EXISTS account;
-- 创建账户表
CREATE TABLE account(
  id int PRIMARY KEY auto_increment,
  name varchar(10),
  money double(10,2)
);
-- 添加数据
INSERT INTO account(name,money) values('张三',1000),('李四',1000);


添加事务：


-- 开启事务
BEGIN;
-- 转账操作
-- 1. 查询李四账户金额是否大于500
-- 2. 张三账户 -500
UPDATE account set money = money - 500 where name = '张三';
3%0;--出现异常
-- 3. 李四账户 +500
UPDATE account set money = money + 500 where name = '李四';
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;


如果不添加事务，张三的账户就会减少 500 ，而李四的账户并不会增加 500，这样并不符合我们的实际生活。否则张三的 500 便不翼而飞了，哈哈哈！可见事务的重要性。


19.3 特征


MySQl 事务具有四大特征，分别是：


原子性: 事务是不可分割的最小操作单位，要么同时成功，要么同时失败

一致性 :事务完成时，必须使所有的数据都保持一致状态

隔离性: 多个事务之间，操作的可见性

持久性: 事务一旦提交或回滚，它对数据库中的数据的改变就是永久的


20. 总结


科技发展，社会进步。未来对数据的定义会不断地革新，生活的方方面面都会被列入数据的行列。从某种意义上来说，数据就是信息，只是数据不能直观地带来价值，而信息可以，但未来，这两者之间的距离会越来越缩小，直至划上等号。


未来是一个数字化的时代，数据是我们最为宝贵的资源，不论是哪个专业出身，未来或多或少都会卷入数据时代的浪潮之中。而那些前提性的工作基本都是依靠数据库来完成。

文章标签：
云数据库 RDS MySQL 版
SQL
关系型数据库
数据库管理
MySQL
数据库
存储
架构师
关键词：
云数据库 RDS MySQL 版教程
云数据库 RDS MySQL 版学习
云数据库 RDS MySQL 版学习教程
云数据库 RDS MySQL 版学习教程学习路线
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发，帮助IT从业者们通过“阿里云Web应用上云解决方案”，了解一个企业级Web应用上云的常见架构，了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品：云数据库RDS MySQL 版 阿里云关系型数据库RDS（Relational Database Service）是一种稳定可靠、可弹性伸缩的在线数据库服务，提供容灾、备份、恢复、迁移等方面的全套解决方案，彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
张侠_
目录
相关文章
阿铭学Java
|
14天前
|
SQL 关系型数据库 MySQL
Mysql基础学习day02-作业
本教程介绍了数据库表的创建与管理操作，包括创建员工表、插入测试数据、删除记录、更新数据以及多种查询操作，涵盖了SQL语句的基本使用方法，适合初学者学习数据库操作基础。
阿铭学Java
33 0
阿铭学Java
|
14天前
|
SQL 关系型数据库 MySQL
Mysql基础学习day03
本课程为MySQL基础学习第三天内容，主要讲解多表关系与多表查询。内容涵盖物理外键与逻辑外键的区别、一对多、一对一及多对多关系的实现方式，以及内连接、外连接、子查询等多表查询方法，并通过具体案例演示SQL语句的编写与应用。
阿铭学Java
33 0
IvanCodes
|
4天前
|
安全 关系型数据库 MySQL
CentOS 7 yum 安装 MySQL教程
在CentOS 7上安装MySQL 8，其实流程很清晰。首先通过官方Yum仓库来安装服务，然后启动并设为开机自启。最重要的环节是首次安全设置：需要先从日志里找到临时密码来登录，再修改成你自己的密码，并为远程连接创建用户和授权。最后，也别忘了在服务器防火墙上放行3306端口，这样远程才能连上。
IvanCodes
134 16
阿铭学Java
|
9天前
|
关系型数据库 MySQL 数据管理
Mysql基础学习day03-作业
本内容包含数据库建表语句及多表查询示例，涵盖内连接、外连接、子查询及聚合统计，适用于员工与部门数据管理场景。
阿铭学Java
21 1
阿铭学Java
|
19天前
|
SQL 关系型数据库 MySQL
Mysql基础学习day01
本课程为MySQL基础学习第一天内容，涵盖MySQL概述、安装、SQL简介及其分类（DDL、DML、DQL、DCL）、数据库操作（查询、创建、使用、删除）及表操作（创建、约束、数据类型）。适合初学者入门学习数据库基本概念和操作方法。
阿铭学Java
110 6
阿铭学Java
|
18天前
|
SQL 关系型数据库 MySQL
Mysql基础学习day01-作业
本教程包含三个数据库表的创建练习：学生表（student）要求具备主键、自增长、非空、默认值及唯一约束；课程表（course）定义主键、非空唯一字段及数值精度限制；员工表（employee）包含自增主键、非空字段、默认值、唯一电话号及日期时间类型字段。每个表的结构设计均附有详细SQL代码示例。
阿铭学Java
39 0
阿铭学Java
|
18天前
|
SQL 关系型数据库 MySQL
Mysql基础学习day02
本课程为MySQL基础学习第二天内容，涵盖数据定义语言（DDL）的表查询、修改与删除操作，以及数据操作语言（DML）的增删改查功能。通过具体SQL语句与实例演示，帮助学习者掌握MySQL表结构操作及数据管理技巧。
阿铭学Java
69 0
艾伦~耶格尔
|
24天前
|
安全 关系型数据库 MySQL
MySQL安全最佳实践：保护你的数据库
本文深入探讨了MySQL数据库的安全防护体系，涵盖认证安全、访问控制、网络安全、数据加密、审计监控、备份恢复、操作系统安全、应急响应等多个方面。通过具体配置示例，为企业提供了一套全面的安全实践方案，帮助强化数据库安全，防止数据泄露和未授权访问，保障企业数据资产安全。
艾伦~耶格尔
58 0
顾翔
|
9天前
|
缓存 关系型数据库 BI
使用MYSQL Report分析数据库性能（下）
使用MYSQL Report分析数据库性能
顾翔
40 3

热门文章

最新文章

  • 1
    从下载到运行：MySQL 详细安装配置完整教程
  • 2
    解决MySQL的sql_mode=only_full_group_by错误提示
  • 3
    MySQL数据库连接过多（Too many connections）错误处理策略
  • 4
    使用命令行cmd查询MySQL表结构信息技巧分享。
  • 5
    查寻MySQL或SQL Server的连接数，并配置超时时间和最大连接量
  • 6
    修复.net Framework4.x连接MYSQL时遇到utf8mb3字符集不支持错误方案。
  • 7
    安装与配置MySQL 8 on Ubuntu，包括权限授予、数据库备份及远程连接指南
  • 8
    深入理解MySQL索引类型及其应用场景分析。
  • 9
    MySQL入门指南：从安装到第一个查询
  • 10
    MySQL锁机制：并发控制与事务隔离
  • 1
    在 MySQL 中使用 Distinct
    1416
  • 2
    在 MySQL 中使用 IS NULL
    913
  • 3
    MySQL——增量备份和全量备份
    123
  • 4
    Mysql80 密码忘记了怎么办
    175
  • 5
    MySQL——如何查看MySQL登录日志
    861
  • 6
    MySQL——删除重复数据
    101
  • 7
    Go语言中进行MySQL预处理和SQL注入防护
    330
  • 8
    MySQL——查看当前数据库中所有库中表的行数
    78
  • 9
    MySQL——大量的deleted进程导致磁盘100%
    86
  • 10
    MySQL——查看数据库大小
    132

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 阿里云数据库产品家族及特性
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • MySQL 技术大全：开发、优化与运维实战
  • 阿里云数据库运维实战手册
  • 搭建电商项目架构连接MySQL

    • 推荐镜像

    更多
  • mysql
    • 下一篇
    2025云栖大会，阿里云百炼邀请您的参与和见证