1.1 Principle of construction of lectures
You and I started our acquaintance with databases from below . This is a feature of my personal approach to teaching people. When talking about new topics, I always first tell you how to use certain tools in practice. And already when I know that a person knows how to use them, I begin to tell how everything works.
There are several reasons for this approach, but the main one is that the most valuable and most missing resource in the learning process is student motivation .
This approach is slightly different from the one we are used to, which is used in schools and universities. But everything is clear: when you study at school or university, you have the right priorities: studying is the most important thing in life at this moment.
If you are engaged in self-education in adulthood, then often you already have to combine your studies with work, household chores, caring for children or elderly parents. And here often study will not be the first priority.
It's all about priorities. There is even such a concept in the world of startups - Fail Fast, fail as quickly as possible . It sounds strange, but in fact it makes a lot of sense: the task of a startup is to quickly check whether his hypothesis is correct. And if it is not true, then you do not need to spend years of your life on it, it is better to understand as early as possible that there is no demand for a certain service or product.
When teaching Java and SQL, I use the same approach: I give you the opportunity to understand as early as possible whether you are rushing from programming or not . If you like programming, and you yourself were able to figure out how to work with loops and arrays, then with the help of mentors and a well-designed program, you have every chance to complete your studies and find a job.
But another fact is no less important: in a couple of days off, you can understand that programming is not for you. You may just not be interested in it, and that's okay . So, you do not need to spend months of your life on it.
Only 40% of graduates work in the specialty they received at the university. Think about it, people studied for 5-6 years, and 60% of them decided not to work in their specialty. Yes, some of the knowledge gained is still used, but about half of it is not.
This is the value of the Fail Fast concept - to understand as early as possible that a certain profession, a certain person or a certain hobby is not suitable for you. And don't waste your time and energy on them. In the long run, this is a very good strategy.
1.2 SQL and all, all, all
We are done with the philosophical introduction, let's get back to learning SQL.
SQL language and DBMS are slightly different things. The SQL language itself is a kind of standard that describes what can be written in SQL queries to the database. DBMS are already implementations of this standard. Some DBMS implements some functions of the standard, the second - others, and so on.
The more expensive the DBMS, the more features of the standard it implements. Also, many DBMSs often implement their own unique features outside of the SQL standards. Sometimes this leads to portability issues: SQL queries written for one DBMS may not work well for another.
Java also has a similar situation. If a Java program is written under Windows, then it will not work normally on Linux. To solve this problem, Java introduces special classes that have different implementations for different operating systems. Example: Path class which has implementations of WindowsPath, LinuxPath, etc.
The second part of the problem is solved with the help of versioning. All successful innovations from different languages or DBMS are added to the new JDK or SQL standard. You already know that there are different versions of the JDK, and the newer the version, the more features it has. It's the same with SQL.
In the SQL language, there are several versions of its standard, which are named by year:
- SQL:1999
- SQL:2003
- SQL:2006
- SQL:2011
- SQL:2016
- SQL:2019
Good news : we will not study these standards. Firstly, it will take years to study and master all this. And secondly, these standards are like versions of Android: only 5-10 years after the release, the standard becomes massively widespread.
In databases with large amounts of data, people need reliability and stability. “It works, don’t touch it” is the motto of everyone who works with databases. And the transition to a new version of databases is done every 5 years, when all the advantages of such a solution are already obvious.
1.3 Beyond the brackets
As I said above, it takes years to become a database professional. A professional knows a lot of things that we will not study. But I'll talk a little about what else is in the databases.
Almost all modern databases support:
1 Procedural Language (PL)
RDBMS support the ability to write procedures and functions that run on SQL Server and can do a lot of things with data during queries. For example, once I wrote PL SQL queries to the Oracle server, which in response to the query generated ... an HTML page with data. Yes, you can.
2 Events (Triggers)
All modern DBMS support the mechanism of events, which are called triggers in the SQL language. A trigger occurs as a response to some action. For example, you can intercept all attempts to write to the database and add the exact time of their change to new lines.
3 Logging
Modern databases try to be super fast, so often all changes (new rows, deleted rows, changed rows) are first written to a special file called a log. And only after some time, the SQL server will merge these records with the main database.
In some ways, this is similar to the behavior of the Garbage Collector in Java: it also simply marks objects as deleted at first, and performs memory cleanup and optimization during idle times.
4 Plugins
To the DBMS, as well as to many programs, you can write your own plugins. Such plugins allow you to add unique data types, functions for working with them, or change the standard behavior of the DBMS. This is especially useful when you work with an open source database and there are some bugs.
5 Distributed work (clusters)
A typical scenario for a modern SQL server is a cluster of several servers. The simplest option is when data is written to one server and read from a group of servers. In this case, you can configure various scenarios for database synchronization between SQL servers.
6 Sharding
When there is a lot of data, they begin to be split into different databases. Up to the fact that one table can be stored in parts in different databases.
Sharding can be vertical and horizontal. Vertical sharding means that the table is, as it were, cut by vertical lines, while horizontal sharding is cut into horizontal ones.
For example, we decided to divide all the data in the table by years: for 2019 - one table, for the data of 2020 - the second, and so on. This will be horizontal sharding.
7 shove the non-shove
At a certain stage in the development of databases, more and more business logic began to be added to them. It all started with procedures, functions, generating web pages by servers, and ended up with the addition of support for almost all popular languages to the DBMS: Python, JavaScript, and even Java and C ++.
Sounds cool until you start getting into the details: do you really want to write your web application business logic in Java, which will be executed inside a SQL server, where there is no JDK, java libraries, frameworks, little memory and a lot of other restrictions?