Mysql8.0学习笔记_mysql8 source

一、数据库的常用概念

1.1为什么使用数据库？

1、实现数据的持久化，将数据保存到可掉电式的设备中使用。 即将数据存在硬盘等硬件中，但存储到硬件资源中，数据需要介质，如xsl,doc,jpg等格式，像更复杂的数据可以存储到数据库中，再存储到硬件资源上。

2、数据库存储数据的量非常庞大，数据的结构可以非常复杂，且数据的调优手段多样，方便数据的存储和维护。

1.2数据库与数据库管理系统

1、DB：数据库（database） 即存储数据的仓库，本质上是一个文件系统。

2、DBMS:数据库管理系统 即管理控制和维护数据的一个大型软件，用户需要通过管理系统访问数据库内的数据

3、SQL: 结构化查询语言 即操作者与数据库进行通信的语言

1.3常见的数据库软件（DBMS）

1.3.1常见数据库介绍

1、Oracle: 是第一个商用的RDBMS（关系型数据库），他的公司也叫Oracle，相当于商用数据库的开山鼻祖。且他收购了mysql，所以Oracle拥有mysql的管理权，相当于应用最广的两款数据库都是由Oracle（甲骨文公司）管理。注意，Oracle数据并非开源的。

2、SQL Server： 是微软开发的大型商业数据库。可以很好与微软全家桶相符合。

3、DB2：是IBM公司所开发的，多用于银行系统中

4、PostgreSQL：是开源免费的一款软件，稳定性十分强大。是最符合SQL标准的数据库。由美国的一个大学开发

5、Sybase：已经淡出历史舞台

6、SQlite:嵌入式的小型数据库，对硬件要求不高。常用于移动端使用。他零配置，不用启动，关闭等等。

7、informix: IBM公司所开发的软件。

1.3.2 Mysql的具体介绍

Mysql是一个开放源码的关系型数据库，诞生于1995年。2008年被甲骨文公司收购。

MariaDB 是mysql的分支项目

Mysql在6.x之后分为社区版和商业版

Mysql是开源的，支持GPL协议。可以修改源码来定制Mysql

Mysql支持大型的数据库操作。存储上千万的数据没有问题

Mysql使用的是SQL语言

Mysql支持运行在多种系统中，支持较多的语言，c c++,python,java,PHP等等的语言。

1.4 RDBMS和非RDBMS之间的区别

引言：从排名中，基本可以看出RDBMS数据基本就是数据库中主流。前五名中有四名都是RDBMS数据库。

1.4.1 RDBMS关系型数据库

1、RDBMS是最古老也是最主流的数据库类型。关系型数据模型是将复杂的数据，以二元关系展示，即以表格行列的形式来存储和展示数据。

2、关系型数据库以行（row）和列（column）的形式存储数据。一系列完整有结构的行和列称为表（table），表的文件后缀名为（ibd） 而一系列的表则构成一个数据库（datebase）

3、 关系型数据库，数据库由一系列的表组成，而关系型，则是形容表与表之间存在一定关系，他们以关系模型建立联系。这就是关系型数据库。

4、关系型数据库优势： 数据复杂但查询简单。可以用sql语对一个表多个表的数据进行查询。

5、关系型数据库之所以使用多，是因为现实世界的很多东西都可以通过整理使用二维表格的形式来展示和存储。

1.4.2、非RDBMS 非关系型数据库

1、非关系型数据库可以看成是关系型数据库的阉割版本。舍弃了一部分的功能，基于键值对存储数据。不需要经过sql语句解析。对性能的提高十分明显。 目前大部分非关系数据库都是免费的

1.4.3 非关系型数据库的分类

• 键值型数据库 目前最流行的是Redis数据库。键值型数据库通过key作为唯一标识来寻找value.优点是查询数据库，但会消耗大量的计算资源。他最常使用于内存缓存中。
• 文档型数据库 目前使用最多的是MangoDB,CouchDB等。 此类数据库可以存储并获取文档，常用的xml,json等格式。文档是文档型数据库的基本单位。也可以理解为特殊的键值型数据库。只不过值不再是一个变量，而是一个或多个文档了。
• 搜索引擎数据库 应用在搜索引擎领域。 核心的原理是倒排索引。 典型产品如：Solr,Splunk等等。数据量较大时也会选用
• 列式数据库 一般关系型数据库所采用的都是行式存储。即一行一行的存储数据。在列式数据库中，他存储的方式是列式存储，即一列一列的存储数据。这种方式可以降低系统IO。适用于分布式的文件系统。典型的产品如hbase等。但功能相对有一定限制。
• 图形数据库 多用于网状关系，这种网状关系使用关系型数据的二维形式来展示的话，就十分复杂了，此时就可以使用图形数据库 。

1.4.4、NOSQL的演变

SQL主要是要来操作RDBMS的一种语言。

NOSQL，不同时期的诠释：

1970： NOSQL= We have no SQL

1980: NOSQL = Know SQL

2000: NOSQL = NO SQL!

2005: NOSQL = Not only SQL

2013: NOSQL = NO,SQL!

NOSQL是对SQL的补充。在实际开发中，有很多需求，其实并不需要完成的RDBMS来做。使用非RDBMS就足够了。这种情况下，使用成本更低，性能更快的NOSQL无非是更加明智的选择。

1.5关系型数据库的设计规则

1、数据库（DB）由数据表构成

