ToB企服应用市场:ToB评测及商务社交产业平台

标题: MySQL超具体学习教程，2023年硬核学习路线 [打印本页]
作者: 王海鱼    时间: 2024-6-14 23:42
标题: MySQL超具体学习教程，2023年硬核学习路线
前言

这是一篇 MySQL 通关一篇过硬核经验学习路线，包括数据库干系知识，SQL语句的使用，数据库束缚，设计等。整理的初衷是为了记录自己的发展，同时帮助到需要学习和查看的朋友。
1. 数据库的干系概念

1.1 数据

数据是指数据库中存储的根本对象，是描述事物的符号记录。
1.2 数据库

数据库是指存放数据的堆栈，长期存放在计算机内的有组织可共享的数据集合。

数据库技能办理了数据的持久化存储题目，同时使用数据库管理系统办理了对大量数据操纵繁琐的题目。
1.3 数据库管理系统

数据库管理系统是指位于用户和操纵系统之间的一层数据管理软件，科学的组织和存储，高效的获取和维护。

我们所说的 MySQL 数据库就是指 MySQL 数据库管理系统，在大家一样平常的使用习惯中把他叫做 MySQL 数据库。在我们的电脑安装好数据库管理系统软件以后就可以创建数据库来管理数据，同时也可以对数据库中的数据进行增编削查的操纵。
1.4 数据库系统

数据库，数据库管理系统，应用程序和数据库管理员共同组成了数据库系统。
1.5 SQL

SQL的英文是 Structured Query Language，简称 SQL，是一种操纵关系型数据库的布局化查询语言，我们在操纵数据库时经常用到的操纵就是查询操纵。
SQL界说了操纵全部关系型数据库的同一尺度，可以使用 SQL 操纵全部的关系型数据库管理系统，在使用其他的数据库管理系统时，也同样可以使用 SQL 来操纵。
2. MySQL数据库

2.1 MySQL安装

MySQL有很多的安装方式，这里可以使用绿色版，制止了安装版的一些繁琐的操纵，直接在官网下载与自己电脑相对应的版本的压缩文件，然后将压缩文件解压到一个非中文的目录中。

这里选择下载的产品版本是 5.7.24 ，原因是各个库支持的最完善且相对于最新版本更加的稳定。
2.2 MySQL配置

2.2.1 添加情况变量


右键此电脑 / 属性 / 高级系统设置 / 情况变量 ，在系统变量中新建变量，定名为 MYSQL_HOME，变量值为刚才 MySQL 的存放路径。
双击系统变量中的 Path，值为 %MySQL_HOME%\bin 。添加情况变量的方式与前面学习配置 Java 情况变量大致雷同。
那么，我们为什么要先配置情况变量呢？
平时，我们在下令行窗口中输入一个可执行程序的下令时，Windows 会先在情况变量中的Path所指的路径中探求，假如找到就直接执行，没找到就在当前工作目录中探求，假如还没找到，就会报错。
我们添加情况变量的目的就是可以或许在恣意路径下运行配置了情况变量的程序，而不用总是修改工作目录，大大简化了操纵。
我们如何验证添加情况变量成功呢？
此时，我们只需要以管理员身份运行下令提示符工具，执行 mysql，假如提示 Can't connect to MySQL server on 'localhost'，则情况变量添加成功。
   注：此时必须以管理员身份运行下令提示符工具，否则会报错。
  2.2.2 新建配置文件

在MySQl的根目录中创建一个配置文件 my.ini ,其内容为：
  1. [mysql]
  2. default-character-set=utf8
  4. [mysqld]
  5. character-set-server=utf8
  6. default-storage-engine=INNODB
  7. sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
复制代码
重要目的是配置数据库的默认编码集为utf-8和默认存储引擎为INNODB。
2.2.3 初始化MySQL

在下令提示符窗口中运行mysqld --initialize-insecure
，假如没有出现报错，则证明data目录初始化成功。
  1. mysqld --initialize-insecure
复制代码
此时当我们再打开查看MySQL目录，已经有data目录生成。
2.2.4 注册MySQL服务

在下令提示符窗口中运行 mysqld -install
，此时你的电脑就成功注册了MySQL服务，此时你的电脑就可以称为MySQL服务器。
  1. mysqld -install
复制代码
2.2.5 启动MySQL服务

在下令提示符窗口中运行net start mysql
，此时，我们已经成功启动MySQL服务。
  1. net start mysql
复制代码
运行net stop mysql

即可停止MySQL服务。
  1. net stop mysql
