Занятие 8

Тема: "Использование и создание функций в разных СУБД"

Цель: получить практические навыки по использованию встроенных функций при построении запросов в разных СУБД

Пусть имеется база данных студентов с полями:

Из таблицы "stud" выполните выбор следующих данных:

1. Отобрать фамилию и инициалы всех студентов мужского пола.

2. Вывести уникальный список всех групп без номеров.

3. Вывести уникальный список всех групп без номеров. При этом заменить 2-значное обозначение года поступления на 4-значное (13 - 2013, 14 - 2014 и т.д.). Например:

4. Вывести ФИО студентов заглавными буквами, отсортировать по фамилии.

5. Вывести список студентов, родившихся в 1997 году

6. Вывести список студентов, родившихся сегодня

7. Вывести список студентов, у которых день рождения будет на протяжении следующих 7 дней (включая сегодняшний).

Указания: Введите тексты запросов:

Запрос 1.

select fam+' '+left(imya,1)+'. '+left(otch,1)+'.' as fio

from stud

where pol='м'

Запрос 2.

Номер группы с пробелом занимает три символа, следовательно выделим из текста все символы, начиная с 4 и до конца текста. Будем использовать функцию substring

select distinct substring(grupa,4,len(grupa)-3) as nazv_grup

from stud

order by nazv_grup

Запрос 3.

Обрезку номера группы будем выполнять аналогично предыдущему запросу.

Для поиска вхождения года в названии группы будем использовать функцию charindex, а для замены года будем использовать функцию replace. Всего в нашей таблице есть группы с годами: 13, 14, 15, 16.

select distinct case when charindex('13',substring(grupa,4,len(grupa)-3))>0

then replace(substring(grupa,4,len(grupa)-3), '13', '2013')

when charindex('14',substring(grupa,4,len(grupa)-3))>0

then replace(substring(grupa,4,len(grupa)-3), '14', '2014')

when charindex('15',substring(grupa,4,len(grupa)-3))>0

then replace(substring(grupa,4,len(grupa)-3), '15', '2015')

when charindex('16',substring(grupa,4,len(grupa)-3))>0

then replace(substring(grupa,4,len(grupa)-3), '16', '2016')

end as nazv_grup

from stud

order by nazv_grup

Запрос 4.

select upper(fam) as fam,

upper(imya) as imya,

upper(otch) as otch

from stud

order by fam

Запрос 5.

select *

from stud

where year(datar)=1997

order by fam

Запрос 6.

select *

from stud

where day(datar)=day(getdate())

and month(datar)=month(getdate())

order by fam

Запрос 7.

Для построения запроса нужно получить диапазон дат: от текущей: getdate() до +7 дней: dateadd(day,7,getdate())

select *

from stud

where day(datar) between day(getdate())

and day(dateadd(day,7,getdate()))

and month(datar) between month(getdate())

and month(dateadd(day,7,getdate()))

order by fam

Над данными таблицы "stud" выполнить следующие действия:

1. Все фамилии записать заглавными буквами, а имена - маленькими.

2. Все фамилии и имена записать с большой буквы и удалить начальные и конечные пробелы

3. Всем студентам, у которых день рождения в текущем месяце, в поле "fam" к фамилии добавить знак "+".

Указания: скачайте архив БД, распакуйте его.

В архиве находится файл со скриптом БД.

Запустите утилиту EMS SQL Manager for MySQL.

Если в левой части окна отображается БД dekanat, то вы можете приступать к работе с ней.

Если БД нет, т о ее нужно подключить. Для этого в правой части окна программы выберите команду "Выполнить SQL скрипт". В новом окне выберите команду "Выполнить скрипт из файла". В диалоговом окне укажите распакованный файл скрипта. Если появится окно регистрации, то укажите Пользовать=root, Пароль=111. Если такого окна не появилось, то нажимайте в окне ОК. Появится сообщение, что скрипт выполнен успешно и появится окно для регистрации БД. Перенесите ее в правую часть окна и нажмите ОК. В результате БД будет установлена, зарегистрирована и готова к работе.

В левой части окна можете открыть БД, просмотреть ее таблицы.

