mysql基础篇

最新推荐文章于 2023-08-08 16:12:55 发布

Zy_blog

最新推荐文章于 2023-08-08 16:12:55 发布

阅读量96

点赞数

分类专栏： java 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_73815624/article/details/131884639

版权

java 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

DE1.概述

学习大纲（windows）请添加图片描述

（Linux）请添加图片描述

1.1数据库（DB）

即存储数据的“仓库”，其本质是一个文件系统。它保存了一系列有组织的数据。

1.2数据库管理系统（DBMS）

是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制。用户通过数据库管理系统访问数据库中表内的数据。

关系型数据库（RDBMS）：（二维表格）表与表之间有关系模型连接（用sql查询）
非关系型数据库(nosql)：键值型数据库，文档型，搜索引擎（量大的），列式数据库，图形……

1.3结构化查询语言（sql）

专门用来与数据库通信的语言。

SQL的分类：

数据定义语言-DDL
数据操纵语言-DML
数据控制语言-DCL
数据查询语言-DQL（使用多）

1.4关系型数据库设计规则

1.41ER模型：

实体集（record），属性、联系集

映射思想：行，列，表

四种：一对一关联、一对多关联、多对多关联、自我引用

1.42多对多（many-to-many）

要表示多对多关系，必须创建第三个表，该表通常称为联接表，它将多对多关系划分为两个一对多关系。将这两个表的主键都插入到第三个表中。

2.环境搭建

2.1MySQL的登录

启动：net start mysql

退出：quit

mysql -h localhost -P 3306 -u root -pabc123

2.2数据库演示

show databases;

“information_schema”是 MySQL 系统自带的数据库，主要保存 MySQL 数据库服务器的系统信息，比如数据库的名称、数据表的名称、字段名称、存取权限、数据文件所在的文件夹和系统使用的文件夹，等等
“performance_schema”是 MySQL 系统自带的数据库，可以用来监控 MySQL 的各类性能指标。
“sys”数据库是 MySQL 系统自带的数据库，主要作用是以一种更容易被理解的方式展示 MySQL 数据库服务器的各类性能指标，帮助系统管理员和开发人员监控 MySQL 的技术性能。
“mysql”数据库保存了 MySQL 数据库服务器运行时需要的系统信息，比如数据文件夹、当前使用的字符集、约束检查信息，等等

root用户密码忘记，重置的操作

1: 通过任务管理器或者服务管理，关掉mysqld(服务进程)
2: 通过命令行+特殊参数开启mysqld mysqld – defaults-file=“D:\ProgramFiles\mysql\MySQLServer5.7Data\my.ini” --skip-grant-tables
3: 此时，mysqld服务进程已经打开。并且不需要权限检查
4: mysql -uroot 无密码登陆服务器。另启动一个客户端进行 5: 修改权限表（1） use mysql; （2）update user set authentication_string=password(‘新密码’) where user=‘root’ and Host=‘localhost’; （3）flush privileges; 6: 通过任务管理器，关掉mysqld服务进程。 7: 再次通过服务管理，打开mysql服务。 8: 即可用修改后的新密码登陆。

修改配置文件中的默认字符集，解决编码问题。

3.基本的select语句使用

3.1sql语言规则和规范

3.11基本规则

SQL 可以写在一行或者多行。为了提高可读性，各子句分行写，

必要时使用缩进每条命令以 ; 或 \g 或 \G 结束

关键字不能被缩写也不能分行

关于标点符号

必须保证所有的()、单引号、双引号是成对结束的
必须使用英文状态下的半角输入方式字符串型和日期时间类型的数据
可以使用单引号（’ '）表示列的别名，尽量使用双引号（" "），而且不建议省略as

日期时间等用’ '表示

3.12大小写规范

windows大小写不敏感

Linux大小写敏感

推荐采用统一的书写规范：

数据库名、表名、表别名、字段名、字段别名等都小写
SQL 关键字、函数名、绑定变量等都大写

3.13注释

单行注释：#注释文字(MySQL特有的方式)

单行注释：-- 注释文字(–后面必须包含一个空格。)

多行注释：/* 注释文字 */（不嵌套）

3.14导入数据

方式一：命令行输入-source d:\mysqldb.sql

方式二：图形化界面工具

3.2基本的select语句

3.20最基本

SELECT 1;

