一、检索数据
1. 检索列
# 从products表中获取prod_name这一列
SELECT prod_name
FROM products;
# 检索多个列
SELECT prod_name, prod_id, prod_price
FROM products;
# 检索所有列
SELECT * FROM products;
2. 检索不同的行
# 重复的行只显示一次
SELECT DISTINCT vend_id
FROM products;
3. 限定检索
注意,行0开始数,检索5行是:0,1,2,3,4行,第6行同理从行0开始数
# 只检索5行
SELECT prod_id
FROM products
LIMIT 5;
# 检索第6行开始的5行
SELECT prod_id
FROM products
LIMIT 6, 5;
# 限定表名的列
SELECT products.prod_id
FROM products;
二、排序检索数据
1. 排序
# 用prod_name的字母顺序排序
SELECT prod_name FROM products
ORDER BY prod_name;
# 先用价格排序,再用名称排序
SELECT prod_id, prod_price, prod_name FROM products
ORDER BY prod_price, prod_name;
2. 降序
无说明默认升序,降序需要用DESC(descrease)加以说明
如果要在多个列上进行降序,需要对每个列都进行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;
3. 语法顺序
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT