E

EdwardWong

V1

2022/08/31阅读:35主题:兰青

PostgreSQL

因为要处理实验数据了,所以先提前总结一些常见的SQL语句,以方便之后的查询。

目前对于数据库的掌握仅限于命令的使用,对于底层原理的实现及数据库优化,还在看cmu的数据库视频,希望到时候看完之后能完成数据库系统的建立。

PostgreSQL


创建数据库并连接数据库

创建数据库的命令

  1. CREATE DATABASE TEST % 创建test数据库
  2. psql -h localhost -p 5432 -u username(用户名) test(数据库名称) 或者 psql进入psql,然后\c test (username) % 创建数据库并连接

创建表

创建表格的命令格式: CREATE TABLE table_name(column_name +data_type + constrains if any);

% 不带外键的表
CREATE TABLE person(
id BIGSERIES NOT NULL 
PRIMARY KEY  ,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
gender VARCHART(6) NOT NULL,
date_of_birth TIMESTAMP  NOT NULL    % 最后一个不需要逗号
);                   
% 创建带外键的表
CREATE TABLE person(
  id BIGSERIAL NOT NULL PRIMARY KEY, 
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  gender VARCHAR(7) NOT NULL,
  date_of_birth DATE NOT NULL,
  car_id BIGINT REFERENCES car(id),       % 指明了car(id)是外键
  UNIQUE(car_id)
);

CREATE TABLE car(
  id BIGSERIES NOT NULL PRIMARY KEY,    % car的id是外键
  make VARCHAR(100) NOT NULL,
  model VARCHAR(100) NOT NULL,
  price NUMERIC(19,2) NOT NULL
);

postgresql常用的datatype列于下面:

  • 数值类型
  • 字符类型
  • 日期/时间类型
  • 布尔类型

还有很多其他数据类型,可以参考 菜鸟驿站


向数据表中加入数据

