изучаем SQL. Она позволяет решать многошаговые задачи одним выражением

Название	Она позволяет решать многошаговые задачи одним выражением
Дата	09.02.2018
Размер	1.6 Mb.
Формат файла
Имя файла	изучаем SQL.pdf
Тип	Документы #36127
страница	4 из 31

1 2 3 4 5 6 7 8 9 ... 31

Инструмент командной строки mysql
При вызове инструмента командной строки mysql можно задать имя пользователя и используемую БД:

Инструмент командной строки mysql
29
mysql u lrngsql p bank
Будет запрошен ваш пароль, и затем появится приглашение mysql>,
с помощью которого вы сможете создавать SQL выражения и просмат ривать результаты их выполнения. Например, чтобы узнать текущие дату и время, можно выполнить следующий запрос:
mysql> SELECT now( );
+
+
| now( ) |
+
+
| 2005 05 06 16:48:46 |
+
+
1 row in set (0.01 sec)
Функция now( ) – это встроенная функция MySQL, возвращающая те кущие дату и время. Как видите, инструмент командной строки mysql
форматирует результаты запросов, помещая их в прямоугольник, очер ченный символами +, и |. Выведя все результаты (в данном случае это всего одна строка), инструмент командной строки mysql покажет коли чество возвращенных строк и длительность выполнения выражения
SQL.
Завершив работу с инструментом командной строки mysql, для возвра щения в консоль просто введите quit; или exit;.
О пропущенном блоке from
При работе с некоторыми серверами БД нельзя создать запрос без блока from (из), в котором должна быть указана по крайней мере одна таблица. Oracle Database – именно такой сервер. Для тех случаев, когда требуется только вызвать функцию, Oracle предос тавляет таблицу dual (двойственная), состоящую всего из одного столбца dummy (макет), который содержит всего одну строку дан ных. Для обеспечения совместимости с Oracle Database MySQL
тоже предоставляет таблицу dual. Следовательно, предыдущий запрос текущих даты и времени можно было бы написать так:
mysql> SELECT now( )
FROM dual;
+
+
| now( ) |
+
+
| 2005 05 06 16:48:46 |
+
+
1 row in set (0.01 sec)
Если вы не работаете с Oracle и вам не нужна совместимость с этой СУБД, таблицу dual можно полностью игнорировать.

30
Глава 2. Создание и заполнение базы данных
Типы данных MySQL
Вообще говоря, все популярные серверы БД обладают способностью хранить одни и те же типы данных, такие как строки, даты и числа.
Обычно их различие заключается в возможности хранения специаль ных типов данных, например XML документов, или очень больших текстов, или двоичных документов. Поскольку данная книга является введением в SQL и 98 % всех столбцов, которые вы когда либо встре тите, будут простыми типами данных, мы рассмотрим только сим вольные, числовые и временные типы данных.
Символьные данные
Символьные данные могут храниться как строки фиксированной или переменной длины. Разница заключается в том, что строки фиксиро ванной длины справа дополняются пробелами, тогда как строки пере менной длины – нет. При определении столбца символьного типа необ ходимо задать максимальный размер сохраняемой в нем строки. На пример, если предполагается хранить строки длиной до 20 символов,
можно использовать любое из этих описаний:
CHAR(20) /* строка фиксированной длины */
VARCHAR(20) /* строка переменной длины */
В настоящее время максимальная длина этого типа данных составляет
255 символов (хотя в будущих версиях будут допустимы более длин ные строки). Для сохранения более длинных строк (таких как сообще ния электронной почты, XML документы и т. д.) используйте один из текстовых типов – tinytext (крошечный текст), text (текст), mediumtext
(средний текст), longtext (длинный текст)), – рассматриваемых в дан ном разделе позже. В общем, тип char подходит для случая, когда в столбце предполагается хранить только строки одинаковой длины,
например сокращенные названия государств, а тип varchar – для строк разной длины. Типы char и varchar одинаково применимы во всех основ ных серверах БД.
Когда речь идет о применении типа данных varchar, СУБД Oracle
Database является исключением. Пользователи Oracle при опи сании символьных столбцов переменной длины должны приме нять тип varchar2.
Наборы символов
В языках, использующих латинский алфавит, например в англий ском, довольно мало символов, то есть каждый символ хранится как один байт. В других языках, таких как японский и корейский, много символов. Таким образом, в них для хранения одного символа требует ся несколько байт. Поэтому такие наборы символов называют много
байтовыми наборами символов
(multibyte character sets).

