MySQL（二）查询操作

2023-08-09 12:40:52

单表查询

  • 基本格式

select [ all | distinct ] selection_list

from table_score

[where search_condition]

[group by grouping_colums] [with rollup]

[having search_condition]

[order by order_expression [asc \ desc]] --asc升 序、desc降序

[limit count] --限制查询的输出结果行

 

SQL select_expr

  • 为字段取别名

    select 项的名称 as 别名

  • 使用谓语过滤记录

    利用distinct可过滤重复的行

    select distinct column

 

SQL where

通过 where 子句可以实现很多复杂的条件查询，需要配合运算符确定查询条件

语法：SELECT 列名称 FROM 表名称 WHERE 列 运算符 值

# 示例 ##################################################################
#查询表student中入学成绩在800分以上的学生的学号、姓名和电话信息。
select studentno, sname, phone
from student
where entrance > 800;

 

  • 带 in 关键字的查询

    in 关键字可以判断某个字段的值是否在指定的集合中

    使用 in 搜索条件相当于用 or 连接两个比较条件

    如 x in(10,15) 相当于表达式 x=10 or x=15

    也可使用 not in 查询不在范围内的数据

# 示例 ##################################################################
#查询学号分别为123、456和789的学生学号、课程号、平时成绩、期末成绩。
select studentno, courseno, daily, final
from score
where studentno in ('123','456','789');

 

  • 带 between and 的范围查询

    使用 between 搜索条件相当于用 and 连接两个比较条件

    如 x between 10 and 27 相当于表达式 x>=10 and x<=27

    检索条件指定排除某个范围的值，一般可以用 关键字来实现

# 示例 ##################################################################
#查询选修课程号为c05109的学生学号和期末成绩，并且要求平时成绩在80～95分。
select studentno, final
from score
where courseno='c05109'and daily between 80 and 95;

 

  • 带 like 的字符匹配查询

    like 可以用于实现模糊查询，包含两种通配符 % 和 _

    % 可以匹配一个或多个字符，可以代表任意长度的字符串，长度可以为0

    _ 只匹配一个字符

# 示例 ##################################################################
#在student表中显示所有姓何或者姓韩的学生的姓名、生日和Email。
select sname, birthdate, Email
from student
where sname like '何%' or sname like '韩%';

 

  • 用 is null 关键字查询空值

    一个字段值是空值或者不是空值，要么表示为 is null 或 is not null ；不能表示为 =null 或 <>null

    如果写成 字段=null 或 字段<>null ，系统的运行结果都直接处理为 null 值，按照 false 处理而不报错

    通用格式 column is [not] null

# 示例 ##################################################################
#在se_score表中添加成绩字段score,查询se_score表中学生的学号、课程号和成绩。
alter table se_coruse
add score float(3,1) null after teacherno;

select studentno, courseno, teacherno, score
from se_course
where score is null;

 

  • 带 and 的多条件查询

    利用指定的条件选择结果集中的行

    条件表达式中字符型和日期类型值要放到单引号中

    并列的条件不能出现逗号

# 示例 ##################################################################
#在score表中显示期中成绩高于90分，期末成绩高于85分的学生学号、课程号和成绩。
select studentno, courseno, daily, final
from score
where daily>=90 and final>=85;

 

  • 带 or 的多条件查询

    or 可以用来连接两个条件表达式

# 示例 ##################################################################
#查询计算机学院的具有高级职称教师的教师号、学生和从事的专业。
select teacherno, tname, major
from teacher
where department='计算机学院' and (prof='副教授' or prof='教授');

 

{% note info simple %}如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。

如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。{% endnote %}

#子查询返回一个值，标量子查询
#不需要给子查询取别名

#where子查询内的表，不能直接用以更新
select * from tb where money = (select max(money) from tb);

#列子查询
	#如果子查询结果返回的是一列
	#使用 in 或 not in 完成查询
	
	#如果子查询返回数据，则返回1或0，常用于判断
	select column from t1 where exists (select * from t2);

#行子查询
	#查询条件是一个行
	select * from t1 where (id,gender) in (select id, gender from t2);
	#行构造符
	#行构造符通常用于对能返回两个或两个以上列的子查询进行比较
	（col1, col2, ...）或 row (col1, col2, ...)

  

  • 特殊运算符

    ! = all 相当于 not in

    = some() 相当于 in 。 any 是 some的别名

    = some() 不等同于 not in ，不等于其中某一个

    all 、some 可以配合其他运算符一起使用

 

SQL group by & having

group by 子句可以将查询结果按属性列或属性列组合在行的上方进行分组，每组在属性列或属性列组合上具有相同的聚合值。

将一列或多列定义成为一组，是组内所有的行在那些列中的数值相同。出现查询的 select 列表中的每一列都必须同时出现在 group by 子句中

 

  • 使用 group by 关键词来分组

    单独使用 group by 关键词，查询结果只显示每组的一条记录。

# 示例 ##################################################################
#利用 group by 子句对 score 表的数据分组，显示每个学生的学号和平均总评成绩。
#avg函数用于求平均值，round函数用于对平均值的某位数据进行四舍五入。
select student 学号, round(avg(daily*0.3 + final*0.7),2)as'平均分'
from score
group by studentno;

 

  • group by 关键字与 group_concat() 函数一起使用

    可以将每个组中的所有字段都显示出来。

# 示例 ##################################################################
#对 score 表中的 studentno 字段进行分组查询，可以查看选该门课程的学生学号。
select courseno 课程号, group_concat(studentno) 选课学生学号
from score
group by courseno;

 

  • group by 关键字与 having 一起使用

    having 子句通常与 group by 子句结合使用，having 子句指定在应用 where 子句的筛选后要进一步应用的筛选。

# 示例 ##################################################################
#查询选课在3门以上且各门课程期末成绩均高于75分的学生的学号及其总成绩，查询结果按总成绩降序列出。
select studentno 学号, sum(daily*0.3 + final*0.7) as '总分'
from score
where final>=75
group by studentno
having count(*)>=3
order by sum(daily*0.3 + final*0.7) desc;

 

SQL order by

使用 order by 子句可以对查询的结果进行升序（asc）或降序（desc）排列。

事项和原则：

  1. 默认情况下，结果集按照升序排列
  2. order by 子句包含的列并不一定出现在选择的列表中
  3. order by 子句可以通过制定列名、函数值和表达式的值进行排序
  4. order by 子句不可以使用text、ntext或image类型的列
  5. 在order by 子句中可以同时出现多个排序项
# 示例 ##################################################################
#在student表中查询高于850分的学生学号、姓名和入学成绩，并按照入学成的降序排列。
select studentno 学号, sname 姓名, entrance 入学成绩
from student
where entrance > 850
order by entrance desc;

 

SQL limit

limit 是用来限制查询结果的数量的子句。可以指定查询结果从哪条记录开始显示，还可以指定一共显示多少条记录。limit 可以指定初始位置。

# 示例 ##################################################################
#查询score表中，期末成绩final高于85分的，按照平时成绩daily进行升序排列，从编号2开始，查询5条记录。
select * from score
where final>85
order by daily desc
limit 2,5;

 

 

聚合函数查询

MySQL常用的聚合函数包括 count()、sum()、avg()、max()、min() 等

count() 用来统计数据的条数

sum() 用来计算字段的值的总和

avg() 用来计算字段的值的平均值

max() 用来查询字段的最大值

min() 用来查询字段的最小值

 

SQL count()

count() 函数对于“*”以外的任何参数，返回所选择聚合中非null值的行的数目

对于参数“*”，返回选择聚合所有行的数目，包含null值的行

# 示例 ##################################################################
#通过查询求18级学生的总数。
select count(studentno) as '18级学生数'
from student
where substring(studentno,1,2)='18';

 

SQL sum() / ayg()

sum() 函数可以求出表中某个字段取值的总和

avg() 函数可以求出表中某个字段去值的平均值