复制代码
可以通过运行mysqladmin -u root password 1234
修改默认账户密码，这里的1234指默认管理员(即root账户)的密码。
  1. mysqladmin -u root password 1234
复制代码
2.3 MySQL登录和退出

在下令行中运行mysql -uroot -p,按照提示输入密码 ，即登录成功。
  1. mysql -uroot -p密码
复制代码
登录下令中的参数：
  1. mysql -u用户名 -p密码 -h要连接的mysql服务器的ip地址(默认127.0.0.1) -P端口号(默认3306)
复制代码
退出MySQL时：
  1. exit
  2. quit
复制代码
2.4 MySQL卸载

我们只需要简单的三步就可以完成MySQL的卸载：
第一步：运行net stop mysql


  1. net stop mysql
复制代码
第二步：运行mysqld -remove mysql

  1. mysqld -remove mysql
复制代码
第三步：删除MySQL目录及干系的情况变量。
2.5 MySQL数据模型

MySQl 是一个关系型数据库。关系型数据库是建立在关系模型基础上的数据库，简单来说，关系型数据库是由多张能相互连接的二维表组成的数据库。
与关系型数据库对应的是非关系型数据库，关系型数据库有很多的长处，例如，都是使用表布局，格式同等，易于维护。使用通用的 SQL 语言操纵，方便快捷，可用于复杂查询等。

我们通过客户端可以用数据库管理系统创建数据库，在数据库中创建表，在表中添加数据。创建的每一个数据库对应到磁盘上都是一个文件夹，而一个数据库下可以创建多张表，文件夹中的 .frm 就是表文件，.MYD是数据文件，通过这两个文件就可以查询到数据展示成二维表的结果。
3. SQL语句

3.1 SQL简介

SQL被称为布局化查询语言，可以用于对全部的关系型数据库进行操纵，即我们可以通过SQL语句对数据库、表、数据进行增编削查操纵。
SQL界说了操纵关系型数据库的同一尺度，但是，对于同一个需求，每一种数据库操纵的方式可能会存在一些不一样的地方。
3.2 通用语法


MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。
SQL 语句可以单行或多行誊写，以分号结尾。
注释：
3.3 SQL分类


DDL： 数据界说语言，用来界说数据库对象：数据库，表，列等
DML： 数据操纵语言，用来对数据库中表的数据进行增编削
DQL：数据查询语言，用来查询数据库中表的记录(数据)
DCL：数据控制语言，用来界说数据库的访问权限和安全级别，及创建用户
数据是数据库中非常重要的部分，以是在日后的操纵中我们最常进行的是对数据的一些操纵，即对数据进行怎编削查，以是最常用操纵的是 DML 和 DQL 。
总的来说DML用于对数据增编削，DQL用于对数据查询操纵，DDL用于操纵数据库，而DCL是用来控制权限。 SQL 的根本操纵一般是指对数据库，数据表，数据的增编削查。
4. DDL- 操纵数据库

首先要学习的是使用DDL来对数据库进行操纵，重要是对数据库的增编削查操纵。
4.1 查询

查询全部的数据库：
  1. show databases;
复制代码
例如：

4.2 创建数据库

创建新的数据库：
  1. create database 数据库名称;
复制代码
使用上面的方式创建新的数据库时，假如该数据库已经存在，则会出现错误，以是我们在创建新的数据库时一般会判定该数据库是否存在，假如已存在，则不会创建。
创建新的数据库(判定数据库是否已经存在)：
  1. create database if not exists 数据库名称;
复制代码
例如：

4.3 删除数据库

删除数据库：
  1. drop database 数据库名称;
复制代码
和前面创建新的数据库雷同，为了制止出现错误，我们一般会先判定该数据库是否存在，假如不存在，则不会删除。
删除数据库(判定数据库是否已经存在)：
  1. drop database if exists 数据库名称;
复制代码
例如：

4.4 使用数据库

现在我们已经成功创建了新的数据库，接下来，我们要在数据库中创建数据表，首先我们要明白是对哪一个数据库进行操纵，此时我们要先学会使用数据库，才能对数据库中的表进行操纵。
使用数据库：
  1. use 数据库名称;
复制代码
查询当前正在使用的数据库：
  1. select database();
复制代码
例如：

5. DDL- 操纵数据表

同样，对数据表的操纵无非就是增编削查，在学习干系操纵之前，我们先要熟悉一下MySQL的数据范例。
5.1 数据范例

