排序检索数据
----------------------------------------------
--按价格升序(1 2 3)
select name , price from Products order by price;
--按价格降序(3 2 1)
select name , price from Products order by price desc;
--使用 order by和limit的组合,找出[一个]列中最高值
select price from Products order by price desc limit 1;
使用where子句
位置: 表名的后面,order by子句前面,limit在order by的后面
----------------------------------------------
--where子句操作符
<> 不等于, between 在指定的两个值之间。
--使用between 操作符
--它检索价格在5块钱到100块钱之间的所有产品(包括指定的开始值和结束值)
select name, price from Products where price between 5 and 100;
--空值检查
select id from customers where email is null;
--and操作符(&&)
--查询供应商id等于2并且价格小于1000的CPU,按升序输出3行
--limit要在order by后面
select id, vendors, name, price from Products where vendors= 2 and price <1000 order by price limit 3;
--or操作符(||)
--注意:混合使用or 和 and 操作符的时候要加括号(and 操作符优先级高)
select id, vendors, name, price from Products where (vendors = 2 or vendors=1) and price <200000 order by price;
--in操作符
--下面的SQl语句完成与上面同样的功能
--in最大的优点是可以包含其它的select语句
select id, vendors, name, price from Products where vendors in(1,2) and price <200000 order by price;
--not操作符
--列出除供应商id 1以外的产品
select id, vendors, name, price from Products where vendors not in(1) and price <200000 order by price;
--like操作符
--百分号(%)通配符
--%告诉MySQL接受AMD之后的任意字符,不管它有多少字符
select *from Products where name like 'AMD%';
--'%MD%'表示匹配任何位置包含文本MD的值,不管它之前或之后出现什么字符
select *from Products where name like '%MD%';
--匹配任何东西,不过不会匹配 null
select *from Products where name like '%';
创建拼接字段
----------------------------------------------
--多数数据库用'+'号来实现拼接,MySQL用concat()
select concat(trim(name), ' (', trim(price), ')') as 报价 from Products order by price;
--下面的SQL语句检索订单号2的汇总信息
select product, quantity, price, (quantity*price) as TotalPrice from orderItems where ordernum=2;
--返回当前日期和时间
select now();
使用数据处理函数
----------------------------------------------
文本处理函数
--将串转换为小写
select lower(name) from Vendors;
日期处理函数
--Date()返回日期时间的日期部分
select num, customer, orderdate from orders where Date(orderdate) = '2008-09-01';
--Year()返回日期时间的年份部分
--检索顾客id为1, 2009年的订单
select num, customer, orderdate from orders where customer = 1 and Year(orderdate) = 2009;
--Month()返回日期时间的月份部分
--检索顾客id为1, 2009年2月份的订单
select num, customer, orderdate
from orders
where customer = 1 and Year(orderdate) = 2009 and Month(orderdate) = 2;
使用avg()、count()、max()、min()、sum()函数
----------------------------------------------
--avg()返回某列的平均值
--下面的例子返回特定供应商所提供产品的平均价格
select avg (price) AS IntelAveragePrice from products where vendors=1;
--count()返回某列的行数
--下面的例子返回Customers表的客户总数(COUNT(*)包含null值哦!)
select COUNT(*) AS num_cus from Customers;
--统计有电子邮件的客户总数(COUNT(email)忽略null值哦!)
select COUNT(email) AS num_cus from Customers;
--max()返回某列的最大值
select Max(price) AS MaxPrice from Products;
--min()返回某列的最小值
select Min(price) AS MinPrice from Products;
--sum()返回某列值之和
--返回 2号订单物品数量之和
select sum(quantity) AS Items from orderItems where ordernum=2
--返回 2号订单总价
select sum(quantity*price) AS TotalPrice from orderItems where ordernum=2
分组数据
----------------------------------------------
--group by
位置:where后, order by 前
select vendors, Max(price) AS max_price from Products group by vendors;
--统计所有顾客的订单数目
select customer, count(*) as OrderNum from orders group by customer;
--下面的例子检索订单号和总计订单价格,按“订单号”分组排序输出
select ordernum, sum(quantity*price) AS TotalPrice from OrderItems group by ordernum order by TotalPrice;
--过滤分组
--找到顾客订单数目大于 2的分组
select customer, count(*) as OrderNum from orders group by customer having count(*) >=2;
使用子查询
----------------------------------------------
--检索订购物品id=4的所有顾客
select ordernum from orderitems where product=4; --结果:2, 7
select customer from orders where num in(2, 7); --结果:1
select name, email from customers where id in(1); --结果:apq | apq@163.com
--合起来写就是这个样子
select name, email
from customers where id in (select customer
from orders where num in (select ordernum
from orderitems where product=4));
--作为计算字段使用子查询
select count(*) as totalorders from orders where cust_id=1; --结果:5
--显示customers表每个客户的订单总数
select cust_name,
(select count(*)
from orders
where orders.cust_id = customers.cust_id) AS totalorders from customers;
联结表
----------------------------------------------
select vend_name, prod_name, prod_price
from vendors, products
where vendors.vend_id = products.vend_id
order by vend_name, prod_name;
--上面的语句也可以这么写
select vend_name, prod_name, prod_price
from vendors inner join products
on vendors.vend_id = products.vend_id;