MySQL基础：拼接字段concat、文本处理函数、时间日期处理函数、聚合函数count()、avg()、max()、min()、sum()、分组计算group by

##第10章 创建计算字段
#拼接字段(concat)
select concat(vend_name,' (',vend_country,')') from vendors order by vend_name;
select concat(RTrim(vend_name),' (',RTrim(vend_country),')') from vendors order by vend_name;
/*
RTrim：去掉串右边的所有空格
LTrim：去掉串左边的所有空格
Trim：去掉串左右两边的空格
*/
select concat(RTrim(vend_name),' (',RTrim(vend_country),')') as vend_title from vendors order by vend_name;

#执行计算
select prod_id,quantity,item_price from orderitems where order_num=20005;
select prod_id,quantity,item_price,quantity*item_price as expanded_price from orderitems where order_num=20005;

select Now();#返回当前时间和日期
select Trim('  abc  ');#去掉串‘   abc   ’左右两边的空格
select 3*2;

 

##第11章 使用数据处理函数
#文本处理函数
/*
函数                        说明
left()                返回串左边的字符
right()             返回传右边的字符
LTrim()           去掉串左边的空格
RTrim()          去掉串右边的空格
upper()           将串转换成大写
lower()           将串转换成小写
length()          返回串的长度
locate()          找出串的一个子串
soundex()      返回串的soundex值
substring()     返回子串的字符
*/
select vend_name,upper(vend_name) as vend_name_upcase from vendors order by vend_name; 
select cust_name,cust_contact from customers where cust_contact='Y Lie';
select cust_name,cust_contact from customers where soundex(cust_contact)=soundex('Y Lie');#匹配发音类似于Y Lie的联系名

#日期和时间处理函数
/*
函数                                  说 明
now()                      返回当前日期和时间
curdate()                返回当前日期
curtime()                返回当前时间
date()                     返回一个日期时间的日期部分
time()                     返回一个日期时间的时间部分
year()                     返回一个日期的年份部分
month()                  返回一个日期的月份部分
day()                      返回一个日期的天数部分
dayofweek()           返回一个日期的对应星期几
hour()                     返回一个时间的小时部分
minute()                 返回一个时间的分钟部分
second()                返回一个时间的秒部分
datediff()                计算两个日期之差
date_add()             高度灵活的日期运算函数
date_format()         返回一个格式化的日期或时间串
adddate()               增加一个日期（天、周等）
addtime()               增加一个时间（时、分等）
*/

select now();
select curdate();
select curtime();
select date('2013-12-14 21:15:30');
select time('2013-12-14 21:15:30');
select year('2013-12-14 21:15:30');
select month('2013-12-14 21:15:30');
select day('2013-12-14 21:15:30');
select dayofweek('2013-12-14 21:15:30');
select hour('2013-12-14 21:15:30');
select minute('2013-12-14 21:15:30');
select second('2013-12-14 21:15:30');

select cust_id,order_num from orders where order_date='2005-09-01';
select cust_id,order_num from orders where date(order_date)='2005-09-01';
#检索日期为2005年9月的所有订单
select cust_id,order_num from orders where date(order_date) between '2005-09-01' and '2005-09-30';
select cust_id,order_num from orders where year(order_date)=2005 and month(order_date)=9;

#数值处理函数
/*
函数                             说明
abs()                 返回一个数的绝对值
sqrt()                 返回一个数的平方根
mod()                返回除操作的余数
exp()                 返回一个数的指数值
sin()                  返回一个数的正弦
cos()                 返回一个数的余弦
tan()                  返回一个数的正切
pi()                    返回圆周率
rand()                返回一个随机数
*/
select rand();
select pi();

 

##第12章 汇总数据
/*
count()：计数
sum()：求和
avg():求平均
max():最大值
min():最小值
注：以上函数均忽略列值为null的行
*/
select avg(prod_price) as avg_price from products; #求平均
select avg(prod_price) as avg_price from products where vend_id=1003;

select count(*) as num_cust from customers;#计算行数
select count(cust_email) as num_cust from customers;#忽略cust_email的null值，只对cust_email有值的行进行计数

select max(prod_price) as max_price from products;#最大值

select min(prod_price) as min_price from products;#最小值

select sum(quantity) as items_ordered from orderitems where order_num=20005;#求和
select sum(quantity*item_price) as total_price from orderitems where order_num=20005;

select distinct prod_price from products where vend_id=1003;
select avg(distinct prod_price) from products where vend_id=1003;

select count(*) as num_items,max(prod_price) as price_max,min(prod_price) as price_min,avg(prod_price) as price_avg from products;

 

##第13章 分组数据（group by）
#分组
select vend_id,count(*) as num_prod from products group by vend_id;
##自动添加一列计算总合
select vend_id,count(*) as num_prod from products group by vend_id with rollup;

#过滤分组

#列出至少有两个订单的所有顾客
select cust_id,count(*) as orders from orders 
group by cust_id 
having orders>=2;
#列出具有2个及以上、价格为10以上的产品的供应商
select vend_id,count(*) as num_prods from products 
where prod_price>=10
group by vend_id
having count(*)>=2;
#注意与上的对比
select vend_id,count(*) as num_prods from products 
group by vend_id
having count(*)>=2;

#检索总计订单价格大于等于50的订单的订单号和总计订单价格
select order_num,sum(quantity*item_price) as ordertotal from orderitems
group by order_num 
having ordertotal>=50;

select order_num,sum(quantity*item_price) as ordertotal from orderitems
group by order_num 
having ordertotal>=50
order by ordertotal;

 

总结：子句顺序
select 字段名 from 表名
where......
group by ......
having......
order by......
limit......

where过滤行,在数据分组前进行过滤，having过滤分组，在数据分组后进行过滤；
一般在使用group by时，应该也给出order by；
group by 分组一般与聚合函数一起使用