Any table has its logical beginning and its logical end. Also, any table consists of data that is often, and in the case of databases to a greater extent, scattered. As noted in the first entry regarding SQL and databases, data can be added regularly throughout the entire life cycle of databases. During this time, more than one dozen data can be entered. In SQL, as in everything else and in all computer programs, you can sort the data. This is done by command ORDER BY. There are two arguments for this command.

• ASC sets the order in ascending order (from smallest to largest)
• DESC - descending (largest to smallest)

In general, the default ordering in SQL is ascending (from smallest to largest).
Let's try to sort the table of debtors by last name - column Sname - in descending order

Debtors

Num	month	year	Sname	City	Address	Penalty	Debt
0001	July	2012	Ivanov	Stavropol	Stavropolskaya, 1	4891.00	50000
0002	December	2019	Kononov	Tatar	Zagorodnaya, 254	19565.00	684068
0003	May	2013	Yamshin	Mikhailovsk	Rural, 48	9868.00	165840
0004	August	2012	preny	Stavropol	Central, 16	4659.00	46580
...	...	...	...	...	...	...	...
9564	March	2015	Uliev	Demino	International, 156	6846.00	435089
9565	October	2012	Pavlova	Stavropol	Station, 37	685.00	68059
9566	January	2012	Uryupa	Mikhailovsk	Fontannaya, 19	1235.00	51238
9567	November	2017	Valetov	Tatar	Exit, 65	13698.00	789654

Execute the request
SELECT *
FROM Debtors
ORDER BY Sname DESC ;

Debtors

Num	month	year	Sname	City	Address	Penalty	Debt
0003	May	2013	Yamshin	Mikhailovsk	Rural, 48	9868.00	165840
9566	January	2012	Uryupa	Mikhailovsk	Fontannaya, 19	1235.00	51238
9564	March	2015	Uliev	Demino	International, 156	6846.00	435089
0004	August	2012	preny	Stavropol	Central, 16	4659.00	46580
9565	October	2012	Pavlova	Stavropol	Station, 37	685.00	68059
0002	December	2019	Kononov	Tatar	Zagorodnaya, 254	19565.00	684068
0001	July	2012	Ivanov	Stavropol	Stavropolskaya, 1	4891.00	50000
9567	November	2017	Valetov	Tatar	Exit, 65	13698.00	789654

Ordering can also be performed in grouped data using the command . But in this case, the ORDER BY command in the query should be the last one, that is
SELECT *
FROM Debtors
GROUP BY Sname
ORDER BY Sname DESC ;

If there are cells with empty values ​​(NULL) in the ordered fields, then, depending on the programs working with the database, such cells can be located either at the end of the list or at the beginning.
There is another trick to performing data ordering. Consider a query
SELECT Num, Year, Sname
FROM Debtors
ORDER BY 2 DESC ;
Here, two means the ordinal number of the column from the string "SELECT Num, Year, Sname" and it is this column that will be used to rank in descending order in the end

Debtors

As you can see, the values ​​are sorted by the Year column, with the latest date at the beginning of the list.
So, ordering in SQL has the same ranking principles as in any computer program. It all depends on the database administrator and his needs for this or that information that he wants to receive.

SQL - SELECT Statement

The name SQL (Structured Query Language) reflects the fact that queries are the most commonly used element of SQL. A query is a statement that sends a command to a Database Management System (DBMS) to manipulate or display certain information. All data selection queries in SQL are constructed using the SELECT statement. It allows you to perform quite complex checks and data processing.

A query can display data from a specific column or from all columns in a table. To create the simplest SELECT query, you must specify the column name and the table name.

SELECT statement syntax

SELECT column_list FROM table_name

SELECT A keyword that tells the database that the statement is a query. All queries begin with this word, followed by a space.

Column_list List of table columns that are selected by the query. Columns not specified in the statement will not be included in the result. If you need to display the data of all columns, you can use the abbreviated notation. An asterisk (*) means the complete list of columns.

FROM table_name Keyword that must be present in every request. It is followed by a space separated by the name of the table that is the data source.

The code in brackets is optional in a SELECT statement. It is necessary for a more precise definition of the request.

It is also necessary to say that SQL code is case insensitive. This means that the SELECT entry can be written as select. The DBMS will not distinguish between these two entries, but it is advised to write all SQL statements in capital letters so that it can be easily distinguished from other code.

Let's take a look at the Salespeople table from Martin Graber's classic SQL tutorial for examples.
Here is the MySQL code to create a test table:

CREATE TABLE `salespeople` (`snum` INTEGER(11) NOT NULL AUTO_INCREMENT, `sname` VARCHAR(20) COLLATE cp1251_general_ci DEFAULT NULL, `city` VARCHAR(20) COLLATE cp1251_general_ci DEFAULT NULL, `comm` DOUBLE(15,3) DEFAULT NULL, PRIMARY KEY (`snum`))ENGINE=InnoDB CHARACTER SET "cp1251" COLLATE "cp1251_general_ci" COMMENT="InnoDB free: 5120 kB"; INSERT INTO `salespeople` (`snum`, `sname`, `city`, `comm`) VALUES (1001,"Peel","London",0.120), (1002,"Serres","San Jose",0.130 ), (1003,"Axelrod","New York",0.100), (1004,"Motika","London",0.110), (1007,"Rifkin","Barcelona",0.150); COMMIT;

The table looks like this:

SELECT Statement Example

1. It is necessary to display a list of sellers, and display their names (sname)

SELECT sname FROM Salespeople

Result:

Here, after the SELECT statement, the columns to be displayed are listed. Column names are separated by commas.

3. It is necessary to display the entire table

To do this, you can use different syntax for writing queries. We list each column after the SELECT statement:

SELECT snum , sname , city , comm FROM Salespeople

Or you can achieve the same result using the shorthand notation:

SELECT * FROM Salespeople Result:

snum	sname	city	comm
1001	Peel	London	0,12
1002	Serres	San Jose	0,13
1003	Axelrod	New York	0,1
1004	Motika	London	0,11
1007	Rifkin	Barcelona	0,15

I also want to give you a little advice. For the convenience of debugging queries, some people write a list of columns that need to be displayed on a separate line. This makes it easier to comment out the code. The syntax for commenting code in SQL is /* commented code */ . Example: SELECT snum ,sname , city , comm FROM Salespeople

This will display 4 columns, however, with such a record, you can easily comment out an unnecessary column. For example like this:

SELECT /* snum , */ sname , city , comm FROM Salespeople

Now the snum column will not be displayed because it is commented out. Very fast and convenient. How to write SQL code is up to you, of course, but knowing such things is sometimes useful.

Using Expressions in a SELECT Statement

Many DBMS provide special features for handling query results. The set of such facilities in different DBMS is different, but there are some standard features, such as expressions. For example, you might want to perform simple mathematical operations on the data to present it in a more convenient way, or insert additional text into the result of a query. SQL allows you to place scalar expressions and constants among selected columns that can complement or replace columns in SELECT clauses. Consider an example.

4. It is necessary to display the commissions (comm) of sellers as percentages, not decimal numbers

SELECT snum, sname, city, comm * 100 FROM Salespeople

Result:

snum	sname	city	comm
1001	Peel	London	12
1002	Serres	San Jose	13
1003	Axelrod	New York	10
1004	Motika	London	11
1007	Rifkin	Barcelona	15

In the last column, all received data is multiplied by 100, and displayed as a percentage.

This column does not have a name, because it does not contain modified data and therefore is named at the discretion of the DBMS (for example, MySQL names the column comm * 100, in M. Grabber's examples, the column has the name 4, i.e. its number).

In such cases, it is convenient to use column naming. For example, you can name the last column percent. To do this, after the column name, you must specify the keyword AS and then the name of the column in the resulting page.

SELECT snum, sname, city, comm * 100 AS "percent" FROM Salespeople Result:

snum	sname	city	percent
1001	Peel	London	12
1002	Serres	San Jose	13
1003	Axelrod	New York	10
1004	Motika	London	11
1007	Rifkin	Barcelona	15

As a result of the query, the last column is named the string "percent", which makes it easier to understand.

Since the number is displayed as a percentage, it would be nice to indicate this in the result. SQL capability comes to the rescue add text to result. It looks like this:

SELECT snum, sname, city, comm * 100 AS percent , "%" FROM Salespeople Result:

snum	sname	city	percent	%
1001	Peel	London	12	%
1002	Serres	San Jose	13	%
1003	Axelrod	New York	10	%
1004	Motika	London	11	%
1007	Rifkin	Barcelona	15	%

It can be seen that after the output of the row from the database, a new column appeared, filled with a percent sign (%).

If you are not satisfied with the output of data and complementary text in different columns, then you can use the special functions of your DBMS to merge into one column.

MySQL uses the function CONCAT . Here is its definition from the handbook:

CONCAT(str1,str2,...)
Returns a string resulting from the concatenation of the arguments. If at least one of the arguments is NULL, NULL is returned. Can take more than 2 arguments. The numeric argument is converted to its equivalent string form.

Example: SELECT snum, sname, city , CONCAT(comm * 100, "%") AS "persent" FROM salespeople Result:

snum	sname	city	persent
1001	Peel	London	12.000%
1002	Serres	San Jose	13.000%
1003	Axelrod	New York	10.000%
1004	Motika	London	11.000%
1007	Rifkin	Barcelona	15.000%

In this query, the CONCAT function takes 2 arguments, these are comm * 100 and the percent sign ("%"). We then name the column with AS.

It is important to know that using functions degrades performance. This is not the only negative, but a very important one. Therefore, if you can get away with standard SQL code, it is better not to use functions. They should be remembered only in extreme cases.

Exclusion of redundant data

It is often necessary to exclude duplicate values ​​from query results. The DISTINCT keyword is used for this. The opposite effect is obtained by using the word ALL, which is used by default (that is, it does not need to be specified).

5. It is necessary to display the cities (city), where there are sellers

Request without exception:

SELECT city FROM salespeople Result:

Duplicate London values ​​are excluded from the result, as required.

Sort result by values ​​by column

The SELECT statement outputs data in an arbitrary sequence. To sort the result by a particular column, SQL uses the ORDER BY operator (i.e., order by...). This statement allows you to change the order in which data is output. ORDER BY orders the query result according to the values ​​of one or more columns selected in the SELECT clause. In this case, for each column, you can set the sorting in ascending order - ascending (ASC) (this parameter is used by default) or in descending order - descending (DESC).

Sort the result by the sname column. After the ORDER BY operator, we indicate which column to sort by, then you need to specify the sort method

Example - sorting in ascending order:

SELECT * FROM Salespeople ORDER BY sname ASC

Result:

Example - sorting by multiple columns:

SELECT snum, sname, city FROM Salespeople ORDER BY sname DESC, city DESC Result:

snum	sname	city
1002	Serres	San Jose
1007	Rifkin	Barcelona
1001	Peel	London
1004	Motika	London
1003	Axelrod	New York

A few important notes:
- the column by which sorting occurs must be specified in SELECT (you can use *)
- the ORDER BY operator is always written at the end of the query

Welcome to my blog site. Today we will talk about sql queries for beginners. Some webmasters may have a question. Why learn sql? Can't get by?

It turns out that this will not be enough to create a professional Internet project. Sql is used to work with the database and create applications for WordPress. Let's take a look at how to use queries in more detail.

What it is

Sql is a structured query language. Created to determine the type of data, provide access to them and process information in short periods of time. It describes the components or some results that you want to see on the Internet project.

In simple terms, this programming language allows you to add, modify, search and display information in the database. The popularity of mysql is due to the fact that it is used to create dynamic Internet projects, which are based on a database. Therefore, to develop a functional blog, you need to learn this language.

What can do

The sql language allows:

• create tables;
• change receive and store different data;
• combine information into blocks;
• protect data;
• create requests in access.

Important! Having dealt with sql, you can write applications for WordPress of any complexity.

What structure

The database consists of tables that can be represented as an Excel file.

She has a name, columns and a row with some information. You can create such tables using sql queries.

What you need to know

Key Points When Learning Sql

As noted above, queries are used to process and enter new information into a database consisting of tables. Each line is a separate entry. So let's create a database. To do this, write the command:

Create database 'bazaname'

In quotation marks we write the name of the database in Latin. Try to think of a meaningful name for her. Don't create a database like "111", "www" and the like.

After creating the database, install:

SET NAMES ‘utf-8’

This is necessary so that the content on the site is displayed correctly.

Now we create a table:

CREATE TABLE 'bazaname' . 'table' (

id INT(8) NOT NULL AUTO_INCREMENT PRIMARY KEY,

log VARCHAR(10),

pass VARCHAR(10),

date DATE

In the second line, we have written three attributes. Let's see what they mean:

• The attribute NOT NULL means that the cell will not be empty (the field is required);
• The value of AUTO_INCREMENT is autocomplete;
• PRIMARY KEY is the primary key.

How to add information

To fill the fields of the created table with values, the INSERT statement is used. We write the following lines of code:

INSERT INTO 'table'

(login , pass , date) VALUES

('Vasa', '87654321', '2017-06-21 18:38:44');

In brackets we indicate the name of the columns, and in the next - the values.

Important! Follow the sequence of column names and values.

How to update information

For this, the UPDATE command is used. Let's see how to change the password for a specific user. We write the following lines of code:

UPDATE 'table' SET pass = '12345678' WHERE id = '1'

Now change the password to '12345678'. Changes occur in the line with "id"=1. If you do not write the WHERE command, all lines will change, not a specific one.

I recommend that you buy the book SQL for dummies ". With its help you will be able to professionally work with the database step by step. All information is built on the basis of the principle from simple to complex, and will be well received.

How to delete an entry

If you wrote something wrong, correct it with the DELETE command. Works the same as UPDATE. We write the following code:

DELETE FROM 'table' WHERE id = '1'

Information sampling

The SELECT command is used to retrieve values ​​from the database. We write the following code:

SELECT * FROM 'table' WHERE id = '1'

IN this example select all available fields in the table. This happens if you write an asterisk "*" in the command. If you need to choose some sample value, write like this:

SELECT log , pass FROM table WHERE id = '1'

It should be noted that the ability to work with databases will not be enough. To create a professional Internet project, you will have to learn how to add data from the database to the pages. To do this, familiarize yourself with the php web programming language. This will help you Mikhail Rusakov's cool course .

Deleting a table

Occurs with a DROP request. To do this, write the following lines:

DROP TABLE table;

Outputting a record from a table according to a certain condition

Consider this code:

SELECT id, countri, city FROM table WHERE people>150000000

It will display the records of countries where the population is more than one hundred and fifty million.

An association

Linking multiple tables together is possible using Join. See how it works in this video:

PHP and MySQL

Once again I want to emphasize that requests when creating an Internet project are a common thing. To use them in php documents, follow the following algorithm of actions:

• Connect to the database using the mysql_connect() command;
• Using mysql_select_db() select the desired database;
• Processing the query with mysql_fetch_array();
• We close the connection with the mysql_close() command.

Important! Working with a database is not difficult. The main thing is to write the request correctly.

Novice webmasters will think. And what to read on this topic? I would like to recommend Martin Graber's book " SQL for mere mortals ". It is written in such a way that beginners will understand everything. Use it as a reference book.

But this is a theory. How does it work in practice? In fact, an Internet project must not only be created, but also brought to the TOP of Google and Yandex. The video course will help you with this " Creation and promotion of the site ».

Video instruction

Still have questions? Watch more online video.

Conclusion

So, dealing with writing sql queries is not as difficult as it seems, but any webmaster needs to do this. The video courses described above will help with this. Subscribe to my VKontakte group to be the first to know about new interesting information.

Queries are written without escape quotes, because MySQL, MS SQL And PostGree they are different.

SQL query: getting the specified (required) fields from the 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 displays EVERYTHING data fields.

SQL query: displaying 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 given 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. A at the beginning, Z at the end.

SELECT id, city_title FROM table_name ORDER BY city_title DESC

We get a list of records: cities in reverse ( DESC) is fine. 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. In this case, there is NO list of records.

SQL query: displaying the desired range of records

SELECT * FROM table_name LIMIT 2, 3

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

SQL queries with conditions

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

SQL query: AND construction (AND)

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

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

SQL query: OR construction (OR)

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

Get a list of records: all cities from Russia OR USA. When is the operator used? OR, then AT LEAST one condition must match.

SQL query: AND NOT construct (AND NOT)

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

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

SQL query: IN construct (B)

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 construct (NOT IN)

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 therefore it is 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 "John%"

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 a salary from 25,000 to 50,000 inclusive.

There are VERY many logical operators, so study the SQL server documentation in detail.

Complex SQL queries

SQL query: join multiple queries

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

We get a list of entries: users who are registered in the system, as well as those users who are registered on the forum separately. The UNION operator can combine multiple queries. 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: calculation of field values ​​MAX, MIN, SUM, AVG, COUNT

The output of one, the maximum value of the counter in the table:

SELECT MAX(counter) FROM table_name

The 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

Conclusion of the number of meters in shop No. 1, in the table:

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

These are the most popular commands. It is recommended, where possible, to use SQL queries of this kind for the calculation, since no programming environment can be compared in the speed of data processing 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 construction, you can find out the size of each continent (based on grouping by continents).

SQL query: using multiple tables via alias (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 buyers who live only in Tyumen.

In fact, with a properly designed database of this type, the query is the most frequent, so MySQL introduced a special operator that 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 for other users. There are very frequent cases of recursive calls with nested queries. Therefore, I strongly recommend NOT to use nested queries, but to break them into smaller ones. Or use the LEFT JOIN combination described above. In addition to this type of requests, they are an increased focus of security breaches. If you decide to use nested subqueries, then you need to design them very carefully and make initial runs on database copies (test databases).

SQL queries changing data

SQL query: INSERT

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

Option number 1. Often used instruction:

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

In the table " table_name» will insert 2 (two) users 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 are slightly slower than one large SQL query, but other queries will be queued for service. That is, if a large SQL query runs for 30 minutes, then during all this time the rest of the queries will smoke bamboo and wait for their turn.
• The request is 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 creating archives in this way, you can easily copy one line and run it through the command line (console), thereby not restoring the entire ARCHIVE.
• The writing style is similar to the UPDATE statement, which is 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 the number 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. All field names are recommended to be written in small letters and, if necessary, separated by a forced space "_" for compatibility with different programming languages ​​such as Delphi, Perl, Python and Ruby.
2. SQL commands are written in CAPITAL letters for readability. Always remember that other people can read the code after you, but most likely you yourself after N amount of time.
3. Name the fields first with a noun and then with an action. For example: city_status, user_login, user_name.
4. Try to avoid reserve 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 pretend to be a super mega authoritative source, which is the primary source of SQL queries of a particular database.

• Translation
• tutorial

Should it be “ SELECT * WHERE a=b FROM c ” or “ SELECT WHERE a=b FROM c ON * ” ?

If you're like me, you'll agree: SQL is one of those things that seems easy at first glance (it reads like English!), but somehow you have to google every simple query to find the correct syntax.

And then joins, aggregation, subqueries begin, and it turns out to be completely rubbish. Like this:

SELECT members.firstname || " " || members.lastname AS "Full Name" FROM borrowings INNER JOIN members ON members.memberid=borrowings.memberid INNER JOIN books ON books.bookid=borrowings.bookid WHERE borrowings.bookid IN (SELECT bookid FROM books WHERE stock>(SELECT avg(stock ) FROM books)) GROUP BY members.firstname, members.lastname;

Bue! This will scare away any beginner, or even an average developer if he sees SQL for the first time. But it is not all that bad.

It's easy to remember what's intuitive, and with this guide, I hope to lower the barrier to entry into SQL for beginners, while also offering a fresh take on SQL for the more experienced.

Although the syntax of SQL is almost the same in different databases, this article uses PostgreSQL for queries. Some examples will work in MySQL and other databases.

1. Three magic words

There are many keywords in SQL, but SELECT , FROM and WHERE are present in almost every query. A little later, you will realize that these three words represent the most fundamental aspects of building queries against the database, and other, more complex queries, are just add-ons on top of them.

2. Our base

Let's take a look at the database we'll be using as an example in this article:

We have a book library and people. There is also a special table for accounting issued books.

• The "books" table stores information about the title, author, publication date, and availability of the book. Everything is simple.
• In the "members" table - the names and surnames of all people who signed up for the library.
• The "borrowings" table stores information about books borrowed from the library. The bookid column refers to the id of the borrowed book in the "books" table, and the memberid column refers to the corresponding person in the "members" table. We also have a date of issue and a date when the book must be returned.

3. Simple request

Let's start with a simple request: we need names And identifiers(id) all books written by the author “Dan Brown”

The request will be like this:

SELECT bookid AS "id", title FROM books WHERE author="Dan Brown";

And the result is like this:

id	title
2	The Lost Symbol
4	Inferno

Pretty simple. Let's break down the request to understand what's going on.

3.1 FROM - where we get the data from

This may seem obvious now, but FROM will be very important later on when we get to joins and subqueries.

FROM points to the table to query. This can be an already existing table (as in the example above), or a table created on the fly via joins or subqueries.

3.2 WHERE - what data to show

WHERE simply behaves like a filter lines that we want to output. In our case, we only want to see rows where the value in the author column is "Dan Brown".

3.3 SELECT - how to display data

Now that we have all the columns we need from the table we need, we need to decide how exactly to show this data. In our case, we only need titles and book IDs, so that's what we're doing. choose with SELECT . At the same time, you can rename the column using AS .

The whole query can be visualized with a simple diagram:

4. Connections (joins)

Now we want to see the titles (not necessarily unique) of all of Dan Brown's books that have been borrowed from the library, and when those books need to be returned:

SELECT books.title AS "Title", borrowings.returndate AS "Return Date" FROM borrowings JOIN books ON borrowings.bookid=books.bookid WHERE books.author="Dan Brown";

Result:

Title	return date
The Lost Symbol	2016-03-23 00:00:00
Inferno	2016-04-13 00:00:00
The Lost Symbol	2016-04-19 00:00:00

For the most part, the request is similar to the previous one. with the exception of FROM section. It means that we are querying data from another table. We are not accessing either the "books" table or the "borrowings" table. Instead, we turn to new table, which was created by joining these two tables.

borrowings JOIN books ON borrowings.bookid=books.bookid is a new table that has been formed by combining all records from the "books" and "borrowings" tables where the bookid values ​​match. The result of such a merger will be:

And then we make a query to this table in the same way as in the example above. This means that when joining tables, you only need to worry about how to conduct this join. And then the request becomes as clear as in the case of the “simple request” from point 3.

Let's try a slightly more complex join with two tables.

Now we want to get the names and surnames of the people who took the books of the author “Dan Brown” from the library.

This time let's go from bottom to top:

Step 1- where do we get the data from? To get the result we want, we need to join the "member" and "books" tables with the "borrowings" table. The JOIN section will look like this:

borrowings JOIN books ON borrowings.bookid=books.bookid JOIN members ON members.memberid=borrowings.memberid

The result of the connection can be seen at the link.

Step 2 What data do we show? We are only interested in data where the author of the book is “Dan Brown”

WHERE books.author="Dan Brown"

Step 3 How do we display data? Now that the data has been received, you just need to display the first and last names of those who took the books:

SELECT members.firstname AS "First Name", members.lastname AS "Last Name"

Super! It remains only to combine the three components and make the request we need:

SELECT members.firstname AS "First Name", members.lastname AS "Last Name" FROM borrowings JOIN books ON borrowings.bookid=books.bookid JOIN members ON members.memberid=borrowings.memberid WHERE books.author="Dan Brown";

What will give us:

first name	last name
Mike	Willis
Ellen	Horton
Ellen	Horton

Great! But the names are repeated (they are not unique). We will fix this soon.

5. Aggregation

Roughly speaking, aggregations are needed to convert multiple strings into one. At the same time, during aggregation, different logic is used for different columns.

Let's continue with our example where duplicate names appear. It can be seen that Ellen Horton took more than one book, but this is not the most The best way show this information. You can make another request:

SELECT members.firstname AS "First Name", members.lastname AS "Last Name", count(*) AS "Number of books borrowed" FROM borrowings JOIN books ON borrowings.bookid=books.bookid JOIN members ON members.memberid=borrowings .memberid WHERE books.author="Dan Brown" GROUP BY members.firstname, members.lastname;

Which will give us the desired result:

first name	last name	Number of books borrowed
Mike	Willis	1
Ellen	Horton	2

Almost all aggregations come with a GROUP BY clause. This thing turns a table that could be obtained by a query into groups of tables. Each group corresponds to a unique value (or group of values) of the column we specified in the GROUP BY . In our example, we are converting the result from the previous exercise into a group of strings. We also do an aggregation with count , which converts multiple rows into an integer value (in our case, the number of rows). This value is then assigned to each group.

Each row in the result is the result of the aggregation of each group.

You can come to the logical conclusion that all fields in the result must either be specified in GROUP BY , or aggregation must be performed on them. Because all other fields can differ from each other in different rows, and if you select them with SELECT "th, then it is not clear which of the possible values ​​\u200b\u200bshould be taken.

In the example above, the count function processed all rows (since we were counting the number of rows). Other functions like sum or max only process the specified rows. For example, if we want to know the number of books written by each author, then we need this query:

SELECT author, sum(stock) FROM books GROUP BY author;

Result:

author	sum
Robin Sharma	4
Dan Brown	6
John Green	3
Amish Tripathi	2

Here the sum function only processes the stock column and calculates the sum of all the values ​​in each group.

6. Subqueries

Subqueries are normal SQL queries embedded in larger queries. They are divided into three types according to the type of the returned result.

6.1 Two-dimensional table

There are queries that return multiple columns. A good example is the query from the previous aggregation exercise. Being a subquery, it will simply return another table on which new queries can be made. Continuing the previous exercise, if we want to know the number of books written by the author “Robin Sharma”, then one of the possible ways is to use subqueries:

SELECT * FROM (SELECT author, sum(stock) FROM books GROUP BY author) AS results WHERE author="Robin Sharma";

Result:

Can be written as: ["Robin Sharma", "Dan Brown"]

2. Now use this result in a new query:

SELECT title, bookid FROM books WHERE author IN (SELECT author FROM (SELECT author, sum(stock) FROM books GROUP BY author) AS results WHERE sum > 3);

Result:

title	bookid
The Lost Symbol	2
Who Will Cry When You Die?	3
Inferno	4

It's the same as:

SELECT title, bookid FROM books WHERE author IN ("Robin Sharma", "Dan Brown");

6.3 Individual values

There are queries that result in only one row and one column. They can be treated as constant values ​​and can be used anywhere values ​​are used, such as in comparison operators. They can also be used as two-dimensional tables or single-element arrays.

Let's, for example, get information about all the books, the number of which in the library exceeds the average value at the moment.

The average can be obtained in this way:

select avg(stock) from books;

What gives us:

7. Write operations

Most database write operations are quite simple compared to more complex read operations.

7.1 Update

The syntax of an UPDATE request is semantically the same as a read request. The only difference is that instead of selecting columns with SELECT "th, we set the knowledge with SET "th.

If all Dan Brown's books are lost, then you need to reset the quantity value. The query for this would be:

UPDATE books SET stock=0 WHERE author="Dan Brown";

WHERE does the same thing as before: selects rows. Instead of SELECT , which was used when reading, we now use SET . However, now you need to specify not only the name of the column, but also the new value for this column in the selected rows.

7.2 Delete

A DELETE query is just a SELECT or UPDATE query with no column names. Seriously. As with SELECT and UPDATE , the WHERE clause remains the same: it selects the rows to be deleted. The delete operation destroys the entire row, so it doesn't make sense to specify individual columns. So, if we decide not to reset the number of Dan Brown's books, but to delete all entries in general, then we can make a request like this:

DELETE FROM books WHERE author="Dan Brown";

7.3 Insert

Perhaps the only thing that differs from other types of queries is INSERT . The format is:

INSERT INTO x(a,b,c) VALUES(x, y, z);

Where a , b , c are the column names and x , y and z are the values ​​to be inserted into those columns, in that order. That's basically it.

Let's look at a specific example. Here is an INSERT query that populates the entire "books" table:

INSERT INTO books (bookid,title,author,published,stock) VALUES (1,"Scion of Ikshvaku","Amish Tripathi","06-22-2015",2), (2,"The Lost Symbol"," Dan Brown","07-22-2010",3), (3,"Who Will Cry When You Die?","Robin Sharma","06-15-2006",4), (4,"Inferno" ,"Dan Brown","05-05-2014",3), (5,"The Fault in our Stars","John Green","01-03-2015",3);

8. Verification

We have come to the end, I propose a small test. Look at that request at the very beginning of the article. Can you figure it out? Try breaking it down into SELECT , FROM , WHERE , GROUP BY sections, and look at the individual subquery components.

Here it is in a more readable form:

SELECT members.firstname || " " || members.lastname AS "Full Name" FROM borrowings INNER JOIN members ON members.memberid=borrowings.memberid INNER JOIN books ON books.bookid=borrowings.bookid WHERE borrowings.bookid IN (SELECT bookid FROM books WHERE stock> (SELECT avg(stock ) FROM books)) GROUP BY members.firstname, members.lastname;

This query returns a list of people who have borrowed a book from the library that has an above-average total.

Result:

Full name
Linda Tyler

I hope you managed to figure it out without any problems. But if not, then I will be glad for your comments and feedback so that I can improve this post.

Tags: Add tags