Question number 1. SQL and its history. 1

Question number 2. Description of the basic SQL statements. 1

Arithmetic functions. 4

String processing functions. five

Special functions. 6

Functions for handling date .. 7

Using aggregate functions in queries. 7

Question number 1. SQL and its history

The only communication between database administrators, designers, developers, and users with a relational database is the Structured Query Language (SQL). SQL is a fully featured language for manipulating data in relational databases. It is currently the generally accepted, standard interface for relational databases such as Oracle, Informix, Sybase, DB / 2, MS SQL Server and a number of others (ANSI and ISO standards). SQL is a non-procedural language that is designed to handle sets of rows and columns in relational database tables. Although there are extensions that allow procedural processing. Database designers use SQL to create all physical objects in a relational database.

The theoretical foundations of SQL were laid in the famous article by Codd, which laid the foundation for the development of the theory of relational databases. The first practical implementation was carried out in the research laboratories of IBM Chamberlin D.D. and Royce R.F. The commercial application of SQL was first implemented in Ingres. One of the first industrial relational DBMSs is Oracle. Essentially, a relational database management system is software that manages the operation of a relational database.

The first international standard for the SQL language was adopted in 1989 (SQL-89). At the end of 1992, the new international standard SQL-92 was adopted. Currently, most relational database vendors use it as their base. However, the work on standardizing the SQL language is far from complete and a draft SQL-99 standard has already been developed, which introduces the concept of an object into the language of the language and allows it to be referred to in SQL statements: In the original version of SQL there were no data flow control commands, they appeared in the recently adopted ISO / IEC 9075-5: 1996 SQL Supplementary Part.

Each specific DBMS has its own implementation of SQL, which generally supports a certain standard, but has its own characteristics. These implementations are called dialects. For example, the 1SO / IEC 9075-5 standard provides for objects called Persistent Stored Modules or PSMs. In Oracle DBMS, the PL / SQL extension is analogous to the above extension of the standard ".

Question number 2. Description of basic SQL statements

SQL consists of a set of commands for manipulating data in a relational database that allow you to create objects in a relational database, modify data in tables (insert, delete, correct), change database relationship schemas, perform calculations on data, make selections from a database, maintain security and data integrity.

The entire set of SQL commands can be broken down into the following groups:

Data definition commands (DDL - Data Defininion Language);

Data manipulation commands (DML - Data Manipulation Language);

· Commands for data selection (DQL - Data Query Language);

· Commands for managing transactions;

· Data management commands.

When executed, each SQL command goes through four processing phases:

· The parsing phase, which includes checking the command syntax, checking the names of tables and columns in the database, and preparing the initial data for the optimizer;

Optimization phase, which includes the substitution of valid names of tables and columns of the database in the view, identification possible options execution of the command, determination of the cost of execution of each option, selection best option based on internal statistics;

· The phase of generating the executable code, which includes the construction of the executable command code;

· Phase of command execution, which includes execution of command code.

Currently optimizer is an part of any industrial SQL implementation. The optimizer's work is based on collecting statistics about the commands executed and performing equivalent algebraic transformations on database relations. These statistics are stored in the system database directory. System directory is a data dictionary for each database and contains information about tables, views, indexes, columns, users and their access privileges. Each database has its own system catalog, which is a collection of predefined database tables.

Table 8.1 lists the SQL commands according to accepted standard, with the exception of some commands that are practically not used in dialects.

Table 8.1. Typical List of SQL Commands

Command	Description
Commands for defining these objects
ALTER TABLE	Modifies the table description (relationship schema)
CREATE EVENT	Creates a timer event on the database
CREATE INDEX	Will create an index on the table
CREATE SEQUENCE	Creates a sequence
CREATE TABLE	Defines a table
CREATE TABLESPACE	Will create a tablespace
CREATE TRIGGER	Creates a trigger on the database
CREATE VIEW	Defines a view on tables
DROP INDEX	Physically removes an index from the database
DROP SEQUENCE	Removes sequence
DROP TABLE	Physically removes a table from the database
DROP TABLESPACE	Removes a tablespace
DROP VIEW	Removes a view
Data manipulation commands
DELETE	Removes one or more rows from a database table
INSERT	Inserts one or more rows into a database table
UPDATE	Updates column values \u200b\u200bin a database table
Data fetch commands
SELECT	Runs a query to fetch data from tables and views
UNION	Combines the results of two or more SELECT statements into one selection
Transaction Commands
COMMIT	Completes the transaction and physically updates the current state of the database
ROLLBACK	Completes a transaction and returns the current state of the database at the time of the last completed transaction and checkpoint
SAVEPOINT	Designates a checkpoint within a transaction
Data management commands
ALTER DATABASE	Modifies storage groups or transaction logs
ALTER DBAREA	Resizes database storage areas
ALTER PASSWORD	Changes the password for accessing the database
ALTER STOGROUP	Changes the composition of storage areas in a storage group
CHECK DATABASE	Checks the integrity of the database
CHECK INDEX	Checks the integrity of the index
CHECK TABLE	Checks the integrity of the table and index
CREATE DATABASE	Physically creates a database
CREATE DBAREA	Creates a database storage area
CREATE STOGROUP	Creates a storage group
CREATE SYSNONYM	Creates a synonym for a table or view
DEINSTALL DATABASE	Makes the database not available to users computer network
DROP DATABASE	Physically drops databases
DROP DBAREA	Physically removes the database storage area
DROP STOGROUP	Deletes a storage group
GRANT	Defines user privileges and access control to the database
INSTALL DATABASE	Makes the database available to users of the computer network
LOCK DATABASE	Locks the currently active database
REVOKE	Revokes user privileges and access control to the database
SET DEFAULT STOGROUP	Defines the default storage group
UNLOCK DATABASE	Releases the currently active database
UPDATE STATISTIC	Updates statistics for the database
Other commands
COMMENT ON	Places comments to the description of database objects in the system catalog
CREATE SYNONYM	Defines alternative names for database tables and views in the system catalog
DROP SYNONYM	Removes aliases for database tables and views from the system catalog
LABEL	Changes labels system descriptions
ROWCOUNT	Calculates the number of rows in a database table

The set of SQL commands listed in the table is not complete. This list is provided to give you an impression of the overall capabilities of SQL. For getting complete list commands, refer to the appropriate manual for the specific DBMS. It should be remembered that SQL is the only way for all categories of users to communicate with relational databases.

Arithmetic functions

SQL supports full set arithmetic operations and mathematical functions for building arithmetic expressions on database columns (+, -, *, /, ABS, LN, SQRT, etc.).

The basic built-in math functions are listed in Table 8.2 below.

Math function	Description
ABS (X)	Returns the absolute value of X
ACOS (X)	Returns the inverse cosine of X
ASIN (X)	Returns the inverse sine of X
ATAN (X)	Returns the arctangent of X
COS (X)	Returns the cosine of X
EXP (X)	Returns the exponent of X
SIGN (X)	Returns -] if X< 0, 0, если X = 0, + 1, если X > 0
LN (X)	Returns the natural logarithm of X
MOD (X, Y)	Returns the remainder of X divided by Y
CEIL (X)	Returns the smallest integer greater than or equal to X
ROUND (X, n)	Rounds X to a number with n digits after the decimal point
SIN (X)	Returns the sine of X
SQRT (X)	Returns the square root of X
TAN (X)	Returns the tangent of X
FLOOR (X)	Returns the largest integer less than or equal to X
LOG (a, X)	Returns the logarithm base A of X
SINH (X)	Returns the hyperbolic sine of X
COSH (X)	Returns the hyperbolic cosine of X
TANH (X)	Returns the hyperbolic tangent of X
TRANC (X.n)	Truncates the number X to a number with n digits after the decimal point
POWER (A, X)	Returns the value of A raised to the power of X

The set of built-in functions may vary depending on the version of the DBMS of one manufacturer and also in the DBMS of different manufacturers . So, for example, in the SQLBase DBMS, Centure Inc. there is a @ ATAN2 (X, Y) function that returns the arctangent of Y / X, but there is no SIGN (X) function.

Arithmetic expressions are needed to get data that is not directly stored in the columns of the database tables, but whose values \u200b\u200bare required by the user. Let's say you want a list of employees showing the pay each employee received, including bonuses and fines.

SELECT ENAME, SAL, COMM. FINE, SAL + COMM - FINE

The arithmetic expression SAL + COMM - FINE is output as a new column in the result table that is calculated as a result of the query. Such columns are also called derived (computed) attributes or fields.

String processing functions

SQL provides you with a wide range of functions for manipulating string data (string concatenation, CHR, LENGTH, INSTR, and others). The list of basic functions for processing string data is shown in Table 8.3.

Table 8.3. SQL functions for string processing

Function	Description
CHR (N)	Returns the ASCII code character for decimal code N
ASCII (S)	Returns the ASCII decimal code of the first character of a string
INSTR (S2, SI, pos \|, N \|)	Returns the position of string SI in string S2 greater than or equal to pos. N - number of occurrences
LENGTH (S)	Returns the length of a string
LOWER (S)	Replaces all characters in a string with uppercase characters
INITCAP (S)	Sets the first character of each word in a string to uppercase and the remaining characters of each word to uppercase
SUBSTR (S, pos, [, len \|)	Selects a substring of length ten in string S, starting at position pos
UPPER (S)	Converts uppercase letters in a string in capital letters
LPAD (S, N \|, A \|)	Returns a string S, left-padded with A characters to the number of N. The default filler character is a space
RPAD (S, N \|, A])	Returns the string S right-padded with A characters to the number of characters N. The default filler character is a space
LTRIM (S, \|, Sll)	Returns the string S truncated from the left. Characters are stripped as long as the stripped character enters the string - SI pattern (default is blank)
RTRIM (S, \|, SI \|)	Returns a right-truncated string S. Characters are stripped as long as the stripped character enters the string - pattern S1 (default is blank)
TRANSLATES, (SI, S2)	Returns the string S with all occurrences of the string SI replaced by the string S2. If SI<>S2, then characters that do not match are excluded from the resulting string
REPLACED (SI, \|, S2 \|)	Returns the string S for which all occurrences of substring SI have been replaced with substring S2. If S2 is not specified, then all occurrences of the substring SI are removed from the resulting string S
NVL (X, Y)	If X is NULL, then returns in Y either a string, or a number, or a date, depending on the original type of Y

The names of the same functions may differ in different DBMS. For example, the Oracle DBMS SUBSTR (S, pos, |, len |) function in SQLBase is called @SUBSTRING (S, pos, Ien). SQLBase has functions that Oracle does not have (see the table below for a list of such functions).

Table 8.4. SQLBase string functions that differ from Oracle DBMS string functions

Function	Description
@EXACT (SI, S2)	Returns the result of comparing two strings
@LEFT (S, lcn)	Returns the left substring of length len
@LENGTH (S)	Returns the length of the string
@MID (S, pos, len)	Returns a substring of the specified length starting at position pos
@REPEAT (S, n)	Repeats string S n times
@REPLACE (SI, pos, len, S2)	Replace pos len characters in S2 with SI characters
@RIGHT (S, len)	Returns the right substring of S of length len
@SCAN (S, pat)	Returns the position of the substring pat in string S
@STRING (X, scale)	Returns the symbolic representation of a number at the specified scale
@TRIM (S)	Removes spaces in a string on the right and left
@VALUE (S)	Converts the character representation of a number to a numeric value

You can use the INITCAP function so that when you get a list of employee names, last names always start with capital letter, and all others were in capital letters.

SELECT INITCAP (ENAME)

Special functions

SQL provides a set of special functions for converting column values. These functions are listed in Table 8.5.

Table 8.5. Special functions

In the EMPLOYEE table for each employee, you can enter a gender attribute - add a SEX column of the CHAR (l) type (0 - male, 1 - female). Let's say that you want a list of employees that needs to be segregated by gender, in numerical format; then you can issue a command like this:

SELECT ENAME, LNAME, AGE, " Floor: ", TO_NUMBER (SEX)

As an example of using the DECODE function, let us give a query that calculates a list of employees with an indication of their manager. If the manager is unknown, then the default is "does not have".

SELECT ENAME, DEC0DE (DEPN0, 10, " Dryagin ", 20,"Zhilyaeva". 30,"

Korotkov", "does not have")

Suppose that the head of the organization has an undefined value for the DEPNO column and, therefore, the default provided in DECODE will work for him .

© 2015-2019 site
All rights belong to their authors. This site does not claim authorship, but provides free use.
Date the page was created: 2016-08-07

Document table of contents

The functions of any DBMS include:

creation, deletion, modification of the database (DB);
adding, changing, deleting, assigning user rights;
entering, deleting and changing data in the database (tables and records);
fetching data from the database.

Only DBMS 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 you do 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, SQL provides the CREATE TABLE, ALTER TABLE, and DROP TABLE statements.

1. CREATE TABLE statement

The CREATE TABLE statement creates a table with the specified name in the current database. See the documentation for rules for valid table names. If there is no active current database or the specified table already exists, then a command execution error occurs.

In Mysql version 3.22 and later, the table name can be specified as dbname.tablename. This form of notation works regardless of whether the specified database is current.

In Mysql version 3.23, the TEMPORARY keyword can be used when creating a table. The temporary table is automatically deleted upon completion of the connection, and its name is valid only during this connection. This means that two different joins can use temporary tables with the same name without conflict with each other or with an existing table with the same name (the existing table is hidden until the temporary table is dropped). In Mysql 4.0.2, you must have CREATE TEMPORARY TABLES privileges to create temporary tables.

In Mysql version 3.23 and later, you can use the IF NOT EXISTS keywords in order to avoid an error if the specified table already exists. It should be borne in mind that the identity of the structures of these tables is not checked.

Each table is represented by a set of specific files in the database directory.

1.1. Syntax

CREATE TABLE
table_name [(column_definition, ...)]
[table_options]

The expression column_definition lists which columns should be created in the table. Each column of the table can be empty (NULL), have a default value, be a key, or auto-increment. In addition, for each column, the type of data that will be stored in it must be indicated. If neither NULL nor NOT NULL is specified, then the column is interpreted as if NULL was specified. If a field is marked as AUTO_INCREMENT, then its value is automatically incremented by one every time data is added to the table and an empty value is written to this field (NULL, i.e. nothing
is written) or 0. There can be only one autoincrement in the table, and it must be indexed.

1.2. Sequence

AUTO_INCREMENT starts at 1. Autoincrement is one of the features of Mysql. Formally, the description of a column (column_definition) looks like this:

column_name type

The column type (type in column_definition) can be one of the following:

integer: INT [(length)]
valid: REAL [(length, decimals)]
character: CHAR (length) and VARCHAR (length)
date and time: DATE and TIME
for working with large objects: BLOB
text: TEXT
an enumerable set: ENUM (value1, value2, value3, ...) and SET (value1, value2, value3, ...)

For a complete list of types, see the Mysql documentation.

Instead of listing columns and their properties in column_definition, you can specify lists of key and index fields, constraints, and checks:

PRIMARY KEY (indexed_column_name, ...)

KEY [index_name] (index_name_column, ...)

INDEX [index_name] (index_name_column, ...)

UNIQUE [index_name]

FULLTEXT [index_name]
(indexed_column_name, ...)

FOREIGN KEY [index_name]
(indexed_column_name, ...)

CHECK (expr)

When setting all these elements, a list of fields (columns) that will be included in the index, key or constraint is specified, the name_of_indexing_column is written as follows:

Column_name [(index_length)]
FOREIGN KEY, CHECK and REFERENCES don't really do anything in Mysql. They are added only for compatibility with other SQL servers.

Therefore, we will not dwell on them.

In addition to all of the above, when creating a table, you can specify some of its properties (table_options), for example:

table type: TYPE \u003d (BDB | HEAP | ISAM | Inno DB | MERGE | MRG_MYISAM | MYISAM)
the initial value of the autoincrement counter: AUTO_INCREMENT \u003d number
average length of rows in the table: AVG_ROW_LENGTH \u003d number
table comments (60 character string): COMMENT \u003d "string" maximum and minimum expected number of rows: MAX_ROWS \u003d number and MIN_ROWS \u003d number

And the last (again optional) element of the CREATE command is the SELECT statement (select_expression). The syntax is:

SELECT ...
(any valid SELECT expression)

If a SELECT statement is specified in the CREATE command when creating a table, then all fields obtained by the selection are added to the created table.

Example 10.1. Creating the Persons table

Mysql\u003e 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");

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

Show all databases:

Mysql\u003e SHOW databases;

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

Mysql\u003e use book;
Mysql\u003e show tables;

Show all columns in Persons table:

Mysql\u003e show columns from Persons;

2. DROP TABLE statement

The DROP TABLE statement drops one or more tables. All table data and definitions are removed, so be careful when using this command.

Syntax:

DROP TABLE table_name
[, table_name, ...]

In Mysql version 3.22 and later, the IF EXISTS keywords can be used to prevent an error if the specified tables do not exist.

The RESTRICT and CASCADE options make it easier to port programs from other DBMS. At the moment they are not involved.

Mysql\u003e DROP TABLE IF EXISTS Persons,
Artifacts, test;

Example 10.2. Using the DROP TABLE statement

3. ALTER TABLE statement

The ALTER TABLE statement provides the ability to modify 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 them are recorded in alter_specification):

adding a field:

ADD column_definition
or
ADD (column_definition,
column_definition, ...)

adding indexes:

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

changing the field:

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

renaming the table:

RENAME new_table_name

reordering table fields:

ORDER BY field
or
table_options

If ALTER TABLE is used to change the definition of a column's type, but DESCRIBE table_name shows that the column has not changed, then Mysql may be ignoring this modification for one of the reasons described in a special section of the documentation. For example, when trying to change a VARCHAR column to CHAR, Mysql will continue to use VARCHAR if the given table contains other variable length columns.

The ALTER TABLE statement, at runtime, creates a temporary copy of the original table. The requested change is performed 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, are automatically included in the new table. During ALTER TABLE execution, the original table is readable by other clients.

Updates and writes on this table are suspended until the new table is ready. It should be noted that when using any other option for ALTER TABLE other than RENAME, Mysql will always create a temporary table, even if the data, strictly speaking, does not need to be copied (for example, when changing the column name).

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

Mysql\u003e 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 to work with tables: create, delete and modify them.

Now let's figure out how to do the same with the data stored in these tables.

4. SELECT statement

The SELECT statement is used to retrieve rows selected from one or more tables. That is, with its help, we set the columns or expressions to be retrieved (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 should correspond to, and the order in to which this data needs to be issued.

In addition, the SELECT statement can be used to retrieve rows computed without reference to any table. For example, to calculate what is 2 * 2, you just need to write
Mysql\u003e SELECT 2 * 2;

The structure of the SELECT statement can be simplified as follows:
SELECT select_expression1, select_expression2,
...

Square brackets indicate that the operator within them is optional, the pipe | means listing the possible options. After the ORDER BY keyword, specify the column name, number (unsigned integer) or formula, and the ordering method (ascending - ASC, or descending - DESC). Ascending ordering is used by default.

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_expression.

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

Example 10.4. Using the SELECT statement

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

5. INSERT statement

The INSERT statement 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 rows are to be inserted. The columns to be set are specified in the list of column names (column_name) or in the SET part.

Syntax:

VALUES (expression, ...), (...), ...
This form of INSERT command inserts rows according to the exact values \u200b\u200bspecified in the command. Columns are listed in parentheses after the table name, and their values \u200b\u200bare listed after the VALUES keyword.

For instance:

Mysql\u003e INSERT INTO Persons
(last_name, bday) VALUES
("Ivanov", "1934");

will insert a row into the Persons table in which the values \u200b\u200bof the surname (last_name) and date of birth (bday) will be set accordingly as "Ivanov" and
"1934".

INSERT

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

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

For instance:

will insert into the Artifacts table in the author field the value of the identifier selected from the Persons table on the condition that the person's surname is Ivanov.

INSERT
table_name
SET column_name \u003d expression,
column_name \u003d expression, ...

For instance:

Mysql\u003e INSERT INTO Persons
SET last_name \u003d "Petrov",
first_name \u003d "Ivan";

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

The INSERT ... VALUES form with a list of multiple values \u200b\u200bis supported in Mysql version 3.22.5 and later. Expression syntax column_name \u003d expression is 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 \u200b\u200bfor all columns must be specified in the VALUES () list or as a result of SELECT. If you do not know the order of the columns in the table, you can use DESCRIBE table_name to get it.
Any column that is not explicitly assigned a value will be set to its default value. For example, if a given list of columns does not include all the columns in a given table, then the unmentioned columns are set to their default values.
Expression can refer to any column that has been previously listed in the list of values. For example, you can specify the following:

Mysql\u003e
VALUES ( 15 , col1 * 2 );

But you cannot specify:

Mysql\u003e 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 parameters are used when a large number of users are working on the table. They prescribe to set the priority of this operation over the operations of other users. If the LOW_PRIORITY keyword is specified, this INSERT command will be delayed until other clients have finished reading this table. In this case, the client must wait until the insert command is complete, which can take a long time if the table is heavily used. In contrast, the INSERT DELAYED command allows a given client to continue the operation immediately, independently of other users.

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

6. UPDATE statement

Syntax:

The UPDATE statement updates the values \u200b\u200bof the existing table columns according to the entered values. The SET statement specifies which columns should be modified and what values \u200b\u200bshould 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 UPDATE command is delayed until other clients have finished reading this table.

If the IGNORE keyword is specified, then the update command will not be interrupted even if a duplicate key error occurs. Conflicting lines will not be updated.

The PHP distribution includes an extension that contains built-in functions for working with the Mysql database. In this lecture we will get acquainted with some of the basic functions for working with Mysql that will be required to solve the problems of building web interfaces in order to display and fill the database. The question arises, why build such interfaces? In order to enter information into the database and view its contents, people who are not familiar with the language could sQL queries... When working with the web interface, to add information to the database, a person just needs to enter this data into an html form and send it to the server, and our script will do the rest. And to view the contents of the tables, just click on the link and go to the desired page.

For clarity, we will build these interfaces for the Artifacts table, which contains information about the exhibits of the virtual museum of informatics. In the previous lecture, we have already given the structure of this collection, as well as its relationship with the collections of descriptions of persons (Persons) and images (Images). As a reminder, each item in the Artifacts collection is described using the following characteristics:

title (title);
author (author);
description (description);
alternative name (alternative);
image (photo).

The title and alternate name are strings less than 255 characters long (ie, they are of the VARCHAR (255) type), the description is a text field (of the TEXT type), and the author and image fields contain the author's identifiers from the Persons collection and images of the exhibit from the Images collection, respectively.

Basic SQL statements. Syntax and examples of using the SELECT statement.

The basis of the SQL language is made up of operators, conditionally divided into several groups according to their functions.

The following groups of operators can be distinguished (not all SQL operators are listed):

Data Definition Language (DDL) statements - statements for defining database objects

CREATE SCHEMA - create a database schema

DROP SHEMA - drop the database schema

CREATE TABLE - create a table

ALTER TABLE - change a table

DROP TABLE - drop a table

CREATE DOMAIN - create a domain

ALTER DOMAIN - change domain

DROP DOMAIN - delete domain

CREATE COLLATION - create a sequence

DROP COLLATION - delete a sequence

CREATE VIEW - create a view

DROP VIEW - delete a view

Data Manipulation Language (DML) Operators - Data Manipulation Operators

SELECT - select rows from tables

INSERT - add rows to a table

UPDATE - change rows in a table

DELETE - delete rows in a table

COMMIT - commit the changes made

ROLLBACK - roll back the changes

Data protection and control operators

CREATE ASSERTION - create a constraint

DROP ASSERTION - remove restriction

GRANT - grant privileges to a user or an application to manipulate objects

REVOKE - revoke user or application privileges

At the same time, there are groups of operators for setting session parameters, obtaining information about a database, static SQL statements, dynamic SQL statements.

The most important to the user are the data manipulation operators (DML).

Examples of using data manipulation operators

INSERT - inserting rows into a table

Example 1... Inserting one row into a table:

VALUES (4, "Ivanov");

UPDATE - updating rows in a table

Example 3... Updating multiple rows in a table:

SET PNAME \u003d "Pushnikov"

WHERE P.PNUM \u003d 1;

DELETE - deleting rows in a table

Example 4... Deleting multiple rows in a table:

WHERE P.PNUM \u003d 1;

Examples of using the SELECT statement

The SELECT statement is in fact the most important and complex SQL statement to the user. It is designed to fetch data from tables, ᴛ.ᴇ. it, in fact, implements one of their basic purpose of the database - to provide information to the user.

The SELECT statement is always executed on some of the tables in the database.

Comment... In fact, in databases there are not only persistent tables, but also temporary tables and so-called views. Views are simply SELECT statements stored in the database. From the point of view of the user, a view is a table that is not permanently stored in the database, but "appears" when it is accessed. From the point of view of the SELECT statement, persistent tables and temporary tables and views look exactly the same. Of course, the actual execution of the SELECT statement by the system takes into account the differences between the stored tables and views, but these differences hiddenfrom the user.

The result of a SELECT statement is always a table. Τᴀᴋᴎᴍ ᴏϬᴩᴀᴈᴏᴍ, as a result of the actions, the SELECT statement is similar to the relational algebra operators. Any relational algebra operator must be expressed by a suitably worded SELECT statement. The complexity of the SELECT statement is determined by the fact that it contains all the possibilities of relational algebra, as well as additional featureswhich are absent in relational algebra.

Order of execution of the SELECT statement

In order to understand how the result of executing a SELECT statement is obtained, let's consider a conceptual diagram of its execution. This scheme is precisely conceptual, since it is guaranteed that the result will be as if it were carried out step by step according to this scheme. In fact, the actual result is obtained by more sophisticated algorithms, which are "owned" by a particular DBMS.

Stage 1. Execution of a single SELECT statement

If the operator contains the keywords UNION, EXCEPT and INTERSECT, then the query is split into several independent queries, each of which is executed separately:

Step 1 (FROM)... The direct cartesian product of all tables specified in the mandatory FROM clause is calculated. As a result of step 1, we get table A.

Step 2 (WHERE)... If a WHERE clause is present in the SELECT statement, then table A obtained during step 1 is scanned. At the same time, for each row from table A, the conditional expression given in the WHERE clause is calculated. Only those rows for which the conditional expression returns TRUE are included in the result. If the WHERE clause is omitted, then go straight to step 3. If nested subqueries are involved in the conditional expression, then they are calculated in accordance with this conceptual scheme. As a result of step 2, we get table B.

Step 3 (GROUP BY)... If the SELECT statement contains a GROUP BY clause, then the rows of table B obtained in the second step are grouped according to the grouping list given in the GROUP BY section. If the GROUP BY clause is omitted, then go directly to step 4. As a result of step 3, we get table C.

Step 4 (HAVING)... If a HAVING clause is present in the SELECT statement, then groups that do not satisfy the conditional expression given in the HAVING clause are excluded. If the HAVING section is omitted, then go directly to step 5. As a result of step 4, we get table D.

Step 5 (SELECT)... Each group obtained in step 4 generates one result row as follows. All scalar expressions specified in the SELECT section are evaluated. According to the rules for using the GROUP BY clause, such scalar expressions must be the same for all rows within each group. For each group, the values \u200b\u200bof the aggregate functions listed in the SELECT section are calculated. If the GROUP BY clause was absent, but the SELECT clause contains aggregate functions, then it is considered that there is only one group. If there is neither a GROUP BY clause nor aggregate functions, then it is considered that there are as many groups as there are rows selected at this moment. As a result of step 5, we get table E, containing as many columns as there are elements in the SELECT section and as many rows as there are selected groups.

Stage 2. Performing UNION, EXCEPT, INTERSECT operations

If the keywords UNION, EXCEPT and INTERSECT were present in the SELECT statement, then the tables obtained as a result of the 1st stage are merged, subtracted or intersected.

Stage 3. Ordering the result

If an ORDER BY clause is present in the SELECT statement, then the rows of the table obtained in the previous steps are ordered in accordance with the ordering list given in the ORDER BY clause.

Basic SQL statements. Syntax and examples of using the SELECT statement. - concept and types. Classification and features of the category "Basic SQL statements. Syntax and examples of using the SELECT statement." 2017, 2018.

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

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) the following capabilities:

Create databases and tables with full description their structures

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

Perform both simple and complex queries.

SQL is relationally complete.

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

The SQL language has two main components:

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

DML (Data Manipulation Language), designed to retrieve and update data.

The SQL language is non-procedural, that is, when using it, it is necessary to specify what information should be obtained, and not how it can be obtained. SQL commands are ordinary words in English (SELECT, INSERT, etc.). Let's look at the SQL DML statements first:

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 operations equivalent to the following relational algebra operations: select, projection, 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 that will be returned from the query. The from keyword indicates from which table (or view) the data is retrieved.

The result of a select query is always a table, which is called the result table. Moreover, the results of a query executed with the 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 resulting table can be the subject of further queries.

To select all columns and all rows of a table, just make a SELECT * FROM tbl;

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

Request result

SELECT * FROM Product;

will be the entire Product table.

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 when it is necessary to change the order of the columns in the resulting table:

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

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

SELECT * FROM Product where Price<3200;

Its result:

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

Using the BETWEEN var1 AND var2 construct allows you to check if the values \u200b\u200bof any expression fall within the range 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.

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

SELECT Type AS model, Type_id AS num FROM Product where Type_id \u003d 3

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

The LIKE operator is for comparing a string against 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.

Two wildcards are allowed in the pattern: "_" and "%". The first one replaces one arbitrary character in the template, and the second replaces a sequence of arbitrary characters. So, "abc%" matches any string beginning with abc, "abc_" - a 4-character string starting with abc, "% z" - an arbitrary 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" as follows:

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


	auto scales

If the search string contains a wildcard character, then you must specify the control character in the ESCAPE clause. This control character should be used in the pattern before the wildcard character, indicating that the latter should be treated like a regular character. For example, if all values \u200b\u200bcontaining the "_" character are to be found in a certain field, 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 NULL values \u200b\u200bin the table fields. Using normal comparison operators in these cases may lead to incorrect results, since comparison with NULL results in UNKNOWN (unknown). Thus, the selection condition should look like this:

where col_name IS NULL, instead of where col_name \u003d NULL.

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

SELECT * FROM tbl ORDER BY col_name;

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

Records can be sorted by several columns. To do this, their names must be specified after the 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, they will be sorted by the col_name2 field.

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

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

If the result of a selection contains hundreds or thousands of records, their output and processing takes a significant amount of time.

Therefore, information is often paginated and presented to the user in chunks. Page navigation is used with the limit keyword, followed by the number of records to display. The following query retrieves the first 10 records, while simultaneously performing a reverse sort by the col_name1 field:

SELECT * FROM tbl ORDER BY col_name1 DESC LIMIT 10

To retrieve the next 10 records, the limit keyword is used with two values: the first indicates the position from which to display the result, and the second indicates 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.

And over the table data.

The SQL language is called embeddedsince it contains the functions of a full-fledged development language, and is focused on data access, as a result of which it is included in the application development tools. The SQL language standards support the programming languages \u200b\u200bPascal, Fortran, COBOL, C, etc.

Exist 2 methods of using embedded SQL:

static language use ( static SQL) - the program text contains calls to SQL functions, which are included in the executable module after compilation.
dynamic language use ( dynamic SQL) - dynamic construction of SQL function calls and their interpretation. for instance, you can refer to the data of the remote database during program execution.

The SQL language (like other languages \u200b\u200bfor working with databases) is intended for preparing and executing queries. As a result of executing a data query from one or more tables, a set of records is obtained, which is called submission.

Definition 1

Performance Is a table that is formed as a result of a query.

Basic operators of the SQL query language

SQL statements are conventionally divided into 2 sublanguages:

Data definition language DDL;
Data manipulation language DML.

In the table, * marked specific operators language.

Let's take a look at the most important SQL statements.

Table creation statement:

The name of the table that is being created and the name of at least one column (field) are required operands. For the column name, you must specify the type of data that will be stored in it.

For individual fields, you can specify additional rules for controlling the values \u200b\u200bthat are entered into them. For instance, NOT NULL means that the field cannot be empty and a value must be entered into it.

Example 1

To create a table books catalog of books, which contains fields:

type - type of book,

name - name of the book,

price - book price

the operator might look like this:

Operator for changing the structure of a table:

When changing the structure of the table, you can add ( ADD), change ( MODIFY) or delete ( DROP) one or more columns of the table. The recording rules for this operator are the same as for the operator CREATE TABLE... You do not need to specify to delete a column.

Example 2

To add to the table books fields number, which will store the number of books, you can write the operator:

Dropping table operator:

Example 3

For example, to drop an existing table named books just use the operator:

Index creation operator:

The operator creates an index on one or more columns of a given table to speed up query and search operations. Multiple indexes can be created for one table.

Optional option UNIQUE is responsible for ensuring the uniqueness of values \u200b\u200bin all columns that are specified in the statement.

ASC sets the automatic sorting of values \u200b\u200bin columns in ascending order (default), and DESC - in descending order.

Index drop operator:

View creation operator:

You can omit column names when creating a view. Then the column names from the query will be used, which is described by the corresponding operator SELECT.

View delete operator:

Record selection operator:

Operator SELECT makes selections and calculations on data from one or more tables. The result of executing the operator is a response table that contains ( ALL) or does not contain ( DISTINCT) lines that are repeated.

Operand FROM contains a list of tables from which records are taken to select data.

Record change operator:

New field values \u200b\u200bin records may not contain values \u200b\u200b( NULL) or calculated according to an arithmetic expression.

New record insertion operator:

In the first record of the operator INSERT new records are entered with the specified values \u200b\u200bin the columns.

In the second statement of the operator INSERT new rows are introduced, selected from another table through a sentence SELECT.

Delete record operator:

As a result of the operator execution, rows are deleted from the specified table that satisfy the condition, which is defined by the optional operand WHERE... If the operand WHERE is not specified, then all table records are deleted.