成都 网站 建设,城乡建设网站报建需要什么,wordpress 文艺主题,wordpress 根目录MySQL自带数据库
information_schema#xff1a;是MySQL自带的数据库#xff0c;主要保持MySQL数据库服务器的系统信息#xff0c;比如数据库的名称#xff0c;数据库表的名称#xff0c;字段名称#xff0c;存储权限等。
performance_schema#xff1a;是MySQL系统自…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(MD)其中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 主表名连接
内连接 – 表示查询结果只返回符合连接条件的记录这种连接方式比较常用。外连接 – 表示查询结果返回某一个表中的所有记录以及另一个表中满足连接条件的记录。
内连接
关键字JOININNER JOINCROSS JOIN的含义是一样的。
我们定义一个背景 SELECT a.transactionno,a.itemnumber,a.quantity,a.price,a.transdate,b.membername
FROMdemo.trans AS aJOINdemo.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
FROMdemo.trans AS a -- 这个是左边的表LEFT JOINdemo.membermaster AS b ON (a.cardno b.cardno);我们使用右连接也可以完成一样的效果
SELECT a.transactionno,a.itemnumber,a.quantity,a.price,a.transdate,b.membername
FROMdemo.membermaster AS b -- 这个是左边的表RIGHT JOINdemo.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之后的字段
typerange使用索引查询特定范围的数据记录rows5411表示需要读取的记录数possible_keysindex_trans表示可以选择的索引是index_transkeyindex_trans表示实际选择的索引是index_transextraUsing index conditionUsing whereUsing MRR这里面的信息对SQL语句的执行细节做了进一步解释包含了3层含义 执行时使用了索引执行时通过WHERE条件进行了筛选使用了顺序磁盘读取的策略
临时表
临时表是一种特殊的表用来存储查询的中间结果并且会随着当前连接的结果而自动删除。MySQL中两种连接表分别是内部临时表和外部临时表。
内部临时表主要用于性能优化由系统自动产生我们无法看到外部临时表通过SQL语句创建我们可以使用
CREATE TEMPORARY TABLE 表名
(字段名 字段类型,...
);临时表创建的时候需要加上格外的TEMPORARY字段临时表创建完成之后只有当前连接可见其他连接是不可见的具有连接隔离性。临时表在当前连接结束之后会被自动删除。
内存临时表和磁盘临时表
内存临时表
CREATE TEMPORARY TABLE demo.mytrans
(itemnumber int,groupnumber int,branchnumber int
) ENGINE MEMORY; (临时表存在内存中)磁盘临时表
CREATE TEMPORARY TABLE demo.mytrans
(itemnumber int,groupnumber int,branchnumber int
);内存临时表如果断电的话数据就全部消失了。 高并发系统不建议使用 视图
视图如何简化查询
创建视图
CREATE [OR REPLACE]
VIEW 视图名称 [(字段列表)]
AS 查询语句子查询嵌套在另一个查询中的查询。派生表如果我们在查询中把子查询的结果作为一共表来使用这个表就是派生表
查询的具体步骤是
通过子查询获得单品销售统计的查询结果把第一步中的查询结果作为一个派生表跟历史库存表进行连接查询获得包括销售日期商品名称销售数量和历史库存数量在内的最终结果。
使用视图的查询语句明显简单多了可读性更好也更容易维护。
如果操作视图和视图中的数据
修改视图
ALTER VIEW 视图名
AS 查询语句;查看视图
DESCRIBE 视图名删除视图要使用DROP关键字
DROP VIEW 视图名视图的优缺点
优点 因为我们可以把视图当作一张表来进行查询所以在使用视图的时候我们不用考虑视图本身是如何获取数据的里面有什么逻辑包括了多少张表有哪些关联操作而是可以直接使用。这样一来实际上就把查询模块化了查询变的简单提高了开发和维护的效率。所以你可以把那些经常会用到的查询和复杂的子查询定义成视图存储到数据库中这样可以为你以后的使用提供方便。 视图跟实际数据不一样它是存储查询语句。所以在使用的时候我们要通过定义视图的查询语句来获取结果集。而视图本身不存储数据不占用数据存储的资源。 视图具有隔离性。视图相当于在用户和实际的数据表之间加了一层虚拟表。也就是说用户不需要查询数据表可以直接通过视图获取数据表中的信息。这样不仅提高了数据表的安全性同时也通过视图把用户实际需要的信息汇总在了一起查询起来很轻松。 视图的数据结构相对独立即便实际数据表的结构发生变换我们也可以通过修改定义视图的查询语句让查询结果集里面的字段保持不变。
我们举一个例子让大家理解
假设我们有一个实际的数据表demo.goodsmaster包括商品编号、条码、名称、规格和售价等信息 缺点
数据表的更变需要及时对视图进行维护特别当视图是由视图生成的时候维护会变的非常复杂。
MySQL日志
系统出现问题应该如何及时发现
我们应该马上看MySQL的日志
通用查询日志
通用查询日志记录了所有用户的连接开始时间以及截止时间以及发给MySQL数据库服务器的所有SQL指令。
mysql SHOW VARIABLES LIKE %general%;
---------------------------------------
| Variable_name | Value |
---------------------------------------
| general_log | OFF -- 通用查询日志处于关闭状态
| general_log_file | LAPTOP-7IL73D6I.log -- 通用查询日志的名称
---------------------------------------
2 rows in set, 1 warning (0.00 sec)默认是关闭因为一旦开启通用查询日志MySQL会记录所有的连接起止和相关的SQL操作这样会消耗系统资源并且占用磁盘空间。
开启通用查询日志
set GLOBAL general_log ON;set global.general_log_file D:\Lxy;慢查询日志
慢查询日志用来记录执行时间超过指定时长的查询。它的作用是帮助我们发现那些执行时间特别长的SQL查询并且有针对性的进行优化从而提高系统的整体效率。当我们的数据库发生阻塞运行变慢的时候检查一下慢查询日志找到那些慢查询对解决问题很有帮助。
slow_query_log1 -- 表示开启慢查询日志系统将会对慢查询进行记录
slow_query_log_fileGJTECH-PC-slow.log; -- 表示慢查询日志的名称
long_query_time10 -- 表示慢查询的标准是查询执行时间超过10秒min_exmined_row_limit0 -- 表示扫描过的记录大于等于这个变量的值就算慢查询错误日志
错误日志中记录了MySQL服务器启动停止运行的时间以及系统自动启动运行和停止过程中的诊断信息包含错误警告和提示等。当我们的数据库服务器发生系统故障的时候错误日志是首选。
log-errorGJTECH-PC.err二进制日志
二进制日志主要数据库的更新事件。比如创建数据库表更新表中的数据数据更新所花费的时长等信息。通过这些信息我们可以再先数据更新的全过程。是数据恢复和数据复制的利器。
如何操作二进制日志
查看二进制日志主要有3种情况分别是查看当前正在写入的二进制日志查看所有的二进制日志和查看二进制日志种的所有数据更新事件。
查看正在写入的二进制日志的SQL语句是
SHOW MASTER STATUS;查看所有的二进制文件
SHOW BINARY LOGS查看二进制日志中所有数据更新事件的SQL语句
SHOW BINLOG EVENTS IN 二进制文件名刷新二进制日志
FLUSH BINARY LOGS这条语句的意思是关闭服务器正在写入的二进制日志文件并重新打开一个新文件文件的后缀名在现有的基础上加1.
中继日志
中继日志只是主从服务器架构存在。
回滚日志
进行事物回滚。
这些日志太过重要我们到时候会单独进行讲解。
范式
范式如何消除冗余和高效存储
第一范式所有字段都是基本数据字段不可以进一步拆分第二范式在满足第一范式的基础上还要满足数据表里面的每一条记录都是可唯一标识的。而且所有字段都必须完全依赖主键不能只依赖主键的一部分。在满足第二范式的基础上数据表中不能存在可以被其他非主键字段派生出来的字段或者说不能存在依赖于非主键字段的字段。
设计表原则
表太大了如何设计才能提高性能 数据类型优化 优化字段的数据类型。 对整数类型数据进行优化 既可以使用文本类型也可以使用整数类型的字段要使用整数类型而不是文本类型。 合理增加冗余字段以提高效率 拆分表 尽量使用非空约束。 可以节省一个比特位并且省去判断是否为空的逻辑。开发时会如果提交给数据库的数据不符合数据库的条件则会报错。保证开发时不会忽略掉该做的一些非空判断等客户保证逻辑正确安全防止sql注入
