LXF157:Для продвинутых. SQL
Olkol (обсуждение | вклад) (Новая страница: «Категория: Учебники Категория: Базы данных =SQL: Дизайн базы данных= '''Hardcore Linux Про…») |
Текущая версия на 13:49, 19 августа 2018
|
|
|
Содержание |
[править] SQL: Дизайн базы данных
Hardcore Linux Проверьте себя на крутом проекте для продвинутых пользователей
Для большинства людей база данных подразумевает реляционную базу вроде MySQL или SQLite. А Дейв Кросс создает базу данных про телешоу.
- Метамодернизм в позднем творчестве В.Г. Сорокина
- ЛитРПГ - последняя отрыжка постмодерна
- "Ричард III и семиотика"
- 3D-визуализация обложки Ridero создаем обложку книги при работе над самиздатом.
- Архитектура метамодерна - говоря о современном искусстве, невозможно не поговорить об архитектуре. В данной статье будет отмечено несколько интересных принципов, характерных для построек "новой волны", столь притягательных и скандальных.
- Литература
- Метамодерн
- Рокер-Прометей против изначального зла в «Песне про советскую милицию» Вени Дркина, Автор: Нина Ищенко, к.ф.н, член Союза Писателей ЛНР - перепубликация из журнала "Топос".
- Как избавиться от комаров? Лучшие типы ловушек.
- Что делать если роблокс вылетает на windows
- Что делать, если ребенок смотрит порно?
- Почему собака прыгает на людей при встрече?
- Какое масло лить в Задний дифференциал (мост) Visco diff 38434AA050
- О чем может рассказать хвост вашей кошки?
- Верветки
- Отчетность бюджетных учреждений при закупках по Закону № 223-ФЗ
- Срок исковой давности как правильно рассчитать
- Дмитрий Патрушев минсельхоз будет ли преемником Путина
- Кто такой Владислав Поздняков? Что такое "Мужское Государство" и почему его признали экстремистским в России?
- Как правильно выбрать машинное масло в Димитровграде?
- Как стать богатым и знаменитым в России?
- Почему фильм "Пипец" (Kick-Ass) стал популярен по всему миру?
- Как стать мудрецом?
- Как правильно установить FreeBSD
- Как стать таким как Путин?
- Где лучше жить - в Димитровграде или в Ульяновске?
- Почему город Димитровград так называется?
- Что такое метамодерн?
- ВАЖНО! Временное ограничение движения автотранспортных средств в Димитровграде
- Тарифы на электроэнергию для майнеров предложено повысить
B реляционных базах данных с помощью SQL производится и объявление таблиц, и работа с данными в этих таблицах. Поэтому внутри SQL есть два различных языка – DDL (Data Definition Language – язык определения данных) и DML (Data Manipulation Language – язык управления данными). Сначала познакомимся с DDL.
При создании базы данных полезно начать с перечисления объектов реального мира, сведения о которых нужно хранить в базе. Каждый из таких объектов скорее всего будет представлен таблицей в нашей базе данных. Как обещано, мы будем хранить данные о телешоу, поэтому первой таблицей будет tv_show. И под телешоу я имею в виду нечто вроде Доктора Кто или Шерлока, а не отдельную серию. Поэтому наша следующая таблица – episode [серия]. Но в большинстве телешоу серии еще и объединяются в сезоны, вот вам и следующая таблица – series [сезоны].
[править] Обрисуем сцену
На этом этапе полезно нарисовать схему нашей базы данных. Мы нарисуем так называемую «схему сущность–связь». На таких схемах таблицы (или сущности) изображаются в виде прямоугольников, а связи между ними – в виде линий:
Обратите внимание, что каждая линия на дальнем конце разветвляется. Это означает, что с этой стороны отношения может быть несколько экземпляров сущности. Так, наша схема говорит, что у телешоу есть несколько сезонов, а в каждом сезоне – несколько серий. Эти отношения «один ко многим» – ключевая идея в проектировании баз данных. Вскоре мы рассмотрим, как проектировать таблицы с такими отношениями. Начнем с команды DDL для создания нашей базы данных. В одной установке MySQL может быть несколько отдельных баз данных, и для каждого нового проекта стоит создавать новую базу данных и нового пользователя, который будет владельцем таблиц в этой базе данных. Для начала нужно подключиться к серверу MySQL от имени суперпользователя-root, но после создания базы данных и нового пользователя административный доступ больше не понадобится.
Вот начальная команда для подключения к базе данных:
mysql -uroot -p
В большинстве установок MySQL начальный пароль пользователя root по умолчанию пуст. Если вам он понадобится, можете его изменить.
Команда для создания новой базы данных довольно проста:
create database television;
Точка с запятой в конце говорит MySQL, что команда закончилась. Если нажать Enter в конце строки, не завершающейся точкой с запятой, MySQL будет ждать ввода дальнейших команд и ничего не выполнит. Таким способом легко набирать сложные команды, состоящие из нескольких строк. Команда для создания нового пользователя менее очевидна:
grant all on television.*
to ‘television’@’localhost’
identified by ‘PASSWORD’;
На самом деле, создание нового пользователя – лишь побочный эффект этой команды. Ее основная цель – дать пользователю права доступа к объектам базы данных. Здесь мы говорим, что хотим дать все права доступа ко всем объектам в базе данных television (television.*) пользователю television, и он будет подключаться с локального хоста с паролем “PASSWORD”. При создании вашего пользователя советую взять другой пароль! Теперь есть надежда подключиться к нашей базе данных MySQL командой
mysql -utelevision -Dtelevision -p
Параметр -u — имя пользователя, -D – имя базы данных, а -p велит MySQL запросить пароль. Пароль можно указать прямо в командной строке, но если вы не уверены, что никто не стоит у вас за спиной, лучше ввести его в ответ на запрос.
[править] Создаем таблицы
Пора создавать таблицы. Но перед этим нужно решить, какие данные для различных сущностей будут храниться. Элемент данных в базе – это столбец. В каждой из наших таблиц их будет совсем немного: например, в таблице tv_show их будет всего два. Для каждого столбца нужно указать имя и тип данных, определяющий, какие данные будут там храниться.
create table tv_show (
id integer not null auto_increment,
title varchar(100) not null,
primary key(id)
) engine=InnoDB;
Хотя это простая таблица, в ее определении нужно кое-что объяснить. В таблице всегда стоит заводить первичный ключ [primary key]. Это целое число, увеличивающееся на единицу с каждой записью, которое однозначно определяет строку в таблице. Я всегда называю этот столбец id. Мы определили его как not null, что означает, что в нем всегда должно быть значение; но также указав auto_increment, мы позволяем MySQL самому заполнять эти значения. Каждый раз при добавлении новой строки в таблицу MySQL будет определять следующее неиспользованное значение идентификатора и заносить его в столбец id. Затем мы определяем столбец title – это единственные данные, которые мы будем хранить в данной таблице. Мы определяем их тип как varchar(100), то есть это строка длиной до 100 символов.
В следующей строке определения таблицы не задается нового столбца: она просто сообщает MySQL, какой из наших столбцов является первичным ключом. Наконец, после списка столбцов мы говорим MySQL, какой движок базы данных использовать. Это неважно, пока мы не говорим о ссылочной целостности данных. А говорить об этом мы не можем, пока у нас нет хотя бы двух таблиц. Вот наша таблица series.
create table series (
id integer not null auto_increment,
number integer not null,
name varchar(100),
tv_show integer not null,
primary key (id),
foreign key (tv_show) references tv_show(id)
) engine=InnoDB;
Она начинается похоже на таблицу tv_show. У нас есть первичный ключ, под названием id. Затем идут два столбца данных, number и name. Мы определили number как not null, так как у любого сезона есть номер, но name может быть пустым (да и будет по умолчанию пустым, если мы ничего не укажем), так как у отдельных сезонов обычно нет собственных названий. Следующий столбец – tv_show, и это целое число, которое не может быть пустым. Этот столбец связывает сезон с соответствующим телешоу.
- Метамодернизм в позднем творчестве В.Г. Сорокина
- ЛитРПГ - последняя отрыжка постмодерна
- "Ричард III и семиотика"
- 3D-визуализация обложки Ridero создаем обложку книги при работе над самиздатом.
- Архитектура метамодерна - говоря о современном искусстве, невозможно не поговорить об архитектуре. В данной статье будет отмечено несколько интересных принципов, характерных для построек "новой волны", столь притягательных и скандальных.
- Литература
- Метамодерн
- Рокер-Прометей против изначального зла в «Песне про советскую милицию» Вени Дркина, Автор: Нина Ищенко, к.ф.н, член Союза Писателей ЛНР - перепубликация из журнала "Топос".
- Как избавиться от комаров? Лучшие типы ловушек.
- Что делать если роблокс вылетает на windows
- Что делать, если ребенок смотрит порно?
- Почему собака прыгает на людей при встрече?
- Какое масло лить в Задний дифференциал (мост) Visco diff 38434AA050
- О чем может рассказать хвост вашей кошки?
- Верветки
- Отчетность бюджетных учреждений при закупках по Закону № 223-ФЗ
- Срок исковой давности как правильно рассчитать
- Дмитрий Патрушев минсельхоз будет ли преемником Путина
- Кто такой Владислав Поздняков? Что такое "Мужское Государство" и почему его признали экстремистским в России?
- Как правильно выбрать машинное масло в Димитровграде?
- Как стать богатым и знаменитым в России?
- Почему фильм "Пипец" (Kick-Ass) стал популярен по всему миру?
- Как стать мудрецом?
- Как правильно установить FreeBSD
- Как стать таким как Путин?
- Где лучше жить - в Димитровграде или в Ульяновске?
- Почему город Димитровград так называется?
- Что такое метамодерн?
- ВАЖНО! Временное ограничение движения автотранспортных средств в Димитровграде
- Тарифы на электроэнергию для майнеров предложено повысить
- Метамодернизм в позднем творчестве В.Г. Сорокина
- ЛитРПГ - последняя отрыжка постмодерна
- "Ричард III и семиотика"
- 3D-визуализация обложки Ridero создаем обложку книги при работе над самиздатом.
- Архитектура метамодерна - говоря о современном искусстве, невозможно не поговорить об архитектуре. В данной статье будет отмечено несколько интересных принципов, характерных для построек "новой волны", столь притягательных и скандальных.
- Литература
- Метамодерн
- Рокер-Прометей против изначального зла в «Песне про советскую милицию» Вени Дркина, Автор: Нина Ищенко, к.ф.н, член Союза Писателей ЛНР - перепубликация из журнала "Топос".
- Как избавиться от комаров? Лучшие типы ловушек.
- Что делать если роблокс вылетает на windows
- Что делать, если ребенок смотрит порно?
- Почему собака прыгает на людей при встрече?
- Какое масло лить в Задний дифференциал (мост) Visco diff 38434AA050
- О чем может рассказать хвост вашей кошки?
- Верветки
- Отчетность бюджетных учреждений при закупках по Закону № 223-ФЗ
- Срок исковой давности как правильно рассчитать
- Дмитрий Патрушев минсельхоз будет ли преемником Путина
- Кто такой Владислав Поздняков? Что такое "Мужское Государство" и почему его признали экстремистским в России?
- Как правильно выбрать машинное масло в Димитровграде?
- Как стать богатым и знаменитым в России?
- Почему фильм "Пипец" (Kick-Ass) стал популярен по всему миру?
- Как стать мудрецом?
- Как правильно установить FreeBSD
- Как стать таким как Путин?
- Где лучше жить - в Димитровграде или в Ульяновске?
- Почему город Димитровград так называется?
- Что такое метамодерн?
- ВАЖНО! Временное ограничение движения автотранспортных средств в Димитровграде
- Тарифы на электроэнергию для майнеров предложено повысить
Из нашей схемы «сущность–связь» мы знаем, что телешоу состоит из нескольких сезонов. В нашей модели за это отвечает столбец tv_show таблицы series, содержащий id соответствующего телешоу. Так, если в таблице tv_show у нас есть строка для сериала «Доктор Кто» и ее идентификатор равен 10, то в каждой строке в таблице сезонов для «Доктора Кто» в столбце tv_show будет стоять 10. Столбец, ссылающийся на родительскую таблицу, называется внешним ключом [foreign key], так как он содержит значения из столбца первичного ключа, но они не являются первичными ключами этой таблицы. Для задания внешних ключей мы добавляем clauses к определению таблицы. В этой таблице мы говорим, что столбец tv_show – внешний ключ; он содержит значения столбца id таблицы tv_show. В таблице допускается всего один первичный ключ, но внешних вполне может быть несколько. Позже мы рассмотрим такой пример.
- Метамодернизм в позднем творчестве В.Г. Сорокина
- ЛитРПГ - последняя отрыжка постмодерна
- "Ричард III и семиотика"
- 3D-визуализация обложки Ridero создаем обложку книги при работе над самиздатом.
- Архитектура метамодерна - говоря о современном искусстве, невозможно не поговорить об архитектуре. В данной статье будет отмечено несколько интересных принципов, характерных для построек "новой волны", столь притягательных и скандальных.
- Литература
- Метамодерн
- Рокер-Прометей против изначального зла в «Песне про советскую милицию» Вени Дркина, Автор: Нина Ищенко, к.ф.н, член Союза Писателей ЛНР - перепубликация из журнала "Топос".
- Как избавиться от комаров? Лучшие типы ловушек.
- Что делать если роблокс вылетает на windows
- Что делать, если ребенок смотрит порно?
- Почему собака прыгает на людей при встрече?
- Какое масло лить в Задний дифференциал (мост) Visco diff 38434AA050
- О чем может рассказать хвост вашей кошки?
- Верветки
- Отчетность бюджетных учреждений при закупках по Закону № 223-ФЗ
- Срок исковой давности как правильно рассчитать
- Дмитрий Патрушев минсельхоз будет ли преемником Путина
- Кто такой Владислав Поздняков? Что такое "Мужское Государство" и почему его признали экстремистским в России?
- Как правильно выбрать машинное масло в Димитровграде?
- Как стать богатым и знаменитым в России?
- Почему фильм "Пипец" (Kick-Ass) стал популярен по всему миру?
- Как стать мудрецом?
- Как правильно установить FreeBSD
- Как стать таким как Путин?
- Где лучше жить - в Димитровграде или в Ульяновске?
- Почему город Димитровград так называется?
- Что такое метамодерн?
- ВАЖНО! Временное ограничение движения автотранспортных средств в Димитровграде
- Тарифы на электроэнергию для майнеров предложено повысить
У внешних ключей есть два важных свойства. Во-первых, поскольку сезон принадлежит телешоу, в каждой строке таблицы series обязательно должно быть значение в столбце tv_show. Для этого мы принудительно объявляем столбец как непустой. А во-вторых, каждое значение в столбце tv_show должно быть действительным, существующим в таблице tv_show. Именно это я имел в виду ранее, когда упоминал о ссылочной целостности данных. Если попробовать вставить в таблицу series строку со значением поля tv_show, которого нет в таблице tv_show, вставка должна завершиться неудачно. Точно так же, при попытке удалить строку в таблице tv_show удаление должно завершиться неудачно, иначе в таблице series останутся строки-сироты.
В ранних версиях MySQL не было поддержки внешних ключей – их нельзя было даже задать. Позже такая возможность появилась, но MySQL внешние ключи не контролировал, то есть можно было иметь внешние ключи, указывающие на несуществующие записи. С версии 4 в MySQL используются жесткие внешние ключи, но только в движке InnoDB. В движке по умолчанию MyISAM жесткие внешние ключи все-таки не используются, и поэтому мы указываем движок InnoDB в определениях таблиц.
Вот определение нашей третьей таблицы, episode.
create table episode (
id integer not null auto_increment,
number integer not null,
title varchar(100) not null,
first_broadcast datetime,
series integer not null,
primary key (id),
foreign key (series) references series(id)
) engine=InnoDB;
Здесь нам незнаком только новый тип данных. Мы определили столбец first_broadcast как datetime. В MySQL предусмотрено множество предопределенных типов данных, и всегда имеет смысл воспользоваться тем, который больше всего подходит для хранимых данных.
Это первые три таблицы; теперь добавим в них немного данных.
insert into tv_show (title) values (‘Доктор Кто’);
insert into tv_show (title) values (‘Шерлок’);
insert into series (number, tv_show) values (1, 1);
insert into series (number, tv_show) values (1, 2);
insert into series (number, tv_show) values (2, 1);
insert into series (number, tv_show) values (2, 2);
insert into episode (number, title, first_broadcast, series)
values (1, ‘Роза’, ‘2005-03-26’, 1);
insert into episode (number, title, first_broadcast, series)
values (2, ‘Конец света’, ‘2005-04-02’, 1);
insert into episode (number, title, first_broadcast, series)
values (1, ‘Этюд в розовых тонах’, ‘2010-07-25’, 2);
insert into episode (number, title, first_broadcast, series)
values (2, ‘Слепой банкир’, ‘2010-08-01’, 2);
insert into episode (number, title, first_broadcast, series)
values (1, ‘Новая земля’, ‘2006-04-15’, 3);
insert into episode (number, title, first_broadcast, series)
values (2, ‘Зуб и коготь’, ‘2006-04-22’, 3);
insert into episode (number, title, first_broadcast, series)
values (1, ‘Скандал в Бельгравии’, ‘2012-01-01’, 4);
insert into episode (number, title, first_broadcast, series)
values (2, ‘Собака Баскервилей’, ‘2012-01-08’, 4);
Теперь информацию из нашей базы данных можно добыть простым SQL-запросом
select title from tv_show;
Еще интереснее станет, если объединить таблицы вместе.
select tv_show.title, series.number as series,
episode.number as episode, episode.title,
date_format(episode.first_broadcast, ‘%a %e %b %Y’)
from tv_show
join series on series.tv_show = tv_show.id
join episode on episode.series = series.id;
С помощью ключевого слова join в SQL можно выбрать данные из нескольких таблиц одним запросом. Для этого в запросе надо указать объединяемые таблицы и отношения первичного и вторичного ключей. В первом примере в запросе выше мы связываем таблицу tv_show с таблицей series, и внешний ключ в таблице series (series.tv_show) соответствует первичному ключу в таблице tv_series (tv_series.id).
Этот запрос также демонстрирует возможности типа данных datetime. В MySQL есть несколько функций для обработки даты и времени. В этом примере вы воспользовались простейшей из них. Функция date_format берет значение даты и времени и переформатирует его. Первый аргумент – значение даты и времени, а второй – строка формата, в котором мы хотим их отобразить. Сочетания %X в строке формата представляют различные части даты и времени, которые нужно отобразить. Список этих сочетаний приведен в документации по MySQL (о том, где ее найти, см. информацию во врезке).
Сейчас мы также можем продемонстрировать мощь ссылочной целостности. Попытавшись удалить строку из таблицы tv_show, вы получите сообщение об ошибке, и строка не будет удалена. Причина в том, что для каждой строки в таблице tv_show есть строки в таблице series, которые на нее ссылаются. Вы не можете удалить строку в tv_show, пока не удалите все соответствующие ей сезоны. Аналогично, нельзя удалить строку сезона, пока есть соответствующие ей серии.
[править] Добавляем персонажей
Все это мило, но отношения между нашими таблицами до сих пор были очень простыми: это были отношения «один ко многим». Увы, в мире не всегда все так гладко. Давайте добавим в нашу базу данных информацию о персонажах и их появлении. Какие таблицы нужно создать? И какие внешние ключи выбрать?
Очевидно, нужна таблица с персонажами [character]. Но character [тж. символ] – также зарезервированное ключевое слово в SQL, поэтому давайте назовем нашу таблицу person [личность]. Эта таблица выглядит так:
create table person (
id integer not null auto_increment,
name varchar(100) not null,
primary key (id)
) engine=InnoDB;
- Метамодернизм в позднем творчестве В.Г. Сорокина
- ЛитРПГ - последняя отрыжка постмодерна
- "Ричард III и семиотика"
- 3D-визуализация обложки Ridero создаем обложку книги при работе над самиздатом.
- Архитектура метамодерна - говоря о современном искусстве, невозможно не поговорить об архитектуре. В данной статье будет отмечено несколько интересных принципов, характерных для построек "новой волны", столь притягательных и скандальных.
- Литература
- Метамодерн
- Рокер-Прометей против изначального зла в «Песне про советскую милицию» Вени Дркина, Автор: Нина Ищенко, к.ф.н, член Союза Писателей ЛНР - перепубликация из журнала "Топос".
- Как избавиться от комаров? Лучшие типы ловушек.
- Что делать если роблокс вылетает на windows
- Что делать, если ребенок смотрит порно?
- Почему собака прыгает на людей при встрече?
- Какое масло лить в Задний дифференциал (мост) Visco diff 38434AA050
- О чем может рассказать хвост вашей кошки?
- Верветки
- Отчетность бюджетных учреждений при закупках по Закону № 223-ФЗ
- Срок исковой давности как правильно рассчитать
- Дмитрий Патрушев минсельхоз будет ли преемником Путина
- Кто такой Владислав Поздняков? Что такое "Мужское Государство" и почему его признали экстремистским в России?
- Как правильно выбрать машинное масло в Димитровграде?
- Как стать богатым и знаменитым в России?
- Почему фильм "Пипец" (Kick-Ass) стал популярен по всему миру?
- Как стать мудрецом?
- Как правильно установить FreeBSD
- Как стать таким как Путин?
- Где лучше жить - в Димитровграде или в Ульяновске?
- Почему город Димитровград так называется?
- Что такое метамодерн?
- ВАЖНО! Временное ограничение движения автотранспортных средств в Димитровграде
- Тарифы на электроэнергию для майнеров предложено повысить
В ней задается только имя персонажа. Мы не соединили таблицу ни с одной из других, потому что не можем. Должна быть связь между записью о персонаже и записью о серии, означающая, что персонаж появляется в серии. Но внешний ключ серии в таблицу персонажей поместить нельзя, так как персонаж может появляться в нескольких сериях.
И нельзя поместить внешний ключ персонажа в таблицу с сериями, потому что в одной серии появляется несколько персонажей. Это отношение «многие ко многим». Смоделировать его сложнее, чем отношения «один ко многим», которые встречались нам до настоящего момента.
Но сделать это можно. И ключ к решению задачи приведен несколько абзацев назад. Я сказал, что нам нужна информация о персонажах и об их появлении. Решение – создать отдельную таблицу appearance [появления персонажей]. Она выглядит так:
create table appearance (
id integer not null auto_increment,
person integer not null,
episode integer not null,
primary key (id),
foreign key (person) references person(id),
foreign key (episode) references episode(id)
) engine=InnoDB;
[править] Ключевая фигура
Вся эта таблица состоит из ключей. Это первичный ключ и два внешних ключа – к таблице с персонажами и к таблице с сериями. Каждый раз, когда мы знаем, что некий персонаж появился в какой-то серии, мы можем добавить строку в эту таблицу.
И если у нас есть episode id [идентификатор серии], мы сможем получить список всех персонажей, появляющейся в этой серии; и наоборот – по person id [идентификатору персонажа] можно получить список всех серий, где он появляется. Вот немного данных для наших новых таблиц:
insert into person (name) values (‘Девятый Доктор’);
insert into person (name) values (‘Десятый Доктор’);
insert into person (name) values (‘Роз Тайлер’);
insert into appearance (person, episode) values (1, 1);
insert into appearance (person, episode) values (3, 1);
insert into appearance (person, episode) values (1, 2);
insert into appearance (person, episode) values (3, 2);
insert into appearance (person, episode) values (2, 5);
insert into appearance (person, episode) values (3, 5);
insert into appearance (person, episode) values (2, 6);
insert into appearance (person, episode) values (3, 6);
И вот вам несколько запросов, демонстрирующих, как таблица appearance работает в обоих направлениях.
select episode.title
from episode
join appearance on episode.id = appearance.episode
join person on person.id = appearance.person
where person.name = ‘Девятый Доктор’;
select person.name
from episode
join appearance on episode.id = appearance.episode
join person on person.id = appearance.person
where episode.title = ‘Новая земля’;
Обновленнная схема базы данных приведена слева. На ней ясно видно, что таблица appearance является связующим звеном между таблицами person и episode.
Это все, о чем мы можем узнать в нашем простом руководстве по проектированию баз данных. Я написал простое PHP-приложение, демонстрирующее работу с этой базой данных. Его код можно найти на DVD, а несколько снимков экрана показаны по соседству. |