Basic operators of the SQL language. Interactive SQL
The Structure Query Language (SQL) was created as a result of the development of the relational data model and is currently the de facto language standard for relational DBMS. The SQL language today is supported by a huge number of DBMS of various types.
The name of the SQL language is usually pronounced by the letters "es-q-el". The mnemonic name "See-Quel" is sometimes used.
The SQL language provides the user (with minimal effort on his part) with the following features:
Create databases and tables with a complete description of their structure
Perform basic data manipulation operations: insert, modify, delete data
Perform both simple and complex queries.
The SQL language is relationally complete.
The structure and syntax of its commands are quite simple, and the language itself is universal, i.e. the syntax and structure of its commands does not change when moving from one DBMS to another.
The SQL language has two main components:
DDL (Data Definition Language) for defining database structures and controlling access to data
DML (Data Manipulation Language) is used to retrieve and update data.
The SQL language is non-procedural, i.e. when using it, you must specify what information is to be obtained, and not how it can be obtained. SQL commands are ordinary English words (SELECT, INSERT, etc.). Consider first the SQL DML statements:
SELECT - fetching data from the database
INSERT - inserting data into a table
UPDATE - updating data in a table
DELETE - deleting data from a table
SELECT statement
The SELECT statement performs actions equivalent to the following relational algebra operations: select, project, and join.
The simplest SQL query using it looks like this:
SELECT col_name FROM tbl
The select keyword is followed by a comma-separated list of columns whose data will be returned as a result of the query. The from keyword specifies which table (or view) the data is being retrieved from.
The result of a select query is always a table, which is called the result table. Moreover, the results of a query executed with a select statement can be used to create a new table. If the results of two queries against different tables have the same format, they can be combined into one table. Also, the table obtained as a result of a query can become the subject of further queries.
To select all columns and all rows of a table, it is sufficient to issue a SELECT * FROM tbl;
Consider a Product table containing price information for various products:
Request result
SELECT * FROM Product;
will be the entire table Product.
You can select specific columns of a table using a query
SELECT col1, col2, … , coln FROM tbl;
So, the query result
SELECT Type, Price FROM Product;
there will be a table
The list of columns in the select statement is also used if it is necessary to change the order of the columns in the resulting table:
In order to select only those table rows that satisfy certain restrictions, the special keyword where is used, followed by a logical condition. If the entry satisfies this condition, it is included in the result. Otherwise, the entry is discarded.
For example, selecting those products from the Product table whose price satisfies the Price condition<3200, можно осуществить, используя запрос
SELECT * FROM Product where Price<3200;
His result:
The condition can be compound and combined using the logical operators NOT , AND, OR, XOR, for example: where id_ Price>500 AND Price<3500. Допускается также использование выражений в условии: where Price>(1+1) and string constants: where name= "autobalances".
Using the BETWEEN var1 AND var2 construct allows you to check if the values of an expression fall within the interval from var1 to var2 (including these values):
SELECT * FROM Product where Price BETWEEN 3000 AND 3500;
By analogy with the NOT BETWEEN operator, there is the NOT IN operator.
Column names specified in the SELECT clause can be renamed. For this, the AS keyword is used, which, however, can be omitted, since it is implicitly implied. For example, a request
SELECT Type AS model, Type_id AS num FROM Product where Type_id =3
will return (alias names should be written without quotes):
The LIKE operator is for comparing a string with a pattern:
SELECT * FROM tbl where col_name LIKE "abc"
This query only returns records that contain the string value abc in the col_name column.
Two wildcard characters are allowed in the pattern: "_" and "%". The first of them replaces one arbitrary character in the template, and the second replaces a sequence of arbitrary characters. So, "abc%" matches any string that starts with abc, "abc_" matches any string of 4 characters that starts with abc, "%z" matches any string that ends with z, and finally "%z%" - sequences of characters containing z.
You can find all records in the Product table where the Type value begins with the letter "a" as follows:
SELECT * FROM Product where Type LIKE "a%";
|
truck scales |
If the search string contains a wildcard character, then you must specify a control character in the ESCAPE clause. This control character must be used in the pattern before the wildcard character, indicating that the latter should be treated as a regular character. For example, if you want to find all values containing the character "_" in a certain field, then the pattern "%_%" will return all records from the table. In this case, the template should be written as follows:
"%|_%" ESCAPE "|"
To check if the value matches the string "20%", you can use the following operator:
LIKE "20#%" ESCAPE "#"
The IS NULL operator allows you to check the absence (presence) of a NULL value in the fields of a table. Using normal comparison operators in these cases can lead to incorrect results, since comparison against NULL results in UNKNOWN (unknown). Thus, the selection condition should look like this:
where col_name IS NULL instead of where col_name=NULL.
The default fetch result returns records in the same order as they are stored in the database. If you want to sort records by one of the columns, you must use the ORDER BY clause, after which the name of this column is indicated:
SELECT * FROM tbl ORDER BY col_name;
As a result of this query, records will be returned in ascending order of the value of the col_name attribute.
Records can also be sorted by multiple columns. To do this, their names must be specified after ORDER BY, separated by commas:
SELECT * FROM tbl ORDER BY col_name1, col_name2.
The records will be sorted by the col_name1 field; if there are several records with the same value in the col_name1 column, then they will be sorted by the col_name2 field.
If you want to sort records in reverse order (for example, by date descending), you must specify ORDER BY col_name DESC.
For direct sorting, there is the ASC keyword, which is accepted as the default value.
If the result of the selection contains hundreds and thousands of records, their output and processing take a significant amount of time.
Therefore, information is often paginated and presented to the user in chunks. Pagination is used with the limit keyword followed by the number of entries to display. The following query retrieves the first 10 records while simultaneously sorting back on the col_name1 field:
SELECT * FROM tbl ORDER BY col_name1 DESC LIMIT 10
To extract the next 10 records, the limit keyword is used with two values: the first specifies the position from which to display the result, and the second specifies the number of records to retrieve:
SELECT * FROM tbl ORDER BY col_name1 DESC LIMIT 10,10
To retrieve the next 10 records, use the LIMIT 20, 10 construct.
Basic SQL statements. Syntax and examples of using the SELECT statement.
The SQL language is based on statements that are conditionally divided into several groups according to the functions they perform.
The following groups of statements can be distinguished (not all SQL statements are listed):
DDL (Data Definition Language) statements - statements for defining database objects
CREATE SCHEMA - create a database schema
DROP SHEMA - remove the database schema
· CREATE TABLE - create a table
· ALTER TABLE - change the table
DROP TABLE - drop a table
CREATE DOMAIN - create a domain
· ALTER DOMAIN - change the domain
DROP DOMAIN - delete a domain
CREATE COLLATION - create a sequence
DROP COLLATION - delete a sequence
CREATE VIEW - create a view
· DROP VIEW - delete view
DML (Data Manipulation Language) statements - data manipulation statements
SELECT - select rows from tables
INSERT - add rows to the table
· UPDATE - change rows in a table
· DELETE - delete rows in the table
COMMIT - commit changes made
ROLLBACK - roll back the changes made
Data protection and management operators
CREATE ASSERTION - create a constraint
DROP ASSERTION - remove a constraint
GRANT - grant privileges to a user or application to manipulate objects
REVOKE - revoke user or application privileges
At the same time, there are groups of statements for setting session parameters, obtaining information about the database, static SQL statements, dynamic SQL statements.
The most important for the user are data manipulation statements (DML).
Examples of Using Data Manipulation Operators
INSERT - insert rows into a table
Example 1. Inserting one row into a table:
VALUES (4, "Ivanov");
UPDATE - update rows in a table
Example 3. Update multiple rows in a table:
SET PNAME = "Pushnikov"
WHERE P.PNUM = 1;
DELETE - delete rows in a table
Example 4. Deleting multiple rows in a table:
WHERE P.PNUM = 1;
Examples of using the SELECT statement
The SELECT statement is actually the most important and most complex SQL statement for the user. It is designed to retrieve data from tables, ᴛ.ᴇ. he, in fact, implements one of their basic purpose of the database - to provide information to the user.
The SELECT statement is always executed on some of the tables in the database.
Comment. In fact, in databases there are not only permanently stored tables, but also temporary tables and so-called views. Views are simply SELECT statements stored in the database. From a user's point of view, a view is a table that is not permanently stored in the database, but "emerges" when it is accessed. From the point of view of the SELECT statement, both persistent tables and temporary tables and views look exactly the same. Of course, when the system actually executes the SELECT statement, the differences between stored tables and views are taken into account, but these differences hidden from the user.
The result of a SELECT statement is always a table. Τᴀᴋᴎᴍ ᴏϬᴩᴀᴈᴏᴍ, in effect, the SELECT operator is similar to relational algebra operators. Any relational algebra operator must be expressed by a suitably worded SELECT statement. The complexity of the SELECT statement is determined by the fact that it contains all the features of relational algebra, as well as additional features that are not available in relational algebra.
SELECT Statement Execution Order
In order to understand how the result of executing a SELECT statement is obtained, let's consider the conceptual scheme of its execution. This scheme is just a conceptual one, because it is guaranteed that the result will be the same as if it were carried out step by step according to this scheme. In fact, the real result is obtained by more sophisticated algorithms that a particular DBMS "owns".
Stage 1. Execution of a single SELECT statement
If the operator contains the keywords UNION, EXCEPT and INTERSECT, then the query is divided into several independent queries, each of which is executed separately:
Step 1 (FROM). The direct Cartesian product of all tables specified in the mandatory FROM clause is calculated. As a result of step 1, we get table A.
Step 2 (WHERE). If there is a WHERE clause in the SELECT statement, then table A, obtained in step 1, is scanned. In this case, for each row from table A, the conditional expression given in the WHERE clause is evaluated. Only those rows for which the conditional evaluates to TRUE are included in the result. If the WHERE clause is omitted, then we immediately go to step 3. If nested subqueries are involved in the conditional expression, then they are calculated in accordance with this conceptual scheme. As a result of step 2, we get table B.
Step 3 (GROUP BY). If there is a GROUP BY clause in the SELECT statement, then the rows of table B obtained in the second step are grouped according to the grouping list given in the GROUP BY clause. If the GROUP BY section is omitted, then we immediately go to step 4. As a result of step 3, we get table C.
Step 4 (HAVING). If a HAVING clause is present in the SELECT statement, groups that do not satisfy the conditional expression given in the HAVING clause are excluded. If the HAVING section is omitted, then we immediately go to step 5. As a result of step 4, we get table D.
Step 5 (SELECT). Each group obtained in step 4 generates one result row as follows. All scalar expressions specified in the SELECT clause are evaluated. According to the rules for using the GROUP BY clause, such scalar expressions must be the same for all rows within each group. For each group, the values of the aggregate functions given in the SELECT section are calculated. If there was no GROUP BY clause, but there are aggregate functions in the SELECT clause, then it is considered that there is only one group. If there is neither a GROUP BY clause nor aggregate functions, then it is considered that there are as many groups as there are rows selected so far. As a result of step 5, we get table E containing as many columns as there are elements in the SELECT section and as many rows as there are selected groups.
Stage 2. Execution of UNION, EXCEPT, INTERSECT operations
If the keywords UNION, EXCEPT and INTERSECT were present in the SELECT statement, then the tables obtained as a result of the execution of the 1st stage are combined, subtracted or intersected.
Stage 3. Ordering the result
If the SELECT statement contains an ORDER BY clause, then the rows of the table obtained in the previous steps are ordered according to the ordering list given in the ORDER BY clause.
Basic SQL statements. Syntax and examples of using the SELECT statement. - concept and types. Classification and features of the category "Basic SQL statements. Syntax and examples of using the SELECT statement." 2017, 2018.
Question number 1. SQL and its history. one
Question number 2. Description of basic SQL statements.. 1
Arithmetic functions. four
String processing functions. 5
Special features. 6
Date Processing Functions.. 7
Using aggregate functions in queries. 7
Question number 1. SQL and its history
The only means of communication for database administrators, designers, developers, and users with a relational database is the Structured Query Language (SQL). SQL is a full featured language for manipulating data in relational databases. It is currently the accepted, standard interface for relational databases such as Oracle, Informix, Sybase, DB/2, MS SQL Server, and a number of others (ANSI and ISO standards). SQL is a non-procedural language that is designed to process sets consisting of rows and columns of tables in a relational database. Although there are its extensions that allow procedural processing. Database designers use SQL to create all the physical objects of a relational database.
The theoretical foundations of SQL were laid down in the well-known article by Codd, which laid the foundation for the development of the theory of relational databases. The first practical implementation was carried out in the research laboratories of IBM Chamberlin D.D. and Royce R.F. The industrial application of SQL was first implemented in the Ingres DBMS. One of the first industrial relational DBMS is Oracle. Essentially, a relational DBMS is the software that manages the operation of a relational database.
The first international standard for the SQL language was adopted in 1989 (SQL-89). At the end of 1992, a new international standard, SQL-92, was adopted. Currently, most vendors of relational DBMS use it as a base. However, the work on the standardization of the SQL language is far from complete and the draft SQL-99 standard has already been developed, which introduces the concept of an object into the language and allows it to be referred to in SQL statements: In the original version of SQL there were no data flow control commands, they appeared in the recently adopted ISO/IEC 9075-5: 1996 SQL Supplement.
Each specific DBMS has its own implementation of SQL, which generally supports a certain standard, but has its own characteristics. These implementations are called dialects. For example, the 1SO/IEC 9075-5 standard provides for objects called persistently stored modules or PSM modules (Persistent Stored Modules). In Oracle DBMS, the PL/SQL extension is analogous to the above standard extension."
Question number 2. Description of basic SQL statements
SQL consists of a set of commands for manipulating data in a relational database that allow you to create relational database objects, modify data in tables (insert, delete, correct), change database relationship schemas, perform calculations on data, make selections from the database, maintain data security and integrity.
The entire set of SQL commands can be divided into the following groups:
Data definition commands (DDL - Data Definition Language);
Data manipulation commands (DML - Data Manipulation Language);
Data retrieval commands (DQL - Data Query Language);
transaction management commands;
data management commands.
When executed, each SQL command goes through four phases of processing:
The parsing phase, which includes checking the syntax of the command, checking the names of tables and columns in the database, as well as preparing the initial data for the optimizer;
the optimization phase, which includes substituting the actual names of tables and columns of the database into the view, identifying possible options for executing the command, determining the cost of executing each option, choosing the best option based on internal statistics;
the phase of generating the executable code, which includes the construction of the executable code of the command;
The command execution phase, which includes the execution of the command code.
Currently optimizer is an integral part of any industrial implementation of SQL. The work of the optimizer is based on collecting statistics about the commands being executed and performing equivalent algebraic transformations with database relations. Such statistics are stored in the system database directory. System directory is a data dictionary for each database and contains information about tables, views, indexes, columns, users and their access privileges. Each database has its own system catalog, which represents a collection of predefined database tables.
Table 8.1 contains a list of SQL commands in accordance with the accepted standard, with the exception of some commands that are practically not used in dialects.
Table 8.1. Typical list of SQL commands
| Team | Description |
| Commands for defining these objects | |
| ALTER TABLE | Changes the table description (relation schema) |
| CREATE EVENT | Creates a timer event in the database |
| CREATE INDEX | Create an index on a table |
| CREATE SEQUENCE | Creates a sequence |
| CREATE TABLE | Defines a table |
| CREATE TABLESPACE | Creates a tablespace |
| CREATE TRIGGER | Creates a trigger on the database |
| CREATE VIEW | Defines a view on tables |
| DROP INDEX | Physically removes an index from the database |
| DROP SEQUENCE | Deletes a sequence |
| DROP TABLE | Physically removes a table from the database |
| DROP TABLESPACE | Deletes a tablespace |
| DROP VIEW | Deletes a view |
| Data Manipulation Commands | |
| DELETE | Removes one or more rows from a database table |
| INSERT | Inserts one or more rows into a database table |
| UPDATE | Updates column values in a database table |
| Data selection commands | |
| SELECT | Executes a query to fetch data from tables and views |
| UNION | Combines the results of two or more SELECT statements into a single selection |
| Transaction management commands | |
| COMMIT | Ends a transaction and physically updates the current state of the database |
| ROLLBACK | Ends a transaction and returns the current state of the database at the time of the last completed transaction and checkpoint |
| SAVEPOINT | Assigns a breakpoint within a transaction |
| Data management commands | |
| ALTER DATABASE | Changes storage groups or transaction logs |
| ALTER DBAREA | Changes the size of database storage areas |
| ALTER PASSWORD | Changes the password for accessing the database |
| ALTER STOGROUP | Changes the composition of storage areas in a storage group |
| CHECK DATABASE | Checks the integrity of the database |
| CHECK INDEX | Checks the integrity of the index |
| CHECK TABLE | Checks the integrity of the table and index |
| CREATE DATABASE | Physically creates a database |
| CREATE DBAREA | Creates a database storage area |
| CREATE STOGROUP | Creates a storage group |
| CREATE SYSNONYM | Creates a synonym for a table or view |
| DEINSTAL DATABASE | Makes the database inaccessible to users of the computer network |
| DROP DATABASE | Physically removes databases |
| DROP DBAREA | Physically removes the database storage area |
| DROP STOGROUP | Deletes a storage group |
| GRANT | Defines user privileges and access control to the database |
| INSTALL DATABASE | Makes the database available to users of the computer network |
| LOCK DATABASE | Locks the currently active database |
| REVOKE | Revokes user privileges and database access restrictions |
| SET DEFAULT STOGROUP | Specifies the default storage group |
| UNLOCK DATABASE | Releases the current active database |
| UPDATE STATISTIC | Updates statistics for a database |
| Other commands | |
| COMMENT ON | Places comments to the description of database objects in the system catalog |
| CREATE SYNONYM | Defines alternative names for database tables and views in the system catalog |
| DROP SYNONYM | Removes alternative names for database tables and views from the system catalog |
| LABEL | Changes labels for system descriptions |
| ROWCOUNT | Calculates the number of rows in a database table |
The set of SQL commands listed in the table is not complete. This list is provided to give you an idea of what SQL can do in general. For a complete list of commands, please refer to the appropriate manual for your particular DBMS. It should be remembered that SQL is the only means of communication for all categories of users with relational databases.
Arithmetic functions
SQL supports a full set of arithmetic operations and mathematical functions for building arithmetic expressions on database columns (+, -, *, /, ABS, LN, SQRT, etc.).
A list of the main built-in math functions is given in Table 8.2 below.
| math function | Description |
| ABS(X) | Returns the absolute value of the number X |
| ACOS(X) | Returns the arc cosine of the number X |
| ASIN(X) | Returns the arcsine of X |
| ATAN(X) | Returns the arc tangent of the number X |
| COS(X) | Returns the cosine of X |
| EXP(X) | Returns the exponent of the number X |
| SIGN(X) | Returns -] if X< 0, 0, если X = 0, + 1, если X > 0 |
| LN(X) | Returns the natural logarithm of the number X |
| MOD(X,Y) | Returns the remainder of X divided by Y |
| CEIL(X) | Returns the smallest integer greater than or equal to X |
| ROUND(X,n) | Rounds the number X to a number with n decimal places |
| SIN(X) | Returns the sine of X |
| SQRT(X) | Returns the square root of X |
| TAN(X) | Returns the tangent of the number X |
| FLOOR(X) | Returns the largest integer less than or equal to X |
| LOG(a, X) | Returns the logarithm of the number X to base A |
| SINH(X) | Returns the hyperbolic sine of X |
| COSH(X) | Returns the hyperbolic cosine of X |
| TANH(X) | Returns the hyperbolic tangent of X |
| TRANC(X.n) | Truncates the number X to a number with n decimal places |
| POWER(A,X) | Returns the value of A raised to the power of X |
The set of built-in functions may vary depending on the version of the DBMS of one manufacturer and also in the DBMS of different manufacturers. . So, for example, in the SQLBase DBMS, Centure Inc. there is an @ATAN2(X,Y) function that returns the arc tangent of Y/X, but there is no SIGN(X) function.
Arithmetic expressions are needed to obtain data that is not directly stored in the columns of database tables, but whose values are needed by the user. Let's say you want a list of employees showing the payout each employee received, including bonuses and penalties.
SELECT ENAME, SAL, COMM. FINE, SAL + COMM - FINE
The arithmetic expression SAL + COMM - FINE is output as a new column in the resulting table, which is calculated as a result of the query. Such columns are also called derived (calculated) attributes or fields.
String Processing Functions
SQL provides you with a wide range of functions for manipulating string data (string concatenation, CHR, LENGTH, INSTR, and more). The list of basic functions for processing string data is given in Table 8.3.
Table 8.3. SQL Functions for String Processing
| Function | Description |
| CHR(N) | Returns the ASCII code character for the decimal code N |
| ASCII(S) | Returns the decimal ASCII code of the first character of a string |
| INSTR(S2,SI,pos|,N|) | Returns the position of string SI in string S2 greater than or equal to pos. N - number of occurrences |
| LENGTH(S) | Returns the length of the string |
| LOWER(S) | Changes all characters in a string to uppercase characters |
| INITCAP(S) | Sets the first character of each word in a string to uppercase and the remaining characters of each word to uppercase |
| SUBSTR(S,pos,[,len|) | Selects a substring of length ten in string S, starting at position pos |
| UPPER(S) | Converts uppercase letters in a string to uppercase letters |
| LPAD(S,N|,A|) | Returns the string S padded from the left with A's to the number of N characters. The default fill character is a space |
| RPAD(S, N |, A]) | Returns the string S right-padded with A's up to N characters. The default fill character is a space |
| LTRIM(S,|,Sll) | Returns the left-truncated string S. Characters are removed as long as the character to be removed is in the string - SI pattern (default is space) |
| RTRIM(S,|,SI |) | Returns the right-truncated string S. Characters are removed as long as the character to be removed is in the string - pattern S1 (default is space) |
| TRANSLATES,(SI,S2) | Returns string S with all occurrences of string SI replaced by string S2. If SI<>S2, then characters that do not match are excluded from the resulting string |
| REPLACED(SI,|,S2|) | Returns the string S with all occurrences of the substring SI replaced by the substring S2. If S2 is not specified, then all occurrences of the substring SI are removed from the resulting string S |
| NVL(X,Y) | If X is NULL, then returns to Y either a string, or a number, or a date, depending on the original type of Y |
The names of the same functions may differ in different DBMS. For example, the Oracle DBMS SUBSTR(S, pos, |, len|) function in SQLBase is called @SUBSTRING(S, pos, Ien). SQLBase has features that are not found in Oracle (see the table below for a list of such features).
Table 8.4. SQLBase String Functions Different from Oracle String Functions
| Function | Description |
| @EXACT(SI,S2) | Returns the result of comparing two strings |
| @LEFT(S,lcn) | Returns the left substring of length len |
| @LENGTH(S) | Returns the length of the string |
| @MID(S, pos, len) | Returns a substring of the specified length, starting at position pos |
| @REPEAT(S,n) | Repeats string S n times |
| @REPLACE(SI,pos,len,S2) | Replace positions pos len of characters in string S2 with characters from string SI |
| @RIGHT(S,len) | Returns the right substring of S of length len |
| @SCAN(S,pat) | Returns the position of the substring pat in the string S |
| @STRING(X, scale) | Returns the symbolic representation of a number with the specified scale |
| @TRIM(S) | Removes spaces from the right and left of a string |
| @VALUE(S) | Converts the character representation of a number to a numeric value |
You can use the INITCAP function so that when you get a list of employee names, last names always begin with a capital letter and all others are in uppercase.
SELECT INITCAP(ENAME)
Special Features
SQL provides a set of special functions for converting column values. A list of such functions is given in Table 8.5.
Table 8.5. Special Features
In the EMPLOYEE table, for each employee, you can enter a gender attribute - add a CHAR (l) SEX column (0 - male, 1 - female). Let's say that you need a list of employees, in which you want to separate them by gender, indicating it in a numeric format; then you can issue the following command:
SELECT ENAME, LNAME, AGE, " Floor:", TO_NUMBER(SEX)
As an example of using the DECODE function, here is a query that calculates a list of employees with their manager. If the leader is unknown, then the default is "no".
SELECT ENAME, DEC0DE(DEPN0, 10, " Dryagin", 20,"Zhilyaeva". 30,"
Korotkov", "does not have")
Suppose that the head of the organization has an undefined value for the DEPNO column and, therefore, the default provided in DECODE will work for him .
©2015-2019 site
All rights belong to their authors. This site does not claim authorship, but provides free use.
Page creation date: 2016-08-07
The SQL language is based on statements that are conditionally divided into several groups according to the functions they perform. The following groups of statements can be distinguished (not all SQL statements are listed):
ddl (Data Definition Language) statements
DDL statements - statements for defining database objects
CREATE SCHEMA - create a database schema
DROP SHEMA - drop a database schema
CREATE TABLE - create a table
ALTER TABLE - change a table
DROP TABLE - drop a table
CREATE DOMAIN - create a domain
ALTER DOMAIN - change domain
DROP DOMAIN - delete a domain
CREATE COLLATION - create a sequence
DROP COLLATION - remove a sequence
CREATE VIEW - create a view
DROP VIEW - delete a view
dml (Data Manipulation Language) statements
DML Statements - Data Manipulation Statements
SELECT - select rows from tables
INSERT - add rows to a table
UPDATE - change rows in a table
DELETE - delete rows in a table
COMMIT - commit the changes made
ROLLBACK - rollback changes made
Data protection and management operators
CREATE ASSERTION - create a constraint
DROP ASSERTION - remove a constraint
GRANT - grant privileges to a user or application to manipulate objects
REVOKE - revoke user or application privileges
In addition, there are groups of statements for setting session parameters, obtaining information about the database, static SQL statements, dynamic SQL statements. The most important for the user are data manipulation statements (DML).
Examples of Using Data Manipulation Operators
INSERT - insert rows into a table
Example 1 . Inserting one row into a table:
VALUES (4, "Ivanov");
Example 2 . Inserting several rows selected from another table into the table (data about suppliers from table P with numbers greater than 2 are inserted into the TMP_TABLE table):
TMP_TABLE (PNUM, PNAME)
SELECT PNUM, PNAME
WHERE P.PNUM>2;
UPDATE - update rows in a table
Example 3 . Update multiple rows in a table:
SET PNAME = "Pushnikov"
WHERE P.PNUM = 1;
DELETE - delete rows in a table
Example 4 . Deleting multiple rows in a table:
WHERE P.PNUM = 1;
Example 5 . Deleting all rows in a table:
Examples of using the SELECT statement
The SELECT statement is actually the most important and most complex SQL statement for the user. It is designed to retrieve data from tables, i.e. it, in fact, implements one of the main purposes of the database - to provide information to the user.
The SELECT statement is always executed on some of the tables in the database.
Comment. In fact, databases can have not only permanently stored tables, but also temporary tables and so-called views. Views are simply SELECT statements stored in the database. From the user's point of view, a view is a table that is not stored permanently in the database, but "arises" at the moment it is accessed. From the point of view of the SELECT statement, both persistent tables and temporary tables and views look exactly the same. Of course, when the system actually executes the SELECT statement, the differences between stored tables and views are taken into account, but these differences hidden from the user.
The result of a SELECT statement is always a table. Thus, in terms of the results of actions, the SELECT statement is similar to the operators of relational algebra. Any relational algebra operator can be expressed by a suitably worded SELECT statement. The complexity of the SELECT statement is determined by the fact that it contains all the features of relational algebra, as well as additional features that are not available in relational algebra.
SQL (StructuredQueryLanguage - “Structured Query Language”) is a general-purpose computer language used to create, modify, and manipulate data in relational databases. The SQL language is based on relational algebra and is a collection of operators.
There are 4 groups of operators. Consider a group of data manipulation operators (Data Manipulation Language, DML, SQL DML)
Data selection
Selecting data is the most common operation performed using SQL. The SELECT statement is one of the most important statements in this language for selecting data. The syntax for 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 dataset. An asterisk (*) denotes all fields in a table, for example:
To select a single column, the following syntax is used:
SELECT company
An example of selecting multiple columns looks like this:
SELECT company, phone,Mail
The FROM keyword is used to specify the names of the tables from which to select records, 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 a SELECT statement
SELECT * FROM Products WHERE Category = 4
You can use different 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
The ORDER BY clause (optional) is used to sort the result set by one or more columns. The keywords ASC (ascending) or DESC (descending) are used to define the sort order. By default, the 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 accomplish some of these tasks.
UPDATE statement
An UPDATE statement is used to change values in one or more columns of a table. The syntax for this operator is:
UPDATE table SET column1 = expression1 WHERE criteria
An expression in a SET clause can be a constant or the result of a calculation. For example, to increase the price 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, use the DELETE statement, the syntax of which is:
DELETE FROM table WHERE criteria
Remove all products whose cost is less than 100:
DELETE FROM Products WHERE price< 100
OperatorINSERT
To add records to tables, 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 can use the following query:
INSERT INTO Customers (CustomerID, CompanyName) VALUES ('XYZ', 'XYZ Deli')
