Mysql syntax: Difference between revisions
From wikinotes
No edit summary |
|||
Line 3: | Line 3: | ||
<blockquote> | <blockquote> | ||
{| class="wikitable" | {| class="wikitable" | ||
|- | |||
| language structure || https://dev.mysql.com/doc/refman/5.7/en/language-structure.html | |||
|- | |- | ||
| data types || https://dev.mysql.com/doc/refman/5.7/en/data-types.html | | data types || https://dev.mysql.com/doc/refman/5.7/en/data-types.html |
Revision as of 16:36, 10 September 2022
Documentation
language structure https://dev.mysql.com/doc/refman/5.7/en/language-structure.html 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;