Главная страница
qrcode

Руководство по SQLServer. Руководство по ms sql server


НазваниеРуководство по ms sql server
Дата25.10.2019
Размер2.86 Mb.
Формат файлаpdf
Имя файлаРуководство по SQLServer.pdf
оригинальный pdf просмотр
ТипРуководство
#38234
страница3 из 10
Каталог
1   2   3   4   5   6   7   8   9   10
DROP COLUMN название_столбца |
ALTER COLUMN название_столбца тип_данных_столбца [NULL|NOT NULL] |
ADD [CONSTRAINT] определение_ограничения |
DROP [CONSTRAINT] имя_ограничения}
Таким образом, с помощью ALTER TABLE мы можем провернуть самые различные сценарии изменения таблицы. Рассмотрим некоторые из них.
Добавление нового столбца
Добавим в таблицу Customers новый столбец Address:
ALTER TABLE Customers
ADD Address NVARCHAR(50) NULL;
В данном случае столбец Address имеет тип NVARCHAR и для него определен атрибут NULL. Но что если нам надо добавить столбец, который не должен принимать значения NULL? Если в таблице есть данные, то следующая команда не будет выполнена:
ALTER TABLE Customers
ADD Address NVARCHAR(50) NOT NULL;
Поэтому в данном случае решение состоит в установке значения по умолчанию через атрибут
DEFAULT:
ALTER TABLE Customers
ADD Address NVARCHAR(50) NOT NULL DEFAULT 'Неизвестно';
В этом случае, если в таблице уже есть данные, то для них для столбца Address будет добавлено значение "Неизвестно".
Удаление столбца
Удалим столбец Address из таблицы Customers:
ALTER TABLE Customers
DROP COLUMN Address;
MS SQL Server и T-SQL | Изменение таблицы
Изменение типа столбца
Изменим в таблице Customers тип данных у столбца FirstName на NVARCHAR(200):
ALTER TABLE Customers
ALTER COLUMN FirstName NVARCHAR(200);
Добавление ограничения CHECK
При добавлении ограничений SQL Server автоматически проверяет имеющиеся данные на соответствие добавляемым ограничениям. Если данные не соответствуют ограничениям, то такие ограничения не будут добавлены. Например, установим для столбца Age в таблице Customers ограничение Age > 21.
ALTER TABLE Customers
ADD CHECK (Age > 21);
Если в таблице есть строки, в которых в столбце Age есть значения, несоответствующие этому ограничению, то sql-команда завершится с ошибкой. Чтобы избежать подобной проверки на соответствие и все таки добавить ограничение, несмотря на наличие несоответствующих ему данных, используется выражение WITH NOCHECK:
ALTER TABLE Customers WITH NOCHECK
ADD CHECK (Age > 21);
По умолчанию используется значение WITH CHECK, которое проверяет на соответствие ограничениям.
Добавление внешнего ключа
Пусть изначально в базе данных будут добавлены две таблицы, никак не связанные:
CREATE TABLE Customers
(
Id INT PRIMARY KEY IDENTITY,
Age INT DEFAULT 18,
FirstName NVARCHAR(20) NOT NULL,
LastName NVARCHAR(20) NOT NULL,
Email VARCHAR(30) UNIQUE,
Phone VARCHAR(20) UNIQUE
);
CREATE TABLE Orders
(
Id INT IDENTITY,
CustomerId INT,
CreatedAt Date
);
Добавим ограничение внешнего ключа к столбцу CustomerId таблицы Orders:
ALTER TABLE Orders
ADD FOREIGN KEY(CustomerId) REFERENCES Customers(Id);
MS SQL Server и T-SQL | Изменение таблицы
Используя выше определенную таблицу Orders, добавим к ней первичный ключ для столбца Id:
ALTER TABLE Orders
ADD PRIMARY KEY (Id);
Добавление ограничений с именами
При добавлении ограничений мы можем указать для них имя, используя оператор CONSTRAINT,
после которого указывается имя ограничения:
ALTER TABLE Orders
ADD CONSTRAINT PK_Orders_Id PRIMARY KEY (Id),
CONSTRAINT FK_Orders_To_Customers FOREIGN KEY(CustomerId) REFERENCES Customers(Id);
ALTER TABLE Customers
ADD CONSTRAINT CK_Age_Greater_Than_Zero CHECK (Age > 0);
Удаление ограничений
Для удаления ограничений необходимо знать их имя. Если мы точно не знаем имя ограничения, то его можно узнать через SQL Server Management Studio:
Раскрыв узел таблиц в подузле Keys можно увидеть названия ограничений первичного и внешних ключей. Названия ограничений внешних ключей начинаются с "FK". А в подузле Constraints можно найти все ограничения CHECK и DEFAULT. Названия ограничений CHECK начинаются с "CK", а ограничений DEFAULT - с "DF".
Например, как видно на скриншоте в моем случае имя ограничения внешнего ключа в таблице
Orders называется "FK_Orders_To_Customers". Поэтому для удаления внешнего ключа я могу использовать следующее выражение:
Добавление первичного ключа
ALTER TABLE Orders
DROP FK_Orders_To_Customers;
MS SQL Server и T-SQL | Пакеты. Команда GO
Пакеты. Команда GO
В предыдущих случаях сначала создавалась база данных, а затем в эту БД добавлялась таблица с помощью отдельных команд SQL. Но можно сразу совместить в одном скрипте несколько команд. В
этом случае отдельные наборы команд называются пакетами (batch).
Каждый пакет состоит из одного или нескольких SQL-выражений, которые выполняются как оно целое. В качестве сигнала завершения пакета и выполнения его выражений служит команда GO.
Смысл разделения SQL-выражений на пакеты состоит в том, что одни выражения должны успешно выполниться до запуска других выражений. Например, при добавлении таблиц мы должны бы уверены, что была создана база данных, в которой мы собираемся создать таблицы.
Например, определим следующий скрипт:
CREATE DATABASE internetstore;
GO
USE internetstore;
CREATE TABLE Customers
(
Id INT PRIMARY KEY IDENTITY,
Age INT DEFAULT 18,
FirstName NVARCHAR(20) NOT NULL,
LastName NVARCHAR(20) NOT NULL,
Email VARCHAR(30) UNIQUE,
Phone VARCHAR(20) UNIQUE
);
CREATE TABLE Orders
(
Id INT PRIMARY KEY IDENTITY,
CustomerId INT,
CreatedAt DATE,
FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE CASCADE
);
Вначале создается бд internetstore. Затем идет команда GO, которая сигнализирует, что можно выполнять следующий пакет выражений. И далее выполняется второй пакет, который добавляет в нее две таблицы - Customers и Orders.
MS SQL Server и T-SQL | Добавление данных. Команда Insert
Основы T-SQL. DML
Добавление данных. Команда Insert
Для добавления данных применяется команда INSERT, которая имеет следующий формальный синтаксис:
INSERT [INTO] имя_таблицы [(список_столбцов)] VALUES (значение1, значение2, ... значениеN)
Вначале идет выражение INSERT INTO, затем в скобках можно указать список столбцов через запятую, в которые надо добавлять данные, и в конце после слова VALUES скобках перечисляют добавляемые для столбцов значения.
Например, пусть ранее была создана следующая база данных:
CREATE DATABASE productsdb;
GO
USE productsdb;
CREATE TABLE Products
(
Id INT IDENTITY PRIMARY KEY,
ProductName NVARCHAR(30) NOT NULL,
Manufacturer NVARCHAR(20) NOT NULL,
ProductCount INT DEFAULT 0,
Price MONEY NOT NULL
)
Добавим в нее одну строку с помощью команды INSERT:
INSERT Products VALUES ('iPhone 7', 'Apple', 5, 52000)
После удачного выполнения в SQL Server Management Studio в поле сообщений должно появиться сообщение "1 row(s) affected":
Стоит учитывать, что значения для столбцов в скобках после ключевого слова VALUES передаются по порядку их объявления. Например, в выражении CREATE TABLE выше можно увидеть, что первым
MS SQL Server и T-SQL | Добавление данных. Команда Insert столбцом идет Id. Но так как для него задан атрибут IDENTITY, то значение этого столбца автоматически генерируется, и его можно не указывать. Второй столбец представляет
ProductName, поэтому первое значение - строка "iPhone 7" будет передано именно этому столбцу.
Второе значение - строка "Apple" будет передана третьему столбцу Manufacturer и так далее. То есть значения передаются столбцам следующим образом:
ProductName: 'iPhone 7'
Manufacturer: 'Apple'
ProductCount: 5
Price: 52000
Также при вводе значений можно указать непосредственные столбцы, в которые будут добавляться значения:
INSERT INTO Products (ProductName, Price, Manufacturer)
VALUES ('iPhone 6S', 41000, 'Apple')
Здесь значение указывается только для трех столбцов. Причем теперь значения передаются в порядке следования столбцов:
ProductName: 'iPhone 6S'
Manufacturer: 'Apple'
Price: 41000
Для неуказанных столбцов (в данном случае ProductCount) будет добавляться значение по умолчанию, если задан атрибут DEFAULT, или значение NULL. При этом неуказанные столбцы должны допускать значение NULL или иметь атрибут DEFAULT.
Также мы можем добавить сразу несколько строк:
INSERT INTO Products
VALUES
('iPhone 6', 'Apple', 3, 36000),
('Galaxy S8', 'Samsung', 2, 46000),
('Galaxy S8 Plus', 'Samsung', 1, 56000)
В данном случае в таблицу будут добавлены три строки.
Также при добавлении мы можем указать, чтобы для столбца использовалось значение по умолчанию с помощью ключевого слова DEFAULT или значение NULL:
INSERT INTO Products (ProductName, Manufacturer, ProductCount, Price)
VALUES ('Mi6', 'Xiaomi', DEFAULT, 28000)
В данном случае для столбца ProductCount будет использовано значение по умолчанию (если оно установлено, если его нет - то NULL).
Если все столбцы имеют атрибут DEFAULT, определяющий значение по умолчанию, или допускают значение NULL, то можно для всех столбцов вставить значения по умолчанию:
INSERT INTO Products
DEFAULT VALUES
Но если брать таблицу Products, то подобная команда завершится с ошибкой, так как несколько полей не имеют атрибута DEFAULT и при этом не допускают значение NULL.
MS SQL Server и T-SQL | Выборка данных. Команда SELECT
Выборка данных. Команда SELECT
Для получения данных применяется команда SELECT. В упрощенном виде она имеет следующий синтаксис:
SELECT список_столбцов FROM имя_таблицы
Например, пусть ранее была создана таблица Products, и в нее добавлены некоторые начальные данные:
CREATE TABLE Products
(
Id INT IDENTITY PRIMARY KEY,
ProductName NVARCHAR(30) NOT NULL,
Manufacturer NVARCHAR(20) NOT NULL,
ProductCount INT DEFAULT 0,
Price MONEY NOT NULL
);
INSERT INTO Products
VALUES
('iPhone 6', 'Apple', 3, 36000),
('iPhone 6S', 'Apple', 2, 41000),
('iPhone 7', 'Apple', 5, 52000),
('Galaxy S8', 'Samsung', 2, 46000),
('Galaxy S8 Plus', 'Samsung', 1, 56000),
('Mi6', 'Xiaomi', 5, 28000),
('OnePlus 5', 'OnePlus', 6, 38000)
Получим все объекты из этой таблицы:
SELECT * FROM Products
Символ звездочка * указывает, что нам надо получить все столбцы.
MS SQL Server и T-SQL | Выборка данных. Команда SELECT
Получение всех столбцов с помощью символа звездочки * считается не очень хорошей практикой,
так как, как правило, не все столбцы бывают нужны. И более оптимальный подход заключается в указании всех необходимых столбцов после слова SELECT. Исключение составляет тот случай,
когда надо получить данные по абсолютно всем столбцам таблицы. Также использование символа
* может быть предпочтительно в таких ситуациях, когда в точности не известны названия столбцов.
Если нам надо получить данные не по всем, а по каким-то конкретным столбцам, то тогда все эти спецификации столбцов перечисляются через запятую после SELECT:
SELECT ProductName, Price FROM Products
Спецификация столбца необязательно должна представлять его название. Это может быть любое выражение, например, результат арифметической операции. Так, выполним следующий запрос:
SELECT ProductName + ' (' + Manufacturer + ')', Price, Price * ProductCount
FROM Products
Здесь при выборке будут создаваться три столбца. Первый столбец представляет результат объединения двух столбцов ProductName и Manufacturer. Второй столбец - стандартный столбец
Price. А третий столбец представляет значение столбца Price, умноженное на значение столбца
ProductCount.
MS SQL Server и T-SQL | Выборка данных. Команда SELECT
С помощью оператора AS можно изменить название выходного столбца или определить его псевдоним:
SELECT
ProductName + ' (' + Manufacturer + ')' AS ModelName,
Price,
Price * ProductCount AS TotalSum
FROM Products
В данном случае результатом выборки являются данные по 3-м столбцам. Первый столбец
ModelName объединяет столбцы ProductName и Manufacturere, второй представляет стандартный столбец Price. Третий столбец TotalSum хранит произведение столбцов ProductCount и Price. При этом, как в случае со столбцом Price, необязательно определять название результирующего столбца с помощью AS.
MS SQL Server и T-SQL | Выборка данных. Команда SELECT
DISTINCT
Оператор DISTINCT позволяет выбрать уникальные строки. Например, в нашем случае в таблице может быть по несколько товаров от одних и тех же производителей. Выберем всех производителей:
SELECT DISTINCT Manufacturer
FROM Products
В данном случае критерием разграничения строк является столбец Manufacturer. Поэтому в результирующей выборке будут только уникальные значения Manufacturer. И если, к примеру, в базе данных есть два товара с производителем Apple, то это название будет встречаться в результирующей выборке только один раз.
Выборка с добавлением
SELECT INTO
Выражение SELECT INTO позволяет выбрать из одной таблицы некоторые данные в другую таблицу,
при этом вторая таблица создается автоматически. Например:
SELECT ProductName + ' (' + Manufacturer + ')' AS ModelName, Price
INTO ProductSummary
FROM Products
SELECT * FROM ProductSummary
После выполнения этой команды в базе данных будет создана еще одна таблица ProductSummary,
которая будет иметь два столбца ModelName и Price, а данные для этих столбцов будут взяты из таблицы Products:
MS SQL Server и T-SQL | Выборка данных. Команда SELECT
При выполнении этой команды таблица, в которую идет выборка (в данном случае
ProductSummary), не должна существовать в базе данных.
Но, допустим, мы потом решили добавить все данные из таблицы Products в уже существующую таблицу ProductSummary. В этом случае можно опять же использовать команду INSERT:
INSERT INTO ProductSummary
SELECT ProductName + ' (' + Manufacturer + ')' AS ModelName, Price
FROM Products
Здесь добавляемые значения фактически представляют результат выборки из таблицы Products.
MS SQL Server и T-SQL | Сортировка. ORDER BY
Сортировка. ORDER BY
Оператор ORDER BY позволяет отсортировать извлекаемые значения по определенному столбцу:
SELECT *
FROM Products
ORDER BY ProductName
В данном случае строки сортируются по возрастанию значения столбца ProductName:
Сортировку также можно проводить по псевдониму столбца, который определяется с помощью оператора AS:
SELECT ProductName, ProductCount * Price AS TotalSum
FROM Products
ORDER BY TotalSum
MS SQL Server и T-SQL | Сортировка. ORDER BY
По умолчанию применяется сортировка по возрастанию. С помощью дополнительного оператора DESC можно задать сортировку по убыванию.
SELECT ProductName
FROM Products
ORDER BY ProductName DESC
По умолчанию вместо DESC используется оператор ASC:
SELECT ProductName
FROM Products
ORDER BY ProductName ASC
Если необходимо отсортировать сразу по нескольким столбцам, то все они перечисляются после оператора ORDER BY:
MS SQL Server и T-SQL | Сортировка. ORDER BY
SELECT ProductName, Price, Manufacturer
FROM Products
ORDER BY Manufacturer, ProductName
В этом случае сначала строки сортируются по столбцу Manufacturer по возрастанию. Затем если есть две строки, в которых столбец Manufacturer имеет одинаковое значение, то они сортируются по столбцу ProductName также по возрастанию. Но опять же с помощью ASC и DESC можно отдельно для разных столбцов определить сортировку по возрастанию и убыванию:
SELECT ProductName, Price, Manufacturer
FROM Products
ORDER BY Manufacturer ASC, ProductName DESC
В качестве критерия сортировки также можно использовать сложно выражение на основе столбцов:
SELECT ProductName, Price, ProductCount
FROM Products
ORDER BY ProductCount * Price
MS SQL Server и T-SQL | Извлечение диапазона строк
Извлечение диапазона строк
Оператор TOP
Оператор TOP позволяет выбрать определенное количество строк из таблицы:
SELECT TOP 4 ProductName
FROM Products
Дополнительный оператор PERCENT позволяет выбрать процентное количество строк из таблицы.
Например, выберем 75% строк:
SELECT TOP 75 PERCENT ProductName
FROM Products
OFFSET и FETCH
Оператор TOP позволяет извлечь определенное количество строк, начиная с начала таблицы. Для извлечения набора строк из любого места, применяются операторы OFFSET и FETCH. Важно, что эти операторы применяются только в отсортированном наборе данных после выражения ORDER BY.
ORDER BY выражение
OFFSET смещение_относительно_начала {ROW|ROWS}
[FETCH {FIRST|NEXT} количество_извлекаемых_строк {ROW|ROWS} ONLY]
Например, выберем все строки, начиная с третьей:
SELECT * FROM Products
ORDER BY Id
OFFSET 2 ROWS;
Число после ключевого слова OFFSET указывает, сколько строк необходимо пропустить.
MS SQL Server и T-SQL | Извлечение диапазона строк
Теперь выберем только три строки, начиная с третьей:
SELECT * FROM Products
ORDER BY Id
OFFSET 2 ROWS
FETCH NEXT 3 ROWS ONLY;
После оператора FETCH указывается ключевое слово FIRST или NEXT(какое именно в данном случае не имеет значения) и затем указывается количество строк, которое надо получить.
Данная комбинация операторов, как правило, используется для постраничной навигации, когда необходимо получить определенную страницу с данными.
MS SQL Server и T-SQL | Фильтрация. WHERE
Фильтрация. WHERE
Для фильтрации в команде SELECT применяется оператор WHERE. После этого оператора ставится условие, которому должна соответствовать строка:
WHERE условие
Если условие истинно, то строка попадает в результирующую выборку. В качестве можно использовать операции сравнения. Эти операции сравнивают два выражения. В T-SQL можно применять следующие операции сравнения:
=: сравнение на равенство (в отличие от си-подобных языков в T-SQL для сравнения на равенство используется один знак равно)
<>: сравнение на неравенство
<: меньше чем
>: больше чем
!<: не меньше чем
!>: не больше чем
<=: меньше чем или равно
>=: больше чем или равно
Например, найдем всех товары, производителем которых является компания Samsung:
SELECT * FROM Products
WHERE Manufacturer = 'Samsung'
1   2   3   4   5   6   7   8   9   10

перейти в каталог файлов


связь с админом