【DBS】Lecture 03. SQL

发表于 | 分类于 ， | | 阅读次数:

Data Definition Language

Domain Types in SQL

  • char(n): Fixed length character string, with user-specified length.
  • varchar(n): Variable length character strings, with user-specified maximum length n.
  • int: Integer (a finite subset of the integers that is machine-dependent).
  • smallint: Small integer (a machine-dependent subset of the integer domain type).
  • numeric(p, d): Fixed point number, with user-specified precision of p digits, with d digits to the right of decimal point.

  • real, double precision: Floating point and double-precision floating point numbers, with machine-dependent precision.

  • float(n): Floating point number, with user-specified precision of at least n digits.

  • Null values are allowed in all the domain types. Declaring an attribute to be not null prohibits null values for that attribute.

  • date: Dates, containing a (4 digits) year, month and date.

    E.g., date ‘2007-2-27’

  • Time: Time of day, in hours, minutes and seconds.

    E.g., time ‘11:18:16’, time ‘11:18:16.28’

  • timestamp: date plus time of day.

    E.g., timestamp ‘2011-3-17 11:18:16.28’

Creat Table

1
2
3
4
CREATE TABLE r (A1 D1, A2 D2, ..., An Dn,
              (integrity constraint1), 
              ..., 
              (integrity constraintk))

  • r is the name of the relation

  • Each Ai is an attribute name in the schema of relation r

  • Di is the data type of values in the domain of attribute Ai

Drop Table

1
DROP TABLE r

Alter Table（增减属性）

1
2
3
ALTER TABLE r ADD A D;
ALTER TABLE r ADD (A1D1,…,AnDn);
ALTER TABLE r DROP A

Creat Index or Drop Index（构建索引）

1
2
3
CREATE INDEX <i-name> ON <table-name>(<attribute-list>)
CREATE UNIQUE INDEX <i-name> ON <table-name>(<attribute-list>)
DROP INDEX <i-name>

Select

1
2
3
4
5
6
7
8
9
10
11
12
13
14
//allow duplicates
SELECT branch_name
FROM loan

//no duplicates
SELECT distinct branch_name
FROM loan

//allow duplicates
SELECT all branch_name
FROM loan

//all attributes
SELECT * FROM loan

allow arithmetic expressions

Where

SELECT branch_name
FROM loan

1
2
3
4
5
6
7
SELECT branch_name
FROM loan
WHERE branch_name = "Perryridge"

SELECT loan_name
FROM loan
WHERE amout BETWEEN 90000 AND 100000

From

1
2
3
//Cartesian product borrow x loan
SELECT *
FROM borrow,loan

String Operations

  • % — matches any substring (like * in the file system).

  • _ — matches any character (like ? in the file system).

  • Converting from upper to lower case (and vice versa) using Functions lower( ) and upper( )

  • Concatenation (using “||” )

    1
    2
    3
    SELECT ‘客户名=’ || customer_name 
    FROM *customer* 
    WHERE …

Ordering

We may specify desc for descending order or asc for ascending order, and for each attribute, ascending order is the default. 

1
ORDER BY customer_name asc

Set Operations

UNION: ⋃

INTERSECT: ⋂

EXCEPT: -

1
2
3
4
5
6
7
8
9
10
11
(SELECT customer_name FROM depositor)
UNION
(SELECT customer_name FROM borrower) 

(SELECT customer_name FROM depositor) 
INTERSECT 
(SELECT customer_name FROM borrower) 

(SELECT customer_name FROM depositor) 
EXCEPT
(SELECT customer_name FROM borrower)

Aggregate Functions

  • avg(col): average value

  • min(col): minimum value

  • max(col): maximum value

  • sum(col): sum of values

  • count(col): number of values 

1
2
3
SELECT avg(balance) avg_bal 
FROM account 
WHERE branch_name = ‘Perryridge’

Attributes in select clause outside of aggregate functions must appear in group by list. 

1
2
3
SELECT branch_name, avg(balance) avg_bal 
FROM account 
GROUP BY brach_name

Null

  • The meaning is “missing information” or “inapplicable information”, i.e., unknown value or that a value does not exist.

  • The result of any arithmetic expression involving ‘null’ is null. eg. 5 + null returns null.

  • Any comparison with null returns “unknown” eg. 5 < null or null <> null or null = null

View

1
2
3
4
5
6
7
CREATE VIEW <v_name> AS 
SELECT c1, c2, … From … 

CREATE VIEW <v_name> (c1, c2, …) AS 
SELECT e1, e2, … FROM … 

DROP VIEW <V_NAME>

With

allows views to be defined locally for a query

1
2
3
4
5
6
WITH max_balance(value) as 
  SELECT max(balance)
  FROM account
SELECT account_number
FROM account, max_balance
WHERE account.balance = max_balance.value

Deletion

1
2
DELETE FROM <table|view>
[WHERE <condition>}

Insertion

1
2
3
4
5
6
INSERT INTO <table|view>[(c1,c2,...)]
VALUES(e1,e2,...)

INSERT INTO <table|view>[(c1,c2,...)]
SELECT e1,e2,...
FROM ...

Update

1
2
3
4
5
6
UPDATE account
SET balance = case
                when balance <= 10000
                then balance*1.05
                else balance*1.06
              end

Transactions

  • commit work: makes all updates of the transaction permanent in the database
  • rollback work: undoes all updates performed by the transaction
    1
    2
    3
    4
    UPDATE account SET balance = balance - 100 WHERE
    account_number = 'A-101'
    UPDATE account SET balance = balance + 100 WHERE
    account_number = 'A-201'

Joined Relations

  • Join types:
    • inner join
    • left outer join
    • right outer join
    • full outer join
  • Join Conditions
    • natual
    • on
    • using (A1,A1,…,An)

jr

Natural join: 以同名属性相等作为连接条件
Inner join：只输出匹配成功的元组
Outer join：还要考虑不能匹配的元组
非自然连接，容许不同名属性的比较，且结果关系中不消去重名属性。


Reference

https://www.cnblogs.com/grandyang/p/5346963.html