sql statements. SQL SELECT and data selection queries
The SQL SELECT statement is intended for queries to select data from a database. It can be used either without conditions (selecting all rows in all columns or all rows in certain columns) or with multiple conditions (selecting specific rows) that are specified in the WHERE clause. Let's get acquainted with SQL tools that can be used to set these conditions for selecting data, and also learn how to use the SELECT statement in subqueries.
SELECT to select table columns
A query with a SELECT statement to select all columns of a table has the following syntax:
SELECT * FROM TABLE_NAME
That is, to select all columns of a table, you need to put an asterisk after the word SELECT.
Example 1. There is a company database - Company. It contains a table Org (Company Structure) and Staff (Employees). You need to select all columns from tables. The corresponding query to select all columns from the Org table is as follows:
SELECT * FROM ORG
This query will return the following (to enlarge the image, left-click on it):
The query to select all columns from the Staff table looks like this:
SELECT * FROM STAFF
This query will return the following:
To select specific table columns, instead of using an asterisk, we need to list the names of all the columns that need to be selected, separated by commas:
SELECT SELECTABLE_COLUMNS FROM TABLE_NAME
Example 2. Suppose you want to select the Depnumb and Deptname columns from the Org table, which contain data on the numbers of the company’s departments and their names, respectively. The query to obtain such a sample would be as follows:
SELECT DEPNUMB, DEPTNAME FROM ORG
And from the Staff table you need to select the columns DEPT, NAME, JOB, which respectively contain data about the number of the department in which the employee works, his name and position:
SELECT and ORDER BY - sorting (ordering) rows
The SQL SELECT queries discussed so far returned rows that could be in any order. However, you often need to sort rows by numerical order, alphabet, and other criteria. This is done using the ORDER BY keyword. Such requests have the following syntax:
Example 15. Let the requirement be to select from the Staff table employees working in department number 84 and sort (order) the records by the number of years worked in ascending order:
The word ASC indicates that the sort order is ascending. This word is optional because ascending sort order is the default. Result of the request:
Example 16. Let the requirement be to select the same data as in the previous example, but sort (arrange) the records by the number of years worked in descending order.
Structure Query Language (SQL) was created as a result of the development of the relational data model and is currently the de facto standard language for relational DBMSs. The SQL language today is supported by a huge number of DBMS of various types.
The name of the SQL language is usually pronounced "es-qu-el". Sometimes the mnemonic name "See-Quel" is used.
The SQL language provides the user (with minimal effort on his part) the following capabilities:
Create databases and tables with a complete description of their structure
Perform basic data manipulation operations: inserting, changing, deleting data
Run both simple and complex queries.
The SQL language is relationally complete.
The structure and syntax of its commands are quite simple, and the language itself is universal, i.e. the syntax and structure of its commands does not change when moving from one DBMS to another.
The SQL language has two main components:
DDL (Data Definition Language) for defining database structures and controlling access to data
DML (Data Manipulation Language) language designed for retrieving and updating data.
SQL is a non-procedural language, meaning that when you use it, you must specify what information should be obtained, not how it can be obtained. SQL commands are ordinary English words (SELECT, INSERT, etc.). Let's look at the SQL DML statements first:
SELECT - selecting 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 operator performs actions equivalent to the following relational algebra operations: selection, 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 whose data will be returned by the query. The from keyword specifies from which table (or view) the data is retrieved.
The result of a select query is always a table called the result table. Moreover, the results of a query executed using the select statement can be used to create a new table. If the results of two queries on different tables have the same format, you can combine them into one table. Also, the table obtained as a result of a query can be the subject of further queries.
To select all columns and all rows of a table, simply issue 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 table columns using a query
SELECT col1, col2, … , coln FROM tbl;
So, the result of the request
SELECT Type, Price FROM Product;
there will be a table
The list of columns in the select statement is also used if it is necessary to change the order of the columns in the resulting table:
In order to select only those table rows that satisfy certain restrictions, a special keyword where is used, followed by a logical condition. If a record satisfies this condition, it is included in the result. Otherwise, the entry is discarded.
For example, selecting those products from the Product table whose price satisfies the Price condition<3200, можно осуществить, используя запрос
SELECT * FROM Product where Price<3200;
His result:
The condition can be compound and combined using the logical operators NOT, AND, OR, XOR, for example: where id_ Price>500 AND Price<3500. Допускается также использование выражений в условии: where Price>(1+1) and string constants: where name= "autoweights".
Using the BETWEEN var1 AND var2 construct allows you to check whether the values of any expression fall within the range from var1 to var2 (including these values):
SELECT * FROM Product where Price BETWEEN 3000 AND 3500;
Similar to the NOT BETWEEN operator, there is the NOT IN operator.
Column names specified in the SELECT clause can be renamed. For this, the keyword AS is used, which, however, can be omitted, since it is implicitly implied. For example, request
SELECT Type AS model, Type_id AS num FROM Product where Type_id =3
will return (alias names should be written without quotes):
The LIKE operator is designed to compare a string with 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.
The sample is allowed to use two wildcard characters: "_" and "%". The first of them replaces one arbitrary character in the template, and the second replaces a sequence of arbitrary characters. So, "abc%" matches any string starting with abc, "abc_" matches a 4-character string starting with abc, "%z" matches any 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" like this:
SELECT * FROM Product where Type LIKE "a%";
|
truck scales |
If the search string contains a wildcard character, then you must specify the escape character in the ESCAPE clause. This control character must be used in the pattern before the wildcard character, indicating that the wildcard character should be treated as a regular character. For example, if a field were to be searched for all values containing the character "_", then the pattern "%_%" would 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 a NULL value in the fields of a table. Using regular comparison operators in these cases may produce incorrect results because comparing with NULL results in UNKNOWN. Thus, the selection condition should look like this:
where col_name IS NULL, instead of where col_name=NULL.
The default selection result returns records in the same order in which they are stored in the database. If you want to sort records by one of the columns, you must use the ORDER BY clause, followed by the name of that column:
SELECT * FROM tbl ORDER BY col_name;
This query will return records in ascending order of the col_name attribute value.
You can also sort records by multiple columns. To do this, their names must be specified after ORDER BY separated by commas:
SELECT * FROM tbl ORDER BY col_name1, col_name2.
Records will be sorted by the col_name1 field; if there are several records with a matching value in the col_name1 column, they will be sorted by the col_name2 field.
If you want to sort the records in reverse order (for example, descending by date), you must specify ORDER BY col_name DESC.
For direct sorting, there is the ASC keyword, which is accepted as the default value.
If the sample result contains hundreds or thousands of records, their output and processing takes considerable time.
Therefore, information is often divided into pages and presented to the user in portions. Pagination is used using the limit keyword followed by the number of entries to display. The following query retrieves the first 10 records while simultaneously sorting backwards on the col_name1 field:
SELECT * FROM tbl ORDER BY col_name1 DESC LIMIT 10
To retrieve the next 10 records, use the limit keyword with two values: the first specifies the position from which the result should be printed, and the second specifies the number of records to retrieve:
SELECT * FROM tbl ORDER BY col_name1 DESC LIMIT 10,10
To retrieve the next 10 records, you must use the LIMIT 20, 10 construct.
The structured query language SQL is based on relational calculus with variable tuples. The SQL language is designed to perform operations on tables, create, delete, change structure, and on table data, select, change, add and delete, as well as some related operations. SQL is a non-procedural language and does not contain control statements for organizing routines, input, output, etc.
Share your work on social networks
If this work does not suit you, at the bottom of the page there is a list of similar works. You can also use the search button
Structured query language SQL: history, standards,
Basic language operators.
The structured query language SQL is based on relational calculus with variable tuples. The language has several standards. The SQL language is designed to perform operations on tables (creating, deleting, changing the structure) and on table data (selecting, modifying, adding and deleting), as well as some related operations. SQL is a non-procedural language and does not contain control statements, subroutine organization, input-output, etc. In this regard, SQL is not used autonomously; it is usually immersed in the environment of the built-in DBMS programming language (for example, FoxPro DBMS Visual FoxPro, ObjectPAL DBMS Paradox, Visual Basic for Applications DBMS Access).
In modern DBMSs with an interactive interface, you can create queries using other tools, such as QBE. However, the use of SQL can often improve the efficiency of data processing in the database. For example, when preparing a query in the Access environment, you can move from the Query Builder window (formulating a sample query in the QBE language) to a window with the equivalent SQL statement. In some cases, preparing a new query by editing an existing one is easier to do by changing the SQL statement. The composition of SQL statements may differ slightly in different DBMSs. The SQL language does not have the functions of a full-fledged development language, but is focused on data access, so it is included in program development tools. In this case it is called embedded SQL. The SQL language standard is supported by modern implementations of the following programming languages: PL/1, Ada, C, COBOL, Fortran, MUMPS and Pascal.
In specialized application development systems of the client-server type, the programming environment is, in addition, usually supplemented with communication tools (establishing and disconnecting connections with database servers, detecting and processing errors occurring in the network, etc.), tools for developing user interfaces, design tools and debugging There are two main methods for using embedded SQL: static and dynamic. When using a language statically (static SQL), the program text contains calls to SQL language functions, which are rigidly included in the executable module after compilation.
Changes in called functions can be at the level of individual call parameters using programming language variables. When using the language dynamically (dynamic SQL), it is assumed that calls to SQL functions are built dynamically and the interpretation of these calls, for example, accessing data from a remote database, during program execution. The dynamic method is usually used in cases where the application does not know the type of SQL call in advance and it is constructed in dialogue with the user. The main purpose of the SQL language (as well as other languages for working with databases) is to prepare and execute queries. Retrieving data from one or more tables can result in a set of records called a view. A view is essentially a table that is generated as a result of a query. We can say that it is a type of stored query. You can build multiple views using the same tables. The view itself is described by specifying the view identifier and the request that must be made to obtain it.
For the convenience of working with views, the concept of a cursor was introduced into the SQL language. A cursor is a kind of pointer used to move through sets of records while processing them. The description and use of a cursor in SQL is as follows. In the descriptive part of the program, a variable of type cursor (CURSOR) is associated with an SQL statement (usually a SELECT statement). In the executing part of the program, the cursor is opened (OPEN<имя курсора>), moving the cursor through records (FETCH<имя курсора>...), followed by appropriate processing, and finally closing the cursor (CLOSE<имя курсора>).
Basic language operators
Let us describe a minimal subset of the SQL language, based on its implementation in the standard ODBC (Open Database Connectivity) interface from Microsoft. SQL language operators can be divided into two sublanguages: Data Definition Language (DDL) and Data Manipulation Language (DML). The main SQL language operators are presented in the table.
Let's consider the format and basic capabilities of the most important operators, with the exception of specific operators marked in the table with the symbol “*”. We will omit unimportant operands and syntax elements (for example, the rule adopted in many programming systems to put “;” at the end of the operator).
1. Operator creating a tablehas the format:
CREATE TABLE<имя таблицы>
(<имя столбца> <тип данных>
[,<имя столбца> <тип данных> ]...)
The required operands of the operator are the name of the table being created and the name of at least one column (field) indicating the type of data stored in this column.
When creating a table, some additional rules for controlling the values entered into them can be specified for individual fields. The NOT NULL construct serves precisely this purpose and, for a table column, means that a value must be defined in that column.
SQL statements
|
View |
Name |
Purpose |
|
CREATE TABLE DROP TABLE ALTER TABLE CREATE INDEX DROP INDEX CREATE VIEW DROP VIEW GRAND* REVOKE* |
creating a table deleting a table changing table structure index creation deleting an index creating a view deleting a view assigning privileges removal of privileges |
|
|
SELECT UPDAT INSERT DELETE |
sample records changing records inserting new records deleting entries |
In general, different DBMSs can use different types of data. The ODBC interface supports its own standard data types, for example, character ones (SQL_CHAR, SQL_VARCHAR, SQL_LONGVARCHAR), etc. When working with the database of a certain DBMS through the ODBC interface, the standard data types supported by the interface are automatically converted into source data types and vice versa. If necessary, data exchange between the program and the data source can be carried out without conversion in the internal data format of the source.
Example 1 . Creating a table.
Suppose you want to create a table goods describing goods that has the following fields: type type of product, comp_id identifier of the manufacturing company, name name of the product and price price of the product. The table definition statement could look like this:
CREATE TABLE goods (type SQL_CHAR(8) NOT NULL,
comp_id SQL_CHAR(10) NOT NULL, name SQL_VARCHAR(20),
price SQL_DECIMAL(8,2)).
2. Operator table structure changeshas the format:
ALTER TABLE<имя таблицы>
((ADD, MODIFY, DROP)<имя столбца> [<тип данных>]
[,(ADD, MODIFY, DROP)<имя столбца> [<тип данных>]]...)
Changing the structure of a table can consist of adding (ADD), changing (MODIFY) or removing (DROP) one or more table columns. The rules for writing the ALTER TABLE statement are the same as for the CREATE TABLE statement. When deleting a column, indicate<тип данных>no need.
3. Operator deleting a tablehas the format:
DROP TABLE<имя таблицы>
The operator allows you to delete an existing table. For example, to delete a table named items, it is enough to write a statement like: DROP TABLE items.
4. Operator creating an indexhas the format:
CREATE INDEX< имя индекса >
ON< имя таблицы >
(<имя столбца>[ASC | DESC ]
[,<имя столбца>[ASC | DESC]...)
The operator allows you to create an index on one or more columns of a given table in order to speed up the execution of query and search operations on the table. You can create multiple indexes on a single table. By specifying the optional UNIQUE option, you can ensure that the values in all columns specified in the statement are unique. Essentially, creating an index using the UNIQUE attribute means defining a key in a previously created table. When you create an index, you can set the values in columns to be automatically sorted in ascending order ASC (default), or descending order DESC. You can set different sort order for different columns.
5. Operator index deletionhas the format:
DROP INDEX<имя индекса>
This operator allows you to delete a previously created index with the corresponding name. So, for example, to destroy the main_indx index on the emp table, it is enough to write the DROP INDEX main_indx statement.
6. Operator creating a viewhas the format:
CREATE VIEW<имя представления>
[(<имя столбца> [,<имя столбца> ]...)]
AS<оператор SELECT>
This operator allows you to create a view. If column names are not specified in the view, the column names from the query described by the corresponding SELECT statement will be used.
7. Delete operator representation has the following format:
DROP VIEW<имя представления>
The operator allows you to delete a previously created view. Note that when you delete a view, the tables participating in the query are not deleted. Removing a gerg representation is done with an operator of the form: DROP VIEW repr.
8. Record selection operator has the format:
SELECT
< список данных >
FROM<список таблиц>
... ]
...]
This is the most important statement of all SQL statements. Its functionality is enormous. Let's look at the main ones. The SELECT statement allows you to select and perform calculations on data from one or more tables. The result of the statement is a response table, which may or may not have (DISTINCT) duplicate rows. By default, all rows, including duplicate ones, are included in the response table. Data selection involves records from one or more tables listed in the list of the FROM operand. The data list can contain the names of the columns participating in the query, as well as expressions over the columns. In the simplest case, expressions can contain column names, arithmetic symbols (+, , *, /), constants, and parentheses. If an expression is written in the data list, then along with the data selection, calculations are performed, the results of which fall into a new (created) column of the response table. When using column names of several tables in data lists, a construction of the form is used to indicate that a column belongs to a certain table:<имя таблицы>.<имя столбца>.
The WHERE operand specifies the conditions that must be satisfied by the records in the resulting table. Expression<условие выборки>is Boolean. Its elements can be column names, comparison operations, arithmetic operations, logical connectives (AND, OR, NOT), parentheses, special functions LIKE, NULL, IN, etc. The GROUP BY operand allows you to select groups in the resulting set of records.
9. Operator record changeshas the format:
UPDATE<имя таблицы>
SET<имя столбца> = {<выражение>, NULL )
[, SET<имя столбца> = {<выражение>, NULL)...]
The execution of an UPDATE statement consists of changing the values in the table columns specified by the SET operand for those records that satisfy the condition specified by the WHERE operand. New field values in records can be empty (NULL) or calculated according to an arithmetic expression. The rules for writing arithmetic and logical expressions are similar to the corresponding rules for the SELECT statement.
10. Operator inserting new recordshas two types of formats:
INSERT INTO<имя таблицы>
[(<список столбцов>)]
VALUES (<список значений>)
INSERT INTO<имя таблицы>
[(<список столбцов>)]
<предложение SELECT>
In the first format, the INSERT statement is used to enter new records with specified values in the columns. The order of listing the column names must match the order of the values listed in the list of the VALUES operand. If<список столбцов>omitted, then in<списке значений>All values must be listed in the order of the table structure columns.In the second format, the INSERT statement is intended to enter intoa given table of new rows selected from another table withusing the SELECT clause.
PAGE 1
Other similar works that may interest you.vshm> |
|||
| 16. | Learn the basics of the structured query language T-SQL | 34.15 KB | |
| To achieve this goal, it is necessary to solve the following tasks: create queries for selection from several tables in SQL language using specified selection criteria; create a selection query in SQL containing static aggregate functions; create a query that combines the results of two or more queries into one set of results using the UNION command. As a result of completing the work, students should know: categories of SQL commands; basic SQL commands used to build a query; principles of creating SQL queries... | |||
| 6030. | LANGUAGE AS A DEVELOPING PHENOMENON. EXTERNAL AND INTERNAL FACTORS OF LANGUAGE DEVELOPMENT | 17.38 KB | |
| The problem of the origin of language involves two questions. The first question is related to the problem of the origin of language in general, how the human language developed, how a person learned to speak the second with the origin of each individual language. There is no evidence of this period preserved, therefore, when studying the origin of language in general, linguists have to operate not only with linguistic facts but also with data from related sciences. Interest in the problem of the origin of language arose a long time ago. | |||
| 10870. | Interdisciplinary connections of the course “Professional Russian language”. Transformation and differentiation of the professional Russian language | 10.57 KB | |
| Transformation and differentiation of the professional Russian language 1. Transformation and differentiation of the professional Russian language. Syntactic norms are determined by the structure of the language and, like other orthoepic, lexical and morphological norms, undergo changes in the process of language development. When mastering the syntax of a non-native language, there are a number of difficulties that arise when choosing forms of control and coordinating the construction of sentences, using participles, choosing the right preposition, and so on. | |||
| 6929. | History of the Delphi language | 13.01 KB | |
| Delphi is the Greek city where the Delphic oracle lived. Delphi is a combination of several important technologies: High-performance compiler to machine code Object-oriented model of components Visual and therefore high-speed construction of applications from software prototypes Scalable tools for building databases Compiler to machine code The compiler built into Delphi provides the high performance necessary for building applications in the architecture ... | |||
| 10869. | The concepts of “professional language”, “language of specialty”, their differentiation. Professional Russian language: its origin, functions, scope of functioning (taking into account the specifics of the specialty) | 9.5 KB | |
| Differentiation of language. Each economic specialty, in addition to the common language common to all economists, also has its own special and specialized language. Specialists communicate in these professional languages orally and in writing; these professional languages are taught to students; such professional languages describe systems of knowledge and skills in scientific educational reference and other literature. In the economic language system, there are problems common to all professional languages. | |||
| 1335. | Basic word-formation models in modern American English | 117.01 KB | |
| The main differences between American and British English. The problem of determining the world status of American English. American English in the modern world. Lexical features of American English. | |||
| 1936. | HISTORY, STRUCTURE AND BASIC CONCEPTS OF ECOLOGY | 495.77 KB | |
| The term “ecology” is formed from the Greek roots “oikos” - home, the immediate environment of a person and “logos” - science. Therefore, in a literal sense, ecology is the science of organisms, including humans, observed within the confines of their home, with special attention paid to the nature of the relationships between organisms and their environment. | |||
| 17746. | Pedagogy of visual creativity: history and main development trends | 25.96 KB | |
| The task of the test is to consider the concept of children's artistic creativity, to identify the research of outstanding teachers and psychologists and the history of the formation of children's artistic creativity. A manifestation of artistic creativity can be individual works - done independently or under the guidance of an adult, drawings, modeling, oral and written art, melodies, dramatization, dancing, as well as choral singing, theatrical performances, arts and crafts, carving, puppet theater, drawn and fiction films, and... | |||
| 6285. | Control statements | 103.51 KB | |
| Loop Operators Loop operators are used to organize repeated calculations. To organize a loop, you need a variable called a loop parameter or a loop control variable. Any cycle consists of: initial settings or a cycle parameter initialization block; loop bodies, that is, those statements that are executed several times; cycle parameter modification block; checking the condition for exiting the loop, which can be placed either before the body of the loop, then we speak of a loop with a precondition or after the body... | |||
| 2784. | Conditional and selection statements | 16 KB | |
| Condition operator If. Therefore, you can already write the following assignment operators: Koren:=Sqrtxy; Modul:=bsxy. To implement such conditional jumps in Pascal, the If and Else operators are used, as well as the Goto unconditional jump operator. Let's look at the If statement. | |||
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 this query 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 obtained 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 the values in column 2 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 by 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. The FROM keyword is used for this. 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, comparison operators can test different types of 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 NULL.
DROP TABLE
Performs one simple task - dropping the specified table. Has an 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. Used operator 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. It takes as input the 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.
SQL (Structured Query Language) is a universal computer language used to create, modify and manage data in relational databases. The SQL language is based on relational algebra and is a collection of operators.
There are 4 groups of operators. Let's consider a group of data manipulation operators (Data Manipulation Language, DML, SQL DML)
Data selection
Data selection is the most common operation performed using SQL. The SELECT statement is one of the most important statements in this language, used to select data. The syntax of this operator is as follows:
SELECT column FROM table
SELECT statements must contain the words SELECT and FROM; other keywords are optional.
The SELECT keyword is followed by information about which fields to include in the resulting data set. An asterisk (*) denotes all table fields, for example:
To select one column, use the following syntax:
SELECT Company
An example of selecting multiple columns looks like this:
SELECT Company,Phone,Mail
To specify the names of the tables from which records are selected, use the FROM keyword, for example:
SELECT * FROM Customers
This query will return all fields from the Customers table.
You can use the WHERE clause (optional) to filter the results returned by the SELECT statement
SELECT * FROM Products WHERE Category = 4
You can use various expressions in the WHERE clause,
WHERE expression1 [(AND | OR) expression2 ...]
For example:
SELECT * FROM Products WHERE Category = 2 AND Postavshik > 10
SELECT Name, Price FROM Products WHERE Category= 3 OR Price< 50
You can use the following operators:
< Меньше
<= Меньше или равно
<>Not equal
> More
>= Greater than or equal to
The ORDER BY clause (optional) is used to sort the resulting data set by one or more columns. The keywords ASC (ascending) or DESC (descending) are used to determine the sort order. By default, data is sorted in ascending order.
Data modification
In addition to retrieving data, SQL can be used to update and delete data, copy records to other tables, and perform other operations. Below we'll look at the UPDATE, DELETE, and INSERT statements used to solve some of these problems.
UPDATE statement
To change values in one or more columns of a table, use the UPDATE statement. The syntax of this operator is:
UPDATE table SET column1 = expression1 WHERE criteria
The expression in the SET clause can be a constant or the result of a calculation. For example, to increase the prices of all products that cost less than $10, you could run the following query:
UPDATE Products SET Price = Price * 1.1 WHERE Price< 10
DELETE statement
To delete rows from tables, you should use the DELETE operator, the syntax of which is:
DELETE FROM table WHERE criteria
Delete all products whose cost is less than 100:
DELETE FROM Products WHERE Price< 100
OperatorINSERT
To add records to tables, you should use the INSERT statement, the syntax of which is:
INSERT INTO table ( VALUES (expression [, ...])
For example, to add a new customer to the Customers table, you could use the following query:
INSERT INTO Customers (CustomerID, CompanyName) VALUES ('XYZ', 'XYZ Deli')
