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