Метка: SQL

Опубликовано

Супербестселлер! “SQL. Курс молодого бойца”

SQL. Курс молодого бойца

Изложены основы работы с реляционными базами данных и практическое применение SQL в наиболее востребованных сценариях. Рассмотрены принципы хранения данных, структура таблиц и методы извлечения информации. Подробно описаны операции выборки, фильтрации, сортировки, группировки и агрегации, работа с текстовыми и числовыми данными, обработка дат, использование выражений и условий. Последовательно разобраны механизмы соединения таблиц, особенности различных типов JOIN и способы объединения выборок. Описано создание и изменение таблиц, настройка ограничений, индексов и автоинкрементных полей, а также принципы добавления, обновления и удаления данных. Приведена справочная информация о типах данных, порядке выполнения запросов, планах и статистике, транзакциях и требованиях ACID, а также основах клиент-серверной архитектуры.

Для начинающих специалистов по работе с данными, тестировщиков ПО и специалистов технической поддержки

От автора бестселлера «Что такое тестирование. Курс молодого бойца»

Ольга Назина —  автора бестселлера
«Что такое тестирование. Курс молодого бойца»!

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

Художники

Об авторе

 

Назина Ольга

Ольга Назина — генеральный директор обучающего центра ООО «Тестбейз». Тестировщик, тренер и писатель. Автор статей на «Хабре» и бесплатных уроков на YouTube. Создала порталы Багред («Главред» для тестировщиков) и Testbase, на котором собраны полезные материалы по тестированию. Проработала в тестировании 17 лет, 10 из которых активно учила новичков. Провела более 200 курсов для тестировщиков, материал которых послужил основой для этой книги.

Книгу “SQL. Курс молодого бойца” можно купить в нашем интенет-магазине.

Опубликовано

Встречайте: “MySQL. Сборник рецептов. 4-е издание”

MySQL. Сборник рецептов. 4-е издание

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

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

Решения для разработчиков и администраторов

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

Если вам нужны быстрые решения, в этой книге вы найдете десятки коротких, практичных фрагментов кода, сотни рабочих примеров и четкие, лаконичные объяснения для программистов и администраторов, у которых нет времени разбираться в особенностях MySQL с нуля.

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

Вы научитесь:

  • Соединяться с сервером, выполнять запросы и обрабатывать результаты
  • Получать данные с сервера MySQL
  • Сохранять, извлекать и изменять строки
  • Работать с датами и временем
  • Сортировать результаты запросов и создавать сводки
  • Оценивать характеристики набора данных
  • Создавать хранимые функции и процедуры
  • Использовать хранимые подпрограммы, триггеры и запланированные события
  • Выполнять основные задачи по администрированию MySQL
  • Понимать основы мониторинга MySQL

Будь то простые вопросы о соединении с сервером MySQL или сложные проблемы, связанные с выбором правильной топологии репликации, эта книга придет вам на помощь. Света и Алкин делятся своим более чем десятилетним опытом, помогая сотням пользователей MySQL решать ежедневные задачи.
Хенрик Инго, руководитель отдела проектирования и разработки, компания DataStax

Книгу “MySQL. Сборник рецептов. 4-е издание” можно купить со скидкой в интернет-магазине издательства “БХВ“.

Отзывы………………………………………………………………………………………………….. 14

Вступительное слово………………………………………………………………………………. 15

Предисловие…………………………………………………………………………………………… 16

Для кого эта книга……………………………………………………………………………………………………………………… 18

Как построена эта книга…………………………………………………………………………………………………………… 18

Программные интерфейсы для MySQL, используемые в данной книге………………………………… 22

Примечания по версиям и платформам……………………………………………………………………………………. 22

Соглашения, принятые в этой книге…………………………………………………………………………………………. 23

Репозиторий GitHub…………………………………………………………………………………………………………………… 25

Получение MySQL и сопутствующего программного обеспечения……………………………………… 26

Использование примеров кода…………………………………………………………………………………………………. 28

Онлайн-обучение от издательской компании O’Reilly……………………………………………………………. 29

Как с нами связаться………………………………………………………………………………………………………………….. 29

Благодарности…………………………………………………………………………………………………………………………… 30

Глава 1. Работа с клиентской программой mysql……………………………………. 32

1.0. Введение………………………………………………………………………………………………………………………………. 32

1.1. Создание учетной записи пользователя MySQL……………………………………………………………… 33

1.2. Создание базы данных и тестовой таблицы…………………………………………………………………….. 36

1.3. Как найти клиент MySQL…………………………………………………………………………………………………… 37

1.4. Задание параметров команды для mysql………………………………………………………………………….. 39

1.5. Выполнение инструкций SQL в интерактивном режиме…………………………………………………. 44

1.6. Чтение запросов из файла или из программы…………………………………………………………………… 46

1.7. Управление направлением и форматом вывода mysql…………………………………………………….. 48

1.8. Использование в SQL-запросах переменных, определяемых пользователем……………….. 53

1.9. Настройка приглашения на ввод в mysql………………………………………………………………………….. 56

1.10. Использование внешних программ…………………………………………………………………………………. 58

1.11. Фильтрация и обработка вывода…………………………………………………………………………………….. 59

