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

4.1，DQL（data query language: 数据查询语言）

• 所有的查询操作都用它 Select
• 简单的查询，复杂的查询它都能做
• 数据库是最核心的语言，最重要的语句
• 使用频率最高的语句

4.2，指定查询字段

-- 查询全部的学生  SELECT 字段 FROM 表
SELECT * FROM student

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

-- 别名，给结果起一个名字  AS 可以给字段起别名，也可以表起别名
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM student


-- 函数  Concat(a,b)
SELECT CONCAT('姓名:',studentname) AS 新名字 FROM student

语法：SELECT 字段,... FROM 表

有的时候，列名字不是那么的见名知意。我们可以起别名 as 字段名 as 别名 表名 as 别名

去重 distinct

作用：去除select查询出来的结果中重复的数据，只显示一条

-- 查询一下有哪些同学参加了考试，成绩
SELECT * FROM result   -- 查询全部的考试成绩
SELECT `studentno` FROM result  -- 查询有哪些同学参加了考试
SELECT DISTINCT `studentno` FROM result  -- 发现重复数据，去重复数据

数据库的列（表达式）

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

-- 赏考试成绩 + 1分查看
SELECT `studentno`,`studentresult`+1 AS '提分后' FROM result

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

select 表达式 from 表

4.3，where条件子句

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

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

运算符 语法 描述

and && a and b a&&b 逻辑与，两个都为真，结果 为真

or || a or b a||b 逻辑或，其中一个为真，则结果为真

Not ! not a !a 逻辑非，真为假，假为真！

尽量使用英文字母

-- ===============where================
SELECT studentNo,`studentresult` FROM result  -- 查询学生的成绩

-- 查询考试成绩在95-100分之间
SELECT studentNo,`studentresult` FROM result
WHERE studentresult>=95 AND studentresult<=100

-- and改为&&
SELECT studentNo,`studentresult` FROM result
WHERE studentresult>=95 && studentresult<=100

-- 模糊查询（有区间范围）
SELECT studentNo,`studentresult` FROM result
WHERE studentresult BETWEEN 95 AND 100

-- 除了1000号学生之外的同学的成绩
SELECT studentNo,`studentresult` FROM result
WHERE studentNo!=1000

-- !=  not
SELECT studentNo,`studentresult` FROM result
WHERE NOT studentNo = 1000

模糊查询：比较运算符

运算符 语法 描述

is null a is null 如果操作符为null，结果为真

is not null a is not null 如果操作符不为null，结果为真

between a between b and c 若a在b和c之间，则结果为真

like a like b SQL匹配，如果a 匹配b ,则结果为真

in a in(a1,a2...) 假设a在a1,或者a2...其中的某一个值中，结果为真

--  ======================模糊查询============

-- 查询姓刘的同学
-- 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 '%嘉%'

-- ==========in（具体的一个或多个值）=========
-- 查询1001，1002，1003号学员
SELECT `studentno`,`studentname` FROM `student`
WHERE studentno IN (1001,1002,1003);

-- 查询在北京的学生
SELECT `studentno`,`studentname` FROM `student`
WHERE `address` IN ('北京');

-- ============null  not null=======
-- 查询地址为空的学生null ''
SELECT `studentno`,`studentname` FROM `student`
WHERE address='' OR address IS NULL

-- 查询有出生日期的同学   不为空
SELECT `studentno`,`studentname` FROM `student`
WHERE `borndate` IS NOT NULL

-- 查询没有出生日期的同学  为空
SELECT `studentno`,`studentname` FROM `student`
WHERE `borndate` IS NULL 

4.4，联表查询

join 对比

-- ============联表查询 join==============
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
/*思路
1.分析需求，分析查询的字段来自哪些表，（连接查询）
2.确定使用哪种连接查询？7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件：学生表中的 studentno= 成绩表 studentno

*/
-- join（连接的表） on（判断的条件）  连接查询
-- where 等值查询


SELECT * FROM student
SELECT * FROM result

SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentno = r.studentno

-- right join   as这时可以用空格替代
SELECT s.studentno,studentname,subjectno,studentresult
FROM student  s
RIGHT JOIN result  r
ON s.studentno = r.studentno

-- left join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student  s
LEFT JOIN result  r
ON s.studentno = r.studentno

-- 查询缺考的同学
SELECT s.studentno,studentname,subjectno,studentresult
FROM student  s
LEFT JOIN result  r
ON s.studentno = r.studentno
WHERE studentresult IS NULL

操作 描述

inner join 如果表中至少有一个匹配，就返回行

left join 会从左表中返回所有的值，即使右表中没有匹配

right join 会从右表中返回所有的值，即使左表中没有匹配

-- 查询了参加考试的同学信息：学号，学生姓名，科目名，分数
/*思路
1.分析需求，分析查询的字段来自哪些表，student,result,subject（连接查询）
2.确定使用哪种连接查询？7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件：学生表中的 studentno= 成绩表 studentno
*/

SELECT s.studentno,studentname,subjectname,`studentresult`
FROM student  s
RIGHT JOIN result  r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno

-- 我要查询哪些数据 select...
-- 从那几个表中查 from 表  xxx join  连接的表  on  交叉条件
-- 假设存在一种多张表查询，慢慢来，先查询两张表然后再慢慢增加
-- from a left join b  以左边的a 为基准
-- from a right join b  以右边的b 为基准

