Пример 1: Вывести список стран, площадь которых больше 1 млн. кв. км: SELECT Название
,Столица
,Площадь
,Население
,Континент FROM
Страны WHERE
Площадь > 1000000
Пример 2: Вывести список стран, население которых не больше 1 млн. чел.: SELECT Название
,Столица
,Площадь
,Население
,Континент FROM
Страны WHERE
Население !> 1000000
Пример 3: Вывести список африканских стран: SELECT Название
,Столица
,Площадь
,Население
,Континент FROM
Страны WHERE
Континент = 'Африка'
Пример 4: Вывести список всех стран, кроме европейских: SELECT Название
,Столица
,Площадь
,Население
,Континент FROM
Страны WHERE
Континент != 'Европа'
Пример 5: Вывести список стран, население которых больше 1 млн. чел., а площадь меньше 100 тыс. кв. км:
SELECT Название
,Столица
,Площадь
,Население
,Континент FROM
Страны WHERE
(Население > 1000000) AND (Площадь < 100000)
Пример 6: Вывести список стран, которые находятся в Европе и их население больше 10 млн. чел., или находятся в Азии, а население больше 50 млн. чел.:
SELECT Название
,Столица
,Площадь
,Население
,Континент FROM
Страны WHERE
(Континент = 'Европа') AND (Население > 10000000) OR
(Континент = 'Азия') AND (Население > 50000000)
Пример 7: Вывести список стран, население которых от 10 до 100 млн. чел., а площадь от 100 до 200 тыс. кв. км:
SELECT Название
,Столица
,Площадь
,Население
,Континент FROM
Страны WHERE
(Население BETWEEN 10000000 AND 100000000) AND
(Площадь >= 100000) AND (Площадь <= 200000)
Пример 8: Вывести отсортированный в алфавитном порядке список стран от Бенина до Ватикана:
SELECT Название
,Столица
,Площадь
,Население
,Континент FROM
Страны WHERE
Название BETWEEN 'Бенин' AND 'Ватикан' ORDER BY
Название
Пример 9: Вывести список стран, название которых начинается с буквы «С»: SELECT Название
,Столица
,Площадь
,Население
,Континент FROM
Страны WHERE
Название LIKE 'С%'
Пример 10: Вывести список стран, в названии которых вторая буква - «а», а последняя - я»: SELECT Название
,Столица
,Площадь
,Население
,Континент FROM
Страны WHERE
Название LIKE '_а%я'
Пример 11: Вывести список стран, в названии которых третья буква - «а», «о» или «у»: SELECT Название
,Столица
,Площадь
,Население
,Континент FROM
Страны WHERE
Название LIKE ' [аоу]%'
Пример 12: Вывести список стран, название которых начинается с буквы от «А» до «Г»: SELECT Название
,Столица
,Площадь
,Население
,Континент FROM
Страны WHERE
Название LIKE '[А-Г]%'
Пример 13: Вывести список стран, название которых не начинается с буквы от «А» до
«Г» или с буквы «С»:
SELECT Название
,Столица
,Площадь
,Население
,Континент FROM
Страны WHERE
Название LIKE '[^А-Г,^С]%'
Пример 14: Вывести список стран, столицы которых не введены в базу: SELECT Название
,Столица
,Площадь
,Население
,Континент FROM
Страны WHERE
Столица IS NULL
Пример 15: Вывести список европейских, азиатских и африканских стран: SELECT Название
,Столица
,Площадь
,Население
,Континент FROM
Страны WHERE
Континент IN ('Европа', 'Азия', 'Африка')
Задание
Вывести названия и столицы пяти наибольших стран по площади.
Вывести список африканских стран, население которых не превышает 1 млн. чел.
Вывести список стран, население которых больше 5 млн. чел., а площадь меньше 100 тыс. кв. км, и они расположены не в Европе.
Вывести список стран Северной и Южной Америки, население которых больше 20 млн. чел., или стран Африки, у которых население больше 30 млн. чел.
Вывести список стран, население которых составляет от 10 до 100 млн. чел., а пло- щадь не больше 500 тыс. кв. км.
Вывести список стран, названия которых не начинаются с буквы «К».
Вывести список стран, в названии которых третья буква - «а», а предпоследняя - «и».
Вывести список стран, в названии которых вторая буква - гласная.
Вывести список стран, названия которых начинаются с букв от «К» до «П».
Вывести список стран, названия которых начинаются с букв от «А» до «Г», но не
с буквы «Б».
Вывести список стран, столицы которых есть в базе.
Вывести список стран Африки, Северной и Южной Америки.
Лабораторная работа 4
Типы данных и встроенные функции
Цель работы
Изучить основные типы данных.
Изучить встроенные функции для работы со строками.
Изучить встроенные функции для работы с числами.
Изучить встроенные функции для работы с датами и временем.
Изучить встроенные функции преобразования данных.
Изучить CASE и IIF.
Теоретическая часть
Типы данных
На языке Transact-SQL используется множество различных типов данных. Всех их можно разделить на следующие группы:
Числовые типы данных: BIT (значение 0 или 1), TINYINT (от 0 до 255), SMALLINT (от
-32768 до 32767), INT (от -2147483648 до 2147483647), BIGINT (от -9223372036854775808 до
9223372036854775807), DECIMAL (числа c фиксированной точностью), NUMERIC: (аналоги- чен типу DECIMAL), SMALLMONEY (дробные значения от -214748.3648 до 214748.3647), MONEY (дробные значения от -922337203685477.5808 до 922337203685477.5807), FLOAT (от
-1.79E+308 до 1.79E+308), REAL (числа от -340E+38 до 3.40E+38);
Типы данных, представляющие дату и время: DATE (дата от 01/01/0001 до 31/12/9999), TIME (время в диапазоне от 00:00:00.0000000 до 23:59:59.9999999), DATETIME (дата и время от 01/01/1753 до 31/12/9999), DATETIME2 (дата и время от 01/01/0001 00:00:00.0000000 до 31/12/9999 23:59:59.9999999), SMALLDATETIME (дата и время от 01/01/1900 до 06/06/2079),
DATETIMEOFFSET (дата и время от 01/01/0001 до 31/12/9999);
Строковые типы данных: CHAR (фиксированная строка длиной от 1 до 8000 симво- лов), VARCHAR (переменная строка длиной от 1 до 8000 символов), NCHAR (Unicode - фик- сированная строка длиной от 1 до 4000 символов), NVARCHAR (Unicode - переменная строка длиной от 1 до 4000 символов), TEXT и NTEXT (устаревшие, не рекомендуется использовать);
Бинарные типы данных: BINARY (фиксированные бинарные данные от 1 до 8000 байт), VARBINARY (переменные бинарные данные от 1 до 8000 байт), IMAGE (устаревшая, не рекомендуется использовать);
Другие типы данных: UNIQUEIDENTIFIER (уникальный идентификатор GUID), TIMESTAMP (номер версии строки в таблице), CURSOR (набор строк таблицы),
HIERARCHYID (позиция в иерархии), SQL_VARIANT (данные любого типа), XML (доку- менты или фрагменты XML), TABLE (таблица), GEOGRAPHY (географические данные, такие как широта и долгота), GEOMETRY (координаты на плоскости).
Встроенные функции Transact-SQL
Функции SQL производят действия с данными и возвращают результат. Встроенные функции делятся на три основные группы:
скалярные функции - обрабатывают одиночное значение и возвращают одно значе- ние. Их можно использовать везде, где допускается применение выражений.
агрегатные функции - используются для получения обобщающих значений. Они, в отличие от скалярных функций, оперируют значениями столбцов множества строк;
- функции для списка значений.
Скалярные функции бывают следующих категорий:
строковые функции - выполняют определенные действия над строками и возвращают строковые или числовые значения;
числовые функции - возвращают числовые значения на основании заданных в аргу- менте значений того же типа;
функции времени и даты - выполняют различные действия над входными значениями времени и даты и возвращают строковое, числовое значение или значение в формате даты и времени;
функции преобразования типа.
Список часто используемых строковых функций:
|
LEN(строка) |
возвращает количество символов в заданной строке |
|
|
TRIM(строка) TRIM([символ FROM] строка) |
удаляет символ пробела или другие заданные символы в начале и в конце строки. |
|
|
LTRIM(строка) |
удаляет начальные пробелы из заданной строки |
|
|
RTRIM(строка) |
удаляет конечные пробелы из заданной строки |
|
|
CHARINDEX(подстрока, строка) CHARINDEX(подстрока, строка, началь- ная позиция) |
возвращает индекс, по которому находится первое вхождение подстроки в строке. |
|
|
PATINDEX('%шаблон%', строка) |
возвращает индекс, по которому находится первое вхождение определенного шаблона в строке |
|
|
LEFT(строка, число) |
возвращает с начала строки определенное количество символов |
|
|
RIGHT(строка, число) |
возвращает с конца строки определенное количество символов |
|
|
SUBSTRING(строка, начальная позиция, длина ) |
возвращает подстроку заданной длиной, начиная с дан- ной позиции |
|
|
REPLACE(строка, подстрока, замена) |
заменяет одну подстроку другой |
|
|
REVERSE(строка) |
переворачивает строку наоборот |
|
CONCAT(строка1, строка2 [, строкаN ] ) |
объединяет заданные строки в одну |
|
|
LOWER(строка) |
переводит строку в нижний регистр |
|
|
UPPER (строка) |
переводит строку в верхний регистр |
|
|
SPACE(число) |
возвращает заданное количество пробелов |
|
|
REPLICATE(строка, число) |
повторяет значение строки указанное число раз |
|
|
STUFF(строка, начальная позиция, коли- чество, замена) |
удаляет указанное количество символов первой строки в начальной позиции и вставляет на их место замену. |
Список часто используемых числовых функций:
|
ABS(число) |
возвращает абсолютное значение числа |
|
|
CEILING(число) |
возвращает наименьшее целое, большее или равное задан- ного числа. |
|
|
FLOOR(число) |
возвращает наибольшее целое число, меньшее или равное заданного числа |
|
|
POWER(число, степень) |
возвращает значение указанного выражения, возведенное в заданную степень |
|
|
RAND([начальное значение]) |
возвращает псевдослучайное значение от 0 до 1 |
|
|
ROUND(число, точность) |
возвращает число, округленное до указанной точности |
|
|
SIGN(число) |
возвращает положительное (+1), нулевое (0) или отрица- тельное (-1) значение, обозначающее знак заданного выра- жения |
|
|
SQRT(число) |
возвращает квадратный корень данного числа |
|
|
SQUARE(число) |
возвращает квадрат указанного числа |
|
|
PI() |
возвращает константное значение р |
|
|
ACOS(число) |
возвращает угол в радианах, косинус которого задан - арк- косинус. |
|
|
ASIN(число) |
возвращает угол в радианах, синус которого задан - аркси- нус. |
|
|
ATAN(число) |
возвращает угол в радианах, тангенс которого задан - арк- тангенс. |
|
|
COS(число) |
возвращает косинус указанного угла в радианах. |
|
|
SIN(число) |
возвращает синус указанного угла в радианах. |
|
|
TAN(число) |
возвращает тангенс указанного угла в радианах. |
|
|
COT(число) |
возвращает котангенс указанного угла в радианах |
|
|
DEGREES(число) |
возвращает для значения угла в радианах соответствующее значение в градусах. |
|
|
RADIANS(число) |
возвращает для значения угла в градусах соответствующее значение в радианах |
|
|
EXP(число) |
возвращает экспонент заданного числа |
|
|
LOG(число) |
возвращает натуральный логарифм указанного числа |
|
|
LOG(число, основа) |
возвращает логарифм указанного числа |
|
|
LOG10(число) |
возвращает десятичный логарифм указанного числа |
Список часто используемых функций времени и даты:
|
GETDATE() |
возвращает текущую дату и время |
|
|
CURRENT_TIMEZONE() |
возвращает имя часового пояса |
|
|
GETUTCDATE() |
возвращает текущую дату и время по Гринвичу (UTC/GMT) |
|
|
DAY(дата) |
возвращает день месяца указанной даты |
|
|
MONTH(дата) |
возвращает номер месяца указанной даты |
|
|
YEAR(дата) |
возвращает год указанной даты |
|
|
DATEPART(часть, дата) |
возвращает целое число, представляющее указанную часть заданной даты |
|
|
DATENAME(часть, дата) |
возвращает строку символов, представляющую указан- ную часть заданной даты |
|
|
DATEADD(часть, число, дата) |
добавляет указанное целое число со знаком к части входного значения даты, а затем возвращает это изме- ненное значение |
|
|
DATEDIFF(часть, начальная дата, конеч- ная дата) |
возвращает разницу как целое число со знаком между частями заданных дат |
|
|
EOMONTH(дата) |
возвращает последний день месяца, заданной даты |
Для функций времени и даты используются следующие аргументы как часть даты и времени:
|
Часть даты и времени |
Сокращения |
|
|
year |
yy, yyyy |
|
|
quarter |
qq, q |
|
|
month |
mm, m |
|
|
dayofyear |
dy, y |
|
|
day |
dd, d |
|
|
week |
wk, ww |
|
|
weekday |
dw |
|
|
hour |
hh |
|
|
minute |
mi, n |
|
|
second |
ss, s |
|
|
millisecond |
ms |
|
|
microsecond |
mcs |
|
|
nanosecond |
ns |
|
|
tzoffset |
tz |
|
|
iso_week |
isowk, isoww |
Список часто используемых функций преобразования:
|
CAST(выражение AS тип) |
преобразуют выражение в заданный тип |
|
|
CONVERT(тип, выражение [, стиль]) |
||
|
ASCII(строка) |
возвращает код ASCII первого символа указанного символьного выражения |
|
|
UNICODE(строка) |
возвращает код Юникод первого символа указанного символьного выражения |
|
|
CHAR(число) |
возвращает символ ASCII с указанным кодом |
|
|
NCHAR(число) |
возвращает символ Юникода с указанным кодом |
|
|
STR(число) |
возвращает символьные данные, преобразованные из числовых данных |