4、DQL查询数据(最重点)
4.1、DQL
Data Query Language 数据查询语言
- 所有的查询操作都用它 SELECT
- 简单的查询,复杂的查询它都能做~
- 数据库中最核心的语言,最重要的语句
- 使用频率最高的语句
SELECT语句语法
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr] ...
[into_option]
[FROM table_references
[PARTITION partition_list]]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
[HAVING where_condition]
[WINDOW window_name AS (window_spec)
[, window_name AS (window_spec)] ...]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[into_option]
[FOR {UPDATE | SHARE}
[OF tbl_name [, tbl_name] ...]
[NOWAIT | SKIP LOCKED]
| LOCK IN SHARE MODE]
[into_option]
注意:[]括号代表可选的,{}括号代表必选的
SELECT [ALL | DISTINCT]
{ * | table.* | [table.field1[ AS alias1][, table.field2[ AS alias2]][,...]]}
FROM table_name1[ AS table_alias]
[LEFT | RIGHT | INNER JOIN table_name2] -- 联合查询
[WHERE ...] -- 指定结果需满足的条件
[GROUP BY ...] -- 指定结果按照哪几个字段来分组
[HAVING ...] -- 过滤分组的记录必须满足的次要条件
[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
[LIMIT {[ OFFSET,]ROW_COUNT | row_countOFFSET OFFSET}]; -- 指定查询的记录从哪条至哪条
4.2、指定查询字段
-- 查询全部的学生
-- SELECT 字段 FROM 表
SELECT * FROM student
-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM student
-- 别名,给结果起一个名字 AS(字段或表)
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM student AS S
-- 函数 Concat (a,b)
SELECT CONCAT('姓名:',StudentName) AS 新名字 FROM student
语法:SELECT
字段,.....
FROM表
有时候,列名字不是那么的见名知意,我们起别名——AS
表名/字段名 AS 别名
去重 distinc
作用:去掉SELECT 查询出来的结果中重复的数据,只显示一条
-- 查询一个有哪些同学参加了考试,成绩
SELECT * FROM result -- 查询全部的考试成绩
SELECT `StudentNo` FROM result -- 查询有哪些同学参加了考试
SELECT DISTINCT SELECT `StudentNo` FROM result -- 发现重复数据,去重
数据库的列(表达式)
SELECT VERSION() -- 查询系统版本(函数)
SELECT 100 * 3 -1 AS 计算结果 -- 用来计算(表达式)
SELECT @@auto_increment_increment -- 查询自增的步长(变量)
-- 学员考试成绩 + 1分查看
SELECT `StudentNo`,`StudentResult` + 1 AS '提分后' FROM result
数据库中的表达式:文本值,列,函数,计算表达式,系统变量。。。。。
select `表达式` from `表`
4.3、where条件子句
作用:检索数据中 符合条件
的值
搜索条件:由一个或者多个表达式组成!结果 布尔值
逻辑运算符
尽量使用英文字母
运算符 | 语法 | 描述 |
---|---|---|
and && | a and b a && b | 逻辑与,双真为真 |
or | ||
not ! | not a ! a | 逻辑非,取反 |
-- 查询考试成绩在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 StudentResult != 1000
-- != not
SELECT StudentNo,StudentResult FROM result
WHERE NOT StudentResult = 1000
模糊查询:比较运算符
运算符 | 语法 | 描述 |
---|---|---|
IS NULL | a is null | 如果操作符为NULL,结果为真 |
IS NOT NULL | a is not null | 如果操作符不为NULL,结果为真 |
BETWEEN | a between b and c | 若a在b和d之间,结果为真 |
LIKE | a like b | SQL匹配,如果a匹配b,结果为真 |
IN | a in (a1,a2,a3,…) | 假设a在其中,结果为真 |
-- ========= LIKE ======
-- 查询姓刘的同学
-- like结合 %(代表0到任意个字符) _(一个字符)
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 StudentName IN (1001,1002,1003)
-- 查询在北京的学生 `Address` IN ('安徽')
-- ========= null not null ======
-- 查询地址为空的学生 null ''
SELECT StudentNo,StudentName FROM student
WHERE Address = '' OR Address IS NULL
-- 查询有出生日期的同学 is not null
SELECT StudentNo,StudentName FROM student
WHERE BornDate IS NOT NULL
4.4、联表查询
JoinON
-- 查询参加了考试的同学(学号,姓名,科目编号,分数)
SELECT * FROM student
SELECT * FROM result
/* 思路
1. 分析需求,分析查询查询字段来自哪些表(连接查询)
2. 确定使用哪种连接查询?7种
确定交叉点(这两个表中哪个数据是相同的)
判断条件:学生表中StudentNo = 成绩表 StudentNo
*/
-- Inner join
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student AS s
INNER JOIN result AS r
WHERE s.StudentNo = r.StudentNo
-- Right Join
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
-- join (连接的表) on (判断的条件) 连接查询
-- where 等值查询
-- 查询缺考的同学
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. 确定使用哪种连接查询?七种
确定交叉点(这两个表中哪个数据是相同的)
判断条件:学生表中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 交叉条件
-- 假设存在一种多张表查询,慢慢来,先查询两张表然后再慢慢增加
自连接
核心:一张表拆为两张一样的表即可
父类
categoryid | categoryName |
---|---|
2 | 信息技术 |
3 | 软件开发 |
5 | 美术设计 |
子类
pid | categoryid | categoryName |
---|---|---|
3 | 4 | 数据库 |
2 | 8 | 办公信息 |
3 | 6 | web开发 |
5 | 7 | 美术设计 |
操作:查询父类对应的子类关系
父类 | 子类 |
---|---|
信息技术 | 办公信息 |
软件开发 | 数据库 |
软件开发 | web开发 |
美术设计 | ps技术 |
CREATE TABLE `category` (
`catetoryid` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
`pid` INT NOT NULL COMMENT '父id',
`categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY (`catetoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
INSERT INTO `category` (`catetoryid`,`pid`,`categoryName`)
VALUES
('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');
-- 查询父子信息,把一张表看成两张一模一样的表
SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`catetoryid` = b.`pid`
-- 查询学员所属的年级(学号,学生姓名,年级名称)
SELECT studentNo,studentName,gradeName
FROM student s
INNER JOIN grade g
ON s.gradeId = g.graId
4.5、分页和排序
分页
-- 为什么要分页?
-- 缓解数据库压力,给人的体验更好,瀑布流
-- 分页,每页只显示五条数据
-- 语法:limit `起始值`,`页面大小`
-- 网页应用:当前,总的页数,页面的大小
-- LIMIT 0,5 1~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 DESC
LIMIT 1,5
-- 【pageSize:页面大小】
-- 【(n-1)* pageSIze:起始值】
-- 【n:当前页】
-- 【数据总数 / 页面大小 = 总页数】
语法:limit
起始值
,页面大小
-- 思考:
-- 查询 Java第一学年 课程成绩排名前十的学生,并且分数要大于80 的学生信息(学号,姓名,课程名称,分数)
SELECT 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 SubejectName = 'Java第一学年' AND StudentResult >= 80
ORDER BY StudentResult DESC
LIMIT 0,10
排序
-- 排序:升序ASC 降序DESC
-- ORDER BY 通过哪个字段排序,怎么排
-- 查询的结果根据 成绩降序 排序
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 DESC
4.6、子查询
where(这个值是计算出来的)
本质:在where语句中嵌套一个子查询语句
-- 1、查询 数据库结构-1 的所有考试结果(学号,科目编号,成绩),降序排列
-- 方式一:使用连接查询
SELECT StudentNo,SubjectName,StudentResult
FROM result r
INNER JOIN `subject` sub
ON r.SubjectNo = sub.SubjectNo
WHERE SubjectName = '数据库结构-1'
ORDER BY StudentResult DESC
-- 方式二:使用子查询(由里及外)
SELECT StudentNo,SubjectName,StudentResult
FROM result r
WHERE SubjectNo = (
SELECT SubjectNo FROM `subject`
WHERE SubjectName = '数据库结构-1'
)
ORDER BY StudentResult DESC
-- 查询所有 数据库结构-1 的学生学号
SELECT SubjectNo FROM `subject` WHERE SubjectName = '数据库结构-1'
4.7、分组和过滤
-- 查询不同课程的平均分,最高分,平均分大于80
-- 核心:(根据不同的课程分组)
SELECT SubjectName,AVG(StudentResult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
FROM result r
INNER JOIN `subject` sub
ON r.SubejectNo = sub.SubjectNo
GROUP BY r.SubjectNo -- 通过什么字段来分组
HAVING 平均分>80