Mysql syntax: Difference between revisions
From wikinotes
(→UUIDs) |
No edit summary |
||
Line 1: | Line 1: | ||
= Documentation = | |||
<blockquote> | |||
{| class="wikitable" | |||
|- | |||
| data types || https://dev.mysql.com/doc/refman/5.7/en/data-types.html | |||
|- | |||
| functions and operators || https://dev.mysql.com/doc/refman/5.7/en/functions.html | |||
|- | |||
|} | |||
</blockquote><!-- Documentation --> | |||
= Basics = | = Basics = | ||
Line 15: | Line 26: | ||
# list columns in 'my_table' for currently selected database | # list columns in 'my_table' for currently selected database | ||
SELECT column_name | SELECT column_name | ||
FROM information_schema.columns | FROM information_schema.columns | ||
WHERE table_name = 'my_table' | WHERE table_name = 'my_table' | ||
AND TABLE_SCHEMA IN (SELECT DATABASE()); | AND TABLE_SCHEMA IN (SELECT DATABASE()); | ||
Line 61: | Line 72: | ||
# ... # | # ... # | ||
CREATE TABLE mytable( | CREATE TABLE mytable( | ||
id BINARY(16), ## store the UUID as binary | id BINARY(16), ## store the UUID as binary | ||
id_text VARCHAR(36) ## virtual function that builds text-form UUID | id_text VARCHAR(36) ## virtual function that builds text-form UUID | ||
(insert( insert( insert( insert(hex(id_bin),9,0,'-'), ## dynamically for displayed rows. | (insert( insert( insert( insert(hex(id_bin),9,0,'-'), ## dynamically for displayed rows. | ||
14,0,'-'), 19,0,'-'), 24,0,'-')) virtual, | 14,0,'-'), 19,0,'-'), 24,0,'-')) virtual, | ||
Line 92: | Line 103: | ||
# CREATE PROCEDURE | # CREATE PROCEDURE | ||
# ================ | # ================ | ||
DELIMITER // # Redefine what marks end of command From ';' to '//'(so you can use ;) | DELIMITER // # Redefine what marks end of command From ';' to '//'(so you can use ;) | ||
CREATE PROCEDURE test( # Create Procedure | CREATE PROCEDURE test( # Create Procedure | ||
IN Id INT, | IN Id INT, | ||
OUT stat CHAR(255)) # CHARs need a fixed bit-width here, or else assumes 1. | OUT stat CHAR(255)) # CHARs need a fixed bit-width here, or else assumes 1. | ||
BEGIN | BEGIN | ||
SELECT modeling | SELECT modeling | ||
INTO stat # Save results into outVariable 'stat' | INTO stat # Save results into outVariable 'stat' | ||
FROM assetTableMaya | FROM assetTableMaya | ||
WHERE asset_Id = Id; # Use inVariable 'Id' | WHERE asset_Id = Id; # Use inVariable 'Id' | ||
END // # Mark end of procedure, and End of SQL command ('//') | END // # Mark end of procedure, and End of SQL command ('//') | ||
DELIMITER ; # Change Delimiter back to ';' | DELIMITER ; # Change Delimiter back to ';' |
Revision as of 16:35, 10 September 2022
Documentation
data types https://dev.mysql.com/doc/refman/5.7/en/data-types.html functions and operators https://dev.mysql.com/doc/refman/5.7/en/functions.html
Basics
See SQL for an overview of SQL.
Database/Table Info
SHOW DATABASES; # show all databases on server SHOW TABLES; # show all tables DESCRIBE pet; # show pet table schema SHOW INDEX FROM pet; # shows indexed columns on table pet # list columns in 'my_table' for currently selected database SELECT column_name FROM information_schema.columns WHERE table_name = 'my_table' AND TABLE_SCHEMA IN (SELECT DATABASE());
Datatypes
MySQL has some quirks when creating certain datatypes (like MySQL DATETIME types). The following is a quick reference for converting between types.
DATETIME
# datetime (utc implied) STR_TO_DATE('2016-11-08 08:57:46', '%Y-%m-%d %H:%i:%S') # datetime in UTC SELECT CONVERT_TZ(STR_TO_DATE('2020-08-24 14:40:00', '%Y-%m-%d %H:%i:%S'), '+00:00', 'system') # queries can be written as a string SELECT * FROM `x` WHERE '2020-08-24 14:00:00' < `date_modified`;from datetime import datetime now = datetime.now() now.strftime( 'STR_TO_DATE("%Y-%m-%d %H:%M:%S", "%%Y-%%m-%%d %%H:%%i:%%S")' )UUIDs
Storing UUIDs in a mysql database can be done *much* more efficiently than storing a varchar(36). You can strip the dashes, and store it (safely) as 16-bit binary. See this incredibly insightful blog post from the mysql team: http://mysqlserverteam.com/storing-uuid-values-in-mysql-tables/
SELECT UUID(); ## aab5d5fd-70c1-11e5-a4fb-b026b977eb28 SELECT UNHEX( REPLACE( UUID(), '-', '' )); ## convert uuid to 16bit binary # ... # CREATE TABLE mytable( id BINARY(16), ## store the UUID as binary id_text VARCHAR(36) ## virtual function that builds text-form UUID (insert( insert( insert( insert(hex(id_bin),9,0,'-'), ## dynamically for displayed rows. 14,0,'-'), 19,0,'-'), 24,0,'-')) virtual, some_value TINYTEXT, ## some arbitrary column... );JSON
https://dev.mysql.com/doc/refman/5.7/en/json.html
SELECT my_json_column->>'$.my_key' FROM my_table;
Procedures
# ================ # CREATE PROCEDURE # ================ DELIMITER // # Redefine what marks end of command From ';' to '//'(so you can use ;) CREATE PROCEDURE test( # Create Procedure IN Id INT, OUT stat CHAR(255)) # CHARs need a fixed bit-width here, or else assumes 1. BEGIN SELECT modeling INTO stat # Save results into outVariable 'stat' FROM assetTableMaya WHERE asset_Id = Id; # Use inVariable 'Id' END // # Mark end of procedure, and End of SQL command ('//') DELIMITER ; # Change Delimiter back to ';' CALL test(6,@var); # Run Procedure SELECT @var; ## MANAGE PROCEDURES # all created procedures are saved with the database. # You can get a list of them with the following command. # SHOW PROCEDURE STATUS; # List of all procedures SHOW CREATE PROCEDURE test; # display command contained in procedure DROP PROCEDURE test; # Delete Procedure
Variables
### Sample Query with variable SET @col = 'texturing'; SET @sql = CONCAT('SELECT ', @col, ' FROM assetTableMaya'); PREPARE cmd FROM @sql; EXECUTE cmd; ### Query With Variable Column Names # This is the only way that I know of to match variable # column names without processing on the back-end in python/mel. # this is super noteworthy. ### Non-Working Attempt (Standard query) : SELECT ( SELECT department FROM taskTableMaya WHERE task_Id = 1173) # SELECT 'texturing' FROM assetTableMaya # FROM assetTableMaya WHERE ( SELECT tracking_Id FROM taskTableMaya WHERE task_Id = 1173 ) = asset_Id; # WHERE taskTableMaya.tracking_Id = assetTableMaya.asset_Id ### Working Example (with variables): SET @dept= (SELECT department FROM taskTableMaya WHERE task_Id = 1173); SET @trackId= (SELECT tracking_Id FROM taskTableMaya WHERE task_Id = 1173); SET @sql = CONCAT('SELECT ', @dept, ' FROM assetTableMaya WHERE asset_Id = ', @trackId ); PREPARE cmd FROM @sql; EXECUTE cmd;