数据库总结

复习

复习：建表（create table tablename(表字段名，字段类型);）新增数据（insert into table
(列名1，列名2) values(对应的数据1，value2); ）；查询表中数据（select * from
table;）修改表中数据（update tablename set fieldname =value）删除表中数据（delete from
table where…;）

删除表：drop table tablename; truncate tablename;

条件查询；模糊查询；联表查询；子查询；聚合函数；分组查询；排序

数据库在执行页面删除数据功能时，做了什么操作？ 感觉用：delete from table where name=‘bug’;
实际上：未进行物理删除，进行逻辑删除-加一个标记删除字段，设置为1的 是已删除。

接口变化后如何处理？

实例：假设12306，接口有变化**（参数个数类型变化）**时（包括频率），我们这系统如何应对。
思路：fiddler抓包，修改响应，网络速度（弱网）
通过第三方工具，截获响应，解析返回数据。联合前端开发和功能开发添加相应的参数和类型。

数据完整性：
不正确不能 存入数据库
超长数据是 否能保存到数据库
为什么设置数据长度？
在前端开发会限制超长数据，传入数据库。即在代码端，检测返回数据结果。而不是前端无限制，传到数据库，返回数据库，表，无法存储该超长数据。有安全隐患，会暴露数据库，容易被sql注入攻击，对数据库造成危险损失。

建立表：

CREATE TABLE `testdata` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `payload` json DEFAULT NULL,
  `response` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查询是否建立成功，查看表结构的类型是否正确

 desc testdata;

插入数据：

INSERT INTO testdata (payload, response) VALUES ('{"id": 1, "name": "linda"}', '{"code":40000 ,"msg": "ok"}');

查询数据

SELECT id, payload->'$.id', payload->'$.name' FROM testdata;

插入数据

INSERT INTO testdata (payload, response) VALUES (JSON_OBJECT("id", 2, "name", "sevenriruby"), JSON_ARRAY(1, 3, 5));

查询id response索引为0 的数据和索引为2的数据在testdata表中

SELECT id, response->'$[0]', response->'$[2]' FROM testdata;

数据库建议：不建议把不同类型的数据存储到一张表中，一列中
从testdata表中查询条件为name是linda的数据

select * from testdata where payload->'$.name'='linda';

查询一下response中的数据是否包括 3

select * from testdata where response->'$[1]'=3;
select * from testdata where response like '%3%';

payload（字段名）->’$.name（keyname）’ 具体的字典内容name这个key的调用方式

sql注入

SQL 注入的主要形式包括直接将代码插入到与 SQL 命令串联在一起并使其得以执行的用户输入变量。
一种间接的攻击会将恶意代码注入要在表中存储或作为元数据存储的字符串。在存储的字符串随后串连到一个动态 SQL 命令中时，将执行该恶意代码。

开发有应对方案，不动态拼接，预处理，把sql先写出来，把变量使用？占位，再替换的方式实现。

INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)
优势在于归纳为：一次编译、多次运行，省去了解析优化等过程；此外预编译语句能防止 SQL 注入。

怎么实现大量的数据测试？

场景一：验证，购物车添加商品100个是否有翻页。
场景二：性能测试中数据量的测试，100万条数据的辅底数据

删除存储过程，如果这个名字的已存在
DROP PROCEDURE if exists insert_data_pro;
由于语句较多，使用这个告诉mysql，看看$$就表示语句结束了。
DELIMITER $$
建立存储过程名字叫 insert_data_pro
CREATE
 PROCEDURE insert_data_pro()
    BEGIN    开始
     DECLARE i INT;        定义 i
     SET i=0;        设置初始值为0
     WHILE  i>=0 && i<= 10000 DO         循环 插入10000条数据，可以变化。
           INSERT INTO big_data(name1,age,email)values(concat(‘linda’,i),rand()*50,concat('linda',i,'@qq.com'));
写的sql，concat是将多个str连在一起。
           SET i=i + 1;      
     END WHILE;       结束循环
    END$$       存储过程结束
DELIMITER ;  恢复设置
下面是调用存储过程
call insert_data_pro();

数据联合调优

ui—网络—服务器-web—db-server
数据库性能测试监控方面
大数据量时，验证超时，锁表，重启；

数据库性能调优方面
分库分表，主从复制，配置参数，SQL调优策略：索引，慢查询等

select * from employees where emp_no=89500
select * from employees where first_name like '%tom%';

