SQL command structure. Introduction to Structured Query Language SQL

Language structured queries Structure Query Language (SQL) was created as a result of the development relational model data and is now the de facto language standard relational DBMS. The SQL language today is supported by a huge number of DBMS of various types.

Name SQL language usually pronounced "es-qu-el". Sometimes the mnemonic name "See-Quel" is used.

The SQL language provides the user (with minimal effort on his part) the following capabilities:

Create databases and tables with full description their structures

Perform basic data manipulation operations: inserting, changing, deleting data

Run both simple and complex queries.

The SQL language is relationally complete.

The structure and syntax of its commands are quite simple, and the language itself is universal, i.e. the syntax and structure of its commands does not change when moving from one DBMS to another.

The SQL language has two main components:

DDL (Data Definition Language) for defining database structures and controlling access to data

DML (Data Manipulation Language) language designed for retrieving and updating data.

SQL is a non-procedural language, meaning that when you use it, you must specify what information should be obtained, not how it can be obtained. SQL commands are just words English language(SELECT, INSERT, etc.). Let's look at the SQL DML statements first:

SELECT - selecting data from the database

INSERT - inserting data into a table

UPDATE - updating data in a table

DELETE - deleting data from a table

SELECT statement

The SELECT statement performs actions equivalent to the following operations relational algebra: sampling, projection and connection.

The simplest SQL query using it looks like this:

SELECT col_name FROM tbl

The select keyword is followed by a comma-separated list of columns whose data will be returned by the query. The from keyword specifies from which table (or view) the data is retrieved.

The result of a select query is always a table called the result table. Moreover, the results of a query executed using the select statement can be used to create a new table. If the results of two queries to different tables have the same format, they can be combined into one table. Also, the table obtained as a result of a query can be the subject of further queries.

To select all columns and all rows of a table, simply issue a SELECT * FROM tbl;

Consider the Product table, which contains information about the price of various types products:

Request result

SELECT * FROM Product;

will be the entire Product table.

You can select specific table columns using a query

SELECT col1, col2, … , coln FROM tbl;

So, the result of the request

SELECT Type, Price FROM Product;

there will be a table

The list of columns in the select statement is also used if it is necessary to change the order of the columns in the resulting table:

In order to select only those table rows that satisfy certain restrictions, a special keyword where followed by a logical condition. If a record satisfies this condition, it is included in the result. Otherwise, the entry is discarded.

For example, selecting those products from the Product table whose price satisfies the Price condition<3200, можно осуществить, используя запрос

SELECT * FROM Product where Price<3200;

His result:

The condition can be compound and combined using the logical operators NOT, AND, OR, XOR, for example: where id_ Price>500 AND Price<3500. Допускается также использование выражений в условии: where Price>(1+1) and string constants: where name= "autoweights".

Using the BETWEEN var1 AND var2 construct allows you to check whether the values ​​of any expression fall within the range from var1 to var2 (including these values):

SELECT * FROM Product where Price BETWEEN 3000 AND 3500;

Similar to the NOT BETWEEN operator, there is the NOT IN operator.

Column names specified in the SELECT clause can be renamed. For this, the AS keyword is used, which, however, can be omitted, since it is implicitly implied. For example, request

SELECT Type AS model, Type_id AS num FROM Product where Type_id =3

will return (alias names should be written without quotes):

The LIKE operator is designed to compare a string with a pattern:

SELECT * FROM tbl where col_name LIKE "abc"

This query returns only those records that contain the string value abc in the col_name column.

The sample is allowed to use two wildcard characters: "_" and "%". The first of them replaces one arbitrary character in the template, and the second replaces a sequence of arbitrary characters. So, "abc%" matches any string starting with abc, "abc_" matches a 4-character string starting with abc, "%z" matches any string ending with z, and finally, "%z%" - sequences of characters containing z.

You can find all records of the Product table in which the Type value begins with the letter "a" like this:

SELECT * FROM Product where Type LIKE "a%";

If the search string contains a wildcard character, then you must specify the escape character in the ESCAPE clause. This control character must be used in the pattern before the wildcard character, indicating that the wildcard character should be treated as a regular character. For example, if you want to find all values ​​in a field that contain the character "_", then the pattern "%_%" will result in all records from the table being returned. In this case, the template should be written as follows:

"%|_%" ESCAPE "|"

To check the value for compliance with the string "20%" you can use the following operator:

LIKE "20#%" ESCAPE "#"

The IS NULL operator allows you to check the absence (presence) of a NULL value in the fields of a table. Using regular comparison operators in these cases may produce incorrect results because comparing with NULL results in UNKNOWN. Thus, the selection condition should look like this:

where col_name IS NULL, instead of where col_name=NULL.

The default selection result returns records in the same order in which they are stored in the database. If you want to sort records by one of the columns, you must use the ORDER BY clause, followed by the name of that column:

SELECT * FROM tbl ORDER BY col_name;

This query will return records in ascending order of the col_name attribute value.

You can also sort records by multiple columns. To do this, their names must be specified after ORDER BY separated by commas:

SELECT * FROM tbl ORDER BY col_name1, col_name2.

Records will be sorted by the col_name1 field; if there are several records with a matching value in the col_name1 column, they will be sorted by the col_name2 field.

If you want to sort the records in reverse order (for example, descending by date), you must specify ORDER BY col_name DESC.

For direct sorting, there is the ASC keyword, which is accepted as the default value.

If the sample result contains hundreds or thousands of records, their output and processing takes considerable time.

Therefore, information is often divided into pages and presented to the user in portions. Pagination is used using the limit keyword followed by the number of entries to display. The following query retrieves the first 10 records while simultaneously sorting backwards on the col_name1 field:

SELECT * FROM tbl ORDER BY col_name1 DESC LIMIT 10

To retrieve the next 10 records, use the limit keyword with two values: the first specifies the position from which the result should be printed, and the second specifies the number of records to retrieve:

SELECT * FROM tbl ORDER BY col_name1 DESC LIMIT 10,10

To retrieve the next 10 records, you must use the LIMIT 20, 10 construct.

Introduction to Relational Database Management

sql is often called the Esperanto language for database management systems (DBMS). Indeed, there is no other language in the world for working with databases that would be so widely used in programs. The first sol standard appeared in 1986 and has now gained universal recognition. It can be used even when working with non-relational DBMSs. Unlike other software tools, such as the C and Cobol languages, which are the prerogative of professional programmers, sql is used by specialists from a variety of fields. Programmers, DBMS administrators, business analysts - they all successfully process data using sql. Knowledge of this language is useful for everyone who has to deal with the database.

In this article we will look at the basic concepts of sql. Let's tell his backstory (and dispel a few myths along the way). You will become familiar with the relational model and will be able to acquire first skills in working with sql, which will help in further mastering the language.

Is it difficult to learn sql? It depends on how deep you're going to go. In order to become a professional, you have to study a lot. The sql language began life in 1974 as the subject of a short 23-page research paper and has come a long way since then. The text of the current standard - the official document "the international standard database language sql" (commonly called sql-92) - contains over six hundred pages, but it does not say anything about the specific features of the versions of sol implemented in DBMSs from Microsoft, Oracle, Sybase etc. The language is so developed and diverse that just listing its capabilities would require several journal articles, and if you collect everything that has been written on the topic sol, you will get a multi-volume library.

However, for the average user it is not at all necessary to know sql completely. Just as a tourist who finds himself in a country where they speak an incomprehensible language only needs to learn a few common expressions and grammar rules, so in SQL - knowing a little, you can get many useful results. In this article we will look at the basic sql commands, the rules for setting criteria for selecting data, and show how to get results. As a result, you will be able to create tables yourself and enter information into them, create queries and work with reports. This knowledge can become the basis for further independent development of sql.

What is sql?

sql is a specialized non-procedural language that allows you to describe data, retrieve and process information from relational DBMSs. Specialization means that sol is intended only for working with the database; You cannot create a full-fledged application system using only this language - this will require the use of other languages ​​in which you can embed SQL commands. Therefore, sql is also called an auxiliary language tool for data processing. An auxiliary language is used only in conjunction with other languages.

A general-purpose application language usually has facilities for creating procedures, but SQL does not. With its help, you cannot specify how a certain task should be performed, but you can only determine what exactly it is. In other words, when working with sql, we are interested in the results, not the procedures for obtaining them.

The most significant property of sql is the ability to access relational databases. Many even believe that the expressions “database processed using sql” and “relational database” are synonymous. However, you will soon see that there is a difference between them. The sql-92 standard doesn't even have the term relation.

What is a relational DBMS?

Without going into details, a relational DBMS is a system based on a relational data management model.

The concept of a relational model was first proposed in the work of Dr. E. F. Codd, published in 1970. It described a mathematical apparatus for structuring and manipulating data, and also proposed an abstract model for representing any real-world information. Previously, when using a database, it was necessary to take into account the specific features of storing information in it. If the internal structure of the database changed (for example, to improve performance), it was necessary to rework application programs, even if no changes occurred at the logical level. The relational model made it possible to separate the private features of data storage from the application program level. In fact, the model does not describe in any way how information is stored and accessed. What is taken into account is how this information is perceived by the user. Thanks to the emergence of the relational model, the approach to data management has qualitatively changed: from an art it turned into a science, which led to the revolutionary development of the industry.

Basic concepts of the relational model

According to the relational model, a relation is a table with data. A relation may have one or more attributes (features) corresponding to the columns of this table, and some set (possibly empty) of data, which are sets of these attributes (they are called n-ary tuples, or records) and corresponding to the rows of the table.

For any tuple, attribute values ​​must belong to so-called domains. In fact, a domain is a certain set of data that defines the set of all valid values.

Let's look at an example. Let there be a Days of Week domain containing values ​​from Monday to Sunday. If a relation has a WeekDay attribute corresponding to this domain, then any tuple in the relation must have one of the listed values ​​in the WeekDay column. The values ​​January or Cat are not allowed to appear.

Please note: the attribute must have one of the valid values. Specifying multiple values ​​at once is prohibited. Thus, in addition to the requirement that attribute values ​​belong to a certain domain, the condition of its atomicity must be met. This means that these meanings cannot be decomposed, i.e., they cannot be broken down into smaller parts without losing the underlying meaning. For example, if the attribute value simultaneously contained Monday and Tuesday, then two parts could be distinguished, preserving the original meaning - Day of the Week; therefore, this attribute value is not atomic. However, if you try to break the meaning “Monday” into parts, you will get a set of individual letters - from “P” to “K”; the original meaning is lost, so the meaning of "Monday" is atomic.

Relationships also have other properties. The most significant of them is the mathematical property of closed operations. This means that as a result of performing any operation on a relation, a new relation must appear. This property allows you to obtain predictable results when performing mathematical operations on relationships. In addition, it becomes possible to represent operations in the form of abstract expressions with different levels of nesting.

In his original work, Dr. Codd defined a set of eight operators called relational algebra. Four operators—union, logical multiplication, difference, and Cartesian product—were carried over from traditional set theory; the remaining operators were created specifically to handle relationships. Subsequent work by Dr. Codd, Chris Date, and other researchers proposed additional operators. Later in this article, we'll look at three relational operators—project, select, or restrict, and join.

sql and relational model

Now that you're familiar with the relational model, let's forget about it. Of course, not forever, but only to explain the following: although it was the relational model proposed by Dr. Codd that was used in the development of sql, there is no complete or literal correspondence between the two (this is one of the reasons why the sql-92 standard does not have the term attitude). For example, the concepts of an sql table and a relation are not equivalent, because tables can have several identical rows at once, while identical tuples are not allowed to appear in relations. In addition, SQL does not provide for the use of relational domains, although data types play a role to some extent (some influential supporters of the relational model are now attempting to push for the inclusion of relational domains in the future SQL standard).

Unfortunately, the inconsistency between sql and the relational model has given rise to many misunderstandings and disputes over the years. But since the main topic of the article is studying sql, and not the relational model, these problems are not discussed here. Just remember that there are differences between the terms used in sql and the relational model. Further in the article only terms accepted in sql will be used. Instead of relationships, attributes and tuples, we will use their sql analogues: tables, columns and rows.

Static and dynamic sql

You may already be familiar with terms such as static and dynamic sql. An sql query is static if it is compiled and optimized at a stage prior to program execution. We have already mentioned one form of static sql when we talked about embedding sql commands in C or Cobol programs (there is another name for such expressions - embedded sql). As you probably guess, a dynamic SQL query is compiled and optimized during program execution. As a rule, ordinary users use dynamic sql, which allows them to create queries in accordance with their immediate needs. One of the options for using dynamic SQL queries is their interactive or direct call (there is even a special term - directsql), when the queries sent for processing are entered interactively from the terminal. There are certain differences between static and dynamic SQL in the syntax of the constructs used and execution features, but these issues are beyond the scope of the article. Let us only note that for clarity of understanding, examples are given in the form of direct sql queries, since this allows not only programmers, but also most end users, to learn how to use sql.

How to learn sql

Now you are ready to write your first sql queries. If you have access to the database via sql and you want to use our examples in practice, then consider the following: you must log in as a user with unlimited rights and you will need software tools for interactive processing of sql queries (if we are talking about a network database, You should talk to your database administrator about granting you the appropriate rights). If you don’t have access to the database via sql, don’t worry: all the examples are very simple and you can figure them out “dry”, without going to the machine.

In order to perform any actions in sql, you must execute an expression in sql language. There are several types of expressions, but among them three main groups can be distinguished: ddl commands (data definition language), dml commands (data manipulation language) and data control tools. Thus, in a sense, sql combines three different languages.

Data Description Language Commands

Let's start with one of the main ddl commands - create table. There are several types of tables in sql, the main ones are two types: basic (base) and selective (views). Basic tables are those related to real-life data; selective are “virtual” tables that are created based on information obtained from base tables; but to users the forms look like regular tables. The create table command is designed to create base tables.

In the create table command, you must specify the name of the table, specify the list of columns and the types of data they contain. Other optional elements may also be present as parameters, but first let's look at only the basic parameters. Let's show the simplest syntactic form for this command:

create tableTableName(ColumnDataType) ;

create and table are sql keywords; TableName, Column and DataType are formal parameters, instead of which the user enters actual values ​​each time. The Column and DataType parameters are enclosed in parentheses. In sql, parentheses are commonly used to group individual elements. In this case, they allow you to combine definitions for a column. The ending semicolon is a command separator. It must terminate any expression in sql language.

Let's look at an example. Let's say you need to create a table to store data about all appointments. To do this, enter the command in sql:

create table appointments (appointment_date date) ;

After executing this command, a table named appointments will be created, where there is one column, appointment_date, in which data of type date can be written. Since no data has been entered yet, the number of rows in the table is zero (the create table command only defines the table; the actual values ​​are entered with the insert command, which is discussed later).

The appointments and appointment_date parameters are called identifiers because they specify names for specific database objects, in this case names for a table and a column, respectively. There are two types of identifiers in sql: regular and delimited. Highlighted identifiers are enclosed in double quotes and are case-sensitive. Regular identifiers are not distinguished by any limited characters, and their writing is not case sensitive. This article uses only regular identifiers.

The characters used to construct identifiers must satisfy certain rules. Regular identifiers can only use letters (not necessarily Latin, but also other alphabets), numbers and the underscore character. The identifier must not contain punctuation, spaces or special characters (#, @, % or!); in addition, it cannot begin with a number or an underscore. You can use separate sql keywords for identifiers, but this is not recommended. An identifier is intended to designate some object, so it must have a unique (within a certain context) name: you cannot create a table with a name that is already found in the database; You cannot have columns with the same names in the same table. By the way, keep in mind that appointments and appointments are the same names for sql. Changing the case of letters alone cannot create a new identifier.

Although a table can have just one column, in practice tables with multiple columns are usually required. The command to create such a table in general looks like this:

create tableTableName(ColumnDataType[ ( , ColumnDataType )]) ;

Square brackets are used to denote optional elements, curly brackets contain elements that can represent a list of single-path constructs (when entering a real SQL command, neither one nor the other brackets are placed). This syntax allows you to specify any number of columns. Note that the second element is preceded by a comma. If there are several parameters in the list, they are separated from each other by commas.

create table appointments2 (appointment_date date, appointment_time time, description varchar (256)) ;

This command creates the appointments2 table (the new table must have a different name, since the appointments table is already present in the database). Like the first table, it has an appointment_date column to record the dates of appointments; In addition, an appointment_time column has appeared to record the time of these meetings. The description parameter is a text string that can contain up to 256 characters. This parameter is specified as varchar (short for character varying) because it is not known in advance how much space will be required for the entry, but it is clear that the description will take no more than 256 characters. When describing a parameter of the character string type (and some other types), the length of the parameter is indicated. Its value is specified in parentheses to the right of the type name.

You may have noticed that in the two examples discussed, the command entry is formatted differently. If in the first case the command is completely placed on one line, then in the second, after the first open parenthesis, the entry is continued on a new line, and the definition of each subsequent column begins on a new line. There are no special requirements for record formatting in sql. Breaking a record into lines makes it easier to read. When writing commands, the sql language allows you not only to break the command into lines, but also to insert indents at the beginning of lines and spaces between record elements.

Now that you know the basic rules, let's look at a more complex example of creating a table with multiple columns. At the beginning of the article, the employees table was shown. It contains the following columns: last name, first name, hire date, department, category and salary for the year. The following sql command is used to define this table:

create table employees (last_name character (13) not null, first_name character (10) not null, hire_date date, branch_office character (15), grade_level smallint, salary decimal (9, 2));

The team meets several new elements. First of all, there is the not null expression at the end of the definition of the last_name and first_name columns. With the help of such structures, requirements are set that must be complied with. In this case, it is indicated that the last_name and first_name fields must be filled in when entering; You cannot leave these columns empty (this is quite logical: how can you identify an employee without knowing his name?).

In addition, the example contains three new data types: character, smallint and decimal. So far we haven't talked much about types. Although sql does not have relational domains, it does have a set of basic data types. This information is used when allocating memory and comparing values; to a certain extent narrows the list of possible input values, but type control in sql is less strict than in other languages.

All data types available in sql can be divided into six groups: character strings, exact numeric values, approximate numeric values, bit strings, datetimes and intervals. We have listed all the varieties, but this article will discuss in detail only some of them (bit strings, for example, are not of particular interest to ordinary users).

By the way, if you thought that the date and time was a typo, you were mistaken. This group (datetime) includes most of the time-related data types used in sql (parameters such as time intervals are separated into a separate group). In the previous example, we already encountered two data types from the date-time group - date and time.

The next data type you're already familiar with is character varying (or just varchar); it belongs to the group of character strings. If varchar is used to store strings of variable length, then the char type encountered in the third example is intended to store strings with a fixed number of characters. For example, the last_name column will contain strings of 13 characters, regardless of the actual last names entered, be it poe or penworth-chickering (in the case of poe, the remaining 10 characters will be filled with spaces).

From the user's point of view, varchar and char have the same meaning. Why was it necessary to introduce two types? The fact is that in practice you usually have to look for a compromise between performance and saving disk space. As a rule, using fixed-length strings gives some benefit in access speed, but if the string length is too long, disk space is wasted. If in appointments2 you reserve 256 characters for each comment line, then this may turn out to be irrational; most often the lines will be significantly shorter. On the other hand, last names also vary in length, but they generally require around 13 characters; in this case, losses will be minimal. A good rule of thumb is: if you know that the length of the string varies little or is relatively small, then use char; in other cases - varchar.

The next two new data types, smallint and decimal, belong to the group of exact numeric values. smallint is short for small integer. SQL also provides an integer data type. The presence of two similar types in this case is explained by space saving considerations. In our example, the values ​​of the grade_level parameter can be represented using a two-digit number, so the smallint type is used; however, in practice it is not always known what maximum values ​​the parameters can have. If there is no such information, then use integer. The actual amount allocated to store smallint and integer parameters, and the corresponding range of values ​​for these parameters, is specific to each platform.

The decimal data type, commonly used for financial accounting, allows you to specify a pattern with the required number of decimal places. Because this type is used for precise numeric notation, it guarantees precision when performing mathematical operations on decimal data. If you use data types from the approximate numeric notation group for decimal values, for example float (floating point number), this will lead to rounding errors, so this option is not suitable for financial calculations. To define parameters of the decimal type, the following notation is used:

where p is the number of decimal places, d is the number of decimal places. Instead of p, write the total number of significant figures in the values ​​used, and instead of d, write the number of decimal places.

The "Creating a Table" sidebar shows a complete summary of the create table command. It contains new elements and shows the format for all the considered data types (In principle, there are other data types, but we are not considering them yet).

At first, it may seem that the syntax of sql commands is too complicated. But you can easily understand it if you carefully study the examples above. An additional element appeared on the diagram - a vertical line; it serves to distinguish between alternative designs. In other words, when defining each column, you need to select the appropriate data type (as you remember, optional parameters are enclosed in square brackets, and constructs that can be repeated many times are enclosed in curly braces; these special characters are not written in real sql commands). The first part of the diagram shows the full names for the data types, the second contains their abbreviated names; in practice, any of them can be used.

The first part of the article is completed. The second will be devoted to studying the DML commands insert, select, update and delete. Data sampling conditions, comparison operators and logical operators, the use of null values ​​and ternary logic will also be covered.

Creating a table. The syntax of the create table command is as follows: optional parameters are indicated in square brackets, and repeating structures are indicated in curly brackets.

create table table (column character (length) [ constraint ] | character varying (length) [ constraint ] | date [ constraint ] | time [ constraint ] | integer [ constraint ] | smallint [ constraint ] | decimal (precision, decimal places) [ constraint ] | float (precision) [ constraint ] [( , column char (length) [ constraint ] | varchar (length) [ constraint ] | date [ constraint ] | time [ constraint ] | int [ constraint ] | smallint [ constraint ] | dec (precision, decimal places) [ constraint ] | float (precision) [ constraint ] )]) ​​;

The secret of the sql name

In the early 1970s. ibm began to put into practice the relational database model proposed by Dr. Codd. Donald Chamberlin and a group of others at the Advanced Research Unit created a prototype language called structured English query language, or simply sequel. Subsequently it was expanded and refined. The new version proposed by ibm was called sequel/2. It was used as a program interface (api) to design the first relational database system from ibm - system/r. For reasons related to legal nuances, ibm decided to change the name: instead of sequel/2, use sql (structured query language). This abbreviation is often pronounced "see-ku-el."

There are significant differences between the early sequel prototypes and the sql standard now recognized in various organizations. Jim Melton, who prepared the sql-92 standard, even stated that many people are mistaken in thinking that the word “structured” correctly reflects the specifics of this language (jim melton and Alan R. Simon "Understanding the new sql: a complete guide." San Francisco : morgan kaufmann, 1993. isbn: 1-55860-245-3). Therefore, in fact, sql is just a name, a sequence of letters s-q-l and nothing more.

Good Bad

One of the languages ​​that emerged as a result of the development of the relational data model is the SQL language (Structured Query Language), which has now become very widespread and has actually become standard language relational databases. Standard SQL was released by the American National Standards Institute (ANSI) in 1986, and was adopted internationally by the International Standards Organization (ISO) in 1987. The current SQL standard is known as SQL/92.

The use of any standards is associated not only with numerous and quite obvious advantages, but also with certain disadvantages. First of all, standards direct the development of the relevant industry in a certain direction; In the case of the SQL language, having strong underlying principles ultimately leads to interoperability among its various implementations and contributes to both increased portability of software and databases in general, and the versatility of database administrators. On the other hand, standards limit the flexibility and functionality of a particular implementation. Under language implementation SQL refers to the SQL software product of the respective manufacturer. To expand functionality, many developers who adhere to accepted standards add to standard language SQL various extensions. It should be noted that the standards require any completed language implementations SQL has certain characteristics and broadly reflects major trends that not only lead to compatibility between all competing implementations, but also help to increase the value of SQL programmers and users relational databases in the modern software market.

All specific language implementations are somewhat different from each other. It is in the manufacturers' best interest to ensure that their implementations meet current ANSI standards for portability and user experience. However, each implementation of SQL contains enhancements to meet the requirements of a particular database server. These enhancements or extensions to the SQL language are additional commands and options that are additions to the standard package and are available in that particular implementation.

Currently, the SQL language is supported by many dozens of DBMSs of various types, developed for a wide variety of computing platforms, ranging from personal computers to mainframes.

All data manipulation languages ​​created for many DBMSs before the advent of relational databases, were focused on operations with data presented in the form of logical file records. Of course, this required the user to have detailed knowledge of the data storage organization and serious effort to specify what data was needed, where it was located, and how to obtain it.

The SQL language under consideration is focused on operations with data presented in the form of logically interconnected sets of relation tables. The most important feature of its structures is its focus on the final result of data processing, and not on the procedure for this processing. The SQL language itself determines where the data is located, the indexes, and even what the most efficient sequence of operations should be used to obtain the result, so it is not necessary to specify these details in the database query.

Introduction to client-server technology

In connection with the expansion of the information services market, software manufacturers began to produce increasingly intelligent, and therefore voluminous, software systems. Many organizations and individual users often could not place purchased products on their own computers. For the exchange of information and its distribution, computer networks were created, and generalizing programs and data began to be installed on special file servers.

Thanks to DBMSs working with file servers, many users have access to the same databases. The development of various automated management systems for organizations is simplified. However, with this approach, all processing of requests from programs or from user computer terminals is performed on them, therefore, to implement even a simple request, it is necessary to read or write entire files from the file server, and this leads to conflict situations and network overload. To eliminate these shortcomings, it was proposed client-server technology, but at the same time a common language for communicating with the server was needed - the choice fell on SQL.

Client-server technology means a way of interaction of software components in which they form a single system. As the name itself suggests, there is a certain client process that requires certain resources, as well as server process, which provides these resources. It is not necessary for them to be on the same computer. It is usually customary to place the server on one node of the local network, and clients on other nodes.

In a database context, the client controls the application's user interface and logic, acting as a workstation that runs database applications. The client accepts a request from the user, checks the syntax, and generates a database query in SQL or another database language appropriate to the application logic. It then sends a message to the server, waits for a response, and formats the received data for presentation to the user. The server receives and processes requests to the database, and then sends the results back to the client. This processing includes verifying the client's authority, ensuring integrity requirements, and fulfilling the request and updating the data. In addition, concurrency control and recovery are supported.

Client-server architecture has a number of advantages.

Today, SQL courses “for dummies” are becoming increasingly popular. This can be explained very simply, because in the modern world you can increasingly find so-called “dynamic” web services. They are distinguished by a fairly flexible shell and are based on All novice programmers who decide to dedicate websites, first of all enroll in SQL courses “for dummies”.

Why learn this language?

First of all, SQL is taught in order to further create a wide variety of applications for one of the most popular blog engines today - WordPress. After completing a few simple lessons, you will be able to create queries of any complexity, which only confirms the simplicity of this language.

What is SQL?

Or a structured query language, was created for one single purpose: to determine, provide access to and process them in fairly short periods of time. If you know the SQL meaning, then you will understand that this server is classified as a so-called “non-procedural” language. That is, its capabilities only include a description of any components or results that you want to see in the future on the site. But when does not indicate exactly what results are going to be obtained. Each new request in this language is like an additional “superstructure”. It is in the order in which they are entered into the database that the queries will be executed.

What procedures can be performed using this language?

Despite its simplicity, the SQL database allows you to create a wide variety of queries. So what can you do if you learn this important programming language?

• create a wide variety of tables;
• receive, store and modify received data;
• change table structures at your discretion;
• combine the received information into single blocks;
• calculate the received data;
• ensure complete protection of information.

What commands are the most popular in this language?

If you decide to take a SQL for Dummies course, then you will receive detailed information about the commands that are used in creating queries using it. The most common today are:

1. DDL is a command that defines data. It is used to create, modify and delete a wide variety of objects in the database.
2. DCL is a command that manipulates data. It is used to provide different users with access to information in the database, as well as to use tables or views.
3. TCL is a team that manages a variety of transactions. Its main purpose is to determine the progress of a transaction.
4. DML - manipulates the received data. Its task is to allow the user to move various information from the database or enter it there.

Types of privileges that exist in this server

Privileges refer to those actions that a particular user can perform in accordance with his status. The most minimal, of course, is a regular login. Of course, privileges may change over time. Old ones will be deleted and new ones will be added. Today, all those who take SQL Server "for dummies" courses know that there are several types of permitted actions:

1. Object type - the user is allowed to execute any command only in relation to a specific object that is located in the database. At the same time, privileges differ for different objects. They are also tied not only to a particular user, but also to tables. If someone, using his capabilities, created a table, then he is considered its owner. Therefore, he has the right to assign new privileges to other users related to the information in it.
2. The system type is the so-called data copyright. Users who have received such privileges can create various objects in the database.

History of SQL

This language was created by IBM Research Laboratory in 1970. At that time, its name was slightly different (SEQUEL), but after a few years of use it was changed, shortened slightly. Despite this, even today many world-famous programming experts still pronounce the name the old fashioned way. SQL was created with one single goal - to invent a language that would be so simple that even ordinary Internet users could learn it without any problems. An interesting fact is that at that time SQL was not the only such language. In California, another group of specialists developed a similar Ingres, but it never became widespread. Before 1980, there were several variations of SQL that were only slightly different from each other. To prevent confusion, a standard version was created in 1983, which is still popular today. SQL courses "for dummies" allow you to learn a lot more about the service and fully study it in a few weeks.

Client programs

TFTP protocol

TFTP- also the FTP protocol, but on top of the UDP protocol (i.e., a protocol without guaranteed delivery). Can be used on a local network where transmission speed is more important. In practice it is rarely used.

FTP- the program is launched from the command line.

Windows Commander- can work as an FTP client. Allows you to work with remote directories in the same way as with local ones.

NetVampire - A specialized FTP client that allows you to download large files and download through bad channels.

SQL (Structured Query Language) is a structured query language for relational databases. In this language, you can formulate expressions (queries) that retrieve the required data, modify it, create tables and change their structures, determine access rights to data, and much more.

Queries are executed by a database management system (DBMS). If you are not a specialist in database development and administration, then you may well be a database user who views and/or changes data in existing tables. In many cases, these and other database operations are performed using special applications that provide the user with a convenient interface. Typically, applications are written in special programming languages ​​(C, Pascal, Visual Basic, etc.) and are most often created using integrated development environments, for example, Delphi, C++ Builder, etc. However, access to the database can be obtained without them - using only SQL. It should also be noted that specialized applications usually use SQL code fragments when accessing the database.

Thus, SQL is a widely used standard language for working with relational databases. The syntax of this language is simple enough that ordinary users, not just programmers, can use it. Nowadays, the average computer user should at least be proficient in a word processor (such as Microsoft Word) and a spreadsheet program (such as Microsoft Excel). It's good if he also knows how to use databases. There are many different DBMSs, but there is only one universal tool for working with databases - SQL. Knowledge of SQL, at least its basics, and the ability to use it to search and analyze data is a fundamental part of computer literacy, even for ordinary users.

The first developments of relational database management systems (relational DBMS) were carried out at IBM in the early 1970s. At the same time, a data language was created to work in these systems. The experimental version of this language was called SEQUEL - from the English. Structured English QUEry Language (structured English query language). However, the official version was called shorter - SQL (Structured Query Language). More precisely, SQL is a data sublanguage, since the DBMS contains other language tools.

In 1981, IBM released the relational DBMS SQL/DS. By this time, Relation Software Inc. (today it is Oracle Corporation) has already released its relational DBMS. These products immediately became the standard for database management systems. These products also included SQL, which became the de facto standard for data sublanguages. Manufacturers of other DBMSs have released their own versions of SQL. They included more than just the core capabilities of IBM products. To gain some advantage for “their” DBMS, manufacturers introduced some SQL extensions. At the same time, work began on creating a generally accepted SQL standard.

In 1986, the American National Standards Institute (ANSI) released the official standard SQL-86, which was updated in 1989 and renamed SQL-89. In 1992, this standard was named SQL-92 (ISO/IEC 9075:1992). The latest version of the SQL standard is SQL:2003 (ISO/IEC 9075X:2003).

Any implementation of SQL in a specific DBMS is somewhat different from the standard that the manufacturer declares compliance with. Thus, many DBMSs (for example, Microsoft Access 2003, PostgreSQL 7.3) do not fully support SQL-92, but only with some level of compliance. In addition, they also support elements that are not included in the standard. However, DBMS developers strive to ensure that new versions of their products comply as closely as possible with the SQL standard.

Attention. This tutorial describes SQL2003 elements, not all of which are supported by existing DBMSs. Before you put them into practice, you should make sure that they will work in your DBMS. You can learn about this from the technical documentation. Most of the elements described correspond to earlier versions of SQL, in particular the widely used SQL-92.

SQL was conceived as a simple query language for a relational database, close to natural (more precisely, to English) language. It was hoped that the closeness in form to natural language would make SQL a tool that could be widely used by ordinary database users, not just programmers. Initially, SQL did not contain any of the control structures found in conventional programming languages. Requests, the syntax of which is quite simple, were entered directly from the console sequentially one after another and executed in the same sequence. However, SQL never became a tool for bank employees, airline and train ticket sellers, economists, and other employees of various companies who use information stored in databases. For them, simple SQL turned out to be too complex and inconvenient, despite its closeness to the natural language of questions.

In practice, a database is usually worked with through applications written by programmers in procedural languages, for example, C, Visual Basic, Pascal, Java, etc. Applications are often created in special visual development environments, such as Delphi, Microsoft Access, Visual dBase, etc. . n. At the same time, the application developer practically does not have to write program codes, since the development system does it for him. In any case, the work with the program code turns out to be minimal. These applications have a user-friendly graphical interface that does not force the user to directly enter queries in SQL. The application does this instead. However, the application may or may not use SQL to access the database. SQL is not the only, although it is a very effective means of retrieving, adding and changing data, and if it is possible to use it in an application, then it should be done.

Relational databases can and do exist independent of the applications that provide the user interface. If for some reason there is no such interface, then the database can be accessed using SQL, using the console or some application with which you can connect to the database, enter and send an SQL query (for example, Borland SQL Explorer ).

The SQL language is considered a declarative (descriptive) language, in contrast to the languages ​​in which programs are written. This means that SQL expressions describe what needs to be done, not how.

For example, in order to select information about the names and positions of employees of department 102 from the employees table, just run the following query:

SELECT Last name, Position FROM Employees WHERE Department=102;

In Russian this expression sounds like this:

CHOOSE Last name, position FROM Employees PROVIDED THAT Department = 102;

To change the value " Ivanov "on" Petrov " column Surname , just run the following query:

UPDATE Employees SET Last name = "Petrov" WHERE Last name = "Ivanov";

In Russian this expression looks like this:

UPDATE Employees INSTALLED Surname EQUAL " Petrov " WHERE Last name = "Ivanov" ;

You do not need to describe in detail the actions that the DBMS must perform to select the data specified in the query from the table. You simply describe what you want to receive. As a result of executing the query, the DBMS returns a table containing the data you requested. If there is no data in the database that matches the request, an empty table will be returned.

However, recent versions of SQL support computational control statements native to procedural control languages ​​(conditional branch and loop statements). Therefore, SQL is now not a purely declarative language.

In addition to retrieving, adding, modifying, and deleting data from tables, SQL allows you to perform all the necessary actions to create, modify, and secure databases. All these capabilities are distributed among three SQL components:

· DML (Data Manipulation Language - data manipulation language ) is intended to support the database: selection ( SELECT ), additions ( INSERT ), changes ( UPDATE ) and deletion ( DELETE ) data from tables. These operators (commands) can contain expressions, including calculated ones, as well as subqueries - queries contained within another query. In general, a query expression can be so complex that you can't immediately tell what it does. However, a complex query can be mentally broken down into parts that are easier to analyze. Likewise, complex queries are created from relatively easy-to-understand expressions (subqueries).

· DDL (Data Definition Language - data definition language ) is designed to create, modify and delete tables and the entire database. Examples of statements included in DDL are CREATE TABLE (create Table)," CREATE VIEW (create view), CREATE SHEMA (create a diagram), ALTER TABLE (change table), DROP (delete), etc.

· DCL (Data Control Language - data management language ) is designed to protect the database from various types of damage. The DBMS provides some data protection automatically. However, in some cases additional measures provided by the DCL should be considered.