Лекция №2. Тема: Создание таблиц и работа с таблицами

Тема: Создание таблиц и работа с таблицами.

Описание учетной базы

Таблица 1 STUDENTS

SNUM	SFAM	SIMA	SOTCH	STIP
	Поляков	Анатолий	Алексеевич	25,50

Таблица 2 PREDMET

PNUM	PNAME	TNUM	HOURS	COURS
код предмета	физика	код преподавателя

Таблица 3 TEACHERS

TNUM	TFAM	TIMA	TOTCH	TDATE
	Вакулина	Валентина	Ивановна	01/04/1984

Таблица 4 USP

UNUM	OCENKA	UDATE	SNUM	PNUM
код факта сдачи учебного предмета		10/06/1999	код студенческого билета	код предмета

1. Создание таблицы STUDENTS:

CREATE TABLE STUDENTS

(SNUM INT,

SFAM CHAR(20),

SIMA CHAR(10),

SOTCH CHAR(15),

STIP DECIMAL)

2. Добавление столбца в таблицу:

ALTER TABLE STUDENTS

ADD COURS INT,

SPEC CHAR(10)

Команда ALTER TABLE не действует, когда таблица должна быть переопределена, а также когда таблица используется другим пользователем, это может привести к потере данных.

3. Удаление таблицы (пустой):

DROP TABLE имя_таблицы

DROP TABLE STUDENTS

4. Добавление информации в таблицу:

INSERT INTO имя_таблицы

VALUES (<value>, <value>…)

INSERT INTO, TEACHERS

VALUES (4006, ‘Федченко’, ‘Светлана’, ‘Анатольевна’, 01/09/1999)

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

INSERT INTO TEACHERS

VALUES (NULL, ‘Федченко’, …)

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

INSERT INTO TEACHERS (TDATE, TEAM, TIMA)

VALUES (01/09/1999, ‘Федченко’, ‘Светлана’)

7. Использование команды INSERT для того, чтобы получать или выбирать значения из одной таблицы в другую вместе с запросом.

INSERT INTO EXCELENT

SELECT *

FROM USP

WHERE OCENKA=5

Таблица EXCELENT должна быть предварительно создана и иметь одинаковое количество столбцов, совпадающих по типам данных с исходной. При изменении данных в таблице USP изменений в таблице EXCELENT не произойдет.

8. Для вставки в уже имеющуюся таблицу STO, ФИО студентов, у которых есть хотя бы одна отличная оценка используется команда:

INSERT INTO STO (SFAM, SIMA, SOTCH)

SELECT SFAM, SIMA, SOTCH

FROM STUDENTS

WHERE SNUM=ANY

(SELECT SNUM FROM USP WHERE OCENKA = 5)

Удаление данных

1. Удаление всех записей таблицы:

DELETE FROM STUDENTS

2. Удаление строк, соответствующих условию:

DELETE FROM STUDENTS

WHERE SNUM=3416

При нахождении нескольких одинаковых значений в поле SNUM удаляются все записи.

1. Использование вложенных подзапросов при удалении:

DELETE FROM STUDENTS

WHERE SNUM =

(SELECT SNUM

FROM USP

WHERE OCENKA = 3)

Удаление записей из таблицы STUDENTS, у которых имеются тройки по любому предмету в таблице USP.

Изменение данных

1. Изменение оценки на “5” у всех студентов

UPDATE USP

SET OCENKA = 5

2. Изменение по условию

UPDATE USP

SET OCENKA = 5

WHERE PNUM = 2003

Предложение SET позволяет использовать любое число столбцов, определяемых занятыми.

3. Использование выражений при изменении

UPDATE STUDENTS

SET STIP = STIP * 2

WHERE STIP = 25.20

4. Команда UPDATE может работать с NULL значениями

UPDATE USP

SET OCENKA = NULL

WHERE PNUM = 2003

5. Использование операторов EXISTS, IN и т.д. при замене

UPDATE STUDENTS

SET STIP = STIP * 2

WHERE 2<=

(SELECT COUNT (SNUM)

FROM USP

WHERE STUDENTS.SNUM = USP.SNUM)

Увеличивает размер стипендии в 2 раза студентам, у которых имеются оценки, хотя бы по 2–м учебным предметам.

6. Невозможно одной командой выполнить модификацию оценок для студентов, у которых оценки ниже средней. Для этого выполняются 2 команды.

SELECT AVG(OCENKA)

FROM USP => результат 4.2

а затем этот результат используется для

изменения UPDATE USP

SET OCENKA = OCENKA – 1

WHERE OCENKA < 4.2

Выборка данных из таблиц

1. Вывод всех столбцов и записей из таблицы

SELECT * FROM имя_таблицы

SELECT * FROM STUDENTS

2. Вывод определенных стоблцов

SELECT SFAM, SNUM, STIP FROM STUDENTS

Выводить столбцы можно в любой последовательности.

3. Удаление дубликатов записей при выводе

SELECT DISTINCT SNUM FROM USP

DISTINCT может указываться только один раз в предложении SELECT. Если предложение выбирает несколько полей, то DISTINCT опускает записи, где все выбранные поля идентичны.

