数据表查询操作
准备工作
导入之前需要先创建一个数据库
数据库资料传送门
使用新创建的数据库
使用 source 文件地址 导入数据
create database db charset utf8;
use db;
source /home/python/Desktop/school.sql
单表查询操作
- 查询所有数据:
select * from t_student;
- 查询指定字段的显示
select c_id, c_name, c_gender from t_student;
- as 别名 在查询时,默认结果显示的字段和表中字段名相同,可以通过别名来修改显示的样式 (as 可以省略)
select c_id 学号, c_name 姓名, c_address as 地址 from t_student;
- 消除重复数据 在查询数据时,查询结果可能会有很多重复的数据,如果不想重复,可以使用 distinct 来实现去重
distinct 在去重时,会比较所有的指定字段,只有完全相同时才认为是重复的。
select distinct c_name, c_address from t_student;
- 带条件查询 where 子句 查询数据时
select * from t_student where c_gender = "男";
-
where 条件可以使用运算符操作。
-
比较运算符
等于: =
大于: >
大于等于: >=
小于: <
小于等于: <=
不等于: != 或 <>
select * from t_student where c_age < 20;
-
逻辑运算符
and
or
not
select * from t_student where c_age < 20 and c_gender = '女';
- 模糊查询
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 '孙_%';
- 范围查询
- 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;
- 空判断 在数据库中,允许在数据添加是没有数据,使用空值来表示。 空值不等于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;
- 查询结果排序 排序是一个在查询数据时非常重要的操作
排序使用 order by 子句 asc(默认) 升序 / desc 降序 语法:
select * from 表名 order by 列1 asc|desc [,列2 asc|desc,…] - 单字段排序
select * from t_student order by c_age asc;
- 多字段排序 可以对多个字段进行排序,只需将字段的排序方式依次写在 order by 后面即可,字段间使用逗号分隔
select * from t_student order by c_age desc,c_id asc;
-
分页查询 查询数据库时,由于数据较多,在显示过程中不可能将数据全部显示。 可以使用分页查询,只显示指定的一部分数据 语法:select from 表名 limit start=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
- 聚合函数 在 MySQL 中提供了一些定义好的函数,利用这些函数提供对数据的统计功能。 常用的聚合函数如图:
select sum(c_age) from t_student;
select avg(c_age) from t_student;
select max(c_age) from t_student where c_gender = '男';
select min(c_age) from t_student where c_gender = '女';
select count(*) from t_student where c_gender = '女';
- 分组 分组就是将相同数据放到一起进行处理。 单纯的分组是没有意义的,需要配合聚合函数一起使用。 语法: select 分组的字段名,聚合函数… from 表名 group by 分组字段名 having 分组后的条件
注意:在执行 group by 分组时,select 后只能有被分组的字段,不允许有其它字段,除非这些字段在聚合函数中
- 单字段分组
select c_gender from t_student group by c_gender;
- 多字段分组(了解)
可以对多个字段进行分组,作用同上,需要注意的是多字段时,只有对应字段完全相同,才能分为同一组
select c_gender,c_address from t_student group by c_gender,c_address;
- group_concat()
作用:根据分组结果,使用group_concat()来获取分组中指定字段的集合
语法:group_concat(字段名)
select c_gender,group_concat(c_name) from t_student group by c_gender;
- 分组和聚和函数使用
单纯的使用分组并没有实际意义,需要使用聚合函数对数据进行处理。
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;
- having条件子句
having 作用和 where 类似,用来去分组数据进行筛选
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 = '女';
- 分组汇总(无大用,了解即可)
作用:会在分组下方,加一行,显示汇总
语法: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;
多表查询
在数据库操作中,数据往往不是存在一张表中的,同一个项目中,根据设计范式,数据可能分散在不同的多张表中,这时查询数据时,就需要多表查询。
- 普通多表查询(无意义) 作用:直接将表放在from后面,进行读取。 语法:select 表名.字段 … from 表名1,表名2…
select * from t_student,t_class;
这种查询方式没有任何意义。 在查询时,数据库会将表1中的数据逐条和表2中的所有数据连接,组成一条新记录。 查询的结果为 M * N 条,实际就是笛卡尔积结果。
- 多表查询连接条件 在多表个表进行查询时,表与表之间应该是有有关系的,一般会以外键的形式来建立表间的关系。 查询时按照条件建立记录的匹配规则。 比如学生表中保存了学生的信息和所在班级的ID,班级表中保存了班级的信息。 在查询学生的班级信息时,可以通过学生表中的班级ID和班级表中的ID匹配进行查询
select ts.c_name as '姓名' , tc.c_name '班级名' from t_student as ts,t_class tc where ts.c_class_id = tc.c_id;
- 内连接查询 作用:查询的结果为两个表匹配到的数据 语法: 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;
- 左连接查询 作用:查询的结果为根据左表中的数据进行连接,如果右表中没有满足条件的记录,则连接空值。 语法: 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;
在实际工作中,右连接使用的非常少,因为左连接完全可以替代右连接,在连接过程中,只需要调整表的顺序即可。
-
子查询 作用:作用:在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句
语法: select * from 表1 where 条件 运算符 (select 查询) -
外部那个select语句则称为主查询
主查询和子查询的关系子查询是嵌入到主查询中
子查询是辅助主查询的,要么充当条件,要么充当数据源
子查询是可以独立存在的语句,是一条完整的 select 语句 -
标量子查询 作用:子查询返回的结果是一个数据(一行一列)
语法:主查询 where 条件 比较运算符 (列子查询)- 查询班级中年龄大于平均年龄的学生信息
select * from t_student where c_age > (select avg(c_age) from t_student);
- 列级子查询 作用:子查询返回的结果是一列(一列多行)
查询所有学生所在班级的班级名称
select * from t_class where c_id in (select c_class_id from t_student);
- 行级子查询 作用:子查询返回的结果是一行(一行多列) 语法:主查询 where (字段1,2,…) = (行子查询)
查找班级年龄最大,所在班号最小的的学生
select * from t_student where(c_age,c_class_id) = (select max(c_age),min(c_class_id) from t_student);
自连接查询
作用:在查询数据时,只有一张表,查询时使用自己连接自己。
语法: select * from 表1 inner join 表2 on 表1.列 运算符 表2.列 where 条件
- 为什么需要自连接
以要设计表结构来存储 全国 所有的省份和 全国所有的市
设计省信息的表结构provinces
id 省的编号
ptitle 省名称
设计市信息的表结构citys
id 市编号
ctitle 市名称
proid 市所属的省的编号
citys表的proid表示城市所属的省,对应着provinces表的id值
如果需要查询一个省 比如广东省对应的所有的时的信息 ,我们可以使用两个表连接查询。
> 问题: 能不能将两个表合成一张表呢?
观察两张表发现,citys表比provinces表多一个列proid,其它列的类型都是一样的。
这样做的**好处**在于: 存储的都是地区信息,而且每种信息的数据量有限,没必要增加一个新表,或者将来还要存储区、乡镇信息,都增加新表的开销太大。
- 定义表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 /home/python/Desktop/areas.sql;
- 自查询
查询一共有多少个省
select count(*) from areas where pid is null;
查询省的名称为“山西省”的所有城市
select city.* from areas as city inner join areas as province on city.pid=province.aid where province.atitle='山西省';
查询市的名称为“广州市”的所有区县
select dis.* from areas as dis inner join areas as city on city.aid=dis.pid where city.atitle='广州市';
查询操作的一般执行顺序:
读数据源->分组->分页->筛选条件->Select
注释使用一般使用–,这个会导致一些诸如sql注入之类的问题,之后讲解
三种注释1、#…
2、"-- …" # – 加空格
3、/…/
回溯
可以通过看下面的表,回忆一下上面的命令
数据查询
单表查询
◦ select 字段名[,(字段名…)] from 表名;
字段别名
◦ select 字段1 as 别名,字段2 别名,... from 表名;
消除重复行
◦ select distinct 字段名 from 表名;
条件查询
◦ select 字段名… form 表名 where 条件;
运算符
◦ 比较运算符
▪ = 等于
▪ > 大于
▪ < 小于
▪ >= 大于等于
▪ <= 小于等于
▪ != 或 <> 不等于
◦ 逻辑运算符
▪ and 与
▪ or 或
▪ not 非
◦ 模糊查询
▪ like
▪ % 任意多个字符
▪ _ 一个任意字符
◦ 范围查询
▪ in 非连续值
▪ between-and 连续值
◦ 空判断
▪ is null 判断是否为空
▪ is not null 判断是否不为空
排序
◦ select 字段名… from 表名 order by 排序字段名 排序方式 [,排序字段名 排序方式…];
◦ asc 升序 ,默认排序方式
◦ desc 降序
分页
◦ select 字段名 from 表名 limit 开始索引, 记录条数;
聚合函数
◦ sum() 求和
◦ avg() 求平均值
◦ max() 求最大值
◦ min() 求最小值
◦ count() 统计记录条数
◦ group_concat() 拼接分组中的数据
◦ 聚合函数一般配合分组功能一起使用
分组
◦ select 分组的字段名,聚合函数... from 表名 group by 分组字段名 having 分组后的条件;
◦ 查询显示的字段必须和分组的字段相同
◦ 分组一般配合聚合函数使用,做数据统计功能
◦ 分组后如果需要设置条件要使用 having 指定
多表查询
普通多表查询
◦ select 表名.字段 ... from 表名1,表名2...
◦ 结果为笛卡尔积,无意义
表别名
◦ select 表别名.字段名... from 表1 as 表1别名,表2 表2别名... [条件];
内连接
◦ select 表名.字段名… from 表1 inner join 表2 on 表1.列 运算符 表2.列;
◦ 内连接默认不指定连接条件时,实际就是普通多表连接,结果为笛卡尔积
◦ 连接条件可以使用 where 指定,也可以使用 on 指定, 但建议使用 on
◦ 内连接指定连接条件取两表的交集
左连接
◦ select 表名1.字段名… from 表1 left join 表2 on 表1.列 运算符 表2.列;
◦ 连接条件只能使用 on 指定
◦ 连接结果以左表记录为准,连接右表中符合条件的记录,无符合记录连接NULL
右连接
◦ select 表名1.字段名… from 表1 right join 表2 on 表1.列 运算符 表2.列;
◦ 连接条件只能使用 on 指定
◦ 连接结果以右表记录为准,连接左表中符合条件的记录,无符合记录连接NULL
◦ 右连接可以使用左连接代替使用。只需要交换两个表的位置即可.
子查询
◦ 在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句
◦ 子查询语句是一个可以独立执行的查询语句
◦ select * from 表1 where 条件 运算符 (select 查询)
◦ 子查询有三种:
▪ 标量子查询 ,子查询 返回结果 为单行单列的值
▪ 列级子查询 ,子查询 返回结果 为多行一列的值
▪ 行级子查询 ,子查询 返回结果 为多行多列的值
自连接(了解)
◦ 由于数据的特殊性,表示不同类型的数据保存到了同一张表中。
◦ 在查询数据时,需要做多表连接时,连接的多个表实际是同一张表,但是在表中取的数据是不同类型的数据