Глава 2. Работа с MySQL Shell……………………………………………………………….. 63

2.0. Введение………………………………………………………………………………………………………………………………. 63

2.1. Соединение с сервером MySQL при помощи MySQL Shell……………………………………………… 64

2.2. Выбор протокола………………………………………………………………………………………………………………… 67

2.3. Выбор режимов SQL, JavaScript или Python…………………………………………………………………….. 70

2.4. Выполнение сессий SQL…………………………………………………………………………………………………….. 70

2.5. Выполнение запросов SQL в режиме JavaScript………………………………………………………………. 71

2.6. Выполнение запросов SQL в режиме Python……………………………………………………………………. 74

2.7. Работа с таблицами в режиме JavaScript………………………………………………………………………….. 76

2.8. Работа с таблицами в режиме Python……………………………………………………………………………….. 78

2.9. Работа с коллекциями в режиме JavaScript………………………………………………………………………. 81

2.10. Работа с коллекциями в режиме Python………………………………………………………………………….. 84

2.11. Управление форматом вывода………………………………………………………………………………………… 86

2.12. Формирование отчетов с помощью MySQL Shell…………………………………………………………… 90

2.13. Работа с утилитами MySQL Shell……………………………………………………………………………………. 93

2.14. Использование Admin API для автоматизации управления репликацией…………………… 96

2.15. Работа с объектами JavaScript…………………………………………………………………………………………. 98

2.16. Заполнение базы данных тестовыми данными с помощью модулей Python
для Data Science………………………………………………………………………………………………………………. 102

2.17. Повторное использование сценариев для MySQL Shell………………………………………………. 112

Глава 3. Репликация MySQL………………………………………………………………… 115

3.0. Введение…………………………………………………………………………………………………………………………….. 115

3.1. Настройка базовой репликации между одним источником и одной репликой…………… 117

3.2. Репликация на основе позиции в новой среде………………………………………………………………… 118

3.3. Настройка репликации на основе позиции в активной среде………………………………………. 119

3.4. Настройка репликации на основе GTID…………………………………………………………………………. 121

3.5. Настройка формата бинарного журнала……………………………………………………………………….. 125

3.6. Использование фильтров репликации…………………………………………………………………………….. 126

3.7. Перезапись базы данных на реплике………………………………………………………………………………. 132

3.8. Параллельное выполнение событий (многопоточная реплика)…………………………………… 133

3.9. Настройка кольцевой репликации………………………………………………………………………………….. 135

3.10. Репликация из нескольких источников…………………………………………………………………………. 137

3.11. Использование подключаемого модуля полусинхронной репликации…………………….. 141

3.12. Использование групповой репликации…………………………………………………………………………. 144

3.13. Безопасное хранение учетных данных репликации……………………………………………………. 149

3.14. Использование TLS (SSL) для репликации…………………………………………………………………… 150

3.15. Устранение неполадок при репликации……………………………………………………………………….. 152

3.16. Использование списка процессов для понимания производительности
репликации………………………………………………………………………………………………………………………. 166

3.17. Настройка автоматической репликации………………………………………………………………………. 171

Глава 4. Создание программ для MySQL……………………………………………… 178

4.0. Введение…………………………………………………………………………………………………………………………….. 178

4.1. Соединение с сервером MySQL, выбор базы данных и отключение…………………………… 182

4.2. Обработка ошибок……………………………………………………………………………………………………………. 196

4.3. Создание библиотечных файлов…………………………………………………………………………………….. 205

4.4. Выполнение запросов и извлечение результатов………………………………………………………….. 219

4.5. Использование в запросах специальных символов и значений NULL…………………………. 234

4.6. Обработка специальных символов в идентификаторах……………………………………………….. 242

4.7. Обработка значений NULL в результирующих множествах………………………………………… 244

4.8. Способы получения параметров соединения………………………………………………………………… 250

4.9. Возвращение таблицы profile в исходное состояние…………………………………………………….. 259

Глава 5. Выбор записей………………………………………………………………………… 261

5.0. Введение…………………………………………………………………………………………………………………………….. 261

5.1. Задание столбцов и строк вывода…………………………………………………………………………………… 262

5.2. Присваивание имен столбцам вывода…………………………………………………………………………….. 264

5.3. Упорядочивание результирующего множества…………………………………………………………….. 268

5.4. Удаление повторяющихся строк……………………………………………………………………………………… 269

5.5. Обработка значений NULL………………………………………………………………………………………………. 270

5.6. Использование в программах операций сравнения с участием NULL…………………………. 273

5.7. Использование представлений для упрощения доступа к таблицам…………………………… 274

5.8. Выбор данных из нескольких таблиц……………………………………………………………………………… 275

5.9. Выбор строк из начала, конца или середины результирующего множества……………… 277

5.10. Что делать, если для LIMIT нужен “неправильный” порядок сортировки…………………. 280

5.11. Получение значений LIMIT из выражений……………………………………………………………………. 281

5.12. Объединение двух или более результатов SELECT…………………………………………………….. 282

5.13. Получение результатов подзапросов……………………………………………………………………………. 284

Глава 6. Управление таблицами…………………………………………………………… 286

6.0. Введение…………………………………………………………………………………………………………………………….. 286

6.1. Клонирование таблицы……………………………………………………………………………………………………. 286

