以下为《mysql必然知必会》第4-8章学习笔记,主要涉及查询的select语句,where语句和like通配符。
SELECT语句
1.检索一列
Select name from products;
2.检索多列
Select id,name,price fromproducts;
3.检索所有列
Select * from products;
4.检索id列(返回唯一值)
Select distinct id from products;
5.检索name列(返回前5行)
Select name from products limit 5;
6.检索name列(返回6-10行);
Select name from products limit 5,5;
7.检索name列,并以name列排序
Select name from products order by name;
8.检索id ,price,name三列,并以price,name排序,首先按price,再按name。
Select id,price,name from productsorder by price,name;
9.检索id,price,name三列,并以price降序排列(升序是默认的,ASC)
Select id,price,name fromproducts order by price desc;
10.检索price列中的最大值
Select price from products order by pricedesc limit 1;
WHERE语句
1. 检索name,price两列,只返回price为2.5的行
Select name,price from products where price=2.5;
2.检索name,price两列,只返回name为fuse的行
Select name,price from products where name=‘fuse’;
3.检索name,price两列,只返回price小于10的行
Select name,price from products where price<10;
4.检索出不是由id为1003供应商制造的产品
Select id pro_name from products where id<>1003;(或者用!=)
5.检索价格在5元-10元之间的产品
Select name price from products where pricebetween 5 and 10;
6.检索价格为空的产品列表
Select name from products where price isnull;
组合WHERE子句
1. 检索1003供应商制作且价格小于等于10元的所有产品名称和价格
Select name and price from products whereprice <=10 and id=1003;
2. 检索1002 或者1003供应商制作的产品名称和价格
Select name,price from products where id=1002 or id=1003;
Select name,price from products where id in(1002,1003);
3. 检索除1002,1003供应商之外生产的所有产品名称和价格;
Select name,price from products where id not in(1002,1003);
LIKE操作符
1.检索以产品名称以jet开头的所有产品的id和name
Select id,name from products where name like‘jet%’;
2.检索产品名称以s开头,e结尾的所有产品
Select name from products where name like‘s%e’;
3.检索产品名称第二位到最后一位是ton anvil的产品
Select name from products where name like‘_ton anvil’;
注意%与_的区别:%可以匹配0,1,多个字符,而_仅可以匹配1个字符。