数据库中select 查询详解

本文为数据查询操作介绍。

在数据库操作中，使用频率最多的是查询操作。

查询数据时，根据不同的需求、条件对数据库中的数据进行查询，并返回结果。

查询数据库使用select 命令。这个命令相对比较复杂。可变化样式较多，这里分功能依次讲解。

单表查询数据

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;

在给字段起别名时，可以使用as，也可以直接在字段后跟别名，省略as。

4.消除重复数据 

在查询数据时，查询结果可能会有很多重复的数据，如果不想重复，可以使用 distinct 来实现去重。

语法：select distinct 字段名 from 表名

select distinct c_address from t_student;

注意：distinct 在去重时，会比较所有的指定字段，只有完全相同时才认为是重复的。

5.带条件查询 where 子句 

查询数据时，需要根据不同的需求设置条件。通过 where 子句来设置查询条件。

select * from t_student where c_gender='男';

条件查询

1.比较运算符

• 等于: =

• 大于: >

• 大于等于: >=

• 小于: <

• 小于等于: <=

• 不等于: != 或 <>

select * from t_student where c_age < 20;

2.逻辑运算符

• and

• or

• not

select * from t_student where c_age < 20 and c_gender = '女';

3.模糊查询

• 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 '孙_';

4.范围查询

• in 表示在一个非连续的范围内 , 可以使用 or 实现

• between ... and ... 表示在一个连续的范围内，可以使用 and 实现 

select * from t_students where id in(1,3,8);select * from t_students where id between 1 and 8;select * from t_students where id>=1 and id<=8;

5.空判断 

在数据库中，允许某些字段是没有数据的，使用空值来表示。空值不等于0，也不等于‘’，需要使用特殊的判断方式

（1）判断空值    语法：is null

select * from t_student where c_age is null;

（2）判断非空值    语法：is not null

select * from t_student where c_age is not null;

6.查询结果排序 

排序是一个在查询数据时非常重要的操作。比如买东西时，想按一定的条件进行有序显示。就需要使用排序

排序使用 order by 子句    asc(默认) 升序 / desc 降序 

语法：select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]

（1）单字段排序

select * from t_student order by c_age;select * from t_student order by c_age asc;

默认使用就是升序排序，可以不指定 asc ，效果相同。

（2）多字段排序 

可以对多个字段进行排序，只需将字段的排序方式依次写在 order by 后面即可，字段间使用逗号分隔。

select * from t_student order by c_age desc,c_id asc;

7.分页查询

查询数据库时，由于数据较多，在显示过程中不可能将数据全部显示。可以使用分页查询，只显示指定的一部分数据。

语法：select from 表名 limit start=0,count *

说明：

1. 从start开始，获取count条数据

2. start默认值为0

3. 需要获取数据的前n条的时候可以直接写 limit n

select * from t_student limit 3;select * from t_student limit 2,3;

查询第 N 页 M 条数据，可以通过公式算出：(N - 1) * M

8.聚合函数

在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 = '女';

9.分组 

分组就是将相同数据放到一起进行处理。单纯的分组是没有意义的，需要配合聚合函数一起使用。

语法：select 分组的字段名,聚合函数... from 表名 group by 分组字段名 having 分组后的条件

注意：在执行 group by 分组时，select 后只能有被分组的字段，不允许有其它字段，除非这些字段在聚合函数中。

分组查询

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_concat()

作用：根据分组结果，使用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条件子句

• 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 = '女';

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;

多表查询数据

在数据库操作中，数据往往不是存在一张表中的，同一个项目中，根据设计范式，数据可能分散在不同的多张表中，这时查询数据时，就需要多表查询。

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.表别名 

在多表操作时，由于表的名字比较长，在写SQL语句时非常不方便。可以在查询时，给表起个别名，代替表名来操作。

语法：select 别名.字段名... from 表1 as 表1别名，表2 表2别名... [条件]

select ts.c_name as '姓名' , tc.c_name '班级名' from t_student as ts,t_class tc where ts.c_class_id = tc.c_id;

4.内连接查询 

作用：查询的结果为两个表匹配到的数据。

语法：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;

5.左连接查询 

作用：查询的结果为根据左表中的数据进行连接，如果右表中没有满足条件的记录，则连接空值。

语法：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;

6.右连接查询 

作用：查询的结果为根据右表中的数据进行连接，如果左表中没有满足条件的记录，则连接空值。

语法：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;

在实际工作中，右连接使用的非常少，因为左连接完全可以替代右连接，在连接过程中，只需要调整表的顺序即可。

7.子查询 

作用：在一个 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);

自连接查询 

作用：在查询数据时，只有一张表，查询时使用自己连接自己。

语法：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表的语句如下：

注意，表所在的数据库字符集必须是utf-8的，如果不是会导入数据出错。

create table areas(  aid int primary key,  atitle varchar(20),  pid int);

从sql文件中导入数据

source /home/python/Desktop/areas.sql;

1.查询一共有多少个省

select count(*) from areas where pid is null;

2.查询省的名称为“山西省”的所有城市

select city.* from areas as city inner join areas as province on city.pid=province.aid where province.atitle='山西省';

3.查询市的名称为“广州市”的所有区县

select dis.* from areas as dis inner join areas as city on city.aid=dis.pid where city.atitle='广州市';

areas表和自身进行连接这种形式的连接就称为为自连接。