SELECT 1+1,2*3

FROM DUAL(伪表);

3.21SELECT … FROM …

select 字段（列）from 表

‘*’表示所有字段。

返回结果集

3.22列的别名

在字段后加入空格+别名
字段后加 AS +别名
字段后+" 别名"

3.23去除重复行

select distinct ……from……

3.24空值参与运算

空值：null

null不等同于0，‘’，‘null’

空值参与运算结果也为空值。

解决方法：函数ifnull(字段名,0)

3.25着重号

我们需要保证表中的字段、表名等没有和保留字、数据库系统或常用方法冲突。

如果真的相同，请在 SQL语句中使用一对``（着重号）引起来。

3.26查询常数

SELECT 查询还可以对常数进行查询。对的，就是在 SELECT 查询结果中增加一列固定的常数列。这列的取值是我们指定的，而不是从数据表中动态取出的。

3.3显示表结构

显示表中详细信息：

DESCRIBE 表名

DESC 表名;

运行结果： 请添加图片描述
14161449293.png)]

其中，各个字段的含义分别解释如下：

Field：表示字段名称。

Type：表示字段类型，这里 barcode、goodsname 是文本型的，price 是整数类型的。

Null：表示该列是否可以存储NULL值。

Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分；UNI表示该列是UNIQUE索引的一部分；

MUL表示在列中某个给定值允许出现多次。

Default：表示该列是否有默认值，如果有，那么值是多少。

Extra：表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等。

3.4过滤数据

查询90号部门的员工信息(sql在windows忽略大小写，但在此处过滤时不符合这一点，但是mysql编写不严谨所以不好显现)

select *from employees

WHERE department_id=90;

WHERE过滤需要写在from的后面（紧挨着）

4.运算符

4.1算术运算符

±*/%

**+：**仅仅会做加法，数值隐式转换，非数值看成0

*/：

一个数乘以整数1和除以整数1后仍得原数；
一个数乘以浮点数1和除以浮点数1后变成浮点数，数值与原数相等；
一个数除以整数后，不管是否能除尽，结果都为一个浮点数
一个数除以另一个数，除不尽时，结果为一个浮点数，并保留到小数点后4位；
乘法和除法的优先级相同，进行先乘后除操作与先除后乘操作，得出的结果相同。
在数学运算中，0不能用作除数，在MySQL中，一个数除以0为NULL。

%，mod结果与被模数相同。

4.2比较运算符

比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回1，比较的结果为假则返回0，其他情况则返回NULL。

4.21运算符比较

如果等号两边的值、字符串或表达式都为字符串，则MySQL会按照字符串进行比较，其比较的是每个字符串中字符的ANSI编码是否相等。
如果等号两边的值都是整数，则MySQL会按照整数来比较两个值的大小。
如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较。
如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL。

<=>:

使用安全等于运算符时，两边的操作数的值都为NULL时，返回的结果为1而不是NULL，其他返回结果与等于运算符相同。(可以比null)

<>/!=:

不等于运算符不能判断NULL值

4.22关键字比较

请添加图片描述

与null有关的：isnull(字段名)，字段+IS NULL，字段+ IS NOT NULL;

比较大小的：LEAST(,)，GREATEST(,);参数可以是字段名

**查询一段范围的数据：**BETWEEN……AND……（包括边界）

查询：IN运算符用于判断给定的值是否是IN列表中的一个值

模糊查询： LIKE运算符主要用来匹配字符串，通常用于模糊匹配，如果满足条件则返回1，否则返回 0。如果给定的值或者匹配条件为NULL，则返回结果为NULL。

“%”：匹配0个或多个字符。
“_”：只能匹配一个字符（'-'匹配下划线需要仅仅加上转移字符）

正则表达式来查询：

REGEXP运算符用来匹配字符串，语法格式为： expr REGEXP 匹配条件。

如果expr满足匹配条件，返回 1；如果不满足，则返回0。

若expr或匹配条件任意一个为NULL，则结果为NULL。

REGEXP运算符在进行匹配时，常用的有下面几种通配符：

SQL语句示例如下：

（1）‘^’匹配以该字符后面的字符开头的字符串。

（2）‘$’匹配以该字符前面的字符结尾的字符串。

（3）‘.’匹配任何一个单字符。

