DQL（Data Query Language 数据库查询语言）

DQL（Data Query Language 数据库查询语言）

SELECT * FROM t_student 
WHERE name = 'Changed' 
ORDER BY age ASC

select语句执行顺序
from --> where --> select --> order by

简单查询

查询全部数据
SELECT * FROM t_student

查询指定列
SELECT name,age FROM t_student

指定别名
SELECT name AS 姓名,age AS 年龄 FROM t_student

查询单条数据，去除重复数据，DISTINCT 
SELECT DISTINCT name FROM t_student

#需求：查询所有货品的id，名称和批发价，批发价=卖价*折扣
SELECT id , productName , salePrice * cutoff FROM product

#需求：查询所有货品的id，名称，和各进50个的成本价(成本=costPrice)
SELECT id , productName, costPrice * 50 FROM product

#需求：查询所有货品的id，名称，各进50个，并且每个运费1元的成本
SELECT id,productName, (costPrice + 1)* 50  FROM product

#需求：修改别名，AS可省略，别名使用有意义的英文单词
SELECT id,productName, (costPrice + 1)* 50 AS PF FROM product

条件查询

SELECT * FROM t_student WHERE age > 20

WHERE：指定查询条件

年龄 > 20，相应的有 < ，!=，<=，>=，=
SELECT * FROM t_student WHERE age > 20

#需求：查询id，货品名称，批发价大于350的货品
SELECT id,productName,salePrice * cutoff FROM product WHERE salePrice * cutoff > 350

逻辑查询

关键字：AND，OR，NOT

SELECT * FROM t_student WHERE age > 20 AND `name` = 'Demo'

SELECT * FROM t_student WHERE age > 20 OR `name` = 'Demo'

SELECT * FROM t_student WHERE NOT age > 20

模糊查询

SELECT * FROM t_student WHERE name LIKE '%C%'

LIKE 执行通配查询，查询条件包含文字，数字。

通配符
% ：表示零个或多个任意字符。
_ ：表示一个字符。

没有用通配符LIKE 相当于 =

结果集排序

SELECT * FROM t_student ORDER BY age DESC

ASC：升序
DESC：降序

注意
ORDER BY，出现在SELECT 语句的最后。

---

分页查询

• 假分页（内存分页）
  所有数据都已在内存中，只是部分显示。
  优点：每次翻页时都从内存中读取数据，翻页速度极快，简单。
  缺点：消耗内存大，容易内存溢出。

• 真分页（数据库分页）
  每次翻页都会去数据库查询数据。
  优点：不会造成内存溢出(每次30-50条数据)。
  缺点：翻页比较慢，复杂。

SELECT * FROM product LIMIT 5,5

LIMIT 5,5 计算公式
参数1：表示开始取值的索引从0开始，startIndex = (n-1) * pagerSize
参数2：表示每页最多显示的记录数，pagerSize

第N页：(n-1) * pagerSize,pagerSize

---

聚合/分组函数

聚合函数

聚合函数	功能
COUNT()	统计此列数据个数
MAX()	计算最大值
MIN()	计算最小值
SUM()	计算两个数的和
AVG()	计算平均值

聚合函数作用于一条数据，并对一组数据返回一条记录

SELECT COUNT(name) as 学生人数 FROM t_student
SELECT MAX(age) as 最大年龄 FROM t_student
SELECT AVG(age) as 平均年龄 FROM t_student

分组函数

查询学生平均年龄，按性别分类
SELECT AVG(age) FROM t_student GROUP BY sex

GROUP BY会将表查询的数据分成小组

#需求：查询产品表中每种分类零售价的平均值并且平均值大于300
SELECT AVG(salePrice) FROM product 
GROUP BY dir_id 
HAVING 
AVG(salePrice) > 300

#需求：查询产品表中每种分类零售价大于100并且平均值大于400
SELECT AVG(salePrice) FROM product 
WHERE salePrice > 100 
GROUP BY dir_id 
HAVING 
AVG(salePrice) > 400

SQL的执行顺序

where --> group by --> having --> order by

---

多表查询

分表操作

在数据库中，每张表存储的事物要为同一类型，不同类型要分表存储，类似于Java的类，不同的事物定义不同的类。
如公司员工信息(姓名，年龄，性别，工作号)可作为一张表描述，而员工所属的部门信息就要作为另一张表描述。

笛卡尔积问题

多表查询时，没有连接条件的表，查询返回的结果会产生笛卡尔积问题。
即查询后，得到的数据是两个表数据的乘积。

假设：集合A = {a，b}，集合B = {0，1，2}。
查询后的笛卡尔积：{(a，0)，(a，1)，(a，2)，(b，0)，(b,1)，(b，2)}。

解决方案
在where 中加入有效的连接条件

内外连接查询

• 内连接查询：返回结果是多张表交集部分的数据。
• 外连接查询：返回结果是多张表并集的数据。

内连接查询

隐式内链接
select * from A,B WHERE A.id = B.cid

, 替换成JOIN
WHERE替换成ON
注意：ON 只能写等值连接条件


显式内连接
select * from A join B on A.id = B.cid

两种查询效果相同，仅仅是语法不同，不过推荐使用显式内连接查询

外连接查询

外连接查询
select * from A left join B on A.主键 = B.外键

select * from A right join B on A.主键 = B.外键

自连接查询

自连接查询：把一张表看成两张做查询。

SELECT child.id , child.dirName , child.parent_id 
FROM productdir child LEFT JOIN productdir parent 
ON child.id = parent.parent_id