4.DQL查询数据（最重点）

# 4.DQL查询数据（最重点）

4.1DQL

Data Query Language 数据查询语言

• 所有查询操作都用它
• 什么查询都能做
• 最核心的语言

4.2指定查询字段

语法：

SELECT `字段名` FROM `表`

例子：

-- 查询全部的学生的所有信息 SELECT * FROM `表`
SELECT * FROM `student`

-- 查询指定字段	SELECT `字段名` FROM `表`
SELECT `studentno`, `studentname` FROM `student`

-- 别名，给结果起名字 AS（AS可以省略）
SELECT `studentno` 学号, `studentname` 学生名字 FROM `student`

-- 函数 Concat(a, b)
SELECT CONCAT('姓名：', `studentname`) 拼接后的样子 FROM `student`

去重distinct

去掉SELECT语句查询结果中重复出现的数据

adj.不同的; 明显的; 清晰的; 清楚的; 明白的; 有区别的; 不同种类的; 确定无疑的; 确切的;

-- 查询有哪些同学参加了考试，成绩
SELECT * FROM result

-- 查询有哪些同学参加了考试
SELECT `studentno` FROM result

发现查询到了多个1000号考生，是因为一个人要考多个科目，所以我们要去重

-- 去重
SELECT DISTINCT `studentno` FROM result

SELECT的高级用法

格式：

SELECT 表达式 

数据库中的表达式：文本值，列，null，函数，计算表达式，系统变量...

例子：

SELECT VERSION() -- 查询系统版本（函数）
SELECT 1000 - 7 计算结果 -- 用来计算（表达式）
SELECT @@auto_increment_increment -- 查询自增的步长（变量）

-- 学员考试成绩+1
SELECT `studentno`, `studentresult` + 1 提分后 FROM `result`

select的完整语法：

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
  [left | right | inner join table_name2]  -- 联合查询
  [WHERE ...]  -- 指定结果需满足的条件
  [GROUP BY ...]  -- 指定结果按照哪几个字段来分组
  [HAVING]  -- 过滤分组的记录必须满足的次要条件
  [ORDER BY ...]  -- 指定查询记录按一个或多个条件排序
  [LIMIT {[offset,]row_count | row_countOFFSET offset}];
   -- 指定查询的记录从哪条至哪条

4.3 where 条件子句

作用：检索数据中符合条件的值

搜索的条件由一个或者多个表达式组成！结果返回布尔值

• 逻辑运算符

尽量使用英文，因为SQL里面用的都是英文关键字

-- 先随便查一张表
SELECT `studentno`, `studentresult` FROM `result`

-- where使用
-- 查询考试成绩在85-100之间的
SELECT `studentno`, `studentresult` FROM `result`
WHERE `studentresult` >= 85 AND `studentresult` <= 100

-- 用逻辑运算符&&也是一样的，但是可读性没那么高
WHERE `studentresult` >= 85 && `studentresult` <= 100
-- 还可以用之前学的BETWEEN AND
WHERE `studentresult` BETWEEN 85 AND 100

• 比较运算符

这里的A是字段名

• LIKE运算符

-- 先随便查个表
SELECT `studentno`, `studentname` FROM `student`

-- 查询姓张的同学
-- like结合 %(代表0到任意个字符)  _(一个字符)
SELECT `studentno`, `studentname` FROM `student`WHERE `studentname` LIKE '张%'

-- 基本用法就是这样
-- 查询姓张的同学且名字后面只有一个字的（一个字就一条杠）
SELECT `studentno`, `studentname` FROM `student`WHERE `studentname` LIKE '张_'
-- 查询姓张的同学名字后面有两个字的（两个字就两条杠）
SELECT `studentno`, `studentname` FROM `student`WHERE `studentname` LIKE '张__'
-- 查询名字中含有飞字的（以此类推）
SELECT `studentno`, `studentname` FROM `student`WHERE `studentname` LIKE '%张%'

注意： %(代表0到任意个字符) _(一个字符)只能用在like运算符里

• IN运算符

-- in
-- 查询1000,1001，1002，号学员
SELECT `studentno`, `studentname` FROM `student`WHERE `studentno` IN(1001,1002,1000)

-- 查询住在广东深圳的学生
SELECT `studentno`, `studentname` FROM `student`WHERE `address` IN('广东深圳')

• NOT NULL

-- not null
SELECT `studentno`, `studentname` FROM `student`WHERE `phone` IS NOT NULL

• NULL

-- NULL
SELECT `studentno`, `studentname` FROM `student`WHERE `phone` IS  NULL

4.4联表查询

当同时要查询多张表的信息时，就要联表查询，前提是这两张表必须有至少有一个相同的字段

