SQL

• SQL style guide by Simon Holywell

• The annotated table of contents

• CheatSheets/SQL-V1.pdf at main · ABZ-Aaron/CheatSheets

Tables names:

• sql - Table Naming Dilemma: Singular vs. Plural Names - Stack Overflow

• mysql - Native table 'performance_schema'.'???' has the wrong structure - Stack Overflow

• mysql error: Table "mysql"."innodb_table_stats" not found - Stack Overflow

• windows - Howto: Clean a mysql InnoDB storage engine? - Stack Overflow

BOOLEAN is TINYINT. See MySQL :: MySQL 5.7 Reference Manual :: 11.1.1 Numeric Type Overview

MySQL unique keys can be NULL whereas primary keys cannot be There can be only one Primary key in a table but one or more unique keys

• sql - Best way to get identity of inserted row? - Stack Overflow

Actions when a table is modified:

• MySQL :: MySQL 5.7 Reference Manual :: 23.3 Using Triggers

Syntax

• BlocklySQL Demo: Generating SQL Code with Blocks

Race condition

Aka concurrency

Use single atomic statement (like INNER JOIN) or transaction

• How to INSERT If Row Does Not Exist (UPSERT) in MySQL

• sql - Insert into a MySQL table or update if exists - Stack Overflow

• MySQL :: MySQL 5.7 Reference Manual :: 13.2.5.2 INSERT ... ON DUPLICATE KEY UPDATE Syntax

• MySQL insert on duplicate update for non-PRIMARY key - Stack Overflow

Game Leaderboard

For performances, you can create temporary/virtual table CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (SELECT * FROM table1). It's unique per session (this is not supported by non CGI PHP). Else use a regular table. See also views

-- insert game
INSERT INTO games (time, player, ip, email, mobile, over_reason, round, beginning, score)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);
-- get rank
SELECT (SELECT 1 + COUNT(*) FROM games WHERE round = ? AND score > ?) as rank;
-- get top 10
SELECT player, score FROM games
WHERE round = ?
ORDER BY score DESC
LIMIT 10;

1. game end timestamp

2. player's ID

3. player's ip (see Get client IP)

4. player's email

5. player's mobile

6. game over reason

7. game's round

8. game beginning timestamp

9. game score

10. game's round

11. game's score

12. game's round

leaderboards are read-heavy. Favor memory to store fields used to compute rank

• mysql - Get the rank of a user in a score table - Database Administrators Stack Exchange

• design - Efficient SQL Query/Schema for a Leader Board - Stack Overflow

• Leaderboard design using SQL Server - Stack Overflow

Row number

• How can I get row's index from a table in SQL Server? - Stack Overflow

• sql - ROW_NUMBER() in MySQL - Stack Overflow

• MySQL - Generating Row Number for Each Row Using Variable - SQL Authority with Pinal Dave

Insert new lines

INSERT INTO test VALUES(replace('a\nb\nc\nd\ne', '\n', char(10)));

Insert in multiple tables, use transaction to rollbak in case of error (conflict, database connection lost, etc.)

BEGIN;
INSERT INTO users (username, password)
  VALUES('test', 'test');
INSERT INTO profiles (userid, bio, homepage)
  VALUES(LAST_INSERT_ID(),'Hello world!', 'http://www.stackoverflow.com');
COMMIT;

• MySQL Insert into multiple tables? (Database normalization?) - Stack Overflow

INSERT INTO table_listnames (name, address, tele)
SELECT * FROM (SELECT 'Rupert', 'Somewhere', '022') AS tmp
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name = 'Rupert'
) LIMIT 1;

• MySQL: Insert record if not exist in table - Stack Overflow

INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE name="A", age=19

• MySQL :: MySQL 5.7 Reference Manual :: 13.2.5.2 INSERT ... ON DUPLICATE KEY UPDATE Syntax

• sql - Insert into a MySQL table or update if exist - Stack Overflow

Import file:

• sql - Import large CSV file in mysql by disable/enable constraint and indexe - Stack Overflow

Column size

• bcrypt hash: BINARY(60)

  • mysql - What column type/length should I use for storing a Bcrypt hashed password in a Database? - Stack Overflow

• Facebook user ID (numeric string): VARCHAR(128)

  • What's the max. length of a Facebook uid? - Stack Overflow

• IPV6 and IPV4: VARCHAR(50) (canonical format: as text) or VARBINARY(16) (binary, supported with functions by MySQL 5.6)

  • ip - Maximum length of the textual representation of an IPv6 address? - Stack Overflow

  • IPv6 address - Wikipedia

  • MySQL :: MySQL 5.6 Reference Manual :: 12.19 Miscellaneous Functions

  • php - Store IPv6 in database - Stack Overflow

• sex (ISO/IEC 5218): TINYINT

  • 0 = Not Known

  • 1 = Male

  • 9 = Not applicable

Variables

SET @myvar = 10

SELECT @myvar := 10

UPDATE mytable SET col = @tmp := col, col = 10

• sql - MySql How to set a local variable in an update statement (Syntax?) - Stack Overflow

• sql - How to declare a variable in MySQL? - Stack Overflow

• sql server - How to set variable from a SQL query? - Stack Overflow

