SQL — очень востребованный навык в области науки о данных. Прежде чем углубляться в SQL, важно понять, почему он является неотъемлемой частью этой области.

SQL — наиболее широко используемый язык во всем мире, и он остается жизненно важным в науке о данных, поскольку данные лежат в его основе. Чтобы проанализировать данные, мы должны сначала извлечь их из баз данных, где в игру вступает SQL. Более того, чистые данные необходимы для обучения моделей машинного обучения, что делает SQL бесценным инструментом.

Как подчеркивает Эндрю Нг, хотя модели и код значительно усовершенствовались, обеспечение качества и удобства использования данных теперь является основной задачей.

Мы изучим SQL более подробно, а также различные SQL-запросы для науки о данных. Но сначала очень важно понять базы данных, поскольку они составляют основу науки о данных. Без баз данных наука о данных не имеет своей фундаментальной структуры.

1. Концепции базы данных

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

«База данных — это, по сути, совокупность взаимосвязанных данных, а система баз данных — это компьютерная система учета».

1.1 Назначение баз данных

Основная цель базы данных — хранить данные, необходимые для нужд обработки данных организации. Эти данные должны быть точными, конфиденциальными и защищенными от повреждения. Кроме того, он должен быть организован таким образом, чтобы его могли эффективно использовать различные приложения с различными требованиями к данным.

Традиционные системы обработки файлов хранят постоянные записи в нескольких файлах, а за извлечение и добавление записей в эти файлы отвечают различные прикладные программы. Однако этот подход имеет ограничения, включая избыточность данных, несогласованность и проблемы безопасности. Система управления базами данных (СУБД) решает эти проблемы, обеспечивая централизованный контроль данных.

Преимущества системы баз данных:

(i) Значительно снижает избыточность данных.

(ii) Контролирует несогласованность данных.

(iii) Облегчает обмен данными.

(iv) Обеспечивает безопасность данных.

(v) Поддерживает целостность данных.

Модели данных. Для управления базами данных используются три основные модели данных: реляционная модель данных, иерархическая модель данных и сетевая модель данных. Мы сосредоточимся на реляционной модели данных, поскольку она важна.

1.2 Реляционная модель

Реляционная модель, разработанная Эдгаром Фрэнком «Тедом» Коддом из IBM, является фундаментальной концепцией систем управления базами данных (СУБД). Он стал основой для современных систем баз данных, таких как MS SQL Server, IBM DB2, Oracle, MySQL и PostgreSQL.

В двух словах, отношение в реляционной модели представлено в виде таблицы, данные в которой организованы в строки и столбцы.

Свойства отношения:

(i) Все строки различны; в каждом столбце нет двух одинаковых строк.

(ii) Порядок строк внутри отношения не имеет значения; нет предопределенного порядка строк.

(iii) Столбцы в отношении имеют разные имена, и порядок столбцов неважен.

(iv) Каждый столбец в строке должен иметь атомарное (неделимое) значение, и столбец не может иметь более одного значения для данной строки.

Терминология:

Строки в отношении называются кортежами.

Столбцы в отношении называются атрибутами.

Количество атрибутов в отношении — это его степень, а количество строк — его мощность.

Понимание этих фундаментальных концепций баз данных и реляционной модели обеспечивает прочную основу для углубления в SQL.

2. Язык структурированных запросов (SQL)

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

«По сути, SQL — это простой язык запросов, используемый для доступа, манипулирования и управления данными в реляционных базах данных».

2.1 Возможности обработки SQL

SQL включает в себя несколько возможностей обработки:

1. Язык определения данных (DDL): DDL предоставляет команды для определения схем отношений, удаления отношений, создания индексов и изменения схем отношений.

2.Язык интерактивного манипулирования данными (DML): DML включает язык запросов, основанный на реляционной алгебре и реляционном исчислении кортежей. Он также включает команды для вставки, удаления и изменения кортежей в базе данных.

3. Определение представления. DDL включает команды для определения представлений.

4. Авторизация: команды DDL определяют права доступа к отношениям и представлениям.

5. Целостность. SQL предлагает ограниченную проверку целостности с возможными улучшениями в будущих продуктах и ​​стандартах.

6. Управление транзакциями. SQL включает команды для указания начала и окончания транзакций, а также для управления обработкой транзакций.

2.2 Язык определения данных (DDL)

DDL предоставляет определения для указания структуры хранения и методов доступа, используемых системой базы данных. Идеальный DDL должен:

1. Определите подразделения данных, такие как элементы данных, сегменты, записи и файлы базы данных.

2. Присвойте уникальные имена типам элементов данных, типам записей, типам файлов, базам данных и другим подразделениям данных.

3. Определите связи между типами записей для создания структур.

4. Укажите длину элементов данных.

5. Установите блокировку конфиденциальности, чтобы предотвратить несанкционированное чтение и изменение данных.

2.3 Язык манипулирования данными (DML)

После того как схема базы данных определена и создана, манипулирование данными включает извлечение, вставку, удаление и изменение информации с использованием специального языка, известного как язык манипулирования данными (DML).

2.4 Обработка SQL

SQL адаптирован для реляционных баз данных и работает с целыми группами таблиц как с отдельными объектами. Он может обрабатывать любое количество данных, извлеченных или полученных из этих таблиц, как единую единицу. SQL состоит из трех подъязыков:

  • Язык определения данных (DDL): описан в разделе 2.2.
  • Язык манипулирования данными (DML): описан в разделе 2.3.
  • Язык управления данными: используется для управления доступом к базе данных и необходим для безопасности системы.

Доступны различные базы данных SQL, такие как MS SQL Server, MySQL, Oracle, PostgreSQL, IBM DB2 и другие, причем PostgreSQL является предпочтительным выбором для некоторых пользователей.

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

PostgreSQL

PostgreSQL, часто называемый Postgres, представляет собой мощную систему управления базами данных с открытым исходным кодом, предназначенную для приложений корпоративного класса. Он поддерживает как SQL, так и JSON для реляционных и нереляционных запросов, обеспечивая расширяемость и соответствие SQL. PostgreSQL предоставляет расширенные типы данных и функции оптимизации производительности, которые обычно встречаются в дорогих коммерческих базах данных, таких как Oracle и SQL Server.

Преимущества PostgreSQL:

  • Может запускать динамические веб-сайты и веб-приложения в качестве опции стека LAMP.
  • Использует журналирование с упреждающей записью для обеспечения высокой отказоустойчивости.
  • Исходный код PostgreSQL доступен бесплатно по лицензии с открытым исходным кодом, что обеспечивает гибкость настройки.
  • Это удобно и не требует длительного обучения.
  • Подходит для минимального обслуживания и администрирования как во встроенных, так и в корпоративных сценариях использования.

В этом разделе основное внимание уделяется синтаксису PostgreSQL, который применим к большинству основных механизмов SQL, таких как MS SQL Server, MySQL, Oracle и т. д.

3. Различные команды и функции SQL

SQL предоставляет предопределенный набор команд для работы с реляционными базами данных. Прежде чем обсуждать эти команды, давайте ознакомимся с соглашениями и терминологией SQL:

  • Ключевые слова: слова со специальным значением в SQL, служащие инструкциями. Ключевые слова обычно пишутся заглавными буквами.
  • Команды (операторы): инструкции, передаваемые базе данных SQL, состоящие из одной или нескольких логически различных частей, называемых предложениями. Предложения начинаются с ключевого слова и состоят из ключевых слов и аргументов. Примеры: «ОТ продаж» и «ГДЕ значение = 1500,00».
  • Аргументы. Они дополняют или изменяют значение предложения. В «FROM sales» «продажи» — это аргумент, а «FROM» — ключевое слово предложения FROM.
  • Объекты. Структуры в базе данных с именами, хранящимися в памяти. Объекты включают базовые таблицы, представления и индексы.

3.1 Создание баз данных и таблиц

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

Типы данных:

  • Существуют различные типы данных, в том числе логические (истина или ложь), символьные (char, varchar, текст), числовые (целое число, число с плавающей запятой), временные (дата, время, временная метка, интервал), UUID (универсальные уникальные идентификаторы), Массив (хранит массив строк, чисел и т. д.) и JSON.

«При создании баз данных и таблиц следует тщательно продумать, какие типы данных следует использовать для хранения данных».

Например. Представьте, что мы хотим сохранить номер телефона. Должен ли он храниться в числовом виде? Если да, то какой числовой тип?

Учитывая ограничения, вы можете подумать, что имеет смысл хранить его как тип данных BIGINT, но на самом деле нам следует подумать, что лучше всего подходит для данной ситуации. Зачем вообще беспокоиться о числовых значениях?

-› Мы не выполняем арифметические операции с числами, поэтому, вероятно, имеет смысл использовать тип данных VARCHAR.

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

вы всегда можете просмотреть документацию по различным вариантам… https://www.postgresql.org/docs/9.5/datatype

Первичный ключ:

  • Первичный ключ — это столбец или группа столбцов, которые используются для уникальной идентификации строки в таблице. Это необходимо для объединения таблиц.

Здесь я привожу пример «базы данных dvdrental»,вы также можете скачать ее отсюда и выполнять различные запросы к этой базе данных.

👉 https://www.postgresqltutorial.com/postgresql-sample-database/ 👈

пример первичного ключа,

вы можете заметить сверху, что первичный ключ является целым числом и уникальным.

Внешний ключ:

  • Внешний ключ — это поле или группа полей в таблице, которые однозначно идентифицируют строку в другой таблице. Он определен в таблице, которая ссылается на первичный ключ другой таблицы. Таблица, содержащая внешний ключ, является ссылочной таблицей (или дочерней таблицей), а таблица, на которую делается ссылка, является ссылочной таблицей (или родительской таблицей).

Помня об этих концепциях, мы можем перейти к созданию таблиц и работе с базами данных PostgreSQL.

3.2 Команда CREATE TABLE

