CMU-DATABASE-02-高级SQL

高级SQL

• 聚合函数

• 字符串操作

• output control

• 嵌套查询

  • 集合的比较,some,all的应用

  • 空关系测试,exists,not exists

  • 重复元组存在性测试,unique的使用

  • with子句

  • from子句中的子查询

  • 标量子查询

我们的目标是站在一个高级层面去告诉数据库系统计算我们想要的答案,而不是告诉它具体怎么去做。以对数据进行排序为例来思考这个问题:如果我们必须要告诉数据库系统具体该怎么做的话,我们就必须提供给它具体的内容,例如提供给DBMS冒泡排序算法。但如果我们用的是高级语言或者声明式语言(例如SQL),当需要对数据进行排序时,作为程序员并不介意实际上 是如何完成这个排序的。这样我们就可以腾出时间来找到最佳优化方案,让数据库根据你想要的方式来对数据进行查询,这其实就是查询优化。
查询优化:重新指定SQL执行计划并生成查询,并且查询优化器可以通过不同的策略来进行优化。
关系语言:

• Data Manipulation Language,DML,就是用delete,update,insert,select来操作数据
• Data Definition Language,DDL,定义schemas来创建表存储数据的方式
• Data Control Language,DCL,关于安全性授权,用来控制用户权限,例如读取哪些数据

重点:SQL是基于bags而不是sets和list,list是有序 集合且可以有重复元素,set集合是无序的,但不能有重复数据,若key重复会覆盖,bag中既没有固定位置也没有顺序,但允许元素重复。因此如果想让我们的元素有序,或者想保证没有重复元素出现,那么本质上来说,数据库必须为我们做一些额外工作才能提供这种功能。所以当我们需要排序和删除重复元素时需要指明。

聚合函数

• AVG(col)
• MIN(col)
• MAX(col)
• SUM(col)
• COUNT(col)

select count(login) as cnt from student where login like '%@cs'
select count(*) as cnt from student where login like '%@cs'
select count(1) as cnt from student where login like '%@cs'
select avg(gpa), count(sid) from student where login like '%@cs'
select count(distinct login) from student where login like '%@cs'

看一个错误的例子

error case:
select avg(s.gpa), e.cid from enrolled as e, student as s where e.sid = s.sid

错误的原因就是这个cid可能会重复,如果是低版本的Mysql因为是不严格的模式也许不会报错,但是结果是错的,因为它是随便出一个cid,正确的写法如下所示,用group by分组一下就可以了

true case:
select avg(s.gpa), e.cid from enrolled as e, student as s where e.sid = s.sid group by e.cid

再看一个错误的例子:

error case:
select avg(s.gpa) as avg_gpa, e.cid from enrolled as e, student as s where e.sid = s.sid and avg_gpa>3.9 group by e.cid

聚合函数是分组过后的结果,所以不能直接操作其结果,应该使用Having在分组后再进行过滤:

true case:
select avg(s.gpa) as avg_gpa, e.cid from enrolled as e, student as s where e.sid = s.sid group by e.cid Having avg_gpa > 3.9

字符串操作

like被用于字符串匹配:’%’:匹配任意的子串;’_'仅匹配一个字符串,例如:

select * from enrolled as e where e.cid like '15-%'
select * from student as s where s.login like '%c_'

不同的SQL标准是有介绍不同的函数的,但是具体的数据库实现其实各有不同,他们并不是完全按照标准来进行实现,所以不同的数据库函数所有不同,接下来介绍一些SQL-92标准定义的字符串函数:

SELECT SUBSTRING(name, 0.5) as abbrv_name from student where sid = 53688
select * from student as s where UPPER(e.name) like 'kan%'

接下来是字符串拼接,虽然标准是||就是指拼接,但是MYSQL就是不支持这种写法要用concat来连接字符串

SELECT name FROM student WHERE login = LOWER(name) || '@cs'
SELECT name FROM student WHERE login = LOWER(name) + '@cs'
SELECT name FROM student WHERE login = CONCAT(LOWER(name), '@cs')

output control

• order by <column*> [ASC|DESC],按照列的顺序,正序或逆序
• LIMIT [offset],限制返回的数据量

SELECT sid, name FROM student WHERE login LIKE '%@cs' LIMIT 10
SELECT sid, name FROM student WHERE login LIKE '%@cs' LIMIT 20 OFFSET 10

嵌套查询

SQL提供嵌套子查询机制。子查询是嵌套在另一个查询中的select-from-where表达式。子查询嵌套在where子句中,通常用于对集合的成员资格、集合的比较以及集合的基数进行检查。

SELECT name FROM student
WHERE sid IN (
SELECT sid FROM enrolled
WHERE cid = '15-445'
)

还可以使用not in

SELECT distinct name from instructor where name not in ('Mozart', 'Einstein')

集合的比较

考虑"找出满足下面条件的所有教师的姓名,他们的工资至少比Biology系某一个教师的工资要高",SQL可以写为:

select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Biology'

SQL提供了另一种更贴切的书写,短语"至少比某一个要大"在SQL中用>some表示:

select name
from instructor 
where salary > some(select salary from instructor where dept_name = 'Biology')

SQL也允许<some, <=some, >= some, =some, <>some的比较,=some是相当于in的,然而<>some并不等价于not in。

结构>all对应词组"比所有都大",对应SQL：

select name
from instructor 
where salary > all(select salary from instructor where dept_name = 'Biology')

类似some,SQL也允许<all, <=all, >=all, =all和<>all的比较。<>all等价于not in,但=all并不等价于in

考虑找出平均工资最高的系

SELECT dept_name
from instructor
group by dept_name
having avg(salary) >= all(select avg(salary) from instructor group by dept_name);

空关系测试

SQL还有一个特性可测试一个子查询的结果中是否存在元组。exist结构在作为参数的子查询非空时返回true值。使用exists结构,我们还能用另一种方法书写查询2009年秋季和2010年春季同时开课的所有课程。

SELECT course_id
from section as S
where semester = 'Fall' and year=2009 and exists(select * from section as T
where semester = 'Spring' and year = 2010 and S.course_id = T.course_id);

请注意外层的S是可以在内层被使用的,这被称为相关子查询。显然有exists自然有not exits

SELECT S.ID, S.name
from student as S
where not exists((
				select course_id
				from course
				where dept_name = 'Biology')
				except 
				(select T.course_id
				from token as T
				where S.ID = T.ID
))

重复元组存在性测试

SQL提供一个布尔函数,用于测试在一个子查询的结果中是否存在重复元组。如果没有存在重复元组则unique将返回true值。

SELET T.course_id
from course as T
where unique(select R.course_id
						from section as R
						where T.course_id = R.course_id and 
						R.year = 2009)

经过上面的描述必然有not unique,不唯一就说是至少两次!

SELET T.course_id
from course as T
where not unique(select R.course_id
						from section as R
						where T.course_id = R.course_id and 
						R.year = 2009)

with子句

with子句提供定义临时关系的方法,这个定义只对包含with子句的查询有效。

with max_budget as 
(select max(budget)
from department)
select budget
from department,max_budget
where department.budget = max_budget.value

from子句中的子查询

SQL允许在from子句中使用子查询表达式。其实就是from后面的查询语句查询出的数据可以看成一张虚拟的表

select max(tot_salary)
from(select dept_name, sum(salary)
from instructor group by dept_name) as dept_total(dept_name, tot_salary)

标量子查询

SQL允许子查询出现在返回单个值的表达式能够出现的任何地方,只要该子查询只返回包含单个属性的单个元组;这样的子查询称为标量子查询(scalary subquery)

select dept_name,
	(select count(*)
		from instructor
		where department.dept_name=instructor.dept_name)
		as num_instructors
	from department