1、第一条emp_no是索引列，速度快；
2、第二条first_name没有索引，模糊查询。

查询慢的原因：explain select * from employees where first_name like ‘%tom%’;

原因 select_type ：simple 单表简单查询 type:all 最糟糕类型，rows：你查询了多少行。extra:where。
为什么查询type（all）慢？

all
这便是所谓的“全表扫描”，如果是展示一个数据表中的全部数据项，倒是觉得也没什么，如果是在一个查找数据项的sql中出现了all类型，那通常意味着你的sql语句处于一种最原生的状态，有很大的优化空间。
为什么这么说呢？因为all是一种非常暴力和原始的查找方法，非常的耗时而且低效。用all去查找数据就好比这样的一个情形：S学校有俩万人，我告诉你你给我找到小明，然后你怎么做呢！你当然是把全校俩万人挨个找一遍，即使你很幸运第一个人便找到了小明，但是你仍然不能停下，因为你无法确认是否有另外一个小明存在，直到你把俩万人找完为止。所以，基本所有情况，我们都要避免这样类型的查找，除非你不得不这样做。

index
这种连接类型只是另外一种形式的全表扫描，只不过它的扫描顺序是按照索引的顺序。这种扫描根据索引然后回表取数据，和all相比，他们都是取得了全表的数据，而且index要先读索引而且要回表随机取数据，因此index不可能会比all快（取同一个表数据），但为什么官方的手册将它的效率说的比all好，唯一可能的原因在于，按照索引扫描全表的数据是有序的。这样一来，结果不同，也就没法比效率的问题了。

所以说如果type为all或者index，都应对其进行优化。甚至我们可以将其理解相当于索引无效

刚到公司，你应该了解哪些事？

1、上级是谁？有哪些领导，直接领导，我负责做什么？（职位岗位说明）提供的资源有哪些？注意点别的什么？领导还有什么交待。
2、公司组织架构图，主营业务，客户

OA是什么？
企业—办公系统

刚到一个项目组，担任测试应该了解哪些事？

有需求吗？
有测试用例吗？
测试计划有吗？
开发是谁？
好不好沟通？
业务框架？
业务流程 业务文件？

需求分析的意义？

需求—需求评审：
开发（ui,架构师），测试，项目经理，产品经理，客户代表，甲方，用户。相关人员
评审的作用？
让其更完善，定需求，签字！！达成一致

合理分析需求要求？
每月1-15号是征期，上传要上传文件
需求是否有二义性，是否有缺失遗漏。

没在规定时间 上传怎么办？
提前上传怎么处理的？
国税局
节假日怎么传？
顺延

作业：

手机号存成什么类型，长度多长？

将手机号定义为字符串，用 char(11) 来存储手机号码.
另外，如果确定是11位的可以用char，要是长度，格式不确定，比如带+86格式的手机号什么的，用varchar，长度设长一点，拓展性更好。

mysql的int是4byte，1byte =8bit，所以int类型存储空间是32位，并且符号位占一位，所以int的数据范围为[-2^31~231-1]，也就
是-2147483648~2147483647，此外int(11)的11是只mysql的int类型显示的宽度。手机号是11位，共五个字节，所以用int存储手机号会丢失1字节，而且并不是丢失一位剩下10位这种情况。数据会被转换成二进制数，并且丢失超出int类型位数的部分。

字符集是哪个？需要注释吗？

字符集就是字符和编码的集合，常用的中文字符集是gbk，英文字符集是ASCII 多种字符在一个字符集里，常用的UTF8
mysql内部存储字符集有4种。分别是服务器字符集，数据库字符集，表字符集，列字符集，他们的优先级顺序是列字符集 > 表字符集 > 数据库字符集 > 数据库字符集，因为数据最终存放在列上。我们常用的字符集是数据库字符集和表字符集，服务器级别一般忽略 。如果列的字符集没有设置，那么列的字符集就会继承表的字符集
需要声明，对于不同的存储业务需要选择不同的字符集，这样可以保证存储效果更佳。

工作中使用存储过程、视图、触发器、Event？
存储过程是MySQL5.X版本新增的功能，简单来说，就是为以后的使⽤⽽保存 的⼀条或多条MySQL语句的集合，有点像子查询，但是子查询只是一条SQL语句而已。

储存过程是⼀组为了完成特定功能的SQL语句集，经过编译之后存储在数据库中，在需要时直接调⽤。
存储过程就像脚本语⾔中函数定义⼀样。
为什么要使⽤存储过程?

