《四》MySQL基本查询

        表查询操作是数据库中最重要的操作（没有之一），可以说对于数据库，每天的工作就是组合各种各样的查询语句进行不同场景的数据查询。

1. 基础查询

查询所有列 

select * from 表名;
例：
select * from student;

查询指定列

• 查询时可以使用as为列或表指定别名。

select 列1,列2,... from 表名;
例：
select id, name from student;
例（使用别名）：
select id as id2, name from student;

 

2. 条件查询

使用where子句对表中的数据筛选，结果为true的行会出现在结果集中

 语法如下:

select * from 表名 where 条件;
例：
select * from student where score=102;

where后面支持多种运算符，进行条件的处理

• 比较运算符
• 逻辑运算符
• 模糊查询
• 范围查询
• 空判断

比较运算符

• 等于: =
• 大于: >
• 大于等于: >=
• 小于: <
• 小于等于: <=
• 不等于: != 或 <>

 例1：查询编号大于1的学生

select * from student where id > 1;

例2：查询分数不大于103的学生 

select * from student where score <= 103;

查询姓名不是“king”的学生:

select * from student where name != 'king';

逻辑运算符

• and
• or
• not

 例1：查询编号大于1的22岁以上的同学

select * from student where id > 1 and age > 22;

模糊查询

• MySQL中使用like语句进行模糊查询，模糊查询常用的符号如下：
• %表示任意多个任意字符
• _表示一个任意字符

 例：查询叫’ing’的学生

select * from student where name like '%ing';

范围查询

• in表示在一个非连续的范围内进行检索，查询数据是否在给定范围内。

• 例1：查询成绩是102或104的学生：

select * from student where score in(102,104);

空判断

• 注意：null与’'是不同的
• 判空is null 

我们在创建一个表：

例1：查询没有填写身高的学生

# 插入带有空值的数据
insert into students(id, name, age) values (5, 'xiaomei', 30);

# 查询没有填写身高的学生
select * from students where height is null;

• 判非空is not null

• 例2：查询填写了性别的学生

select * from students where gender is not null;

条件优先级

• 查询条件的优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符
• and比or先运算，如果同时出现并希望先算or，需要结合()使用

3. 排序 

 为了方便查看数据，可以对数据进行排序

语法：

select * from 表名 order by 列1 asc|desc ,列2 asc|desc,...;

说明

• 将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推
• 默认按照列值从小到大排列（asc）
• asc从小到大排列，即升序
• desc从大到小排序，即降序

示例

• 例1：查询1班女生信息，按学号降序

select * from students where gender='女' and cls_id=1 order by id desc;

例2：查询1班学生信息，按身高升序 

select * from students where cls_id=1 order by height;

例3：显示所有的学生信息，先按照年龄从大–>小排序，当年龄相同时 按照身高从高–>矮排序 

 

4. 聚合查询

操作数据的时候，我们经常需要进行统计计算，SQL中提供了多种聚合函数可以让我们快速计算。

计数

• count(*)表示计算总行数，括号中写星与列名，结果是相同的

例1：查询学生总数

select count(*) from students;

 

最大值/最小值

• max(列)表示求此列的最大值
• min(列)表示求此列的最小值

 例2：查询女生的编号最大值

select max(id) from students where gender='女';

求和

• sum(列)表示求此列的和

• 例3：查询男生的总年龄

select sum(age) from students where gender='男';

平均值

• avg(列)表示求此列的平均值

• 例4：查询女生年龄的平均值

select avg(age) from students where gender='女';

聚合函数组合

• 聚合函数之间也可以组合进行使用，比如我们可以使用count和sum来求得平均值。
• 例：查询女生年龄的平均值

select sum(age)/count(*) as avg_age from students where gender='女';

5.分组查询

• 分组查询在实际工作中经常会用到，当我们需要统计某一字段下不同类别的统计数据时就需要用到分组查询了，比如：统计各个性别的学生数量，各个班级的男女生数量等。

group by

1. group by的含义:将查询结果按照1个或多个字段进行分组，字段值相同的为一组
2. group by可用于单个字段分组，也可用于多个字段分组

 直接使用group by的方法如下

select gender from students group by gender;

上段代码根据gender字段来分组，gender字段的全部值有3个’男’,‘女’,NULL,所以出现了三个数据，但是单独使用groupby是没有过多意义的（可以作为去重来使用），通常分组函数要配合聚合函数/concat函数一同使用。 

