cmu 15445 2: crash SQL

2021-02-07

misc

第二课, 快速学习SQL…

relational languages

用户只需要说明需要什么, 而不需要说明如何计算得到他…(中文好别扭, 还是用英文).

User only needs to specify the answer that they want, not how to compute it.

DBMS负责query的高效执行, 一些high-end系统有query优化器可以重写输入query并且查找最优的执行策略.

Data Manipulation Language (DML)
Data Definition Language (DDL)
Data Control Language (DCL)

Important: SQL is based on bags (duplicates) not sets (no duplicates).

SQL history

最烦历史了

样例表, 后续操作基于这三张表

Aggregates

Aggregate functions can (almost) only be used in the SELECT output list.

Functions that return a single value from a bag of tuples:

AVG(col): 返回col的平均值;
min(col): 返回col的最小值;
max(col): 返回col的最大值;
sum(col): 返回col的和;
count(col): 返回col的个数;

例子

get login邮箱结尾是@cs的学生人数:

1 2	select count(login) as cnt from student where login like '%@cs';

根据student表可知login是@cs结尾的人数为3, 即cnt = 3;

下述三种应该都是一样的结果, 待会儿试试:

multiple aggregates: 找到login是@cs结尾的所有学生的人数以及他们的平均gpa:

1 2	select avg(gpa), count(sid) from student where login like '%cs';

distinct aggregates:
- COUNT, SUM, AVG support DISTINCT
- Get the number of unique students that have an “@cs” login.

1 2	select count(distinct login) from students where login like '%cs';

Output of other columns outside of an aggregate is undefined. 他不知道该怎么给你展示结果, 不知道怎么排列.这时候该使用group by声明按照哪一个属性来排列.

Non-aggregated values in SELECT output clause must appear in GROUP BY clause.

上图中, 为何s.name处有问题呢? 因为最后输出是按照e.cid(课程号)来排列的, avg(s.gpa) 是一个数字, 表示该门课的平均成绩, 可以正常显示. 但是s.name是选了该门课的学生的姓名, 你怎么能够在一行中显示多个姓名呢?(group by e.cid, 一个课程号就一行, 显然没法显示多个name);

可修改为(效果为止, 有空试试, 应该是笛卡尔积吧):

select avg(s.gpa), e.cid, s.name 
	from enrolled as e, student as s
	where e.sid = s.sid
	group by e.cid, s.name;

having:

string operations

单双引号

从上图, 显然我们应该取交集, 坚持使用单引号; 不知道工业实践是怎么样的…

字符串匹配

% match 一个string, string可以为空;
_ match一个char;

字符串拼接

输出重定向

输出控制

ORDER BY <column*> [ASC|DESC]

下图按照成绩字典序升序来排列(即高分排前面):

下图按照成绩字典序降序来排列, 并且遇到成绩相同的时候按照sid升序来排列, 并且project了sid:

LIMIT <count> [offset]

Limit the # of tuples returned in output;
Can set an offset to return a “range”;

上图中第一个语句是查询login以@cs结尾的学生的sid和name, 最多显示10条;

第二个语句是查询login以@cs结尾的学生的sid和name, 并且跳过前10条, 最多显示20条;

重叠查询 nested queries

比如要查询上15445这门课的学生的名字:

select name from student
  where sid in (
    select sid from enrolled 
     where cid = '15-445'
  );

重叠方式:

all : must satisfy expression for all rows int the sub-query;
any: must satisfy expression for at least one row in the sub-query;
in: equivalent to ‘=any()’;
exists: at least one row is returned;

查找至少选一门课的学生中sid最大的学生的sid和name:

select sid, name from student
  where sid in (
  	select max(sid) from enrolled
  );

or:

select sid, name from student 
	where sid in (
      select sid from enrolled 
        order by sid desc limit 1
    );

查找所有没人选的课:

select * from course 
  where not exists (
    select * from enrolled 
      where course.cid = enrolled.cid
  );

window function

查找每一门课成绩第二高的学生:

select * from (
  select *, rank() over (paritition by cid order by grade asc) as rank
    from enrolled) as ranking 
  where ranking.rank = 2;

common table expression 懒得看了… 乏了