（4）“[…]”匹配在方括号内的任何字符。例如，“[abc]”匹配“a”或“b”或“c”。为了命名字符的范围，使用一个‘-’。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。

（5））‘’匹配零个或多个在它前面的字符。例如，“x * ” 匹配任何数量的‘x’字符，“[0-9]* ”匹配任何数量的数字，而“”匹配任何数量的任何字符。

4.3逻辑运算符

请添加图片描述

注意： OR可以和AND一起使用，但是在使用时要注意两者的优先级，由于AND的优先级高于OR，因此先对AND两边的操作数进行操作，再与OR中的操作数结合。

逻辑异或运算符 逻辑异或（XOR）运算符（排斥或）是当给定的值中任意一个值为NULL时，则返回NULL；

4.4位运算符

（使用频率低，速率高）
请添加图片描述

&：二进制下上下为1

|: 二进制下上下有1则为1

^: 二进制下上下不同则为1（异或）

~：二进制下0变1，1变0；

<<: 左移(数值*2)

>>: 右移(数值/2)

超过范围就为0了。

5.排序与分页

5.1排序数据

5.11排序规则

使用 ORDER BY 子句排序

ASC（ascend）: 升序
DESC（descend）:降序

ORDER BY 子句在SELECT语句的结尾。

！！！！问题： order by不生效的原因

是字段类型为varchar(255),在字段后+0转换数据类型。

列的别名可以在order by使用，where不能使用别名（因为sql执行代码从from，where开始执行，然后才是select和order by）

5.12二级排序（多级排序）

SELECT last_name, department_id, salary

FROM employees

ORDER BY department_id, salary DESC;

先按部门id升序排列，i相同时按工资降序排列。

5.2分页

5.21背景

背景1：查询返回的记录太多了，查看起来很不方便，怎么样能够实现分页查询呢？

背景2：表里有 4 条数据，我们只想要显示第 2、3 条数据怎么办呢？

5.22实现规则

分页原理

所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件。

格式：

LIMIT 位置偏移量, 行数（从0开始可以省略偏移量）

分页显式公式：（当前页数-1）*每页条数，每页条数

SELECT * FROM table

LIMIT(PageNo - 1)*PageSize,PageSize;

注意：LIMIT 声明子句必须放在整个SELECT语句的最后！

5.23mysql8.0的新特性

limit……（条数）offset……（偏移量）

limit 1表示第一条

5.24分页的好处

约束返回结果的数量可以减少数据表的网络传输量 ，也可以 提升查询效率 。如果我们知道返回结果只有 1 条，就可以使用 LIMIT 1 ，告诉 SELECT 语句只需要返回一条记录即可。这样的好处就是 SELECT 不需要扫描完整的表，只需要检索到一条符合条件的记录即可返回。

6.多表查询

6.1 案例说明

6.11背景

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。

前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。

不把多个表合一的原因：数据冗余，io交互多效率差，表被锁定别人没法查

6.12笛卡尔积

分析错误情况：

SELECT COUNT(employee_id) ``FROM employees;

#输出107行

SELECT COUNT(department_id)FROM departments;

#输出27行

SELECT 107*27 FROM dual

输出27.000

我们把上述多表查询中出现的问题称为：笛卡尔积的错误。

可能原因：

缺少多表的连接条件，
连接条件（或关联条件）无效，
所有表中的所有行互相连接。

为了避免笛卡尔积，可以在 WHERE 加入有效的连接条件。

优化角度：建议多表查询时，每个字段前加入所在的表。

6.13表起别名

格式：在from后面加空格加别名，

在where，select中使用表的别名
起了别名，在select,where 中只能用别名。

6.2分类讲解

6.21等值连接与非等值连接

非等值：

select last_name,salary,grade_level

from job_grades t,employees e

where e.salary between t.lowest_sal and t.highest_sal

order by salary+0 ASC;

6.22自连接与非自连接

自连接：

当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。

然后两个表再进行内连接，外连接等查询。

SELECT CONCAT(worker.last_name ,' works for ' , manager.last_name)

FROM employees worker, employees manager

WHERE worker.manager_id = manager.employee_id ;

格式的函数：CONCAT(worker.last_name ,’ works for ’ , manager.last_name)

6.23内连接与外连接

内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行

外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件

关键字：所有的

如果是左外连接，则连接条件中左边的表也称为主表，右边的表称为从表。
如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为从表。
左外+右外 = 满外连接

