Главная | Обратная связь | Поможем написать вашу работу!

Урок 11: Продолжение. Урок 12: Пара полезных фишек. Урок 13: Внешний ключ и виды связей. Многотабличное хранение данных

Урок 11: Продолжение

Еще более удобный вывод с помощью дополнительных плюшек для ключевого слова SELECT.

SELECT поля + агрегатная функция FROM таблица GROUP BY поле;
SELECT DISTINCT…
SELECT … LIMIT число / LIMIT число, число;

Давайте рассмотрим, что такое GROUP BY?

Я хочу узнать, сколько в нашем магазине каждый покупатель в сумме потратил денег за сегодня?

Что я должен сделать? Тут не обойтись без ключевых слов GROUP BY. Это означает, то, что он возьмет и сожмет все ваши дубликаты в одну запись, но при этом он требует указания агрегатной функции в SELECT.

Что такое агрегатная функция? Это такие функция, которые позволяют из большого количества вывести что-то одно.

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

mysql> SELECT fnames_list, SUM(price) FROM sales GROUP BY fnames_list;

fnames_list	SUM(price)
Аня
Николай
Петр

Давайте попробуем замутить что-то с ключевым словом MAX

mysql> SELECT fnames_list, MAX(price) FROM sales GROUP BY fnames_list;

fnames_list	MAX(price)
Аня
Николай
Петр

Перейдем к ключевому слову DISTINCT – оно создает вывод не содержащее дубликатов

mysql> SELECT DISTINCT fnames_list FROM sales;

fnames_list

Аня

Николай

Петр

И последнее, что мы пройдем в этом уроке – это LIMIT

mysql> SELECT fnames_list, s_product, price FROM sales ORDER BY price DESC LIMIT 1;

Вывести один результат

fnames_list	s_product	price
Аня	Торт

mysql> SELECT fnames_list, s_product, price FROM sales ORDER BY price DESC LIMIT 2;

fnames_list	s_product	price
Аня	Торт
Аня	Арбуз

А теперь посмотрим на следующую конструкцию:

mysql> SELECT fnames_list, s_product, price FROM sales ORDER BY price DESC LIMIT 1, 3;

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

Когда мы используем два числа, он сначала берет индекс записи с которой начинать, т. к. с 1, а потом указываем количество записей, которые нужно вывести. Как и в ЯП, где 1 – это вторая строка, а 0 – первая.

fnames_list	s_product	price
Аня	Арбуз
Николай	Кола
Аня	Колбаса

Урок 12: Пара полезных фишек

SELECT поле AS всевдоним…; -кастомное имя столбца в выводе SELECT
CREATE… имя поля… DEFAULT значение…; - заполнение поля значениями по умолчанию.
INSERT INTO таблица VALUES(, …); - указание ключевого поля можно пропустить

mysql> CREATE DATABASE users;	Для начала нужно создать БД.
mysql> USE users;
mysql> CREATE TABLE users ( user_id INT AUTO_INCREMENT PRIMARY KEY, fname VARCHAR(25) NOT NULL, lname VARCHAR(25), email VARCHAR(25) );	NB: CHAR используется для какого-то жесткого количества символов VARCHAR – подразумевает изменяемый тип

Отлично! Таблица создана. Мы развивает наш сайт, он у нас уже большой, и тут мы решаем добавить биографию нашим пользователям.

mysql> ALTER TABLE users ADD COLUMN income INT DEFAULT 0;

DEFAULT 0 означает значение по умолчанию. Т. е. если мы не указываем значение в income, то оно будет равно 0.

Давайте заполним нашу таблицу информацией.

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

