SQL command structure
SQL LANGUAGE: DATA MANIPULATION
IN THIS LECTURE...
· The purpose of the Structure Query Language (SQL) and its special role when working with databases.
· History of the emergence and development of the SQL language.
· Writing SQL statements.
· Retrieving information from databases using the SELECT statement.
· construction of SQL statements characterized by the following features:
· using the WHERE construct to select rows that satisfy various conditions;
· sorting query results using the ORDER BY construct;
· use of SQL aggregation functions;
· grouping selected data using the GROUP BY construct;
· use of subqueries;
· using table joins;
· application of operations with sets (UNION, INTERSECT, EXCEPT).
· Making changes to the database using INSERT, UPDATE and DELETE statements.
One of the languages that emerged as a result of the development of the relational data model is SQL, which has now become very widespread and has actually become the standard language for relational databases. The SQL language standard was released by the US National Standards Institute (ANSI) in 1986, and in 1987 the International Organization for Standardization (ISO) adopted this standard as an international standard. Currently, the SQL language is supported by hundreds of different types of DBMSs, developed for a wide variety of computing platforms, ranging from personal computers to mainframes.
This lecture uses the ISO definition of SQL.
Introduction to SQL
In this part, we will look at the purpose of the SQL language, get acquainted with its history, and analyze the reasons why it has now become so important for database applications.
Purpose of the SQL language
Any language designed to work with databases must provide the user with the following capabilities:
· create databases and tables with a complete description of their structure;
· Perform basic data manipulation operations, such as inserting, modifying and deleting data from tables;
· Perform simple and complex queries.
In addition, the database language must solve all the above problems with minimal effort on the part of the user, and the structure and syntax of its commands must be quite simple and easy to learn.
And finally, the language must be universal, i.e. meet some recognized standard, which will allow the use of the same syntax and command structure when moving from one DBMS to another. The modern SQL language standard satisfies almost all of these requirements.
SQL is an example of a data transformation language, or a language designed to work with tables to transform input data into the desired output form. The SQL language, which is defined by the ISO standard, has two main components:
· Data Definition Language (DDL), designed to define database structures and control access to data;
· Data Manipulation Language (DML), designed for retrieving and updating data.
Before the advent of the SQL3 standard, SQL included only commands for defining and manipulating data; it lacked any commands to control the progress of calculations. In other words, this language did not have IF ... THEN ...ELSE, GO TO, DO ... WHILE and any other commands designed to control the progress of the computational process. Such tasks had to be solved programmatically, using programming or task control languages, or interactively, as a result of actions performed by the user himself. Due to this incompleteness, from the point of view of organizing the computing process, the SQL language could be used in two ways. The first involved interactive work, consisting in the user entering individual SQL statements from the terminal. The second was to embed SQL statements into procedural language programs.
Advantages of the SQL3 language, the formal definition of which was adopted in 1999:
· The SQL language is relatively easy to learn.
· It is a non-procedural language, so it needs to specify what information should be obtained, not how it can be obtained. In other words, the SQL language does not require specifying data access methods.
· Like most modern languages, SQL supports a free format for writing statements. This means that individual operator elements are not associated with fixed positions on the screen as you type.
· The command structure is specified by a set of keywords, which are ordinary English words, such as CREATE TABLE - Create a table, INSERT - Insert, SELECT - Select.
For example:
CREATE TABLE [Sales] ( (S), [Object name] VARCHAR(15), [Cost] DECIMAL(7,2));
INSERT INTO [Object] VALUES ("SG16", "Brown", 8300);
SELECT , [Object name], [Cost];
FROM [Sales]
WHERE [Cost] > 10000;
· The SQL language can be used by a wide range of users, including database administrators (DBAs), company management personnel, application programmers, and many other end users of various categories.
Currently, there are international standards for the SQL language that formally define it as the standard language for creating and manipulating relational databases, which it actually is.
History of the SQL language
The history of the relational data model, and indirectly the SQL language, began in 1970 with the publication of a seminal article by E. F. Codd, who at that time worked at the IBM research laboratory in San Jose. In 1974, D. Chamberlain, who worked in the same laboratory, published a definition of a language called “Structured English Query Language”, or SEQUEL. A revised version of this language, SEQUEL/2, was released in 1976; subsequently its name had to be changed to SQL for legal reasons - the abbreviation SEQUEL was already in use by philologists. But to this day, many people still pronounce the SQL acronym as "sequel", although it is officially recommended to read it as "es-qu-el".
In 1976, IBM released a prototype DBMS based on the SEQUEL/2 language, called "System R". The purpose of this trial was to test the feasibility of implementing the relational model. Among other positive aspects, the most important result of this project can be considered the development of the SQL language itself. However, the roots of this language go back to the SQUARE (Specifying Queries as Rational Expressions) language, which was the predecessor of the System R project. The SQUARE language was developed as a research tool for implementing relational algebra through phrases composed in English.
In the late 1970s, the company that has now become Oracle Corporation released the Oracle DBMS. Perhaps this is the very first commercial implementation of a relational DBMS built using the SQL language.
A little later, the INGRES DBMS appeared, using the QUEL query language.
This language was more structured than SQL, but its semantics were less close to ordinary English. Later, when SQL was adopted as the standard language for relational databases, the INGRES DBMS was completely converted to use it. In 1981, IBM released its first commercial relational DBMS, called SQL/DS (for the DOS/VSE environment). In 1982, a version of this system was released for the VM/CMS environment, and in 1983 - for the MVS environment, but already under the name DB2.
In 1982, the US National Standards Institute (ANSI) began work on the Relation Database Language (RDL), guided by concept documents received from IBM Corporation. In 1983, the International Organization for Standardization (ISO) became involved in this work. The joint efforts of both organizations culminated in the release of the SQL language standard. The name RDL was abandoned in 1984, and the draft language was redesigned to be closer to existing implementations of the SQL language.
The original version of the standard, which was released by ISO in 1987, attracted a wave of criticism. In particular, Date, a well-known researcher in this area, pointed out that the standard omits critical functions, including referential integrity controls, and some relational operators.
In addition, he noted the excessive redundancy of the language - the same request could be written in several different versions. Most of the criticism was recognized as fair, and the necessary adjustments were made to the standard before its publication. However, it was decided that it was more important to release the standard as quickly as possible so that it could serve as a common basis on which both the language itself and its implementations could be further developed, rather than waiting until all the features that different experts considered mandatory were defined and agreed upon. for a similar language.
In 1989, ISO published an amendment to the standard that defined data integrity functions. In 1992, the first substantially revised version of the ISO standard, sometimes called SQL2 or SQL-92, was released. Although some of the functions were defined in this standard for the first time, many of them have already been implemented in whole or in part in one or more commercial implementations of the SQL language.
And the next version of the standard, which is commonly called SQL3, was released only in 1999. This version provides additional support for object-oriented data management features.
Functions that are added to the language standard by developers of commercial implementations are called extensions. For example, the SQL language standard defines six different types of data that can be stored in databases. In many implementations, this list is supplemented by various extensions. Each implementation of a language is called a dialect. There are no two dialects that are completely identical, and there is currently no dialect that fully complies with the ISO standard.
Moreover, as database developers add new functionality to their systems, they continually expand their dialects of SQL, causing individual dialects to become more and more different from each other. However, the basic core of the SQL language remains more or less standardized across all implementations.
Although the original concepts of the SQL language were developed by IBM, its importance soon prompted other developers to create their own implementations. Currently, there are literally hundreds of products available on the market built using the SQL language, and we constantly hear about the release of more and more new versions,
databases that could function on numerous computer systems of various types. Indeed, with its help, users can manipulate data regardless of whether they are working on a personal computer, a network workstation or a mainframe.One of the languages that emerged as a result of the development of the relational data model is the SQL language (Structured Query Language), which has now become very widespread and has actually become standard language relational databases. Standard SQL was released by the American National Standards Institute (ANSI) in 1986, and was adopted internationally by the International Standards Organization (ISO) in 1987. The current SQL standard is known as SQL/92.
The use of any standards is associated not only with numerous and quite obvious advantages, but also with certain disadvantages. First of all, standards direct the development of the relevant industry in a certain direction; In the case of the SQL language, having strong underlying principles ultimately leads to interoperability among its various implementations and contributes to both increased portability of software and databases in general, and the versatility of database administrators. On the other hand, standards limit the flexibility and functionality of a particular implementation. Under language implementation SQL refers to the SQL software product of the respective manufacturer. To expand functionality, many developers who adhere to accepted standards add to standard language SQL various extensions. It should be noted that the standards require any completed language implementations SQL has certain characteristics and broadly reflects major trends that not only lead to compatibility between all competing implementations, but also help to increase the value of SQL programmers and users relational databases in the modern software market.
All specific language implementations are somewhat different from each other. It is in the manufacturers' best interest to ensure that their implementations meet current ANSI standards for portability and user experience. However, each implementation of SQL contains enhancements to meet the requirements of a particular database server. These enhancements or extensions to the SQL language are additional commands and options that are additions to the standard package and are available in that particular implementation.
Currently, the SQL language is supported by many dozens of DBMSs of various types, developed for a wide variety of computing platforms, ranging from personal computers to mainframes.
All data manipulation languages created for many DBMSs before the advent of relational databases, were focused on operations with data presented in the form of logical file records. Of course, this required the user to have detailed knowledge of the data storage organization and serious effort to specify what data was needed, where it was located, and how to obtain it.
The SQL language under consideration is focused on operations with data presented in the form of logically interconnected sets of relation tables. The most important feature of its structures is its focus on the final result of data processing, and not on the procedure for this processing. The SQL language itself determines where the data is located, the indexes, and even what the most efficient sequence of operations should be used to obtain the result, so it is not necessary to specify these details in the database query.
Introduction to client-server technology
In connection with the expansion of the information services market, software manufacturers began to produce increasingly intelligent, and therefore voluminous, software systems. Many organizations and individual users often could not place purchased products on their own computers. For the exchange of information and its distribution, computer networks were created, and generalizing programs and data began to be installed on special file servers.
Thanks to DBMSs working with file servers, many users have access to the same databases. The development of various automated systems for managing organizations is simplified. However, with this approach, all processing of requests from programs or from user computer terminals is performed on them, therefore, to implement even a simple request, it is necessary to read or write entire files from the file server, and this leads to conflict situations and network overload. To eliminate these shortcomings, it was proposed client-server technology, but at the same time a unified language for communicating with the server was needed - the choice fell on SQL.
Client-server technology means a way of interaction of software components in which they form a single system. As the name itself suggests, there is a certain client process that requires certain resources, as well as server process, which provides these resources. It is not necessary for them to be on the same computer. It is usually customary to place the server on one node of the local network, and clients on other nodes.
In a database context, the client controls the application's user interface and logic, acting as a workstation that runs database applications. The client accepts a request from the user, checks the syntax, and generates a database query in SQL or another database language appropriate to the application logic. It then sends a message to the server, waits for a response, and formats the received data for presentation to the user. The server receives and processes requests to the database, and then sends the results back to the client. This processing includes verifying client credentials, ensuring integrity requirements, and fulfilling the request and updating the data. In addition, concurrency control and recovery are supported.
Client-server architecture has a number of advantages.
Are you new to programming or have you simply avoided learning SQL in the past? Then you have come to the right address, since any developer is eventually faced with the need to know this query language. You may not be the main database designer, but it is almost impossible to avoid working with them. I hope this brief overview of basic SQL query syntax will help the interested developer and anyone else who needs it.
What is a SQL database?
Structured Query Language ( S structured Q uery L anguage) is a database communication standard that is supported by ANSI. The most recent version is SQL-99, although a new standard is already in development. Most databases adhere firmly to the ANSI-92 standard. There has been much discussion about introducing more modern standards, but commercial database vendors are moving away from this by developing their own new concepts for storing stored data. Almost every single database uses some unique set of syntax, although very similar to the ANSI standard. In most cases, this syntax is an extension of the base standard, although there are cases where this syntax produces different results for different databases. It is always a good idea to review the database documentation, especially if you are getting unexpected results.
If you're new to SQL, there are some basic concepts you need to understand.
In general terms, "database" is the generic name for relational database management system(RDBMS). For some systems, "database" also refers to a group of tables, data, or configuration information that is an inherently separate part from other, similar constructs. In this case, each SQL database installation may consist of several databases. In other systems, they are referred to as tables.
A table is a database structure that consists of columns containing lines data. Typically, tables are created to contain related information. Multiple tables can be created within the same database.
Each column represents an attribute or set of attributes of objects, such as employee identification numbers, height, car color, etc. The term often used to refer to a column is field indicating the name, for example "in the Name field". A row field is the minimum element of a table. Each column in a table has a specific name, data type, and size. Column names must be unique within the table.
Each row (or record) represents a collection of attributes of a specific object, for example, a row may contain the employee’s identification number, his salary, his year of birth, etc. Table rows do not have names. To access a specific row, the user must specify some attribute (or set of attributes) that uniquely identifies it.
One of the most important operations that are performed when working with data is retrieving information stored in the database. To do this, the user must execute request(query).
Now let's look at the basic types of database queries that focus on manipulating data within a database. For our purposes, all examples are provided in standard SQL to suit any environment.
Types of Data Queries
There are four main types of data queries in SQL, which are referred to as data manipulation language(Data Manipulation Language or DML):
- SELECT– select rows from tables;
- INSERT– add rows to the table;
- UPDATE– change rows in the table;
- DELETE– delete rows in the table;
Each of these queries has different operators and functions that are used to perform some actions on the data. The SELECT query has the largest number of options. There are also additional query types used in conjunction with SELECT, such as JOIN and UNION. But for now, we will focus only on basic queries.
Using a SELECT Query to Select the Data You Need
To retrieve information stored in the database, a SELECT query is used. The basic effect of this query is limited to a single table, although there are designs that allow you to select from multiple tables at the same time. In order to get all rows of data for specific columns, a query like this is used:
SELECT column1, column2 FROM table_name;
You can also get all columns from a table using the * wildcard:
SELECT * FROM table_name;
This can be useful when you are going to select data with a specific WHERE clause. The following query will return all columns from all rows where "column1" contains the value "3":
In addition to = (equals), there are the following conditional operators:
Additionally, you can use the BITWEEN and LIKE conditions to compare with the WHERE condition, as well as combinations of the AND and OR operators.
Which, translated into Russian, means: select all rows from the table_name table, where the value of the age column is greater than or equal to 18, and the value of the LastName column is in the alphabetical range from Ivanov to Sidorov inclusive, or the value of the Company column is Motorola.
Using an INSERT query to insert new data
The INSERT query is used to creation new row of data. To update existing data or empty row fields, you must use an UPDATE query.
Example INSERT query syntax:
INSERT INTO table_name (column1, column2, column3) VALUES ("data1", "data2", "data3");
If you are going to insert all the values in the order in which the table columns appear, you can omit the column names, although this is preferable for readability. Also, if you list columns, you don't have to list them in the order they appear in the database, as long as the values you enter match that order. You should not list columns that do not contain information.
Already existing information in the database is changed in a very similar way.
UPDATE Query and WHERE Condition
UPDATE is used to change existing values or free a field in a row, so the new values must match the existing data type and provide acceptable values. If you do not want to change the values in all rows, then you need to use the WHERE clause.
You can use WHERE on any column, including the one you want to change. This is used when it is necessary to replace one specific value with another.
Be careful! DELETE query deletes entire rows
A DELETE query completely deletes a row from the database. If you want to delete one single field, then you need to use an UPDATE request and set this field to a value that will be analogous to NULL in your program. Be careful to limit your DELETE query to a WHERE clause, otherwise you may lose the entire contents of the table.
Once a row has been deleted from your database, it cannot be restored, so it is advisable to have a column named "IsActive", or something like that, which you can change to null, which will indicate that the view of the data from that row is locked.
Now you know the basics of SQL queries
SQL is a database language and we have covered the most important and basic commands used in data queries. There are a lot of core concepts that weren't covered (SUM and COUNT, for example), but the few commands we managed to list above should encourage you to get active and dig deeper into the wonderful query language called SQL.
Today, SQL courses “for dummies” are becoming increasingly popular. This can be explained very simply, because in the modern world you can increasingly find so-called “dynamic” web services. They are distinguished by a fairly flexible shell and are based on All novice programmers who decide to dedicate websites, first of all enroll in SQL courses “for dummies”.
Why learn this language?
First of all, SQL is taught in order to further create a wide variety of applications for one of the most popular blog engines today - WordPress. After completing a few simple lessons, you will be able to create queries of any complexity, which only confirms the simplicity of this language.
What is SQL?
Or a structured query language, was created for one single purpose: to determine, provide access to and process them in fairly short periods of time. If you know the SQL meaning, then you will understand that this server is classified as a so-called “non-procedural” language. That is, its capabilities only include a description of any components or results that you want to see in the future on the site. But when does not indicate exactly what results are going to be obtained. Each new request in this language is like an additional “superstructure”. It is in the order in which they are entered into the database that the queries will be executed.
What procedures can be performed using this language?
Despite its simplicity, the SQL database allows you to create a wide variety of queries. So what can you do if you learn this important programming language?
- create a wide variety of tables;
- receive, store and modify received data;
- change table structures at your discretion;
- combine the received information into single blocks;
- calculate the received data;
- ensure complete protection of information.
What commands are the most popular in this language?
If you decide to take a SQL for Dummies course, then you will receive detailed information about the commands that are used in creating queries using it. The most common today are:
- DDL is a command that defines data. It is used to create, modify and delete a wide variety of objects in the database.
- DCL is a command that manipulates data. It is used to provide different users with access to information in the database, as well as to use tables or views.
- TCL is a team that manages a variety of transactions. Its main purpose is to determine the progress of a transaction.
- DML - manipulates the received data. Its task is to allow the user to move various information from the database or enter it there.
Types of privileges that exist in this server
Privileges refer to those actions that a particular user can perform in accordance with his status. The most minimal, of course, is a regular login. Of course, privileges may change over time. Old ones will be deleted and new ones will be added. Today, all those who take SQL Server “for dummies” courses know that there are several types of permitted actions:
- Object type - the user is allowed to execute any command only in relation to a specific object that is located in the database. At the same time, privileges differ for different objects. They are also tied not only to a particular user, but also to tables. If someone, using his capabilities, created a table, then he is considered its owner. Therefore, he has the right to assign new privileges to other users related to the information in it.
- The system type is the so-called data copyright. Users who have received such privileges can create various objects in the database.
History of SQL
This language was created by IBM Research Laboratory in 1970. At that time, its name was slightly different (SEQUEL), but after a few years of use it was changed, shortening it a little. Despite this, even today many world-famous programming experts still pronounce the name the old-fashioned way. SQL was created with one single purpose - to invent a language that would be so simple that even ordinary Internet users could learn it without any problems. An interesting fact is that at that time SQL was not the only such language. In California, another group of specialists developed a similar Ingres, but it never became widespread. Before 1980, there were several variations of SQL that were only slightly different from each other. To prevent confusion, a standard version was created in 1983, which is still popular today. SQL courses "for dummies" allow you to learn much more about the service and fully study it in a few weeks.
Structured Query Language or SQL is a declarative programming language for use in quasi-relational databases. Many of the original features of SQL were taken from tuple calculus, but recent extensions to SQL include more and more relational algebra.
SQL was originally created by IBM, but many vendors have developed their own dialects. It was adopted as a standard by the American National Standards Institute (ANSI) in 1986 and ISO in 1987. In the SQL programming language standard, ANSI stated that the official pronunciation of SQL is "es q el". However, many database specialists used the "slang" pronunciation "Sequel", which reflects the original name of the language, Sequel, which was later changed due to a trademark and name conflict with IBM. Programming for beginners.
SQL programming language was revised in 1992 and this version is known as SQL-92. 1999 was then revised again to become SQL:1999 (AKA SQL3). Programming for dummies. SQL 1999 supports objects that were not previously supported in other versions, but as of late 2001, only a few database management systems supported SQL implementations: SQL 1999.
SQL, although defined as ANSI and ISO, has many variations and extensions, most of which have their own characteristics, such as Oracle Corporation's "PL/SQL" implementation or Sybase and Microsoft's implementation called "Transact-SQL", which can be confusing to those familiar with programming basics. It is also not uncommon for commercial implementations to omit support for the main features of the standard, such data types as date and time, preferring some of their own variant. As a result, unlike ANSI C or ANSI Fortran which can usually be ported from platform to platform without major structural changes, SQL programming language queries can rarely be ported between different database systems without significant modifications. Most people in the database industry believe that this lack of compatibility is intentional, in order to provide each developer with their own database management system and tie the buyer to a specific database.
As its name suggests, the SQL programming language is designed for specific, limited purposes - querying data contained in a relational database. As such, it is a set of programming language instructions for creating data samples, rather than a procedural language such as C or BASIC, which are designed to solve a much wider range of problems. Language extensions such as "PL/SQL" are designed to solve this limitation by adding procedural elements to SQL while maintaining the benefits of SQL. Another approach is to embed procedural programming language commands into SQL queries and interact with the database. For example, Oracle and others support Java in the database, while PostgreSQL allows functions to be written in Perl, Tcl, or C.
One joke about SQL: "SQL is neither structured nor a language." The point of the joke is that SQL is not a Turing language. .
| C1 | C2 |
|---|---|
| 1 | a |
| 2 | b |
| C1 | C2 |
|---|---|
| 1 | a |
| 2 | b |
| C1 |
|---|
| 1 |
| 2 |
| C1 | C2 |
|---|---|
| 1 | a |
| 2 | b |
| C1 | C2 |
|---|---|
| 1 | a |
Given a table T, the Select * from T query will display all the elements of all rows in the table.
From the same table, the query Select C1 from T will display the elements from column C1 of all rows in the table.
From the same table, the query Select * from T where C1=1 will display all elements of all rows where the value of column C1 is "1".
SQL keywords
SQL words are divided into a number of groups.
The first one is Data Manipulation Language or DML(data management language). DML is a subset of the language used to query databases and add, update, and delete data.
- SELECT is one of the most commonly used DML commands and allows the user to specify a query as a set description of the desired result. The query does not specify how the results should be arranged - translating the query into a form that can be executed in the database is the job of the database system, more specifically the query optimizer.
- INSERT is used to add rows (formal set) to an existing table.
- UPDATE is used to change data values in an existing table row.
- DELETE specifies existing rows that will be deleted from the table.
Three other keywords can be said to fall into the DML group:
- BEGIN WORK (or START TRANSACTION, depending on the SQL dialect) can be used to mark the start of a database transaction that will either complete all or not execute at all.
- COMMIT states that all data changes made after operations are performed are saved.
- ROLLBACK specifies that all data changes after the last commit or rollback must be destroyed, up to the point that was recorded in the database as a “rollback”.
COMMIT and ROLLBACK are used in areas such as transaction control and locking. Both instructions complete all current transactions (sets of operations on the database) and remove all locks on changing data in tables. The presence or absence of a BEGIN WORK or similar statement depends on the particular SQL implementation.
The second group of keywords belongs to the group Data Definition Language or DDL (Data Definition Language). DDL allows the user to define new tables and their associated elements. Most commercial SQL databases have their own DDL extensions that allow control over non-standard, but usually vital, elements of a particular system.
The main points of the DDL are the create and delete commands.
- CREATE specifies the objects (such as tables) that will be created in the database.
- DROP specifies which existing objects in the database will be deleted, usually permanently.
- Some database systems also support the ALTER command, which allows the user to modify an existing object in various ways, such as adding columns to an existing table.
The third group of SQL keywords is Data Control Language or DCL (Data Control Language). DCL is responsible for data access rights and allows the user to control who has access to view or manipulate data in the database. There are two main keywords here:
- GRANT - allows the user to perform operations
- REVOKE - Removes or restricts the user's ability to perform operations.
Database systems using SQL
- InterBase
- MySQL
- Oracle
- PostgreSQL
- SQL Server
How to become a website development professional and start making money? Inexpensive video courses with an introductory introduction.
