An illustrated self-study guide to SQL for beginners. Category archives: Books on SQL The sql programming language for dummies

From the author: Have you been called a teapot? Well, this is fixable! Every samovar was once a teapot! Or was every professional once a samovar? No, again something is wrong! All in all, MySQL is for beginners.

Why dummies need MySQL

If you are seriously going to connect your life with the Internet, then at the very first steps in the "web" you will come across this DBMS. MySQL can be safely called the "all Internet" database management system. Not a single more or less serious resource can do without it, it is present in the admin panel of each hosting. And most of all popular CMS and even self-made engines are built with her participation.

In general, you cannot do without this platform. But to study it, you also need the right approach, the right tools, and most importantly, desire and patience. I hope you have plenty of the latter. And be prepared for the fact that your brains will boil, and steam will tumble out of your head, like from a real teapot

But MySQL is so hard for dummies only if you start learning it wrong. We will not make such a mistake, and we will begin our acquaintance with this technology from the very beginning.

Basic concepts

First, let's go through the basic concepts that we will be mentioning in this post:

Database (DB) is the main constituent unit of the DBMS. The database includes tables that consist of columns and records (rows). The cells formed at the intersection contain structured data of a certain type.

DBMS (database management system) - a set of all software modules for database administration.

SQL is a structured query language, with the help of which the developer "communicates" with the core (server) of the DBMS. Like any programming language, SQL has its own syntax, set of commands and operators, supported data types.

I think we have enough theoretical knowledge to begin with. We will “paint” the missing gaps in theory with practice. Now all that remains is to choose the correct software tool.

Finding the right tool

Having pretty much "rummaged" in the entire range of MySQL shells for beginners, I realized that these simply do not exist. All software products for DBMS administration require an already installed database server. In general, I decided once again not to reinvent the "scooter", and opted for the domestic Denwer package. You can download it on the official website.

It already includes all the components of the DBMS, allowing a beginner to start practical acquaintance with MySQL right after a simple and understandable installation. In addition, Denwer includes several other tools necessary for a novice developer: local server, PHP.

The first steps

I will not describe the installation process of the "gentlemen's" set, since everything happens automatically there. After launching the instalah, only have time to press the necessary keys. Just what you need in the dummy version of MySQL.

When the installation process is over, start the local server, wait a couple of seconds. After that, type localhost in the address bar of your browser.

On the page "Hurray, it's working!" follow one of the links in the picture. Then you will be taken to phpMyAdmin - a shell for database administration.

Clicking on the link http://downloads.mysql.com/docs/world.sql.zip, you will download a sample test database from the official MySQL website. Go to phpMyAdmin again, in the main menu at the top go to the "Import" tab. In the "Import to current" window in the first section ("File to be imported") set the value "Browse your computer".

In the explorer window, select the archive with the downloaded example database. Do not forget to click "Ok" at the bottom of the main window.

I advise you not to change the specified parameter values ​​for now. This can lead to incorrect display of the data of the imported source. If phpMyAdmin gives an error that it cannot recognize the database compression algorithm, then unzip it and repeat the entire import process from the beginning.

If everything went well, then the program message will appear at the top that the import was successful, and on the left in the DB list there is another one (word).

Let's look at its structure from the inside so that you can more clearly imagine what you will have to deal with.

Click on the name of MySQL Database for Beginners. A list of tables from which it consists will be displayed below it. Click on one of them. Then go to the top menu item "Structure". The main work area displays the structure of the table: all column names, data types, and all attributes.

This tutorial is something like a "stamp of my memory" for the SQL language (DDL, DML), i.e. this is information that has accumulated in the course of my 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 it becomes necessary 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 it is very useful to know the basic constructions, because they are applicable in almost the same form in many relational databases such as Oracle, MySQL, Firebird. The differences are mainly in data types, which may differ in detail. There are not so many basic constructs of the SQL language, and with constant practice they are quickly remembered. For example, to create objects (tables, constraints, indexes, etc.), it is enough to have at hand a text editor of the environment (IDE) for working with the database, and there is no need to study visual tools sharpened for working with a specific type of database (MS SQL , Oracle, MySQL, Firebird, ...). It is also convenient because all the text is in front of your eyes, and you do not need to run through numerous tabs in order to create, for example, an index or a constraint. With constant work with a database, creating, changing, and especially re-creating an object using scripts is many times faster than if it is done in visual mode. Also in the script mode (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, a test one) need to be transferred in the same form to another (productive) database.

The SQL language is divided into several parts, here I will discuss the 2 most important parts of it:

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

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

This tutorial is based on the Step by Step principle, i.e. it is necessary to read it sequentially and preferably immediately following the examples. But if along the way you have a need to learn more about a command in more detail, then use a specific search on the Internet, for example, in the MSDN library.

When writing this tutorial, the MS SQL Server database version 2014 was used, to execute the scripts I used 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 administering database components. This utility contains a script editor (which we will mainly use) and a graphical program that works with server objects and settings. The main tool in SQL Server Management Studio is Object Explorer, which allows the user to view, retrieve, and manipulate 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 a group of commands), select it and press the Execute button or the F5 key. If there is only one command in the editor at the moment, or you need to execute all commands, then you do not need to select anything.

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

Actually, this is all we need to know to execute the examples given here. The rest of the SSMS utility is easy to learn on your own.

A bit of theory

A relational database (RDB, or further in the context of just a database) is a collection of tables that are interconnected. Roughly speaking, a database is a file in which data is stored in a structured form.

DBMS - Management System for these Databases, i.e. it 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 tutorial the term DB will sometimes be used. From the context, I think it will be clear what it is about.

A table is a collection of columns. Columns can also be called fields or columns, all of these words will be used synonymously to express the same thing.

The table is the main object of the RDB, all RDB data is stored line by line in the columns of the table. Strings, records are also synonyms.

For each table, as well as for its columns, names are given, by which they are subsequently accessed.
An 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 keep within the limit on the number of characters.

SQL is a language that allows you to execute queries in a database using a DBMS. In a specific DBMS, the SQL language can 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 links.
• DML language allows manipulation of table data, i.e. with her lines. It allows you to select data from tables, add new data to tables, and update and delete existing data.

There are 2 types of comments that can be used in SQL (single-line and multi-line):

Single line comment
and

/ * multi-line comment * /

Actually, this will be enough for the theory.

DDL - Data Definition Language

For example, consider a table with data about employees, in the usual form for 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
• E-mail - string
• Position - string
• Department - string

Column type is a characteristic that indicates what kind of data a given column can store.

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

Meaning	MS SQL notation	Description
Variable length string	varchar (N) and nvarchar (N)	With the number N, we can specify the maximum possible row 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 it to the nvarchar (30) type. The difference between varchar and nvarchar is that varchar stores strings in ASCII format, where one character is 1 byte, and nvarchar stores strings in Unicode format, where each character is 2 bytes. The varchar type should only be used if you are 100% sure that the field does not need to store Unicode characters. For example, varchar can be used to store email addresses because they usually only contain ASCII characters.
Fixed length string	char (N) and nchar (N)	This type differs from a variable-length string in that if the string is less than N characters long, then it is always padded to the right to the length of N with spaces and stored in the database in this form, i.e. it occupies exactly N characters in the database (where one character occupies 1 byte for char and 2 bytes for nchar). In my practice, this type is very rarely used, and if it is used, then it is used mainly in the char (1) format, i.e. when the field is defined by one 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 set identifiers.
Real or real number	float	In simple terms, these are numbers in which a decimal point (comma) may be present.
date	date	If you need to store only the Date in the column, which consists of three components: Number, 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. in cases where it is important for us to fix 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 only time data needs to be stored in a column, i.e. Hours, Minutes, Seconds and Milliseconds. For example, 17:38: 31.3231603 For example, the daily "Flight Departure Time".
date and time	datetime	This type allows you to simultaneously save both Date and Time. For example, 15.02.2014 17:38: 31.323 For example, this can be the date and time of an event.
Flag	bit	This type is convenient for storing values ​​like "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, for this purpose the NULL keyword is used.

To run the examples, let's create a test base 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 bases.

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, [Full name] nvarchar (30), [Date of birth] date, nvarchar (30), [Position] nvarchar (30), [Department] nvarchar (30))
In this case, we have to enclose the names in square brackets [...].

But in the database, for greater convenience, it is better to specify all names of objects in the Latin alphabet and not use spaces in the 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 DBMS, the following naming format "PHONE_NUMBER" may be more preferable, for example, this format is often used in ORACLE DB. Naturally, when specifying the 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 square brackets syntax and delete the [Employees] table:

DROP TABLE [Employees]
For example, a table with employees can be named "Employees", and its fields can be named as follows:

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

Very often the word ID is used to name the 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))
You can use the NOT NULL option to specify required columns.

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

Updating the ID field ALTER TABLE Employees ALTER COLUMN ID int NOT NULL - updating the Name field ALTER TABLE Employees ALTER COLUMN Name nvarchar (30) NOT NULL

On a note
The general concept of the SQL language for most DBMSs remains the same (at least, I can judge this by the DBMSs that I have worked with). The difference between DDL in different DBMSs mainly lies in data types (here not only their names may differ, but also the details of their implementation), the 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 the dialect, alas, but there is no one standard). Knowing the basics of SQL, you can easily switch from one DBMS to another, because in this case, you only need to understand the details of the implementation of commands in the new DBMS, i.e. in most cases, a simple analogy will suffice.

Create table CREATE TABLE Employees (ID int, - in ORACLE, 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 like 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 set 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 a simple indication of the varchar2 (30) type in ORACLE, depends on the database settings, it can also sometimes be set in the IDE settings. In general, sometimes it can be easy to get confused, therefore, 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 read the string length in characters ).

But in this case, if the table already contains some data, then for the successful execution of the commands it is necessary that the ID and Name fields in all rows of the table must 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, since 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 have completed successfully, and the command "ALTER TABLE Employees ALTER COLUMN Name int NOT NULL" would have generated an error message, that there are NULL (unspecified) values ​​in the Name field.

Add values ​​for the Name field and fill in the data again:

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

First, let's delete the table using the command:

DROP TABLE Employees
Now let's create a table with mandatory 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))
You can also write NULL after the name of the column, which will mean that NULL values ​​(not specified) will be allowed in it, but this is not necessary, since this characteristic is assumed by default.

If, on the contrary, it is required to make the existing column optional for filling, then we use the following command syntax:

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

ALTER TABLE Employees ALTER COLUMN Name nvarchar (30)
Also, with this command, we can change the type of the field 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 primary key of the table. For our Employees table, such a unique value can be the ID column (which contains the "Employee Personnel Number" - even if in our case this value is 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 "PK_" prefix is ​​used to name the primary key, 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 be NOT NULL.

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

DROP TABLE Employees
And then let's 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 in the data into the 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" Sidorov S.S. "), (1003, N" Senior programmer ", N" IT ", N" Andreev A. A.")
If the primary key in the table consists of only the values ​​of one column, then the following syntax can be used:

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, the name of the constraint can be omitted, 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 always explicitly specifying the constraint name for persistent tables, since by an explicitly given and understandable name, it will subsequently be easier to manipulate it, for example, you can delete it:

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

Let's summarize

So far we have covered the following commands:

• CREATE TABLE table_name (enumeration of fields and their types, restrictions) - is used to create a new table in the current database;
• DROP TABLE table_name - serves to delete a table from the current database;
• ALTER TABLE table_name ALTER COLUMN column_name… - is used to update the type of a column or to change its settings (for example, to specify 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 the constraint from the table.

A little about temporary tables

Excerpt 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 to the SQL Server instance ends, as soon as they are first created. Local temporary tables are automatically dropped after a user disconnects from an instance of SQL Server. Global temporary tables are visible to all users during any connection sessions after these tables are created, and are dropped when all users referencing these tables disconnect from the SQL Server instance.

Temporary tables are created in the tempdb system database, i.e. by creating them, we do not litter the main base, otherwise the temporary tables are completely identical to ordinary tables, they can also be dropped using the DROP TABLE command. Local (#) temporary tables are more commonly used.

You can use the CREATE TABLE command to create a temporary table:

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

DROP TABLE #Temp

Also, a temporary table (like a regular table itself) can be created and immediately filled with data returned by a query using the SELECT ... INTO syntax:

SELECT ID, Name INTO #Temp FROM Employees

On a note
Implementation of temporary tables may differ in different DBMSs. For example, in ORACLE and Firebird DBMS, the structure of temporary tables must be defined in advance with the CREATE GLOBAL TEMPORARY TABLE command indicating the specifics of storing data in it, then the user can see it among the main tables and work with it as with a regular table.

Database normalization - splitting into subtables (reference books) and defining relationships

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 one employee can simply specify "IT" as a department, and a second employee, for example , enter "IT department", at the third "IT". As a result, it will be unclear what the user meant, i.e. Are these employees employees of the same department, or is the user described 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 required to show the number of employees in the context of each department.

The second disadvantage is the amount 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 complexity of updating these fields if the title 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 line of the table, in which the Position is equal to "Programmer".

To avoid these shortcomings, the so-called normalization of the database is applied - splitting it into sub-tables, 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 tables "Positions" and "Departments", the first one will be called Positions, and the second, respectively, 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 have used the new IDENTITY option, which says that the data in the ID column will be numbered automatically, starting from 1, with a step of 1, i.e. when adding new records, they will be sequentially assigned the values ​​1, 2, 3, etc. Such fields are commonly referred to as auto-incrementing. Only one field with the IDENTITY property can be defined in a table, and usually, but not necessarily, such a field is the primary key for that table.

On a note
In different DBMSs, the implementation of fields with a counter can be done in their own way. In MySQL, for example, such a field is defined using the AUTO_INCREMENT option. In ORACLE and Firebird earlier this functionality could 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:

Fill in 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 whose 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 assigning 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 the identifier data:

Add a field for the position ID ALTER TABLE Employees ADD PositionID int - add a field for the ID of the department ALTER TABLE Employees ADD DepartmentID int
The type of the reference fields should be the same as in the references, 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 we will write links (reference constraints - FOREIGN KEY) for these fields so that the user is not able to write in these fields the values ​​that are absent among the ID values ​​found in the reference books.

ALTER TABLE Employees ADD CONSTRAINT FK_Employees_PositionID FOREIGN KEY (PositionID) REFERENCES Positions (ID)
And we will 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 into these fields. Accordingly, in order to use a new department or position, he will first have to add a new record to the corresponding directory. Because positions and departments are now stored in directories in one single copy, then to change the name, it is enough to change it only in the directory.

The name of the reference constraint is usually a composite one, it consists of the prefix "FK_", then the name of the table follows, and after the underscore there is the name of the field that refers to the identifier of the reference table.

The identifier (ID) is usually an internal value that is used only for links and what value is stored there is, in most cases, absolutely indifferent, so you do not need to try to get rid of holes in the sequence of numbers that arise during the work with the table, for example, after deleting records from the reference book.

ALTER TABLE table ADD CONSTRAINT constraint_name FOREIGN KEY (field1, field2, ...) REFERENCES reference_table (field1, field2, ...)
In this case, in the table "dir_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 the 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 happened by running the query:

SELECT * FROM Employees

That's it, the PositionID and DepartmentID fields are filled in corresponding to the positions and departments with identifiers there is no need for the Position and Department fields in the Employees table, you can delete these fields:

ALTER TABLE Employees DROP COLUMN Position, Department
Now the table has acquired the following form:

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. as a result, we got rid of the storage of redundant information. Now, by the position and department numbers, we can uniquely 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 this 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 ManagerID field to our table with employees, which will point to the employee to whom this employee is subordinate. Let's create a field:

ALTER TABLE Employees ADD ManagerID int
NULL is allowed in this field, the field will be empty if, for example, there are no superiors above the employee.

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 see how the relationships between our tables look like on it:

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

Finally, it should be said that reference keys can include additional options ON DELETE CASCADE and ON UPDATE CASCADE, which tell 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 reference table for the record to which there are links from another table, also we will not be able to delete such a record from the reference until we delete all lines referring to this record, or, we will update the links in these lines to a different value.

For example, let's recreate the table with 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 Positions ID (ID), EmploySERT Names ) 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 has also been deleted from the Employees table.

The ON UPDATE CASCADE option behaves similarly, but it works when the ID value in the lookup is updated. For example, if we change the position ID in the job directory, then the DepartmentID in the Employees table will be updated to the new ID value that we specified in the directory. But in this case it will simply not be possible to demonstrate this, since the ID column in the Departments table has the IDENTITY option, which will not allow us to execute the following request (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 on very rare occasions, and I recommend that you think carefully before using them in a referencing constraint. if you accidentally delete a record from the lookup table, this can lead to big problems and create a chain reaction.

Let's restore department 3:

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

TRUNCATE TABLE Employees
And again, 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, a few more DDL commands have been added to our knowledge:

• Adding the IDENTITY property to a field - allows you to make this field automatically filled (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_of_field - allows you to delete fields from the table;
• ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY(fields) REFERENCES reference_table (fields) - allows you to define the relationship between the table and the reference table.

Other restrictions - UNIQUE, DEFAULT, CHECK

With the UNIQUE constraint, you can say that the values ​​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 restriction 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
And 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.

The uniqueness constraint is usually named as follows - first comes the prefix "UQ_", then the name of the table, and after the underscore there is the name of the field on which this constraint is applied.

Accordingly, if a combination of fields should 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 the field, we can set 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 a table.

Let's add a new Appointment Date field to the Employees table and name it HireDate and say that the default value for this field is 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, since in the case of DEFAULT, I was of the opinion that this is not so critical. But if you do it in an amicable way, then, I think, you shouldn't be lazy and you should give 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 record, the current date will also be inserted automatically, of course, if we do not explicitly set it, i.e. will not be specified 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 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 is the employee's identifier (ID). Using this constraint, let's say that personnel numbers should have a value between 1000 and 1999:

ALTER TABLE Employees ADD CONSTRAINT CK_Employees_ID CHECK (ID BETWEEN 1000 AND 1999)
The constraint is usually named the same, first comes 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 entry to check that the constraint is working (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 good practice and it is better to specify the name of the constraint explicitly, since to figure it out later, which will be more difficult, you will need to open the object and see what it is responsible for.

With a good name, a lot of information about the restriction can be recognized directly by its name.

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

DROP TABLE Employees
And we will recreate it with all 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 throw CONSTRAINT PK_Employees PRIMARY KEY exception (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 UNIQUEQUE (Email)

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 bit about indexes created when creating PRIMARY KEY and UNIQUE constraints

As you can see in the screenshot above, when creating 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 should be said that not all DBMS have the concept of a clustered index. A table can have only one CLUSTERED index. CLUSTERED - means that table records will be sorted by this index, you can also say that this index has direct access to all table data. This is the main index of the table, so to speak. More roughly, it is an index bolted to the table. The clustered index is a very powerful tool that can help you optimize your queries, just keep that in mind for now. If we want to tell the clustered index to be used not in the primary key, but for 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 PK_Employees constraint index non-clustered and the UQ_Employees_Email constraint index clustered. First of all, we will 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, after fetching from the Employees table, we can see that the records are sorted by the clustered UQ_Employees_Email 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

Prior to this, when the PK_Employees index was the clustered index, records were sorted by ID by default.

But in this case, this is just an example that shows the essence of the clustered index, since most likely, queries will be made to the Employees table by the ID field, and in some cases, it may itself act as a reference book.

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

It is advantageous to apply a clustered index to the most frequently sampled fields.

Sometimes a key is created in tables by a surrogate field, in which case it is 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

At this stage, we got acquainted with all types of constraints, in their simplest form, which are created by a command of the form "ALTER TABLE table_name ADD CONSTRAINT constraint_name ...":

• PRIMARY KEY- primary key;
• FOREIGN KEY- setting up links and controlling the referential integrity of data;
• UNIQUE- allows you to create uniqueness;
• CHECK- allows the correctness of the entered data;
• DEFAULT- allows you to set the 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 analyzed the concept of cluster ( CLUSTERED) and nonclustered ( NONCLUSTERED) index.

Create stand-alone indexes

Self-reliance here refers to indexes that are not created for a PRIMARY KEY or UNIQUE constraint.

Indexes by field or fields can be created with the following command:

CREATE INDEX IDX_Employees_Name ON Employees (Name)
Also here you can specify the CLUSTERED, NONCLUSTERED, UNIQUE options, and you can also specify the sorting direction for each individual field ASC (by default) or DESC:

CREATE UNIQUE NONCLUSTERED INDEX UQ_Employees_EmailDesc ON Employees (Email DESC)
When creating a nonclustered index, the NONCLUSTERED option can be released because it is the 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, like constraints, can be created in the context of the CREATE TABLE command.

For example, let's drop the table again:

DROP TABLE Employees
And we will recreate it with all created constraints 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 ), 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 (Email) 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's worth noting that you can include values ​​in a nonclustered index by specifying them in INCLUDE. Those. in this case, the INCLUDE index will somewhat resemble a clustered index, only now the index is not bolted to the table, but the necessary values ​​are bolted to the index. Accordingly, such indexes can greatly improve the performance of select queries (SELECT), if all the listed fields are present in the index, then it is possible that you will not need to access the table at all. But this naturally increases the size of the index, since the values ​​of the listed fields are duplicated in the index.

Excerpt from MSDN. General command syntax for creating indexes

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