自学软件测试day13——数据库

1、列的别名concat()函数

1、表列名比较繁琐，为了简化和方便使用，给列取别名

2、有些应用中，存储在表中的数据不是应用所需要的，需要创建新的字段来表示数据库中的数据进行计算机或函数运算转换后的数据

列1：使用concat()函数进行字符拼接，给查询出来的数据列指定新名字

例如：使用新列名，并进行字符拼接

selectconcat(flower_name,'(',flower_color,')')fromflowersystem.flower;

例如：创建一个新的列名

selectconcat(flower_name,'(',flower_color,')')as name_colorfromflowersystem.flower;

select flower_id,flower_name,flower_new_price*2as new_pricefromflowersystem.flowerwhereflower_new_price<200;

2、文本处理函数

concat()、upper()、lower()、substring()

2.1 Upper 函数和 Lower函数

Upper 函数将小写字母转为大写字母

例如：将flower_label列的字符全部转换成大写字母

select flower_name,flower_label,upper(flower_label)fromflowersystem.flower;

Lower 函数将大写字母转为小写字母

例如：将flower_label列的字符全部转换成小写字母

select flower_name,flower_label,lower(flower_label)fromflowersystem.flower;

2.2 Length 函数

使用Length函数返回指定列的长度

例如：获取鲜花名的字节长度（UTF-8：一个汉字占3个字节,GBK：一个中文汉字占2个字节） select flower_name,flower_stuff,length(flower_name) as sizefromflowersystem.flower;

例如：获取鲜花名的字符长度

select flower_name,flower_stuff,CHAR_LENGTH(flower_name) as sizefromflowersystem.flower;

例如：去掉字符左边的空格

selectflower_name,flower_stuff,CHAR_LENGTH(ltrim(flower_name)) as sizefromflowersystem.flower;

例如：去掉字符右边的空格

select flower_name,flower_stuff,CHAR_LENGTH(rtrim(flower_name)) as sizefromflowersystem.flower;

例如：去掉字符左边和右边的空格

select flower_name,flower_stuff,CHAR_LENGTH(trim(flower_name)) as sizefromflowersystem.flower;

2.3 substring 函数

使用 substring 函数返回串中的子串

例如：从鲜花名截取第3个字符之后的所有字符

select flower_name,flower_stuff,substring(flower_name,3) as namefromflowersystem.flower;

例如：从鲜花名截取第2个字符开始往后取3个字符的数据

select flower_name,flower_stuff,ltrim(substring(flower_name,2,3))as name,length(substring(flower_name,2,3))fromflowersystem.flower;

3、日期与时间处理函数

获取当前日期时间函数

curdate() 返回当前日期

curtime() 返回当前时间

now() 返回当前日期时间

例如：selectcurdate(),curtime(),now();

时间日期计算函数

AddDate() 增加一个日期（天、周等）

例如：计算当前日期35天后的日期

selectcurdate(),adddate(curdate(),35);

例如：计算鲜花30天后的下架日期

select flower_name,flower_shelves_time,adddate(flower_shelves_time,30) as later_timefromflowersystem.flower;

DateDiff() 计算两个日期之差

例如：计算当前日期离2023年10月1日还有多久

selectcurdate(),datediff('2023-10-1',curdate());

例如：查找制定年月的鲜花上架的数据（2023年5月的数据） ​

select flower_name,flower_shelves_timefromflowersystem.flowerwhereyear(flower_shelves_time)=2023 andmonth(flower_shelves_time)=5;

4、聚集函数

4.1 AVG 函数

AVG函数计算列的平均值，AVG() 统计所有的行，但不包括值为null的行

例如：计算鲜花名为‘虞美人’的平均价格

select flower_name,avg(flower_new_price) as avg_pricefromflowersystem.flowerwhereflower_name="虞美人";

4.2 COUNT 函数

使用 COUNT() 确定表中行的数目

例如：计算鲜花中flower_color不为空的数目

selectcount(*)fromflowersystem.flowerwhereflower_color is not null;

例如：计算鲜花名为‘虞美人’的行数

select flower_name,count(*)fromflowersystem.flowerwhereflower_name="虞美人";