MySQL支持多种数据范例，但是大致可以分为3中，分别是数值型，日期型和字符串型。下面是比较常用的几种数据范例：
数值：
数据范例解释tinyint小整数型，占1个字节int大整数型，占4个字节double浮点范例 日期：
数据范例解释date日期值，只包含年代日datetime混合日期和时间值，包含年代日时分秒 字符串：
数据范例解释char定长字符串varchar变长字符串 定长字符串和变长字符串的区别：字符串是我们在数据库中经常使用的数据范例，使用变长字符串，假如字符的长度没有达到指定的长度，那么实际的长度是多少就占用几个字符，这样的做法显然是使用时间换空间，而使用定长字符串虽然有时会出现浪费空间的情况，但是一般储存性能比较高。
5.2 查询表

查询当前数据库下全部的表：
  1. show tables;
复制代码
查询表布局：
  1. desc 表名称;
复制代码
例如：

5.3 创建表

创建一个新的表：
  1. create table 表名称(
  2.                 字段名1 数据类型,
  3.                 字段名2 数据类型,
  4.                 ...
  5.                 字段名n 数据类型  #这里是不需要加上,的
  6. );
复制代码
例如：

5.4 删除表

删除表：
  1. drop table 表名;
复制代码
删除表(判定表是否存在)：
  1. drop table if exists 表名;
复制代码
例如：

5.5 修改表

修改表名：
  1. alter table 表名 rename to 新的表名;
复制代码
增长一列：
  1. alter table 表名 add 列名 数据类型;
复制代码
修改数据范例：
  1. alter table 表名 modify 列名 新数据类型;
复制代码
修改列名和数据范例：
  1. alter table 表名 change 列名 新列名 新数据类型;
复制代码
删除列：
  1. alter table 表名 drop 列名;
复制代码
例如：

6. 实战案例详解

需求：设计包含如下信息的学生表，请注重数据范例、长度的合理性。
在完成这样一个案例前，首先要创建一个学生数据库，在数据库中创建一张新的表，创建表时注意语法格式，数据范例和长度的合理性。
以管理员身份运行下令提示符cmd，启动Mysql服务，登录MySQL：

创建学生信息数据库：
  1. create database if not exists student;
复制代码

使用student数据库：
  1. use student;
复制代码

创建数据表：
  1. create table stu(
  2.                 id int ,-- 编号
  3.                 name varchar(10),-- 姓名
  4.                 gender char(1),-- 性别
  5.                 birthday date,-- 生日
  6.                 score double(5,2) ,-- 分数
  7.                 addr varchar(50),-- 地址
  8.                 status tinyint-- 状态
  9. );
复制代码

现在，我们已经学会了写SQL来操纵数据库，但是我们在下令行中写SQL时，每每有体验感差，效率低等题目，现在开始我们就要学习在MySQL的图形化客户端Navicat中执行SQL语句。

Navicat 为数据库管理、开辟和维护提供了一款直观而强大的图形化界面，大大的提高了工作效率，建议在学习中也使用这款开辟工具。接下来，在Navicat中新建数据库，新建查询，我们就可以编写SQL并且执行SQL语句了。
7. DML- 增编削数据

7.1 添加数据

给指定列添加数据：
  1. insert into 表名(列名1,列名2...) values(值1,值2...);
复制代码
给全部列添加数据：
  1. insert into 表名 values(值1,值2...);
复制代码
批量添加数据：
  1. insert into 表名(列名1,列名2...) values(值1,值2...),(值1,值2...)，(值1,值2...)...;
复制代码
批量添加数据(省略字段名)：
  1. insert into 表名 values(值1,值2...),(值1,值2...)，(值1,值2...)...;
复制代码
在开辟过程中添加数据时是不建议省略字段名的，这样降低了代码的可读性，使效率降落。例如：
查询表中的全部数据的方法是：
  1. select * from 表名;
复制代码
反面会用到的。
需求：往下面的tb_user表中添加一条数据。

  1. insert into tb_user(id,name) values(2,'李四');
复制代码
添加成功：

7.2 修改数据

修改表的数据：
  1. update 表名 set 列名1=值1,列名2=值2...[where 条件];
复制代码
在修改数据时，也可以不使用where条件，此时的操纵是修改整列数据，这样的操纵是很伤害的。
需求：把下面tb_user表中的张三的密码改为abc23

  1. update tb_user set passwor d ='abc123' where name='张三';
复制代码
修改成功：

7.3 删除数据

删除表的数据：
  1. delete from 表名 [where 条件];
复制代码
在删除某条数据时，假如不使用where条件，将会导致删除整个表的数据。
需求：删除tb_user表中的李四记录。
  1. delete from tb_user where name='李四';
