5-6 MySQL分组查询与子查询------MySQL数据库技术
目录
查询结果的分组操作
- 分组允许把数据表中的数据按照某一个或几个字段,分为多个组,字段值相同的为一组。分组是为了便于对每个组进行聚集计算。
- 分组是在select语句的group by 子句中建立的
- 注意:group by 只是创建分组,但不保证分组里面的数据的排列顺序,需要使用 order by子句对分组里面的数据进行排序。
分组操作实例:
- 使用group by 语句对select查询的结果进行分组,以便统计
- #group by 分组 集体
分组操作实例(解释)
- select语句指定了两个列,vend_id包含产品供应商的ID,prod_num为计算字段(使用cou()函数)。
- group by 子句按vend_id排序并分组数据,这导致对每个vend_id而不是整个表计算prod_num一次,从而按照prod_num为单位,来计算每个组的总和。
- 注意:group by 子句必须出现在where子句之后,order by 子句之前。
过滤分组
select中子句的顺序
select语句中使用的子句必须按照一定的次序,下图列出来各个子句在select语句中出现的次序。
- 除了能用group by分组数据外,MySQL还允许对分组指定条件,规定包含哪些分组,排除哪些分组。
- MySQL使用having子句来完成该操作。
- where子句过滤指定的行,having子句过滤指定的分组。
- where与having同时使用,列出具有2个(含)以上、价格为10(含)以上的产品的供应商
select中子句的顺序
- select语句中使用的子句必须按照一定的次序,下图列出来各个子句在select语句中出现的次序。
子句 | 说明 | 是否必须使用 |
select | 要返回的列或表达式 | 是 |
from | 从中检索数据的表 | 仅在从表选择数据时使用 |
where | 行级过滤 | 否 |
group by | 分组说明 | 仅在按组计算聚集时使用 |
having | 组级过滤 | 否 |
order by | 输出排序顺序 | 否 |
limit | 要检索的行数 | 否 |
注意
- select后面只能查看group by 子句后有的列,和聚集计算的列;
- group by 可以根据多个列进行分组,多个列分组即多个列的值相同。
子查询
- 子查询是嵌套在其他查询中的查询
子查询实例(业务需求)
- 订单存储在两个表中:orders保存订单号、客户ID、订单日期。
- 各订单的物品信息保存在orderitems表中,orders表不存储客户信息,它只保存客户的ID。实际的客户信息存储在customers表中。
- 如果需要列出订购物品编号TNT2的所有客户信息,可以采用下列步骤:
- 查找含物品TNT2的所有订单的编号;
- 查找前一步骤列出的订单编号的所有客户的ID;
- 查找前一步骤返回的所有客户ID的客户信息。
- select order_num from orderitems where prod_id='TNT2' ;
- select cust_id from orders where order_num in ('20005','20007');
- select * from customers where cust_id in ('10001','10004');
- 等价于
- select * from customers where cust_id in (select cust_id from orders where order_num in (select order_num from orderitems where prod_id='TNT2'));
- #查找出拥有5种以上的供应商ID、供应商名称、供应商地址
- select vend_id, vend_name,vend_address from vendors where vend_id in (select vend_id from products group by vend_id having count(*)>5);
MySQL数据增、删、改操作
插入数据
- 使用insert语句来完成插入操作,插入操作可以采用下列方式:
-
1.插入完整的行
- 格式: insert into 表名(列名) values (各个列的值);
insert into customers (cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email) values ('8806','lisisi','njlu','nanjing','null','null','null','null','null');
等价于
insert into customers values ('8807','liusisi','njlu','nanjing','null','null','null','null','null');
-
2.插入行的一部分
insert into customers(cust_id,cust_name) values('88078','wusisi');
-
3.插入多行
- 例中单挑insert语句有多组值,每组值用一对圆括号括起来,用逗号分隔。
insert into customers(cust_id,cust_name) values('88079','yusisi'),('88080','kunsisi'),('88081','masisi'),('88082','wangsisi'),('88083','simasisi');
-
4.插入某些查询的结果
- insert可以插入由select查询出来的值。该方法由一条insert语句和一条select语句组成。
- 格式:insert 表A (列1,列2) select 列1,列2 from 表B;
- insert into customers(cust_id,cust_name) select ven_id,vend_name from vendors;
更新数据操作时需谨慎
-
为了更新表中的数据,可使用update语句。
-
update语句可以更新表中的特定的行;也可以更新表中所有的行。
-
注意:update语句如果后面不跟where语句的话,将修改表中所有的行,使用的时候需要小心,以免产生错误的修改。
- #更新数据实例:修改客户编号为10005的客户的电子邮件地址
- update customers set cust_email='[email protected]' where cust_id='10005';
#创建一张备份表
create table productsnotes_bak select * from productnotes;
create table productsnotes_bak like productnotes; #这个是只创建了一个和productnotes结构一样的表,里面没有数据。
删除数据(操作时需谨慎)
- 从一个表中删除数据,使用delete语句,delete的使用方法有以下两种:
- 从表中删除特定的行(通过where子句指定条件)
- 从表中删除所有的行(不带where子句)
- delete from productnotes; #删除productnotes表
insert into productnotes select * from productsnotes_bak; #恢复productnotes表数据,productsnotes_bak是之前备份productnotes的表
truncate 语句
- 可以使用truncate table语句删除整个表中的行,而且速度比delete语句快,truncate table语句是先删除整张表,然后重新创建一个空表。
- 格式:truncate table 表名;#清除整个表的数据