Basics of SQL on the example of the task

In this tutorial we will look at the basic sql commands on the example of a small task. When reading, it is advisable to sit at the mysql console and enter all requests for greater clarity.

Formulation of the problem

A database is given, in it there are 3 tables of the following type:




table1: user_id (INT (5), PRIMARY KEY), username (VARCHAR (50), INDEX)
table2: phone_id (INT (5), PRIMARY KEY), user_id (INT (5), INDEX), phone_number (INT (10), INDEX)
table3: room_id (INT (5), PRIMARY KEY), phone_id (INT (5), INDEX), room_number (INT (4) INDEX)
')
You must select the number of the room in which the user with the nickname qux is sitting ...

Preparing data for the task

To create databases and tables, use the CREATE DATABASE and CREATE TABLE statements, respectively (to remove DROP DATABASE and DROP TABLE). At the end of each command is a semicolon (;). First, create a database named article:

CREATE DATABASE IF NOT EXISTS article; 

We use the keywords IF NOT EXISTS so that an error does not occur if the specified database or table already exists (we will omit IF NOT EXISTS for the sake of simplicity).
Now you need to create the tables:

 CREATE TABLE `table1` (`user_id` INT(5) NOT NULL AUTO_INCREMENT, `username` VARCHAR(50), PRIMARY KEY(`user_id`), INDEX(`username`)); CREATE TABLE `table2` (`phone_id` INT(5) NOT NULL AUTO_INCREMENT, `user_id` INT(5) NOT NULL, phone_number INT(10) NOT NULL, PRIMARY KEY (`phone_id`), INDEX(`user_id`, `phone_number`)); CREATE TABLE `table3` (`room_id` INT(5) NOT NULL AUTO_INCREMENT, `phone_id` INT(5) NOT NULL, `room_number` INT(4) NOT NULL, PRIMARY KEY(`room_id`), INDEX(`phone_id`, `room_number`)); 

Let's sort these commands in order. After CREATE TABLE, the name of the table is specified, then in parentheses follow the names of the fields with the types and attributes, separated by commas and key instructions. The first command we create a table with the name table1 and fields user_id, username. The user_id field has an integer type (INT) and is 5 characters long, cannot be zero and has an auto_increment attribute (when creating each record, the value in this field is created automatically and incremented by one), and it is also the primary key. [The primary key is one of the examples of unique indexes and is used to uniquely identify table entries. None of the two entries in the table can have the same primary key values. ] The username field is of character type (255 characters long) and is an index. The second and third teams are similar to the first.

To check which tables you have in the database, you can use the command:

 SHOW TABLES; 

Now you need to add data to the table. To add entries, use the INSERT statement.

 INSERT INTO table1 (username) VALUE ('foo'); 

We do not add anything to the user_id field as it is automatically created for each INSERT (remember the auto_increment magic attribute). After the name of the table in parentheses (hereinafter, we will call these brackets a tuple) we indicate the list of fields to which we will assign values. After VALUE, the values ​​themselves are specified. They must be in appropriate positions in the tuple.
The same commands add users bar, baz, qux.
For verification, use the command:

[one]

 SELECT * FROM table1; 

The SELECT command itself will be covered in more detail later.
Next, fill in the table table2 and table3.

[2]

 INSERT INTO table2 (user_id, phone_number) VALUE ('2','200'); 

Here, the user_id field is assigned the value 2, and the phone_number field is set to 200. If you swap the field names or values ​​in the tuples, the result will change. For example:

[3]

 INSERT INTO table2 (user_id, phone_number) VALUE ('200','2'); 

Now the user_id field is assigned the value 200, and the phone_number is 2.
Suppose we made a mistake when adding values ​​(using the command [3] instead of [2]), do not rush to delete the table or the entire database - the value can be changed using the UPDATE statement.

 UPDATE table2 SET user_id='2', phone_number='200' WHERE phone_id='1'; 

After SET we indicate the fields whose values ​​need to be changed, and accordingly the new values ​​through the equal sign. We see the WHERE operator for the first time. It is necessary to impose restrictions on the request. In this case, the changes will not be applied to all the rows of the table, but only to those with the value of the phone_id field equal to '1'.
The rest of the data is added by analogy (which can be added at the top of the page).

Decision

Database and tables we created. Now you can do the solution of the problem itself. The database is sampled using the SELECT statement, which we are a little familiar with on the command [1]. Consider it in more detail. In general, it looks like this:
SELECT field_name FROM table_name WHERE condition [ORDER BY, LIMIT]
Where ORDER BY and LIMIT are additional options.
Let's try to apply it. Select all the values ​​of the username field from the table1 table.

 SELECT username FROM table1; 

and sort them

 SELECT username FROM table1 ORDER BY username; 

As you can see, ORDER BY is used to sort by one of the fields specified after the SELECT statement. By default, an increasing sorting is done, if you want to use sorting in the reverse order, then DESC should be added after the field:

 SELECT username FROM table1 ORDER BY username DESC; 

Since we need all the values, the WHERE clause can be omitted. Another example: select the values ​​of the phone_id and user_id fields from the table table2, where the phone_number is '200'.

 SELECT phone_id, user_id FROM table2 WHERE phone_number=200; SELECT phone_id, user_id FROM table2 WHERE phone_number=200 LIMIT 1, 3; 

LIMIT outputs lines in the specified range (the lower bound does not turn on). If the first argument is not specified, then it is considered to be equal to 0.

As we can see, all three of our tables are related. table1 and table2 via the user_id field, and table2 and table3 via the phone_id. In order to link them into one unit by the specified columns, it is necessary to use the JOIN operator. JOIN, translated into great and mighty, means "to unite", that is, to assemble a single whole from several pieces. In the MySQL database, these “slices” are the table fields that can be combined in a sample. Joins allow you to retrieve data from multiple tables in a single query. Depending on the requirements for the result, MySQL allows you to make three different types of joins:
1. INNER JOIN (CROSS JOIN) - internal (cross) union
2. LEFT JOIN - left-sided outer join
3. RIGHT JOIN - right-side outer join

INNER JOIN allows you to retrieve rows that are necessarily present in all joined tables.
Let's try to write a request:

[four]

 SELECT table3.room_number FROM table1 INNER JOIN table2 USING(user_id) INNER JOIN table3 USING(phone_id) WHERE table1.username = 'qux'; 

With the help of the USING operator, we specify the field by which the tables will be linked. Its use is possible only if the fields have the same name. Otherwise, you must use ON, as shown below:

 SELECT table3.room_number FROM table1 INNER JOIN table2 ON table1.user_id = table2.user_id INNER JOIN table3 ON table2.phone_id = table3.phone_id WHERE table1.username = 'qux'; 

LEFT / RIGHT JOIN allows you to extract data from a table, complementing it with data from another table where possible. To show the difference with INNER JOIN, we first need to add another field to table1.

 INSERT INTO table1 (username) VALUE ('quuz'); 

And now we use the command [4], only replace the INNER JOIN with the LEFT JOIN, and qux with quuz:

 SELECT table3.room_number FROM table1 LEFT JOIN table2 USING(user_id) LEFT JOIN table3 USING(phone_id) WHERE table1.username = 'quuz'; 

We get the following result:


New user got user_id = 5. This value is not available in other tables, so as a result we get NULL. With INNER JOIN, the result would be empty, since only values ​​that are in all tables are displayed. Here, the tables table1 and table2 are supplemented with the value from table3, even if it does not exist.

Appendix

The following are examples of commands with some explanations:

Delete the row with user_id equal to 1 from table1:

 DELETE FROM table1 WHERE user_id = 1; 

Rename the table table1 to nya:

 RENAME TABLE table1 TO nya; 

Rename the user_id field to id (table1):

 ALTER TABLE table1 CHANGE user_id id INT; 

Change the type and attribute of the phone_number field:

 ALTER TABLE table2 MODIFY phone_number VARCHAR(100) NOT NULL; 

View description of table1 table:

 DESCRIBE table1; 

Add an abate DATE field:

 ALTER TABLE table3 ADD abra DATE; 

From table3, we select all the values ​​of the room_id field, for which room_number begins with the number 3 (% means any number of any characters; like checks if the character string matches the specified pattern):

 SELECT room_id FROM table3 WHERE room_number LIKE '3%'; 

PS

1. Part of the material about join `s is taken from the MySQL article a little about JOINs .
2. The task met at one of the interviews that I took. It is quite synthetic, but it is well suited to describe the material.
3. Descriptions of the constructions of operators are intentionally simplified for easier perception by beginners. For everyone else, there is a MySQL Reference Guide.