常用SQL笔记

记录下SQL笔记,久了没用忘掉了,咳,真TMD郁闷

分组后WHERE用having

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

防止重复数据的产生
SELECT DISTINCT FDepartment FROM T_Employee;

字段间计算

SELECT 125+521 as staticnm,FNumber,FName,FAge * FSalary FROM T_Employee;

字符串处理

SELECT FName, LENGTH(FName) AS namelength FROM T_Employee WHERE FName IS NOT NULL

SELECT FName, SUBSTRING(FName,2,3) FROM T_Employee WHERE FName IS NOT NULL

SELECT CONCAT('年龄:',FAge) FROM T_Employee

结果集联合(需要遵守：一是每个结果集必须有相同的列数；二是每个结果集的列必须类型相容。)

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

默认情况下，UNION运算符合并了两个查询结果集,需要在联合结果集中返回所有的记录而不管它们是否唯一，则需要在 UNION运算符后使用ALL操作符，比如下面的SQL语句：
SELECT FName,FAge FROM T_Employee UNION ALL SELECT FName,FAge FROM T_TempEmployee

MYSQL中使用DATEDIFF()函数用于计算两个日期之间的差额

SELECT FRegDay,FBirthDay, DATEDIFF(FRegDay, FBirthDay) , DATEDIFF(FBirthDay ,FRegDay)FROM T_Person

CASE函数

SELECT FName,

(CASE FName

WHEN 'Tom' THEN 'GoodBoy'
WHEN 'Lily' THEN 'GoodGirl'
WHEN 'Sam' THEN 'BadBoy'
WHEN 'Kerry' THEN 'BadGirl'
ELSE 'Normal'
END) as isgood

FROM T_Person

MYSQL不支持如下,当DB2等数据库支持

一次定义多次使用

WITH person_tom AS
(
SELECT * FROM T_Person
WHERE FName='TOM'
)
SELECT * FROM T_Person
WHERE FAge=person_tom.FAge
OR FSalary=person_tom.FSalary