一.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