Using functions in sql queries. SQL aggregate functions - SUM, MIN, MAX, AVG, COUNT. Selecting a Database to Use

Built-in functions SQL designed to facilitate and speed up data processing. The special feature is that they can be specified directly in the expression. All built-in functions can be divided into groups.

Mathematical functions:

ABS(meaning) – returns the absolute value of a number;

Round(value, accuracy) – returns a numeric value rounded to the value specified by the argument accuracy number of decimal places;

SIGN(meaning) – returns minus if the number is negative, and plus otherwise;

POWER(meaning, degree) – raises a number to a power;

SQRT(meaning) – extracts the square root of a number;

CEILING(meaning)– returns the nearest integer greater than or equal to the value;

- FLOOR(meaning)– returns the nearest integer less than or equal to the value.

String functions:

ASCII(line) – returns ASCII code of the first character of the line;

CHAR(number) – return the character by ASCII code;

LEN (line) – returns the length of the string in characters, excluding trailing spaces;

LTRIM(line)/ RTRIM(line)- removes spaces at the beginning/end of a line;

LEFT(string, number)/ RIGHT(string, number)– returns the specified argument number number of characters in the line, starting from the left/right edge;

SUBSTRING(line, position, length) – returns a substring of the specified length from a string, starting from the specified position;

LOWER(line) /UPPER(line) – returns a string converted to lowercase/uppercase etc.

Functions for working with dates:

GETDATE() – returns a value that contains the date and time of the computer on which the instance of SQL Server is running;

DAY(value_date)– returns a number from the specified date;

MONTH(value_date)– returns the month number from the specified date;

YEAR(value_date)– returns the year value from the specified date;

DATENANE( part, value_date) – returns a character string representing the specified part ( Day, Month, Houretc.) from the specified date;

DATEPART( part, value_date) – returns an integer representing the specified part ( Day, Month, Houretc.) from the specified date.

Data type conversion functions

CAST (meaning AS data_type)

CONVERT(data_type, meaning)

Argument meaning in functions specifies the value that needs to be converted.

7.3. Data Definition Language Commands

The Data Definition Language contains commands that are used to create, modify, and delete a database and its objects.

Creating a table

Creating a new table is done with the command CREATE TABLE. The command describes the structure of the table, each column of the table, and the integrity constraints that must be set on the table.

Command syntax:

CREATE TABLE table_name (( column_description |calculated_column_name AS expression | table_level_integrity_constraints) [, ...])

The table name is an identifier of no more than 128 characters.

A table can contain a calculated column, in which case the value of the column is determined by an expression that is stored in the table structure. A calculated column cannot be modified, so it cannot have NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, or DEFAULT integrity constraints.

The syntax for describing a table column is:

column_name data_type[(size)]

[(DEFAULT default_value | IDENTITY [(value, step)]}]

[column_level_integrity_constraints]

DEFAULT - allows you to specify the value assigned to the column in the newly added record.

IDENTITY – indicates that a column is being created that supports automatic numbering (counter column). Only one counter column can be defined in a table. The value parameter specifies the initial value of the counter, and the step parameter specifies the increment step. If these parameters are not specified, they have the value 1. IDENTITY can only be specified for those columns that are of integer or decimal types. Inserting values ​​into the IDENTITY column is not allowed.

There are two groups of integrity constraints processed by the DBMS:

Declarative integrity constraints, which are declared when a table is created or modified;

Procedural integrity constraints that are processed by triggers.

Declarative integrity constraints can be table-level constraints or table-level constraints. Column-level restrictions apply to only one column. Each declarative integrity constraint can be given a name.

Column level constraint definitions have the following syntax:

((PRIMARY KEY | UNIQUE | NOT NULL ) |FOREIGN KEY REFERENCES table_name( column_name)

|CHECK boolean_expression)

The data integrity constraint name must be unique within the database. Let's look at the constraints that can be defined at the column level:

Primary key constraint PRIMARY KEY. All table primary key values ​​must be unique and different from Null. A table can only have one primary key. If it is composite, then integrity constraints on the primary key are specified at the table level;

Constraints on the uniqueness of a UNIQUE column value. This means that a table cannot have two records that have the same value in that column;

A NOT NULL constraint that prohibits storing a NULL value in a column;

FOREIGN KEY foreign key constraint (referential integrity constraint). For a column that is a foreign key, REFERENCES specifies the name of the table to which the relationship is being established and the name of the column of that table on which the relationship will be established. This table is the main (parent) table in relation to the table being created. The main table column whose values ​​are being linked must have a PRIMARY KEY constraint set.

If the key of an external table consists of several fields, then the FOREIGN KEY constraint must be specified at the table level. In this case, you should list all the columns included in the foreign key, indicate the name of the main table and the names of the columns of the main table that are referenced by the foreign key.

Referential integrity establishes rules for adding and changing data in a table using a foreign key and its corresponding primary key constraint. The ON UPDATE and ON DELETE clauses for a foreign key define the following rules for changing related data:

NO ACTION – allows you to change (delete) only those values ​​in the main table that do not have corresponding foreign key values ​​in the child table. This rule is in effect by default;

CASCADE means that each foreign key value of a child table will be automatically changed (deleted) when the primary key value of the parent table is modified;

SET NULL means that if the primary key of the parent table is changed (deleted), in all referencing rows of the child table, the foreign key values ​​will be automatically assigned NULL values;

SET DEFAULT means that if the primary key of the parent table is changed (deleted), in all referencing rows of the child table, the foreign key values ​​will be automatically assigned default values.

Let us supplement the example of the educational database “University”, the design of which was discussed in Chapter. 4.3 tables DISCIPLINE and GENERAL REPORT. Tables 6 and 7 describe the logical structure of the tables.

Table 6

Logical structure of the DISCIPLINE information object

Table 7

Logical structure of the information object GENERAL STATEMENT

Let us present requests for creating tables in accordance with the one shown in Fig. 35 infological database model.

Rice. 35. Database scheme “University”

As can be seen from the database schema, the FACULTY table is an independent table, so it is created first. Request to create a table taking into account the description of the logical structure in the table. 4 (p.61) will look like:

CREATE TABLE faculty

([department number] tinyint PRIMARY KEY , [department name] char(50))

The SPECIALTY table is also independent; we create it second. When creating a query, it uses the description of the logical structure in the table. 5 (p.62).

CREATE TABLE [specialty] (

[specialty number] int PRIMARY KEY,

[name of specialty] char (60),

[cost of education] )

The GROUP table is a table dependent on FACULTY and SPECIALTY. We use table 3 (p. 61) when creating a query and take into account that the columns faculty number And specialty number are foreign keys:

CREATE TABLE [group] (

[group number] smallint PRIMARY KEY,

[specialty number] int FOREIGN KEY REFERENCES specialty( number special- ness)ON DELETE CASCADE ON UPDADE CASCADE,

[faculty number] tinyint FOREIGN KEY REFERENCES faculty( number faculty) ON DELETE CASCADE ON UPDADE CASCADE, [course number] tinyint)

The STUDENT table is a GROUP dependent table. Based on the data in Table 2 (p. 60), we will create a query. We also take into account that the column group number are foreign keys:

CREATE TABLE [student] (

[group number] smallint NOT NULL FOREIGN KEY REFERENCES group( number groups) ,

[last name] char(15) NOT NULL ,

[date of birth] datetime NOT NULL ,

[commercial] bit NOT NULL ,

[registration name] char(9))

The data in the GENERAL REPORT table depends on the STUDENT and DISCIPLINE tables. In this table, the primary key is composite and each of the primary key columns is a foreign key (see Table 7 and Figure 35).

Let's use the description of the logical structure of the discipline table given in Table 6 and create a query:

CREATE TABLE [discipline] (

[discipline code] int PRIMARY KEY,

[discipline name] char(50))

Now you can create a query to create the general statement table. Since the primary key of a table is composite, the PRIMARY KEY constraint must be specified at the table level. For example, let's set FOREIGN KEY restrictions also at the table level. The request will look like:

CREATE TABLE [general statement] (

[discipline code] int,

[grade book number] char(8),

[grade] NOT NULL , PRIMARY KEY ([discipline code],[grade book number]), FOREIGN KEY ([discipline code]) REFERENCES [discipline] ([discipline code]), FOREIGN KEY ([grade book number]) REFERENCES [student] ([grade book number]))

Changing the table structure

Changing the table structure is performed with the ALTER TABLE command. Using the command, you can change the properties of existing columns, delete them or add new columns to the table, and manage integrity constraints both at the column level and at the table level. The meaning of many of the parameters and keywords is the same as the corresponding parameters and keywords of the CREATE TABLE command.

Delete a table

Dropping a table is done using the DROP TABLE command. Command syntax:

DROP TABLE table

For example, a request to delete the STUDENT table looks like this:

DROP TABLE Student

When deleting a table, you SHOULD take into account the relationships established in the database between the tables. If the table being deleted is referenced by another table using the FOREIGN KEY integrity constraint, the DBMS will not allow its deletion.

Creating an index

Indexes are used to speed up access to specific data in a database table. An index is a structure that organizes the values ​​in one or more columns of a database table, such as the Last Names column of the STUDENT table. If you are searching for a specific student by last name, the index helps you get the information you need faster than searching through all rows of the table.

An index provides pointers to data values ​​stored in specific columns of a table and arranges those pointers according to a specified sort order. Searching for data in a table using an index is similar to searching an index in a book. First a search is carried out specific meaning in the index, and then follows the corresponding pointer to the line containing that value.

The index is created using the CREATE INDEX command:

CREATE INDEX

Name_ index ON name _tables(column [,...])

where UNIQUE specifies that the index should store only unique values.

An index can be created on one or more columns (composite index). Composite indexes allow you to differentiate between records that have the same values ​​in the same column.

Example: Create a composite index on the STUDENT table for the Last Name and Date of Birth fields

CREATE INDEX Ind_Fam ON

Student(Last name, [Date of birth] DESC)

You should only create an index on a table if you expect to frequently query the data in the indexed columns. Indexes take up disk space and slow down row addition, deletion, and update operations.

Removing a table index

The DROP command removes an index from a table. The syntax of the DROP command to remove an index is:

DROP INDEX index ON table

Before you can remove an index from a table or the table itself, you must close the table.

Example: Remove index Ind_Fam from table STUDENT

DROP INDEX Ind_Fam ON Student

The SQL language standard was adopted in 1992 and is still in use today. It was this that became the standard for many. Of course, some manufacturers use their own interpretations of the standard. But any system still has the main components - SQL statements.

Introduction

Using SQL statements, values ​​and tables are managed and obtained for further analysis and display. They are a set of keywords by which the system understands what to do with the data.

Several categories of SQL statements are defined:

• defining database objects;
• manipulation of values;
• protection and control;
• session parameters;
• information about the database;
• static SQL;
• dynamic SQL.

SQL statements for data manipulation

INSERT. Inserts rows into an existing table. It can be used for one value or several, defined by a certain condition. For example:

table name (column name 1, column name 2)

VALUES(value 1, value 2).

To use the INSERT statement on multiple values, use the following syntax:

table name 1 (column name 1, column name 2)

SELECT column name 1, column name 2

FROM table name 2

WHERE table name 2.column name 1>2

This query will select all data from table 2 that is greater than 2 in column 1 and insert it into the first one.

UPDATE. As the name suggests, this SQL query statement updates data in an existing table based on a specific feature.

UPDATE table name 1

SET column name 2 = "Vasily"

WHERE table name 1.column name 1 = 1

This construction will fill with the value Vasily all lines in which it encounters the number 1 in the first column.

Data from the table. You can specify a condition or remove all lines.

DELETE FROM table name

WHERE table name.column name 1 = 1

The above query will remove from the database all data with the value one in the first column. Here's how you can clear the entire table:

SELECT statement

The main purpose of SELECT is to select data according to certain conditions. The result of his work is always a new table with selected data. The MS operator can be used in a variety of different queries. Therefore, along with it, you can consider other related keywords.

To select all data from a specific table, use the “*” sign.

FROM table name 1

The result of the work of this request will be an exact copy of table 1.

And here a selection is made using the WHERE condition, which retrieves from table 1 all values ​​greater than 2 in column 1.

FROM table name 1

WHERE table name 1.column name 1 > 2

You can also specify in the selection that only certain columns are needed.

SELECT table name 1.column name 1

FROM table name 1

The result of this query will be all rows with values ​​from column 1. Using MS SQL statements, you can create your own table, replacing, calculating and substituting certain values ​​on the fly.

table name 1.column name 1

table name 1.column name 2

table name 1.column name 3

table name 1.column name 2 * table name 1.column name 3 AS SUMMA

FROM table name 1

This seemingly complex query retrieves all values ​​from Table 1, then creates new columns EQ and SUMMA. The first is filled with the “+” sign, and the second is the product of the data from columns 2 and 3. The result can be presented in the form of a table to understand how it works:

When using the SELECT statement, you can immediately sort the data according to any criterion. The word ORDER BY is used for this.

table name 1.column name 1

table name 1.column name 2

table name 1.column name 3

FROM table name 1

ORDER BY column name 2

The resulting table will look like this:

That is, all rows were set in such an order that in column 2 the values ​​were in ascending order.

Data can also be obtained from several tables. For clarity, you first need to imagine that there are two of them in the database, something like this:

Table "Employees"

Table "Salary"

Now we need to somehow connect these two tables to obtain common values. Using basic SQL statements, you can do this like this:

Employees.Number

Employees.Name

Salary.Rate

Salary. Accrued

FROM Employees, Salary

WHERE Employees.Number = Salary.Number

Here we select from two different tables of values, combined by number. The result will be the following data set:

A little more about SELECT. Using Aggregate Functions

One of the main operators can perform some calculations when fetching. To do this, he uses certain functions and formulas.

For example, to get the number of records from the Employees table, you need to use the query:

SELECT COUNT (*) AS N

FROM Employees

The result is a table with one value and a column.

You can run a query like this and see what happens:

SUM(Salary. Accrued) AS SUMMA

MAX(Salary. Accrued) AS MAX

MIN(Salary. Accrued) AS MIN

AVG(Salary. Accrued) AS SRED

FROM Salary

The final table will be like this:

In this way, you can select the desired values ​​from the database, calculating various functions on the fly.

Union, intersection and differences

Combine multiple queries in SQL

SELECT Employees.Name

FROM Employees

WHERE Employees.Number = 1

SELECT Employees.Name

FROM Employees, Salary

WHERE Salary.Number = 1

It should be borne in mind that with such a union, the tables must be compatible. That is, have the same number of columns.

SELECT statement syntax and processing order

The first thing SELECT does is determine the area from which it will take data. For this purpose it is used keyword FROM. If it is not indicated what exactly to choose.

Then the SQL WHERE clause may be present. With its help, SELECT runs through all the rows of the table and checks the data for compliance with the condition.

If the query contains GROUP BY, then the values ​​are grouped according to the specified parameters.

Operators for comparing data

There are several types. IN SQL statements comparisons can check Various types values.

"=". Denotes, as you might guess, the equality of two expressions. For example, it has already been used in the examples above - WHERE Salary.Number = 1.

">". More sign. If the value of the left side of the expression is greater, then the logical TRUE is returned and the condition is considered satisfied.

«<». Знак меньше. Обратный предыдущему оператор.

Signs "<=» и «>=". It differs from simple more and less operators in that if the operands are equal, the condition will also be true.

LIKE

This keyword can be translated as “similar”. The LIKE operator in SQL is used in approximately the same way - it executes a query based on a template. That is, it allows you to expand the selection of data from the database using regular expressions.

For example, the following task was set: from the already known “Employees” database, get all the people whose name ends with “I”. Then the query can be written like this:

FROM Employees

WHERE Name LIKE `%i`

The percent sign in this case means a mask, that is, any character and their number. And by the letter “I”, SQL will determine that the last character should be exactly like this.

CASE

This SQL Server statement is an implementation of multiple selection. It is reminiscent of the switch construct in many programming languages. The CASE statement in SQL performs an action based on several conditions.

For example, you need to select the maximum and minimum values ​​from the “Salary” table.

Then the query can be written like this:

FROM Salary

WHERE CASE WHEN SELECT MAX(Accrued) THEN Maximum

WHEN SELECT MIN(Accrued) THEN Minimum

In this context, the system looks for the maximum and minimum value in the Accrued column. Then, using END, a “total” field is created, in which “Maximum” or “Minimum” will be entered, depending on the result of the condition.

By the way, SQL also has a more compact form of CASE - COALESCE.

Data Definition Operators

This view allows you to carry out various modifications to tables - creating, deleting, modifying and working with indexes.

The first one worth considering is CREATE TABLE. It does nothing more than create a table. If you simply type the CREATE TABLE query, nothing will happen, since you still need to specify several parameters.

For example, to create the already familiar “Employees” table, you need to use the commands:

CREATE TABLE Employees

(Number(10) NOT NULL

Name varchar(50) NOT NULL

Last name varchar(50) NOT NULL)

In this query, the names of the fields and their types are immediately determined in parentheses, as well as whether it can be equal to NULL.

DROP TABLE

Performs one simple task - dropping the specified table. It has additional parameter IF EXISTS. It absorbs a drop error if the table you are looking for does not exist. Usage example:

DROP TABLE Employees IF EXISTS.

CREATE INDEX

SQL has an index system that allows you to speed up data access. In general, it is a link that points to a specific column. You can create an index with a simple query:

CREATE INDEX index_name

ON table_name(column_name)

This operator is used in T-SQL, Oracle, PL SQL and many other interpretation technologies.

ALTER TABLE

A very functional operator with numerous options. In general, it changes the structure, definition and placement of tables. The operator is used in Oracle SQL, Postgres and many others.

ADD. Adds a column to the table. Its syntax is as follows: ALTER TABLE table_name ADD column_name stored_data_type. May have an IF NOT EXISTS option to suppress the error if the column being created already exists;

DROP. Removes a column. It also has an IF EXISTS key, without which an error will be generated indicating that the required column is missing;

CHANGE. Serves to rename the field name to the specified one. Usage example: ALTER TABLE table_name CHANGE old_name new_name;

MODIFY. This command will help you change the type and additional attributes of a certain column. And it is used like this: ALTER TABLE table_name MODIFY column_name data_type attributes;

CREATE VIEW

In SQL there is such a thing as a view. In short, this is a kind of virtual table with data. It is formed as a result of selection using the SQL SELECT statement. Views can restrict access to the database, hide them, and replace real column names.

The creation process occurs using a simple request:

CREATE VIEW view name AS SELECT FROM * table name

Sampling can occur either from the entire database or based on some condition.

A little about the functions

SQL queries very often use various built-in functions that allow you to interact with data and transform it on the fly. It's worth considering them because they form an integral part of a structured language.

COUNT. Counts records or rows in a specific table. You can specify the column name as a parameter, then the data will be taken from it. SELECT COUNT * FROM Employees;

AVG. Applies only to columns with numeric data. Its result is the determination of the arithmetic mean of all values;

MIN and MAX. These functions have already been used in this article. They determine the maximum and minimum values ​​from a specified column;

SUM. It's simple - the function calculates the sum of the column values. Applies exclusively to numeric data. By adding the DISTINCT parameter to the query, only unique values ​​will be summed;

ROUND. Function for rounding decimal fractions. The syntax uses the column name and the number of decimal places;

LEN. A simple function that calculates the length of a column's values. The result will be a new table indicating the number of characters;

NOW. This keyword is used to calculate the current date and time.

Additional Operators

Many SQL statement examples have keywords that perform small tasks but still greatly simplify retrieval or database manipulation.

AS. It is used when you need to visually format the result by assigning the specified name to the resulting table.

BETWEEN. A very convenient sampling tool. It specifies the range of values ​​among which data is to be retrieved. Takes as input a parameter from and to what number the range is used;.

NOT. The operator gives the opposite of the expression.

TRUNCATE. Deletes data from the specified section of the database. It differs from similar operators in that it is impossible to restore data after using it. It is worth considering that the implementation of this keyword in different SQL interpretations may differ. Therefore, before trying to use TRUNCATE, it is better to read the help information.

LIMIT. Sets the number of lines to output. The peculiarity of the operator is that it is always located at the end. Accepts one required parameter and one optional one. The first specifies how many rows of selected data should be shown. And if the second one is used, then the operator works as for a range of values.

UNION. A very convenient operator for combining multiple queries. It has already been found among examples of this in this article. You can display the desired rows from several tables by combining them with UNION for more convenient use. Its syntax is as follows: SELECT column_name FROM table_name UNION SELECT other_column_name FROM other_table_name. The result is a pivot table with the combined queries.

PRIMARY KEY. Translated as “primary key”. In fact, this is exactly the terminology used in reference materials. It means a unique row identifier. It is used, as a rule, when creating a table to indicate the field that will contain it.

DEFAULT. Just like the previous operator, it is used during the execution of the creating request. It defines the default value with which the field will be populated when it is created.

NULL. Beginners and not only programmers, when writing queries, very often forget about the possibility of obtaining the NULL value. As a result, an error creeps into the code, which is difficult to track down during debugging. Therefore, when creating tables, selecting or recalculating values, you need to stop and think about whether the occurrence of NULL in this part of the query is taken into account.

Memory. This article showed several functions that can perform some tasks. When developing a shell for working with a database, you can outsource the calculation of simple expressions to the database management system. In some cases, this gives a significant increase in performance.

Restrictions. If you need to get only two rows from a database with thousands of rows, then you should use operators like LIMIT or TOP. There is no need to extract data using a shell development language.

Compound. After receiving data from several tables, many programmers begin to bring them together using shell memory. But why? After all, you can create one request in which all this will be present. There is no need to write extra code and reserve additional memory in the system.

Sorting. If it is possible to apply ordering in a query, that is, using the DBMS, then you need to use it. This will allow you to significantly save on resources when running a program or service.

Lots of requests. If you have to insert many records sequentially, then for optimization you should think about batch inserting data with one request. This will also increase the performance of the entire system as a whole.

Thoughtful placement of data. Before compiling the database structure, you need to think about whether so many tables and fields are necessary. Maybe there is a way to combine them or discard some. Very often programmers use an excessive amount of data that will never be used anywhere.

Types. To save space and resources, you need to be sensitive to the types of data you use. If it is possible to use a type that is less “heavy” for memory, then you should use it. For example, if you know that the numeric value in a given field will not exceed 255, then why use a 4-byte INT if there is a 1-byte TINYINT.

Conclusion

In conclusion, it should be noted that the structured query language SQL is now used almost everywhere - websites, web services, PC programs, applications for mobile devices. Therefore, knowledge of SQL will help all branches of development.

However, modifications of the original language standard sometimes differ from each other. For example, PL SQL statements may have a different syntax than in SQL Server. Therefore, before you start developing with this technology, it is worth reading the tutorials on it.

In the future, analogues that could surpass SQL in functionality and performance are unlikely to appear, so this area is a fairly promising niche for any programmer.

Basic SQL Commands Every Programmer Should Know

SQL or Structured Query Language is a language used to manage data in a relational database system (RDBMS). This article will cover commonly used SQL commands that every programmer should be familiar with. This material is ideal for those who want to brush up on their knowledge of SQL before a job interview. To do this, look at the examples given in the article and remember that you studied databases in pairs.

Note that some database systems require a semicolon at the end of each statement. The semicolon is the standard pointer to the end of every statement in SQL. The examples use MySQL, so a semicolon is required.

Setting up a database for examples

Create a database to demonstrate how teams work. To work, you will need to download two files: DLL.sql and InsertStatements.sql. After that, open a terminal and log into the MySQL console using the following command (the article assumes that MySQL is already installed on the system):

Mysql -u root -p

Then enter your password.

Run the following command. Let's call the database “university”:

CREATE DATABASE university; USE university; SOURCE ; SOURCE

Commands for working with databases

1. View available databases

2. Create a new database

3. Selecting a database to use