2、数据库内可以拥有一个或多个数据表，每一个表的表名具有唯一性。

1.5.1、表、记录、字段的概念

表，数据库中的主要对象

记录，即表中的一行数据

字段，即表头属性，表的一列数据

ORM（对象关系映射）

数据库中一个表 <——> 类似于python中的一个类

数据库中表的一行 <——> 类似于python中类的一个函数（方法 实体）

数据库中表的一列 类似于Python中类的一个属性

E-R模型中的三个主要概念是实体集，属性，联系集。实体集就是表，行，属性就是列

1.5.2、表的关联关系

表与表之间的数据关系有四种：一对一关联，一对多关联，多对多关联和自我引用。

1、一对一关联（one-to-two） 开发中使用不多，因为一对一完全可以建成一张表

2、一对多关联（one-to-many） 一个属性内有多个子属性 如部门中有多个员工，部门表和员工表 学生表和学生成绩表，一个学生可以考多门成绩 ，省份表和城市表等等之间的一对多的关系

3、多对多必须有第三个表，称为连接表。连接表将多对多关系分为两个一对多的关系，

4、自我引用，自己表对应自己。

二、Mysql数据库的环境搭建

2.1、Mysql的卸载

服务卸载：

1. 进入mysql的安装目录，删除data文件
2. 使用管理员身份运行cmd，切换到mysql/bin目录下
3. 输入net stop mysql ，停止mysql的服务
4. 输入 mysqld - -remove mysql卸载mysql服务
5. 注意5.7版本服务需要手动删除，8.0版本控制面板卸载mysql后会自动删除服务

软件卸载：

从控制面板直接卸载mysql即可。可以查看环境变量，服务信息及控制台输入命令尝试。

2.2、Mysql的安装

1. Mysql官网：www.mysql.com
2. 参考课件中的Mysql环境搭建

2.3、MySQL的常用命令

1. 确认Mysql服务启动的启动下，可以使用mysql -u 用户名 -p 的方式来输入用户密码登陆到Mysql
2. 使用Mysql自带的客户端，直接输入密码即可进入到Mysql
3. 当服务器中有两个不同版本Mysql的软件时，就需要使用端口号来区别。输入端口号的命令如下： mysql -u root -P 端口号 -p 来输入密码。
4. 当需要访问别的服务器上的Mysql时 使用如下命令： mysql -u root -P 端口号 -h IP地址 -p 回车输入密码
5. 查看Mysql版本 mysql -V \ mysql - - version
6. 登录后查看Mysql版本： select version();
7. 查看所有数据库： show databases；
8. 创建数据库：create database 数据库名;
9. 使用数据库：use 数据库名
10. 查看表中的数据: sqlect * from 表名

2.4、Mysql5.7字符集的设置和更改

1、mysql5.7版本中，无法添加中文到数据表中，这是因为，5.7版本中的字符集是拉丁字符集，无非匹配中文，需要添加中文则需要修改字符集。

2、详情到课件中的MySQL环境搭建中查看

2.5、Mysql的图形使用界面

1、MySQL Workbench —— mysql官方的一个工具 使用量不大

2、SQLyog —— 第三方可视化工具，使用较多 接下来都使用这个sqlyog

三、基本的select语句

3.1 sql语句的背景

1、诞生于1974年，由IBM公司开发。从创建到现在语法结构变化都不大，影响较大的是SQL92和SQL99的标准。

2、SQL规范相当于我们的普通话标准，对于不同数据库软件来说，或多或少对SQL都有一定的修改和特有内容，不同数据的SQL语言类似于方言。

3.2 SQL语言的分类

1. DDL（数据定义语言）： create(创建) alter(修改) DROP(删除) rename(重命名) tryncate(清空表) 创建表对象等操作
2. DML (数据操作语言) ： insert(添加一个数据) delete(删除数据) update(修改数据) select(查询数据) 对数据的增删改查就是数据操作语言。（重点学习）
3. DCL（数据控制语言）： 相当于操作系统中设置，对数据的保存，权限等等就是数据控制语言。

3.3 SQL使用规范

1、关键字大写，名称，自定义变量等使用小写

2、每一句SQL语言末尾都尽量加上分号表示语句结束。

3、字符串、日期时间等变量需要加上一对单引号。

4、SQL语句的大小写在windows环境下是不区分大小写的，在Linux环境下则需要区分大小写。

5、大小写规范如下：

数据库名、表明、元素别名、字段名等元素都需要使用小写
 
SQL语句的关键字，函数名，绑定的变量则必须使用大写。

6、注释：

单行注释：单行注释使用#号
 
多行注释：多行使用/**/来注释 

3.4 SQL数据库的导入的导入

1、使用source语句加上文件路径导入（在命令行导入）

2、使用图形化界面导入。 工具——执行SQL脚本——打开数据库文件

3.5 注意

3.5.1 sql语句基本结构

基本结构：

 select 字段名\列名（多个） from  表名
 
select * *  from 表名    * *号表示表中的所有数据
 
查询结果所构成的集合就是集合集

3.5.2 列的别名（三种方式）

1. 使用as关键字给列名起别名
2. 使用空格隔开，空格后的为别名
3. 将别名使用一对“”双引号括起来

3.5.3 返回数据的去重

去除查询结果中的重复行，让其不返回。

使用distinct关键字，当有多个列查询时，使用distinct关键字要注意各列的行数。

3.5.4 null空值参与运算

1、null不等于0