~~mysql> INSERT INTO users VALUES(, 'Пет~~'р~~', 'Радько', 'example@mail. ru');~~

Это ошибка, потому что апостроф.

Выйти из этой проблемы просто методом экранизации кавычек.

mysql> INSERT INTO users(NULL, 'Пе''тр', 'Радько', 'test@mail. ru', 999999); mysql> INSERT INTO users(NULL, 'Пет\'р', 'Радько', 'test@mail. ru', 999999);

Теперь рассмотрим фишку с SELECT. Мы можем добавлять псевдонимы к нашим полям. Предположим, я хочу получить поле fname, но при этом не как fname, а Имена.

mysql> SELECT fname AS Имена FROM users;

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

Имена

Петр

Пе'тр

Пет'р

Можно еще более упростить данную функцию написав так:

mysql> SELECT fname Имена FROM users;

Урок 13: Внешний ключ и виды связей. Многотабличное хранение данных

6: 09 минут вспоминали, что такое внешний ключ

mysql> CREATE DATABASE contacts_db;	Создаем БД
mysql> USE contacts_db;
mysql> CREATE TABLE contacts ( user_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(25) NOT NULL );
mysql> DESC contacts;

Field	Type	NULL	KEY	Default	Extra
user_id	INT(11)	NO	PRI	NULL	auto_increment
name	VARCHAR(25)	NO		NULL

mysql> INSERT INTO contacts VALUES (NULL, 'Петр'); mysql> INSERT INTO contacts VALUES (NULL, 'Женя'); mysql> INSERT INTO contacts VALUES (NULL, 'Коля'); mysql> INSERT INTO contacts VALUES (NULL, 'Игорь');

mysql> SELECT name Имя FROM contacts;

Выведем все имена

Имя

Петр

Женя

Коля

Игорь

mysql> CREATE TABLE activities ( act_id INT AUTO_INCREMENT PRIMARY KEY, activity VARCHAR(20) NOT NULL, user_id INT FOREIGN KEY(user_id) REFERENCES contacts(user_id) );

А теперь самое главное! Создаем таблицу с внешним ключом!

mysql> DESC activities;

MUL – т. е. могут быть повторы

Field	Type	NULL	KEY	Default	Extra
act_id	INT(11)	NO	PRI	NULL	auto_increment
activity	VARCHAR(20)	NO		NULL
user_id	INT(11)	YES	MUL	NULL

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

Есть несколько типов связей, которые вы должны знать.

mysql> INSERT INTO activities VALUES(NULL, 'Собаки', (SELECT user_id FROM contacts WHERE name = 'Петр') );

mysql> INSERT INTO activities VALUES(NULL, 'SQL', (SELECT user_id FROM contacts WHERE name = 'Петр') );

mysql> SELECT * FROM activities;

act_id	activity	user_id
	Собаки
	SQL

Переходим к связям…

Тип связи

Пояснение

Один к одному

customer – address

customer

Персоны
person_id	name
	Петр
	Николай
	Роман

Адрес проживания
per_adr_id	location
	Москва
	Лондон
	Новгород

Один ко многим

customer – activity

customer – activity, activity

customer

Пользователи
user_id	name	job_id
	Петр
	Женя
	Коля

Профессии
job_id	name
	Маляр
	Кодер
	Уборщик

Многие ко многим

Ученики
student_id	name
	Петр
	Женя
	Коля

Ученики_Классы
student_id	class_id

Классы
class_id	name
	1Б
	2В
	1А

Есть 3 типа связи между таблицами. Они не указываются вами явно. Они происходят (сами создаются) в зависимости от того, какие поля вы связываете, и таких типа всего лишь 3.

Один к одному:

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

# У нас есть какой-то человек. У нас есть первичный ключ и его имя и в другой таблице есть локация, где он проживает. Но поскольку человек не может проживать в двух местах одновременно, возможно только тип связи 1 к 1, т. е. у нас есть Петр с первичным ключом 1 и он проживает только в Москве и соответственно первичный ключ здесь у него будет 1.

Пример:

mysql> DROP TABLES activities, contacts;

Сначала удалим таблицу activities и contacts

mysql> CREATE TABLE persons ( person_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL );

Переходим к созданию 1 к 1. Для этого мы реализуем этот пример: Персона и адрес проживания. Отлично! Создаем таблицу «Персона».

mysql> INSERT INTO persons VALUES(NULL, 'Петр'); mysql> INSERT INTO persons VALUES(NULL, 'Николай'); mysql> INSERT INTO persons VALUES(NULL, 'Роман');

Заполним таблицу значениями

mysql> SELECT * FROM persons;

person_id	name
	Петр
	Николай
	Роман

mysql> CREATE TABLE locations ( person_location_id INT NOT NULL PRIMARY KEY, name VARCHAR(20), FOREIGN KEY(person_location_id) REFERENCES persons(person_id) );

Создаем таблицу локаций, где они проживают Мы создали таблицу persons, у которой первичный ключ person_id. Потом мы создали таблицу locations, в которой поле первичного ключа одновременно является внешним ключом ссылающимся на наш person_id

mysql> INSERT INTO locations VALUES(1, 'Москва'); mysql> INSERT INTO locations VALUES(2, 'Лондон'); mysql> INSERT INTO locations VALUES(3, 'Новгород');

Заполним таблицу значениями

Выводим таблицу persons и locations

person_id	name
	Петр
	Николай
	Роман

person_location_id	name
	Москва
	Лондон
	Новгород

Попробуем нарушить связь:

mysql> INSERT INTO locations VALUES(1, 'Киев');

И ОПА! Ошибка! Duplicate entry ‘1’ for key ‘PRIMARY’

⇐ Предыдущая 2 3 4 5 678 9 10 11 Следующая ⇒

Воспользуйтесь поиском по сайту: