Создание таблицы sql запрос. Основы T-SQL
87Параметр table_name - имя создаваемой базовой таблицы. Максимальное количество таблиц, которое может содержать одна база данных, ограничивается количеством объектов базы данных, число которых не может быть более 2 миллиардов, включая таблицы, представления, хранимые процедуры, триггеры и ограничения. В параметрах col_name1, col_name2, ... указываются имена столбцов таблицы, а в параметрах type1, type2, ... - типы данных соответствующих столбцов.
Имя объекта базы данных может обычно состоять из четырех частей, в форме:
]]object_name
Здесь object_name - это имя объекта базы данных, schema_name - имя схемы, к которой принадлежит объект, а server_name и db_name - имена сервера и базы данных, к которым принадлежит объект. Имена таблиц, сгруппированные с именем схемы, должны быть однозначными в рамках базы данных. Подобным образом имена столбцов должны быть однозначными в рамках таблицы.
Рассмотрим теперь ограничение, связанное с присутствием или отсутствием значений NULL в столбце. Если для столбца не указано, что значения NULL разрешены (NOT NULL), то данный столбец не может содержать значения NULL, и при попытке вставить такое значение система возвратит сообщение об ошибке.
Как уже упоминалось, объект базы данных (в данном случае таблица) всегда создается в схеме базы данных. Пользователь может создавать таблицы только в такой схеме, для которой у него есть полномочия на выполнение инструкции ALTER. Любой пользователь с ролью sysadmin, db_ddladmin или db_owner может создавать таблицы в любой схеме.
Создатель таблицы не обязательно должен быть ее владельцем. Это означает, что один пользователь может создавать таблицы, которые принадлежат другим пользователям. Подобным образом таблица, создаваемая с помощью инструкции CREATE TABLE, не обязательно должна принадлежать к текущей базе данных, если в префиксе имени таблицы указать другую (существующую) базу данных и имя схемы.
Схема, к которой принадлежит таблица, может иметь два возможных имени по умолчанию. Если таблица указывается без явного имени схемы, то система выполняет поиск имени таблицы в соответствующей схеме по умолчанию. Если имя объекта найти в схеме по умолчанию не удается, то система выполняет поиск в схеме dbo. Имена таблиц всегда следует указывать вместе с именем соответствующей схемы. Это позволит избежать возможных неопределенностей.
В примере ниже показано создание всех таблиц базы данных SampleDb. (База данных SampleDb должна быть установлена в качестве текущей базы данных.)
USE SampleDb; CREATE TABLE Department (Number CHAR (4) NOT NULL, DepartmentName NCHAR (40) NOT NULL, Location NCHAR (40) NULL); CREATE TABLE . ( CHAR (4) NOT NULL, NCHAR (15) NOT NULL, FLOAT (53) NULL); CREATE TABLE dbo.Employee (Id INT NOT NULL, FirstName NCHAR (20) NOT NULL, LastName NCHAR (20) NOT NULL, DepartamentNumber CHAR (4) NULL); CREATE TABLE dbo.Works_on (EmpId INT NOT NULL, ProjectNumber CHAR (4) NOT NULL, Job NCHAR (15) NULL, EnterDate DATE NULL);
Кроме типа данных и наличия значения NULL, в спецификации столбца можно указать следующие параметры:
предложение DEFAULT;
свойство IDENTITY.
Предложение DEFAULT в спецификации столбца указывает значение столбца по умолчанию, т.е. когда в таблицу вставляется новая строка, ячейка этого столбца будет содержать указанное значение, которое останется в ячейке, если в нее не будет введено другое значение. В качестве значения по умолчанию можно использовать константу, например одну из системных функций, таких как, USER, CURRENT_USER, SESSION_USER, SYSTEM_USER, CURRENT_TIMESTAMP и NULL.
Столбец идентификаторов, создаваемый указанием свойства IDENTITY , может иметь только целочисленные значения, которые системой присваиваются обычно неявно. Каждое следующее значение, вставляемое в такой столбец, вычисляется, увеличивая последнее, вставленное в этот столбец, значение. Поэтому определение столбца со свойством IDENTITY содержит (явно или неявно) начальное значение и шаг инкремента (такой столбец еще называют столбцом с автоинкрементом).
Ниже показан пример использования этих инструкций:
USE SampleDb; CREATE TABLE UserInfo (-- Для столбца Id будет использоваться автоинкремент IDENTITY(10,5), -- т.е. при вставке данных первому элементу будет присвоено -- значение 10, второму 15, третьему 20 и т.д. Id INT NOT NULL PRIMARY KEY IDENTITY (10,5), Login VARCHAR(40) NOT NULL, -- Для поля BirthDate будет указана дата по умолчанию -- (если это поле не задано явно при вставке данных) BirthDate DATETIME DEFAULT (-- По умолчанию -30 лет от текущей даты DATEADD(year, -30, GETDATE())))
Инструкция CREATE TABLE и ограничения декларативной целостности
Одной из самых важных особенностей, которую должна предоставлять СУБД, является способ обеспечения целостности данных. Ограничения, которые используются для проверки данных при их модификации или вставке, называются ограничениями для обеспечения целостности (integrity CONSTRAINTs) . Обеспечение целостности данных может осуществляться пользователем в прикладной программе или же системой управления базами данных. Наиболее важными преимуществами предоставления ограничений целостности системой управления базами данных являются следующие:
повышается надежность данных;
сокращается время на программирование;
упрощается техническое обслуживание.
Определение ограничений для обеспечения целостности посредством СУБД повышает надежность данных, поскольку устраняется возможность, что программист прикладного приложения может забыть реализовать их. Если ограничения целостности предоставляются прикладными программами, то все приложения, затрагиваемые этими ограничениями, должны содержать соответствующий код. Если код отсутствует хоть в одном приложении, то целостность данных будет поставлена под сомнение.
Если ограничения для обеспечения целостности не предоставляются системой управления базами данных, то их необходимо определить в каждой программе приложения, которая использует данные, включенные в это ограничение. В противоположность этому, если ограничения для обеспечения целостности предоставляются системой управления базами данных, то их требуется определить только один раз. Кроме этого, код для ограничений, предоставляемых приложениями, обычно более сложный, чем в случае таких же ограничений, предоставляемых СУБД.
Если ограничения для обеспечения целостности предоставляются СУБД, то в случае изменений ограничений, соответствующие изменения в коде необходимо реализовать только один раз - в системе управления базами данных. А если ограничения предоставляются приложениями, то модификацию для отражения изменений в ограничениях необходимо выполнить в каждом из этих приложений.
Системами управления базами данных предоставляются два типа ограничений для обеспечения целостности:
декларативные ограничения для обеспечения целостности;
процедурные ограничения для обеспечения целостности, реализуемые посредством триггеров.
Декларативные ограничения определяются с помощью инструкций языка DDL CREATE TABLE и ALTER TABLE. Эти ограничения могут быть уровня столбцов или уровня таблицы. Ограничения уровня столбцов определяются наряду с типом данных и другими свойствами столбца в объявлении столбца, тогда как ограничения уровня таблицы всегда определяются в конце инструкции CREATE TABLE или ALTER TABLE после определения всех столбцов.
Между ограничениями уровня столбцов и ограничениями уровня таблицы есть лишь одно различие: ограничения уровня столбцов можно применять только к одному столбцу, в то время как ограничения уровня таблицы могут охватывать больше, чем один столбец таблицы.
Каждому декларативному ограничению присваивается имя. Это имя может быть присвоено явно посредством использования опции CONSTRAINT в инструкции CREATE TABLE или ALTER TABLE. Если опция CONSTRAINT не указывается, то имя ограничению присваивается неявно компонентом Database Engine. Настоятельно рекомендуется использовать явные имена ограничений, поскольку это может значительно улучшить поиск этих ограничений.
Декларативные ограничения можно сгруппировать в следующие категории:
предложение DEFAULT;
предложение UNIQUE;
предложение PRIMARY KEY;
предложение CHECK;
ссылочная целостность и предложение FOREIGN KEY.
Использование предложения DEFAULT для определения ограничения по умолчанию было показано ранее. Все другие ограничения рассматриваются в последующих разделах.
Предложение UNIQUE
Иногда несколько столбцов или группа столбцов таблицы имеет уникальные значения, что позволяет использовать их в качестве первичного ключа. Столбцы или группы столбцов, которые можно использовать в качестве первичного ключа, называются потенциальными ключами (candidate key) . Каждый потенциальный ключ определяется, используя предложение UNIQUE в инструкции CREATE TABLE или ALTER TABLE. Синтаксис предложения UNIQUE следующий:
Опция CONSTRAINT в предложении UNIQUE присваивает явное имя потенциальному ключу. Опция CLUSTERED или NONCLUSTERED связана с тем обстоятельством, что компонент Database Engine создает индекс для каждого потенциального ключа таблицы. Этот индекс может быть кластеризованным, когда физический порядок строк определяется посредством индексированного порядка значений столбца. Если порядок строк не указывается, индекс является некластеризованным. По умолчанию применяется опция NONCLUSTERED. Параметр col_name1 обозначает имя столбца, который создает потенциальный ключ. (Потенциальный ключ может иметь до 16 столбцов.)
Применение предложения UNIQUE показано в примере ниже. (Прежде чем выполнять этот пример, в базе данных SampleDb нужно удалить таблицу Projects, используя для этого инструкцию DROP TABLE Projects.)
USE SampleDb; CREATE TABLE Projects (Number CHAR(4) DEFAULT "p1", ProjectName NCHAR (15) NOT NULL, Budget FLOAT (53) NULL, CONSTRAINT unique_number UNIQUE (Number));
Каждое значение столбца Number таблицы Projects является уникальным, включая значение NULL. (Точно так же, как и для любого другого значения с ограничением UNIQUE, если значения NULL разрешены для соответствующего столбца, этот столбец может содержать не более одной строки со значением NULL.) Попытка вставить в столбец Number уже имеющееся в нем значение будет неуспешной, т.к. система не примет его. Явное имя ограничения, определяемого в примере - unique_number.
Предложение PRIMARY KEY
Первичным ключом таблицы является столбец или группа столбцов, значения которого разные в каждой строке. Каждый первичный ключ определяется, используя предложение PRIMARY KEY в инструкции CREATE TABLE или ALTER TABLE. Синтаксис предложения PRIMARY KEY следующий:
Все параметры предложения PRIMARY KEY имеют такие же значения, как и соответствующие одноименные параметры предложения UNIQUE. Но в отличие от столбца UNIQUE, столбец PRIMARY KEY не разрешает значений NULL и имеет значение по умолчанию CLUSTERED.
В примере ниже показано объявление первичного ключа для таблицы Employee базы данных SampleDb. Прежде чем выполнять этот пример, в базе данных SampleDb нужно удалить таблицу Employee, используя для этого инструкцию DROP TABLE Employee.
USE SampleDb; CREATE TABLE Employee (Id INT NOT NULL, FirstName NCHAR (20) NOT NULL, LastName NCHAR (20) NOT NULL, DepartamentNumber CHAR (4) NULL, CONSTRAINT primary_id PRIMARY KEY (Id));
В результате выполнения этого кода снова создается таблица Employee, в которой определен первичный ключ. Первичный ключ таблицы определяется посредством декларативного ограничения для обеспечения целостности с именем primary_id. Это ограничение для обеспечения целостности является ограничением уровня таблицы, поскольку оно указывается после определения всех столбцов таблицы Employee.
Следующий пример эквивалентен предыдущему, за исключением того, что первичный ключ таблицы Employee определяется как ограничение уровня столбца.
USE SampleDb; DROP TABLE Employee; CREATE TABLE Employee (Id INT NOT NULL CONSTRAINT primary_id PRIMARY KEY, FirstName NCHAR (20) NOT NULL, LastName NCHAR (20) NOT NULL, DepartamentNumber CHAR (4) NULL);
В примере предложение PRIMARY KEY принадлежит к объявлению соответствующего столбца, наряду с объявлением его типа данных и свойства содержать значения NULL. По этой причине это ограничение называется ограничением на уровне столбца .
Предложение CHECK
Проверочное ограничение (CHECK CONSTRAINT) определяет условия для вставляемых в столбец данных. Каждая вставляемая в таблицу строка или каждое значение, которым обновляется значение столбца, должно отвечать этим условиям. Проверочные ограничения устанавливаются посредством предложения CHECK , определяемого в инструкции CREATE TABLE или ALTER TABLE. Синтаксис предложения CHECK следующий:
Параметр expression должен иметь логическое значение (true или false) и может ссылаться на любые столбцы в текущей таблице (или только на текущий столбец, если определен как ограничение уровня столбца), но не на другие таблицы. Предложение CHECK не применяется принудительно при репликации данных, если присутствует параметр NOT FOR REPLICATION. (При репликации база данных, или ее часть, хранится в нескольких местах. С помощью репликации можно повысить уровень доступности данных.)
В примере ниже показано применение предложения CHECK:
USE SampleDb; CREATE TABLE Customer (CustomerId INTEGER NOT NULL, CustomerRole VARCHAR(100) NULL, CHECK (CustomerRole IN ("admin", "moderator", "user")));
Создаваемая в примере таблица Customer включает столбец CustomerRole, содержащий соответствующее проверочное ограничение. При вставке нового значения, отличающегося от значений в наборе ("admin", "moderator", "user"), или при попытке изменения существующего значения на значение, отличающегося от этих значений, система управления базой данных возвращает сообщение об ошибке.
Предложение FOREIGN KEY
Внешний ключ (foreign key) - это столбец (или группа столбцов таблицы), содержащий значения, совпадающие со значениями первичного ключа в этой же или другой таблице. Внешний ключ определяется с помощью предложения FOREIGN KEY в комбинации с предложением REFERENCES . Синтаксис предложения FOREIGN KEY следующий:
[ ({col_name1} ,...)] REFERENCES table_name ({col_name2},...) Соглашения по синтаксису
Предложение FOREIGN KEY явно определяет все столбцы, входящие во внешний ключ. В предложении REFERENCES указывается имя таблицы, содержащей столбцы, создающие соответствующий первичный ключ. Количество столбцов и их тип данных в предложении FOREIGN KEY должны совпадать с количеством соответствующих столбцов и их типом данных в предложении REFERENCES (и, конечно же, они должны совпадать с количеством столбцов и типами данных в первичном ключе таблицы, на которую они ссылаются).
USE SampleDb; CREATE TABLE Works_on (EmpId INT NOT NULL, ProjectNumber CHAR (4) NOT NULL, Job NCHAR (15) NULL, EnterDate DATE NULL, CONSTRAINT primary_works PRIMARY KEY (EmpId, ProjectNumber), CONSTRAINT foreign_employee FOREIGN KEY (EmpId) REFERENCES Employee (Id), CONSTRAINT foreign_project FOREIGN KEY (ProjectNumber) REFERENCES Projects (Number));
Таблица Works_on в этом примере задается с тремя декларативными ограничениями для обеспечения целостности: primary_works, foreign_employee и foreign_project. Эти ограничения являются ограничением уровня таблицы, где первое указывает первичный ключ, а второе и третье - внешний ключ таблицы Works_on. Кроме этого, внешние ключи определяют таблицы Employee и Projects, как ссылочные таблицы, а их столбцы Id и Number, как соответствующий первичный ключ столбца с таким же именем в таблице Works_on.
Предложение FOREIGN KEY можно пропустить, если внешний ключ определяется, как ограничение уровня таблицы, поскольку столбец, к которому применяется ограничение, является неявным "списком" столбцов внешнего ключа, и ключевого слова REFERENCES достаточно для указания того, какого типа является это ограничение. Таблица может содержать самое большее 63 ограничения FOREIGN KEY.
Определение внешних ключей в таблицах базы данных налагает определение другого важного ограничения для обеспечения целостности: ссылочной целостности.
Ссылочная целостность (referential integrity) обеспечивает выполнение правил для вставок и обновлений таблиц, содержащих внешний ключ и соответствующее ограничение первичного ключа. Пример выше имеет два таких ограничения: foreign_employe и foreign_project. Предложение REFERENCES в примере определяет таблицы Employee и Projects в качестве ссылочных (родительских) таблиц.
Если для двух таблиц указана ссылочная целостность, модифицирование значений в первичном ключе и соответствующем внешнем ключе будет не всегда возможным. В последующих разделах рассматривается, когда это возможно, а когда нет.
Модификация значений внешнего или первичного ключа может создавать проблемы в четырех случаях. Все эти случаи будут продемонстрированы с использованием базы данных SampleDb. В первых двух случаях затрагиваются модификации ссылающейся таблицы, а в последних двух - родительской.
Возможные проблемы со ссылочной целостностью - случай 1
Вставка новой строки в таблицу Works_on с номером сотрудника 11111. Соответствующая инструкция Transact-SQL выглядит таким образом:
USE SampleDb; INSERT INTO Works_on VALUES (11111, "p1", "qwe", GETDATE())
При вставке новой строки в дочернюю таблицу Works_on используется новый номер сотрудника EmpId, для которого нет совпадающего сотрудника (и номера) в родительской таблице Employee. Если для обеих таблиц определена ссылочная целостность, как это сделано ранее, то компонент Database Engine не допустит вставки новой строки с таким номером EmpId.
Возможные проблемы со ссылочной целостностью - случай 2
Изменение номера сотрудника 9502 во всех строка таблицы Works_on на номер 11111. Соответствующая инструкция Transact-SQL выглядит таким образом:
В данном случае существующее значение внешнего ключа в ссылающейся таблице Works_on заменяется новым значением, для которого нет совпадающего значения в родительской таблице Employee. Если для обеих таблиц определена ссылочная целостность, то система управления базой данных не допустит модификацию строки с таким номером EmpId в таблице Works_on.
Возможные проблемы со ссылочной целостностью - случай 3
Замена значения 9502 номера сотрудника Id на значение 22222 в таблице Employee. Соответствующая инструкция Transact-SQL будет выглядеть таким образом:
В данном случае предпринимается попытка заменить существующее значение 9502 номера сотрудника Id значением 22222 только в родительской таблице Employee, не меняя соответствующие значения Id в ссылающейся таблице Works_on. Система не разрешает выполнения этой операции. Ссылочная целостность не допускает существования в ссылающейся таблице (таблице, для которой предложением FOREIGN KEY определен внешний ключ) таких значений, для которых в родительской таблице (таблице, для которой предложением PRIMARY KEY определен первичный ключ) не существует соответствующего значения. В противном случае такие строки в ссылающейся таблице были бы "сиротами". Если бы описанная выше модификация таблицы Employee была разрешена, тогда строки в таблице Works_on со значением Id равным 9502 были бы сиротами. Поэтому система и не разрешает выполнения такой модификации.
Возможные проблемы со ссылочной целостностью - случай 4
Удаление строки в таблице Employee со значением Id равным 9502.
Этот случай похожий на случай 3. В случае выполнения этой операции, из таблицы Employee была бы удалена строка со значением Id, для которого существуют совпадающие значения в ссылающейся (дочерней) таблице Works_on.
Опции ON DELETE и ON UPDATE
Компонент Database Engine на попытку удаления и модифицирования первичного ключа может реагировать по-разному. Если попытаться обновить значения внешнего ключа, то все эти обновления будут несогласованы с соответствующим первичным ключом, база данных откажется выполнять эти обновления и выведет сообщение об ошибке.
Но при попытке внести обновления в значения первичного ключа, вызывающие несогласованность в соответствующем внешнем ключе, система базы данных может реагировать достаточно гибко. В целом, существует четыре опции, определяющих то, как система базы данных может реагировать:
NO ACTIONМодифицируются (обновляются или удаляются) только те значения в родительской таблице, для которых нет соответствующих значений во внешнем ключе дочерней (ссылающейся) таблицы.
CASCADEРазрешается модификация (обновление или удаление) любых значений в родительской таблице. При обновлении значения первичного ключа в родительской таблице или при удалении всей строки, содержащей данное значение, в дочерней (ссылающейся) таблице обновляются (т.е. удаляются) все строки с соответствующими значениями внешнего ключа.
SET NULLРазрешается модификация (обновление или удаление) любых значений в родительской таблице. Если обновление значения в родительской таблице вызывает несогласованность в дочерней таблице, система базы данных присваивает внешнему ключу всех соответствующих строк в дочерней таблице значение NULL. То же самое происходит и в случае удаления строки в родительской таблице, вызывающего несогласованность в дочерней таблице. Таким образом, все несогласованности данных пропускаются.
SET DEFAULTАналогично опции SET NULL, но с одним исключением: всем внешним ключам, соответствующим модифицируемому первичному ключу, присваивается значение по умолчанию. Само собой разумеется, что после модификации первичный ключ родительской таблицы все равно должен содержать значение по умолчанию.
В языке Transact-SQL поддерживаются первые две из этих опций. Использование опций ON DELETE и ON UPDATE показано в примере ниже:
USE SampleDb; CREATE TABLE Works_on (EmpId INT NOT NULL, ProjectNumber CHAR (4) NOT NULL, Job NCHAR (15) NULL, EnterDate DATE NULL, CONSTRAINT primary_works PRIMARY KEY (EmpId, ProjectNumber), CONSTRAINT foreign_employee FOREIGN KEY (EmpId) REFERENCES Employee (Id) ON DELETE CASCADE, CONSTRAINT foreign_project FOREIGN KEY (ProjectNumber) REFERENCES Projects (Number) ON UPDATE CASCADE);
В этом примере создается таблица Works_on с использованием опций ON DELETE CASCADE и ON UPDATE CASCADE. Если таблицу Works_on загрузить значениями, каждое удаление строки в таблице Employee будет вызывать каскадное удаление всех строк в таблице Works_on, которые имеют значения внешнего ключа, соответствующие значениям первичного ключа строк, удаляемых в таблице Employee. Подобным образом каждое обновление значения столбца Number таблицы Project будет вызывать такое же обновление всех соответствующих значений столбца ProjectNumber таблицы Works_on.
Язык SQL используется не только для обработки информации, но и предназначена для выполнения всех операций с базами данных и таблицами, включая также создание таблиц и работа с ними. Существует два способа создания таблиц: 1) большинство СУБД обладают визуальным интерфейсом для интерактивного создания таблиц и управление ими; 2) таблицами можно манипулировать, используя операторы SQL. Стоит отметить, что, когда вы используете интерактивный инструментарий СУБД, на самом деле вся работа выполняется операторами SQL, т.е. интерфейс сам создает эти команды незаметно для пользователя (это подобно на запись макроса в Excel, когда макрорекодер записывает ваши действия и преобразует их в команды VBA).
1. Создание таблиц
Для создания таблиц программным способом используют оператор CREATE TABLE . Для этого нужно указать следующие данные:
- - имя таблицы, которое указывается после ключевого слова CREATE TABLE
- - имена и определения столбцов таблицы, отделены запятыми
- - в некоторых СУБД также требуется, чтобы было указано местоположение таблицы.
Давайте создадим новую таблицу и назовем ее Customers :
CREATE TABLE
Customers (
ID CHAR(10)
NOT NULL Primary key
,
Custom_name CHAR(25)
NOT NULL,
Custom_address CHAR(25)
NULL,
Custom_city CHAR(25)
NULL,
Custom_Country CHAR(25)
NULL,
ArcDate CHAR(25)
NOT NULL, DEFAULT
NOWO)
Так мы сначала указываем название новой таблицы, затем в скобках перечисляем столбцы, которие будем создавать, причем их названия не могут повторяться в пределах одной таблицы. После названий столбцов указывается тип данных для каждого поля (CHAR (10) ), затем отмечаем может ли поле содержать пустые значения (NULL или NOT NULL ), а также нужно указать поле, которое будет первичным ключом (Primary key ).
Язык SQL также позволяет определять для каждого поля значение по умолчанию, то есть, если пользователь не укажет значение определенного поля - оно будет автоматически проставлено СУБД. Значение по умолчанию определяется ключевым словом DEFAULT при определении столбцов оператором CREATE TABLE .
2. Обновление таблиц
Для того, чтобы изменить таблицу в SQL используется оператор ALTER TABLE . При использовании данного оператора необходимо ввести следующую информацию:
- - имя таблицы, которую мы хотим изменить
- - перечень изменений, которые мы хотим сделать.
Для примера давайте добавим новую колонку в таблицу Sellers , в которой будем указывать телефон реализатора:
ALTER TABLE Sellers ADD Phone CHAR (20)
Кроме добавления столбцов, мы можем их удалять. Давайте теперь удалим поле Phone . Для этого пропишем следующий запрос:
ALTER TABLE Sellers DROP COLUMN Phone
3. Удаление таблиц
Удаление таблиц осуществляется с помощью оператора DROP TABLE . Чтобы удалить таблицу Sellers_new , мы можем прописать следующий запрос:
DROP TABLE Sellers_new
Во многих СУБД применяются правила, предотвращающие удаление таблиц, которые являются уже связаны с другими таблицами. Если эти правила действуют и вы удаляете такую таблицу, то СУБД блокирует операцию удаления до тех пор, пока не будет удалена связь. Такие меры предотвращают случайное удаление нужных таблиц.
Последнее обновление: 09.07.2017
Создание базы данных
Для создания базы данных используется команда CREATE DATABASE .
Чтобы создать новую базу данных откроем SQL Server Management Studio. Нажмем на назначение сервера в окне Object Explorer и в появившемся меню выберем пункт New Query .
В центральное поле для ввода выражений sql введем следующий код:
CREATE DATABASE usersdb
Тем самым мы создаем базу данных, которая будет называться "usersdb":
Для выполнения команды нажмем на панели инструментов на кнопку Execute или на клавишу F5. И на сервере появится новая база данных.
После создания базы даных, мы можем установить ее в качестве текущей с помощью команды USE :
USE usersdb;
Прикрепление базы данных
Возможна ситуация, что у нас уже есть файл базы данных, который, к примеру, создан на другом компьютере. Файл базы данных представляет файл с расширением mdf, и этот файл в принципе мы можем переносить. Однако даже если мы скопируем его компьютер с установленным MS SQL Server, просто так скопированная база данных на сервере не появится. Для этого необходимо выполнить прикрепление базы данных к серверу. В этом случае применяется выражение:
CREATE DATABASE название_базы_данных ON PRIMARY(FILENAME="путь_к_файлу_mdf_на_локальном_компьютере") FOR ATTACH;
В качестве каталога для базы данных лучше использовать каталог, где хранятся остальные базы данных сервера. На Windows 10 по умолчанию это каталог C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA . Например, пусть в моем случае файл с данными называется userstoredb.mdf. И я хочу этот файл добавить на сервер как базу данных. Вначале его надо скопировать в выше указанный каталог. Затем для прикрепления базы к серверу надо использовать следующую команду:
CREATE DATABASE contactsdb ON PRIMARY(FILENAME="C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\userstoredb.mdf") FOR ATTACH;
После выполнения команды на сервере появится база данных contactsdb.
Удаление базы данных
Для удаления базы данных применяется команда DROP DATABASE , которая имеет следующий синтаксис:
DROP DATABASE database_name1 [, database_name2]...
После команды через запятую мы можем перечислить все удаляемые базы данных. Например, удаление базы данных contactsdb:
DROP DATABASE contactsdb
Стоит учитывать, что даже если удаляемая база данных была прикреплена, то все равно будут удалены все файлы базы данных.
Раздел 4 Информационные системы
Введение в SQL.
Создание, изменение и удаление таблиц.
Выборка данных из таблицы.
Создание SQL-запросов.
Обработка данных в SQL.
Методика обучения данной теме в школе.
Введение в SQL. SQL - структурированный язык запросов, который дает возможность создавать и работать в реляционных базах данных, которые являются наборами связанной информации сохраняемой в таблицах. Язык ориентирован на операции с данными, представленными в виде логически взаимосвязанных совокупностей таблиц-отношений. Важнейшая особенность структур этого языка состоит в ориентации на конечный рез-тат обработки данных, а не на процедуру этой обработки. SQL сам определяет, где находятся данные, индексы и даже какие наиболее эффективные последовательности операций следует использовать для получения рез-та.
Изначально, SQL был основным способом работы пользователя с базой данных и позволял выполнять следующий набор операций: создание в базе данных новой таблицы; добавление в таблицу новых записей; изменение записей; удаление записей; выборка записей из одной или нескольких таблиц (в соответствии с заданным условием); изменение структур таблиц.
Со временем SQL обеспечил возможность описания и управления новыми хранимыми объектами (например, индексы, представления, триггеры и хранимые процедуры). SQL остаётся единственным механизмом связи между прикладным программным обеспечением и базой данных. В то же время, современные СУБД, а, также, информационные системы, использующие СУБД, предоставляют пользователю развитые средства визуального построения запросов. Каждое предложение SQL - это либо запрос данных из базы, либо обращение к базе данных, которое приводит к изменению данных в базе.
В соответствии с тем, какие изменения происходят в базе данных, различают следующие типы запросов: на создание или изменение в базе данных новых или существующих объектов; на получение данных; на добавление новых данных (записей); на удаление данных; обращения к СУБД.
Основным объектом хранения реляционной базы данных является таблица, поэтому все SQL-запросы - это операции над таблицами. В соответствии с этим, запросы делятся на:
Запросы, оперирующие самими таблицами (создание и изменение таблиц);
Запросы, оперирующие с отдельными записями (или строками таблиц) или наборами записей.
Каждая таблица описывается в виде перечисления своих полей (столбцов таблицы) с указанием: типа хранимых в каждом поле значений; связей между таблицами (задание первичных и вторичных ключей); информации, необходимой для построения индексов.
Таким образом, использование SQL сводится, по сути, к формированию всевозможных выборок строк и совершению операций над всеми записями, входящими в набор.
Команды SQL разделяются на следующие группы:
1. Команды языка определения данных - DDL (Data Definition Language). Эти SQL команды можно использовать для создания, изменения и удаления различных объектов базы данных.
2. Команды языка управления данными - DCL (Data Control Language). С помощью этих SQL команд можно управлять доступом пользователей к базе данных и использовать конкретные данные (таблицы, представления и т.д.).
3. Команды языка управления транзакциями - TCL (Тгаnsасtiоn Соntrol Language). Эти SQL команды позволяют определить исход транзакции.
4. Команды языка манипулирования данными - DML (Data Manipulation Language). Эти SQL команды позволяют пользователю перемещать данные в базу данных и из нее.
Операторы SQL делятся на:
Операторы определения данных (Data Definition Language, DDL )
CREATE создает объект БД (саму базу, таблицу, представление, пользователя и т. д.)
ALTER изменяет объект
DROP удаляет объект
Операторы манипуляции данными (Data Manipulation Language, DML )
SELECT считывает данные, удовлетворяющие заданным условиям
INSERT добавляет новые данные
UPDATE изменяет существующие данные
DELETE удаляет данные
Операторы определения доступа к данным (Data Control Language, DCL )
GRANT предоставляет пользователю (группе) разрешения на определенные операции с объектом
REVOKE отзывает ранее выданные разрешения
DENY задает запрет, имеющий приоритет над разрешением
Операторы управления транзакциями (Transaction Control Language, TCL )
COMMIT применяет транзакцию.
ROLLBACK откатывает все изменения, сделанные в контексте текущей транзакции.
SAVEPOINT делит транзакцию на более мелкие участки.
Преимущества: 1.Независимость от конкретной СУБД (тексты SQL-запросов, содержащие DDL и DML, могут быть достаточно легко перенесены из одной СУБД в другую). 2. Наличие стандартов (наличие стандартов и набора тестов для выявления совместимости и соответствия конкретной реализации SQL общепринятому стандарту только способствует «стабилизации» языка). 3. Декларативность (с помощью SQL программист описывает только то, какие данные нужно извлечь или модифицировать)
Недостатки: 1.Несоответствие реляционной модели данных 2.Повторяющиеся строки 3. Неопределённые значения (nulls) 4. Явное указание порядка колонок слева направо 5. Колонки без имени и дублирующиеся имена колонок 6. Отсутствие поддержки свойства «=» 7. Использование указателей 8. Высокая избыточность
2.2 Создание, изменение и удаление таблиц.
Создание таблицы:
Таблицы создаются командой CREATE TABLE. Эта команда создает пустую таблицу - таблицу без строк. Значения вводятся с помощью DML команды INSERT. Команда CREATE TABLE в основном определяет им таблицы, в виде описания набора имен столбцов указанных в определенном порядке. Она также определяет типы данных и размеры столбцов. Каждая таблица должна иметь по крайней мере один столбец.
Синтаксис команды:
CREATE TABLE
(
Изменение таблицы:
Команда ALTER TABLE – это содержательна форма, хотя ее возможности несколько ограничены. Она используется чтобы изменить определение существующей таблицы. Обычно, она добавляет столбцы к таблице. Иногда она может удалять столбцы или изменять их размеры, а также в некоторых программах добавлять или удалять ограничения. Типичный синтаксис чтобы добавить столбец к таблице:
ALTER TABLE
