Les liens de la mer numérique
Eḿbed SQL examples in a web page. It uses a browser-compatible compiled sqlite under the hood .
The deficiencies of SQL to use it as frontend databases:
- Standard SQL doesn’t support nesting
- SQL syntax is verbose and non-uniform.
- SQL’s scalar expression language is weird and limited
- SQL doesn't have good tools for metaprogramming and changing the shape of a query at runtime
Maybe useful someday
The use of SQL within streaming systems opens up a new chapter in the story of SQL within the data domain.
Machine learning needs data and a lot of processing. Thus the data needs to be efficiently stored and retrieved
“Every decade, another hyped-up database technology comes along that claims SQL is terrible, slow, or impractical,” Pavlo says. “Over time, the conventional wisdom comes back to realizing that [SQL] is a good idea, and everyone returns to it.”
Reference to NoSQL, document-based database, graphs with nodes and edges.
SQL table expressions are somewhat similar to functions in a regular programming language — they reduce the overall complexity.
You can write an unreadable sheet of code, or you can break the code into understandable individual functions and compose a program out of them.
You can build a tower of nested subqueries, or you can extract them into CTEs and reference from the main query.There is a myth that “CTEs are slow”. It came from old versions of PostgreSQL (11 and earlier), which always materialized CTE — calculated the full result of a table expression and stored it until the end of the query.
Ok. There are some rules:
- CTE runs on every request
- CTE splits the query code into multiple chunks
- instead of subquery, always use CTE for clarity
Installer pv, puis passer de
zcat data-export.gz | mysql -u measuser -p -h db_host -P 3306 mydatabase
à
pv -pret --name ' Importing.. ' data-export.gz | zcat | mysql -u measuser -p -h db_host -P 3306 mydatabase
And that’s the characteristic problem with the normalized approach: In exchange for the simplicity of working exclusively with normalized data, you have to write queries that don’t scale.
With denormalization, there is so much to think about, so much edge cases that needs to be handled !
A reason to use sqlite for small projects.
SQLite is not client/server, however. The SQLite database runs in the same process address space as the application. Queries do not involve message round-trips, only a function call. The latency of a single SQL query is far less in SQLite. Hence, using a large number of queries with SQLite is not the problem.
About the usage of assert, ALWAYS, NEVER, testcase
Différentes astuces pour optimiser la base de données
Un guide PL/SQL. Sous le coude au besoin éventuel
Bien utiliser les indexes des systèmes de gestion de base de données. Plusieurs systèmes sont passées en revue.
Des diagrammes qui sont plus explicites ! J'ai enfin compris les différences entre les JOIN en SQL.