• Developer Advocate para o Google Cloud • Foco em bancos de dados • Engenheira de Dados (Data Engineer) • Desenvolvedora PHP/Ruby/JavaScript por muito tempo. • SQL Nerd
10 to 100M rows or around 100MB/table • Now supports terabyte-sized databases • Supports SQL standards as new as SQL 2016 • But... some stuff from SQL 2003 just became available (Window Functions, CTEs) • What is new? 23 OF MAY 1995 GPL V2
support • Accent Insensitive • Case Insensitive • No more = bug • Caused by utf8mb4_general_ci or utf8mb4_unicode_ci More information on how collations behave here.
permitted, except columns with the AUTO_INCREMENT attribute. • Literals, deterministic built-in functions, and operators are permitted. • Non-deterministic built-in functions (such as AVG, COUNT, RAND, LAST_INSERT_ID, FIRST_VALUE, LAST_VALUE, …) are not permitted. • Sub-queries are not permitted.
CURRENT_DATE, …) are not permitted. • Variables (system variables, user-defined variables, and stored program local variables) are not permitted. • Stored functions and user-defined functions are not permitted.
or DESC when defining an index However... An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.
SELECT * FROM users WHERE username LIKE 'g%' ORDER BY username DESC; -- OR WITH THE SAME COST SELECT * FROM users WHERE username LIKE 'g%' ORDER BY username;
unsigned NO PRI auto_increment `username` varchar(255) NO `created_at` timestamp NO CURRENT_TIMESTAMP DEFAULT_GENERATED `updated_at` timestamp NO CURRENT_TIMESTAMP DEFAULT_GENERATED on update CURRENT_TIMESTAMP
Visible by default, to create an invisible index: ALTER TABLE users ADD INDEX ix_username (username) INVISIBLE; ALTER TABLE users ALTER INDEX ix_username INVISIBLE; ALTER TABLE users ALTER INDEX ix_username VISIBLE; • Toggle visibility:
• Must be appending a column to a table • Must not have a DEFAULT on the new column • Rename Table • Modify Columns • Virtual Columns • SET/DROP DEFAULT value of a column
NOT NULL AUTO_INCREMENT, `user_id` bigint(20) UNSIGNED NOT NULL, `status` varchar(20) NOT NULL DEFAULT 'new', `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL )
id, user_id, status, LAG(created_at) OVER(ORDER BY created_at) AS previous_order, created_at, LEAD(created_at) OVER(ORDER BY created_at) AS next_order FROM orders WHERE user_id = 654321 ORDER BY created_at LIMIT 10;
AS previous_order, created_at, LEAD(created_at) OVER(ORDER BY created_at) AS next_order FROM orders WHERE user_id = 654321 ORDER BY created_at LIMIT 10;
previous_order, created_at, LEAD(created_at) OVER(dates) AS next_order FROM orders WINDOW dates AS (ORDER BY created_at) WHERE user_id = 654321 ORDER BY created_at LIMIT 10;
AS ( SELECT 1, CAST('root' AS CHAR(255)), CAST('root' AS CHAR(65535)), 0 UNION ALL SELECT tree.depth_level + 1, categories.name, CONCAT_WS('/', tree.path, categories.name), categories.id FROM tree INNER JOIN categories ON tree.node_id = categories.parent_category_id WHERE tree.depth_level < 5 ) SELECT * FROM tree ORDER BY path;
for each user SELECT users.id, users.username, (SELECT id FROM orders WHERE users.id = user_id ORDER BY total LIMIT 1) AS order_id, (SELECT total FROM orders WHERE users.id = user_id ORDER BY total LIMIT 1) AS order_total FROM users ORDER BY users.id LIMIT 10;
order_total FROM users, LATERAL( SELECT id, total FROM orders WHERE users.id = user_id ORDER BY total LIMIT 1 ) AS total_orders ORDER BY users.id LIMIT 10;
gabidavila
itkq
inesmontani
sioncojp
oracle4engineer
shotashiratori
baseballyama
tamaiyutaro
trycycle
ks91
shoota
sansantech
geoffreycrofte
jeffersonlam
shpigford
lara
hatefulcrawdad
tanoku
zakiwarfel
wjessup
samanthasiow
swwweet
bluesmoon
kevinliebholz
![@gabidavila Common Table Expressions • Similar to CREATE [TEMPORARY] TABLE](https://files.speakerdeck.com/presentations/c9d9ca031e0d438eba928267d64eb960/slide_64.jpg){kind=link}
