SQL is one of the most common programming languages for creating and managing a database, as well as for performing various actions with the data itself.
As practice shows, it is quite easy 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 their use.
SQL Commands Classification
All standard SQL commands can be considered based on their purpose. As the basis of the unofficial classification, you can take such sets as:
Commands for building queries.
Commands of built-in procedures and functions.
Trigger and system table commands.
Sets of combinations 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 SQL language commands will be built on the basis of these types.
Considering the classification of a language, one cannot fail to mention that it is universal, as evidenced 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 SQL can be seen in terms of office software, namely MicrosoftAccess. This language, or rather, its kind - MySQL, allows you to administer databases on the Internet. Even the Oracle development environment uses SQL commands as the basis of its queries.
Using SQL in MicrosoftAccess
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 in computer science, and in the eleventh grade the MicrosoftAccess database management system is considered.
It is during the study of this application that students become familiar with the database development language and get a basic understanding of everything that goes into it. Access SQL commands are quite primitive, of course, if you consider them professionally. The execution of such commands is very simple, but they are created in the adapted code editor.
Consider a specific example:
SELECT Pe_SurName
FROM Pherson
WHERE Pe_Name = 'Mary';
Based on the syntax of the command, you can understand that it will return to the user the last name of the person, in this case, the woman named Mary, which 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 task.
Using SQL Commands in Oracle
Oracle is probably the only serious competitor to Microsoft SQL Server. It is this database development and management environment that constantly leads to the improvement of Microsoft software product functions, since competition is an engine of progress. Despite constant rivalry, Oracle SQL teams are repeating SQL. It is worth noting that although Oracle is considered an almost 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 instructions. If you consider the capabilities of these database development environments, Oracle does not have a complex structure of nested queries.
This difference allows you to speed up work with data many times, but, in contrast, leads to irrational use of memory, in some individual cases. The Oracle framework 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 they differ slightly 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 in a certain period of time. Although the structure of the query is different from Microsoft SQL Server, the execution of SQL commands on these systems is similar, with the exception of small details.
Using SQL on the Internet
With the advent of the World Wide Web, that is, the Internet, the use of the SQL language is expanding. As you know, a lot of information is stored in the network, but it is not randomly located, but placed on sites and servers according to certain criteria.
For storing information on the Internet, as in other places, databases are directly responsible, and sites are management systems. As a rule, sites and their program 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 under the MySQL web interfaces.
The syntax and the main set of commands of this language completely copy the familiar SQL to all, 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 calling and structuring the request. For example, you can consider the request to create a new table, it is she who is the first that children are taught in computer science schools:
$ 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 "Table created.";
elseecho "Table not created:" .mysqli_error ();
mysqli_close ($ link);
As a result of such a request, you can get a new table "Users", in which there will be two fields: login and password.
The syntax has been changed for the Web, but it is based on the MicrosoftSQLServer commands.
Building MicrosoftSQLServer Queries
Fetching a specific dataset from tables 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 itself in SQL is built as follows. For example, there is a table in which there is data about the employee, which, for example, has the name Person. We set the task that we need to select data on employees from the table whose birth date is in the interval from January 1 to March 1 of the current year inclusive. For such a selection, it is necessary to execute the SQL command, in which there will be not only a standard construction, but also a selection condition:
Select * from Person
Where P_BerthDay> = '01 / 01/2016 'and P_BerthDay <= '03 / 01/2016'
Executing such a command will return all data about employees whose birthday is in the period that was set by you. Sometimes the task may be to display only the last name, first name and patronymic of the employee. To do this, the query needs to be built a little differently, for example, in this way:
SelectP_Name - name
P_SurName - last name
P_Patronimic - middle name
from person
Where P_BerthDay> = '01 / 01/2016 'and P_BerthDay <= '03 / 01/2016'
However, this is just a choice. He, in essence, does not affect anything, but only provides information. But if you decide to take the SQL language seriously, you will have to learn how to make changes to the databases, since building them without it is simply impossible. How this is done will be discussed below.
Basic SQL Commands for Modifying Data
The language syntax is built not only for query execution, but also for data manipulation. Basically, the task of a database programmer is to write scripts for samples and reports, but sometimes you need to make changes to the tables. The list of SQL commands for such actions is small and consists of three main commands:
Insert (per. Insert).
Update (trans. Update).
Delete (per. 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 complicated construction scheme, but it is worth mentioning that some of them, if used incorrectly, can cause irreparable harm to the database.
As a rule, before using such MSSQL commands, you need to think through and take into account all the possible consequences of their implementation.
Having learned these commands, you can fully start working with database tables, thereby modifying it and introducing some new variables or deleting old ones.
Insert Command
To insert data into a table, the safest command is used - Insert. Incorrectly inserted data can always be deleted and re-entered into the database.
The Insert command is designed to insert new data into the table and allows you to add both a complete set and selectively.
As an example, consider the insert command in the previously described Person table. In order to enter data into the table, you need to execute the SQL command, which will allow you to insert all the data into the table or fill it selectively.
Insert into person
Select 'Grigoryev', 'Vitaliy', 'Petrovich', '01/01/1988'
Commands of MS SQL SERVER of such a plan automatically fill all the cells of the table with the specified data. There are situations when an employee does not have a middle name, say, he came to work in exchange from Germany. In this case, you need to execute the data insertion command, which will bring only what is needed to the table. The syntax for such a command would be:
Insertintoperson (P_Name, P_SurName, P_BerthDay)
Values ('David', 'Hook', '02/11/1986')
Such a command will fill only the specified cells, and all the rest will be null.
Command to change data
To change the data of both the entire row and some cells, the Update SQL command is used. Such a command needs to be executed only with a certain condition, namely, precisely indicate which line by number needs to be changed.
The Update SQL command has simple syntax. For proper use, it is necessary to indicate what data, in which column and in which record it is worth changing. Next, compose a script and execute it. Consider an example. You need to change the date of birth of David Hook, which is listed in the employee table at number 5.
Update person
Set P_BerthDay = '02 / 10/1986 'where P_ID = 5
The condition (in this script) will not let you change the date of birth in all records in the table, but will update only the ones you need.
It is this team that programmers use most often, since it allows you to change the data in the table without causing significant harm to all information.
Commands for Using Built-in Procedures and Functions
Using the SQL language, 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 the selection written earlier in the body of one request.
Judging logically, you need to copy the sample text and paste it in the right place, but you can get by with a simpler solution. Consider an example when a button for printing a report is displayed on the working interface, say in Excel. This operation will be performed as needed. For such purposes, built-in stored procedures are used. The SQL query commands , in this case, are enclosed in a procedure and called using the SQLExec command.
Suppose that a procedure has been created to display the date of birth of employees from the previously described Person table. In this case, there is no need to write the entire request. To obtain the necessary information, it is enough to execute the Exec command [procedure name] and pass the parameters necessary for the selection. As an example, we 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 the specified time period.
Organization of data integrity. Triggers
Some MS SQL commands, one might even say constructs, allow not only to organize data manipulations, but also to ensure their integrity. For such purposes in the language are designed system constructs that the programmer himself creates. These are the so-called triggers that can provide data control.
In this case, standard SQL query commands are used to organize condition checking. In triggers, you can create a lot of conditions and restrictions for working with data that will help to manage not only access to information, but also prohibit the deletion, modification or insertion of data.
The types of SQL commands that can be used in a trigger are not limited. Let's look at an example.
If we describe the mechanism for creating a trigger, then the types of SQL commands are the same as when creating a procedure. The algorithm itself will be described below.
First of all, you need to describe the utility command for creating triggers:
CREATE TRIGGER Person_Insert
Next, indicate for which table:
ONPerson
We indicate for what operation with data (in our case, this is the operation of changing data).
The next step is to specify the tables and variables:
declare @ID int. @Date smalldatetime @nID int. @nDatesmalldatetime
Next, we declare cursors for selecting data from the data removal 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 data selection steps. After, in the body of the cursors, we write down the condition and reaction to it:
if @ID = @nID and @nDate = '01 / 01/2016 '
begin
sMasseges' Operation cannot be completed. Date does not fit '
end
It is worth mentioning that a trigger can not only be created, but also disabled for a while. Only a programmer can do this manipulation by executing 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 possibilities.
Conclusion
From the foregoing, the only conclusion can be made: knowledge of the SQL language is simply necessary for those who are going to seriously engage in programming. It underlies all operations performed both on the Internet and in home databases. That is why the future programmer must know a lot of commands of this language, since only with their help you can, so to speak, communicate with the computer.
Of course, there are drawbacks, as in everything else in this world, but they are so insignificant that they simply fade before virtues. Among all programming languages, SQL is almost unique, because it is universal, and knowledge of writing scripts and codes is the basis of almost all sites.
The main advantage of SQL can unconditionally be considered its simplicity, because, after all, it was he who was included in the school curriculum. Even a novice programmer, who really does not understand languages, can handle it.