The basics of the SQL query language are the SELECT statement. SQL triggers on the example of a SQLite database. Simplified syntax of the SELECT statement

Requests are written without escaping quotes, since MySQL, MS SQL and PostGree they are different.

SQL query: getting specified (required) fields from a table

SELECT id, country_title, count_people FROM table_name

We get a list of records: ALL countries and their population. The names of the required fields are separated by commas.

SELECT * FROM table_name

* denotes all fields. That is, there will be impressions EVERYTHING data fields.

SQL query: output records from a table excluding duplicates

SELECT DISTINCT country_title FROM table_name

We get a list of records: the countries where our users are located. There can be many users from one country. In this case, this is your request.

SQL query: displaying records from a table according to a specified condition

SELECT id, country_title, city_title FROM table_name WHERE count_people> 100000000

We get a list of records: countries where the number of people is more than 100,000,000.

SQL query: displaying records from a table with ordering

SELECT id, city_title FROM table_name ORDER BY city_title

We get a list of records: cities in alphabetical order... At the beginning of A, at the end of I.

SELECT id, city_title FROM table_name ORDER BY city_title DESC

We get a list of records: cities in reverse ( DESC) ok. At the beginning I, at the end A.

SQL query: counting the number of records

SELECT COUNT (*) FROM table_name

We get the number (number) of records in the table. V in this case NO list of records.

SQL query: output the required range of records

SELECT * FROM table_name LIMIT 2, 3

We get 2 (second) and 3 (third) records from the table. The request is useful when creating navigation on WEB pages.

SQL queries with conditions

Output of records from a table according to a given condition using logical operators.

SQL query: AND construction

SELECT id, city_title FROM table_name WHERE country = "Russia" AND oil = 1

We get a list of records: cities from Russia AND have access to oil. When the operator is used AND, then both conditions must match.

SQL query: OR construct

SELECT id, city_title FROM table_name WHERE country = "Russia" OR country = "USA"

We get a list of records: all cities from Russia OR USA. When the operator is used OR, then at least one condition must match.

SQL query: AND NOT construct

SELECT id, user_login FROM table_name WHERE country = "Russia" AND NOT count_comments<7

We get a list of records: all users from Russia AND who made NOT LESS 7 comments.

SQL query: IN (B) construct

SELECT id, user_login FROM table_name WHERE country IN ("Russia", "Bulgaria", "China")

We get a list of records: all users who live in ( IN) (Russia, or Bulgaria, or China)

SQL query: NOT IN construction

SELECT id, user_login FROM table_name WHERE country NOT IN ("Russia", "China")

We get a list of records: all users who do not live in ( NOT IN) (Russia or China).

SQL query: IS NULL construct (empty or NOT empty values)

SELECT id, user_login FROM table_name WHERE status IS NULL

We get a list of records: all users where status is not defined. NULL is a separate topic and is therefore checked separately.

SELECT id, user_login FROM table_name WHERE state IS NOT NULL

We get a list of records: all users where status is defined (NOT ZERO).

SQL query: LIKE construct

SELECT id, user_login FROM table_name WHERE surname LIKE "Ivan%"

We get a list of records: users whose last name begins with the combination "Ivan". The% sign means ANY number of ANY characters. To find the% sign, you need to use the escaping "Ivan \%".

SQL query: BETWEEN construct

SELECT id, user_login FROM table_name WHERE salary BETWEEN 25000 AND 50000

We get a list of records: users who receive salaries from 25,000 to 50,000 inclusive.

There are a LOT of logical operators, so study the SQL server documentation in detail.

Complex SQL queries

SQL query: combining multiple queries

(SELECT id, user_login FROM table_name1) UNION (SELECT id, user_login FROM table_name2)

We get a list of records: users who are registered in the system, as well as those users who are registered on the forum separately. Multiple queries can be combined with the UNION operator. UNION acts like SELECT DISTINCT, that is, it discards duplicate values. To get absolutely all records, you need to use the UNION ALL operator.

SQL query: counting field values ​​MAX, MIN, SUM, AVG, COUNT

Output of one, the maximum value of the counter in the table:

SELECT MAX (counter) FROM table_name

Output of one, the minimum value of the counter in the table:

SELECT MIN (counter) FROM table_name

Displaying the sum of all counter values ​​in the table:

SELECT SUM (counter) FROM table_name

Displaying the average value of the counter in the table:

SELECT AVG (counter) FROM table_name

Displaying the number of counters in the table:

SELECT COUNT (counter) FROM table_name

Display of the number of counters in workshop No. 1, in the table:

SELECT COUNT (counter) FROM table_name WHERE office = "Shop # 1"