复制代码
操纵成功：

8. DQL- 数据的查询操纵

查询是数据操纵至关重要的一部分，比如说在全部商品中查找出价格在规定范围内的全部商品，要想把数据库中的数据在客户端中展示给用户，一般都进行了查询的操纵。
在实际开辟中，我们要根据差别的需求，并且思量查询的效率来决定怎样进行查询，学习查询前，可以先看看查询的完备语法：
  1. SELECT
  2.         字段列表
  3. FROM
  4.         表名列表
  5. WHERE
  6.         条件列表
  7. GROUP BY
  8.         分组字段
  9. HAVING
  10.         分组后条件
  11. ORDER BY
  12.         排序字段
  13. LIMIT
  14.         分页限定
复制代码
根据查询的完备语法中的关键字，我们分别来学习基础查询，条件查询，排序查询，分组查询和分页查询。
下面的训练中使用以下的案例学习单表查询：
  1. -- 删除stu表
  2. drop table if exists stu;
  3. -- 创建stu表
  4. CREATE TABLE stu (
  5. id int, -- 编号
  6. name varchar(10), -- 姓名
  7. age int, -- 年龄
  8. gender varchar(5), -- 性别
  9. math double(5,2), -- 数学成绩
  10. english double(5,2) -- 英语成绩
  12. );
  13. -- 添加数据
  14. INSERT INTO stu(id,name,age,gender,math,english)
  15. VALUES
  16. (1,'小张',23,'男',66,78),
  17. (2,'小李',20,'女',98,87),
  18. (3,'小陈',55,'男',56,77),
  19. (4,'小樊',20,'女',76,65),
  20. (5,'小马',20,'男',86,NULL),
  21. (6,'小赵',57,'男',99,99);
复制代码
在Navicat中选中SQL并执行：

8.1 基础查询

1. 基础查询语法

查询多个字段：
  1. select 字段列表 from 表名;
复制代码
查询全部字段：
  1. select * from 表名;
复制代码
去除重复记录：
  1. select distinct 字段列表 from 表名;
复制代码
起别名操纵：
  1. select 字段名 别名 from 表名;
复制代码
2. 基础查询训练

使用学生表进行基础查询训练：
查询多个字段的训练：
  1. select name,math from stu;
复制代码

起别名操纵训练：
  1. select name,english 英语成绩 from stu;
复制代码

8.2 条件查询

1. 条件查询语法

一般语法：
  1. select 字段列表 from 表名 where 条件列表;
复制代码
条件查询一般配合运行符进行，下面是常见的几个运算符：
运算符功能描述> < = ！大于 小于 等于 不等于between…and…在这个范围之内in(…)多选一is null / is not null是null / 不是nulland 或 &&并且or 或 ||或者 2. 条件查询训练

使用学生表进行条件查询训练：
查询年事大于20的学生信息：
  1. select * from stu where age>20;
复制代码
查询年事等于18岁 或者 年事等于20岁 或者 年事等于21岁的学生信息：
  1. select * from stu where age in(18,20,21);
复制代码
模糊查询使用like关键字，可以使用通配符进行占位:

查询姓名中含有张的学生信息：
  1. select * from stu where name like '%张%';
复制代码

8.3 排序查询

1. 排序查询语法

  1. select 字段列表 from 表名 order by 排序字段名1 [排序方式]...;
复制代码
  注：排序方式有两种：分别是升序ASC和降序DESC，默认情况下是升序ASC。
  2. 排序查询训练

使用学生表进行排序查询训练：
查询学生信息，按照数学成绩降序排列：
  1. select * from stu order by math DESC;
复制代码
8.4 聚合函数

1. 聚合函数语法

什么是聚合函数呢？在进行查询操纵时，每每需要对一整列进行运算，例如可以计算一整列成绩数据的均匀值，我们就要使用聚合函数。下面是常见的聚合函数：
函数名功能count(列名)统计数量（一般选用不为null的列）max(列名)最大值min(列名)最小值sum(列名)求和avg(列名)均匀值 一般语法：
  1. select 聚合函数 from 表名;
复制代码
  注：NULL值不参与聚合函数运算。
  2.聚合函数训练

使用学生表进行聚合函数的训练：
统计该表中一共有几个学生：
  1. select count(id) from stu;
复制代码

上面我们使用某一字段进行运算，这样做可能面临的题目是某一个值可能是NULL，以是我们一般使用 * 进行运算，因为一行中不可能全部的字段都是NULL。
  1. select count(*) from stu;
