SQL之QL

从中文语法上来说，应该先写FROM语句比较好理解

基础查询语句

SELECT [DISTINCT] target-list 
FROM tables 
WHERE qualification
GROUP BY grouping-list
HAVING group-qualification  

• DISTINCT：要求结果没有重复元组

查询过程（不优化，好理解）

1. 将tables做笛卡尔乘积（FROM）
2. 根据qualification删除元组（WHERE）
3. 根据target-list删除的属性（SELECT）
4. 根据是否DISTINCT删除重复元组
5. 根据group-list分组（GROUP BY）
6. 根据group-qu alification删除组（HAVING）

注意：select语句和having语句里的属性只能是group by里属性的子集。例如：group by sex，那么select和having里不能有sname。

定义列别名

SELECT SNAME AS '学生姓名'

范围条件BETWEEN AND

SELECT SNAME FROM S WHERE BIRTHDADY NOT BETWEEN '1996-07-01' AND '1998-06-30'

属于集合条件 IN

SELECT SNAME FROM S WHERE SID IN (SELECT SID FROM S WHERE xxx)

字符匹配

SELECT * FROM S WHERE SNO LIKE 'S_%0'

'-'为通配符，匹配任意一个字符

'%'匹配任意个字符

聚合函数

COUNT(*)
COUNT([DISTINCT] A)
SUM([DISTINCT] A)
AVG([DISTINCT] A)
MAX(A)
MIN(A)

一般group by要和聚合函数一起使用。

CAST表达式

CAST (Expression AS Data type)

例子：

Students (name, school)
Soldiers (name, service)

CREATE VIEW PROSPECTS(name, school, service) AS
	SELECT name, school, CAST(NULL AS Varchar(20))
UNION
	SELECT name, CAST(NULL AS Varchar(20)), service
	FROM Soldiers;

有点类似C++里的强制类型转换。

CASE表达式

例子：

Officers (name, status, rank, title)
SELECT name, CASE status
				  WHEN 1 THEN 'Active Duty'
				  WHEN 2 THEN 'Reserve'
				  WHEN 3 THEN 'Special Assignment'
				  WHEN 4 THEN 'Retired'
				  ELSE 'Unknown'
		 	 END AS tatus
FROM Officers;

给status重命名以便于阅读。

例子：

Machines (id, type, ohurs_used, accidents)
'找到“chain saw”故障时间所占比例'
SELECT sum(CASE
				WHEN type='chain saw' THEN accidents
				ELSE 0e0
		   END) / sum(accidents)
FROM Machines;

子查询（嵌套查询）

• 结果是一个值

  '工作在纽约的部门和这个部门最高工资'
  SELECT d.deptno, d.deptname, (SELECT MAX(salary)
                                FROM emp
                                WHERE deptno = d.deptno) AS maxpay
  FROM dept AS d
  WHERE d.location = 'New York';
  

• 结果是表

  '查询每年新入职员工的平均pay'
  SELECT startyear, avg(pay)
  FROM (SELECT name salary+bonus AS pay,
       		 year(startdate) AS startyear
        FROM emp) AS emp2
  GROUP BY startyear;
  

• 公共表表达式（临时视图）

  防止payroll运算两次。

  '查找总收入最高的部门'
  WITH payroll (depno, totalpay) AS
  	(SELECT deptno, sum(salary) + sum(bonus)
      FROM emp
      GROUP BY deptno)
  SELECT deptno
  FROM payroll
  WHERE totalpay = (SELECT max(totalpay)
                    FROM payroll)
  

  实现外连接

  WITH
  '自然连接'
  innerjoin(name, rank, subject, enrollment) AS
  	(SELECT t,name, t.rank, c.subject, c.rnrollment
  	FROM teachers AS t, course AS c
  	WHERE t.name=c.teacher AND c.quarter='FALL 96'),
  '左外连接'
  teacher-only(name, rank) AS
  	(SELECT name, rank
  	FROM teachers
  	EXCEPT ALL
  	SELECT name, rank
  	FROM innerjoin),
  '右外连接'
  course-only(subject, enrollment) AS 
  	(SELECT subject, enrollment
  	FROM courses
  	EXCEPT ALL
  	SELECT subject, enrollment
  	FROM innerjoin)
  
  SELECT name, rank, subject, enrollment
  FROM innerjoin
  UNION ALL
  SELECT name, rank,
  	CAST (NULL AS Varchar(20)) AS subject
  	CAST (NULL AS Integer) AS enrollment
  FROM teacher-only
  UNION ALL
  SELECT CAST (NULL AS Varchar(20)) AS name,
  	   CAST (NULL AS Varchar(20)) AS rank,
  	   subject, enrollment
  FROM course-only;