Mysql реализация языка sql в субд mysql


Содержание

SQL и NoSQL: разбираемся в основных моделях баз данных

С незапамятных времен память была одной из самых важных и необходимых составляющих компьютера. Несмотря на разницу в методах реализации, большинство вычислительных машин оснащены необходимым аппаратным обеспечением для обработки и хранения информации. В наше время невозможно представить работу какого-либо приложения, хоть игры, хоть сайта, без получения, обработки и записи определённого типа данных. Системы управления базами данных (СУБД) — это высокоуровневое программное обеспечение, работающее с низкоуровневыми API. Для решения различных проблем создавались новые виды СУБД (реляционные, NoSQL и т.д.) и их новые реализации (MySQL, PostgreSQL, MongoDB, Redis и т.д.). В этой статье мы разберемся в основах баз данных и СУБД.

Системы управления базами данных

СУБД — это общий термин, относящийся ко всем видам абсолютно разных инструментов, от компьютерных программ до встроенных библиотек. Эти приложения управляют или помогают управлять наборами данных. Так как эти данные могут быть разного формата и размера, были созданы разные виды СУБД.

СУБД основаны на моделях баз данных — определённых структурах для обработки данных. Каждая СУБД создана для работы с одной из них с учётом особенностей операций над информацией.

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

Модели баз данных

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

«КРОК», Москва, Санкт-Петербург, Троицк, Челябинск, Воронеж, Иркутск, Краснодар, Нижний Новгород, Самара, Пермь, от 120 000 до 240 000 ₽

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

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

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

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

Несмотря на строгие принципы формирования и обработки данных, РСУБД могут быть весьма гибкими, если приложить немного усилий.

Безмодельный (NoSQL) подход

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

Популярные СУБД

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

РСУБД

Реляционные системы управления базами данных берут своё название от реализуемой модели — реляционной. Сейчас они остаются, да и ещё какое-то время будут, самым популярным выбором для надёжного, безопасного и производительного хранения данных.

РСУБД требуют чётких и ясных схем — не стоит путать со специфическим определением для PostgreSQL — для работы с данными. Эти рамки, определённые пользователем, задают способ их хранения и использования. Схемы очень похожи на таблицы, столбцы которых отражают порядковый номер и тип информации в каждой записи, а строки — содержимое этих записей.

Самыми популярными РСУБД сейчас являются:

  • SQLite: очень мощная встраиваемая РСУБД.
  • MySQL: самая популярная и часто используемая РСУБД.
  • PostgreSQL: самая продвинутая и гибкая РСУБД.

NoSQL-СУБД

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

В отличие от традиционных РСУБД, некоторые базы данных NoSQL, например, MongoDB, позволяют группировать коллекции данных с другими базами данных. Такие СУБД хранят данные как одно целое. Эти данные могут представлять собой одиночный объект наподобие JSON и вместе с тем корректно отвечать на запросы к полям.

NoSQL базы данных не используют общий формат запроса (как SQL в реляционных базах данных). Каждое решение использует собственную систему запросов.

Сравнение SQL и NoSQL

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

  • Структура и тип хранящихся данных: SQL/реляционные базы данных требуют наличия однозначно определённой структуры хранения данных, а NoSQL базы данных таких ограничений не ставят.
  • Запросы: вне зависимости от лицензии, РСУБД реализуют SQL-стандарты, поэтому из них можно получать данные при помощи языка SQL. Каждая NoSQL база данных реализует свой способ работы с данными.
  • Масштабируемость: оба решения легко растягиваются вертикально (например, путём увеличения системных ресурсов). Тем не менее, из-за своей современности, решения NoSQL обычно предоставляют более простые способы горизонтального масштабирования (например, создания кластера из нескольких машин).
  • Надёжность: когда речь заходит о надёжности, SQL базы данных однозначно впереди.
  • Поддержка: РСУБД имеют очень долгую историю. Они очень популярны, и поэтому получить поддержку, платную или нет, очень легко. Поэтому, при необходимости, решить проблемы с ними гораздо проще, чем с NoSQL, особенно если проблема сложна по своей природе (например, при работе с MongoDB).
  • Хранение и доступ к сложным структурам данных: по своей природе реляционные базы данных предполагают работу с сложными ситуациями, поэтому и здесь они превосходят NoSQL-решения.

MySQL Учебник: установка, создание БД и таблицы, вставка и выбор записей

Главное меню » Базы данных » База данных MySQL » MySQL Учебник: установка, создание БД и таблицы, вставка и выбор записей

1. Установка и настройка MySQL

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

  • Если вы устанавливаете на дистрибутиве на основе RedHat (например: CentOS), следовать этой статье: Установка MySQL на Ubuntu с помощью rpm
  • Вы можете также использовать yum groupinstall для установки группы базы данных MySQL.
  • Если вы хотите установить MySQL вместе с другими компонентами LAMP (Apache и PHP), следовать этой статье: Как установить или обновить LAMP

2. Создание базы данных MySQL

После того, как MySQL установлен, подключитесь к нему с помощью пользователя root и создайте базу данных, как показано ниже.

После подключения, как MySQL пользователя root, выполните следующую команду “mysql> ” для создания базы данных.

Следующая команда создаст базу данных с именем “andreyexbase”.

Используйте команду “show databases”, чтобы убедиться, что база данных была успешно создана.

3. Создать таблицу MySQL

Подключение к вновь созданной базе данных с помощью команды “use”, прежде чем выполнить любую операцию в базе данных.

Для того, чтобы подключиться к базе данных, выполните следующие действия.

Следующий пример создает таблицу сотрудников.

При копировании вставьте выше строки команды создания таблицы “mysql> ”, он будет отображать продолжение строки “->”, начиная со 2-й линии, что указывает на то, что команда еще не закончена. Конец команды SQL идентифицируется с помощью запятой.

Вы также можете использовать один из следующих типов данных.

  • dcode SMALLINT
  • MCode MEDIUMIN
  • project_start DATE
  • loggedon TIME

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

Для просмотра описания таблицы, выполните следующие действия.

4. Вставить запись в таблицу

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

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

Примечание: Вы можете также загружать данные из текстового файла в базу данных MySQL с помощью команды mysqlimport.

5. Запрос записей из таблицы

Для того, чтобы просмотреть все записи из таблицы, используйте следующий оператор выбора.

Для того, чтобы просмотреть только определенные столбцы из таблицы, указать имена столбцов в команде select.

Следующий оператор select имеет состояние, которое отображает записи о сотрудниках, которые принадлежат к техническому отделу и получают зарплату> = 7000.

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Реляционные СУБД – сравнение MySQL и SQL сервер

Вступление

База данных играет важную роль для каждого современного веб-приложения. Благодаря динамической природе веб-приложений сейчас, даже простейшие приложения требуют некоторых механизмов хранения, доступа и изменения данных (вот почему в Hostinger мы предлагаем неограниченные Базы данных MySQL для наших клиентов с премиум и бизнес аккаунтами). Естественно, поскольку важность баз данных стремительно растёт, реляционные системы управления базами данных или реляционные СУБД набирают свою популярность (Relational Database Management Systems – RDBMS)

Две из них MySQL и SQL Server. Обе выполняют одинаковую функцию, хотя имею различные варианты использования. Они различаются некоторыми особенностями, но обе системы базируются на SQL или Structured Query Language (структурированный язык запросов). В связи с этим, разработчики могут обнаружить несколько схожестей между MySQL и SQL сервер, таких как использование таблиц для сохранения данных, ссылки на первичные и внешние ключи, также как несколько баз данных в одной среде или на одном сервере.

Не будет ошибкой сказать, что MySQL и SQL сервер – это две наиболее популярные реляционные СУБД среди существующих, хотя Oracle и Postgres найдётся, что сказать по этому поводу. Не смотря на то, что мы постепенно становимся свидетелями перехода с SQL на NoSQL, первые всё же продолжают доминировать. Это означает, что сейчас всё ещё актуально изучить как MySQL, так и SQL сервер.

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

MySQL и SQL сервер – сравнение

Что такое MySQL?

Разработанная в середине 90х (позже приобретённая Oracle), MySQL была одной из первый баз данных с открытым исходным кодом и остаётся таковой и до сегодня. Это значит, что существует несколько альтернатив MySQL. Но различия между этими вариантами не слишком явные; синтаксис и основная функциональность остаётся одинаковой.

А что является отличительной чертой MySQL, так это её популярность среди стартап-сообществ. Открытый код и бесплатность даёт возможность разработчикам легко начать с MySQL и изменять свой код, когда понадобится. MySQL обычно используется вместе с PHP(англ.) и Веб-сервером Apache, в дистрибутивах Linux, что и привело к известной аббревиатуре LAMP (Linux, Apache, MySQL, PHP).

Что такое SQL сервер?

SQL сервер также известен, как Microsoft SQL Сервер, появился значительно раньше, чем MySQL. Microsoft разработал SQL сервер в 80х, с обещанием разработать надёжную и расширяемую реляционную СУБД. Они остаются ядром качества SQL сервера по прошествии всех этих лет, и предоставляют незаменимое решение для крупномасштабного корпоративного программного обеспечения.

SQL сервер больше подходит для разработчиков, использующих .NET в качестве языка разработки, как конкурирующей связке PHP для MySQL. Это весьма логично, так как обе платформы принадлежать Microsoft.

Ключевые различия между MySQL и SQL сервером

Теперь, после краткого знакомства с системами, давайте посмотрим на несколько ключевых различий между MySQL и SQL сервером:

  • Среда
    Как упоминалось ранее, SQL сервер лучше работает с .NET, в то время как MySQL может был использован с практически любыми другими языками, наиболее распространённая связка с PHP. Не лишним будет также сказать, что SQL сервер может быть запущен только лиш под ОС Windows, но за последние годы это условие изменилось, когда Microsoft анонсировала поддержку Linux для SQL сервера. Версия для Linux всё ещё зреет и имеет незавершённых вид, что значит мы рекомендуем вам использовать ОС Windows при работе с SQL сервером и переключатся на Linux, если работаете с СУБД MySQL.
  • Синтаксис
    Для большинства людей это наиболее важное различие в этих двух системах. Знакомство с одним набором правил синтаксиса может значительно повлиять на ваше решение относительно того, какая система подходит вам больше. Хотя MySQL и SQL сервер базируются на SQL, различия синтаксиса всё же ощутимы и заслуживают внимания. Например, давайте посмотрим на этот фрагмент:

MySQL

Microsoft SQL Server

Обе цепочки кода достигают одного и того же результата – возвращают 3 записи со значением самого молодого возраста из таблицы имён людей. Но синтаксис сильно отличается. Конечно, синтаксис – это субъективный параметр оценки, поэтому мы не может тут давать рекомендацию; выбирайте то, что кажется вам более интуитивно понятным. Полный список описательных различий между MySQL и SQL сервером можно найти здесь (англ.).

  • SQL сервер больше, чем реляционная СУБД
    Главное преимущество платного ПО в сравнении с бесплатным – это особая поддержка, которую вы получаете. В данном случае, преимущество ещё более значимое, так как SQL сервер поддерживается одной из самых больших компаний в мире. Microsoft создало дополнительный инструменты для SQL сервера, которые привязываются к реляционной СУБД, включая инструменты для анализа данных. Система также имеет сервер отчётов – Служба отчётов SQL Сервера, равно как и инструмент ETL. Это делает SQL сервер швейцарским армейский ножом среди реляционных СУБД. Вы можете получить подобные функции и в MySQL, но вам придётся искать в интернете сторонние решения – что многим не подойдёт.
  • Система хранения данных
    Другим большим различием между MySQL и SQL сервером, которое иногда упускают, это система хранения данных. SQL сервер использует единую систему, разработанную Microsoft, в сравнении с множеством движков, предлагаемых MySQL. Это даёт разработчикам, использующим MySQL больше гибкости, поскольку они могут выбирать разные системы для разных таблиц, основываясь на скорости, надёжности или каких-то других параметрах. Популярный движок MySQL – это InnoDB, который немного теряет в скорости, но обеспечивает усиленную надёжность. Другой известный – MyISAM.
  • Отмена запроса
    Немногие это знают, но кардинальным различием между MySQL и SQL сервером является то, что MySQL не позволяет вам отменить запрос в середине его выполнения. Это значит, что, как только команда запущена на выполнение, вам лучше надеяться, что любой ущерб, который она может сделать, является обратимым. SQL сервер, с другой стороны, позволяет вам отменить запрос на пол пути его выполнения. Это различие может быть несущественным для администраторов, так как они обычно выполняют скрипты команд, и это редко требует отмены во время их выполнения, чего не всегда скажешь о разработчиках.
  • Безопасность
    Очевидно не требуется тщательного рассмотрения вопроса, когда идёт речь о сравнении различий в безопасности в MySQL с SQL сервера. Обе системы совместимы с EC2, что означает вы в безопасности, выбирая любую из двух. Нужно отметить, что величие Microsoft сказалось и здесь наличием в SQL сервере собственной, ультрасовременной системы безопасности. Выделенный инструмент безопасности – анализатор Microsoft Baseline Security Analyzer (MBSA) – гарантирует надёжную защиту для SQL сервера. Поэтому, если безопасность имеет ключевое значение для вас, выбор очевиден.
  • Стоимость
    Здесь SQL сервер становится гораздо менее привлекательным, и MySQL зарабатывает большие очки. Microsoft требует, чтобы вы покупали лицензии для запуска нескольких баз данных на SQL сервер, есть бесплатная версия, но она предназначена только для ознакомления с реляционной СУБД. Напротив, MySQL использует лицензию GNU, что делает её полностью свободной. Однако, если вам нужна поддержка или помощь для MySQL, вам нужно будет заплатить за нее.
  • Поддержка сообщества
    Что переносит нас к следующей точке. За поддержка MySQL вам вряд ли придётся платить, за исключением, быть может, редких случаев, благодаря вкладу большого сообщества в его поддержку. Преимущество огромного сообщества в том, что большинству людей не нужно обращаться за специальной помощью – можно просто искать в Интернете и находить массу решений.
  • IDE
    Важно отметить, что обе реляционные СУБД поддерживаются различными интегрированными средами разработки ( >Заключение

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

В этом руководстве мы обсудили две наиболее распространенные реляционные СУБД – MySQL и Microsoft SQL сервер. Мы рассмотрели несколько ключевых различий между MySQL и SQL сервером, даже одного из которых может быть достаточно, чтобы сделать выбор.

В конечном счёте, выбор за вами. Как правило, если вы разрабатываете приложения среднего и малого размера и преимущественно используете PHP, переходите к MySQL. Принимая во внимание, что если вы заинтересованы в создании крупномасштабных, безопасных, устойчивых корпоративных приложений, SQL сервер может вам подойти куда больше.

Базы данных и СУБД. Введение в SQL

В данной лекции мы рассмотрим основные понятия теории баз данных и познакомим читателей с системой управления базами данных MySql , способами работы с ней, ее особенностями и реализацией языка запросов SQL в этой СУБД . В основе приводимых в лекции примеров лежит информационная модель виртуального музея истории информатики. Эта модель есть набор коллекций описания исторических личностей, экспонатов музея (артефактов), статей и изображений.

Базы данных: основные понятия

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

В этой таблице данные – это собственно номера телефонов, адреса и ФИО., т.е. строки «Иванов Иван Иванович», «32-43-12» и т.п., а названия столбцов этой таблицы, т.е. строки «ФИО», «Номер телефона» и » Адрес » задают смысл этих данных, их семантику.

Таблица 10.1. Пример базы данных: телефонная книга
ФИО Номер телефона Адрес
Иванов Иван Иванович 32-43-12 ул. Ленина, 12, 43
Ильин Федор Иванович 32-32-34 пр. Маркса, 32, 45

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

Для точности дадим определение базы данных , предлагаемое Глоссарий .ру

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

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

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

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

Примеры реляционных СУБД : MySql , PostgreSql.

В основу объектной модели положена концепция объектно-ориентированного программирования, в которой данные представляются в виде набора объектов и классов, связанных между собой родственными отношениями, а работа с объектами осуществляется с помощью скрытых (инкапсулированных) в них методов.

Примеры объектных СУБД : Cache , GemStone (от Servio Corporation), ONTOS (ONTOS).

В последнее время производители СУБД стремятся соединить два этих подхода и проповедуют объектно-реляционную модель представления данных. Примеры таких СУБД – IBM DB2 for Common Servers, Oracle8.

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

Ключи

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

Первичный ключ (primary key, PK) – минимальный набор полей, уникально идентифицирующий запись в таблице. Значит, первичный ключ – это в первую очередь набор полей таблицы, во-вторых, каждый набор значений этих полей должен определять единственную запись (строку) в таблице и, в-третьих, этот набор полей должен быть минимальным из всех обладающих таким же свойством. Поскольку первичный ключ определяет только одну уникальную запись, то никакие две записи таблицы не могут иметь одинаковых значений первичного ключа .

Например, в нашей таблице (см. выше) ФИО и адрес позволяют однозначно выделить запись о человеке. Если же говорить в общем, без связи с решаемой задачей, то такие знания не позволяют точно указать на единственного человека, поскольку существуют однофамильцы, живущие в разных городах по одному адресу. Все дело в границах, которые мы сами себе задаем. Если считаем, что знания ФИО, телефона и адреса без указания города для наших целей достаточно, то все замечательно, тогда поля ФИО и адрес могут образовывать первичный ключ . В любом случае проблема создания первичного ключа ложится на плечи того, кто проектирует базу данных (разрабатывает структуру хранения данных). Решением этой проблемы может стать либо выделение характеристик, которые естественным образом определяют запись в таблице (задание так называемого логического, или естественного, PK), либо создание дополнительного поля, предназначенного именно для однозначной идентификации записей в таблице (задание так называемого суррогатного, или искусственного, PK). Примером логического первичного ключа является номер паспорта в базе данных о паспортных данных жителей или ФИО и адрес в телефонной книге (таблица выше). Для задания суррогатного первичного ключа в нашу таблицу можно добавить поле >ключей имеет смысл, если естественный первичный ключ представляет собой большой набор полей или его выделение нетривиально.

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

Например, у нас есть три таблицы: содержащая информацию об исторических личностях (Persons), содержащая информацию об их изобретениях ( Artifacts ) и содержащая изображения как личностей, так и артефактов (Images) (рис 10.1).

Первичным ключом во всех этих таблицах является поле >Artifacts есть поле author, в котором записан идентификатор, присвоенный автору изобретения в таблице Persons. Каждое значение этого поля является внешним ключом для первичного ключа таблицы Persons. Кроме того, в таблицах Persons и Artifacts есть поле photo, которое ссылается на изображение в таблице Images. Эти поля также являются внешними ключами для первичного ключа таблицы Images и устанавливают однозначную логическую связь Persons-Images и Artifacts -Images. То есть если значение внешнего ключа photo в таблице личности равно 10, то это значит, что фотография этой личности имеет >организации связей между таблицами базы данных (родительскими и дочерними) и для поддержания ограничений ссылочной целостности данных.

Индексирование

Одна из основных задач, возникающих при работе с базами данных , – это задача поиска. При этом, поскольку информации в базе данных , как правило, содержится много, перед программистами встает задача не просто поиска, а эффективного поиска, т.е. поиска за сравнительно небольшое время и с достаточной точностью. Для этого (для оптимизации производительности запросов) производят индексирование некоторых полей таблицы. Использовать индексы полезно для быстрого поиска строк с указанным значением одного столбца. Без индекса чтение таблицы осуществляется по всей таблице, начиная с первой записи, пока не будут найдены соответствующие строки. Чем больше таблица, тем больше накладные расходы. Если же таблица содержит индекс по рассматриваемым столбцам, то база данных может быстро определить позицию для поиска в середине файла данных без просмотра всех данных. Это происходит потому, что база данных помещает проиндексированные поля поближе в памяти, так, чтобы можно было побыстрее найти их значения. Для таблицы, содержащей 1000 строк, это будет как минимум в 100 раз быстрее по сравнению с последовательным перебором всех записей. Однако в случае, когда необходим доступ почти ко всем 1000 строкам, быстрее будет последовательное чтение, так как при этом не требуется операций поиска по диску. Так что иногда индексы бывают только помехой. Например, если копируется большой объем данных в таблицу, то лучше не иметь никаких индексов. Однако в некоторых случаях требуется задействовать сразу несколько индексов (например, для обработки запросов к часто используемым таблицам).

Если говорить о MySQL , то там существует три вида индексов: PRIMARY , UNIQUE , и INDEX , а слово ключ ( KEY ) используется как синоним слова индекс ( INDEX ). Все индексы хранятся в памяти в виде B-деревьев .

PRIMARY – уникальный индекс (ключ) с ограничением, что все индексированные им поля не могут иметь пустого значения (т.е. они NOT NULL ). Таблица может иметь только один первичный индекс, но он может состоять из нескольких полей.

UNIQUE – ключ (индекс ), задающий поля, которые могут иметь только уникальные значения.

INDEX – обычный индекс (как мы описали выше). В MySqL , кроме того, можно индексировать строковые поля по заданному числу символов от начала строки.

Основные преимущества СУБД MySQL

Читайте также:

  1. I. Основные подходы к управлению реализаций стратегических изменений.
  2. I.3. Основные принципы психологии.
  3. IV. Основные направления современной социологии.
  4. Microsoft Office 2007. Основные принципы интерфейса
  5. Mysql_num_rows(результат)
  6. N.B! Основные виды научения.
  7. VI Мартовские статьи. Основные результаты войны.
  8. VII.2. Основные особенности восприятия.
  9. Административно-контрольные методы УООС. Виды экологического лицензирования. Экологический контроль. Основные формы государственного экологического контроля.
  10. Банковский аудит и его основные направления
  11. Бихевиоризм и его основные черты.
  12. В каждом суставе различают основные элементы и добавочные образования.

Состав дистрибутива СУБД MySQL

Тема 1. Введение в СУБД MySQL

Дистрибутив MySQL включает следующие программные продукты:

Дата добавления: 2013-12-13 ; Просмотров: 6971 ; Нарушение авторских прав? ;

Нам важно ваше мнение! Был ли полезен опубликованный материал? Да | Нет

Доступ к базам данных. СУБД MySQL. Система безопасности. Утилиты. Язык SQL

Доступ к базам данных.

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

Самыми простыми базами данных изо всех возможных являются плоские текстовые файлы. Для доступа к ним можно использовать средства DHTML либо файловые операции, включенные в состав серверных языков сценариев. Для взаимодействия с популярными настольными базами данных, такими как хорошо известная вам СУБД Microsoft Access, используются свои технологии. Поскольку самыми популярными настольными операционными системами по-прежнему остаются представители семейства Microsoft Windows, средства доступа к настольным СУБД будут рассмотрены в теме, посвященной серверному языку сценариев Active Server Pages (ASP). Самыми же продвинутыми средствами доступа располагают серверы баз данных, такие как MS SQL или MySQL, особенностям которого и посвящена данная тема.

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

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

Рис. 3.1. Клиент-серверная архитектура MySQL

Другое преимущество многопоточной обработки присуще всем многопоточным приложениям. Несмотря на то, что потоки совместно используют память процесса, они выполняются раздельно. Благодаря этому разделению выполнение потоков на многопроцессорных машинах может быть распределено по нескольким ЦП. На рис. 3-1 показана эта многопоточная природа сервера MySQL.

Помимо выигрыша в производительности, полученного благодаря многопоточности, MySQL поддерживает большое подмножество языка запросов SQL. MySQL поддерживает более десятка типов данных, а также функции SQL. Ваше приложение может получить доступ к этим функциям через команды ANSI SQL. MySQL фактически расширяет ANSI SQL несколькими новыми возможностями. В их числе новые функции (ENCRYPT, WEEKDAY, IF и другие), возможность инкрементирования полей (AUTO_INCREMENT и LAST_INSERT ID), а также возможность различать верхний и нижний регистры.

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

С 1996 года ТсХ использует MySQL в среде, где имеется более 40 баз данных, содержащих 10 000 таблиц. Из этих 10 000 более 500 таблиц имеют, в свою очередь, более 7 миллионов записей — около 100 Гбайт данных.

Система безопасности MySQL

Вам не только нужно иметь надежный доступ к своим данным, но и быть уверенным, что у других нет никакого доступа к ним. MySQL использует собственный сервер баз данных для обеспечения безопасности. При первоначальной установке MySQL создается база данных под названием «mysql». В этой базе есть пять таблиц: db, host, user, tables_priv, и columns_priv. Более новые версии MySQL создают также базу данных с названием func, но она не имеет отношения к безопасности. MySQL использует эти таблицы для определения того, кому что позволено делать. Таблица user содержит данные по безопасности, от­носящиеся к серверу в целом. Таблица host содержит права доступа к серверу для удаленных компьютеров. И наконец, db, tables_priv и соlumns_priv управляют доступом к отдельным базам данных, таблицам и колонкам.

Мы кратко рассмотрим все таблицы, поддерживающие безопасность в MySQL, а затем рассмотрим технологию их использования при обеспечении защиты ядром MySQL.

Таблица user.Таблица user имеет вид, показанный в Таблице 3.1:

Таблица 3.1. Таблица user

Поле Тип Null Ключ Значение по умолчанию
Host char(60) PRI
User char(16) PRI
Password char(16)
Select_priv enum(‘N’,’Y’) N
Insert_priv enum(‘N’,’Y’) N
Update_priv enum(‘N’,’Y’) N
Delete_priv enum(‘N’,’Y’) N
Create_priv enum(‘N’,’Y’) N
Drop_priv enum(‘N’,’Y’) N
Reload_priv enum(‘N’,’Y’) N
Shutdown_priv enum(‘N’,’Y’) N
Process_priv enum(‘N’,’Y’) N
File_priv enum(‘N’,’Y’) N
Grant_priv enum(‘N’,’Y’) N
References_priv enum(‘N’,’Y’) N
Index_priv enum(‘N’,’Y’) N
Alter_priv enum(‘N’,’Y’) N

В колонках Host и User можно использовать символ «%», заменяющий произвольную последовательность символов. Например, имя узла «chem%lab» включает в себя «chembiolab», «chemtestlab» и т. д. Специальное имя пользователя «nobody» действует как одиночный «%», то есть охватывает всех пользователей, не упомянутых где-либо в другом месте. Ниже разъясняется смысл различных прав доступа:

Select_priv — Возможность выполнять команды SELECT.

Insert_priv — Возможность выполнять команды INSERT.

Update_priv — Возможность выполнять команды UPDATE.

Delete_priv — Возможность выполнять команды DELETE.

Create_priv — Возможность выполнять команды CREATE или создавать базы данных.

Drop_priv — Возможность выполнять команды DROP для удаления баз данных.

Reload_priv — Возможность обновлять информацию о доступе с помощью mysqladmin reload.

Shutdown_priv — Возможность останавливать сервер через mysqladmin shutdown.

Process_priv — Возможность управлять процессами сервера.

File_priv — Возможность читать и записывать файлы с помощью команд типа SELECT INTO OUTFILE и LOAD DATA INFILE.

Grant_priv — Возможность давать привилегии другим пользователям.

Index_priv — Возможность создавать и уничтожать индексы.

Alter_priv — Возможность выполнять команду ALTER TABLE.

В MySQL есть специальная функция, позволяющая скрыть пароли от любопытных глаз. Функция password() зашифровывает пароль. Ниже показано, как использовать функцию password() в процессе добавления пользователей в систему.

INSERT INTO user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv)