• MySQL :: MySQL 5.7 Reference Manual :: 1.8.2.1 SELECT INTO TABLE Differences (MySQL: "You can use SELECT ... INTO with user-defined variables")

Prepare statement

Aka SQL injection protection, supported natively by the server.

Doesn't support column name nor value. For latest, user variable can be used (eg. SET @var1 = 1, @var2 = 2), see also stored procedure - Set variable with dynamic name in trigger (MySQL) - Database Administrator Stack Exchange Often doesn't support multiple statements (prepare individualy), or use procedure

• java - Dynamic column name using prepared statement + sql query with variable containing ' - Stack Overflow

Optimisation

Indexes:

A clustered index is like the contents of a phone book. You can open the book at 'Hilditch, David' and find all the information for all of the 'Hilditch's right next to each other. Here the keys for the clustered index are (lastname, firstname).

• indexing - What is an index in SQL? - Stack Overflow

Redundant data vs join tables (foreign keys):

• php - Is it better to store redundant information or join tables when necessary in MySQL? - Stack Overflow

• Schema types: Data retrieval performance versus redundant storage

Insert lot of rows:

• compine multiple records to one INSERT instead of one record per INSERT

• How can mysql insert millions records faster? - Stack Overflow

• MySQL :: MySQL 5.6 Reference Manual :: 8.2.4.1 Optimizing INSERT Statements

• mysqlimport --local database data.txt import tab-delimited text files or use mysqlimport --local --fields-optionally-enclosed-by='"' --fields-terminated-by=, --lines-terminated-by='\r\n' database data.csv for CSV (add --ignore-lines=1 to skip a header row). Note: use value 0 for auto increment fields.

• MySQL :: MySQL 5.6 Reference Manual :: 13.2.6 LOAD DATA INFILE Syntax

• MySQL :: MySQL 5.6 Reference Manual :: 4.5.5 mysqlimport — A Data Import Program

• How to import CSV file to MySQL table - Stack Overflow

./mysqltuner.pl --user root --pass root --defaults-file .../my.cnf --mysqladmin .../mysqladmin --mysqlcmd .../mysql

./mysqltuner.pl --user root --pass root --defaults-file /Applications/MAMP/conf/my.cnf --mysqladmin /Applications/MAMP/Library/bin/mysqladmin --mysqlcmd /Applications/MAMP/Library/bin/mysql

Compress data

Use COMPRESS() in VARBINARY or BLOB: 4-byte length + zlib data (zlib header + deflate data)

• MySQL :: MySQL 5.5 Reference Manual :: 12.13 Encryption and Compression Functions

Errors

Transaction : commit or rollback

Use SQLSTATE codes in your client, it's more standardized.

• MySQL :: MySQL 5.7 Reference Manual :: B.3 Server Error Code and Messages

Clear a table

# Kick, internaly delete and recreate the table
TRUNCATE TABLE table;

# Delete each rows, but doesn't reset auto increment
DELETE FROM table;

Truncate a table with referenced fields as foreign keys

DELETE FROM table;
-- will execute cascades
ALTER TABLE table AUTO_INCREMENT = 1;

• mysql - How to truncate a foreign key constrained table? - Stack Overflow

String comparison

Aka like

-- select URL contains url encoded space
SELECT 'http://example/David%20and%20Goliath' LIKE '\%20'

• MySQL :: MySQL 5.7 Reference Manual :: 12.5.1 String Comparison Functions

• mysql - Wildcard in Java PreparedStatement - Stack Overflow

• java - Using "like" wildcard in prepared statement - Stack Overflow

Row match vs affected

• match the WHERE clause

• affected row inserted, deleted, changed

• MySQL :: MySQL 5.7 Reference Manual :: 27.8.7.1 mysql_affected_rows()

• mysql/Readme.md at master · mysqljs/mysql - Node.js MySQL clientchangedRows vs affectedRows

Time

• CAST(date AS DATETIME)

• MySQL :: MySQL 5.5 Reference Manual :: 12.7 Date and Time Functions - TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01')

• php - Should I use the datetime or timestamp data type in MySQL? - Stack Overflow

Enum

FIND_IN_SET and ENUM

If number of values in set change, the col size will be recomputed

Or:

status can contains one or more (bits flags):

1 = Prospective Customer 2 = Cancelled Service 4 = Pool Service 8 = lawn mowing 16 = window washing 32 = tree trimming 64 = house painting 128 = mobile oil change

All of window washing customers even if they are cancelled:

SELECT * FROM customers WHERE (status & 16) AND !(status & 2)

All of tree trimming AND pool service customers i.e. 32+4:

SELECT * FROM customers WHERE status & 36

• MySQL :: MySQL 8.0 Reference Manual :: 12.12 Bit Function and Operators

Concatenate list as string

SET SESSION group_concat_max_len = 1000000;#max 1MB
SELECT GROUP_CONCAT(k SEPARATOR ',') FROM (SELECT CONCAT(col1, ':', col2) AS k FROM sometable WHERE condition) AS tmp

• sql - Can I concatenate multiple MySQL row into one field? - Stack Overflow

JSON with SQLite

• SQLite as a document database

Merge data

• dolthub/dolt: Dolt – It's Git for Data