comp3311 辅导 week4

​

文章目录

In operator may be wrong

SELECT *
FROM suppliers
WHERE supplier_name IN ('Microsoft', 'Oracle', 'Flowers Foods');

SELECT *
FROM suppliers
WHERE supplier_name = 'Microsoft'
OR supplier_name = 'Oracle'
OR supplier_name = 'Flowers Foods';

Division

division 是如何达到使用except代表的功能的？

Division (cont)
Not all SQL implementations provide a division operator

But can be achieved by combination of existing operations

Example: Find bars that each sell all of the beers Justin likes.

SELECT DISTINCT a.bar
FROM   Sells a
WHERE  NOT EXISTS (
           (SELECT beer FROM Likes
            WHERE drinker = 'Justin')
           EXCEPT
           (SELECT beer FROM Sells b
            WHERE bar = a.bar)
       );

Partitions

和group by的区别，就是partition原来有多少row，处理之后还有多少row，而且可使用的函数也更多。
​​​partition 使用​​

recursive queries

难理解，在很多层级结构的数据库中使用。

plpgsql

一种嵌入在dbms中的过程式语言
这种语言的function和view很相似。

学习一门语言要注意的地方

数据类型
if else
for
while

PLpgSQL constants and variables can be defined using:

standard SQL data types (CHAR, DATE, NUMBER, …)
user-defined PostgreSQL data types (e.g. Point)
a special structured record type (RECORD)
table-row types (e.g. Branches%ROWTYPE or simply Branches)
types of existing variables (e.g. Branches.location%TYPE)
There is also a CURSOR type for interacting with SQL.

Variables can also be defined in terms of:

the type of an existing variable or table column
the type of an existing table row (implict RECORD type)
Examples:
quantity INTEGER;
start_qty quantity%TYPE;

employee Employees%ROWTYPE;
– or
employee Employees;

name Employees.name%TYPE;