VALUES (‘%’, ‘bob’, password(‘mypass’), ‘Y’, ‘Y’, ‘Y’, ‘Y’)

INSERT INTO user (Host, User, Password, Select_priv)

VALUES (‘athens.imaginary.com’, ‘jane’, », ‘Y’)

INSERT INTO user(Host, User, Password)

INSERT INTO user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv)

VALUES (‘athens.imaginary.com’, ‘nobody’, password(‘thispass’), ‘Y’, ‘Y’, ‘Y’, ‘Y’)

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

Первый созданный нами пользователь, «bob», может подключаться к базе данных с любого компьютера и выполнять команды SELECT, INSERT, UPDATE и DELETE. Второй пользователь, «jane», может подключаться с «athens.imaginary.com», не имеет пароля и может выполнять только SELECT. Третий пользователь — «nobody» — с любой машины. Этот пользователь вообще ничего не может делать. Последний пользователь — «nobody» — с машины «athens.imaginary.com», он может выполнять SELECT, INSERT, UPDATE и DELETE, как и пользователь «bob».

Как MySQL производит сопоставление? Некоторое имя может соответствовать на деле нескольким записям. Например, «nobody @athens.imaginary.com» соответствует и «nobody@%», и «nobody@athens.imaginary.com». Прежде чем осуществлять поиск в таблице user, MySQL сортирует данные следующим образом:

1. Сначала ищется соответствие для узлов, не содержащих масок «%», при этом пустое поле Host трактуется как «%».

2. Для одного и того же узла сначала проверяется соответствие имен, не содержащих масок. Пустое поле User трактуется как содержащее «%».

3. Первое найденное соответствие считается окончательным.

В предыдущем примере пользователь сначала будет сравниваться с «nobody» из «athens.imaginary.com», поскольку «athens.imaginary.com» в порядке сортировки стоит выше «%». Поскольку имена компьютеров сортируются раньше имен пользователей, значения привилегий для компьютера, с которого вы подключаетесь, имеют приоритет перед любыми конкретными правами, которые у вас могут быть. Если таблица user содержит записи:

Host User
% jane
athens.imaginary.com

и jane подключается с «athens.imaginary.com», то MySQL будет использовать привилегии, данные «athens.imaginary.com».

Таблица db.В таблице user не упоминаются конкретные базы данных и таблицы. Таблица user управляет сервером в целом. Однако на сервере обычно находится несколько баз данных, которые служат различным целям и, соответственно, обслуживают разные группы пользователей. Права доступа к отдельным базам данных хранятся в таблице db:

Таблица 3.2. Таблица db

Поле Тип Null Ключ Значение по умолчанию
Host char(60) PRI
Db char(32) PRI
User char(16) PRI
Select_priv enum(‘N’,’Y’) N
Insert_priv enum(‘N’,’Y’) N
Update_priv enum(‘N’,’Y’) N
Delete_priv enum(‘N’,’Y’) N
Create_priv enum(‘N’,’Y’) N
Drop_priv enum(‘N’,’Y’) N
References_priv enum(‘N’,’Y’) N
Index_priv enum(‘N’,’Y’) N
Alter_priv enum(‘N’,’Y’) N

Эта таблица во многом похожа на таблицу user. Основное отличие в том, что вместо колонки Password имеется колонка Db. Таблица управляет правами пользователей в отношении определенных баз данных. Поскольку привилегии, указанные в таблице user, относятся ко всему серверу в целом, права, присвоенные пользователю в таблице user, перекрывают права, присвоенные тому же пользователю в таблице. Например, если пользователю в таблице user разрешают доступ типа INSERT, это право действует в отношении всех баз данных, вне зависимости от того, что указано в таблице db.

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

Те же правила, которые действуют в отношении колонок User и Host в таблице user, действуют и в таблице db, но с некоторой особенностью. Пустое поле Host вынуждает MySQL найти запись, соответствующую имени узла пользователя, в таблице host. Если такой записи не найдено, MySQL отказывает в доступе. Если соответствие найдено, MySQL определяет права как пересечение прав, определяемых таблицами host и db. Иными словами, в обеих записях разрешение должно иметь значение «Y», иначе в доступе отказывается.

Таблица host.Таблица host служит особой цели. Ее структура показана в таблице 3.3:

Таблица 3.3. Таблица Host

Поле Тип Null Ключ Значение по умолчанию
Host char(60) PRI
Db char(32) PRI
Select_priv enum(‘N’,’Y’) N
Insert_priv enum(‘N’,’Y’) N
Update_priv enum(‘N’,’Y’) N
Delete_priv enum(‘N’,’Y’) N
Create_priv enum(‘N’,’Y’) N
Drop_priv enum(‘N’,’Y’) N
Grant_priv enum(‘N’,’Y’) N
References_priv enum(‘N’,’Y’) N
Index_priv enum(‘N’,’Y’) N
Alter_priv enum(‘N’,’Y’) N

Таблица host позволяет задать основные разрешения на межкомпьютерном уровне. При проверке прав доступа MySQL ищет в таблице db соответствие имени пользователя и его машине. Если он находит запись, соответствующую имени пользователя, поле host которой пусто, MySQL обращается к таблице host и использует пересечение обоих прав для определения окончательного права доступа. Если у вас есть группа серверов, которые вы считаете менее защищенными, то вы можете запретить для них все права записи. Если «bob» заходит с одной из таких машин, и его запись в таблице db содержит пустое поле host, ему будет запрещена операция записи, даже если она разрешена ему согласно таблице db.

Таблицы tables_priv и columns_priv.Эти две таблицы, по сути, уточняют данные, имеющиеся в таблице db. Именно, право на всякую операцию сначала проверяется по таблице db, затем по таблице tables_priv , затем по таблице columns_priv. Операция разрешается, если одна из них дает разрешение. С помощью этих таблиц можно сузить область действия разрешений до уровня таблиц и колонок. Управлять этими таблицами можно через команды SQL GRANT и REVOKE.

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

При подключении проводятся две проверки. Сначала MySQL проверяет, есть ли в таблице user запись, соответствующая имени пользователя и машины, с которой он подключается. Поиск соответствия основывается на правилах, которые мы обсудили раньше. Если соответствие не найдено, в доступе отказывается. В случае, когда соответствующая запись найдена и имеет непустое поле Password , необходимо ввести правильный пароль. Неправильный пароль приводит к отклонению запроса на подключение.

Если соединение установлено, MySQL переходит к этапу верификации запроса. При этом сделанные вами запросы сопоставляются с вашими правами. Эти права MySQL проверяет по таблицам user, db, host, tables_pnv и columns_priv. Как только найдено соответствие в таблице user с положительным разрешением, команда немедленно выполняется. В противном случае MySQL продолжает поиск в следующих таблицах в указанном порядке:

Если таблица db содержит разрешение, дальнейшая проверка прекращается и выполняется команда. Если нет, то MySQL ищет соответствие в таблице tables_priv . Если это команда SELECT, объединяющая две таблицы, то пользователь должен иметь разрешения для обеих этих таблиц. Если хотя бы одна из записей отказывает в доступе или отсутствует, MySQL точно таким же способом проверяет все колонки в таблице columns_priv.

Изменение прав доступа.MySQL загружает таблицы доступа при запуске сервера. Преиму­ществом такого подхода по сравнению с динамическим обращением к таблицам является скорость. Отрицательная сторона состоит в том, что изменения, производимые в таблицах доступа MySQL, не сразу начинают действовать. Для того чтобы сервер увидел эти изменения, необходимо выполнить команду mysqladmin reload. Если таблицы изменяются с помощью SQL-команд GRANT или REVOKE, явно перегружать таблицы не требуется.

ТсХ распространяет MySQL с большим набором вспомогательных утилит, однако набор утилит, предлагаемых сторонними разработчиками, еще богаче.

Утилиты командной строки (Command Line Tools):

Isamchk — Производит проверку файлов, содержащих данные базы. Эти файлы называются ISAM-файлами (ISAM — метод индексированного последовательного доступа). Эта утилита может устранить большую часть повреждений ISAM-файлов.

Isamlog — Читает создаваемые MySQL журналы, относящиеся к ISAM-файлам. Эти журналы можно использовать для воссоздания таблиц или воспроизведения изменений, внесенных в таблицы в течение некоторого промежутка времени.

mysql — Создает прямое подключение к серверу баз данных и позволяет вводить запросы непосредственно из приглашения MySQL.

mysqlaccess — Модифицирует таблицы прав доступа MySQL и отображает их в удобном для чтения виде. Использование этой утилиты — хороший способ изучения структуры таблиц доступа MySQL.

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

Mysqlbug — Составляет для ТсХ отчет о возникшей в MySQL неполадке. Отчет будет также послан в почтовый список рассылки MySQL, и армия добровольцев MySQL будет исследовать проблему.

Mysqldump — Записывает все содержимое таблицы, включая ее структуру, в файл в виде SQL-команд, которыми можно воссоздать таблицу. Выходные данные этой утилиты можно использовать для воссоздания таблицы в другой базе или на другом сервере. Синтаксис ее применения: mysqldump -u user -p dbname —tab=path, где path — путь для сохранения файлов.

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

Mysqlshow — Выводит на экран структуру баз данных, имеющихся на сервере, и таблицы, из которых они состоят.

Утилиты сторонних разработчиков.Ни один поставщик или разработчик не может самостоятельно предоставить все необходимые для программного продукта средства под­держки. За самым свежим списком обратитесь на домашнюю страницу MySQL: http://www.mysql.com/Contrib.

Утилиты преобразования баз данных:

access_to_mysql — Преобразует базы данных Microsoft Access в таблицы MySQL. Включается в Access в виде функции, позволяющей сохранять таблицы в формате, позволяющем экспортировать их в MySQL.

dbf2mysql — Конвертирует файлы dBASE (DBF) в таблицы MySQL. Хотя dBASE утратил популярность, формат DBF установился как наиболее распространенный для передачи данных между различными приложениями баз данных. Все главные настольные приложения баз данных могут читать и писать DBF-файлы. Это приложение полезно для экспорта/импорта данных в коммерческие настольные базы данных.

Exportsql/Importsql — Конвертирует базы данных Microsoft Access в MySQL и обратно. Эти утилиты являются функциями Access, которые можно использовать для экспорта таблиц Access в формате, пригодном для чтения MySQL. С их помощью можно также преобразовывать SQL-выход MySQL в вид, пригодный для чтения Access.

Интерфейсы CGI:

PHP — Создает HTML-страницы с использованием специальных тегов, распознаваемых анализатором РНР. РНР имеет интерфейсы к большинству основных баз данных, включая MySQL и mSQL.

Mysql-webadmin — Осуществляет веб-администрирование баз данных MySQL. Используя это средство, можно просматривать таблицы и изменять их содержимое с помощью HTML-форм.

Mysqladm — Осуществляет веб-администрирование баз данных MySQL. Эта CGI-программа позволяет просматривать таблицы через WWW, добав­лять таблицы и изменять их содержимое.

www-sql — Создает HTML-страницы из таблиц баз данных MySQL. Эта программа осуществляет разбор HTML-страниц в поисках специальных тегов и использует извлеченные данные для выполнения команд SQL на сервере MySQL.

Клиентские приложения:

Mysqlwinadmn — Позволяет администрировать MySQL из Windows. С помощью этого средства можно выполнять функции mysqladmin из графического интерфейса.

Xmysql — Обеспечивает полный доступ к таблицам баз данных MySQL для клиента X Window System. Поддерживает групповые вставки и удаления.

Xmysqladmin — Позволяет осуществлять администрирование MySQL из X Window System. Это инструмент для графического интерфейса, позволяющий создавать и удалять базы данных и управлять таблицами. С его помощью можно также проверять, запущен ли сервер, перегружать таблицы доступа и управлять потоками.

Интерфейсы программирования:

MyODBC — Реализует ODBC API к MySQL в Windows.

mm.mysql.jdbc — Реализует стандартный API JDBC (Java Database Connectivity -доступ к базам данных из Java).

TwzJdbcForMysql — Реализация JDBC API для Java.

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

SQL является разновидностью «естественного языка». Иными словами, команда SQL должна читаться, по крайней мере на первый взгляд, как предложение английского языка. У такого подхода есть как преимущества, так и недостатки, но факт заключается в том, что этот язык очень непохож на традиционные языки программирования, такие как С, Java или Perl. Здесь мы рассмотрим язык SQL, как он реализован в MySQL.

Основы SQL.SQL «структурирован» в том отношении, что он следует определенному набору правил. Компьютерной программе легко разобрать на части сформулированный запрос SQL. Действительно, в книге издательства O’Reilly «lex & уасс», написанной Дж. Ливайном, Т. Мэйсоном и Д. Брауном (John Levine, Tony Mason, Doug Brown), реализована грамматика SQL для демонстрации процесса создания программы, интерпретирующей язык! Запрос (query) — это полностью заданная команда, посылаемая серверу баз данных, который выполняет запрошенное действие. Ниже приведен пример SQL-запроса:

SELECT name FROM people WHERE name LIKE Stac%’

Как можно видеть, это предложение выглядит почти как фраза на ломаном английском языке: «Выбрать имена из список люди, где имена похожи на Stac». SQL в очень незначительной мере использует форматирование и специальные символы, обычно ассоциируемые с компьютерными языками. Сравните, к примеру, «$++;($*++/$!);$&$»,,;$!» в Perl и «SELECT value FROM table» в SQL.

История SQL.В IBM изобрели SQL в начале 1970-х, вскоре после введения д-ром Е. Ф. Коддом (Е. F. Codd) понятия реляционной базы данных. С самого начала SQL был легким в изучении, но мощным языком. Он напоминает естественный язык, такой как английский, и поэтому не утомляет тех, кто не является техническим специалистом.

SQL действительно был настолько популярен среди пользователей, для которых предназначался, что в 1980-х компания Oracle выпустила первую в мире общедоступную коммерческую SQL-систему. Oracle SQL был хитом сезона и породил вокруг SQL целую индустрию. Sybase, Informix, Microsoft и ряд других компаний вышли на рынок с собственными разработками реляционных систем управления базами данных (РСУБД), основанных на SQL.

В то время когда Oracle и ее конкуренты вышли на сцену, SQL был новинкой, и для него не существовало стандартов. Лишь в 1989 году комиссия по стандартам ANSI выпустила первый общедоступный стандарт SQL. Сегодня его называют SQL89. К несчастью, этот новый стандарт не слишком углублялся в определение технической структуры языка. Поэтому, хотя различные коммерческие реализации языка SQL сближались, различия в синтаксисе делали задачу перехода с одной реализации языка на другую нетривиальной. Только в 1992 году стандарт ANSI SQL вступил в свои права.

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

SQL2 — не последнее слово в стандартах SQL. В связи с ростом популярности объектно-ориентированных СУБД (ООСУБД) и объектно-реляционных СУБД (ОРСУБД) возрастает давление с целью принятия объектно-ориентированного доступа к базам данных в качестве стандарта SQL. Ответом на эту проблему должен послужить SQL3. Он не является пока официальным стандартом, но в настоящее время вполне определился и может стать официальным стандартом.

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

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

CREATE TABLE people (name CHAR(10))
глагол дополнение расширенное определение
INSERT INTO people VALUES(‘me’)
глагол косвенное дополнение прямое дополнение
SELECT name FROM people WHERE name LIKE ‘%e’
глагол прямое дополнение косвенное дополнение придаточное предложение

Большинство реализаций SQL, включая MySQL, нечувствительны к регистру: неважно, в каком регистре вы вводите ключевые слова SQL, если орфография верна. Например, CREATE из верхнего примера можно записать и так:

cREatE ТАblЕ people (name cHaR(10))

Нечувствительность к регистру относится только к ключевым словам SQL. В MySQL имена баз данных, таблиц и колонок к регистру чувствительны. Но это характерно не для всех СУБД. Поэтому, если вы пишете приложение, которое должно работать с любыми СУБД, не следует использовать имена, различающиеся одним только регистром.

Первый элемент SQL-запроса — всегда глагол. Глагол выражает действие, которое должно выполнить ядро базы данных. Хотя остальная часть команды зависит от глагола, она всегда следует общему формату: указывается имя объекта, над которым осуществляется действие, а затем описываются используемые при действии данные. Например, в запросе CREATE TABLE people (char(10)) используется глагол CREATE, за которым следует дополнение (объект) TABLE. Оставшаяся часть запроса описывает таблицу, которую нужно создать.

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

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

Создание и удаление таблиц.Успешно установив MySQL, вы можете приступить к созданию своей первой таблицы. Таблица, структурированное вместилище данных, является основным понятием реляционных баз. Прежде чем начать вводить данные в таблицу, вы должны определить ее структуру. Рассмотрим следующую раскладку:

people
name char(10) not null
address text(100)
id int

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

Общий синтаксис для создания таблиц следующий:

CREATE TABLE table_name (column_name1 type [modifiers]

[, column_name2 type [modifiers]] )

Какие идентификаторы — имена таблиц и колонок — являются допустимыми, зависит от конкретной СУБД. В MySQL длина идентификатора может быть до 64 символов, допустим символ «$», и первым символом может быть цифра. Более важно, однако, что MySQL допускает использование любых символов из установленного в системе локального набора. Для хорошей переносимости SQL избегайте имен, начинающихся не с допустимой буквы.

Колонка — это отдельная единица данных в таблице. В таблице может содержаться произвольное число колонок, но использование больших таблиц бывает неэффективным. Создав правильно нормализованные таблицы, можно объединять их («join») для осуществления поиска в данных, размещенных в нескольких таблицах. Механику объединения таблиц мы обсудим позднее.

Как и бывает в жизни, разрушить легче, чем создать. Следующая команда удаляет таблицу:

DROP TABLE table_name

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

В MySQL можно одной командой удалить несколько таблиц, разделяя их имена запятыми. Например, DROP TABLE people, animals, plants удалит эти три таблицы. Можно также использовать модификатор IF EXISTS для подавления ошибки в случае отсутствия удаляемой таблицы. Этот модификатор полезен в больших сценариях, предназначенных для создания базы данных и всех ее таблиц. Прежде чем создавать таблицу, выполните команду DROP TABLE table_name IF EXISTS.

Типы данных в SQL.Каждая колонка таблицы имеет тип. Типы данных SQL сходны с типами данных традиционных языков программирования. В то время как во многих языках определен самый минимум типов, необходимых для работы, в SQL для удобства пользователей определены дополнительные типы, такие как MONEY и DATE. Данные типа MONEY можно было бы хранить и как один из основных числовых типов данных, однако использование типа, специально учитывающего особенности денежных расчетов, повышает легкость использования SQL.

Таблица 3.4.Наиболее употребительные типы данных, поддерживаемые MySQL

Тип данных Описание
INT Целое число, может быть со знаком или без знака.
REAL Число с плавающей запятой. Этот тип допускает больший диапазон значений, чем INT, но не обладает его точностью.
CHAR(length) Символьная величина фиксированной длины. Поля типа CHAR не могут содержать строки длины большей, чем указанное значение. Поля меньшей длины дополняются пробелами.
TEXT(length) Символьная величина переменной длины. TEXT — лишь один из нескольких типов данных переменного размера.
DATE Стандартное значение даты.
TIME Стандартное значение времени. Этот тип используется для хранения времени дня безотносительно какой-либо даты. При использовании вместе с датой позволяет хранить конкретную дату и время. Есть дополнительный тип DATETIME для совместного хранения даты и времени в одном поле.

MySQL поддерживает атрибут UNSIGNED для всех числовых типов. Этот модификатор позволяет вводить в колонку только положительные (беззнаковые) числа. Беззнаковые поля имеют верхний предел значений вдвое больший, чем у соответствующих знаковых типов. Беззнаковый TINYINT — однобайтовый числовой тип MySQL — имеет диапазон от 0 до 255, а не от -127 до 127, как у своего знакового аналога.

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

Числовые типы данных.Прежде чем создавать таблицу, вы должны хорошо представить себе, какого рода данные вы будете в ней хранить. Помимо очевидного решения о том, будут это числовые или символьные данные, следует выяснить примерный размер хранимых данных. Если это числовое поле, то каким окажется максимальное значение? Может ли оно измениться в будущем? Если минимальное значение всегда положительно, следует рассмотреть использование беззнакового типа. Всегда следует выбирать самый маленький числовой тип, способный хранить самое большое мыслимое значение. Если бы требовалось хранить в поле численность населения штата, следовало бы выбрать беззнаковый INT. В штате не может быть отрицательной численности населения, а чтобы беззнаковое поле типа INT не могло вместить число, представляющее его население, численность населения штата должна примерно равняться численности населения всей Земли.

Символьные типы.С символьными типами работать немного труднее. Вы должны подумать не только о максимальной и минимальной длине строки, но также о среднем размере, частоте отклонения от него и необходимости в индексировании. В данном контексте мы называем индексом поле или группу полей, в которых вы собираетесь осуществлять поиск — в основном, в предложении WHERE. Индексирование, однако, значительно сложнее, чем такое упрощенное определение, и мы займемся им далее. Здесь важно лишь отметить, что индексирование по символьным полям происходит значительно быстрее, если они имеют фиксированную длину. Если длина строк не слишком колеблется или, что еще лучше, постоянна, то, вероятно, лучше выбрать для поля тип CHAR. Хороший кандидат на тип CHAR — код страны. Стандартом ISO определены двух символьные коды для всех стран. CHAR(2) будет правильным выбором для данного поля.

Чтобы подходить для типа CHAR, поле необязательно должно быть фиксированной длины, но длина не должна сильно колебаться. Телефонные номера, к примеру, можно смело хранить в поле CHAR(13), хотя длина номеров различна в разных странах. Просто различие не столь велико, поэтому нет смысла делать поле для номера телефона переменным по длине. В отношении поля типа CHAR важно помнить, что, вне зависимости от реальной длины хранимой строки, в поле будет ровно столько символов, сколько указано в его размере — не больше и не меньше. Разность в длине между размером сохраняемого текста и размером поля заполняется пробелами. Не стоит беспокоиться по поводу нескольких лишних символов при хранении телефонных номеров, но не хотелось бы тратить много места в некоторых других случаях. Для этого существуют текстовые поля переменной длины.

Хороший пример поля, для которого требуется тип данных с переменной длиной, дает URL Интернет. По большей части адреса Web занимают сравнительно немного места — http://www.ora.com, http://www.hughes.com.au, http://www.mysql.com — и не представляют проблемы. Иногда, однако, можно наткнуться на адреса подобного вида:

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

Поля переменной длины.Преимуществом текстовых полей переменной длины является то, что они используют ровно столько места, сколько необходимо для хранения отдельной величины. Например, поле типа VARCHAR(255) , в котором хранится строка «hello, world», занимает только двенадцать байтов (по одному байту на каждый символ плюс еще один байт для хранения длины). В отличие от стандарта ANSI, в MySQL поля типа VARCHAR не дополняются пробелами. Перед записью из строки удаляются лишние пробелы.

Сохранить строки, длина которых больше, чем заданный размер поля, нельзя. В поле VARCHAR(4) можно сохранить строку не длиннее 4 символов. Если вы попытаетесь сохранить строку «happy birthday», MySQL сократит ее до «happ». Недостатком подхода MySQL к хранению полей переменной длины является то, что не существует способа сохранить необычную строку, длина которой превосходит заданное вами значение. В таблице 3.5 показан размер пространства, необходимого для хранения 144-символьного URL, продемонстрированного выше, и обычного, 30-символьного URL.

Таблица 3.5.Пространство памяти, необходимое для различных символьных типов MySQL

Тип данных Пространство для хранения строки из 144 символов Пространство для хранения строки из 30 символов Максимальная длина строки
СНАR(150)
VARCHAR(150)
TINYTEXT(150)
ТЕХТ(150)
MEDIUMTEXT(150)
LONGTEXT(150)

Если через годы работы со своей базой данных вы обнаружите, что мир изменился, и поле, уютно чувствовавшее себя в типе VARCHAR(25), должно теперь вмещать строки длиной 30 символов, не все потеряно. В MySQL есть команда ALTER TABLE , позволяющая переопределить размер поля без потери данных.

ALTER TABLE mytable MODIFY mycolumn LONGTEXT

Двоичные типы данных.В MySQL есть целый ряд двоичных типов данных, соответствующих своим символьным аналогам. Двоичными типами, поддерживаемыми MySQL, являются CHAR BINARY, VARCHAR BINARY, TINYBLOB, BLOB, MEDIUMBLOB и LONGBLOB. Практическое отличие между символьными типами и их двоичными аналогами основано на принципе кодировки. Двоичные данные просто являются куском данных, которые MySQL не пытается интерпретировать. Напротив, символьные данные предполагаются представляющими текстовые данные из используемых человеком алфавитов. Поэтому они кодируются и сортируются, основываясь на правилах, соответствующих рассматриваемому набору символов. Двоичные же данные MySQL сортирует в порядке ASCII без учета регистра.

Перечисления и множества.MySQL предоставляет еще два особых типа данных. Тип ENUM позволяет при создании таблицы указать список возможных значений некоторого поля. Например, если бы у вас была колонка с именем «фрукт», в которую вы разрешили бы помещать только значения «яблоко», «апельсин», «киви» и «банан», ей следовало бы присвоить тип ENUM:

CREATE TABLE meal(meal_id INT NOT NULL PRIMARY KEY,

фрукт ENUM(‘яблоко’, ‘апельсин’, ‘киви’, ‘банан’))

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

Тип MySQL SET позволяет одновременно хранить в поле несколько значений.

Другие типы данных.Любые мыслимые данные можно хранить с помощью числовых или символьных типов. В принципе, даже числа можно хранить в символьном виде. Однако то, что это можно сделать, не означает, что это нужно делать. Рассмотрим, к примеру, как хранить в базе данных де­нежные суммы. Можно делать это, используя INT или REAL. Хотя интуитивно REAL может показаться более подходящим — в конце концов, в денежных суммах нужны десятичные знаки, — на самом деле более правильно использовать INT. В полях, содержащих значения с плавающей запятой, таких как REAL, часто невозможно найти число с точным десятичным значением. Например, если вы вводите число 0.43, которое должно представлять сумму $0.43, MySQL может записать его как 0.42999998. Это небольшое отличие может вызвать проблемы при совершении большого числа математических операций.

MySQL предоставляет специальные типы данных для таких денежных сумм. Одним из них является тип MONEY, другим — DATE.

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

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

CREATE INDEX index_name ON tablename (column1, column2, columnN)

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

CREATE TABLE materials (id INT NOT NULL,

name CHAR(50) NOT NULL,

INDEX index1 (id, name),

UNIQUE INDEX index2 (name))

В этом примере для таблицы создается два индекса. Первый индекс index1 состоит из полей id и name. Второй индекс включает в себя только поле name и указывает, что значения поля name должны быть уникальными. Если вы попытаетесь вставить в поле name значение, которое уже есть в этом поле в какой-либо строке, операция не будет осуществлена. Все поля, указанные в уникальном индексе, должны быть объявлены как NOT NULL .

Хотя мы создали отдельный индекс для поля name, отдельно для поля id мы не создавали ин­декса. Если такой индекс нам понадобится, создавать его не нужно — он уже есть. Когда ин­декс содержит более одной колонки (например, name, rank, и serial_number), MySQL читает колонки в порядке слева направо. Благодаря используемой MySQL структуре индекса всякое подмножество колонок с левого края автоматически становится индексом внутри «главного» индекса. Например, когда вы создаете индекс name, rank, serial_number, создаются также «свободные» индексы name и name вместе с rank. Однако индексы rank или name и serial_number не создаются, если не потребовать этого явно.

MySQL поддерживает также семантику ANSI SQL для особого индекса, называемого первичным ключом. В MySQL первичный ключ — это уникальный индекс с именем PRIMARY. Назначив при создании таблицы колонку первичным ключом, вы делаете ее уникальным индексом, который будет поддерживать объединения таблиц. В следующем примере создается таблица cities с первичным ключом id.

CREATE TABLE cities (id INT NOT NULL PRIMARY KEY,

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

Последовательности и автоинкрементирование.Лучше всего, когда первичный ключ не имеет в таблице никакого иного значения, кроме значения первичного ключа. Для достиже­ния этого лучшим способом является создание числового первичного ключа, значение кото­рого увеличивается при добавлении в таблицу новой строки. Если вернуться к примеру с таблицей cities, то первый введенный вами город должен иметь id, равный 1, второй — 2, тре­тий — 3, и т. д. Чтобы успешно управлять такой последовательностью первичных ключей, нужно иметь какое-то средство, гарантирующее, что в данный конкретный момент только один клиент может прочесть число и увеличить его на единицу. В базе данных с транзакция­ми можно создать таблицу, скажем, с именем sequence, содержащую число, представляющее очередной id. Когда необходимо добавить новую строку в таблицу, вы читаете число из этой таблицы и вставляете число на единицу большее. Чтобы эта схема работала, нужно быть уверенным, что никто другой не сможет произвести чтение из таблицы, пока вы не ввели но­вое число. В противном случае два клиента могут прочесть одно и то же значение и попы­таться использовать его в качестве значения первичного ключа в одной и той же таблице.

MySQL не поддерживает транзакции, поэтому описанный механизм нельзя использовать для генерации уникальных чисел. Использовать для этих целей команду MySQL LOCK TABLE обременительно. Тем не менее СУБД предоставляет свой вариант понятия последовательно­сти, позволяющий генерировать уникальные идентификаторы, не беспокоясь о транзакциях.

Последовательности.При создании таблицы в MySQL можно одну из колонок специфицировать как AUTO_INCREMENT. В этом случае, при добавлении новой строки, имеющей значение NULL или 0 в данной колонке, автоматически будет происходить замена на значение на единицу больше, чем наибольшее текущее значение в колонке. Колонка с модификатором AUTO_INCREMENT должна быть индексирована. Ниже приведен пример использования поля типа AUTO_INCREMENT:

CREATE TABLE cities (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

Когда вы первый раз добавляете строку, поле id получает значение 1, если в команде INSERT для него используется значение NULL или 0. Например, следующая команда использует возможность AUTO_INCREMENT:

INSERT INTO cities (id, name, pop)

VALUES (NULL, ‘Houston’, 3000000)

Если вы выполните эту команду, когда в таблице нет строк, поле id получит значение 1, а не NULL. В случае, когда в таблице уже есть строки, полю будет присвоено значение на 1 большее, чем наибольшее значение id в данный момент.

Другим способом реализации последовательностей является использование значения, возвращаемого функцией LAST_INSERT_ID:

UPDATE table SET >

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

Добавление данных.Добавление данных в таблицу является одной из наиболее простых операций SQL. Несколько примеров этого вы уже видели. MySQL поддерживает стандартный синтаксис INSERT:

INSERT INTO table_name (column1, column2, . columnN)

VALUES (value1, value2, . valueN)

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

INSERT INTO addresses (name, address, city, state, phone, age)

VALUES(‘Irving Forbush’, ‘123 Mockingbird Lane’, ‘Corbin’, ‘KY’, ‘(800) 555-1234’, 26)

Кроме того, управляющий символ — по умолчанию ‘\’ — позволяет вводить в литералы одиночные кавычки и сам символ ‘\’:

# Ввести данные в каталог Stacie’s Directory, который находится

INSERT INTO files (description, location)

VALUES (‘Stacie\’s Directory’, ‘C:\\Personal\\Stacie’)

MySQL позволяет опустить названия колонок, если значения задаются для всех колонок и в том порядке, в котором они были указаны при создании таблицы командой CREATE. Однако если вы хотите использовать значения по умолчанию, нужно задать имена тех колонок, в которые вы вводите значения, отличные от установленных по умолчанию. Если для колонки не установлено значение по умолчанию, и она определена как NOT NULL, необходимо включить эту колонку в команду INSERT со значением, отличным от NULL. MySQL позволяет указать значение по умолчанию при создании таблицы в команде CREATE.

Новые версии MySQL поддерживают INSERT для одновременной вставки нескольких строк:

INSERT INTO foods VALUES (NULL, ‘Oranges’, 133, 0, 2, 39),

(NULL, ‘Bananas’, 122, 0, 4, 29),

(NULL, ‘Liver’, 232, 3, 15. 10)

Хотя поддерживаемый MySQL нестандартный синтаксис удобно использовать для быстрого выполнения задач администрирования, не следует без крайней нужды пользоваться им при написании приложений. Как правило, следует придерживаться стандарта ANSI SQL2 настолько близко, насколько MySQL это позволяет. Благодаря этому вы получаете возможность перейти в будущем на какую-нибудь другую базу данных. Переносимость особенно важна для тех, у кого потребности среднего масштаба, поскольку такие пользователи предполагают когда-нибудь перейти на полномасштабную базу данных.

MySQL поддерживает синтаксис SQL2, позволяющий вводить в таблицу результаты запроса:

INSERT INTO foods (name, fat)

SELECT food_name, fat_grams FROM recipes

Обратите внимание, что число колонок в INSERT соответствует числу колонок в SELECT. Кроме того, типы данных колонок в INSERT должны совпадать с типами данных в соответствующих колонках SELECT. И, наконец, предложение SELECT внутри команды INSERT не должно содержать модификатора ORDER BY и не может производить выборку из той же таблицы, в которую вставляются данные командой INSERT.

Изменение данных.Если ваша база не является базой данных «только для чтения», вам, вероятно, понадобится периодически изменять данные. Стандартная команда SQL для изменения данных выглядит так:

SET column1=value1, column2=value2, . columnN=valueN

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

SET end_year = begin_year+5

В этой команде значение колонки end_year устанавливается равным значению колонки begin_year плюс 5 для каждой строки таблицы.

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

SET lead_singer = ‘Ian Anderson’

WHERE band_name = ‘Jethro Tull’

Эта команда — UPDATE — указывает, что нужно изменить значение в колонке lead_singer для тех строк, в которых band_name совпадает с «Jethro Tull». Если рассматриваемая колонка не является уникальным индексом, предложение WHERE может соответствовать нескольким строкам. Многие команды SQL используют предложение WHERE, чтобы отобрать строки, над которыми нужно совершить операции. Поскольку по колонкам, участвующим в предложении WHERE, осуществляется поиск, следует иметь индексы по тем их комбинациям, которые обычно используются.

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

DELETE FROM table_name [WHERE clause]

Как и в других командах, допускающих использование предложения WHERE, его использование является необязательным. Если предложение WHERE опущено, то из таблицы будут удалены все записи!

Запросы.Самая часто используемая команда SQL — та, которая позволяет просматривать данные в базе: SELECT. Ввод и изменение данных произво­дятся лишь от случая к случаю, и большинство баз данных в основном занято тем, что предоставляет данные для чтения. Общий вид команды SELECT следующий:

SELECT column1, column2, . columnN

FROM table1, table2, . tableN

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

В первой части команды SELECT перечисляются колонки, которые вы хотите извлечь. Можно задать «*», чтобы указать, что вы хотите извлечь все колонки. В предложении FROM указываются таблицы, в которых находятся эти колонки. Предложение WHERE указывает, какие именно строки должны использоваться, и позволяет определить, каким образом должны объединяться две таблицы.

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

SELECT book, title, author. name

FROM author, book

WHERE book.author = author.id

Рассмотрим базу данных, в которой таблица book имеет вид, как в таблице 3.6.

Таблица 3.6.Таблица книг

ID Title Author Pages
The Green Mile
Guards, Guards!
Imzadi
Gold
Howling Mad

А таблица авторов author имеет вид таблицы 3.7.

Таблица 3.7.Таблица авторов

ID Name Citizen
Isaac Asimov US
Terry Pratchet UK
Peter David US
Stephen King US
Neil Gaiman UK

В результате внутреннего объединения создается таблица, в которой объединяются поля обеих таблиц для строк, удовлетворяющих запросу в обеих таблицах. В нашем примере запрос указывает, что поле author в таблице book должно совпадать с полем id таблицы author. Результат выполнения этого запроса представлен в таблице 3.8.

Таблица 3.8.Результаты запроса с внутренним объединением

Book Title Author Name
The Green Mile Stephen King
Guards, Guards! Terry Pratchet
Imzadi Peter David
Gold Isaac Asimov
Howling Mad Peter David

В этих результатах нет автора с именем Neil Gaiman, поскольку его author.id не найден в таблице book.author. Внутреннее объединение содержит только те строки, которые точно соответствуют запросу. Ниже мы обсудим понятие внешнего объединения, которое оказывается полезным в случае, когда в базу данных внесен писатель, у которого нет в этой базе книг.

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

SELECT long_field_names_are_annoying AS myfield

WHERE myfield = ‘Joe’

# Псевдоним таблицы в MySQL

SELECT people.names, tests.score

FROM tests really_long_people_table_name AS people

Группировка и упорядочение.По умолчанию порядок, в котором появляются результаты выборки, не определен. К счастью, SQL предоставляет некоторые средства наведения порядка в этой случайной последовательности. Первое средство — упорядочение. Вы можете потребовать от базы данных, чтобы выводимые результаты были упорядочены по некоторой колонке. Например, если вы укажете, что запрос должен упорядочить результаты по полю last_name, то результаты будут выведены в алфавитном порядке по значению поля last_name. Упорядочение осуществляется с помощью предложения ORDER BY:

SELECT last_name, first_name, age

ORDER BY last_name, first_name

В данном случае упорядочение производится по двум колонкам. Можно проводить упорядочение по любому числу колонок, но все они должны быть указаны в предложении SELECT. Если бы в предыдущем примере мы не выбрали поле last_name, то не смогли бы упорядочить по нему.

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

mysql> SELECT name, rank, salary FROM people\g

name rank salary
Jack Smith Private
Jane Walker General
June Sanders Private
John Barker Sergeant
Jim Castle Sergeant

5 rows in set (0.01 sec)

После группировки по званию (rank) выдача изменяется:

mysql> SELECT rank FROM people GROUP BY rank\g

rank
General
Private
Sergeant

3 rows in set (0.01 sec)

После применения группировки можно, наконец, найти среднюю зарплату (salary) для каждого звания. О функциях, используемых в этом примере, мы поговорим позднее.

mysql> SELECT rank, AVG(salary) AS income FROM people GROUP BY rank\g

rank income
General
Private
Sergeant

3 rows in set (0 04 sec)

Дата добавления: 2015-11-04 ; просмотров: 1468 ; ЗАКАЗАТЬ НАПИСАНИЕ РАБОТЫ

Записки инженера

Доступным языком заметки по IT технологиям

Структура и синтаксис SQL (выжимка)

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

Если вы только знакомитесь с БД, то рекомендую прочесть пост:

Теоретические азы БД (введение в SQL)

Для того, чтобы использовать язык SQL необходима СУБД, которая будет его исполнять. В качестве такой СУБД, мы будем рассматривать MySQL, как наиболее популярную в web разработке. Если вам нужна помощь в установки MySQL под Windows или FreeBSD, то рекомендую прочесть пост:

Установка MySQL

После установки любой СУБД, первым делом возникает вопрос, а как собственно с ней работать? С чего и как посылать SQL запросы, как добавить или удалить пользователя, изменить пароль, сделать резервную копию и восстанавливается из неё. Ответы на такого рода вопросы вы найдете в посте:

Как работать с MySQL?

Теперь перейдем к сути поста — «структура и синтаксис SQL».

Если вам лень читать весь пост, то для быстрого поиска, я привел список вопросов на которые вы найдете ответы

Язык SQL (в реализации MySQL)

Начиная описывать SQL под MySQL, хочу сразу обозначить, что данный пост не является единственным верным источником информации (кстати, как любой блог в Интернете). Единственно верным источником информации всегда является официальная документация, для MySQL она располагается по ссылке — http://dev.mysql.com/doc/ (документация к сожалению англоязычная, в левой части сайта вы выбираете версию MySQL, справа оглавление, по середине таблица для удобного ориентирование по разделам документации для: администраторов, разработчиков, интеграторов (Connectors & APIs) и т.п.). Перевод документации на русский, как я понимаю, происходит не переодично, а от случая к случаю и не разработчиками, а сторонними конторами. Могу предложить следующий сайт http://www.mysql.ru/docs/, содержащий как мне кажется, наиболее свежую версию перевода.

p.s. В текущий момент (версия 5.7) MySQL реализует стандарт SQL версии 2003 года (пока существует следующие SQL стандарты SQL-87, SQL-89, SQL-92, SQL:1999, SQL:2003, SQL:2006, SQL:2008), если вам вдруг будет интересно в него заглянуть, то я вас огорчу, найти его в открытом доступе вам не удастся (из доступных стандартов бесплатно оказался только SQL-92 — http://www.contrib.andrew.cmu.edu/


shadow/sql/sql1992.txt). Последние версии стандарта SQL языка (2006 и 2008 год), можно купить на сайте ISO (http://www.iso.org/). Стандарты SQL начинаются с «ISO/IEC 9075», введя её в поиске, вы увидите список документов формата ISO/IEC 9075-X:Y, где X– раздел стандарта (про разделы читайте на википедии http://ru.wikipedia.org/wiki/SQL), а Y – год выпуска стандарта.

1. Типы данных в SQL

Типы данных, которыми мы будем манипулировать, могут варьироваться в зависимости от СУБД. В нашем случае в качестве СУБД мы рассматриваем MySQL, т.к. данная система, наиболее часто используемая под web разработку.

Типы данных MySQL можно разделить на группы:

— числовые типы с фиксированной точкой

TINYINT – целое число (+-128), занимаемый размер 1 байт

SMALLINT – «малое» целое число (+-32768), занимаемый размер 2 байт

MEDIUMINT – «среднее» целое чило (+-8388608), занимаемый размер 3 байт

INTEGER или INT – «обычное» целое число (+-2147483648), занимаемый размер 4 байт

BIGINT — «большое» целое число (+-9223372036854775808), занимаемый размер 8 байт

DECIMAL (x,y) — число, в котором x разрядов в числе, y знаков после запятой

NUMERIC (x,y) в MySQL это тоже самое, что и DECIMAL

Если перед именем типа использовать ключевое слово UNSIGNED, то величина для числового типа будет только положительной, причем максимальное положительное число будет больше в два раза прежнего, т.е. диапазон сдвинется, например INT это от -2147483648 до +2147483647, а UNSIGNED INT от 0 до 4294967295 .

— числовые типы с плавающей точкой

REAL – вещественное число, размером 8 байт

DOUBLE PRECISION или DOUBLE — в MySQL такое же вещественное число, как и REAL, размер 8 байт

FLOAT (x) – вещественное число, точность указывается в скобках в байтах, если ничего не указать то точность — 4 байт, можно также использовать вариант FLOAT (x,y), где x – так же точность в байтах, а y количество знаком после запятой.

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

CHAR (x) – строка с длинной x (от 1 до 255), если значения меньше этой длины, то оно заполняется пробелами, если больше урезается, выделяемый размер под данный тип x байт.

VARCHAR (x) – строка с длинной x (от 1 до 255), длина строки зависит от длины значения, пробелами ничего не заполняется, если значение больше заданной длины x, то оно, как и в CHAR урезается, выделяемый размер под данный тип зависит от длины значения.

TINYTEXT или TINYBLOB строка c максимальной длиной текста 255, занимаемый размер зависит от длины текста (максимум 255 байт)

TEXT или BLOB строка c максимальной длиной текста 65535, занимаемый размер зависит от длины текста (максимум 65535 байт)

Отличие TEXT от BLOB заключается только в том, что в TEXT при сравнение не учитывается регистр (заглавная или маленькая буква).

MEDIUMTEXT или MEDIUMBLO – строка с максимальной длиной текста 16777215, занимаемый размер зависит от длины текста (максимум 16777215 байт)

LONGBLOB или LONGTEXT – строка с максимальной длиной текста 4294967295, занимаемый размер зависит от длины текста (максимум 4294967295 байт)

ENUM (‘value1′,’value2’,…) — символьный тип перечисления, может принимать значение из списка допустимых значений, максимум 65535 байт

SET (‘value1′,’value2’,…) – символьный тип – множество, которое содержит значения указанные в скобках, максимум 64 значения, максимальный занимаемый размер 64.

DATETIME – дата и время в формате YYYY-MM-DD HH:MM:SS, диапазон допустимых значений от ‘1000-01-01 00:00:00’ до ‘9999-12-31 23:59:59’, занимаемый объем 8 байт.

DATE – дата в формате YYYY-MM-DD, диапазон допустимых значений от ‘1000-01-01’ до ‘9999-12-31’, занимаемый объем 3 байта.

TIMESTAMP – дата и время в формате YYYYMMDDHHMMSS, занимаемый объем 4 байт.

TIME – время в формате HH:MM:SS, диапазон допустимых значений от ‘-838:59:59’ до ‘838:59:59’, занимаемый объем 3 байта.

YEAR – год в формате YYYY, занимаемый объем 1 байт.

2. Синтаксис языка SQL

Синтаксис языка SQL традиционно делят на три части:

1) Первая часть языка SQL отвечающая за определение данными (Date Definition Language или DDL), в неё входят такие действия, как:

  • создать БД;
  • удалить БД;
  • создать таблицу;
  • изменить таблицу;
  • удалить таблицу;
  • создать первичный/внешний ключ;
  • удалить первичный/внешний ключ.

С помощью DDL задают схему БД (Что такое схема БД? — статья в википедии).

2) Вторая часть языка SQL отвечающая за манипуляцию данными (Date Manipulation Language или DML), в неё входят такие действия, как:

  • вставить запись (строку) в таблицу;
  • выбрать запись в таблице;
  • обновить запись в таблице;
  • удалить запись в таблице.

3) Третья часть языка SQL отвечающая за управлениями данными (Data Control Language или DCL), в неё входят такие действия, как:

  • управление транзакциями;
  • управление правами доступа.

Если кратко, то данная часть языка нужна для администрирования БД.

Мы будем рассматривать данные части SQL по очереди, начиная с 1-ой

Содержание

Лекция 1. Введение в MySQL

Компьютерные системы хранения

В наши дни люди часто говорят о базах данных. Компьютеры составляют неотъемлемую часть современного общества, поэтому нередко можно услышать фразы вроде «Я поищу твою запись в базе данных». И речь идет не о больших ящиках, где хранятся груды папок, а о компьютерных системах, предназначенных для ускоренного поиска информации.

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

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

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

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

MySQL — это быстрая, надежная, открыто распространяемая СУБД . MySQL, как и многие другие СУБД , функционирует по модели «клиент/сервер». Под этим подразумевается сетевая архитектура, в которой компьютеры играют роли клиентов либо серверов. На рис. 1.1 изображена схема передачи информации между компьютером клиента и жестким диском сервера.

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

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

Строки таблиц могут быть связаны друг с другом одним из трех способов. Простейшее отношение — «один к одному». В этом случае строка первой таблицы соответствует одной единственной строке второй таблицы. На диаграммах такое отношение выражается записью 1:1.

Отношение «один ко многим» означает ситуацию, когда строка одной таблицы соответствует нескольким строкам другой таблицы. Это наиболее распространенный тип отношений. На диаграммах он выражается записью 1:N.

Наконец, при отношении «многие ко многим» строки первой таблицы могут быть связаны с произвольным числом строк во второй таблице. Такое отношение записывается как N:M.

Программист, работающий с базой данных, не заботится о том, как эти данные хранятся, и приложения, взаимодействующие с СУБД , не знают о способе записи данных на диск. «Снаружи» виден лишь логический образ данных, и это позволяет менять код СУБД , не затрагивая код самих приложений.

Подобная обработка данных осуществляется посредством языка четвертого поколения ( 4GL ), который поддерживает запросы, записываемые и исполняемые немедленно. Данные быстро утрачивают свою актуальность, поэтому скорость доступа к ним важна. Кроме того, программист должен иметь возможность формулировать новые запросы. Они называются нерегламентированными (ad hoc), поскольку не хранятся в самой базе данных и служат узкоспециализированным целям.

Язык четвертого поколения позволяет создавать схемы — точные определения данных и отношений между ними. Схема хранится как часть базы данных и может быть изменена без ущерба для данных.

Схема предназначена для контроля целостности данных. Если, к примеру, объявлено, что поле содержит целочисленные значения, то СУБД откажется записывать в него числа с плавающей запятой или строки. Отношения между записями тоже четко контролируются, и несогласованные данные не допускаются. Операции можно группировать в транзакции, выполняемые по принципу «все или ничего».

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

СУБД поддерживает параллельный доступ к базе данных. Приложения могут обращаться к базе данных одновременно, что повышает общую производительность системы. Кроме того, отдельные операции могут «распараллеливаться» для еще большего улучшения производительности.

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

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

Системы управления файлами

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

Системы управления файлами нельзя классифицировать как СУБД , так как обычно они являются частью операционной системы и ничего не знают о внутреннем содержимом файлов. Это знание заложено в прикладных программах, работающих с файлами. В качестве примера можно привести таблицу пользователей UNIX, хранящуюся в файле /etc/passwd. Программы, обращающиеся к этому файлу, знают, что в его первом поле находится имя пользователя, оканчивающееся двоеточием. Если приложению нужно отредактировать эту информацию, оно должно непосредственно открыть файл и позаботиться о правильном форматировании полей.

Такая модель базы данных очень неудобна, поскольку она требует использовать язык третьего поколения ( 3GL ). В результате время программирования запросов увеличивается, а программист должен обладать более высокой квалификацией, так как ему нужно продумать не только логическую, но и физическую структуру хранения данных. Это приводит к тому, что между приложением и файлом образуется тесная связь. Вся информация о полях таблиц закодирована в приложении. Другое приложение, обращающееся к тому же файлу, вынуждено дублировать существующий код.

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

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

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

Иерархические базы данных

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

На рис. 1.2 изображена простая иерархическая база данных, в которой фиксируется деятельность независимого подрядчика. Корень дерева представляет собой запись о клиенте. Ее потомками являются две записи о счет-фактурах и три записи об оплатах счетов. Структура счета номер 17 уточняется в трех дочерних записях, у счета номер 23 одна такая запись.

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

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

Сетевые базы данных

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

Следуя спецификации CODASYL, сетевая модель поддерживает DDL (Data Definition Language — язык определения данных) и DML ( Data Manipulation Language — язык обработки данных ). Это специальные языки, предназначенные для определения структуры базы данных и составления запросов. Несмотря на их наличие, программист по-прежнему должен знать структуру базы данных.

В сетевой модели допускаются отношения «многие ко многим», а записи не зависят друг от друга. При удалении записи удаляются и все ее связи, но не сами связанные записи.

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

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

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

Реляционные базы данных

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

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

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

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

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

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

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

Объектно-ориентированные базы данных

Объектно-ориентированная база данных (ООБД) позволяет программистам, которые работают с языками третьего поколения, интерпретировать все свои информационные сущности как объекты, хранящиеся в оперативной памяти. Дополнительный интерфейсный уровень абстракции обеспечивает перехват запросов, обращающихся к тем частям базы данных, которые находятся в постоянном хранилище на диске. Изменения, вносимые в объекты, оптимальным образом переносятся из памяти на диск.

Преимуществом ООБД является упрощенный код. Приложения получают возможность интерпретировать данные в контексте того языка программирования, на котором они написаны. Реляционная база данных возвращает значения всех полей в текстовом виде, а затем они приводятся к локальным типам данных. В ООБД этот этап ликвидирован. Методы манипулирования данными всегда остаются одинаковыми независимо от того, находятся данные на диске или в памяти.

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

С помощью ООБД решаются две проблемы. Во-первых, сложные информационные структуры выражаются в них лучше, чем в реляционных базах данных, а во-вторых, устраняется необходимость транслировать данные из того формата, который поддерживается в СУБД . Например, в реляционной СУБД размерность целых чисел может составлять 11 цифр, а в используемом языке программирования — 16. Программисту придется учитывать эту ситуацию.

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

Большим недостатком объектно-ориентированных баз данных является их тесная связь с применяемым языком программирования. К данным, хранящимся в реляционной СУБД , могут обращаться любые приложения, тогда как, к примеру, Java-объект, помещенный в ООБД, будет представлять интерес лишь для приложений, написанных на Java.

Объектно-реляционные базы данных

Объектно-реляционные СУБД объединяют в себе черты реляционной и объектной моделей. Их возникновение объясняется тем, что реляционные базы данных хорошо работают со встроенными типами данных и гораздо хуже — с пользовательскими, нестандартными. Когда появляется новый важный тип данных, приходится либо включать его поддержку в СУБД , либо заставлять программиста самостоятельно управлять данными в приложении.

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

Перестройка СУБД с целью включения в нее поддержки нового типа данных — не лучший выход из положения. Вместо этого объектно-реляционная СУБД позволяет загружать код, предназначенный для обработки «нетипичных» данных. Таким образом, база данных сохраняет свою табличную структуру, но способ обработки некоторых полей таблиц определяется извне, т.е. программистом.

Основные характеристики MySQL

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

Клиентские программы могут работать не только в режиме командной строки. Есть и графические клиенты, например MySQL GUI, PhpMyAdmin и др. Но они – тема отдельного курса.

MySQL взаимодействует с базой данных на языке, называемом SQL (Structured Query Language — язык структурированных запросов).

SQL предназначен для манипуляции данными, которые хранятся в Системах управления реляционными базами данных (RDBMS). SQL имеет команды, с помощью которых данные можно извлекать, сортировать, обновлять, удалять и добавлять. Стандарты языка SQL определяет ANSI (American National Standards Institute). В настоящее время действует стандарт, принятый в 2003 году (SQL-3).

SQL можно использовать с такими RDBMS как MySQL, mSQL, PostgreSQL, Oracle, Microsoft SQL Server, Access, Sybase, Ingres. Эти системы RDBMS поддерживают все важные и общепринятые операторы SQL, однако каждая из них имеет множество своих собственных патентованных операторов и расширений.

SQL является общим языком запросов для нескольких баз данных различных типов. Данный курс рассматривает систему MySQL, которая является RDBMS c открытым исходным кодом, доступной для загрузки на сайте MySQL.com.

Вот как характеризуют MySQL её разработчики.

  • MySQL — это система управления базами данных.

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

  • MySQL — это система управления реляционными базами данных.

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

  • Программное обеспечение MySQL — это ПО с открытым кодом.

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

  • Технические возможности СУБД MySQL

ПО MySQL является системой клиент-сервер, которая содержит многопоточный SQL-сервер, обеспечивающий поддержку различных вычислительных машин баз данных, а также несколько различных клиентских программ и библиотек, средства администрирования и широкий спектр программных интерфейсов (API).

Система безопасности основана на привилегиях и паролях с возможностью верификации с удаленного компьютера, за счет чего обеспечивается гибкость и безопасность. Пароли при передаче по сети при соединении с сервером шифруются. Клиенты могут соединяться с MySQL, используя сокеты TCP/IP, сокеты Unix или именованные каналы (named pipes, под NT)

Начиная с MySQL версии 3.23, где используется новый тип таблиц, максимальный размер таблицы доведен до 8 миллионов терабайт (2 63 bytes). Однако следует заметить, что операционные системы имеют свои собственные ограничения по размерам файлов. Ниже приведено несколько примеров:

— 32-разрядная Linux-Intel – размер таблицы 4 Гб.

— Solaris 2.7 Intel — 4 Гб

— Solaris 2.7 UltraSPARC — 512 Гб

— WindowsXP — 4 Гб

Как можно видеть, размер таблицы в базе данных MySQL обычно лимитируется операционной системой. По умолчанию MySQL-таблицы имеют максимальный размер около 4 Гб. Для любой таблицы можно проверить/определить ее максимальный размер с помощью команд SHOW TABLE STATUS или myisamchk -dv table_name . Если большая таблица предназначена только для чтения, можно воспользоваться myisampack , чтобы слить несколько таблиц в одну и сжать ее. Обычно myisampack ужимает таблицу по крайней мере на 50%, поэтому в результате можно получить очень большие таблицы.

Лекция 2. Подготовка к работе с MySQL

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

Загрузка MySQL

MySQL можно инсталлировать двумя способами: скомпилировав исходные коды программы или воспользовавшись предварительно скомпилированными двоичными файлами. Первый вариант допускает больше возможностей в плане конфигурации, но более продолжителен. Второй вариант удобнее, так как есть готовые дистрибутивы для многих операционных систем. На текущий момент существуют версии MySQL для FreeBSD, HP-UX, IBM AIX, Linux, Mac OS X, SCO, SGI Irix, Solaris и многих вариантов Microsoft Windows.

Информацию обо всех дистрибутивах можно получить на Web-узле http://www.mysql.com. Там же публикуются последние новости о программе.

Проверка исходных требований

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

Выбор версии

Команда разработчиков MySQL публикует тестовые и стабильные версии дистрибутивов отдельно. Информацию о статусе той или иной версии программы можно найти на Web-узле. Эти же сведения закодированы в названии дистрибутива.

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

Инсталляция с помощью менеджера пакетов RedHat Linux

Если программа MySQL инсталлируется в Linux , то лучше всего воспользоваться модулем RPM (RedHat Packet Manager— менеджер пакетов RedHat). MySQL работает в Linux версий 2.0 и выше. Тестирование программы выполнялось в RedHat 6.2. В программе используется библиотека glibc , подключаемая статически. Если в системе установлена более старая версия библиотеки, программу придется скомпилировать заново.

В таблице 2.1 приведено описание доступных модулей RPM (для последней версии пакета MySQL 4.01.10, на момент создания этого материала)

Таблица 2.1.
MySQL-4.01.10-1.i386.rpm Содержит все файлы, необходимые для запуска сервера MySQL, включая клиентские программы.
MySQL-4.01.10-l.src.rpm Содержит все исходные коды MySQL
MySQL-bench-4.01.10-1.i386.rpm Содержит программы, предназначенные для тестирования производительности MySQL. Для запуска тестов необходим основной дистрибутив, а также интерпретатор Perl.
MySQL-client-4.01.10-1.i386.rpm Содержит лишь клиентские программы
MySQL-devel-4.01.10-1.i386.rpm Содержит библиотеки и файлы заголовков , необходимые для компиляции клиентских программ
MySQL-shared-4.01.10-1.i386.rpm Содержит совместно используемые библиотеки для клиентских программ

Опытные пользователи Linux знают, что флаг -i служит программе rpm указанием инсталлировать пакет. Таким образом, основной модуль MySQL инсталлируется следующей командой:

В результате инсталляции в каталог /etc/rc.d добавляется файл сценария, содержащий команду запуска сервера MySQL после перезагрузки компьютера. Однако сам серверный домен запускается немедленно.

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

Можно также инсталлировать модуль RPM с исходными кодами программы. В этом случае воспользуйтесь опцией —rebuild , чтобы подготовить бинарный модуль.

Обычно пользователи инсталлируют лишь модули MySQL-4.01.10-1.i386.rpm и MySQL-client-4.01.10-l.i386.rpm . Для тех, кто собирается писать собственные клиентские программы, потребуется также модуль MySQL-devel-4.01.10-1.i386.rpm .

Инсталляция в Windows

Программа MySQL распространяется и в виде ZIP-архива, содержащего набор инсталляционных файлов. Перед извлечением файлов из архива создайте отдельный каталог, например с:\windows\mysql, так как в архиве нет информации о путевых именах файлов.