优点:

• 可以把⼀些复杂的sql进⾏封装,简化复杂操作
• 保证了数据的完整性,防⽌错误
• 简单的变动只需要更改存储过程的代码即可
• 提⾼性能。因为使⽤存储过程⽐使⽤单独的SQL语句要快。(预先编译)

缺点:

• 存储过程的编写⽐SQL语句复杂
• ⼀般可能还没有创建存储过程的权限,只能调⽤

个⼈观点:

• 业务逻辑不要封装在数据库⾥⾯,应该由应⽤程序(JAVA、Python、PHP)处理。
• 让数据库只做它擅⻓和必须做的，减少数据库资源和性能的消耗。
• 维护困难，⼤量业务逻辑封装在存储过程中，造成业务逻辑很难剥离出来。动A影响B。
• ⼈员也难招聘，因为既懂存储过程，⼜懂业务的⼈少。使⽤困难。
• 在电信、银⾏业、⾦融⽅⾯以及国企都普遍使⽤存储过程来熟悉业务逻辑，但在互联⽹中相对较少。
  触发器是MySQL响应写操作(增、删、改)⽽⾃动执⾏的⼀条或⼀组定义在BEGIN和END之间的MySQL语句。

触发器：
可以理解为提前定义好⼀个或⼀组操作,在指定的SQL操作前或后来触发指定的SQL⾃动执⾏，触发器就像是JavaScript中的事件⼀样。

举例:
定义⼀个update语句,在向某个表中执⾏insert添加语句时来触发执⾏,就可以使⽤触发器

触发器语法：
注意:如果触发器中SQL有语法错误,那么整个操作都会报错

CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt

说明：

trigger_name：触发器名称

trigger_time:触发时间，可取值：BEFORE或AFTER

trigger_event：触发事件，可取值：INSERT、UPDATE或DELETE。

tb1_name：指定在哪个表上

trigger_stmt：触发处理SQL语句。

视图

视图是虚拟的表。与包含数据的表不⼀样，视图只包含使⽤时动态检索数据的查询。

视图仅仅是⽤来查看存储在别处的数据的⼀种设施或⽅法。

视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。

在添加或更改这些表中的数据时，视图将返回改变过的数据。

因为视图不包含数据，所以每次使⽤视图时，都必须处理查询执⾏时所需的任⼀个检索。

如果你⽤多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降得很厉害。

视图的作⽤

• 重用SQL
• 简化复杂SQL。在编写查询后，可以方便地重用它而不必知道它的基本查询细节
• 使用表的注册部分而不是整个表
• 保护数据。给以给用户授予表的特定部分的访问权限而不是整个表的访问权限
• 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。
• 注意：视图不能有索引，可不能有关联的触发器和默认值

EVENT

事件（event）是mysql在相应的时刻调用的过程式数据库对象。一个事件可调用一次，也可周期性的启动，它由一个特定的线程来管理的，也就是所谓的“事件调度器”。
事件和触发器类似，都是在某些事情发生的时候启动。当数据库上启动一条语句的时候，触发器就启动了，而事件是根据调度事件来启动的。由于他们彼此相似，所以事件也称为临时性触发器。
事件取代了原先只能由操作系统的计划任务执行的工作，而且mysql事务调度器可以精确到没秒钟执行一个任务，而操作系统的计划任务（如：Linux下的cron或windows下的任务计划）只能精确到每分钟执行一次。

事件的优缺点
优点：

• 一些对数据定时性操作不再依赖外部程序，而直接使用数据库本身提供的功能。
• 可以实现每秒钟执行一个任务，这在一些对实时性要求高的环境下就非常实用了。
  缺点：
• 定时触发，不可以调用。

创建事件：
一条create event语句创建一个事件。每个事件由两个主要部分组成，第一部分是事件调度（event schedule），表示事件何时启动以及按什么频率启动，第二部分是事件动作（event action），这是事件启动时执行的代码，事件的动作包含一条sql语句，它可能是一个简单的insert或者update语句，也可以是一个存储过程或者begin…end语句块，这两种情况下允许我们执行多条sql。
一个事件可以是活动（打开）的或者停止（关闭）的，活动意味着事件调度器检查事件动作是否必须调用，停止意味着事件的声明存储在目录中，但调度器不会检查它是否应该应用。在一个事件创建之后，它立即变味活动的，一个活动的事件可以执行一次或者多次。

