MySQL~数据库知识梳理

一只码农菜汪

已于 2022-08-05 19:51:17 修改

阅读量820

点赞数 3

文章标签：数据库 mysql java

于 2022-08-05 19:50:06 首次发布

本文链接：https://blog.csdn.net/qq_52223733/article/details/126183074

版权

什么是数据库？

首先，要理解什么是数据？

描述事物的符号称为数据。数据有多种表现形式，可以是数字，也可以是文字、图形、图像、声音、语言等。在数据库中数据表示为一条记录。例如，在学生管理数据库中，记录学生的信息包括学号、姓名、性别、年龄、籍贯和联系电话等，这些信息就是数据。

数据库（Database）指长期存储在计算机内的、有组织的、可共享的数据集合。通俗的讲，数据库就是存储数据的地方，就像冰箱是存储食物的地方一样。在生活中，每个人都在使用数据库。当我们在电话簿里查找名字时，就是在使用数据库。在某个浏览器上进行搜索时，也是在使用数据库。以及平时我们登录网络，也需要依靠数据库验证自己的名字和密码。即便是在使用 ATM 机时，也要利用数据库进行 PIN 码验证和余额检查。

数据库管理系统（DBMS）是数据库系统的核心软件之一，是位于用户与操作系统之间的数据管理软件，用于建立，使用和维护数据库。它的主要功能包括数据定义、数据操作、数据库的运行管理、数据库的建立和维护等几个方面。

我们常说 XX 数据库，其实实质上是 XX 数据库管理系统。目前，较为流行的数据库管理系统有 MySQL、SQL Server、Oracle 和 DB2 等。

数据管理技术的3个发展阶段

数据管理就是对各种数据进行分类、组织、编码、查询和维护，主要经历了 3 个阶段，即人工管理阶段、文件系统阶段和数据库系统阶段。每一个阶段都是以减小数据冗余、增强数据独立性和方便操作数据为目的进行发展。

第一阶段：人工管理阶段

人工管理阶段的特点如下：
●数据不能长期保存
●不便于查询数据
●数据不能共享，冗余度大
●数据不具有独立性

第二阶段：文件系统阶段

文件系统阶段的特点如下：
        ●数据可以长期保存
        ●数据由文件系统来管理
        ●数据冗余大，共享性差
        ●数据独立性差
        ●无法应对突发事故（文件误删，磁盘故障等）

第三阶段：数据库系统阶段

数据库系统阶段的特点如下：
        ●数据由数据库管理系统统一管理和控制
        ●数据共享性高，冗余度低
        ●数据独立性强
        ●数据粒度小（数据粒度是数据库中数据的细化程度。细化程度越高，粒度越小；细化程度越低，粒度越大。）

从数据的管理方式上来划分，数据库有两种类型，分别是关系型数据库和非关系型数据库。

关系型数据库

关系型数据库是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。简单说，关系型数据库是由多张能互相连接的表组成的数据库。

常见的关系型数据库有 Oracle、DB2、PostgreSQL、Microsoft SQL Server、Microsoft Access 和 MySQL 等。

优点	都是使用表结构，格式一致，易于维护。
	使用通用的 SQL 语言操作，使用方便，可用于复杂查询。
	数据存储在磁盘中，安全。
缺点	读写性能比较差，不能满足海量数据的高效率读写。
	不节省空间。因为建立在关系模型上，就要遵循某些规则，比如数据中某字段值即使为空仍要分配空间。
	固定的表结构，灵活度较低。

非关系型数据库

非关系型数据库又被称为 NoSQL（Not Only SQL )，意为不仅仅是 SQL。通常指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定。

常见的非关系型数据库有 Neo4j、MongoDB、Redis、Memcached 等。

优点	非关系型数据库存储数据的格式可以是 key-value 形式、文档形式、图片形式等。使用灵活，应用场景广泛，而关系型数据库则只支持基础类型。
	速度快，效率高。 NoSQL 可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘。
	海量数据的维护和处理非常轻松。
	非关系型数据库具有扩展简单、高并发、高稳定性、成本低廉的优势。
	可以实现数据的分布式处理。
