//order by 位置，位于from、where之后，limit之前
select * from proucts order by prod_price DESC limit 1

#检查单个值、不匹配检查、范围值检查、空值检查
select * from proucts where pro_name='fuses';
select * from proucts where pro_price < 10;
select * from proucts where vend_id <> 1003;
select * from proucts where pro_price between 5 and 10; #范围值检查
select * from proucts where pro_price is null; #空值检查，返回没有价格的所有产品

#数据过滤：组合where子句（and、or、in、not）、计算次序
select * from proucts where vend_id =1002 and pro_price =10;
select * from proucts where vend_id =1002 or vend_id =1003;
select * from proucts where (vend_id =1002 or vend_id =1003) and pro_price >= 10;#计算次序，and优先级高，需要用分组（括号）进行计算次序
select * from proucts where pro_price in (5,10); 
select * from proucts where pro_price not in (5,10);

#%表示任何字符出现任意次数[0、1、无数]，区分大小写
select * from proucts where pro_price like 'jet%';
#_表示匹配一个字符
select * from proucts where pro_price like '_ ton anvil';

#REGEXP匹配列id包含文本'测试'的所有行
select * from message_station_info WHERE type REGEXP '测试' ;
#LIKE匹配整个列id,除非用通配符%和_
select * from message_station_info WHERE type LIKE '测试' ;
#.匹配一个任意字符,关键字BINARY，区分大小写
select * from message_station_info WHERE type REGEXP BINARY '.测试' ;
#进行OR匹配：|，将匹配测试消息订阅关系或测试123包含的行
select * from message_station_info WHERE type REGEXP '测试消息订阅关系|测试123' ;
#[]匹配特定的字符，[123] ton 为[1|2|3] ton的缩写
select * from message_station_info WHERE type REGEXP '[123] ton' ;
select * from message_station_info WHERE type REGEXP '[1|2|3] ton' ;
#匹配范围[0-9]、[a-z]
select * from message_station_info WHERE type REGEXP '[0-2] ton' ;
#匹配特殊字符\\_;\\.;\\|;\\[];\\()
select * from message_station_info WHERE type REGEXP '\\.' ;
select * from message_station_info WHERE type REGEXP '\\.[0-9]{3} ton?' ;

#将字段type、title拼接成一个字段进行显示,去掉字段两边的空格
select CONCAT(TRIM(type),'   (',TRIM(title),')') AS type_title from message_station_info WHERE title='test';
#+、-、*、/ 等MYSQL算术操作符
select on_del*status as del_status from message_station_info WHERE title='test';

#文本处理函数,upper()函数，列type字段全部转换为大写
select UPPER(type) from message_station_info;
#文本处理函数，left()函数，列type长度左边开始3个字符
select LEFT(type,3) from message_station_info;

#日期和时间处理函数，检索创建时间为2020-5-13到2020-5-14的数据
select * from message_station_info where DATE(create_time)='2020-5-14';
select * from message_station_info where DATE(create_time) BETWEEN '2020-5-13' and '2020-5-14';
select * from message_station_info where Year(create_time)='2020' AND MONTH(create_time)='6'; 

#AVG()函数，求平均值,用来返回所有列的平均值，也可以用来返回特定列或行的平均值.AVG()函数忽略列值为NULL的行。
select AVG(define_id) as avg_id from message_station_info;
#COUNT()函数，计数，可利用COUNT()确定表中行的数目或符合特定条件的行的数目。
#COUNT(*)不管是空值(NULL)还是非空值;COUNT(id)对特定列中具有值的行进行计数，忽略NULL值。
select COUNT(*) as num_id from message_station_info;
select COUNT(id) as num_id from message_station_info;
#MAX()函数，返回指定列中的最大值。忽略列值为NULL的行。
select MAX(id) as max_id from message_station_info;
#MIN()函数，返回指定列中的最小值。忽略列值为NULL的行。
select MIN(id) as min_id from message_station_info;
#SUM()函数，用来返回指定列值的和（总计）。
select SUM(id*num) as sum_id from message_station_info;
#DISTINCT()函数，用来返回指定列值的和（总计）。
select AVG(DISTINCT prod_price) as avg_price from products where id=1003;

#GROUP BY子句和HAVING 子句,WITH ROLLUP可以得到每个分组以及每个分组汇总级别的值
#WHERE过滤指定的是行；HAVING过滤指定的是分组
select type,id,title,COUNT(*) as num from message_station_info group by type HAVING COUNT(*) >='11';

#MYSQL执行3条select语句，从最里边的子查询返回订单号列表，用于其外面的子查询的where子句，外面的子查询返回客户ID列表，用于最外层查询的where子句，最外层查询确实返回所需的数据
#利用子查询进行过滤
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 cust_name,cust_state,
(SELECT COUNT(*) FROM orders where orders.cust_id = customers.cust_id) AS orders
FROM customers 
ORDER BY cust_name; 

#UNION组合查询[UNION默认取消重复的行，全匹配需要UNION ALL]
select id,type,on_del
from app_info
WHERE on_del='1';
UNION
select id,type,on_del
from app_info
WHERE id < '20';

#UNION全匹配需要加个UNION ALL
select id,type,on_del
from app_info
WHERE on_del='1';
UNION ALL
select id,type,on_del
from app_info
WHERE id < '20';

#UNION与where可以互相调换
select id,type,on_del
from app_info
WHERE on_del='1' or id < '20';

#UNION排序
select id,type,on_del
from app_info
WHERE on_del='1';
UNION
select id,type,on_del
from app_info
WHERE id < '20';
ORDER BY id;

#内部联结[等值联结]：基于两个表之间的相等测试。
select * 
from app_station_cls INNER JOIN app_info
on app_station_cls.from_id = app_info.id;

#联结多个表：
select b.*
from sys_user as a,app_info as b,sys_user_app_relation as c
where c.app_id = b.id and c.user_id = a.id and a.account='upm_test';

#自联结
select A1.id,A1.type
from app_info as A1,app_info as A2
WHERE A1.id=A2.id and A2.type='1'
#自然联结:排除多次出现，使每个列只返回一次。内部联结：返回所有数据。
select A.*,B.id
from app_station_cls AS A,app_info AS B
WHERE A.from_id = B.id;
#联结包含了那些在相关表中没有关联行的行。这种类型的联结称为外部联结：左联结、右联结。[LEFT|RIGHT OUTER JOIN]