# 示例 ##################################################################
#查询score表中学生的期末总成绩大于270分的学生学号、总成绩及平均成绩。
select studentno 学号, sum(final) 总分, avg(final) 平均分
from score
group by studentno
having sum(final) > 270
order by studentno;

 

SQL max() / min()

max() 函数可以求出表中某个字段取值的最大值

min() 函数可以求出表中某个字段取值的最小值

# 示例 ##################################################################
#查询选修课程号为c05109的课程的期末最高分、最低分及之间相差的分数。
select max(final) 最高分, min(final)最低分 , max(final)-min(final) as 分数
from score
where course='c05109';

 

SQL with rollup

可以在分组统计数据的基础上在进行相同的总日统计

# 示例 ##################################################################
#查询score表中每一门课的期末平均成绩和所成绩的平均值。
select course 课程号, avy(final) 课程期末平均分
from score
group by courseno with rollup;

 

 

多表连接

被显示定义的与连接有关的关键字如下：

  1. inner join：内连接，结果只包含满足条件的列
  2. left outer join：左外连接，结果只包含满足条件的行及左侧表中的全部行
  3. right outer join：右外连接，结果只包含满足条件的行及右侧表中的全部行
  4. cross join：结果只包含两个表中的所有行的组合，指明两表间的笛卡尔积操作

 

内连接

内连接查询是通过比较数据源表间共享列的值，从多个源表检索符合条件的行的操作。可以使用等号运算符的连接，也可以连接两个不相等的列的值。

# 示例 ##################################################################
#查询选修课程号为c05109的学生的学号、姓名和期末成绩。
select student.studentno, sname, final
from student inner join score
on student.studentno = score.studentno
where score.courseno = 'c05109'

#方法二：
select student.studentno, sname, final
from student, score
where student.studentno=score.studentno and score.course = 'c05109';

 

左外连接

左外连接是指将左表中的数据分别与右表中的每条数据进行连接和结合，返回的结果除内连接的数据外，还包括左表中不符合条件的数据，并在右表的相应列中添加 null 值。

# 示例 ##################################################################
#在mysqltest数据库中利用左外连接方式查询学生的学号、姓名、平时成绩和期末成绩。
use mysqltest
select student.studentno, sname, daily, final
from student left join score
on student.studentno = score.studentno;

 

右外连接

右外连接包含 join 子句中最右侧表的所有行。如果右侧表中的行与左侧表中的行不匹配，将为结果集中来自左侧表的所有列分配 null 值。

# 示例 ##################################################################
#利用右外连接方式查询教师的排课情况。
select teacher.teacherno, tname, major, course
from teacher right join teach_course
on teacher.teacherno=teacher_course.teacherno;

 

交叉连接

交叉连接是在没有 where 子句的情况下，产生的表的笛卡尔积。两个表作交叉连接时，结果集大小为二者行数之积。

# 示例 ##################################################################
#显示student表和score表的笛卡尔积。
select student.student, sname, score
from student corss join score;

 

连接多个表

对于三个以上关系表的连接查询，一般遵循下列规则：

连接n个表至少需要n-1个连接条件，比避免笛卡尔积的出现

为了缩小结果集，采用多余n-1个连接条件或使用其他条件都是允许的

# 示例 ##################################################################
#查询18级学生的学号、姓名、课程号、期末成绩及学分。
select student.studentno, sname, cname, final, round(period/16,1)
from score join student on student.studentno=score.studentno
	join course on score.course=course.courseno
where substring(student.studentno,1,2)='18';

 

合并多个结果集

union 操作可以将多个 select 语句的返回结果组合到一个结果集中。

使用 union 合并两个查询结果集时，所有查询中的列数和列的顺序必须相同且数据类型必须兼容。

语法格式如下：

select_statement union [all] select_statement

格式参考说明：

  1. select_statement：select 语句
  2. union：指定组合多个结果集并返回为单个结果集
  3. all：将所有行合并到结果中，包括重复的行。如果不指定，将删除重复的行
# 示例 ##################################################################
#在mysqltest数据库中利用student表创建student01，将student01和student表的部分查询结果集合并。
create table student01 as
select studentno, sname, phone from teaching.student;

select studentno, sname, phone from student01
where phone like '%131%'
union
select studentno, sname, phone from teaching.studnet
where phone like '%132%'

 

连接查询

#将多个表的字段进行连接，可以指定连接条件

#内连接 （inner join）
	#默认就是内连接，可省略inner
	#只有数据存在时才能发送连接，即连接结果不能出现空行
	#on 表示连接条件，其表达式于where类似，也可以省略条件（表示条件永远为真）
	#也可使用where表示连接条件
	#如果使用 using ，需字段名相同  
	using (`字段名`);
	
	#交叉连接 （cross join）
	#没有条件的连接
	select * from tb1 cross join tb2;
	
#外连接 （outer join）
	#如果数据不存在，也会出现在连接结果中
	
	#左外连接 （left join）
	#如果数据不存在，左表记录会出现，而右表为null填充
	
	#右外连接 （right join）
	#如果数据不存在，右表记录会出现，而左表为null填充
	
#自然连接 （natural join）
	#自动判断连接条件完成连接
	#相当于省略了using，会自动查找相同字段
	natural join;
	natural left join;
	natural right join;

 

 

子查询

  1. 子查询的执行过程

    MySQL对嵌套查询的处理过程是从内层向外层处理，即先处理最内层的子查询,然后把查询的结果用于其外查询的查询条件，再层层向外求解,最后得出查询结果。

  2. 子查询连接的关系

    一般情况下，包含子查询的查询语句可以写成连接查询的方式。因此，通过子查询也可以实现多表之间的查询。在有些方面，多表连接的性能要优于子查询，原因是连接不需要查询优化器执行排序等额外的操作。

  3. 子查询中的常见运

    子查询中可以包括 in、not in、any、all exists、not exists 等逻辑运算符，也可以包含比较运算符，如 “=” “!=” “>” 和 “<” 等。

  4. 子查询的类型：

    ● 返回一个表的子查询是表子查询。
    ● 返回带有一个或多个值的一行的子查询是行子查询。
    ● 返回一行或多行，但每行上只有一个值的是列子查询。
    ● 只返回一个值的是标量子查询。从定义上讲,每个标量子查询都是一个列子查询和行子查询。

  5. 使用子查询时应该注意如下的事项：

    ● 子查询需要用括号括起来。子查询中也可以再包含子查询，嵌套可以多至32层。

    ● 当需要返回一个值或一个值列表时,可以利用子查询代替一个表达式。也可以利用子查询返回含有多个列的结果集替代表或连接操作相同的功能。
    ● 子查询不能够检索数据类型为 varchar ( max)、nvarchar( max) 和 varbinary( max) 的列。
    ● 子查询使用 order by 时，只能在外层使用，不能在内层使用。

 

表达式

在MySQL语句中，可以把子查询的结果当成一个普通的表达式来看待，用在其外查询的选择条件中。此时子查询必须返回一个值或一个单个列值列表，此时的子查询可以替换 where 子句中包含 in 关键词的表达式。

# 示例 ##################################################################
#查询学号为18125121107的学生的入学成绩、所有学生的平均入学成绩及该学生成绩与所有学生的平均入学成绩的差。
select studentno, sname, entrance
(select avg(ectrance) from student) 平均成绩,
ectrance - (select avg(ectrance) from student) 分差
from student
where studentno='18125121107';

 

派生表

利用子查询生成一个派生表，用于替代 from 子句中的数据源表，派生表可以定义一个别名，即子查询的结果集可以作为外层查询的源表。实际上是在 from 子句中使用子查询作为派生表数据源。

# 示例 ##################################################################
#查询期末成绩高于85分、总评成绩高于90分的学生的学号、课程号和成绩
select TT.student 学号, TT.courseno 课程号, TT.final*0.8+TT.daily*0.2 总评
from (select * from score where final>85) as TT
where TT.final*0.8+TT.daily*0.2 > 90;

 

where 子查询

where 语句中的子查询实际上是将子查询的结果作为该语句条件中的一部分，然后利用这个条件过滤本层查询的数据。

 

  • 比较运算符

    比较运算符包括 =、!=、>、>=、<、<=

# 示例 ##################################################################
#查询期末成绩比选修课程平均期末成绩低的学生的学号、课程号和期末成绩
select studentno, course, final
from score a
where final <(select avg(final)
             from score b
             where a.courseno=b.courseno
             group by courseno);

 

  • in / not in

    in 运算符可以检测结果集中是否存在某个特定的值，如果检测成功就执行外部的查询。

# 示例 ##################################################################
#获取期末成绩中含有高于93分的学生的学号、姓名、电话和Emial
select studentno, sname, phone, Emial
from student
where studentno in (select studentno
                   from score
                   where final>93);

 

  • exists / not exists

    使用 exists 关键字时，内层查询语句不返回查询的记录，而是返回一个真假值。

    当返回值为 true 时，外层查询语句将进行查询；当返回 false 时，外层查询语句不进行查询或查询不出任何记录。

# 示例 ##################################################################
#查询student表中是否存在2001年12月12日以后出生的学生，如果存在，输出学生的学号、姓名、生日和电话
select studentno, sname, birthdate, phone
from student
where exists (select *
             from student
             where birthdate<'2001-12-12');

 

  • 对比较算法进行限制

    all、some和any运算都是比较运算的进一步限制。使用 all 时，当所有值都满足比较的关系时才返回 true。some 或 any 时同义词，表示表达式只要与子查询结果集中的某个值满足比较的关系时就返回 true

# 示例 ##################################################################
#查找score表中所有比c05109课程期末成绩都高的学号、姓名、电话和期末成绩
select student.studentno, sname, phone, final
from score inner join student
on score.studentno = student.studentno
where final>all (select final
                from score
                where course='c05109');

 

增、更、删

利用子查询修改数据，就是利用一个嵌套在insert、update或 delete 语句的子查询成批地增加、更新和删除表中的数据。

  • 插入

    insert 语句中的 select 子句可用于讲一个或多个其他的表或视图的值添加到表中。使用 select 子查询可同时插入多行。

# 示例 ##################################################################
#将student表中2001年以后出生的学生记录添加到student02表中。
insert into mysqltest.student02 (select * from student
                                where birthdaye >= '2001-12-31');

 

  • 更新

    update 语句中的 select 子查询可用于将一个或多个其他的表或视图的值进行更新。使用 select 子查询可同时更新多行数据。实际上是通过将子查询的结果作为更新条件表达式中的一部分。

# 示例 ##################################################################
#将student表中入学成绩低于800分的所有学生的期末成绩增加5%
update score
set final=final*1.5
where studentno in (select studentno from student
                   where entrance < 800);

 

模糊查询

  • 字符 / 字符串开头

    使用字符“^”可以匹配以特定字符或字符串开头的记录。

# 示例 ##################################################################
#查询student表中姓“赵”的学生的部分信息
select studentno, sname
from student
where sname regexp '^赵';

 

  • 字符 / 字符串结尾

    使用字符“$”可以匹配以特定字符或字符串结尾的记录。

# 示例 ##################################################################
#查询student表中学生电话号码尾数为5的学生的部分信息
select studentno, sname, phone
from student
where phone regexp '5$';

 

  • 符号“.”

    用正则表达式来查询时，可以使用“.”来替代字符串中的任意一个字符。

# 示例 ##################################################################
#查询student表中学生姓名以“赵”开头，以“江”结尾的学生的部分信息
select studentno, sname
from student
where phone regexp '^赵..江$';

 

  • 匹配指定字符串

    正则表达式可以匹配字符串。如果指定多个字符串，需要用符号“|”隔开，只要匹配这些字符中的任意一个即可。

# 示例 ##################################################################
#查询学生电话号码出现131或132数字的学生信息
select studentno, sname
from student
where phont regexp '131 | 132';

 

❤️ END ❤️
上一篇：mysql创建索引的原则


下一篇：mysql查询案例