数据库管理系统笔记

发表于 分类于

安装PostgreSQL（macOS）

直接homebrew安装即可（brew install postgresql）

安装pgAdmin 4

Download

版本V5.7 V5.7

(postgresql start后)打开pgAdmin 4后在servers中新建server(myServer, localhost, username), databases中新建用户名同名database。

本地数据库服务器运行和关闭

General:

启动：pg_ctl -D /[PATH_TO_POSTGRESQL]/postgres -l /[PATH_TO_POSTGRESQL]/postgres/server.log start

停止：pg_ctl -D /[PATH_TO_POSTGRESQL]/postgres -l /[PATH_TO_POSTGRESQL]/postgres/server.log stop

通过homebrew安装可以参考以下两种可能的路径，执行前务必确认postgres实际所在目录。

MBA:

pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log stop

MBP:

pg_ctl -D /opt/homebrew/var/postgres -l /opt/homebrew/var/postgres/server.log start

pg_ctl -D /opt/homebrew/var/postgres -l /opt/homebrew/var/postgres/server.log stop

/opt/homebrew/var/postgres

课堂练习

Week 2

（在schemas）导入sql表（打开query tool，将四张表拖入运行即可）

Week 3

1
2
3
4
5
6
7
8
9
10
11
select model, speed, hd from pc where price < 1000; -- 2

select model, speed as gigahertz, hd as gigabytes from pc where price < 1000; -- 3

select maker from product where type = 'printer'; -- 4

select model, ram, screen from laptop where price > 1500; -- 5

select * from printer where color = true; -- 6

select model, hd from pc where speed = 3.2 and price < 2000; -- 7

Week 4

1
2
3
select ans.drinker from likes ans, likes diff where ans.drinker=diff.drinker and ans.beer<diff.beer; -- L1-P30

select DISTINCT ans.drinker from likes ans, likes diff where ans.drinker=diff.drinker and ans.beer<diff.beer; -- L1-P30-只出现一次

集合操作：并UNION、交INTERSECT、差EXCEPT/MINUS

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select speed, maker from laptop, product where laptop.hd>=30 and product.model=laptop.model; -- 1

select pc.model, price from pc, product where pc.model=product.model and product.maker='B'; -- 2

(select DISTINCT maker from product where type='laptop')
EXCEPT 
(select DISTINCT maker from product where type='pc'); -- 3

select DISTINCT ans.hd from pc ans, pc diff where ans.hd=diff.hd and ans.model<diff.model; -- 4

select ans.model, diff.model 
from pc ans, pc diff 
where ans.speed=diff.speed and ans.ram=diff.ram and ans.model<diff.model; -- 5

select DISTINCT product.maker from product, printer where product.model=printer.model and printer.color=true; -- 6

Week 5

EXIST: 存在，对询问使用，若询问结果不为空则为真，否则为假。

IN: 属于

ANY: 集合中任意一个

ALL: 集合中所有

相关子查询：L1-P48

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
-- L1-P53
select DISTINCT maker 
from product piece 
where EXISTS (
  select * from pc PC where PC.speed >= 3.0 AND PC.model = piece.model
); -- 1-1

select DISTINCT maker 
from product piece 
where piece.model IN (
  select model from pc PC where PC.speed >= 3.0
); -- 1-2

select * 
from printer PR 
where PR.price >= ALL(
  select price from printer
); -- 2-1

select * 
from printer PR 
where NOT EXISTS (
  select price from printer PR2 where PR.price < PR2.price
); -- 2-2

select * 
from laptop LT 
where LT.speed < ANY(
  select speed from pc
); -- 3-1

select * 
from laptop LT 
where EXISTS (
  select speed from pc PC where PC.speed > LT.speed
); -- 3-2

select maker 
from product prod, printer PR 
where prod.model = PR.model and (PR.price <= ALL (
  select price from printer PR2 where PR2.color = true
)); -- 4-1

select maker 
from product prod, printer PR 
where prod.model = PR.model and PR.color = true and NOT EXISTS (
  select price from printer PR2 where PR.price > PR2.price and PR2.color = true
); -- 4-2

select DISTINCT maker 
from product PR 
where 250 <= ALL(
  select hd from pc PC where PC.model = ANY(
    select model from product PR2 where PR2.maker = PR.maker
  )
); -- 5-1

select DISTINCT maker 
from product PR 
where NOT EXISTS (
  select hd from pc PC where PC.hd < 250 and PC.model = ANY(
    select model from product PR2 where PR2.maker = PR.maker
  )
); -- 5-2

Assignment 4

用python连接数据库：本地安装psycopg2（pip3 install psycopg2-binary）。

psql命令

psql命令

psql