Для выполнения запросов в окне выполните команду "Инструменты - Показать редактор SQL". В результате в правой части окна откроется поле для ввода тестов запросов.

Из таблицы "stud" выполните выбор следующих данных:

1. Отобрать фамилию и инициалы всех студентов мужского пола.

2. Вывести уникальный список всех групп без номеров.

3. Вывести уникальный список всех групп без номеров. При этом заменить 2-значное обозначение года поступления на 4-значное (13 - 2013, 14 - 2014 и т.д.)

4. Вывести ФИО студентов заглавными буквами, отсортировать по фамилии.

5. Вывести список студентов, родившихся в 1997 году

6. Вывести список студентов, родившихся сегодня

Указания: введите текст запросов:

Запрос 1.

В MySQL для суммирования текстов используют функцию concat

select concat(fam,' ',left(imya,1),'. ',left(otch,1),'.') as fio

from stud

where pol='м'

Запрос 2.

Номер группы с пробелом занимает три символа, следовательно выделим из текста все символы, начиная с 4 и до конца текста. Будем использовать функцию substring.

Запрос похож на SQL Server, только вместо функции len будем использовать функцию char_length.

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

select distinct substring(grupa,4,char_length(grupa)-3) as nazv_grup

from stud

order by nazv_grup

Однако, если в MySQL в функции substring не указано количество выделяемых символов, то выделяется весь текст до конца. Следовательно этот же запрос можно записать короче:

select distinct substring(grupa,4) as nazv_grup

from stud

order by nazv_grup

Запрос 3.

Обрезку номера группы будем выполнять аналогично предыдущему (укороченному) запросу.

Для поиска вхождения года в названии группы будем использовать функцию locate, а для замены года будем использовать функцию replace. Всего в нашей таблице есть группы с годами: 13, 14, 15, 16.

select distinct case when locate('13' ,substring(grupa,4))>0

then replace(substring(grupa,4), '13', '2013')

when locate('14',substring(grupa,4))>0

then replace(substring(grupa,4), '14', '2014')

when locate('15',substring(grupa,4))>0

then replace(substring(grupa,4), '15', '2015')

when locate('16',substring(grupa,4))>0

then replace(substring(grupa,4), '16', '2016')

end as nazv_grup

from stud

order by nazv_grup

Запрос 4.

select upper(fam) as fam,

upper(imya) as imya,

upper(otch) as otch

from stud

order by fam

Запрос 5.

select *

from stud

where year(datar)=1997

order by fam

Запрос 6.

В MySQL текущая дата определяется функцией curdate:

select *

from stud

where day(datar)=day(curdate())

and month(datar)=month(curdate())

order by fam

Запрос 7.

Для построения запроса нужно получить диапазон дат: от текущей: curdate() до +7 дней: adddate(curdate(),7)

select *

from stud

where day(datar) between day(curdate())

and day(adddate(curdate(),7))

and month(datar) between month(curdate())

and month(adddate(curdate(),7))

order by fam

Над данными таблицы "stud" выполнить следующие действия:

1. Все фамилии записать заглавными буквами, а имена - маленькими.

2. Все фамилии и имена записать с большой буквы и удалить начальные и конечные пробелы

3. Всем студентам, у которых день рождения в текущем месяце, в поле "fam" к фамилии добавить знак "+".

Указания:

Запрос 1.

update stud

set fam=upper(fam),

imya=lower(imya)

Запрос 2.

В MySQL комбинацию функций ltrim(rtrim()) можно записать как trim(). В функции substring можно не указывать число выделяемых символов. Суммирование текстов записывается функцией concat()

update stud

set fam=concat(upper(left(ltrim(fam),1)),lower(substring(trim(fam),2))),

imya=concat(upper(left(ltrim(imyA),1)),lower(substring(trim(imya),2)))

Запрос 3.

В MySQL комбинацию функций ltrim(rtrim()) можно записать как trim(). Суммирование текстов записывается функцией concat(). Текущая дата определяется функцией curdate()

update stud

set fam=concat(trim(fam),'+')

where month(datar)=month(curdate())

Из таблицы "stud" выполните выбор следующих данных:

1. Отобрать фамилию и инициалы всех студентов мужского пола.

2. Вывести уникальный список всех групп без номеров.

4. Вывести ФИО студентов заглавными буквами, отсортировать по фамилии.

5. Вывести список студентов, родившихся в 1997 году

6. Вывести список студентов, родившихся сегодня

Указания: при работе в SQLite с русским текстом в кодировке UTF8 часто возникают проблемы с использованием строковых функций. Для их решения необходимо настроить параметры утилиты SQLite Expert. Выполните команду меню "Tools - Options". В окне настроек в левой части выберите строку "Data". В правой части внизу выберите второй переключатель "sqlite3.dll (version 3.8.7.1) supports FTS3, FTS4, RTREE, ICU". После этого все строковые функции будут корректно работать с русским текстом в кодировке UTF8.

Введите тексты запросов:

Запрос 1.

select fam || ' ' || substr(imya,1,1) || '. ' || substr(otch,1,1) || '.' as fio

from stud

where pol='м'

Запрос 2.

Номер группы с пробелом занимает три символа, следовательно выделим из текста все символы, начиная с 4 и до конца текста. Будем использовать функцию substr

select distinct substr(grupa,4,length(grupa)-3) as nazv_grup

from stud

order by nazv_grup

Запрос 3.

Обрезку номера группы будем выполнять аналогично предыдущему (укороченному) запросу.

Для поиска вхождения года в названии группы будем использовать функцию instr, а для замены года будем использовать функцию replace. Всего в нашей таблице есть группы с годами: 13, 14, 15, 16.

select distinct case when instr(substr(grupa,4),'13')>0

then replace(substr(grupa,4), '13', '2013')

when instr(substr(grupa,4),'14')>0

then replace(substr(grupa,4), '14', '2014')

when instr(substr(grupa,4),'15')>0

then replace(substr(grupa,4), '15', '2015')

when instr(substr(grupa,4),'16')>0

then replace(substr(grupa,4), '16', '2016')

end as nazv_grup

from stud

order by nazv_grup

Запрос 4.

СУБД SQLite не умеет работать с текстовыми функциями, если текст набран в кодировке UTF-8. Поэтому запрос выполнится, но содержимое полей не изменится.

select upper(fam) as fam,

upper(imya) as imya,

upper(otch) as otch

from stud

order by fam

Запрос 5.

В SQLite выделение частей из даты выполняется с помощью функции strftime. При этой каждая часть выделяется в текстовом формате.

select *

from stud

where strftime('%Y',datar)='1997'

order by fam

Запрос 6.

Для получения текущей даты используют текстовую константу 'now'

select *

from stud

where strftime('%d',datar)=strftime('%d','now')

and strftime('%m',datar)=strftime('%m','now')

order by fam

Запрос 7.

Для построения запроса нужно получить диапазон дат: от текущей: 'now' до +7 дней: date('now','+7 days')

select *

from stud

where strftime('%d',datar) between strftime('%d','now')

and strftime('%d',date('now','+7 days'))

and strftime('%m',datar) between strftime('%m','now')

and strftime('%m',date('now','+7 days'))

order by fam

Над данными таблицы "stud" выполнить следующие действия:

1. Все фамилии записать заглавными буквами, а имена - маленькими.

2. Все фамилии и имена записать с большой буквы и удалить начальные и конечные пробелы

3. Всем студентам, у которых день рождения в текущем месяце, в поле "fam" к фамилии добавить знак "+".

Указания:

Запрос 1.

update stud

set fam=upper(fam),

imya=lower(imya);

Запрос 2.

В SQLite длина строки определяется функцией length(), первый символ выделяется функцией substr(поле, 1, 1), часть строки выделяется функцией substr(), суммирование текстов записывается операцией ||

update stud

set fam = upper(substr(ltrim(fam),1,1))||lower(substr(ltrim(rtrim(fam)),2,length(ltrim(rtrim(fam)))-1)),

imya = upper(substr(ltrim(imya),1,1))||lower(substr(ltrim(rtrim(imya)),2,length(ltrim(rtrim(imya)))-1))

Запрос 3.

update stud

set fam=ltrim(rtrim(fam)) || '+'

where strftime('%m',datar)=strftime('%m','now')