ON后面跟的就是这个相同字段，也就是两个表交叉的点，这个点可以有多个，任选其中一个即可

七种JOIN理论

主要是这三种

主要是这三种

• 内连接

-- INNER JOIN
-- 就是查询两个表中studentno相同的行，并返回
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno

• 右连接

-- RIGHT JOIN
-- 查询右表result中的subjectno（这里查询的是s表中的studentno，r表的studentno只是拿过来做判断），studentresult并全部返回，左表中没有对应的值就返回null
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student s
RIGHT JOIN result r
ON s.studentno = r.studentno

• 左连接

-- LEFT JOIN
/*
要查询左表student中包含的studentno,studentname的行并全部返回，如果右表中没有对应的值就返回null
*/
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student s
LEFT JOIN result r
ON s.studentno = r.studentno

来个练习

-- 查询三张表
/*
要求：查询参加了考试的学生信息
 	 查询学号`studentno`，学生姓名`studentname`，科目名`subjectname`，分数`studentresult`
     这里涉及到三张表`student``subject``result`
     我们可以先联表查询两张表，然后这两张表的笛卡尔积再去联表最后一张表
    
分析：参加了考试的学生，在三张表中都有信息，而没有参加考试的同学只在student表里有信息，所以我们有两种方法
1.result表作为A表，左连接student表
2.student表作为A表，右连接result表
查完两张表之后，再去查最后一张表subject，subject表只与result表中的subjectno有关联
这里用inner join 和left join 都是可以的，但是不可以用right join，因为这样会把subject表中所有的subjectname返回

总结：就是两张表联成了一张大表，这样大表里包含了两张表所有的字段，这张大表再去连接另一张表，以此类推
*/
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM result r
LEFT JOIN student s
ON r.studentno = s.studentno
LEFT JOIN `subject` sub
ON r.subjectno = sub.subjectno

-- ---------------
-- 查询参加了数据库结构-1的学生
-- 学号，学生姓名，科目名，分数
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
INNER JOIN `subject` sub
ON s.`gradeid` = sub.`gradeid`
INNER JOIN `result` r
ON sub.`subjectno` = r.`subjectno`
WHERE `subjectname` = '数据库结构-1'
-- WHERE只能写在联合查询的后面

自连接

自己的表和自己的表连接，核心：一张表拆成两张一样的表即可

这里pid指的是父表的id，categoryid就是自己的id

这么一张表，我们可以拆成两张表，一张父表，一张子表

我们最终想要查询出来的效果是这样子的

-- 查询父子信息
SELECT a.`categoryname` '父栏目', b.`categoryname` '子栏目'
FROM `category` a, `category` b
WHERE a.`categoryid` = b.`pid`

解析：这里就把一张表拆成两张一样的表，一张表是父表，一张是子表，然后用父表的id等于子表的父id

4.5分页和排序

排序（ORDER BY）

order by一定要在where后面

-- 先把1000号学生所有的成绩挑出来
-- 排序：升序ASC，降序DESC
SELECT s.`studentno`,`studentname`,`studentresult`
FROM student s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
WHERE s.studentno = 1000
ORDER BY `studentresult` ASC 

分页（LIMIT）

为什么要分页？

1.缓解数据库压力

2.给人更好的体验

现在也可以是无限往下拉（瀑布流）

LIMIT一定要放在最后面

语法：

LIMIT 起始值，页面的大小

SELECT s.`studentno`,`studentname`,`studentresult`
FROM student s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
WHERE s.studentno = 1000
ORDER BY `studentresult` ASC
LIMIT 0,5 -- 起始是0号数据，一页能显示5个数据

4.6子查询

where(条件判断)

这里面的条件判断是我们计算出来的，而子查询的本质是：在where语句中嵌套一个子查询语句

执行的顺序是先执行where里的子查询语句，再执行外面的（里面->外面）

格式：where(select...from)

例子：

-- 查询课程为 高等数学-2 且分数不小于80的同学的学号和姓名
SELECT `studentno`,`studentname` FROM student WHERE `studentno` IN (
  SELECT `studentno` FROM `result` WHERE `studentresult`>80 AND `subjectno` = (
    SELECT `subjectno` FROM `subject` WHERE `subjectname` = '高等数学-2'	
  )
)

可以总结一些套路

where  字段名  运算符 (select  同一个字段名  from  另一张表  判断运算)

如果使用联表查询的话是这样的：

SELECT s.`studentno`,`studentname`,`studentresult`
FROM student s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE `studentresult` > 80 AND `subjectname` = '高等数学-2'

两种方式看你喜欢用哪种就用哪种