6.2. Выбор результирующего множества в существующую таблицу………………………………… 287

6.3. Создание временных таблиц……………………………………………………………………………………………. 290

6.4. Формирование уникальных имен таблиц……………………………………………………………………….. 292

6.5. Проверка или изменение механизма хранения данных для таблиц…………………………….. 293

6.6. Копирование таблиц с использованием утилиты mysqldump……………………………………….. 294

6.7. Копирование таблицы InnoDB с использованием переносимых
табличных пространств……………………………………………………………………………………………………. 297

6.8. Копирование таблицы MyISAM с помощью файла sdi…………………………………………………. 299

Глава 7. Работа со строками…………………………………………………………………. 301

7.0. Введение…………………………………………………………………………………………………………………………….. 301

7.1. Свойства строк………………………………………………………………………………………………………………….. 301

7.2. Выбор типа строковых данных……………………………………………………………………………………….. 306

7.3. Настройка набора символов для обмена данными с клиентской программой…………… 308

7.4. Создание строковых литералов………………………………………………………………………………………. 311

7.5. Проверка или изменение набора символов или порядка расстановки символов для строки         314

7.6. Преобразование регистра букв строки…………………………………………………………………………… 316

7.7. Сравнение строковых значений………………………………………………………………………………………. 318

7.8. Преобразование между десятичным, восьмеричным и шестнадцатеричным форматами 321

7.9. Преобразование между ASCII, BIT и шестнадцатеричным форматами……………………… 322

7.10. Поиск по образцу с помощью шаблонов SQL………………………………………………………………. 325

7.11. Поиск по образцу с помощью регулярных выражений……………………………………………….. 328

7.12. Инвертирование строк……………………………………………………………………………………………………. 333

7.13. Проверка вхождения подстроки в строку…………………………………………………………………….. 334

7.14. Разбиение и объединение строк…………………………………………………………………………………….. 335

7.15. Поиск с помощью индекса FULLTEXT…………………………………………………………………………… 338

7.16. FULLTEXT-поиск и короткие слова……………………………………………………………………………….. 344

7.17. Включение и исключение слов из FULLTEXT-поиска………………………………………………….. 346

7.18. Поиск фразы при помощи индекса FULLTEXT……………………………………………………………… 347

Глава 8. Работа с датами и временем……………………………………………………. 349

8.0. Введение…………………………………………………………………………………………………………………………….. 349

8.1. Выбор временных типов данных…………………………………………………………………………………….. 350

8.2. Представление секунд в виде дроби……………………………………………………………………………….. 352

8.3. Изменение формата даты MySQL……………………………………………………………………………………. 355

8.4. Установка часового пояса клиента…………………………………………………………………………………. 358

8.5. Установка часового пояса сервера…………………………………………………………………………………. 360

8.6. Смещение временных значений при изменении часового пояса………………………………….. 362

8.7. Определение текущей даты или времени……………………………………………………………………….. 363

8.8. Использование TIMESTAMP или DATETIME для отслеживания времени изменения записей        364

8.9. Разбиение дат и времени на части…………………………………………………………………………………… 367

8.10. Синтез дат и времени с помощью функций извлечения составляющих…………………….. 372

8.11. Преобразование времени в более простые единицы и обратно…………………………………. 374

8.12. Вычисление интервалов между датами или значениями времени…………………………….. 378

8.13. Добавление значений даты или времени……………………………………………………………………… 382

8.14. Вычисление возраста……………………………………………………………………………………………………… 388

8.15. Нахождение первого и последнего дней месяца или длительности месяца……………… 389

8.16. Определение дня недели для даты………………………………………………………………………………… 392

8.17. Определение дат для дней текущей недели………………………………………………………………….. 392

8.18. Стандартизация не-совсем-ISO-строк…………………………………………………………………………… 395

8.19. Выбор записей по временным характеристикам…………………………………………………………. 397

Глава 9. Сортировка результатов запроса…………………………………………….. 401

9.0. Введение…………………………………………………………………………………………………………………………….. 401

9.1. Использование ORDER BY для сортировки результатов запроса………………………………… 402

9.2. Сортировка результатов выражения………………………………………………………………………………. 405

9.3. Сортировка одного набора значений и вывод другого…………………………………………………. 407

9.4. Сортировка и чувствительность к регистру…………………………………………………………………… 411

9.5. Сортировка в хронологическом порядке………………………………………………………………………… 414

9.6. Сортировка по подстрокам значений столбцов…………………………………………………………….. 418

9.7. Сортировка по подстрокам фиксированной длины………………………………………………………. 419

9.8. Сортировка по подстрокам переменной длины…………………………………………………………….. 422

9.9. Сортировка имен хостов по доменам……………………………………………………………………………… 426

9.10. Сортировка IP-адресов в числовом порядке………………………………………………………………… 429

9.11. Размещение некоторых значений в начале или в конце упорядоченного списка…….. 431

9.12. Сортировка в порядке, определенном пользователем………………………………………………… 434

9.13. Сортировка значений ENUM…………………………………………………………………………………………. 435

Глава 10. Формирование итогов…………………………………………………………… 439

10.0. Введение………………………………………………………………………………………………………………………….. 439

