MYSQL---------使用DQL命令查询数据(二)

你的名字 2024-04-29 11:10 49阅读 0赞

## 学习目标： ##

`掌握ORDER BY 子句`

`使用LIMIT子句实现分页查询`

`掌握MySQL的分组查询`

`掌握MySQL的子查询`

--------------------

## 学习内容： ##

## 1.SELECT语法——排序 ##

语法

SELECT [ALL|DESTINCT]
    { * | TABLE.*| [TABLE.FIELD1[AS ALIAS1] [,TABLE.FIELD2[AS ALIAS2]][AS ALIAS2]]}
    FROM TABLE_NAME [AS TABLE_ALIAS]
    [LEFT|OUT|INNER JOIN TABLE_NAME2] #多表连接查询
    [WHERE ...]    #指定结果需满足的条件
    [GROUP BY ...] #指定结果按照哪几个字段来分组
    [HAVING ...]   #过滤分组的巨鹿必须满足的次要条件
    [ORDER BY...] #指定查询记录按一个或者多个条件排序
    [LIMIT {[OFFSET,]ROW_COUNT | ROW_COUNT OFFSET OFFSET}];  #分页查询

注意

\[ \] 括号代表可选的；

\{ \} 括号代表必须的；

\# MySQL语句中的注释符，也可以用 /\*该处为注释\*/

## 2.ORDER BY 子句 ##

ORDER BY子句：实现按一定顺序显示查询结果。

排序可以是升序（ASC）或者降序（DESC）,如果不指定ASC或DESC，结果集默认按ASC升序排序。

把成绩都降低10%后加5分，再查询及格成绩，并按照成绩从高到低排序
    SELECT STUDENTNO AS 学生编号，(studentresult*0.9+5) AS 综合成绩
    FROM `RESULT`
    WHERE (`STUDENTRESULT`*0.9+5) >=60
    ORDER BY STUDENTRESULT DESC;

## 3.SELECT语法——分页查询 ##

LIMIT 子句

LIMIT 子句：MySQL查询语句中使用LIMIT子句限制结果集

#应用2：分页查询（分页显示数据）
    #查询学生表的学号、姓名、电话 ，每页显示2条记录
    #第一条记录的偏移量为0
    #第一页
    #偏移量 0 1
    SELECT STUDENTNO,STUDENTNAME,PHONE FROM STUDENT LIMIT 0,2

## 4.SELECT语法——分组查询 ##

### 4.1 ###

GROUP BY作用：使用GROUP BY关键字对查询结果分组

对所有的数据进行分组统计

分组的依据字段可以有多个，并依次分组

与HAVING结合使用，进行分组后的数据筛选

**GROUP BY子句经常和聚合函数结合使用完成分组统计功能**

### 4.2 ###

WHERE与HAVING对比：

WHERE子句 用来筛选 FROM 子句中指定的操作所产生的行

GROUP BY子句

用来分组 WHERE 子句的输出

HAVING子句 用来从分组的结果中筛选行

## 5.子查询 ##

子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其他子查询中的查询。

子查询在WHERE语句中的一般用法

SELECT ... FROM 表名 WHERE 字段X 比较运算符（子查询）

**注意**

习惯上，外层查询称为父查询，圆括号中嵌入的查询称为子查询。

执行SQL语句时，先执行子查询，返回所有来自子查询的结果，再执行外围的父查询，返回查询的最终结果（即求解方式为由里及外）。

将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个（否则会出现提示信息：错误代号1242 Subquery returns more than 1 row）。

### 5.1IN子查询 ###

IN后面的子查询可以返回多条记录。

常用IN替换等于（＝）的子查询。

语法

SELECT ... FROM 表名 WHERE 字段X IN(子查询)

#查询课程名称为”JavaOOP”且考试分数大于60分的学生信息（学号、姓名和联系电话）。
    SELECT STUDENTNO,STUDENTNAME,PHONE FROM STUDENT WHERE STUDENTNO IN 
    (SELECT STUDENTNO FROM RESULT WHERE STUDENTRESULT > 60 AND
    SUBJECTNO = (SELECT SUBJECTNO FROM `SUBJECT` WHERE SUBJECTNAME = `JAVAOOP`))

### 5.2EXISTS 子查询 ###

EXISTS 子查询：带有EXISTS的子查询不返回任何记录的数据，只返回逻辑值“True”或“False”。

SELECT ...... FROM 表名 WHERE EXISTS(子查询);

**注意**

子查询有返回结果： EXISTS子查询结果为TRUE，执行外层查询；

子查询无返回结果： EXISTS子查询结果为FALSE, 外层查询不执行；

### 5.3子查询注意事项 ###

任何允许使用表达式的地方都可以使用子查询

嵌套在父查询SELECT语句的子查询可包括

SELECT子句

FROM子句

WHERE子句

GROUP BY子句

HAVING子句

只出现在子查询中而没有出现在父查询中的列不能包含在输出列中

## 本次学习所有代码关键字实战演练 ##

#### 1.ORDER BY子句 ####

SELECT * FROM STUDENT;
    -- ORDER BY子句：实现一定顺序显示查询结果
    -- 排序可以是升序（ASC）或者降序（DESC）,如果不指定ASC或DESC，结果集默认按ASC升序排序。
    -- 按照出生年月的降序排列
    SELECT * FROM STUDENT ORDER BY BIRTHDAY DESC;
    -- 按照出生年月的降序排列，关键词asc,升序排列时，asc可以不写
    -- 若排序的字段值相同时，相同的几行记录会默认按照主键升序排列
    SELECT * FROM STUDENT ORDER BY DIRTHDAY;
    SELECT * FROM STUDENT ORDER BY ID DESC;
    -- ORDER BY 按照多个字段进行分别排列，但是要注意顺序，越靠前越优先
    SELECT * FROM STUDENT ORDER BY BIRTHDAY DESC,ID DESC;

#### **2.分页查询，关键字 limit** ####

-- 分页查询，关键字 limit 分页语句要写在最后
    -- 分页查询的作用：节约资源，提高响应速度，优化用户体验，数据显而易见
    SELECT * FROM STUDENT;
    -- 分页思想的步骤
    -- 1.多少条为一页（页面大小），LIMIT中第二个数字就是页面大小
    SELECT * FROM STUDENT LIMIT ?,10;
    SELECT * FROM STUDENT LIMIT ?,5;
    -- 2.计算起始位置 （第一条是从0开始的）
    SELECT * FROM STUDENT LIMIT 0,5;#第一页
    SELECT * FROM STUDENT LIMIT 5,5;#第二页
    SELECT * FROM STUDENT LIMIT 10,5#第三页
    -- 分页中计算起始位置的公式（页码-1）*页面大小=起始位置

#### 3.分组查询 ####

-- 分组查询 关键字GROUP BY  且分组查询通常是结合聚合函数一起使用,并且通常
     -- 在返回结果中将聚合函数的结果和分组的字段一起展示
     -- 统计学生表中男生人数和女生人数
    SELECT COUNT(1),SEX FROM STUDENT GROUP BY SEX;
    -- 统计每个年级的人数
    SELECT COUNT(1),GRADE FROM STUDENT GROUP BY SEX;
    -- 查询学生表中年级总人数大于3的年级
    -- 对分组查询的结果进行筛选时，需要使用having,而不能使用where
    SELECT GRADEID,COUNT(1) FROM STUDENT GROUP BY GRADEID HAVING COUNT(1) >3;
    -- 查询每个年级的男生女生人数
    SELECT GRADEID,SEX,COUNT(1) FROM STUDENT GROUP BY GRADEID HAVING COUNT(1) >3;
    -- 查询每个年级的男生女生人数
    SELECT GRADEID,SEX,COUNT(1) FROM STUDENT GROUP BY GRADEID,SEX;
     -- 查询每个年级中出生日期最近的
    SELECT ID,MAC(BIRTHDAY) FROM STUDENT GROUP BY ID;
    -- 查询每个年级的总课时
    SELECT GRADEID,SUM(CLASSHOUR) FROM `SUBJECT` GROUP BY GRADEID;

#### 4.子查询 ####

