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 inmastering and maximizing 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 the unofficial classification, one can take such sets as:
Commands for building queries.
Commands for built-in procedures and functions.
Trigger and system table commands.
Combination sets for working with date and string variables.
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,to mention that it is universal, as indicated by the scope of its use. This programming language and its variants are used not only in a standard environment, but also in other programs that, one way or another, you used.
The scope of using SQL can be viewed withpoint 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 usinglanguage for programming databases is the software package MicrosoftOffice. The study of this software product is provided by the school course of informatics, and in the eleventh class the MicrosoftAccess database management system is considered.
It is during the study of this application that studentsget 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:
WHERE Pe_Name = "Mary";
Based on the syntax of the command, you can understand that it will return to the user the name of the person, in this case a woman named Mary, which is stored in the database table Contacts.
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 - this is probably the only seriouscompetitor 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 should be noted 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 using a certainA set of commands does not have such a complex structure. 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 acceleratework with data, but, in contrast, leads to 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 ('Employee', sname),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 isInternet, 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.
For storing information on the Internet, as inother places, are directly responsible for the database, 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.
The SQL commands are completely similar not only tosyntax, but also by a standard set of service words. The difference is only in the call and structuring of the request. For example, you can consider the 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 team are very simple, and itselfthe select command in SQL is constructed as follows. For example, there is a table in which there are data about an employee, which, for example, has the name Person. Let's set the 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 this sample, you need to 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'
Running such a command will return all the data about theemployees 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. For this, the query needs to be constructed slightly differently, for example, in this way:
SelectP_Name - name
P_SurName - last name
P_Patronimic - patronymic
Where P_BerthDay> = '01 / 01/2016 'and P_BerthDay <= '03 / 01/2016'
However, this is just a choice of something. He, in fact, 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 a language is not only built for executionqueries, 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:
The purpose of these commands is easy to determine, forIt 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.
Typically, 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 the 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 safest 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 insertion command in earlierdescribed the 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 automaticallyfill 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', 'Guk', '02/11/1986')
Such a command will fill only the specified cells, and all others will be null.
Command for changing data
To change the data of both the whole line andsome 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 proper 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, which is included in the table of employees at number 5.
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 buildqueries, 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 the sample written earlier.
If you judge logically, you need to copy the textsample and paste 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 has been created foroutput 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:
SET NOCOUNT ON;
SELECT * from Person
WHERE P_BerthDay> = @DB and P_BerthDay <= @DE
This procedure will return all information about employees whose birthday will be in the given time period.
Organization of data integrity. Triggers
Some MS SQL commands, you can even say,constructions, allow not only to organize manipulations with data, 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, to organize the verification of the conditionsstandard commands of SQL-queries are used. In triggers, you can create a lot of conditions and restrictions for working with data that will help you 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 a 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 for creating triggers:
CREATE TRIGGER Person_Insert
Next, specify for which table:
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
We set the steps for selecting the data. Afterwards, in the body of the cursors we prescribe the condition and the reaction to it:
if @ID = @nID and @nDate = "01/01/2016"
sMasseges "The operation can not be performed, the date does not fit"
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 the 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.
From all of the above, you can dothe only conclusion: knowledge of the SQL language is simply necessary for those who are going to seriously engage in programming. It is the basis 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 drawbacks, as in everything in thisworld, but they are so insignificant that they just fade in front of virtues. 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 is unconditionallyconsider its simplicity, because, after all, it was he who was included in the school curriculum. Even a novice programmer can handle it, not really versed in languages.