10.1. Суммирование с помощью функции COUNT()……………………………………………………………… 441

10.2. Суммирование при помощи функций MIN() и MAX()…………………………………………………… 443

10.3. Суммирование при помощи функций SUM() и AVG()…………………………………………………… 444

10.4. Использование ключевого слова DISTINCT для удаления дубликатов…………………….. 446

10.5. Создание представления для упрощения работы с итоговыми данными…………………. 448

10.6. Поиск значений, связанных с минимальным и максимальным значениями………………. 449

10.7. Управление чувствительностью к регистру функций MIN() и MAX()…………………………. 451

10.8. Разбиение итогов на подгруппы……………………………………………………………………………………. 453

10.9. Обработка значений NULL с помощью агрегирующих функций……………………………….. 457

10.10. Выбор групп только с определенными характеристиками………………………………………. 460

10.11. Устанавливаем уникальность значения……………………………………………………………………… 461

10.12. Группировка по результатам выражения…………………………………………………………………… 462

10.13. Классификация некатегориальных данных……………………………………………………………….. 463

10.14. Нахождение наибольшего и наименьшего из итоговых значений…………………………… 466

10.15. Итоги по датам……………………………………………………………………………………………………………… 468

10.16. Одновременная работа с итогами по группам и общим итогом………………………………. 470

10.17. Формирование отчета, содержащего итоговую информацию и список………………….. 473

10.18. Формирование итогов из временных результирующих множеств…………………………… 476

Глава 11. Использование хранимых подпрограмм, триггеров и запланированных событий      478

11.0. Введение………………………………………………………………………………………………………………………….. 478

11.1. Создание объектов с составными инструкциями…………………………………………………………. 480

11.2. Использование хранимых функций для упрощения вычислений………………………………. 482

11.3. Использование хранимых процедур для получения нескольких значений………………. 484

11.4. Использование триггеров для регистрации изменений в таблице……………………………… 486

11.5. Планирование действий в базе данных с помощью событий…………………………………….. 489

11.6. Разработка вспомогательных подпрограмм для выполнения
динамических запросов………………………………………………………………………………………………….. 491

11.7. Обнаружение ситуаций “no more rows” с помощью обработчиков
особых ситуаций…………………………………………………………………………………………………………….. 493

11.8. Перехват и игнорирование ошибок с помощью обработчиков особых ситуаций……. 494

11.9. Генерация ошибок и предупреждений………………………………………………………………………….. 495

11.10. Регистрация ошибок путем доступа к диагностической области……………………………. 497

11.11. Использование триггеров для предварительной обработки или отклонения данных 501

Глава 12. Работа с метаданными………………………………………………………….. 504

12.0. Введение………………………………………………………………………………………………………………………….. 504

12.1. Определение числа строк, обработанных запросом…………………………………………………… 505

12.2. Получение метаданных результирующего множества……………………………………………….. 509

12.3. Вывод списков таблиц и баз данных или проверка их существования…………………….. 519

12.4. Вывод списков представлений или проверка их существования……………………………….. 520

12.5. Получение определений столбцов таблицы………………………………………………………………… 521

12.6. Получение информации о столбцах ENUM и SET……………………………………………………….. 526

12.7. Получение метаданных сервера……………………………………………………………………………………. 528

12.8. Создание приложений, адаптирующихся к версии сервера MySQL…………………………. 529

12.9. Обнаружение дочерних таблиц, которые ссылаются на определенную таблицу через ограничения внешнего ключа………………………………………………………………………………………………………………………………………….. 531

12.10. Получение списка триггеров……………………………………………………………………………………….. 533

12.11. Получение списка хранимых подпрограмм и запланированных событий…………….. 534

12.12. Получение списка подключаемых модулей………………………………………………………………. 536

12.13. Получение списка наборов символов и порядков расстановки……………………………….. 537

12.14. Получение списка ограничений CHECK…………………………………………………………………….. 541

Глава 13. Импорт и экспорт данных…………………………………………………….. 542

13.0. Введение………………………………………………………………………………………………………………………….. 542

13.1. Импорт с помощью LOAD DATA и утилиты mysqlimport……………………………………………… 545

13.2. Указание разделителей полей и строк………………………………………………………………………….. 549

13.3. Использование кавычек и специальных символов………………………………………………………. 551

13.4. Обработка дубликатов индексированных записей……………………………………………………… 552

13.5. Диагностика при ошибках во входных данных…………………………………………………………… 553

13.6. Пропуск строк в файле данных……………………………………………………………………………………… 555

13.7. Определение порядка ввода столбцов………………………………………………………………………….. 555

13.8. Предварительная обработка входных значений, помещаемых в базу данных………… 556

13.9. Пропуск столбцов файла данных………………………………………………………………………………….. 558

13.10. Импорт файлов в формате CSV……………………………………………………………………………………. 559

13.11. Экспорт результатов запроса из MySQL……………………………………………………………………. 560

13.12. Импорт и экспорт значений NULL……………………………………………………………………………….. 562

13.13. Экспорт данных в SQL-формат……………………………………………………………………………………. 564

13.14. Импорт данных SQL……………………………………………………………………………………………………… 565

13.15. Экспорт результатов запроса в XML………………………………………………………………………….. 566

