Mysql之查询

持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第4天，点击查看活动详情

DQL

• 所有的查询操作都用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 表名

样例：

--查询全部的学生
select * from `student`;
​
--查询指定字段
select `sudentno`, `studentname` from student;
​
--别名，给结果取一个名字，用as。也可以给字段和表取别名
select `studentno` as 学号, `studentname` as 学生姓名 from student as s;
​
--函数 concat(a, b)，拼接字符串
select concat('姓名：', studentname) as 新名字 from student;

去重 (distinct)

作用：去除select 查询出来的结果中重复的数据，重复的数据只显示一条

--查询有那些同学参加了考试，去重
select distinct `studentno` from result;

如果没有 distinct 则默认为 all

数据库的列（表达式）

语法：

select 表达式 from 表

--查询系统版本
select version();
​
--用来计算
select 100*3 - 1 as 计算结果;
​
--查询自增的步长（变量）
select @@auto_increment_increment;

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

where 条件子句

查询结合逻辑运算符

作用：检索数据中符合条件的值

--查询考试成绩在95-100之间，and 和 && 是相同
select studentno, `studentresult` from result
where studentresult >= 95 and studentresult <= 100;
​
--模糊查询（区间）
select studentno, `studentresult` from result
where studentresult between 95 and 100;
​
--除了100号学生之外的同学的成绩，not 和 != 和 <> 相同
select studentno,`studentresult` from result
where studentno != 1000;
--where not studentno = 1000;

模糊查询：比较运算符

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

样例：

1、like 运算符

--查询姓李的同学，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 '%嘉%';
​
--如果查询的字符串本身就有通配符，则用 escape'换码字符'
select `studentno`, `studentname` from `student`
where studentname like '李_' escape'';   --将_通配符转义

2、in 运算符

--查询1001，1002，1003号学员
select `studentno`, `studentname` from `student`
where studentno in(1001, 1002, 1003);

3、is /not null 运算符

--查询地址为空的学生
select `studentno`, `studentname` from `student`
where address = '' or address is null;

--查询有出生日期的学生
select `studentno`, `studentname` from `student`
where `borndate` is not null;

联表查询

join 对比

1、inner join

--查询参加了考试的同学（学号，姓名，科目编号，分数）
select s.studentno, studentname, subjectno, studentresult
from student as s
inner join result as r
on s.studentno = r.studentno;

2、right join

select student.studentno, studentname, subjectno, studentresult
from student
right join result
on student.studentno = result.studentno;

3、left join

select student.studentno, studentname, subjectno, studentresult
from student
left join result
on student.studentno = result.studentno;

三者对比：

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

如果不能理解可以看看这个 左右内外连接

语法：

join (连接的表)  on (判断的条件)  连接查询
where  等值查询

自连接

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

样例：

父类

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

子类

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

操作代码：

--查询父子信息：把一张表看成两个一样的表
select a.`categoryname` as `父栏目`, b.`categoryname` as `子栏目`
from `category` as a, `category` as b
where a.`categoryid` = b.`pid`;

操作结果：

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

分页和排序

排序

排序：升序（ASC），降序（DESC）

--查询的结果根据成绩降序排序
select s.`studentno`, `studentname`, `subjectname`, `studentresult`
from student
inner join `result`
on student.studentno = result.studentno
inner join `subject`
on result.`subjectno` = subject.`subjectno`
where subjectname = '数据结构-1'
order by studentresult asc;

分页

为什么要分页？

缓解数据库压力，体验更好

limit使用语法：

limit(查询起始下标, pagesize)

样例：

--每页只显示五条数据
select s.`studentno`, `studentname`, `subjectname`, `studentresult`
from student
inner join `result`
on student.studentno = result.studentno
inner join `subject`
on result.`subjectno` = subject.`subjectno`
where subjectname = '数据结构-1'
order by studentresult asc
limit 0, 5;

/*
第N页的limit设定： (n - 1) * pagesize, pagesize
pagesize是页面大小(页面显示的数据个数)
n表示当前页面
数据总数/页面大小 = 总页数，向上取整
*/

子查询（嵌套）

where 后面可以用计算出来的值

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

--查询数据库结构-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`, `subjectno`, `studentresult`
from `result`
where subjectno = (
	select studentno
    from `subject`
    where subjectname = '数据库结构-1'
)
order by studentresult desc

分组和过滤

语法：

group by 条件

样例：

--查询不同课程的平均分，用分组
select subjectname, avg(studentresult)
from result r
inner join subject s
on r.subjectno = s.subjectno
group by r.subjectno

集合查询

集合操作主要包括并操作(union)、交操作(intersect)和差操作(except)

并操作

就是将多个查询结果合并起来，默认去除重复元组，不去除用：union all

--查询选了课程1和课程2的学生学号
select sno from sc
where cno = 1
union
select sno from sc
where cno = 2;

交操作

相当于使用 and 运算符

--查询计算机系中年龄小于等于18岁的学生
select * from student
where sdept = 'cs'
intersect
select * from student
where sage <= 18;

差操作

第一个查询的结果去除第二个查询的结果

--查询计算机系中年龄小于等于18岁的学生
select * from student
where sdept = 'cs'
except
select * from student
where sage > 18;