-- 子查询
    -- 查看年龄比‘流星’小的学生
    -- 1.查询流星的年龄
    SELECT BIRTHDAY FROM STUDENT WHERE NAME = '流星'; #2003-10-19
    -- 2.查询年龄比流星小的学生
    SELECT * FROM student WHERE BIRTHDAY > '2003-10-19';
    -- 使用子查询的写法
    SELECT * FROM STUDENT WHERE BIRTHDAY > (SELECT BIRTHDAY FROM STUDENT WHERE NAME = '流星');
    -- EXPLAIN 关键字是点明执行的顺序
    EXPLAIN SELECT * FROM STUDENT WHERE BIRTHDAY > (SELECT BIRTHDAY FROM STUDENT WHERE NAME = '流星');
    -- 查看流星是哪个年级的（要求展示年级名称）
    SELECT GRADEID FROM STUDENT WHERE NAME = '流星';
    SELECT GRADENAME FROM grade WHERE GRADEID = (SELECT GRADEID FROM STUDENT WHERE NAME = '流星');
    
    
    
    -- 查询年龄比流星小的学生的成绩
    -- 1.查询流星的年龄
    SELECT BIRTHDAY FROM STUDENT WHERE STUDENTNAME = '流星';
    -- 2.查询出生日期大于流星的学生信息
    SELECT * FROM STUDENT WHERE BIRTHDAY > '2003-10-19';
    
    SELECT * FROM STUDENT WHERE BORNDATE > (SELECT  BIRTHDAY FROM STUDENT WHERE STUDENTNAME = '流星');
    -- 3.查询学生表中学号存在于查询2中的结果
    -- 外层是父级 内层是子级
    -- 子级的查询结果必须与父级使用条件的字段保持一致
    -- 若子级一定返回唯一结果时，父级条件可以用= 若子级返回结果可能为多行时，父级条件必须用in
    SELECT * FROM RESULT WHERE STUDENTNO IN (SELECT STUDENTNO FROM STUDENT WHERE BIRTHDAY > (SELECT BIRTHDAY FROM STUDENT WHERE STUDENTNAME = '流星'))
    
    -- 查询计算机网络课程最近一次考试成绩，如果有80分以上的成绩，显示分数排在前5名的学员的学号和分数
    -- 1.查询计算机网络的课程编号
    SELECT SUBJECTNO FROM SUBJECT WHERE SUBJECTNAME = '计算机网络';
    -- 2.查询计算机网络课程最近一次的考试时间
    SELECT MAX(EXAMDATE) FROM RESULT WHERE SUBJECTNO = (SELECT SUBJECTNO FROM SUBJECT WHERE SUBJECTNAME = '计算机网络')
    
    SELECT * FROM RESULT WHERE EXAMDATE = (SELECT MAX(EXAMDATE) FROM RESULT WHERE SUBJECTNO = (SELECT SUBJECTNO FROM SUBJECT WHERE SUBJECTNAME = '计算机网络')
    ) AND SUBJECTNO = (SELECT SUBJECTNO FROM SUBJECT WHERE SUBJECTNAME = '计算机网络')
    -- 4.查询第三步结果中大于80分的信息，看是否能查询到结果
    SELECT * FROM RESULT WHERE EXAMDATE = (SELECT MAX(EXAMDATE) FROM RESULT WHERE SUBJECTNO = (SELECT SUBJECTNO FROM SUBJECT WHERE SUBJECTNAME = '计算机网络')
    ) AND SUBJECTNO = (SELECT SUBJECTNO FROM SUBJECT WHERE SUBJECTNAME = '计算机网络') AND STUDENTRESULT > 80
    -- 5.将步骤4作为最终的一个条件，判断步骤4的结果是否有记录，如果有记录则查询成绩表显示计算机网络最近以西考试成绩分数排在前4名的学生
    -- EXISTS关键字是判断查询结果是否存在的
    SELECT STUDENTNO,STUDENTRESULT FROM RESULT WHERE EXISTS (SELECT * FROM RESULT WHERE EXAMDATE = (SELECT MAX(EXAMDATE) FROM RESULT WHERE SUBJECTNO = (SELECT SUBJECTNO FROM SUBJECT WHERE SUBJECTNAME = '计算机网络')
    ) AND SUBJECTNO = (SELECT SUBJECTNO FROM SUBJECT WHERE SUBJECTNAME = '计算机网络') AND STUDENTRESULT > 80)
    AND SUBJECTNO = (SELECT SUBJECTNO FROM SUBJECT WHERE SUBJECTNAME = '计算机网络')
    AND EXAMDATE = (SELECT MAX(EXAMDATE) FROM RESULT WHERE SUBJECTNO = (SELECT SUBJECTNO FROM SUBJECT WHERE SUBJECTNAME = '计算机网络'))
    ORDER BY STUDENTRESULT DESC
    LIMIT 0,4