跳转至

SQL 语句

表操作

创建

CREATE TABLE student (
    sid INT PRIMARY KEY, 
    name VARCHAR(16),
    login VARCHAR(32) UNIQUE, 
    age SMALLINT,
    gpa FLOAT 
);

CREATE TABLE course (
    cid VARCHAR(32) PRIMARY KEY, 
    name VARCHAR(32) NOT NULL
);

CREATE TABLE enrolled (
    sid INT REFERENCES student (sid),
    cid VARCHAR(32) REFERENCES course (cid), 
    grade CHAR(1)
);

(课件:

数值形如 numeric(3, 1)

约束写在最后面,如:

primary key(ID, course_id),
foreign key(ID, ...) references student,

删除

drop table r;

修改

alter table r add A D;

(新值被赋予 null)

alter table r drop A;

基本:SFW语句

SELECT s.name
FROM enrolled AS e, student AS s
WHERE e.grade = 'A' AND e.cid = '15-721'
AND e.sid = s.sid;

AS 来换名

is null

聚合属性

几乎只能用在 select 后面,当作输出。

主要的有: AVG(COL), MIN(COL), MAX(COL) , COUNT(COL)

select 里面非聚合的属性必须在 GROUP BY 语句中出现。

Example

SELECT AVG(s.gpa), e.cid
FROM enrolled AS e, student AS s
WHERE e.sid = s.sid
GROUP BY e.cid;

HAVING 语句可以根据聚合属性进一步筛选。

Example

SELECT AVG(s.gpa), e.cid
FROM enrolled AS e, student AS s
WHERE e.sid = s.sid
GROUP BY e.cid
HAVING AVG(s.gpa) > 3.9;

DISTINCT 可以去重。

Example

例子待补。

输出控制

ORDER BY 语句可以把输出排序。 DESC 是降序,ASC 是升序(默认)。

Example

ORDER BY a DESC, b ASC

LIMIT 语句控制输出几个。如果不排序结果是随机的。可以用 OFFSET 语句控制偏移(没有逗号分割)。

嵌套查询

几乎在哪里都可以嵌套。

加个括号就完事()

如果是 WHERE 语句内,会用 IN/ALL/ANY 来串联。

IN 等价于 =ANY

EXISTS ...

> SOME( subquery)

Example

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

Window Functions

SELECT *, ROW_NUMBER() OVER (ORDER BY cid) 
FROM enrolled ORDER BY cid;
SELECT cid, sid, ROW_NUMBER() OVER (PARTITION BY cid) 
FROM enrolled ORDER BY cid;

Common Table Expression(CTE)

WITH ... AS... 定义表格,简化 SELECT 的查询

with cte1 (col1, col2) as (SELECT 1,2), cte2(col3) as (SELECT 1)
SELECT * from cte1, cte2;

用上 Recursive 甚至可以递归,成了图灵完备的语言...

Example

WITH RECURSIVE cte(counter) AS (
    (SELECT 1)
    UNION
    ( SELECT counter + 1 FROM cte WHERE counter < 10)
)
SELECT * FROM cte;

删除

delete from r
where r.A = 'b';

插入

insert into course [(course_id, ...)]
values('a', 'b', 123, 'd');

update instructor
set salary = salary * 1.03
where salary > 100000;

where 中的子查询

字符串

% 匹配任意子串(包括空)

_ 匹配任意一个字符

|| 连接两个字符串

数据表示

  • 整数: INTEGER/BIGINT/SMALLINT/TINYINT
  • 非整数/小数: FLOAT/REAL v.s. NUMERIC/DECIMAL (IEEE-754)
  • 字符串: VARCHAR/CARBINARY/TEXT/BLOB
  • 时间: TIME/DATE/TIMESTAMP

过长的数据会用单独的 overflow page 来存。

有的还可以存很大很大的文件,blob ,但是理论上就会缺少许多保护。

以上都会用指针。

元数据(Schema)

可以如此查询表头:

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE table_catalog = '<db name>'
\d student
DESCRIBE student;
.schema student