SQL Notes

Notes on SQL-related stuff.

1. Resources

• GROUP_CONCAT and DISTINCT
• Replacing NULL with a different value
• Pivoting in MySQL
• Performance tuning — Query Caching
• TypeORM — Databases with TypeScript

2. Show columns from a SELECT query

-- create a temporary table
CREATE TEMPORARY TABLE exportTable AS (your_query);
-- show the columns
SHOW COLUMNS FROM exportTable;

Source

3. TRUNCATE all tables in a database

SET FOREIGN_KEY_CHECKS = 0;

truncate table "yourTableName";

SET FOREIGN_KEY_CHECKS = 1;

Source

4. Big data

A data set of 9 million tuples is nothing for a relational database. For maintaining sufficient performance as always use indexes and all will be well.

Source