数据库MySQL学习第一天|了解数据库、数据类型、存储引擎、sql语言

君君超有趣

于 2024-07-18 03:44:34 发布

阅读量496

点赞数 12

分类专栏：数据库MySql学习文章标签：数据库 mysql 学习 java sql

本文链接：https://blog.csdn.net/weixin_60364343/article/details/140510197

版权

数据库MySql学习专栏收录该内容

1 篇文章 0 订阅

订阅专栏

了解数据库

什么是数据库

概念：数据仓库，软件，安装在操作系统之上

作用：存储数据，管理数据

数据库分类

关系型数据库：SQL（Structured Query Language）

MySQL、Oracle、Sql Server、DB2、SQLlite
通过表和表之间，行和列之间的关系进行数据的存储
通过外键关联来建立表与表之间的关系

非关系型数据库：NoSQL（Not Only SQL）

Redis、MongoDB
指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定

MySQL概念

DBMS:数据库管理系统

数据库的管理软件，科学有效的管理、维护和获取我们的数据
MySQL就是数据库管理系统

可以看到，MySQL是用来操作DB文件的数据库管理系统。

数据类型

整数类型

整数类型	描述	整数大小
tinyInt	很小的整数(8位二进制)	1字节
smallInt	小的整数(16位二进制)	2字节
mediumInt	中等大小的整数(24位二进制)	3字节
int(integer)	普通大小的整数(32位二进制)	4字节
bigInt	极大的整数(64位二进制)	8字节

任何整数类型都可以加上UNSIGNED属性，表示数据是无符号的，即非负整数。

整数类型可以被指定长度，例如：INT(11)表示长度为11的INT类型。长度在大多数场景是没有意义的，它不会限制值的合法范围，只会影响显示字符的个数，而且需要和UNSIGNED ZEROFILL属性配合使用才有意义。

假定类型设定为INT(5)，属性为UNSIGNED ZEROFILL，如果用户插入的数据为12的话，那么数据库实际存储数据为00012。

小数类型

小数类型	描述
float	单精度浮点数
double	双精度浮点数
decimal(m,d)	压缩严格的定点数

DECIMAL可以用于存储比BIGINT还大的整型，能存储精确的小数。

而FLOAT和DOUBLE是有取值范围的，并支持使用标准的浮点进行近似计算。

计算时FLOAT和DOUBLE相比DECIMAL效率更高一些，DECIMAL你可以理解成是用字符串进行处理。

日期类型

日期类型	描述
year	YYYY 1901~2155
time	HH:MM:SS -838:59:59~838:59:59
date	YYYY-MM-DD 1000-01-01~9999-12-3
datetime	YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~ 9999-12-31 23:59:59
timestamp	YYYY-MM-DD HH:MM:SS 19700101 00:00:01 UTC~2038-01-19 03:14:07UTC

尽量使用timestamp，空间效率高于datetime，

用整数保存时间戳通常不方便处理。

如果需要存储微秒，可以使用bigint存储。

文本,二进制类型

文本,二进制类型	描述
CHAR(M)	M为0~255之间的整数
VARCHAR(M)	M为0~65535之间的整数
TINYBLOB	允许长度0~255字节
BLOB	允许长度0~65535字节
MEDIUMBLOB	允许长度0~167772150字节
LONGBLOB	允许长度0~4294967295字节
TINYTEXT	允许长度0~255字节
TEXT	允许长度0~65535字节
MEDIUMTEXT	允许长度0~167772150字节
LONGTEXT	允许长度0~4294967295字节
VARBINARY(M)	允许长度0~M个字节的变长字节字符串
BINARY(M)	允许长度0~M个字节的定长字节字符串

对于经常变更的数据来说，CHAR比VARCHAR更好，因为CHAR不容易产生碎片。

对于非常短的列，CHAR比VARCHAR在存储空间上更有效率。

使用时要注意只分配需要的空间，更长的列排序时会消耗更多内存。

尽量避免使用TEXT/BLOB类型，查询时会使用临时表，导致严重的性能开销。

存储引擎

存储引擎Storage engine：MySQL中的数据、索引以及其他对象是如何存储的，是一套文件系统的实现。

种类

Innodb引擎
- Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
- 特性
  - 插入缓冲（insert buffer)
  - 二次写(double write)
- 自适应哈希索引(ahi)
- 预读(read ahead)
MyIASM引擎
- (原本Mysql的默认引擎)：不提供事务的支持，也不支持行级锁和外键。
MEMORY引擎
- 所有的数据都在内存中，数据的处理速度快，但是安全性不高。

引擎选择

如果没有特别的需求，使用默认的Innodb即可。

MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。

Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键。比如OA自动化办公系统。

sql

结构化查询语言(Structured Query Language)简称SQL，是一种数据库查询语言。

作用：用于存取数据、查询、更新和管理关系数据库系统。

主键和外键

主键

定义

唯一标识一条记录，不能有重复的，不允许为空

作用

用来保证数据完整性

个数

主键只能有一个

设计原则

主键应当是对用户没有意义的。如果用户看到了一个表示多对多关系的连接表中的数据，并抱怨它没有什么用处，那就证明它的主键设计地很好。

主键应该是单列的，以便提高连接和筛选操作的效率。

永远也不要更新主键。实际上，因为主键除了惟一地标识一行之外，再没有其他的用途了，所以也就没有理由去对它更新。如果主键需要更新，则说明主键应对用户无意义的原则被违反了。

主键不应包含动态变化的数据，如时间戳、创建时间列、修改时间列等。

选取策略

自动增长字段

很多数据库设计者喜欢使用自动增长型字段，因为它使用简单。自动增长型字段允许我们在向数据库添加数据时，不考虑主键的取值，记录插入后，数据库系统会自动为其分配一个值，确保绝对不会出现重复。

手动增长字段

既然自动增长型字段会带来如此的麻烦，我们不妨考虑使用手动增长型的字段，也就是说主键的值需要自己维护，通常情况下需要建立一张单独的表存储当前主键键值。
这次我们新建一张表叫IntKey，包含两个字段，KeyName以及KeyValue。就像一个HashTable，给一个KeyName，就可以知道目前的KeyValue是什么，然后手工实现键值数据递增。

使用UniqueIdentifier

SQL Server为我们提供了UniqueIdentifier数据类型，并提供了一个生成函数NEWID( )，使用NEWID( )可以生成一个唯一的UniqueIdentifier。UniqueIdentifier在数据库中占用16个字节，出现重复的概率非常小，以至于可以认为是0。

使用“COMB（Combine）”类型(SQL Server)

COMB数据类型的基本设计思路是这样的：既然UniqueIdentifier数据因毫无规律可言造成索引效率低下，影响了系统的性能，那么我们能不能通过组合的方式，保留UniqueIdentifier的前10个字节，用后6个字节表示GUID生成的时间（DateTime），这样我们将时间信息与UniqueIdentifier组合起来，在保留UniqueIdentifier的唯一性的同时增加了有序性，以此来提高索引效率。

外键

定义

外键是某个表中的一列，它包含在另一个表的主键中。

外键也是索引的一种，是通过一张表中的一列指向另一张表中的主键，来对两张表进行关联。

一张表可以有一个外键，也可以存在多个外键，与多张表进行关联。

作用

外键的主要作用是保证数据的一致性和完整性，并且减少数据冗余。
主要体现在以下两个方面：

阻止执行

从表插入新行，其外键值不是主表的主键值便阻止插入。
从表修改外键值，新值不是主表的主键值便阻止修改。
主表删除行，其主键值在从表里存在便阻止删除(要想删除，必须先删除从表的相关行)。
主表修改主键值，旧值在从表里存在便阻止修改(要想修改，必须先删除从表的相关行)。

总结为一句话,从表的外键必须和主表的主键一起出现

级联执行

主表删除行，连带从表的相关行一起删除。
主表修改主键值，连带从表相关行的外键值一起修改。

删除修改就是先从后主

外键创建限制

父表必须已经存在于数据库中，或者是当前正在创建的表。

如果是后一种情况，则父表与子表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。

必须为父表定义主键。

外键中列的数目必须和父表的主键中列的数目相同。

两个表必须是 InnoDB 表，MyISAM 表暂时不支持外键。

外键列必须建立了索引，MySQL 4.1.2 以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显式建立。

外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如 int 和tinyint 可以，而 int 和 char 则不可以；

索引

定义:该字段没有重复值，但可以有一个空值

**作用:**是提高查询排序的速度

个数:一个表可以有多个惟一索引

表与表的关联

在语法上

内连接

内连接通过INNER JOIN来实现，它将返回两张表中满足连接条件的数据，不满足条件的数据不会查询出来。

外连接

外连接通过OUTER JOIN来实现，它会返回两张表中满足连接条件的数据，同时返回不满足连接条件的数据。外连接有两种形式：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）。

左外连接

可以简称为左连接（LEFT JOIN），它会返回左表中的所有记录和右表中满足连接条件的记录

右外连接

可以简称为右连接（RIGHT JOIN），它会返回右表中的所有记录和左表中满足连接条件的记录

等值相连

等值连接。这种连接是通过WHERE子句中的条件，将两张表连接在一起，它的实际效果等同于内连接。出于语义清晰的考虑，一般更建议使用内连接，而不是等值连接。

关联关系

一对多关联

这种关联形式最为常见，一般是两张表具有主从关系，并且以主表的主键关联从表的外键来实现这种关联关系。另外，以从表的角度来看，它们是具有多对一关系的，所以不再赘述多对一关联了。

多对多关联