2、null参与运算，不管加减乘除，所得结果都为null

3、ifnull函数，将空值替换为指定值 ：ifnull函数（字段，替换值）

3.5.5 着重号和查询常数

1、着重号

当表名等变量与属性相同时，就需要使用着重号，告诉SQL改名字是一个变量。 错误示范： SELECT * FROM ORDER; 加上着重号，着重是键盘上1左边的符号 SELECT * FROM order;

2、查询常数

在每行数据前添加常数（即添加一个固定的值）

当添加的值为字符串格式时，需要加上单引号，为数字时则不用

示例：

select '尚硅谷' ,123, first_name,last_name from employees;

3、显示表结构

显示表结构即查看表创建时的详细信息。

desc 函数 + 表名

describe + 表名

3.5.6 表中数据查找

过滤数据 按照指定过滤条件筛选表中数据并返回

SELECT * FROM employees;

使用where关键字来实现筛选，where关键字必须使用在from关键字后。

查询员工表中的属于90号部门的信息

SELECT * FROM employees WHERE department_id = 30;

查询名字为last_name为‘king’的员工信息

注意：此时表中king为大写，按照SQL标准来说，我们使用小写是查询不到的但由于mysql并不严谨的原因此时的数据依旧可以查找，但在别的数据库中是无法查找的。

故此，查找字符串时，尽量按照表中的原字符来查找

SELECT * FROM employees WHERE last_name = 'King';

四、SQL中的运算符

4.1算数运算符

注意：

在SQL中，加号只有加法的作用，没有连接的作用

在SQL中，一个数除以0等于null

在SQL中，只要进行除法运算，所得值都为浮点型。

取模等于取余数

4.2比较运算符

注意： 1、比较后获得三种结果，真为1，假为0，与null比较时为null 2、当一个数值和一个字符串进行比较时，字符串存在隐式转换，字符串数字转为原本数字的数值，其他的转换为0. 3、当两字符串进行比较时，则按照编ANSI的编码规则进行比较。 4、只要Null参与比较，那么比较的结果就为null，除了使用<=>之外。

4.3 比较关键字

注意：

Is null:为null \ is not null:不为null \ isnull（）可替换值，将其当作函数使用

Least(字段\值，字段\值) 获取括号内两个数据小的一个数据。

Greatest(字段\值，字段\值)获取括号内两个数据大的一个数据。

Between A数值 And B数值 从 A数值到B数值这个区间的数（包含A和B）加上Not则相反

In (set):取出在集合内的数：

SELECT last_name,department_id FROM employees WHERE department_id IN (10,20,30);

Not In (set)：取出不在集合内的数：

SELECT last_name,department_id FROM employees WHERE department_id NOT IN (10,20,30);

Like:模糊查找，比如到含A的值，记得%号为1到多个字符，下划线_为一个字符

练习：查询last_name中含a和e的数据
 
SELECT last_name FROM employees WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';

4.4 逻辑运算符

AND:且，满足左右两边条件才为真

OR:或，满足左右两边一边条件即为真

NOT:非，加上Not后，真为假，假为真

五、排序与分页

5.1 一级排序

1. 在查询数据后使用ORDER BY关键字： ORDER BY 排序资料 排序格式
2. ASC：表示升序，ORDER BY函数默认排序方式也为升序，直接添加在排序资料后即可
3. DESC： 表降序。
4. ORDER BY函数可以使用列的别名来作为排序资料

5.2 多级排序

多级排序意为，第一排序条件相等的情况下，考虑二级排序条件，以此类推，若上一级的排序条件不相等，则不考虑下一级。

使用方式：

在ORDER BY 第一排序资料，排序格式，第二排序资料，格式。

直接添加逗号使用即可。

5.3 分页

分页返回数据可以提高效率。

分页使用limit关键字。他有两个参数，第一个是偏移量，即从什么位置开始显示。第二个是每页显示的数量。

limit关键字一般写在一个查询语句的最后。

Limit的参数总结公式为：pagenum为要显示的页数，pagesize为每页显示的数量。

LIMIT(pagenum-1*20，pagesize)

六、多表查询

6.1 为什么使用多表查询

当需要查看不同表的信息时，虽然可以使用多个select语句来查询信息，但做不到将数据整合，且过程繁琐，冗余字段多，加载次数多，代码的维护也相对比较困难。

6.2 多表查询时笛卡尔积的错误

当查询多表时，如果直接FROM两个表明，那么SQL会在底层将两个或多个表的所有数据相匹配，所得的数据就是表内数据相乘的结果。3

6.3 使用WHERE实现的多表查询

在SQL92中，使用where关键字来实现多表查询，以对笛卡尔积的数据进行筛选。

具体的实现方式，是将数据以表中的字段为做为连接条件，以进行筛选，代码如下：

SELECT employee_id,last_name,employees.department_id,department_name FROM employees,departments WHERE employees.department_id = departments.department_id;

此时，我们可以通过给字段起别名的方式，来说明每一个字段是属于哪个表的。

从SQL优化的角度来说，每个字段前都应该尽量加上归属表名。

使用WHERE所实现的外连接

使用WHERE关键字来实现外连接时，使用（+）的方式

左外连接，在WHERE关键字的链接语句之后，在左表加上（+）。右外连接则将（+）加在右表

6.4 SQL多表查询的分类

6.4.1 SQL中多表查询的分类