复制代码
查询数学成绩的均匀分:
  1. select avg(math) from stu;
复制代码

8.5 分组查询

1. 分组查询语法

  1. select 字段列表 from 表名 [where 分组前的条件限定] group by 分组字段名 [having 分组后的条件过滤]
复制代码
  注：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义。
  2. 分组查询训练

使用学生表进行分组查询训练：
查询男同砚和女同砚各自的数学均匀分，以及各自人数，要求：分数低于70分的不参与分组:
  1. select gender, avg(math),count(*) from stu where math > 70 group by gender;
复制代码

查询男同砚和女同砚各自的数学均匀分，以及各自人数，要求：分数低于70分的不参与分组，分组之后人数大于2个的:
  1. select gender, avg(math),count(*) from stu where math > 70 group by gender having count(*) > 2;
复制代码

注：where 和 having 执行机遇不一样：where 是分组之进步行限定，不满意where条件，则不参与分组，而having是分组之后对结果进行过滤。以是，where 不能对聚合函数进行判定，having 可以。
8.6 分页查询

1. 分页查询语法

在我们的印象中，网页在展示大量的数据时，每每不是把数据一下全部展示出来，而是用分页展示的形式，实在就是对数据进行分页查询的操纵，即每次只查询一页的数据展示到页面上。
  1. select 字段列表 from 表名 limit 查询起始索引,查询条目数;
复制代码
在 limit 关键字中，查询起始索引这个参数是从0开始的。
2. 分页查询训练

使用学生表进行分页查询训练：
从0开始查询，查询3条数据：
  1. select * from stu limit 0,3;
复制代码
起始索引 = (当前页码 - 1) * 每页显示的条数
在SQL尺度中，一共规定了6种差别的束缚，包括非空束缚，唯一束缚和检查束缚等，而在MySQL中是不支持检查束缚的，以是这篇文章先对其余5种束缚做一个详解和训练。
9. 束缚的概念

束缚是作用于表中列上的规则，用于限定加入表的数据。例如，作为主键的列一定是非空的唯一的，否则将无法对数据进行区分。束缚的存在包管了数据库中数据的正确性、有用性和完备性。以是束缚在数据库设计中是非常重要的。
10. 束缚的分类

前面说到SQL尺度把束缚分为了6大类，分别是非空束缚，唯一束缚，主键束缚，检查束缚，默认束缚和外键束缚，添加束缚时我们只需要在SQL中添加关键词，便可以限定表中的数据。
束缚范例功能非空束缚 NOT NULL包管列中全部的数据不能有null值唯一束缚 UNIQUE包管列中全部数据各不雷同主键束缚 PRIMARY KEY主键是一行数据的唯一标识，要求非空且唯一检查束缚 CHECK包管列中的值满意某一条件默认束缚 DEFAULT保存数据时，未指定值则接纳默认值外键束缚 FOREIGN KEY外键用来让两个表的数据之间建立链接，包管数据的同等性和完备性 11. 非空束缚

目的：包管列中全部的数据不能有null值
添加束缚：
  1. CREATE TABLE 表名(
  2.         列名 数据类型 NOT NULL,
  3.         …
  4. );
复制代码
建完表后添加非空束缚：
  1. ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;
复制代码
删除束缚：
  1. ALTER TABLE 表名 MODIFY 字段名 数据类型;
复制代码
12. 唯一束缚

目的：包管列中全部数据各不雷同
添加束缚：
  1. CREATE TABLE 表名(
  2.         列名 数据类型 UNIQUE [AUTO_INCREMENT],
  3.         -- AUTO_INCREMENT: 当不指定值时自动增长
  4.         …
  5. );
  7. CREATE TABLE 表名(
  8.         列名 数据类型,
  9.         …
  10.         [CONSTRAINT] [约束名称] UNIQUE(列名)
  11. );
复制代码
建完表后添加唯有束缚：
  1. ALTER TABLE 表名 MODIFY 字段名 数据类型 UNIQUE;
复制代码
删除束缚：
  1. ALTER TABLE 表名 DROP INDEX 字段名;
复制代码
13. 主键束缚

目的：主键是一行数据的唯一标识，要求非空且唯一
添加束缚：
  1. CREATE TABLE 表名(
  2.         列名 数据类型 PRIMARY KEY [AUTO_INCREMENT],
  3.         …
  4. );
  6. CREATE TABLE 表名(
  7.         列名 数据类型,
  8.         [CONSTRAINT] [约束名称] PRIMARY KEY(列名)
  9. );