Чтобы приступить к инсталляции, выполните двойной щелчок на файле setup.ехе, после чего начнут появляться различные диалоговые окна. Первый вопрос, на который предстоит ответить, касается папки, куда должна быть помещена программа. По умолчанию предлагается папка с:\mysql. Можно выбрать любую другую папку, но в таком случае придется отредактировать конфигурационный файл.

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

Далее начнется собственно установка программы. Если инсталляционный каталог называется не с:\mysql, то по окончании инсталляции нужно будет дополнительно установить файл my.ini . Для этого перейдите в каталог программы и найдите файл my-example. cnf . Скопируйте его в системный каталог (с:\windows) и переименуйте в my.ini . Можно поступить и по-другому: скопировать файл в корневой раздел диска С: и назвать его my. cnf .

Теперь нужно отредактировать этот файл, чтобы переменная basedir указывала на инсталляционный каталог. Если соответствующая строка присутствует в виде комментария, удалите символы комментария. В противном случае добавьте эту строку самостоятельно, например:

Если программа MySQL инсталлируется в Windows 2000 (XP) , то, возможно, ее нужно запустить в виде сервиса. Для этого требуется перейти в режим командной строки и ввести следующую команду:

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

Если Вы хотите запустить MySQL вручную, то Вам потребуется выполнить следующую последовательность действий:

  • запустите сеанс MS-DOS и перейдите в каталог c:\mysql\bin
  • введите в строке приглашения:

или (в зависимости от версии)

тем самым будет запущен сервер MySQL.

  • введите «mysql» (без кавычек) в приглашении DOS.
  • приглашение изменится на приглашение «mysql».
  • чтобы протестировать сервер MySQL, введите в строке приглашения «show databases;»
  • на экране должно появиться окно, изображенное на рис 2.1

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

  • введите «quit» в приглашении mysql.
  • снова появится приглашение MS-DOS.
  • так как в данный момент работа закончена, необходимо остановить сервер MySQL. выполните в приглашении следующую команду.

Инсталляция вручную

Если программа MySQL инсталлируется не в Linux или Windows либо если услуги менеджера пакетов не нужны, можно инсталлировать двоичные файлы вручную. Соответствующий дистрибутив распространяется в виде tar-архива, сжатого с помощью программы gzip.

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

Программе MySQL нельзя предоставлять права суперпользователя, и никакие компромиссы здесь недопустимы. Можно, например, создать группу mysql и одноименного пользователя с помощью команд addgroup и adduser либо groupadd и useradd , в зависимости от версии UNIX. Ниже показан пример для RedHat Linux:

Обычно начальным каталогом MySQL выбирают /usr/local/mysql. После распаковки архива будет создан каталог, имя которого совпадает с именем дистрибутива, поэтому удобнее всего просто создать символическую ссылку mysql. Вот как это делается:

Далее необходимо запустить сценарий mysql_install_db , находящийся в каталоге scripts. Он создаст базу данных с описанием существующих привилегий и тестовую базу данных.

Как правило, программа MySQL инсталлируется от имени пользователя root , поэтому следующий шаг заключается в изменении владельца всех файлов программы:

Теперь можно запустить демон MySQL с помощью сценария safe_mysqld . Следующая команда запускает демон от имени пользователя mysql:

Если нужно, чтобы сервер MySQL запускался всякий раз после перезагрузки компьютера, добавьте соответствующую строку в файл /etc/rc.d/rc.local или же скопируйте сценарий mysql.server в каталог /etc/init.d и создайте правильные символические ссылки на него. В комментариях к файлу support-files/mysql.server рекомендуются такие ссылки:

Чтобы запустить программу клиента mysql вручную, введем в строке приглашения команду:

Система попросит ввести пароль. Введите пароль для root (mysqldata).

Если приглашение ввести пароль не появилось, то это может означать, что сервер MySQL не работает. Чтобы запустить сервер, перейдите в каталог /etc/rc.d/init.d/ и выполните команду ./mysql start (или mysql start , в зависимости от значения переменной PATH). Затем вызовите программу клиента mysql .

Если клиент MySQL работает, то появится приглашение mysql> . Введите в строке приглашения следующее:

На экране должен появиться вывод, как на рис. 2.1.

Теперь можно считать, что система MySQL успешно установлена в Linux.

Компиляция программы

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

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

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

Предоставление привилегий

Сценарий mysql_install_db предоставляет любому пользователю локального компьютера привилегии, позволяющие регистрироваться на сервере баз данных. Сетевые соединения не допускаются. По умолчанию любой пользователь имеет доступ к базе test, а пользователь root имеет полный доступ ко всем базам данных. Если в какой-то из баз хранится важная информация, нужно назначить суперпользователю пароль.

Программа MySQL не работает со списком пользователей, который есть у операционной системы. У нее своя таблица пользователей. Тем не менее, если при работе с имеющимися клиентскими программами не ввести имя пользователя в процессе регистрации на сервере, будет подставлено системное имя пользователя.

Чтобы поменять пароль пользователя root , нужно запустить интерпретатор команд MySQL от имени суперпользователя. Данный интерпретатор представляет собой программу mysql , путь к которой должен быть указан в переменной среды PATH .

Пользователям Windows придется вводить путевое имя целиком, например c:\mysql\bin\mysql. С помощью опции —user задается имя для регистрации. В нашем случае интерпретатор запускается с помощью такой команды:

Вызвав интерпретатор, необходимо обновить две строки в таблице user , касающиеся пользователя root . Это делает следующая инструкция:

В ответ на эту инструкцию интерпретатор отобразит две модифицируемые записи. Естественно, вместо строки ‘secret’ следует выбрать более надежный пароль. Этот пароль должен применяться лишь в административных целях.

Далее нужно сообщить серверу об изменении привилегий. Для этого предназначена такая инструкция:

Любой пользователь может захотеть создать персональную базу данных для собственных экспериментов, но делать это разрешено только пользователю root . Он же может создавать учетные записи новых пользователей и предоставлять им необходимые привилегии. Рассмотрим пример:

Первая инструкция создает базу данных mybase . Вторая инструкция создает учетную запись пользователя freak и предоставляет ему доступ к одноименной базе данных. Пароль для доступа — ‘secret’. Пользователь freak может подключаться к базе данных с любого компьютера, даже если он расположен в сети Internet.

В следующей лекции мы перейдем к рассмотрению основ собственно системы MySQL.

Лекция 3. Создание базы данных, основы работы с таблицами

В этой лекции мы научимся создавать базы данных.

Команды для создания базы данных в Windows и Linux одинаковы. Однако предварительные команды в Linux немного сложнее. Так как этот материал рассчитан на широкий круг читателей, в том числе и слабо знакомых с ОС Linux, то системы Windows и Linux будут рассмотрены по отдельности.

Создадим базу данных с именем employees, которая содержит данные о сотрудниках некой компании BigFoot. Предполагается хранить имя, зарплату, возраст, адрес, e-mail, дату рождения, увлечения, номера телефонов, и т.д. сотрудников.

Создание базы данных в Windows

1. Запустите сервер MySQL, выполняя команду mysqld- shareware -standalone в строке приглашения в каталоге c:\mysql\bin. Более подробно об этом сказано выше, в лекции об установке MySQL в Windows.

2. Затем вызовите программу клиента mysql , вводя в строке приглашения mysql .

3. Приглашение изменится на mysql> . Введите команду:

(Примечание: Команда заканчивается символом точки с запятой).

4. Сервер MySQL должен ответить примерно как на рис. 3.1

[Запрос обработан, изменилась 1 строка (0.00 сек)]

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

Сервер ответит списком баз данных, как показано на рис. 3.2.

Здесь показаны три базы данных, две были созданы MySQL во время установки и вновь созданная база данных employees .

6. Чтобы вернуться снова к приглашению DOS, введите команду quit в приглашении mysql .

Создание базы данных в Linux

1. Пусть пользователь работает под своей учетной записью, а не как суперпользователь root. Необходимо запустить терминальный сеанс и стать суперпользователем (Для этого выполните команду su и введите пароль суперпользователя root ).

2. Запустим сервер MySQL. Вводим:

Система предлагает ввести пароль пользователя root MySQL, который был задан при установке MySQL в Linux. (Примечание: Это пароль пользователя root системы MySQL, а не пользователя root системы Linux). Введите пароль, который не изображается на экране по соображениям безопасности.

После успешной регистрации, система выводит приветствие и приглашение mysql, как показано на рис.3.3

(Вас приветствует монитор MySQL. Команды заканчиваются символами ; или \g. id соединения с MySQL равен 1 для сервера версии: 5.01.01. Введите ‘help’, чтобы получить справку).

3. Теперь можно создавать базу данных employees . Выполните команду:

(Примечание: команда заканчивается точкой с запятой)

4. Важно отметить, что эта база данных создается пользователем root и поэтому будет доступна только тем пользователям, которым это разрешит root . Чтобы использовать эту базу данных с другой учетной записью, например, freak , необходимо задать соответствующие полномочия, выполняя следующую команду:

Эта команда предоставляет учетной записи freak@localhost все полномочия на базу данных employees и задает пароль pass . Для любого другого пользователя freak можно заменить на любое другое имя пользователя и выбрать подходящий пароль.

5. Закройте сеанс mysql , вводя в приглашении команду quit . Выйдите из режима суперпользователя и перейдите в свою учетную запись. (Введите exit ).

6. Чтобы соединиться с MySQL с помощью обычной учетной записи, введите:

Затем введите после приглашения пароль. (Этот пароль был задан выше командой GRANT ALL.. .). После успешной регистрации в MySQL система выведет приветственное сообщение. Сеанс пользователя должен выглядеть как показано на рис. 3.4.

7. Ввод команды SHOW DATABASES; выведет список всех доступных в системе баз данных.

На экране должно появиться окно, аналогичное рис. 3.2.

Введите quit в строке приглашения mysql> , чтобы выйти из программы клиента mysql .

Команда CREATE DATABASE

Синтаксис команды CREATE DATABASE имеет вид:

Команда CREATE DATABASE создает базу данных с указанным именем. Для использования команды необходимо иметь привилегию CREATE для базы данных. Если база данных с таким именем существует, генерируется ошибка.

Опция спецификация_сrеаtе может указываться для определения характеристик базы данных. Характеристики базы данных сохраняются в файле db.opt , расположенном в каталоге данных. Конструкция CHARACTER SET определяет набор символов для базы данных по умолчанию. Конструкция COLLATION задает порядок сопоставления по умолчанию.

Базы данных в MySQL реализованы в виде каталогов, которые содержат файлы, соответствующие таблицам базы данных. Поскольку изначально в базе нет никаких таблиц, оператор CREATE DATABASE только создает подкаталог в каталоге данных MySQL.

Работа с таблицами

Теперь рассмотрим команды MySQL для создания таблиц базы данных и выбора базы данных.

Базы данных хранят данные в таблицах. Чем же являются эти таблицы?

Проще всего таблицы можно представлять себе, как состоящие из строк и столбцов. Каждый столбец определяет данные определенного типа. Строки содержат отдельные записи.

Рассмотрим таблицу 3.1, в которой приведены персональные данные некоторых людей:

Таблица 3.1. Персональные данные
Имя Возраст Страна e-mail
Михаил Петров 28 Россия misha@yandex.ru
Джон Доусон 32 Австралия j. dow @australia.com
Морис Дрюон 48 Франция md@france.fr
Снежана 19 Болгария sneg@bulgaria.com

Приведенная выше таблица содержит четыре столбца, в которых хранятся имя, возраст, страна, и адрес e-mail. Каждая строка содержит данные одного человека. Эта строка называется записью. Чтобы найти страну и адрес e-mail Снежаны, сначала надо выбрать имя в первом столбце, а затем посмотреть содержимое третьего и четвертого столбцов этой же строки.

База данных может содержать множество таблиц, именно таблицы содержат реальные данные.

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

Команда SQL для создания такой таблицы выглядит следующим образом:

Примечание: в MySQL команды и имена столбцов не различают регистр символов, однако имена таблиц и баз данных могут зависеть от регистра в связи с используемой платформой (как в Linux). Поэтому можно вместо CREATE TABLE использовать create table .

За ключевыми словами CREATE TABLE следует имя создаваемой таблицы employee_data . Каждая строка внутри скобок представляет один столбец. Эти столбцы хранят для каждого сотрудника идентификационный номер ( emp_id ), фамилию ( l_name ), имя ( f_name ), должность ( title ), возраст ( age ), стаж работы в компании ( yos ), зарплату ( salary ), надбавки ( perks ), и адрес e-mail ( email ).

За именем каждого столбца следует тип столбца. Типы столбцов определяют тип данных, которые будет содержать столбец. В данном примере столбцы f_name , l_name , title и email будут содержать текстовые строки, поэтому тип столбца задан как varchar , что означает переменное количество символов. Максимальное число символов для столбцов varchar определяется числом, заключенным в скобки, которое следует сразу за именем столбца. Столбцы age , yos , salary и perks будут содержать числа (целые), поэтому тип столбца задается как int . Первый столбец ( emp_id ) содержит идентификационный номер ( id ) сотрудника. Его тип столбца выглядит несколько перегруженным, поэтому рассмотрим его по частям:

  • int : определяет тип столбца как целое число.
  • unsigned : определяет, что число будет без знака (положительное целое).
  • not null : определяет, что значение не может быть null (пустым); то есть каждая строка в этом столбце должна иметь значение.
  • auto_increment : когда MySQL встречается со столбцом с атрибутом auto_increment , то генерируется новое значение, которое на единицу больше, чем наибольшее значение в столбце. Поэтому мы не должны задавать для этого столбца значения, MySQL генерирует их самостоятельно. Из этого также следует, что каждое значение в этом столбце будет уникальным.
  • primary key : помогает при индексировании столбца, что ускоряет поиск значений. Каждое значение должно быть уникально. Ключевой столбец необходим для того, чтобы исключить возможность совпадения данных. Например, два сотрудника могут иметь одно и то же имя, и тогда встанет проблема – как различать этих сотрудников, если не задать им уникальные идентификационные номера. Если имеется столбец с уникальными значениями, то можно легко различить две записи. Лучше всего поручить присваивание уникальных значений самой системе MySQL.

Использование базы данных

База данных employees уже создана. Для работы с ней, необходимо её «активировать» или «выбрать». В приглашении mysql выполните команду:

На экране увидим ответ системы, как показано на рис. 3.5

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

Определить текущую базу данных можно несколькими способами:

  • определение имени базы данных при запуске

Введите в приглашении системы следующее:

  • определение базы данных с помощью оператора USE в приглашении mysql
  • Определение базы данных с помощью \u в приглашении mysql

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

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

После выбора базы данных employees , выполните в приглашении mysql команду CREATE TABLE .

Примечание: нажатие клавиши Enter после ввода первой строки изменяет приглашение mysql на -> . Это означает, что mysql понимает, что команда не завершена и приглашает ввести дополнительные операторы. Помните, что каждая команда mysql заканчивается точкой с запятой, а каждое объявление столбца отделяется запятой. Можно также при желании ввести всю команду на одной строке.

Вывод на экране должен соответствовать рис. 3.6.

Синтаксис команды CREATE TABLE

Общий формат инструкции CREATE TABLE таков:

Флаг TEMPORARY задает создание временной таблицы , существующей в течение текущего сеанса. По завершении сеанса таблица удаляется. Временным таблицам можно присваивать имена других таблиц, делая последние временно недоступными. Спецификатор IF NOT EXIST подавляет вывод сообщений об ошибках в случае, если таблица с указанным именем уже существует. Имени таблицы может предшествовать имя базы данных, отделенное точкой. Если это не сделано, таблица будет создана в базе данных, которая установлена по умолчанию.

Чтобы задать имя таблицы с пробелами, необходимо заключить его в обратные кавычки, например ‘courses list’. То же самое нужно будет делать во всех ссылках на таблицу, поскольку пробелы используются для разделения идентификаторов.

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

Типы столбцов более подробно будут рассмотрены в лекции 4.

Спецификация типа включает название типа и его размерность. По умолчанию столбцы принимают значения NULL . Спецификатор NOT NULL запрещает подобное поведение.

У любого столбца есть значение по умолчанию. Если оно не указано, программа MySQL выберет его самостоятельно. Для столбцов, принимающих значения NULL , значением по умолчанию будет NULL , для строковых столбцов — пустая строка, для численных столбцов — нуль. Изменить эту установку позволяет предложение DEFAULT .

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

Удаление таблиц

Для того, чтобы удалить таблицу, убедимся сперва, что она существует. Это можно проверить с помощью команды SHOW TABLES , как показано на рис. 3.7.

Для удаления таблицы используется команда DROP TABLE , как показано на рис. 3.8.

Теперь команда SHOW TABLES ; этой таблицы больше не покажет.

Синтаксис команды DROP TABLE

Инструкция DROP TABLE имеет следующий синтаксис:

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

Флаги RESTRICT и CASCADE предназначены для выполнения сценариев, созданных в других СУБД.

Лекция 4. Типы данных столбцов

MySQL поддерживает несколько типов столбцов , которые можно разделить на три категории: числовые типы данных, типы данных для хранения даты и времени и символьные (строковые) типы данных. В данной лекции мы вначале рассмотрим все возможные типы и приведём требования по хранению для каждого типа столбца, затем опишем свойства типов более подробно по каждой категории.

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

  • M — указывает максимальный размер вывода. Максимально допустимый размер вывода составляет 255 символов.
  • D — употребляется для типов данных с плавающей точкой и указывает количество разрядов, следующих за десятичной точкой. Максимально возможная величина составляет 30 разрядов, но не может быть больше, чем M-2.

Квадратные скобки (‘[‘ и ‘]’) указывают для типа данных группы необязательных признаков.

В таблице 4.1 представлены типы полей MySQL.

Таблица 4.1. Типы полей MySQL
TINYINT[(M)] [UNSIGNED] [ZEROFILL] Очень малое целое число. Диапазон со знаком от -128 до 127. Диапазон без знака от 0 до 255
BIT, BOOL Синонимы TINYINT(1)
SMALLINT[(M)] [UNSIGNED] [ZEROFILL] Малое целое число. Диапазон со знаком от -32768 до 32767. Диапазон без знака от 0 до 65535.
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] Целое число среднего размера. Диапазон со знаком от -8388608 до 8388607. Диапазон без знака от 0 до 16777215
INT[(M)] [UNSIGNED] [ZEROFILL] Целое число нормального размера. Диапазон со знаком от -2147483648 до 2147483647. Диапазон без знака от 0 до 4294967295.
INTEGER[(M)] [UNSIGNED] [ZEROFILL] Синоним для INT
BIGINT[(M)] [UNSIGNED] [ZEROFILL] Большое целое число. Диапазон со знаком от -9223372036854775808 до 9223372036854775807. Диапазон без знака от 0 до 18446744073709551615
FLOAT(точность) [UNSIGNED] [ZEROFILL] Число с плавающей точкой. Атрибут точности может иметь значение FLOAT и DOUBLE , описанными ниже. FLOAT(X) относится к тому же интервалу, что и соответствующие типы FLOAT и DOUBLE , но диапазон значений и количество десятичных знаков не определены.
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] Малое число с плавающей точкой обычной точности. Допустимые значения: от -3,402823466E+38 до -1,175494351E-38, 0, и от 1,175494351E-38 до 3,402823466E+38. Если указан атрибут UNSIGNED , отрицательные значения недопустимы. Атрибут M указывает количество выводимых пользователю знаков, а атрибут D — количество разрядов, следующих за десятичной точкой. Обозначение FLOAT без указания аргументов или запись вида FLOAT(X) , где X DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] Число с плавающей точкой удвоенной точности нормального размера. Допустимые значения: от -1,7976931348623157E+308 до -2,2250738585072014E-308, 0, и от 2,2250738585072014E-308 до 1,7976931348623157E+308. Если указан атрибут UNSIGNED , отрицательные значения недопустимы. Атрибут M указывает количество выводимых пользователю знаков, а атрибут D — количество разрядов, следующих за десятичной точкой. Обозначение DOUBLE без указания аргументов или запись вида FLOAT(X) , где 25 DECIMAL[(M[,D])][UNSIGNED] [ZEROFILL] или DEC[(M[,D])] [UNSIGNED] [ZEROFILL] или NUMERIC[(M[,D])][UNSIGNED] [ZEROFILL] «Неупакованное» число с плавающей точкой. Ведет себя подобно столбцу CHAR , содержащему цифровое значение. Термин «неупакованное» означает, что число хранится в виде строки и при этом для каждого десятичного знака используется один символ. Разделительный знак десятичных разрядов, а также знак ‘-‘ для отрицательных чисел не учитываются в M (но место для них зарезервировано). Если атрибут D равен 0, величины будут представлены без десятичного знака, т.е. без дробной части. Максимальный интервал значений типа DECIMAL тот же, что и для типа DOUBLE , но действительный интервал для конкретного столбца DECIMAL может быть ограничен выбором значений атрибутов M и D . Если указан атрибут UNSIGNED , отрицательные значения недопустимы. Если атрибут D не указан, его значение по умолчанию равно 0. Если не указан M , его значение по умолчанию равно 10.
DATE Дата. Поддерживается интервал от ‘1000-01-01’ до ‘9999-12-31’. MySQL выводит значения DATE в формате ‘YYYY-MM-DD’, но можно установить значения в столбец DATE , используя как строки, так и числа.
DATETIME Комбинация даты и времени. Поддерживается интервал от ‘1000-01-01 00:00:00’ до ‘9999-12-31 23:59:59’. MySQL выводит значения DATETIME в формате ‘YYYY-MM-DD HH:MM:SS’, но можно устанавливать значения в столбце DATETIME , используя как строки, так и числа.
TIMESTAMP[(M)] Временная метка. Интервал от ‘1970-01-01 00:00:00’ до некоторого значения времени в 2037 году. MySQL выводит значения TIMESTAMP в форматах YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD или YYMMDD в зависимости от значений M : 14 (или отсутствующее), 12, 8, или 6; но можно также устанавливать значения в столбце TIMESTAMP , используя как строки, так и числа. Столбец TIMESTAMP полезен для записи даты и времени при выполнении операций INSERT или UPDATE , так как при этом автоматически вносятся значения даты и времени самой последней операции, если эти величины не введены программой. Можно также устанавливать текущее значение даты и времени, задавая значение NULL .
TIME Время. Интервал от ‘-838:59:59’ до ‘838:59:59’. MySQL выводит значения TIME в формате ‘HH:MM:SS’, но можно устанавливать значения в столбце TIME , используя как строки, так и числа.
YEAR[(2|4)] Год в двухзначном или четырехзначном форматах (по умолчанию формат четырехзначный). Допустимы следующие значения: с 1901 по 2155, 0000 для четырехзначного формата года и 1970-2069 при использовании двухзначного формата (70-69). MySQL выводит значения YEAR в формате YYYY, но можно задавать значения в столбце YEAR , используя как строки, так и числа.
[NATIONAL] CHAR(M) [BINARY] Строка фиксированной длины, при хранении всегда дополняется пробелами в конце строки до заданного размера. Диапазон аргумента M составляет от 0 до 255 символов. Концевые пробелы удаляются при выводе значения. Если не задан атрибут чувствительности к регистру BINARY , то величины CHAR сортируются и сравниваются как независимые от регистра в соответствии с установленным по умолчанию алфавитом. Атрибут NATIONAL CHAR (или его эквивалентная краткая форма NCHAR ) представляет собой принятый в ANSI SQL способ указания, что в столбце CHAR должен использоваться установленный по умолчанию набор символов ( CHARACTER ).
CHAR Это синоним для CHAR(1) .
[NATIONAL] VARCHAR(M) [BINARY] Строка переменной длины. Примечание: концевые пробелы удаляются при сохранении значения (в этом заключается отличие от спецификации ANSI SQL). Диапазон аргумента M составляет от 0 до 255 символов. Если не задан атрибут чувствительности к регистру BINARY , то величины VARCHAR сортируются и сравниваются как независимые от регистра.
TINYBLOB, TINYTEXT Столбец типа BLOB или TEXT с максимальной длиной 255 символов.
BLOB, TEXT Столбец типа BLOB или TEXT с максимальной длиной 65535 символов.
MEDIUMBLOB, MEDIUMTEXT Столбец типа BLOB или TEXT с максимальной длиной 16777215 символов.
LONGBLOB, LONGTEXT Столбец типа BLOB или TEXT с максимальной длиной 4294967295 символов.
ENUM(‘значение1′,’значение2’. ) Перечисляемый тип данных. Объект строки может иметь только одно значение, выбранное из заданного списка величин ‘значение1’, ‘значение2’, . NULL или специальная величина ошибки «». Список ENUM может содержать максимум 65535 различных величин
SET(‘значение1′,’значение2’. ) Набор. Объект строки может иметь ноль или более значений, каждое из которых должно быть выбрано из заданного списка величин ‘значение1’, ‘значение2’, .. . Список SET может содержать максимум 64 элемента.