13.16. Импорт XML в MySQL…………………………………………………………………………………………………. 568

13.17. Импорт данных в формате JSON…………………………………………………………………………………. 569

13.18. Импорт данных из MongoDB……………………………………………………………………………………….. 571

13.19. Экспорт данных в формате JSON………………………………………………………………………………… 572

13.20. Разработка структуры таблицы на основе файла данных……………………………………….. 573

Глава 14. Проверка и изменение формата данных……………………………….. 576

14.0. Введение………………………………………………………………………………………………………………………….. 576

14.1. Использование режима SQL для отклонения неверных входных значений……………… 577

14.2. Использование ограничений CHECK для отклонения недопустимых значений………. 579

14.3. Использование триггеров для отклонения входных значений……………………………………. 581

14.4. Создание цикла обработки ввода…………………………………………………………………………………. 583

14.5. Размещение часто выполняемых проверок в библиотеке…………………………………………… 584

14.6. Проверка корректности. Сравнение с образцом………………………………………………………….. 586

14.7. Образцы для широкой классификации…………………………………………………………………………. 589

14.8. Образцы для числовых значений………………………………………………………………………………….. 589

14.9. Образцы для дат и времени……………………………………………………………………………………………. 591

14.10. Образцы для адресов электронной почты и URL………………………………………………………. 594

14.11. Проверка корректности при помощи метаданных таблицы…………………………………….. 595

14.12. Проверка корректности при помощи справочной таблицы……………………………………… 599

14.13. Преобразование двузначных значений года в четырехзначные……………………………… 601

14.14. Проверка корректности составляющих даты и времени…………………………………………… 603

14.15. Создание утилит для обработки дат…………………………………………………………………………… 605

14.16. Импорт не-ISO значений дат……………………………………………………………………………………….. 609

14.17. Экспорт не-ISO значений дат………………………………………………………………………………………. 611

14.18. Предварительная обработка и импорт файла……………………………………………………………. 612

Глава 15. Формирование и использование последовательностей…………. 614

15.0. Введение………………………………………………………………………………………………………………………….. 614

15.1. Создание последовательности со столбцами AUTO_INCREMENT……………………………. 615

15.2. Выбор типа для столбца последовательности…………………………………………………………….. 618

15.3. Удаление записей и формирование последовательности…………………………………………… 620

15.4. Извлечение значений последовательности………………………………………………………………….. 622

15.5. Перенумерация существующей последовательности…………………………………………………. 626

15.6. Расширение диапазона последовательности………………………………………………………………. 628

15.7. Повторное использование последних значений последовательности………………………. 629

15.8. Управление изменением нумерации строк…………………………………………………………………… 630

15.9. Добавление последовательности в существующую таблицу…………………………………….. 631

15.10. Управление несколькими столбцами AUTO_INCREMENT одновременно………………. 632

15.11. Использование значений AUTO_INCREMENT для связывания таблиц……………………. 633

15.12. Генераторы однострочных последовательностей……………………………………………………. 636

15.13. Формирование повторяющихся последовательностей…………………………………………….. 639

15.14. Использование произвольных значений приращения………………………………………………. 640

15.15. Использование оконных функций для нумерации строк в результирующем множестве……….. 643

15.16. Генерация последовательностей с помощью рекурсивных обобщенных табличных выражений          644

15.17. Создание и хранение нестандартных последовательностей…………………………………… 647

Глава 16. Использование соединений и подзапросов……………………………. 654

16.0. Введение………………………………………………………………………………………………………………………….. 654

16.1. Нахождение строк одной таблицы, соответствующих строкам другой……………………. 655

16.2. Нахождение строк, которым не соответствуют никакие строки другой таблицы……. 663

16.3. Выявление и удаление несвязанных записей……………………………………………………………….. 668

16.4. Сравнение таблицы с самой собой……………………………………………………………………………….. 671

16.5. Вывод списков для записей “главная-подчиненная” и итогов……………………………………. 675

16.6. Отношение “многие-ко-многим”…………………………………………………………………………………….. 679

16.7. Нахождение строк с минимальным и максимальным значением в группе……………….. 683

16.8. Заполнение или обнаружение пустых мест в списке с помощью соединений………….. 686

16.9. Управление порядком вывода запроса с помощью соединения………………………………… 689

16.10. Объединение результатов нескольких запросов……………………………………………………….. 691

16.11. Обращение в программах к именам столбцов вывода, полученных при соединении таблиц   693

Глава 17. Статистические методы………………………………………………………… 695

17.0. Введение………………………………………………………………………………………………………………………….. 695

17.1. Получение описательных статистических показателей…………………………………………….. 695

17.2. Групповые статистические показатели………………………………………………………………………… 699

17.3. Получение частотного распределения…………………………………………………………………………. 701

17.4. Подсчет отсутствующих значений……………………………………………………………………………….. 704

17.5. Вычисление линейной регрессии и коэффициентов корреляции……………………………….. 706

17.6. Генерация случайных чисел………………………………………………………………………………………….. 709

17.7. Рандомизация набора строк………………………………………………………………………………………….. 710

17.8. Случайный выбор из набора строк………………………………………………………………………………. 713

17.9. Вычисление разности между последовательными строками…………………………………….. 715

17.10. Нарастающий итог и скользящее среднее………………………………………………………………….. 717

17.11. Присваивание рангов……………………………………………………………………………………………………. 721

17.12. Вычисление рейтинга команд……………………………………………………………………………………… 725

Глава 18. Обработка повторяющихся записей……………………………………… 731

18.0. Введение………………………………………………………………………………………………………………………….. 731

18.1. Предотвращение появления дубликатов в таблице…………………………………………………….. 732

18.2. Несколько уникальных ключей в таблице……………………………………………………………………. 734

18.3. Обработка дубликатов на этапе создания записи………………………………………………………. 735

18.4. Подсчет и выявление дубликатов………………………………………………………………………………….. 740

18.5. Удаление дубликатов из таблицы…………………………………………………………………………………. 743

Глава 19. Работа с JSON………………………………………………………………………. 748

19.0. Введение………………………………………………………………………………………………………………………….. 748

19.1. Выбор правильного типа данных…………………………………………………………………………………. 749

19.2. Вставка значений JSON………………………………………………………………………………………………….. 749

19.3. Проверка соответствия стандарту JSON………………………………………………………………………. 750

19.4. Форматирование значений JSON…………………………………………………………………………………… 754

19.5. Извлечение значений из документа JSON…………………………………………………………………….. 755

19.6. Поиск в документах JSON………………………………………………………………………………………………. 757

19.7. Вставка новых элементов в документ JSON…………………………………………………………………. 758

19.8. Обновление значений JSON……………………………………………………………………………………………. 760

19.9. Удаление элементов из документа JSON………………………………………………………………………. 761

19.10. Объединение нескольких документов JSON в один…………………………………………………… 761

19.11. Преобразование реляционных данных в формат JSON…………………………………………….. 765

19.12. Преобразование формата JSON в реляционный формат…………………………………………… 768

19.13. Исследование структуры данных JSON……………………………………………………………………… 770

19.14. Работа с JSON в MySQL как с хранилищем документов…………………………………………… 772

Глава 20. Выполнение транзакций………………………………………………………. 780

20.0. Введение………………………………………………………………………………………………………………………….. 780

20.1. Выбор механизма хранения, который поддерживает транзакции…………………………….. 781

20.2. Выполнение транзакций средствами SQL……………………………………………………………………. 782

20.3. Выполнение транзакций в программах………………………………………………………………………… 784

20.4. Использование транзакций в программах на Perl………………………………………………………… 786

20.5. Использование транзакций в программах на Ruby…………………………………………………….. 787

20.6. Использование транзакций в программах на PHP………………………………………………………. 788

20.7. Использование транзакций в программах на Python………………………………………………….. 789

20.8. Использование транзакций в программах на Go…………………………………………………………. 790

20.9. Обработка транзакций в Go с помощью функций, использующих контекст…………….. 791

20.10. Использование транзакций в программах на Java……………………………………………………. 794

Глава 21. Производительность запросов………………………………………………. 796

21.0. Введение………………………………………………………………………………………………………………………….. 796

21.1. Создание индексов………………………………………………………………………………………………………….. 797

21.2. Суррогатный первичный ключ……………………………………………………………………………………… 799

21.3. Поддержание индексов…………………………………………………………………………………………………… 801

21.4. Как принять решение об использовании индекса в запросе……………………………………….. 803

21.5. Определение порядка для составных индексов…………………………………………………………… 804

21.6. Использование возрастающих и убывающих индексов……………………………………………… 806

21.7. Использование функциональных индексов………………………………………………………………….. 809

21.8. Использование индексов в вычисляемых столбцах…………………………………………………….. 811

21.9. Поиск с помощью полнотекстового индекса………………………………………………………………… 814

21.10. Использование пространственных индексов и географических данных………………… 816

21.11. Создание и использование гистограмм………………………………………………………………………. 820

21.12. Написание высокопроизводительных запросов………………………………………………………… 827

Глава 22. Администрирование сервера…………………………………………………. 833

22.0. Введение………………………………………………………………………………………………………………………….. 833

22.1. Конфигурирование сервера…………………………………………………………………………………………… 833

22.2. Управление интерфейсом подключаемых модулей…………………………………………………….. 836

22.3. Управление журналированием сервера……………………………………………………………………….. 838

22.4. Ротация и установка срока действия файлов журналов……………………………………………… 841

22.5. Ротация таблиц журналов или строк таблицы журналов, у которых истекает
срок хранения………………………………………………………………………………………………………………….. 844

22.6. Конфигурирование механизмов хранения данных……………………………………………………… 845

Глава 23. Мониторинг сервера MySQL…………………………………………………. 848

23.0. Введение………………………………………………………………………………………………………………………….. 848

23.1. Зачем выполнять мониторинг сервера MySQL?………………………………………………………….. 849

23.2. Поиск источников информации для мониторинга MySQL………………………………………….. 855

23.3. Проверка времени доступности сервера………………………………………………………………………. 859

23.4. Устранение проблем с запуском сервера……………………………………………………………………… 860

23.5. Определение нагрзуки на систему ввода-вывода сервера MySQL…………………………….. 861

23.6. Определение загрузки процессора потоками MySQL…………………………………………………. 864

23.7. Лимиты соединений MySQL………………………………………………………………………………………….. 867

