Как работать с базами данных SQL в Python
В инструкции научим работать с SQL: писать запросы, получать информацию из таблиц, а также познакомим с пакетами и библиотеками Python.
Введение
Сейчас в любой сфере деятельности человека необходима работа с большим объемом данных. Данные — это поддающееся многократной интерпретации представление информации, приведенное к формализованному виду, который будет пригоден для передачи или обработки. Для удобства использования огромных объемов данных была придумана структура, называемая база данных (БД). База данных — одна из ключевых компонент любой информационной системы.
Для управления базами используются системы управления базами данных (СУБД).
SQL-запросы
Близкое взаимодействие с базами данных неразрывно связано с SQL (Structured Query Language). С помощью него можно не только создать таблицу и заполнить ее уникальной информацией, но и «вытащить» из базы практически любую информацию, используя специальные запросы.
Инициализация таблицы в SQL
В базах данных таблицы представляют собой совокупность данных, хранящихся в структурированном виде. Большие базы данных состоят из множества таблиц, взаимосвязанных специальными видами связи. Для начала создадим таблицу с помощью SQL:
CREATE TABLE Customers ( id INTEGER, name VARCHAR, surname VARCHAR, birthdate DATE, PRIMARY KEY (id) ); Рассмотрим подробнее этот обобщенный код. Команды, выделенные заглавными буквами, являются командами языка SQL. Разные виды БД поддерживают немного различающиеся команды, но большинство из них будет иметь CREATE TABLE.
Важно внимательно работать с типами столбцов, так как они могут отличаться. В нашем примере есть такие типы, как INTEGER для чисел, VARCHAR для строк и DATE для дат. Перед тем как использовать разные типы данных лучше ознакомиться с документацией, так как VARCHAR и DATE могут иметь свои особенности.
Созданная нами таблица будет состоять из четырех столбцов: уникальный идентификатор (будет являться основным ключом), имя, фамилия в строковом типе и дата рождения.
Также при добавлении новой таблицы мы можем задать столбец, в который необходимо будет записать значение. В противном случае при попытке оставить указанный столбец пустым, нам вернется ошибка. Здесь можно провести аналогию с формой регистрации на сайте, когда без указания номера телефона или электронной почты пользователь не сможет зарегистрироваться.
Введение данных в SQL
Сейчас наша таблица пустая. Поэтому сейчас научимся заполнять ее данными:
INSERT INTO Customers (id, name, surname, birthdate) VALUES (1, 'Ivan', 'Petrov', '2001-10-16'); Для добавления новых данных в SQL нужно применять команду INSERT INTO. Также нужно указать, куда будут добавлены данные. При передаче неверного типа данных мы получим ошибку.
Обновление данных в SQL
Может возникнуть ситуация, когда какую-то часть данных необходимо поменять. Конечно, проще изменить данные выборочно, а не переписывать всю базу целиком.
UPDATE Customers SET name='Semyon' WHERE >После команды UPDATE мы обязательно пишем имя таблицы, где требуется обновить данные. После мы применяем SET во всех необходимых местах для замены обновленного значения. Если мы хотим внести изменения в конкретную строку, то нужно сообщить, куда мы собираемся внести правки. Например, в данном случае применяя WHERE, мы выбираем строку с id, равным 1. В результате будет только одна строка, так как подразумевается, что id — это уникальный идентификатор.
Чтение данных в SQL
При чтении данных нужно воспользоваться SELECT:
SELECT name, surname FROM Customers; Так мы вернем все строки нашей таблицы, но в результате будут только две части информации: имя и фамилия. Если мы хотим получить все, что хранится в таблице, то можно воспользоваться запросом:
SELECT * FROM ; В этом случае звездочка означает, что мы намерены получить все имеющиеся в таблице столбцы.
Основные команды SQL
В SQL есть определенное количество команд, используя которые мы усложняем запросы. Комбинируя и правильно используя их, можно получить самые разные выборки данных в зависимости от потребностей. Давайте рассмотрим основные такие команды.
WHERE
Эту команду мы уже упоминали выше. С помощью нее мы указываем какое-то условие:
SELECT , , … FROM WHERE ; Условие может содержать сравнение текста или численных значений. Также допустимо использование классических логических операций: AND (и), OR (или) и NOT (отрицание).
GROUP BY
Оператор GROUP BY применяется для группировки выходных значений, в которых присутствуют агрегатные функции (например, COUNT, MAX, SUM, AVG и другие).
SELECT , , … FROM GROUP BY ; HAVING
По своей сути HAVING является аналогом WHERE. Это ключевое слово применяется вместе с GROUP BY. Это связано с тем, что команда WHERE не может использоваться с вышеперечисленными агрегатными функциями.
SELECT , , . FROM GROUP BY HAVING
ORDER BY
ORDER BY применяется в том случае, когда к результату запроса нужно применить сортировку. По умолчанию команда отсортирует выходной результат в порядке возрастания. Однако можно указать способ сортировки с помощью ASC (по возрастанию) и DESC (по убыванию).
SELECT , , … FROM ORDER BY , , … ASC|DESC; BETWEEN
BETWEEN применяется для выбора значений в определенном диапазоне. Этот оператор работает для чисел и строк, а также дат.
SELECT , , … FROM WHERE BETWEEN AND ; LIKE
Оператор LIKE тоже используется в WHERE, когда задача требует задать шаблон для поиска.
Существует два свободных оператора, с помощью которых мы можем создавать определенные паттерны:
- % (любое количество символов (в том числе ни одного символа));
- _ (ровно один символ).
SELECT , , … FROM WHERE LIKE ; JOIN
JOIN применяется для соединения двух (и более таблиц), основываясь на общих атрибутах.
SELECT , , … FROM JOIN ON = ; Вложенные подзапросы
Вложенные подзапросы — это запросы, включающие стандартные выражения SQL, вложенные в другой запрос. Это удобно использовать, если задача требует написать выделить какую-то информацию из результата другого запроса.
Удаление данных
Теперь мы умеем обрабатывать данные. Осталось научиться их удалять:
DELETE FROM WHERE >Такая команда удалит одну строку, id которой равно 1. Если необходимо удалить таблицу, то стоит применить DROP TABLE:
DROP TABLE ; Однако можно очистить таблицу от данных, не удаляя ее саму:
TRUNCATE TABLE ; С такими командами нужно работать предельно осторожно, ведь есть вероятность потерять важные данные. Чтобы избежать такой ситуации, следует всегда иметь актуальный бекап всей локальной базы данных.
API-модули
Python DB-API — это свод правил, которым следуют самостоятельные модули, задача которых заключается в реализации работы с БД. API-модулем мы называем программный интерфейс, посредством которого мы взаимодействуем с данными. Один принцип позволяет применять общий подход для разных БД. Поэтому для полноценной работы не нужно углубленно изучать каждую БД — достаточно разобраться с несколькими основными моментами.
adodbapi
Если возникает такая ситуация, где обязательно надо реализовать доступ через Microsoft ADO, то идеальным решением будет пакет adodbapi. И нельзя забывать, что adodbapi зависим от уже установленного PyWin32.Чтобы установить adodbapi, делаем следующее:
pip install adodbapi Чтобы все заработало, сначала импортируем библиотеку:
import adodbapi Далее мы указываем все необходимые для соединения данные: имя БД, строку подключения и название соответствующей таблицы.
database = "database.mdb" connect_str = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=%s; User Password=MyPassword;" % database table_name = "customers" После этого для связи с БД создаем подключение, где аргументом у метода connect() указываем строку связи:
connect = adodbapi.connect(connect_str) Теперь создаем курсор — область памяти базы, предназначенная для хранения последнего оператора SQL. Иными словами, объект, отвечающий и за отправку запросов, и за получение их результатов.
cursor = connect.cursor() Дальше мы уже можем передавать конкретные запросы и обрабатывать их вывод.
query = "SELECT * FROM %s" % table_name cursor.execute(query) В конце обязательно завершаем подключение:
cursor.close() connect.close() pyodbc
Сейчас ознакомимся с ODBC (Open Database Connectivity) — интерфейсом доступа к БД, разработанный в компании Microsoft. Суть ODBC заключается в разработке приложений для использования программного интерфейса доступа без опасений о различиях взаимодействия с разными источниками. Это достигается написанием драйверов, осуществляющих стандартные функции с учетом деталей реализации конкретного продукта. Самый используемый метод связи через ODBC — пакет pyodbc. Он устанавливается с помощью pip:
pip install pyodbc После установки импортируем библиотеку:
import pyodbc Далее необходимо написать строку связи:
connect_str = "DRIVER=; SERVER=localhost; PORT=1433; DATABASE=database; UID=uid; PWD=password" В данном случае строка связи состоит из нескольких частей. Хорошей практикой будет сохранить все параметры в отдельный конфигурационный файл, в котором и будут храниться все необходимые параметры. Это добавит удобства при использовании и может обезопасить систему. После соединяемся с БД и получаем курсор:
connect = pyodbc.connect(connect_str) cursor = connect.cursor() Если мы успешно подключились, то, используя это соединение, мы можем применить курсор для получения ответа на запрос:
cursor.execute('SELECT * FROM ') Теперь можно получить результат, вызвав методы fetchone() и fetchall(). Способы их использования мы рассмотрим чуть ниже.Так как не только базы данных от Microsoft поддерживают такой вид соединения, то пакет pyodbc можно использовать при работе и с другими базами данных, совместимыми с ODBC.
pypyodbc
Пакет pypyodbc можно назвать скриптом, написанным на Python. Интересно, что на самом деле pyodbc — это Python, завернутый в бэкэнд С++, в то время как pypyodbc уже является чистым кодом на Python. Чаще всего эти модули взаимозаменяемы. Единственное различие будет заключаться в импорте:
import pypyodbc SQLite в Python
В отличие от других баз данных SQL, которые мы будем рассматривать дальше, у Python’a уже есть встроенная поддержка для SQLite — компактной встраиваемой СУБД. Для этой БД API-модулем будет sqlite3. Поэтому для корректной работы достаточно добавить импортирование стандартной библиотеки и ничего заранее устанавливать не нужно:
import sqlite3 Подключение к базе данных
Далее обязательным этапом следует создание соединения:
connect = sqlite3.connect('database.sqlite') Здесь мы указываем путь до файла базы данных. Следующим шагом требуется создать объект курсора:
cursor = connect.cursor() Чтение из базы
Для чтения необходимо сделать следующее:
cursor.execute("SELECT FROM ") После вызова метода execute() мы уже пользуемся привычным синтаксисом SQL. Для получения ответа воспользуемся fetchall():
results = cursor.fetchall() Так мы получаем все строки результата сделанного запроса. Важно помнить, что после того, как мы получили ответ на запрос из курсора, чтобы получить этот результат еще раз, необходимо повторно выполнить запрос. Иначе вернется пустой результат (null).
После окончания всех требуемых операций, обязательно нужно закрыть наше соединение:
connect.close() Запись в базу
Аналогично чтению, для записи в БД нужно написать запрос к ней:
cursor.execute("INSERT INTO (id, name, surname, birthdate) VALUES (2, 'Petr', 'Ivanov', '2003-12-13') ") Однако, если мы не только читаем, но и вносим какие-либо изменения, обязательно нужно сохранить транзакцию:
connect.commit() Когда к базе установлено не одно соединение, а одно из них пытается как-то модифицировать данные, база данных SQLite блокируется до завершения или отмены текущей транзакции. Закончить транзакцию можно методом commit(), а отменить ее — методом rollback().
MySQL в Python
MySQL — это СУБД с открытым исходным кодом (open source). Ее можно подключить несколькими способами. Один из наиболее распространенных — это использование пакета MySQLdb, у которого существует несколько версий. Из-за наличия различных версий, часть из которых несовместима с конкретными версиями Python, может возникнуть путаница.
Поэтому рассмотрим подробно один конкретный пакет mysqlclient — ответвление MySQL-Python (как раз MySQLdb), которое предоставляет поддержание Python 3. Важно отметить, что нам понадобится MySQL или MySQL Client для его успешной установки:
pip install mysqlclient После этого пакет mysqlclient будет установлен. Теперь посмотрим, как работа с этим пакетом будет реализована в коде:
import MySQLdb Так мы подключаем сам пакет.
connect = MySQLdb.connect('localhost', 'username', 'password', 'table_name') Этой строчкой мы создаем соединение. Обязательно указываем сервер, куда подключаемся, логин и пароль для соединения, а также название таблицы, с которой будем осуществлять взаимодействие.
cursor = connect.cursor() cursor.execute("SELECT * FROM ") Курсор создан. Можно начинать выполнять конкретные запросы.
row = cursor.fetchone() Здесь мы извлекаем только одну строку из всего результата и дальше можем обрабатывать ее так, как требует решаемая задача.
connect.close() Не забываем закрыть связь с БД.
PostgreSQL в Python
PostgresSQL является еще одной БД, распространяемой как свободное программное обеспечение, широко используемое в разработке. У Python существует несколько пакетов, которые поддерживают этот бэкэнд, но мы изучим работу с одним из них — Psycopg. Аналогично другим пакетам для начала необходимо его установить:
pip install psycopg2 Уже в коде будет необходимо импортировать этот пакет:
import psycopg2 После этого аналогично работе с MySQL мы передаем в переменную connect соединение:
connect = psycopg2.connect(host='hostname', user='username', password='password', dbname='database') Что делать дальше нам уже известно — создавать курсор:
cursor = connect.cursor() Далее посмотрим на этот фрагмент кода:
cursor.execute("SELECT * FROM ") row = cursor.fetchone() cursor.close() connect.close() С помощью метода execute() мы делаем запрос к БД. После этого получаем одну строку результата с помощью fetchone(). После чего разрываем подключение к базе данных, закрывая и курсор, и соединение.
За исключением нескольких особенностей, работа с Psycopg практически не отличается от работы с другими пакетами. Мы помним, что и mysqlclient, и Psycopg следуют стандартному API, которому, на самом деле, следует большая часть пакетов. Именно поэтому код взаимодействия с разными БД практически не отличается между собой. Так мы можем подтвердить, что несмотря на существующие различия, работа с разными пакетами сводится к нескольким одинаковым командам.
Расширенные методы курсора
Теперь рассмотрим особые возможности курсора, которые могут помочь при взаимодействии с базой данных.
Разбивка запроса на строки
Часто приходится писать длинные SQL-запросы, состоящие из множества строк. К сожалению, при написании запроса в одну строку теряется читабельность. Поэтому в коде удобно разбить такой запрос на несколько строчек, заключив его в тройные кавычки:
cursor.execute(""" SELECT surname FROM ORDER BY surname LIMIT 4 """) Объединение запросов к БД
Метод execute() позволяет выполнить за раз лишь один запрос (если написать сразу несколько запросов, разделив их точкой с запятой или другим разделителем, то мы получим ошибку). Однако чаще всего в разработке требуется обратиться к БД далеко не один раз. Конечно, можно просто вызвать этот метод несколько раз подряд:
cursor.execute("INSERT INTO (id, name, surname, birthdate) VALUES (3, 'Stepan', 'Platonov', '2010-01-01') ") cursor.execute("INSERT INTO (id, name, surname, birthdate) VALUES (4, 'Platon', 'Stepanov', '2010-02-02') ") Но можно воспользоваться более изящным решением и вызвать executescript():
cursor.executescript(""" INSERT INTO (id, name, surname, birthdate) VALUES (3, 'Stepan', 'Platonov', '2010-01-01'); INSERT INTO (id, name, surname, birthdate) VALUES (4, 'Platon', 'Stepanov', '2010-02-02'); """) Также этот метод может помочь в том случае, если мы сохранили тело запроса в файл или отдельную переменную.
Подстановка значения в запрос
В процессе написания кода, в котором мы тесно взаимодействуем с БД, может возникнуть ситуация, когда необходимо подставить конкретное значение в запрос. Тогда поможет применение второго аргумента в методе execute():
cursor.execute("SELECT surname FROM ORDER BY surname LIMIT ?", ('2')) Или есть еще один способ:
cursor.execute("SELECT surname FROM ORDER BY surname LIMIT :limit", ) Важно! В PostgreSQL и в MySQL для подстановки вместо знака ‘?’ нужно писать %s.
Также стоит отметить, что этот способ не подойдет для замены названия таблицы.
Множественная вставка строк
Для вставки нескольких строк воспользуемся методом executemany(), в который в независимости от количества значений необходимо передавать список кортежей:
new_users = [ ('User',), ('User-2',), ('User-3',), ] В этом примере мы как раз используем кортеж (поэтому после имени пользователя идет запятая), несмотря на то что передаем только одно значение. После этого вставляем полученный список:
cursor.executemany("INSERT INTO Users VALUES (Null, ?);", new_users) Таким образом, проходясь по списку, мы вставляем сразу несколько строк.
Повышение устойчивости кода
Сейчас в любом проекте требуется правильно обрабатывать и отлавливать возможные ошибки во время исполнения программы. Особенно это может быть критично при записи информации в базу данных. Поэтому полезно оборачивать обращение к БД в конструкцию «try-except-else»:
try: cursor.execute(query) result = cursor.fetchall() except sqlite3.DatabaseError as error: print("Error: ", error) else: connect.commit() Подробнее рассмотрим этот фрагмент. В try мы передаем инструкцию для SQL, после чего записываем в result весь результат. При возникновении ошибки мы используем встроенный в sqlite3 объект ошибок и печатаем его в консоль. В противном случае, если же все отработало корректно, мы сохраняем изменения. Такой подход может сильно упростить исправление ошибок в ходе разработки.
Создание таблиц
Перед нами стоит простая задача: создать таблицу. На примере этой задачи мы окончательно разберемся в принципе работы с БД в Python. Ниже посмотрим на функцию, задача которой будет осуществлять работу с такими БД, как SQLite и MySQL (для PostgreSQL будет незначительно отличаться несколькими строчками):
def execute_query(connect, query): cursor = connect.cursor() try: cursor.execute(query) connect.commit() print("Success!") except Error as err: print("Error: ", err) В аргументы execute_query() мы передаем соединение и сам запрос. Дальше с помощью курсора мы исполняем запрос и сохраняем транзакцию. Также сразу обрабатываем ошибки, которые могут возникнуть.
Выше мы уже подробно изучили, что делает каждая строчка кода, здесь же все объединено в одну функцию для удобства написания кода. Теперь осталось написать сам запрос, который будет создавать новую таблицу:
create_table_query = """ CREATE TABLE customers ( id INTEGER PRIMARY KEY AUTO_INCREMENT, name TEXT NOT NULL, surname TEXT, age INTEGER ); В этом запросе мы создаем таблицу клиентов, у каждого из которых будет свой id (генерирующийся самостоятельно), имя, фамилия и возраст. Осталось вызвать execute_query():
execute_query(connect, create_table_query) Все, новая таблица будет создана.
Работа с записями в БД с помощью Python
Теперь мы уже можем подвести итог о принципе работы Python с базами данных. Вне зависимости от того, какую цель мы преследуем и какие действия мы хотим сделать с таблицей, все сводится к отправлению запросов на SQL, которые мы передаем в курсор. Далее мы кратко рассмотрим примеры, связанные с различными операциями с таблицами.
Мы уже знаем, что в зависимости от конкретной задачи будет изменяться только SQL-запрос. Поэтому ниже посмотрим на соответствующие запросы для разных подзадач.
Добавление записей
add_customers_query = """ INSERT INTO customers (name, surname, age) VALUES ('Anne', 'Samoilova', 26), ('Petr', 'Ogurechkin', 44), ('Samanta', 'Ivanova', 31); """ Внутри запроса мы добавляем в таблицу customers трех человек, для каждого указывая имя, фамилию и возраст. Осталось передать этот запрос в execute_query(), как мы делали выше.
Обновление данных
update_customer_query = """ UPDATE customers SET name = 'Pavel' WHERE >В этом запросе мы изменяем имя клиенту с id равном 2. При передаче этого запроса в функцию мы успешно обновим информацию в таблице.
Чтение данных
select_customers_query = "SELECT * FROM customers" Выполняя этот запрос, мы получим всех клиентов, информация о ком хранится в таблице.
Удаление данных
delete_customer_query = "DELETE FROM customers WHERE >А теперь удалим клиента с id равным 1.
Дополнительные возможности SQLite
SQLite широко используется и легко поддерживается большинством клиентов SQL. Рассмотрим некоторые интересные возможности SQLite.
Подключение к БД из клиента SQL
Если мы запускаем Python на локальном компьютере, то с помощью какого-нибудь клиента SQL можно напрямую подключиться к файлу БД. Одним из таких клиентов является приложение DBeaver, позволяющий управлять базой данных.
Алгоритм работы как и раньше: сначала нужно создаем новое соединение: правой кнопкой мыши по названию БД (при установке DBeaver предлагает создать тестовую базу данных, чтобы ознакомиться с функционалом приложения) → Создать → Соединение:
После выбора подходящей нам базы данных SQLite, мы нажимаем Далее и в специальном окне настраиваем соединение нужным нам образом:
После завершения всех настроек и указания верных путей мы уже можем сделать любой SQL-запрос.
Интеграция с фреймворком Pandas
На самом деле в Python есть библиотека, специально предназначенная для обработки и анализа структурированных данных. Библиотека Pandas — это гибкий и мощный инструмент, широко применяемый в анализе данных.
В фреймворке Pandas есть множество специальных структур и операций для эффективной работы с данными. Основополагающей частью Pandas является фрейм данных (от DataFrame) — структура, представляющая собой двумерный набор данных, хранящихся в табличной форме. Но также фрейм данных непрерывно интегрируется с SQLite.
Как мы уже привыкли, сначала нужно импортировать библиотеку:
import pandas as pd Для демонстрации этого сначала определим какой-нибудь фрейм данных:
df_films = pd.DataFrame(< 'film_id': [1, 2, 3, 4, 5, 6], 'year': [1999, 2017, 2014, 1985, 2002, 2001], 'name': ['The Green Mile', 'Coco', 'Interstellar', 'Back to the Future', 'The Lord of the Rings: The Two Towers', 'Sen to Chihiro no kamikakushi'] >) 
Также мы могли прочитать дата фрейм из файла с расширением csv:
df_films = pd.read_csv(filepath_or_buffer = "file_with_films.csv", sep = ';') Теперь у нас есть небольшой фрейм данных, посвященный фильмам. У фрейма есть метод to_sql(), позволяющий сохранить его в БД. Воспользуемся им:
df_films.to_sql('Films', connection) Теперь у нас есть таблица в нашей БД, соединение с которой мы передали в аргументы метода. Длина столбцов и типы данных будут сгенерированы автоматически. Но если появится такая необходимость, конечно, их можно будет изменить.
Если же мы хотим написать какой-то SQL-запрос к таблице, то это также легко делается с помощью метода read_sql():
df = pd.read_sql(''' SELECT * FROM Films ''', connection) Можем сделать вывод, что с помощью библиотеки Pandas можно очень просто реализовать работу с реляционной базой данных SQLite.
Заключение
Мы тесно поработали с SQL, научились писать запросы, используя специальные операторы и теперь умеем получать самую разную информацию из таблиц в зависимости от поставленной задачи.
Также в этой статье мы ознакомились с разнообразными пакетами и библиотеками языка Python, позволяющими реализовать и упростить неочевидное взаимодействие с базами данных SQL. Python позволяет без труда совершать множество операций, начиная от создания таблиц и заканчивая модификацией строк в уже существующих записях.
Зарегистрируйтесь в панели управления
И уже через пару минут сможете арендовать сервер, развернуть базы данных или обеспечить быструю доставку контента.
Работа с базами данных SQL для новичков
Работу с базами данных SQL, как структурированный язык запросов, выполняет практически идеально. Более того, он считается основным инструментом для взаимодействия с реляционными БД, позволяющим проводить с ними самые разные манипуляции.
И пусть возраст SQL насчитывает уже несколько десятилетий, он до сих пор используется весьма широко. Создать без него нечто серьезное весьма затруднительно.
Особенности языка SQL
SQL является непроцедурным языком программирования, предназначенным в первую очередь для описания данных, их выборки из реляционных БД и последующей обработки. Таким образом, SQL оперирует исключительно базами данных, и использовать только его для создания полноценного приложения нельзя.
В этом случае потребуются инструменты других языков, поддерживающих встраивание SQL-команд. Именно по причине своей специфичности SQL считают вспомогательным средством, позволяющим обрабатывать данные. Этот язык на практике используется только совместно с другими языками.
В общем случае прикладные средства программирования подразумевают создание процедур. SQL такими возможностями не обладает. Здесь нельзя указать способы решения задач — задается лишь смысл каждой конкретной задачи. Иначе говоря, в работе с базами данных SQL важны результаты, а не процедуры, приводящие к этим результатам.
Этот специфический язык программирования обладает одним важным свойством — возможностью доступа к реляционным базам данных. Иногда все реляционные БД ошибочно приравниваются к СУБД с применением средств SQL. На самом деле эти понятия следует различать.
Понятие реляционной СУБД
Не углубляясь в детали, можно дать такое определение: реляционной называется СУБД, использующая реляционную модель управления.
Доктор Е. Ф. Кодд в 1970 году опубликовал свою работу, где впервые было дано понятие реляционной модели. В публикации описывался некий математический аппарат, структурирующий данные и оперирующий ими. Основная идея состояла в представлении любых данных в виде абстрактной модели.
В соответствии с предложенной концепцией отношение между объектами (relation) представляет собой некую таблицу с данными. При этом существуют атрибуты (или признаки) отношения, которые соответствуют столбцам рассматриваемой таблицы. Сами данные предстают в виде наборов этих признаков и формируют записи (кортежи). Последние в свою очередь соответствуют табличным строкам.
Значения атрибутов каждого кортежа входят в домены, представляющие собой определенные наборы данных и задающие пределы допустимых значений.
Разберем это на примере. Существует домен «Неделя», в котором содержатся значения всех дней недели («Понедельник», «Вторник», …, «Воскресенье»). Атрибут, имеющий эти значения, называется «ДеньНедели». Тогда соответствие этого атрибута домену автоматически означает, что в одноименном столбце должны содержаться только перечисленные значения. Любые другие символы и группы символов недопустимы.
Узнай, какие ИТ — профессии
входят в ТОП-30 с доходом
от 210 000 ₽/мес
Павел Симонов
Исполнительный директор Geekbrains
Команда GeekBrains совместно с международными специалистами по развитию карьеры подготовили материалы, которые помогут вам начать путь к профессии мечты.
Подборка содержит только самые востребованные и высокооплачиваемые специальности и направления в IT-сфере. 86% наших учеников с помощью данных материалов определились с карьерной целью на ближайшее будущее!
Скачивайте и используйте уже сегодня:
Павел Симонов
Исполнительный директор Geekbrains
Топ-30 самых востребованных и высокооплачиваемых профессий 2023
Поможет разобраться в актуальной ситуации на рынке труда
Подборка 50+ бесплатных нейросетей для упрощения работы и увеличения заработка
Только проверенные нейросети с доступом из России и свободным использованием
ТОП-100 площадок для поиска работы от GeekBrains
Список проверенных ресурсов реальных вакансий с доходом от 210 000 ₽
Получить подборку бесплатно
Уже скачали 24088
Также запрещается ввод сразу нескольких значений. Кроме того, требуется соблюдение условия атомарности. Иначе говоря, значения нельзя объединять друг с другом и разбивать их на более мелкие составляющие, сохранив при этом смысл. В случае присутствия в ячейке атрибута сразу двух значений и более (например, «Вторник» и «Среда») атомарность теряется. Тут можно выделить две части, сохранив смысл, но при дальнейшем разбитии слов на отдельные символы исходный смысл также утратится.
Другое важное свойство отношений в СУБД — замкнутость операций. Оно заключается в том, что любая операция над отношением порождает новое отношение. Благодаря этому свойству программисты SQL получают предсказуемые результаты математических действий. Также становится возможным представление операций в виде абстрактных выражений, обладающих разными уровнями вложенности.
Популярные сервисы для работы с SQL
Как язык работы с базами данных, SQL предполагает обязательное наличие установленной БД с доступом для подключения и выполнения запросов.
С помощью данного сервиса все SQL-операции можно выполнять в облаке. Это достаточно серьезное преимущество, ведь программистам здесь нет необходимости устанавливать и настраивать СУБД на локальную машину. Достаточно лишь зарегистрироваться.
В целом процесс работы весьма простой. После регистрации необходимо войти под созданной учетной записью и выбрать пункт «SQL WorkSheet» в боковом меню слева. Откроется рабочее окно, куда, собственно, и нужно вводить SQL-запросы. Для выполнения запросов следует нажать кнопку «Run» над полем ввода текста.
Это еще одна популярная программа для работы с SQL базами данных, работающая как сервис и поддерживающая множество форматов БД. Регистрация здесь не требуется.
При входе на SQL Fiddle в первую очередь нужно выбрать подходящую для работы БД (например, Oracle). Далее создается схема из таблиц путем ввода текста специального ddl-скрипта. После нажатия на кнопку «Build Schema» можно приступать к выполнению SQL-запросов. Для их ввода используется панель «Query Panel», расположенная справа. Выполнение запроса осуществляется нажатием на «Run Sql». Результаты работы будут видны под рабочими панелями.
Основные команды SQL
Помимо трех основных команд (CREATE, UPDATE и DELETE), используются и несколько других. Перечислим их ниже с примерами для MySQL (поэтому везде после операторов стоит точка с запятой).
Итак, прежде всего создаем базу данных с текстовым наполнением.
Далее необходимо скачать файлы DLL.sql и InsertStatements.sql, а затем установить на компьютер СУБД MySQL. После чего в командной строке нужно ввести mysql -u root -p для входа в консоль MySQL.
Основные команды SQL, которые должен знать каждый программист
Рассмотрим основные команды SQL, включая агрегатные функции и вложенные подзапросы, на примере работы с MySQL.
Основные команды SQL не ограничиваются стандартными CREATE , UPDATE и DELETE . Данная статья будет полезна тем, кто хочет освежить свои знания по SQL перед собеседованием на работу.
Обратите внимание, что в примерах используется MySQL, поэтому в конце каждого оператора ставится точка с запятой.
Примечание Вы читаете улучшенную версию некогда выпущенной нами статьи.
Настройка базы данных
Перед началом создайте БД с тестовыми данными. Для работы вам понадобится скачать два файла: DLL.sql и InsertStatements.sql. После установите MySQL, откройте терминал и войдите в консоль MySQL с помощью команды:
mysql -u root -p Затем введите пароль и выполните следующую команду. Назовём базу данных «university»:
CREATE DATABASE university; USE university; SOURCE ; SOURCE ; SHOW DATABASES
SQL-команда, которая отвечает за просмотр доступных баз данных.
CREATE DATABASE
Команда для создания новой базы данных.
USE
С помощью этой SQL-команды USE выбирается база данных, необходимая для дальнейшей работы с ней.
SOURCE
А SOURCE позволит выполнить сразу несколько SQL-команд, содержащихся в файле с расширением .sql.
DROP DATABASE
Стандартная SQL-команда для удаления целой базы данных.
SHOW TABLES
С помощью этой несложной команды можно увидеть все таблицы, которые доступны в базе данных.
CREATE TABLE
SQL-команда для создания новой таблицы:
CREATE TABLE ( , , PRIMARY KEY(), FOREIGN KEY() REFERENCES () ); Ограничения целостности при использовании CREATE TABLE
Может понадобиться создать ограничения для определённых столбцов в таблице. При создании таблицы можно задать следующие ограничения:
- ячейка таблицы не может иметь значение NULL ;
- первичный ключ — PRIMARY KEY(col_name1, col_name2, …) ;
- внешний ключ — FOREIGN KEY(col_namex1, …, col_namexn) REFERENCES table_name(col_namex1, …, col_namexn) .
Можно задать больше одного первичного ключа. В этом случае получится составной первичный ключ.
Пример
Создайте таблицу «instructor»:
CREATE TABLE instructor ( ID CHAR(5), name VARCHAR(20) NOT NULL, dept_name VARCHAR(20), salary NUMERIC(8,2), PRIMARY KEY (ID), FOREIGN KEY (dept_name) REFERENCES department(dept_name) ); DESCRIBE
С помощью DESCRIBE можно просмотреть различные сведения (тип значений, является ключом или нет) о столбцах таблицы.
INSERT
Команда INSERT INTO в SQL отвечает за добавление данных в таблицу:
INSERT INTO (, , , …) VALUES (, , , …); При добавлении данных в каждый столбец таблицы не требуется указывать названия столбцов.
INSERT INTO VALUES (, , , …); UPDATE
SQL-команда для обновления данных таблицы:
UPDATE SET = , = , . WHERE ; DELETE
SQL-команда DELETE FROM используется для удаления данных из таблицы.
DROP TABLE
А так можно удалить всю таблицу целиком.
SELECT
Далее мы рассмотрим основные команды SQL, которые позволяют работать непосредственно с данными. К одной из таких SQL-команд относится SELECT для получения данных из выбранной таблицы:
SELECT , , … FROM ; Следующей командой можно вывести все данные из таблицы:
SELECT * FROM ; SELECT DISTINCT
В столбцах таблицы могут содержаться повторяющиеся данные. Используйте SELECT DISTINCT для получения только неповторяющихся данных.
SELECT DISTINCT , , … FROM ; WHERE
Можно использовать ключевое слово WHERE в SELECT для указания условий в запросе:
SELECT , , … FROM WHERE ; В запросе можно задавать следующие условия:
- сравнение текста;
- сравнение численных значений;
- логические операции AND (и), OR (или) и NOT (отрицание).
Пример
Попробуйте выполнить следующие команды. Обратите внимание на условия, заданные в WHERE :
SELECT * FROM course WHERE dept_name=’Comp. Sci.’; SELECT * FROM course WHERE credits>3; SELECT * FROM course WHERE dept_name='Comp. Sci.' AND credits>3; SQL-команды: пример вывода с WHERE
GROUP BY
Оператор GROUP BY часто используется с агрегатными функциями, такими как COUNT , MAX , MIN , SUM и AVG , для группировки выходных значений.
SELECT , , … FROM GROUP BY ; Пример
Выведем количество курсов для каждого факультета:
SELECT COUNT(course_id), dept_name FROM course GROUP BY dept_name; SQL-команды: пример вывода с GROUP BY
HAVING
Ключевое слово HAVING было добавлено в SQL по той причине, что WHERE не может использоваться для работы с агрегатными функциями.
SELECT , , . FROM GROUP BY HAVING Пример
Выведем список факультетов, у которых более одного курса:
SELECT COUNT(course_id), dept_name FROM course GROUP BY dept_name HAVING COUNT(course_id)>1; SQL-команды: пример вывода с HAVING
ORDER BY
ORDER BY используется для сортировки результатов запроса по убыванию или возрастанию. ORDER BY отсортирует по возрастанию, если не будет указан способ сортировки ASC или DESC .
SELECT , , … FROM ORDER BY , , … ASC|DESC; Пример
Выведем список курсов по возрастанию и убыванию количества кредитов:
SELECT * FROM course ORDER BY credits; SELECT * FROM course ORDER BY credits DESC; BETWEEN
BETWEEN используется для выбора значений данных из определённого промежутка. Могут быть использованы числовые и текстовые значения, а также даты.
SELECT , , … FROM WHERE BETWEEN AND ; Пример
Выведем список инструкторов, чья зарплата больше 50 000, но меньше 100 000:
SELECT * FROM instructor WHERE salary BETWEEN 50000 AND 100000; LIKE
Оператор LIKE используется в WHERE , чтобы задать шаблон поиска похожего значения.
Есть два свободных оператора, которые используются в LIKE :
- % (ни одного, один или несколько символов);
- _ (один символ).
SELECT , , … FROM WHERE LIKE ; Пример
Выведем список курсов, в имени которых содержится «to» , и список курсов, название которых начинается с «CS-» :
SELECT * FROM course WHERE title LIKE ‘%to%’; SELECT * FROM course WHERE course_id LIKE 'CS-___'; SQL-команды: пример вывода с LIKE
IN
С помощью IN можно указать несколько значений для оператора WHERE :
SELECT , , … FROM WHERE IN (, , …); Пример
Выведем список студентов с направлений Comp. Sci., Physics и Elec. Eng.:
SELECT * FROM student WHERE dept_name IN (‘Comp. Sci.’, ‘Physics’, ‘Elec. Eng.’); JOIN
JOIN используется для связи двух или более таблиц с помощью общих атрибутов внутри них. На изображении ниже показаны различные способы объединения в SQL. Обратите внимание на разницу между левым внешним объединением и правым внешним объединением:
SQL-команды: схема использования JOIN
SELECT , , … FROM JOIN ON = ; Пример
Выведем список всех обязательных курсов и детали о них:
SELECT prereq.course_id, title, dept_name, credits, prereq_id FROM prereq LEFT OUTER JOIN course ON prereq.course_id=course.course_id; SQL-команды: пример вывода с JOIN
VIEW
VIEW — это виртуальная таблица SQL, созданная в результате выполнения выражения. Она содержит строки и столбцы и очень похожа на обычную SQL-таблицу. VIEW всегда показывает самую свежую информацию из базы данных.
Создание
CREATE VIEW AS SELECT , , … FROM WHERE ; Удаление
DROP VIEW ; Агрегатные функции
Это не совсем основные команды SQL, однако знать их тоже желательно. Агрегатные функции используются для получения совокупного результата, относящегося к рассматриваемым данным:
- COUNT(col_name) — возвращает количество строк;
- SUM(col_name) — возвращает сумму значений в данном столбце;
- AVG(col_name) — возвращает среднее значение данного столбца;
- MIN(col_name) — возвращает наименьшее значение данного столбца;
- MAX(col_name) — возвращает наибольшее значение данного столбца.
Вложенные подзапросы
Вложенные подзапросы — это SQL-запросы, которые включают выражения SELECT , FROM и WHERE , вложенные в другой запрос.
Пример
Найдём курсы, которые преподавались осенью 2009 и весной 2010 годов:
SELECT DISTINCT course_id FROM section WHERE semester = ‘Fall’ AND year= 2009 AND course_id IN ( SELECT course_id FROM section WHERE semester = ‘Spring’ AND year= 2010 ); SQL-запросы: базовые команды и примеры
Чтобы создать таблицу, используют команду CREATE TABLE, новую запись — INSERT.
Анастасия Хамидулина
Автор статьи
14 июня 2022 в 15:01
SQL — язык структурированных запросов. Его создали в 1974 году, чтобы хранить и обрабатывать данные. Все реляционные СУБД — системы управления базами данных — используют его в качестве препроцессора для обработки команд. Сами же базы данных представляют наборы таблиц, где запись — это строка.
SQL в работе используют разработчики и тестировщики, чтобы улучшать сайт или приложение через грамотную работу с базами данных. Тестировщики таким образом помогают бизнесу принимать эффективные решения на основе данных. Маркетологи — глубже анализировать поведение пользователей.
Инженер-тестировщик: новая работа через 9 месяцев
Получится, даже если у вас нет опыта в IT
Виды SQL-запросов
Ключевые слова этого языка делят на четыре логические группы.
1️⃣ DDL
Data Definition Language — язык определения данных. В него входят ключевые слова CREATE, DROP, RENAME и другие, которые относят к определению и манипулированию структурой базы данных. Их используют, чтобы создавать базы данных и описывать структуру, устанавливать, как размещать данные.
2️⃣ DML
Data Manipulation Language — язык манипулирования данными. В этой группе — запросы SELECT, INSERT, UPDATE, DELETE и другие. Их используют, чтобы изменять, получать, обновлять и удалять данные из базы.
3️⃣ DCL
Data Control Language — язык управления данными. К этой группе относят запросы разрешений, прав и различных ограничивающих доступ настроек. Например, GRANT или DENY.
4️⃣ TCL
Transaction Control Language — язык управления транзакциями. В эту группу входят все запросы, которые относят к управлению транзакциями и их жизненными циклами. Например, BEGIN TRANSACTION, ROLLBACK TRANSACTION, COMMIT TRANSACTION.
Особенности языка изучите на курсе «Аналитик данных». Под руководством наставников научитесь делать таблицы и составлять запросы для анализа. Сможете соединять и обрабатывать несколько таблиц, использовать оконные функции.
Структура SQL-запросов
https://sky.pro/media/sql-podzaprosy-rukovodstvo-po-ispolzovaniyu/
Запросы на языке SQL последовательны: логика их составления почти не отличается от обычного предложения. Например, вы хотите отфильтровать записи, чтобы получить только те, где в первом столбце значение равно единице. А после получить значения второго и третьего столбцов в выборке. Предложение на такую команду будет следующее:
Выбрать Столбец2, Столбец3 из Таблица1, где Столбец1 равен одному
На SQL это выглядит похоже:
SELECT (Column2, Column3) FROM Table1 WHERE Column1 = 1
Простые запросы SQL
Ключевые слова
Их используют, чтобы составить запросы:
Это ключевое слово отфильтровывает записи. Мы использовали его в абстрактном примере:
SELECT (Column2, Column3) FROM Table1 WHERE Column1 = 1
Группирует записи выборки по значениям указанных столбцов. Это ключевое слово должно следовать после WHERE.
✔️ AND, OR и BETWEEN
AND или OR расширяют выборку, создаваемую с помощью WHERE. Либо сужают ее, если указать дополнительные значения. Ключевое слово BETWEEN позволяет указать диапазон значений, чтобы создать выборку.
Лимитирует количество значений выборки. Например, по указанным фильтрам получено 100 значений, а нужны только первые 10. Тогда применяют синтаксис LIMIT 10.
На курсе «Анализ данных» несколько уроков посвящены тому, чтобы делать простые запросы на выборку, фильтрацию и сортировку данных, очищать и подготавливать их для анализа. На практике научитесь составлять разные комбинации, чтобы решать реальные задачи. Создадите проекты для портфолио, а если успешно окончите курс, получите диплом установленного образца.
Команды
С них начинаются запросы.
Предположим, нам необходимо создать базу данных, чтобы хранить информацию о прочитанных книгах, извлекать и изменять данные. В примерах мы будем использовать самую простую СУБД — sqlite3 в среде Linux. Создайте базу данных командой sqlite3 demo.db — и сразу попадете в командную строку программы:
sqlite3 demo.db SQLite version 3.27.2 2019-02-25 16:06:06 Enter ".help" for usage hints. sqlite>
�� CREATE TABLE
Чтобы создать таблицу, используют команду CREATE TABLE. Если создаете таблицу с прочитанными книгами, вероятно, понадобятся три столбца: id, название и автор.
sqlite> CREATE TABLE Books (id INTEGER PRIMARY KEY, title CHAR(255), author CHAR(255)); sqlite> .tables Books
Команда .tables отображает список таблиц.
�� INSERT
Команда создает новые записи. Добавим три книги в нашу таблицу:
sqlite> INSERT INTO Books(title, author) VALUES . > ("Язык SQL", "Неизвестный автор"), . > ("SQL. Сборник рецептов", "Энтони Молинаро"), . > ("Книга №3", "Без автора");
Указываем, в какие столбцы нужно вставить данные, игнорируя столбец id: он помечен как первичный ключ. Будет автоматически инкрементироваться, генерируя уникальные значения. В примере вставляем несколько записей за один запрос.
�� SELECT
Извлекает записи из таблицы:
sqlite> SELECT * FROM Books; 1|Язык SQL|Неизвестный автор 2|SQL. Сборник рецептов|Энтони Молинаро 3|Книга №3|Без автора
Каждая запись будет на новой строке, а значения столбцов — разделены вертикальной линией. Если, например, нужны не все, а определенные столбцы, то звездочку замените на названия столбцов через запятую:
sqlite> SELECT title, author FROM Books; Язык SQL|Неизвестный автор SQL. Сборник рецептов|Энтони Молинаро Книга №3|Без автора
�� UPDATE
Изменяет существующие записи. Чтобы использовать эту команду, укажите уникальный идентификатор изменяемой записи. Либо характеристику, по которой можно получить одну запись или группу из нескольких записей. Обновим авторов у первой и последней записи:
sqlite> UPDATE Books . > SET author = "Unknown" . > WHERE OR sqlite> SELECT title, author FROM Books; Язык SQL|Unknown SQL. Сборник рецептов|Энтони Молинаро Книга №3|Unknown
�� DELETE
Удаляет записи из таблицы по поисковому запросу. Удалим книгу с id, равным двум:
sqlite> DELETE FROM Books WHERE sqlite> SELECT * FROM Books; 1|Язык SQL|Unknown 3|Книга №3|Unknown
�� DROP TABLE
Удаляет таблицы из базы данных. Создадим и удалим демонстрационную таблицу:
sqlite> CREATE TABLE Demo (id INTEGER PRIMARY KEY, text TEXT); sqlite> sqlite> .tables Books Demo sqlite> sqlite> DROP TABLE Demo; sqlite> sqlite> .tables Books
�� ALTER TABLE
Команда в сочетании с другими ключевыми словами изменяет названия таблиц или добавляет новые столбцы. Изменим название нашей таблицы Books:
sqlite> ALTER TABLE Books RENAME TO MyBooks; sqlite> sqlite> .tables MyBooks
Добавим в нее новый столбец is_finished с булевым значением:
sqlite> ALTER TABLE MyBooks ADD COLUMN is_finished BOOLEAN; sqlite> UPDATE MyBooks . > SET is_finished = True; sqlite> sqlite> SELECT * FROM MyBooks; 1|Язык SQL|Unknown|1 3|Книга №3|Unknown|1
Агрегатные функции
Их используют, чтобы проводить дополнительные вычисления внутри полученной выборки:
✔️ COUNT(название_столбца) — возвращает количество строк выборки, где значение столбца не NULL.
✔️ SUM(название_столбца) — вычисляет и возвращает сумму значений в указанном столбце.
✔️ AVG(название_столбца) — вычисляет и возвращает среднее значение по столбцу.
✔️ MIN(название_столбца) — возвращает наименьшее значение для указанного столбца.
✔️ MAX(название_столбца) — возвращает наибольшее значение указанного столбца.
Научиться работе со всеми видами агрегатных функций можно на курсе «Анализ данных». Сможете взаимодействовать с синтаксисом и операторами для создания, модификации и удаления таблиц в SQL. В конце обучения у вас будет резюме, портфолио и диплом о профессиональной переподготовке. Это поможет устроиться на хорошую работу.
Вложенные подзапросы
Это SQL-запрос внутри другого SQL-запроса. Подзапросы помогают, если выборку фильтруют по значениям, которые тоже можно отфильтровать. Например, получим названия футбольных команд — участников соревнований с 2010 по 2020 годы:
SELECT DISTINCT club_name FROM clubs WHERE game_year = 2010 AND club_id IN (SELECT club_id FROM clubs WHERE game_year = 2020 );
Ключевое слово DISTINCT убирает из выборки дублирующиеся результаты.