Числовые типы данных

MySQL поддерживает все числовые типы данных языка SQL92 по стандартам ANSI/ISO. Они включают в себя типы точных числовых данных ( NUMERIC , DECIMAL , INTEGER и SMALLINT ) и типы приближенных числовых данных ( FLOAT , REAL и DOUBLE PRECISION ). Ключевое слово INT является синонимом для INTEGER , а ключевое слово DEC — синонимом для DECIMAL .

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

В этом примере — 5 (точность) представляет собой общее количество значащих десятичных знаков, с которыми будет храниться данная величина, а цифра 2 (масштаб) задает количество десятичных знаков после запятой. Следовательно, в этом случае интервал величин, которые могут храниться в столбце salary , составляет от -99,99 до 99,99 (в действительности для данного столбца MySQL обеспечивает возможность хранения чисел вплоть до 999,99, поскольку можно не хранить знак для положительных чисел).

Величины типов DECIMAL и NUMERIC хранятся как строки, а не как двоичные числа с плавающей точкой, чтобы сохранить точность представления этих величин в десятичном виде. При этом используется по одному символу строки для каждого разряда хранимой величины, для десятичного знака (если масштаб > 0) и для знака ‘-‘ (для отрицательных чисел). Если параметр масштаба равен 0, то величины DECIMAL и NUMERIC не содержат десятичного знака или дробной части.

Максимальный интервал величин DECIMAL и NUMERIC тот же, что и для типа DOUBLE , но реальный интервал может быть ограничен выбором значений параметров точности или масштаба для данного столбца с типом данных DECIMAL или NUMERIC . Если конкретному столбцу присваивается значение, имеющее большее количество разрядов после десятичного знака, что разрешено параметром масштаба, то данное значение округляется до количества разрядов, разрешенного масштаба. Если столбцу с типом DECIMAL или NUMERIC присваивается значение, выходящее за границы интервала, заданного значениями точности и масштаба (или принятого по умолчанию), то MySQL сохранит данную величину со значением соответствующей граничной точки данного интервала.

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

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

Для типов REAL и DOUBLE PRECISION не предусмотрены установки точности. В MySQL оба типа реализуются как 8-байтовые числа с плавающей точкой удвоенной точности. Чтобы обеспечить максимальную совместимость, в коде, требующем хранения приблизительных числовых величин, должны использоваться типы FLOAT или DOUBLE PRECISION без указаний точности или количества десятичных знаков.

Если в числовой столбец попытаться записать величину, выходящую за границы допустимого интервала для столбца данного типа, то MySQL ограничит величину до соответствующей граничной точки данного интервала и сохранит результат вместо исходной величины.

Например, интервал столбца INT составляет от -2147483648 до 2147483647. Если попытаться записать в столбец INT число -9999999999, то оно будет усечено до нижней конечной точки интервала и вместо записываемого значения в столбце будет храниться величина -2147483648. Аналогично, если попытаться записать число 9999999999, то взамен запишется число 2147483647.

Если для столбца INT указан параметр UNSIGNED , то величина допустимого интервала для столбца останется той же, но его граничные точки сдвинутся к 0 и 4294967295. Если попытаться записать числа -9999999999 и 9999999999, то в столбце окажутся величины 0 и 4294967296.

Для команд ALTER TABLE , LOAD DATA INFILE , UPDATE и многострочной INSERT выводится предупреждение, если могут возникнуть преобразования данных вследствие вышеописанных усечений. В таблице 4.2 представлены наиболее часто используемые числовые типы полей MySQL.

Таблица 4.2. Наиболее часто используемые числовые типы полей MySQL.
Тип Байт От До
TINYINT 1 -128 127
SMALLINT 2 -32768 32767
MEDIUMINT 3 -8388608 8388607
INT 4 -2147483648 2147483647
BIGINT 8 -9223372036854775808 9223372036854775807

Типы данных даты и времени

Существуют следующие типы данных даты и времени : DATETIME , DATE , TIMESTAMP , TIME и YEAR . Каждый из них имеет интервал допустимых значений, а также значение «ноль», которое используется, когда пользователь вводит действительно недопустимое значение. Отметим, что MySQL позволяет хранить некоторые не вполне достоверные значения даты, например 1999-11-31. Причина в том, что управление проверкой даты входит в обязанности конкретного приложения, а не SQL-серверов. Для ускорения проверки правильности даты MySQL только проверяет, находится ли месяц в интервале 0-12 и день в интервале 0-31. Данные интервалы начинаются с 0, это сделано для того, чтобы обеспечить для MySQL возможность хранить в столбцах DATE или DATETIME даты, в которых день или месяц равен нулю. Эта возможность особенно полезна для приложений, которые предполагают хранение даты рождения — здесь не всегда известен день или месяц рождения. В таких случаях дата хранится просто в виде 1999-00-00 или 1999-01-00 (при этом не следует рассчитывать на то, что для подобных дат функции DATE_SUB() или DATE_ADD дадут правильные значения).

Ниже приведены некоторые общие рекомендации, полезные при работе с типами данных даты и времени:

MySQL извлекает значения для данного типа даты или времени только в стандартном формате, но в то же время пытается интерпретировать разнообразные форматы, которые могут поступать от пользователей (например, когда задается величина, которой следует присвоить тип даты или времени или сравнить со значением, имеющим один из этих типов). Тем не менее, поддерживаются только форматы, описанные в следующих разделах. Предполагается, что пользователь будет вводить допустимые значения величин, так как использование величин в других форматах может дать непредсказуемые результаты.

  • Хотя MySQL пытается интерпретировать значения в нескольких форматах, во всех случаях ожидается, что крайним слева будет раздел значения даты, содержащий год. Даты должны задаваться в порядке год-месяц-день (например, ’98-09-04′), а не в порядке месяц-день-год или день-месяц-год, т.е. не так, как мы их обычно записываем (например ’09-04-98′, ’04-09-98′).
  • MySQL автоматически преобразует значение, имеющее тип даты или времени, в число, если данная величина используется в числовом контексте, и наоборот.
  • Значение, имеющее тип даты или времени, которое выходит за границы установленного интервала или является недопустимым для этого типа данных (см. начало раздела), преобразуется в значение «ноль» для данного типа. (Исключение составляют выходящие за границы установленного интервала величины типа TIME , которые усекаются до соответствующей граничной точки заданного интервала TIME ). В таблице 4.3. представлены форматы значения «ноль» для каждого из типов столбцов:
Таблица 4.3. Нулевые значения для типов данных даты и времени
Тип столбца Значение «Ноль»
DATETIME ‘0000-00-00 00:00:00’
DATE ‘0000-00-00’
TIMESTAMP 00000000000000 (длина зависит от количества выводимых символов)
TIME ’00:00:00′
YEAR 0000
  • Значения ‘ноль’ — особые. Для их хранения или ссылок на них можно явно применять представленные в таблице значения, а можно использовать ‘0’, что легче в написании.
Типы данных DATETIME, DATE и TIMESTAMP

Далее рассмотрим типы DATETIME , DATE и TIMESTAMP , которые являются родственными типами данных. Опишем их свойства, общие черты и различия.

Тип данных DATETIME используется для величин, содержащих информацию как о дате, так и о времени. MySQL извлекает и выводит величины DATETIME в формате ‘YYYY-MM-DD HH:MM:SS’. Поддерживается диапазон величин от ‘1000-01-01 00:00:00’ до ‘9999-12-31 23:59:59’. («поддерживается» означает, что хотя величины с более ранними временными значениями, возможно, тоже будут работать, но нет гарантии того, что они будут правильно храниться и отображаться).

Тип DATE используется для величин с информацией только о дате, без части, содержащей время. MySQL извлекает и выводит величины DATE в формате ‘YYYY-MM-DD’. Поддерживается диапазон величин от ‘1000-01-01’ до ‘9999-12-31’.

Тип столбца TIMESTAMP обеспечивает тип представления данных, который можно использовать для автоматической записи текущих даты и времени при выполнении операций INSERT или UPDATE . При наличии нескольких столбцов типа TIMESTAMP только первый из них обновляется автоматически.

Для остальных (кроме первого) столбцов типа TIMESTAMP также можно задать установку в значение текущих даты и времени. Для этого необходимо просто установить столбец в NULL или в NOW() .

Любой столбец типа TIMESTAMP (даже первый столбец данного типа) можно установить в значение, отличное от текущих даты и времени. Это делается путем явной установки его в желаемое значение. Данное свойство можно использовать, например, если необходимо установить столбец TIMESTAMP в значение текущих даты и времени при создании строки, а при последующем обновлении этой строки значение столбца не должно изменяться.

Величины типа TIMESTAMP могут принимать значения от начала 1970 года до некоторого значения в 2037 году с разрешением в одну секунду. Эти величины выводятся в виде числовых значений.

Формат данных, в котором MySQL извлекает и показывает величины TIMESTAMP , зависит от количества показываемых символов. Это проиллюстрировано в таблице 4.4. Полный формат TIMESTAMP составляет 14 десятичных разрядов, но можно создавать столбцы типа TIMESTAMP и с более короткой строкой вывода:

Таблица 4.4. Формат данных TIMESTAMP в зависимости от количества извлекаемых разрядов
Тип столбца Формат вывода
TIMESTAMP(14) YYYYMMDDHHMMSS
TIMESTAMP(12) YYMMDDHHMMSS
TIMESTAMP(10) YYMMDDHHMM
TIMESTAMP(8) YYYYMMDD
TIMESTAMP(6) YYMMDD
TIMESTAMP(4) YYMM
TIMESTAMP(2) YY

Величины DATETIME , DATE и TIMESTAMP могут быть заданы любым стандартным набором форматов:

  • Как строка в формате ‘YYYY-MM-DD HH:MM:SS’ или в формате ‘YY-MM-DD HH:MM:SS’. Допускается «облегченный» синтаксис — можно использовать любой знак пунктуации в качестве разделительного между частями разделов даты или времени. Например, величины ’98-12-31 11:30:45′, ‘98.12.31 11+30+45′, ’98/12/31 11*30*45′ и ’98@12@31 11^30^45’ являются эквивалентными.
  • Как строка в формате ‘YYYY-MM-DD’ или в формате ‘YY-MM-DD’. Здесь также допустим «облегченный» синтаксис. Например, величины ’98-12-31′, ‘98.12.31’, ’98/12/31′ и ’98@12@31′ являются эквивалентными.
  • Как строка без разделительных знаков в формате ‘YYYYMMDDHHMMSS’ или в формате ‘YYMMDDHHMMSS’, при условии, что строка понимается как дата. Например, величины ‘19970523091528’ и ‘970523091528’ можно интерпретировать как ‘1997-05-23 09:15:28’, но величина ‘971122129015’ является недопустимой (значение раздела минут является абсурдным) и преобразуется в ‘0000-00-00 00:00:00’.
  • Как строка без разделительных знаков в формате ‘YYYYMMDD’ или в формате ‘YYMMDD’, при условии, что строка интерпретируется как дата. Например, величины ‘19970523’ и ‘970523’ можно интерпретировать как ‘1997-05-23’, но величина ‘971332’ является недопустимой (значения разделов месяца и дня не имеют смысла) и преобразуется в ‘0000-00-00’.
  • Как число в формате YYYYMMDDHHMMSS или в формате YYMMDDHHMMSS, при условии, что число интерпретируется как дата. Например, величины 19830905132800 и 830905132800 интерпретируются как ‘1983-09-05 13:28:00’.
  • Как число в формате YYYYMMDD или в формате YYMMDD, при условии, что число интерпретируется как дата. Например, величины 19830905 и 830905 интерпретируются как ‘1983-09-05’.
  • Как результат выполнения функции, возвращающей величину, приемлемую в контекстах типов данных DATETIME , DATE или TIMESTAMP (например, функции NOW() или CURRENT_DATE() ).
Тип данных TIME

MySQL извлекает и выводит величины типа TIME в формате ‘HH:MM:SS’ (или в формате ‘HHH:MM:SS’ для больших значений часов). Величины TIME могут изменяться в пределах от ‘-838:59:59’ до ‘838:59:59’. Причина того, что «часовая» часть величины может быть настолько большой, заключается в том, что тип TIME может использоваться не только для представления времени дня (которое должно быть меньше 24 часов), но также для представления общего истекшего времени или временного интервала между двумя событиями (который может быть значительно больше 24 часов или даже отрицательным).

Величины TIME могут быть заданы в различных форматах:

Как строка в формате ‘D HH:MM:SS.дробная часть’ (следует учитывать, что MySQL пока не обеспечивает хранения дробной части величины в столбце рассматриваемого типа). Можно также использовать одно из следующих «облегченных» представлений: HH:MM:SS.дробная часть , HH:MM:SS , HH:MM , D HH:MM:SS , D HH:MM , D HH или SS . Здесь D — это дни из интервала значений 0-33.

  • Как строка без разделителей в формате ‘HHMMSS’, при условии, что строка интерпретируется как дата. Например, величина ‘101112’ понимается как ’10:11:12′, но величина ‘109712’ будет недопустимой (значение раздела минут является абсурдным) и преобразуется в ’00:00:00′.
  • Как число в формате HHMMSS , при условии, что строка интерпретируется как дата. Например, величина 101112 понимается как ’10:11:12′. MySQL понимает и следующие альтернативные форматы: SS , MMSS , HHMMSS , HHMMSS.дробная часть . При этом следует учитывать, что хранения дробной части MySQL пока не обеспечивает.
  • Как результат выполнения функции, возвращающей величину, приемлемую в контексте типа данных типа TIME (например, такой функции, как CURRENT_TIME ).
Тип данных YEAR

Тип YEAR — это однобайтный тип данных для представления значений года.

MySQL извлекает и выводит величины YEAR в формате YYYY . Диапазон возможных значений — от 1901 до 2155.

Величины типа YEAR могут быть заданы в различных форматах:

  • Как четырехзначная строка в интервале значений от ‘1901’ до ‘2155’.
  • Как четырехзначное число в интервале значений от 1901 до 2155.
  • Как двухзначная строка в интервале значений от ’00’ до ’99’. Величины в интервалах от ’00’ до ’69’ и от ’70’ до ’99’ при этом преобразуются в величины YEAR в интервалах от 2000 до 2069 и от 1970 до 1999 соответственно.
  • Как двухзначное число в интервале значений от 1 до 99. Величины в интервалах от 1 до 69 и от 70 до 99 при этом преобразуются в величины YEAR в интервалах от 2001 до 2069 и от 1970 до 1999 соответственно. Необходимо принять во внимание, что интервалы для двухзначных чисел и двухзначных строк несколько различаются, так как нельзя указать «ноль» непосредственно как число и интерпретировать его как 2000. Необходимо задать его как строку ‘0’ или ’00’, или же оно будет интерпретировано как 0000.
  • Как результат выполнения функции, возвращающей величину, приемлемую в контексте типа данных YEAR (такой как NOW() ).

Недопустимые величины YEAR преобразуются в 0000.

Символьные типы данных

Существуют следующие символьные типы данных: CHAR , VARCHAR , BLOB , TEXT , ENUM и SET . Рассмотрим описание их работы, требований к их хранению и использования их в запросах. В таблице 4.5 приведены символьные типы данных и их размерность.

Таблица 4.5. Символьные типы данных
Тип Макс.размер Байт
TINYTEXT или TINYBLOB 2^8-1 255
TEXT или BLOB 2^16-1 (64K-1) 65535
MEDIUMTEXT или MEDIUMBLOB 2^24-1 (16M-1) 16777215
LONGBLOB 2^32-1 (4G-1) 4294967295
Типы данных CHAR и VARCHAR

Типы данных CHAR и VARCHAR очень схожи между собой, но различаются по способам их хранения и извлечения.

В столбце типа CHAR длина поля постоянна и задается при создании таблицы. Эта длина может принимать любое значение между 1 и 255. Величины типа CHAR при хранении дополняются справа пробелами до заданной длины. Эти концевые пробелы удаляются при извлечении хранимых величин.

Величины в столбцах VARCHAR представляют собой строки переменной длины. Так же как и для столбцов CHAR , можно задать столбец VARCHAR любой длины между 1 и 255. Однако, в противоположность CHAR , при хранении величин типа VARCHAR используется только то количество символов, которое необходимо, плюс один байт для записи длины. Хранимые величины пробелами не дополняются, наоборот, концевые пробелы при хранении удаляются.

Если задаваемая в столбце CHAR или VARCHAR величина превосходит максимально допустимую длину столбца, то эта величина соответствующим образом усекается.

Различие между этими двумя типами столбцов в представлении результата хранения величин с разной длиной строки в столбцах CHAR(4) и VARCHAR(4) проиллюстрировано следующей таблицей 4.6.

Таблица 4.6. Результат хранения величин с разной длиной строки типов Char и Varchar
Величина CHAR(4) Требуемая память VARCHAR(4) Требуемая память
» ‘ ‘ 4 байта » 1 байт
‘ab’ ‘ab ‘ 4 байта ‘ab’ 3 байта
‘abcd’ ‘abcd’ 4 байта ‘abcd’ 5 байтов
‘abcdefgh’ ‘abcd’ 4 байта ‘abcd’ 5 байтов

Извлеченные из столбцов CHAR(4) и VARCHAR(4) величины в каждом случае будут одними и теми же, поскольку при извлечении концевые пробелы из столбца CHAR удаляются.

Если при создании таблицы не был задан атрибут BINARY для столбцов, то величины в столбцах типа CHAR и VARCHAR сортируются и сравниваются без учета регистра. При задании атрибута BINARY величины в столбце сортируются и сравниваются с учетом регистра в соответствии с порядком таблицы ASCII на том компьютере, где работает сервер MySQL.

Типы данных BLOB и TEXT

Тип данных BLOB представляет собой двоичный объект большого размера, который может содержать переменное количество данных. Существуют 4 модификации этого типа — TINYBLOB , BLOB , MEDIUMBLOB и LONGBLOB , отличающиеся только максимальной длиной хранимых величин.

Тип данных TEXT также имеет 4 модификации — TINYTEXT , TEXT , MEDIUMTEXT и LONGTEXT , соответствующие упомянутым четырем типам BLOB и имеющие те же максимальную длину и требования к объему памяти. Единственное различие между типами BLOB и TEXT состоит в том, что сортировка и сравнение данных выполняются с учетом регистра для величин BLOB и без учета регистра для величин TEXT . Другими словами, TEXT — это независимый от регистра BLOB .

Если размер задаваемого в столбце BLOB или TEXT значения превосходит максимально допустимую длину столбца, то это значение соответствующим образом усекается.

Тип перечисления ENUM

ENUM (перечисление) — это столбец, который может принимать значение из списка допустимых значений, явно перечисленных в спецификации столбца в момент создания таблицы.

Перечисление может иметь максимум 65535 элементов.

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

Если вам нужно получить список возможных значений для столбца ENUM , вы должны вызвать SHOW COLUMNS FROM имя_таблицы LIKE имя_столбца_enum и проанализировать определение ENUM во втором столбце.

Тип множества SET

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

Например, столбец, определенный как SET(«один», «два») NOT NULL может принимать такие значения:

Множество SET может иметь максимум 64 различных элемента.

Оконечные пробелы удаляются из значений множества SET в момент создания таблицы.

Если вы вставляете в столбец SET некорректную величину, это значение будет проигнорировано.

Если вам нужно получить все возможные значения для столбца SET , вам следует вызвать SHOW COLUMNS FROM имя_таблицы LIKE имя_столбца_set и проанализировать SET-определение во втором столбце.

Выбор правильного типа данных в столбце

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

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

Если же требуется высокая точность, всегда можно выполнить конвертирование в тип данных с фиксированной точкой. Такие данные хранятся в виде BIGINT. Это позволяет выполнять все вычисления с ними как с целыми числами, а впоследствии при необходимости результаты можно преобразовать обратно в величины с плавающей точкой.

Требования к памяти для различных типов столбцов

Требования к объему памяти для столбцов каждого типа, поддерживаемого MySQL, перечислены ниже по категориям.

Требования к памяти для числовых типов приведены в таблице 4.7

Таблица 4.7. Требования к памяти для числовых типов
Тип столбца Требуемая память
TINYINT 1 байт
SMALLINT 2 байта
MEDIUMINT 3 байта
INT 4 байта
INTEGER 4 байта
BIGINT 8 байтов
FLOAT(X) 4, если X FLOAT 4 байта
DOUBLE 8 байтов
DOUBLE PRECISION 8 байтов
REAL 8 байтов
DECIMAL(M,D) M+2 байт, если D > 0, M+1 байт, если D = 0 (D+2, если M NUMERIC(M,D) M+2 байт, если D > 0, M+1 байт, если D = 0 (D+2, если M Таблица 4.8. Требования к памяти для типов даты и времени
Тип столбца Требуемая память
DATE 3 байта
DATETIME 8 байтов
TIMESTAMP 4 байта
TIME 3 байта
YEAR 1 байт

Требования к памяти для символьных типов приведены в таблице 4.9.

Таблица 4.9. Требования к памяти для символьных типов
Тип столбца Требуемая память
CHAR(M) M байт, 1 VARCHAR(M) L+1 байт, где L TINYBLOB, TINYTEXT L+1 байт, где L BLOB, TEXT L+2 байт, где L MEDIUMBLOB, MEDIUMTEXT L+3 байт, где L LONGBLOB, LONGTEXT L+4 байт, где L ENUM(‘value1′,’value2’. ) 1 или 2 байт, в зависимости от количества перечисляемых величин (максимум 65535)
SET(‘value1′,’value2’. ) 1, 2, 3, 4 или 8 байт, в зависимости от количества элементов множества (максимум 64)

Лекция 5. Работа с таблицами. Внесение, извлечение, поиск и удаление данных

Запись данных в таблицы

Оператор INSERT заполняет таблицу данными. Вот общая форма INSERT .

где table_name является именем таблицы, в которую надо внести данные; column1, column2 и т.д. являются именами столбцов, а value1, value2 и т.д. являются значениями для соответствующих столбцов.

Следующий оператор вносит первую запись в таблицу employee_data , которую мы рассматривали в лекции 3.

Как и другие операторы MySQL, эту команду можно вводить на одной строке или разместить ее на нескольких строках.

Несколько важных моментов:

  • Значениями для столбцов f_name , l_name , title и email являются текстовые строки, и они записываются в кавычках.
  • Значениями для age , yos , salary и perks являются числа (целые), и они не имеют кавычек.
  • Можно видеть, что данные заданы для всех столбцов кроме emp_id . Значение для этого столбца задает система MySQL, которая находит в столбце наибольшее значение, увеличивает его на единицу, и вставляет новое значение.

Если приведенная выше команда правильно введена в приглашении клиента mysql , то программа выведет сообщение об успешном выполнении, как показано на рис. 5.1.

Создание дополнительных записей требует использования отдельных операторов INSERT . Чтобы облегчить эту работу можно поместить все операторы INSERT в файл здесь. Это должен быть обычный текстовый файл с оператором INSERT в каждой строке.

Заполнение таблицы employee_data данными с помощью файла employee.dat

В системе Windows

1) Поместите файл в каталог c:\mysql\bin.

2) Проверьте, что MySQL работает.

3) Выполните команду

В системе Linux

1) Перейдите в каталог с файлом данных.

2) Выполните команду

3) Введите свой пароль.

Пусть таблица содержит теперь 21 запись (20 из файла employee.dat и одну, вставленную оператором INSERT в начале лекции).

Запрос данных из таблицы MySQL


Таблица employee_data содержит теперь достаточно данных, чтобы можно было начать с ней работать. Запрос данных выполняется с помощью команды MySQL SELECT . Оператор SELECT имеет следующий формат:

Часть оператора с условиями является необязательной (мы рассмотрим ее позже). По сути, требуется знать имена столбцов и имя таблицы, из которой извлекаются данные.

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

Оператор приказывает MySQL вывести все данные из столбцов f_name и l_name . Результат работы оператора представлен на рис. 5.2.

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

Чтобы вывести всю таблицу, можно либо ввести имена всех столбцов, либо воспользоваться упрощенной формой оператора SELECT .

Символ * в этом выражении означает ‘ВСЕ столбцы’. Поэтому этот оператор выводит все строки всех столбцов.

Рассмотрим ещё один пример.

Выборка столбцов f_name , l_name и age представлена на рис. 5.3.

Задания

1. Напишите оператор SQL для создания новой базы данных с именем addressbook

2. Какой оператор используется для получения информации о таблице? Как используется этот оператор?

3. Как получить список всех баз данных, доступных в системе?

4. Напишите оператор для записи следующих данных в таблицу employee_data

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

6. Как извлечь данные столбцов f_name , email из таблицы employee_data ?

7. Напишите оператор для вывода данных из столбцов salary , perks и yos таблицы employee_data .

8. Как узнать число строк в таблице с помощью оператора SELECT ?

9. Как извлечь данные столбцов salary , l_name из таблицы employee_data ?

Возможные решения

1. create database addressbook;

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

2. Оператор DESCRIBE , например:

3. SHOW DATABASES; (в приглашении mysql)

Примечание: Текстовые строки заключаются в кавычки.

5. SELECT emp_id, f_name, l_name, title, age, yos, salary, perks, email from employee_data;

Вторая форма лучше. Ее легче использовать и труднее ошибиться.

6. Чтобы вывести данные столбцов f_name и email , используем следующий оператор.

7. SELECT salary, perks, yos from employee_data;

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

9. select salary, l_name from employee_data;

Выборка данных с помощью условий

Теперь более подробно рассмотрим формат оператора SELECT . Его полный формат имеет вид:

В операторе SELECT условия являются необязательными.

Оператор SELECT без условий выводит все данные из указанных столбцов. Одним из достоинств RDBMS является возможность извлекать данные на основе определенных условий.

Теперь перейдём к рассмотрению операторов сравнения.

Операторы сравнения = и !=

Результат запроса приведен на рис. 5.4.

Этот оператор выводит имена и фамилии всех сотрудников, которые имеют имя Иван. Отметим, что слово Иван в условии заключено в одиночные кавычки. Можно использовать также двойные кавычки. Кавычки являются обязательными, так как MySQL будет порождать ошибку при их отсутствии. Кроме того сравнения MySQL не различают регистр символов, что означает, что с равным успехом можно использовать «Иван», «иван» и даже «ИвАн».

Результат запроса приведен на рис. 5.5.

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

Результат запроса приведен на рис. 5.6.

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

Оператор != означает ‘не равно’ и является противоположным оператору равенства.

Операторы больше и меньше

Давайте получим имена и фамилии всех сотрудников, которые старше 32 лет.

Результат запроса приведен на рис. 5.7.

Попробуем найти сотрудников, которые получают зарплату больше 120000.

Результат запроса приведен на рис. 5.8.

Теперь перечислим всех сотрудников, которые имеют стаж работы в компании менее 3 лет.

Результат запроса приведен на рис. 5.9.

Операторы =

Используемые в основном с целочисленными данными операторы меньше или равно ( ) и больше или равно ( >= ) обеспечивают дополнительные возможности.

Результат запроса приведен на рис. 5.10.

Выборка содержит имена, возраст и зарплаты сотрудников, которым больше 32 лет.

Результат запроса приведен на рис. 5.11.

Запрос выводит имена сотрудников, которые работают в компании не более 2 лет.

Задания

1. Напишите оператор SELECT для извлечения идентификационного номера сотрудников, которые старше 30 лет.

2. Напишите оператор SELECT для извлечения имен и фамилий всех Web-разработчиков.

3. Что выведет следующий оператор SELECT :

4. Как вывести зарплаты и надбавки сотрудников, которые получают в качестве надбавок более 16000?

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

Возможные решения

1. select emp_id from employee_data where age > 30;

2. select f_name, l_name from employee_data where title=’Web-разработчик’;

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

4. select salary, perks from employee_data where perks > 16000;

5. select l_name, f_name from employee_data where title = ‘бухгалтер’;

Поиск текстовых данных по шаблону

В данной части мы рассмотрим поиск текстовых данных по шаблону с помощью предложения where и оператора LIKE .

Оператор сравнения на равенство ( = ) помогает выбрать одинаковые строки. Таким образом, чтобы перечислить имена сотрудников, которых зовут Иван, можно воспользоваться следующим оператором SELECT .

Результат запроса приведен на рис. 5.12.

Как быть, если надо вывести данные о сотрудниках, имя которых начинается с буквы В? Язык SQL позволяет выполнить поиск строковых данных по шаблону. Для этого в предложении where используется оператор LIKE следующим образом.

Результат запроса приведен на рис. 5.13.

Можно видеть, что здесь в условии вместо знака равенства используется LIKE и знак процента в шаблоне.

Знак % действует как символ-заместитель (аналогично использованию * в системах DOS и Linux). Он заменяет собой любую последовательность символов. Таким образом «В%» обозначает все строки, которые начинаются с буквы В. Аналогично «%В» выбирает строки, которые заканчиваются символом В, а «%В%» строки, которые содержат букву В.

Давайте выведем, например, всех сотрудников, которые имеют в названии должности строку «про».

Результат запроса приведен на рис. 5.14.

Перечислим всех сотрудников, имена которых заканчиваются буквой ‘а’. Это очень просто сделать.

Результат запроса приведен на рис. 5.15.

Задания

1. Перечислить всех сотрудников, фамилии которых начинаются с буквы P.

2. Вывести имена всех сотрудников в отделе продаж.

3. Что выведет следующий оператор

4. Перечислить фамилии и должности всех программистов

Возможные решения

1. select l_name, f_name from employee_data where l_name like ‘P%’;

2. select f_name, l_name from employee_data where title like ‘%продавец%’;

3. Этот оператор выводит имена, фамилии и заплаты всех сотрудников, у которых имя содержит букву ‘к’.

4. SELECT l_name, title from employee_data where title like ‘%программист%’;

Предложение HAVING

Чтобы вывести среднюю зарплату сотрудников в различных подразделениях (должностях), используется предложение GROUP BY , например:

Результат запроса приведен на рис. 5.16.

Предположим теперь, что требуется вывести только те подразделения, где средняя зарплата более 100000. Это можно сделать с помощью предложения HAVING .

Результат запроса приведен на рис. 5.17.

Задание

Вывести подразделения и средний возраст, где средний возраст больше 30.

Возможное решение

Результат запроса приведен на рис. 5.18.

Удаление записей из таблицы

Для удаления записей из таблицы можно использовать оператор DELETE .

Оператор удаления DELETE требует задания имени таблицы и необязательных условий.

Примечание: Если никакие условия не будут заданы, то удаляются все данные в таблице.

Предположим, один из специалистов по мультимедиа ‘Василий Пупкин’ уволился из компании. Надо удалить его запись.

Результат запроса приведен на рис. 5.19.

Лекция 6. Логические операторы

В этой лекции мы рассмотрим, как выбрать данные на основе условий SQL, представленных с помощью булевых (логических) операторов .

Использовать их очень просто. Ниже показан оператор SELECT , который выводит имена сотрудников, которые получают более 70000, но меньше 90000.

На рис. 6.1. приведен результат запроса.

Давайте выведем список сотрудников, фамилии которых начинаются с буквы К или Л.

На рис. 6.2. приведен результат запроса.

Вот более сложный пример: список имен и возраста сотрудников, фамилии которых не начинаются с К или Л, и которые младше 30 лет.

На рис. 6.3. приведен результат запроса.

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

Оператор NOT поможет при поиске всех сотрудников, которые не являются программистами. (Программисты включают старших программистов, программистов мультимедиа и программистов).

На рис. 6.4. приведен результат запроса.

И последний пример перед упражнениями.

Показать всех сотрудников со стажем работы в компании более 3 лет, которые старше 30 лет.

На рис. 6.5. приведен результат запроса.

Задания

1. Вывести имена и фамилии всех сотрудников, которые получают зарплату не более 90000 и не являются программистами, старшими программистами или программистами мультимедиа.

2. Что делает следующий оператор?

3. Вывести все идентификационные номера и имена сотрудников в возрасте между 32 и 40 годами.

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

Возможные решения

Операторы IN и BETWEEN

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

На рис. 6.6. приведен результат запроса.

В SQL имеется более простой способ сделать это с помощью оператора IN (в множестве). Его использование не представляет никаких трудностей.

Результат будет аналогичен рис. 6.6.

Использование NOT перед IN позволяет вывести данные, которые не входят в множество, определяемое условием IN . Следующий оператор выводит список сотрудников, которые не занимают должность программиста или системного администратора.

Оператор BETWEEN используется для определения целочисленных границ. Поэтому вместо age >= 32 AND age можно использовать age BETWEEN 32 AND 40 .

На рис. 6.7. приведен результат запроса.

NOT также можно использовать вместе с BETWEEN , как в следующем операторе, который выводит сотрудников, зарплата которых меньше 90000 или больше 150000.

Задания

1. Найдите всех сотрудников, которые занимают должность «старший программист» и «программист мультимедиа».

2. Выведите список имен сотрудников, зарплата которых составляет от 70000 до 90000.

3. Что делает следующий оператор?

4. Вот более сложный оператор, который объединяет BETWEEN и IN . Что он делает?

Возможные решения

Упорядочивание данных

Рассмотрим вопрос о том, как можно изменить порядок вывода данных , извлеченных из таблиц MySQL, используя предложение ORDER BY оператора SELECT .

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

А вот так сотрудников можно отсортировать по возрасту.

Предложение ORDER BY может сортировать в возрастающем порядке ( ASCENDING или ASC ) или в убывающем порядке ( DESCENDING или DESC ) в зависимости от указанного аргумента.

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

Примечание: Возрастающий порядок ( ASC ) используется по умолчанию.

Задания

1. Вывести список сотрудников в порядке, определяемом зарплатой, которую они получают.

2. Выведите список сотрудников в убывающем порядке их стажа работы в компании.

3. Что делает следующий оператор?

4. Вывести список сотрудников (фамилию и имя), которые занимают должность «программист» или «разработчик Web» и отсортировать их фамилии по алфавиту.

Возможные решения

Примечание: Сначала сортируются должности в убывающем порядке. Затем для каждой должности сортируется возраст сотрудников в возрастающем порядке.

Ограничение количества извлекаемых данных

Далее рассмотрим, как ограничить число записей , выводимых оператором SELECT .

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

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

На рис. 6.8. приведен результат запроса.

Это первые пять записей таблицы.

Можно соединить оператор LIMIT с оператором ORDER BY . Таким образом, следующий оператор выведет четверых самых молодых сотрудников компании.

На рис. 6.9. приведен результат запроса.

Аналогично можно вывести двух самых молодых сотрудников.

На рис. 6.10. приведен результат запроса.

Извлечение подмножеств

LIMIT можно использовать также для извлечения подмножества данных, используя дополнительные аргументы.

Общая форма оператора LIMIT имеет следующий вид:

На рис. 6.11. приведен результат запроса.

Будут извлечены три строки, начиная с седьмой.

Задания

  1. Найдите имена 5 самых молодых сотрудников компании.
  2. Извлеките 5 записей, начиная с 10 строки.
  3. Выведите имя, фамилию и зарплату сотрудника, который получает самую большую зарплату.
  4. Что делает следующий оператор?

Возможные решения

Ключевое слово DISTINCT

Рассмотрим теперь, как выбрать и вывести записи таблиц MySQL с помощью ключевого слова DISTINCT (РАЗЛИЧНЫЙ), использование которого исключает появление повторяющихся данных.

Чтобы вывести все должности базы данных компании, можно выполнить следующий оператор:

На рис. 6.12. приведен результат запроса.

Можно видеть, что список содержит повторяющиеся данные. Предложение SQL DISTINCT выводит только уникальные данные. Вот как оно используется.

На рис. 6.13. приведен результат запроса.

Из этого можно видеть, что в компании имеется 11 уникальных должностей.

Уникальные записи можно также отсортировать с помощью ORDER BY .

На рис. 6.14. приведен результат запроса.

DISTINCT часто используется вместе с функцией COUNT , которая будет рассмотрена далее.

Задания

  1. Сколько уникальных вариантов зарплаты имеется в компании? Представьте их в убывающем порядке.
  2. Сколько различных имен имеется в базе данных?

Возможные решения

Изменение записей

Команда UPDATE выполняет изменение данных в таблицах. Она имеет очень простой формат.

Как и все другие команды SQL можно вводить ее на одной строке или на нескольких строках.

Рассмотрим несколько примеров.

Предположим, директор увеличил свою зарплату на 20000 и надбавки на 5000. Его предыдущая зарплата была 200000, а надбавки были 50000.

На рис. 6.15. приведен результат запроса.

Можно проверить эту операцию, выводя данные из таблицы.

На рис. 6.16. приведен результат запроса.

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

Результат запроса аналогичен рис. 6.15.

В качестве другого примера можно попробовать изменить название должности «разработчик Web» на «программист Web».

На рис. 6.17. приведен результат запроса.

Важно также перед выполнением изменений внимательно изучить часть оператора с условием, так как легко можно изменить не те данные. Оператор UPDATE без условий изменит все данные столбца во всех строках. Надо быть очень осторожным при внесении изменений.

Задания

  1. Измените фамилию Чащина на Петрова. Внесите соответствующие изменения в базу данных.
  2. Название должности «программист мультимедиа» необходимо изменить на «специалист по мультимедиа».
  3. Увеличьте зарплату всем сотрудниками (кроме директора) на 10000.

Возможные решения

Примечание: Если бы были еще сотрудники с фамилией Чащина, то эти записи также были бы изменены. В таком случае может помочь столбец emp_id , так как он содержит уникальные значения. Лучше использовать значение emp_id вместо l_name , как в следующем примере:

Лекция 7. Команды обработки данных

Поиск минимального и максимального значений

В MySQL имеются встроенные функции для вычисления минимального и максимального значений.

SQL имеет 5 агрегатных функций.

  1. MIN() : минимальное значение
  2. MAX() : максимальное значение
  3. SUM() : сумма значений
  4. AVG() : среднее значений
  5. COUNT() : подсчитывает число записей

В этом параграфе мы рассмотрим поиск минимального и максимального значений столбца.

Минимальное значение

На рис. 7.1. приведен результат запроса.

Максимальное значение

На рис. 7.2. приведен результат запроса.

Задания

  1. Найдите минимальные надбавки.
  2. Найдите максимальную зарплату среди всех «программистов».
  3. Найдите возраст самого старого «продавца».
  4. Найдите имя и фамилию самого старого сотрудника.

Возможные решения

1. mysql> select MIN(perks) from employee_data;

На рис. 7.3. приведен результат запроса.

На рис. 7.4. приведен результат запроса.

На рис. 7.5. приведен результат запроса.

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

На рис. 7.6. приведен результат запроса.

Поиск среднего значения и суммы

Суммирование значений столбца с помощью функции SUM

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

Давайте посмотрим, сколько компания BigFoot тратит на зарплату своих сотрудников.

На рис. 7.7. приведен результат запроса.

Аналогично можно вывести общую сумму надбавок, выдаваемых сотрудникам.

На рис. 7.8. приведен результат запроса.

Можно найти также общую сумму зарплаты и надбавок.

На рис. 7.9. приведен результат запроса.

Здесь показаны также дополнительные возможности команды SELECT. Значения можно складывать, вычитать, умножать или делить. В действительности можно записывать полноценные арифметические выражения.

Вычисление среднего значения

Агрегатная функция AVG() используется для вычисления среднего значения данных в столбце.

На рис. 7.10. приведен результат запроса.

Пример выше вычисляет средний возраст сотрудников компании BigFoot, а следующий выводит среднюю зарплату.

На рис. 7.11. приведен результат запроса.

Задания

  1. Вывести сумму всех возрастов сотрудников, работающих в компании BigFoot.
  2. Как вычислить общее количество лет стажа работы сотрудников в компании BigFoot?
  3. Вычислите сумму зарплат и средний возраст сотрудников, которые занимают должность «программист».
  4. Что делает следующий оператор?

Возможные решения

1. mysql> select SUM(age) from employee_data;

На рис. 7.12. приведен результат запроса.

2. mysql> select SUM(yos) from employee_data;

На рис. 7.13. приведен результат запроса.

На рис. 7.14. приведен результат запроса.

4. Этот оператор выводит процент зарплаты, который сотрудники BigFoot получают в качестве надбавок.

На рис. 7.15. приведен результат запроса.

Именование столбцов

MySQL позволяет задавать имена для выводимых столбцов. Поэтому вместо f_name или l_name и т.д. можно использовать более понятные и наглядные термины. Это делается с помощью оператора AS .