复制代码
建完表后添加主键束缚：
  1. ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
复制代码
删除束缚：
  1. ALTER TABLE 表名 DROP PRIMARY KEY;
复制代码
14. 默认束缚

目的：保存数据时，未指定值则接纳默认值
添加束缚：
  1. CREATE TABLE 表名(
  2.         列名 数据类型 DEFAULT 默认值,
  3.         …
  4. );
复制代码
建完表后添加默认束缚：
  1. ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;
复制代码
删除束缚：
  1. ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;
复制代码
15. 外键束缚

当我们添加了外键以后，就在数据库层面建立了两张表的关系。
目的：外键用来让两个表的数据之间建立链接，包管数据的同等性和完备性
添加束缚：
  1. CREATE TABLE 表名(
  2.         列名 数据类型,
  3.         …
  4.         [CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名)
  5. );
复制代码
建完表后添加外键束缚：
  1. ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
复制代码
删除束缚：
  1. ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
复制代码
16. 束缚的案例训练

首先，我们使用以下的案例来训练束缚：
  1. -- 删除stu表
  2. drop table if exists stu;
  3. -- 创建stu表
  4. CREATE TABLE stu (
  5. id int primary key, -- 编号 主键
  6. name varchar(10)  not null unique, -- 姓名 非空，唯一
  7. age int not null, -- 年龄 非空
  8. gender varchar(5) not null, -- 性别 非空
  9. math double(5,2) not null, -- 数学成绩  非空
  10. english double(5,2) default 0 -- 英语成绩 默认为0
  11. );
复制代码

验证主键束缚，其特点是非空且唯一，先添加一条数据：
  1. insert into stu(id,name,age,gender,math,english) values(1,'小张',23,'男',66,78);
复制代码
添加第二条数据时，尝试 id 添加为空值：
  1. insert into stu(id,name,age,gender,math,english) values (null,'小李',20,'女',98,87);
复制代码

尝试 id 添加为重复的值：
  1. insert into stu(id,name,age,gender,math,english) values (1,'小陈',55,'男',56,77);
复制代码
我们已经验证了主键束缚，当我们添加不正当的数据时，添加失败。
验证非空束缚：
当我们添加以下的数据时：
  1. insert into stu(id,name,age,gender,math,english) values (2,NULL,20,'女',76,65);
复制代码

验证唯一束缚：
当我们添加以下的数据时：
  1. insert into stu(id,name,age,gender,math,english) values
  2. (5,'小张',20,'男',86,NULL);
复制代码
验证默认束缚：
当我们添加以下的数据时：
  1. insert into stu(id,name,age,gender,math) values (6,'小赵',23,'男',99);
复制代码

验证外键束缚：
我们使用一下的案例来验证外键束缚：
  1. -- 删除表
  2. DROP TABLE IF EXISTS emp;
  3. DROP TABLE IF EXISTS dept;
  4. -- 部门表
  5. CREATE TABLE dept(
  6. id int primary key auto_increment,
  7. dep_name varchar(20),
  8. addr varchar(20)
  9. );
  10. -- 员工表
  11. CREATE TABLE emp(
  12. id int primary key auto_increment,
  13. name varchar(20),
  14. age int,
  15. dep_id int,
  16. -- 添加外键 dep_id,关联 dept 表的id主键
  17. CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)
  18. );
复制代码
此时，我们在员工表中添加了外键，相称于在数据库层面上建立了两张表的关系，此时假如员工表中有员工 a ，他属于 1 号部分，那么我们想要删除部分表中的 1 号部分就会删除失败，因为员工 a 是属于 1 号部分的，此时两张表建立了关系。

添加数据：
  1. -- 添加 2 个部门
  2. insert into dept(dep_name,addr) values
  3. ('研发部','西安'),('销售部', '成都');
  4. -- 添加员工,dep_id 表示员工所在的部门
  5. INSERT INTO emp (NAME, age, dep_id) VALUES
  6. ('张三', 20, 1),
  7. ('李四', 20, 2);
复制代码
此时想要是删除销售部分时，发现删除失败。

17. 数据库设计

17.1 概念

数据库设计是软件研发过程中很重要的一个环节，在学习数据库设计之前，要先相识软件研发的步调。
一个成熟的软件，从想法的产生到成功上线，要履历需求调研及分析，设计，编码，测试和摆设等过程，在开始阶段产品司理会根据客户的需求设计产品原型，而设计一般有架构师和开辟工程师完成，这其中就包括数据库设计，还有软件布局设计，接口设计等，接着就是由开辟工程师来编写代码，由测试工程师来测试，接着就可以摆设上线。

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

