Когда портятся хорошие выражения

До сих пор все SQL-выражения для работы с данными, приведенные в этой главе, были правильными и играли по правилам. Однако, исходя из описаний таблиц person и favorite_food, у вас есть много возможностей наделать ошибок при вставке или изменении данных.

Распространенные ошибки:

1. Неуникальный первичный ключ.

Поскольку описания таблиц включают создание ограничений первичного ключа, MySQL проверит, чтобы в таблицы не вводились дублирующие значения. Следующее выражение делает попытку обойти свойство автоприращения столбца person_id и создать в таблице person еще одну строку со значением person_id, равным 1:

INSERT INTO person > (person_id, fname, lname, gender, birth_date)

VALUES (1, 'Charles','Fulton', 'M', '1968-01-15');

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

2. Несуществующий внешний ключ

Описание таблицы favorite_food включает создание ограничения внешнего ключа для столбца person_id. Это ограничение гарантирует, что все значения person_id, введенные в таблицу favorite_food, имеются в таблице person. Вот что произошло бы при попытке создания строки, нарушающей это ограничение:

INSERT INTO favorite_food (person_id, food)

VALUES (999, 'lasagna');

В этом случае таблица favorite_food считается дочерней (child), а таблица person – родителем (parent), поскольку таблица favorite_food зависит от данных таблицы person. Если требуется ввести данные в обе таблицы, сначала следует создать строку в person, а затем уже можно будет ввести данные в favorite_food. Ограничения внешнего ключа выполняются, только если таблицы создаются с использованием механизма хранения InnoDB.

3. Применение недопустимых значений

Столбец gender таблицы person может иметь только два значения: 'M' для мужчин и 'F' для женщин. Если по ошибке делается попытка задать любое другое значение, будет ошибка

UPDATE person

SET gender = 'Z'

WHERE person_id = 1;

Выражение update не даст сбой, но будет сформировано предупреждение. Чтобы увидеть описание предупреждения, можно выполнить команду show warnings

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

Оператор SELECT.

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

Разберем на примерах. В качестве примера, будем использовать таблицу:

Рассмотрим таблицу:

Запрос для создания такой таблицы:

CREATE DATABASE mydb;

CREATE TABLE IF NOT EXISTS `mydb`.`realization` (

`id_realization` INT NOT NULL AUTO_INCREMENT,

`Data` DATETIME NOT NULL,

`customer` VARCHAR(45) NOT NULL,

`product` VARCHAR(45) NOT NULL,

`count` INT NOT NULL,

`price` DECIMAL(10,2) NOT NULL,

PRIMARY KEY (`id_realization`))

ENGINE = InnoDB;

INSERT INTO `mydb`.`realization` (`id_realization`, `Data`, `customer`, `product`, `count`, `price`) VALUES ('2', '2007-10-03 13:56:00', 'Иванов', 'Принтер', '3', '3500');

INSERT INTO `mydb`.`realization` (`id_realization`, `Data`, `customer`, `product`, `count`, `price`) VALUES ('2', '2007-10-03 14:30:00', 'Петров', 'Принтер', '2', '3500');

INSERT INTO `mydb`.`realization` (`id_realization`, `Data`, `customer`, `product`, `count`, `price`) VALUES ('3', '2007-10-03 14:35:00', 'Сидоров', 'Ноутбук', '1', '45000');

INSERT INTO `mydb`.`realization` (`id_realization`, `Data`, `customer`, `product`, `count`, `price`) VALUES ('4', '2007-10-03 16:30:00', 'Калюбанов', 'Ноутбук', '1', '45000');

INSERT INTO `mydb`.`realization` (`id_realization`, `Data`, `customer`, `product`, `count`, `price`) VALUES ('5', '2007-10-04 10:30:00', 'Иванов', 'Мышь', '5', '1200');

INSERT INTO `mydb`.`realization` (`id_realization`, `Data`, `customer`, `product`, `count`, `price`) VALUES ('6', '2007-10-04 10:35:00', 'Лосев', 'Ноутбук', '1', '45000');

INSERT INTO `mydb`.`realization` (`id_realization`, `Data`, `customer`, `product`, `count`, `price`) VALUES ('7', '2007-10-04 11:35:00', 'Чуркин', 'Принтер', '1', '3500');

INSERT INTO `mydb`.`realization` (`id_realization`, `Data`, `customer`, `product`, `count`, `price`) VALUES ('8', '2007-10-05 10:35:00', 'Сидоров', 'Принтер', '4', '3500');

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

SELECT * FROM realization;

SELECT – выбрать

* - выбрать все столбцы

FROM – из

2. Если требуется выбрать только некоторые столбцы указываем их через запятую после слова SELECT:

SELECT customer, product FROM realization;

3. Для устранения повторяющихся строк используется команда DISTINCT

SELECT DISTINCT customer FROM realization;

Обратная команда ALL – она включена по умолчанию.

4. Если требуется извлечь данные по какому-то условию, используется команда WHERE – ГДЕ :

SELECT * FROM realization WHERE customer = 'Иванов';

Получим только записи по клиенту Иванову.

5. Условия могут быть составными:

SELECT * FROM realization WHERE customer = 'Иванов' or customer = 'Сидоров';

6. В условиях можно применять знаки сравнения:

SELECT product FROM realization WHERE price<=3500;

7. Сравнивать можно и даты:

SELECT * FROM realization WHERE data<'2007-10-04 10:30:00';

При составлении условия в блоке WHERE используются операторы сравнения и логические операторы AND OR NOT, а также операторы IN, BETWEEN, LIKE, IS NULL

8. Получить все продажи, кроме принтера.

SELECT * FROM realization WHERE NOT product = 'Принтер';

9. Получить продажи товаров, все, кроме принтера и мыши

SELECT * FROM realization WHERE NOT (product = 'Принтер' OR product = 'Мышь');

10. Получить продажи По Сидорову и Лосеву:

SELECT * FROM realization WHERE customer = 'Лосев' or customer = 'Сидоров';

11. Но можно тоже самое сделать с помощью оператора IN:

SELECT * FROM realization WHERE customer IN ('Лосев', 'Сидоров');

Оператор IN определяет набор значений, в который данное значение может или может не быть включено.

12. BETWEEN определяет диапазон значений.

SELECT * FROM realization WHERE price BETWEEN 3500 AND 40000;

Границы диапазона включаются.

13. Оператор LIKE применим только для полей типа CHAR или VARCHAR. И осуществляет поиск подстроки по шаблону. Имеется два вида шаблонов:

Символ _ - замещает одиночный символ

Символ % замещает последовательность символов.

SELECT * FROM realization WHERE customer LIKE 'Л%'

SELECT * FROM realization WHERE customer LIKE 'Л_сев'

14. Часто бывает необходимо выбрать только те значения, которые не равны NULL. Для этого используют команду NOT NULL. Обратная команда IS NULL

SELECT * FROM realization WHERE customer IS NULL;

SELECT * FROM realization WHERE NOT customer IS NULL;

15. В выражении SELECT можно также использовать вычисляемые поля. О них более подробно будем проходить на других уроках. Сейчас кратко:

SELECT data, customer, product, count, price, count*price FROM realization;

Таким образом, мы получили сумму по товару. Но название столбца нам не нравится. Чтобы придать вычисляемому полю нормальное название, используем псевдоним:

SELECT data, customer, product, count, price, count*price AS summ FROM realization;

Встроенные функции

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

Функции для работы со строками:

1. Одна из наиболее широко используемых строковых функций, возвращающих числа, – функция length() (длина), которая возвращает число символов в строке.

SELECT LENGTH(customer) FROM realization;

2. Кроме определения длины строки может потребоваться найти местоположение подстроки в строке. Для этого используется функция position() (положение)

SELECT POSITION('сев' IN customer) FROM realization;

Если не получается найти подстроку, функция position() возвращает 0. Порядковый номер первого символа в строке равен 1. Если функция position() возвращает значение 0, это указывает на то, что подстрока не найдена, а не на то, что подстрока обнаружена в строке на первой позиции.

3. Если требуется начать поиск не с первого символа целевой строки, необходимо использовать функцию locate(), аналогичную функции position() за тем исключением, что допускает третий необязательный параметр, предназначенный для задания стартовой позиции поиска.

SELECT LOCATE ('сев', customer, 3) FROM realization;

4. Еще одна функция, принимающая строки в качестве аргументов и возвращающая числа, – функция сравнения строк strcmp(). Strcmp(), которая реализована только в MySQL и не имеет аналогов в Oracle Database или SQL Server. Она принимает в качестве аргументов две строки и возвращает одно из следующих значений:

-1 если первая строка в порядке сортировки расположена до второй строки

0 если строки идентичны

1 если первая строка в порядке сортировки расположена после второй строки

SELECT STRCMP('12345','12345') 12345_12345; 0

SELECT STRCMP('abcd','xyz') abcd_xyz; -1

SELECT STRCMP('xyz','qrstuv') xyz_qrstuv; 1

5. Функции concat(). Соединяет строки.

SELECT CONCAT('Клиент: ', customer) customer_txt FROM realization;

Кроме упомянутых, сервера включают множество других встроенных функций для работы со строковыми данными. Более подробную информацию можно получить в справочном руководстве по SQL для конкретного сервера или универсальном справочнике по SQL, например «SQL in a Nutshell» (O’Reilly).

Функции для работы с числовыми данными:

Для вычислений доступны все обычные арифметические операторы (+, -, *, /), а для задания порядка вычислений – скобки. Большинство функций для работы с числовыми данными являются специфическими (минус, косинус и т.п.) изучать мы их не будем. Рассмотрим только некоторые:

1. Функция mod() – вычисляет остаток от деления.

SELECT MOD(10,4);

2. Функция pow() – возведение в степень.

SELECT POW(2,8);

3. Функция round() – округление.

SELECT ROUND(72.0909, 1), ROUND(72.0909, 2), ROUND(72.0909, 3);

4. Функция sign() (знак), возвращает: 1, если число отрицательн0, 0, если равно нулю, и 1, если число положительно.

5. Функция abs() – число по модулю.

Функции для работы с датами и временем

1. Функция now() – возвращает текущую дату

SELECT NOW();

2. Функции DAY(), MONTH(), YEAR() – возвращают соответственно день, месяц и год заданной даты.

3. Функция MySQL date_add(дата, интервал) позволяет добавить любой интервал (т. е. дни, месяцы, года) к заданной дате, чтобы получить другую дату

SELECT DATE_ADD(DATA, INTERVAL 5 DAY) FROM realization;

Типы интервалов

Second Количество секунд

Minute Количество минут

Hour Количество часов

Day Количество дней

Month Количество месяцев

Year Количество лет

Minute_second Количества минут и секунд, разделенные двоеточием

Hour_second Количества часов, минут и секунд, разделенные двоеточием

Year_month Количества лет и месяцев, разделенные дефисом

4. Функция last_day() (последний день) – последний день месяца

Все функции можно посмотреть в справочнике

http://www.mysql.ru/docs/man/Functions.html