What is the structure of a relational database?

Question

MySQL

As I understand it: a hierarchical database is stored as XML data. Those. The hierarchy is defined by nesting.
The network has many links to other fields - related to the desired field.
I do not understand the logic of relational databases, namely how they are stored and the logic of their relationships.
Now I will state a little of my imagination on this topic, please check.
Speak that relational is stored in the form of the table. But in a PC, after all, memory is considered linear (meaning as a one-dimensional array), exactly how this table is stored there. For example, there are 2 tables:

I know that the order of records in such a database is chaotic.
So the data is stored in rows. EXAMPLE 1st table in memory:
"1", "P.P. Ivanov", "laboratory assistant", "[2t:2]";
"3","Petrov A.N.","
"2","Sidorova A.M","accountant","[2v:1]";
(it's all on one line)
In square brackets, type reference to the second table, where the value of the same field has a value after the colon.
Is this how relational databases are stored in memory?
And yet, how is the data connection manifested in the query?
There is a trace. query: SELECT * FROM TABLE1 WHERE "Name"="Sidorova A.M."
I don't know how to write that the tables are related, but let's assume that "Department" is related to "Employee #" like in the picture.
Let's assume that the second table in memory has the same order.
Having our tables in memory, the query logic will be as follows:
1) The server will find and read the 1st record, because it does not match the WHERE clause, will go to next. records.
2) Check the second entry, also skip it, because condition is false.
3) Read the 3rd entry, because the condition is true, will return to the number and display the values ​​of the table fields, and since the last field is a link, will go to the second table.
4) In the first record of the 2nd table, check the related fields, because they are not equal, will go from the next. entries in the 2nd table.
5) In the 2nd record, the related fields are identical, it will also display the records of the second table.
If in the 2nd table "Employee number" the key search will stop. Because there is no point in looking further. Otherwise, the server will check the entire second table.
The above is my understanding of the logic of the rel. DB.
At the university, the SQL language is explained to us in an abstract way, but I don’t understand it. Filled up a subject where SQL was necessary. I need to understand exactly the step-by-step logic of how SQL works.
Whether there is any book in which logic of operation of a DB is so described (on a low level)?

Answer 1

They switch to a low level when at least something is clear on the normal one.
How does it help you if you don't know how it works in "normal use" at all?
First you need to know SQL. This is a rather modest language and there is nothing super complicated there.
All the nuances already depend on which DBMS to use: postgre, mysql, mssql.
And the main base of the language with the simplest things select, insert, update, delete, where is the same in all.
And there is almost nothing to learn, because. it is understood at the level of reading. written not with abstract functions that need to be studied from the documentation, but with words that do what they mean.
There are no search/connection processes like you describe.
The tables refer to each other, but this is not for select at all, because select itself does not affect related data from other tables in any way.
Table linking works in case of updating/deleting records, for example.
Otherwise, the work of the base inside is much more complicated than several points in one sentence, and differs greatly depending on the actual base used.
Mysql (and analogues in all other subds) has indexes, cache, partitions, data on disk, data in RAM.
All this is somehow involved in the process of searching and selecting data.
Not to mention a bunch of optimizations and all sorts of abstruse algorithms to make it work with super speed and huge data that it can contain.
So if you can’t understand the basics of SQL, then there’s a dark forest inside. (Sources are open, you can read to the holes)

Answer 2

К примеру, есть 2 таблицы

Это нарушение нормальной формы. Изучение БД, SQL нужно начинать с математики, конкретнее, теории множеств.