бд. метод указ Проектирование БД. Методическое указания для выполнение лабораторных работ по дисциплине

• Название: Методическое указания для выполнение лабораторных работ по дисциплине
• Дата: 16.03.2022
• Размер: 0.74 Mb.
• Имя файла: метод указ Проектирование БД.docx
• Тип: Задача; #399109
• страница: 5 из 9

Замечание: в предложении ORDER BY нет ограничения по числу элементов.

 

При использовании предложения ORDER BY с инструкцией UNION сортируемые столбцы должны быть именами или псевдонимами столбцов, указанными в первой инструкции SELECT. Например, первая из следующих инструкций SELECT выполнится успешно, а во время выполнения второй произойдет сбой:

Create t1 (col1 int, col2 int);

Create t2 (col3 int, col4 int);

SELECT * from t1

UNION

SELECT * from t2

ORDER BY col1;

Данная инструкция успешно выполняется, поскольку имя «col1» принадлежит первой таблице (t1)

         SELECT * from t1

UNION

SELECT * from t2

ORDER BY col3;

Данная инструкция дает сбой, поскольку имя «col3» не принадлежит первой таблице (t1)

 

3.4  Операции в условиях для отбора данных

 

Transact-SQL позволяет строить сложные условия отбора записей и для этого использует операторы отношения, логические операторы и специальные операторы.

Операторы отношения:

        =  Равно

        >  Больше чем

        <  Меньше чем

        >= Больше или равно

        <= Меньше или равно

        <> Не равно

Они имеют стандартные значения для символьных значений и дат.

Символьные значения сравниваются в терминах их кодов.

 

Предположим, что нам надо увидеть всех студентов со стипендией выше 1200:

         SELECT *

             FROM Students

WHERE Stud_STIP > 1200

 

Логические операторы:

        AND логическое "И"

        OR    логическое "ИЛИ"

        NOT логическое отрицание

Оператор AND сравнивает два логических значения и возвращает TRUE (истина), если оба значения истинны (т.е. равны TRUE), в остальных случаях - FALSE (ложь). Оператор OR возвращает TRUE, если хотя бы один из аргументов равен TRUE. Оператор NOT возвращает TRUE, если его аргумент равен FALSE и наоборот.

Использование логических операторов значительно увеличивает возможности команды SELECT.

Логический оператор AND (логическое И) имеет смысл, если возвращаемые в результате выполнения запроса строки должны удовлетворять обоим условиям сравнения, заданным в предложении WHERE:

SELECT *

           FROM Students

   WHERE Stud_IMYA = 'Алексей'        AND

                   Stud_ADDRESS = 'Таугуль'

         Если в таблице имеется две строки, в которых значение в столбце Stud_IMA равно 'Алексей' (то есть имеются тезки), то логический оператор AND позволяет выбрать ту строку, в которой значение столбца Stud_ADDRESS равно «Таугуль» (то есть того Алексея, который живет в Таугуле).

C помощью оператора OR можно выбрать несколько значений из одного столбца:

   SELECT *

         FROM Students

            WHERE Stud_IMYA = 'Алексей'      OR

                            Stud_IMYA = 'Николай'

Оператор NOT указывается перед столбцом в операциях сравнения:

    SELECT *

         FROM Students

WHERE NOT Stud_ADDRESS = 'Таугуль'

Этот запрос позволяет считать из таблицы Students те строки, которые содержат информацию обо всех студентах, кроме тех, кто проживает в микрорайоне Таугуль.

Для группировки выражений Transact-SQL позволяет использовать круглые скобки ( ).

Например:

         SELECT *

               FROM Students

                  WHERE NOT (Stud_IMYA = 'Алексей'   OR

                                     Stud_IMYA = 'Николай')

3.5  Специальные операторы: IN, BETWEEN, LIKE, IS NULL

        

Оператор IN определяет список значений, в который должно входить значение поля. Например, если необходимо  найти всех  студентов с именем  'Алексей'  и 'Николай', можно использовать такой запрос:

  SELECT *

      FROM Students

          WHERE Stud_IMYA  IN ('Алексей' , 'Николай')

Набор значений для оператора IN заключается в круглые скобки, значения разделяются запятыми.

 

Оператор BETWEEN похож на оператор IN. В отличие от списка допустимых значений, BETWEEN определяет диапазон значений. В запросе необходимо указать слово BETWEEN, затем начальное значение, ключевое слово AND и конечное значение. Первое значение должно быть меньше второго. Следующий запрос выберет студентов  с оценками между  3 и 5:

    SELECT *

         FROM   Progress

             WHERE Ocenka BETWEEN 3 AND 5

 

Оператор LIKE применим только к символьным полям, с которыми он используется, чтобы находить подстроки. То есть, он ищет поле символа, чтобы видеть совпадает ли с условием часть его строки. В качестве условия он использует специальные символы:

          символ подчеркивания '_' замещает любой одиночный символ. Например, 'к_т' будет соответствовать 'кот' и 'кит', но не 'крот';

          знак процента % замещает последовательность любого числа символов. Например, '%м%р' будет соответствовать 'компьютер' и 'омар'.

 

