SQL语句-3

最新推荐文章于 2020-11-14 13:50:31 发布

原创最新推荐文章于 2020-11-14 13:50:31 发布 · 507 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

Java 专栏收录该内容

41 篇文章

订阅专栏

本文详细介绍了SQL的DQL（数据查询）语句，包括查询语法、order by子句、limit子句、子查询等。还介绍了使用函数（聚合、字符串、日期、数字函数）、in子查询、分组筛选、distinct去重、like关键字模糊查询以及关联查询（内连接、左外连接、右外连接）等内容，并给出了相应的代码示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

三，DQL(数据查询)语句

3.1 查询语法

SELECT <列名|表达式|函数|常量>

FROM <表名>

[where <查询条件表达式>]

[order by <排序的列名>[ASC或DESC]];

[LIMIT [位置偏移量,]行数];

**注意：语法中 <>中的内容是必须的，[]中的内容不是必须要的，视情况而定**

案例：查询gradeId = 1的数据并根据studentNo排序

~~~mysql
SELECT `studentNo`,`studentName`,`phone`,`address`,`bornDate`
FROM `student`
WHERE `gradeId` = 1
ORDER BY `studentNo`;
~~~

查询全部的列：

~~~mysql
# 格式：select * from 表名;
# 案例：select * from student;
~~~

查询部分列：

~~~mysql
SELECT `studentNo`,`studentName`,`address`
FROM `student`
WHERE `address`=‘河南新乡’;
~~~

3.2 order by子句

order by子句实现按一定顺序显示查询结果

~~~mysql
#案例：把成绩都降低10%后加5分，再查询及格成绩，并按照成绩从高到低排序
#参考代码
SELECT `studentNo` AS 学生编号,(studentResult*0.9+5 ) AS 综合成绩
FROM `result`
WHERE (`studentResult`*0.9+5) >=60
ORDER BY studentResult DESC;

#注意：order by 默认的是升序排列，如果需要降序就在order by字段后面加desc，升序排列可以在字段后面加asc

~~~

3.3 limit 子句：限制结果集显示条数

~~~mysql
#案例：
#查询所有年级编号为1的学员信息，按学号升序排序
#每页4条，显示第2页，即从第5条记录开始显示4条数据
SELECT `studentNo`,`studentName`,`phone`,`address`,`bornDate`
FROM `student`
WHERE `gradeId` = 1
ORDER BY studentNo
LIMIT 4,4;
#注意：使用LIMIT子句时，注意第1条记录的位置是0！

~~~

3.4 子查询

**案例一：**

编写SQL语句，查看年龄比“李斯文”小的学生，要求显示这些学生的信息

**分析：**

第一步：查询得到“李斯文”的出生日期

~~~mysql
SELECT `bornDate` FROM `student` WHERE `studentName` = '李斯文';
~~~

加入查询出来李斯文的出生日期是 1993-07-23

第二步：利用WHERE语句，筛选出生日期比“李斯文”大的学生

~~~mysql
SELECT `studentNo`, `studentName`,`sex`,`bornDate`,`address` FROM `student` WHERE bornDate > '1993-07-23';
~~~

上面使用两步可以实现案例一的需求，同时我们也可以用子查询来解决这个问题：

SELECT studentNo,studentName,sex,bornDate,address FROM student

WHERE bornDate> (**SELECT bornDate FROM student WHERE studentName='李斯文'**);

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

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

SELECT … FROM 表1 WHERE 字段1 比较运算符(子查询)

**将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个**

3.5查询数据-使用函数

3.5.1 聚合函数

| 函数名 | 作用 |
| ------- | ------------------ |
| AVG() | 返回某字段的平均值 |
| COUNT() | 返回某字段的行数 |
| MAX() | 返回某字段的最大值 |
| MIN() | 返回某字段的最小值 |
| SUM() | 返回某字段的和 |

3.5.2 字符串函数

| 函数名 | 作用 | 举例 |
| -------------------------- | ---------------- | ------------------------------------------------------------ |
| CONCAT(str1, str1...strn) | 字符串连接 | SELECT CONCAT('My','S','QL');返回：MySQL |
| INSERT(str,pos,len,newstr) | 字符串替换 | SELECT INSERT( '这是SQL Server数据库', 3,10,'MySQL');返回：这是MySQL数据库 |
| LOWER(str) | 将字符串转为小写 | SELECT LOWER('MySQL');返回：mysql |
| UPPER(str) | 将字符串转为大写 | SELECT UPPER('MySQL'); 返回：MYSQL |
| SUBSTRING (str,num,len) | 字符串截取 | SELECT SUBSTRING( 'JavaMySQLOracle',5,5);返回：MySQL |

3.5.3 日期函数

