从零开始学习MySQL--基础查询教程2--005

BETWEEN 运算符（between…and…）

--查找价格在 90 和 100 (含 90 和 100 )元范围内的商品--前后都包含
select * from products where buyPrice between 90 and 100;

select * from products where buyPrice >= 90 and buyPrice <= 100;

--查找购买价格不在 20 到 100 (含 20 到 100 )之间的产品--前后都不包含
select * from products where buyPrice not between 20 and 100;
select * from products where buyPrice < 20 or buyPrice > 100;

BETWEEN 与日期类型数据

--查询获取所需日期( requiredDate )从 2013-01-01 到 2013-01-31 的所有订单
select * from orders where requireddate between CAST('2013-01-01' AS DATE) AND CAST('2013-01-31' AS DATE);

like 运行符- NOT like

%name：以name结尾的模糊查询

name%：以name开头的模糊查询

%name%：含有name词语的模糊查询

_：表示一个字符

select employeeNumber, lastName, firstName from employees where firstName like 'a%';
select employeeNumber, lastName, firstName from employees where lastName like '%on';
select employeeNumber, lastName, firstName from employees where lastname like '%on%';

select employeeNumber, lastName, firstName from employees where firstname like 'T_m';

like 与 escape 子句

escape 子句指定转义字符

--把_转换成字符
select productCode, productName from products where productCode like '%\_20%';
select productCode, productName from products where productCode like '%$_20%' escape '$';

limit子句

使用 limit 子句来约束结果集中的行数。 limit 子句接受一个或两个参数。 两个参数的值必须为零或正整数。

第一个参数为偏移量，第二个为最大行数

--要查询 employees 表中前 5 个客户
select customernumber, customername, creditlimit from customers limit 5;
select customernumber, customername, creditlimit from customers limit 0,5;

limit获得最高和最低的值

--要查询信用额度最高的前五名客户
select customernumber, customername, creditlimit from customers order by creditlimit desc
limit 5;

--查询将返回信用限额最低的五位客户
select customernumber, customername, creditlimit from customers order by creditlimit asc
limit 5;

limit获得第n个最高值

select productCode, productName, buyprice from products order by buyprice desc;

--找出结果集中价格第二高的产品
select productCode, productName, buyprice from products order by buyprice desc limit 1, 1;

IS NULL 操作符

--查询没有销售代表的客户
select customerName, country, salesrepemployeenumber from customers where salesrepemployeenumber is NULL order by customerName;

--查询有销售代表的客户
select customerName, country, salesrepemployeenumber from customers where salesrepemployeenumber is not NULL order by customerName;

ORDER BY子句

--查询从 customers 表中查询联系人， 并按 contactLastname 升序对联系人进行排序
SELECT contactLastname, contactFirstname FROM customers ORDER BY contactLastname;

--降序
SELECT contactLastname, contactFirstname FROM customers ORDER BY contactLastname DESC;

--按姓氏按降序和名字按升序排序联系人
SELECT contactLastname, contactFirstname FROM customers ORDER BY contactLastname DESC, contactFirstname ASC;