Sql alter table syntax. Syntax of the alter table statement. Examples of the ALTER command in SQL

© 2014 site

Layers are the basis of Photoshop. Any, no matter how serious, manipulation of an image usually involves the use of layers and masks. Without understanding the concept of layering, you simply won't be able to truly edit your photos in Adobe Photoshop, and even if you can, the process will be painful and unnatural.

What are layers?

Layers in Adobe Photoshop can be thought of as sheets of transparent film stacked on top of each other. Any image can be applied to each of the sheets, so that the images lying on top overlap the images below them. Image-free areas of the sheets remain transparent and the underlying layers are visible through them. In general, if you look at the stack from above, you get a single complex image made up of complementary overlapping elements of individual layers.

Layers are needed so that you can create one from several images, and also so that in a multilayer image you can edit each layer independently of the other layers.

Working in Photoshop, we repeatedly create new layers, glue existing ones, duplicate, move, change the order of layers and their transparency, in a word, we do with layers everything that may be required when editing a photo.

Obviously, any file opened or created in Adobe Photoshop contains at least one layer. Usually this layer is called Background, i.e. background or backdrop.

Technically, many actions in Adobe Photoshop can be performed directly with the Background layer, without resorting to additional layers, but even in such situations, I still prefer to create a copy of the main layer and work with the copy, keeping the source intact, and only if the result is completely me okay, I allow myself to merge the layers together. This approach is more convenient and safer. And why, in fact, turn Photoshop into Paint?

Layers palette

The Layers palette is usually located at the bottom left. If you don't see it, press F7.

Let's look at the main elements of the layers palette.

Each layer has a small colored icon. To the right of it is the name of the layer. By default, new layers are given standard names (Layer 1, Layer 2, etc.), but you can always rename them to your liking by double-clicking on the layer name.

Next to the color layer icon there can be a black and white icon layer masks, which is responsible for which areas of this layer should be shown and which should be hidden. If there is no layer mask, the entire layer is visible. The layer mask is the most important tool when working with layers, and therefore we will return to this issue later and talk about masks.

To the left of the layer icon is an eye icon, symbolizing layer visibility. Click on it and the eye will disappear and the layer will become invisible.

To make a layer active, just click on it with the mouse. To select multiple layers, use the Ctrl/Cmd or Shift keys.

To create a copy of the active layer, press Ctrl/Cmd+J. To merge selected layers, press Ctrl/Cmd+E. If only one layer is selected, it will be merged with the underlying layer. Ctrl/Cmd+Shift+E merges all currently visible layers. To merge all the layers of the document together, go to the Layers menu and select Flatten Image.

Layers can be dragged with the mouse and, thus, their relative positions can be changed. The keyboard shortcuts Ctrl/Cmd+] and Ctrl/Cmd+[ move active layer one position up or down, respectively.

At the top of the layers palette are the following settings:

Layer filtering allows you to display in the palette only those layers that meet certain criteria and hide all others. You can filter layers by type, by name, by styles, etc.

By using Lock layer You can partially or completely prohibit editing a specific layer.

To the right are the settings windows Opacity And Fill responsible for the degree layer opacity. The default values ​​are 100%, i.e. The layer is completely opaque and visible in all its glory. 50% will mean that the layer is half transparent and the lower layers are visible through it. At 0% the layer becomes completely transparent and invisible. There is only one difference between Opacity and Fill: Opacity adjusts the opacity of the layer along with all the styles and special effects applied to it, while Fill only affects the pixels of the layer itself, without affecting the styles.

The Background layer differs from other layers in that its opacity is always 100% and cannot be adjusted. In addition, Background is permanently protected from movement. The blend mode for Background is always Normal, since there are no other layers underneath it and there is simply nothing for it to blend onto.

There are seven buttons at the bottom of the Layers palette:

Link layers. Links (but does not merge) selected layers. Linked layers remain independent layers, but when you try to move them, they move as a single unit.

Add a layer style. Allows you to apply various special effects to the layer, such as shadow, glow, outline, etc. Styles are rarely used when processing photographs and are mainly used in graphic design.

Add layer mask. Adds a mask to the selected layer. By default, the mask is white, i.e. the contents of the layer are fully visible. See below for more information on masks.

Create new fill or adjustment layer. Creates an adjustment layer or fill layer for non-destructive editing. See below for more information on adjustment layers.

Create new group(Create a new group). Creates a new empty group. To combine existing layers into a group, select them and press Ctrl/Cmd+G. To disband a group, press Ctrl/Cmd+Shift+G. Groups are useful when a document contains many layers and there is a need to organize them somewhat. In addition, masks and styles applied to a group automatically affect all layers included in the group. Otherwise, groups are similar to linking layers.

Create a new layer. Creates a new layer. You can also use the keyboard shortcut Ctrl/Cmd+Shift+N. The new layer does not contain any image and is therefore invisible.

Delete layer. Deletes the active layer. You can use the Del button.

Why are masks needed?

A layer mask is needed to selectively and reversibly hide from view (in other words, mask) part of the pixels of a layer. For example, when working with HDR, I take several photographs of the same scene, taken at different exposures, then paste them into one file as separate layers, and use masks to specify which elements of each photograph should be present in the final image.

The mask is a black and white image the same size as any layer. The color of the mask encodes the transparency of the layer. White color means normal visibility of the layer, black color means its absolute transparency. Different shades of gray correspond to different degrees of transparency - the darker, the more transparent. Thus, if the mask contains white areas, the corresponding areas of the layer retain their original density, and where the mask is painted black, the image will become invisible, and the underlying layer will be visible through it. The areas marked in gray on the mask will be only partially transparent.

To see the entire mask, just hold down Alt/Option and click on the mask icon.

The mask created using the “Add Layer Mask” command is completely filled with white, i.e. leaves the layer completely visible. If you click on the mask icon and press Ctrl/Cmd+I, the mask will be inverted and painted black. The layer will completely disappear from view, i.e. will be disguised.

If you want to hide some part of the active layer, create a white mask, select it by clicking on it, take a black brush and paint over the areas you don't like - they will disappear, as if you had used an eraser. However, unlike an eraser, which actually erases part of a layer, a mask does not destroy the layer itself, but simply hides it. At any time you can take a white brush and restore any area of ​​the image. This approach is called non-destructive editing.

Often you need to apply an effect to only a small part of the image. In such cases, I duplicate the working layer (or create an adjustment layer), modify it in the way I need (for example, increasing the contrast, sharpening, shading or brightening the image), then hide this layer using a solid black mask, and then use a white brush to show the effect where necessary.

Of course, editing masks is not limited to just painting on them with a brush. You can use any tools. Very often you have to resort, for example, to a gradient fill, or create a mask based on an area selected according to the principle of a color or brightness range. Sometimes the template for a mask is one of color channels. In short, there are countless ways to create masks, and I won’t even try to list them all. Just keep in mind that, if you wish, you can create masks of very complex shapes if the artistic tasks in front of you require it. There are even special plugins for Photoshop (for example, MASK PANEL) that partially automate the creation and editing of complex masks.

Adjustment layers are needed for non-destructive image editing. Instead of applying curves, levels, or some other tool directly to the layer, you create a special adjustment layer and work with it. The adjustment layer itself does not contain any image, but rather represents instructions on how the program should modify the image underlying the adjustment layer. Advantage this method is that you can return to the adjustment layer many times and freely change its parameters without fear of ruining the image. The adjustment layer can be turned off, its opacity can be changed, and if desired, it can be completely removed without any consequences for the photo. It's also convenient to be able to copy an adjustment layer and paste it into another document, thus applying the same settings to several photos at once.

Adding a new adjustment layer is done through the special Adjustments palette, or through the corresponding button in the Layers palette, or through the Layer > New Adjustment Layer menu.

Each adjustment layer is automatically supplied with a mask that allows you to control the area of ​​influence of the adjustment layer. It's only natural that different areas of an edited photograph may require the intervention of different tools. With the help of several adjustment layers, limited by masks, this is quite doable. Adjustment layer masks are no different from regular masks and can be edited using standard methods.

When combining an adjustment layer with underlying layers, the correction made is rasterized, i.e. the changes are finally transferred to the real image and become irreversible. In this regard, you should merge layers only when you are completely satisfied with the result and do not plan further changes.

Thank you for your attention!

Vasily A.

Post scriptum

If you found the article useful and informative, you can kindly support the project by making a contribution to its development. If you didn’t like the article, but you have thoughts on how to make it better, your criticism will be accepted with no less gratitude.

Please remember that this article is subject to copyright. Reprinting and quoting are permissible provided there is a valid link to the source, and the text used must not be distorted or modified in any way.

This tutorial is something like a “stamp of my memory” in the SQL language (DDL, DML), i.e. this is information that has accumulated along the way professional activity and is constantly stored in my head. This is a sufficient minimum for me, which is used most often when working with databases. If there is a need to use more complete SQL constructs, then I usually turn to the MSDN library located on the Internet for help. In my opinion, it is very difficult to keep everything in your head, and there is no particular need for this. But knowing the basic structures is very useful, because... they are applicable in almost the same form in many relational databases, such as Oracle, MySQL, Firebird. The differences are mainly in the data types, which may differ in detail. Basic designs SQL language not so many, and with constant practice they are quickly memorized. For example, to create objects (tables, constraints, indexes, etc.) it is enough to have at hand text editor environment (IDE) for working with a database, and there is no need to study visual tools tailored to work with a specific type of database (MS SQL, Oracle, MySQL, Firebird, ...). This is also convenient because all the text is in front of your eyes, and you don’t need to run through numerous tabs in order to create, for example, an index or constraint. When constantly working with a database, creating, changing, and especially re-creating an object using scripts is many times faster than if you do it in visual mode. Also in script mode (and, accordingly, with due care), it is easier to set and control the rules for naming objects (my subjective opinion). In addition, scripts are convenient to use when changes made in one database (for example, test) need to be transferred in the same form to another database (productive).

The SQL language is divided into several parts, here I will look at the 2 most important parts:

• DML – Data Manipulation Language, which contains the following constructs:
  • SELECT – data selection
  • INSERT – inserting new data
  • UPDATE – data update
  • DELETE – deleting data
  • MERGE – data merging

Because I am a practitioner, as such there will be little theory in this textbook, and all constructions will be explained in practical examples. In addition, I believe that a programming language, and especially SQL, can only be mastered through practice, by experiencing it yourself and understanding what happens when you execute this or that construct.

This textbook was created according to the Step by Step principle, i.e. you need to read it sequentially and preferably immediately follow the examples. But if along the way you need to learn about a certain command in more detail, then use a specific search on the Internet, for example, in the MSDN library.

When writing this tutorial, the MS database was used SQL Server version 2014, I used MS SQL Server Management Studio (SSMS) to execute the scripts.

Briefly about MS SQL Server Management Studio (SSMS)

SQL Server Management Studio (SSMS) is a utility for Microsoft SQL Server for configuring, managing and administering database components. This utility contains a script editor (which we will mainly use) and graphics program, which works with server objects and settings. The main tool of SQL Server Management Studio is Object Explorer, which allows the user to view, retrieve, and manage server objects. This text is partially borrowed from Wikipedia.

To create a new script editor, use the “New Query” button:

To change the current database you can use the drop-down list:

To execute a specific command (or group of commands), select it and press the “Execute” button or the “F5” key. If there is only one command currently in the editor, or you need to execute all commands, then you do not need to select anything.

After running scripts, especially those creating objects (tables, columns, indexes), to see the changes, use refresh from context menu by selecting the appropriate group (for example, Tables), the table itself, or the Columns group in it.

Actually, that's all we need to know to complete the examples given here. The rest of the SSMS utility is easy to learn on your own.

A little theory

A relational database (RDB, or hereinafter in the context simply DB) is a collection of tables interconnected. Roughly speaking, a database is a file in which data is stored in a structured form.

DBMS – Database Management System, i.e. this is a set of tools for working with a specific type of database (MS SQL, Oracle, MySQL, Firebird, ...).

Note
Because in life, in colloquial speech, we mostly say: “Oracle DB”, or even just “Oracle”, actually meaning “Oracle DBMS”, then in the context of this textbook the term DB will sometimes be used. From the context, I think it will be clear what exactly we are talking about.

A table is a collection of columns. Columns can also be called fields or columns; all these words will be used as synonyms expressing the same thing.

The table is the main object of the RDB; all RDB data is stored row by row in the columns of the table. Lines and records are also synonyms.

For each table, as well as its columns, names are specified by which they are subsequently accessed.
The object name (table name, column name, index name, etc.) in MS SQL can have a maximum length of 128 characters.

For reference– in the ORACLE database, object names can have a maximum length of 30 characters. Therefore, for a specific database, you need to develop your own rules for naming objects in order to meet the limit on the number of characters.

SQL is a language that allows you to query a database using a DBMS. In a specific DBMS, the SQL language may have a specific implementation (its own dialect).

DDL and DML are a subset of the SQL language:

• The DDL language is used to create and modify the database structure, i.e. to create/modify/delete tables and relationships.
• The DML language allows you to manipulate table data, i.e. with her lines. It allows you to select data from tables, add new data to tables, as well as update and delete existing data.

In SQL, you can use 2 types of comments (single-line and multi-line):

One line comment
And

/* multiline comment */

Actually, this will be enough for the theory.

DDL – Data Definition Language (data description language)

For example, consider a table with data about employees, in a form familiar to a person who is not a programmer:

In this case, the columns of the table have the following names: Personnel number, Full name, Date of birth, E-mail, Position, Department.

Each of these columns can be characterized by the type of data it contains:

• Personnel number – integer
• Full name – string
• Date of birth – date
• Email – string
• Position - string
• Department - line

Column type is a characteristic that indicates what type of data it can store. this column.

To begin with, it will be enough to remember only the following basic data types used in MS SQL:

Meaning	Notation in MS SQL	Description
Variable length string	varchar(N) And nvarchar(N)	Using the number N, we can specify the maximum possible string length for the corresponding column. For example, if we want to say that the value of the “Name” column can contain a maximum of 30 characters, then we need to set its type to nvarchar(30). The difference between varchar and nvarchar is that varchar allows you to store strings in ASCII format, where one character occupies 1 byte, and nvarchar stores strings in Unicode format, where each character occupies 2 bytes. The varchar type should only be used if you are 100% sure that the field will not need to store Unicode characters. For example, varchar can be used to store addresses Email, because they usually contain only ASCII characters.
Fixed length string	char(N) And nchar(N)	This type differs from a variable-length string in that if the length of the string is less than N characters, then it is always padded on the right to a length of N with spaces and stored in the database in this form, i.e. in the database it takes up exactly N characters (where one character takes up 1 byte for char and 2 bytes for nchar). In my practice, this type is very rarely used, and if it is used, it is used mainly in the char(1) format, i.e. when a field is defined by a single character.
Integer	int	This type allows us to use only integers in the column, both positive and negative. For reference (now this is not so relevant for us), the range of numbers that the int type allows is from -2,147,483,648 to 2,147,483,647. Usually this is the main type that is used to specify identifiers.
Real or real number	float	If we talk in simple language, then these are numbers that may contain a decimal point (comma).
date	date	If the column needs to store only the Date, which consists of three components: Day, Month and Year. For example, 02/15/2014 (February 15, 2014). This type can be used for the column “Date of admission”, “Date of birth”, etc., i.e. in cases where it is important for us to record only the date, or when the time component is not important to us and can be discarded or if it is not known.
Time	time	This type can be used if the column needs to store only time data, i.e. Hours, Minutes, Seconds and Milliseconds. For example, 17:38:31.3231603 For example, daily “Flight departure time”.
date and time	datetime	This type allows you to simultaneously save both Date and Time. For example, 02/15/2014 17:38:31.323 For example, this could be the date and time of an event.
Flag	bit	This type is convenient to use to store values ​​of the form “Yes”/“No”, where “Yes” will be stored as 1, and “No” will be stored as 0.

Also, the field value, if it is not prohibited, may not be specified; the NULL keyword is used for this purpose.

To run the examples, 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 delete the database with the command (you should be very careful with this command):

DROP DATABASE Test
In order to switch to our database, you can run the command:

USE Test
Alternatively, select the Test database from the drop-down list in the SSMS menu area. When working, I often use this method of switching between databases.

Now in our database we can create a table using the descriptions as they are, using spaces and Cyrillic characters:

CREATE TABLE [Employees]([Personnel number] int, [Name] nvarchar(30), [Date of birth] date, nvarchar(30), [Position] nvarchar(30), [Department] nvarchar(30))
In this case, we will have to enclose names in square brackets […].

But in the database, for greater convenience, it is better to specify all object names in Latin and not use spaces in names. In MS SQL, usually in this case each word begins with a capital letter, for example, for the “Personnel Number” field, we could set the name PersonnelNumber. You can also use numbers in the name, for example, PhoneNumber1.

On a note
In some DBMSs, the following naming format “PHONE_NUMBER” may be more preferable; for example, this format is often used in the ORACLE database. Naturally, when specifying a field name, it is desirable that it does not coincide with the keywords used in the DBMS.

For this reason, you can forget about the syntax with square brackets and delete the [Employees] table:

DROP TABLE [Employees]
For example, a table with employees can be named “Employees”, and its fields can be given the following names:

• ID – Personnel number (Employee ID)
• Name – full name
• Birthday – Date of birth
• Email – Email
• Position - Position
• Department - Department

Very often the word ID is used to name an identifier field.

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))
To specify required columns, you can use the NOT NULL option.

For an existing table, fields can be redefined using the following commands:

Update ID field ALTER TABLE Employees ALTER COLUMN ID int NOT NULL -- update Name field ALTER TABLE Employees ALTER COLUMN Name nvarchar(30) NOT NULL

On a note
The general concept of the SQL language remains the same for most DBMSs (at least, this is what I can judge from the DBMSs that I have worked with). The differences between DDL in different DBMSs mainly lie in the data types (not only their names may differ here, but also the details of their implementation), and the very specifics of the implementation of the SQL language may also differ slightly (i.e., the essence of the commands is the same, but there may be slight differences in dialect, alas, but there is no one standard). Having mastered the basics of SQL, you can easily switch from one DBMS to another, because... In this case, you will only need to understand the details of the implementation of commands in the new DBMS, i.e. in most cases, simply drawing an analogy will suffice.

Creating a table CREATE TABLE Employees(ID int, -- in ORACLE the int type is the equivalent (wrapper) for number(38) Name nvarchar2(30), -- nvarchar2 in ORACLE is equivalent to nvarchar in MS SQL Birthday date, Email nvarchar2(30) , Position nvarchar2(30), Department nvarchar2(30)); -- updating the ID and Name fields (here MODIFY(...) is used instead of ALTER COLUMN) ALTER TABLE Employees MODIFY(ID int NOT NULL,Name nvarchar2(30) NOT NULL); -- adding PK (in this case the construction looks the same as in MS SQL, it will be shown below) ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY(ID);
For ORACLE there are differences in terms of implementation of the varchar2 type; its encoding depends on the database settings and the text can be saved, for example, in UTF-8 encoding. In addition, the field length in ORACLE can be specified both in bytes and in characters; for this, additional options BYTE and CHAR are used, which are specified after the field length, for example:

NAME varchar2(30 BYTE) -- the field capacity will be 30 bytes NAME varchar2(30 CHAR) -- the field capacity will be 30 characters
Which option will be used by default BYTE or CHAR, in the case of simply specifying the varchar2(30) type in ORACLE, depends on the database settings, and it can sometimes be set in the IDE settings. In general, sometimes you can easily get confused, so in the case of ORACLE, if the varchar2 type is used (and this is sometimes justified here, for example, when using UTF-8 encoding), I prefer to explicitly write CHAR (since it is usually more convenient to calculate the length of the string in characters ).

But in this case, if the table already has some data, then for successful implementation commands, it is necessary that the ID and Name fields in all rows of the table be filled in. Let's demonstrate this with an example: insert data into the table in the ID, Position and Department fields; this can be done with 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")
In this case, the INSERT command will also generate an error, because When inserting, we did not specify the value of the required Name field.
If we already had this data in the original table, then the command “ALTER TABLE Employees ALTER COLUMN ID int NOT NULL” would be executed successfully, and the command “ALTER TABLE Employees ALTER COLUMN Name int NOT NULL” would produce an error message, that the Name field contains NULL (unspecified) values.

Let's add values ​​for the Name field and fill in the data again:

The NOT NULL option can also be used directly when creating a new table, i.e. in the context of the CREATE TABLE command.

First, delete the table using the command:

DROP TABLE Employees
Now let’s create a table with the required ID and Name columns:

CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30) NOT NULL, Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30))
You can also write NULL after the column name, which will mean that NULL values ​​(not specified) will be allowed in it, but this is not necessary, since this characteristic is implied by default.

If, on the contrary, you want to make an existing column optional, then use the following command syntax:

ALTER TABLE Employees ALTER COLUMN Name nvarchar(30) NULL
Or simply:

ALTER TABLE Employees ALTER COLUMN Name nvarchar(30)
With this command we can also change the field type to another compatible type, or change its length. For example, let's expand the Name field to 50 characters:

ALTER TABLE Employees ALTER COLUMN Name nvarchar(50)

Primary key

When creating a table, it is desirable that it have a unique column or a set of columns that is unique for each of its rows - a record can be uniquely identified by this unique value. This value is called the table's primary key. For our Employees table, such a unique value could be the ID column (which contains the “Employee Personnel Number” - let in our case given value unique for each employee and cannot be repeated).

You can create a primary key to an existing table using the command:

ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY(ID)
Where "PK_Employees" is the name of the constraint responsible for the primary key. Typically, the primary key is named using the prefix “PK_” followed by the table name.

If the primary key consists of several fields, then these fields must be listed in parentheses, separated by commas:

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY(field1,field2,…)
It is worth noting that in MS SQL, all fields that are included in the primary key must have the NOT NULL characteristic.

The primary key can also be determined directly when creating a table, i.e. in the context of the CREATE TABLE command. Let's delete the table:

DROP TABLE Employees
And then we'll create it using the following syntax:

CREATE TABLE Employees(ID int NOT NULL, Name nvarchar(30) NOT NULL, Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30), CONSTRAINT PK_Employees PRIMARY KEY(ID) -- describe PK after all fields as a limitation)
After creation, fill the table with data:

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"Sidorov S.S."), (1003,N"Senior Programmer",N"IT",N"Andreev A. A.")
If the primary key in a table consists of only the values ​​of one column, then you can use the following syntax:

CREATE TABLE Employees(ID int NOT NULL CONSTRAINT PK_Employees PRIMARY KEY, -- specify as a characteristic of the field Name nvarchar(30) NOT NULL, Birthday date, Email nvarchar(30), Position nvarchar(30), Department nvarchar(30))
In fact, you don’t have to specify the name of the constraint, in which case it will be assigned a system name (like “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))
Or:

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))
But I would recommend that for permanent tables you always explicitly set the name of the constraint, because With an explicitly specified and understandable name, it will be easier to manipulate it later; for example, you can delete it:

ALTER TABLE Employees DROP CONSTRAINT PK_Employees
But such short syntax, without specifying constraint names, is convenient to use when creating temporary database tables (temporary table name begins with # or ##), which will be deleted after use.

Let's summarize

So far we have looked at the following commands:

• CREATE TABLE table_name (listing of fields and their types, restrictions) – used to create a new table in the current database;
• DROP TABLE table_name – used to delete a table from the current database;
• ALTER TABLE table_name ALTER COLUMN column_name... – used to update the column type or change its settings (for example, to set the NULL or NOT NULL characteristic);
• ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY(field1, field2,...) – adding a primary key to an existing table;
• ALTER TABLE table_name DROP CONSTRAINT constraint_name – removes a constraint from the table.

A little about temporary tables

Extract from MSDN. There are two types of temporary tables in MS SQL Server: local (#) and global (##). Local temporary tables are visible only to their creators until the connection session to the SQL Server instance ends when they are first created. Local temporary tables are automatically deleted after a user disconnects from the instance of SQL Server. Global temporary tables are visible to all users during any connection sessions after those tables are created, and are deleted when all users referencing those tables disconnect from the instance of SQL Server.

Temporary tables are created in the tempdb system database, i.e. By creating them we do not clog the main database; otherwise, temporary tables are completely identical to regular tables; they can also be deleted using the DROP TABLE command. Local (#) temporary tables are more commonly used.

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

CREATE TABLE #Temp(ID int, Name nvarchar(30))
Since a temporary table in MS SQL is similar to a regular table, it can also be deleted using the DROP TABLE command:

DROP TABLE #Temp

You can also create a temporary table (like a regular table) and immediately fill it with the data returned by the query using the SELECT ... INTO syntax:

SELECT ID,Name INTO #Temp FROM Employees

On a note
The implementation of temporary tables may differ in different DBMSs. For example, in the ORACLE and Firebird DBMS, the structure of temporary tables must be determined in advance by the CREATE GLOBAL TEMPORARY TABLE command, indicating the specifics of storing data in it, then the user sees it among the main tables and works with it as with a regular table.

Database normalization – splitting into subtables (directories) and identifying connections

Our current Employees table has the disadvantage that in the Position and Department fields the user can enter any text, which is primarily fraught with errors, since for one employee he can simply indicate “IT” as the department, and for a second employee, for example , enter “IT department”, the third has “IT”. As a result, it will be unclear what the user meant, i.e. Are these employees employees of the same department, or did the user describe himself and these are 3 different departments? Moreover, in this case, we will not be able to correctly group the data for some report, where it may be necessary to show the number of employees by each department.

The second disadvantage is the volume of storage of this information and its duplication, i.e. For each employee, the full name of the department is indicated, which requires space in the database to store each character from the department name.

The third drawback is the difficulty of updating these fields if the name of a position changes, for example, if you need to rename the position “Programmer” to “Junior Programmer”. In this case, we will have to make changes to each row of the table whose Position is equal to “Programmer”.

To avoid these shortcomings, so-called database normalization is used - splitting it into subtables and reference tables. It is not necessary to go into the jungle of theory and study what normal forms are; it is enough to understand the essence of normalization.

Let's create 2 directory tables “Positions” and “Departments”, the first one will be called Positions, and the second one will be called 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 KEY, Name nvarchar(30 ) NOT NULL)
Note that here we used the new IDENTITY option, which says that the data in the ID column will be numbered automatically, starting from 1, in increments of 1, i.e. When adding new records, they will be sequentially assigned the values ​​1, 2, 3, etc. Such fields are usually called auto-incrementing. A table can only have one field defined with the IDENTITY property, and usually, but not necessarily, that field is the primary key for that table.

On a note
In different DBMSs, the implementation of fields with a counter can be done differently. In MySQL, for example, such a field is defined using the AUTO_INCREMENT option. In ORACLE and Firebird, this functionality could previously be emulated using SEQUENCE. But as far as I know, ORACLE has now added the GENERATED AS IDENTITY option.

Let's fill these tables automatically, based on the current data recorded in the Position and Department fields of the Employees table:

We fill the Name field of the Positions table with unique values ​​from the Position field of the Employees table INSERT Positions(Name) SELECT DISTINCT Position FROM Employees WHERE Position IS NOT NULL -- discard records for which the position is not specified
Let's do the same for the Departments table:

INSERT Departments(Name) SELECT DISTINCT Department FROM Employees WHERE Department IS NOT NULL
If we now open the Positions and Departments tables, we will see a numbered set of values ​​for the ID field:

SELECT * FROM Positions

SELECT * FROM Departments

These tables will now play the role of reference books for specifying positions and departments. We will now refer to job and department IDs. First of all, let's create new fields in the Employees table to store identifier data:

Add a field for position ID ALTER TABLE Employees ADD PositionID int -- add a field for department ID ALTER TABLE Employees ADD DepartmentID int
The type of reference fields must be the same as in directories, in this case it is int.

You can also add several fields to the table at once with one command, listing the fields separated by commas:

ALTER TABLE Employees ADD PositionID int, DepartmentID int
Now let's write links (reference restrictions - FOREIGN KEY) for these fields so that the user does not have the opportunity to write into these fields values ​​that are not among the ID values ​​​​found in the directories.

ALTER TABLE Employees ADD CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(ID)
And we'll do the same for the second field:

ALTER TABLE Employees ADD CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERENCES Departments(ID)
Now the user will be able to enter only ID values ​​from the corresponding directory in these fields. Accordingly, in order to use a new department or position, he will first have to add a new entry to the corresponding directory. Because Positions and departments are now stored in directories in one single copy, so to change the name, it is enough to change it only in the directory.

The name of a reference constraint is usually a composite name, consisting of the prefix "FK_", followed by the table name, and followed by an underscore, followed by the name of the field that refers to the reference table identifier.

An identifier (ID) is usually an internal value that is used only for relationships and what value is stored there is completely indifferent in most cases, so there is no need to try to get rid of holes in the sequence of numbers that arise while working with the table, for example, after deleting records from the directory.

ALTER TABLE table ADD CONSTRAINT constraint_name FOREIGN KEY(field1,field2,…) REFERENCES reference_table(field1,field2,…)
In this case, in the “reference_table” table, the primary key is represented by a combination of several fields (field1, field2,...).

Actually, now let’s update the PositionID and DepartmentID fields with ID values ​​from the directories. Let's use the DML UPDATE command for this purpose:

UPDATE e SET PositionID=(SELECT ID FROM Positions WHERE Name=e.Position), DepartmentID=(SELECT ID FROM Departments WHERE Name=e.Department) FROM Employees e
Let's see what happens by running the query:

SELECT * FROM Employees

That’s it, the PositionID and DepartmentID fields are filled with the identifiers corresponding to positions and departments; the Position and Department fields are no longer needed in the Employees table, you can delete 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

Those. We eventually got rid of storing redundant information. Now, based on the job and department numbers, we can unambiguously determine their names using the values ​​in the reference tables:

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 see all the objects created for a given table. From here you can perform various manipulations with these objects - for example, rename or delete objects.

It is also worth noting that the table can refer to itself, i.e. you can create a recursive link. For example, let's add another field ManagerID to our table with employees, which will indicate the employee to whom this employee reports. Let's create a field:

ALTER TABLE Employees ADD ManagerID int
This field allows a NULL value; the field will be empty if, for example, there are no superiors over the employee.

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

ALTER TABLE Employees ADD CONSTRAINT FK_Employees_ManagerID FOREIGN KEY (ManagerID) REFERENCES Employees(ID)
Let's now create a diagram and see how the relationships between our tables look on it:

As a result, we should see the following picture (the Employees table is connected to the Positions and Depertments tables, and also refers to itself):

Finally, it is worth saying that reference keys can include additional options ON DELETE CASCADE and ON UPDATE CASCADE, which indicate how to behave when deleting or updating a record that is referenced in the reference table. If these options are not specified, then we cannot change the ID in the directory table for a record that is referenced from another table, and we will also not be able to delete such a record from the directory until we delete all rows referencing this record or, Let’s update the references in these lines to a different value.

For example, let's recreate the table specifying the ON DELETE CASCADE option 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 PRIMARY KEY (ID), CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID ) REFERENCES Departments(ID) ON DELETE CASCADE, CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(ID), CONSTRAINT FK_Employees_ManagerID FOREIGN KEY (ManagerID) REFERENCES Employees(ID)) INSERT Employees (ID,Name,Birthday,PositionID,DepartmentID,Man agerID )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)
Let's delete the department with ID 3 from the Departments table:

DELETE Departments WHERE ID=3
Let's look 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

As you can see, the data for department 3 from the Employees table was also deleted.

The ON UPDATE CASCADE option behaves similarly, but it is effective when updating the ID value in the directory. For example, if we change the ID of a position in the position directory, then in this case the DepartmentID in the Employees table will be updated to the new ID value that we set in the directory. But in this case it simply won’t be possible to demonstrate this, because the ID column in the Departments table has the IDENTITY option, which will not allow us to execute the following query (change department ID 3 to 30):

UPDATE Departments SET ID=30 WHERE ID=3
The main thing is to understand the essence of these 2 options ON DELETE CASCADE and ON UPDATE CASCADE. I use these options very rarely and recommend that you think carefully before specifying them in a reference constraint, because if you accidentally delete an entry from a directory table, this can lead to big problems and create a chain reaction.

Let's restore department 3:

We give permission to add/change IDENTITY value SET IDENTITY_INSERT Departments ON INSERT Departments(ID,Name) VALUES(3,N"IT") -- we prohibit adding/change IDENTITY value SET IDENTITY_INSERT Departments OFF
Let's completely clear the Employees table using the TRUNCATE TABLE command:

TRUNCATE TABLE Employees
And again we will reload the data into it using the previous 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"Sidorov S.S.","19760607",1,2,1000), (1003,N"Andreev A.A.","19820417" ,4,3,1000)

Let's summarize

At the moment, several more DDL commands have been added to our knowledge:

• Adding the IDENTITY property to a field – allows you to make this field an automatically populated field (counter field) for the table;
• ALTER TABLE table_name ADD list_of_fields_with_characteristics – allows you to add new fields to the table;
• ALTER TABLE table_name DROP COLUMN list_fields – allows you to remove fields from the table;
• ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY(fields) REFERENCES table_reference (fields) – allows you to define the relationship between the table and the reference table.

Other restrictions – UNIQUE, DEFAULT, CHECK

With help UNIQUE restrictions we can say that the value for each row in a given field or set of fields must be unique. In the case of the Employees table, we can impose such a constraint on the Email field. Just pre-fill Email with values ​​if they are not already defined:

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
Now you can impose a uniqueness constraint on this field:

ALTER TABLE Employees ADD CONSTRAINT UQ_Employees_Email UNIQUE(Email)
Now the user will not be able to enter the same E-Mail for several employees.

A unique constraint is usually named as follows - first comes the prefix “UQ_”, then the name of the table and after the underscore comes the name of the field on which this constraint is applied.

Accordingly, if a combination of fields must be unique in the context of table rows, then we list them separated by commas:

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE(field1,field2,…)
By adding a DEFAULT constraint to a field, we can specify a default value that will be substituted if, when inserting a new record, this field is not listed in the list of fields of the INSERT command. This restriction can be set directly when creating the table.

Let's add a new Hire Date field to the Employees table and call it HireDate and say that the default value is of this field will be the current date:

ALTER TABLE Employees ADD HireDate date NOT NULL DEFAULT SYSDATETIME()
Or if the HireDate column already exists, then the following syntax can be used:

ALTER TABLE Employees ADD DEFAULT SYSDATETIME() FOR HireDate
Here I did not specify the name of the constraint, because... in the case of DEFAULT, I have the opinion that this is not so critical. But if you do it in a good way, then I think you don’t need to be lazy and you should set a normal name. This is done as follows:

ALTER TABLE Employees ADD CONSTRAINT DF_Employees_HireDate DEFAULT SYSDATETIME() FOR HireDate
Since this column did not exist before, when it is added to each record, the current date value will be inserted into the HireDate field.

When adding a new entry, the current date will also be inserted automatically, of course, unless we explicitly set it, i.e. We will not indicate it in the list of columns. Let's show this with an example without specifying the HireDate field in the list of added values:

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 check constraint is used when it is necessary to check the values ​​inserted into the field. For example, let's impose this restriction on the personnel number field, which for us is an employee identifier (ID). Using this constraint, we say that personnel numbers must have a value from 1000 to 1999:

ALTER TABLE Employees ADD CONSTRAINT CK_Employees_ID CHECK(ID BETWEEN 1000 AND 1999)
The constraint is usually named the same way, first with the prefix “CK_”, then the name of the table and the name of the field on which this constraint is imposed.

Let's try to insert an invalid record to check that the constraint works (we should get the corresponding error):

INSERT Employees(ID,Email) VALUES(2000," [email protected]")
Now let’s change the inserted value to 1500 and make sure that the record is inserted:

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

ALTER TABLE Employees ADD UNIQUE(Email) ALTER TABLE Employees ADD CHECK(ID BETWEEN 1000 AND 1999)
But this is not a very good practice and it is better to specify the name of the constraint explicitly, because To figure it out later, which will be more difficult, you will need to open the object and look at what it is responsible for.

With a good name, a lot of information about the constraint can be learned directly from its name.

And, accordingly, all these restrictions can be created immediately when creating a table, if it does not exist yet. Let's delete the table:

DROP TABLE Employees
And we will recreate it with all the created restrictions 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 DEFAULT SYSDATETIME(), -- for DEFAULT I will make an exception CONSTRAINT PK_Employees PRIMARY KEY (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 BETWE EN 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)

A little about the indexes created when creating PRIMARY KEY and UNIQUE constraints

As you can see in the screenshot above, when creating the PRIMARY KEY and UNIQUE constraints, indexes with the same names (PK_Employees and UQ_Employees_Email) were automatically created. By default, the index for the primary key is created as CLUSTERED, and for all other indexes as NONCLUSTERED. It is worth saying that the concept of a cluster index is not available in all DBMSs. A table can only have one CLUSTERED index. CLUSTERED – means that the table records will be sorted by this index, we can also say that this index has direct access to all data in the table. This is the main index of the table, so to speak. To put it even more roughly, this is an index attached to a table. A clustered index is a very powerful tool that can help with query optimization, but let's just remember this for now. If we want to tell the clustered index to be used not on the primary key, but on another index, then when creating the primary key we must specify the NONCLUSTERED option:

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY NONCLUSTERED(field1,field2,…)
For example, let's make the constraint index PK_Employees non-clustered, and the constraint index UQ_Employees_Email clustered. First of all, let's remove these restrictions:

ALTER TABLE Employees DROP CONSTRAINT PK_Employees ALTER TABLE Employees DROP CONSTRAINT UQ_Employees_Email
Now let's 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, by selecting from the Employees table, we will see that the records are sorted by the UQ_Employees_Email clustered index:

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

Previously, when the clustered index was the PK_Employees index, records were sorted by the ID field by default.

But in this case, this is just an example that shows the essence of a clustered index, because Most likely, queries will be made to the Employees table using the ID field and in some cases, perhaps, it itself will act as a directory.

For directories, it is usually advisable for the clustered index to be built on the primary key, because in requests we often refer to the directory identifier to obtain, for example, the name (Position, Department). Let us remember here what I wrote above, that a clustered index has direct access to table rows, and it follows that we can get the value of any column without additional overhead.

It is advantageous to apply a cluster index to fields that are sampled most frequently.

Sometimes tables are created with a key based on a surrogate field; in this case, it can be useful to save the CLUSTERED index option for a more suitable index and specify the NONCLUSTERED option when creating a surrogate primary key.

Let's summarize

On at this stage we became acquainted with all types of restrictions, in their very in simple form, which are created by a command like “ALTER TABLE table_name ADD CONSTRAINT constraint_name …”:

• PRIMARY KEY– primary key;
• FOREIGN KEY– setting up connections and monitoring referential integrity of data;
• UNIQUE– allows you to create uniqueness;
• CHECK– allows you to ensure the correctness of the entered data;
• DEFAULT– allows you to set a default value;
• It is also worth noting that all restrictions can be removed using the command “ ALTER TABLE table_name DROP CONSTRAINT constraint_name".

We also partially touched on the topic of indexes and examined the concept of cluster ( CLUSTERED) and non-clustered ( NONCLUSTERED) index.

Creating standalone indexes

By independent here we mean indexes that are not created under the PRIMARY KEY or UNIQUE constraint.

Indexes on a field or fields can be created with the following 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 sorting direction of each individual field ASC (default) or DESC:

CREATE UNIQUE NONCLUSTERED INDEX UQ_Employees_EmailDesc ON Employees(Email DESC)
When creating a non-clustered index, the NONCLUSTERED option can be omitted, because it is implied by default and is shown here simply to indicate the position of the CLUSTERED or NONCLUSTERED option in the command.

You can delete the index with the following command:

DROP INDEX IDX_Employees_Name ON Employees
Simple indexes, as well as constraints, can be created in the context of the CREATE TABLE command.

For example, let's delete the table again:

DROP TABLE Employees
And we will recreate it with all the created restrictions 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 SYSDATETIME(), ManagerID int, CONSTRAINT PK_Employees PRIMARY KEY (ID ), 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_Emplo yees_Email UNIQUE(Email), CONSTRAINT CK_Employees_ID CHECK(ID BETWEEN 1000 AND 1999), INDEX IDX_Employees_Name(Name))
Finally, let’s insert our employees into the table:

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"Sidorov S.S.","19760607"," [email protected]",1,2,1000), (1003,N"Andreev A.A.","19820417"," [email protected]",4,3,1000)
Additionally, it is worth noting that you can include values ​​in a non-clustered index by specifying them in INCLUDE. Those. in this case, the INCLUDE index will be somewhat reminiscent of a clustered index, only now the index is not attached to the table, but the necessary values ​​are attached to the index. Accordingly, such indexes can greatly improve the performance of selection queries (SELECT); if all the listed fields are in the index, then access to the table may not be needed at all. But this naturally increases the size of the index, because... the values ​​of the listed fields are duplicated in the index.

Extract from MSDN. General syntax commands to create indexes

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