主外键做什么，实际中你们用吗？

什么是主键、外键：
关系型数据库中的一条记录中有若干个属性，若其中某一个属性组(注意是组)能唯一标识一条记录，该属性组就可以成为一个主键
比如
学生表(学号，姓名，性别，班级)
其中每个学生的学号是唯一的，学号就是一个主键
课程表(课程编号,课程名,学分)
其中课程编号是唯一的,课程编号就是一个主键
成绩表(学号,课程号,成绩)
成绩表中单一一个属性无法唯一标识一条记录，学号和课程号的组合才可以唯一标识一条记录，所以 学号和课程号的属性组是一个主键

	主键	外键	索引
定义：	唯一标识一条记录，不能有重复的，不允许为空	表的外键是另一表的主键, 外键可以有重复的, 可以是空值	该字段没有重复值，但可以有一个空值
作用：	用来保证数据完整性	用来和其他表建立联系用的	是提高查询排序的速度
个数：	主键只能有一个	一个表可以有多个外键	一个表可以有多个惟一索引

索引怎么用？
索引是帮助MySQL高效获取数据的数据结构。
索引非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。索引能够轻易将查询性能提高好几个数量级，总的来说就是可以明显的提高查询效率。

存储过程用吗？
一，什么是存储过程？
简单的说，就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，有点类似于应用程序的一个功能函数。
存储过程跟触发器有点类似，都是一组SQL集，但是存储过程是主动调用的，且功能比触发器更加强大，触发器是某件事触发后自动调用；
二、存储过程特性

• 有输入输出参数，可以声明变量，有if/else, case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能；
• 函数的普遍特性：模块化，封装，代码复用；
• 速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤；
  基于以上特性，所以对于一些性能要求高，业务复杂的模块可以写到存储过程中，应用层直接调用即可。

什么时候用TEXT、BLOB类型？大文件或者大照片怎么存的？
BLOB，二进制大对象（字节流）。可以用来存储图片，声音和视频等二进制文件。没有字符集的说法。
TEXT，文本大对象（字符流）。可以用来存储大量的字符串，可以理解为超大的char或者varchar类型。由于是存储字符，所以有字符集的说法。

一、主要差别
TEXT与BLOB的主要差别就是BLOB保存二进制数据，TEXT保存字符数据。目前几乎所有博客内容里的图片都不是以二进制存储在数据库的，而是把图片上传到服务器然后正文里使用标签引用，这样的博客就可以使用TEXT类型。而BLOB就可以把图片换算成二进制保存到数据库中。

二、类型区别 BLOB有4种类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。 类型
大小(单位：字节) TinyBlob 最大 255个字符 Blob 最大 65K MediumBlob 最大 16M LongBlob 最大
4G
实际使用中根据需要存入的数据大小定义不同的BLOB类型，除了类型对后面存取文件大小有限制，还要修改mysql的配置文件。Windows、linux基本一样通过修改文件my.ini或my.cnf文件，在文件中增加
max_allowed_packet=1M（就是最大1M） 需要注意的是：如果你存储的文件过大，数据库的性能会下降很多。

TEXT也有4种类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些类型同BLOB类型一样，有相同的最大长度和存储需求。

三、字符集 BLOB列没有字符集，并且排序和比较基于列值字节的数值值。TEXT列有一个字符集，并且根据字符集的校对规则对值进行排序和比较

四、大小写 在TEXT或BLOB列的存储或检索过程中，不存在大小写转换，都一样！

五、严格模式
运行在非严格模式时，如果你为BLOB或TEXT列分配一个超过该列类型的最大长度的值值，值被截取以保证适合。如果截掉的字符不是空格，将会产生一条警告。使用严格SQL模式，会产生错误，并且值将被拒绝而不是截取并给出警告。

六、其它 当保存或检索BLOB和TEXT列的值时不删除尾部空格。

对于BLOB和TEXT列的索引，必须指定索引前缀的长度。

BLOB和TEXT列不能有默认值。

当排序时只使用该列的前max_sort_length个字节。max_sort_length的 默认值是1024。

当你想要使超过max_sort_length的字节有意义，对含长值的BLOB或TEXT列使用GROUP BY或ORDER
BY的另一种方式是将列值转换为固定长度的对象。标准方法是使用SUBSTRING函数。

