MySQL自带数据库

最新推荐文章于 2024-03-08 15:45:00 发布

thndjj

最新推荐文章于 2024-03-08 15:45:00 发布

阅读量1.2k

点赞数

文章标签：数据库 mysql java Powered by 金山文档

本文链接：https://blog.csdn.net/thndjj/article/details/129032941

版权

MySQL自带数据库

information_schema：是MySQL自带的数据库，主要保持MySQL数据库服务器的系统信息，比如数据库的名称，数据库表的名称，字段名称，存储权限等。

performance_schema：是MySQL系统自带的数据库，可以用来监控MySQL的各类性能指标。

sys：是MySQL系统自带的数据库，主要作用是，以一种更容易被理解的方式展示MySQL数据库服务器的各类性能指标。

MySQL主键

MySQL中的主键主要有3个特征：

必须唯一，不能重复

不能是空

必须可以唯一标识数据表中的记录

alter table demo.test

add column itenumber int primary key auto_increment;

MySQL数据类型

整数类型：

在评估用哪种整数类型的时候，我们需要考虑存储空间和可靠性平衡的问题。使用占用字节数少的整数类型可以节省存储空间，但是如果超出取值范围那么可能会引起系统错误，影响可靠性。

浮点数类型和定点数类型：

FLOAT（单精度浮点数），DOUBLE（双精度浮点数），REAL（默认是DOUBLE）

但是你可以通过这个语句把默认改变。

SET sql_mode = "REAL_AS_FLOAT";

浮点数类型有一个缺陷，就是不精确

那么为什么会出现这样的情况呢？

MySQL对浮点类型数据的存储方式

4个字节存储FLOAT类型，8个字节存储DOUBLE类型数据。但是它们都是用二进制方式来进行存储的。例如9.625用二进制表示，就是1001.101，你可以发现，如果尾数不是0或者5的话那么你无法用一个二进制数来精确的表达，就只能四舍五入了。

于是MySQL提供了定点数类型：DECIMAL

浮点数的存储方式决定了它是不可能精确的，而DECIMAL的存储方式决定了它一定是精确的。

DECIMAL把十进制转换成二进制数存储，DECIMAL则不同，它是把十进制数的整数部分和小数部分拆开，分别转换成十六进制数来进行存储。

DECIMAL(M，D)其中M表示整数加小数一共多少位，M <= 65。D表示小数部分位数,D < M。

文本类型：

CHAR(M)：固定长度字符串，必须预先定义字符串长度。

VARCHAR(M)：可变长度字符串，必须预先知道字符串的最大长度，不过只要不超过这个最大长度，具体存储的时候，是按照字符串长度存储的。

TEXT：系统自动按照实际长度存储，不需要预先定义，因此TEXT不能做主键。

MySQL的基本操作

有哪些约束？

非空约束

唯一性约束

自增约束

如何修改表？

把原来的表复制一下：

CREATE TABLE demo.importheadhist LIKE demo.importhead;

插入查询结果：

INSERT INTO 表名(字段名)

SELECT 字段名或值

FROM 表名

WHERE 条件

修改数据：

UPDATE 表名

SET 字段名=值

WHERE 条件

查询数据：

GROUP BY ：告诉MySQL查询结果要如何分组，经常与MySQL的聚合函数一起使用。

HAVING：用于筛选查询结果，跟WHERE类似。

FROM：

FROM关键字表示查询的数据源。

数据源不一定是表，也可以是一个查询结果。

SELECT a.goodsname, a.price

FROM (

SELECT *

FROM demo.goodsmaster

) AS a;

这个就是派生表：

SELECT *

FROM demo.goodsmaster

或者叫做子查询。

意思是我们把一个查询结果数据集当作一共虚拟的数据表来看待。MySQL规定，必须要用AS关键字给这个派生表起一个别名。

ORDER BY：

ASC表示升序，DESC表示降序。

如何设置主键？

尽量不要用业务字段，也就是跟业务有关的字段作为主键。

使用自增字段做主键（不推荐），多台服务器就寄了。

采用手动赋值的方法，通过一定的逻辑性，确保字段值全系统的唯一性。

外键和连接

外键和连接：如何做关联查询？

这种把分散在多个不同的表里面的数据查询出来的操作就叫做多表查询。

在MySQL中为了把2个表关联起来，会用到2个重要功能：外键（FOREIGN KEY）和连接（JOIN）。

外键需要在创建表的阶段就定义出来；连接可以通过相同意义的字段把两个表连接起来用在查询阶段。

如何创建外键？

假设我们有两张表，A和B，它们通过一个公共字段id发生关联关系，我们把这个关联关系叫做R。如果id在表A中是主键，那么表A就是这个关系R中的主表。相应的，表B就是这个关系中的从表，表B中的id，就是表B用来引用表A中数据的，叫外键。所以外键就是从表中用来引用主表数据的那个公共字段。

如何创建外键：

CREATE TABLE 从表名

(

-- 定义外键约束，提出外键字段和参照的主表字段

CONSTRAINT 外键约束名

FOREIGN KEY (字段名) REFERENCES 主表名 (字段名)

也可以修改表的字段：

ALTER TABLE 从表名 ADD CONSTRAINT 约束名 FOREIGN KEY 字段名 REFERENCES 主表名

连接：

内连接 – 表示查询结果只返回符合连接条件的记录，这种连接方式比较常用。

外连接 – 表示查询结果返回某一个表中的所有记录，以及另一个表中满足连接条件的记录。

内连接：

关键字JOIN，INNER JOIN，CROSS JOIN的含义是一样的。

我们定义一个背景：

SELECT

a.transactionno,

a.itemnumber,

a.quantity,

a.price,

a.transdate,

b.membername

FROM

demo.trans AS a

JOIN

demo.membermaster AS b ON (a.cardno = b.cardno);

可以看到外面通过公共字段cardno把两个表关联到了一起，查询出了会员消费的数据。

关键字JOIN与关键字ON配对使用，意思是查询满足关联条件demo.trans表中cardno的值与demo.membermaster表中的cardno值相等两个表中的所有记录。

外连接：

左连接，一般写成LEFT JOIN，返回左边所有记录以及右表中符合连接条件的记录。

右连接，一般写成RIGHT JOIN，返回右边表中的所有记录，以及左表中符合连接条件的记录。

SELECT

a.transactionno,

a.itemnumber,

a.quantity,

a.price,

a.transdate,

b.membername

FROM

demo.trans AS a -- 这个是左边的表

LEFT JOIN

demo.membermaster AS b ON (a.cardno = b.cardno);

我们使用右连接也可以完成一样的效果：

SELECT

a.transactionno,

a.itemnumber,

a.quantity,

a.price,

a.transdate,

b.membername

FROM

demo.membermaster AS b -- 这个是左边的表

RIGHT JOIN

demo.trans AS a ON (a.cardno = b.cardno);

关联查询的误区：

MySQL允许关联查询的时候没有外键的约束。因为外键的约束是要占用系统资源的，如果是高并发的网站的话，这是无法承担的。但是我们既然要关联查询，就说明有两张表，也就是我们的插入或者删除操作是无法用一条SQL语句完成的，也就是没有原子性。所以如果没有外键约束的话，需要在应用层解决原子性的问题，否则会出现业务上的错误。

where和having有什么区别？

WHERE是直接对表中的字段进行限定。HAVING则需要根据分组关键字GROUP BY一起使用，通过对分组字段或分组计算函数进行限定。

WHERE关键字的特点是，直接用表的字段对数据集进行筛选。如果需要通过关联查询从其他的表获取需要的信息，那么执行的时候，也是先通过WHERE条件进行筛选，用筛选后的比较小的数据集进行连接。这样一来，连接过程中占用的资源比较少，执行效率也比较高。

HAVING的查询过程：

我们需要把所有的信息都准备好，包括从关联表中获取需要的信息，对数据集进行分组，形成一个包含所有需要的信息的数据集合。接着，再通过HAVING条件的筛选，得到需要的数据。

也就是说：

如果需要通过连接从关联表中获取需要的数据，WHERE是先筛选后连接，而HAVING是先连接后筛选。这一点就已经决定了WHERE比HAVING更高效。因为WHERE可以先筛选，用一个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高。HAVING则需要先把结果集准备好，也就是用没有被筛选的数据集进行关联，然后对这个大的数据集进行筛选，占用资源多，效率低。

WHERE可以直接把表中的字段作为筛选条件，但是不能使用分组中的计算函数作为筛选条件。HAVING必须要和GROUP BY配合使用，可以把分组计算的函数和分组字段作为筛选条件。

因此这也决定了HAVING可以完成WHERE不可能完成的任务。因为在查询语法结构中，WHERE在GROUP BY之前，所以无法对分组结果进行筛选。HAVING在GROUP BY之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选。

解释一下EXPLAIN之后的字段：

type=range：使用索引查询特定范围的数据记录

rows=5411：表示需要读取的记录数

possible_keys=index_trans：表示可以选择的索引是index_trans

key=index_trans：表示实际选择的索引是index_trans

extra=Using index condition；Using where；Using MRR；这里面的信息对SQL语句的执行细节做了进一步解释，包含了3层含义：

执行时使用了索引

执行时通过WHERE条件进行了筛选

使用了顺序磁盘读取的策略

临时表：