group by + group_concat()

1. group_concat(字段名)可以作为一个输出字段来使用，
2. 表示分组之后，根据分组结果，使用group_concat()来放置每一组的某字段的值的集合

 查询各个性别的学生名字集合

select gender,group_concat(name) from students group by gender;

group by + 集合函数

1. 通过group_concat()的启发，我们既然可以统计出每个分组的某字段的值的集合，那么我们也可以通过集合函数来对这个值的集合做一些操作

分别统计各个性别的平均身高

select gender,avg(height) from students group by gender;

group by + having

1. having 条件表达式：用来分组查询后指定一些条件来输出查询结果
2. having作用和where一样，但having只能用于group by

 查找班级人数超过2的班级

select cls_id, count(*) from students group by cls_id having count(*)>2;

6.分页查询 

当数据量过大时，在一页中查看数据是一件非常麻烦的事情，我们可以使用limit进行限制进行分页查询。

语法

• start表示查询开始位置（第几行开始），end表示查询结束位置（第几行结束），可以省略填写start默认从0开始到end结束。

select * from 表名 limit start,end

例1：查询前2行的女生信息

select * from students where gender='女' limit 2;

7.连接查询 

在很多工作中我们需要面对成百上千个不同的数据表，当进行查询的时候，我们需要的结果也会来自于不同的表，这种情况下查询时我们需要将多张表连接成一个大的数据集，再选择合适的列返回 

mysql支持三种类型的连接查询，分别为：

• 内连接查询

  查询的结果为匹配字段相同时两个表匹配到的数据

 

• 左连接查询

  查询的结果为右表匹配字段中的值在左表的匹配字段中存在时匹配到的数据，左表特有的数据，对于左表中无法被右表匹配的数据使用null填充

 

• 右连接查询

  查询的结果为左表匹配字段中的值在右表的匹配字段中存在时匹配到的数据，右表特有的数据，对于右表中无法被左表匹配的数据使用null填充

 

创建示例数据表

// 分数表
create table scores (
  id int,
	subject varchar(20),
	score int
);

//学生表
create table students2 (
  id int,
	name varchar(20)
);

 添加数据

insert into students2 values
(10001, '张三'),
(10002, '李四'),
(10003, '王五'),
(10004, '赵六');


insert into scores values
(10001, '数学', 80),
(10001, '语文', 90),
(10003, '数学', 70),
(10004, '数学', 100),
(10005, '语文', 70);

 语法:

select * from 表1 inner/left/right join 表2 on 表1.列 = 表2.列

例1：使用内连接查询

select students2.id, name, subject, score 
from
students2
inner join
scores
on students2.id = scores.id;

例2：使用左连接查询

select students2.id, name, subject, score 
from
students2
left join
scores
on students2.id = scores.id;

 

例3：使用右连接查询

select scores.id, name, subject, score 
from
students2
right join
scores
on students2.id = scores.id;

 

8. 子查询

子查询概念

 我们在进行select查询的时候，查询的结果本质上也是一张数据表，我们可以使用自查询的方式继续对该表进行查询。

在一个 select 语句中，嵌入了另外一个 select 语句，那么被嵌入的 select 语句称之为子查询语句。

主查询

主要查询的对象,第一条 select 语句

主查询和子查询的关系

• 子查询是嵌入到主查询中
• 子查询是辅助主查询的，要么充当条件，要么充当数据源
• 子查询是可以独立存在的语句，是一条完整的 select 语句

• 例1：标量子查询

1. 查询班级学生所有科目平均分
2. 查询大于等于平均分的记录

select * from scores where score >= (select avg(score) from scores);

• 例2：列级子查询

1. 具有考试成绩的学生id

2. 找出学生表中对应的名字

select name from students2 where id in (select id from scores);

in () 括号内的内容表示查询范围，数据在括号中存在则满足。

• 例3：表级别子查询

1. 查询成绩表里具有数学成绩的学生
2. 找出符合条件的学生姓名

select * from students2
inner join
(select * from scores 
where subject='数学') a
on students2.id = a.id;

 

上述代码中，我们对一个查询到的结果表进行了join。在使用该方法操作时，我们使用()将自查询扩起来，并在末尾对查询结果临时命名为a，当命名为a后，我们可以认为有数据表a可以供我们使用，a中的内容就是我们查询到的结果，接下来按照常规数据表连接查询方式进行操作即可。

感谢阅读！！！！！