[*] 数据库核心概念

1. 介绍

数据库中的数据

2. 关系代数

3. SQL

Structured Query Language

关系语言（Relational Laguages）

用户只需指定想要什么答案，而不是怎样去计算。

DBMS的责任是高效的评估查询。

• 查询优化器：操作重排序和生成查询计划。

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

还包括：

• 视图定义
• 完整性和约束
• 事务

重点：SQL是基于bag(duplicates)而不是set(no duplicates)。

案例数据库：

create table student(
  sid int(10),
  name varchar(20),
  login varchar(30),
  age int(3),
  gpa float(2,1),
  primary key (sid)
);

create table course(
  cid varchar(10),
  name varchar(50),
  primary key (cid)
);

create table enrolled(
  sid int(10),
  cid varchar(10),
  grade char(1),
  primary key (sid, cid)
);

插入数据：

insert into student values (53666, 'Kanye', 'kanye@cs', 39, 4.0);
insert into student values (53688, 'Bieber', 'jbieber@cs', 22, 3.9);
insert into student values (53655, 'Tupac', 'shakur@cs', 26, 3.5);

insert into course values ('15-445', 'Database Systems');
insert into course values ('15-721', 'Advanced Database Systems');
insert into course values ('15-826', 'Data Mining');
insert into course values ('15-823', 'Advanced Topic in Databases');

insert into enrolled values (53666, '15-445', 'C');
insert into enrolled values (53688, '15-721', 'A');
insert into enrolled values (53688, '15-826', 'B');
insert into enrolled values (53655, '15-445', 'B');
insert into enrolled values (53666, '15-721', 'C');

聚合

AGGREGATES
一个背包(bag)中的元组(tuples)返回单个值的函数。

• AVG(col): 返回列的平均值
• MIN(col): 返回列的最小值
• MAX(col): 返回列的最大值
• SUM(col): 返回列的和
• COUNT(col): 返回列的值的个数

聚合函数只能用于 SELECT 的输出列表：

-- 获取带有'@cs'的login的学生的数量
-- 方式一
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';

MULTIPLE AGGREGATES

-- 获取login中带有'@cs'的学生的数量和平均GPA
SELECT AVG(gpa), COUNT(sid)
FROM student WHERE login LIKE '%@cs';

DISTINCT AGGREGATES
COUNT, SUM, AVG 支持 DISTINCT

-- 获取login带有'@cs'的不重复的学生的数量
SELECT COUNT(DISTINCT login)
FROM student WHERE login LIKE '%@cs';

GROUP BY

HAVING

过滤输出结果。
像GROUP BY中的WHERE。

错误使用方式

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    -- 此时avg_gpa尚未计算，不知道怎样做这个判断
GROUP BY e.cid;

正确方法

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;

STRING 操作

string operations

LIKE用于字符串匹配
字符串匹配符：

• % ：匹配任何子串，包括空字符串

SELECT * FROM enrolled AS e
WHERE e.cid LIKE '15-%';

• _ ：匹配任意一个字符

SELECT * FROM student AS s
WHERE s.login LIKE '%@c_';

SQL-92定义了string函数，许多DBMS也有它们自己唯一的函数。

这些函数可以用在SQL语句中的任何有string的地方，输出和谓词语句等：

SELECT SUBSTRING(name, 0, 5) AS abbrv_name
FROM student WHERE sid = 53688;

SELECT * FROM student AS s
WHERE UPPER(e.name) LIKE 'KAN%';

字符串连接
SQL标准使用 || 操作符来连接两个和多个字符串。

string concatenate

日期/时间 操作

不同数据库之间对日期函数的支持差异较大。

-- 当前时间
SELECT NOW();

-- 当前日期
SELECT CURRENT_DATE();

-- 函数 timestamp
SELECT CURRENT_TIMESTAMP();
-- 关键字 timestamp
SELECT CURRENT_TIMESTAMP;

-- 获取当前日期 - 天
SELECT EXTRACT(DAY FROM DATA('2019-01-19'));

某个日期是一年中的第几天

-- MySQL不支持该语法
SELECT DATE('2019-01-19') - DATE('2019-01-01') AS days;

-- MySQL的方式一
SELECT ROUND(
  (UNIX_TIMESTAMP(DATE('2019-01-19')) - 
   UNIX_TIMESTAMP(DATE('2019-01-01'))) / 
   (60*60*24), 0) AS days;
-- MySQL的方式二
SELECT DATEDIFF(DATE('2019-01-19'), DATE('2019-01-01')) AS days;

4. 函数依赖

5. 范式