Basic commands of the sql query language with examples. Basic SQL commands that every programmer should know What command is used to query data

Structured Query Language(Structured Query Language) developed by corporations IBM in the early 1970s. In 1986 SQL was first standardized by organizations ANSI.

SQL is a powerful and at the same time not complicated language for managing databases. It is supported by almost all modern databases. SQL are divided into two subsets of commands: DDL (Data Definition Language- data definition language) and DML (Data Manipulation Language- data processing language). Teams DDL are used to create new databases, tables and columns, and the commands DML- for reading, writing, sorting, filtering, deleting data.

This article will take a closer look at the commands DML, since they have to be used much more often.

DDL Commands

CREATE- used to create new tables, columns and indexes.

DROP- used to delete columns and indexes.

ALTER- used to add new columns to tables and change certain columns.

DML Commands

SELECT- the most commonly used command, used to obtain a set of data from a database table. The SELECT command has the following syntax:

SELECT field_list1 FROM table_name ]

Operators inside square brackets are optional, and the vertical bar means that one of the specified phrases must be present, but not both.

For example, let's create a simple query to obtain data from the "name" and "phone" fields of the "friends" table:

SELECT name, phone FROM friends

If you need to get all the fields of the table, then it is not necessary to list them, just put an asterisk (*):

SELECT * FROM friends

To exclude duplicate entries from the displayed list, use the keyword DISTINCT:

SELECT DISTINCT name FROM friends

If it is necessary to obtain a separate record, then the operator is used WHERE. For example, we need to get the phone number of “Vasya Pupkin” from the “friends” table:

SELECT * FROM friends WHERE name = "Vasya Pupkin"

or vice versa, we need to find out who owns the phone 44-65-01:

SELECT * FROM friends WHERE phone = "44-65-01"

In addition, you can use wildcard characters, thereby creating search patterns. For this purpose the operator is used LIKE. The LIKE operator has the following substitution operators:

* - matches a string consisting of one or more characters;

Matches any one character;

Matches one character from a specific set;

For example, to retrieve records from the "name" field containing the word "Vasya", the request would look like this:

SELECT * FROM friends WHERE name LIKE "*Vasya*"

The operator is used to determine the order in which data is returned ORDER BY. Without this operator, the order of the returned data cannot be predicted. Keywords A.S.C. And DESC allow you to determine the direction of sorting. ASC sorts in ascending order, and DESC orders in descending order.

For example, a request to get a list of records from the "name" field in alphabetical order would look like this:

SELECT * FROM friends ORDER BY name

Note that the ASC keyword is not required because it is the default.

INSERT- this command is used to add a new record to the table. It is written as follows:

INSERT INTO table_name VALUES ( value_list)

Please note that the value types in the list of values ​​must match the value types of the table fields, for example:

INSERT INTO friends VALUES ( "Anka the Machine Gunner", "32-09-81" )

This example adds a new entry to the friends table with the specified values.

UPDATE- This command is used to update data in a table and is most often used in conjunction with the WHERE clause. The UPDATE command has the following syntax:

UPDATE table_name SET field_name = meaning

If you omit the WHERE clause, the data in all defined fields in the table will be updated. For example, let’s change Vasya Pupkun’s phone number:

UPDATE friends SET phone = "55-55-55" WHERE name = "Vasya Pupkin"

DELETE- as you probably already understood, this command is used to delete records from the table. Like UPDATE, the DELETE command is usually used with a WHERE clause; if this clause is omitted, all data from the specified table will be deleted. The DELETE command syntax is as follows:

DELETE FROM table_name

For example, let's remove this annoying Vasya Pupkin from our table :) :

DELETE FROM friends WHERE name = "Vasya Pupkin"

The end

In this article I talked about the basic operators SQL, they are quite sufficient for working with databases; I will tell you about the remaining operators in more detail another time.

3.1 General provisions

The RMD mechanisms outlined above formed the basis of data manipulation languages. Note that it is extremely rare that RA or RI are accepted as the complete basis of any RDB language. Typically the language is based on some mixture of algebraic and logical constructs.

The implementation of the concept of operations focused on tabular presentation of data made it possible to create a compact language with a small set of sentences - SQL. This language can be used as an interactive language for executing queries and as an embedded language for building application programs.

Modern DBMSs typically support a single, integrated SQL language that contains a variety of tools to provide a basic user interface for working with the database. It contains the following groups of commands:

1) Data Definition Commands, which are used to create, modify, delete tables and indexes, as well as to define the database schema;

2) Data Manipulation Commands for changing data (editing existing data, adding and deleting records);

3) Data Query Commands for retrieving existing data;

4) Transaction Control Commands for saving or rolling back changes to the database;

5) Data Control Commands for checking the integrity of the database, granting and revoking data access privileges.

However, SQL provides other capabilities, such as performing calculations and transformations, ordering records, and grouping data.

The peculiarity of SQL commands is that they are focused more on the final result of data processing than on the procedure for this processing. SQL itself determines where the data is located, what indexes and sequences of operations should be used to execute it efficiently.

Let's look at the syntax of basic SQL commands. When presenting the material, we will use the following notation:

– an asterisk (*) means “all” and is used in the usual programming sense, i.e. "all cases satisfying the definition";

– square brackets () mean that the structures enclosed in these brackets are optional, i.e. may be omitted;

– curly brackets (()) mean that the constructions enclosed in these brackets should be considered as whole syntactic units; these brackets allow you to clarify the parsing order of syntactic constructs, replacing the usual brackets used in SQL syntax;

– an ellipsis (...) indicates that the immediately preceding syntactic unit may optionally be repeated one or more times;

– a straight line (|) means there is a choice of two or more options; for example, the construction [term_1 | term_2] means that you can choose one of two terms (either term_1 or term_2); in this case, term_1 is selected by default; the absence of this entire structure will be perceived as a choice of term_1;

– semicolon (;) ending element of SQL statements; this sign must be present after each command;

– comma (,) is used to separate list elements;

– spaces () can be introduced to increase clarity between any syntactic constructions of SQL sentences;

– capital Latin letters and symbols are used to write SQL language constructs and must be written unchanged;

– lowercase letters are used to write constructions that must be replaced with specific values ​​selected by the user, and for definiteness, individual words of these constructions are linked together by an underscore (_);

Any conceptual information about relational databases and tables is only useful if you know how to interact with the data. The SQL language consists of structured commands for adding, modifying, and deleting data from a database. These commands form the basis for interacting with the PostgreSQL server.

This section is about the "anatomy" of SQL commands. It examines the structural components of commands, describing the functions of each component and their mutual relationships. The standard PostgreSQL command line client, psql, prints the results of the commands in the examples given.

Most SQL command examples are database related booktown. All psql output is prefixed with the form booktown=#.

Some examples use a test database testdb. By default, the psql prompt displays only the database name and characters =# , indicating that the system is ready for a new command (although you will see that the symbol = changes dynamically as the state of the SQL input data is monitored). The book provides this prompt along with the SQL input and output to help you get comfortable with the psql client output.

The psql client is described in detail in Chapter 4. It is mentioned here only to clarify the style of the SQL command examples.

Note
The booktown database schema (along with example records) is located in the booktown.sql file on the CD. To install this database, enter the command psql - U postgres template! at the command prompt. - f /mnt/cdrom/booktown.sql, where /mnt/cdrom is the path to the mounted CD and postgres is the PostgreSQL superuser name.

Anatomy of SQL Commands

SQL commands always begin with an action ( verb) - words or groups of words describing the operation being performed. In addition, an SQL command usually contains one or more sections that clarify its meaning. In table 3.2 lists the main actions of SQL.

Table 3.2. Basic PostgreSQL Actions.

Select is a powerful tool for creating queries to retrieve information. Here is its preliminary syntax:

Select < fields, expressions or* > From < source tables >

Where < condition for selecting records > Group By < fields for grouping >

Having < group selection condition > Order By < fields for sorting >\

Let's look at the command phrases (not all phrases are required).

Select< expression > [ < pseudonym > ][,…]

From <таблица> [<тип связи> Join <таблица> On <условие связи>]…

Distinct – duplicate request records are not displayed. The field name can be compound, including the table name, a dot, and the table name itself. If the table name contains spaces, it is enclosed in apostrophes. Instead of a field name, you can specify an asterisk if you need to build a selection from all fields of the table.

Nickname – specifies the name of the column (spaces are not allowed). To link tables, use the phrase Join . The type of connection is specified by the words: Left / Right (the query includes all records from the table on the left/right side of the query), Inner (only records with matching link keys are included).

Example setting a basic query on the construction database:

SelectStroiki.Ds,Stroiki.Ss,Stroiki.Fs,Stroiki.M,Stroiki.Em,Podrjdhiki.Np,Zakazhiki.NzFROM“stroiki.DB”Stroiki

Inner Join “zakazhiki.db” Zakazhiki ON (Stroiki.Kz = Zakazhiki.Kz)

Inner Join “podrjdhiki.db” Podrjdhiki ON (Stroiki.Kp = Podrjdhiki.Kp)

As a result of executing the query, a set of columns is obtained, the headers of which may contain field names. If you are not satisfied with the default names, you can assign your own (aliases) by specifying them after the word AS. The expression can use special arithmetic functions that operate “vertically”: the group average (Avg), minimum (Min), maximum (Max), sum (Sum), number of records in group (Count). The function can take an asterisk as an argument (Count(*)), which means counting all records included in the sample.

Where<условие отбора> - condition for selecting records in the request. The conditions allow the use of logical operators And, Or, Not and parentheses. Conditions, in addition to any Pascal functions, may contain the following SQL statements:

<выражение> Like<шаблон>allows you to build a comparison condition using a template, a set of symbols: "_" (undefined symbol), "%" (any symbols, for example: Where Ns Like "School%"), % (any symbols from n to k, for example: Like "%");

<выражение> Between<нижнее значение>And<верхнее значение>checks whether the expression is within the specified range (Where Between 0 And 1000000);

<выражение> In(<выражение>,<выражение>,...) checks whether the expression to the left of the word IN is among those listed to the right of it (Where Kz In (1,2,6)).

Group By<колонка>[,<колонка>...] - specify the columns by which the output data is grouped. All table records for which the column values ​​match are displayed in the selection as a single row. Grouping is convenient for obtaining some data characteristics (sums, number of records, average) of the group.

Example. Display the number of employees, maximum, average, minimum and final salaries by department (Pord field) using the SOTRUDNIKI table.

Select podr, Count(*),Min (zarpl), Avg (zarpl), Max(zarpl), Sum (/nipl)

From Sotrudniki Group By podr Order By podr

Having<условие отбора группы> sets the criterion for selecting groups formed during the sampling process.

Order By<колонка>[Аsc/Desc] [,column> …] - an option that will set the ordering in ascending order (Desc). The column is specified by the number or field name (Order By Kr, Kz).

Let's look at other SQL commands.

Create Table< Nametables> (< field> < typefields> ,...) - creating a table (Create Table Kadr (Tab Integer, Fam Char (30) Not Null Primary Key (Tab))). The mandatory presence of a value in the field is specified by the Not Null parameter. Basic field types in SQL in Delphi: SmallInt (Short), Integer (Long Integer), Numeric(x,y), Float (x,y), Char (n), Data, Boolean, Time, Money, Autoinc. For other DBMSs, field types may be different.

Create View<имя представления> [(<имя столбца>,...)] As - creating a view with new column names (Create View NameStroek As Select Stroiki.Ns FROM Stroiki).

Alter Table<имя таблицы>(Add\Modify\Drop)<поле> [<тип поля>] - changing field values ​​(Update Kadr Set Oklad=1.5*Oklad Where Sех="Workshop N2").

Insert Into (<список полей>) (Values ​​(<список значений>)\Select...)- inclusion of a new record or group of records from another table.

Insert Into Zakazhiki (Kz.Nz) Values ​​(3,"ZIL1); //include one record Insert Into Podrjdhiki (Kp.Np) Select KPodr, NPodr From SpravPodr Where DSozd >01.01.80; //include a group of records from the SpravPodr table

Create Index<имя индекса>On<таблица> (<поле>[(dsc\esc)],...)- creating an index (Create index indproba on stroiki (kz, kp)).

Drop Table/View<имя> - deleting a table/view (Drop Table Stroiki).

Drop Index ["<имя таблицы>".]<имя индекса> - deleting the index.

Drop Index[“<имя таблицы>".] Primary- deleting the main index.

Delete From< Nametables> - deleting records.

Any type of query can have a subquery called a subquery. Subquery is a query whose result is used in the selection condition in the Where expression of another outer query. The subquery is enclosed in parentheses.

Example. Display large construction projects with estimates above the average for construction projects: Select * From Stroiki Where Ss > (Select Avg(Ss) From Stroiki).