4.3 MAX 函数和 MIN 函数

MAX() / MIN() 返回指定列中的最大值 / 最小值，要求指定列名

例如：查询flower表中鲜花名为‘虞美人’的价格最高的数据

select flower_name,max(flower_new_price)fromflowersystem.flowerwhereflower_name="虞美人";

例如：查询flower表中所有鲜花价格最低的数据

selectmin(flower_new_price)fromflowersystem.flower;

例如：计算flower表中所有鲜花价格的总和

selectsum(flower_new_price)fromflowersystem.flower;

例如：计算flower表中名为 ‘ 虞美人 ’ 的鲜花价格的总和

selectsum(flower_new_price)fromflowersystem.flowerwhereflower_name='虞美人';

4.4 函数组合使用

select 语句中可根据需要使用多个函数

例如：在select中使用4个函数

selectcount(*) as fl_count,max(flower_new_price) as max_price,min(flower_new_price) as min_price,avg(flower_new_price) as avg_price fromflowersystem.flower;

二、MySQL 分组查询与子查询

1、查询结果的分组操作

分组允许把数据表中的数据按照某一个或几个字段，分为多个组，字段值相同的为一组。分组是为了便于对每个组进行聚集计算

分组是在 select 语句的 group by 子句中建立的

注意：group by只是创建分组，但并不保证分组里面的数据的排列顺序，需要使用 order by 子句对分组里面的数据进行排序

使用 group by 语句是对 select 查询的结果进行分组，以便统计

例如：把鲜花按照flower_category_id分组，并统计各组的数量

select flower_category_id,count(*) as category_numfromflowersystem.flowergroup byflower_category_id;

例如：把鲜花按照flower_category_id和 flower_label 进行分组，并统计各组的数量

select flower_category_id,flower_label,count(*) as category_numfromflowersystem.flowergroup byflower_category_id,flower_labelorder bycategory_numdesc;

例如：把鲜花按照名字进行分组，并查找分组后各组价格的平均值

select flower_name,avg(flower_new_price) as category_numfromflowersystem.flowergroup byflower_name;

2、过滤分组

除了能用 group by 分组数据外，MySQL 还允许对分组指定条件，规定包含哪些分组，排除哪些分组

MySQL 使用 having 子句来完成该操作

where 子句过滤指定的行，having 子句过滤指定的分组

例如：把鲜花价格大于200的按照鲜花种类 id 进行分组，然后过滤出种类数量中大于等于2的分组-

select flower_category_id,count(*) as category_numfromflowersystem.flowerwhereflower_new_price>200group byflower_category_idhavingcategory_num>=2;

注意：

1、select 后面只能查看 group by 子句后有的列，和聚集计算的列

2、Group by 可以根据多个列进行分组，多个列即多个列的值相同

3、有 having 一定要有 group by ，但有 group by 不一定要有 having

3、select 中子句的顺序

select 语句中使用的字句必须按照一定的次序，下图列出来各个字句在 select 语句中出现的次序

4、子查询

子查询是嵌套在其他查询中的查询

查找订单表中鲜花 id 为 3 的所有订单的编号

selectorder_nofromflowersystem.orderwhereorder_flower_id=3;

再根据得到的订单编号在已付款表中查找所有客户 id

selectpay_user_idfromflowersystem.paywherepay_order_numberin(202302121221049719,202302121221258818);

根据得到的客户 id 在地址表中查找出客户的电话号码和地址等信息

select*fromflowersystem.addresswhereaddress_user_idin(2,3);

子查询一步到位

select*fromflowersystem.addresswhereaddress_user_idin(selectpay_user_idfromflowersystem.paywherepay_order_numberin(selectorder_nofromflowersystem.orderwhereorder_flower_id=3));

5、布尔值 exists 判断（false、true）

例如：查找在用户表中没下单买过鲜花的用户

select*fromflowersystem.userwherenot exists(selectorder_user_idfromflowersystem.orderwhereorder.order_user_id=user.user_id);

例如：查找在用户表中下单买过鲜花的用户

