Образовательный проект «SnakeProject» Михаила Козлова

⇒ FreeBSD and Nix ⇐

CISCO

Voice(Asterisk\Cisco)

Microsoft

Powershell

Python

SQL\T-SQL

1С

Общая

WEB Разработка

ORACLE SQL \ JAVA

Мото

Стрельба, пневматика, оружие

Саморазвитие и психология

PostgreSQL базовые SQL запросы, установка, бэкапы

Установка Postgres в Centos 7 x64

[root@x ~]# yum list postgres*

[root@x ~]# yum install postgresql-server

[root@x ~]# service postgresql start

[root@x ~]#  systemctl enable postgresql

Подключаемся и выводим справку

[root@x ~]# su - postgres

-bash-4.2$ psql

postgres=# help

postgres=# \q

-bash-4.2$ createdb base

-bash-4.2$ psql base

base=# \h

base=# \h CREATE TABLE

base=# \?

Базовые операции SQL
Лучший мануал: http://postgresql.ru.net/manual/

Создание таблиц со связями

base=# CREATE TABLE countries (
c_code char(2) PRIMARY KEY,
c_name text UNIQUE
);

Вставка данных, удаление

base=# INSERT INTO countries (c_code, c_name)
VALUES ('ru','Russia'),('us','USA'),('au','Australia'),('gb','United Kingdom'),('de','Germany');

base=# SELECT * FROM countries;

base=# DELETE FROM countries WHERE c_name = 'Germany';

base=# CREATE TABLE cities (
name text NOT NULL,
c_code char(2) REFERENCES countries (c_code),
PRIMARY KEY (c_code)
);

base=# INSERT INTO cities VALUES ('Moscow','ru');

Изменение данных

base=# UPDATE cities SET name = 'Zlatoust' WHERE name = 'Moscow';

base=# SELECT co.c_name as country_name, ci.name as city_name 
FROM cities as ci INNER JOIN countries as co
ON ci.c_code = co.c_code;

base=# SELECT co.c_name as country_name, ci.name as city_name 
FROM cities as ci, countries as co
WHERE ci.c_code = co.c_code;

Транзакции

base=# BEGIN TRANSACTION;
DELETE FROM cities;
ROLLBACK;
SELECT * FROM cities;

base=# BEGIN TRANSACTION;
UPDATE cities SET name = 'Omsk' WHERE name = 'Zlatoust';
UPDATE cities SET name = 'Kurgan' WHERE name = 'Omsk';
UPDATE cities SET name = 'Voronezh' WHERE name = 'Kurgan';
END;
SELECT * FROM cities;

Создим функцию "procname" в схеме "public" со входным параметром "param" типа данных integer 
RETURNS integer - тип выходных данных
$body$  - тело процедуры
BEGIN и END - блок
RETURN - возвращаемое значение
LANGUAGE 'plpgsql' - синтаксис plsql

base=# REATE OR REPLACE FUNCTION public.procname (param integer)
RETURNS integer AS
$body$ 
BEGIN
 RETURN param + 10; 
END; 
$body$ 
LANGUAGE 'plpgsql';

base=# SELECT * FROM public.procname(20);

Создадим хранимую процедуру без принимаемых параметров

base=# CREATE OR REPLACE FUNCTION public.procname_void()
returns void AS 
$body$ 
DECLARE 
   param text := 'Vladivostok'; -- локальная переменная
BEGIN
   UPDATE cities SET name = param;
END
$body$ 
LANGUAGE plpgsql;

base=# SELECT public.procname_void();
base=# SELECT * FROM cities;

Пример с триггерами
base=# CREATE TABLE operations (
name text NOT NULL,
added timestamp without time zone
);

base=# CREATE OR REPLACE FUNCTION update_cities() RETURNS TRIGGER AS $body$ 
DECLARE
    oper varchar(30);
    city varchar(30);
    nstr varchar(90);
BEGIN
    IF  TG_OP = 'INSERT' THEN
        city = NEW.name;
        oper := 'Add new city ';
        nstr := oper || city;
        INSERT INTO operations(name,added) values (nstr,NOW());
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        city = NEW.name;
        oper := 'Update city ';
        nstr := oper || city;
        INSERT INTO operations(name,added) values (nstr,NOW());
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        city = OLD.name;
        oper := 'Remove city ';
        nstr := oper || city;
        INSERT INTO operations(name,added) values (nstr,NOW());
        RETURN OLD;
    END IF;
END;
$body$  LANGUAGE plpgsql;

base=# CREATE TRIGGER t_trigger
AFTER INSERT OR UPDATE OR DELETE ON cities FOR EACH ROW 
EXECUTE PROCEDURE update_cities();

base=# UPDATE cities SET name = 'Karaganda';
base=# SELECT * FROM operations;

Представление
base=# CREATE VIEW sel_city AS
SELECT co.c_name as country_name, ci.name as city_name 
FROM cities as ci INNER JOIN countries as co
ON ci.c_code = co.c_code;

base=# SELECT * FROM sel_city;

base=# SELECT * FROM sel_city WHERE city_name = 'Karaganda';

Выгрузка базы:
1) -bash-4.2$ pg_dump base > base.sql
2) -bash-4.2$ pg_dump -Fc base > base.dump

Восстановление базы:
-bash-4.2$ dropdb base
-bash-4.2$ createdb base
1) -bash-4.2$ psql -d base -f base.sql
2) -bash-4.2$ pg_restore -C -d base base.dump

Комментарии пользователей