Урок 13 (продолжение)
Один ко многим:
Этот тип немного посложнее, но ничего сложного в нем нет. Давайте удалим таблицы locations и persons. Теперь у нас снова пустая база данных. Что же означает «один ко многим»? У нас есть БД пользователей и у них есть какие-то профессии. У пользователей может быть только одна профессия.
Для каждой записи может быть только одна профессия, если мы смотрим на таблицу пользователи.
| А если мы взглянем на таблицу «Профессии», то у нас маляр используется в пользователях во 2 и 3 строчке. Т. е. профессия «маляр» может использоваться во многих записях таблицы Пользователи, но в таблице «пользователи» у каждой записи может быть только одно значение профессии. Т. е. только 1 профессия может быть у пользователей, но при этом сами профессии могут использоваться много раз для разных пользователей.
|
Пользователи
| user_id
| name
| job_id
|
| Петр
|
|
| Женя
|
|
| Коля
|
|
| Профессии
| job_id
| name
|
| Маляр
|
| Кодер
|
| Уборщик
|
|
Попробуем зафигачить пример:
mysql> CREATE TABLE users
(
user_id INT PRIMARY KEY AUTO_INCREMENT,
user_name VARCHAR(20) NOT NULL,
profession_id INT
);
| Создаем таблицу users
|
mysql> INSERT INTO users VALUES(NULL, 'Петр', NULL);
mysql> INSERT INTO users VALUES(NULL, 'Женя', NULL);
mysql> INSERT INTO users VALUES(NULL, 'Коля', NULL);
| Добавим тройку записей
|
mysql> SELECT * FROM users;
| Теперь посмотрим на нашу табличку
users
| user_id
| user_name
| profession_id
|
| Петр
| NULL
|
| Женя
| NULL
|
| Коля
| NULL
|
|
mysql> CREATE TABLE professions
(
profession_id INT PRIMARY KEY AUTO_INCREMENT,
profession_name VARCHAR(20) NOT NULL
);
| Чтобы добавить внешний ключ к таблице, нужно создать таблицу «профессий»
|
mysql> INSERT INTO professions VALUES(NULL, 'Маляр');
mysql> INSERT INTO professions VALUES(NULL, 'Кодер');
mysql> INSERT INTO professions VALUES(NULL, 'Уборщик');
| professions
| profession_id
| profession_name
|
| Маляр
|
| Кодер
|
| Уборщик
|
|
mysql> ALTER TABLE users
ADD FOREIGN KEY(profession_id) REFERENCES professions(profession_id);
| Теперь с помощью команды ALTER мы добавляем внешний ключ для нашей таблицы users.
|
mysql> UPDATE users SET profession_id=1
WHERE user_name IN('Женя', 'Коля');
| Теперь давайте изменим users
Мы установили значение, используя ключевое слово IN, т. е. там где имя равно Женя и Коля мы установили 1.
|
mysql> SELECT * FROM users;
| Смотрим:
users
| user_id
| user_name
| profession_id
|
| Петр
| NULL
|
| Женя
| 1
|
| Коля
| 1
|
|
Многие ко многим:
Что означает данный тип связи? Дело в том, что иногда требуется, чтобы у нас и значения полей одной таблицы были связаны с полями другой таблицы и наоборот. Напрямую, нельзя реализовать эту связь. У нас есть таблица «Ученики» и таблица «Классы».
Ученики
| student_id
| name
|
| Петр
|
| Женя
|
| Коля
|
| Ученики_Классы
| student_id
| class_id
|
|
|
|
|
|
|
| Классы
| class_id
| name
|
| 1Б
|
| 2В
|
| 1А
|
|
Зафигачим пример:
mysql> DROP TABLES users, professions;
| Удалим прошлые таблицы
|
mysql> CREATE TABLE students
(
student_id INT PRIMARY KEY AUTO_INCREMENT,
student_name VARCHAR(20) NOT NULL
);
| Создадим табличку «Ученики»
|
mysql> INSERT INTO students VALUES(NULL, 'Петр');
mysql> INSERT INTO students VALUES(NULL, 'Женя');
mysql> INSERT INTO students VALUES(NULL, 'Коля');
| Теперь заполняем эту таблицу
|
mysql> CREATE TABLE classes
(
class_id INT PRIMARY KEY AUTO_INCREMENT,
class_name VARCHAR(20) NOT NULL
);
| Создадим табличку «Классы»
|
mysql> INSERT INTO classes VALUES(NULL, '1Б');
mysql> INSERT INTO classes VALUES(NULL, '2В');
mysql> INSERT INTO classes VALUES(NULL, '1А');
| Теперь заполняем эту таблицу
|
mysql> CREATE TABLE student_classes
(
student_id INT NOT NULL,
class_id INT NOT NULL,
PRIMARY KEY(student_id, class_id),
FOREIGN KEY(student_id) REFERENCES students(student_id),
FOREIGN KEY(class_id) REFERENCES classes(class_id)
);
| Теперь финальный шаг
Дело в том, когда мы реализуем связь «многие ко многим» в соединительной таблице первичным ключом должны быть два поля. Таким образом, мы гарантируем, что у нас никогда не будет дубликатов в этой таблице.
|
mysql> INSERT INTO student_classes VALUES(1, 1);
mysql> INSERT INTO student_classes VALUES(1, 2);
mysql> INSERT INTO student_classes VALUES(1, 3);
mysql> INSERT INTO student_classes VALUES(2, 3);
mysql> INSERT INTO student_classes VALUES(2, 1);
| И теперь мы можем написать следующее
Первая запись означает, что у нас Петр учится в классе 1Б. Если мы попытаемся добавить это снова, то нас пошлют куда подальше, ибо это дубликат уникального поля.
|

CREATE TABLE persons(
person_id int(11) NOT NULL AUTO_INCREMENT,
firstname varchar(30) not null,
lastname varchar(30) not null,
primary key(person_id)
);
create table properties(
property_id int(11) not null auto_increment,
property varchar(254) not null unique,
primary key(property_id)
);
create table has_property(
person_id int(11) not null,
property_id int(11) not null,
primary key(person_id, property_id),
foreign key(person_id) REFERENCES persons(person_id),
foreign key(property_id) REFERENCES properties(property_id)
);
INSERT INTO persons(firstname, lastname) VALUES ('John', 'Doe');
SET @person_id = last_insert_id();
INSERT IGNORE INTO properties(property) VALUES ('property_A');
SET @property_id = last_insert_id();
INSERT INTO has_property(person_id, property_id) VALUES(@person_id, @property_id);
INSERT IGNORE INTO properties(property) VALUES ('property_B');
SET @property_id = last_insert_id();
INSERT INTO has_property(person_id, property_id) VALUES(@person_id, @property_id);
INSERT IGNORE INTO properties(property) VALUES ('property_C');
SET @property_id = last_insert_id();
INSERT INTO has_property(person_id, property_id) VALUES(@person_id, @property_id);
Воспользуйтесь поиском по сайту: