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

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

DROP TABLE r

Alter Table（增减属性）

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

Creat Index or Drop Index（构建索引）

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

Select

//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

SELECT branch_name
FROM loan
WHERE branch_name = "Perryridge"

SELECT loan_name
FROM loan
WHERE amout BETWEEN 90000 AND 100000

From

//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 “||” )

  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.

ORDER BY customer_name asc

Set Operations

UNION: ⋃

INTERSECT: ⋂

EXCEPT: -

(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

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.

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

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

WITH max_balance(value) as 
  SELECT max(balance)
  FROM account
SELECT account_number
FROM account, max_balance
WHERE account.balance = max_balance.value

Deletion

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

Insertion

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

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

Update

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

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)

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

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