DQL 查询数据
前言 爆肝学习了数据查询之后，为了理一理思路，在学习的时候做下了如下的笔记，供学习参考和交流！
这一章是MySQL的重点之一，查询数据在数据库非常重要，学好这一章是数据库的关键！
1、DQL （Data Query LANGUAGE：数据查询语言）
- 所有查询操作都用它 Select
- 简单的查询、复杂的查询它都能做
- 数据库最核心、重要的语句
- 使用频率最高的语句
CREATE DATABASE IF NOT EXISTS `school`;
-- 创建一个school数据库
USE `school`;
-- 创建学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
`studentno` INT(4) NOT NULL COMMENT '学号',
`loginpwd` VARCHAR(20) DEFAULT NULL,
`studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
`sex` TINYINT(1) DEFAULT NULL COMMENT '性别，0或1',
`gradeid` INT(11) DEFAULT NULL COMMENT '年级编号',
`phone` VARCHAR(50) NOT NULL COMMENT '联系电话，允许为空',
`address` VARCHAR(255) NOT NULL COMMENT '地址，允许为空',
`borndate` DATETIME DEFAULT NULL COMMENT '出生时间',
`email` VARCHAR (50) NOT NULL COMMENT '邮箱账号允许为空',
`identitycard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`studentno`),
UNIQUE KEY `identitycard`(`identitycard`),
KEY `email` (`email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
-- 创建年级表
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
`gradeid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
) ENGINE=INNODB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;
-- 创建科目表
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
`subjectno`INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
`subjectname` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
`classhour` INT(4) DEFAULT NULL COMMENT '学时',
`gradeid` INT(4) DEFAULT NULL COMMENT '年级编号',
PRIMARY KEY (`subjectno`)
)ENGINE = INNODB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8;
-- 创建成绩表
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(
`studentno` INT(4) NOT NULL COMMENT '学号',
`subjectno` INT(4) NOT NULL COMMENT '课程编号',
`examdate` DATETIME NOT NULL COMMENT '考试日期',
`studentresult` INT (4) NOT NULL COMMENT '考试成绩',
KEY `subjectno` (`subjectno`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;
select的使用：
-- 语法 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 别名
去重 distinct作用:去除select查询出来的结果中重复的数据,重复的数据只显示一条
--查询一下有哪些同学参加了考试(查询成绩)
select * from result --查询全部的考试成绩select `StudentNo` from result --查询有哪些同学参加了考试select distinct `StudentNo` from result--发现重复数据\去重
数据库的列(表达式)
--查询系统版本
select version()--函数--查询计算结果
select 100*10-1 as 计算结果--计算表达式--查询自增步长
select @@auto_increment_increment--变量--查询加分后成绩
SELECT `Studentno`,`Studentresult`+1 AS '加分后的成绩' FROM result
数据库中的表达式:文本值,列,null,函数,计算表达式,系统变量…
select 表达式 from 表
3、 where条件子句 作用：检索数据中符合条件的值
搜索的条件由一个或者多个表达式组成 ， 一般返回结果都是 boolen值
逻辑运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| and 或 && | a and b a&&b | 与，两个都为真，结果为真 |
| or 或 || | a or b a||b | 或，其中一个为真，结果为真 |
| not 或 ! | not a !a | 非，真为假，假为真 |
--查询成绩大于等于90的数据
select `studentno`,`studentresult` from result wherestudentresult>=90--查询成绩在90和100之间的数据
select `studentno`,`studentresult` from result wherestudentresult>=90 && studentresult<=100select `studentno`,`studentresult` from result wherestudentresult between 90 and 100--产线成绩不为60的数据
select `studentno`,`studentresult` from result where studentresult!=60select `studentno`,`studentresult` from result where not studentresult=60
模糊查询 ： 比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| is null | a is null | 如果操作符为null，则结果为真 |
| is not null | a is not null | 如果操作符为 not null，则结果为真 |
| between and | a between b and c | 如果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运算符:具体的一个或多个值
--查询10086,10087,10088号学生
select `studentno`,`studentname` from `student` where studentno in (10086,10087,10088)
is 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
4、联表查询
join 对比
文章图片
为了更深入了解，我们进行如下操作：
--查询参加了考试的同学(学号,姓名,科目编号,分数)
/*
思路
1、分析需求，分析查询字段来自哪里？（连接查询）
2、确定使用哪种连接查询？（一共7种）
确定交叉点（两个表哪个数据是相同的）
判断的条件：总学生表的 studentno = 成绩表中的studentno
*/--join(连接的表) on(判断的条件) 是连接查询
-- where 是等值查询select s.studentno,studentname,subjectno,studentresult
from student as s
inner join result as r
on s.studentno = r.studentno-- right join(result为右表)
select s.studentno,studentname,subjectno,studentresult
from student as s
right join result as r
on s.studentno = r.studentno-- left join(student为左表)
select s.studentno,studentname,subjectno,studentresult
from student as s
left join result as r
on s.studentno = r.studentno| 操作 | 描述 |
|---|---|
| inner join | 如果表中至少有一个匹配,就返回行 |
| left join | 即使右表中没有匹配,也会从左表中返回所有的值（以左为准） |
| right join | 即使左表中没有匹配,也会从右表中返回所有的值（以右为准） |
--查询缺考的同学
select s.studentno,studentname,subjectno,studentresult
from student as s
left join result as r
on s.studentno = r.studentno
where studentresult is null--查询科目成绩(三表连接 student result subject)
SELECT s.studentno AS '学生学号' ,`studentname` AS '学生姓名' ,`subjectname` AS '学科名称',`studentresult` AS '学生成绩'
FROM student s
RIGHT JOIN result r
ON s.studentno = r.studentno
LEFT JOIN SUBJECT sub
ON sub.subjectno = r.subjectno
要领：
1、我要查询哪些数据 select …
2、从哪及格表中查 from 表 xxx join 连接的表 on 交叉条件
3、假设存在一种多张表查询，先查询两张表，再一张一张加。
自连接自己的表和自己的表连接
核心：一张表拆为两张一样的表即可
举例
文章图片
父类
| categoryid | categoryName |
|---|---|
| 2 | 信息技术 |
| 3 | 软件开发 |
| 5 | 美术设计 |
| pid | categoryid | categoryName |
|---|---|---|
| 3 | 4 | 数据库 |
| 2 | 8 | 办公信息 |
| 3 | 6 | web开发 |
| 5 | 7 | ps技术 |
| 父类 | 子类 |
|---|---|
| 信息技术 | 办公信息 |
| 软件开发 | 数据库 |
| 软件开发 | web技术 |
| 美术设计 | ps技术 |
--查询父子信息:把一张表分为两张一模一样的表select `categoryName` as '父栏目',`categoryName` as '子栏目' from `category` as a , `category` as b
where a.`categoriyid` = b.`pid`
--查询学员所属的年纪(学号,学生姓名,年级名称)
select studentno,studentname,gradename
from student as s
inner join `grade` as g
on s.`gradeid` = g.`gradeid`--查询科目所属的年级(科目名称,年级名称)
select `subjectname`,`gradename`
from `subject` as sub
inner join `grade` as g
on sub.`gradeid` = g.`gradeid`--查询参加了 '数据库结构-1' 考试的同学信息 :学号,姓名,科目名,分数
select s.studentno,studentname,subjectname,studentresult
from student as s
inner join result as r
on s.studentno = r.studentno
inner join `subject` as sub
on r.subjectno = sub.subjectno
where subjectname = '数据库结构-1'
5、分页和排序 排序 order by
-- 排序分为俩种:升序 ASC 降序 DESC-- 引用上方的一个查询'数据库结构-1'成绩的例子
-- 查询的结果根据 成绩 降序排序
select s.studentno,studentname,subjectname,studentresult
from student as s
inner join result as r
on s.studentno = r.studentno
inner join `subject` as sub
on r.subjectno = sub.subjectno
where subjectname = '数据库结构-1'
order by studentresult desc
升序 asc
降序 desc
分页 limit
--分页,每页只显示5条数据
--语法,limit 起始值 , 页面的大小
/*
第n页 limit (n-1)*pagesize,pagesize
n代表当前页,pagesize表示页面容量
*/
select s.udentno,studentname,subjectname,studentresult
from student as s
inner join result as r
on s.studentno = r.studentno
inner join `subject` as sub
on r.subjectno = sub.subjectno
where subjectname = '数据库结构-1'
order by studentresult desc
limit 0,5
6、分组和过滤 分组 group by
过滤 having （不同于where，having是次要过滤）
举个例子
--查询不同课程的平均分,最高分,最低分,平均分大于80
select subjectname,avg(studentresult) as 平均分,max(studentresult) as 最高分,min(studentresult) as 最低分
from result as r
inner join `subject` as sub
on r.subjectno = sub.subjectno
group by r.subjectno --通过(学科编号)来分组
having 平均分>80 --次要条件过滤用having
7、子查询和嵌套查询 本质：在where语句中嵌套一个子查询语句
（我个人把它称为 表中查表 ， 由里及外的思想）
联表查询和子查询的使用对比：
inner join 更好理解
子查询、嵌套查询 代码量少
--1、查询 数据库结构-1 的所有考试结果（学号，科目编号，成绩），降序--方式一 联表查询
select studentno,r.subjectno,studentresult
from result as r
inner join subject as sub
on r.subjectno=sub.subjectno
where subjectname = '数据库结构-1'
order by studentresult desc--方式二 子查询
select studentno,subjectno,studentresult
from result
where subjectno = (
select subjectno
from subject
where subjectname = '数据库结构-1'
)
order by studentresult desc--2、查询 高等数学-2 分数不小于80分的学生的编号和姓名--方法一：联表查询
select distinct s.studentno,studentname
from student as s
inner join result as r
on s.studentno = r.studentno
inner join `subject` as sub
on sub.subjectno = r.subjectno
where subjectname = '高等数学-2'
and studentresult >= 80--方法二：子查询（嵌套查询）
/*
where 什么
就
select 什么
*/
select studentno,studentname
from student
where studentno in(
select studentno
from result
where studentresult>=80
and subjectno=(
select subjectno
from `subject`
where subjectname = '高等数学-2'
)
)-- 思考:
-- 查询 JAVA第一学年 课程成绩排名前十的学生,并且分数要大于80的学生信息(学号,姓名,分数)--方法一 联表查询
select s.studentno,studentname,studentresult
from student as s
inner join result as r
on s.studentno = r.studentno
inner join subject as sub
on r.subjectno = sub.subjectno
where subjectname = 'JAVA第一学年'
and studentresult>=80
order by studentresult desc
limit 0,10--方法二 子查询
select s.studentno,studentname,studentresult
from student as s
inner join result as r
where s.studentno = r.studentno
and studentresult>=80
and subjectno = (
select subjectno
from `subject`
where subjectname = 'JAVA第一学年'
)
order by studentresult desc
limit 0,10
8、select小结 select完整的语法：
select [all | distinct]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,.[.]]}
from table_name [as table_alias]
[left | right |inner/left/right join table_name] --联合查询
[where...]--指定结果满足的条件
[group by ...]--指定结果按照哪几个字段来分组
[having ...]--过滤分组的记录必须满足的条件
[order by ...]--指定查询记录按一个或多个条件排序
[limit x,y]--知道那个查询的记录从x开始至y
注意：[]括号代表的是可选的，{}括号代表的是必选的后话 MySQL的学习仍在继续，如果有任何错误或者问题可以在评论区共同交流，与君共勉！
【与君共勉|【MySQL】查询数据库中的数据】ps：我的上一篇MySQL学习笔记是：MySQL学习之《数据管理》，我的下一篇MySQL学习笔记是：MySQL学习之《函数使用》，有兴趣的小伙伴可以学习参考！
推荐阅读
- sql|Mysql的几种语言
- SQLite Android数据库游标窗口分配2048 kb失败
- 如何在android中使用游标（sqlite查询）搜索数据库
- Android BroadcastReceiver数据库访问与房间
- 加密的本地数据库，可在Electron app中使用
- 面试|【面试题】一致性 Hash 算法是什么（它带来了哪些问题以及解决方案）
- 如何在WordPress中使用Google Cloud SQL（）
- 如何在Shiny app中的仪表板主体中输出数据库表（）
- 如何在没有插件的情况下进行WordPress数据库备份