缺点	非关系型数据库暂时不提供 SQL 支持，学习和使用成本较高。
	非关系数据库没有事务处理，没有保证数据的完整性和安全性。适合处理海量数据，但是不一定安全。
	功能没有关系型数据库完善。

数据库系统

数据库系统（Database System，DBS）由硬件和软件共同构成。硬件主要用于存储数据库中的数据，包括计算机、存储设备等。软件部分主要包括数据库管理系统、支持数据库管理系统运行的操作系统，以及支持多种语言进行应用开发的数据库访问接口技术等。

数据库系统主要有以下 3 个组成部分：
1.数据库：用于存储数据的地方。
2.数据库管理系统：用于管理数据库的软件。
3.数据库应用程序：为了提高数据库系统的处理能力所使用的管理数据库库的业务软件系统。

数据库（DataBase，DB）提供了一个存储空间来存储各种数据，可以将数据库视为一个存储数据的容器。一个数据库可能包含许多文件，一个数据库系统中通常包含许多数据库。

数据库管理系统（Database Management System，DBMS）是用户创建、管理和维护数据库时所使用的软件，位于用户和操作系统之间，对数据库进行统一管理。DBMS 能定义数据存储结构，提供数据的操作机制，维护数据库的安全性、完整性和可靠性。

数据库应用程序（DataBase Application）的使用可以满足对数据管理的更高要求，还可以使数据管理过程更加直观和友好。数据库应用程序负责与 DBMS 进行通信、访问和管理 DBMS 中存储的数据，允许用户插入、修改、删除数据库中的数据。

MySQL

MySQL 是一种开放源代码的数据库，具有跨平台性支持在 Windows 、UNIX、Linux 和 Mac OS 等操作系统上运行。

MySQL 是一个真正的多用户、多线程 SQL 数据库服务器。它能够快速、有效和安全的处理大量的数据。相对于 Oracle 等数据库来说，MySQL 的使用是非常简单的。MySQL 主要目标是快速、健壮和易用。

MySQL内部基础结构：

连接层
SQL层
存储引擎层
文件系统层

MySQL的数据类型

MySQL 的数据类型有大概可以分为 5 种：整数类型、浮点数类型和定点数类型、日期和时间类型、字符串类型、二进制类型等。

1.数值类型

整数类型包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，浮点数类型包括 FLOAT 和 DOUBLE，定点数类型为 DECIMAL。

2.日期/时间类型

日期/时间类型有YEAR、TIME、DATE、DATETIME 和 TIMESTAMP。

3.字符串类型

CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT等。

CHAR 和 VARCHAR 的区别如下:
        ●CHAR 是固定长度字符，VARCHAR 是可变长度字符。
        ●CHAR 会自动删除插入数据的尾部空格，VARCHAR 不会删除尾部空格。
        CHAR 是固定长度，所以它的处理速度比 VARCHAR 的速度要快，但是它的缺点就是浪费存储空间。所以对存储不大，但在速度上有要求的可以使用 CHAR 类型，反之可以使用 VARCHAR类型来实现。
存储引擎对于选择 CHAR 和 VARCHAR 的影响:
        ●对于 MyISAM 存储引擎，最好使用固定长度的数据列代替可变长度的数据列。这样可以使整个表静态化，从而使数据检索更快，用空间换时间。
        ●对于InnoDB存储引擎，最好使用可变长度的数据列，因为 InnoDB 数据表的存储格式不分固定长度和可变长度，因此使用 CHAR 不一定比使用 VARCHAR 更好，但由于 VARCHAR 是按照实际的长度存储，比较节省空间，所以对磁盘 I/O 和数据存储总量比较好。

4.二进制类型

包括 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。

MySQL数据库的基本操作

SQL的全称是Structured Query Language，结构化查询语言，对数据库进行查询、修改等操作。