select*fromflowersystem.userwhereexists(selectorder_user_idfromflowersystem.orderwhereorder.order_user_id=user.user_id);

6、数据库增删改操作

6.1 插入完整的行

在插入行时，MySQL 将用 values 列表中的相应值填入列表中的对应项。VALUES 的第一个值对应与第一个指定的列名。因为提供了列名，VALUES 必须以其指定的次序匹配指定的列名，不一定按各个列出现在实际表中的次序，优点是，即使表的结构变化，此 insert 语句仍然能正确工作。

格式为：insert into 表名 ( 列名 ) values ( 各个列的值 ) ;

例如：

insert intotest01.student ( name,stu_id,phone,email )values( '张三','201901','17312345678','123@126.com' );

或：按默认字段插入表中

insert intotest01.studentvalues(2,'张三','201905','17312345678','123@126.com');

例如：某些字段允许为空时

insert intotest01.student(id,name,stu_id)values(3,'李四','201903');

6.2 插入多行

单条 insert 语句有多组值，每组值用一对圆括号括起来，用逗号分隔。

例如：

insert into test01.student (id,name,stu_id,phone,email)values(6,'AA','201906','17312345678','123@126.com'),

(7,'BB','201907','17312345678','123@126.com'),

(8,'CC','201908','17312345678','123@126.com');

6.3 插入其他查询得到的数据

insert 可以插入由 select 查询出来的值。该方法由一条 insert 语句和一条 select 语句组成。

格式：insert into 表A (列1,列2) select 列1,列2 from 表B;

insert intotest01.student(name,stu_id)selectXM,XH from test01.xs;

格式：表A 的数据全部插入表B;

create tabletest01.studentB ​ (id int,name varchar(45), ​ stu_id varchar(45) primary key, ​ phone varchar(45), ​ email varchar(45));

insert intotest01.studentBselect* from test01.student;

注意：插入的字段与查询的字段类型必须要一致

6.4 更新数据

为了更新表中的数据，可使用 update 语句。

Update 语句可以更新表中特定的行；也可以更新表中所有的行。

注意：update 语句如果后面不跟 where 语句的话，将修改表中所有的行，使用的时候需要小心，以免产生错误的修改。

格式：update 表名 set 列名1 = 新值1, 列名2 = 新值2, ... where 条件;

例如：修改学号为 201901 的学生的电话和邮箱

updatetest01.studentsetphone='15912345678',email='201901@126.com'wherestu_id='201901';

6.5 删除数据

从一个表中删除数据，使用 delete 语句，delete 使用方法有以下两种：

1、 从表中删除特定的行（通过 where 字句指定条件）

2、从表中删除所有的行（不带 where 子句）

格式：delete from 表名 where 条件;

set sql_safe_updates=0; 取消安全模式

delete fromtest01.studentb; —— ‌表结构保留，但数据全清（慎用！）

delete fromtest01.studentbwhereid is null; —— ‌删除满足条件的记录

delete fromtest01.studentbwherename like '%A%';

6.6 TRUNCATE语句

使用 truncate table 语句删除整个表中的行，而且速度比 delete 语句快，truncate table 语句是先删除整张表，然后重新创建一个空表

格式：truncate table 表名;

truncate tabletest01.studentb;

注意：truncate 无 log 追踪;

三、表联结

1、联结

SQL 最强大的功能之一就是能在数据检索查询的执行中联结起来

数据是存储在关系表中的，关系表的设计原则是保证把信息分解为多个表，一类数据一个表，各表通过某些常用的值互相联结。

2、笛卡尔乘积

笛卡尔积是由没有联结条件的表关系返回的结果，检索出的行的数目将是第一个表中的行数乘以第二个表中的行数

3、创建联结

创建联结，指定要联结的所有表以及他们如何关联的

3.1 内连接 inner join / join on

内部联结也称为等值联结，它基于两个表之间的相等测试。

格式：select * from 表A inner join / join 表B on 连接条件

例如：通过用户表和订单表进行内连接查询出用户的信息（通过一个公共字段）

(包括用户的个人和订单信息，只有这两个表中同时存在用户的id才会展示出相关数据)

