SQL笔记-第四章，数据的检索

一.select的简单用法

1.简单的数据检索

SELECT * FROM T_Employee;

2.检索出需要的列

SELECT FNumber,FName,FAge FROM T_Employee

3.列别名

SELECT FNumber AS Number1,FName AS Name,FAge AS Age,FSalary AS Salary FROM T_Employee

4.按条件过滤

SELECT FName FROM T_Employee WHERE FSalary<5000

SELECT * FROM T_Employee WHERE FSalary<5000 OR FAge>25

5.数据汇总

SELECT MAX(FSalary) FROM T_Employee WHERE FAge>25

SELECT MAX(FSalary) as MAX_SALARY FROM T_Employee WHERE FAge>25

SELECT AVG(FAge) FROM T_Employee WHERE FSalary>3800

SELECT SUM(FSalary) FROM T_Employee

SELECT MIN(FSalary),MAX(FSalary) FROM T_Employee

SELECT COUNT(*),COUNT(FNumber) FROM T_Employee; (区别是*把空值也统计了)

6.排序

SELECT * FROM T_Employee ORDER BY FAge ASC (升序，降序是DESC)

二.高级数据过滤

1 通配符过滤

单字符匹配

以任意字符开头，剩余部分为“erry”。

SELECT * FROM T_Employee WHERE FName LIKE ‘_erry’

多字符匹配

以“T”开头长度，长度任意。

SELECT * FROM T_Employee WHERE FName LIKE ‘T%’

包含字母“n”。

SELECT * FROM T_Employee WHERE FName LIKE ‘%n%’

集合匹配

集合匹配只在MSSQLServer 上提供支持，在MYSQL、Oracle、DB2 等数据库中不支持，必须采用变通的手段来实现。

2 空值检测

错误：SELECT * FROM T_Employee WHERE FNAME=null

正确：SELECT * FROM T_Employee WHERE FNAME IS NULL

SELECT * FROM T_Employee WHERE FNAME IS NOT NULL

3反义运算符

SELECT * FROM T_Employee WHERE FAge!=22 AND FSALARY!<2000; (只有MS，DB2支持)

SELECT * FROM T_Employee WHERE NOT(FAge=22) AND NOT(FSALARY<2000)

4.多值检测

SELECT FAge,FNumber,FName FROM T_Employee WHERE FAge=23 OR FAge=25 OR FAge=28

5.范围值检测

SELECT * FROM T_Employee WHERE FAGE IN(23,24,25,26,27)

SELECT * FROM T_Employee WHERE FAGE>=23 AND FAGE <=27

SELECT * FROM T_Employee WHERE FAGE BETWEEN 23 AND 27

SELECT * FROM T_Employee WHERE (FSalary BETWEEN 2000 AND 3000) OR (FSalary BETWEEN 5000 AND 8000)

三.数据分组

1.数据分组入门

SELECT FAge FROM T_Employee GROUP BY FAge

SELECT FAge FROM T_Employee WHERE FSubCompany = ‘Beijing’ GROUP BY FAg

SELECT FSubCompany,FDepartment FROM T_Employee GROUP BY FSubCompany,FDepartment

2.数据分组与聚合函数

SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee GROUP BY FAge

SELECT FSubCompany,FAge,COUNT(*) AS CountOfThisSubCompAge FROM T_Employee GROUP BY FSubCompany,FAge

SELECT FSubCompany,FAge,COUNT(*) AS CountOfThisSubCompAge FROM T_Employee GROUP BY FSubCompany,FAge ORDER BY FSubCompany

SELECT FDepartment,SUM(FSalary) AS FSalarySUM FROM T_Employee GROUP BY FDepartment

SELECT FDepartment,MIN(FAge) AS FAgeMIN,MAX(FAge) AS FAgeMAX FROM T_Employee GROUP BY FDepartment

3.HAVING 语句

错误：SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee GROUP BY FAge WHERE COUNT(*)>1 因为聚合函数不能在WHERE语句中使用

正确 ：SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee GROUP BY FAge HAVING COUNT(*)>1

SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee GROUP BY FAge HAVING COUNT(*) =1 OR COUNT(*) =3

SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee GROUP BY FAge HAVING COUNT(*) IN (1,3)

注意：在HAVING语句中不能包含未分组的列名

错误：SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee GROUP BY FAge HAVING FName IS NOT NULL

正确 ： SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee WHERE FName IS NOT NULL GROUP BY FAge

四. 限制结果集行数

mysql:

SELECT * FROM T_Employee ORDER BY FSalary DESC LIMIT 2,5

oracle,DB2:

SELECT * FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY FSalary DESC) row_num,
FNumber,FName,FSalary,FAge FROM T_Employee
) a
WHERE a.row_num>=3 AND a.row_num<=5

DB2:

SELECT * FROM T_Employee ORDER BY FSalary Desc FETCH FIRST 6 ROWS ONLY

五.抑制数据重复

SELECT DISTINCT FDepartment FROM T_Employee

SELECT DISTINCT FDepartment,FSubCompany FROM T_Employee (组合不重复)

六.计算字段

1.常量字段

在数据库中不存在，只是会显示出来

SELECT ‘CowNew 集团’ AS CompanyName,918000000 AS RegAmount,FName,FAge,FSubCompany FROM T_Employee

2.字段间计算

SELECT FNumber,FName,FAge * FSalary AS FSalaryIndex FROM T_Employee

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

3.数据处理函数

LENGTH(FName)：取得字符串长度

SUBSTRING(FName,2,3)：取FName中第2第3个字符

SIN(FAge)：正弦

…

4.字符串的拼接

mysql:

SELECT CONCAT(‘工号为:’,FNumber,’的员工的幸福指数:’,FSalary/(FAge-21)) FROM T_Employee

SELECT CONCAT_WS(‘,’,FNumber,FAge,FDepartment,FSalary) FROM T_Employee (第一个参数是分隔符)

oracle:

SELECT ‘工号为’||FNumber||’的员工姓名为’||FName FROM T_Employee WHERE FName IS NOT NULL

SELECT CONCAT(‘工号:’,FNumber) FROM T_Employee (只支持两个参数,支持类型转换)

DB2:

用||

CONCAT (只支持两个参数,不支持类型转换)

5.计算字段的其他用途

计算处于合理工资范围内的员工

SELECT * FROM T_Employee WHERE Fsalary BETWEEN Fage*1.5+2000 AND Fage*1.8+5000

查询“工资年龄指数”

SELECT MAX(FSalary/FAge) AS MAXVALUE,MIN(FSalary/FAge) AS MINVALUE FROM T_Employee

年龄全部加1

UPDATE T_Employee SET FAge=FAge+1

七.不从实体表中取的数据

SELECT DISTINCT 1 FROM T_Employee

SELECT 1,2,3,’a‘,’b‘,’c’

八.联合结果集

SELECT FNumber,FName,FAge FROM T_Employee
UNION
SELECT FIdCardNumber,FName,FAge FROM T_TempEmployee

一是每个结果集必须有相同的列数

二是每个结果集的列必须类型相容

SELECT FName,FAge FROM T_Employee
UNION ALL
SELECT FName,FAge FROM T_TempEmployee

九.联合结果集应用举例

员工年龄报表

SELECT ‘正式员工最高年龄’,MAX(FAge) FROM T_Employee
UNION
SELECT ‘正式员工最低年龄’,MIN(FAge) FROM T_Employee
UNION
SELECT ‘临时工最高年龄’,MAX(FAge) FROM T_TempEmployee
UNION
SELECT ‘临时工最低年龄’,MIN(FAge) FROM T_TempEmployee

正式员工工资报表

SELECT FNumber,FSalary FROM T_Employee
UNION
SELECT ‘工资合计’,SUM(FSalary) FROM T_Employee

列出员工姓名

SELECT FName FROM T_Employee
UNION
SELECT FName FROM T_TempEmployee

分别列出正式员工和临时工的姓名

SELECT ‘以下是正式员工的姓名’
UNION ALL
SELECT FName FROM T_Employee
UNION ALL
SELECT ‘以下是临时工的姓名’
UNION ALL
SELECT FName FROM T_TempEmployee