2.DQL

一.简单查询

语法

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;

设置列名的别名。

1. 改变列的标题头；
2. 用于表示计算结果的含义；
3. 作为列的别名；
4. 如果别名中使用特殊字符，或者是强制大小写敏感，或有空格时，都需加双引号；

#设置列名的别名
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;

二.过滤查询

1. 比较运算符

	            含义
-------------------------------------------------
=	            等于
>	            大于
>=	            大于或等于
<	            小于
<=	            小于或等于
!=(<>)	        不等于

例子

#需求: 查询货品零售价为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'

2. 逻辑运算符

             含义
----------------------------------------
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;

3. 优先级运算

	        运算符
------------------------------------
1	            所有比较运算符
2	            NOT
3	            AND
4	            OR

#优先级规则
select * from product where (not productName like '%M%' and salePrice > 100) or (dir_id = 2);

4. 范围查询 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;

5. 集合查询 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);

6. 空值查询 IS NULL

#需求:查询商品名为NULL的所有商品信息。
select * from product where productName is null;
#商品名不为空
select * from product where not productName is null;

7. 模糊查询 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__';

8. 结果排序
   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分页

1. 假分页(内存分页)：所有数据已经存在内容中，只是显示部分而已，
   1. 优点:每次翻页时都从内存中取数据,翻页速度极快,简单
   2. 缺点:消耗内存大,容易内存溢出
2. 真分页(数据库分页)：每次翻页都去数据库查询数据(开发推荐)
   1. 优点:不会造成内存溢出
   2. 缺点:翻页比较慢,复杂

语法: 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;

九.数据的备份