• 等值字段连接查询：即两表的连接条件相等且字段相同。
• 不等值字段连接查询：即两表的连接条件不同，字段不同。
• 自连接： 即将一个表看作逻辑上的两个表来实现多表查询
• 非自连接：即多表连接查询
• 内连接：两表进行查询时，结果集中不包含不满足条件的数据即称为内连接
  • 外连接：返回的结果集中包含不满足条件的数据
  • 外连接可分为三类：左外连接，右外连接，满连接
  • 左外连接：返回的结果集中所包含的不满足条件的数据是属于左表的，左外时，左表为主表，右表为从表
  • 右外连接：返回的结果集中所包含的不满足条件的数据是属于右表的，右外时，右表为主表，左表为从表
  • 满外连接：返回的结果集中所包含的不满足条件的数据即有主表，又有从表的。

示意图如下：

6.5 使用JOIN...ON实现的多表查询

6.5.1 JOIN...ON所实现的内连接

JOIN...ON 语法： 写在FROM语句后，JOIN后加查询表明，on后加上查询的连接条件。

JOIN...on所实现的内连接很简单：实例代码如下：

SELECT <查询字段> FROM Table1 INNER JOIN Table2 ON 1.key = 2.key

6.5.2 JOIN所实现的外连接

左外连接：JOIN前加上LEFT关键字，实例代码如下：

SELECT <查询字段> FROM Table1 LEFT JOIN Table2 ON 1.KEY = 2.KEY

右外连接： JOIN 前加上RIGHT关键字。实例如下：

SELECT <查询字段> FROM Table1 RIGHT JOIN Table2 ON 1.KEY = 2.KEY

满外连接：JOIN前加上FULL关键字，实例如下（Mysql中不支持该写法）：

SELECT <查询字段> FROM Table1 FULL JOIN Table2 ON 1.KEY = 2.KEY

6.5.3 UNION 和UNION ALL 关键字

UNION关键字，即联合多聚select查询语句，将两句查询语句的结果集合并成一个，但UNION关键字要求字段和列数相等的情况下，才能使用。

UNION关键字有两个分支：

UNION：UNION关键字返回两句查询语句结果时，会自动完成去重。

UNION ALL：UNION ALL关键字则不会自动去重

两者差别如下图：

6.5.4 JOIN...ON的七种方式实现

• 左上图为左外连接 实现代码如下：

SELECT <查询字段> FROM Table1 LEFT JOIN Table2 ON 1.KEY = 2.KEY

• 右上图为右外连接，实现代码如下：

SELECT <查询字段> FROM Table1 RIGHT JOIN Table2 ON 1.KEY = 2.KEY

• 中图为内连接，实现代码如下：

SELECT <查询字段> FROM Table1 INNER JOIN Table2 ON 1.key = 2.key

• 左中图为左外连接筛选后得，实现代码如下：

SELECT <查询字段> FROM Table1 LEFT JOIN Table2 ON 1.key = 2.key WHERE 2.key IS NULL

• 右中图为右外连接筛选后得，实现代码如下：

SELECT <查询字段> FROM Table1 RIGHT JOIN Table2 ON 1.key = 2.key WHERE 1.key IS NULL

• 左下图为满外连接，多种方式实现，以下列举以左外连接所得加上右外连接筛选所得的左上图和右中图来实现：

SELECT <查询字段> FROM Table1 RIGHT JOIN Table2 ON 1.key = 2.key WHERE 2.key IS NULL

UNION ALL

SELECT <查询字段> FROM Table1 LEFT JOIN Table2 ON 1.KEY = 2.KEY；

• 右下图为左中图加上右中图，使用UNION ALL 连接：

SELECT <查询字段> FROM Table1 LEFT JOIN Table2 ON 1.key = 2.key WHERE 2.key IS NULL

UNION ALL

SELECT <查询字段> FROM Table1 RIGHT JOIN Table2 ON 1.key = 2.key WHERE 1.key IS NULL

6.6 SQL99中新特性和新的关键字使用

• JOIN的自然连接：可以理解为等值连接，他会自动查询两表中相同的字段作为连接条件进行等值连接。
• 实现方式：在JOIN关键字前加上NATURAL关键字，实例如下：

SELECT <查询字段> FROM Table1 NATURAL JOIN Table2;

USING连接：当两个表的字段名相等，即进行等值连接时，可以使用USING关键字替换ON

SELECT employee_id,last_name,department_name

FROM employees e JOIN departments d

USING (department_id);

七、单行函数（见课件查询。）

八、聚合函数

聚合函数作用于一组数据，并对一组数据返回一个值。

8.1 五大常用聚合函数

• AVG(字段) ：求字段内的平均值 AVG = SUM /COUNT
• SUM(字段)：求字段内的总和
• AVG（）和SUM（）都只针对数值类型使用。
• MAX() 求字段内最大值
• MIN（）求字段内最小值
• MAX与MIN可针对数值类型，字符类型，时间日期类型使用

COUNT（）

• 作用：求一个字段在结果集中的个数（不包含null值）
• 当统计一个表内有多少行数据时，可以有三种实现方式：count(*),count(1),count(非空字段)，哪种方式效率更高？
• 根据不同的存储引擎而定：
• 适用MyISAM存储引擎，三者效率相同
• 使用InnoDB存储引擎，则count(*) = count(1)> count(非空字段)

8.2 GROUP BY 的使用

