173 private links
A (somewhat opinionated) list of SQL tips and tricks that I've picked up over the years in my job as a data analyst.
Okay
A query builder. It can be coupled to Prisma schema for better performance.
As much as I dig into it, I find raw SQL better (or with a SQL builder).
Le NoSQL est également plus adaptée pour le développement agile car les données ne sont pas structurées pendant les itérations.
Le SQL a réussi à traverser les âges car: il est simple, il est rapide et il est stable.
Les avantages de SQLite:
- facilité d'administration: tout le contenu peut être visualisé par lecture d'un fichier.
- facilité de déploiement: il est possible de pousser les BDD en production et de les migrer
- simplicité: sqlite est intégré avec tous les langages de programmation
- capacité: une BDD peut contenir plus d'un million de TB
Ses désavantages:
- requêtes concurrentes:
- les types de données limitées: NULL, INTEGER, REA L, TEXT, BLOB. Il n'y a pas de BOOL ni de dates.
- les données sont faiblement validées: "abcd" dans INTEGER o 20 charactères dans VARCHAR(5) passent.
- la sauvegarde des données est à faire soit même
- l'hébergement n'est pas possible avec tous les hébergeurs
Create webpages from SQL requests
13 examples or applications
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