23.8. Проверка надлежащего размера буферного пула……………………………………………………….. 868

23.9. Поиск информации о механизме хранения данных…………………………………………………….. 871

23.10. Использование журнала ошибок для устранения сбоев в работе сервера MySQL.. 874

23.11. Журнал медленных запросов………………………………………………………………………………………. 876

23.12. Мониторинг с помощью общего журнала запросов…………………………………………………. 878

23.13. Использование бинарного журнала для выявления изменений……………………………….. 880

Глава 24. Безопасность………………………………………………………………………… 882

24.0. Введение………………………………………………………………………………………………………………………….. 882

24.1. Основные сведения о таблице mysql.user……………………………………………………………………… 882

24.2. Управление учетными записями пользователей………………………………………………………….. 883

24.3. Внедрение политики паролей………………………………………………………………………………………… 887

24.4. Проверка надежности пароля……………………………………………………………………………………….. 889

24.5. Истечение срока действия паролей………………………………………………………………………………. 889

24.6. Установка нового пароля для собственной учетной записи………………………………………. 891

24.7. Переустановка пароля при истечении срока его действия…………………………………………. 891

24.8. Обнаружение и удаление анонимных учетных записей…………………………………………….. 892

24.9. Изменение учетных записей “Any Host” и “Many Host”………………………………………………. 893

24.10. Использование TLS (SSL)…………………………………………………………………………………………….. 894

24.11. Использование ролей……………………………………………………………………………………………………. 898

24.12. Использование представлений для обеспечения безопасности при доступе к таблицам          899

24.13. Использование хранимых процедур для безопасной модификации данных………….. 902

Предметный указатель…………………………………………………………………………. 905

Об авторах……………………………………………………………………………………………. 910

Об изображении на обложке………………………………………………………………… 911

Cмирнова Cвета

Света Смирнова — ведущий специалист службы технической поддержки в компании Percona. Среди главных сфер ее профессиональной деятельности — решение проблем и обучение эффективной работе с MySQL, помощь в устранении ошибок и преодолении подводных камней этой СУБД.

 

 

Alkin Tezuysal

Алкин Тезуйсал — вице-президент по глобальным сервисам компании ChistaDATA. Имеет большой опыт работы с реляционными базами данных с открытым исходным кодом.

Опубликовано

Новинка: “SQL для анализа данных”

SQL для анализа данных

Рассказывается о возможностях SQL применительно к анализу данных. Сравниваются различные типы баз данных, описаны методы подготовки данных для анализа. Рассказано о типах данных, структуре SQL-запросов, профилировнии, структурировании и очистке данных. Описаны методы анализа временных рядов, трендов, приведены примеры анализа данных с учетом сезонности. Отдельные главы посвящены когортному анализу, текстовому анализу, выявлению и обработке аномалий, анализу результатов экспериментов и А/В-тестирования. Описано создание сложных наборов данных, комбинирование методов анализа. Приведены практические примеры анализа воронки продаж и потребительской корзины.

Для аналитиков, исследователей и специалистов по обработке данных

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

• Узнайте о ключевых шагах по подготовке данных к анализу
• Выполняйте анализ временных рядов
• Используйте когортный анализ для изучения изменений групп с течением времени
• Научитесь применять мощные функции SQL для анализа текста
• Выявляйте отклонения и аномалии в данных
• Установите причинно-следственную связь с помощью анализа экспериментов и результатов А/Б-тестирования
• Применяйте SQL для оценки эффективности воронки продаж

Книгу “SQL для анализа данных” можно купить со скидкой в интернет-магазине издательства “БХВ“.

Предисловие. 11

Условные обозначения. 12

Использование примеров кода. 13

Благодарности. 13

ГЛАВА 1. Анализ с помощью SQL.. 15

1.1. Что такое анализ данных. 15

1.2. Почему SQL. 18

Что такое SQL. 18

Преимущества SQL. 21

SQL против R или Python. 22

SQL как часть процесса анализа данных. 24

1.3. Типы баз данных. 26

Строчные базы данных. 27

Колоночные базы данных. 29

Другие типы хранилищ данных. 31

1.4. Заключение. 32

ГЛАВА 2. Подготовка данных для анализа. 33

2.1. Типы данных. 34

Типы в базах данных. 34

Структурированные и неструктурированные данные. 36

Количественные и качественные данные. 37

Первичные, вторичные и третичные данные. 38

Разреженные данные. 39

2.2. Структура SQL-запроса. 40

2.3. Профилирование: распределения. 43

Гистограммы и частоты.. 44

Биннинг. 46

n-тили. 49

2.4. Профилирование: качество данных. 52

Поиск дубликатов. 52

Исключение дубликатов с помощью GROUP BY и DISTINCT. 54

2.5. Подготовка: очистка данных. 56

Очистка данных с помощью CASE. 56

Преобразование типов. 59

Работа с null-значениями: функции coalesce, nullif, nvl 62

Отсутствующие данные. 65

2.6. Подготовка: структурирование данных. 69

Зависимость от конечной цели: для BI, визуализации, статистики
или машинного обучения. 70

Сворачивание с помощью оператора CASE. 71

Разворачивание с помощью оператора UNION.. 73