6.3sql语法实现

6.31SQL92实现外连接：

使用+ (oracal可以用，mysql不支持92语法)

#左外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id = departments.department_id(+);
#右外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id(+) = departments.department_id;

6.32SQL99语法实现内连接：

使用（inner）JOIN（加表）……ON（加条件）—语法复杂，可读性强

6.33SQL99语法实现左/右外连接：

使用LEFT/RIGHT (outer) JOIN（加表）……ON（加条件）

6.34SQL99是支持满外连接：

使用FULL JOIN 或 FULL OUTER JOIN来实现。

需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替

6.4 union使用

合并查询结果 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

语法格式：

SELECT column,... FROM table
UNION [ALL] 
SELECT column,... FROM table2

union :会执行去重

union all ：不会去重

注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

6.57种sql join实现

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LBfxK0nK-1690118334546)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230715112941842.png)]

6.6sql99新特性（了解）

6.61自然连接

SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。

我们可以把自然连接理解为 SQL92 中的等值连接。

它会帮你自动查询两张连接表中 所有相同的字段 ，然后进行等值连接。====不够灵活

6.62USING使用

当我们进行连接的时候，SQL99还支持使用 USING 指定数据表里的 同名字段 进行等值连接。但是只能配合JOIN一起使用

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

6.7附录：常用的 SQL 标准有哪些

SQL 有两个主要的标准，分别是 SQL92 和 SQL99 。92 和 99 代表了标准提出的时间，SQL92 就是 92 年提出的标准规范。当然除了 SQL92 和 SQL99 以外，还存在 SQL-86、SQL-89、SQL:2003、SQL:2008、 SQL:2011 和 SQL:2016 等其他的标准。

这么多标准，到底该学习哪个呢？实际上最重要的 SQL 标准就是 SQL92 和 SQL99。一般来说 SQL92 的形式更简单，但是写的 SQL 语句会比较长，可读性较差。而 SQL99 相比于 SQL92 来说，语法更加复杂，但可读性更强。我们从这两个标准发布的页数也能看出，SQL92 的标准有 500 页，而 SQL99 标准超过了 1000 页。实际上从 SQL99 之后，很少有人能掌握所有内容，因为确实太多了。就好比我们使用 Windows、Linux 和 Office 的时候，很少有人能掌握全部内容一样。我们只需要掌握一些核心的功能，满足日常工作的需求即可。

SQL92 和 SQL99 是经典的 SQL 标准，也分别叫做 SQL-2 和 SQL-3 标准。也正是在这两个标准发布之后，SQL 影响力越来越大，甚至超越了数据库领域。现如今 SQL 已经不仅仅是数据库领域的主流语言，还是信息领域中信息处理的主流语言。在图形检索、图像检索以及语音检索中都能看到 SQL 语言的使用。

6.8小结

表连接的约束条件可以有三种方式：WHERE, ON, USING

WHERE：适用于所有关联查询

ON ：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写，但分开写可读性更好。

USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等

我们要控制连接表的数量。多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让 SQL 查询性能下降得很严重，因此不要连接不必要的表。在许多 DBMS 中，也都会有最大连接表的限制。

【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。说明：即使双表 join 也要注意表索引、SQL 性能。来源：阿里巴巴《Java开发手册》

7.单行函数

7.1函数理解

7.11背景

从函数定义的角度出发，我们可以将函数分成 内置函数 和 自定义函数 。

章讲解的是 SQL 的内置函数。

DBMS 之间的差异性很大，远大于同一个语言不同版本之间的差异。

7.12分类

MySQL提供的内置函数

实现的功能角度可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MySQL信息函数、聚合函数等。
操作数据与输出：单行函数、聚合函数（或分组函数或多行函数 😊）。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RzpgPKh5-1690118334547)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230715145318704.png)]

7.2数值函数

7.21基本函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ol2dshH2-1690118334547)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230715145658525.png)]

7.22角度与弧度互换

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LDKjZT5T-1690118334547)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230715151200277.png)]

7.23三角函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fsKcO59l-1690118334547)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230715151105923.png)]

7.24指数对数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qQalaOpC-1690118334547)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230715152839160.png)]

7.25进制转换

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4Gtjfcsy-1690118334548)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230715153157805.png)]

7.3字符串函数

concat (格式)as（字段名）