BLOB或TEXT对象的最大大小由其类型确定，但在客户端和服务器之间实际可以传递的最大值由可用内存数量和通信缓存区大小确定。你可以通过更改max_allowed_packet变量的值更改消息缓存区的大小，但必须同时修改服务器和客户端程序。

你们使用什么引擎？

存储引擎字面如何理解？
其实很多名词，把他们拆开的意思组合起来，就可以大致明白组合成的名词是什么意思。就拿存储引擎来说，存储在计算机中的意思是将数据保存到某种介质中，并且保证数据的正常访问。而引擎的是指发动机的核心。

组合起来的意思大致是保存数据的核心技术，也就是说，存储引擎是服务于存储服务的，通过存储引擎将数据保存。就跟计算机如何将数据保存到磁盘中一样，在数据库中，存储引擎的意思就是通过何种引擎将数据存储在磁盘中。
数据库存储引擎
在数据库中，存储引擎是数据库中的底层套件，在创建表中使用，指定通过何种引擎存储数据，MySQL数据库默认使用的存储引擎为InnoDB。

不同的存储引擎有不同的存储机制，并且支持的功能也不相同。对于存储引擎的选择，基本上都是根据需求来定的。

InnoDB：MySQL 8.0中的默认存储引擎。
InnoDB是用于MySQL的事务安全（兼容ACID）的存储引擎，具有提交，回滚和崩溃恢复功能以保护用户数据。
InnoDB行级锁定（无需升级为更粗粒度的锁定）和Oracle风格的一致非锁定读取可提高多用户并发性和性能。InnoDB将用户数据存储在聚集索引中，以减少基于主键的常见查询的I
/ O。为了保持数据完整性， InnoDB还支持FOREIGN KEY引用完整性约束。

MyISAM：这些表占用的空间很小。 表级锁定 限制了读/写工作负载中的性能，因此它通常用于Web和数据仓库配置中的只读或只读工作负载中。

Memory：将所有数据存储在RAM中，以便在需要快速查找非关键数据的环境中进行快速访问。该发动机以前称为HEAP发动机。它的用例正在减少；InnoDB借助其缓冲池内存区域，它提供了一种通用且持久的方式来将大多数或所有数据保留在内存中，并
NDBCLUSTER为大型分布式数据集提供了快速的键值查找。

CSV：其表实际上是带有逗号分隔值的文本文件。CSV表允许您以CSV格式导入或转储数据，以便与读取和写入相同格式的脚本和应用程序交换数据。由于CSV表未建立索引，因此通常InnoDB在正常操作期间将数据保留在表中，并且仅在导入或导出阶段使用CSV表。

Archive：这些紧凑的，未索引的表旨在用于存储和检索大量很少参考的历史，归档或安全审核信息。

Blackhole：Blackhole存储引擎接受但不存储数据，类似于Unix
/dev/null设备。查询总是返回一个空集。这些表可用于将DML语句发送到从属服务器的复制配置中，但是主服务器不保留其自己的数据副本。

NDB（也称为 NDBCLUSTER）：此集群数据库引擎特别适合于需要尽可能高的正常运行时间和可用性的应用程序。

Merge：使MySQL
DBA或开发人员可以在逻辑上对一系列相同的MyISAM表进行分组并将它们作为一个对象引用。适用于VLDB环境，例如数据仓库。

Federated：提供了链接单独的MySQL服务器以从许多物理服务器创建一个逻辑数据库的能力。非常适合于分布式或数据集市环境。

Example：此引擎作为MySQL源代码中的示例，说明了如何开始编写新的存储引擎。它主要是开发人员感兴趣的。存储引擎是什么都不做的 “
存根 ”。您可以使用此引擎创建表，但是不能在其中存储数据或从中检索数据。

若不对存储引擎进行限制，那么在创建表的时候可以指定任意存储引擎。

int(11),是什么意思，varchar(20)是什么意思？

varchar(20)中的20代表字符串最长宽度为20，存进库里的字符串长度超过20将报错无法进行存储

int(11)。。存储为int类型，占4个字节，非负存值为0到2的32次方，并不会因为11的限制存储范围为0到2的10次方
表示的是存储数字宽度，如果宽度不够11就会按照0进行填充。

删除命令你知道哪些？

truncate、delete与drop

你了解事务吗？

事务是一组SQL语句，要么全部执行成功，要么全部执行失败。通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)

事务的提交：COMMIT

事务的回滚：ROLLBACK

事务的关闭：CLOSE

默认情况下一个SQL语句为一个事务。