Mysql Join connection query

know

Multi table join query, I think it should be the place where relational database can best reflect its value and flexibility. I think the function of database is nothing more than storage and query

In a word, a database can store and query data flexibly. In terms of storage, it is also stored in the form of files (from the Linux point of view, everything is files). Files are the carriers of storing data. The difference between a database file and other data files is that there are rules. There are constraints and specifications. Unlike work, many places are fooling around. Just because something is agreed in advance, so the data Database retrieval data is very fast

Of course, in terms of storage mode, I usually use a single machine for my own use. In the company's case, it is generally distributed storage. In fact, the data is distributed on each machine, such as master-slave configuration. I usually don't pay much attention to storage when learning to use it. Of course, I will practice configuring the master-slave

Obviously, most queries are used. Our department hasn't really used the database until now. There are some small problems in books between each system. The daily work is to export query sets from each system and let Excel manually perform Vlookup. Pure manual work is the main reason for the intention to leave. After all, the work efficiency of Vlookup + sumifs / contracts / pivot table is far less than that of sql select and join The work done by the two is the same, only the difference of efficiency

There are left (outer) connection, right (outer) connection, inner connection, Cartesian product and so on in table connection. Usually vlookup is actually left connection. The connection between tables must be based on the common field (key), otherwise, it is directly spliced rather than connected

  • inner join: the "intersection" of two tables, that is, the left table and the right table are connected by a common key, and they have common records
  • left join: that is, vlookup. All records in the left table are displayed. Only records in the "match" are displayed in the right table. If the right table does not match the left table, it is Null
  • right join: it's the same as left, just a right perspective

My business in the past half year is vlookup left connection, which basically does not use inner join. Why, the requirement of business is to use the left table as the basis of number, otherwise, it is difficult to program data from other tables (right table) continuously, which is still demonstrated in the previous student and classes tables

Inner, left, right

-- Sign in mysql Client, View the dataset used under
mysql> use student_db;
Database changed
mysql> show tables;
+----------------------+
| Tables_in_student_db |
+----------------------+
| classes              |
| clock_in             |
| students             |
+----------------------+
3 rows in set (0.04 sec)

mysql> select * from classes;
+----+------+
| id | name |
+----+------+
|  1 | science |
|  2 | Art |
+----+------+
2 rows in set (0.09 sec)

mysql> select * from students;
+----+----------+-----+--------+--------+----------+-----------+
| id | name     | age | height | gender | class_id | is_delete |
+----+----------+-----+--------+--------+----------+-----------+
|  1 | Einstein |  18 | 180.00 | male     |        1 | 0         |
|  2 | Mrs. Curie |  18 | 180.00 | female     |        2 | 1         |
|  3 | Princeling   |  14 | 185.00 | male     |        1 | 0         |
|  4 | Li Yinhe   |  59 | 175.00 | male     |        2 | 1         |
|  5 | Huang Rong     |  38 | 160.00 | female     |        1 | 0         |
|  6 | Bing Xin     |  28 | 150.00 | female     |        2 | 1         |
|  7 | Joey Wong   |  18 | 172.00 | female     |        1 | 1         |
|  8 | Jay Chou   |  36 | NULL   | male     |        1 | 0         |
|  9 | Wang Xiaobao   |  57 | 181.00 | male     |        2 | 0         |
| 10 | Lin Whei-yin   |  25 | 166.00 | female     |        2 | 0         |
| 11 | Little star     |  33 | 162.00 | Not filled |        3 | 1         |
| 12 | Eileen Chang   |  12 | 180.00 | female     |        4 | 0         |
| 13 | Feng Tang     |  12 | 170.00 | male     |        4 | 0         |
| 14 | Hu Shi     |  34 | 176.00 | male     |        5 | 0         |
+----+----------+-----+--------+--------+----------+-----------+
14 rows in set (0.25 sec)

If there is no test data, create database / table by yourself, then create table.. insert into tb_name values(), ()

Association basis: the class id of students table and the id of classes table represent the same thing (class id)

Add some skills of writing sql (roughly simulating the running order of the computer)

  • Write from TB? Name1 as a, TB? Name2 as B (take alias)
  • Then connect the condition join on
  • Then write where
  • Finally write select

At last, select is written, query by blocks, and then join (Union...), with the image of a table in mind

-- Retrieve the corresponding information of students and classes
select s.*, c.*
from students s, classes c  -- as Omission
where s.class_id = c.id;

-- This default where Equivalent connection, Actually follow inner join It's the same.
-- out
+----+----------+-----+--------+--------+----------+-----------+----+------+
| id | name     | age | height | gender | class_id | is_delete | id | name |
+----+----------+-----+--------+--------+----------+-----------+----+------+
|  1 | Einstein |  18 | 180.00 | male     |        1 | 0         |  1 | science |
|  2 | Mrs. Curie |  18 | 180.00 | female     |        2 | 1         |  2 | Art |
|  3 | Princeling   |  14 | 185.00 | male     |        1 | 0         |  1 | science |
|  4 | Li Yinhe   |  59 | 175.00 | male     |        2 | 1         |  2 | Art |
|  5 | Huang Rong     |  38 | 160.00 | female     |        1 | 0         |  1 | science |
|  6 | Bing Xin     |  28 | 150.00 | female     |        2 | 1         |  2 | Art |
|  7 | Joey Wong   |  18 | 172.00 | female     |        1 | 1         |  1 | science |
|  8 | Jay Chou   |  36 | NULL   | male     |        1 | 0         |  1 | science |
|  9 | Wang Xiaobao   |  57 | 181.00 | male     |        2 | 0         |  2 | Art |
| 10 | Lin Whei-yin   |  25 | 166.00 | female     |        2 | 0         |  2 | Art |
+----+----------+-----+--------+--------+----------+-----------+----+------+

-- inner join on 
select s.*, c.*
from students s
inner join classes c
on s.class_id = c.id;

-- out
+----+----------+-----+--------+--------+----------+-----------+----+------+
| id | name     | age | height | gender | class_id | is_delete | id | name |
+----+----------+-----+--------+--------+----------+-----------+----+------+
|  1 | Einstein |  18 | 180.00 | male     |        1 | 0         |  1 | science |
|  2 | Mrs. Curie |  18 | 180.00 | female     |        2 | 1         |  2 | Art |
|  3 | Princeling   |  14 | 185.00 | male     |        1 | 0         |  1 | science |
|  4 | Li Yinhe   |  59 | 175.00 | male     |        2 | 1         |  2 | Art |
|  5 | Huang Rong     |  38 | 160.00 | female     |        1 | 0         |  1 | science |
|  6 | Bing Xin     |  28 | 150.00 | female     |        2 | 1         |  2 | Art |
|  7 | Joey Wong   |  18 | 172.00 | female     |        1 | 1         |  1 | science |
|  8 | Jay Chou   |  36 | NULL   | male     |        1 | 0         |  1 | science |
|  9 | Wang Xiaobao   |  57 | 181.00 | male     |        2 | 0         |  2 | Art |
| 10 | Lin Whei-yin   |  25 | 166.00 | female     |        2 | 0         |  2 | Art |
+----+----------+-----+--------+--------+----------+-----------+----+------+

Alias as for table and field

-- Check the names and classes of the girls in the first three art classes
select s.name as "Full name", c.name as "class"
from students s
inner join classes c
on s.class_id = c.id
where (s.gender = "female") and (c.name = "Art")
limit 3;

-- out
+----------+------+
| Full name     | class |
+----------+------+
| Mrs. Curie | Art |
| Bing Xin     | Art |
| Lin Whei-yin   | Art |
+----------+------+
3 rows in set (0.15 sec)

It's not hard to find that, in fact, sql is very simple. As long as your logic is clear, you can query it bit by bit. In fact, there are only two forms: multi table connection + where +... Or matching sub query and then union. The routines are the same

-- Query the information of students and classes corresponding to the class, And in descending order of class name
select * 
from students s, classes c
where s.class_id = c.id
order by c.name desc
limit 3;

-- out
+----+--------+-----+--------+--------+----------+-----------+----+------+
| id | name   | age | height | gender | class_id | is_delete | id | name |
+----+--------+-----+--------+--------+----------+-----------+----+------+
|  4 | Li Yinhe |  59 | 175.00 | male     |        2 | 1         |  2 | Art |
|  6 | Bing Xin   |  28 | 150.00 | female     |        2 | 1         |  2 | Art |
| 10 | Lin Whei-yin |  25 | 166.00 | female     |        2 | 0         |  2 | Art |
+----+--------+-----+--------+--------+----------+-----------+----+------+
3 rows in set (0.07 sec)

-- In descending order of class name first, If the class name is the same, In descending order of height, According to the students id Ascending order
select s.name, s.height, c.name
from students s, classes c
where s.class_id = c.id
order by c.name desc, s.height desc, s.id asc
limit 6;

-- out
+----------+--------+------+
| name     | height | name |
+----------+--------+------+
| Wang Xiaobao   | 181.00 | Art |
| Mrs. Curie | 180.00 | Art |
| Li Yinhe   | 175.00 | Art |
| Lin Whei-yin   | 166.00 | Art |
| Bing Xin     | 150.00 | Art |
| Princeling   | 185.00 | science |
+----------+--------+------+
6 rows in set (0.09 sec)

I use left join every day in my work. Its function is the same as that of Excel's vlookup. It matches based on the left table. If there is no match, it will be Null

-- from classes In the table, Match out student Name and class of
select s.name as "Full name", c.name as "class"
from students s 
left join classes c
on s.class_id = c.id;

-- out
+----------+------+
| Full name     | class |
+----------+------+
| Einstein | science |
| Princeling   | science |
| Huang Rong     | science |
| Joey Wong   | science |
| Jay Chou   | science |
| Mrs. Curie | Art |
| Li Yinhe   | Art |
| Bing Xin     | Art |
| Wang Xiaobao   | Art |
| Lin Whei-yin   | Art |
| Little star     | NULL |
| Eileen Chang   | NULL |
| Feng Tang     | NULL |
| Hu Shi     | NULL |
+----------+------+
14 rows in set (0.18 sec)

-- Find out the names of students without corresponding classes
select s.name, c.name
from students s
left join classes c
on s.class_id = c.id
having c.name is null;

+--------+------+
| name   | name |
+--------+------+
| Little star   | NULL |
| Eileen Chang | NULL |
| Feng Tang   | NULL |
| Hu Shi   | NULL |
+--------+------+
4 rows in set (0.05 sec)

-- For filter conditions where It's ok
select s.name, c.name 
from students s 
left join classes c 
on s.class_id = c.id
where c.name is null;

The difference between where and having

  • Where is to filter data before grouping, and aggregation function cannot be used after where
  • hvaing is to filter data after grouping, and aggregate function can be used after having
  • Execution order in query: from > where > group by and aggregate function > having > order > select

  • having is used to make up for the shortage of where in group by, because where > aggregate function

Subquery

The so-called subquery can't be just select nesting. As long as a little bit of programming has been learned, all the little partners can get it immediately. Even if they can't program, I think my colleagues can nest it very well with Excel formulas. The outer layer is a SUMIF, and the inner one is a vlookup.... anyway, the principle is the same

  • Scalar subquery: the result of the query is a value
  • Column subquery: the result returned is a column
  • Row subquery: the returned result is a row
-- scalar: Find out the names of the students whose height is higher than the average, height, Class

-- step 1: Find out the average height first(scalar)
select avg(height) from students;
+-------------+
| avg(height) |
+-------------+
| 172.076923  |
+-------------+
1 row in set (0.07 sec)

-- step2: Nesting in
select s.name, s.height, c.name
from students s
left join classes c
on class_id = c.id
where s.height > (select avg(height) from students);

+----------+--------+------+
| name     | height | name |
+----------+--------+------+
| Einstein | 180.00 | science |
| Princeling   | 185.00 | science |
| Mrs. Curie | 180.00 | Art |
| Li Yinhe   | 175.00 | Art |
| Wang Xiaobao   | 181.00 | Art |
| Eileen Chang   | 180.00 | NULL |
| Hu Shi     | 176.00 | NULL |
+----------+--------+------+
7 rows in set (0.11 sec)

Column subquery: one query with many keywords: in

-- Query out, According to class id, Corresponding student name

-- Analysis,A class ID -> Multiple names, It's a check.(Column query)
select s.name
from students s
where s.class_id in (select id from classes)

-- out
| name    |
+----------+
| Einstein |
| Mrs. Curie |
| Princeling   |
| Li Yinhe   |
| Huang Rong     |
| Bing Xin     |
| Joey Wong   |
| Jay Chou   |
| Wang Xiaobao   |
| Lin Whei-yin   |
+----------+

Row level subquery (synthesize multiple fields into one row element)

-- The oldest age found, Information about the tallest student
select * 
from students s
where (s.age, s.height) = (select max(age), max(height) from students);

-- out: Maybe there's no record of such a bull
Empty set

Summary

  • Two core functions of database are storing and retrieving data

  • The reason for efficient query is that there are rules for the organization and storage of data files
  • The execution order is from > where > group by and aggregate function > having > order > select

  • Common table joins include inner join, left join, right join... I usually use the most left join because of Excel's vlookup
  • The subquery is actually the nesting of sql. According to the returned results, there are scalar, column and row forms

Next, we will talk about self join and multi table join query, that is, sub query matches table join, where, union, etc

Tags: MySQL Database Excel SQL

Posted on Sun, 03 Nov 2019 12:20:59 -0800 by spyrral