SQL (Structured Query Language) - this is a universal computer language that is used to create, modify and modify data in relational databases danikh (mova structured requests).

SQL in this way looks like informational-logical my, but not my programming, but at the same time SQL conveys the possibility of its procedural extensions, with the improvement of such language as a whole, it can be seen as a language of programming.

In this hour, the following SQL specifications have been expanded:

Data base and SQL specification

Database Type	SQL specification
Microsoft SQL	Transact SQL
Microsoft Jet/Access	JetSQL
MySQL	SQL/PSM (SQL/Persistent Stored Module)
Oracle	PL/SQL (Procedural Language/SQL)
IBM DB2	SQL PL (SQL Procedural Language)
InterBase/Firebird	PSQL (Procedural SQL)

This article will look at the Transact-SQL specification, as it's won by Microsoft SQL Servers. Oskіlki base in usіh specifіkatsіy SQL is the same, more commands and scenarios are easily transferred to other types of SQL.

Appointment

Transact-SQL is Microsoft's SQL procedural extension. SQL letter extensions like this adventitious abilities yak:

• key operators,
• local and global changes,
• different additional functions for the processing of rows, dates, mathematics, etc.,
• Microsoft Windows Authentication Support

Mova Transact-SQL is the key to the wiki SQL Server. All programs that interact with an instance of SQL Server, regardless of their implementation and core interface, run Transact-SQL instructions on the server.

Data base confirmed

In order to master the theoretical material, yoga, obviously, needs to be put into practice. For the practical, we can create a data base that is memorable for a small amount of value.

Also, in order to create a database and store it with її values, it is necessary to open the console for executing commands and downloading the SQL server and logging the attack script:

Database Creation USE master CREATE DATABASE TestDatabase GO -- Table Creation USE TestDatabase CREATE TABLE Users (UserID int PRIMARY KEY, UserName nvarchar(40), UserSurname nvarchar(40), DepartmentID int, PositionID int) CREATE , DepartmentName nvarchar(40)) CREATE TABLE Positions (PositionID int PRIMARY KEY, PositionName nvarchar(40), BaseSalary money) CREATE TABLE (CustomerID int PRIMARY KEY, CustomerName nvarchar(40), CustomerAddress n PRIMARY KEY, CustomerID int, UserID int, text) GO -- Fill table USE TestDatabase INSERT Users VALUES (1, "Ivan", "Petrov", 1, 1) INSERT Users VALUES (2, "Ivan", "Sidorov", 1, 2) INSERT Users VALUES (3, "Petr", "Ivanov ", 1, 2) INSERT Users VALUES (4, "Nikolay", "Petrov", 1, 3) INSERT Users VALUES (5, "Nikolay", "Ivanov", 2, 1) INSERT Users VALUES (6, "Sergey ", "Sidorov", 2, 3) INSERT Users VALUES (7, "Andrey", "Bukin", 2, 2) INSERT Users VALUES (8, "Viktor", "Rybakov", 4, 1) INSERT Departments VALUES ( 1, "Production") INSERT Departments VALUES (2, "Distribution") INSERT Departments VALUES (3, "Purchasing") ", 1000) INSERT Positions VALUES (2, "Senior analyst", 650) INSERT VALUES (1, "Alex Company", "606443, Russia, Bor, Lenina str., 15") INSERT VALUES (2, "Potrovka", "115516, Moscow, Promyshlennaya str., 1") INSERT VALUES (1, 1, 1, "Special parts ") GO

Note. For Microsoft SQL Server 2000, it is linked to Query Analyzer. For Microsoft SQL Server 2005, check in SQL Server Management Studio.

As a result of the script on the SQL server, the TestDatabase database will be created with five coristuvach tables: Users, Departments, Positions, Local Customers, Local Orders.

Users

UserID	username	UserSurname	DepartmentID	PositionID
1	Ivan	Petrov	1	1
2	Ivan	Sidorov	1	2
3	Peter	Ivanov	1	3
4	Nicholas	Petrov	1	3
5	Nicholas	Ivanov	2	1
6	Sergey	Sidorov	2	3
7	Andrey	Bukin	2	3
8	Victor	Rybakov	4	1

Positions

PositionID	PositionName	BaseSalary
1	manager	1000
2	senior analyst	650
3	Analyst	400

Local orders

OrderID	CustomerID	UserID	Description
1	1	1	Special parts

Departments

DepartmentID	DepartmentName
1	Production
2	distribution
3	Purchasing

Local Customers

CustomerID	CustomerName	CustomerAddress
1	Alex Company	606443, Russia, Bor, Lenina str., 15
2	Potrovka	115516, Moscow, Promyshlennaya str., 1

Syntax Elements

Script directives

Script directives - these are specific commands, which are more common in MS SQL. Qi commands help the server to determine the rules of work with a script and transactions. Typical representatives: GO - signals the SQL server about the end of the script, EXEC (or EXECUTE) - iconizes a procedure or a scalar function.

Comments

Comments are drawn for the creation of explanations for script blocks, as well as for time-limiting of commands when the script is adjusted. Comments are both row-like and block-like:

• -- - a row commentary includes more than one row, in front of which there are two minuses.
• /* */ - block commentary excludes the whole block of commands, instructions for the specified construction.

tipi danih

Like in language programming, learn SQL different types data to save the change:

• Numbers - for saving numerical values ​​(int, tinyint, smallint, bigint, numeric, decimal, money, smallmoney, float, real).
• Date - to save the date of that hour (datetime, smalldatetime).
• Characters for saving character data (char, nchar, varchar, nvarchar).
• Dviykovі - for saving binary data (binary, varbinary, bit).
• More volumes - types of data for saving great binary data (text, ntext, image).
• Special - indicators (cursor), 16-byte hexadecimal number, as a win for GUID (uniqueidentifier), row change stamp (timestamp), row version (rowversion), tables (table).

Note. For the selection of Russian symbols (not ASCII encoding) types of data are written with the prefix "n" (nchar, nvarchar, ntext), as if they encode characters with two bytes. Otherwise, for Unicode work, data types from "n" are used.

Note. For the data of the change of life, types of data from the prefix "var" are typed. Types of data without the prefix "var" may be fixed in the memory area, some of them may be filled with spaces or zeros.

Identifiers

Identifier - ce special characters, yakі vykoristovuyutsya zі zmіnnimi for іdentifіkatsіїїїї їkh type аbo grupuvannya slіv u zminnu. Types of identifiers:

• @ - identifier of the local change (koristuvach).
• @@ - Identifier of the global change (introduced).
• # - identifier of the local table of the procedure.
• ## - Identifier of the global table of the procedure.
• - Identifier of the grouping of the words in the change.

Changes

Changes are made in scripts for the sake of saving Timchas's tribute. Sob pratsyuvati zі zminnoyu, її it is necessary to denounce, before the voicing can be zdіysnen in the transaction, in the same way the team is victorious, like the victorious tsyu zminna. Otherwise, it seems that after the transaction is completed, then after GO, it changes.

Changed settings can be changed with the DECLARE command, changed settings can be set either with the SET command or with the SELECT command:

USE TestDatabase -- Anonymous changes . Seeing changed @EmpName as a result of a SELECT @EmpName AS GO query

Note. For each butt, there is a grouping of words at a change - the design is accepted as one change, because the words are placed at the square bow.

Operators

Operators - tse special commands, are used to perform simple operations on changes:

• Arithmetic operators: "*" - multiply, "/" - divide, "%" - modulus subdivide, "+" - add, "-" - add, "()" - arcs.
• Matching operators: "=" - one, ">" - more, "<" - меньше, ">=" - more than one, "<=" меньше или равно, "<>"- not good.
• Ordering operators: "+" - closing rows.
• Logical operators: "AND" - one, "OR" - either, "NOT" - not.

System functions

The Transact-SQl specification significantly extends the standard SQL capabilities to include the following functions:

• Aggregate functions-functions that work with collections have the same value. Typical representatives: AVG - average value of the column, SUM - sum of the column, MAX - maximum value of the column, COUNT - number of elements in the column.
• Scalar functions are functions that rotate one value, working with scalar data, or instead of entering without input data. Typical representatives: DATEDIFF – difference between dates, ABS – modulus of number, DB_NAME – data base name, USER_NAME – flow line recorder name, LEFT – part of the data row.
• Functions-specifiers-functions, like vikoristovuyutsya as if forced on other data. Typical representatives: OPENXML - indicator for the data tree for looking at XML structures, OPENQUERY - indicator for the data tree for looking at another request.

Note. The full list of functions can be found in the SQL Server Connections.

Note. Before scalar functions, you can add global changes, which in the text of the script are called by the underdog "@@".

USE TestDatabase -- Wiktor aggregation function for baby average salary SELECT AVG (BaseSalary) AS FROM Positions GO -- Wiktor scalar function for retrieving data base name (30) SET @MyUser = USER_NAME() SELECT "The current user""s database username is: "+ @MyUser GO -- Variant caller function to retrieve data from another server SELECT * FROM OPENQUERY(OracleSvr, "SELECT name, id FROM owner.titles") GO

Virazi

Viraz is a combination of symbols and operators, as it takes a scalar value on the input, and on the output it gives a different value, or else it takes a scalar value. Transact-SQL has 3 types: DDL, DCL and DML.

• DDL (Data Definition Language) - hacks for the creation of objects in the database. The main representatives of this class are: CREATE - creation of objects, ALTER - change of objects, DROP - removal of objects.
• DCL (Data Control Language) - recognition of recognition of rights to database objects. The main representatives of this class: GRANT - allowed to the object, DENY - fenced to the object, REVOKE - allowed that fence to the object.
• DML - Data Manipulation Language The main representatives of this class are: SELECT - selection of data, INSERT - insertion of data, UPDATE - change of data, DELETE - view of data.

USE TestDatabase -- DDL tag CREATE TABLE TempUsers (UserID int, UserName nvarchar(40), DepartmentID int) GO -- DCL tag GRANT SELECT ON Users TO public GO -- DML tag SELECT UserID, UserName + " " + UserSurname AS F GO -- DDL DROP TABLE TempUsers GO wiki

Script control

Transact-SQL has special commands that allow you to hack through the script in a stream, looping through it directly with the logic you need.

• A grouping block is a structure that combines viruses into one logical block (BEGIN ... END).
• Mind block - a structure, as a way of perverting the vision of the singing mind (IF ... ELSE).
• Loop block - a structure that organizes the repetition of a logical block (WHILE ... BREAK ... CONTINUE).
• Transition - a command to switch the script flow to the specified label (GOTO).
• Zatrimka - a team that zatrimka vikonannya scenario (WAITFOR)
• Pardon Weekly – a command that generates a pardon for a script (RAISERROR)

Dynamic design of viruses

Then, having understood the basics of Transact-SQL and having practiced on simple applications, you can move on to folding structures. Call the data bases and create them back up for additional scenarios (scripts) - if the visual editor is simple for animals, but if you don’t create a large data base without nedolikiv, you won’t save it. As soon as you guess the cob of the stats, then the last data base was created and filled up for an additional scenario. Scenario - only one or more viruses, combined in a logical block, to automate the administrator's work.

Sound scenarios are written as a universal zasib for standard scripts, so they contain dynamically designed logic - ask and insert commands to change, and not specific names of objects, which allows you to quickly change the parameters of the script.

USE master -- Set dynamic data DECLARE @dbname varchar(30), @tablename varchar(30), @column varchar(30) SET @dbname = "TestDatabase" SET @tablename = "Positions" SET @column = "BaseSalary" - - Variant of dynamic data EXECUTE ("USE" + @dbname + "SELECT AVG(" + @column + ") AS FROM " + @tablename) GO

Collection of data

In SQL moves, the selection of data from the table is specified after the help of the SELECT command:

SELECT<названия колонок или *>FROM<название таблицы>

Behind the lock, the SELECT command has the ALL parameter, which can be omitted. If you specify the DISTINCT parameter in the command, then the result will be consumed only unique (non-repeatable) records from the selection.

To change the names of objects in commands to the SQL server, the AS command is changed. Vicoristannya tsієї commands help to speed up the long time of the row, and so take the result from the handy-eyed.

Select all records from the Local Customers table SELECT * FROM -- Select unique records of the UserName column from the Users table SELECT DISTINCT UserName FROM Users

username
Andrey
Ivan
Nicholas
Peter
Sergey
Victor

The filtering of data is carried out after the help of the WHERE command, in which case the next operators and that command are equal: =,<, >, <=, >=, <>, LIKE, NOT LIKE, AND, OR, NOT, BETWEEN, NOT BETWEEN, IN, NOT IN, NULL, IS NOT NULL. In a wild look, the SELECT command from the filter looks like this:

SELECT<названия колонок или *>FROM<название таблицы>WHERE<условие>

In a row of alignment, it is allowed to vicorate symbols and substitutions:

• % - be it a number of symbols;
• _ – one character;
• - whether there is a symbol, indications at the temples;
• [^] - be a symbol, not in the arms.

-- Select all records from the Users table where DepartmentID = 1 SELECT * FROM Users WHERE DepartmentID = 1

UserID	username	UserSurname	DepartmentID	PositionID
1	Ivan	Petrov	1	1
2	Ivan	Sidorov	1	2
3	Peter	Ivanov	1	2
4	Nicholas	Petrov	1	3

-- Select all records from the Users table that have the letter A in their name SELECT * FROM Users WHERE UserName LIKE "%a%"

UserID	username	UserSurname	DepartmentID	PositionID
1	Ivan	Petrov	1	1
2	Ivan	Sidorov	1	2
4	Nicholas	Petrov	1	3
5	Nicholas	Ivanov	2	1
7	Andrey	Bukin	2	2

-- Select all records from the Users table that have a non-V letter in their friend's name SELECT * FROM Users WHERE UserName LIKE "_[^v]%"

Filtering allows you to select a drink, so that you can design a drink from a number of drinks:

Select the records of the PositionID column from the Positions table, de BaseSalary< 600 SELECT PositionID FROM Positions WHERE BaseSalary < 600 -- Выбрать все записи из таблицы Users, у кого имя Ivan или Andrey SELECT * FROM Users WHERE UserName IN ("Ivan", "Andrey")

To sort the data in the selection, the ORDER BY command is used, and then check that this command does not sort data of type text, ntext and image. By default, sorting is carried out according to the size, so the ASC parameter can be omitted for this type:

SELECT<названия колонок или *>FROM<название таблицы>WHERE<условие>ORDER BY<названия колонок>

In order to increase the number of rows in the result, the TOP command wins:

SELECT TOP [number of rows]<названия колонок или *>FROM<название таблицы>WHERE<условие>ORDER BY<названия колонок>

In the middle of the inquiry, it is possible to carry out the calculation of the taken away tribute. For which aggregation functions are used:

• AVG(column) – average value of the column;
• COUNT(column) – number of non-NULL elements in the column;
• COUNT(*) – number of items in the request;
• MAX(column) – maximum value in the column;
• MIN(column) – minimum value in the column;
• SUM(column) – the sum of the values ​​for the column.

Apply a variety of ORDER, TOP commands and aggregation functions:

Select the first 3 unique records of the UserName column from the Users table, sorted by UserName SELECT DISTINCT TOP 3 UserName FROM Users salary in organization SELECT * FROM Users WHERE PositionID IN (SELECT PositionID FROM Positions WHERE BaseSalary IN (SELECT MAX(BaseSalary) FROM Positions) )

Grouping data

SQL allows you to group data behind the same fields in a table. To group data by some parameter, for SQL query it is necessary to write the GROUP BY command, for which column name, for which grouping is to be carried out. The columns specified in the GROUP BY command are due to be present in the SELECT command, and the SELECT command is also responsible for replacing the aggregation function, as it will be stuck before grouping data.

Find the number of practitioners in the skin group (groups of practitioners by - ID of the species and sort the number of records in the skin group) SELECT DepartmentID, COUNT(UserID) AS "Number of users"

DepartmentID	Number of users
1	4
2	3
4	1

In order to filter the rows at the request for groups, a special HAVING team will be put in place, in order to specify the mind filter. The columns behind which filtering is applied are due to be present in the GROUP BY team. The HAVING command can be used even without GROUP BY, in which case it won't work in the same way as before the WHERE command, but it also allows you to block only the aggregation function in the minds of filtering.

Find the number of practitioners in the first group (groups of practitioners by - identifier of the identities, analyze the number of records in the skin group - enter into the result less than the number of identifiers with the identifier equal 1) SELECTBY DepartmentID, COUNT "users AV DepartmentID, COUNT" ING DepartmentID= 1 -- Find out the number of practitioners with the song planting at the skin vіddіlі -- (group the practitioners by the іdіdіkіka posad i vіddіlіv ta — sort out the number of records in the skin group) ist practitioners SELECT DepartmentID, PositionID, COUNT(UserID) AS "Number of users" FROM Users GROUP BY DepartmentID, PositionID WITH ROLLUP

DepartmentID	PositionID	Number of users
1	1	1
1	2	2
1	3	1
1	NULL	4
2	1	1
2	2	1
2	3	1
2	NULL	3
4	1	1
4	NULL	1
NULL	NULL	8

The grouping command can also be supplemented with the WITH CUBE operator, which will further form all combinations of grouped columns: if there are N columns, then there will be 2^N combinations.

To know the number of practitioners with a song planting in the skin vіddіlі - (groups of practitioners by the іdіdіfіkatorі posad i vіddіlіv і - sort out the number of records in the skin group) number of practitioners SELECT DepartmentID, PositionID , COUNT(UserID) AS "Number of users" FROM Users GROUP BY DepartmentID, PositionID WITH CUBE

DepartmentID	PositionID	Number of users
1	1	1
1	2	2
1	3	1
1	NULL	4
2	1	1
2	2	1
2	3	1
2	NULL	3
4	1	1
4	NULL	1
NULL	NULL	8
NULL	1	3
NULL	2	3
NULL	3	2

The GROUPING aggregation function allows you to determine if a record is added by the ROLLUP and CUBE commands, or if a record is removed from the data box.

Find the number of practitioners in the skin group (groups of practitioners by - identifier of the variables and determine the number of records in the skin group) - and also determine the additional rows that are not found in the data gerel SELECTUUPERING (UsersID of DepartmentID, COUNT(userID of DepartmentID), COUNT(userID of DepartmentID, COUNT(userID of DepartmentID) DepartmentID) AS "Added row" FROM Users GROUP BY DepartmentID WITH ROLLUP

DepartmentID	Number of users	added row
1	4	0
2	3	0
4	1	0
NULL	8	1

Another grouping command, COMPUTE, allows you to group data and display it in different tables. That is, the GROUP BY command with the ROLLUP and CUBE operators adds group data to the table of additional rows with a star, and the COMPUTE command for group data, expanding the table into a row of subtables, and also forms a subtable with stars. The COMPUTE command can be overridden in two modes:

• how simple is the aggregation function to display the result around the table;
• with the BY parameter as a grouping command that expands the table into a sprat subtable

The COMPUTE command with the BY parameter can be more or less combined with the ORDER BY command, and the sorting columns are the same as the grouping columns.

Enter a table of company accounts and check their number SELECT * FROM Users COMPUTE COUNT(UserID)

UserID	username	UserSurname	DepartmentID	PositionID
1	Ivan	Petrov	1	1
2	Ivan	Sidorov	1	2
3	Peter	Ivanov	1	2
4	Nicholas	Petrov	1	3
5	Nicholas	Ivanov	2	1
6	Sergey	Sidorov	2	3
7	Andrey	Bukin	2	2
8	Victor	Rybakov	4	1

cnt
8

-- Find the number of practitioners in a skin group (Group practitioners by -- ID of the species and sort out the number of records in a skin group) SELECT * FROM Users

UserID	username	UserSurname	DepartmentID	PositionID
1	Ivan	Petrov	1	1
2	Ivan	Sidorov	1	2
3	Peter	Ivanov	1	2
4	Nicholas	Petrov	1	3

cnt
4

UserID	username	UserSurname	DepartmentID	PositionID
5	Nicholas	Ivanov	2	1
6	Sergey	Sidorov	2	3
7	Andrey	Bukin	2	2

cnt
3

UserID	username	UserSurname	DepartmentID	PositionID
8	Victor	Rybakov	4	1

cnt
1

Z'ednannya table

The most important and necessary queries in SQL - ce queries for the tables, if the selection is necessary at the rate of the number of dzherel. So, ask for folding in writing, but also for writing, shards often see the result already prepared in the program, which is no longer enough to display on the screen.

You can link tables in SQL in two ways: vertically and horizontally.

Vertically created by the UNION command, as for example, the first table, add another table. With such a combination, the number of columns of the table, which are combined, can be the same, and the columns themselves have the same names and types of data. If you add the same rows that appear in both tables, you will see that the ALL parameter is not specified in the command.

Know all correspondents named Ivan and return the result -- the result of the query "Find all correspondents named Petrov" -- duplicate entries enable SELECT * FROM Users WHERE UserName = "Ivan"

UserID	username	UserSurname	DepartmentID	PositionID
1	Ivan	Petrov	1	1
2	Ivan	Sidorov	1	2
4	Nicholas	Petrov	1	3

-- Find all correspondents named Ivan and return result from -- result from query "Find all correspondents named Petrov" -- duplicate records save SELECT * FROM Users WHERE UserName = "Ivan" UNION ALL SELECT * FROM Users WHERE UserSurname = " Petrov"

UserID	username	UserSurname	DepartmentID	PositionID
1	Ivan	Petrov	1	1
2	Ivan	Sidorov	1	2
1	Ivan	Petrov	1	1
4	Nicholas	Petrov	1	3

Horizontally z'ednannya zdіysnyuєtsya shlyah zcheplennya kіlkoh tables behind the key columns. The simplest horizontal join is followed by the help of the INNER JOIN command, like zchіplyuє tables, choosing the rows of the key field, like zustrіchaєtsya in both tables.

SELECT<названия колонок или *>FROM<таблица_1>INNER JOIN table_2 ON table_1.key_field = table_2.key_field

In order to select the join by all fields of the left table, independently, as well as the records in the right table, it is necessary to select the LEFT JOIN command. This command returns the bottom table, selecting all rows from the left table, and the data in the right table, which are daily, are filled with NULL values.

SELECT<названия колонок или *>FROM<таблица_1>LEFT JOIN table_2 ON table_1.key_field = table_2.key_field

The RIGHT JOIN command is similar to the front, the only difference is that it is in the bottom table, selecting all rows from the right table, and the daily data of the left table is filled with NULL values.

SELECT<названия колонок или *>FROM<таблица_1>RIGHT JOIN table_2 ON table_1.key_field = table_2.key_field

The FULL JOIN command has its own right to join, so it won't join tables, select rows from both tables, and return daily data to NULL values.

SELECT<названия колонок или *>FROM<таблица_1>FULL JOIN table_2 ON table_1.key_field = table_2.key_field

The rest of the table is rarely beaten by the CROSS JOIN command. This command generates tables without the choice of the key field, and the result is the same combination of different rows of output tables.

SELECT<названия колонок или *>FROM<таблица_1>CROSS JOIN table_2

Joining is not limited by more than two tables, it can also take a few JOIN commands, which is more convenient for forming final zvіtіv. Below is an example for all commands from the table.

SELECT * FROM Users INNER JOIN Departments ON Users.DepartmentID = Departments.DepartmentID

UserID	username	UserSurname	DepartmentID	PositionID	DepartmentID	DepartmentName
1	Ivan	Petrov	1	1	1	Production
2	Ivan	Sidorov	1	2	1	Production
3	Peter	Ivanov	1	2	1	Production
4	Nicholas	Petrov	1	3	1	Production
5	Nicholas	Ivanov	2	1	2	distribution
6	Sergey	Sidorov	2	3	2	distribution
7	Andrey	Bukin	2	2	2	distribution

SELECT * FROM Users LEFT JOIN Departments ON Users.DepartmentID = Departments.DepartmentID

UserID	username	UserSurname	DepartmentID	PositionID	DepartmentID	DepartmentName
1	Ivan	Petrov	1	1	1	Production
2	Ivan	Sidorov	1	2	1	Production
3	Peter	Ivanov	1	2	1	Production
4	Nicholas	Petrov	1	3	1	Production
5	Nicholas	Ivanov	2	1	2	distribution
6	Sergey	Sidorov	2	3	2	distribution
7	Andrey	Bukin	2	2	2	distribution
8	Victor	Rybakov	4	1	NULL	NULL

SELECT * FROM Users RIGHT JOIN Departments ON Users.DepartmentID = Departments.DepartmentID

UserID	username	UserSurname	DepartmentID	PositionID	DepartmentID	DepartmentName
1	Ivan	Petrov	1	1	1	Production
2	Ivan	Sidorov	1	2	1	Production
3	Peter	Ivanov	1	2	1	Production
4	Nicholas	Petrov	1	3	1	Production
5	Nicholas	Ivanov	2	1	2	distribution
6	Sergey	Sidorov	2	3	2	distribution
7	Andrey	Bukin	2	2	2	distribution
NULL	NULL	NULL	NULL	NULL	3	Purchasing

SELECT * FROM Users FULL JOIN Departments ON Users.DepartmentID = Departments.DepartmentID

UserID	username	UserSurname	DepartmentID	PositionID	DepartmentID	DepartmentName
1	Ivan	Petrov	1	1	1	Production
2	Ivan	Sidorov	1	2	1	Production
3	Peter	Ivanov	1	2	1	Production
4	Nicholas	Petrov	1	3	1	Production
5	Nicholas	Ivanov	2	1	2	distribution
6	Sergey	Sidorov	2	3	2	distribution
7	Andrey	Bukin	2	2	2	distribution
NULL	NULL	NULL	NULL	NULL	3	Purchasing
8	Victor	Rybakov	4	1	NULL	NULL

SELECT * FROM Users CROSS JOIN Departments

UserID	username	UserSurname	DepartmentID	PositionID	DepartmentID	DepartmentName
1	Ivan	Petrov	1	1	1	Production
2	Ivan	Sidorov	1	2	1	Production
3	Peter	Ivanov	1	2	1	Production
4	Nicholas	Petrov	1	3	1	Production
5	Nicholas	Ivanov	2	1	1	Production
6	Sergey	Sidorov	2	3	1	Production
7	Andrey	Bukin	2	2	1	Production
8	Victor	Rybakov	4	1	1	Production
1	Ivan	Petrov	1	1	2	distribution
2	Ivan	Sidorov	1	2	2	distribution
3	Peter	Ivanov	1	2	2	distribution
4	Nicholas	Petrov	1	3	2	distribution
5	Nicholas	Ivanov	2	1	2	distribution
6	Sergey	Sidorov	2	3	2	distribution
7	Andrey	Bukin	2	2	2	distribution
8	Victor	Rybakov	4	1	2	distribution
1	Ivan	Petrov	1	1	3	Purchasing
2	Ivan	Sidorov	1	2	3	Purchasing
3	Peter	Ivanov	1	2	3	Purchasing
4	Nicholas	Petrov	1	3	3	Purchasing
5	Nicholas	Ivanov	2	1	3	Purchasing
6	Sergey	Sidorov	2	3	3	Purchasing
7	Andrey	Bukin	2	2	3	Purchasing
8	Victor	Rybakov	4	1	3	Purchasing

SELECT dpt.DepartmentName AS "Department", usr.UserName + " " + usr.UserSurname AS "User name", pos.PositionName AS "Position" FROM Users AS usr LEFT JOIN Departments AS dpt ON usr.DepartmentID = dpt.DepartmentID LEFT JOIN Positions AS pos ON usr.PositionID = pos.PositionID ORDER BY dpt.DepartmentID, pos.PositionID

Department	user name	Position
NULL	Viktor Rybakov	manager
Production	Ivan Petrov	manager
Production	Ivan Sidorov	senior analyst
Production	Petr Ivanov	senior analyst
Production	Nikolay Petrov	Analyst
distribution	Nikolay Ivanov	manager
distribution	Andrey Bukin	senior analyst
distribution	Sergey Sidorov	Analyst

Change of data

First, let's talk about the commands to change data, it is necessary to explain the specifics of the Transact-SQL dialect. As you can see from the name itself, the whole mechanism is based on transactions, that is, on the sequence of operations, combined into one logical module, which is fed into the data collection, change the data, or the structure of the table. For an hour, all transactions in the data script are blocked, which allows the inconsistency of data to be hidden for an hour on the cob of work with the table and the completed script.

Per Transact-SQL transactions, a BEGIN TRANSACTION ... COMMIT TRANSACTION structure is passed. Tsyu structure vikoristovuvat neobov'yazkovo, but then all commands to the scenario are irreversible, so it is impossible to work to the front. The structure of the transaction block is the same:

BEGIN TRANSACTION [my transaction] [operation] COMMIT TRANSACTION [my transaction] or ROLLBACK TRANSACTION [my transaction]

Below is the butt of the victorious block:

Set new salary for all workers BEGIN TRANSACTION TR1 UPDATE Positions SET BaseSalary = 25000000000000000 IF @@ERROR<>0 BEGIN RAISERROR("Error, transaction no completed!",16,-1) ROLLBACK TRANSACTION TR1 END ELSE COMMIT TRANSACTION TR1

To insert data in a SQL server table, the INSERT INTO command is used:

INSERT INTO [table name] (columns) VALUES ([column values])

Another part of the command is not obov'yazkovoy for MS SQL Server 2003, but MS JET SQL without a word will see pardon syntax. The insertion should be carried out in rows, so that all columns of the tables and the values ​​are indicated in the room, as it is necessary to enter them in them. If a column can have a value behind a close, or if it allows an empty value, then in the insert command, this column can be omitted. The INSERT INTO command also allows you to specify the insertion of data for the order of the columns, but at the same time it is necessary to specify the order of the columns.

In the Users table, insert a row with UserID = 9, UserName = "Nikolay", - UserSurname = "Gryzlov", DepartmentID = 4, PositionID = 2. INSERT INTO Users VALUES (9, "Nikolay", "Gryzlov", 4, 2 ) -- In the Users table, insert a row with data UserID = 10, UserName = "Nikolay", -- UserSurname = "Kozin", DepartmentID - promotional value, PositionID - not specified. INSERT Users VALUES (10, "Nikolay", "Kozin", DEFAULT, NULL) -- In the Users table, insert a row with data UserName = "Angrey", UserSurname = "Medvedev", -- UserID = 11, other values ​​for INSERT INTO Users (UserName, UserSurname, UserID) VALUES ("Angrey", "Medvedev", 11)

In order to change the values ​​of the table table, the UPDATE command is used:

UPDATE [table name] SET [column name]=[column value] WHERE [um]

Update (change) the value of the table can be carried out insanely, with a mind or selection of data from another table.

Set the salary of 2000 units to all the posads. UPDATE Positions SET BaseSalary = 2000 -- Posads with id 1 set the salary to 2500 units. UPDATE Positions SET BaseSalary = 2500 WHERE PositionID = 1 -- Posad with id 2 will change salary by 30%. UPDATE Positions SET BaseSalary = BaseSalary * 0.7 WHERE PositionID = 2 -- Set all posads to a healthy salary (30,000 add per number of -- lists of employees in the organization) UPDATE Positions SET BaseSalary = 30000 / (SELECT COUNT(UserID) FROM Users)

The view of data is set with the DELETE command:

DELETE FROM [table name] WHERE [umova]

Vydalennya danikh call to see through for some criterion. Since the removal of data is not a safe operation, then before such a command, it is best to create a test selection with the SELECT command, as if you have seen the result of these data, they will be deleted. As much as you need, you can boldly replace SELECT with DELETE and delete data.

Checklist view with identifier 10 - In upgrade mode, it is recommended to use the SELECT command, - to know if the data will be deleted: - SELECT UserID FROM Users WHERE UserID = 10 Users FROM Users INNER JOIN Departments ON Users.DepartmentID = Departments.DepartmentID WHERE Departments.DepartmentName = "Production" -- Views of all inputs DELETE FROM Users

Note! At the butt for filtering data, there is a fixed table. If you want to rearrange a sprinkling of tables in the command, the data will be deleted only from the tables, as indicated after the word DELETE.

The best command for clearing tables is TRUNCATE TABLE.

TRUNCATE TABLE [table name]

An example of a view of all data:

Clear the Users table TRUNCATE TABLE Users

Transact-SQL allows clock tables to be written, so that tables are created in the server's memory for an hour to work with the data base. Timchasov tables can mother be-yak im'ya, but start goiters' tying with the symbol #.

Create a time table #TempTable, copy and paste - UserName columns of the table Users SELECT UserName INTO #TempTable FROM Users - Select all records of the time table #TempTable SELECT *

Saving procedures and functions

The procedures and functions are saved by a set of SQL-operators, which can be saved on the server. If the script is saved on the server, then the clients will not have to re-set the same okre operators, the stench can go back to the procedure that is saved. Situations, if the procedures to be avoided, are especially complicated:

• Numerous client programs are written in different languages, or work on different platforms, but also perform the same operations with databases.
• Bezpeka plays a primary role. Saving procedures are vicorous for all standard operations, which ensure safety and security of the middle, and procedures guarantee proper registration of skin surgery. With this type of installation, the programs and drivers do not take away direct access to the database tables and may choose only specific procedures that are saved.
• It is necessary to reduce the traffic between the client and the server. The information that is transferred between the server and the client is gradually reduced, and the burden on the database server system is increased, so that in this case on the server side, more work is done from data processing.

An example of a set of procedures, what is taken care of, and functions, what is taken care of:

Salary update function CREATE PROCEDURE usp_UpdateSalary AS UPDATE Positions SET BaseSalary = 2000 GO -- Salary update function created CREATE FUNCTION usf_GetName (@UserID int) RETURNS varchar(255) + " " + UserSurname FROM Users WHERE UserID = @UserID) -- EXEC Salary Update TestDatabase.dbo.usp_UpdateSalary -- Change employee name with id 2 SELECT TestDatabase.dbo.usf_GetName(2)

Again, the procedures and functions that are taken care of give an advantage:

• productivity;
• zagalna logic for all zapitіv;
• traffic change;
• safety - access to the coristuvachev is given not to the table, but to the procedure;

Productivity

To increase productivity, then for a swedish drinker, remember the following rules for stacking rows of drinks:

• Unicode NOT - commands for the list are selected at the end of the steps, which increase the traffic to the server.
• Unicate LIKE - this operator is a matching operator for multiple matching patterns, a lower operator is = to reduce the number of necessary filtering steps.
• Zastosovuvaty exact templates pokuku - zastosuvannya symbolіv pіdstanovki zbіlshuє hour vykonannya zaputu, oskіlki for revіrki vsіh vіkh variant_v_іnіnіv_іnіїіі installation nіbnіbnі additional server resources.
• Unique ORDER - the sorting command allows you to sort the rows in the table in a row, which will hinder the result.

Vitaliy Bochkarov

The T-SQL language is recognized for managing datasets. For reasons of guilt, there are not any characteristic features of traditional languages, which are necessary for programming add-ons. Since you have been working on creating add-ons for a long time, then, sing-song, oppose the idea of ​​programming in T-SQL and other languages, such as VB, C# and Java.

T-SQL packages

One T-SQL instruction is called a request, and a set of them is called a package. The entire sequence of package instructions is superseded by the server from client programs as one single unit.

SQL Server looks at the entire package as if it were working alone. The presence of a pardon, if only in one instruction, would lead to the impossibility of wrapping up the whole package. At that very hour, the grammatical analysis does not change the names of objects and schemes, but the scheme itself can be changed by the process of typing instructions.

Revisiting the package

The SQL script file and the Query Analyzer window may contain a few packets. At times, all packages add the keywords of the terminators. For zamovchuvannyam tsim the key word є GO, and there can be buti united in a row. All other symbols (navit comments) will neutralize the package distributor.

Packet distribution is actually a function of Management Studio, not a server. You can change it on the side of the Query Execution dialog box of the program's power, but I don't recommend it (to friends).

DDL instructions

Some DDL mov T-SQL statements, such as Create Procedure, are the first instructions in the package. Even the old scenarios, which create impersonal objects, often require a lot of repackaging packages. SQL Server oskіlki okremo parse the syntax behind the packages, such a presence of impersonal distributors helps to localize pardons.

Switching between data bases

In the work mode, the data base is always displayed on the toolbar, and at some point it can be changed. For program code, the thread base is defined by the keyword USE. The keyword in the package indicates, with which data base the robot will be based, starting from the flow point:

Winning packages

The package can be used in dekilkom ways.

The SQL script in general (so that all packages that enter before the new one) can be followed by a path to the file. sql in the SQL editor of the Management Studio and pressing keys (either by pressing the button! Execute of the toolbar, or by choosing the Query 1 ^Execute item in the menu). (I set up my Windows operating system so that when I click on the file. SQL, the entire query analyzer automatically starts.)

The SQL editor in Management Studio can also be used as a SQL statement. For whom it is necessary to see and press a key (either click on the Execute button on the toolbar or select the Query 1 ^Execute item in the menu).

The program package T-SQL can be used for the help of ADO or ODBC.

About T-SQL Scripting You can also look for additional SQLCmd command-line utility to pass the file name. SQL yak parameter.

The SQLCmd utility can have a few parameters and can be easily customized for practical needs.

Additional information about the SQLCmd utility div. at branch 6 dedicated to Management Studio.

information

Vikonannya procedures that are taken

For the SQL package, the procedure that is saved is called after the help of the exec keyword. Whom should follow the low rules. Oskіlki razrivi ryadkіv for SQL Server do not make sense, the exec command means the end of the previous instructions.

If the procedure that needs to be selected is in the first row of the package (otherwise it is a single instruction), then it is not obligatory to specify the key word exes in the new language. At the same time, the insertion of which keyword will not lead to a pardon, and before that, it will help solve problems for the future, as the text of the package will be changed.

The next two wikis of the system procedure, which are selected, demonstrate the alternate exec commands in the package:

EXEC sp_help;

At whom we divided, we only looked at the exec command at the package. I will report information about the creator of the keyword yeshes in the “Dynamic SQL” section.

Formatting in T-SQL

By stretching the text of the book, the program code has been formatted to reduce accuracy; At whom we have divided the key moments of formatting.

Complete instructions

The ANSI SQL standard requires the use of dot-to-coma in the skin instructions. At the same hour, when programming on the T-SQL mov, the point with whom is not obov'yazkova. Whom should follow the rules of kilkom.

Do not place її after the try end statement.

Do not post її after you think if.

Place the obov'yazkovo її after the heat-table verses of the CTE.

For the shortest run of the program code, it is still recommended to beat the specks with a clod. In future versions of SQL Server, it may become more complex, which may be more advanced work.

Prodovzhennya rowkіv

Instructions T-SQL, by its very nature, can be powerful. Deyakі zapitami remaining rasdіlu s chislennym ob'ednannymi and pіdzapitami occupy the whole side. It's especially appropriate that T-SQL ignores whitespace characters in that end of the row. Tse means that the current instruction can be carried out on the offensive row without the need for any special symbol. This power allows the significant world to move the reading of the program code.

In other implementations of SQL, such as Access, the completion of the statement requires the presence of a speck with a lump. SQL Server allows for some variation, but does not care about the language.

Comments

The T-SQL language can be used in one package of comments in two styles: ANCI and move C. The first one starts with two hyphens and ends in the end of the line:

- Commentary on ANSI style

Also, ANSI style comments can be inserted like a row of instructions:

Select FirstName, LastName - columns FROM Persons, which are selected - output table

Where LastName Like 'Hal%'; - lined up in rows

The SQL editor can freeze and see comments on all the seen rows. For which you need to select the menu command Edit^Advanced^Comment Out ( or ) or Edit ^ Advanced 1 ^ Remove Comments ( or ).

Comments on the style of the movie C begin with oblique drawings and little stars (/*) and end with the same symbols at the turning sequence. This type of comments is the best choice for commenting blocks of rows, such as headings or great test requests. /*

Table Insert Trigger Order Paul Nielsen

One of the main highlights of comments in style C are those that can be written in them without commenting.

T-SQL customization

If the SQL editor shows a pardon, it will show the character and row number in the package. Having clicked on the pardon, you can immediately move to the upper row.

To finish the pardon is often broken in the wrong word, as it was appointed in the reminder, - to lay everything in the way, as the instructions were cleared up. Sound, in fact, the pardon has been repaid without delay until or after what was ordered in the next month - in any case, it is ordered to do it exactly in the next place.

SQL Server pronounces a bunch of commands to make it easier to upgrade packages. Zocrema, the print command overpowers the update without creating the result set of data. In particular, I respect the print command as especially valuable for debugging a package. If the demand analyzer is in network mode, select the next packet:

The resulting set of data appears at the table and is folded in one row. At the same time, the Messages tab will show the next result:

(1 row(s) affected)

Sometimes it is necessary to set up the program to block the number of objects. The pause command allows you to pause the batch for an hour job. For example, when vikonannі offensive yoga code another row appears after a two-second pause:

Print 1 Cob";

waitfor delay *00:00:02′;

Print 'Kіnets 1;

Result of vikonanny code:

The key point is those that in the Management Studio version of SQL New in Server 2005 do not include the T-SQL engine, which is present in the package

2005 Visual Studio 2005

update, I will tell you about it on the website www. SQLServerBible. com.

Changes

Be-yak mov vimagaє for timchasovogo zberezhennya meaning in the memory of the zminnykh. Changes in T-SQL are created after an additional declare command, which is followed by their change and type. Winnings for different types of data are exactly matched with the ones in the tables. To which you can add a table type and a SQLVariant type. In one command, declare through whom can a dekilka of change be redeemed.

Significance for locking that area designated for change

The scope of change (that's the term for one's life) is expanding only for a streaming package. For the locking of the creation of the change, the empty values ​​of null and before the inclusion of the violation of the guilty buti initialization are removed.

In the offensive scenario, two test changes are created, with which they are demonstrated, the area is assigned that value for locking. The whole script is in one file, although from a technical point of view it is folded into two packages (separated by the GO command). Immediately after the script, three SELECT statements are shown:

DECLARE @Test INT,

@TestTwo NVARCHAR(25);

SELECT @ Test, @ Test Two;

SET @TestTwo = 'value';

SELECT @ Test, @ Test Two ;

SELECT @Test as BatchTwo, @TestTwo;

(1 row(s) affected)

1 value

(1 row(s) affected)

Msg 137, Level 15, State 2, Line 2 Must declare the scalar variable “@Test”.

The first SELECT statement rotates two empty values. After that, as the changes were initialized, the stench turns the assigned values. When the package is completed as a result of the advancing SELECT instruction, pardon #137 is notified.

Changes can be made to the local area of ​​assignment, so that the procedures that are saved are not extended to other packages.

Variant of set and select commands

The SET and SELECT commands can be used to assign values ​​to changes. The main difference between them is that the SELECT command can extract information from the database (such as tables, data, data, etc.) and include other SELECT instructions, while the SET command is separated from the data from the virus. Like one, so another team can replace the function. Use the SET command if you need to assign a variable result to the function, or a constant and you don't need to look at the data.

The SELECT statement can take values ​​from multiple entries. The value of skin lesions can be assigned to the change. As the SELECT statement moves the row of rows, the values ​​of the remaining rows are changed. The SELECT instruction is coming in 32 rows, as ordered by the field of the individual identifier. At the same hour, the code is changed, and the name is no longer the rest of the people on the list:

Declare ©TempID INT,

@TempLastName VARCHAR(25);

SET@TempID=99;

@TempID=PersonID,

@TempLastName = LastName

ORDER BY PersonID;

The result of vikonannya package:

32 @code rest:Campbell

At the pointed butt, the pardon has been extended. ka. Don't double-check the SELECT statement to fill in the changes, as you don't think the resulting set of data will add up to more than one row. On another occasion, you will happen to be satisfied with just the remaining row of tributes.

If the SELECT statement does not rotate the same row, then it does not change. The next request does not rotate the value, the records with identifier 100 do not exist in the Person table. For reasons of changing @TempIDvariable, we hope the value of the remaining essential row, when changing the preference, we take the empty value:

Declare @TempID INT,

@TempLastName VARCHAR(25);

SET@TempID=99;

SELECT @TempID = PersonID,

@TempLastName = LastName FROM Person WHERE PersonID = 100 ORDER BY PersonID;

SELECT @TempID, @TempLastName;

99 @code last:NULL

Umovny vіdbіr

The next SELECT statement is to replace the WHERE proposition and the syntax is correct, although it may look unintelligible for them:

SELECT @ change = viraz WHERE boolean-viraz;

At times, the WHERE clause functions like a smart if statement. If true, then the change value is assigned, otherwise the SELECT statement is overridden, but the change value is not changed.

Variant of Changes in SQL Queries

One of my favorites of the authorities of the T-SQL language are those that can be changed in applications without collapsing folding dynamic rows, that will be changed in the program code. Dynamic SQL continues its own basis, but you can change the values ​​​​single in a simpler way - for the help of a change.

Skrіz, de zapі can victorious viraz, can victorize and change. At the stepping butt, the substitution of the change was demonstrated in the proposition WHERE: USE OBXKites;

DECLARE @ProductCode CHAR(10);

SET @Code = '1001';

SELECT ProductName FROM Product

WHERE Code = @ProductCode;

The result will be rejected:

Basic Box Kite 21 inch

Changes with multiple assignments

Changes with multiple assignments - this is a hostile method that allows you to add change to yourself for additional SELECT instructions and subsume.

Whom will be shown a low number of applications from real life. For the sake of simplicity, however, the SELECT statement, let me introduce the basic form:

SELECT @ change = @ change + d. stowpet FROM(kerowan_table) as d;

A leather row of ceramic tables is added to the change, turning the vertical line into a horizontal list.

This type of foreshadowing is often practical. In other words, the vertical list can be more easily transformed into a horizontal list divided by lumps, stretching the subtitle by a few inches. Short horizontal lists are easily accepted by people, and also save space.

A list of dates for the Outer Banks Lighthouses tour is created at the offensive butt, which is shown at the head database of Cape Hatter as Ad ventures:

@EventDates VARCHAR(1024);

SET @EventDates = '';

SELECT ©EventDates = @EventDates + CONVERT(VARCHAR(15), a.d,107) + '

FROM (select DateBegin as [d] from Event join Tour

on Event.TourID = Tour.TourlD WHERE Tour. = 'Outer Banks Lighthouses') as a;

SELECT Left(@EventDates, Len(@EventDates)-1)

AS 'Outer Banks Lighthouses Events";

The result of vikonannya package:

Outer Banks Lighthouse Events

The problem of changing the numbers with numerical assignments is that the correct order of the denormalized data is guaranteed. Since this method of non-documentation looks like a gimmick, you can't boast of the full brilliance of SQL Server. Prote vin can be shown as a red flag for the commemoration of the day's appointments (I see the cursor override).

Leran2002 April 9, 2015 at 12:31

Learner to learn SQL (DDL, DML) using the dialect of MS SQL Server. Part of perch

• SQL,
• Microsoft SQL Server

• tutorial

About what this assistant

Tsey podruchnik є shchos on kshtalt "to the stamp of my memory" z mov SQL (DDL, DML), tobto. This information, as it has accumulated in the process of professional activity, is constantly collected in my head. Tse for me is a sufficient minimum, which is the most common when working with data bases. Because of the need to post more new SQL constructs, I call for help in the MSDN library and put it on the Internet. In my opinion, take everything in your head rather smoothly, that one doesn’t need much in particular. Ale to know the main constructions is even better, because stench zastosovnі practically in such a rich relational data bases, like from Oracle, MySQL, Firebird. Vidminnosti are mainly related to the types of data, but they can be modified by the details. The main constructions of the SQL language are not so rich, and for constant practice, the stench is quickly forgotten. For example, for creating objects (table, index, index, etc.), a text editor (IDE) for working with a data base is enough for working with a data base, and there is no need to use visual tools for working with a specific type of database (MS SQL), Oracle, MySQL, Firebird, …). It’s handy that the entire text is in front of your eyes, and it’s not necessary to sort through the numerical tabs to create, for example, an index or an exchange. In case of post-work with the data base, create, change, and especially re-create the object for additional scripts, it will appear more quickly, even lower, in the visual mode. Also, in the scripting mode (vіdpovіdno, for proper accuracy), it's easier to set and control the rules for naming objects (my subjective thought). Before that, the scripts can be manually tweaked at a different time, if you change, to roam in one database (for example, a test one), it is necessary to transfer it in such a way to another database (productive).

Mova SQL is subdivided into a few parts, here I will look at the 2 most important parts:

• DML – Data Manipulation Language
  • SELECT - selection of data
  • INSERT - insert new data
  • UPDATE - data update
  • DELETE - delete data
  • MERGE - angry danich

Because I am a practitioner, as such a theory will not be enough for a given assistant, and all constructions are explained on practical butts. Until then, I care that language programming, and especially SQL, can only be mastered in practice, self-studying it and understanding what to learn, if you win that other design.

This assistant was created according to the principle of Step by Step, tobto. it is necessary to read yogo afterward and then put it on the bag. And if you need to find out more about the command in more detail, then search for a specific search on the Internet, for example, from the MSDN library.

Just before the writing of this assistant, the database of MS SQL Server version 2014 was quized, for typing scripts, I quized MS SQL Server Management Studio (SSMS).

Briefly about MS SQL Server Management Studio (SSMS)

SQL Server Management Studio (SSMS) is a utility for Microsoft SQL Server for configuring, managing and administrating database components. This utility will replace the script editor (which will basically be used by us) and the graphic program that works with objects and tweaks the server. The main tool of SQL Server Management Studio is Object Explorer, which allows you to review, view server objects, and also cherish them. This text is often referenced from Wikipedia.

To create a new script editor, click the New Query button:

To change the streaming data base, you can twist the list that you see:

To win a song command (or a group of commands), you can see it and press the “Execute / Vikonati” button or the “F5” key. As a rule, there is only one command in the editor at the moment, otherwise you need to select all the commands, you don’t need to see anything.

If you want to change the scripts, which especially create objects (tables, tables, indexes), to change it, change it from the context menu, having seen the specific group (for example, Tables), the table itself or the Stovpts group in the other.

All is well that we need to know for the purpose of aiming butts here. Reshta for the SSMS utility is not very important to learn independently.

Trochy theory

Relational data base (RDB, otherwise in the context just a database) is a collection of tables related to each other. To put it bluntly, the database is a file for which data is saved from a structured look.

DBMS - Data Base Management System, tobto. tse complex of tools to work with a specific type of database (MS SQL, Oracle, MySQL, Firebird, ...).

Note
Because in life, in roman language, we say: “Oracle DB”, or just call “Oracle”, really looking like “Oracle DBMS”, then in the context of this assistant, the term DB is sometimes used. From the context, in my opinion, it would be reasonable to go about it myself.

Table Stovptsі, so they can call themselves fields or columns, all words will be victorious as synonyms, which they say to themselves.

The table is the main object of the RDB, all data of the RDB are taken in a row in the columns of the table. Rows, records - also synonyms.

For skin tables, like and її stovptsіv, names are given, what kind of years before them.
Object name (table name, column name, index name) in MS SQL can have a maximum length of 128 characters.

For finalization– DB ORACLE object names can have a maximum of 30 characters. Therefore, for a particular database, it is necessary to modify its own rules for naming objects, in order to fit into the limit for the number of characters.

SQL - language that allows you to write to the database in front of the DBMS. For a specific DBMS, language SQL can have a specific implementation (its own dialect).

DDL and DML - subset of the SQL movie:

• Mova DDL serve before modifying the structure of the database, tobto. to create/change/display tables and links.
• Mova DML allows you to manipulate data from tables, tobto. h її rows. Win allows you to select data from the tables, add new data in the tables, and also update and view the relevant data.

Move SQL can have 2 types of comments (single-row and multiple-row):

Single row comment
і

/* bug ordinal comment */

Vlasne, everything will be enough for the theory.

DDL - Data Definition Language

For example, let's take a look at the table with data about spivrobitnikov, in the case of a programmer, which is not є:

Different columns of the tables may have the following names: Personnel number, PIB, Date of birth, E-mail, Posada, Vіddіl.

The skin of these stovptsіv can be characterized by the type of data that is to be avenged in the new:

• Personnel number - whole number
• PIB - row
• Date of birth - date
• E-mail - row
• Posada - row
• Viddil - row

Stovptsya type - a characteristic, how to talk about those, yakі danі can save dans stovpets.

For the beginning, it will be enough to remember only such basic types of data that are distinguished in MS SQL:

Value	Designated by MS SQL	Description
Row of change house	varchar(N) і nvarchar(N)	For the additional number N, we can indicate the maximum possible length of the row for a double row. For example, if you want to say that the value "PIB" can contain a maximum of 30 characters, you must specify the type nvarchar(30). The difference between varchar and nvarchar is that varchar allows you to save rows from the ASCII format, where one character takes 1 byte, and nvarchar takes rows from the Unicode format, when a character takes 2 bytes. The varchar type can only be chosen in the way that you are 100% sure that it is not necessary to save Unicode characters in this field. For example, varchar can be used to save the email address, because stink sing more than ASCII characters.
Row of fixed dozhina	char(N) і nchar(N)	In the row of changeable dozhina, this type is considered to be the same, that the row of dozhina is less than N characters, it will always be supplemented by the right-hander to dozhina N with spaces and taken from the database at the sight, tobto. it takes exactly N characters from the database (one character takes 1 byte for char and 2 bytes for the nchar type). In practice, this type is rarely known to be congested, and it usually wins, it wins more importantly in the char(1) format, then. if the field is one character.
Whole number	int	The Danish type allows us to sing in stovptsi more than a whole number, both positive and negative. For finalization (it's not so important for us now) - the range of numbers that allows the int type -2 147 483 648 to 2 147 483 647. Name the main type, which is used to set identifiers.
Speech chi deisne number	float	If you speak in plain language, then these are numbers, in which there may be a dozen dots (coma).
date	date	If at the stovpts it is necessary to save only the Date, as it is accumulated from three warehouses: Numbers, Months and Roku. Napriklad, 02/15/2014 (Fierce 15, 2014). The Danish type can be selected for "Date of acceptance", "Date of birth", etc. in quiet situations, if it’s important for us to fix the date, but if the warehouse hour is not important for us, we can leave it or we don’t see it.
Hour	time	The Danish type can be beaten, so it is necessary to save more than a given hour from the stovpts, tobto. Years, Khvilini, Seconds and Milliseconds. For example, 17:38:31.3231603 For example, the famous "Hour of the departure of the flight."
date that hour	datetime	This type allows saving both the Date and the Hour at the same time. For example, 02/15/2014 05:38:31.323 PM For the butt, you can but the date is the same hour as it should be.
Prapor	bit	The Danish type is manually set to save the value of the form “Tak” / “Hi”, de “Tak” is taken as 1, and “Hi” is taken as 0.

So the value of the field itself, even though it is not fenced, may not be specified, for the value of the field, the keyword NULL is selected.

For the purposes of application, let's create a test database called Test.

A simple database (without specifying additional parameters) can be created by running the following command:

CREATE DATABASE Test
You can view the data base with the command (we will also protect you from this command):

DROP DATABASE Test
In order to go to our data base, you can use the command:

US Test
Otherwise, select the Test database from the list in the SSMS menu area. When working with me, I most often win by the very method of switching between bases.

Now we can create a table in our database, vicorist descriptions look like stench, vicorist spelled out those Cyrillic characters:

CREATE TABLE [Recorders]([Personnel Number] int, [PIB] nvarchar(30), [Pop Date] date, nvarchar(30), [Posada] nvarchar(30), [Viddil] nvarchar(30))
On this occasion, we happen to put the names at the square bow […].

In addition, in the database for greater clarity, all the names of objects should be more briefly set in Latin and do not sing in the names of the glade. In MS SQL, sound at the same time the word starts with a great letter, for example, for the field "Personnel number", we could set the name PersonnelNumber. Also, the name can be twisted with numbers, for example, PhoneNumber1.

Note
For some DBMS, we can shorten the existing format for the name "PHONE_NUMBER", for example, this format is often used in the ORACLE database. Naturally, when setting the name of the field, it is not necessary to avoid the keywords that are selected in the DBMS.

For some reason, you can forget about the syntax with square arches and see the table [Spivrobitniki]:

DROP TABLE
For example, a table with 3 workers can be called "Employees", and її fields can be given the following names:

• ID - personnel number (identifier of the caller)
• Name - PIB
• Birthday - birthday date
• Email
• Position - Posada
• Department - Viddil

The most common name for the identifier field is the word ID.

Now let's create our table:

CREATE TABLE Employees(ID int, Name nvarchar(30), Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30))
In order to set the strings for the stowpci, you can use the NOT NULL option.

For the already basic table, the fields can be re-assigned with the help of offensive commands:

ID field update ALTER TABLE Employees ALTER COLUMN ID int NOT NULL

Note
The main concept of SQL mov for most DBMS is left the same (I can judge about this DBMS, for which I had a chance to improve). The scope of DDL in different DBMSs is mainly related to data types (here, not only their names, but the details of their implementation can be modified), so the very specifics of the SQL implementation can be small differences in the dialect, unfortunately, there is not one standard). With the basics of SQL, you can switch from one DBMS to another, because you need to learn more about the details of the implementation of commands in the new DBMS, then. In most cases, it will be sufficient to simply draw an analogy.

Table creation CREATE TABLE Employees(ID int, -- in ORACLE type int - equivalent to number(38) Name nvarchar2(30), -- nvarchar2 in ORACLE equivalent to nvarchar in MS SQL Birthday date, Email n , Position nvarchar2 (30), Department nvarchar2(30)); -- update fields ID and Name (here ALTER COLUMN is replaced by MODIFY(…)) ALTER TABLE Employees MODIFY(ID int NOT NULL,Name nvarchar2(30) NOT NULL); -- add PK (which way the construction looks like i in MS SQL, it will be shown below) ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY(ID);
For ORACLE є vіdminnostі vіdminnostі vіdmіnnostі vіdіnії іn implementation ії type varchar2, yogo coding fall in іd lаshtuvan db і text can be saved, for example, in coding UTF-8. First of all, field length in ORACLE can be set both in bytes and in characters, for which the additional options BYTE and CHAR are specified, which are specified after the field length, for example:

NAME varchar2(30 BYTE) -- Field scope up to 30 bytes NAME varchar2(30 CHAR) -- Field space up to 30 characters
As an option, it will be possible to replace BYTE or CHAR locks, in case of a simple insertion in ORACLE type varchar2(30), to store it in the database, so it can sometimes be set in the IDE customizations. For an hour you can easily get lost, because the ORACLE type, as a rule, uses the varchar2 type (and here it’s true for an hour, for example, with UTF-8 coding), I will explicitly prescribe CHAR (because it’s easier to name the row itself in characters).

But in times as well as in the tables already є yak-nebud data, then for the successful typing of commands it is necessary that all rows of the table have the ID and Name fields obov'yazkovo zapovneni. We will demonstrate it on the example, insert it into the data table in the fields ID, Position and Department, which can be generated by the following script:

INSERT Employees(ID,Position,Department) VALUES (1000,N"Director",N"Administration"), (1001,N"Programmer",N"IT"), (1002,N"Accountant",N"Accounting" ), (1003,N"Senior programmer",N"IT")
At times, the INSERT command also sees a pardon, because when inserting, they did not assign the value of the Name field to the language field.
If we already have data in the table, then the ALTER TABLE Employees ALTER COLUMN ID int NOT NULL command was successful, and the ALTER TABLE Employees ALTER COLUMN Name int NOT NULL command saw a pardon message that the Name field is NULL (not assigned ) value.

Add a value for the Name field and renew the data entry:

Also, the NOT NULL option can be beaten every time a new table is created, that is. in the context of the CREATE TABLE command.

On the back, we can see the table for the help of the command:

DROP TABLE Employees
Now let's create a table with obligatory for filling columns ID and Name:

CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30) NOT NULL, Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30))
It is also possible to write NULL after the name, which means that in the new NULL values ​​(not specified) will be valid, but this work is not obov'yazkovo, so the characteristic is given for the sake of understanding.

As it is necessary to make the necessary stovpets neobov'yazkovym for zapovnennia, then the following command syntax is victorious:

ALTER TABLE Employees ALTER COLUMN Name nvarchar(30) NULL
Abo is simple:

ALTER TABLE Employees ALTER COLUMN Name nvarchar(30)
With this command, we can also change the type of the field to a lower sum type, or change the second one. For example, expand the Name field to 50 characters:

ALTER TABLE Employees ALTER COLUMN Name nvarchar(50)

Primary key

When the tables are combined, it is important that there is a small unique stovpets, or a combination of stovptsiv, which is unique for the skin row - for given unique values, you can unambiguously identify the record. This value is called the primary key of the table. For our Employees table, such unique values ​​can be assigned an ID (which can be used as the “Temporary personnel number of the employee” - in our case, the value is unique for the skin specialist and cannot be repeated).

You can create the first key to the already existing table using the additional command:

ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY(ID)
De "PK_Employees" tse іm'ya obmezhennya, scho vydpovidaє for the first key. Sound the name of the primary key, the prefix “PK_” is selected after any table name.

Since the first key is formed from dekilkoh fields, then these fields need to be re-rendered in the arches through whom:

ALTER TABLE table_name ADD CONSTRAINT exchange_name PRIMARY KEY(field1,field2,…)
Varto designate that in MS SQL the fields must be included before the primary key, due to the characteristic NOT NULL.

Also, the first key can be assigned without delay at the time of the table's creation, tobto. in the context of the CREATE TABLE command. Let's see the table:

DROP TABLE Employees
And then let's create її, vikoristovuyuuchi offensive syntax:

CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30) NOT NULL, Virtual Hour, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30), CONSTRAINT PK_Employees PRIMARY KEY of all fields, as an employee)
After the creation of the data in the data table:

INSERT Employees(ID,Position,Department,Name) VALUES (1000,N"Director",N"Administration",N"Ivanov I.I."), (1001,N"Programmer",N"IT",N" Petrov P.P."), (1002,N"Accountant",N"Accounting",N"Sidoriv S.S."), (1003,N"Senior Programmer",N"IT",N"Andreev A. A.")
Since the first key of a table is less than the value of one column, you can twist the following syntax:

CREATE TABLE Employees(ID int NOT NULL CONSTRAINT PK_Employees PRIMARY KEY, - can be specified as a characteristic of the field Name nvarchar(30) NOT NULL, Birthday date, Email nvarchar(30), Position nvarchar(30), Department n
You can and do not set the real name of the company, in which case you will be assigned a system name (for example, PK__Employee__3214EC278DA42077):

CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30) NOT NULL, Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30), PRIMARY KEY(ID))
Abo:

CREATE TABLE Employees(ID int NOT NULL PRIMARY KEY, Name nvarchar(30) NOT NULL, Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30))
Ale, I recommend bi for post_ynih tables zavzhdya explicitly set im'ya obmezhennya, tk. for explicitly setting and understanding them, it will be easier to carry out manipulations with him, for example, you can work out the following:

ALTER TABLE Employees DROP CONSTRAINT PK_Employees
But such a short syntax, without assigning names to the boundary, manually zastosovuvat pіd the hour of the creation of the time table of the database (the time of the time table is based on # chi ##), as soon as the match will be seen.

Probably

At once we looked at the advancing commands:

• CREATE TABLE table_name (resurfacing fields of that type, delimitation) – serve until a new table is created in the streaming database;
• DROP TABLE table_name – serve as a remote table from a streaming database;
• ALTER TABLE table_name ALTER COLUMN im'ya_stovptsya ... – serve to update the type of the stovptsya or change its setting (for example, to set the characteristic NULL or NOT NULL);
• ALTER TABLE table_name ADD CONSTRAINT im'ya_obezhennya PRIMARY KEY(field1, field2,…) – adding the primary key to the already existing table;
• ALTER TABLE table_name DROP CONSTRAINT exchange_name - remote exchange from tables.

Trochs about timchas tables

Virizka from MSDN. MS SQL Server has two types of time tables: local (#) and global (##). Local time tables are less visible to their creators until the session is completed with an instance of SQL Server, just like the stench of creation in the past. Local clock tables are automatically visible when the clock is turned on in the instance of SQL Server. The global clock tables can be seen by all the clocks, whether they are created after the creation of these tables and are visible, if all the clocks, as they appear on the tables, are included in the instance of SQL Server.

Timing tables are created in the tempdb system database, that is. creating them without being seen by the main database, in another time table, they are identical to the primary tables, they can be seen with the help of the DROP TABLE command. Most often, local (#) clock tables are victorious.

To create a time table, you can use the CREATE TABLE command:

CREATE TABLE #Temp(ID int, Name nvarchar(30))
Since the time table in MS SQL is similar to the primary table, it can also be seen by the DROP TABLE command itself:

DROP TABLE #Temp

So the time table itself (like the original table) can be created and immediately loaded with data, rotated by the victorious syntax SELECT ... INTO:

SELECT ID,Name INTO #Temp FROM Employees

Note
In different DBMSs, the implementation of time tables can be different. For example, in the ORACLE and Firebird DBMS, the structure of the timchasovyh tables is due to be assigned later by the CREATE GLOBAL TEMPORARY TABLE command from the designated data collection specifics, and it was already necessary to check the middle of the main tables and work with it as a sizable table.

Normalization of the database - splitting into subtables (documents) and assigning links

Our streaming table Employees may not be enough for those who can enter any text in the Position and Department fields of the coristuvach, which I will threaten with pardons in the first part of the year, so as the wine of one spіvrobіtnik can be entered as а vіddіl just “ІТ”, and for another spіvrobіtnik, for example, enter "IT-viddil", at the third "IT". At the result, it was unreasonable that the mother was on the uvazi koristuvach, tobto. what did the spіvrobіtniki given by the practitioners of the same vіddіlu, what koristuvach described himself and tse 3 different vіddіlu? And then more, in our opinion, we can’t correctly group the data for such a name, de, it’s possible, it’s necessary to show the number of spivrobitniks in the analysis of the skin disease.

Another nedolіk polygaє y obsyazі zberіgannya tsієї іnformatsії її її dubluvannyam, tobto. for a skin symbol, it is required to name the name of the person, which is used in the database for the selection of the skin symbol for the name of the person.

The third nedolіk is the foldability of updating these fields, as if changing the name of a plant, for example, as it is necessary to change the name “Programist” to “Young programmer”. At this point, we will be able to make changes to the skin row of the table, in the same Posada dorovnyu «Programist».

To avoid these shortcomings and zastosovuєtsya so called the normalization of the data base - splitting them into subtables, tables dovidniki. It is not obov'yazkovo to lie in non-triple theories and twists, what to imagine normal forms, it is enough to understand the essence of normalization.

Let's create 2 tables “Plant” and “Viddili”, the first one is called Positions, and the other one is Departments:

CREATE TABLE Positions(ID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Positions PRIMARY KEY, Name nvarchar(30) NOT NULL) CREATE TABLE Departments(ID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Departments PRIMARY ) NOT NULL)
Respectfully, we have introduced a new IDENTITY option here, so to speak about those that are given in the ID column are automatically numbered, starting from 1, from 1, that is. when adding new records, they are sequentially assigned the values ​​1, 2, 3, and so on. Such fields are called autoincremental. A table may have more than one IDENTITY field assigned to it, and, as a rule, it is not required to have a language, such a field is the primary key for this table.

Note
In different DBMS, the implementation of watering from a private person can work for his own. In MySQL, for example, such a field is assigned to the additional AUTO_INCREMENT option. In ORACLE and Firebird, previously this functionality could be emulated for the help of a variety of sequences (SEQUENCE). As far as I know, ORACLE added the GENERATED AS IDENTITY option at once.

Let's remember the tables automatically, based on the streaming data records in the Position and Department fields of the Employees table:

The Name field of the Positions table is loaded with unique values ​​from the Position field of the table.
The same is possible for the Departments table:

INSERT Departments(Name) SELECT DISTINCT Department FROM Employees WHERE Department IS NOT NULL
As we look at the Positions and Departments tables, we can also number the values ​​by typing the value in the ID field:

SELECT * FROM Positions

SELECT * FROM Departments

The data of the tables now play the role of dovіdnikov for the head of the settlement and viddіlіv. Now we are asking for the identifier of the landing and vіddіlіv. We are creating new fields in the Employees table to collect these identifiers:

Add a field for the plant ID ALTER TABLE Employees ADD PositionID int -- add a field for the ID of the plant ALTER TABLE Employees ADD DepartmentID int
The type of feasible watering is the fault of both themselves, like in the dovidniks, in this tse int.

It is also possible to add an amount of watering to the table in the table with one command, having resurrected the fields through whom:

ALTER TABLE Employees ADD PositionID int, DepartmentID int
Now we will write down the value (possible exchange - FOREIGN KEY) for these fields, in order that it is not possible to write in the given fields, the value, the daily average value of the ID, which is in the fields.

ALTER TABLE Employees ADD CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(ID)
I those same zrobimo for another field:

ALTER TABLE Employees ADD CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERENCES Departments(ID)
Now it is possible to enter an extra ID value for this field from the specified date. Apparently, if you want to win a new record, or the planting, it will be our responsibility to add a new record to the final date. Because plant that weeds are now saved in dovіdniki in one single example, then to change the name, it’s enough to change її tіlki in dovidnik.

Іm'ya possilnogo obezhennya, call, є storable, it is folded from the prefix "FK_", then go ім'я tablesі і after the sign of the sign іde ім'ya fields, yak ask for the identifier of the table-dodnik.

Identifier (ID) sing to the internal values, so that only the words for the calls are made, and the values ​​​​are taken there, in most cases, it’s absolutely wrong, it’s not necessary to make a mistake for the sake of remoteness records from the doctor.

ALTER TABLE tbl ADD CONSTRAINT IM_ID FOREIGN KEY(field1, eld2,…) REFERENCES table_address(field1, eld2,…)
In this case, in the "table_domain" table, the first key of representations is a combination of decal fields (field1, field2, ...).

Alright, now let's update the PositionID and DepartmentID fields with the ID values ​​from the docs. Upload that DML with the UPDATE command:

UPDATE e SET PositionID=(SELECT ID FROM Positions WHERE Name=e.Position), DepartmentID=(SELECT ID FROM Departments WHERE Name=e.Department) FROM Employees e
We marvel at what happened, vykonavshi asked:

SELECT * FROM Employees

That's it, the fields PositionID and DepartmentID filled with different posads and other identifiers are now not available in the Position and Department fields in the Employees table, you can see these fields:

ALTER TABLE Employees DROP COLUMN Position,Department
Now our table looks like this:

SELECT * FROM Employees

ID	Name	Birthday	Email	PositionID	DepartmentID
1000	Ivanov I.I.	NULL	NULL	2	1
1001	Petrov P.P.	NULL	NULL	3	3
1002	Sidorov S.S.	NULL	NULL	1	2
1003	Andreev A.A.	NULL	NULL	4	3

Tobto. As a result, we have collected overworld information. Now, after the numbers, put that variable, you can unambiguously name them, vicorist values ​​in the tables-dodniks:

SELECT e.ID,e.Name,p.Name PositionName,d.Name DepartmentName FROM Employees e LEFT JOIN Departments d ON d.ID=e.DepartmentID LEFT JOIN Positions p ON p.ID=e.PositionID

In the Object Inspector, we can include all the objects created for this table. You can carry out various manipulations with these objects - for example, rename and remove objects.

It is also necessary to indicate that the table can be asked for itself, tobto. can be done recursively by force. For the butt, we add one more field ManagerID to our table with spivrobitniks, as it is indicative of the spivrobitnik, to which this spivrobitnik is ordered. We create a field:

ALTER TABLE Employees ADD ManagerID int
This field has a valid value of NULL, the field will be empty, as, for example, there are no supernumeraries above the spacer.

Now let's create a FOREIGN KEY on the Employees table:

ALTER TABLE Employees ADD CONSTRAINT FK_Employees_ManagerID FOREIGN KEY (ManagerID) REFERENCES Employees(ID)
Let's now create a diagram and marvel at how we look at the connections between our tables:

As a result of my fault, the picture will come (the Employees table is linked to the Positions and Depertments tables, and also works on itself):

We want to say that the keys you send can include additional options ON DELETE CASCADE and ON UPDATE CASCADE, like to talk about those, how to behave when you see or update a record, on how to send it to the add-on table. If these options are not specified, then we cannot change the ID in the date table of that record, to which one is sent from the other table, so we cannot see such a record from the date itself, until we can see all the rows that refer to the whole record. Well, it’s new in these rows to add more meaning.

For the example, let's recreate the table from the assigned ON DELETE CASCADE options for FK_Employees_DepartmentID:

DROP TABLE Employees CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30), Birthday date, Email nvarchar(30), PositionID int, DepartmentID int, ManagerID int, CONSTRAINT PK_Employees PRIMARYKE ) REFERENCES Departments(ID) ON DELETE CASCADE, CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(ID), CONSTRAINT FK_Employees_ManagerID FOREIGN KEY (ManagerID) )VALUES (1000,N"Ivanov I.I.","19550219",2,1,NULL), (1001,N"Petrov P.P.","19831203",3,3,1003), (1002 ,N"Sidorov S.S.","19760607",1,2,1000), (1003,N"Andreev A.A. ","19820417",4,3,1000)
We saw that it was done with ID 3 from the Departments table:

DELETE Departments WHERE ID=3
Looking at the data in the Employees table:

SELECT * FROM Employees

ID	Name	Birthday	Email	PositionID	DepartmentID	ManagerID
1000	Ivanov I.I.	1955-02-19	NULL	2	1	NULL
1002	Sidorov S.S.	1976-06-07	NULL	1	2	1000

Like Bachimo, given the 3rd table of Employees, it just so happened.

The ON UPDATE CASCADE option behaves in the same way, but only when the value of the ID is updated. For example, if we change the ID of the plant in the posad checklist, then in which case the DepartmentID of the Employees table will be updated with the new ID value, as we set in the checklist. Ale, in this vipadka tse demonstrate not to see, because the ID column in the Departments table should have the IDENTITY option, so as not to allow us to attack the ensuing request (change the ID of the entry 3 to 30):

UPDATE Departments SET ID=30 WHERE ID=3
Golovne zrozumit the essence of tsikh 2 options ON DELETE CASCADE and ON UPDATE CASCADE. I zastosovuyu tsі optsії even seldom and I recommend to think well, how to get into a feasible obezhennya, tk. with an unfamiliar record from the table of the report, it can lead to great problems and create a Lantzugian reaction.

Note 3:

Allowed to add/change IDENTITY value SET IDENTITY_INSERT Departments ON INSERT Departments(ID,Name) VALUES(3,N"IT") -- prevent adding/changing IDENTITY value SET
Let's clean up the Employees table again with the help of the TRUNCATE TABLE command:

TRUNCATE TABLE Employees
I repeat it again in this data, revisiting the front INSERT command:

INSERT Employees (ID,Name,Birthday,PositionID,DepartmentID,ManagerID)VALUES (1000,N"Ivanov I.I.","19550219",2,1,NULL), (1001,N"Petrov P.P." ,"19831203",3,3,1003), (1002,N"Sidoriv S.S.","19760607",1,2,1000), (1003,N"Andreev A.A.","19820417" ,4,3,1000)

Probably

At the moment, a few more DDL commands have come to our knowledge:

• Adding IDENTITY to the field - allows the field to be automatically populated (field-name) for the table;
• ALTER TABLE table_name ADD change_fields_with_characteristics – allows you to add new fields to the table;
• ALTER TABLE table_name DROP COLUMN list_of_fields - allows you to see fields from tables;
• ALTER TABLE table_name ADD CONSTRAINT im'ya_obezhennya FOREIGN KEY(fields) REFERENCES doc_table(fields) – allows you to assign a link between a table and a doc table.

Other exchanges – UNIQUE, DEFAULT, CHECK

For the help of UNIQUE watering, you can say what the value for the skin line in a given field or in a set of waterings can be unique. In the case of the Employees table, you can also place a boundary on the Email field. Only in advance remember Email values, as if they were not assigned yet:

UPDATE Employees SET Email=" [email protected]"WHERE ID=1000 UPDATE Employees SET Email=" [email protected]" WHERE ID=1001 UPDATE Employees SET Email=" [email protected]"WHERE ID=1002 UPDATE Employees SET Email=" [email protected] WHERE ID=1003
And now you can put on the field of obmezhennia-uniqueness:

ALTER TABLE Employees ADD CONSTRAINT UQ_Employees_Email UNIQUE(Email)
Now the correspondent cannot enter the same E-Mail with a number of spivrobitniks.

The exchange of uniqueness is called like this - first of all, the prefix “UQ_”, gave the name of the table and after the sign of the subdivision of the name of the field, as if the given exchange is superimposed.

It seems that the combination of waterings is unique in the rows of rows in the tables, but it is possible to redeem them through whom:

ALTER TABLE table_name ADD CONSTRAINT exchange_name UNIQUE(field1,field2,…)
For additional help adding to the DEFAULT box field, we can set the value for the lock, so that it will be presented in the dropdown, so that when inserting a new record, the field will not be relisted in the list of fields of the INSERT command. You can install the same exchange without intermediary when folding the tables.

Let's add a new field "Date to Received" to the Employees table and name it HireDate and let's say that the value for locking this field will be the current date:

ALTER TABLE Employees ADD HireDate date NOT NULL DEFAULT SYSDATETIME()
If the HireDate already exists, then you can tweak the following syntax:

ALTER TABLE Employees ADD DEFAULT SYSDATETIME() FOR HireDate
Here I did not enter the name of the obezhennya, because in vipadku DEFAULT, I had an idea that it is not so critical. But if you work kindly, then, I guess, you don’t need to line up and ask a normal name. Fight like this:

ALTER TABLE Employees ADD CONSTRAINT DF_Employees_HireDate DEFAULT SYSDATETIME() FOR HireDate
If there was no sign of this earlier, then when you add it to the skin record, a more accurate date will be inserted in the HireDate field.

When adding a new entry, the current date will also be inserted automatically, but we cannot explicitly set that. not shown in the list of stovptsіv. It is shown on the application, without specifying the HireDate field in the translation, the value that is added:

INSERT Employees(ID,Name,Email)VALUES(1004,N"Sergeev S.S."," [email protected]")
Let's see what happened:

SELECT * FROM Employees

ID	Name	Birthday	Email	PositionID	DepartmentID	ManagerID	HireDate
1000	Ivanov I.I.	1955-02-19	[email protected]	2	1	NULL	2015-04-08
1001	Petrov P.P.	1983-12-03	[email protected]	3	4	1003	2015-04-08
1002	Sidorov S.S.	1976-06-07	[email protected]	1	2	1000	2015-04-08
1003	Andreev A.A.	1982-04-17	[email protected]	4	3	1000	2015-04-08
1004	Sergeev S.S.	NULL	[email protected]	NULL	NULL	NULL	2015-04-08

The CHECK conversion is checked in that case, if it is necessary to change the value to be inserted into the field. For example, we put an exchange on the personnel number field, like mi є іdidentifier svrobіtnik (ID). For the help of such an exchange, let’s say that the personnel numbers and guilty mothers are from 1000 to 1999:

ALTER TABLE Employees ADD CONSTRAINT CK_Employees_ID CHECK(ID BETWEEN 1000 AND 1999)
The exchange is called so by itself, firstly, there is a prefix "CK_", then we will name the table and the name of the field, on the yak the boundary is imposed.

Let's try to insert an unacceptable entry for re-verification, which the exchange works (we may take the necessary pardon):

INSERT Employees(ID,Email) VALUES(2000," [email protected]")
And now we change the value that is inserted by 1500 and change the record to be inserted:

INSERT Employees(ID,Email) VALUES(1500," [email protected]")
You can also create a UNIQUE and CHECK exchange without specifying a name:

ALTER TABLE Employees ADD UNIQUE (Email) ALTER TABLE Employees ADD CHECK (ID BETWEEN 1000 AND 1999)
But it’s still good practice and it’s better to put it in front of a clear view, because so that we can find out later, what will be more important, it will be necessary to show the object and marvel, for which we will answer.

With a good name, a lot of information about the exchange can be recognized without intermediary on this name.

I, apparently, all the exchanges can be created at once when the tables are combined, but it is still not possible. Let's see the table:

DROP TABLE Employees
І it is possible to rebuild її z usіma with the created exchanges with one command CREATE TABLE:

CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30), Birthday date, Email nvarchar(30), PositionID int, DepartmentID int, HireDate date NOT NULL DEFAULT SYSDATETIME(), -- for DEFAULT i z(ID), CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERENCES Departments(ID), CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(ID), CONSTRAINT UQ_Employees_Email UNIQUE (Email), CONSTRAINT CK_Employees_ID CHECK (ID BETWEEN 1000 AND 1999))

INSERT Employees (ID,Name,Birthday,Email,PositionID,DepartmentID)VALUES (1000,N"Ivanov I.I.","19550219"," [email protected]",2,1), (1001,N"Petrov P.P.","19831203"," [email protected]",3,3), (1002,N"Sidorov S.S.","19760607"," [email protected]",1,2), (1003,N"Andreev A.A.","19820417"," [email protected]",4,3)

Trochs about indexes, which are created when the borders of PRIMARY KEY and UNIQUE are combined

As you can see on the screenshot, when the PRIMARY KEY and UNIQUE borders were combined, indexes with the same names (PK_Employees and UQ_Employees_Email) were automatically created. For locking, the index for the primary key is created as CLUSTERED, and the index is resolved as NONCLUSTERED. Varto say that the understanding of the clustered index is not in the us DBMS. The table may have more than one clustered (CLUSTERED) index. CLUSTERED - means that the records of the table will be sorted by this index, so you can say that this index can have direct access to all table data. The head index of the table. How to say rougher, price index, bolting to the table. Cluster index - this is an even harder task, which can help with the optimization of drinking, for now, just remember it. If we want to say that the clustered victorist index is not in the primary key, but for a different index, then when the primary keys are combined with the guilty one, specify the NONCLUSTERED option:

ALTER TABLE table_name ADD CONSTRAINT exchange_name PRIMARY KEY NONCLUSTERED(field1,field2,…)
For example, the exchange index PK_Employees is non-clustered, and the exchange index UQ_Employees_Email is clustered. We have seen before us the following obezhennya:

ALTER TABLE Employees DROP CONSTRAINT PK_Employees ALTER TABLE Employees DROP CONSTRAINT UQ_Employees_Email
And now we create them with the CLUSTERED and NONCLUSTERED options:

ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY NONCLUSTERED (ID) ALTER TABLE Employees ADD CONSTRAINT UQ_Employees_Email UNIQUE CLUSTERED (Email)
Now, having selected the selection from the Employees tables, we see that the records were sorted behind the clustered index UQ_Employees_Email:

SELECT * FROM Employees

ID	Name	Birthday	Email	PositionID	DepartmentID	HireDate
1003	Andreev A.A.	1982-04-17	[email protected]	4	3	2015-04-08
1000	Ivanov I.I.	1955-02-19	[email protected]	2	1	2015-04-08
1001	Petrov P.P.	1983-12-03	[email protected]	3	3	2015-04-08
1002	Sidorov S.S.	1976-06-07	[email protected]	1	2	2015-04-08

Before that, if the clustered index was PK_Employees index, the standard records were sorted by the ID field.

But in this case, it’s more than a butt, which shows the essence of the cluster index, because above all before the Employees table, you will be asked to enter the ID field and in some cases, it may be that you yourself act as a doc.

For dovіdnikіv call dotsіlno, schob cluster index of promptings by the primary key, tk. in requests, we often ask for the identifier of the driver for otrimanna, for example, naming (Plant, Viddilu). Here we guess, about what I wrote earlier, that the cluster index can have direct access to row tables, and the stars are clear, that we can take the value of any table without additional data.

Clustering index is likely to stagnate before watering, which is the most common choice.

Inodes in tables create a key by a surrogate field, the y-axis of that column is used to change the value of the CLUSTERED index option for the surrogate primary key and specify the NONCLUSTERED option when collating the surrogate primary key.

Probably

At this stage, we got to know about the usual types of exchanges, in their simplest way, they are created by a command like “ALTER TABLE im'ya_table_ADD CONSTRAINT im'ya_obrazhennia...”:

• PRIMARY KEY– primary key;
• FOREIGN KEY- Nalashtuvannya zv'yazkіv i control tіl_snostі data;
• UNIQUE- allows you to create uniqueness;
• CHECK- allows you to correct the correctness of data entry;
• DEFAULT- allows you to set the value for the lock;
• Also, note that all the facilities can be seen by victorious command “ ALTER TABLE table_name DROP CONSTRAINT im'ya_obezhennya".

So we often bumped into those indexes and sorted out the concepts of cluster ( CLUSTERED) and non-cluster ( NONCLUSTERED) index.

Creation of independent indexes

Under self-sufficiency, here they are toiling on the indexes, as they are created for the exchange of PRIMARY KEY and UNIQUE.

Indexes on the field or fields can be created with an offensive command:

CREATE INDEX IDX_Employees_Name ON Employees(Name)
Also here you can specify the options CLUSTERED, NONCLUSTERED, UNIQUE, and you can also specify the direct sorting of the skin-enclosed field ASC (by locking) or DESC:

CREATE UNIQUE NONCLUSTERED INDEX UQ_Employees_EmailDesc ON Employees(Email DESC)
Under the hour of creation of a non-clustered index, the NONCLUSTERED option can be allowed, because won't work on uvazі for umovchannyam, here won't be shown simply, to specify the position of the option CLUSTERED and NONCLUSTERED in the command.

You can view the index with the offensive command:

DROP INDEX IDX_Employees_Name ON Employees
Simple indexes can be created in the context of the CREATE TABLE command, just like an index.

For example, I can see the table again:

DROP TABLE Employees
І it is resolvable її with the creation of exchanges and indexes with one CREATE TABLE command:

CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30), Birthday date, Email nvarchar(30), PositionID int, DepartmentID int, HireDate date NOT NULL CONSTRAINT DF_Employees_HireDATE DEFAULT ), CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERENCES Departments(ID ), CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(ID), CONSTRAINT FK_Employees_ManagerID FOREIGN KEY (ManagerID) REFERENCES Employees(ID), CONSTRAINT UQ_Employees_Email UNIQUE(Email), CONSTRAINT CK_Employees_ID CHECK(ID BETWEEN 1000 AND 1999_DXName), ))
Nasamkіnets is inserted into the table of our spіvrobіtnikіv:

INSERT Employees (ID,Name,Birthday,Email,PositionID,DepartmentID,ManagerID)VALUES (1000,N"Ivanov I.I.","19550219"," [email protected]",2,1,NULL), (1001,N"Petrov P.P.","19831203"," [email protected]",3,3,1003), (1002,N"Sidoriv S.S.","19760607"," [email protected]",1,2,1000), (1003,N"Andreev A.A.","19820417"," [email protected]",4,3,1000)
Additional varto indicate that before the non-cluster index, you can include the value after the additional entry of їх in INCLUDE. Tobto. in this case, INCLUDE-index is supposed to be a clustered index, but now it’s not the index of attachments to the table, but the necessary values ​​are attached to the index. Obviously, such indexes can also increase the productivity of drinking per selection (SELECT), since all the fields are listed in the index, then you may not need to look up the table in the index. Ale ce naturally contributes to the index, because The values ​​of the transferred fields are duplicated in the index.

Virizka from MSDN. General command syntax for collating indexes

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name ON