32
Глава 2. Создание и заполнение базы данных
С MySQL можно также задавать набор символов по умолчанию для всей базы данных:
CREATE DATABASE foreign_sales CHARACTER SET utf8;
Пожалуй, для ознакомительной книги о наборах символов сказано дос таточно, но еще есть тема локализации, которая гораздо шире. Если вы планируете работать с несколькими наборами символов или использо вать незнакомые наборы символов, обратитесь к таким книгам, как
«Java Internationalization» (O’Reilly) или «Unicode Demystified: A Pra ctical Programmer’s Guide to the Encoding Standard» (Addison Wesley).
Текстовые данные
Если нужно хранить данные, для которых не хватит 255 символов столбца типа char или varchar, вам понадобится один из текстовых типов.
В табл. 2.1 показаны доступные текстовые типы и их максимальные размеры.
Таблица 2.1. Текстовые типы данных MySQL
Выбирая тот или иной текстовый тип, необходимо помнить следующее:
•
Если размер данных, загружаемых в текстовый столбец, превыша ет максимальный размер для этого типа, не поместившиеся данные отсекаются.
•
В отличие от столбца типа varchar, при загрузке данных в такой столбец пробелы в конце строки не удаляются.
•
При использовании столбцов типа text для сортировки или группи ровки используются только первые 1024 байта, хотя при необходи мости это ограничивающее значение можно увеличить.
•
Разные текстовые типы присущи исключительно MySQL. У SQL
Server для больших символьных данных есть только один тип text,
а в DB2 и Oracle применяется тип данных под названием clob (Cha racter Large Object, большой символьный объект).
При создании столбца для данных произвольного формата, например столбца notes (примечания) для хранения информации о взаимодейст виях клиента с отделом клиентского сервиса вашей компании, кото рую вам не хотелось бы ограничивать 255 символами, следует выбрать тип text или mediumtext.
Тип
Максимальное число символов
Tinytext
255
Text
65 535
Mediumtext
16 777 215
Longtext
4 294 967 295

Типы данных MySQL
33
В Oracle Database допускаются столбцы char до 2000 байт и var char до 4000 байт. SQL Server может оперировать данными типа char и varchar размером до 8000 байт. Поэтому при работе с Orac le или SQL Server потребность в текстовых типах данных мень ше, чем при работе с MySQL. Однако начиная с версии 5.0.3
(в настоящее время она проходит бета тестирование) MySQL об скачет оба эти сервера, поскольку максимальный размер столб цов типа char и varchar в нем достигнет 65 535 байт.
Числовые данные
Хотя и кажется, что хватило бы одного числового типа данных с на званием «numeric» (числовой), все же есть разные числовые типы, от ражающие разные способы использования чисел, как показано ниже:
Столбец, являющийся индикатором поставки заказа покупателю
Столбец такого типа, называемого Boolean (булев), может содер жать 0, что означает false (ложь) и 1, что означает true (истина).
Первичный ключ для таблицы транзакций, генерируемый системой
Обычно начинается с 1 и увеличивается с шагом 1, возможно, до очень больших значений.
Номер позиции в клиентской электронной корзине для покупок
Значениями столбца данного типа являются положительные целые числа от 1 до (максимум) 200 (для фанатов шопинга).
Данные позиционирования сверлильного станка для печатных плат
Высокоточные научные или технологические данные часто требу ют точности до восьми десятичных знаков.
MySQL располагает несколькими разными числовыми типами для ра боты с этими (и многими другими) видами информации. Наиболее час то числовые типы используют для хранения целых чисел. При зада нии одного из таких типов можно также указать, что данные беззнако
вые
, тогда сервер будет знать, что все хранящиеся в столбце данные не отрицательные. В табл. 2.2 показано пять разных типов данных,
предназначенных для хранения целых чисел.
Таблица 2.2. Целые типы данных MySQL
Тип
Диапазон значений со знаком Диапазон значений без знака
Tinyint от 128 до 127
от 0 до 255
Smallint от 32 768 до 32 767
от 0 до 65 535
Mediumint от 8 388 608 до 8 388 607
от 0 до 16 777 215
Int от 2 147 483 648 до 2 147 483 647
от 0 до 4 294 967 295
Bigint от 9 223 372 036 854 775 808 до 9 223 372 036 854 775 807
от 0 до 18 446 744 073 709 551 615

34
Глава 2. Создание и заполнение базы данных
При создании столбца одного из целых типов MySQL выделит для хра нения данных соответствующее количество памяти – от 1 байта для типа tinyint до 8 байт для bigint. Поэтому попытайтесь подобрать тип достаточного размера для хранения самого большого из предполагае мых чисел без неоправданного расхода памяти.
Для чисел с плавающей точкой (таких как 3,1415927) можно выбрать один из типов, приведенных в табл. 2.3.
Таблица 2.3. Типы данных MySQL для чисел с плавающей точкой
Для типа с плавающей точкой можно задать точность (precision) (об щее допустимое число разрядов, как справа, так и слева от десятичной точки) и масштаб (scale) (допустимое число разрядов справа от десятич ной точки), но эти параметры не являются обязательными. В табл. 2.3
они представлены как p и s. Задавая точность и масштаб для столбца,
имеющего тип с плавающей точкой, необходимо помнить, что сохра няемые в нем данные будут округляться, если число разрядов в них превысит заданный масштаб и/или точность. Например, столбец, оп ределенный как float(4,2), будет сохранять всего четыре разряда, два слева и два справа от десятичной точки. Поэтому с такими числами,
как 27,44 и 8,19, будет все в порядке, а вот число 17,8675 будет округ лено до 17,87, а число 178,5 будет округлено (грубо) до 99,99 – самое большое число, которое может быть сохранено в этом столбце.
Как и данные целого типа, данные с плавающей точкой могут быть оп ределены как unsigned (беззнаковые), но это только предотвращает хранение в столбце отрицательных чисел, но не изменяет диапазон данных, которые могут быть сохранены в столбце.
Временные данные
Наряду со строками и числами довольно часто приходится работать с ин формацией о датах и/или времени. Этот тип данных называют времен
ным
(temporal). К примерам временных данных в базе данных относятся:
•
Дата будущего события, например доставки заказа покупателю
•
Фактическая дата доставки заказа покупателю
•
Дата и время изменения пользователем определенной строки таб лицы
•
Дата рождения сотрудника
•
Год, соответствующий строке таблицы yearly_sales (продажи за год)
в хранилище данных
Тип
Числовой диапазон
Float(p,s)
от 3,402823466E+38 до 1,175494351E 38 и от 1,175494351E 38
до 3,402823466E+38
Double(p,s)
от 1,7976931348623157E+308 до 2,2250738585072014E 308
и от 2,2250738585072014E 308 до 1,7976931348623157E+308

