一.简单查询
语法
SELECT {*, column [alias],...}
FROM table_name where 条件;
实例
#查询商品的分类编号。消除重复的数据
select distinct dir_id from product;
#需求:查询所有货品的id,名称和批发价(批发价=卖价*折扣)
select id, productName, salePrice * cutoff FROM product;
#需求:查询所有货品的id,名称,和各进50个的成本价(成本=costPirce)
select id, productName, costPrice * 50 from product;
#需求:查询所有货品的id,名称,各进50个,并且每个运费1元的成本
select id, productName, (costPrice + 1) * 50 from product;
设置列名的别名。
- 改变列的标题头;
- 用于表示计算结果的含义;
- 作为列的别名;
- 如果别名中使用特殊字符,或者是强制大小写敏感,或有空格时,都需加双引号;
#设置列名的别名
select id, productName, (costPrice + 1) * 50 cost from product; #推荐
select id, productName, (costPrice + 1) * 50 'cost Price' from product;
select id, productName, (costPrice + 1) * 50 成本 from product;
二.过滤查询
- 比较运算符
含义
-------------------------------------------------
= 等于
> 大于
>= 大于或等于
< 小于
<= 小于或等于
!=(<>) 不等于
例子
#需求: 查询货品零售价为119的所有货品信息.
select * from product where salePrice = 119;
#需求: 查询货品名为罗技G9X的所有货品信息.
select * from product where productName = '罗技G9X';
#需求: 查询货品名 不为 罗技G9X的所有货品信息.
select * from product where productName != '罗技G9X';
#需求: 查询分类编号不等于2的货品信息
select * from product where dir_id != 2;
#需求: 查询货品名称,零售价小于等于200的货品
select productName, salePrice from product where salePrice <= 200;
#需求: 查询id,货品名称,批发价大于350的货品
select id, productName, salePrice * cutoff tradePrice from product where salePrice * cutoff > 350;
注意:字符串和日期要用单引号扩起来.
要让MySQL查询区分大小写,可以:
SELECT * FROM table_name WHERE BINARY productName='g9x'
- 逻辑运算符
含义
----------------------------------------
AND(&&) 如果组合的条件都是TRUE,返回TRUE
OR(||) 如果组合的条件之一是TRUE,返回TRUE
NOT(!) 如果下面的条件是FALSE,返回TRUE
例子
#需求: 选择id,货品名称,零售价在300-400之间的货品
select id, productName, salePrice from product where 300 < salePrice and salePrice < 400;
#需求: 选择id,货品名称,分类编号为2或4的所有货品
select id, productName, dir_id from product where dir_id = 2 or dir_id = 4;
#需求: 选择id,货品名称,分类编号不为2的所有商品
select id, productName, dir_id from product where not dir_id = 2;
#需求: 选择id,货品名称,分类编号的货品零售价大于等于250或者成本大于等于200
select id, productName, dir_id, salePrice, costPrice from product where salePrice >= 250 or costPrice >= 200;
- 优先级运算
运算符
------------------------------------
1 所有比较运算符
2 NOT
3 AND
4 OR
#优先级规则
select * from product where (not productName like '%M%' and salePrice > 100) or (dir_id = 2);
- 范围查询 BETWEEN AND
#需求: 选择id,货品名称,零售价在300-400之间的货品
select id, productName, salePrice from product where salePrice between 300 and 400;
#需求: 选择id,货品名称,零售价不在300-400之间的货品
select id, productName, salePrice FROM product where not salePrice between 300 and 400;
- 集合查询 IN
#需求:选择id,货品名称,分类编号为2或4的所有货品
select id, productName, dir_id from product where dir_id in(2,4);
#需求:选择id,货品名称,分类编号不为2或4的所有货品
select id, productName, dir_id from product where not dir_id in(2,4);
- 空值查询 IS NULL
#需求:查询商品名为NULL的所有商品信息。
select * from product where productName is null;
#商品名不为空
select * from product where not productName is null;
- 模糊查询 LIKE
%通配符:可表示零或多个字符。
_通配符:可表示一个字符。
#需求: 查询货品名称带有 'M' 的所有信息
select * from product where productName like '%M%';
#需求: 查询匹配货品名称 '罗技M9?' 的所有信息
select * from product where productName like '罗技M9_';
#需求: 查询匹配货品名称 '罗技M9??' 的所有信息
select * from product where productName like '罗技M9__';
- 结果排序
ASC : 升序,缺省。
DESC: 降序。
#需求按照零售价格升序排序
select * from product order by salePrice asc;
#需求按照零售价格降序排序
select * from product order by salePrice desc;
#需求名称包含'M'的,按照升序
select * from product where productName like '%M%' ORDER by salePrice asc;
三.MySQL分页
- 假分页(内存分页):所有数据已经存在内容中,只是显示部分而已,
- 优点:每次翻页时都从内存中取数据,翻页速度极快,简单
- 缺点:消耗内存大,容易内存溢出
- 真分页(数据库分页):每次翻页都去数据库查询数据(开发推荐)
- 优点:不会造成内存溢出
- 缺点:翻页比较慢,复杂
语法: select * from 表 where xxx LIMIT ?, ?
参数1: 分页起始位置
参数2: pageSize 每页显示多少条
例子
#分页查询
select * from product limit 0, 3;
select * from product limit 3, 3;
四.聚合函数
COUNT:统计结果记录数
MAX: 统计计算最大值
MIN: 统计计算最小值
SUM: 统计计算求和
AVG: 统计计算平均值
例子
#需求:查询所有商品平均零售价
select AVG(salePrice) from product;
#需求:查询商品总记录数(注意在Java7前必须使用long接收)
select COUNT(id) from product;
#需求:查询分类为2的商品总数
select COUNT(id) from product where dir_id = 2;
#需求:查询商品的最小零售价,最高零售价,以及所有商品零售价总和
select MIN(salePrice), MAX(salePrice),SUM(saleprice) from product;
五.笛卡尔积
select * from product, productdir;
#消除笛卡尔积数据臃肿
select * from product, productdir where product.dir_id = productdir.id;
六.外键约束
主键约束(PRIMARY KEY): 约束在当前表中,指定列的值非空且唯一.
外键约束(FOREIGN KEY): A表中的外键列的值必须参照于B表中的主键列的值,外键列允许为NULL.
删除表时:
先删除从表再删除主表
七.内连接查询
内连接查询:是相对于外连接。
内连接分为:隐式内连接、显示内连接,其查询效果相同,仅仅是语法不同。
隐式内连接:
SELECT <selectList>
FROM A ,B
WHERE A.列 = B.列
显示内连接(推荐写法):.
SELECT <selectList>
FROM A [INNER] JOIN B ON A.列 = B.列
例子
#需求:查询所有商品的名称和分类名称:
#隐式
select * from product p, productdir pd where p.dir_id = pd.id;
#显式
select * from product p join productdir pd on p.dir_id = pd.id;
#需求: 查询零售价大于200的无线鼠标
#隐式
select * from product p, productdir pd
where p.dir_id = pd.id and p.salePrice > 200 and pd.dirName = '无线鼠标';
#显式
select * from product p join productdir pd on p.dir_id = pd.id
where p.salePrice > 200 and pd.dirName = '无线鼠标';
#需求: 查询每个货品对应的分类以及对应的库存
#隐式
select p.id, p.productName, pd.dirName, ps.storeNum
FROM product p, productdir pd, productstock ps
where p.dir_id = pd.id and p.id = ps.product_id;
#显式
select p.id, p.productName, pd.dirName, ps.storeNum
from product p join productdir pd on p.dir_id = pd.id
join productstock ps on p.id = ps.product_id;
#需求: 如果库存货品都销售完成,按照利润(profit)从高到低查询货品名称,零售价,货品分类(三张表).
#隐式
select p.id, p.productName, p.salePrice, pd.dirName, (p.salePrice - p.costPrice) * ps.storeNum
from product p, productdir pd, productstock ps
where p.dir_id = pd.id and p.id = ps.product_id
order by (p.salePrice - p.costPrice) * ps.storeNum desc;
#显式
select p.id, p.salePrice, pd.dirName, (p.salePrice - p.costPrice) * ps.storenum
from product p join productdir pd on p.dir_id = pd.id
join productstock ps on p.id = ps.product_id
order by (p.salePrice - p.costPrice) * ps.storeNum desc;
八.自连接查询
自连接查询:把一张表看成两张来做查询.
#需求: 查询每个商品分类的名称和父分类名称:
#隐式
select son.dirName, father.dirName
from productdir son, productdir father
where son.parent_id = father.id;
#显式
select son.dirName, father.dirName
from productdir son join productdir father
on son.parent_id = father.id;
#需求: 查询出所有的顶级分类
#隐式
select * from productdir where parent_id is null;