17.2 表的关系

表和表之间的关系有一对一的关系，重要用于表的拆分，利于把常用数据和不常用数据分开存储，提高查询的效率。一对多或者多对一的关系，例如部分表和员工表的关系，多对多的关系，例如商品和订单的关系。
一对多的关系中，在表示多的一方建立外键，指向另一方的主键，示例：
  1. -- 删除表
  2. DROP TABLE IF EXISTS tb_emp;
  3. DROP TABLE IF EXISTS tb_dept;
  4. -- 部门表
  5. CREATE TABLE tb_dept(
  6. id int primary key auto_increment,
  7. dep_name varchar(20),
  8. addr varchar(20)
  9. );
  10. -- 员工表
  11. CREATE TABLE tb_emp(
  12. id int primary key auto_increment,
  13. name varchar(20),
  14. age int,
  15. dep_id int,
  16. -- 添加外键 dep_id,关联 dept 表的id主键
  17. CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES tb_dept(id)
  18. );
复制代码
查看两表布局模型：

多对多的关系中，建立第三张中心表，中心表至少包含两个外键，分别关联两方主键，例如商品表和订单表。示例：
  1. -- 删除表
  2. DROP TABLE IF EXISTS tb_order_goods;
  3. DROP TABLE IF EXISTS tb_order;
  4. DROP TABLE IF EXISTS tb_goods;
  6. -- 订单表
  7. CREATE TABLE tb_order(
  8.         id int primary key auto_increment,
  9.         payment double(10,2),
  10.         payment_type TINYINT,
  11.         status TINYINT
  12. );
  14. -- 商品表
  15. CREATE TABLE tb_goods(
  16.         id int primary key auto_increment,
  17.         title varchar(100),
  18.         price double(10,2)
  19. );
  21. -- 订单商品中间表
  22. CREATE TABLE tb_order_goods(
  23.         id int primary key auto_increment,
  24.         order_id int,
  25.         goods_id int,
  26.         count int
  27. );
  29. -- 建完表后，添加外键
  30. alter table tb_order_goods add CONSTRAINT fk_order_id FOREIGN key(order_id) REFERENCES
  31. tb_order(id);
  32. alter table tb_order_goods add CONSTRAINT fk_goods_id FOREIGN key(goods_id) REFERENCES
  33. tb_goods(id);
复制代码
查看两表的布局模型：

一对一的关系中，在恣意一方加入外键，关联另一方主键，并且设置外键为唯一(UNIQUE)，例如用户和用户详情。
示例：
  1. create table tb_user_desc (
  2.         id int primary key auto_increment,
  3.         city varchar(20),
  4.         edu varchar(10),
  5.         income int,
  6.         status char(2),
  7.         des varchar(100)
  8. );
  9. create table tb_user (
  10.         id int primary key auto_increment,
  11.         photo varchar(100),
  12.         nickname varchar(50),
  13.         age int,
  14.         gender char(1),
  15.         desc_id int unique,
  16. -- 添加外键
  17. CONSTRAINT fk_user_desc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id)
  18. );
复制代码

18. 多表查询

前面学习查询操纵时，一直是从一张表中查询数据，而数据库的多表查询操纵也是非常重要的一部分。多表查询就是一次性从多张表中查询出需要的数据。
在MySQL中多表查询分为连接查询和子查询，而连接查询又分为内连接查询和外连接查询，外连接查询的方式有左外连接查询和右外连接查询。下面对多表查询做一些基础的训练！
下面是演示多表查询使用的 sql:
  1. DROP TABLE IF EXISTS emp;
  2. DROP TABLE IF EXISTS dept;
  4. # 创建部门表
  5. CREATE TABLE dept(
  6.         did INT PRIMARY KEY AUTO_INCREMENT,
  7.         dname VARCHAR(20)
  8. );
  9. # 创建员工表
  10. CREATE TABLE emp (
  11.         id INT PRIMARY KEY AUTO_INCREMENT,
  12.         NAME VARCHAR(10),
  13.         gender CHAR(1), -- 性别
  14.         salary DOUBLE, -- 工资
  15.         dep_id INT,
  16.         FOREIGN KEY (dep_id) REFERENCES dept(did) -- 外键，关联部门表(部门表的主键)
  17. );
  18. -- 添加部门数据
  19. INSERT INTO dept (dNAME) VALUES ('研发部'),('财务部'),('销售部');
  20. -- 添加员工数据
  21. INSERT INTO emp(NAME,gender,salary,dep_id) VALUES
  22. ('小张','男',3000,1),
  23. ('小李','女',3600,2),
  24. ('小王','男',9000,2),
  25. ('小美','女',5000,null);
复制代码
执行 sql ，创建表：

18.1 内连接查询

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

语法：
  1. -- 隐式内连接查询
  2. SELECT 字段列表 FROM 表1,表2… WHERE 条件;
  4. -- 显示内连接查询
  5. SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;
复制代码
隐式内连接查询示例：
  1. select * from emp,dept where dep_id=did;
复制代码
结果：

显式内连接查询示例：
  1. select * from emp inner join dept on dep_id=did;
复制代码
结果：

18.2 外连接查询

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

右外连接查询：相称于查询B表全部数据和交集部分数据。

语法：
  1. -- 左外连接
  2. SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;
  4. -- 右外连接
  5. SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;
复制代码
左外连接查询示例：
  1. select * from emp left outer join dept on dep_id=did;
复制代码
结果：

右外连接查询示例：
  1. select * from emp right outer join dept on dep_id=did;
复制代码
结果：

18.3 子查询

子查询是指查询中嵌套有查询，以下的几种情况通常使用嵌套查询：
子查询语句结果是单行单列，子查询语句作为条件值，使用 = != > < 等进行条件判定，例如查询员工中工资大于小李的员工信息：
  1. select * from emp where salary >(select salary from emp where name='小李');
复制代码
结果：

子查询语句结果是多行单列，子查询语句作为条件值，使用 in 等关键字进行条件判定，例如查询研发部和财务部员工信息：
  2. select * from emp where dep_id in (select did from dept where dname='研发部' or dname= '财务部');
复制代码
结果：

子查询语句结果是多行多列，子查询语句作为捏造表，例如查询员工中工资大于3000的员工的信息和部分信息：
  1. select * from (select * from emp where salary>3000) t1, dept where t1.dep_id=dept.did;
复制代码

19. 事务

19.1 概念

数据库的事务是一种机制，包含了一组数据库操纵下令。事务把全部的下令作为一个团体一起向系统提交或撤销操纵请求，即这一组数据库下令要么同时成功，要么同时失败。
例如在进行转账操纵时，张三转账给李四 500 元，首先会验证张三账户余额是否充足，假如是，则张三账户淘汰 500，李四账户增长 500 。假如在这过程中，由于某种原因出现错误，李四的账户没有增长 500 ，那么张三账户淘汰 500 的操纵也不能完成。也就是要包管在这个过程中张三和李四账户余额总和要保持不变。

19.2 语法

开启事务：
  1. START TRANSACTION;
  2. 或者
  3. BEGIN;
复制代码
回滚事务：
  1. commit;
复制代码
提交事务：
  1. rollback;
复制代码
小训练：张三转账给李四
  1. DROP TABLE IF EXISTS account;
  2. -- 创建账户表
  3. CREATE TABLE account(
  4.         id int PRIMARY KEY auto_increment,
  5.         name varchar(10),
  6.         money double(10,2)
  7. );
  8. -- 添加数据
  9. INSERT INTO account(name,money) values('张三',1000),('李四',1000);
复制代码
添加事务：
  1. -- 开启事务
  2. BEGIN;
  3. -- 转账操作
  4. -- 1. 查询李四账户金额是否大于500
  5. -- 2. 张三账户 -500
  6. UPDATE account set money = money - 500 where name = '张三';
  7. 3%0;--出现异常
  8. -- 3. 李四账户 +500
  9. UPDATE account set money = money + 500 where name = '李四';
  10. -- 提交事务
  11. COMMIT;
  12. -- 回滚事务
  13. ROLLBACK;
复制代码
假如不添加事务，张三的账户就会淘汰 500 ，而李四的账户并不会增长 500，这样并不符合我们的实际生存。否则张三的 500 便不翼而飞了，哈哈哈！可见事务的重要性。
19.3 特性

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

20. 总结

科技发展，社会进步。将来对数据的界说会不断地革新，生存的方方面面都会被列入数据的行列。从某种意义上来说，数据就是信息，只是数据不能直观地带来价值，而信息可以，但将来，这两者之间的距离会越来越缩小，直至划上等号。
将来是一个数字化的时代，数据是我们最为名贵的资源，不论是哪个专业出身，将来或多或少都会卷入数据时代的海潮之中。而那些条件性的工作根本都是依赖数据库来完成。
祝你奇迹有成！下期见。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4