Сообщения

Показаны сообщения с ярлыком "SQL"

SQL в PostgreSQL: оконные функции

Изображение
Оконная функция выполняет вычисление по набору строк таблицы, которые каким-то образом связаны с текущей строкой. Это сравнимо с типом расчета, который можно выполнить с помощью агрегатной функции. Однако оконные функции не заставляют строки группироваться в одну выходную строку, как это было бы при не оконных агрегатных вызовах. Вместо этого строки сохраняют свои отдельные идентификаторы. За кулисами оконная функция может получить доступ не только к текущей строке результата запроса. Вот пример, который показывает, как сравнить зарплату каждого сотрудника со средней зарплатой в его или ее отделе: SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary; depname | empno | salary | avg -----------+-------+--------+----------------------- develop | 11 | 5200 | 5020.0000000000000000 develop | 7 | 4200 | 5020.0000000000000000 develop | 9 | 4500 | 5020.0000000000000000 develop | 8 | 6000...

SQL в PostgreSQL: транзакции

Изображение
Транзакции - фундаментальная концепция всех систем баз данных. Существенным моментом транзакции является то, что она объединяет несколько шагов в одну операцию по принципу "все или ничего". Промежуточные состояния между шагами не видны другим параллельным транзакциям, и если произойдет какой-либо сбой, который препятствует завершению транзакции, то ни один из шагов не повлияет на базу данных вообще. Например, рассмотрим банковскую базу данных, которая содержит остатки по различным счетам клиентов, а также общие остатки по депозитам для филиалов. Предположим, мы хотим записать платеж в размере 100 долларов США со счета Алисы на счет Боба. Предельно упрощая, команды SQL для этого могут выглядеть так: UPDATE accounts SET balance = balance - 100.00 WHERE name = 'Alice'; UPDATE branches SET balance = balance - 100.00 WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice'); UPDATE accounts SET balance = balance + 100.00 WHERE name = ...

SQL в PostgreSQL: внешние ключи (Foreign Keys)

Изображение
По данным предыдущих постов . Рассмотрим следующую проблему: вы хотите убедиться, что никто не может вставлять строки в таблицу weather, для которых нет соответствующей записи в таблице cities. Это называется поддержанием ссылочной целостности ваших данных. В упрощенных системах баз данных это может быть реализовано (если вообще возможно), сначала просмотрев таблицу cities, чтобы проверить, существует ли соответствующая запись, а затем вставить или отклонить новые записи weather. Этот подход имеет ряд проблем и очень неудобен, поэтому PostgreSQL может сделать это за вас. Новое объявление таблиц будет выглядеть так: CREATE TABLE cities ( name varchar(80) primary key, location point ); CREATE TABLE weather ( city varchar(80) references cities(name), temp_lo int, temp_hi int, prcp real, date date ); Теперь попробуйте вставить недопустимую запись: INSERT INTO weather VALUES ('Berkeley', 45, ...

SQL в PostgreSQL: представления (Views)

Изображение
По данным предыдущих постов . Предположим, комбинированный список записей погоды и местоположения города представляет особый интерес для вашего приложения, но вы не хотите вводить запрос каждый раз, когда он вам нужен. Вы можете создать представление по запросу, которое дает имя запросу, на которое вы можете ссылаться как на обычную таблицу: CREATE VIEW myview AS SELECT name, temp_lo, temp_hi, prcp, date, location FROM weather, cities WHERE city = name; SELECT * FROM myview; Широкое использование представлений - ключевой аспект хорошего дизайна базы данных SQL. Представления позволяют вам инкапсулировать детали структуры ваших таблиц, которые могут меняться по мере развития вашего приложения, за согласованными интерфейсами. Представления можно использовать практически в любом месте, где можно использовать настоящую таблицу. Построение представлений на другие представления не редкость. Читайте также: SQL в PostgreSQL: концепции и создание таблиц SQ...

SQL в PostgreSQL: объединение таблиц (JOIN)

Изображение
До сих пор наши запросы обращались только к одной таблице за раз. Запросы могут обращаться к нескольким таблицам одновременно или к одной и той же таблице таким образом, что одновременно обрабатываются несколько строк таблицы. Запросы, которые обращаются к нескольким таблицам (или нескольким экземплярам одной и той же таблицы) одновременно, называются join запросами. Они объединяют строки из одной таблицы со строками из второй таблицы с выражением, определяющим, какие строки должны быть объединены в пары. Например, чтобы вернуть все записи weather вместе с местоположением связанного города, базе данных необходимо сравнить столбец city в каждой строке таблицы weather со столбцом name всех строк в таблице cities и выбрать пары строки, в которых совпадают эти значения. Это можно сделать с помощью следующего запроса: SELECT * FROM weather JOIN cities ON city = name; city | temp_lo | temp_hi | prcp | date | name | location ---------------+---------+------...

SQL в PostgreSQL: запрос к таблице

Изображение
Для получения данных из таблицы выполняется запрос к таблице. Для этого используется оператор SQL SELECT. Оператор разделен на список выбора (часть, в которой перечислены столбцы, которые должны быть возвращены), список таблиц (часть, в которой перечислены таблицы, из которых следует извлекать данные) и необязательную квалификацию (часть, которая определяет любые ограничения) . Например, чтобы получить все строки таблицы weather, введите: SELECT * FROM weather; Здесь * сокращенное обозначение "все столбцы". Таким образом, такой же результат будет с: SELECT city, temp_lo, temp_hi, prcp, date FROM weather; Результат должен быть (по данным, добавленным в предыдущем посте ): city | temp_lo | temp_hi | prcp | date ---------------+---------+---------+------+------------ San Francisco | 46 | 50 | 0.25 | 1994-11-27 San Francisco | 43 | 57 | 0 | 1994-11-29 Hayward | 37 | 54 | | 1994-11-29 (3 rows) Хотя S...

SQL в PostgreSQL: заполнение таблицы строками

Изображение
Оператор INSERT используется для заполнения таблицы строками: INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27'); Обратите внимание, что все типы данных используют довольно очевидные форматы ввода. Константы, которые не являются простыми числовыми значениями, обычно должны быть заключены в одинарные кавычки ('), как в примере. Тип даты на самом деле довольно гибкий в том, что он принимает. Тип point требует ввода пары координат, как показано здесь: INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)'); Синтаксис, который использовался до сих пор, требует, чтобы вы запомнили порядок столбцов. Альтернативный синтаксис позволяет явно перечислить столбцы: INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29'); Вы можете перечислить столбцы в другом порядке, если хотите, или даже опустить некоторые столбцы, например, если количеств...

SQL в PostgreSQL: концепции и создание таблиц

Изображение
Концепции PostgreSQL - это система управления реляционными базами данных (СУБД). Это означает, что это система для управления данными, хранящимися в отношениях. Отношение - это, по сути, математический термин для обозначения таблицы. Идея хранения данных в таблицах сегодня настолько распространена, что может показаться очевидной по своей сути, но существует ряд других способов организации баз данных. Файлы и каталоги в Unix-подобных операционных системах образуют пример иерархической базы данных. Более современная разработка - объектно-ориентированная база данных. Каждая таблица представляет собой именованный набор строк. Каждая строка данной таблицы имеет одинаковый набор именованных столбцов, и каждый столбец имеет определенный тип данных. В то время как столбцы имеют фиксированный порядок в каждой строке, важно помнить, что SQL никоим образом не гарантирует порядок строк в таблице (хотя они могут быть явно отсортированы для отображения). Таблицы сгруппированы в базы данных, а наб...

Индекс базы данных: методы индексирования, приложения и ограничения

Изображение
Индексная архитектура и методы индексирования Некластерированный индекс Данные представлены в произвольном порядке, но логический порядок определяется индексом. Строки данных могут быть распределены по всей таблице независимо от значения индексированного столбца или выражения. Некластеризованное дерево индексов содержит ключи индекса в отсортированном порядке, причем конечный уровень индекса содержит указатель на запись (страница и номер строки на странице данных в механизмах, организованных по страницам; смещение строк в механизмах, организованных по файлам). В некластеризованном индексе Физический порядок строк не совпадает с порядком индекса. Индексированные столбцы обычно представляют собой столбцы не первичного ключа, используемые в предложениях JOIN, WHERE и ORDER BY. В таблице базы данных может быть несколько некластеризованных индексов. Кластерный индекс Кластеризация изменяет блок данных в определенном отдельном порядке, чтобы соответствовать индексу, в результате че...

Индекс базы данных

Индекс базы данных - это структура данных, которая повышает скорость операций поиска данных в таблице базы данных за счет дополнительных операций записи и хранения для поддержания структуры данных индекса. Индексы используются для быстрого поиска данных без необходимости поиска каждой строки в таблице базы данных каждый раз при обращении к таблице базы данных. Индексы могут быть созданы с использованием одного или нескольких столбцов таблицы базы данных, обеспечивая основу как для быстрого случайного поиска, так и для эффективного доступа к упорядоченным записям. Индекс - это копия выбранных столбцов данных из таблицы, называемая ключом базы данных или просто ключом, который можно очень эффективно искать, который также включает низкоуровневый адрес блока диска или прямую ссылку на полную строку данных, с которых данные индекса были скопированы. Некоторые базы данных расширяют возможности индексации, позволяя разработчикам создавать индексы для функций или выражений. Например, индекс ...

SQL задача: найти записи, в которых значение столбца встречается по крайней мере N раз

В данном посте рассмотрим варианты решения SQL задачи: найти записи, в которых значение столбца встречается по крайней мере N раз . Для решения задачи будем использовать GROUP BY и HAVING в трех различных вариантах: EXISTS, JOIN, IN. Для примера будем использовать простую таблицу Names: CREATE TABLE Names( `id` INT(10) NOT NULL AUTO_INCREMENT, `name` VARCHAR(100) NOT NULL, PRIMARY KEY (`id`) ); Будем искать записи в таблицы Names где одинаковые значения столбца name встречаются более 3 раз. Решение с помощью EXISTS . По моим замерам выполняющееся с наибольшей скоростью. SELECT a.* FROM Names a WHERE EXISTS (SELECT NULL FROM Names c WHERE c.name = a.name GROUP BY c.name HAVING COUNT(*) > 3); Решение с помощью JOIN . По моим замерам выполняющееся с меньшей скоростью, чем решение с EXISTS. SELECT a.* FROM Names a JOIN (SELECT c.name FROM Names c GROUP BY c.name HAVING COUNT(*) > 3) b ON b....