格式说明
$ command line # shell 命令行 > command line -- postgresql 命令行 (dbname)# command line -- postgresql 命令行，已经进入 dbname 数据库 > -- 注释 > /* 多行注释 */ dbname 数据库名 tbname 表名
Database
create
$ createdb dbname
> CREATE DATABASE dbname;drop
$ dropdb dbname
> DROP DATABSE dbname;connect
$ psql
> \c dbname
(dbname)#$ psql dbname
(dbname)#Table
反斜线
\d -- 列出所有表
\d dbname -- 列出指定表的所有列信息create
CREATE TABLE company
( id INT PRIMARY KEY NOT NULL
, name TEXT NOT NULL
, age INT NOT NULL
, address VARCHAR(50)
, salary REAL
);
CREATE TABLE department
( id INT PRIMARY KEY NOT NULL
, dept CHAR(50) NOT NULL
, emp_id INT NOT NULL
);约束
- CHECK constraint
CREATE TABLE products
( product_no INTEGER
, name TEXT
, price NUMERIC CHECK (price>0)
-- 约束 price 必须大于 0
, discounted_price NUMERIC CHECK (discounted_price>0)
, CHECK(price > discounted_price));
-- 合并 discounted_price NUMERIC CHECK
-- (discounted_price>0 and CHECK(price > discounted_price))
INSERT INTO products VALUES(10, 'Soap', -10, 5);
-- 错误
INSERT INTO products VALUES(10, 'Soap', 10, 5);
-- 成功- UNIQUE and NOT NULL constraint
CREATE TABLE products
( product_no INTEGER UNIQUE
-- 约束 product_no 唯一
, name TEXT NOT NULL
-- 约束 name 不能为 NULL
, price NUMERIC NOT NULL CHECK(price>1));
INSERT INTO products VALUES(12, NULL, 5);
-- 错误 (NOT NULL)
INSERT INTO products VALUES(12, 'Soap', 5);
-- 成功
INSERT INTO products VALUES(12, 'Soap', 5);
-- 错误 (UNIQUE)- PRIMARY KEY and FOREIGN KEY
CREATE TABLE products
( product_no INTEGER PRIMARY KEY
-- PRIMARY KEY 包括 UNIQUE 和 NOT NULL
, name TEXT
, price NUMERIC
-- 也可以写在后面
-- , PRIMARY KEY(product_no)
-- 多字段主键
-- , PRIMARY KEY(priduct_no, name)
);
INSERT INTO products VALUES(1, 'Soap', 50), (2, 'Hello', 100);CREATE TABLE orders
( order_id INTEGER PRIMARY KEY
, address TEXT);
CREATE TABLE orders_items
( product_no INTEGER REFERENCES products(product_no)
, order_id INTEGER REFERENCES orders(order_id)
, quantity INTEGER
, PRIMARY KEY(product_no, order_id));
INSERT INTO orders VALUES(1, 'Delhi');
INSERT INTO orders VALUES(2, 'Kolkata');
INSERT INTO orders_items VALUES(1, 1, 2);
-- 成功
INSERT INTO orders_items VALUES(1, 3, 4);
-- 失败 (3 不在 products.product_no 中)
INSERT INTO orders_items VALUES(1, 2, 4);
-- 成功- RESTRICT and CASCADE
见 delete 部分
drop
DROP TABLE company;alter
ALTER TABLE company ADD gender CHAR(1);
-- 增加一列
ALTER TABLE company DROP COLUMN gender;
-- 删除一列
ALTER TABLE company ADD CONSTRAINT pri PRIMARY KEY(id);
-- 添加主键
ALTER TABLE company DROP CONSTRAINT pri;
-- 删除主键select
SELECT * FROM company;
SELECT * FROM company WHERE address='Mumbai';
SELECT name FROM company WHERE age!=34;
SELECT name AS employee_name FROM company;
SELECT max(age) FROM company;
-- max, min 最值
SELECT sum(salary) FROM company;
-- sum, avg 总和, 平均值
SELECT current_timestamp; -- 当前时间Logical Operators
SELECT * FROM company WHERE age>33 AND salary>2000;
SELECT * FROM company WHERE age>33 OR salary>2000;
SELECT * FROM company WHERE salary IS NULL;
SELECT * FROM company WHERE name LIKE 'P%';
SELECT * FROM company WHERE name LIKE '%a';
SELECT * FROM company WHERE name LIKE '%a%';
SELECT * FROM company WHERE age IN (20, 34, 40);LIMIT
SELECT * FROM company LIMIT 4;
-- 只返回前 4 条结果
SELECT * FROM company LIMIT 4 OFFSET 4;
-- 跳过前 4 条结果并返回GROUP BY
SELECT age, count(*) FROM company GROUP BY age;
-- age | count
-- -----+-------
-- 40 | 1
-- 12 | 1
-- 23 | 1
-- 20 | 1
-- 16 | 1
-- 24 | 1
-- 34 | 2
-- (7 rows)ORDER BY
SELECT * FROM company ORDER BY age;
-- 默认从小到大输出
SELECT * FROM company ORDER BY age,salary DESC;
-- age 从小到大, salary DESC 从大到小Aggregate Function
SELECT max(age) FROM company;
SELECT avg(age) FROM company;
SELECT sum(age) FROM company;
SELECT stddev(age) FROM company; -- 标准差
SELECT variance(age) FROM company; -- 方差
SELECT mode() WITHIN group(ORDER BY age) FROM company; -- 众数
SELECT array_agg(name) FROM company;
SELECT json_agg(name) FROM company;
SELECT json_object_agg(name, age) FROM company;insert
INSERT INTO company (
id, name, age, address, salary ) VALUES (
7, 'Rahul', 20, 'Kolkata', 50000 );
INSERT INTO company VALUES (
4, 'Priya', 24, 'Delhi', 30000 );
INSERT INTO company VALUES (
1, 'Sunil', 12, 'Mumbai', 30000 ), (
2, 'Heena', 16, 'Mumbai', 30000 ), (
5, 'Rashi', 34, 'Bangalore', 4000);delete
DELETE FROM company WHERE id=2;RESTRICT and CASCADE
CREATE TABLE order_items
( product_no INTEGER REFERENCES products(product_no)
ON DELETE RESTRICT
-- 约束, 如果删除 products.product_no 则检查是否被 order_items 使用
-- 如果被使用则不允许删除
, order_id INTEGER REFERENCES orders(order_id)
ON DELETE CASCADE
-- 级联, 如果删除 orders.order_id 则检查是否被 order_items 使用
-- 如果被使用则一并删除
, quantity INTEGER
, PRIMARY KEY(product_no, order_id));
INSERT INTO products VALUES(3, 'Shampoo', 200);
INSERT INTO order_items VALUES(2, 1, 2);
INSERT INTO order_items VALUES(3, 1, 1);
DELETE FROM products WHERE product_no=2;
-- 失败, productsproduct_no=2 被 (2, 1, 2) 引用
DELETE FROM products WHERE product_no=1;
-- 成功
DELETE FROM order_items WHERE order_id=1;
-- 成功， order_items 内的两条记录也被删除update
UPDATE company SET salary=salary/10 WHERE salary>10000;Data Types
Numeric
| Type | Value |
|---|---|
| smallint | 32767,2 Bytes |
| integer | 2^31-1, 4 Bytes |
| bigint | 2^63-1, 8 Bytes |
| numeric | unlimit |
| real | float, 4 Bytes |
| double | double, 8 Bytes |
| serial | 2^31-1, Auto inc. |
| bigserial | 2^63-1, Auto inc. |
Enumeration
CREATE TYPE mood as ENUM('sad', 'ok', 'happy');
-- sad is min mood, happy is max mood
CREATE TABLE person
( name text
, current_mood mood);
INSERT INTO person VALUES('Rishi', 'ok');
INSERT INTO person VALUES('Rashi', 'sad');Geometric
CREATE TABLE Geometric
( pri SERIAL PRIMARY KEY
, pt POINT
, ln LINE
, poly POLYGON
, cir CIRCLE);
-- Point
INSERT INTO Geometric(pt) VALUES('12, 34'), ('23, 78'), ('45, 67');
-- Line, 无限长的直线
-- ax+by+c = 0
-- ((x1, y1),(x2, y2))
-- (x1, y1, x2, y2)
INSERT INTO Geometric(ln) VALUES('{2,4,6}'),
('(3,5)(4,5)'),
('(5,6,7,8)');
-- Polygon, 多边形
INSERT INTO Geometric(poly) VALUES('(2,3),(4,5),(6,8),(6,8)')
-- Circle
-- (x0, y0), R
INSERT INTO Geometric(cir) VALUES('((3,4),6)');JSON
CREATE TABLE jsondata
( id INTEGER
, doc JSON);
INSERT INTO jsondata VALUES
( 1, '{ "name": "Rahui"
, "address": {"lane": "Lajpat Nagar 1"}}');
-- 获取 json 的一部分
SELECT doc->>'address' FROM jsondata;
SELECT doc->'address'->>'lane' FROM jsondata;
-- 等价 SELECT doc#>>'{address,lane}' FROM jsondata;
-- --> 表示强制作为 text 类型提取Array
CREATE TABLE sal_emp
( name text
, pay integer[]
, schedule text[][]);
INSERT INTO sal_emp VALUES('Rahul',
'{10000, 10000, 10000, 10000}',
'{ {"meeting", "lunch"},
{"training", "presentation"} }');
-- 除了最外层，维数要保持相同
INSERT INTO sal_emp VALUES('Rishi',
'{10000, 25000, 60000, 10000}',
'{ {"meeting", "dinner"},
{"walk", "presentation"} }');
SELECT name,pay[2] FROM sal_emp;
SELECT array_dims(schedule) FROM sal_emp;- 修改数组
UPDATE sal_emp SET pay='{25000, 25000, 25000, 25000}' WHERE name='Rahul';
UPDATE sal_emp SET pay[3]=20000 WHERE name='Rahul';
UPDATE sal_emp SET pay[1:2]='{20000, 20000} WHERE name='Rahul';
UPDATE sal_emp SET pay = array_append(pay, 20000);
UPDATE sal_emp SET pay = array_cat(pay, [20000, 15000]);
UPDATE sal_emp SET pay = array_remove(pay, 20000);ANY and ALL
SELECT * FROM sal_emp WHERE 10000 = ANY(pay);
SELECT * FROM sal_emp WHERE 25000 = ALL(pay);Arithmetic
SELECT 1+2;
SELECT 3*3;
SELECT 4^2; -- 乘方
SELECT 5%3;
SELECT 5!; -- 阶乘
SELECT |/25; -- 开方
SELECT ||/27; -- 开三次方Mathematical Functions
SELECT abs(-15.6);
SELECT cbrt(27.0); -- 开三次方
SELECT ceil(45.7); -- 向上取整
SELECT floor(45.7);
SELECT round(42.6);
SELECT round(42.623, 2);-- 指定保留位数
SELECT degrees(1); -- X * 180 / pi()
SELECT ln(2.0); -- log_e
SELECT log(100.0); -- log_10
SELECT log(2, 64.0); -- log_X
SELECT pi();
SELECT sign(-1); -- 取符号(1, 0, -1)
SELECT random();
-- sin, cos, asin, acos, ...String Functions
SELECT 'sirat' || 'ori';
SELECT char_length('Hello'); -- 汉字算一个长度
SELECT length('Hello'); -- 汉字按照字节
SELECT lower('HELLO');
SELECT upper('hello');
SELECT substr('hello', 2, 4); -- ello, 起始位置, 长度
SELECT position('an' in 'thanks');
SELECT ascii('x');
SELECT concat('abcde', 2, NULL, 5);
SELECT initcap('hi hello');
SELECT repeat('Hello', 2);
SELECT reverse('Hello');