На рис. 7.16. приведен результат запроса.

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

На рис. 7.17. приведен результат запроса.

Подсчет числа записей

Агрегатная функция COUNT() подсчитывает и выводит общее число записей. Например, чтобы подсчитать общее число записей в таблице, выполните следующую команду.

На рис. 7.18. приведен результат запроса.

Как мы уже знаем, знак * означает «все данные».

Теперь давайте подсчитаем общее число сотрудников, которые занимают должность «программист».

На рис. 7.19. приведен результат запроса.

Группировка данных

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

На рис. 7.20. приведен результат запроса.

Можно видеть, что это аналогично использованию DISTINCT в предыдущей лекции.

Вот как можно подсчитать число сотрудников имеющих определенную должность.

На рис. 7.21. приведен результат запроса.

В предыдущей команде MySQL сначала создает группы различных должностей, а затем выполняет подсчет в каждой группе.

Сортировка данных

Теперь давайте найдем и выведем число сотрудников, имеющих различные должности, и отсортируем их с помощью ORDER BY .

На рис. 7.22. приведен результат запроса.

Задания

  1. Подсчитайте число сотрудников, которые проработали в BigFoot более трех лет.
  2. Подсчитайте количество сотрудников в группах одного возраста.
  3. Измените предыдущее задание так, чтобы возраст выводился в убывающем порядке.
  4. Найдите средний возраст сотрудников в различных подразделениях (должностях).
  5. Измените предыдущий оператор так, чтобы данные выводились в убывающем порядке среднего возраста.

Возможные решения

На рис. 7.23. приведен результат запроса.

На рис. 7.24. приведен результат запроса.

На рис. 7.25. приведен результат запроса.

На рис. 7.26. приведен результат запроса.

На рис. 7.27. приведен результат запроса.

Примечание: Нам нужно задать псевдо-имя для столбца, содержащего среднее значение возраста, чтобы его можно было сортировать.

Лекция 8. Математические функции

Математические функции MySQL

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

Эта функция возвращает модуль числа

На рис. 8.1(а) и 8.1(б) приведены примеры работы с функцией ABS .

Эта функция возвращает арксинус числа. Диапазон допустимых значений – от -1 до 1. Вне этого диапазона значение арксинуса не определено.

На рис. 8.2(а), 8.2(б) и 8.2(в) приведены примеры работы с функцией ASIN .

Эта функция возвращает арккосинус числа. Диапазон допустимых значений – от -1 до 1. Вне этого диапазона значение арккосинуса не определено.

На рис. 8.3(а), 8.3(б) и 8.3(в) приведены примеры работы с функцией ACOS .

Эта функция возвращает арктангенс числа.

На рис. 8.4(а), 8.4(б) и 8.4(в) приведены примеры работы с функцией ATAN .

Эта функция возвращает угол в радианах точки с заданными координатами.

На рис. 8.5(а), 8.5(б) и 8.5(в) приведены примеры работы с функцией ATAN2 .

Эта функция округляет число до ближайшего большего целого числа.

На рис. 8.6(а), 8.6(б) и 8.6(в) приведены примеры работы с функцией CEIL .

Возвращает косинус числа

На рис. 8.7 приведен пример работы с функцией COS .

Возвращает котангенс числа.

На рис. 8.8(а) и 8.8(б) приведены примеры работы с функцией COT .

Вычисляет проверочное значение в циклическом избыточном коде и возвращает 32-разрядное целое. Результат равен NULL , если передается аргумент NULL . Ожидается, что аргумент будет строкой, и будет рассматриваться в качестве таковой в противном случае.

На рис. 8.9 приведен пример работы с функцией CRC32 .

Возвращает аргумент, преобразованный из радианов в градусы.

На рис. 8.10 приведен пример работы с функцией DEGREES .

Эта функция возводит число e (основание натурального логарифма) в заданную степень.

На рис. 8.11(а) и 8.11(б) приведены примеры работы с функцией EXP .

Эта функция округляет число до ближайшего меньшего целого числа.

На рис. 8.12(а), 8.12(б) и 8.12(в) приведены примеры работы с функцией FLOOR .

Эта функция возвращает наибольшее значение из списка. Она может работать как с числами, так и со строками.

На рис. 8.13 приведен пример работы с функцией GREATEST .

Функция возвращает наименьшее значение из списка.

На рис. 8.14 приведен пример работы с функцией LEAST .

Эта функция возвращает натуральный логарифм числа.

На рис. 8.15(а) и 8.15(б) приведены примеры работы с функцией LN .

При вызове с одним параметром функция LOG возвращает натуральный логарифм числа, а при вызове с двумя параметрами — возвращает логарифм числа2 по основанию число1 .

На рис. 8.16(а) и 8.16(б) приведены примеры работы с функцией LOG2 .

LOG(число1, число2) эквивалентна LOG(число2) / LOG(число1) .

Возвращает логарифм числа по основанию 2.

На рис. 8.17(а) и 8.17(б) приведены примеры работы с функцией LOG .

Функция LOG2() удобна для того, чтобы определить, сколько бит потребуется для сохранения числа. Вместо нее можно использовать LOG(число) /LOG(2) .

Возвращает логарифм числа по основанию 10.

На рис. 8.18(а), 8.18(б) и 8.18(в) приведены примеры работы с функцией LOG10 .

число1 MOD число2

Эта функция возвращает остаток от деления первого числа на второе подобно оператору % .

На рис. 8.19(а), 8.19(б), 8.19(в) и 8.19(г) приведены примеры работы с функцией MOD .

Возвращает значение числа . По умолчанию отображается пять знаков после десятичной запятой, но внутренне MySQL использует полное представление действительного числа двойной точности.

На рис. 8.20(а) и 8.20(б) приведены примеры работы с функцией PI .

Возвращает значение число1 , возведенное в степень число2 .

На рис. 8.21(а), 8.21(б) и 8.21(в) приведены примеры работы с функцией POW .

Возвращает аргумент, преобразованный из градусов в радианы.

На рис. 8.22(а) и 8.22(б) приведены примеры работы с функцией RADIANS .

Возвращает случайное число двойной точности в диапазоне от 0 до 1. Если указан целочисленный аргумент, он служит начальным числом для генератора случайных чисел (генерируя повторяющуюся последовательность). Если аргумент отсутствует, используется значение системных часов.

На рис. 8.23(а) и 8.23(б) приведены примеры работы с функцией RAND .

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

ORDER BY RAND() в комбинации с LIMIT удобно для выбора случайного примера из набора строк:

Следует отметить, что RAND() в конструкции WHERE вычисляется заново при каждом выполнении WHERE .

Эта функция округляет число с плавающей запятой до целого числа или, если указан второй аргумент, до заданного количества цифр после запятой. Если точность отрицательная, обнуляется целая часть числа.

На рис. 8.24(а), 8.24(б), 8.24(в), 8.24(г), 8.24(д) и 8.24(е) приведены примеры работы с функцией ROUND .

Примеры SQL запросов к базе данных MySQL

Содержание статьи
1. Самые простые MySQL запросы
2. Простые SELECT (выбрать) запросы
3. Простые INSERT (новая запись) запросы
4. Простые UPDATE (перезаписать, дописать) запросы
5. Простые DELETE (удалить запись) запросы
6. Простые DROP (удалить таблицу) запросы
7. Сложные MySQL запросы
8. MySQL запросы и переменные PHP

1. Самые простые SQL запросы

1. Выведет список ВСЕХ баз.

2. Выведет список ВСЕХ таблиц в Базе Данных base_name.

2. Простые SELECT (выбрать) запросы к базе данных MySQL

SELECT – запрос, который выбирает уже существующие данные из БД. Для выбора можно указывать определённые параметры выбора. Например, суть запроса русским языком звучит так — ВЫБРАТЬ такие-то колонки ИЗ такой-то таблицы ГДЕ параметр такой-то колонки равен значению.

1. Выбирает ВСЕ данные в таблице tbl_name.

2. Выведет количество записей в таблице tbl_name.

3. Выбирает (SELECT) из(FROM) таблицы tbl_name лимит (LIMIT) 3 записи, начиная с 2.

4. Выбирает (SELECT) ВСЕ (*) записи из (FROM) таблицы tbl_name и сортирует их (ORDER BY) по полю id по порядку.

5. Выбирает (SELECT) ВСЕ записи из (FROM) таблицы tbl_name и сортирует их (ORDER BY) по полю id в ОБРАТНОМ порядке.

6. Выбирает (SELECT) ВСЕ (*) записи из (FROM) таблицы users и сортирует их (ORDER BY) по полю id в порядке возрастания, лимит (LIMIT) первые 5 записей.

7. Выбирает все записи из таблицы users, где поле fname соответствует значению Gena.

8. Выбирает все записи из таблицы users, где значение поля fname начинается с Ge.

9. Выбирает все записи из таблицы users, где fname заканчивается на na, и упорядочивает записи в порядке возрастания значения id.

10. Выбирает все данные из колонок fname, lname из таблице users.

Внимание! Старайтесь указывать конкретные колонки (как в примере 10). Это важно для того, чтобы запросы обрабатывались намного быстрее!

11. Допустим у Вас в таблице пользовательских данных есть страна. Так вот если Вы хотите вывести ТОЛЬКО список встречающихся значений (чтобы, например, Россия не выводилось 20 раз, а только один), то используем DISTINCT. Выведет, из массы повторяющихся значений Россия, Украина, Беларусь. Таким образом, из таблицы users колонки country будут выведены ВСЕ УНИКАЛЬНЫЕ значения

12. Выбирает ВСЕ данные строк из таблицы users где age имеет значения 18,19 и 21.

13. Выбирает МАКСИМАЛЬНОЕ значение age в таблице users. То есть если у Вас в таблице самое большее значение age(с англ. возраст) равно 55, то результатом запроса будет 55.

14. Выберет данные из таблицы users по полям name и age ГДЕ age принимает самое маленькое значение.

15. Выберет данные из таблицы users по полю name ГДЕ id НЕ РАВЕН 2.

3. Простые INSERT (новая запись) запросы

INSERT – запрос, который позволяет ПЕРВОНАЧАЛЬНО вставить запись в БД. То есть создаёт НОВУЮ запись (строчку) в БД.

1. Делает новую запись в таблице users, в поле name вставляет Сергей, а в поле age вставляет 25. Таким образом, в таблицу дописывается новая строки с данными значениями. Если колонок больше, то они оставшиеся останутся либо пустыми, либо с установленными по умолчанию значениями.

4. Простые UPDATE запросы к базе данных MySQL

UPDATE – запрос, который позволяет ПЕРЕЗАПИСАТЬ значения полей или ДОПИСАТЬ что-то в уже существующей строке в БД. Например, есть готовая строка, но в ней нужно перезаписать параметр возраста, так как он изменился со временем.

1. В таблице users ГДЕ id равно 3 значение поля age становится 18.

2. Всё то же самое, что и в первом запросе, просто показан синтаксис запроса, где перезаписываются два поля и более.
В таблице users ГДЕ id равно 3 значение поля age становится 18, а country Россия.

5. Простые DELETE (удалить запись) запросы к базе данных MySQL

DELETE – запрос, который удаляет строку из таблицы.

1. Удаляет строку из таблицы users ГДЕ id равен 10.

6. Простые DROP (удалить таблицу) запросы к базе данных MySQL

DROP – запрос, который удаляет таблицу.

1. Удаляет целиком таблицу tbl_name.

7. Сложные запросы к базе данных MySQL

Любопытные запросы, которые могут пригодиться даже опытным пользователям

Данный сложный запрос ВЫБИРАЕТ колонки id,name,country В ТАБЛИЦАХ users,admins ГДЕ registration_date (дата) не старше 14 дней И activation НЕ РАВНО , СОРТИРОВАТЬ по registration_date в обратном порядке (новое в начале).

Выше указан пример так называемого запроса в запросе в SQL. Обновить возраст среди пользователей на 18+, где пол — мужской. Подобные варианты запроса не рекомендую. По личному опыту скажу, лучше создать несколько отдельных — они будут прорабатываться быстрее.

8. Запросы к базе данных MySQL и PHP

В MySQL запросы в PHP странице можно вставлять переменные в качестве сравниваемых и тп значений. Пара примеров

1. Выбирает все записи из таблицы users, где поле fname соответствует значению переменной $name.

2. В таблице users ГДЕ id равно 3 значение поля age изменяется на значение переменной $age.

Внимание! Если Вам интересен какой-либо ещё пример, то пишите вопрос в комментарии!

Перенос БД с SQLite на MySQL на примере MediaWiki

Содержание

Рассматривая в этой статье перенос сайта на другую СУБД на примере MediaWiki, создаваемом для Википедии и распространяемом бесплатно, дадим также общие советы.

Обе бесплатные открытые СУБД SQLite и MySQL очень популярны, имеют свои преимущества, но предназначены для разных целей, потому если цели или условия нашего проекта поменялись, то возникает необходимость перехода от одной СУБД к другой. Возможно мы захотели получить удалённый доступ к базе данных, или разграничить права доступа к таблицам, или использовать какое-то конкретное средство администрирования – причин для перехода может быть несколько. К сожалению пока не обнаружено простого решения этой задачи выполняющего работу путем нажатия одной кнопки, но несложный алгоритм описанный в статье позволит выполнить всё самостоятельно, быстро и бесплатно.

Итак, у нас имеется сайт под управлением MediaWiki использующий SQLite, который мы решили перенести на MySQL. Во избежании проблем крайне рекомендуется при переходе использовать ту же самую версию движка, так как при смене версий может быть изменена структура базы данных. Можно вообще использовать этот же набор файлов MediaWiki, лишь изменяя в конфигурационном файле LocalSettings.php переменные доступа к базе данных (они имеют общее начало: $wgDB. ). Используя ту же версию мы можем не заботиться о переносе структуры, всё что нам остаётся – это перенести содержимое таблиц. Самым простым решением было бы выгрузить содержимое SQLite в виде текстового файла с последовательностью SQL команд и затем выполнить эти команды на MySQL. Однако, существует

Разница между реализацией SQL языка в SQLite и MySQL

В нашей конкретной задаче не имеет значения в чём различается функционал SQLite и MySQL, для нас важно чем отличаются SQL команды в этих СУБД, так как в конечном счёте работа с любой СУБД сводится к выполнению последовательности этих команд в её среде. С их помощью создаются, изменяются и удаляются все объекты, а также записываются и считываются данные. Несмотря на существующие стандарты языка SQL, каждая СУБД вносит что-то своё в его реализацию. Рассматриваемые нами СУБД не соответствуют стандартам по-разному, поэтому при подготовке команд SQLite для MySQL нужно будет:

  • Удалить строки (Некоторые парсеры удаляют ещё строки «CREATE UNIQUE INDEX» и «COMMIT;», но это не обязательно, так как обе команды есть в MySQL.):
    • PRAGMA
    • BEGIN TRANSACTION;
    • sqlite_sequence
    • DELETE FROM sqlite_sequence
    • INSERT INTO sqlite_sequence
  • Заменить:
    • AUTOINCREMENT на AUTO_INCREMENT
    • Все значения логических полей с ‘t’ на ‘1’ и с ‘f’ на ‘0’
    • Символ » на ` – т.е. двойную кавычку заменить на апостроф (кроме как в текстовых полях, где эти символы могут использоваться как часть текста).

Всё это можно было бы доверить парсеру (тем более, что их для SQLite создано уже довольно много), преобразующему синтаксис по известным нам правилам, но последнее из перечисленных условий является самым проблемным. Оно делает невозможным корректное автоматическое преобразование синтаксиса, так как в текстах сайта вполне вероятно могут встретится оба упомянутых символа в непредсказуемых комбинациях. Кроме того, задача парсера осложняется ещё и тем, что в статьях переносимого сайта могут встречаться SQL выражения, которые, конечно, же надо обрабатывать, но объяснить это парсеру очень трудно. Такая ситуация вынуждает нас использовать более долгий

Алгоритм перехода сайта под управлением MediaWiki c SQLite на MySQL

Перед началом переноса сайта MediaWiki желательно прочитать общие рекомендации на официальном сайте.

Так как в движке MediaWiki реализован механизм резервного копирования БД в XML-файл, не зависящей от используемой базы данных, то переход значительно упрощается. Его использование решит нашу основную сложность: XML представление позволяет обойти проблемы с кавычками в текстовых полях и с использованием в них SQL команд. Однако в этот файл выгружается только содержимое вики страниц и история их изменений, он не включает в себя данные из других таблиц. Поэтому наш алгоритм будет состоять из трёх этапов, с тремя шагами в каждом. Первым этапом будет

1. Подготовка нового сайта

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

1.1. Переключаем действующий сайт в режим только для чтения. Для этого достаточно в конфигурационном файле определить переменную $wgReadOnly , задав сообщение, которое будет появляться в случае, если кто-то захочет отредактировать статью. Например:

1.2. Дублируем файлы действующего сайта, кроме файла базы данных SQLite. Можно не копировать файлы и каталоги, а создать на них мягкие ссылки. Для автоматизации этого процесса можно взять скрипт create_wiki.sh , написанный bash для создания вики-фермы.

1.3. Запускаем инсталлятор MediaWiki через веб-интерфейс и проводим процедуру установки до конца. Это создаст необходимую структуры базы данных в MySQL.

Теперь можно начать

2. Перенос страниц сайта

2.1. Выгружаем из действующего сайта содержимое вики-страниц в XML-файл с помощью штатного скрипта резервного копирования DumpBackup.php .

2.2. Делаем импорт данных на новый сайт с помощью importDump.php (работает не быстро). Если вики очень большая, то можно использовать MWDumper – специально написанный инструмент на Java. Данные для подключения к базе данных как здесь так и в предыдущем шаге скрипты берут из конфигурационного файла LocalSettings.php , поэтому они должны запускаться из соответствующих каталогов старого и нового сайтов.

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

Теперь самое трудное позади, на очереди

3. Перенос оставшихся таблиц

Данные из остальных таблиц переносить уже гораздо проще, т. к. в них нет проблемного текста (с одной оговоркой, о которой скажем попозже). Будем брать SQL команды SQLite, конвертировать их в команды MySQL и запускать на последнем. Рассмотрим этот процесс на примере переноса таблицы user (пользователи вики). Вообще переноса одной только этой таблицы уже будет достаточно, чтобы проверить работоспособность нового сайта. Далее можно будет перенести таблицы user_properties (пользовательские настройки), category (категории статей), interwiki (короткие ссылки на другие вики сайты) и т.д. на усмотрение администратора. Все таблицы переносить смысла нет. С помощью phpMyAdmin можно легко просмотреть какие таблицы на действующем сайте у нас заняты и чем. Итак,

3.1. Получаем таблицы SQLite в виде SQL команд. Для этого можно использовать, например, утилиту с графическим интерфейсом sqliteman, взяв таблицы по отдельности – так будет проще всего. Также можно выполнить команду dump в командном интерпретаторе sqlite для получения всей базы данных:

А из полученного дампа выбрать команды для интересующих нас таблиц и перенести их в один файл (скажем sqlite_tables.sql) для последующего преобразования.

3.2. Преобразуем SQL команды SQLite в MySQL с помощью парсера, например, parse_sqlite_sql.py :

Этот питоновский парсер на выходе создаёт два файла, группируя отдельно sql команды для данных и для структуры БД. Нас интересуют только данные (о совпадении структур мы уже позаботились). После преобразования команды имеют вид:

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

3.3. Выполняем команды в MySQL. Можно небольшими порциями, чтобы отслеживать результат или всё сразу, в веб интерфейсе phpMyAdmin или в консоли:

где UserName и DBName лучше указать те же, что и в LocalSettings.php нового сайта.

Остальные таблицы переносятся по этому же алгоритму. Если при переносе некоторых таблиц возникли проблемы, то обратите внимание какие именно это таблицы: возможно данные в них и не нужны. Некоторые таблицы можно восстановить с помощью скриптов сопровождения, находящихся в каталоге maintenance . Например, recentchanges восстанавливается скриптом rebuildrecentchanges.php .

После окончания переноса не забываем проверить базовый функционал: регистрация пользователей, ограничение прав на редактирование (если было), загрузка файлов и общий вид страниц. Также можно просмотреть спец страницы: Версия MediaWiki (Служебная:Version), Статистика (Служебная:Statistics), Список участников (Служебная:ListUsers) и др. Если в вики использовались прямые URL ссылки на локальные ресурсы, а новый сайт располагается в другой папке, то это нужно учесть и либо исправить URL, либо перенести все такие файлы в прежнее место.

Если мы использовали стороннее программное обеспечение, кроме самого движка MediaWiki, например, поисковый движок Sphinx, то его тоже нужно не забыть перенастроить или даже переустановить, как в случае со Sphinx.

В заключении можно добавить еще несколько слов для тех, кто использует

Сайты не MediaWiki

Если перед вами стоит схожая задача, но для другого движка, то суть решения и сложности с которыми столкнетесь остаются теми же. Поэтому предложенные здесь решения во многом вам помогут. Пожалуй, единственное отличие и основная трудность будет оставаться в переносе текста статей. Однако, скорее всего, у вашего движка также есть способы осуществлять перенос содержимого через промежуточные структуры путём создания и загрузки дампа базы данных. Первым делом нужно проверить такую возможность. Если этого нет, то можно написать небольшую программу с подключением к обоим СУБД. Так как SQLite и MySQL очень распространены, то думаю не будет ошибкой сказать, что во всех широко используемых языках программирования существуют библиотеки для работы с ними. По крайней мере для PHP, С, С#, Perl, Python, Java это так.

В случае с MediaWiki, как мы увидели, переход с СУБД SQLite на MySQL не такой уж сложный и долгий.

Илон Маск рекомендует:  Что такое код cos cosh
Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL