编程之路之数据库mysql（六）- SELECT查询语句详解

一、SELECT查询语句配置

SELECT [选项子句] 字段表达式子句 [from子句] [where子句] [group by子句] [having子句] [order by子句] [limit子句]

注：有[]表示可以省略

重要：各个子句的顺序是固定的，不能随便调整顺序。

1、字段表达式子句

表示我们需要查询哪些内容。

*表示所有的字段

也可以指明需要哪些字段

也可以使用函数

字段或函数调用可以使用别名，如

Studname as name

Count(*) as num

AS关键字可以省略，如

Studname as name写成 studname name

还可以是表达式

字段也可以作为函数的参数或参与运算

2、选项子句

有两个值，all|distinct，代表所有|去除重复。默认是all

不指定则返回所有的。

增加distinct会把重复的记录去除

注意：重复是指整个记录的内容相同

3、FROM子句

FROM子句用于指定数据源。

数据源可以是多个，中间用逗号分开。

4、特殊的数据源，dual

Dual是一个虚拟的数据源，完全是为了语法匹配。

5、Where子句

Where子句通常是用于条件判断。

比较运算符

>  大于

<  小于

>=

<=

!=  <>

=

逻辑运算符

&&  and

||   or

!

Xor

上面使用and，如果改成or

 

举一个优先级的例子

In和not in

In: 当数据和集合中的某个数据相等时叫in

Not in ：当数据和集合中的所有数据都不相等叫not in

当比较的字段没有NULL数据时，in + not in等于所有的数据。

 

Is null和is not null

两种方式加起来，应该是所有的记录

 

Between和Not between

注意：between是包含边界值的（判断的是一个范围）

Between和not between加起来是所有记录

7、Group by子句

对记录进行分组。Group by子句通常需要使用一些统计函数辅助分组。

1.group by处怎么写？

Group by 准备对其分组的字段1，准备对其分组的字段2

2．字段表达式处怎么写？

通常先写上被分组的字段列表，后面跟分组的函数

统计函数：count()  sum()  avg()  max()  min()

分组的目的在于统计

 

还可以对多个字段进行分组的统计

8、回溯统计，with rollup

在原有group by语句基础上再加上with rollup

 

9、Having子句

通常用于对分组后的结果进行再次的过滤。

select * from studinfo having gender=1;

并不等于having可以代替where！

相当于省略了where子句和group by子句

不能用where代替having

各个子句是有顺序的，位置的固定的。

10、ORDER BY子句

用于排序的一个子句。

语法格式：order by 字段1  asc|desc, 字段2  asc|desc, …..

多个字段排序的规则：当某几条记录它的第一个字段的值相同时，将根据第2个字段的要求进行排序

Tip:如果不写asc或desc，默认是asc

11、LIMIT子句

用于分页。

语法格式有两种：

limit 起始记录位置，记录数

limit 记录数   相当于 limit 0, 记录数

tip：记录的起始位置是从0开始计算

如何计算起始位置的值：

$start = ($page-1)*$perpage

起始位置值=(第几页-1)*每页记录数

Order by对分页取出的记录有很大影响

 

二、联合查询

把多条查询语句的结果组合成一个查询结果，这种方式就叫做联合查询。

使用union关键字可以进行联合。

语法格式：

Select语句1

Union 选项distinct|all

Select语句2

Union

Select语句3

…..

 

选项：

Distinct代表去除重复的记录，是默认选项，可以不写

All 代表查询出来来的所有记录

 

联合查询特点：

1.各个SELECT语句查询出来的字段数量要一样多，不要求对应的字段类型一致

2.查询结果的字段名使用第1个SELECT语句的字段名

3.会自动去除重复的数据

3.当查询语句中含有order by和limit子句时，SELECT语句应该放到括号中

         建议每一个SELECT语句都放括号中比较好

4.当需要对整个查询结果进行排序时，更需要把每个SELECT语句放到括号中，排序的字段名要使用第一个SELECT语句的字段名

5.当SELECT中只有order by 而没有limit子句时，order by会被优化掉，不起作用

 

三、子查询，subquery

当把一条SELECT语句放到另外一条SQL语句中时，称为子查询。

要求：找出所有同学中年龄最大的同学

如果使用排序加limit

但是仔细观察，年龄最大的同学不只一个时，就不知道limit多少了，所以这个方法不好

在这个情况，使用子查询是很好的选择

子查询可以得到最大年龄

把这句查询作为一个子查询放到select语句中就可以了

 

1、子查询的分类

按位置分：

WHERE型子查询：当子查询出现在WHERE子句中

FROM型子查询：当子查询出现在FROM子句中

按返回结果分：

标量子查询：返回结果是一行一列的数据

行子查询：返回结果是一行记录，一行多列

列子查询：返回结果是多行一列

表子查询：返回结果是一个表，多行多列

 

2、WHERE型子查询

上面的例子就是一个WHERE型子查询

 

3、FORM型子查询

子查询是出现在from子句中的，它是作为一个数据源的方式

子查询的结果是作为一个临时数据源，需要使用as关键字起一个别名

 

4、标量子查询

返回的是一行一列的数据

 

5、列子查询

返回结果是多行一列的数据（是同一字段的数据）

要求：查找出有学生的班级的详细信息

使用SQL，可以从学生表获取到有学生的班级的ID

再从班级表中得到班级的信息

列子查询通常使用： 字段名 in 子查询的结果

 

6、行子查询

要求：用子查询，查出班级ID为1的年龄最大，性别是男的学生资料

先使用查询找出班级ID为1的最小年龄数据