Чтобы определить таблицы, вы используете команду CREATE TABLE. При создании таблицы вы указываете имена столбцов, типы данных и размеры для каждого столбца. В каждой таблице должен быть хотя бы один столбец. Синтаксис команды CREATE TABLE следующий:

CREATE TABLE <table-name> (
 <column name> <data type> [(size)],
 <column name> <data type> [(size)] …
);

Например:

CREATE TABLE employees (
 employee_id INT,
 first_name VARCHAR(50),
 last_name VARCHAR(50),
 salary DECIMAL(10, 2)
);

Эта команда создает пустую таблицу с указанными именами столбцов, типами данных и размерами.

3.2(A) Ограничения

Ограничения — это условия или проверки, применяемые к полям. При создании таблицы вы можете наложить ограничения на значения, разрешенные в ее полях. SQL будет отклонять любые значения, нарушающие эти критерии. Существует два основных типа ограничений: ограничения столбцов (применяются к отдельным столбцам) и ограничения таблиц (применяются к группам из одного или нескольких столбцов).

Вот расширенный синтаксис команды CREATE TABLE с ограничениями:

sql
CREATE TABLE <table name> (
 <column name> <data type> [(size)] <column constraints>,
 <column name> <data type> [(size)] <column constraints>,
 …
 <table constraint> (column name, [<column name …] ) …
);

Некоторые часто используемые ограничения включают в себя:

  • NOT NULLОграничение: гарантирует, что столбец не может иметь значение NULL.
  • Ограничение UNIQUE: гарантирует, что все значения в столбце различны.
  • ПЕРВИЧНЫЙ КЛЮЧ: уникально идентифицирует каждую строку в таблице базы данных.
  • FOREIGN KEY: ограничивает данные на основе столбцов в других таблицах.
  • ПРОВЕРКА Ограничение: гарантирует, что все значения в столбце соответствуют определенным условиям.
  • Ограничение DEFAULT: автоматически вставляет определенное значение по умолчанию, если для столбца не указано значение.

Пример:

CREATE TABLE students (
 student_id SERIAL PRIMARY KEY,
 first_name VARCHAR(50) NOT NULL,
 last_name VARCHAR(50) NOT NULL,
 age INT CHECK (age >= 18),
 grade CHAR DEFAULT ‘A’
);

В этом примере создается таблица «студент» с различными ограничениями и значениями по умолчанию.

3.3 Команда INSERT

Команда INSERT позволяет добавлять строки в таблицу. Общий синтаксис следующий:

INSERT INTO table (column1, column2, …)
VALUES
 (value1, value2, …),
 (value1, value2, …),
 …;

Например:

INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES
 (1, ‘John’, ‘Doe’, 50000),
 (2, ‘Jane’, ‘Smith’, 60000);

Эта команда вставляет строки с указанными значениями в таблицу «сотрудники».

Вы также можете вставить значения из другой таблицы с помощью оператора SELECT:

INSERT INTO table (column1, column2, …)
SELECT column1, column2, …
FROM another_table
WHERE condition;

Пример:

INSERT INTO new_students (first_name, last_name)
SELECT first_name, last_name
FROM old_students
WHERE age >= 18;

При этом значения из таблицы «old_students» вставляются в таблицу «new_students» на основе условия.

Столбцы, не перечисленные в команде INSERT, будут иметь значения по умолчанию, если они определены; в противном случае они будут NULL.

«Столбец, который не указан в команде INSERT, будет иметь значение по умолчанию, если для них определено в противном случае значение NULL»

3.4 Команда ОБНОВИТЬ

Команда UPDATE используется для изменения значений в столбцах таблицы. Его общий синтаксис:

UPDATE table
SET column1 = value1,
 column2 = value2,
 …
WHERE condition;

Например:

3.5 Команда DELETE

Команда DELETE удаляет строки из таблицы. Общий синтаксис:

DELETE FROM <table_name>
WHERE <condition>;

Например:

3.6 Команда ALTER TABLE

Команда ALTER TABLE позволяет вносить изменения в существующую структуру таблицы. Вы можете использовать его для таких задач, как добавление, удаление или переименование столбцов, изменение типа данных столбца, установка значений по умолчанию, добавление ограничений CHECK и даже переименование самой таблицы. Вот общий синтаксис ALTER TABLE:

ALTER TABLE <table_name> <action>;

Некоторые общие действия включают в себя:

  • Добавление столбцов:
ALTER TABLE <table_name> ADD COLUMN <new_col> <TYPE>;
  • Удаление столбцов:
ALTER TABLE <table_name> DROP COLUMN <col_name>;
  • Изменение ограничений:
ALTER TABLE <table_name> ALTER COLUMN <col_name> SET DEFAULT <value>;

В предоставленных примерах вы можете увидеть, как выполнить эти действия над существующей таблицей.

В этой статье представлены понятия баз данных, SQL и создания таблиц. Здесь также рассматриваются важные команды SQL для изменения и запроса данных. В следующей статье мы рассмотрим более сложные SQL-запросы для различных задач управления базами данных. Если этот пост оказался для вас полезным, выразите свою признательность аплодисментами! 👏 Спасибо, что прочитали.