从头到尾写SQL(四)

最新推荐文章于 2024-03-06 10:54:10 发布

跟风的eassy

最新推荐文章于 2024-03-06 10:54:10 发布

阅读量448

点赞数

本文链接：https://blog.csdn.net/houwenjie11/article/details/50379892

版权

数据的检索

为什么数据检索需要单独一章呢，因为记得大学学习数据库时，这部分是花费时间最多的章节，而且其他数据库的操作，很多时候也依赖于查询语句。

其实迄今为止涉及到的都只是数据库的操作，个人感觉数据库最重要的还是那些数据表的结构，关联等等，这些是经验才能满足的。

SELECT 基本用法

取出一张表中所有数据

SELECT * FROM PERSON

列出了表中所有的数据

取出需要的列

SELECT FName FROM PERSON

取出来所有的FName字段

SELECT FName,FAge FROM PERSON

取出了所有的FName 和 FAge字段，并且是一一对应的

按条件过滤

使用WHERE语句来指定查找数据的条件。

例如查找年龄小于20岁的人的name：

SELECT FName FROM PERSON WHERE FName < 20

做一些数据统计

当我们查找出来数据后，我们可能想做一些处理，比如，将所有18岁的人身高加起来等等，SQL中提供了聚合函数，某个字段的最大值、最小值，某个字段的平均值及某个字段的合计值等数据统计的功能：

MAX(最大值) 、 MIN(最小值)、AVG(平均值)、SUM(求和)、COUNT(计数)

假如查询年龄大于25岁员工的最高工资：

SELECT MAX(FSalary) FROM PERSON WHERE FAge > 25

查询工资大于3800员工的平均工资：

SELECT AVG(FAge) FROM PERSON WHERE FSalary > 3800

同理，统计数量使用COUNT，但是COUNT 使用时需要注意

SELECT COUNT (*) FROM PERSON

这个语句统计了PERSON 表中的记录数

SELECT COUNT (FName) FROM PERSON

这个语句统计了PERSON表中，FName不为NULL的记录数，所以COUNT(*) 和COUNT(FName) 并不相同

排序

迄今为止，检索出来的数据排序，都是根据数据库决定，同样我们可以使用ORDER BY选择排序的规则

一般ORDER BY 位于 SELECT 语句的结尾，允许指定按照一个列或者多个列进行正序或者倒序

SELECT * FROM PERSON ORDER BY Fage ASC

按照Fage进行了升序排列，即越来越大，DESC 为降序，默认为ASC

ORDER BY 语句允许指定多个排序列，每个列用逗号隔开即可，并且排序的规则根据顺序，比如

SELECT * FROM PERSON ORDER BY Fag DESC,FSalary DESC

即先按照Fage 查找，假如Fage相同，再按照FSalary排序

假如ORDER BY 与 WHERE 一起使用，需要将ORDER BY 放在WHERE 后面

高级数据过滤

通配符过滤

假如需要模糊查找，需要使用通配符，SQL 使用LIKE 执行模糊匹配，LIKE左边为待匹配的字段，右边为通配符表达式，通配符表达式由通配符和普通字符组成，通配符又分为单字符匹配和多字符匹配，有的数据库还支持集合匹配。

单字符匹配

进行单字符匹配的通配符为 _ 它匹配单个出现的字符，表示任意字符，有点正则里面 * 的味道,不过只能匹配一个字符一次，且不能为0次，单字符匹配可以在语句中出现多次。

_k 匹配后面一个字符为k的两个字符

SELECT * FROM PERSON WHERE FName LIKE '_k'

查找FName 符合 _k 条件的记录

多字符匹配

进行多字符匹配的通配符为 % 它匹配任意次数(包括零次)

SELECT * FROM PERSON WHERE FName LIKE '%k'

查找FName 以 k 结尾的记录

多字符匹配与单字符匹配可以共同使用，如最后一个字符为任意字符，倒数第二个字符为n，长度不限定的字符串，我们可以使用 %n_ 来匹配该字符串

SELECT * FROM PERSON WHERE FName LIKE '%n_'

使用通配符虽然特别灵活，但是数据库会进行全表扫面，运行成本特别大，所以尽量使用其他方式。

空值检测

假如想查询某字段为NULL 的记录，不能使用 = null，而是要使用 IS NULL

SELECT * FROM PERSON WHERE FName IS NULL 查询PERSON表中，FName 为NULL 的记录

假如想查询某字段不为NULL 的记录，使用 IS NOT NULL

假如想使用多个条件，使用AND

反义运算符

SQL 中使用 ! 代表反义，比如 !> 代表不大于，!= 代表不等于

NOT 可以将一个表达式置反，比如 NOT(FName = 'Tom') 代表 FName 不等于Tom,推荐使用NOT 来表示反义，因为 ! 只在MS SQL Server 和DB2两种数据库上

多值检测

公司要为年龄为23，25，28岁的员工发福利，请将他们的名字查询出来

SELECT FName FROM PERSON WHERE FAge = 23 OR FAge = 25 OR FAge = 28

可以用OR 表示多个条件，但是假如特别多的条件，将会有所不便，可以使用IN代表多值匹配

SELECT FName FROM PERSON WHERE FAge IN(23,25,28)

范围值检测

检测所有年龄在23 ~ 27岁之间的记录

可以使用SELECT * FROM PERSON WHERE FAge > 23 AND FAge < 27

也可以使用SELECT * FROM PERSON WHERE FAge BETWEEN 23 AND 27

数据库对 BETWEEN AND进行了查询优化，使用范围检测的时候，我们应该尽量使用BETWEEN AND，但是BETWEEN AND 包括边界值，即 [ ] ,所以有所欠缺

WHERE 1 = 1

在做数据库系统时，是没有确定的查询条件的，经常是用户自己选择查询的条件，所以查询语句需要动态组装

假如用户可以选择根据FName，FAge，或者没有条件，那么查询语句分别为

WHERE FName... WHERE FAge .... 和没有WHERE

这样每次拼接需要区分是否已经有了WHERE ，比较麻烦，所以就有人想出了一个办法，就是不管有没有查询条件，默认都拼接上一个 WHERE 1= 1 那么，再拼接条件的时候就不用再判断是否已经拼接过参数，直接拼接 AND

这样看上去很方便的解决了问题，但是会造成很大的性能问题，因为添加了 1= 1 的查询条件后，数据库系统就不能再使用索引等查询优化策略，数据库将被迫遍历全表来查询，查询速度会非常慢，所以还是需要使用其他方法来完成。

数据分组

假如我们想知道每个年龄段的员工数，可以先得到所有员工的年龄信息，在分别查询每个年龄段的人数，显然这样效率非常低，可以使用数据分组来实现这一功能

SQL 语句中使用 GROUP BY 进行分组，一般与聚合函数一起使用，即 GROUP BY 将数据分成逻辑上的几组，而聚合函数对这些组进行处理。所以GROUP BY 是可以离开聚合函数使用的，例：

SELECT FAge FROM PERSON GROUP BY FAge

将会按照FAge 将数据分为几组，但是并不能体现出数量，因为我们没有使用聚合函数，假如存在WHERE语句，GROUP BY 语句必须放到WHERE 后面。

我们来看这样一个SQL 语句：

SELECT FAge ，FName，FSalary FROM PERSON GROUP BY FAge

看上去没有语法错误，但是这句话是不能执行的，这句话首先根据FAge将记录分了组，但是却要查询FName，这是不合逻辑的，因为FName 不能统一代表一个组，同理FSalary也不能代表，但是AVG(Salary)就可以使用，因为一个组中平均水平是可以算出来的。

GROUP BY 可以指定多个列，数据库排序时会先按照第一个进行分组，然后依次排列

数据分组和聚合函数

假如想统计每个年龄段的人数，可以将数据分组和聚合函数一起使用

SELECT FAge ,(COUNT *) FROM PERSON GROUP BY FAge

假如想统计每个分公司年龄段的人数

SELECT FCompany ， FAge ，COUNT(*) FROM PERSON GROUP BY FAge,FCompany ORDER BY FCompany

HAVING 语句

假如想将分组进行过滤，如查询组里的数量大于1的情况，不能使用WHERE，可以使用HAVING

SELECT FAge ，COUNT(*) FROM PERSON GROUP BY FAge HAVING COUNT(*) > 1

HAVING 可以像WHERE 一样，HAVING COUNT （*) = 1 OR COUNT (*) == 3

限制结果集的数量

通过查询数据库，结果都会以数组的形式返回，称为结果集

假如想查询成绩排名前三的记录，不同的数据库有不同的方法

MySQL

MySQL 提供了LIMIT 语句，放在SELECT 语句的最后

SELECT * FROM PERSON ORDER BY FSalary DESC LIMIT 2，5 ：从第二行开始，查找共5条

MS SQL Server 2000

提供了TOP关键字，即前5条

SELECT TOP 5 * FROM PERSON ORDER BY FSalary DESC

数据库分页

请求接口时数据一般会进行分页，数据库也可以进行分页

数据库分页的原理就是限制结果集行数，如My SQL，查找从pageIndex 页，pageSize 个记录

SELECT * FROM PERSON LIMIT pageIndex*pageSize, pageSize

抑制数据重复

假如检索成绩都有哪些

SELECT Salary FROM PERSON

那么所有的成绩都会列出，难免出现重复的数据，我们可以通过DISTINCT将重复的数据刨除

SELECT DISTINCT Salary FROM PERSON

计算字段

假如数据库中存储的是姓名，工号，但是展示的时候想展示姓名 + 工号，这就需要用到计算字段

计算字段是数据库系统提供的对数据进行计算，转换或者格式化的功能，由于是在数据库内部做的操作，数据库对此做了优化。

常量字段

当我们查询具体某个班的学生成绩时，想将班级也列出来，虽然表中没有这个字段，但是我们可以固定添加上去

SELECT '980' ,FName, FAge FROM PERSON

执行这个语句的时候，虽然表中没有这980个列，但是所有记录被搜索出来时，都会默认添加上这个列，且值为980

字段间的计算

SELECT FNumber ，FName，FAge * FSalary FROM PERSON

计算出了FAge * FSalary 的值，即SELECT 时可以将表中的字段进行处理，同样，WHERE 中也能进行计算：

SELECT * FROM PERSON WHERE FSalary/(FAge - 21) > 1000

即查询工资除以年龄减去21后，大于1000 的记录

数据处理函数

就像C语言一样，SQL 支持使用函数处理数据，像是COUNT(*) 等，都可以算作数据库中的函数

常见的数据库函数，有获得字符串长度的LENGTH(),获得字符串字串的SUBSTR(mainStr,startIndex,subStrLength),三个参数分别为主字符串，子串开始的位置，子串的长度，如取得一个FName 不为空，从第二个字符开始，长度为3的字串长度，SELECT FName SUBST (FName,2,3) FROM PERSON WHERE FName IS NOT NULL MySQL 中该函数为SUBSTRING

多个处理函数可以嵌套使用，