自连接

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

父类

categoryid categoryName

2 信息技术

3 软件开发

5 美术设计

子类

pid categoryid categoryName

3 4 数据库

2 8 办公信息

3 6 web开发

5 7 ps设计

操作：查询父类对应的子类关系

父类 子类

信息技术 办公信息

软件开发 数据库

软件开发 web开发

美术设计 ps技术

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


-- 查询学员所属的年级（学号，学生的姓名，年级名称）
SELECT studentNo,studentName,`gradeName`
FROM student  s
INNER JOIN `grade`  g
ON s.`gradeId`=g.`gradeId`

-- 查询科目所属的年级（科目名称，年级名称）
SELECT `subjectname`,`gradename`
FROM `subject`  sub
INNER JOIN `grade`  g
ON sub.`gradeId`=g.`gradeId`

-- 查询了参加数据库结构-1 考试的同学信息：学号，学生姓名，科目名，分数
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student  s
INNER JOIN `result`  r
ON s.studentNo = r.studentNo
INNER JOIN `subject`  sub
ON r.`subjectNo` = sub.`subjectNo`
WHERE subjectName = '数据库结构-1'

4.5 分布和排序

排序

SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student  s
INNER JOIN `result`  r
ON s.studentNo = r.studentNo
INNER JOIN `subject`  sub
ON r.`subjectNo` = sub.`subjectNo`
WHERE subjectName = '数据库结构-1'
ORDER BY studentresult ASC

分页

--  100万
--  为什么要分页？
--  缓解数据库压力，给人的体验更好，  瀑布流

 
--  分页，每页只显示五条数据
-- 语法：limit 起始值，页面的大小
-- 网页应用：当前，总的页数，页面的大小
--  LIMIT 0,5  1~5条数据
-- LIMIT 1,5    2~6条数据
-- LIMIT 6,5    第二页
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student  s
INNER JOIN `result`  r
ON s.studentNo = r.studentNo
INNER JOIN `subject`  sub
ON r.`subjectNo` = sub.`subjectNo`
WHERE subjectName = '数据库结构-1'
ORDER BY studentresult ASC
LIMIT 1,5
/*
第一页  limit 0,5
第二页  limit 5,5
第三页  limit 10,5   (n-1)*pageSize,pageSize
pagesize: 页面大小
(n-1)*pageSize： 起始值
n:当前页
数据总数/页面大小 = 总页数

*/

--  查询java第一学年，课程成绩排名前十的学生，并且分数要大于80的学生信息（学号，姓名，课程名称，分数）
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student`  s
INNER JOIN `result`  r
ON s.studentno = r.studentno
INNER JOIN `subject`  sub
ON sub.`subjectno` = r.`subjectno`
WHERE subjectname = 'javat第一学年' AND studentresult>=80
ORDER BY studentresult DESC
LIMIT 0,10

语法：limit (查询起始下标，pagesize)

4.6, 子查询

where(这个值是计算出来的)

本质：在where语句是嵌套一个子查询语句

-- ===============where====================
-- 1.查询数据库结构-1的所有考试结果 （学号，科目编号，成绩），降序排列
-- 方式一：使用连接查询
SELECT r.`studentno`,`subjectname`,`studentresult`
FROM `result`  r
INNER JOIN `subject`  sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '数据库结构-1'
ORDER BY studentresult DESC

--  方式二：使用子查询（由里及外）
-- 查询所有 数据库存结构-1 的学生学号
-- SELECT subjectno FROM `subject` WHERE subjectname = '数据库结构-1'
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result`
WHERE studentno = (
SELECT subjectno FROM `subject` WHERE subjectname = '数据库结构-1'
)
ORDER BY studentresult DESC

-- 查询所有 数据库存结构-1 的学生学号
SELECT subjectno FROM `subject` WHERE subjectname = '数据库结构-1'


-- 分数不小于80分的学生的学号和姓名
SELECT s.`studentno`,`studentname`
FROM student  s
INNER JOIN result  r
ON r.studentno = s.studentno
WHERE `studentresult` >=80

-- 在这个基础上增加一个科目，高等数学-2
-- 查询 高等数学-2的编号
SELECT s.`studentno`,`studentname`
FROM student  s
INNER JOIN result  r
ON r.studentno = s.studentno
WHERE `studentresult` >=80 AND `subjectno` = (
SELECT subjectno FROM `subject` WHERE `subjectname` = '高等数学-2'
)

-- 查询课程为 高等数学-2 且分数不小于80 的同学的学号和姓名
SELECT s.`studentno`,`studentname`
FROM student  s
INNER JOIN result  r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '高等数学-2' AND studentresult >=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'
	)	
)

 

4.7 ，分组和过滤

-- 查询不同课程的平均分，最高分，最低分，平均分大于80
-- 核心：（根据不同的课程分组）
SELECT subjectname, AVG(studentresult) AS 平均分,MAX(studentresult) AS 最高分,MIN(studentresult) AS 最低分
FROM result  r
INNER JOIN `subject`  sub
ON r.`subjectno` = sub.`subjectno`
GROUP BY r.subjectno  -- 通过什么字段来分组
HAVING 平均分>80

转载https://www.cnblogs.com/yangp67/p/9281754.html