Programming language

SQL (Structured Query Language - Structured Query Language) is a database management language for relational databases. SQL itself is not a Turing-complete programming language, but its standard allows you to create procedural extensions for it that extend its functionality into a full-fledged programming language.

The language was created in the 1970s under the name “SEQUEL” for the System R database management system (DBMS). It was later renamed “SQL” to avoid trademark conflicts. In 1979, SQL was first published as a commercial product, Oracle V2.

The first official language standard was adopted by ANSI in 1986 and ISO in 1987. Since then, several more versions of the standard have been created, some of them repeating the previous ones with minor variations, others taking on new significant features.

Despite the existence of standards, most common SQL implementations differ so much that code can rarely be ported from one DBMS to another without major changes. This is due to the length and complexity of the standard, as well as the lack of specifications in some important areas of implementation.

SQL was created as a simple, standardized way to retrieve and manipulate the data contained in a relational database. Later, it became more complex than intended and turned into a tool for the developer, not the end user. At present, SQL (mostly implemented by Oracle) remains the most popular of the database languages, although there are a number of alternatives.

SQL consists of four distinct parts:

1. Data Definition Language (DDL) is used to define data structures stored in a database. DDL statements allow you to create, modify, and delete individual objects in the database. Valid object types depend on the DBMS used and typically include databases, users, tables, and a number of smaller support objects such as roles and indexes.
2. Data Manipulation Language (DML) is used to retrieve and modify data in a database. DML statements allow you to retrieve, insert, modify, and delete data in tables. Sometimes data retrieval select statements are not considered part of the DML because they do not change the state of the data. All DML statements are declarative.
3. Data Access Definition Language (DCL) is used to control access to data in a database. DCL statements apply to privileges and allow you to grant and revoke rights to apply certain DDL and DML statements to certain database objects.
4. Transaction Control Language (TCL) is used to control the processing of transactions in the database. Typically, TCL statements include commit to commit changes made during a transaction, rollback to cancel them, and savepoint to split the transaction into several smaller parts.

It should be noted that SQL implements a declarative programming paradigm: each statement describes only the necessary action, and the DBMS decides how to perform it, i.e. plans the elementary operations necessary to perform the action and executes them. However, to effectively use the power of SQL, the developer needs to understand how the DBMS parses each statement and creates its execution plan.

Examples:

Hello World!:

Example for Oracle 10g SQL , Oracle 11g SQL

The string 'Hello, World!' is selected from the built-in table dual , used for queries that do not require access to real tables.

select "Hello World!" from dual ;

Factorial:

Example for Oracle 10g SQL , Oracle 11g SQL

SQL does not support loops, recursions, or user-defined functions. This example demonstrates a possible workaround using:

• pseudo-column level to create pseudo-tables t1 and t2 containing numbers from 1 to 16,
• aggregate function sum , which allows you to sum the elements of a set without explicitly using a loop,
• and the mathematical functions ln and exp , which allow you to replace the product (needed to calculate the factorial) with the sum (provided by SQL).

The string “0! = 1” will not be included in the resulting rowset, because attempting to evaluate ln(0) results in an exception.

Fibonacci numbers:

Example for Oracle 10g SQL , Oracle 11g SQL

SQL does not support loops or recursion, and concatenating fields from different rows in a table or query is not a standard aggregate function. This example uses:

• Binet's formula and mathematical functions ROUND , POWER and SQRT to calculate the n-th Fibonacci number;
• pseudo-column level to create a pseudo-table t1 containing numbers from 1 to 16;
• built-in function SYS_CONNECT_BY_PATH for ordered concatenation of received numbers.

SELECT REPLACE (MAX (SYS_CONNECT_BY_PATH (fib || ", " , "/" )), "/" , "" ) || "..." fiblist FROM ( SELECT n , fib , ROW_NUMBER () OVER (ORDER BY n ) r FROM (select n , round ((power ((1 + sqrt (5 )) * 0 . 5 , n ) - power ((1 - sqrt (5 )) * 0 . 5 , n )) / sqrt (5 )) fib from (select level n from dual connect by level<= 16 ) t1 ) t2 ) START WITH r = 1 CONNECT BY PRIOR r = r - 1 ;

