5-6 MySQL分组查询与子查询------MySQL数据库技术

目录

查询结果的分组操作

分组操作实例：

分组操作实例（解释）

过滤分组

 select中子句的顺序

select中子句的顺序

注意

子查询

子查询实例（业务需求）

MySQL数据增、删、改操作

插入数据

1.插入完整的行

2.插入行的一部分

3.插入多行

4.插入某些查询的结果

更新数据操作时需谨慎

删除数据（操作时需谨慎）

truncate 语句

---

​​​​​​​

查询结果的分组操作

• 分组允许把数据表中的数据按照某一个或几个字段，分为多个组，字段值相同的为一组。分组是为了便于对每个组进行聚集计算。
• 分组是在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	要检索的行数	否

 

 

 

 

 

 

 

 

 

注意

1. select后面只能查看group by 子句后有的列，和聚集计算的列；
2. group by 可以根据多个列进行分组，多个列分组即多个列的值相同。

子查询

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

子查询实例（业务需求）

• 订单存储在两个表中：orders保存订单号、客户ID、订单日期。
• 各订单的物品信息保存在orderitems表中，orders表不存储客户信息，它只保存客户的ID。实际的客户信息存储在customers表中。
• 如果需要列出订购物品编号TNT2的所有客户信息，可以采用下列步骤：

1. 查找含物品TNT2的所有订单的编号；
2. 查找前一步骤列出的订单编号的所有客户的ID；
3. 查找前一步骤返回的所有客户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='elmer@fudd.com' where cust_id='10005';

      #创建一张备份表

       create table productsnotes_bak select * from productnotes;
       create table productsnotes_bak like productnotes; #这个是只创建了一个和productnotes结构一样的表，里面没有数据。

删除数据（操作时需谨慎）

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

1. 从表中删除特定的行（通过where子句指定条件）
2. 从表中删除所有的行（不带where子句）
3. delete from productnotes;  #删除productnotes表
   insert into productnotes select * from productsnotes_bak; #恢复productnotes表数据，productsnotes_bak是之前备份productnotes的表

truncate 语句

• 可以使用truncate table语句删除整个表中的行，而且速度比delete语句快，truncate table语句是先删除整张表，然后重新创建一个空表。
• 格式：truncate table 表名;#清除整个表的数据