• 作用：根据字段进行分组，NULL值单独为一组，其余相同值为一组
• GROUP BY 使用在FROM关键字后
• 在SELECT语句中，所查询的字段必须在GROUP BY 后声明
• GROUP BY 的字段位置可以更换，不会影响最后的结果。
• WITH ROLLUP关键字
• 声明在GROUP BY关键字后，在分组完成后，对所有分组再进行一次汇总。

8.3 Having 关键字的使用

HAVING关键字也是用于过滤条件，和where一样

但HAVING关键字必须和GROUP BY关键字配套使用

且使用了聚合函数来做判断条件的话，也必须使用HAVING关键字，使用where是直接报错的。

WHERE关键字和HAVING关键字是可以一起使用的。

8.4 WHERE和HAVING关键字的区别

1. 从适用范围来说，Having的适用范围更广。
2. 如果过滤条件中没有聚合函数，WHERE的执行效率是高于HAVING的
3. WHERE的执行效率为什么会比HAVING更高呢？
4. 因为WHERE是在遍历数据时，通过一行一行的来筛选，而HAVING必须和GROUP BY 一起使用，他需要先遍历所有数据将其分组成为满足GROUP BY分组条件的过滤后数据，再来遍历一次分组数据来完成筛选
5. WHERE为何无法使用聚合函数作为过滤条件？
6. 因为WHERE是在遍历过程中一行一行的筛选数据，而聚合函数，一般都需要一组相同数据类型的数据来传入计算出结果。而WHERE遍历的是行数据，因此无法使用聚合函数来作为过滤条件。

8.5 SELECT语句完整结构和执行原理

8.5.1 SELECT的完整结构

SELECT...(聚合函数) FROM.... (LEFT RIGHT)JOIN.... ON 连接条件 (LEFT RIGHT)JOIN.... ON 连接条件 WHERE ....不为聚合函数的条件 GROUP BY 以什么为分组 HAVING 聚合函数过滤条件 ORDER BY.....DESC/ASC LIMIT ....;

执行顺序

执行顺序

FROM -> ON ->（left/right）JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT(去重) -> ORDER BY -> LIMIT、

九、子查询

9.1 子查询的定义

9.1.1 子查询定义

子查询：可理解是嵌套的两个查询语句

将写在内部的查询语句称为子查询，写在外部的称为主查询

子查询的结果被主查询所使用

9.1.2 子查询分类

两个角度

1. 从返回结果上讲，可以分为单行子查询和多行子查询
2. 从子查询的执行次数上讲，可分为相关子查询和不相关子查询

9.2 单行查询

单行查询：即返回的数据是单行的情况下，称之为单行子查询

单行子查询的操作符： = > < ≥ ≤ 等等

实现的语法也很简单，只是两个查询语句嵌套。如下所示：

case 因素 when 条件 then 条件成立为 else 不成立为 end 结束 SELECT employee_id,last_name, CASE department_id WHEN(SELECT department_id FROM departments WHERE location_id = 1800) THEN 'Canada' ELSE 'USA' END "location" FROM employees;

注意：当单行子查询的查询结果为null时，主查询的结果也为null

9.3 多行查询

多行查询：即返回的数据是多行的。

多行查询操作符：

1. IN：等于子查询列表中的其中一个
2. ANY: 和子查询返回结果集中的任一值进行比较
3. ALL：和子查询返回结果集中的所有值进行比较
4. SOME:与ANY相同，只是别名

多行子查询的空值问题：子查询内如果有null值，主查询结果也为Null

9.4 相关性子查询（课件详细资料）

9.4.1 不相关性子查询

不相关性子查询的执行步骤一般只为两步：

1、先执行子查询，获得子查询的结果集

2、在由主查询来对结果集通过比较等操作获取要查询的数据。

十、创建和管理表

命令查课件，和代码练习

十一、数据处理之增删改

DDL 创建数据库

DML 对数据的增删改查

DCL 跟事物相关（COMMIT,ROLLBACK）权限等。

十二、数据类型详解

12.1 整数类型

TINYINT SMALLINT MEDIUMINT INT BIGINT

从左到右是按照取值方位从小到大的顺序排列的。

他们依次的取值范围是2的8次，16次，24次，32次，64次方

INT使用最多，一般设置整数类型的数值的时，不刻意设置宽度。

UNSIGNED：关键字，表示数值前无符号，即为正数

ZEROFILL: 给数值为满的位数自动填充0

12.2 浮点数和定点数类型

12.2.1 浮点数

FLOAT:占位4个字节，占位小，效率快，取值小且单精度

DOUBLE：占位8个字节，占位较大，效率适中，取值范围大且双精度

浮点类型的数值设置时，一般都需要加上精度和标度，第一个参数是小数点前几位加上小数点后几位的和，第二位是小数点后几位。

如下代码所示：

CREATE TABLE test_double1 ( f1 FLOAT, f2 FLOAT(5,2), f3 DOUBLE, f4 DOUBLE(5,2) );

5和2 表示小数点后最多可有两位，小数点前最多可以有5-2位。

当添加表中的数据超过小数位时，他会自动四舍五入到设置的小数位上。

当添加表中的数据超过整数位时，直接报错，数据也无法添加。

浮点类型的数都多多少少的存在精度误差问题，所有就有了定点数

12.2.2 定点数DECIMAL

定点数在底层是使用字符串保存的，所以他的精度是最高的。

定点数在使用时，一般都会设置精度和标度，与浮点数的规则相同。未设置默认为（10,0）

