Database and SQL (3)- Functions, Sub-Queries, Multiple Tables

1. Built-in Database Functions

SQL 有很多内置函数，可以作为SQL statement的一部分，加速数据处理和减少代码量。你也可以自己定义function。

SUM(COLUMN_NAME)

MIN(COLUMN_NAME)

MAX(COLUMN_NAME)

AVG(COLUMN_NAME)

我们也可以使用数学表达式来处理列，例如

AVG(SALEPRICE / QUANTITY)

Scaler function:

ROUND(COLUMN_NAME)

LENGTH(COLUMN_NAME)

String function:

UCASE(COLUMN_NAME)

LCASE(COLUMN_NAME)

2. Date and Time Built-in Functions

许多数据库包含了特殊数据类型，对应日期和时间。

DATE: YYMMDD

TIME: HHMMSS

TIMESTAMP: YYYYXXDDHHMMSSZZZZZZ

Date/time functions:

YEAR(), MONTH(), DAY(), DAYOFMONTH(), DAYOFWEEK(),
DAYOFYEAR(), WEEK(), HOUR(), MINUTE(), SECOND()

Examples:

select DAY(SALEDATE) FROM PETSALE
WHERE ANIMAL='Cat';

select COUNT(*) from PETSALE
where MONTH(SALEDATE)='05';

What date is it 2 days after each sale date?

select (SALEDATE + 2 DAYS) from PETSALE;

Special Registers:

CURRENT_DATE, CURRENT_TIME

Find how many days have passed since each SALEDATE till now:

select (CURRENT_DATE-SALEDATE) from PETSALE

 

3. Sub-Queries and Nested Selects

Sub-queries指的是在一个查询结果里，继续查询。如

SELECT COLUMN1 FROM TABLE 
WHERE COLUMN2 = (SELECT MAX(COLUMN2) FROM TABLE)

为什么需要sub-querie？因为built-in function不能直接在where 后使用。还有另一种情况，如下代码，第一行的会报错，因为没有group by，而使用第二行的参数就可以成功实现对应的查询：

SELECT EMP_ID, SALARY, AVG(SALARY) AS AVG_SALARY FROM EMPLOYEES;

SELECT EMP_ID, SALARY, 
(SELECT AVG(SALARY) FROM EMPLOYEES) AS AVG_SALARY
FROM EMPLOYEES;

也能在sub-queries时使用多个column结合AS命名一个子table。

4. Working with Multiple Tables

有三种方法可以在同一条查询语句里访问多个tables:

1. Sub-queries；2. Implicit JOIN; 3. JOIN operators (INNER JOIN, OUTER JOIN, etc).

这里先介绍前两种，第三种在后面的文章里再写。

假设我们有employees 和 departments 两个tables，为了取得部门数据库中对应部门的员工数据，可以 使用sub-queries：

select * from employees
where DEP_ID in
(select DEPT_ID_DEP from departments);

使用Implicit Join：

select * employees, departments;

select * from employees, departments
where employees.DEP_ID = departments.DEPT_ID_DEP;

select * from employees E, departments D
where E.DEP_ID = D.DEPT_ID_DEP;

select employees.EMP_ID, departments.DEPT_NAME
from employees E, departments D
where E.DEP_ID = D.DEPT_ID_DEP;

select E.EMP_ID, D.DEPT_NAME
from employees E, departments D
where E.DEP_ID = D.DEPT_ID_DEP;