Операторы PIVOT и UNPIVOT. 75

2.7. Заключение. 77

ГЛАВА 3. Анализ временных рядов. 79

3.1. Работа с Date, Datetime и Time. 80

Преобразование часовых поясов. 80

Форматирование дат и временных меток. 83

Арифметические действия с датами. 87

Арифметические действия со временем.. 90

Объединение данных из разных источников. 91

3.2. Набор данных о розничных продажах. 92

3.3. Анализ трендов данных. 93

Простые тренды.. 94

Сравнение временных рядов. 96

Вычисление процента от целого. 105

Приведение к базовому периоду. 109

3.4. Скользящие временные окна. 114

Расчет скользящих временных окон. 116

Скользящие окна на разреженных данных. 122

Расчет накопительного итога. 125

3.5. Анализ с учетом сезонности. 127

Сравнение периодов: YoY и MoM.. 129

Сравнение периодов: этот же месяц в прошлом году. 132

Сравнение с несколькими предыдущими периодами. 137

3.6. Заключение. 140

ГЛАВА 4. Когортный анализ. 141

4.1. Составляющие когортного анализа. 141

4.2. Набор данных о законодателях. 144

4.3. Анализ удержания. 146

Общая кривая удержания. 148

Заполнение отсутствующих дат для большей точности. 152

Когорты, полученные из временного ряда. 158

Определение когорт по другой таблице. 164

Работа с разреженными когортами. 169

Когорты по датам, отличным от первой даты.. 174

4.4. Связанные когортные анализы.. 177

Выживаемость. 177

Возвращаемость или поведение при повторной покупке. 182

Накопительный итог. 188

4.5. Поперечный анализ через все когорты.. 192

4.6. Заключение. 201

ГЛАВА 5. Текстовый анализ. 203

5.1. Текстовый анализ и SQL. 203

Что такое текстовый анализ. 204

Как можно использовать SQL для текстового анализа. 204

Когда не стоит использовать SQL. 206

5.2. Набор данных о наблюдениях НЛО.. 206

5.3. Характеристики текста. 207

5.4. Разбор текста. 210

5.5. Преобразование текста. 216

5.6. Поиск в текстовых данных. 225

Подстановочные знаки: LIKE, ILIKE. 225

Точное соответствие: IN, NOT IN.. 231

Регулярные выражения. 234

5.7. Конкатенация и реорганизация. 251

Конкатенация строк. 251

Реорганизация текстовых полей. 255

5.8. Заключение. 259

ГЛАВА 6. Выявление аномалий. 261

6.1. Возможности SQL для обнаружения аномалий. 262

6.2. Набор данных о землетрясениях. 263

6.3. Поиск аномалий. 264

Сортировка для поиска аномалий. 265

Расчет процентилей и стандартных отклонений. 268

Поиск аномалий с помощью графиков. 276

6.4. Виды аномалий. 284

Аномальные значения. 284

Аномальное количество или частота. 288

Аномальное отсутствие данных. 293

6.5. Обработка аномалий. 295

Исследование аномалий. 295

Исключение аномальных записей. 296

Замена на альтернативные значения. 298

Изменение масштаба. 300

6.6. Заключение. 302

ГЛАВА 7. Анализ экспериментов. 303

7.1. Плюсы и минусы SQL для анализа экспериментов. 304

7.2. Набор данных о мобильной игре. 306

7.3. Типы экспериментов. 307

Эксперименты с бинарными результатами: тест хи-квадрат. 308

Эксперименты с непрерывными результатами: t-тест. 310

7.4. Спасение неудачных экспериментов. 313

Система назначения вариантов. 313

Выбросы.. 314

Метод временных рамок. 316

Эксперименты с повторным воздействием.. 317

7.5. Альтернативные анализы, когда контролируемые эксперименты невозможны   319

Анализ «до и после». 319

Анализ естественных экспериментов. 321

Анализ популяции около порогового значения. 323

7.6. Заключение. 324

ГЛАВА 8. Создание сложных наборов данных. 325

8.1. SQL для сложных наборов данных. 325

Преимущества использования SQL. 326

Перенос логики в ETL. 326

Перенос логики в другие инструменты.. 329

8.2. Упорядочивание кода. 330

Комментарии. 330

Регистр, отступы, круглые скобки и другие приемы форматирования. 332

Хранение кода. 335

8.3. Контроль над порядком вычислений. 335

Порядок выполнения операций SQL. 336

Подзапросы.. 339

Временные таблицы.. 342

Общие табличные выражения. 343

Расширения для группировки. 345

8.4. Управление размером набора данных и проблемы конфиденциальности. 349

Частичная выборка с помощью остатка от деления. 349

Уменьшение размерности. 351

Персональные данные и конфиденциальность. 356

8.5. Заключение. 358

ГЛАВА 9. Комбинирование методов анализа и полезные ресурсы.. 359

9.1. Анализ воронки продаж.. 359

9.2. Отток, отставшие и анализ разрывов. 361

9.3. Анализ потребительской корзины.. 366

9.4. Полезные ресурсы.. 368

Книги и блоги. 369

Наборы данных. 370

9.5. Заключение. 371

Об авторе. 373

Об обложке. 374

Предметный указатель. 375

Танимура Кэти

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