DECIMAL的占位字节等于精度+2，所以他的取值范围是由宽度来决定的。

当数值超过DECIMAL的设置宽度时，他的处理方式和浮点数一样，小数位自动四舍五入到设置位数上，整数位直接报错。

12.2.3 浮点数和定点数的使用场景

当你对精度要求极高时，那就一定使用定点数DECIMAL来作为小数的数据类型。

当你要求小数的取值范围大，效率高且对精度要求不高时，就可以使用浮点数double和FLoat

12.3 时间日期类型

5.1 YEAR：按照四位来表示时，最小值为1901,2155

5.2 DATE：表示年月日，即日期使用的规范格式是（‘2020-10-10’）

5.3 TIME: 表示时分秒，规范格式，使用字符串（‘20:35:00’）来表示

还可以在字符串前加上一个表示天数的数值，系统会自动加上天数来获得最后时间，格式如下：

'2 10:20:22' 表示两天后的十点二十分二十二秒

5.4 DATATIME: 表示年月日时分秒 规范格式是（‘2020-10-10 10:10:10’） 占用了8个字节

5.5 TIMESTAMP：也表示年月日时分秒 占用了4个字节，规范格式与DATATIME相同。他的取值范围只能是1970到2038年

且TIMESTAMP还可以进行时区的更换。他所存储的是距离1970年的毫秒值。

总结: 开发中的使用

使用datetime，他完整了记录了日期和时间。此外，在记录注册时间，日志文件，商品发布时，建议使用时间戳，便于运算。

12.4 文本类型字符串类型

12.4.1 char和varchar

char类型一般都会设置宽度，不设置宽度默认为1。但如果添加的数据不足宽度，那么char类型会自动添加到指定宽度，但在检索时，他又会自动去掉。

varchar类型必须设置宽度，宽度等于多少就可以存储多少个字符。

char 是固定长度的，他会浪费一些存储空间，但效率高，适用于存储字符不大，速度要求高。

varchar是可变长度的，在大部分的情况下，他比较节省存储空间，但效率会慢一些。使用对空间要求高的场景。

char和varchar一般都用来存储较短的字符。

12.4.2 Text类型（可以理解是存储文章的数据）

当文本相当大的时候，就考虑使用Text，且建议Text类型的数据单独为一个表。

至于char,varchar,和Text应该怎么考虑使用？

当文本较小时，建议使用char或varchar，当文本大时再使用text来存储数据

12.4.3 枚举类型（ENUM类型）

EMUM枚举类型：即从枚举类型中所列的数据中挑选一个添加到表中

EMUM类型的成员个数最多为65535个

EMUM中的成员必须为字符串

添加时忽略大小写，也可以使用枚举成员的位置顺序来添加数据，还可以使用NULL来添加。

12.4.4 SET类型

SET类型：即从列表中多个数据来挑选一或多个数据添加到表中。

12.5 二进制字符串类型

12.5.1 BINARY 和VARBINARY类型

BINARY 类似于char,都是固定长度，都会自动填充，不设置宽度也默认为一。

VARBINARY类似于varchar,两者都是可变长度，都是必须设置宽度，都一定程度上的节省了一点空间。

12.5.2 BLOB类型(图片,音频,视频)

TINYBLOB: 最小

BLOB: 适中，相当于64KB

MEDIUMBLOB: 相当于16MB

LONGBLOB: 相当于4GB

BLOB类型一般用来存储像图片，视频，音频等数据

需要注意的是，在实际工作中，往往不会在MySQL数据库中使用BLOB类型存储大对象数据，通常会将图

片、音频和视频文件存储到 服务器的磁盘上 ，并将图片、音频和视频的访问路径存储到MySQL中。

12.5.3 JSON类型

JSON类型将JavaScript对象中表示的一组数据的方式转换为字符串。然后通过传递字符串的形式来传递数据。需要时还能将字符串转换为各种编程语言所支持的数据格式。

Mysql中可以存储JSON类型的数据，存储时，使用字符串的格式。

调取JSON中的数据时，使用的是： js - > '$.key' = value as name

12.6 数据类型的选择

一般情况下：

整数：int

小数：DECIMAL(M,D)

日期时间：DATETIME

任何数值当他不为负数时，加上UNSIGNED关键字

字符串长度如果几乎相等的话，建议使用char

当字符串长度长短不一时，建议使用varchar，且注意varchar的长度不要超过5000，如果超过5000建议创建新表使用TEXT类型。

十三、数据完整性和约束的分类

13.1 约束的分类

功能分类：

1. 非空约束：NOT NULL 不能为空
2. 外键约束：FORKIGN KEY REFERENCES
3. 主键约束: PRIMARY KEY 非空且唯一
4. 唯一约束：UNIQUE 值不能重复
5. 默认值约束： 不添加时该字段数据为默认值 DEFAULT
6. 值域约束：CHECK 值域约束，字段数据的值必须在该范围内

作用字段分类：

根据约束数据列的限制，约束可分为：

单列约束：每个约束只约束一列

多列约束：每个约束可约束多列数据

根据约束的作用范围，约束可分为：

列级约束：只能作用在一个列上，跟在列的定义后面

表级约束：可以作用在多个列上，不与列一起，而是单独定义

13.2 非空约束（NOT NULL）