Давайте выберем студентов, чьи имена начинаются с буквы 'О':        

         SELECT *

               FROM Students

                   WHERE Stud_IMYA  LIKE 'О%'

 

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

Часто необходимо различать строки, содержащие значения NULL в каком-либо столбце. Так как NULL указывает на отсутствие значения, для сравнненя с NULL существует специальный оператор - IS NULL. Выберем из нашей базы студентов с NULL значениями в столбцеStud_STIP:

         SELECT *

              FROM Students

                  WHERE Stud_STIP IS NULL

3.6  Обобщение данных с помощью агрегатных функций

 

Агрегатные функции берут группы значений и сводят их к одиночному значению. SQL Server предоставляет несколько агрегатных функций:

          COUNT производит подсчет строк, удовлетворяющих условию запроса;

          SUM  вычисляет арифметическую сумму всех значений колонки;

          AVG  вычисляет среднее арифметическое всех значений;

          MAX  определяет наибольшее из всех выбранных значений;

          MIN  определяет наименьшее из всех выбранных значений.

 

     Функции SUM и AVG применимы только к числовым полям. С функциями COUNT, MAX, MIN могут использоваться числовые или символьные поля. При применении к символьным полям MAX, MIN сравнивают значения в алфавитном порядке. Агрегатные функции при своей работе игнорируют значения NULL.

Чтобы найти среднюю стипендию  студентов, можно ввести запрос:

         SELECT  AVG (Stud_STIP)

    FROM Students

 

Функция COUNT несколько отличается от остальных. Она подсчитывает число значений в данной колонке или число строк в таблице. Например, подсчитаем количество студентов, сдавших учебные предметы:

         SELECT COUNT( DISTINCT Stud_ID)

               FROM Progress

               

Обратите внимание, что в приведенном выше примере используется ключевое слово DISTINCT - это означает что подсчитываться будет количество уникальных значений в колонке Stud_ID таблицы Progress. Если опустить его, результат изменится.

 

Чтобы подсчитать общее число строк в таблице, используйте функцию COUNT со звездочкой вместо имени поля:

         SELECT COUNT (*)

              FROM Students

 

Предложение GROUP BY позволяет задавать Вам подмножество значений, для которых применяется агрегатная функция. Это дает возможность объединять поля и агрегатные функции в одном предложении SELECT. Предположим, что надо найти номера студентов с минимальной оценкой из ведомости успеваемости:

         SELECT  Stud_ID,  MIN(ocenka )

            FROM Progress      

     GROUP BY Stud_ID

 

Чтобы названия столбцов были осмысленными, измените предыдущую инструкцию следующим образом (примените псевдонимы для столбцов):

         SELECT  Stud_ID,  MIN(ocenka) AS  Мин_оценка

            FROM Progress

               GROUP BY Stud_ID

 

GROUP BY применяет агрегатные функции к группам записей. Условие формирования группы - одинаковое значение поля (в данном случае Stud_ID).  GROUP BY можно использовать с несколькими полями. Усложним предыдущий запрос:

          SELECT Stud_ID, Pr_Date, MAX(ocenka )

            FROM Progress      

                GROUP BY Stud_ID, Pr_DATE 

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

В следующем примере возвращается информация о количестве студентов в группах.

 SELECT  Grup_ID,  COUNT(*) AS  'Количество'

   FROM  Students

      GROUP BY  Grup_ID

 

Ключевое слово HAVING используется в операторе SELECT вместе с ключевым словом GROUP BY, чтобы указать, какие из групп должны быть представлены в выводе.

Задает условие поиска, удовлетворяющее данную группу. Условие поиска может использовать статистические и нестатистические выражения. В нестатистических выражениях можно использовать только столбцы, отмеченные в предложении GROUP BY как столбцы группирования. Причина в том, что сгруппированные столбцы имеют общие для всей группы свойства. Аналогичным образом, статистические выражения имеют общее для всей группы свойство. Условие поиска предложения HAVING выражает предикат над свойствами группы.

Типы данных image и text нельзя использовать в предложении HAVING.

 Для GROUP BY ключевое слово HAVING играет ту же роль, что и WHERE для ORDER BY. Предположим, что мы хотим получить максимальную оценку  каждого студента, которая больше 4. Для достижения такого эффекта применяется предложение HAVING, которое определяет критерий, используемый для удаления групп из результата запроса, как это делает предложение WHERE для отдельных строк:                                        

SELECT Stud_ID, Pr_DATE, MAX (ocenka )

    FROM Progress                        

         GROUP BY Stud_ID, Pr_DATE  

         HAVING MAX (ocenka ) > 4

 

Агрегатные функции позволяют не просто выбирать определенные значения из базы, но и производить их обобщение и анализ.      

 

В следующем примере возвращается список всех групп,  в которых больше 20 студентов:

SELECT Grup_ID, COUNT(*) AS  'Количество'

    FROM Stud

        GROUP BY Grup_ID HAVING (COUNT(*))>20     

                  

3.7  Построение вычисляемых полей и выборка записей по дате

В общем случае для создания вычисляемого (производного) поля в списке SELECT следует указать некоторое выражение языка SQL. В этих выражениях применяются арифметические операции сложения, вычитания, умножения и деления, а также встроенные функции языка SQL. Можно указать имя любого столбца (поля) таблицы или запроса, но использовать имя столбца только той таблицы или запроса, которые указаны в списке предложения FROM соответствующей инструкции. При построении сложных выражений могут понадобиться скобки.

Стандарты SQL позволяют явным образом задавать имена столбцов результирующей таблицы, для чего применяется фраза AS.

Пример. Получить список студентов с указанием фамилии и инициалов студентов:

SELECT Grup_id, Stud_FAM+'  '+ Left(Stud_IMYA,1)+'.'+ Left(Stud_OTCH, 1)  +'.'  AS  'Ф.И.О'                          

  FROM Students

В запросе использована встроенная функция Left, позволяющая вырезать в текстовой переменной один символ слева в данном случае.

При обращении к базе данных для выборки каких-то записей по дате нужно написать символьную строку приблизительно такого вида:

         SELECT *

             FROM SomeTable

                 WHERE Time <= '02/29/2000'

 

Пример. Получить список студентов с указанием года и месяца рождения:

SELECT Stud_FAM, Year(Stud_DATE)   AS Год, Month (Stud_DATE) AS Месяц

                FROM Students

В запросе использованы встроенные функции Year и Month для выделения года и месяца из даты.

Запрос: выбрать для каждого преподавателя его два инициала с точкой и фамилию, например, W.J.SMITH.

SELECT left(Teach_IMYA,1) + '.' + ' '+ left(Teach_OTCH,1) + '.' + ' ' + Teach_FAM

         FROM Teacher

                                                                                                                

Контрольные вопросы                                                                                 

1.   Назовите обязательные составляющие оператора SELECT.             

2.   Для всех ли данных в выражении ключевого слова WHERE обязательно нужно использовать кавычки?

3.   К какому разделу SQL относится оператор SELECT?

4.   Можно ли в выражении для ключевого слова WHERE задать несколько условий?

5.   Допустимы ли кавычки для значений числовых полей?                            

6.   Играет ли роль тип данных при использовании функции COUNT.           

7.   Чтобы группировать данные по столбцу, должен ли этот столбец бытьуказан в списке ключевого слова SELECT?     

8.   При использовании ключевого слова ORDER BY в операторе SELECT обязательно ли использовать ключевое слово GROUP BY?                        

Лабораторная работа № 5 Создание сложных запросов

 

1  Цель работы: научить студентов создавать сложные  запросы к базе данных скриптом и в графической среде  СУБД MS SQL Server 2008.

 

2   Задание на лабораторную работу

 

Перед выполнением задания рекомендуется проработать все примеры, приведенные в п.п. 3.1 – 3.3.      

        

Получите следующую информацию из базы данных:                                           

1.        Список преподавателей, ведущих дисциплины «Информатика» и  «Физика».    

2.        Список студентов, имеющих неудовлетворительные оценки.                

3.        Список студентов, не сдавших экзамен по высшей математике.             

4.        Список преподавателей кафедры «Информатика».                                    

5.        Список кафедр с указанием фамилий заведующих кафедр.                     

6.        Список названий групп с указанием фамилий старост этих групп.         

7.        Списки студентов каждой группы с их оценками по всем предметам.           

8.        В каких группах проводятся занятия по предмету «Информатика»?      

9.        Какие виды занятий по Информатике проводятся в первой группе?      

10.   Сколько часов занятий по каждому предмету в каждой группе   проводится в семестре?

 

3  Методические указания к выполнению лабораторной работы

 

3.1 Объединение таблиц в запросах

                                                                                                                

Использование псевдонимов для имен таблиц. Псевдонимы назначаются таблицам с целью сокращения объема печатания, а также для использования при рекурсивном связывании таблиц.

Пример:

SELECT  A.Teach_ID, B.Chair_NAME

                         FROM Teacher  A

 INNER JOIN Chair B ON A.Chair_ID=B.Chair_ID                

         

Рекурсивное связывание удобно использовать, когда все необходимые данные размещаются в одной таблице, но требуется каким-то образом сравнить одни записи таблицы с другими. В нашей базе в таблице Students хранятся фамилии студентов и номера старост этих студентов. Получим список студентов с фамилиями их старост:

SELECT  A.Stud_FAM, B.Stud_FAM                                        

                         FROM Students A

LEFT JOIN Students B  ON A.Stud_Star = B.Stud_ID

                                                                          

          Связывание по нескольким ключам. Предположим вы хотите увидеть,  по каким видам работ и по каким предметам какие студенты какие получили оценки  (для этого требуется связать четыре таблицы):