本文系统介绍了MySQL数据库查询与操作的核心技术主要包括三部分内容1查询结果处理与函数应用涵盖文本处理、日期函数和聚合函数2分组查询与子查询技术包括分组操作、过滤分组和复杂子查询3表联结方法详细解析内连接、外连接及多表联结的实现。文章还深入探讨了数据库增删改操作、布尔值判断等实用技巧并对比了不同连接方式的性能特点为数据库开发者提供了全面的技术参考。目录一、MySQL 查询结果处理与函数1、列的别名 concat() 函数2、文本处理函数2.1 Upper 函数和 Lower函数2.2 Length 函数2.3 substring 函数3、日期与时间处理函数4、聚集函数4.1 AVG 函数4.2 COUNT 函数4.3 MAX 函数和 MIN 函数4.4 函数组合使用二、MySQL 分组查询与子查询1、查询结果的分组操作2、过滤分组3、select 中子句的顺序4、子查询5、布尔值 exists 判断false、true6、数据库增删改操作6.1 插入完整的行6.2 插入多行6.3 插入其他查询得到的数据6.4 更新数据6.5 删除数据6.6 TRUNCATE语句三、表联结1、联结2、笛卡尔乘积3、创建联结3.1 内连接 inner join / join on3.2 左连接 left join on3.3 右连接 right join on3.4、自联结3.5 多表联结4、连接中 on / where 的区别一、MySQL 查询结果处理与函数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_price200;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_price200group byflower_category_idhavingcategory_num2;注意1、select 后面只能查看 group by 子句后有的列和聚集计算的列2、Group by 可以根据多个列进行分组多个列即多个列的值相同3、有 having 一定要有 group by 但有 group by 不一定要有 having3、select 中子句的顺序select 语句中使用的字句必须按照一定的次序下图列出来各个字句在 select 语句中出现的次序4、子查询子查询是嵌套在其他查询中的查询查找订单表中鲜花 id 为 3 的所有订单的编号selectorder_nofromflowersystem.orderwhereorder_flower_id3;再根据得到的订单编号在已付款表中查找所有客户 idselectpay_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_id3));5、布尔值 exists 判断false、true例如查找在用户表中没下单买过鲜花的用户select*fromflowersystem.userwherenot exists(selectorder_user_idfromflowersystem.orderwhereorder.order_user_iduser.user_id);例如查找在用户表中下单买过鲜花的用户select*fromflowersystem.userwhereexists(selectorder_user_idfromflowersystem.orderwhereorder.order_user_iduser.user_id);6、数据库增删改操作6.1 插入完整的行在插入行时MySQL 将用 values 列表中的相应值填入列表中的对应项。VALUES 的第一个值对应与第一个指定的列名。因为提供了列名VALUES 必须以其指定的次序匹配指定的列名不一定按各个列出现在实际表中的次序优点是即使表的结构变化此 insert 语句仍然能正确工作。格式为insert into 表名 ( 列名 ) values ( 各个列的值 ) ;例如insert intotest01.student ( name,stu_id,phone,email )values( 张三,201901,17312345678,123126.com );或按默认字段插入表中insert intotest01.studentvalues(2,张三,201905,17312345678,123126.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,123126.com),(7,BB,201907,17312345678,123126.com),(8,CC,201908,17312345678,123126.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.studentsetphone15912345678,email201901126.comwherestu_id201901;6.5 删除数据从一个表中删除数据使用 delete 语句delete 使用方法有以下两种1、 从表中删除特定的行通过 where 字句指定条件2、从表中删除所有的行不带 where 子句格式delete from 表名 where 条件;set sql_safe_updates0; 取消安全模式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_labelb.flower_label and b.flower_name向阳而生;例如假如发现物品其 ID 为 DTNTR存在问题因此想知道生产该物品的供应商生产的其他物品是否也存在问题。此查询要求先找到生产 ID 为 DTNTR 的物品的供应商然后找出这个供应商生产的其他物品。select*fromcrashcourse.productswherevend_idin(selectvend_idfromcrashcourse.productswhereprod_idDTNTR);自联结select*fromproducts a,products bwherea.vend_idb.vend_idandb.prod_idDTNTR;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_idp.prod_id and p.vend_idv.vend_id and o.order_num20005;多表联结性能问题注意MySQL 在运行时关联指定的每个表以处理联结这种处理可能非常消耗资源因此不要联结不必要的表。联结的表越多性能下降越厉害4、连接中 on / where 的区别where 是在 left jion 结果 之后进行的筛选from → where → group by → having → order byleft join 属于 from 这个步骤例如select * from products a join products b on a.vend_idb.vend_id and b.prod_idDTNTR where a.prod_idDTNTR;注意点ON 后面的筛选条件主要是针对关联表的对于主表的筛选条件不适应对于主表的筛选条件应该放在 where 后面不应该放在 ON 后面。例如1select * from flowersystem.user a left join flowersystem.order b on a.user_id b.order_user_id and a.user_id1;例如2select * from flowersystem.user a left join flowersystem.order b on a.user_id b.order_user_id where a.user_id1;