【MySQL】-2 函数、分组、子查询、联合查询

时间:2023-01-08 08:43:31

函数

  Mysql的函数特性没有SQL可移植性强。

  大多数情况下支持的函数:

  处理文本串的函数: 

   RTrim():处理列值右边的空格

   LTrim():处理列值左边的空格

     Trim():处理列值的左右两边的空格(中间的不处理)

     Upper():文本转大写

   Length():返回串的长度

   Locate():找出串的一个子串

   Lower():文本转小写

   ...

  算术操作函数:

    包含一些常见的数值处理函数如:

    Abs()取绝对值;

    Mod()返回除操作的余数;

    Pi()返回圆周率;

    Rand();返回一个随机数;

    Exp()返回一个数的指数值;

  处理日期和时间的函数:

    列举常用的几个:

    CurDate() 返回当前日期;

    CurTime()返回当前时间;

    Date()返回日期时间的日期部分

    Date_Format()返回一个格式化的日期或时间

    Time()返回一个日期时间的时间部分

    ...

  •   查询订单表中,发生于2005-09-01日的订单的有关信息:
select  cust_id, order_num from orders
where date(order_date) = '2005-09-01';

  获取系统信息的系统函数:

  暂略

  聚集函数:

    部分对表中数据进行汇总(不对实际数据本身感兴趣)的函数。

    AVG()返回某列的平均值;

    COUNT()返回某列的行数(查询的结果数);

    MAX()返回某列的最大值;

    MIN()返回某列的最小值;

    SUM()返回某列的值的和:

  •   查看产品表所有产品的平均价格:
select avg(prod_price) as avg_price
from products;

  AVG()值为NULL的行会被忽略;

  •   查询在本店由邮箱地址的客户总数。
select count(cust_email) from customers;

  count()同样会忽略NULL值,所以没有统计到没留email的客户;如果要统计所有客户可以使用count(*)

  聚集函数都会忽略NULL值。

  分组

  group by :让查询结果按指定列的值排序并给每个列值分组,聚集函数将会作用于组而不是原来的结果集

  •   查询产品表中,每个供应商提供的产品数:

  

select vend_id, count(*) as num_prod
from products
group by vend_id WITH ROLLUP;

with rollup 将返回每个分组汇总的值,这里得到的所有供应商提供的产品总数。

  •   查询每个顾客下的订单数量,只展示3单以上的顾客
    select cust_id, count(*)
    from customers
    group by cust_id
    having count(*) > 2;

    having 关键字能对分组的结果再进行筛选(可以理解成where再数据分组前筛选,having在数据分组后再次筛选)

  • 查询订单表中表号在20000以后,所有总价大于50的订单,按总价的增序排列:
select order_num, sum(quantity*item_price) as sum_price from orderitems
where order_num > 20000
group by order_num
having sum_price > 50
order by sum_price;

子查询

  嵌套在其他查询中的查询,子查询在性能上并不占优势。

  •   查找购买过TNT2商品的客户:

  1.查询TNT2商品售出记录的订单号

  2.由订单号查询客户id

  3.由客户id查询客户信息

select cust_name, cust_contact
from customers
where cust_id in (select cust_id
from orders
where order_num in (select order_num
from orderitems
where prod_id = 'TNT2'));

联合查询

  内部联合:基于两个表之间的相等测试联结,也叫等值联结。

  •   查看每个供应商提供的产品的价格
select vend_name, prod_name, prod_price
from vendors inner join products
on vendors.vend_id = products.vend_id;

  外部联合,联合时允许保留没有对应关联行的那些行。其中:LEFT|RIGHT OUTER JOIN 保留关键字左|右边的表所有行,另一边的表如果没有匹配到改行对应的行则补上NULL.

  • 检索所有用户,已有订单的检索其订单:
select customers.cust_id, order_num
from customers left outer join orders
on customers.cust_id = orders.cust_id;

组合查询

UNION关键字,UNION ALL 表示不删除两次查询结果中相同的行