Типы данных MySQL
35
•
Время, необходимое для монтажа электропроводки в автомобиле на сборочном конвейере
В MySQL есть типы данных для обработки всех подобных ситуаций.
В табл. 2.4 показаны временные типы данных, поддерживаемые MySQL.
Таблица 2.4. Временные типы данных MySQL
Серверы БД хранят временные данные по разному, и назначение фор матирующей строки (второй столбец табл. 2.4) – показать, как будут представлены данные при извлечении, а также то, как должна быть сформирована строка даты при вставке или обновлении столбца вре менного типа. Таким образом, если бы вам понадобилось вставить дату
23 марта 2005 года в столбец date (дата) с форматом по умолчанию YYYY
MM DD
, то вы бы использовали строку '2005 03 23'. Построение и отобра жение временных данных подробно рассмотрено в главе 7.
На всех серверах БД допустимы различные диапазоны дат для столбцов временного типа. Oracle Database допускает даты от
4712 г. до н. э. до 9999 г. н.э., тогда как SQL Server обрабатыва ет только даты от 1753 г. н. э. до 9999 г. н. э. Хотя для большин ства систем, отслеживающих текущие и будущие события,
большой разницы здесь нет, об этом важно помнить при хране нии исторических дат.
Различные компоненты форматов даты, приведенных в табл. 2.4, опи саны в табл. 2.5.
Таблица 2.5. Компоненты формата даты
Тип
Формат по умолчанию
Допустимые значения
Date
YYYY MM DD
от 1000 01 01 до 9999 12 31
Datetime
YYYY MM DD HH:MI:SS от 1000 01 01 00:00:00 до 9999 12 31 23:59:59
Timestamp
YYYY MM DD HH:MI:SS от 1970 01 01 00:00:00 до 2037 12 31 23:59:59
Year
YYYY
от 1901 до 2155
Time
HHH:MI:SS
от 838:59:59 до 838:59:59
Компонент
Описание
Диапазон
YYYY
Год, включая столетие от 1000 до 9999
MM
Месяц от 01 (январь) до 12 (декабрь)
DD
День от 01 до 31
HH
Час от 01 до 24
HHH
Часы (прошедшие)
от 838 до 838
MI
Минута от 01 до 60
SS
Секунда от 01 до 60

36
Глава 2. Создание и заполнение базы данных
Вот как были бы использованы различные временные типы при реали зации приведенных выше примеров:
•
Для хранения предполагаемой даты доставки заказа покупателю и даты рождения сотрудника использовались бы столбцы типа date,
поскольку знать точное время рождения человека необязательно,
а спланировать будущую доставку с точностью до секунды нереально.
•
Для хранения информации о фактической доставке заказа покупа телю использовался бы тип datetime (дата и время), поскольку важ но отследить не только дату, но и точное время доставки.
•
Столбец, отслеживающий время последнего изменения пользовате лем определенной строки таблицы, использовал бы тип timestamp
(временная метка). Этот тип содержит ту же информацию, что и тип datetime
(год, месяц, день, час, минуту, секунду), но при добавлении или изменении строки таблицы сервер MySQL автоматически за полнит столбец timestamp текущими значениями даты/времени.
•
Столбец для хранения только данных о годе использовал бы тип year
(год).
•
Столбцы, содержащие данные о временном интервале, необходимом для выполнения задачи, использовали бы тип time (время). Этому типу данных не нужно хранить компонент даты – это сбивало бы с толку, поскольку интерес представляет только количество часов/
минут/секунд, необходимое для выполнения задания. Эту информа цию можно было бы получить, найдя разность значений из двух столбцов типа datetime (первый хранит дату/время начала выполне ния задания, а второй – дату/время его завершения). Но проще ис пользовать один столбец time.
В главе 7 будет рассказано, как работать с каждым из этих временных типов данных.
Создание таблиц
Теперь, имея четкое представление о том, какие типы данных могут храниться в базе данных MySQL, самое время взглянуть, как эти типы используются при описании таблиц. Начнем с описания таблицы для хранения информации о человеке.

1 2 3 4 5 6 7 8 9 ... 31