Basic SQL statements

The SQL language standard was adopted in 1992 and is still in use. It was he who became the standard for many database management systems. Of course, some manufacturers use their own interpretations of the standard. But in any system there are still the main components - SQL statements.

Introduction

Using SQL statements in databases , values, tables are managed and retrieved 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:

• definition of database objects;
• value manipulation;
• protection and management;
• Session parameters
• database information;
• static SQL
• dynamic SQL.

SQL statements for data manipulation

This category includes keywords that can be used to control the placement of values ​​in the database.

INSERT Inserts rows in an existing table. It can be used both for one value or several, determined by some condition. For instance:

INSERT INTO

table name (column name 1, column name 2)

VALUES (value 1, value 2).

To use the SQL statement INSERT query with multiple values, the following syntax is used:

INSERT INTO

table 1 name (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 the data from table 2 that is greater than 2 in column 1 and insert them into the first.

UPDATE As the name implies, this SQL query statement updates the data in the existing table for a certain characteristic.

Example:

UPDATE table name 1

SET column name 2 = "Basil"

WHERE table name 1. column name 1 = 1

This design will fill in the value of Vasily all the lines in which it will meet the number 1 in the first column.

DELETE Deletes data from a 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 delete from the database all data with a value of one in the first column. And so you can clear the whole table:

DELETE FROM table name.

Further it is worth talking about the SELECT statement. He is one of the most important, so he will have to devote a separate chapter.

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 SQL SELECT statement can be used in a host of different queries. Therefore, along with it, you can consider other related keywords.

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

SELECT *

FROM table name 1

The result of this query will be an exact copy of table 1.

And here we select according to the WHERE clause, which extracts all values ​​from table 1, more than 2 in column 1.

SELECT *

FROM table name 1

WHERE table name 1. column name 1> 2

You can also indicate 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.

SELECT

table name 1. column name 1

table name 1. column name 2

table name 1. column name 3

"=" AS EQ

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

FROM table name 1

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

When using the SELECT operator, you can immediately organize the data according to some characteristic. To do this, use the word ORDER BY.

SELECT

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 a way that in column 2 the values ​​went in ascending order.

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

Employee table

Salary table

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

SELECT

Employees.Number

Employees Name

Salary. Rates

Salary. Accrued

FROM Employees, Salary

WHERE Employees. Number = Salary. Number

Here, a selection is made of two different value tables, united by number. The result is the following data set:

A little more about SELECT. Using aggregate functions

One of the main SQL SELECT statements can do 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.

In queries, you can use functions that calculate the sum, maximum and minimum values, as well as the average. The keywords SUM, MAX, MIN, AVG are used for this.

For example, you need to select from the already known table “Salary”:

You can apply this query and see what happens:

SELECT

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 as follows:

In this way, you can select the desired values ​​from the database by performing on-the-fly calculation of various functions.

Union, Intersection, and Differences

Combine multiple queries in SQL

SELECT Employees. Name

FROM Employees

WHERE Employees. Number = 1

UNION

SELECT Employees. Name

FROM Employees, Salary

WHERE Salary. Number = 1

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

SELECT statement syntax and processing order

First of all, SELECT determines the area from which it will take data. To do this, use the FROM keyword. If it is not indicated what to choose.

Then, the SQL WHERE clause may be present. With it, 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.

Data Comparison Operators

There are several types of them. In SQL, comparison operators can check various types of values.

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

• ">". The sign is bigger. If the value of the left side of the expression is greater, a logical TRUE is returned and the condition is considered fulfilled.

• "<". The sign is less. Inverse of the previous statement.

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

• "<>". Not equal. The condition will be considered TRUE only if one operand is not equal to another. He has one more interpretation - “! =”.

LIKE

You can translate this keyword as "similar." The LIKE operator in SQL is used in approximately the same way - it executes a query using a template. That is, it allows you to expand the selection of data from the database using regular expressions.

For example, such a task was set: to get all the people whose name ends with “I” from the already known base “Employees”. Then the query can be composed as follows:

SELECT *

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 determines that the last character should be just that.

CASE

This SQL Server statement is a multiple choice implementation. It resembles the switch construct in many programming languages. The CASE statement in SQL performs an action under several conditions.

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

Then the query can be composed as follows:

SELECT *

FROM Salary

WHERE CASE WHEN SELECT MAX (Accrued) THEN Maximum

WHEN SELECT MIN (Accrued) THEN Minimum

END total

In this context, the system searches for the maximum and minimum values ​​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, in SQL there is also a more compact form of CASE - COALESCE.

Data Definition Operators

This view allows for a variety of table changes — creating, deleting, modifying, and working with indexes.

The first one worth considering is CREATE TABLE. He does nothing but creates a table. Just typing a CREATE TABLE query will not happen, as you need to specify a few more parameters.

For example, to create an already familiar table "Employees" you need to use the commands:

CREATE TABLE Employees

(Number number (10) NOT NULL

Name varchar (50) NOT NULL

Surname varchar (50) NOT NULL)

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

DROP TABLE

Performs one simple task - deleting the specified table. It has an optional IF EXISTS parameter. It absorbs a delete 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 faster access to data. 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 interpretations of technologies.

ALTER TABLE

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

Various options for using ALTER TABLE will be presented below.

• ADD. Adds a column to the table. Its syntax is: ALTER TABLE table_name ADD column_name stored_data_type. May have an IF NOT EXISTS parameter, which suppresses the error if the created column already exists;

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

• CHANGE. Renames the field name to the specified one. Example usage: ALTER TABLE table_name CHANGE old_name new_name;

• MODIFY. This command will help to change the type and additional attributes of a particular column. And it is used like this: ALTER TABLE table_name MODIFY column_name column_data_type attributes;

CREATE VIEW

In SQL, there is such a thing as representation. 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, replace real column names.

The creation process takes place with a simple request:

CREATE VIEW view name AS SELECT FROM * table name

Sampling can occur as a whole base, or by some condition.

A bit about features

In SQL queries, various built-in functions are very often used that allow you to interact with data and convert it on the fly. It is worth considering them, as they form an integral part of a structured language.

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

• AVG. This function applies only to columns with numeric data. Its result is the determination of the arithmetic mean of all values;

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

• SUM. It's simple - the function calculates the sum of the column values. It is used exclusively for the numerical data type. By adding the DISTINCT parameter to the query, only unique values ​​will be summed;

• ROUND. The function of rounding decimal fractional numbers. The syntax uses the column name and the number of decimal places;

• Len. A simple function that calculates the length of column 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 examples with SQL statements have keywords that perform small tasks, but nevertheless greatly simplify fetching or database operations.

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

• BETWEEN. A very convenient tool for sampling. It indicates the range of values ​​among which you want to get data. The input accepts 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 recover data after using it. It should be noted that the implementation of this keyword in different interpretations of SQL may differ. Therefore, before trying to use TRUNCATE, it is better to familiarize yourself with the reference 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. The first indicates how many rows with the 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. He has already met among the examples of this in this article. You can derive the necessary rows from several tables by combining them UNION for more convenient use. Its syntax is: SELECT column_name FROM table_name UNION SELECT other_column_name FROM other_table_name. The result is a pivot table with combined queries.

• PRIMARY KEY. Translated as "primary key." Actually, it is precisely this terminology that is used in the reference materials. It means a unique string identifier. It is used, as a rule, when creating a table to indicate the field that will contain it.

• DEFAULT. Like the previous statement, it is used during the execution of the creating request. It defines the default value by which the field will be filled when it is created.

Some tips for developing a platform for working with SQL

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

2. Memory. This article has shown several features that can perform some tasks. When developing a shell for working with a database, you can “outweigh” the calculation of simple expressions on a database management system. In some cases, this gives a significant increase in performance.

3. Limitations If you need to get only two from a database with thousands of rows, then you should use operators like LIMIT or TOP. No need to retrieve data using shell development language tools.

4. Compound. After receiving data from several tables, many programmers begin to bring them together by means of shell memory. But why? After all, you can make one request in which all this will be present. You do not have to write extra code and reserve additional memory in the system.

5. Sorting. If it is possible to apply ordering in a request, that is, by DBMS forces, then you need to use it. This will significantly save on resources when running a program or service.

6. A lot of requests. If you have to insert many records in series, then for optimization, you should think about batch inserting data with a single query. This will also increase the performance of the entire system.

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

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

Conclusion

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

. , PL SQL , SQL Server. , .

, SQL, , .