数据库入门介绍及SQL语句（四）

DQL中使用函数或表达式

在SELECT子句中使用表达式

SELECT salary,salary*12

FROM teacher

在SELECT子句中使用函数

IFNULL函数

定义:IFNULL(arg1,arg2)

当arg1不为NULL时,函数返回arg1的值,如果arg1为NULL则函数返回arg2的值.

IFNULL的功能就是将一个NULL值替换为一个非NULL值

在WHERE子句中使用表达式

SELECT name,salary

FROM teacher

WHERE salary*12<60000

别名

别名通常使用在SELECT子句和FROM子句中

在SELECT子句中可以为字段取别名,这样在查询的结果集中对应的字段的名字就使用该别名

通常当字段含有表达式或函数时,为了增加可读性我们会使用别名

在FROM子句中我们可以为表取别名

字段名<空格> 别名

字段 AS 别名

字段 [AS] '别名'

字段 [AS] "别名"

如果别名含有SQL关键字或者含有空格时,要使用引号括起来

***********

如果多行在 ORDER BY 列中具有相同的值，则服务器可以自由地以任意顺序返回这些行，并且可能会根据整体执行计划以不同的方式返回这些行。换句话说，这些行的排序顺序对于无序列是不确定的。

影响执行计划的一个因素是 LIMIT，因此带有和不带 LIMIT 的 ORDER BY 查询可能会返回不同顺序的行。

解决办法:排序时指定字段值相同的记录无论是否添加LIMIT子句,要保证这些记录的顺序始终一致即可.我们要额外的追加一个辅助字段进行排序,确保顺序一定一致**.通常辅助字段值不要用重复的,赋值字段首选ID**

**************

聚合函数

概念：

聚合函数又称为:分组函数,多行函数.对结果集进行统计的

将结果集中所有的记录按照指定的字段进行统计并最终得到一条结果.

聚合函数分类:

MIN:求最小值

MAX:求最大值

SUM:求和

AVG:求平均数

COUNT:统计记录数的

MIN,MAX,SUM,AVG是对值的统计,而COUNT是对记录数的统计.

聚合函数忽略NULL值,尤其在AVG和COUNT中体现的比较明显.

要现将参与统计的记录查询出来,然后再基于聚合函数对结果集进行统计.

GROUP BY子句 -分组

GROUP BY 子句可以将结果集按照指定的字段值相同的记录分组,然后在组内根据聚合函数进行统计并得出结果

GROUP BY子句是配合聚合函数使用的,如果SELECT子句中没有聚合函数,通常不会使用GROUP BY子句

当SELECT子句中出现了聚合函数,那么不在聚合函数中的其它字段都要出现在GROUP BY子句中.

按照多字段分组

GROUP BY子句中后面指定多个字段,此时会将结果集按照这些字段值组合相同的记录看作一组

聚合函数不能出现在WHERE子句中

原因：

WHERE子句的过滤时机:在检索表中每条记录时用于过滤使用的,在生成结果集时WHERE会发挥作用.

我们希望将统计结果进行过滤,这应当实在检索表(WHERE发挥作用)之后生成结果集,并将结果集分组统计后才能得到统计结果从而进行过滤(这个过滤时机已经是在WHERE之后进行了).

HAVING子句

HAVING与WHERE一样都是用来添加过滤条件进行过滤的

他们的区别:

WHERE是对记录进行过滤的,而HAVING是对分组过滤的

只有满足HAVING要求的分组才会被保留

过滤时机不同

WHERE是在检索表时进行过滤的,产生结果集之前进行过滤的

HAVING是在产生结果集并根据GROUP BY对结果集分组后进行过滤的

HAVING可以使用聚合函数作为过滤条件,WHERE不可以.

子查询

概念：

嵌套在其他SQL语句中的一条DQL语句被称为子查询

应用场景：

DQL中使用:基于一个查询结果集进行二次查询

DML中使用:基于一个查询结果集对表数据进行增,删,改操作

DDL中使用:基于结果集创建数据库对象(表,视图)

子查询分类：

单行单列子查询:查询结果集为一个值.仅一行一列

多行单列子查询:查询结果集为一组数据

多列子查询:结果集检索出来是一张表.多用于DDL语句.

多行单列子查询

多行单列子查询会检索出一组数组,此时如果作为过滤条件使用时:

判断相等,不能再使用"=".任何一个值都不可能同时等于多个值,只能等于其中之一.

因此判断相等时要伴随IN使用:IN(列表)

IN和NOT IN都可以使用

判断>,>=,<,<=时,要伴随ANY和ALL使用

>ANY(列表):大于列表中的其中之一即可.判断标准:大于最小的即可

>ALL(列表):大于列表中所有.判断标准:大于最大的

<ANY(列表):小于列表其中之一,小于最大的即可

<ALL(列表):小于列表所有,小于最小的

关联查询

定义：

联合多张表查询数据.查询结果集中的数据来自于多张表.

关联关系：

表与表中的记录在设计是会产生一种对应关系,它称为关联关系

关联关系的分类：

一对一:A表的一条记录仅对应B表的一条记录,称为A与B表存在一对一关系

一对多:A表中的一条记录可以对应B表中的多条记录,称为A与B表存在一对多关系

多对多:当A与B表双向都存在一对多关系时就称为多对多关系

连接条件

在关联关系中,非常重要的一点就是指定连接条件.

作用是让数据库清楚两张表间记录与记录之间的对应关系.

如果指定的连接条件无效或没有指定连接条件时,会产生笛卡尔积,这通常是一个无意义的结果集,要尽量避免.

SELECT 各表中的字段

FROM A表,B表[,C表...]

WHERE 连接条件              用来让数据库清楚表与表之间记录的对应关系

AND 过滤条件             用来筛选数据的.

注:连接条件要与过滤条件同时满足!!!

不指定连接条件时,会产生笛卡尔积

笛卡尔积的产生

当不指定连接条件时,数据库在进行关联查询时,仍然会用A表一条记录与B表每条记录连接一次,并产生结果集中的一条记录.此时的数据量为A表记录数与B表记录数的乘积.

当表中数据量大时,这样的结果集开销巨大,甚至可能导致服务器宕机.因此要尽量避免.

主键与外键

主键:一张表中通常第一个字段为主键字段,用来唯一标识表中的一条记录.主键要求的条件是非空且唯一.

外键:一张表中一个字段保存了另一张表中主键字段的值,那么这个字段就是外键字段.

在关联关系中,两张表通常就是使用主外键进行关联的.并且在关联查询中总是用等值连接主键与外键来建立两张表中记录的对应关系.

通常:定义外键字段的表在关联关系中处于"多"的一方.

例如:学生与班级表,学生表中有一个字段class_id保存着class表主键id字段的值.此时class_id字段就是外键字段.因此班级表与学生表存在一对多的关系,即:班级表的一条记录可以对应学生表的多条记录.

外键约束

外键约束是专门用来对外键字段施加的约束,它要求外键字段:

外键字段保存的值必须是它所对应的主键字段中已有的值或NULL

当我们想删除主键字段表中某条记录时,很可能失败,因为外键字段表中某些条记录中外键字段保存着这个主键时,此时会因为违反外键约束,导致主键字段表中无法删除记录 ,除非将该主键值在外键表中所有对应记录更新为NULL后方可进行.这在实际业务场景下开销巨大.

多对多关系

多对多关系:A表与B表双向都是一对多,就是多对多关系.

多对多关系需要提供一张关联关系表来维系多对多关系.该表保存着两张产生关系表的外键字段.

多对多还是关联查询

内连接

内连接是关联查询的另一种写法,与关联查询效果一样.

特点

将连接条件放在ON子句中,而WHERE子句仅用于添加过滤条件.因此结构清晰.

语法：

SELECT 子句

FROM 表A

JOIN 表B ON 连接条件(A与B的连接条件)

JOIN 表C ON 连接条件(A与C或B与C的连接条件)

...

WHERE 过滤条件

外连接

外连接在关联查询中可以将不满足连接条件的记录也查询出来

左外连接:以JOIN左侧表为驱动表,该表中所有记录都会查询出来,当来自JOIN右侧表中的记录有不满足连接条件时,那么结果集中对应字段值全为NULL

右外连接:与左外连接相反

自连接

定义：

同一张表的一条记录可以对应多条记录.

自己这张表上定义的外键对应自己这张表中主键字段的值.

场景：

当数据存在上下级关系的树状结构数据时,可以使用自连接进行关联