Ввод, удаление и изменение значений полей

ПРАКТИЧЕСКАЯ РАБОТА

Тема: Ввод, удаление и изменение значений полей. Подзапросы с командами модификации.

Цель:

· Ввод, удаление и изменение значений полей

· Использование подзапросов с командами модификации

Оборудование и/или программное обеспечение:IBM PC, MS Access /OpenOfficedBase.

Теоретическая часть

Ввод, удаление и изменение значений полей

Команды модификации языка dml

Значения могут быть помещены и удалены из полей, тремя командами языка DML (Язык Манипулирования Данными):

INSERT (ВСТАВИТЬ),

UPDATE (МОДИФИЦИРОВАТЬ),

DELETE (УДАЛИТЬ).

Не смущайтесь, все они упоминались ранее в SQL, как команды модификации.

Ввод значений

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

INSERT INTO <table name>
VALUES (<value>, <value>...);

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

INSERT INTO Salespeople
VALUES (1001, 'Peel', 'London',0.12);

Команды DML не производят никакого вывода, но ваша программа должна дать вам некоторое подтверждение того что данные были использованы. Имя таблицы (в нашем случае - Salespeople (Продавцы)), должно быть предварительно определено, в команде CREATE TABLE, а каждое значение пронумерованное в предложении значений, должно совпадать с типом данных столбца, в который оно вставляется. В ANSI, эти значения не могут составлять выражений, что означает что 3 - это доступно, а выражение 2 + 1 - нет. Значения, конечно же, вводятся в таблицу в поименном порядке, поэтому первое значение с именем, автоматически попадает в столбец 1, второе в столбец 2, на так далее.

Вставка пустых указателей (null)

Если вам нужно ввести пустое значение(NULL), вы вводите его точно также как и обычное значение. Предположим, что еще не имелось поля city для мистера Peel. Вы можете вставить его строку со значением=NULL в это поле, следующим образом:

INSERT INTO Salespeople
VALUES (1001, 'Peel', NULL,.12);

Так как значение NULL - это специальный маркер, а не просто символьное значение, он не включается в одиночные кавычки.

Именование столбца для вставки (insert)

Вы можете также указывать столбцы, куда вы хотите вставить значение имени. Это позволяет вам вставлять имена в любом порядке. Предположим что вы берете значения для таблицы Заказчиков из отчета выводимого на принтер, который помещает их в таком порядке: city, cname, и cnum, и для упрощения, вы хотите ввести значения в том же порядке:

INSERT INTO Customers (city, cnamе, cnum)
VALUES ('London', 'Honman', 2001);

Обратите внимание что столбцы rating и snum - отсутствуют. Это значит, что эти строки автоматически установлены в значение - по умолчанию. По умолчанию может быть введено или значение NULL или другое значение определяемое как - по умолчанию. Если ограничение запрещает использование значения NULL в данном столбце, и этот столбец не установлен как по умолчанию, этот столбец должен быть обеспечен значением для любой команды INSERT которая относится к таблице.

Вставка результатов запроса

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

INSERT INTO Londonstaff
SELECT *
FROM Salespeople
WHERE city = 'London';

Здесь выбираются все значения произведенные запросом - то-есть все строки из таблицы Продавцов со значениями city = "London" - и помещаются в таблицу называемую Londonstaff. Чтобы это работало, таблица Londonstaff должна отвечать следующим условиям:

· Она должна уже быть создана командой CREATE TABLE.

· Она должна иметь четыре столбца которые совпадают с таблицей Продавцов в терминах типа данных; то-есть первый, второй, и так далее, столбцы каждой таблицы, должны иметь одинаковый тип данных (причем они не должны иметь одинаковых имен).

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

Londonstaff - это теперь независимая таблица которая получила некоторые значения из таблицы Продавцов(Salespeople). Если значения в таблице Продавцов будут вдруг изменены, это никак не отразится на таблице Londonstaff (хотя вы могли бы создать такой эффект, с помощью Представления(VIEW)).

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

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

Понимая что таблица Порядков охватывает последний финансовый год, а не только несколько дней, как в нашем примере, вы можете видеть преимущество использования следующего условия INSERT в подсчете и вводе значений

INSERT INTO Daytotals (date, total)
SELECT odate, SUM (amt)
FROM Orders
GROUP BY odate;

Обратите внимание что, как предложено ранее, имена столбцов таблицы Порядков и таблицы Daytotals - не должны быть одинаковыми. Кроме того, если дата приобретения и общее количество - это единственные столбцы в таблице, и они находятся в данном порядке, их имена могут быть исключены из вывода из-за их очевидной простоты.

Удаление строк из таблиц

Вы можете удалять строки из таблицы командой модификации - DELETE. Она может удалять только введенные строки, а не индивидуальные значения полей, так что параметр поля является необязательным или недоступным. Чтобы удалить все содержание таблицы Продавцов, вы можете ввести следующее условие:

DELETE FROM Salespeople;

Теперь когда таблица пуста ее можно окончательно удалить командой DROP TABLE

Обычно, вам нужно удалить только некоторые определенные строки из таблицы. Чтобы определить какие строки будут удалены, вы используете предикат, так же как вы это делали для запросов. Например, чтобы удалить продавца Axelrod из таблицы, вы можете ввести

DELETE FROM Salespeople
WHERE snum = 1003;

Мы использовали поле snum вместо поля sname потому, что это лучшая тактика при использовании первичных ключей когда вы хотите чтобы действию подвергалась одна и только одна строка. Для вас - это аналогично действию первичного ключа.

Конечно, вы можете также использовать DELETE с предикатом который бы выбирал группу строк, как показано в этом примере:

DELETE FROM Salespeople
WHERE city = 'London';

Изменение значений поля

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

Это выполняется командой UPDATE. Эта команда содержит предложение UPDATE в которой указано имя используемой таблицы и предложение SET которое указывает на изменение которое нужно сделать для определенного столбца. Например, чтобы изменить оценки всех заказчиков на 200, вы можете ввести

UPDATE Customers
SET rating = 200;

Вывод

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

INSERT	используемой чтобы помещать строки в базу данных;
DELETE	чтобы удалять их;
REFERENCES	чтобы изменять значения в уже вставленных строках.

Вы обучались использованию предиката с командами UPDATE и DELETE чтобы определять, на которую из строк будет воздействовать команда. Конечно, предикаты как таковые - не значимы для INSERT, потому что обсуждаемая строка не существует в таблице до окончания выполнения команды INSERT. Однако, вы можете использовать запросы с INSERT, чтобы сразу помещать все наборы строк в таблицу. Причем это, вы можете делать со столбцами в любом порядке.

Вы узнали, что значения по умолчанию, могут помещаться в столбцы, если вы не устанавливаете это значение явно. Вы также видели использование стандартного значения по умолчанию, которым является NULL. Кроме того, вы поняли, что UPDATE может использовать выражение значения, тогда как INSERT не может.

Вывод

Теперь вы овладели тремя командами которые управляют всем содержанием вашей базы данных. Осталось только несколько общих вопросов относительно ввода и стирания значений таблицы, когда например эти команды могут выполниться данным пользователем в данной таблице и когда действия сделанные ими, становятся постоянными. Подведем итог: Вы используете команду INSERT чтобы добавлять строки в таблицу. Вы можете или дать имена значениям этих строк в предложении VALUES (когда только одна строка может быть добавлена), или вывести значения с помощью запроса (когда любое число строк можно добавить одной командой). Если используется запрос, он не может ссылаться к таблице в которую вы делаете вставку, каким бы способом Вы ее ни делали, ни в предложении FROM, ни с помощью внешней ссылки (как это делается в соотнесенных подзапросах). Все это относится к любым подзапросам внутри этого запроса.

Запрос, однако, оставляет вам свободу использования соотнесенных подзапросов или подзапросов которые дают в предложении FROM имя таблице, которое уже было указано в предложении FROM внешнего запроса (это - общий случай для запросов).

DELETE и UPDATE используются чтобы, соответственно удалить строки из таблицы и изменить в них значения. Оба они применимы ко всем строкам таблицы, если не используется предикат определяющий какие строки должны быть удалены или модифицированы. Этот предикат может содержать подзапросы, которые могут быть связаны с таблицей, удаляемой, или модифицированной, с помощью внешней ссылки. Эти подзапросы, однако, не могут ссылать к таблице модифицируемой любым предложением FROM. Может показаться, что мы прошли материал SQL который обладает не самым понятным логическим порядком. Сначала мы сделали запрос таблицы которая уже заполнена данными. Потом мы показали как можно фактически помещать эти значения изначально. Но, как вы видите, полное ознакомление с запросами здесь неоценимо. Теперь, когда мы показали вам как заполнять значениями таблицы которые уже были созданы (по определению), мы покажем откуда появились эти таблицы.

Создание таблиц

ВПЛОТЬ ДО ЭТОГО МЕСТА, МЫ ЗАПРАШИВАЛИ ТАБЛИЦЫ данных и выполняли команды по извлечению этих данных, считая, что эти таблицы уже были созданы кем - то до нас. Это действительно наиболее реальная ситуация, когда небольшое количество людей создают таблицы, которые затем используются другими людьми. Наша цель состоит в том, чтобы охватив информацию сначала более широко, перейти затем к более узким вопросам.

DDL (Язык Определения Данных), где создаются объекты данных SQL. Индекс. Индексы используются чтобы делать поиск более эффективным и, иногда, заставлять значения отличаться друга от друга. Они обычно работают незаметно для Вас, но если вы попробуете поместить значения в таблицу и они будут отклонены, из-за их не уникальности, это будет означать что другая строка имеет то же самое значение для этого поля, и что это поле имеет уникальный индекс или ограничение которое предписывает ему уникальность.

Команда создания таблицы

Таблицы создаются командой CREATE TABLE. Эта команда создает пустую таблицу - таблицу без строк. Значения вводятся с помощью DML команды INSERT. Команда CREATE TABLE в основном определяет имя таблицы, в виде описания набора имен столбцов указанных в определенном порядке. Она также определяет типы данных и размеры столбцов. Каждая таблица должна иметь по крайней мере один столбец.

Синтаксис команды CREATE TABLE:

CREATE TABLE <table-name >
(<column name > <data type>[(<size>)],
<column name > <data type> [(<size>)]...);

Типы данных значительно меняются от программы к программе. Для совместимости со стандартом, они должны все, по крайней мере, поддерживать стандарт типа ANSI. Он описан в Приложении B.

Так как пробелы используются для разделения частей команды SQL, они не могут быть частью имени таблицы (или любого другого объекта, такого как индекс). Подчеркивание (_) - обычно используется для разделения слов в именах таблиц.

Значение аргумента размера зависит от типа данных. Если вы его не указываете, ваша система сама будет назначать значение автоматически. Для числовых значений, это - лучший выход, потому что в этом случае, все ваши поля такого типа получат один и тот же размер что освобождает вас от проблем их общей совместимости. Кроме того, использование аргумента размера с некоторыми числовым наборами, не совсем простой вопрос. Если вам нужно хранить большие числа, вам несомненно понадобятся гарантии, что поля достаточно велики чтобы вместить их.

Один тип данных для которого вы, в основном, должны назначать размер - CHAR. Аргумент размера - это целое число которое определяет максимальное число символов которое может вместить поле. Фактически, число символов поля может быть от нуля (если поле - NULL) до этого числа. По умолчанию, аргумент размера = 1, что означает что поле может содержать только одну букву. Это конечно не совсем то что вы хотите.

Таблицы принадлежат пользователю который их создал, и имена всех таблиц принадлежащих данному пользователю должны отличаться друга от друга, как и имена всех столбцов внутри данной таблицы. Отдельные таблицы могут использовать одинаковые имена столбцов, даже если они принадлежат одному и тому же пользователю. Примером этому - столбец city в таблице Заказчиков и в таблице Продавцов. Пользователи не являющиеся владельцами таблиц могут ссылаться к этим таблицам с помощью имени владельца этих таблиц сопровождаемого точкой; например, таблица Employees созданная Smith будет называться Smith.Employees когда она упоминается каким-то другим пользователем (мы понимаем что Smith - это Идентифика тор Разрешения (ID). (ID) сообщаемый пользователем (ваш разрешенный ID - это ваше имя в SQL).

Эта команда будет создавать таблицу Продавцов:

CREATE TABLE Saleepeople
(snum integer,
sname char (10),
city char (10),
comm declmal);

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

Индексы

Индекс - это упорядоченный (буквенный или числовой) список столбцов или групп столбцов в таблице. Таблицы могут иметь большое количество строк, а, так как строки не находятся в каком-нибудь определенном порядке, на их поиск по указанному значению может потребовать время.

Индексный адрес - это и забота, и в то же время обеспечение способа объединения всех значений в группы из одной или больше строк, которые отличаются одна от другой. Но этот метод не существует в ранних версиях SQL. Так как уникальность часто необходима, индексы и использовались с этой целью.

Индексы - это средство SQL, которое родил сам рынок, а не ANSI. Поэтому, сам по себе стандарт ANSI в настоящее время не поддерживает индексы, хотя они очень полезны и широко применяемы.

Когда вы создаете индекс в поле, ваша база данных запоминает соответствующий порядок всех значений этого поля в области памяти. Предположим что наша таблица Заказчиков имеет тысячи входов, а вы хотите найти заказчика с номером=2999. Так как строки не упорядочены, ваша программа будет просматривать всю таблицу, строку за строкой, проверяя каждый раз значение поля cnum на равенство значению 2999. Однако, если бы имелся индекс в поле cnum, то программа могла бы выйти на номер 2999 прямо по индексу и дать информацию о том как найти правильную строку таблицы.

В то время как индекс значительно улучшает эффективность запросов, использование индекса несколько замедляет операции модификации DML(такие как INSERT и DELETE), а сам индекс занимает объем памяти. Следовательно, каждый раз когда вы создаете таблицу Вы должны принять решение, индексировать ее или нет. Индексы могут состоять из многочисленных полей. Если больше чем одно поле указывается для одного индекса, второе упорядочивается внутри первого, третье внутри второго, и так далее. Если вы имели первое и последнее имя в двух различных полях таблицы, вы могли бы создать индекс который бы упорядочил предыдущее поле внутри последующего. Это может быть выполнено независимо от способа упорядочивания столбцов в таблице.

Синтаксис для создания индекса - обычно следующий (помните, что это не ANSI стандарт):

CREATE INDEX <index name> ON <table name>
(<column name> [,<column name>]...);

Таблица, конечно, должна уже быть создана и должна содержать имя столбца. Имя индекса не может быть использовано для чего-то другого в базе данных (любым пользователем). Однажды созданный, индекс будет невидим пользователю. SQL сам решает когда он необходим чтобы ссылаться на него и делает это автоматически. Если, например, таблица Заказчиков будет наиболее часто упоминаемой в запросах продавцов к их собственной клиентуре, было бы правильно создать такой индекс в поле snum таблицы Заказчиков.

CREATE INDEX Clientgroup ON Customers (snum);

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

Уникальность индекса

Индексу в предыдущем примере, к счастью, не предписывается уникальность, несмотря на наше замечание, что это является одним из назначений индекса. Данный продавец может иметь любое число заказчиков. Однако, этого не случится если мы используем ключевое слово UNIQUE перед ключевым словом INDEX. Поле сnum, в качестве первичного ключа, станет первым кандидатом для уникального индекса:

CREATE UNIQUE INDEX Custid ON Customers (cnum);

ПРИМЕЧАНИЕ: эта команда будет отклонена если уже имеются идентичные значения в поле cnum. Лучший способ иметь дело с индексами состоит в том, чтобы создавать их сразу после того, как таблица создана и прежде, чем введены любые значения. Так же обратите внимание что, для уникального индекса более чем одного поля, это - комбинация значений, каждое из которых, может и не быть уникальным.

Предыдущий пример - косвенный способ заставить поле cnum работать как первичный ключ таблицы Заказчиков. Базы данных воздействуют на первичные и другие ключи более непосредственно.

Удаление индексов

Главным признаком индекса является его имя - поэтому он может быть удален. Обычно пользователи не знают о существовании индекса. SQL автоматически определяет позволено ли пользователю использовать индекс, и если да, то разрешает использовать его. Однако, если вы хотите удалить индекс, вы должны знать его имя. Этот синтаксис используется для удаления индекса:

DROP INDEX <Index name>;

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

Удаление таблиц

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

DROP TABLE < table name >;

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

Эта команда фактически не является частью стандарта ANSI, но она обще поддерживаема и полезна. К счастью, она более проста, и следовательно более непротиворечива, чем ALTER TABLE. ANSI просто не имеет способа для определения разрушенных или неправильных таблиц.

Практическая часть

1. Напишите команду которая бы поместила следующие значения, в их нижеуказанном порядке, в таблицу Продавцов:

city - San Jose,
name - Bianco,
comm - NULL,
cnum - 1100.

2. Напишите команду которая бы удалила все порядки заказчика Clemens из таблицы Порядков.

3. Напишите команду которая бы увеличила оценку всех заказчиков в Риме на 100.

4. Продавец Serres оставил компанию. Переназначьте его заказчиков продавцу Motika.

2. Предположите, что имеется таблица называемая Multicust, с такими же именами столбцов что и таблица Продавцов. Напишите команду, которая бы вставила всех продавцов (из таблицы Продавцов)имеющих более чем одного заказчика в эту таблицу.

3. Напишите команду которая бы удаляла всех заказчиков не имеющих текущих порядков.

4. Напишите команду которая бы увеличила на двадцать процентов комиссионные всех продавцов имеющих общие текущие порядки выше чем $3,000.

5. Напишите предложение CREATE TABLE которое бы вывело нашу таблицу Заказчиков.

6. Напишите команду которая бы давала возможность пользователю быстро извлекать порядки сгруппированные по датам из таблицы Порядков.

7. Если таблица Порядков уже создана, как Вы можете заставить поле onum быть уникальным (если допустить что все текущие значения уникальны)?

8. Создайте индекс который бы разрешал каждому продавцу быстро отыскивать его порядки сгруппированные по датам.

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

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

1. Что такое индексы?
2. Как создать таблицу?
3. Как использовать подзапрос созданный во внешней таблице запроса?
4. Как изменить значение поля?

Литература

1. Голицина О. Л. Базы данных: Учебное пособие. – М.: ФОРУМ: ИНФРА-М, 2005 (Гриф.)
2. Малыхина М.П. Базы данных: основы, проектирование, использование. – СПб.:БХВ-Петербург, 2004 (Гриф.)
3. Советов Б. Я.Базы данных: теория и практика. учебник. – М.: Высшая школа. 2005.(Гриф.)
4. Мэтью Мак-Дональд. Access 2007/. 2007 год. 767 стр.
5. В. Дунаев. Базы данных. Язык SQL. 2006 год. 279 стр.
6. Базы данных. Проектирование, реализация и сопровождение. Теория и практика. 3-е издание.: Пер. с англ. — М.: Издательский дом "Вильяме", 2003. — 1440 с.
7. А.В. Кузин, С.В Левонисова. Базы данных 2008 год. 316 стр.
8. Диго С.М. Базы данных проектирование и использование: Учебник. – М.: Финансы и статистика, 2005 (Гриф.)

ПРАКТИЧЕСКАЯ РАБОТА

Тема: Ввод, удаление и изменение значений полей. Подзапросы с командами модификации.

Цель:

· Ввод, удаление и изменение значений полей

· Использование подзапросов с командами модификации

Оборудование и/или программное обеспечение:IBM PC, MS Access /OpenOfficedBase.

Теоретическая часть

Ввод, удаление и изменение значений полей