2.5 数据表查询操作
学习目标
能够熟练对数据表进行各种查询操作
--------------------------------------------------------------------------------
2.5.1 数据查询操作介绍
在数据库操作中，使用频率最多的是查询操作。
查询数据时，根据不同的需求，条件对数据库中的数据进行查询 ，并返回结果。
2.5.2 准备工作,导入数据库
在查询之前，首先要有数据表和相应的数据。
在 辅助资料中 有 school.sql 文件，通过导入该文件来快速准备数据。
导入之前需要先创建一个数据库
使用新创建的数据库
使用 source 文件地址 导入数据
注意，导入数据库时的文件地址尽量使用绝对路径，或者把文件放在家目录下
2.5.3 单表查询数据
查询数据库使用 select 命令。 这个命令相对比较复杂。可变化样式较多，这里分功能依次讲解。
1.数据表单表查询数据总结：
select 字段名[,(字段名…)] from 表名;
<1>查询数据表中所有数据
语法：select * from 表名
select * from t_student;
<2>查询指定字段的显示
语法：select 字段1，字段2，... from 表名
select c_id,c_name,c_address from t_student；
<3>as 别名
在查询时，默认结果显示的字段和表中字段名相同，可以通过别名来修改显示的样
给字段起别名
■ 语法：select 字段1 as 别名，字段2 别名，... from 表名
■ select c_id as 学号 ,c_name as 姓名 ,c_address 地址 from t_student;
给数据表起别名
■ 语法：select 字段1，字段2 别名，... from 表名 as 别名
注意：
■ 在给字段或表起别名时，可以使用 as ，也可以直接在字段后跟别名，省略 as 。
■ 如果起了别名，原名将不起作用！后面的语句中只能使用别名
<4>消除重复数据distinct
在查询数据时，查询结果可能会有很多重复的数据，如果不想重复，可以使用 distinct 来实现去重。
语法：select distinct 字段名 from 表名
语法：select distinct 字段名1 字段名2 from 表名
select distinct c_address from t_student;
注意：
■ 可以指定多个字段名
■ distinct 在去重时，会比较所有的指定字段，只有完全相同时才认为是重复的。
<5>带条件查询where
where 子句 查询数据时，需要根据不同的需求设置条件。 通过 where 子句来设置查询条件
语法：select 字段名… form 表名 where 条件;
select * from t_student where c_gender='男';
注意：
■ where 条件可以使用运算符操作。
1>比较运算符
等于: =
大于: >
大于等于: >=
小于: <
小于等于: <=
不等于: != 或 <>
■ select * from t_student where c_age < 20;
注意sql中只有一个等号，没有==这种形式
2>逻辑运算符
and
or
not
■ select * from t_student where c_age < 20 and c_gender = '女';
<6>模糊查询like
like（这是一个操作符）
% 表示任意多个任意字符
_ 表示一个任意字符
■ select * from t_student where c_name like '孙';
■ select * from t_student where c_name like '孙%';
■ select * from t_student where c_name like '孙_';
<7>范围查询，非连续范围in，连续范围between ... and ...
in 表示在一个非连续的范围内 , 可以使用 or 实现
■ select * from t_students where id in(1,3,8);
between ... and ... 表示在一个连续的范围内，可以使用 and 实现 ```
■ select * from t_students where c_id between 2 and 5;
■ 相当于select * from t_students where c_id >=2 and c_id <= 5;
<8>空判断is null、is not null
在数据库中，允许在数据添加是没有数据，使用空值来表示。 空值不等于0，也不等于''，需要使用特殊的判断方式
判断空值
■ 语法：is null
select * from t_student where c_age is null;
判断非空值
■ 语法：is not null
select * from t_student where c_age is not null;
<9>查询结果排序order by
排序order by（子句）是一个在查询数据时非常重要的操作。比如买东西时，想按一定的条件进行有序显示。就需要使用排序
排序使用 order by 子段名 asc(默认) 升序 / desc 降序
语法：select * from 表名 order by 列1 asc/desc [,列2 asc/desc,...]
单字段排序
select * from t_student order by c_age;
select * from t_student order by c_age asc;
默认使用就是升序排序，可以不指定 asc ，效果相同。
多字段排序
■ 可以对多个字段进行排序，只需将字段的排序方式依次写在 order by 后面即可，字段间使用逗号分隔
■ select * from t_student order by c_age desc,c_id asc;
<10>分页查询limit
查询数据库时，由于数据较多，在显示过程中不可能将数据全部显示。 可以使用分页查询，只显示指定的一部分数据
语法：select 字段名 from 表名 limit start=0(开始索引默认为0),count(记录条数)
■ 从start开始，获取count条数据，start默认值为0
■ 需要获取数据的前n条的时候可以直接写 limit n
■ select * from t_student limit 3;
■ select * from t_student limit 2,3;
■ 注意：查询第 N 页 M 条数据，可以通过公式算出：(N - 1) * M
<11>聚合函数
在 MySQL 中提供了一些定义好的函数，利用这些函数提供对数据的统计功能，聚合函数一般配合分组功能一起使用。
1>sum 求和函数 对指定的字段求和
■ select sum(c_age) from t_student;
2>avg 求平均值函数 对指定字段求平均值
■ select avg(c_age) from t_student;
3>max 求最大值函数
■ select max(c_age) from t_student where c_gender = '男';
4>min 求最小值函数
■ select min(c_age) from t_student where c_gender = '女';
5>count 统计记录总数
■ select count(*) from t_student;
■ select count(*) from t_student where c_gender = '女';
6>group_concat() 拼接分组中的数据
<12>分组group by
分组就是将相同数据放到一起进行处理。 单纯的分组是没有意义的，需要配合聚合函数一起使用。
group by的含义:将查询结果按照1个或多个字段进行分组，字段值相同的为一组
group by可用于单个字段分组，也可用于多个字段分组
语法： select 分组的字段名,聚合函数... from 表名 group by 分组字段名 having 分组后的条件
注意：
■ 在执行 group by 分组时，select 后只能有被分组的字段，不允许有其它字段，除非这些字段在聚合函数中
查询显示的字段必须和分组的字段相同
■ 分组一般配合聚合函数使用，做数据统计功能
■ 分组后如果需要设置条件要使用 having 指定
1>单字段分组
■ select c_gender from t_student group by c_gender;
2>多字段分组（了解）
■ 可以对多个字段进行分组，作用同上，需要注意的是多字段时，只有对应字段完全相同，才能分为同一组
■ select c_gender,c_address from t_student group by c_gender,c_address;
3>group by + group_concat()
■ group_concat(字段名)可以作为一个输出字段来使用，表示分组之后，根据分组结果，使用group_concat()来放置每一组的某字段的值的sel集合
■ 作用：根据分组结果，使用group_concat()来获取分组中指定字段的集合
■ 语法：group_concat(字段名)
select c_gender,group_concat(c_name) from t_student group by c_gender;
4>分组和聚和函数使用
■ 单纯的使用分组并没有实际意义，需要使用聚合函数对数据进行处理。
select c_gender,max(c_age),min(c_age),sum(c_age),avg(c_age),count(*) from t_student group by c_gender;
select c_gender,max(c_age),min(c_age),sum(c_age),avg(c_age),count(c_age) from t_student group by c_gender;
5>having 作用和 where 类似，用来对分组数据进行筛选
■ 注意having只能配合分组使用
■ where 是对 form 表 中取数据时进行筛选
■ having 是对 group by 分组后的数据进行筛选
■ 因为在执行顺序上，在执行 where 时，分组还没有执行，得先根据 where 的条件取出数据，才能去取出的数据进行分组。
select c_gender,group_concat(c_name) from t_student group by c_gender having c_gender = '女';
select c_gender,group_concat(c_name) from t_student where c_age > 50 group by c_gender having c_gender = '女';
6>分组汇总（无大用，了解即可）
■ 作用：会在分组下方，加一行，显示汇总
■ 语法：with rollup
select c_gender from t_student group by c_gender with rollup;
select c_gender,count(*) from t_student group by c_gender with rollup;
2.5.4 多表查询数据
在数据库操作中，数据往往不是存在一张表中的，同一个项目中，根据设计范式，数据可能分散在不同的多张表中，这时查询数据时，就需要多表查询。
<1>普通多表查询（无意义）
作用：直接将表放在from后面，进行读取。
语法：select 表名.字段 ... from 表名1，表名2...
■ select * from t_student,t_class;
这种查询方式没有任何意义。 在查询时，数据库会将表1中的数据逐条和表2中的所有数据连接，组成一条新记录。 查询的结果为 M * N 条，实际就是笛卡尔积结果。
<2>多表查询连接条件
在多表个表进行查询时，表与表之间应该是有有关系的，一般会以外键的形式来建立表间的关系。 查询时按照条件建立记录的匹配规则。 比如学生表中保存了学生的信息和所在班级的ID，班级表中保存了班级的信息。 在查询学生的班级信息时，可以通过学生表中的班级ID和班级表中的ID匹配进行查询
select t_student.c_name,t_class.c_name from t_student,t_class where t_student.c_class_id = t_class.c_id;
<3>内连接查询（交集）
作用：查询的结果为两个表匹配到的数据，内连接指定连接条件取两表的交集
语法：select * from 表1 inner join 表2 on 表1.列 运算符 表2.列
数据库默认的连接方式就是内连接查询，inner join 可以不显示的写出来。 不指定连接条件时，实际就是普通多表连接，会以笛卡尔积的形式进行连接。 所以在连接时，必须要给定连接条件。 连接条件使用 on 进行指定。尽量不要使用 where,where在其它连接方式时，指定的连接条件无效。
■ select ts.c_name, tc.c_name from t_student as ts inner join t_class tc on ts.c_class_id = tc.c_id;
<4>左连接查询
作用：查询的结果为根据左表中的数据进行连接，如果右表中没有满足条件的记录，则连接空值。
注意：连接条件只能使用 on 指定
语法： select * from 表1 left join 表2 on 表1.列 运算符 表2.列
■ select ts.c_name, tc.c_name from t_student as ts left join t_class tc on ts.c_class_id = tc.c_id;
<5>右连接查询
作用：查询的结果为根据右表中的数据进行连接，如果左表中没有满足条件的记录，则连接空值。
注意：连接条件只能使用 on 指定
语法： select * from 表1 right join 表2 on 表1.列 运算符 表2.列
■ select ts.c_name, tc.c_name from t_student as ts right join t_class tc on ts.c_class_id = tc.c_id;
在实际工作中，右连接使用的非常少，因为左连接完全可以替代右连接，在连接过程中，只需要调整表的顺序即可。
<6>子查询
作用：在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句，子查询语句是一个可以独立执行的查询语句
语法： select * from 表1 where 条件 运算符 （select 查询）
外部那个select语句则称为主查询
主查询和子查询的关系
■ 子查询是嵌入到主查询中
■ 子查询是辅助主查询的,要么充当条件,要么充当数据源
■ 子查询是可以独立存在的语句,是一条完整的 select 语句
1>标量子查询
作用：子查询返回的结果是一个数据(一行一列)
语法：主查询 where 条件 比较运算符 (列子查询)
查询班级中年龄大于平均年龄的学生信息
查询班级学生平均年龄
查询大于平均年龄的学生
select * from t_student where c_age > (select avg(c_age) from t_student);
2>列级子查询
作用：子查询返回的结果是一列(一列多行)
语法：主查询 where 条件 in (列子查询)
查询所有学生所在班级的班级名称
找出学生表中所有的班级 id
找出班级表中对应的名字
select * from t_class where c_id in (select c_class_id from t_student);
3>行级子查询
作用：子查询返回的结果是一行(一行多列)
语法：主查询 where (字段1,2,...) = (行子查询)
查找班级年龄最大,所在班号最小的的学生
找出最大年龄和最小班号
找出年龄和班号满足条件的学生
select * from t_student where(c_age,c_class_id) = (select max(c_age),min(c_class_id) from t_student);
<7>自连接查询
作用：在查询数据时，只有一张表，查询时使用自己连接自己。
语法： select * from 表1 inner join 表2 on 表1.列 运算符 表2.列 where 条件
为什么需要自连接
设计表结构来存储 全国 所有的省份和 全国所有的市
设计省信息的表结构provinces
id 省的编号
ptitle 省名称
设计市信息的表结构citys
id 市编号
ctitle 市名称
proid 市所属的省的编号
citys表的proid表示城市所属的省，对应着provinces表的id值
将两个表合为一个
定义表areas，结构如下
■ id
■ atitle
■ pid
关于这个表的说明:
■ 因为省没有所属的省份，所以可以填写为null
■ 城市所属的省份pid，填写省所对应的编号id
■ 这就是自关联，表中的某一列，关联了这个表中的另外一列，但是它们的业务逻辑含义是不一样的，城市信息的pid引用的是省信息的id
■ 在这个表中，结构不变，可以添加区县、乡镇街道、村社区等信息
areas表和自身进行连接这种形式的连接 就成为自连接。
创建areas表的语句如下：
注意，表所在的数据库字符集必须是utf8的，如果不是会导入数据出错
■ create table areas(aid int primary key,atitle varchar(20),pid int);
从sql文件中导入数据
source areas.sql;
查询一共有多少个省
select count(*) from areas where pid is null;
例1：查询省的名称为“山西省”的所有城市
select city.* from areas as city inner join areas as province on city.pid=province.aid where province.atitle='山西省';
例2：查询市的名称为“广州市”的所有区县
select dis.* from areas as dis inner join areas as city on city.aid=dis.pid where city.atitle='广州市';
网友评论