| 函数名 | 作用 | 举例（结果与当前时间有关） |
| --------------------- | -------------------------------------- | --------------------------------------- |
| CURDATE() | 获取当前日期 | SELECT CURDATE();返回：系统当前日期 |
| CURTIME() | 获取当前时间 | SELECT CURTIME();返回：系统当前时间 |
| NOW() | 获取当前日期和时间 | SELECT NOW();返回：系统当前时间和日期 |
| WEEK(date) | 返回日期date为一年中的第几周 | SELECT WEEK(NOW()); |
| YEAR(date) | 返回日期date的年份 | SELECT YEAR(NOW()); |
| HOUR(time) | 返回时间time的小时值 | SELECT HOUR(NOW()); |
| MINUTE(time) | 返回时间time的分钟值 | SELECT MINUTE(NOW()); |
| DATEDIFF(date1,date2) | 返回日期参数date1和date2之间相隔的天数 | SELECT DATEDIFF(NOW(), '2008-8-8'); |
| ADDDATE(date,n) | 计算日期参数date加上n天后的日期 | SELECT ADDDATE(NOW(),5); |

3.5.4 数字函数

| 函数名 | 作用 | 举例 |
| -------- | ----------------------------- | ------------------------------------- |
| CEIL(x) | 返回大于或等于数值x的最小整数 | SELECT CEIL(2.3)返回：3 |
| FLOOR(x) | 返回小于或等于数值x的最大整数 | SELECT FLOOR(2.3)返回：2 |
| RAND() | 返回0~1间的随机数 | SELECT RAND()返回：0.5525468583708134 |

3.6 in 子查询

~~~mysql
# 格式： select ... from 表名 where 字段 in (列表)；
# 案例：查询出分数为 60，95 ,71 分的学生编号，和分数
select studentNo ,studentResult from result
where studentResult=60 or studentResult=95 or studentResult=71;
# 使用in
select studentNo ,studentResult from result where studentResult in (60,95,71);

# 案例：查询出哪些同学的分数大于71分，显示出学生名称
# 步骤一，查询出分数大于71的学生编号
select studentNo from result where studentResult>71； -- (10002,10005,10096,10008)
# 步骤二，查出学生编号在步骤一的集合中的学生名称和编号
select studentNo,studentName from student where studentNo
in(select studentNo from result where studentResult>71);

# 如果格式为： select ... from 表名 where 字段 in(子查询)
# in括号中为子查询时，子查询返回的列只能是一列，否则就会出错
# 比如：下面的in中的子查询，查询studentResult，studentNo 两列，执行语句时会报
# Operand should contain 1 column(s) 错误
select studentNo,studentName from student where studentNo
in(select studentResult, studentNo from result where studentResult>71);
~~~

案例：查询参加“Logic Java”课程最近一次考试的在读学生名单

分析：

第一步：获得 “Logic Java”课程的课程编号

```mysql
SELECT `subjectNo` FROM `subject` WHERE `subjectName`='Logic Java';
```

第二步：根据课程编号查询得到“Logic Java”课程最近一次的考试日期

```mysql
SELECT MAX(`examDate`) FROM `result` WHERE `subjectNo`= (
SELECT `subjectNo` FROM `subject`
WHERE `subjectName`='Logic Java' );
```

第三步：根据课程编号和最近一次的考试日期查询出在读学生信息

```mysql
SELECT `studentNo`, `studentName` FROM `student`
WHERE `studentNo IN (
SELECT `studentNo` FROM `result`
WHERE `subjectNo` IN (
SELECT `subjectNo` FROM `subject`
WHERE `subjectName`=' Logic Java'
) AND `examDate` = (
SELECT MAX(`examDate`) FROM `result`
WHERE `subjectNo` = (
SELECT `subjectNo` FROM `subject`
WHERE `subjectName`='Logic Java '
)
)
);

```

练习：查询“Logic Java”课程考试成绩为60分的学生名单

参考代码

```mysql
SELECT `studentName` FROM `student`
WHERE `studentNo` IN(
SELECT `studentNo` FROM `result`
WHERE `subjectNo` = (
SELECT `subjectNo` FROM `subject`
WHERE `subjectName`='Logic Java'
)AND `studentResult` = 60
);

