数据库之MySQL之代数指令和MySQL语句的转化

---

一、查

0.null值

Find all loan number which appear in the loan relation with null values for amount.

select loan_number
from loan
where amount is null

1.基本代数指令

（1）where语句与select:$\sigma$

select A1, A2, ..., An
from r1, r2, ..., rm
where P

A是属性，r是表名，P是谓词

相当于${\Pi }_{A1,A2,...,An}({\sigma }_P(r1\times r2\times ...\times rm))$

多个谓词的链接

AND, OR, NOT

（2）select from语句与project:$\Pi$

基本形式

select A1, A2, ..., An
from r

A是属性，r是表名
相当于${\Pi }_{A1,A2,...,An}(r)$

选择所有列

select * 
from table

去重：distinct
select 在查询出结果时不会去重。注意这一点与关系代数不同

select distinct branch_name
from loan;

默认保留所有重复元组，可以不写all

select all branch_name
from loan;

重复duplicates：重复的实体不会被删掉，会参与其他的运算。

相关关系 r1 ( A, B ) and r2 ( C ) ：r1 = {(1, a) (2,a)} ，r2 = {(2), (3), (3)}
${\Pi }_B(r1)$: {(a), (a)},
${\Pi }_B(r1)\times r2$ ：{(a,2), (a,2), (a,3), (a,3), (a,3), (a,3)}

注意：不像代数运算

（3）union语句和union:$\cup$

• 自动去重
• 如果要保留重复的：union all

Find all customers who have a loan, an account, or both:

(select customer_name from depositor)
union
(select customer_name from borrower);

（4）except语句和set difference: −

同union语句

• 自动去重
• 如果要保留重复的：except all

Find all customers who have an account but no loan.

(select customer_name from depositor)
except
(select customer_name from borrower);

（5）from语句和cartesian product: X

select A1, A2, ..., An
from r1, r2, ..., rm

相当于${\Pi }_{A1,A2,...,An}(r1\times r2\times ...\times rm)$

（6）as语句和rename：$\rho$

可以像rename：$\rho$一样重命名相关关系

select customer_name, T.loan_number, S.amount
from borrower as T, loan as S
where  T.loan_number = S.loan_number;

${\Pi }_{customer\_name,T.loan\_number,S.amount}({\sigma }_{T.loan\_number=S.loan\_number}({\rho }_T(borrower)\bowtie {\rho }_S(loan)))$

还可以重命名列名

select customer_name, borrower.loan_number as loan_id, amount
from borrower, loan
where borrower.loan_number = loan.loan_number;

${\Pi }_{customer\_name,borrower.loan\_numberasloan\_id,amount}$

PS:as可以不写，新旧名字之间有个空格就行。

mysql> select balance b
    -> from account a
    -> where a.balance >800;

2.扩展代数指令

（1）intersect语句和set-intersection：∩

同union语句

• 自动去重
• 如果要保留重复的：intersect all

Find all customers who have a loan and an account

(select customer_name from depositor)
intersect
(select customer_name from borrower);

（2）natural join(inner join)

①只通过from和where

同代数的符号原理一样。

• 这样写是效果一样，但会有重复的列。
  
• 如果要选择重复的元素，还得指定它的表名，不然数据库不知道选择哪个。
  

Find the names of all customers who have a loan at the Perryridge branch.

${\Pi }_{customer\_name}({\sigma }_{branch\_name=“Perryridge”}(loan\bowtie borrower))$

select customer_name
from loan,borrower
where borrower.loan_number = loan.loan_number and branch_name = 'Perryridge';

②通过inner join关键字

格式：

select <列名>
from <表名1> inner join <表名2>
on 表名1.同名列=表名2.同名列；

Find the names of all customers who have a loan at the Perryridge branch.

select customer_name
from loan inner join borrower
on loan.loan_number=borrower.loan_number;

3.附加代数指令

（1）generalized projection

select loan_number, branch_name, amount *100
from loan;

相当于${\Pi }_{loan\_number,branch\_name,amount\ast 100}(loan)$

（2）aggregate functions

同代数指令一样的名字：

聚合函数	含义
avg	average value
min	minimum value
max	maximum value
sum	sum of values
count	number of values

如果你不rename起一个名字的话，列名就是函数名加（）里的字符串

①聚合函数

Find the average account balance at the Perryridge branch.

select avg(balance)
from account
where branch_name = 'Perryridge';

+---------------+
| avg(balance) |
+---------------+
|      433.3333 |
+---------------+

Find the number of tuples in the customer relation.

select count(*)
from customer;

+----------+
| count(*) |
+----------+
|       13 |
+----------+

Find the number of depositors in the bank.

select count(distinct customer_name)
from depositor;

+-------------------------------+
| count(distinct customer_name) |
+-------------------------------+
|                             6 |
+-------------------------------+

PS：聚合函数后面紧跟()，不要留空格，如max ()中有空格时就会发生报错

FUNCTION bank.max does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual

②分组：group by语句

聚合函数作用于分组后的各个元组，而不是原始的所有元组

Find the average loan amount for each branch

select branch_name, avg(amount)
from loan
group by branch_name;

+-------------+-------------+
| branch_name | avg(amount) |
+-------------+-------------+
| Downtown    |   1250.0000 |
| Mianus      |    500.0000 |
| Perryridge  |   1400.0000 |
| Redwood     |   2000.0000 |
| Round Hill  |    900.0000 |
+-------------+-------------+

③having语句：WHERE 关键字无法与聚合函数一起使用

having语句条件是对分配后的组施加条件，而不是原始的元组！

Find the names of all branches where the average loan amount is more than $1,200.

select branch_name, avg(amount)
from loan
group by branch_name
having avg (amount) > 1200;

+-------------+-------------+
| branch_name | avg(amount) |
+-------------+-------------+
| Downtown    |   1250.0000 |
| Perryridge  |   1400.0000 |
| Redwood     |   2000.0000 |
+-------------+-------------+

④执行顺序

⑤聚合函数不能作用于查询外部

SELECT branch_name
FROM  account
GROUP BY branch_name
HAVING avg(balance)= max
( SELECT avg(balance)
FROM account
GROUP BY branch_name);

max的用法就是非法的，执行失败

⑥聚合函数不能嵌套

SELECT branch_name
FROM account
GROUP BY branch_name
HAVING avg(balance)= 
(SELECT max(avg(balance)) 
FROM account
GROUP BY branch_name) ;

max(avg(balance)) 的用法就是非法的，执行失败

3.outer join

都和natural join(inner join)是同一个格式。

代数指令	关键字
natural join(inner join)	inner join
left join	left join
right join	right join
full join	full join

如：left join

select customer_name
from loan left join borrower
on loan.loan_number=borrower.loan_number;

4.特殊语句

（1）string operation字符串匹配：like语句

• percent (%)：The % character matches any substring（任意字符串）.
• underscore (_) ：The _ character matches any character (单个任意字符).

Find the names of all customers whose street includes the substring “Main”.

select customer_name
from customer
where customer_street like '%Main%'

Match the name “Main%”

加个escape ''表示声明此为转移字符，%是一个符号，而不是表示任意字符串。

like 'Main\%' escape '\'

（2）排序行：order by语句

用来排序元组的顺序，

List in alphabetic order the names of all customers having a loan in Perryridge branch

select distinct customer_name
from borrower, loan
where borrower loan_number = loan.loan_number and branch_name = 'Perryridge'
order by customer_name

升序与降序

desc：降序descending order
asc：升序ascending order（默认）

order by customer_name desc

5.嵌套语句

（1）from嵌套

可以在from里嵌套一个查询操作

Find the average account balance of those branches where the average account balance is greater than $1200.

select branch_name, avg_balance
from (
	select branch_name, avg (balance)
	from account
	group by branch_name )
	as branch_avg ( branch_name, avg_balance )
where avg_balance > 1200

（2）IN谓词

判断列名是否在一个相关关系结果中

Find all customers who have both an account and a loan at the bank.

select distinct customer_name
from borrower
where customer_name in (select customer_name from depositor)

（3）NOT IN谓词

判断列名是否不在在一个相关关系结果中

Find all customers who have a loan at the bank but do not have an account at the bank

select distinct customer_name
from borrower
where customer_name not in (select customer_name from depositor)

（4）Set Comparison之some

some表示相关关系结果中的某一个值

Find all branches that have greater assets than some branch located in Brooklyn

select branch_name
from branch
where assets > some
(select assets from branch where branch_city = 'Brooklyn')

（5）Set Comparison之all

some表示相关关系结果中的所有的值

（6）EXISTS语句

含义

The exists construct returns the value true if the argument subquery is nonempty.
如果子引用的相关相关结果存在，那就where判断返回真。

语法

用在where中，EXISTS()内部的子语句要引用外部的表

何时使用？

问题是至少一个时（至少一个即表示存在）

Find the customers name who have at least one deposit of a balance greater than $700.

SELECT DISTINCT customer_name
FROM  depositor
WHERE EXISTS (
	SELECT  *
	FROM  account
	WHERE depositor.account = account.account AND balance > 700 ) 

PS :自然有NOT EXISTS语句

（7）UNIQUE语句

The unique construct tests whether a subquery has any duplicate tuples in its result.
选择没有重复的元组即只有一个

Find all customers who have at most one account at the Perryridge branch.

select T.customer_name
from depositor as T
where unique (
select R.customer_name
from account, depositor as R
where T.customer_name = R.customer_name 
and R.account_number = account.account_number
and	account.branch_name = 'Perryridge' )

PS：书上将的这个东西我在Mysql中打出来没有这个Unique语法，(oﾟvﾟ)ノ求解惑。

（8）With语句

含义

The with clause provides a way of defining a temporary view whose definition is available only to the query in which the with clause occurs.
实就是把一大堆重复用到的SQL语句放在with as 里面，取一个别名，后面的查询就可以用它

语法

单个：

With [别名1](列名...) as （一个相关关系结果）

多个：

With 
[别名1](列名...) as (一个相关关系结果),
[别名2](列名...) as (一个相关关系结果)

PS：不写列名错，一个相关关系结果不用括号括起来错
PS：单独只有with语句发生错误，因为这是一个临时视图，如with max_balance(value) as (select max(balance) from account);

Find all accounts with the maximum balance

with max_balance(value) as
	(select max(balance)
	from account)
select account_number
from account, max_balance
where account.balance = max_balance.value;

Find all branches where the total account deposit is greater than the average of the total account deposits at all branches.

with
branch_total(branch_name,value) as
	(select branch_name,sum(balance)
	from account
	group by branch_name),
branch_total_avg(value) as
	(select avg(value)
	from branch_total)
select branch_name
from branch_total, branch_total_avg
where branch_total.value >= branch_total_avg.value;