MySQL数据表约束筛选v.s.行列运算

本文是个人学习笔记，内容主要包含对数据表加约束筛选行列、对数据表里的行列进行运算，学习文档来自易百教程：http://www.yiibai.com/mysql/

GROUP BY//DISTINCT+ORDER BY//ORDER BY

select state from customers group by state;   #有顺序

select distinct state from customers;   #无顺序
select distinct state from customers order by state;

聚合函数SUM，AVG和COUNT

select distinct state from customers where country='USA';
select count(distinct state) from customers where country='USA';

LIMIT限制查找几条样本

select state from customers where state is not null limit 6;
select distinct state from customers where state is not null limit 6;
select state from customers where state is not null;

select customernumber,customername,creditlimit from customers order by creditlimit asc limit 0,5;   #0是查询起始偏移值,5是查询数目
#查询信用额度最低(asc)的前五名客户

select productCode,productName,buyprice from products order by buyprice desc limit 1,1;
#找出结果集中价格第二高的产品;第三高的LIMIT 2,1 和第四高的LIMIT 3,1

WHERE查找满足某些条件的行

数值区间条件筛选//IN条件//条件嵌套//having(配合group by进行条件筛选)

select productCode,productName,buyPrice from products where buyPrice between 90 and 100;   #左闭右闭
select productCode,productName,buyPrice from products where buyPrice>90 and buyPrice<100;

select productCode,productName,buyPrice from products where buyPrice not between 20 and 100;
select productCode,productName,buyPrice from products where buyPrice<20 or buyPrice>100; 

select orderNumber,customerNumber,status,shippedDate from orders 
where orderNumber in(select from orderDetails group by orderNumber having sum(quantityOrdered*priceEach)>60000);
#嵌套查询，in后列表范围多个必须用()括起来

select ordernumber,sum(quantityOrdered) as itemsCount,sum(priceeach*quantityOrdered) as total from orderdetails 
group by ordernumber having total>5000 and itemsCount>600;

时间条件筛选

select orderNumber,requiredDate,status from orders 
where requireddate between cast('2013-01-01' as date) and cast('2013-01-31' as date);
#cast(...as...)转换运算符将文字字符串“2013-01-01”转换为DATE数据类型

LIKE基于模式查询选择数据

• 百分比( % )通配符允许匹配任何字符串的零个或多个字符。
• 下划线( _ )通配符允许匹配任何单个字符。

select employeeNumber,lastName,firstName from employees where firstName like 'a%';   
#搜索firstName以字符a开头的员工信息

select employeeNumber,lastName,firstName from employees where lastName like '%on';   
#搜索lastName以on字符结尾的员工信息

select employeeNumber,lastName,firstName from employees where lastName like '%on%';   
#查找lastname字段值中包含on字符串的所有员工

select employeeNumber,lastName,firstName from employees where firstName like 'T_m';   
#查找first以T开头/以m结尾/中间包含一个字符的员工

select employeeNumber,lastName,firstName from employees where lastName not like 'B%';   
#搜索lastName不以字符B/b开头的员工

##反斜杠字符\是默认转义字符;也可以使用ESCAPE子句指定一个不同的转义字符LIKE '%$_20%' ESCAPE '$';
select productCode,productName from products where productCode like '%\_20%';   #搜索productCode字段值中间包含_20字符串的产品

as定义列别名

select concat_ws(',',lastname,firstname) as 'full name' from employees;