```

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

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

3.7 exists关键字

如何用SQL语句检测表是否创建？

~~~mysql
drop table if exists 表名 ;
~~~

**exists作用在子查询中**

~~~mysql
# 格式 select ... from 表名 where exists(子查询)
# 子查询有结果返回：exists(子查询) 结果为true
# 子查询没有结果返回: exists(子查询) 结果就为false，外层查询不执行
# 比如：判断是否有成绩大于80的数据
select exists(select*from result where studentResult>80);

# 案例：检查"Logic Java" 课程最近一次考试成绩
# 如果考试成绩有80分以上的成绩，显示分数排在前5名的学员学号和分数

# 分析：
# 采用exists 检测是否有人考试成绩达到了80分以上
# 如果有，使用select语句查询成绩从高到低排序，显示前5名的学号和成绩

# 第一步：查询result表中所有Logic Java 的考试成绩
select * from result where subjectNo=(select subjectNo from subject where subjectName="Logic Java" );
# 第二步：查询出成绩大于80的数据
select * from result where subjectNo=(select subjectNo from subject where subjectName="Logic Java" ) and studentResult>80;

# 综合
select studentNo ,studentResult
from result where exists(
select * from result where subjectNo=(
select subjectNo from subject where subjectName="Logic Java" )
and studentResult>80 )
order by studentResult desc
limit 0,5;
~~~

3.8分组查询 group by

~~~mysql
问题：查询出每门课的平均成绩
## 查询所有的平均成绩
select avg(studentResult) avgresult from result;

# 分组查询的格式
# select ... from 表名 where ... group by 分组字段;
# 表示根据某个字段进行分组
select subjectNo ,avg(studentResult) avgresult from result group by subjectNo;

# 练习1：查询出每门课的总成绩
# 练习2：分别统计每个年级的人数
select count(*) from student group by gradeId;

# 问题：分别统计每个年级男，女生人数
select gradeId 年级, sex 性别 ,count(*) 人数 from student group by gradeId ,sex;
# 注意：如果需要根据多个字段分组，可以将多个字段都加到group by后面，用逗号隔开

# 问题：查询出每门课的平均成绩，按照成绩从高到低排序
select subjectNo ,avg(studentResult) avgresult from result
group by subjectNo
order by avg(studentResult) desc;
~~~

3.9分组筛选

~~~mysql
# 格式：select ... from 表名 where ... group by ... having...
# 问题：查询出平均成绩大于68分的的课程编号
# 步骤一：查询出每门课的平均成绩，及课程编号
select subjectNo ,avg(studentResult) avgresult from result group by subjectNo;
# 步骤二：筛选出平均成绩大于68分的课程编号
select subjectNo ,avg(studentResult) avgresult from result
group by subjectNo
having avg(studentResult)>68;
~~~

3.10 distinct 去重

~~~mysql
# 问题：查询出成绩表中有哪些课程的成绩，显示出课程名称
select subjectName from subject where subjectNo in (select subjectNo from result);

select subjectNo from result；
-- 此条查询语句，会查询出很多重复数据，这些重复数据在列表中，会影响查询性能，一般会选择去掉重复数据。

# distinct 关键字使用
# 格式： select distinct ... from 表名；
select distinct subjectNo from result;
# 表示查询subjectNo ，并去掉重复数据

select distinct subjectNo,studentResult from result
# 表示查询 subjectNO ,studentResult ，并去掉重复的结果数据

# 注意：
# distinct 会将后面的字段值看做一个整体，只有当字段值都一样时才会认为是重复数据
# dustinct 只能卸载select 后面
# 比如下面写法会出错
select subjectNo , distinct studentResult from result;
~~~

3.11 like 关键字：模糊查询

~~~mysql
# 问题：查询出姓李的学生数据
# 使用like模糊查询解决问题

# 格式： select ... from 表名 where 字段 like ...
select * from student where studentName like '_斯_';
# 注意：下划线 _ 表示一个占位符，表示此处一个内容
# %表示此处可以有0个或者多个内容
select * from student where studentName like '李%';

#练习：查询出名字中包含 ‘文’ 字的学生数据
select * from student where studentName like '%文%'；
~~~

3.12 关联查询

3.12.1 内连接（inner join）

~~~mysql
# 内连接格式：
# select ... from 表1 inner join 表2 on 关联字段关系

#查询出学号，学生成绩，以及课程名称
select result.studentNo ,result.studentResult,subject.subjectName
from result inner join subject on result.subjectNo = subject.subjectNo;

#使用别名
select rs.studentNo ,rs.studentResult ,sb.subjectName
from result rs inner join subject sb on rs.subjectNo = sb.subjectNo;

# 注意： on 后面写的是多表关联的字段

# 格式: select ... from 表1 ，表2 where 关联字段关系
select result.studentNo ,result.studentResult,subject.subjectName
from result,subject where result.subjectNo = subject.subjectNo;

# 练习：查询出分数大于71 的学生姓名，和分数

select st.studentName ,rs.studentResult from student st
inner join result rs on st.studentNo=rs.studentNo
where rs.studentResult >71 ;
# 注意：关联查询中，使用字段时，建议在字段前面都加上表名. ,表示区分使用的哪张表中的字段

# 练习：查询出分数大于71的学生姓名，分数，以及课程名称(三张表关联)
select st.studentName ,rs.studentResult ,sb.subjectName from student st
inner join result rs on st.studentNo=rs.studentNo
inner join subject sb on rs.subjectNo=sb.subjectNo
where rs.studentResult > 71;

#使用where 格式实现三张表关联
select st.studentName,rs.studentResult,sb.subjectName
from student st, result rs , subject sb
where st.studentNo= rs.studentNo and
rs.subjectNo = sb.subjectNo and
rs.studentResult > 71;

~~~

3.12.2 左外连接

~~~mysql
# 格式: select ... from 表1 left join 表2 on ....
# 查询出每个学生姓名以及成绩
select st.studentName , rs.studentResult from student st
left join result rs on st.studentNo = rs.studentNo;
~~~

查询结果

~~~mysql
+-------------+---------------+

| studentName | studentResult |

+-------------+---------------+

| 郭靖 | 71 |

| 郭靖 | 60 |

| 李文 | 46 |

| 李斯文 | 83 |

| 张萍 | 60 |

| 韩秋洁 | 60 |

| 张秋丽 | 95 |

| 肖梅 | 93 |