4. Использование условий для поиска

SELECT SFAM, STIP FROM STUDENTS

WHERE STIP = 25.50

В запросах можно использовать знаки арифметических операций >, <, >=, <=, <>.

5. SELECT * FROM STUDENTS

WHERE STIP > 0 – Вывод студентов, у которых стипендия > 0.

6. Использование в запросах булевых операторов AND, OR и NOT.

6a. SELECT *

FROM USP

WHERE OCENKA >= 3 AND PNUM = 2003

Выводит список студентов, сдавших предмет с кодом 2003

6б. SELECT *

FROM USP

WHERE OCENKA >= 3 OR PNUM 2003

Выводит список студентов, у которых оценка 3 и более или сдавших предмет с кодом 2003

6в. SELECT *

FROM USP

WHERE NOT (OCENKA = 3)

Выводит список студентов, у которых оценка не 3.

6.2. Булевский оператор помещается перед реляционным оператором, а при необходимости расширения используются скобки

SELECT *

FROM USP

WHERE NOT (OCENKA = 3) AND NOT (PNUM = 2005)

Выводится информация о студентах, у которых оценки не являются 3 и по предмету, с кодом не равным 2005.

6.3. Булевые операторы могут использоваться и для символьных данных

SELECT *

FROM STUDENTS

WHERE SIMA = ’Анатолий’ OR SIMA = ‘Владимир’

Выводит информацию о студентах, у которых имена Анатолий или Владимир.

7. Использование оператора IN

SELECT *

FROM STUDENTS

WHERE SIMA IN (‘Анатолий’,’Владимир’)

SELECT *

FROM STUDENTS

WHERE STIP IN (17.00, 25.50)

Выводится информация о студентах, у которых стипендия 17.00, 25.50. Оператор IN проверяет различные значения указанного поля, пытаясь найти совпадение со значениями из набора.

8. Оператор BETWEEN определяет диапазон значений, в который должны умещаться искомые значения

SELECT SNUM, OCENKA

FROM USP

WHERE OCENKA BETWEEN 3 AND 5

Выводит номера и оценки студентов, оценки которых заключены между 3 и 5. Граничные значения диапазона также выводятся на экране. Для их вывода их на экран можно использовать запрос:

SELECT SNUM, OCENKA

FROM USP WHERE (OCENKA BETWEEN 3 AND 5)

AND NOT OCENKA IN (3,5).

Выводятся только студенты, у которых оценки «4».

BETWEEN может работать с символьными полями:

SELECT SFAM, SIMA, SOTCH

FROM STUDENTS

WHERE SFAM BETWEEN ‘K’ AND ‘C’

Выводит список студентов, чьи фамилии попали в диапазон от К до С.

9. Оператор LIKE применим к полям типа CHAR или VARCHAR. В качестве условия оператор использует групповые символы.

Существует два типа групповых символов, используемых с LIKE:

– символ подчеркивает замещает любой одиночный символ, например ‘М_Л’ будет соответствует словам ‘МОЛ’, ‘МЕЛ’;

– знак % замещает последовательность любого числа символов, в том числе нулевой длины. Например, ‘%М%Л’ соответствует словам ‘МЕЛ’ или ‘ПОМОЛ’, не соответствует ‘МОЛОКО’.

SELECT TEAM, TIMA, TOTCH

FROM TEACHERS

WHERE TEAM LIKE ‘K%’

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

SELECT TEAM, TIMA, TOTCH

FROM TEACHERS

WHERE TEAM LIKE ‘К_занко%’

можно использовать, если неизвестно как правильно имеется фамилия Казанко.

10. Нахождение нулевых и ненулевых значений.

SELECT *

FROM USP

WHERE OCENKA IS NULL

SELECT *

FROM USP

WHERE OCENKA IS NOT NULL

1)–й запрос выводит записи, у которых нулевые оценки.

2)–й не нулевые.

2­­–й запрос может быть записан по-другому

SELECT *

FROM USP

WHERE NOT OCENKA IS NULL

Допускается использовать NOT c IN

SELECT *

FROM USP

WHERE SNUM NOT IN (3412, 3413)

или

SELECT *

FROM USP

WHERE NOT SNUM IN (3412, 3413) –

выводят данные о студентах, номера студенческих билетов которых не 3412 и не 3413.

Добавление вычисляемых и не вычисляемых полей при выводе на экран

SELECT SFAM, SIMA, SOTCH, STIP*2

FROM STUDENTS

добавляет еще один столбец в таблицу. При выводе он будет без названия, т.к. имя столбца – это атрибут таблицы, а столбец создавался не в таблице.

Можно размещать текст в выборе запроса

SELECT SFAM, SIMA, SOTCH, ‘у.е.’, STIP*2

FROM STUDENTS

Упорядочивание строк в таблице

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

SELECT * FROM STUDENTS

ORDER BY SFAM ASC

Выводит список студентов в алфавитном порядке

SELECT * FROM STUDENTS

ORDER BY STIP DESC, SFAM ASC

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

132.50 Иванов

132.50 Петров

132.50 Яблов

100.00 Ивкин

100.00

90.00

Дата добавления: 2014-01-20