INSERT INTO person(
first_name,
last_name,
gender,
date_of_birth

VALUES('Anne','Smith','FEMALE', DATE'1988-01-09');


创建schema

schema的用途是相同的对象名称可以被用于不同的模式中而不会出现冲突,例如schema 1myschema都可以包含mytable的表。

  1. CREATE SCHEMA myschema; 创建schema
  2. CREATE SCHEMA schame_name AUTHORIZATION username; % % 将schema_name赋予用户
  3. SELECT * FROM information_schema.schemata; % 查看所有的schema;

从一个数据库复制数据到另一个数据库

  1. INSERT INTO over_test_copy1 SELECT * FROM over_test;
  2. WITH t as (SELECT * FORM over_test) INSERT INTO over_test_copy1 SELECT * FROM over_test;

  1. DROP DATABASE TEST % 删除数据库
  2. DELETE FROM person where id =1 AND country='China'; %删除id=1的记录
  3. DELECT FROM person; % 删除整个person表
  4. DROP TABLE IF EXIST customers; % 如果存在customers表时则删除
  5. UPDATE person SET car_id=NULL WHERE id=1; DELECT FROM car WHERE id=13; % car 是一张外键表,person是主表,此条命令可以删除带有记录的
  6. DROP SCHEMA myschema; %删除一个为空的模式
  7. DROP SCHEMA myschema CASCADE; % 删除一个模式以及其中包含的所有对象


删除/增加约束(ALTER TABLE ADD/DROP CONSTRAIN

  1. ALTER TABLE person DROP CONSTRAINT person_key; % 丢弃主键
  2. ALTER TABLE person ADD PRIMARY KEY(id); % 把id作为主键
  3. ALTER TABLE person ADD CONSTRAIN unique_email_address UNIQUE(email) % 列中的数据要唯一

也可以使用ALTER TABLE person ADD UNIQUE(email);, 这条语句会自动定义constrain的name,而不需要人为的指定。

  1. ALTER TABLE person DROP CONSTRAIN unique_email_address; %删除unique的constrain
  2. ALTER TABLE price ADD CONSTRAIN price_discount_check CHECK(price>0 AND discount >0 AND price >discount); % 使用CHECK关键字增加大于小于约束。

此条可以增加table的约束,当然也可以在创建表的时候直接赋予约束,例如

CREATE TABLE price_list(
id serial PRIMARY key,
product_id INT NOT NULL,
price NUMERIC NOT NULL CHECK(price>0 AND price > discount),
discount NUMERIC NOT NULL CHECK(price>0),
valid_from DATE NOT NULL ,
valid_to DATE NOT NULL CHECK(valid_to > valid_from)
);
  1. ALTER TABLE table_name ADD column_name datatype; % 添加列
  2. ALTER TABLE table_name DROP column_name; % 删除列
  3. ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype; % 修改某列的数据类型
  4. ALTER TABLE table_name ALTER column_name datatype NOT NULL; % 给某列设置非空属性
  5. ALTER TABLE table_name1 RENAME TO table_name2; %重命名一个表
  6. ALTER TABLE RENAME COLUMN column_name1 TO column_name2 % 重命名一个字段
  7. ALTER TABLE ALTER COLUMN SET DEFAULT 默认值; % 给一个字段设置默认值
  8. ALTER TABLE ALTER COLUMN DROP DEFAULT; % 去除缺省值
  9. ALTER TABLE test1 OWNER TO username; % 添加用户对test1表的所属关系
  10. ALTER DATABASE postgres RENAME TO experiment; % 将数据库改名

更新表中的数据

  1. UPDATE person SET email='ommar@gmail.com' %更新email列的数据为ommar@gmail.com
  2. UPDATE person SET email='ommar@gmail.com' WHERE id=2021 % 更新id=2021的人的邮箱
  3. UPDATE person SET email='ommar@gmail.com', first_name='omor', last_name='Montana' WHERE id=2011; % 更新多列的信息
  4. INSERT INTO table_name(column_list) VALUES(value_list) ON CONFLICT constrain_name action 或者 INSERT INTO table_name(column_list) VALUES(value_list) ON CONFLICT(字段名) action

该条命令是postgresql中的upsert特性,即如果记录存在更新数据,记录不存在时插入新的数据。 比较常见的action有DO NOTHINGDO UPDATE SET column1=value_1 WHERE ...两种。 以下是关于upsert的一个例子:

CREATE TABLE customers(
   customer_id serial PRIMARY_KEY,
   name VARCHAR UNIQUE,
   email VARCHAR NOT NULL,
   active bool NOT NULL DEFAULT TRUE
);
INSERT INTO 
    customers(name,email)
VALUES
    ('IBMS','contact@ibm.com'),
    ('Microsoft','contact@microsoft.com'),
    ('Intel','contact@intel.com');

INSERT INTO
    customers (name,email)
VLAUES
    ('Microsoft','hotline@microsoft.com')
ON CONFLICT ON CONSTRAIN constrains_name_key
DO NOTHING                        % 如果冲突了的话,就什么也不做

INSERT INTO 
    customers(name,email)
VALUES 
    ('Microsoft','hotline@microsoft.com')
ON CONFLICT ON CONSTRAIN(name)     % 可以换成name,不一定使用constarin名称
DO UPDATE
  SET email= EXCLUDED.email || ';' || customers.eamil  % 不太明白这个地方的含义
  1. UPDATE person SET car_id=2 where id =1 % 修改外键

多个表的操作(INNER JOINT/CROSS JOINT/LEFT OUTER JOINT/RIGHT OUTER JOINT/FULL OUTER JOINT)

  1. SELECT * FROM person (INNER)JOIN car on person.car_id=car.id; % 根据外键将两个表合并
  2. SELECT * FROM person LEFT(OUTER) JOIN car on car.id=person.car_id;
  3. SELECT * FROM person RIGHT(OUTER) JOIN car on car.id=person.car_id;
  4. SELECT * FROM person FULL (OUTER) JOIN car on car.id=person.car_id;

查找的基本格式 SELECT * FROM TABLE WHERE key='value'

  1. SELECT * FROM;
  2. SELECT first_name, last_name FROM person;
  3. SELECT * FROM person ORDER BY country_of_birth;(排序)

对于排序而言,默认的是ASC,如果是降序排序的话,需要使用DESC, 例如SELECT * FROM person ORDER BY country DESC

  1. SELECT DISTINCT country_of_birth ORDER BY country % 去除重复的数据, 先对country排序然后将筛选出来的country_of_birth 进行去重处理
  2. SELECT * FROM person WHERE gender='MALE' AND (country_of_birth='Poland' OR country_of_birth='China')

可以简化为 SELECT * FROM person where country_of_birth IN ('China', Poland);

  1. SELECT 1=1; or SELECT 1<>2; or SELECT 'AMIGOSCODE' <> 'amigoscode'; or SELECT 60|13; or SELECT 60 & 13; or SELECT (~60); or SELECT (60<<2) % 逻辑运算及位运算
  2. SELECT * FROM person LIMIT 10; % 只能取10条记录
  3. SELECT * FROM person OFFSET 5 LIMIT 5; % 取6-10之间的记录
  4. SELECT * FROM person WHERE data_of_birth BETWEEN DATR'2000-01-01' AND '2015-01-01'; % 选取时间位于一个区间的数据
  5. SELECT * FROM person WHERE email LIKE '%.com'; % %代表可以多个任意字母
  6. SELECT * FROM person WHERE email LIKE '----h@%.com' % -是只能一个任意字母
  7. SELECT country_of_birth,count(*) FROM person GROUP BY country_of_birth ORDER BY country_of_birth; %首先按照country_of_birth进行分组,然后用聚合函数记录每个分组有多少个记录,然后按照country_of_birth进行排序。
  8. SELECT country_of_birth, count(*) FROM person GROUP BY country_of_birth HAVING count(*)>5 ORDER BY country_of_birth;

主要HAVING子句必须放在GROUP BY子句后面,在ORDER BY子句前面。HAVING的作用主要是让我们可以对筛选分组GROUP BY 的数据设置条件,这与WHERE不同, WHERE是对所选列设置条件。

  1. SELECT model, max(price)/min(price)/avg(price)/round(avg(price))/sum(price) FROM car ORDER BY model; %可以在SELECT后使用聚合函数
  2. SELECT 10+/-/*//(加减乘除)2 % 可以使用select进行加减乘除运算
  3. SELECT id,make,model,price as original_price,ROUND(price*.10,2) AS ten_percent, ROUND(price-price*.10,2) AS discount_after_10_percent FROM cars % 打折之后的汽车价格
  4. SELECT COALESCE(expression_1, expression_2, expression_3,....expression_n)

SELECT COALESCE 依次参考各参数表达式,遇到非null值即可停止并返回该值,如果所有的表达式都是空值,最终将返回一个空值。

  1. SELECT NULLIF(expression_1, expression_2)

SELECT NULLIF命令会比较两个表达式,如果不相等,则返回第一个表达式的值,如果相等,则返回null

  1. SELECT * FROM company WHERE age IS (NOT) NULL % 找出age中不为(为)0的数据

  2. EXISTS 谓词经常使用在sql语句中,功能十分强大,下面将详细介绍这个谓词。

EXIST用于检测子查询的结果集是否包含目标值,如果结果集中至少包含一条目标值,则EXISTS的结果为true,否则为false。

  • 对于上述这样一个数据表,如果想要查询某个同学的成绩全部大于60分以上的,我们可以找出是否存在不满足大于60分的学生集合,然后通过NOT EXIST筛选出所需目标值。
SELECT DISTINCT student_name
FROM stuscores t1
WHERE NOT EXISTS
  (SELECT *
  FROM stuscore t2
  WHERE(t1.student_name=t2.student_name AND t2.score<60));
  • 如果想要查询某个学生的姓名,其中数学大于90分,语文大于70分的学生。
实现方式一
SELECT * FROM
(SELECT * FROM stuscores WHERE subjects='数学' AND score>=90)as m1,
 (SELECT * FROM stuscores WHERE subjects='语文' AND score>=70) as m2
WHERE 
  m1.student_name=m2.student_name;
  
实现方式二:

SELECT * FROM 
  stuscores t1,
  stuscores t2
WHERE(t1.student_name=t2.student_name AND t1.subject='数学' AND t1.score >=90  AND t2.subject='语文' AND t2.score >=70);

实现方式三:
SELECT * FROM stuscore t1
WHERE student_name IN
  (SELECT student_name 
   FROM stuscore
   WHERE subject='数学' AND score >=90)
   AND subject='语文' 
   AND score >=70;
   
实现方式四
SELECT * FROM stuscores t1
WHERE NOT EXISTS
  ( SELECT *
    FROM stuscore t2
    WHERE t1.student_name=t2.student_name AND
      'true'= CASE WHEN subject ='数学' AND score <90 THEN 'true' 
      WHEN subject = '语文' AND score <70 THEN 'true'
      else 'false' END);
      
这样还存在一个缺点,只要一种数学或者语文一门课满足条件就会输出,但是要求必须数学和语文同时满足条件,所以可以加约束进行改进。

SELECT DISTINCT student_name
FROM stuscore t1
WHERE subject IN ('数学',‘语文’)
AND NOT EXISTS
  (SELECT * FROM stuscore t2
  WHERE t1.student_name = t2.student_name
  AND 'true' = CASE WHEN subject='数学' AND score<90 THEN 'true' 
  WHEN subject='语文' AND score<70 THEN 'true' ELSE 'false' END)
  GROUP BY student_name HAVING count(*) =2;
  1. CASE WHEN THEN ELSE END
CASE WHEN condition THEN result
    WHEN condition THEN result
    ELSE result
END

如果为真,那么CASE表达式的结果就是符合条件的result,如果为假,那么以相同的方式搜寻随后的WHEN子句,如果没有WHEN condition为真,那么case表达式的结果就是在ELSE子句里的值。

SELECT SUM(city_id),CASE name
WHEN '北京' THEN '古都'
WHEN ‘西安’ THEN ‘古都’
WHEN '上海' THEN '魔都'
WHEN 'NewYork' THEN '纽约'
ELSE '其他城市' END
GROUP BY
CASE name
WHEN '北京' THEN ‘古都’
WHEN '西安' THEN '古都'
WHEN '上海' THEN '魔都'
WHEN 'NewYork' THEN '纽约'
ELSE '其他城市' END

为了在GROUP BY 中使用CASE, 查询语句需要在GROUP BY 块中重复SELECT块中的CASE块 为了在ORDER BY块中使用CASE,查询语句需要在ORDER BY块中重复SELECT块中的CASE块 如果要比较的数据是数字,则在case后不能写字段名,如果比较的数据为字符,则要在case后写上字段名

  1. SELECT * FROM company WHERE age::text LIKE '2%' % 找出age中以2开头的数据。
  2. WITH 查询子句

WITH提供了一种方式来书写在一个大型查询中使用的辅助语句,WITH子句中的每一个辅助语句可以是SELECT、INSERT、UPDATE或DELECT。

WITH  regional_sales AS(
  SELECT region,SUM(amount) AS total_sales
  FROM orders
  GROUP BY region),
  top_regions AS(
    SELECT region
    FROM regional_sales
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)  
    
% WITH 子句定义了两个辅助语句,其中regional_sales的输出用在top_regions中而top_regions的输出用在主SELECT查询
    
SELECT region, 
        product, 
        SUM(quantity) AS product_units, 
        SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region,product
% WITH 递归
WITH RECURSIVE 名字 as(
  A. 初始语句(非递归部分)
  
  union all
  
  B.递归部分语句
)[SELECT|INSERT|UPDATE|DELETE]

执行过程: 执行A的部分,其结果作为B中对result的引用,同时将这部分结果放入临时的working table中,重复执行以上步骤,直到working table为空,用working table的内容替换递归的自引用,执行B,并用该结果替换working table

WITH 递归的一个例子如下:

CREATE TABLE department(
  "id" int4 NOT NULL,
  "name" varchar(255) COLLATE "pg_catalog"."default",
  "parent_id" int4,
  CONSTRAINT "department_pkey" PRIMARY KEY("id")
);
WITH RECURSIVE tree AS(
    SELECT dep.id,dep.name,dep.parent_id FROM department dep WHERE dep.id=1
    UNION ALL
    SELECT dep.id,dep.name,dep.parent_id FROM department dep INNER JOIN tree ON dep.parent_id=tree.id
) SELECT * FROM tree;

其他常见命令

  1. \du % 列出所有的用户
  2. \l %列出所有的数据库
  3. \? %帮助文档 或 psql --help(linux 操作)
  4. \d % 列出当前模式下所有的数据表,默认是public
  5. \dt *.* % 列出在所有架构下的表
  6. \dt public.* % 列出在特定模式下的表
  7. \dt (public | myschema).company % 会列出public和myschema下的company表
  8. \df %列出所有的函数 \dn %列出schema
  9. \d(\d+) person % 累出person表中的属性
  10. 将查询结果输出到csv文件中,可以使用\copy 或copy 命令

\copycopy 命令是不同的。copy是从服务器导入导出文件,而\copy是从qsql客户端导入导出文件,而且\copy不需要超级用户权限,普通用户即可执行。 两个命令的基本语法基本一样:

1. COPY ... FROM
COPY table_name[(column_name)]
FROM {'filename'|PROGRAM 'command'|STDIN}
[[WITH](option[.,...])]

2. COPY ...TO
COPY {table_name[(column_name)]|(query)}
TO {'filename'|PROGRAM 'command'|STDIN}
[[WITH](option[.,...])]

options:
FORMAT format_name  (文本/csv/二进制格式)
DELIMITER 'delimiter_character'
NULL 'null_string'
HEADER [boolean]
ENCODING 'encoding_name'

以下是一些copy\copy的实例:

COPY my_table FROM '/path/to/directory/test.csv' DELIMITER ',' CSV HEADER;

COPY customers TO PROGRAM 'gzip > /data/test_data.copy.gz';

COPY customers(col_1,col_2) TO '/data/test_data.copy' DELIMITER '';

COPY customer to 'path/to/file' (with) binary;

COPY customers to 'path/to/file' (with) csv;
\copy(SELECT * FROM person LEFT JOIN car on car.id=person.car_id) TO '/path/to/directory/results.csv' DELIMITER ',' csv HEADER;
  1. 使用uuid
  • CREATE EXTENSION IF NOT EXISTS 'uuid-ossp'; % 如果没有uuid,需要先安装extension
  • SELECT uuid_generate_V4();
CREATE TABLE person(
person_uid UUID NOT NULL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
date_of_birth DATE NOT NULL,
car_uid UUID REFERENCES car(car_uid),
UNIQUE(car_id),
UNIQUE(email)
);

CREATE TABLE car(
car_uid UUID NOT NULL PRIMARY KEY,
make VARCHAR(100) NOT NULL,
model VARCHAR(100) NOT NULL,
price NUMERIC(19,2) NOT NULL CHECK(price>0)
);

INSERT INTO person
  (person_uid, first_name, last_name,date_of_birth)
VALUES
  (uuid_generate_v4(),'Fernanda','Beardon','1953-10-20');
  
INSERT INTO 
  car(car_uid, make,model, price)
VALUES 
  (uuid_generate_v4(),'GMC','Acadia','17662.69');

  1. 双冒号::的含义
  • :: 在postgresql中是一个强制类型转换符,作用等同于CAST.有的时候需要将一种数据类型的值转换为另一种数据类型,那么可以使用expression::type转换。 使用强制类型转换时如果表达式不能转换为目标类型,则会触发错误。
SELECT 
'100':: INTEGER,
'01-OCT-2015'::DATE;

SELECT 
  CAST ('100' AS INTEGER)
  CAST ('01-OCT-2015' AS DATE)  

  • 双冒号:: 还可以用于两个数字相除,结果保留整数部分
SELECT round(400/300,4)       % 此时结果为1

SELECT round(400::NUMERIC/300::NUMERIC,4)    % 先转换成浮点数,相除的结果为浮点数。
  1. \timing on % 开启SQL执行时间的显示
  2. EXCLUDE 约束

EXCLUDE排他约束是用来保证如果将任何两行的指定列或表达式使用指定操作符进行比较,至少其中一个操作符比较将会返回否或空值

CREATE TABLE company7
    ID INT PRIMARY KEY NOT NULL,
    NAME TEXT,
    AGE INT,
    ADDRESS CHAR(50),
    SALARY REAL,
    EXCLUDE USING GIST
    (NAME WITH =,
    AGE WITH <>
    );                   % 如果满足NAME相同,AGE不相同则不允许插入,否则允许插入
    
插入以下两条记录将会产生冲突
INSERT INTO company7 VALUES (1, 'PAUL',32,'California',200000.00);
INSERT INTO company7 VALUES (2,'PAUL',42,'California',200000.00)
  1. TRIGGER触发器

目前对trigger函数还不是特别会写,之后遇到会补充。

% 创建TRIGGER的语法
CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW|STATEMENT EXECUTE PROCEDURE 触发器函数;

CREATE TRIGGER INSERT_TRIGGER 
AFTER INSERT ON TBL_SENTENCE
FOR EACH ROW
EXECUTE PROCEDURE SENTENCE_INSERT();

% 创建触发器函数
CREATE FUNCTION fun_name() RETURNS TRIGGER AS $fun_name$
    BEGIN
      函数执行代码/SQL语句
    END;
$fun_name$  LAUGUAGE plpgsql;

% 创建触发器的一个函数
CREATE | REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_tables$
   BEGIN
       INSERT INTO AUDIT (EMP_ID,ENTRY_DATE) VALUES(new.ID, current_timestamp);
       RETURN NEW;
  END;
$example_table$ LAUGUAGE plpgsql;
  1. SELECT * FROM pg_trigger; % 列出所有的trigger

  2. SELECT tgname FROM pg_trigger,pg_class WHERE tgrelid=pg_class.oid AND relname='company'; % 列出company表的触发器

  3. DROP TRIGGER ${trigger_name} on ${table_of_trigger_dependent}; % 删除触发器

  4. DROP TRIGGER example_trigger(触发器名称) ON company; % 删除表company的example_trigger 触发器

  5. 权限管理 无论何时创建数据库对象,都会为其分配一个所有者,所有者通常是执行create语句的人。 对于大多数类型的对象,初始状态是只有所有者(或超级用户)才能修改或删除对象,要允许其他用户使用它,必须为该用户设置权限。

基本语法

GRANT privileges[,...]
ON object [,...]
TO {PUBLIC | GROUP groupname | username}
REVOKE privileges[,...]
ON object [,...]
FROM {PUBLIC | GROUP groupname | username}
  • object: 授予访问权限的对象名称,可能的对象是table/view/sequence
  • PUBLIC: 代表所有用户
  • GROUP: 为用户组设置权限
  • username: 要授予权限的用户名
  1. CREATE USER username WITH PASSWORD '****' % 创建用户并设置密码
  2. ALTER ROLE username WITH privileges; % 修改用户权限
  3. GRANT ALL ON tablename TO user; % 赋予用户表的所有权限;
  4. GRANT ALL ON DATABASE dbname TO dbuser; % 赋给用户数据库的所有权限
  5. REVOKE privileges ON tablename FROM user; % 撤销用户对表的权限
  6. DROP USER usename; % 删除用户
  7. SELECT USER % 查看当前用户
  8. \du % 列出所有的用户
  9. \c username % 切换用户

数据库的备份与恢复

  1. 数据备份 可使用pg_dump --help 查看所有的相关命令:

pg_dump -Fc -h 127.0.0.1 -U username db111(database) -f db111.dump

  1. 数据恢复 在数据恢复之前需要先创建一个数据库createdb --owner username db222,接着进行存储操作pg_restore -d db222 -h localhost -U username db111.dump.

分类:

后端

标签:

数据库

作者介绍

E
EdwardWong
V1