再把子查询作为条件，放到where子句中，比较时使用相应字段的集合=的方式

 

7、表子查询

要求：找出所有班中年龄最小的学生的资料

先用子查询查到多生多列的结果

再把子查询作为条件

总结：

如果返回结果是标量或行，可以使用=进行比较

如果返回结果是列或表，需要使用in进行比较

四、In，some()，all()

比如这是一个使用in的例子

集合：(1,20) (2,18) (5,18)

In相当于 where 组合字段=集合数据1 or 组合字段1=集合数据2 or …..

Not in又是什么？

不等于集合中的任何一个数据

In是集合内，not in是集合外

 

Some()它和in很相似，也是表示数据等于我里面的一些数据就合格

结果相同，只不同some()是函数形式

 

再看in和not in

 

In和not in会得到两部分的结果，一个是条件内的，一个条件外的

 

In有not in和它对应，那么=some()是不是和!=some()对应呢？!=some()是不是和not in一样呢？

!=some()含义是数据不等于集合中的某些数据

有一个a   集合是(b,c,d)

A!=b or a!=c or a!=d

我们会发现，!=some()无法得到有用的结果

结论：!=some()是没有实际意义的，只有=some()相当于in比较有用

提示：除了=some()和!=some()，其实还可以>some()

 

=All() 要求数据要等于集合的所有数据（除非集合内只有一个数据）

!=all() 要求数据不能等于集合内的任何一个数据，有点象not int

结论：可以认为!=all()和not in是具有相同作用的

五、条件判断函数exists()

比如：查询有学生的班级的信息

可以通过子查询

还可以通过使用exists()条件判断函数

两种方法都得到了有学生班级的班级资料

通过判断exists()的返回结果是true代表前面查询的记录是合格，如果为false，则前面查询的记录是不合格。

 

六、连接查询，join

连接查询就把多个表进行拼接。如A表有5个字段，B表有4个字段，则连接后的查询结果一共有5+4=9个字段。

连接查询的方式：

内连接：inner join

外连接：又分成left join左外连接，right join右外连接

交叉连接：cross join

自然连接：又分为natural inner join，natural left join，natural right join

 

语法格式：

SELECT 字段列表 FROM A表 [inner join|left join|right join|cross join|natural inner join|natural left join|natural right join] B表 on 连接条件

1、内连接，inner join

两个表的数据将根据连接条件判断是否是合格数据，是则做两个表数据的连接。

如果两个表中凡是不符合连接条件的数据，都将被过滤掉，不会出现在结果中。

体会：一对多的关系

Classinfo是一表。Studinfo是多表。

那么如果不加连接条件呢？（inner join是允许不加连接条件的）

不加连接条件，将产生一个笛卡尔积。结果的记录数等于两个表记录数的乘积。

内连接语法上可以省略inner关键字

2、外连接之左外连接，left join

以左表为主

特点：

左表的数据会全部出现，右表符合连接的数据将会与左表数据进行对接。

左表中找不到连接条件的记录，右表数据部分将补NULL

右表的数据如果有不满足连接条件的记录不会出现

3、外连接之右连接，right join

以右表为主

观察结果，发现和左连接类似。

特点：

右表的数据会全部出现，左表符合连接的数据将会与右表数据进行对接。

右表中找不到连接条件的记录，左表数据部分将补NULL

左表的数据如果有不满足连接条件的记录不会出现

把classinfo作为右表为主的情况

结论：

不管左连接右连接，一表的数据通常会有重复情况。多表通常是出现一次。

举例：

商品信息表，含有商品分类

商品信息表是多表（使用分类），好多商品可以是同一分类

商品分类表是一表，分类是唯一的

当列出商品列表时，肯定是把商品信息表（多表）放到左边，left join商品分类表（一表）

好处：主表（当前左表是主表）的记录将全部出现

 

提示：left和right不能省略。

4、交叉连接，cross

两个表的记录进行交叉对接。

根据条件，成立则进行记录的对接。

如果不加条件，同样是一个笛卡尔积。

5、连接方式，using

前面我们都是使用的on进行连接。

还有一种连接方式叫using

 

当进行inner join时，使用using，会

连接条件字段将会放到第一的位置

连接条件字段现在只出现一次了（on是出现两次）

合格的数据会进行对接

使用using的要求：两个表在连接条件字段必须名称相同

6、自然连接之自然内连接，natural join

自然连接 相当于 内连接 + using()

7、自然左连接，natural left join

结果是左连接一样，只不过连接字段是自己发现的，连接字段写到最前面了。

同样，左表中找不到 匹配数据的记录，右表部分补NULL

8、自然右连接，natural right join

和自然左连接类似。

举个例子

足球比赛，主客场

1.球队信息表（球队id, 球队名称）

2.比赛结果表（主队ID，客队ID，比分）

球队信息表

球队ID	球队名称
1	国安队
2	恒大队
3	鲁能队

比赛信息表

ID	主队ID	客队ID	比分
1	1	2	0:2
2	1	3	3:1
3	2	1	2:0
4	2	3	3:5
5	3	1	7:1
6	3	2	1:1

创建数据表并插入记录

查询显示格式

主队名   比分  客队名

 

如果不要队名，很好查，比赛信息表中就有

但是我们要求显示是球队名称

如果left join球队表一次

将得到一个加宽了字段数量的新表，join上来的球队是主队的名称

双进行left join，则会把客队的名称连接上来

我们指定字段名

我是小咖

发个邀请：
如果你正好想学习php，可以与我一起交流，我的VX：feilueze333。下面资料免费赠送。