Будьте внимательны при использовании названий месяцев, так как они различают регистр символов. Поэтому January будет работать, а JANUARY не будет
Лабораторная работа 3
ВЫБОР И МОДИФИКАЦИЯ ДАННЫХ ИЗ НЕСКОЛЬКИХ ТАБЛИЦ
Цель работы
Создать базу данных и несколько таблиц. Подготовить и реализовать серию запросов, связанных с выборкой информации из созданных таблиц.
Содержание работы и методические указания к ее выполнению
1. Создание базы данных
Создать базу данных «Книги» (B_ХХ_13_YY) , (где XX-номер группы, YY-номер в журнале)
2. Создание таблиц в базе данных и заполнение их записями.
Создать в БД B_ХХ_13_YY таблицу авторов Avt_ХХ_13_YY:
| Аvt_id | А_firstname (varchar50) | А_lastname (varchar40) | BirthD (date) | Sex (enum) | Town (int) |
| Иванов | Иван | 15.09.1989 | м |
3. Проверить, что таблица была создана (через команду SHOW TABLES);
4. Заполнить ее записями - придумать штук 10 авторов разного пола, возраста от 30 до 80 лет и с разным количеством детей.
Все записи с типом "строка" должны содержать 3 первые буквы из вашей ФИО и Ваш номер.
Вывести содержимое таблицы.
5. Создать таблицу книги Book_ХХ_11_YY,
Задать значение по умолчанию для поля Student- ХХ13YY(XX-номер группы, YY-номер в журнале)
Добавить для столбца Avt_id ограничение Внешний ключ:
| Book_id | Book_name | Avt_id | Student |
| Int unsigned not null primary key auto_increment | varchar(10) | Int, unsigned | int |
Int, unsigned, FOREIGN KEY (Avt_id) REFERENCES Avt (Avt_id)
Заполнить ее любым способом. {Авторы берутся из предыдущей таблицы). Могут быть авторы, у которых нет книг.
6. Посмотреть структуру таблиц ( с помощью команды DESCRIBE).
Вывести содержимое таблицы.
7.Создать таблицу Town
| Town (int) | Town_name varchar(10) | Country varchar(10) |
Заполнить ее. Посмотреть структуру и вывести содержимое таблицы.
Выполнение запросов.
8. Вывести авторов, родившихся в конкретном месяце (например, в феврале);
9. Вывести фамилии авторов, у которых есть книги;
10. Вывести количество авторов каждого пола (2 варианта )
| F | |
| m |
| f | m |
11. Вывести всех авторов, фамилия которых содержит букву «В» (например).
12. Вывести авторов, которым меньше 50 лет.
13. Вывести 5 самых старых авторов.
14. Вывести общее количество книг у авторов каждого пола.
Показать результаты работы преподавателю.
Методические указания.
Тип столбца Null
Тип столбца NULL является специальным значением. Чтобы вставить значение NULL, удалите просто имя столбца из оператора INSERT. Столбцы содержат NULL по умолчанию, если только не определены как NOT NULL. Значение null может использоваться для целочисленных, а также текстовых или двоичных данных.
NULL нельзя сравнивать с помощью арифметических операторов. Сравнение для NULL можно делать с помощью IS NULL или IS NOT NULL.
Особенности типа данных Date. Функции для работы с датой.
Даты в MySQL всегда представлены с годом, за которым следует месяц и затем день месяца. Даты часто записывают в виде YYYY-MM-DD, где YYYY -- 4 цифры года, MM -- 2 цифры месяца и DD -- 2 цифры дня месяца.
Тип столбца даты позволяет выполнять несколько операций, таких как сортировка, проверка условий с помощью операторов сравнения и т.д.
Примечание: MySQL требует, чтобы даты были заключены в кавычки
Использование операторов = и !=
Поиск по дате рождения
select p_email, phone from employee_per where birth_date = '1969-12-31';Использование операторов >= и <=
Поиск по дате рождения с использованием оператора >=
select e_id, birth_date from employee_per where birth_date >= '1970-01-01';Определение диапазонов
select e_id, birth_date from employee_per where birth_date BETWEEN'1969-01-01' AND '1974-01-01';Тот же запрос можно представить без конструкции BETWEEN:
select e_id, birth_date from employee_per where birth_date >= '1969-01-01' AND birth_date <= '1974-01-01';Использование Date для сортировки данных
select e_id, birth_date from employee_per ORDER BY birth_date;Выбор данных из значений типа Date.
DAY(date) и DAYOFMONTH(date) функции-синонимы, возвращают из даты порядковый номер дня месяца.
SELECT DAY('2011-04-17'), DAYOFMONTH('2011-04-17');
MONTH(date) и MONTHNAME(date) обе функции возвращают значения месяца. Первая - его числовое значение (от 1 до 12), вторая - название месяца:
Примеры:
Вот как можно выбрать сотрудников, которые родились в марте.
select e_id, birth_date from employee_per where MONTH(birth_date) = 3Можно также использовать вместо чисел названия месяцев.
select e_id, birth_date from employee_per where (birth_date) = 'January';Будьте внимательны при использовании названий месяцев, так как они различают регистр символов. Поэтому January будет работать, а JANUARY не будет
YEAR(date) функция возвращает значение года (от 1000 до 9999).
SELECT YEAR('2011-04-17');
DAYOFYEAR(date) возвращает порядковый номер дня в году (от 1 до 366).
Пример:
Нужно выбрать сотрудников, родившихся в определенный год или в определенный день.
Поиск по году рожденияselect e_id, birth_date from employee_per where year(birth_date) = 1972;Поиск по дню рожденияselect e_id, birth_date from employee_per where DAYOFMONTH(birth_date) = 20 ADDDATE(date, INTERVAL value) Функция возвращает дату date, к которой прибавлено значение value. Значение value может быть отрицательным, тогда итоговая дата уменьшится. В качестве значения value могут выступать не только дни, но и недели (WEEK), месяцы (MONTH), кварталы (QUARTER) и годы (YEAR).Пример:SELECT ___, ADDDATE(date_incoming, INTERVAL -1 WEEK)FROM ___WHERE__;
SUBDATE(date, INTERVAL value) функция идентична предыдущей, но производит операцию вычитания, а не сложения.PERIOD_ADD(period, n) функция добавляет n месяцев к значению даты period. Нюанс: значение даты должно быть представлено в формате YYYYMM. Давайте к февралю 2011 (201102) прибавим 2 месяца: SELECT PERIOD_ADD(201102, 2);.TIMEDIFF(date1, date2) вычисляет разницу в часах, минутах и секундах между двумя датами.DATEDIFF(date1, date2) вычисляет разницу в днях между двумя датами.PERIOD_DIFF(period1, period2) функция вычисляет разницу в месяцах между двумя датами, представленными в формате YYYYMM.SUBTIME(date, time) функция вычитает из времени date время time:DATE(datetime) возвращает дату, отсекая время.Пример:SELECT SUBTIME('2011-04-18 23:17:00', '02:15:30');TIME(datetime) возвращает время, отсекая дату.TO_DAYS(date) и FROM_DAYS(n) взаимообратные функции. Первая преобразует дату в количество дней, прошедших с нулевого года. Вторая, наоборот, принимает число дней, прошедших с нулевого года и преобразует их в дату: MAKEDATE(year, n) функция принимает год и номер дня в году и преобразует их в дату:Текущие даты
Текущую дату, месяц и год можно вывести с помощью аргумента CURRENT_DATE предложений DAYOFMONTH(), MONTH() и YEAR(),То же можно использовать для выборки данных из таблицПример: Поиск по текущему месяцуselect e_id, birth_date from employee_per where MONTH(birth_date) = MONTH(CURRENT_DATE);CURDATE(), CURTIME() и NOW() Первая функция возвращает текущую дату, вторая - текущее время, а третья - текущую дату и время. Функции CURDATE() и NOW() удобно использовать для добавления в базу данных записей, использующих текущее времяПример: SELECT CURDATE(), CURTIME(), NOW();Строковые функции
Рассмотрим команды MySql, предназначенные для обработки строковых данных.