Hello World!:

Example for versions Microsoft SQL Server 2005 , Microsoft SQL Server 2008 R2 , Microsoft SQL Server 2012 , MySQL 5 , PostgreSQL 8.4 , PostgreSQL 9.1 , sqlite 3.7.3

select "Hello World!" ;

Factorial:

Example for Microsoft SQL Server 2005 , Microsoft SQL Server 2008 R2 , Microsoft SQL Server 2012

The recursive definition of the factorial is used, implemented through a recursive query. Each line of the query contains two numeric fields, n and n!, and each subsequent line is calculated using the data from the previous one.

You can calculate integer factorials only up to 20!. When trying to calculate 21! an “Arithmetic overflow error” occurs, i.e. overflow occurs.

For real numbers, the factorial of 100 is calculated! (To do this, in the example, you need to replace bigint with float in the 3rd line)

Fibonacci numbers:

Example for Microsoft SQL Server 2005 , Microsoft SQL Server 2008 R2 , Microsoft SQL Server 2012

An iterative definition of Fibonacci numbers is used, implemented through a recursive query. Each line of the query contains two adjacent numbers in the sequence, and the next line is calculated as the (last number, sum of the numbers) of the previous line. Thus, all numbers except the first and last occur twice, so only the first numbers of each row are included in the result.

Factorial:

Example for Oracle 10g SQL , Oracle 11g SQL

This example demonstrates the use of the model statement, available since Oracle 10g, which allows query strings to be treated as array elements. Each line contains two fields - the line number n and its factorial f.

select n || "!=" || f factorial from dual model return all rows dimension by ( 0 d ) measures ( 0 f , 1 n ) rules iterate (17 ) ( f [ iteration_number ] = decode (iteration_number , 0 , 1 , f [ iteration_number - 1 ] * iteration_number ) , n [ iteration_number ] = iteration_number );

Fibonacci numbers:

Example for Oracle 10g SQL , Oracle 11g SQL

This example demonstrates the use of the model statement, available since Oracle 10g, which allows query strings to be treated as array elements. Each line contains two fields - the Fibonacci number itself and the concatenation of all numbers less than or equal to it. Iterative concatenation of numbers in the same query in which they are generated is easier and faster than aggregation as a standalone operation.

select max(s) || ", ..." from (select s from dual model return all rows dimension by ( 0 d ) measures ( cast (" " as varchar2 (200 )) s , 0 f ) rules iterate (16 ) ( f [ iteration_number ] = decode (iteration_number , 0 , 1 , 1 , 1 , f [ iteration_number - 1 ] + f [ iteration_number - 2 ]), s [ iteration_number ] = decode (iteration_number , 0 , to_char (f [ iteration_number ]), s [ iteration_number - 1 ] || ", " || to_char (f [ iteration_number ])) ) );

Factorial:

Example for MySQL versions 5

select concat (cast (t2 . n as char ), "!= " , cast (exp (sum (log (t1 . n ))) as char )) from ( select @ i := @ i + 1 AS n from TABLE , (select @ i := 0 ) as sel1 limit 16 ) t1 , ( select @ j : = @ j + 1 AS n from TABLE , (select @ j := 0 ) as sel1 limit 16 ) t2 where t1 . n<= t2 . n group by t2 . n

Fibonacci numbers:

Example for MySQL versions 5

Replace TABLE with any table you can access, such as mysql.help_topic .

select concat (group_concat (f separator ", " ), ", ..." ) from (select @ f := @ i + @ j as f , @ i := @ j , @ j := @ f from TABLE , (select @ i := 1 , @ j := 0 ) sel1 limit 16 ) t

Hello World!:

Example for Oracle 10g SQL , Oracle 11g SQL

This example uses an anonymous PL/SQL block that prints a message to standard output using the dbms_output package.

begin dbms_output . put_line("Hello, World!"); end ;

Factorial:

Example for Oracle 10g SQL , Oracle 11g SQL

This example demonstrates iterative factorial calculation using PL/SQL.

declare n number := 0 ; fnumber := 1 ; begin while (n<= 16 ) loop dbms_output . put_line (n || "! = " || f ); n : = n + 1 ; f : = f * n ; end loop ; end ;

Fibonacci numbers:

Example for Oracle 10g SQL , Oracle 11g SQL

This example uses the iterative definition of Fibonacci numbers. Already calculated numbers are stored in the varray data structure, analogous to an array.

declare type vector is varray (16 ) of number ; fib vector := vector(); i number ; svarchar2(100); beginfib . extend(16); fib (1 ) := 1 ; fib (2 ) := 1 ; s:=fib(1) || ", " || fib(2) || ", "; for i in 3 .. 16 loop fib (i ) : = fib (i - 1 ) + fib (i - 2 ); s := s || fib(i) || ", "; end-loop; dbms_output . put_line(s || "..." ); end ;

Quadratic equation:

Example for Oracle 10g SQL , Oracle 11g SQL

This example has been tested with SQL*Plus, TOAD, and PL/SQL Developer.

Pure SQL allows you to enter variables during query execution as substituted variables. To define such a variable, its name (in this case A, B, and C) must be used with an ampersand & in front of it each time the variable is to be referenced. When a query is executed, the user is prompted to enter the values ​​of all substituted variables used in the query. After entering the values, each reference to such a variable is replaced with its value, and the resulting query is executed.

There are several ways to enter values ​​for substituted variables. In this example, the first reference to each variable is preceded by a double ampersand && instead of a single ampersand. Thus, the value for each variable is entered only once, and all subsequent references to it will be replaced by the same value (when using a single ampersand in SQL*Plus, the value for each reference to the same variable must be entered separately). In PL/SQL Developer, references to all variables must be preceded by a single & , otherwise an ORA-01008 “Not all variables bound” error will occur.

The first line of the example specifies the character for the decimal separator, which is used when converting root numbers to strings.

The request itself consists of four different requests. Each query returns a string containing the result of calculations, in one of the cases (A=0, D=0, D>0 and D<0) и ничего — в трех остальных случаях. Результаты всех четырех запросов объединяются, чтобы получить окончательный результат.

alter session set NLS_NUMERIC_CHARACTERS = ". " ; select "Not a quadratic equation." ans from dual where && A = 0 union select "x = " || to_char (-&& B / 2 /& A ) from dual where & A != 0 and & B *& B - 4 *& A *&& C = 0 union select "x1 = " || to_char ((-& B + sqrt (& B *& B - 4 *& A *& C )) / 2 /& A ) || ", x2 = " || to_char (-& B - sqrt (& B *& B - 4 *& A *& C )) / 2 /& A from dual where & A != 0 and & B *& B - 4 *& A *& C > 0 union select "x1 = (" || to_char (-& B / 2 /& A ) || "," || to_char (sqrt (-& B *& B + 4 *& A *& C ) / 2 /& A ) || "), " || "x2 = (" || to_char (-& B / 2 /& A ) || "," || to_char (- sqrt (-& B *& B + 4 *& A *& C ) / 2 /& A ) || ")" from dual where & A != 0 and & B *& B - 4 *& A *& C< 0 ;

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

The name of the SQL language is usually pronounced by the letters "es-q-el". The mnemonic name "See-Quel" is sometimes used.

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

Create databases and tables with a complete description of their structure

Perform basic data manipulation operations: insert, modify, delete data

Perform 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) is used to retrieve and update data.

The SQL language is non-procedural, i.e. when using it, you must specify what information is to be obtained, and not how it can be obtained. SQL commands are ordinary English words (SELECT, INSERT, etc.). Consider first the SQL DML statements:

SELECT - fetching 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 relational algebra operations: select, project, and join.

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 as a result of the query. The from keyword specifies which table (or view) the data is being retrieved from.

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

To select all columns and all rows of a table, it is sufficient to issue a SELECT * FROM tbl;

Consider a Product table containing price information for various products:

Request result

SELECT * FROM Product;

will be the entire table Product.

You can select specific columns of a table using a query

SELECT col1, col2, … , coln FROM tbl;

So, the query result

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, the special keyword where is used, followed by a logical condition. If the entry 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= "autobalances".

Using the BETWEEN var1 AND var2 construct allows you to check if the values ​​of an expression fall within the interval from var1 to var2 (including these values):

SELECT * FROM Product where Price BETWEEN 3000 AND 3500;

By analogy with 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, a 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 for comparing a string with a pattern:

SELECT * FROM tbl where col_name LIKE "abc"

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

Two wildcard characters are allowed in the pattern: "_" 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 that starts with abc, "abc_" matches any string of 4 characters that starts with abc, "%z" matches any string that ends with z, and finally "%z%" - sequences of characters containing z.

You can find all records in the Product table where the Type value begins with the letter "a" as follows:

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

	truck scales

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

"%|_%" ESCAPE "|"

To check if the value matches 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 normal comparison operators in these cases can lead to incorrect results, since comparison against NULL results in UNKNOWN (unknown). Thus, the selection condition should look like this:

where col_name IS NULL instead of where col_name=NULL.

The default fetch result returns records in the same order as they are stored in the database. If you want to sort records by one of the columns, you must use the ORDER BY clause, after which the name of this column is indicated:

SELECT * FROM tbl ORDER BY col_name;

As a result of this query, records will be returned in ascending order of the value of the col_name attribute.

Records can also be sorted 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.

The records will be sorted by the col_name1 field; if there are several records with the same value in the col_name1 column, then they will be sorted by the col_name2 field.

If you want to sort records in reverse order (for example, by date descending), 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 result of the selection contains hundreds and thousands of records, their output and processing take a significant amount of time.

Therefore, information is often paginated and presented to the user in chunks. Pagination is used with the limit keyword followed by the number of entries to display. The following query retrieves the first 10 records while simultaneously sorting back on the col_name1 field:

SELECT * FROM tbl ORDER BY col_name1 DESC LIMIT 10

To extract the next 10 records, the limit keyword is used with two values: the first specifies the position from which to display the result, 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, use the LIMIT 20, 10 construct.

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

Why study 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 going through a few simple lessons, you will already 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 with one single purpose: to determine to provide access to them and process them in fairly short periods of time. If you know the SQL value, then it will be clear to you that this server belongs to the so-called "non-procedural" languages. That is, its capabilities include only a description of any components or results that you want to see in the future on the site. But when does not indicate what exactly the results are going to get. Each new request in this language is, as it were, an additional "add-on". It is in the order in which they are entered in 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 lot of a wide variety of queries. So what can you do if you learn this important programming language?

• create a variety of tables;
• receive, store and modify the received data;
• change the structure of tables at your discretion;
• combine the information received 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 attend the SQL "for dummies" courses, then you will get detailed information about the commands that are used in creating queries with 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 manages data. It is used to provide access to different users 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 course 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 are 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 removed and new ones added. Today, all those who take SQL Server "for dummies" courses know that there are several types of allowed actions:

1. Object type - the user is allowed to execute any command only in relation to a specific object that is 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 their capabilities, created a table, then he is considered its owner. Therefore, it is in his 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.

The History of SQL Creation

This language was created by the IBM Research Lab in 1970. At that time, its name was somewhat different (SEQUEL), but after a few years of use it was changed, slightly reduced. Despite this, even today, many well-known world experts in the field of programming still pronounce the name in the old way. SQL was created with the sole purpose of inventing 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. Prior to 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 understand it in a few weeks.

Structured Query Language (structured query language) or SQL- is a declarative programming language for use in quasi-relational databases. Many of the original features of SQL were taken over by tuple calculi, but recent extensions to SQL include more and more relational algebra.
SQL was originally created by IBM, but many vendors have developed their own dialects. It was adopted as a standard by the American National Standards Institute (ANSI) in 1986 and by ISO in 1987. In the SQL Programming Language Standard, ANSI stated that the official pronunciation of SQL is "es cue el". However, many database specialists used the "slang" pronunciation "Sequel", reflecting the language's original name, Sequel, which was later changed due to trademark and naming conflicts at IBM. Programming for beginners.
SQL programming language was revised in 1992 and this version is known as SQL-92's. Then 1999 was revised again to become SQL: 1999 (AKA SQL3). Programming for dummies. SQL 1999 supports objects that were not previously supported in other versions, but only in late 2001, only a few database management systems supported SQL implementations: 1999.
SQL, although defined as ANSI and ISO, has many variations and extensions, most of which have characteristics of their own, such as the Oracle corporation's "PL/SQL" implementation, or the Sybase and Microsoft implementation called "Transact-SQL", which can confuse the user. the basics of programming. It's also not uncommon for commercial implementations to omit support for key features of the standard, such as data types such as date and time, in favor of some of their own. As a result, unlike ANSI C or ANSI Fortran, which can usually be ported from platform to platform without major structural changes, SQL programming language queries can rarely be ported between different database systems without major modifications. Most people in the database field believe that this lack of interoperability is intentional in order to provide each developer with their own database management system and tie the customer to a particular database.
As the name suggests, the SQL programming language is designed for a specific, limited purpose - querying the data contained in a relational database. As such, it is a set of programming language instructions for making data samples, rather than a procedural language like C or BASIC, which are designed to solve a much wider range of problems. Language extensions such as "PL/SQL" are designed to address this limitation by adding procedural elements to SQL while retaining the benefits of SQL. Another approach is to allow SQL queries to embed procedural programming language commands and interact with the database. For example, Oracle and others support the Java language in the database, while PostgreSQL allows functions to be written in Perl, Tcl, or C.
One SQL joke: "SQL is neither structured nor a language." The point of the joke is that SQL is not a Turing language. .

Select * from T

C1	C2
1	a
2	b

C1	C2
1	a
2	b

Select C1 from T

C1
1
2

C1	C2
1	a
2	b

Select * from T where C1=1

C1	C2
1	a

Given a table T, a Select * from T query will display all the elements of all rows in the table.
From the same table, a Select C1 from T query will display the elements from column C1 of all rows in the table.
From the same table, the query Select * from T where C1=1 will display all the elements of all rows where the value of column C1 is "1".

SQL keywords

SQL words are divided into a number of groups.

The first one is Data Manipulation Language or DML(data management language). DML is a subset of the language used to query databases, add, update, and delete data.

• SELECT is one of the most commonly used DML commands and allows the user to specify a query as a description of the desired result as a set. The query doesn't specify how the results should be arranged - translating the query into a form that can be done in the database is the job of the database system, more specifically the query optimizer.
• INSERT is used to add rows (formal set) to an existing table.
• UPDATE is used to change data values ​​in an existing table row.
• DELETE specifies the existing rows to be removed from the table.

Three other keywords can be said to fall into the DML group:

• BEGIN WORK (or START TRANSACTION, depending on the dialect of SQL) can be used to mark the start of a database transaction that will either run completely or not run at all.
• COMMIT states that all data changes in after the operation is committed are saved.
• ROLLBACK specifies that all data changes since the last commit or rollback should be destroyed, up to the point that was committed to the database as a "rollback".

COMMIT and ROLLBACK are used in areas such as transaction control and locks. Both instructions complete all current transactions (sets of database operations) and release all locks on changing data in tables. The presence or absence of a BEGIN WORK or similar statement depends on the particular implementation of SQL.

The second group of keywords refers to the group Data Definition Language or DDL (Data Definition Language). DDL allows the user to define new tables and related elements. Most commercial SQL databases have their own DDL extensions that allow control over non-standard but usually vital elements of a particular system.
The main points of DDL are the create and delete commands.

• CREATE specifies the objects (such as tables) to be created in the database.
• DROP specifies which existing objects in the database will be dropped, usually permanently.
• Some database systems also support the ALTER command, which allows the user to modify an existing object in different ways, such as adding columns to an existing table.

The third group of SQL keywords is Data Control Language or DCL(Data Control Language). DCL is responsible for data access rights and allows the user to control who has access to view or manipulate the data in the database. There are two main keywords here:

• GRANT - allows the user to perform operations
• REVOKE - removes or restricts the user's ability to perform operations.

Database systems using SQL

• InterBase
• MySQL
• Oracle
• PostgreSQL
• SQL Server

How to become a website development professional and start earning? Inexpensive video courses with an introductory introduction.

Language SQL

So, in general terms, we got acquainted with the basic concepts of database theory, installed and configured for MySQL. Now is the time to learn how to manipulate data stored in databases. For this we need SQL - structured query language. This language makes it possible to create, edit and delete information stored in databases, create new databases and much more. SQL is an ANSI (American National Standards Institute) and ISO (International Standards Organization) standard.

A bit of history

First International Language Standard SQL was adopted in 1989, it is often called SQL/89 . Among the shortcomings of this standard, first of all, the fact that it set many important properties as defined in the implementation is distinguished. This resulted in many discrepancies in the implementations of the language by different manufacturers. In addition, there have been complaints about the lack of references in this standard to practical aspects of the language, such as its embedding in the C programming language.

Next International Language Standard SQL was adopted at the end of 1992 and became known as SQL/92 . It turned out to be much more accurate and complete than SQL/89 although it was not without flaws. At present, most systems almost completely implement this standard. However, as you know, progress cannot be stopped, and in 1999 a new standard appeared SQL :1999, also known as SQL3. SQL3 is characterized as "object-oriented SQL ” and is the basis of several object-relational database management systems (for example, Oracle's ORACLE8, Informix's Universal Server, and IBM's DB2 Universal Database). This standard is not just a merger SQL-92 and object technology. It contains a number of extensions of the traditional SQL , and the document itself is designed in such a way as to achieve more effective work in the field of standardization in the future.

As far as MySQL is concerned, it is entry-level SQL92, contains several extensions to this standard, and aims to fully support the ANSI SQL99 standard without sacrificing code speed or quality.

Next, talking about the basics of the language SQL , we will stick to its implementation in the MySQL DBMS.

Basic language operators SQL

The functions of any DBMS include:

1. creating, deleting, changing a database (DB);
2. adding, changing, deleting, assigning user rights;
3. entering, deleting and changing data in the database (tables and records);
4. fetching data from the database.

Only database administrators or privileged users have access to the first two functions. Let's consider how the last two tasks are solved (in fact, these are seven tasks).

Before doing anything with the data, you need to create tables in which this data will be stored, learn how to change the structure of these tables and delete them if necessary. For this, in the language SQL there are operators CREATE TABLE, ALTER TABLE and DROP TABLE.

CREATE TABLE statement

mysql>CREATE TABLE Persons (id INT PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(50), last_name VARCHAR(100), death_date INT, description TEXT, photo INT, citienship CHAR(50) DEFAULT "Russia"); Example 10.1. Creating the Persons Table

With the MySql-specific SHOW command, you can view existing databases, tables in a database, and fields in a table.

Show all databases :

mysql>SHOW databases;

Make the book database current and show all tables in it:

mysql>usebook; mysql>show tables;

Show all columns in Persons table:

mysql> show columns from Persons;

DROP TABLE statement

Operator DROP TABLE deletes one or more tables. All table data and definitions are removed, so care must be taken when using this command.

Syntax:

DROP TABLE table_name [, table_name, ...]

In MySQL version 3.22 and later, you can use the IF EXISTS keywords to raise an error if the specified tables do not exist.

The RESTRICT and CASCADE options make it easier to port the program from other DBMSs. They are not active at the moment.

mysql> DROP TABLE IF EXISTS Persons, Artifacts, test; Example 10.2. Using the DROP TABLE statement

ALTER TABLE statement

Operator ALTER TABLE provides the ability to change the structure of an existing table. For example, you can add or remove columns, create or drop indexes, or rename columns or the table itself. You can also change the comment for the table and its type.

Syntax:

ALTER TABLE table_name alter_specification [, alter_specification ...]

You can make the following changes to the table (all of which are recorded in alter_specification ):

• adding a field:

  ADD column_definition

  ADD (column_definition, column_definition, ...)

• adding indexes:

  ADD INDEX [index_name] (indexed_column_name,...) or ADD PRIMARY KEY (indexed_column_name, ...) or ADD UNIQUE [index_name] (indexed_column_name,...) or ADD FULLTEXT [index_name] (index_column_name,...)

• field change:

  ALTER column_name (SET DEFAULT literal | DROP DEFAULT) or CHANGE old_column_name column_definition or MODIFY column_definition

• deleting a field, index, key:

  DROP column_name DROP PRIMARY KEY DROP INDEX index_name

• table rename:

  RENAME new_table_name

• reordering table fields:

  ORDER BY field

  table_options

If the operator ALTER TABLE is used to change the type definition of a column, but DESCRIBE table_name shows that the column has not changed, then perhaps MySQL ignores this modification for one of the reasons described in a special section of the documentation. For example, if you try to change a VARCHAR column to CHAR, MySQL will continue to use VARCHAR if the table contains other variable length columns.

Operator ALTER TABLE while running, creates a temporary copy of the original table. The required change is made on the copy, then the original table is dropped and the new one is renamed. This is done so that all updates, except for failed ones, automatically get into the new table. At runtime ALTER TABLE the original table is readable by other clients. Update and write operations on this table are suspended until a new table is ready. It should be noted that when using any other option to ALTER TABLE except RENAME , MySQL will always create a temporary table, even if the data does not strictly need to be copied (for example, when changing the name of a column).

Example 10.3. Let's add a field to record the person's year of birth to the created Persons table:

mysql> ALTER TABLE Persons ADD bday INTEGER AFTER last_name; Example 10.3. Adding a field to the Persons table to record a person's year of birth

So, we have learned how to work with tables: create, delete and modify them. Now let's figure out how to do the same with the data that is stored in these tables.

SELECT statement

Operator SELECT used to retrieve rows selected from one or more tables. That is, with its help, we specify the columns or expressions to be extracted ( select_expressions ), the tables ( table_references ) from which the selection should be made, and, possibly, the condition ( where_definition ) that the data in these columns must match, and the order in which the data is to be given.

In addition, the operator SELECT can be used to extract rows computed without reference to any table. For example, to calculate what 2*2 is equal to, you just need to write

mysql> SELECT 2*2;

Simplified operator structure SELECT can be represented as follows:

The square brackets mean that the use of the operator in them is optional, the vertical bar | means a list of possible options. The ORDER BY keyword is followed by a column name, a number (unsigned integer), or a formula and ordering method (ascending - ASC , or descending - DESC ). The default is ascending order.

When we write "*" in select_expression, it means to select all columns. In addition to "*", functions like max , min , and avg can be used in select_expressions.

Example 10.4. Select from the Persons table all data for which the first_name field has the value "Alexander" :

Select the title and description (title , description ) of artifact number 10:

INSERT statement

Operator INSERT inserts new rows into an existing table. The operator has several forms. The table_name parameter in all of these forms specifies the table in which the rows are to be entered. The columns for which values ​​are set are specified in the list of column names (column_name ) or in the SET part.

Syntax:

INSERT table_name [(column_name,...)] VALUES (expression,...),(...),...

This command form INSERT inserts rows exactly as specified in the command. Columns are listed in parentheses after the table name, and their values ​​are listed after the VALUES keyword.

For example:

mysql> INSERT INTO Persons (last_name, bday) VALUES("Ivanov", "1934");

will insert a row into the Persons table in which the values ​​of the last name (last_name ) and date of birth (bday ) will be set to "Ivanov" and "1934" respectively.

INSERT table_name [(column_name,...)] SELECT ...

This command form INSERT inserts rows selected from another table or tables.

For example:

will insert into the Artifacts table in the "author" field ( author ) the value of the identifier selected from the Persons table by the condition that the person's last name is Ivanov.

INSERT table_name SET column_name=expression, column_name=expression, ...

For example:

mysql> INSERT INTO Persons SET last_name="Petrov", first_name="Ivan";

This command will insert the value "Petrov" into the Persons table in the last_name field, and the string "Ivan" in the first_name field.

Form INSERT ... VALUES multilist is supported in MySQL version 3.22.5 and later. Expression syntax column_name=expression supported in MySQL version 3.22.10 and later.

The following agreements apply.

• If no column list is specified for INSERT ... VALUES or INSERT ... SELECT, then the values ​​for all columns must be defined in the VALUES() list or as a result of the work SELECT. If the order of the columns in the table is unknown, you can use DESCRIBE table_name.
• Any column not explicitly given a value will be set to its default value. For example, if a given list of columns does not specify all the columns in a given table, then the columns not mentioned are set to their default values.
• The expression can refer to any column that was previously listed in the list of values. For example, you can specify the following:

  mysql> INSERT INTO table_name (col1,col2) VALUES(15,col1*2);

  But you can't specify:

  mysql> INSERT INTO table_name (col1,col2) VALUES(col2*2,15);

We have not yet discussed the three optional parameters present in all three forms of the command: LOW_PRIORITY , DELAYED , and IGNORE .

The LOW_PRIORITY and DELAYED options are used when a large number of users are working on a table. They prescribe to set the priority of this operation over the operations of other users. If the LOW_PRIORITY keyword is specified, then the execution of this command INSERT will be delayed until other clients have finished reading this table. In this case, the client must wait until the given insert command is completed, which can take a significant amount of time if the table is heavily used. In contrast, the command INSERT DELAYED allows this client to continue the operation immediately, regardless of other users.

If in a team INSERT the IGNORE keyword is specified, then all rows that have duplicate keys PRIMARY or UNIQUE in this table will be ignored and not included in the table. If you do not specify IGNORE , then this insert operation stops when a row is found that has a duplicate value of an existing key.

UPDATE statement

Syntax:

Operator UPDATE updates the values ​​of existing table columns according to the entered values. The SET statement specifies which columns should be modified and what values ​​should be set in them. The WHERE clause, if present, specifies which rows to update. Otherwise, all rows are updated. If an ORDER BY clause is specified, the rows will be updated in the order specified in it.

If the LOW_PRIORITY keyword is specified, then the execution of this command UPDATE delayed until other clients have finished reading this table.

If the IGNORE keyword is specified, then the update command will not be aborted even if a duplicate key error occurs. Rows that cause conflicts will not be updated.

If the expression that specifies a new value for a column uses the name of that field, then the command UPDATE uses the current value for that column. For example, the following command sets the death_date column to a value one greater than its current value:

mysql> UPDATE Persons SET death_date=death_date+1;

In MySQL 3.23, you can use the LIMIT # option to ensure that only a given number of rows have been modified.

For example, such an operation will replace the title title in the first row of our table of exhibits with the string "Lamp computer":

mysql> UPDATE Artifacts SET title="(!LANG:Lamp" Limit 1;!}

DELETE statement

Operator DELETE removes rows from table table_name that satisfy the conditions specified in where_definition and returns the number of records removed.

If the operator DELETE runs without a WHERE definition, then all rows are deleted.

Syntax:

For example, the following command will delete from the Persons table