SQL 包含以下 4 部分：
        ●数据定义语言（Data Definition Language，DDL）：用来创建或删除数据库以及表等对象，主要包含以下几种命令：
                ○DROP：删除数据库和表等对象
                ○CREATE：创建数据库和表等对象
                ○ALTER：修改数据库和表等对象的结构
        ●数据操作语言（Data Manipulation Language，DML）：用来变更表中的记录，主要包含以下几种命令：
                ○INSERT：向表中插入新数据
                ○UPDATE：更新表中的数据
                ○DELETE：删除表中的数据
        ●数据查询语言（Data Query Language，DQL）：用来查询表中的记录，主要包含 SELECT 命令，来查询表中的数据。
        ●数据控制语言（Data Control Language，DCL）：用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对数据库中的用户设定权限。主要包含以下几种命令：
                ○GRANT：赋予用户操作权限
                ○REVOKE：取消用户的操作权限
                ○COMMIT：确认对数据库中的数据进行的变更
                ○ROLLBACK：取消对数据库中的数据进行的变更

SQL代码示例：

查看数据库：SHOW DATABASES;

SHOW DATABASES [LIKE '数据库名'];

创建数据库：CREATE DATABASE IF NOT EXISTS test_db;

CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>]

[[DEFAULT] COLLATE <校对规则名>];

删除数据库：DROP DATABASE IF EXISTS test_db_del;

DROP DATABASE [ IF EXISTS ] <数据库名>

使用(切换)数据库：USE test_db;

SELECT查询语句

SELECT 的语法格式如下：

