The OpenNET Project / Index page

[ новости /+++ | форум | теги | ]

Использование PostgreSQL (database postgresql sql)


<< Предыдущая ИНДЕКС Исправить src / Печать Следующая >>
Ключевые слова: database, postgresql, sql,  (найти похожие документы)
From: Олег Бартунов <http://www.sai.msu.su/~megera/>; Date: Mon, 16 Dec 2007 14:31:37 +0000 (UTC) Subject: Использование PostgreSQL Оригинал: http://www.sai.msu.su/~megera/oddmuse/index.cgi/pgsql_tutorial Предполагается знание основ реляционных баз данных и наличие навыков работы с ними. Лекция рассчитана на 4 занятия по 45 минут. Интерфейсы PostgreSQL * Основные клиентские интерфейсы - libpq - работа с базой данных из языка 'C' - libpqxx,libpq++ - C++ - DBD::Pg - perl интерфейс - psqlODBC - ODBC драйвер - pgjdbc - JDBC драйвер - Npgsql - .Net интерфейс - pgtcl,pgtclng - tcl - PyGreSQL, pygres - python - dbd_pg - ruby - psql - коммандная строка - pgbash - доступ из командной оболочки - php * Программирование на стороне сервера - процедурные языки - встроенные интерфейсы - sql,C - pl/pgsql - sql - pl/perl - perl - pl/tcl - tcl - pl/python - python - plr - R * Пример приложения на языке perl * Особенности использования кавычек в pl/pgsql Примеры использования Краткое изложение основных команд и примеры их использования. * Использование schema - Как псевдо-БД (аналогия с файловой системой, где схема - это директория) - Для программистов это возможность тестирования своих приложений, для этого надо подумать о схемах как о юниксовом PATH, когда сво девелоперская директория ~bin ставится перед системной /usr/bin - Если несколько приложений используют одни и те же объекты в одной БД, то логично выделить их в отдельную схему. * Создание таблиц, индексов, первичные и внешние ключи, ограничения - with/without OID Умолчание задается в postgresql.conf ( #default_with_oids = true ). OID дают возможность найти последнюю вставленную строчку test=# create table with_oid (a integer) with oids; CREATE TABLE test=# insert into with_oid values(1); INSERT 6350719 1 test=# insert into with_oid values(2); INSERT 6350720 1 test=# select a from with_oid where oid=(select max(oid) from with_oid); a --- 2 (1 row) Примечание: "name" != "Name" Двойные кавычки используются для названий таблиц, колонок. test=# select count(temp."X") from temp; ERROR: column temp.X does not exist test=# select count(temp.X) from temp; count ------- 9 Одинарные кавчычки используются только для строк ! test=# select count(temp.'X') from temp; ERROR: parse error at or near "'X'" at character 19 LINE 1: select count(temp.'X') from temp; ^ Примечание: create table "reserved word" - используйте двойные кавычки test=# create table "limit" (a int); CREATE TABLE Примечание: COMMENT ON {TABLE,FUNCTION,,,,} tblname IS '....' test=# COMMENT ON TABLE temp is 'тестовая таблица'; test=# \dt+ temp List of relations Schema | Name | Type | Owner | Description --------+------+-------+--------+------------------ public | temp | table | megera | тестовая таблица test=# COMMENT ON COLUMN temp.x is 'целочисленный атрибут'; COMMENT test=# \d+ temp Table "public.temp" Column | Type | Modifiers | Description --------+---------+-----------+----------------------- x | integer | | целочисленный атрибут y | integer | | использование NULL Anything <operation> NULL returns NULL test=# select 1||NULL, 1>NULL, 1+NULL,1~NULL; ?column? | ?column? | ?column? | ?column? ----------+----------+----------+---------- | | | * select (null=null) is null; см.transform_null_equals in postgresql.conf (false) test=# set transform_null_equals to on; SET test=# select NULL=NULL; ?column? ---------- t (1 row) test=# set transform_null_equals to off; SET test=# select NULL=NULL; ?column? ---------- (1 row) * coalesce(ARG1,VAL1,VAL2,...) - задание значений для NULL-ed аргументов test=# select coalesce(NULL,'значение по умолчанию'); coalesce ----------------------- значение по умолчанию * nullif, IS NOT NULL, IS NULL, * count(*) vs count(some_field_which_could_ne_NULL)) * NULL in subselects (надо следить, чтобы результат subselect не содержал NULL, пример: WHERE co_id NOT IN (SELECT dy_company FROM diary) WHERE co_id NOT IN (1, 2, null, 3...) WHERE NOT (co_id=1 OR co_id=2 OR co_id=null OR co_id=3...) WHERE NOT (null) WHERE null Сделать NULL при сортировке впереди select coll from mytable order by col IS NULL DESC, COL; Виртуальные таблицы (представления) Пример: Показ I/O статистики (stats_* в postgresql.conf): create or replace view iostat as select blk.relname as relname,blk.heap_blks_read || ':' || blk.heap_blks_hit as heap_blk, blk.idx_blks_read || ':' || blk.idx_blks_hit as idx_blk, tpl.seq_scan || ':' || tpl.seq_tup_read as seq_tup, tpl.idx_scan || ':' || tpl.idx_tup_fetch as idx_tup from pg_statio_user_tables blk JOIN pg_stat_user_tables tpl USING (relname); discovery-test=# select * from iostat where relname='message_parts'; relname | heap_blk | idx_blk | seq_tup | idx_tup ---------------+----------+---------+---------+--------- message_parts | 0:2256 | 0:6834 | 0:0 | 2:3640 * индексы создавать нельзя, используются индексы ориг. таблиц * Разделение прав * постоянство запроса при изменении таблиц * Обновление, rule system * materialized views Триггеры и правила. Правила для переписывания запроса, триггер для реакции на изменение данных (per row), Правила срабатывают *до* выполнения запроса (per statement) и в результате могут измениться много записей. Правильное использование правил - для обновления views. * Наследование Наследование таблиц позволяет создавать базовый объект и дочерние, которые помимо своих атрибутов будут наследовать атрибуты базовых объектов. При этом наследуются DEFAULTS и CONSTRAINTS, включая SERIAL. * наследование+index и partitioning Очень заманчиво использовать наследование таблиц для эффективной работы с очень большей таблицей поделив ее на несколько частей. psql test -c "create table a ( i int primary key);" psql test -c "create table a1() inherits(a);" psql test -c "create table a2() inherits(a);" psql test -c "create table a3() inherits(a);" #create index a_idx on a(i); psql test -c "create index a1_idx on a1(i);" psql test -c "create index a2_idx on a2(i);" psql test -c "create index a3_idx on a3(i);" for ((i=0;i<2000;i++)) do echo $i; done| psql test -c "copy a from stdin;" for ((i=2001;i<4000;i++)) do echo $i; done| psql test -c "copy a1 from stdin;" for ((i=4001;i<6000;i++)) do echo $i; done| psql test -c "copy a2 from stdin;" for ((i=6001;i<8000;i++)) do echo $i; done| psql test -c "copy a3 from stdin;" 1. use inheritance, faster explain analyze select 1 from a where i <10; 2. use union explain analyze select 1 from only a where i <10 union select 1 from a1 where i <10 union select 1 from a2 where i <10 union select 1 from a3 where i <10; Транзакции, точки спасения - возможность отката внутри транзакции BEGIN; UPDATE very_big_table set .......; SAVEPOINT update_ok; -- do some bad things ROLLBACK TO SAVEPOINT update_ok; -- destroy SAVEPOINT -- RELEASE SAVEPOINT update_ok; COMMIT; Табличные пространства - создание хранилища для таблиц CREATE TABLESPACE big_storage OWNER robot LOCATION '/db1/big_storage'; CREATE TABLE very_big_table (.......) TABLESPACE big_storage; * Использование индексов: + многоатрибутные индексы + функциональные индексы + частичные индексы * Использование LIMIT + first N results SELECT * FROM companies ORDER BY co_id LIMIT 5; * last N results -- неправильный (обратный) порядок SELECT * FROM companies ORDER BY co_id DESC LIMIT 5; -- поправим дело SELECT * from ( select * FROM companies ORDER BY co_id DESC LIMIT 5) as foo order by co_id;) * Быстрое удаление всех записей из таблицы Обычно для этого используют DELETE from table_name; однако, при этом сканируются все записи, что для очень больших таблиц очень накладно, поэтому рекомендуется команда TRUNCATE (not in SQL standard) TRUNCATE very_big_table; Есть несколько ограничений по ее использованию в contraints и триггерах, см. мануал * Использование команды explain для оптимизации запросов * Работа с иерархическими данными (ltree) * Организация полнотекстового поиска (tsearch2) * Работа с массивами (intarray) * Поиск с ошибками (pg_trgm)

<< Предыдущая ИНДЕКС Исправить src / Печать Следующая >>

 Добавить комментарий
Имя:
E-Mail:
Заголовок:
Текст:




Партнёры:
PostgresPro
Inferno Solutions
Hosting by Hoster.ru
Хостинг:

Закладки на сайте
Проследить за страницей
Created 1996-2024 by Maxim Chirkov
Добавить, Поддержать, Вебмастеру