7.4日期时间函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Qdt8Y389-1690118334548)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230715171055601.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jUXbyU7y-1690118334548)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230715171015107.png)]

7.5流程控制函数

流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。 MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OqaIKCy0-1690118334548)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230715164747764.png)]

expr用字段名替换

7.6加密解密函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-P9HWH8cV-1690118334548)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230715170108876.png)]

7.7mysql信息函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BDuvDcKo-1690118334549)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230715170530086.png)]

7.8其他函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6UHMaa3t-1690118334549)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230715170659152.png)]

8.聚合函数

8.1聚合函数：

sum(),ave(),count(),max(),min();

注意count的函数讲究多，

问题：用count(*)，count(1)，count(列名)谁好呢? *

其实，对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。

Innodb引擎的表用count(*),count(1)直接读行数，复杂度是O(n)，因为innodb真的要去数一遍。但好于具体的count(列名)。

问题：能不能使用count(列名)替换count(*)? *

不要使用 count(列名)来替代 count() ， count(*) 是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。 *

说明：count()会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。

8.2group by语法：

8.21基本使用：

可以使用GROUP BY子句将表中的数据分成若干组

使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。（在分组的字段后面）

注意：当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥 的。

8.22多列分组

用group by分组依据多个字段分组，其中用逗号分隔

8.3having

8.31基本使用

效果是过滤分组，**显示满足条件的分组

1.行已经被分组。
2.使用了聚合函数。
3.HAVING 不能单独使用，必须要跟 GROUP BY 一起使用

having后面加条件，注意不能在where子句中使用聚合函数

8.32与where比较

WHERE 先筛选数据再关联，执行效率高 不能使用分组中的计算函数进行筛选

HAVING 可以使用分组中的计算函数在最后的结果集中进行筛选，执行效率较低

可以结合使用

8.4SELECT的执行过程

关键字顺序：SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT…

执行顺序：FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

每个步骤都会产生一个 虚拟表 ，然后将这个虚拟表传入下一个步骤中作为输入

多张表联查，还会经历：

首先先通过 CROSS JOIN到虚拟表 vt==》通过 ON 进行筛选到虚拟表 vt1-2==》添加外部行（看是什么连接）》分组过滤》select查询和去重==》排序==》分页

9.子查询

9.1引入问题

找出工资比abel多的人？

方法1.两个查询分开查，先查abel的工资，在查比它大的

方法2.自连接：查询员工2的工资及姓名，连接两个员工表，关联条件是员工1 = ‘abel’ AND员工2 的工资比员工1大

方法3.子查询

9.2子查询的基本使用

子查询（内查询）在主查询之前一次执行完成。

子查询的结果被主查询（外查询）使用。

注意：

子查询要包含在括号内
将子查询放在比较条件的右侧
单行操作符对应单行子查询，多行操作符对应多行子查询

9.3子查询的分类

按照返回数据的条数：单行子查询、多行子查询

按照查询执行的次数：不相关子查询、相关子查询

9.4单行子查询

9.41HAVING中的子查询

先执行子查询
再向主查询中的HAVING子句返回结果

9.42CASE语句中的子查询

在CASE表达式中使用单列子查询：在select后from前用大括号来将case when then隔开

9.43常见问题

子查询没有结果时，不返回任何行，不返回NULL；
不能再where子句里将返回的多行结果用单行比较符比较

9.5多行查询

也称为集合比较子查询
内查询返回多行
使用多行比较操作符

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZulX3KMA-1690118334549)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230715213013671.png)]

9.6相关子查询

9.61判断

如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为关联子查询。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jDZ1cCPY-1690118334549)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230715215229912.png)]

9.62在from中使用子查询

from型的子查询：

子查询是作为from的一部分，子查询要用()引起来，并且要给这个子查询取别名，把它当成一张“临时的虚拟的表”来使用。

9.63在order by中使用子查询

作为排序的依据，下图为实践代码

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lDiBaoWn-1690118334549)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230715215437558.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TKueEPZC-1690118334550)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230715215747981.png)]

9.64其他使用的子查询

EXISTS与NOT EXISTS关键字

更新删除操作时在where子句后存在子查询。

10.创建和管理表

10.1基础知识

在 MySQL 中，一个完整的数据存储过程总共有 4 步，分别是

创建数据库、确认字段、创建数据表、插入数据。

