DQL语言（重点）

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

1.1、DQL

（Data Query Language:数据查询语言）

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

1.2、指定查询字段

-- 查询全部的学生
select * from `student`;
-- 查询全部的成绩
select * from `result`;
-- 查询指定字段
select `StudentNo`,`StudentName` from `student`;
-- 别名，给列起一个别名 as 可以给字段起别名，也可以给表起别名
select `StudentNo` as 学号,`StudentName` as 学生姓名 from `student` as s;
-- 函数Concat(a,b)
select concat('姓名:',`StudentName`) as 新名字 from `Student` as s;

语法:select 字段,... from 表

有时候，列名字不是那么得见名知意，我们会取别名 格式：字段名 as 别名 表名 as 别名

1.3、去重 distinct

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

1.4、数据库的列（表达式）

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

-- 学员考试成绩 + 1分查看
SELECT `StudentNo`,`StudentResult`+100 AS 提分后 FROM `result`;

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

1.5、where条件子句

作用：检索数据中符合条件的值
搜索的条件由一个或者多个表达式组成！结果 布尔值

逻辑运算符

运算符	语法	描述
and &&	a and b a&&b
or	a or b
not
尽量使用英文符号

-- 查询考试成绩在95~100分之间
SELECT `StudentNo`,`StudentResult` FROM `result`
WHERE `studentResult` >= 95 AND `studentResult` <= 100;

SELECT `studentNo`,`studentResult` FROM `result`
WHERE `studentResult` BETWEEN 95 AND 100;

-- 查询除学号1000以外的学生的成绩
SELECT `studentNo`,`studentResult` FROM `result`
WHERE `studentNo`!=1000;

SELECT `studentNo`,`studentResult` FROM `result`
WHERE NOT `studentNo`=1000;

模糊查询：比较运算符

运算符	语法	描述
is null	a is null	如果操作符为null，结果返回真
is not null	a is not null	如果操作符不为bull，结果返回真
between	a between a and b	若a在b和c之间，则结果为真
like	a like b	SQL匹配，如果a匹配b，则结果为真
in	a in (a1,a2,a3,...)	假设a在a1,a2,a3...其中的某一个值中，结果为真

-- ===================模糊查询=====================
-- 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` IS NULL OR `address`='';
-- 查询出生日期不为空的同学
SELECT `studentno`,`studentname` FROM `student`
WHERE `borndate` IS NOT NULL;

1.6、联表查询

join 对比

-- ================联表查询 join============
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
/*思路
  1. 分析需求，分析查询的字段来自哪些表
  2. 确定使用哪种链接查询？7种
  确定交叉点（这两个表中哪个数据是相同的）
  判断条件：表达式
 */
-- inner join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS 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`;
-- right join 可查哪些同学没有进学生档案
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` s
RIGHT JOIN `result` r
ON s.`studentno` = r.`studentno`;
-- join (连接的表) on (判断的条件) 连接查询
-- where 等值查询

操作	描述
inner join	如果表中至少有一个匹配，就返回结果
left join	会从左表中返回值，即使右表中没有匹配
right join	会从右表中返回值，即使左表中没有匹配

-- 查询参加了考试的同学信息（学号，学生姓名，科目名，分数）
-- 方法一
SELECT s.`studentno` 学号,`studentname` 姓名,`subjectname` 科目,`studentresult` 分数
FROM `student` s
RIGHT JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` su
ON r.`subjectno` = su.`subjectno`;
-- 方法二
SELECT s.`studentno` 学号,`studentname` 姓名,`subjectname` 科目,`studentresult` 分数
FROM `student` s
RIGHT JOIN (
SELECT `studentno`,`subjectname`,`studentresult`
FROM `result` r
LEFT JOIN `subject` su
ON r.`subjectno` = su.`subjectno`
) r
ON s.`studentno` = r.`studentno`;

-- 我要查询哪些数据 select...
-- 从哪几个表中查 from 表 XXX join 连接的表 on 交叉条件
-- 假设存在一种多张表查询，慢慢来，先查询两张表然后再处理下一张表

自连接（了解）

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

原表：

categoryid	pid	categoryName
2	1	信息技术
3	1	软件开发
4	3	数据库
5	1	美术设计
6	3	web开发
7	5	ps技术
8	2	办公信息

父类：

categoryid	categoryName
2	信息技术
3	软件开发
5	美术设计

子类：

pid	categoryid	categoryName
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	ps设计

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

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps设计

-- 查询父子信息：把一张表看为两个一模一样的表
SELECT f.`categoryName` '父栏目',s.`categoryName` '子栏目'
FROM `category` f,`category` s
WHERE f.`categoryid` = s.`pid`;

1.7、分页和排序

-- ==========分页 limit 和 排序 order by===============

-- 排序：升序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;

-- 100万条数据不分页，形成瀑布流，数据库压力巨大
-- 分页可缓解数据库压力

-- 分页，每页只显示5条数据
-- 语法：limit 起始条，页面的大小
-- 网页应用：当前，总的页数，页面大小
-- limit 0,5
-- limit 5,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 0,5

-- 第一页 limit 0,5
-- 第二页 limit 5,5
-- 第三页 limit 10,5
-- 第n页  limit (n-1)*pageSize,pageSize
-- [pageSize:页面大小]
-- [(n-1)*pageSize:起始值]
-- [n:当前页]
-- [数据总数/页面大小 = 总页数]

语法：limit(查询起始条，pageSize)

练习：

-- 查询 Java程序设计-1 课程成绩排名前十的学生，并且分数要大于80的学生信息（学号，姓名，课程名称，分数）
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` = 'Java程序设计-1' AND `studentresult` > 80
ORDER BY `studentresult` DESC
LIMIT 0,10;

1.8、子查询

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

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

where(select * from)

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

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

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

-- 再改造(由里及外)
SELECT `studentno`,`studentname`
FROM `student`
WHERE `studentno` IN (
    SELECT `studentno`
    FROM `result`
    WHERE `studentresult` >= 70 AND `subjectno` = (
        SELECT `subjectno`
        FROM `subject`
        WHERE `subjectname` = '高等数学-2'
        )
)

练习

-- 查询 C语言-1 成绩前五名的同学信息（学号，姓名，分数）
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 `subjectname` = 'C语言-1'
ORDER BY `studentresult` DESC
LIMIT 0,5;
-- 使用子查询
SELECT s.`studentno`,`studentname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
WHERE `subjectno` = (
	SELECT `subjectno`
	FROM `subject`
	WHERE `subjectname` = 'C语言-1'
)
ORDER BY `studentresult` DESC
LIMIT 0,5

1.9、分组过滤

-- 查询不同课程的平均分，最高分，最低分，平均分大于80分的
-- 核心：（根据不同的课程分组）
select `subjectname` 课程名称,avg(`studentresult`) 平均分,max(`studentresult`) 最高分,min(`studentresult`) 最低分
from`result` r
inner join `subject` sub
on r.`subjectno` = sub.`subjectno`
group by sub.`subjectno`
having 平均分 > 80

1.10、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}];
        -- 指定查询的记录从哪条至哪条

注意：[ ]代表可选的，{ }代表必选的

顺序很重要：

select 去重 要查询的字段 from 表 (注意：表和字段可以取别名)

xxx join 要连接的表 on 等值判段

where(具体的值，子查询语句)

group by (通过哪个字段来分组)

having (过滤分组后的信息，条件和where是一样的，位置不同)

order by (通过哪个字段排序)[升序/降序]（默认升序）

limit startIndex,pageSize

业务层面：

查询：跨表，跨数据库...