Mysql syntax

From wikinotes
Revision as of 16:54, 10 September 2022 by Will (talk | contribs) (→‎Documentation)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

Documentation

ALTER/CREATE/RENAME docs https://dev.mysql.com/doc/refman/5.7/en/sql-data-definition-statements.html
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;