These are the most popular commands. It is recommended, where possible, to use SQL queries of this kind for calculating, since no programming environment can compare in data processing speed than the SQL server itself when processing its own data.

SQL query: grouping records

SELECT continent, SUM (country_area) FROM country GROUP BY continent

We get a list of records: with the name of the continent and with the sum of the areas of all their countries. That is, if there is a directory of countries where each country has its area recorded, then using the GROUP BY clause, you can find out the size of each continent (based on the grouping by continent).

SQL query: using multiple tables via alias

SELECT o.order_no, o.amount_paid, c.company FROM orders AS o, customer AS with WHERE o.custno = c.custno AND c.city = "Tyumen"

We get a list of records: orders from customers who live only in Tyumen.

In fact, with a properly designed database of this type, the query is the most frequent, therefore a special operator was introduced in MySQL, which works many times faster than the code written above.

SELECT o.order_no, o.amount_paid, z.company FROM orders AS o LEFT JOIN customer AS z ON (z.custno = o.custno)

Nested subqueries

SELECT * FROM table_name WHERE salary = (SELECT MAX (salary) FROM employee)

We get one record: information about the user with the maximum salary.

Attention! Nested subqueries are one of the bottlenecks in SQL servers. Together with their flexibility and power, they also significantly increase the load on the server. Which leads to a catastrophic slowdown in the work of other users. Cases of recursive calls with nested queries are very common. Therefore, I strongly recommend NOT to use nested queries, but to break them into smaller ones. Or use the above LEFT JOIN combination. In addition to this type of request, requests are an increased hotbed of security breaches. If you decide to use nested subqueries, then you need to design them very carefully and make the initial runs on copies of databases (test databases).

SQL queries changing data

SQL query: INSERT

Instructions INSERT allow you to insert records into a table. In simple words, create a line with data in the table.

Option number 1. The instruction is often used:

INSERT INTO table_name (id, user_login) VALUES (1, "ivanov"), (2, "petrov")

In the table " table_name"2 (two) users will be inserted at once.

Option number 2. It is more convenient to use the style:

INSERT table_name SET id = 1, user_login = "ivanov"; INSERT table_name SET id = 2, user_login = "petrov";

This has its advantages and disadvantages.

Main disadvantages:

• Many small SQL queries run slightly slower than one large SQL query, but other queries will be queued for service. That is, if a large SQL query is executed for 30 minutes, then during all this time the rest of the queries will smoke bamboo and wait for their turn.
• The request turns out to be more massive than the previous version.

Main advantages:

• During small SQL queries, other SQL queries are not blocked.
• Ease of reading.
• Flexibility. In this option, you can not follow the structure, but add only the necessary data.
• When forming archives in this way, you can easily copy one line and run it through the command line (console), thereby not restoring the whole ARCHIVE.
• The writing style is similar to the UPDATE statement, which makes it easier to remember.

SQL query: UPDATE

UPDATE table_name SET user_login = "ivanov", user_surname = "Ivanov" WHERE id = 1

In the table " table_name"In the record with id = 1, the values ​​of the user_login and user_surname fields will be changed to the specified values.

SQL query: DELETE

DELETE FROM table_name WHERE id = 3

The record with id number 3 will be deleted in the table_name table.

1. It is recommended to write all field names in small letters and, if necessary, separate them with a forced space "_" for compatibility with different programming languages ​​such as Delphi, Perl, Python and Ruby.
2. Write SQL commands in CAPITAL letters for readability. Always remember that other people can read the code after you, and most likely you yourself after N amount of time.
3. Name fields from the beginning of the noun, and then the action. For example: city_status, user_login, user_name.
4. Try to avoid fallback words in different languages ​​that can cause problems in SQL, PHP or Perl, such as (name, count, link). For example: link can be used in MS SQL, but is reserved in MySQL.

This material is a short reference for everyday work and does not claim to be a super mega authoritative source, which is the primary source of SQL queries for a particular database.

Structured Query Language (SQL) is a standard language for accessing databases such as SQL Server, Oracle, MySQL, Sybase, and Access. Knowledge of SQL is essential for anyone who would like to store and retrieve data from a database.

What is SQL?

• SQL - Structured Query Language (SQL)
• SQL allows you to access the DB
• SQL is a computer language based on the ANSI standard
• SQL can send queries to the database
• SQL can fetch data from DB
• SQL can make new records to the database
• SQL can delete records from the database
• SQL can update existing records in the database
• SQL is easy to learn

SQL is standard, but ...

SQL is a computer language based on the ANSI standard for accessing and manipulating databases. SQL commands are used to retrieve and update records in the database. SQL works with such database management systems (DBMS) as MS Access, DB2, Informix, MS SQL Server, Oracle, Sybase, etc.

Unfortunately, there are many versions of the SQL language, but in order to comply with ANSI standards, they must support basic keywords (such as SELECT - select, UPDATE - update, DELETE - destroy, INSERT - insert, WHERE - where and others).

The note: Many DBMSs have their own commands in addition to existing SQL standards.

SQL data tables

DBs most often contain one or several tables. Each cell is identified by a name (for example, "Friends" or "Orders"). Tables contain records with data. Below is a table called "Persons":

This table has three rows (people) and four columns (last name, first name, address, and city).

SQL query

With the help of SQL, we can access the database and get the result. For example, a request like this:

SELECT LastName FROM Persons

will give us the following output:

The note: In some DBMS it is necessary to put a semicolon after the command. We will not use semicolons in our examples.

SQL Data Manipulation Language (DML)

SQL is for executing queries. In addition, SQL includes syntax for updating, inserting, and destroying data. This syntax, together with the update commands, forms the Data Management Language (DML):

• SELECT- retrieves data from a database table
• UPDATE- updates data in the database table
• DELETE- destroys data in the database table
• INSERT INTO- inserts new data into the database table

SQL Data Definition Language (DDL)

DDL is the part of SQL that manages the creation and deletion of tables in the database.In addition, using DDL, we can assign indexes (keywords), establish relationships between tables and impose restrictions on database tables.

The most important DDL commands are the following:

• CREATE TABLE- creating a new table
• ALTER TABLE- modifying an existing table
• DROP TABLE- deleting a table
• CREATE INDEX- creating an index (keyword to make it easier to find)
• DROP INDEX- deleting an index

Hello dear reader! With this post, I will open a new section on my blog, in which I will publish posts and not even just posts, but video posts. The rubric will be called SQL and Relational Databases and will publish video tutorials on SQL technology and relational database theory, of course, in video format. I myself do not like the word course, because I believe that courses are taught at universities, but the realities of Runet are such that this word is used very often, and I will sometimes use it too.

My video course is a set of video screencasts on the topic of SQL and databases, the SQLite library was used as a DBMS. Screencasts are divided into topics, topics, in turn, are divided into parts, each part is a separate video lesson lasting 5-15 minutes, in which we will deal with database theory or SQL commands and queries. But I will not pull the cat by the tail and will immediately give you a playlist link on YouTube: SQL and relational databases... The videos in the playlist are arranged in the order in which I would recommend watching them. And do not forget to subscribe to my channel, it will be even more interesting and more!

Who will benefit from the video tutorials from the SQL course and relational databases for beginners?

These video tutorials will be useful for novice web developers and SQL developers. To study them, in principle, you do not need any specific knowledge, it is enough to be a confident computer user, be able to type on the keyboard and it will be absolutely cool if you have written any program in any programming language at least once in your life, even if it is Helloe World in BASIC ...

I note that these video tutorials will be useful not only for beginners of SQL, but also for more advanced users who have a certain set of knowledge on SQL and databases, but this set of knowledge is not systematized and does not allow you to go to independent work. Although we start with simple things that I try to tell in an accessible and intelligible way, we end up with complex SQL queries, although who am I scaring? :)) There are no complex SQL queries, there are large queries and queries that consume a lot of resources.

How are these video tutorials built?

For the basis of these video lessons, publications from my blog from the heading were taken, these video lessons have a similar structure, but still there are slight differences. Below you will find the topics that this course is broken down into and a brief description of what I am trying to cover.

SQLite basics and features of this DBMS?

In the first topic we will talk about the features of the SQLite DBMS, see where this library is used and for what purposes, and also try to install and configure SQLite on a computer running the Windows 10 operating system. The publications from the first topic were taken as the basis:.

1. Choosing a DBMS and talking about programs for working and administering databases.
2. SQLite is a program for creating a database using the SQL language.
3. Installing a SQLite application for working with databases. Setting up access to the database.
4. SQLite database management system or where this database is used.
5. Free graphical programs for working with databases (database managers).
6. Database management and administration using DBeaver.

Trying to write SQL queries and work with a SQLite database

In this topic, we will try to write several SQL queries, get acquainted with the syntax of the SQL language implemented in SQLite (in principle, the SQL syntax in different DBMS is very, very similar) and get acquainted with useful system commands that are in SQLite that will help us work with databases data..html

Relational Database Theory

One of the basic topics of the entire video course. I will refer to and rely on this topic constantly in all subsequent ones. Here we will get acquainted with the basics of databases and learn how to design the architecture of our databases, we will get acquainted with such seemingly complex terms as normalization, data redundancy, anomalies and others, and give them a human explanation .. html

SQL Query Language

Another fundamental topic of my video tutorials on SQL and relational databases. Here we are introduced to the SQL language, its basic concepts, structure and concept. We also understand the terminology used by SQL developers and draw a parallel between the SQL language and the theory of relational databases, find differences and similarities, so to speak ... html

Data types in SQLite

Throughout the video course, I use the SQLite DBMS, which is a special DBMS with its own typing features, I also want to note that the DBMS actually manages data, so the topic of data typing is, in principle, important and can be difficult for a beginner SQL developer, at least for me, when I got acquainted with databases, the most difficult moment was typing, because I had to memorize a lot .. html

SQL data definition commands (DML commands)

Here we will begin to work closely with the SQL language and get acquainted with the SQL data definition commands, which allow us to work with database objects: create them, delete and modify them. Database objects include: the database itself, a table in the database, VIEW, indexes, triggers, etc. Video tutorials on this topic are devoted to the syntax of SQL commands CREATE, DROP, ALTER and their use .. html

SQL Data Manipulation Commands (DML Commands)

This video tutorial topic will introduce you to the syntax (writing rules) of SQL data manipulation commands (DML commands), these commands include: INSERT, DELETE, UPDATE, SELECT. Accordingly, the commands allow you to: insert and add rows to tables, delete rows from tables, modify data in tables and make data selection ... html

SQL commands for transaction management

This section of the video course SQL and relational databases is devoted to the rules for writing commands that allow you to manage transactions, I will not open the spoiler, just say that these commands are cleverly called TCL, and it is also worth adding that we will deal with transactions in more detail in one of the latest topics .. html

Working with database tables

This video tutorial topic gives a deeper knowledge and understanding of how SQL allows you to work with database tables, what features should be taken into account and what we can do with a database table using SQL..html

Ensuring data integrity

This topic of my video tutorials SQL well demonstrates the application of relational database theory in practice in the SQL language. Here we will learn how to set various restrictions in order to secure your data! We will also look at the use of keys or key attributes in practice and learn how to make different types of relationships between tables .. html

SQL triggers using an example of a SQLite database

SQL table indexes using SQLite databases as an example

2 Comments on Video course and free lessons on SQL and relational databases for beginners and not only

Hello! Where is the video ??? Where are the video tutorials then ??? Rummaged the entire site, all the links, the entire sitemap ...

Please enable JavaScript to view the

Today, SQL courses for dummies are becoming more and more popular. This can be explained very simply, because in the modern world more and more so-called "dynamic" web services can be found. They differ in a rather flexible shell and are based on All novice programmers who decided to devote themselves to creating sites, 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 can already create queries of any complexity, which only confirms the simplicity of this language.

What is SQL?

Or structured query language, was created with one single purpose: to define and provide access to them and process them in fairly short periods of time. If you know the SQL value, then you will understand that this server belongs to the so-called "non-procedural" languages. That is, its capabilities include only a description of any components or results that you want to see in the future on the site. But it doesn’t indicate exactly what results it’s going to get. Each new request in this language is, as it were, an additional "add-on". It is in the order in which they are entered in 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 fairly large 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 the 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 are the most popular commands in this language?

If you decide to attend the SQL Dummies course, then you will receive detailed information about the commands that are used in creating queries with it. The most common today are:

1. DDL is a command that defines data. It is used to create, modify and delete a wide variety of objects in the database.
2. DCL is a command that manages data. It is used to provide different users with access to information in the database, as well as to use tables or views.
3. TCL is a team that manages various kinds of transactions. Its main purpose is to determine the progress of a transaction.
4. DML - manipulates the received data. Its task is to allow the user to move various information from the database or enter it there.

The types of privileges that exist on this server

Privileges are those actions that a particular user can perform in accordance with their status. The most minimal, of course, is the regular login. Privileges can change over time, of course. The old ones will be removed, and the new ones will be added. Today, all those who take the SQL Server "For Dummies" courses know that there are several types of allowed actions:

1. Object type - the user is allowed to execute any command only in relation to a specific object that is in the database. At the same time, the privileges differ for different objects. They also bind not only to a particular user, but also to tables. If someone, using their 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.
2. The system type is the so-called data copyright. Users who have received such privileges can create various objects in the database.

History of the creation of SQL

This language was created by the IBM research laboratory in 1970. At that time its name was somewhat different (SEQUEL), but after a few years of use it was changed, slightly shortened. Despite this, even today, many well-known world experts in the field of programming still pronounce the name in the old fashioned way. SQL was created with one single goal - 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 experts developed a similar Ingres, but it never became widespread. Prior to 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 Dummies courses allow you to learn a lot more about the service and fully master it in a few weeks.