10.11标识符命名规则

数据库名、表名不得超过30个字符，变量名限制为29个
必须只能包含 A–Z, a–z, 0–9, _共63个字符
数据库名、表名、字段名等对象名中间不要包含空格
同一个MySQL软件中，数据库不能同名；
同一个库中，表不能重名；
同一个表中，字段不能重名
必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用`（着重号）引起来
保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了

10.12重要的数据类型

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3SRDxOHe-1690118334550)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230716155254009.png)]

10.2数据库操作

10.21创建数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TC5VKmc0-1690118334550)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230716155439736.png)]

10.22使用数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-t7KKU91B-1690118334550)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230716155834053.png)]

注意:查询当前使用的数据库用的是select;

10.23修改数据库

alter database 库名 character set 字符集；

10.24删除数据库

drop database （if exists ）库名

10.3表的操作

10.31创建表

创建方式

1.自己创建格式如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-q2EKhzUp-1690118334550)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230716162410075.png)]

2.从已有的表中得到某些字段与数据

create table name as select (字段name) from 已有表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Onvcs8cs-1690118334550)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230716162523511.png)]

3.显示建表的语句

show create table 表名\G

10.32修改表

修改表指的是修改数据库中已经存在的数据表的结构。

使用 ALTER TABLE 语句可以实现：

向已有的表中添加列 add
修改现有表中的列 modify
删除现有表中的列 drop column
重命名现有表中的列change(rename to 修改表名)

alter table 表名（关键字）+其他信息

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-U7TvH6HI-1690118334551)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230716163800600.png)]

10.33重命名表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eZObE4yF-1690118334551)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230716165939168.png)]

10.34删除表

在MySQL中，当一张数据表没有与其他任何数据表形成关联关系时，可以将当前数据表直接删除。

数据和结构都被删除
所有正在运行的相关事务被提交
所有相关索引被删除

语法格式：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Kyv6J0I7-1690118334551)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230716170209206.png)]

10.35清空表

TRUNCATE TABLE语句：

删除表中所有的数据
释放表的存储空间

truncate语法：truncate +表名（后不能跟where）

delete语法：delete [from] table [where condition];

delete和truncate命令之间的差别

1、命令类型

delete是数据操作语言（DML）命令；而truncate是数据定义语言（DDL）命令。

2、功能

delete命令根据指定的SQL语句从表中删除单个，多个或所有记录；而truncate命令从数据库中删除所有记录和表结构。

3、Where子句

delete命令支持WHERE子句，可以使用带有DELETE的where子句来过滤和删除特定记录；而，truncate命令不支持WHERE子句。

4、锁定

delete命令采用行级锁定，表中的每一行都被锁定以进行删除；truncate命令采用表级锁定，锁定了整个表以删除所有记录。

5、索引视图

delete命令可以与索引视图一起使用；而truncate命令不能与索引视图一起使用。

6、执行速度

由于delete命令维护日志，因此速度很慢。但是，由于truncate命令在事务日志中维护最少的日志记录，因此执行速度更快。

7、表结构

delete命令不会影响表结构，而truncate命令会从数据库中删除表结构。

8、事务空间

delete命令比truncate命令使用更多的事务空间。

delete执行删除的过程是每次从表中删除一行，并且同时该行的删除操作将作为事务记录在日志中以便进行回归操作。

9.truncate则一次性的从表中删除所有的数据，并不把删除操作记录在日志中，删除时不能回复的。并且在删除的过程中是不会激活与表有关系的触发器，执行速度非常快。

11数据处理-增删改

11.1插入数据

方式：

语法1.insert into 表名（字段名，）values：

1.插入一条所有字段按默认顺序插入数据
2.为表的指定字段插入数据
3.插入时指定多个值列表，每个值列表之间用逗号分隔开

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aUnYACuy-1690118334551)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230716175419636.png)]

一个同时插入多行记录的INSERT语句等同于多个单行插入的INSERT语句，但是多行的INSERT语句在处理过程中效率更高。因为MySQL执行单条INSERT语句插入多行数据比使用多条INSERT语句快，所以在插入多条记录时最好选择使用单条INSERT语句的方式插入。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DWJOFjIU-1690118334552)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230716181625022.png)]

语法2.insert into表名（字段名，）select：

在 INSERT 语句中加入子查询。
不必书写 VALUES 子句。
子查询中的值列表应与 INSERT 子句中的列名对应。

11.2更新数据

语法：update 表名 set（where+条件）

可以一次更新多条数据。
如果需要回滚数据，需要保证在DML前：===》进行设置：SET AUTOCOMMIT = FALSE;
使用 WHERE 子句指定需要更新的数据。

11.3删除数据

delete from (表名)+where条件

如果省略 WHERE 子句，则表中的全部数据将被删除，可以回滚。

11.4新特性-计算列

简单来说就是某一列的值是通过别的列计算得来的

语法：

1.CREATE TABLE

实例：

CREATE TABLE tb1(
id INT,
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL
)

修改更新a,b的数据时c 的数据也会自动改变

2.ALTER TABLE

generated always as (条件) virtual

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HEEMjgUN-1690118334552)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230716181532531.png)]

12数据类型

12.1各种数据类

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bpvAvaLr-1690118334552)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230716204839383.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-k3gNbalZ-1690118334552)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230716204903373.png)]

浮点数与定点数的比较

当相同位数的计算机表示数据（比如64位），浮点数能表示的数据范围远远大于定点数表示的数据范围。
当相同位数的计算机表示数据（比如64位），浮点数的相对精度比定点数要高。
浮点数在计算时，要分阶码部分的计算和尾数部分的计算，而且运算结果要求规格化，故浮点运算步骤比定点数运算步骤多，运算速度比定点运算速度低。
目前大多数计算机使用浮点数表示小数。

12.2常设置的属性

12.21整数类型

一共有 5 种，包括 TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）和 BIGINT。
从MySQL 8.0.17开始，整数数据类型不推荐使用显示宽度属性。
UNSIGNED : 无符号类型（非负），所有的整数类型都有一个可选的属性UNSIGNED
ZEROFILL : 0填充,（如果某列是ZEROFILL，那么MySQL会自动为当前列添加UNSIGNED属性），如果指定了ZEROFILL只是表示不够M位时，用0在左边填充，如果超过M位，只要不超过数据存储范围即可

使用场景：：：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xpORcshW-1690118334553)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230716210052915.png)]

12.22小数类型

浮点类型：

从MySQL 8.0.17开始，FLOAT(M,D) 和DOUBLE(M,D)用法在官方文档中已经明确不推荐使用
因为浮点数是不准确的，所以我们要避免使用“=”来判断两个数是否相等。

定点类型：

DECIMAL(M,D)的最大取值范围与DOUBLE类型一样
定点数在MySQL内部是以 字符串 的形式进行存储，一定是精准的。
定点数类型取值范围相对小，但是精准，没有误差

使用场景：

由于 DECIMAL 数据类型的精准性，在我们的项目中，除了极少数（比如商品编号）用到整数类型 外，其他的数值都用的是 DECIMAL

12.23时间类型

时间类型：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mZgIY8Yf-1690118334553)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230716210622169.png)]

使用函数 CURRENT_TIMESTAMP() 和 NOW() ，可以向DATETIME类型的字段插入系统的当前日期和时间。
TIMESTAMP存储的时间范围比DATETIME要小很多，只能存储 “1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间。其中，UTC表示世界统一时间，也叫作世界标准时间。

使用场景：

用得最多的日期时间类型，就是 DATETIME 。虽然 MySQL 也支持 YEAR（年）、 TIME（时间）、 DATE（日期），以及 TIMESTAMP 类型，但是在实际项目中，尽量用 DATETIME 类型。因为这个数据类型包括了完整的日期和时间信息，取值范围也最大，使用起来比较方便。毕竟，如果日期时间信息分散在好几个字段，很不容易记，而且查询的时候，SQL 语句也会更加复杂。此外，一般存注册时间、商品发布时间等，不建议使用DATETIME存储，而是使用时间戳 ，因为 DATETIME虽然直观，但不便于计算。

12.24字符类型

由于实际存储的长度不确定，MySQL 不允许 TEXT 类型的字段做主键
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-420fMIN5-1690118334553)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230716211256875.png)]

12.25其他：

ENUM类型:设置字段值时，ENUM 类型只允许从成员中选取单个值，不能一次选取多个值。
SET表示一个字符串对象，可以包含0个或多个成员，但成员个数的上限为 64 。SET类型在选取成员时，可以一次选择多个成员
插入重复的SET类型成员时，MySQL会自动删除重复的成员
向SET类型的字段插入SET成员中不存在的值时，MySQL会抛出错误。
JSON（JavaScript Object Notation）是一种轻量级的数据交换格式。简洁和清晰的层次结构使得 JSON 成为理想的数据交换语言。它易于人阅读和编写，同时也易于机器解析和生成，并有效地提升网络传输效率。JSON 可以将 JavaScript 对象中表示的一组数据转换为字符串，然后就可以在网络或者程序之间轻松地传递这个字符串，并在需要的时候将它还原为各编程语言所支持的数据格式。

12.3使用场景

在评估用哪种整数类型的时候，你需要考虑 存储空间 和 可靠性 的平衡问题：一方面，用占用字节数少的整数类型可以节省存储空间；另一方面，要是为了节省存储空间，使用的整数类型取值范围太小，一旦遇到超出取值范围的情况，就可能引起系统错误，影响可靠性。[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vYhkj5mF-1690118334553)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230716205432661.png)]

13.约束

13.1概述

定义：约束实际上就是表中数据的限制条件

目的：表在设计的时候加入约束的目的就是为了保证表中的记录完整性和有效性

分类：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7HLJRmh2-1690118334554)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230716214830446.png)]

13.2使用

自动增长：auto_increment(无法插入时，仍然会自增)

检查约束：check（某或多个条件）

默认约束：default ’ ’

一个字段可以增加多个约束，空格隔开；

13.3外键约束

外键用来让两张表的数据之间建立连接===》

子表具有外键，与父表的主键连接（逻辑上）

要保证数据的一致性与完整性，就要建立数据库关联

操作：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IW9TW5ph-1690118334554)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230716220740949.png)]

添加外键：

1.alter table 表名 add constraint 外键名 foreign key 外键字段 references 主表主表字段

2.在create table时添加外键

constraint 外键名 foreign key 字段名 references 主表名主表字段

删除外键：

alter table 表名 drop foreign key 外键名；

更新外键：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LHIC3dkT-1690118334554)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230716221857073.png)]

前两个默认，剩下的需要自己在添加外键时设置（关键字：ON DELETE/ON UPDATE）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BinlRZH3-1690118334554)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230716222012952.png)]

查询某表的约束名：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aNgSPs27-1690118334554)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230716222631466.png)]

14.视图

14.1介绍

一个或者多个数据表里的数据的逻辑显示，视图并不存储数据

14.2操作语法

创建：

create or replace view 名字 as select 子句

查询：

show create view 视图名（查询创建语句）
select * from 视图名（查数据）

修改：

create or replace view as select ……
alter view 视图名（字段名） as select

删除：

drop view 视图名

14.3增删改操作

insert into 视图名 values……

当向视图中插入数据时，同时也会向原表插入数据插入数据，而原表(employees)中存在多个字段不允许为空，所以无法插入，将这些不允许为空的字段修改为允许为空即可。

可以在创建视图时加入检查子句：

with (cascade/local)check option

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-griuomUV-1690118334555)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230717100722739.png)]

CASCADE option:检查添加的数据满足表一和表二的条件(集联)。

LOCAL:检查自己的，然后递归找上一个表如果有检查条件才会检查。

虽然可以更新视图数据，但总的来说，视图作为虚拟表，主要用于 方便查询 ，不建议更新视图的 数据。对视图数据的更改，都是通过对实际数据表里数据的操作来完成的。

14.4视图的更新

要使视图可更新，视图中的行和底层基本表中的行之间必须存在 一对一的关系；

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7o9MG1pm-1690118334555)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230717103604651.png)]

14.5使用

优点：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-X1V4w47o-1690118334555)(C:\Users\86182\AppData\Roaming\Typora\typora-user-images\image-20230717103822090.png)]

缺点：

如果我们在实际数据表的基础上创建了视图，那么，如果实际数据表的结构变更了，我们就需要及时对相关的视图进行相应的维护。特别是嵌套的视图（就是在视图的基础上创建视图），维护会变得比较复杂，可读性不好，容易变成系统的潜在隐患。因为创建视图的 SQL 查询可能会对字段重命名，也可能包含复杂的逻辑，这些都会增加维护的成本.

实际项目中，如果视图过多，会导致数据库维护成本的问题。