这种关联关系比较复杂，如果两张表具有多对多的关系，那么它们之间需要有一张中间表来作为衔接，以实现这种关联关系。这个中间表要设计两列，分别存储那两张表的主键。因此，这两张表中的任何一方，都与中间表形成了一对多关系，从而在这个中间表上建立起了多对多关系。

自关联

自关联就是一张表自己与自己相关联，为了避免表名的冲突，需要在关联时通过别名将它们当做两张表来看待。一般在表中数据具有层级（树状）时，可以采用自关联一次性查询出多层级的数据。

sql约束

NOT NULL: 用于控制字段的内容一定不能为空（NULL）。
UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。
PRIMARY KEY: (主键约束)也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
FOREIGN KEY: (外键约束)用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一,它主要用于维护数据库表之间的引用完整性。。
CHECK:用于控制字段的值范围。

sql注入

定义

SQL注入的原理是将SQL代码伪装到输入参数中，传递到服务器解析并执行的一种攻击手法。也就是说，在一些对SERVER端发起的请求参数中植入一些SQL代码，SERVER端在执行SQL操作时，会拼接对应参数，同时也将一些SQL注入攻击的“SQL”拼接起来，导致会执行一些预期之外的操作。

预防

严格的参数校验

参数校验就没得说了，在一些不该有特殊字符的参数中提前进行特殊字符校验即可。

SQL预编译

实际上当将绑定的参数传到MySQL服务器，MySQL服务器对参数进行编译，即填充到相应的占位符的过程中，做了转义操作。我们常用的JDBC就有预编译功能，不仅提升性能，而且防止SQL注入。

聚合函数

定义

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

类型

AVG()：求平均值
SUM():求和
MAX():最大值
MIN():最小值
COUNT():求数量

语法

SELECT column, group_function(column)
FROM table
[WHERE	condition]
[GROUP BY	column]
[ORDER BY	column];

聚合函数不能嵌套调用。比如不能出现类似“AVG(SUM(字段名称))”形式的调用。

使用

可以对数值型数据使用AVG 和 SUM 函数。

SELECT AVG(column), MAX(column),MIN(column), SUM(column)
FROM   table
WHERE  ?;

可以对任意数据类型的数据使用 MIN 和 MAX 函数。

SELECT MIN(column), MAX(column)
FROM	table;
WHERE  ?;

COUNT(*)返回表中记录总数，适用于任意数据类型。

SELECT COUNT(*)
FROM	  table
WHERE  ? (column = ?);

COUNT(expr) 返回expr不为空的记录总数。

SELECT COUNT(column)
FROM   table
WHERE  ?;

问题:

用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 值

常见查询关键字

where与having
- WHERE是一个约束声明，使用WHERE约束来自数据库的数据，WHERE是在结果返回之前起作用的，WHERE中不能使用聚合函数。
- HAVING是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作，在HAVING中可以使用聚合函数。另一方面，HAVING子句中不能使用除了分组字段和聚合函数之外的其他字段。

where在获得结果前起作用，having在获得结果后起作用。

从性能的角度来说，having子句如果使用了分组字段作为过滤条件，则应该替换成where子句，因为where可以在执行分组操作和计算聚合函数前过滤掉不需要的数据，性能会更好。

in 和 exists
- in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。
- 一直大家都认为exists比in语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。
- 如果查询的两个表大小相当，那么用in和exists差别不大。
- 如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
- not in 和not exists：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。
UNION 和 UNION ALL

UNION 是一个用于合并多个 SELECT 查询结果的操作符。它的作用是将多个查询的结果集合并为一个结果集，并去除重复的行。

主要特点和作用如下：

合并结果集：UNION 操作符可以将多个查询的结果集合并为一个结果集。每个查询的结果集必须具有相同的列数和相似的数据类型。
去除重复行：与 UNION ALL 操作符不同，UNION 会去除合并结果集中的重复行，只保留一份。它通过对结果集进行去重操作来实现。
排序结果：UNION 默认会对合并后的结果集进行排序，以确保返回的结果是按照默认顺序排列的。如果需要自定义排序顺序，可以使用外部的 ORDER BY 子句来指定排序条件。
数据类型匹配：在使用 UNION 进行结果集合并时，需要确保每个查询的选择列表中的列具有相似的数据类型，或者可以进行隐式类型转换。否则，可能会导致错误或意外的结果。

Copy code
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2

上述示例中，UNION 操作符将 table1 和 table2 的查询结果合并为一个结果集，并去除重复的行。

需要注意的是，UNION 操作符在进行结果集合并时会进行排序和去重操作，这可能会对性能产生一定的影响。如果确保结果集中不会存在重复的行，并且不需要进行排序，可以考虑使用性能更高的 UNION ALL 操作符。

UNION ALL 是一个用于合并多个 SELECT 查询结果的操作符。它的作用是将多个查询的结果集合并为一个结果集，包含所有查询的结果，不去除重复行。