select*fromflowersystem.user ajoinflowersystem.order bona.user_id = b.order_user_id;

或：selecta.user_id,b.order_user_id,a.user_name,a.user_phone,b.order_flower_idfromflowersystem.userainner joinflowersystem.orderbona.user_id = b.order_user_id;

或：selecta.user_id,b.order_user_id,a.user_name,a.user_phone,b.order_no,b.order_flower_idfromflowersystem.useras a,flowersystem.orderas bwherea.user_id = b.order_user_id;

3.2 左连接 left join on

外连接分为：左连接和右连接

以左边表为基准，按照过滤条件查找右边表的记录，如果匹配到，那么就组合成一行，并显示结果；如果没有匹配到，那么只显示左边表的字段，右边表中不存在的字段用空值来表示

左连接以左边表为主表，展示主表所有的记录，副表与其相匹配的数据会展示，如果没有的匹配的数据以null的形式展示出

例如：通过用户表和订单表进行左连接查询出用户的信息(展示用户表的所有信息以及订单信息，若用户没有订单信息的用null展示出来)

select*fromflowersystem.useraleft joinflowersystem.orderbona.user_id = b.order_user_id;

3.3 右连接 right join on

右连接与左连接相反，以右边为主表

作用和左连接刚好相反，右边表为基准，去匹配左边的表，如果左边的表字段为空，那么就用空值来表示。

例如：select*fromflowersystem.useraright joinflowersystem.orderbona.user_id = b.order_user_id;

3.4、自联结

自联结为在同一个表中做联结操作

例如：假如发现某鲜花（其 name 为 向阳而生）存在问题，因此想知道该鲜花的供应商生产的其他鲜花是否也存在问题。此查询要求先找到生产 name 为 向阳而生 的鲜花的供应商（flower_label），然后找出这个供应商生产的其他物品。

select flower_id,flower_name,flower_label from flowersystem.flower where flower_label in (select flower_label from flowersystem.flower where flower_name='向阳而生');

自联结：select a.flower_id,a.flower_name,a.flower_labelfromflowersystem.flower a,flowersystem.flower bwherea.flower_label=b.flower_label and b.flower_name='向阳而生';

例如：假如发现物品（其 ID 为 DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在问题。此查询要求先找到生产 ID 为 DTNTR 的物品的供应商，然后找出这个供应商生产的其他物品。

select*fromcrashcourse.productswherevend_idin(selectvend_idfromcrashcourse.productswhereprod_id='DTNTR');

自联结：select*fromproducts a,products bwherea.vend_id=b.vend_idandb.prod_id='DTNTR';

3.5 多表联结

例如：显示编号为20005的订单中的物品。订单物品存储在 orderitems 表中，每个产品按其产品 ID 存储，它引用 product 表中的产品。这些产品通过供应商 ID 联结到 vendors 表中相应的供应商，供应商 ID 存储在每个产品的纪录中。这里的 from 子句列出了3个表，而 where 子句定义了这两个联结条件，而第三个联结条件用来过滤出订单 20005 中的物品。

select p.prod_name,v.vend_name,p.prod_price,quantity from orderitems o,products p,vendors v where o.prod_id=p.prod_id and p.vend_id=v.vend_id and o.order_num='20005';

多表联结性能问题

注意：MySQL 在运行时关联指定的每个表以处理联结，这种处理可能非常消耗资源，因此不要联结不必要的表。联结的表越多，性能下降越厉害

4、连接中 on / where 的区别

where 是在 left jion 结果 之后进行的筛选

from → where → group by → having → order by

left join 属于 from 这个步骤

例如：select * from products a join products b on a.vend_id=b.vend_id and b.prod_id='DTNTR' where a.prod_id='DTNTR';

注意点：ON 后面的筛选条件主要是针对关联表的（对于主表的筛选条件不适应）；对于主表的筛选条件应该放在 where 后面，不应该放在 ON 后面。

例如1：select * from flowersystem.user a left join flowersystem.order b on a.user_id = b.order_user_id and a.user_id=1;

例如2：select * from flowersystem.user a left join flowersystem.order b on a.user_id = b.order_user_id where a.user_id=1;