SELECT 语句
SELECT检索表数据，必须至少给出两条信息--------想选择什么，以及从什么地方选择。
检索一个列
SELECT prod_name FROM products;
上述语句利用SELECT语句从products表中检索一个名为prod_name的列
检索多个列
SELECT prod_id,prod_name,prod_price FROM products;
检索所有的列
SELECT * FROM products;
检索不同的行
SELECT vend_id FROM products;
select返回了14行，但是表中只有4个供应商，因为products表中列出了14个产品，如何检索有不同值的列表
SELECT DISTINCT vend_id FROM products;
限制结果
SELECT prod_name FROM products LIMIT 5;
SELECT prod_name FROM products LIMIT 5 , 5;
从第五行开始输出5行
第一个数位开始位置，第二个数为检索的行数
SELECT prod_name FROM products LIMIT 4 OFFSET 3;
从第三行开始，输出四行
使用完全限定输出表名
SELECT products.prod_name FROM products;
排序检索数据
排序数据
SELECT prod_name FROM products ORDER BY prod_name;
mysql对prod_name列以字母顺序排序
按多个排序
SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price , prod_name;
首先按照价格然后按照名称排序 作用，当价格相同的时候，会再按照名称来排序。
如果prod_price列中的数据所有值都是唯一的，则不会按照prod_name来排序
制定排序方向
ORDER BY默认是按照升序的方式排列，如果进行降序排列，就必须标出 DESC;
SELECT prod_id , prod_price ,prod_name FROM products ORDER BY prod_price DESC;
SELECT prod_id , prod_price ,prod_name FROM products ORDER BY prod_price DESC, prod_name;
先按照价格从高到底排序，价格相同的再按照名称升序排列。
与DESC相反的是ASC，ASC没有多大作用，因为默认就是ASC升序排列。
SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 2;
找出两个最贵的物品的值
过滤数据
使用where子句
SELECT prod_name ,prod_price FROM products WHERE prod_price = 2.50;
查询价格等于2.50的商品名字和价格
| 操作符 | 说明 |
| = | 等于 |
| <> | 不等于 |
| != | 不等于 |
| < | 小于 |
| > | 大于 |
| <= | 小于等于 |
| >= | 大于等于 |
| BETWEEN | 在制定的两个值之间 |
检查单个值
SELECT prod_name ,prod_price FROM products WHERE prod_name = 'fuses';
SELECT prod_name,prod_price FROM products WHERE prod_price <10;
SELECT vend_id , prod_name FROM products WHERE vend_id <> 1003;
查询供应商不是1003制造的所有产品
SELECT prod_name , prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;
查询价格在5和之间的商品名称和价格
数据过滤
组合where子句
SELECT prod_id , prod_price ,prod_name FROM products WHERE vend_id =1003 AND prod_price <=10;
SELECT prod_id , prod_price ,prod_name FROM products WHERE vend_id =1002 OR vend_id = 1003;
计算次序
SELECT prod_name , prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >=10;
从上面的结果来看，有两个值的结果小于10，显然返回的结果未按照预期的进行过滤，为什么会这样呢，原因是在于计算的次序，SQL在处理OR操作符前，优先处理AND操作符，当SQL看到上述where子句时，它理解为由供应商1003制造的任何价格都为10美元以上，包含10美元，或者由供应商1002提供的产品，不管价格。换句话说，由于AND的计算次序中优先级更高，操作符被错误的组合了。正确的操作方法：
SELECT prod_name , prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >=10;
IN 操作符
IN操作符来制定条件范围，范围中的每个条件都可以进行匹配。
SELECT prod_name , prod_price FROM products WHERE vend_id IN (1002,1003) ORDER BY prod_name;
IN和OR的用法有点类似，上面也可以这样写：
SELECT prod_name , prod_price FROM products WHERE vend_id=1002 OR vend_id=1003 ORDER BY prod_name;
NOT操作符
where子句中的not操作符的用法只有一个，那就是否定后它之后所有的任何条件
SELECT prod_name , prod_price FROM products WHERE vend_id NOT IN (1002,1003) ORDER BY prod_name;
上面用到的所有查询命令
SELECT prod_name FROM products;SELECT prod_id,prod_name,prod_price FROM products;SELECT * FROM products;SELECT vend_id FROM products;SELECT DISTINCT vend_id FROM products;SELECT prod_name FROM products LIMIT 5;SELECT prod_name FROM products LIMIT 5 , 5;SELECT prod_name FROM products LIMIT 1 , 1;SELECT prod_name FROM products LIMIT 4 OFFSET 3;SELECT products.prod_name FROM products;SELECT prod_name FROM products ORDER BY prod_name;SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price , prod_name;SELECT prod_id , prod_price ,prod_name FROM products ORDER BY prod_price DESC;SELECT prod_id , prod_price ,prod_name FROM products ORDER BY prod_price DESC, prod_name;SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 2;SELECT prod_name ,prod_price FROM products WHERE prod_price = 2.50;SELECT prod_name ,prod_price FROM products WHERE prod_name = 'fuses';SELECT prod_name,prod_price FROM products WHERE prod_price <10;SELECT vend_id , prod_name FROM products WHERE vend_id <> 1003;SELECT prod_name , prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;SELECT prod_id , prod_price ,prod_name FROM products WHERE vend_id =1003 AND prod_price <=10;SELECT prod_id , prod_price ,prod_name FROM products WHERE vend_id =1002 OR vend_id = 1003;SELECT prod_name , prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >=10;SELECT prod_name , prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >=10;SELECT prod_name , prod_price FROM products WHERE vend_id IN (1002,1003) ORDER BY prod_name;SELECT prod_name , prod_price FROM products WHERE vend_id=1002 OR vend_id=1003 ORDER BY prod_name;SELECT prod_name , prod_price FROM products WHERE vend_id NOT IN (1002,1003) ORDER BY prod_name;