SELECT
{* | <字段列名>}
[
FROM <表 1>, <表 2>…
[WHERE <表达式>
[GROUP BY <group by definition>
[HAVING <expression> [{<operator> <expression>}…]]
[ORDER BY <order by definition>]
[LIMIT[<offset>,] <row count>]
]

查询表中的所有字段：SELECT * FROM tb_students_info;

SELECT * FROM 表名;

查询表中指定的字段：SELECT name FROM tb_students_info;

SELECT < 列名 > FROM < 表名 >;

SELECT <字段名1>,<字段名2>,…,<字段名n> FROM <表名>;

过滤重复数据：SELECT DISTINCT age FROM student;

SELECT DISTINCT <字段名> FROM <表名>;

通过AS设置别名：

SELECT name AS student_name, age AS student_age FROM tb_students_info AS stu;

<表名> [AS] <别名>

<字段名> [AS] <别名>

限制查询结果的条数：SELECT * FROM tb_students_info LIMIT 3,5;

SELECT * FROM <表名> LIMIT 初始位置，记录数；

对查询结果排序：

ORDER BY <字段名> [ASC|DESC]

1.单字段排序

SELECT * FROM tb_students_info ORDER BY height;

2.多字段排序

SELECT name,height FROM tb_students_info ORDER BY height,name;

WHERE条件查询数据

单一条件的查询语句

SELECT name,age FROM tb_students_info
WHERE age<22;

多条件的查询语句

SELECT name,age,height FROM tb_students_info
WHERE age>21 AND height>=175;

SELECT name,age,height FROM tb_students_info
WHERE age>21 OR height>=175;

LIKE：模糊查询

[NOT] LIKE '字符串'

带有“%”通配符的查询

#在 tb_students_info 表中，查找所有以字母“T”开头的学生姓名

SELECT name FROM tb_students_info
WHERE name LIKE 'T%';

带有“_”通配符的查询

#在 tb_students_info 表中，查找所有以字母“y”结尾，且“y”前面只有 4 个字母的学生姓名：

SELECT name FROM tb_students_info
WHERE name LIKE '____y';

BETWEEN AND：范围查询

#在表 tb_students_info 中查询年龄在 20 到 23 之间的学生姓名和年龄

SELECT name,age FROM tb_students_info
WHERE age BETWEEN 20 AND 23;

IS NULL：空值查询

#使用 IS NULL 关键字来查询 tb_students_info 表中 login_date 字段是 NULL 的记录

SELECT name,login_date FROM tb_students_info
WHERE login_date IS NULL;

分组查询

GROUP BY <字段名>

GROUP BY单独使用

#根据 tb_students_info 表中的 sex字段进行分组查询

SELECT sex FROM tb_students_info GROUP BY sex;

GROUP BY 与 GROUP_CONCAT()

#根据 tb_students_info 表中的 sex 字段进行分组查询，使用 GROUP_CONCAT() 函数将每个分组的 name 字段的值都显示出来

SELECT `sex`, GROUP_CONCAT(name) FROM tb_students_info GROUP BY sex;

GROUP BY 与聚合函数

        在数据统计时，GROUP BY 关键字经常和聚合函数一起使用。聚合函数包括： COUNT()，SUM()，AVG()，MAX() 和 MIN()。
        1.COUNT() 用来统计记录的条数；
        2.SUM() 用来计算字段值的总和；
        3.AVG() 用来计算字段值的平均值；
        4.MAX() 用来查询字段的最大值；
        5.MIN() 用来查询字段的最小值。

#根据 tb_students_info 表的 sex 字段进行分组查询，使用 COUNT() 函数计算每一组的记录数。

SELECT sex,COUNT(sex) FROM tb_students_info GROUP BY sex;

GROUP BY 与 WITH ROLLUP

#下面根据 tb_students_info 表中的 sex 字段进行分组查询，并使用 WITH ROLLUP 显示记录的总和

SELECT sex,GROUP_CONCAT(name) FROM tb_students_info
GROUP BY sex WITH ROLLUP;

HAVING：过滤分组

HAVING <查询条件>

#分别使用 HAVING 和 WHERE 关键字查询出 tb_students_info 表中身高大于 150 的学生姓名，性别和身高
SELECT name,sex,height FROM tb_students_info
HAVING height>150;


#根据 height 字段对 tb_students_info 表中的数据进行分组，并使用 HAVING 和 WHERE 关键字分别查#询出分组后平均身高大于 170 的学生姓名、性别和身高。
SELECT GROUP_CONCAT(name),sex,height FROM tb_students_info
GROUP BY height
HAVING AVG(height)>170;

交叉连接

SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句]
或者
SELECT <字段名> FROM <表1>, <表2> [WHERE子句]

内连接

SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句]

外连接

内连接的查询结果都是符合连接条件的记录，而外连接会先将连接的表分为基表和参考表，再以基表为依据返回满足和不满足条件的记录。外连接可以分为左外连接和右外连接

左外连接

SELECT <字段名> FROM <表1> LEFT OUTER JOIN <表2> <ON子句>

右外连接

SELECT <字段名> FROM <表1> RIGHT OUTER JOIN <表2> <ON子句>

MySQL子查询

其中，操作符可以是比较运算符和 IN、NOT IN、EXISTS、NOT EXISTS 等关键字。

WHERE <表达式> <操作符> (子查询)

select语句的执行顺序如下：

FROM
<表名> # 笛卡尔积
ON
<筛选条件> # 对笛卡尔积的虚表进行筛选
JOIN <JOIN,LEFT JOIN,RIGHT JOIN...>
<JOIN表> # 指定JOIN，用于添加数据到ON之后的虚表中，例如LEFT JOIN会将左表的剩余数据添加到虚表中
WHERE
<WHERE条件> # 对上述虚表进行筛选
GROUP BY
<分组条件> # 分组
<SUM()等聚合函数> # 用于HAVING子句进行判断，在书写上这类聚合函数是写在HAVING判断里面的
HAVING
<分组筛选> # 对分组后的结果进行聚合筛选
SELECT
<返回数据列表> # 返回的单列必须在GROUP BY子句中，聚合函数除外
DISTINCT
# 数据除重
ORDER BY
<排序条件> # 排序
LIMIT
<行数限制>