SQL query commands

SQL is one of the most common programming languages for creating and managing a database, as well as for carrying out a variety of actions with the data itself.

As practice shows, it is quite simple to learn and makes the most of the standard vocabulary of the English language. Like any other programming language, SQL has its own logic and syntax, a set of basic commands and rules for using them.

Classification of SQL language commands

All standard SQL commands can be considered based on their purpose. As a basis for private classification, one can take such sets as:

1. Commands for building queries.

2. Commands for built-in procedures and functions.

3. Trigger and system table commands.

4. Combination sets for working with date and string variables.

5. Commands for working with data and tables.

This classification can be continued indefinitely, but the basic sets of the SQL language command will be built based on these types.

Considering the classification of language, one can not help but mention that it is universal, as indicated by the scope of its use. This programming language and its variants are used not only in the standard environment, but also in other programs that, one way or another, you used.

The scope of using SQL can be viewed from the point of view of office software, namely Microsoft Access. This language, or rather, its version - MySQL, allows you to administer databases on the Internet. Even the Oracle development environment uses the SQL commands as its basis.

Using SQL in Microsoft Access

One of the simplest examples of using the language for database programming is the MicrosoftOffice software package. The study of this software product is provided by the school course of computer science, and in the eleventh class the MicrosoftAccess database management system is considered.

It is during the study of this application that students get acquainted with the language of database development and get a basic understanding of everything in it. Access's SQL commands are quite primitive, of course, if you look at them professionally. The execution of such commands is very simple, and they are created in a customized code editor.

Let's consider a concrete example:

SELECT Pe_SurName

FROM Pherson

WHERE Pe_Name = 'Mary';

Based on the syntax of the command, you can understand that it will return the user's last name, in this case a woman named Mary, that is stored in the table of the Contacts database.

Although the use of SQL in Access is limited, sometimes such simple queries can greatly simplify the execution of the assigned task.

Using SQL commands in Oracle

Oracle is probably the only serious competitor to Microsoft SQL Server. It is this environment for the development and management of the database that constantly leads to the improvement of the functions of the Microsoft software product, since competition is the engine of progress. Despite constant rivalry, Oracle's SQL commands repeat SQL. It is worth noting that, although Oracle is considered to be almost a complete copy of SQL, the logic of this system and the language as a whole is considered simpler.

The Oracle system does not have such a complex structure when using a specific set of commands. If we consider the capabilities of these database development environments, Oracle does not have a complex structure of nested queries.

This difference allows many times to accelerate the work with the data, but, in contrast, leads to an inefficient use of memory, in some individual cases. The structure of Oracle is mainly built on temporary tables and their use. As an example: SQL commands in this system are built by analogy with the standards of the SQL language itself, although insignificantly and different from it.

SELECTCONCAT (CONCAT (CONCAT (CONSTANT), CONCAT (SUBSTR (fname, 0, 1), SUBSTR (otch, 0, 1))), CONCAT ('Accepted, acceptdate)) FROM employees WHERE acceptdate> to_date ('01 .01.80 ',' dd.mm.yyyy ');

This query will return data about employees who are hired for a certain period of time. Although the structure of the request differs from Microsoft SQL Server, the execution of SQL commands in these systems is similar, except for small details.

Using SQL on the Internet

With the advent of the World Wide Web, that is, the Internet, the scope of the use of the SQL language is expanding. As you know, the network stores a lot of information, but it is not chaotically located, but posted on sites and servers according to certain criteria.

The storage of information on the Internet, as elsewhere, is directly the responsibility of databases, and sites are management systems. Typically, the sites and their code are organized in different programming languages, but the database is based on one of the varieties of SQL, namely the database creation language, oriented to MySQL web interfaces.

The syntax and basic set of commands of this language completely copy the usual SQL, but with some of its additions, which give it a difference from Microsoft tSQL Server.

SQL commands are completely similar not only in syntax, but also in the standard set of service words. The difference is only in the call and structuring of the request. For example, you can consider a query to create a new table, it is the first one that children learn in schools on computer science:

$ Link = mysqli_connect ('localhost', 'root', '', 'tester');

If (! $ Link) die ("Error");

$ Query = 'create table users (

Login VARCHAR (20),

Password VARCHAR (20)

) ';

If (mysqli_query ($ link, $ query)) echo "The table is created.";

Elseecho "The table was not created:" .mysqli_error ();

Mysqli_close ($ link);

As a result of this request, you can get a new table "Users", which will have two fields: login and password.

The syntax is changed to Web, but based on the commands MicrosoftSQLServer.

Building queries for Microsoft SQL Server

The selection from the tables of a particular data set is one of the main SQL tasks. For such operations, the select command in SQL is provided. It will be discussed below.

The rules for building a command are very simple, and the select command in SQL is constructed as follows. For example, there is a table in which there are data about the employee, which, for example, has the name Person. Let's set a task that from the table it is necessary to select data about the employees whose date of birth is in the interval from the first of January to the first of March of the current year inclusive. For such a sample, you must execute a SQL command that will not only have a standard construction, but also a selection condition:

Select * from Person

Where P_BerthDay> = '01/01/2016' and P_BerthDay <= '03 / 01/2016 '

The execution of such a command will return all data about the employees whose birthday is in the period that was set by you. Sometimes the task may be to deduce only the last name, first name and patronymic of the employee. To do this, the query must be constructed slightly differently, for example, in this way:

SelectP_Name is the name

P_SurName - last name

P_Patronimic - patronymic

From Person

Where P_BerthDay> = '01/01/2016' and P_BerthDay <= '03 / 01/2016 '

However, this is just a choice of something. He, in essence, does not affect anything, but only provides information. But if you decide to take SQL seriously, you will have to learn how to make changes to databases, because building them without it is simply impossible. How this is done will be discussed below.

Basic SQL commands for changing data

The syntax of the language is built not only for querying, but also for manipulating data. Basically, the task of the database programmer is writing scripts for samples and reports, but sometimes you need to make edits to the tables. The list of SQL commands for such actions is small and consists of three main commands:

1. Insert.

2. Update.

3. Delete.

The purpose of these commands is easy to determine, for this it will be enough just to translate their name. These commands are easy to use and have no complex construction scheme, but it is worth mentioning that some of them, if used improperly, can cause irreparable damage to the database.

As a rule, before use such MSSQL commands need to be thought through, and take into account all possible consequences of their implementation.

Having learned these commands, you will be able to fully start working with database tables, thereby modifying it and introducing some new variables or deleting old ones.

The Insert command

To insert data into the table, use the most secure command - Insert. Incorrectly inserted data can always be deleted and entered into the database again.

The Insert command is used to insert new data into the table and allows you to add both the complete set and selectively.

For example, consider the insert command in the previously described Person table. In order to enter data into the table, you must execute the SQL command, which will allow you to insert all the data into the table or fill it selectively.

Insert into person

Select 'Grigoriev', 'Vitaliy', 'Petrovich', '01/01/1988'

The MS SQL SERVER commands of this plan automatically populate all cells in the table with the specified data. There are situations when an employee does not have a middle name, for example, he came to work from Germany for an exchange. In this case, you need to execute a data insertion command that will only list what is needed in the table. The syntax for this command is:

Insertintoperson (P_Name, P_SurName, P_BerthDay)

Values ('David', 'Hook', '02/11/1986')

Such a command will fill only the specified cells, and all others will be null.

Command for changing data

To update the data for the entire row or for some cells, use the Update SQL command. To execute such a command it is necessary only with a certain condition, namely precisely to specify in what line by number it is necessary to make changes.

The Update SQL command has a simple syntax. For correct use, you need to specify which data, which column and which entry should be changed. Next, compile the script and execute it. Let's consider an example. It is necessary to change the date of birth of David Hooke, who is included in the table of employees under number 5.

Update Person

Set P_BerthDay = '02 / 10/1986 'where P_ID = 5

The condition (in this script) does not allow changing the date of birth in all records of the table, but updates only the required ones.

It is this command that programmers use most often, since it allows you to change data in a table without causing significant harm to all information.

Commands for using built-in procedures and functions

Using SQL, you can not only build queries, but also create built-in mechanisms for working with data. As a rule, there are times when you need to use in the body of one query a sample written earlier.

If you judge logically, you need to copy the text of the sample and paste it into the right place, but you can do with a simpler solution. Let's look at an example when a button is displayed on the working interface for printing a report, say in Excel. This operation will be performed as needed. For such purposes, the built-in stored procedures are used. The SQL query commands , in this case, are the procedure and are called using the SQLExec command.

Suppose that a procedure was created to output the date of birth of employees from the previously described Person table. In this case, there is no need to write the entire query. To obtain the necessary information, it is enough to execute the command Exec [procedure name] and transfer the necessary parameters for the selection. As an example, you can consider the mechanism for creating a procedure of this nature:

CREATEPROCEDUREPrintPerson

@DB smalldatetime

@DE smalldatetime

AS

SET NOCOUNT ON;

SELECT * from Person

FROM HumanResources.vEmployeeDepartmentHistory

WHERE P_BerthDay> = @DB and P_BerthDay <= @DE

ANDEndDateISNULL;

GO

This procedure will return all information about employees whose birthday will be in a given time period.

Organization of data integrity. Triggers

Some MS SQL commands, you can even say designs, allow not only to organize data manipulation, but also to ensure their integrity. For such purposes, the language is designed system design, which creates the programmer. These are so-called triggers that can provide data control.

In this case, standard SQL query commands are used to organize the condition check. In triggers, you can create a lot of conditions and restrictions for working with data that will help manage not only access to information, but also prevent the deletion, modification or insertion of data.

The types of SQL commands that can be used in the trigger are unlimited. Consider the example.

If you describe the mechanism for creating a trigger, then the types of SQL commands are the same as when creating the procedure. The algorithm itself will be described below.

The first step is to describe the service command to create triggers:

CREATE TRIGGER Person_Insert

Next, specify for which table:

ONPerson

Specify for which operation with the data (in our case this is a data modification operation).

The next step is to specify the tables and variables:

Declare @ID int. @Date smalldatetime @nID int. @nDatesmalldatetime

Next, we declare cursors to select data from the data deletion and insertion tables:

DEclare cursor C1 for select P_ID, P_BerthDay from Inserted

DEclare cursor C2 for select P_ID, P_BerthDay from deleted

Define steps for selecting data. Afterwards, in the body of the cursors we prescribe the condition and reaction to it:

If @ID = @nID and @nDate = '01 / 01/2016 '

Begin

SMasseges' The operation can not be performed. Date does not fit '

End

It is worth mentioning that the trigger can not only be created, but also disabled for a while. This manipulation can only be done by the programmer by executing the SQL SERVER commands:

AltertablePERSONdisabletriggerall - to disable all triggers created for this table, and, accordingly, altertablePERSONenabletriggerall - to enable them.

These basic SQL commands are used most often, but their combinations can be very diverse. SQL is a very flexible programming language and gives the developer maximum opportunities.

Conclusion

From all of the above, you can draw a single conclusion: knowledge of the SQL language is simply necessary for those who are going to seriously engage in programming. It lies at the heart of all operations performed on the Internet and in home databases. That is why the future programmer must know a lot of commands of this language, because only with their help you can, so to speak, communicate with the computer.

Of course, there are shortcomings, as in everything in this world, but they are so insignificant that they just fade in front of merits. Among all the programming languages, SQL is almost the only one of its kind, because it is universal, and knowledge of writing scripts and codes are at the heart of almost all sites.

The main advantage of SQL unconditionally can be considered its simplicity, because, after all, it is he who is included in the school curriculum. Even a novice programmer can handle it, not really versed in languages.