1. 使用NOT NULL关键字定义，
2. 添加了非空约束，该字段的值不能为NULL
3. 删除非空约束：ALTER TABLE 表名 MODIFY 字段名 数据类型（宽度）
4. 创建后添加非空约束： ALTER TABLE 表名 MODIFY 字段名 数据类型（宽度） NOT NULL

13.3 唯一性约束（UNIQUE）

1. 作用：用来限制某个字段/某列的值不能重复。（NULL可以重复）

2. 创建表时，在字段后加上UNIQUE关键字即可创建唯一性约束

3. 在表级约束时，使用UNIQUE(字段，字段)的方式可以对多列形成唯一性约束

4. 形成多列唯一性约束后，多列值的组合不可重复

5. 在ALTER时添加UNIQUE:

   ALTER TABLE 表名 MODIFY 字段名 数据类型（宽度） UNIQUE

6. 删除UNIQUE约束：

   ALTER TABLE 表名 MODIFY 字段名 数据类型（宽度）

13.4 主键约束（PRIMARY KEY）

1. 作用：非空且唯一，相等于NOT NULL和UNIQUE的集合，每个表只有一个PRIMARY KEY。

2. 注意：

   一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建。

   主键约束对应着表中的一列或者多列（复合主键）

   如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。

   MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用。

   当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据主键查询

   的，就根据主键查询，效率更高）。如果删除主键约束了，主键约束对应的索引就自动删除了。

   需要注意的一点是，不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的

   值，就有可能会破坏数据的完整性。

3. 建表时添加主键约束：

   列级：在字段数据类型后声明为PRIMARY KEY

   表级：PRIMARY KEY(字段)

4. 已有表时添加主键：

   ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表);

5. 复合主键，多个字段为主键，多个字段之间需要满足：

   字段不可为NULL

   多个字段的组合不可重复

6. 删除主键

   ALTER TABLE 表名称 DROP PRIMARY KEY;

13.4.2 自增列 AUTO_INCREMENT

1. 作用：实现字段的自增长，使用AUTO_INCREMENT来定义，声明在字段后即可。

2. 限制：

   AUTO_INCREMENT只能作用在主键或唯一性约束的字段上，且该字段为整数类型

   一个表最多只能有一个自增长列

3. 健表时添加AUTO_INCREMENT在主键后即可。

4. 建表后：

   alter table 表名称 modify 字段名 数据类型 auto_increment;

5. 删除自增长：

   alter table 表名称 modify 字段名 数据类型;

13.5 外键约束（FOREIGN KEY）

1. 作用：限制字段的引用完整性，即主表中没有数据的值无法在从表对应字段中添加。

2. 注意：

   从表的外键列，必须引用/参考主表的主键或唯一约束的列

   在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名

   创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表

   删表时，先删从表（或先删除外键约束），再删除主表

   从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。如果类型不一样，创建子表时，就会出现错误“ERROR 1005 (HY000): Can't createtable'database.tablename'(errno: 150)”。

3. 添加外键约束

   外键约束在从表中添加，且主表必须已经存在。

   外键添加只能使用表级约束，如下：

   CONSTRAINT 约束名 FOREIGN KEY (从表的约束字段) REFERENCES 主表名（主表字段）

   建表后：

   ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用

   字段) [on update xx][on delete xx];

4.具体详细的可以查询课件。

13.6 CHECK约束

1. 作用：规定字段只能在指定范围内，一般作用于数值
2. 限制：Mysql5.7中不支持CHECK约束。且只可在建表时添加
3. 实现：实现CHECK定义
   1. 建表时： 直接将CHECK关键字声明在字段后：如 id int(10) check(id≤ 100)
   2. 删除： ALTER TABLE 表名 MODIFY 字段 数据类型（宽度）

13.7 DEFAULT约束（默认值约束）

1. 作用：给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值。

2. 实现：

   1. 建表时，声明在字段后：

      • 字段名 数据类型 not null default 默认值*,

   2. 建表后：

   alter table 表名称 modify 字段名 数据类型 default 默认值;

   3.删除：

   alter table 表名称 modify 字段名 数据类型 ;

十四、视图

14.1 视图的基本情况

视图：即存储起来的SELECT语句。

视图的创建：

create view as select语句

查看视图本身：

DESC 视图名

查看视图创建信息：

SHOW CREATE VIEW 试图名称。

14.2 视图的数据更新

视图的数据更新可分为两种情况：

一般情况下：

MySQL支持使用INSERT、UPDATE和DELETE语句对视图中的数据进行插入、更新和删除操作。当视图中的数据发生变化时，数据表中的数据也会发生变化，反之亦然。

当特殊情况下，视图不可更新，因为：

视图和基表不是一对一的对应关系

在定义视图的时候指定了“ALGORITHM = TEMPTABLE”，视图将不支持INSERT和DELETE操作；

视图中不包含基表中所有被定义为非空又未指定默认值的列，视图将不支持INSERT操作；

在定义视图的SELECT语句中使用了 JOIN联合查询 ，视图将不支持INSERT和DELETE操作；

在定义视图的SELECT语句后的字段列表中使用了 数学表达式 或 子查询 ，视图将不支持INSERT，也

不支持UPDATE使用了数学表达式、子查询的字段值；

在定义视图的SELECT语句后的字段列表中使用 DISTINCT 、 聚合函数 、 GROUP BY 、 HAVING 、

UNION 等，视图将不支持INSERT、UPDATE、DELETE；

在定义视图的SELECT语句中包含了子查询，而子查询中引用了FROM后面的表，视图将不支持

INSERT、UPDATE、DELETE；

视图定义基于一个 不可更新视图 ；

常量视图。

14.3 删除视图

方式一：

CREATE OR REPLACE VIEW 视图名称AS SLEECT语句

方式二:

ALTER VIEW 视图名称 AS SELECT语句

十五、存储过程与存储函数

15.1 存储过程

1.存储过程：可以将其理解为一个函数，有形参，有返回值

2.结构：

DELIMITER $

CREATE PROCEDURE name_salary(IN empname VARCHAR(20))

BEGIN

SELECT salary FROM emps WHERE last_name = empname;

END $

DELIMITER ;

解析：DELIMITER是将结束符号修改为$，在函数创建完成修改为分号

PROCEDURE是存储过程的关键字
 
BEGIN END之间存储具体的方法体,END之后记得添加结束符号

3、存储过程的参数类型可以是IN、OUT和INOUT。根据这点分类如下：

1、没有参数（无参数无返回）
 
 2、仅仅带 IN 类型（有参数无返回）
 
 3、仅仅带 OUT 类型（无参数有返回） 
 
4、既带 IN 又带 OUT（有参数有返回） 
 
5、带 INOUT（有参数有返回）
 
注意：IN、OUT、INOUT 都可以在一个存储过程中带多个

4.调用

使用call关键字 call+过程名（参数）

设置参数 set 参数名 =值

查询参数 select 参数名

15.2 自定义存储函数

1. 存储函数和存储过程不同，存储函数必须有返回值，且存储函数只有IN类型的参数

2. 结构：

   CREATE FUNCTION 函数名(参数名 参数类型,...)

   RETURNS 返回值类型

   BEGIN

   return(方法体)

   END

3. 存储函数的调用

   存储函数使用select来调用

   存储函数可以写到select语句在中使用

   存储函数只有IN类型的参数

4. 存储函数和存储过程的对比

15.3 存储过程和存储函数的查看修改和删除

查看创建信息
 
  show create 存储过程/存储命令  
 
查看状态信息：
 
  show 过程/函数  status like  '名 ' 
 
修改：此处的修改只能修改过程或者函数的权限。
 
结构：
 
  ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名 [characteristic ...]
 
具体权限如下：
 
  ![](https://secure2.wostatic.cn/static/fruJj5WV6qjLS9hTMxax8y/image.png)
 
 
 
删除存储过程或存储函数：
 
  DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名
 
### 15.4 使用建议和优缺点对比
 
优点：
 
  1、存储过程可以一次编译多次使用。
 
  2、可以减少开发工作量。将代码 封装 成模块，
 
  3、存储过程的安全性强。
 
  4、可以减少网络传输量。
 
  5、良好的封装性。
 
缺点：
 
  1、可移植性差。
 
  2、调试困难。
 
  3、存储过程的版本管理很困难。
 
  4、它不适合高并发的场景。

十六、变量流程控制和游标

16.1 变量

变量：变量分为系统变量和自定义变量

系统变量由Mysql中所定义好的变量，系统变量又分为全局变量和会话变量

全局变量是在整个Mysql服务器上有效的，会话变量是作用在Mysql的会话上，会话之间不能跨会话使用。

全局变量使用global修饰，会话变量使用session

在语句中，系统变量使用@@global修饰 会话变量使用@@session修饰

系统变量的查看：

SHOW GLOBAL VARIABLES;  全局系统变量的查看
 
SHOW SESSION VARIABLES;  会话变量的查看
 
模糊查找系统变量：
 
SHOW GLOBAL VARIABLES LIKE '%标识符%';
 
SHOW SESSION VARIABLES LIKE '%标识符%';
 
查找指定的系统变量：
 
*SELECT @@变量名;*
 
修改系统变量的值：
 
方式1：修改Mysql的配置文件
 
方式2：通过set的方式去设置
 
  SET @@global.变量名=变量值;
 
  SET @@session.变量名=变量值;

16.2 用户自定义变量

用户最定义变量按照作用域可区分为会话用户变量和局部变量。

会话用户自定义变量以一个@来修饰，作用于当前会话，会话结束即失效

局部变量不使用符号修饰，且他只能作用在存储函数和存储过程,声明并使用BEGIN和END中。

1.会话变量的声明和赋值

方式一：自定义
 
  set 变量名 = 值
 
方式二：通过查询语句赋值，使用into连接
 
select 字段 into  变量名  from 表名

2.局部变量（作用于存储过程和函数）

定义：使用DECLARE来声明，必须声明在BEGIN中首行的位置。
 
结构：
 
  DECLARE 变量名 类型 [default 值]; # 如果没有DEFAULT子句，初始值为NULL
 
赋值方式：
 
  方式一：自定义
 
    set 变量名 = 值
 
  方式二：通过查询语句赋值，使用into连接
 
  select 字段 into  变量名  from 表名
 
 
 
### 16.3 异常处理之定义条件和处理方式
 
定义条件：定义好点程序可能在执行过程中遇到问题
 
程序处理：即遇到异常后的处理方式。

十七、触发器（TRIGGER）

触发器：即作用在表上的，对两个表或多个表进行数据关联的一个数据库对象

作用：他可以保证数据的完整性，比如商品表售卖了一件物品，那么库存表也要减少相应的库存，这个操作可以将其封装在触发器中，当售卖表中多了一个数据，相应的库存表则减少一个数据。

结构：