MySQL Summary

Quickref.me
Contents
Getting Started.......................................................................................................4
Connect MySQL............................................................................................................................ 4
Commons..................................................................................................................................... 4
Database.................................................................................................................................. 4
Table........................................................................................................................................ 4
Process..................................................................................................................................... 4
Other........................................................................................................................................ 4
Backups....................................................................................................................................... 4
MySQL Examples.....................................................................................................5
Managing tables.......................................................................................................................... 5
Querying Data from a Table......................................................................................................... 5
Querying from Multiple Tables..................................................................................................... 6
Using SQL Operators to Combine Rows from Queries..................................................................6
Querying Rows with Conditions................................................................................................... 7
Using SQL Constraints.................................................................................................................. 7
Modifying Data............................................................................................................................. 8
Managing Views........................................................................................................................... 8
Managing Triggers....................................................................................................................... 9
WHEN....................................................................................................................................... 9
EVENT....................................................................................................................................... 9
TRIGGER_TYPE.......................................................................................................................... 9
Managing Indexes........................................................................................................................ 9
MySQL Data Types.................................................................................................10
Strings....................................................................................................................................... 10
Date & Time............................................................................................................................... 10
Numeric..................................................................................................................................... 10
MySQL Functions & Operators................................................................................11
Strings....................................................................................................................................... 11
Date and Time Functions........................................................................................................... 12
Numeric Functions and Operators............................................................................................. 14
Aggregate Functions.................................................................................................................. 15
JSON Functions and Operators................................................................................................... 15
Cast Functions and Operators.................................................................................................... 16
Flow Control Functions and Operators....................................................................................... 16
Information Functions................................................................................................................ 16
Encryption and Compression Functions..................................................................................... 17
Locking Functions...................................................................................................................... 17
Bit Functions and Operators...................................................................................................... 17
Miscellaneous Functions............................................................................................................ 18
Getting Started
Connect MySQL
 mysql -u <user> -p # Connect to MySQL using username and password
 mysql [db_name] # Open a specific database after connecting
 mysql -h <host> -P <port> -u <user> -p [db_name]
# Connect to a remote MySQL server using host, port, username, and password

 mysql -h <host> -u <user> -p [db_name] # Connect to a MySQL server with specified host and database

Commons
Database
 CREATE DATABASE db; # Create a new database
 DROP DATABASE db; # Delete a database
 SHOW DATABASES; # List all databases
 USE db; # Switch to a specific database
 CONNECT db; # Alternate command to switch to a database

Table
 SHOW TABLES; # List all tables in the current database
 SHOW FIELDS FROM t; # List all fields in a specific table
 DESC t; # Display the structure of a table
 SHOW CREATE TABLE t; # Show the SQL statement to create a table
 TRUNCATE TABLE t; # Remove all data from a table
 DROP TABLE t; # Delete a table

Process
 show processlist; # Display the list of current processes
 kill pid; # Terminate a specific process by its ID

Other
 exit or \q # Exit the MySQL session

Backups
 mysqldump -u user -p db_name > db.sql # Create a backup of a database
 mysqldump -u user -p db_name --no-data=true --add-drop-table=false > db.sql # Export bd data without the schema
 mysql -u user -p db_name < db.sql # Restore a database from a backup file
MySQL Examples
Managing tables
 CREATE TABLE t ( # Create a new table named 't' with three columns
id INT, # Column 'id' as an integer
name VARCHAR DEFAULT NOT NULL, # Column 'name' as a non-null string
price INT DEFAULT 0, # Column 'price' with default value 0
PRIMARY KEY(id) # Set 'id' as the primary key
);
 DROP TABLE t; # Delete the table 't' from the database
 TRUNCATE TABLE t; # Remove all data from the table 't', keeping its structure intact
 ALTER TABLE t ADD column; # Add a new column to the table 't'
 ALTER TABLE t DROP COLUMN c; # Remove column 'c' from the table 't'
 ALTER TABLE t ADD constraint; # Add a new constraint to the table 't'
 ALTER TABLE t DROP constraint; # Remove a constraint from the table 't'
 ALTER TABLE t1 RENAME TO t2; # Rename the table 't1' to 't2'
 ALTER TABLE t1 RENAME c1 TO c2; # Rename the column 'c1' to 'c2' in the table 't1'

Querying Data from a Table

 SELECT c1, c2 FROM t; # Query specific columns 'c1' and 'c2' from the table 't'
 SELECT * FROM t; # Query all rows and columns from the table 't'
 SELECT c1, c2 FROM t # Query specific columns and filter rows based on a condition
WHERE condition;

 SELECT DISTINCT c1 FROM t # Query unique values from column 'c1' with an optional filter condition
WHERE condition;

 SELECT DISTINCT c1, c2 FROM t # Query unique values from columns 'c1' and ‘c2’, with an optional filter condition
WHERE condition;

 SELECT c1, c2 FROM t # Sort the result set by 'c1' in ascending (default) or descending order
ORDER BY c1 ASC [DESC];

 SELECT c1, c2 FROM t # Skip the specified number of rows (offset) and return at most the next 'n' rows
ORDER BY c1 # Sorts the results by column c1 in ascending order (default).
LIMIT n OFFSET offset;

 SELECT c1, aggregate(c2) # Group rows by column 'c1' and apply an aggregate function on 'c2'
FROM t # we can have more colums like c1
GROUP BY c1;

 SELECT c1, aggregate(c2) # Filter grouped rows using a condition in the HAVING clause
FROM t
GROUP BY c1
HAVING condition;
Querying from Multiple Tables
 SELECT c1, c2 # Perform an inner join between tables 't1' and 't2' based on a condition
FROM t1
INNER JOIN t2 ON condition;

 SELECT c1, c2 # Perform a left join, including all rows from 't1' and matching rows from 't2'
FROM t1
LEFT JOIN t2 ON condition;

 SELECT c1, c2 # Perform a right join, including all rows from 't2' and matching rows from 't1'
FROM t1
RIGHT JOIN t2 ON condition;

 SELECT c1, c2 # Perform a full outer join, including all rows from both tables
FROM t1
FULL OUTER JOIN t2 ON condition;

 SELECT c1, c2 # Produce a Cartesian product of rows from 't1' and 't2'
FROM t1
CROSS JOIN t2;

 SELECT c1, c2 # Another way to produce a Cartesian product of 't1' and 't2'
FROM t1, t2;

 SELECT c1, c2 # Join table 't1' to itself using an inner join clause to find relationships within the same table.
FROM t1 A
INNER JOIN t1 B ON condition;

Using SQL Operators to Combine Rows from Queries

 SELECT c1, c2 FROM t1 # Combine rows from two queries, removing duplicates unless UNION ALL is used
UNION [ALL]
SELECT c1, c2 FROM t2;

 SELECT c1, c2 FROM t1 # Return rows that are common to both queries
INTERSECT
SELECT c1, c2 FROM t2;

 SELECT c1, c2 FROM t1 # Return rows from the first query that are not in the second query
MINUS
SELECT c1, c2 FROM t2;
Querying Rows with Conditions
 SELECT c1, c2 FROM t
WHERE c1 [NOT] LIKE pattern; # Query rows using pattern matching with wildcards ('%' or '_')

 SELECT c1, c2 FROM t

WHERE c1 [NOT] IN value_list; # Query rows where 'c1' is or isn't in a specified list of values

 SELECT c1, c2 FROM t

WHERE c1 BETWEEN low AND high; # Query rows where 'c1' is between two values (inclusive)

 SELECT c1, c2 FROM t

WHERE c1 IS [NOT] NULL; # Check if a value in 'c1' is NULL or not

Using SQL Constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL)
 CREATE TABLE t( # Set columns 'c1' and 'c2' as a composite primary key which will be unique
c1 INT, c2 INT, c3 VARCHAR, # and neither c1 nor c2 can be NULL.
PRIMARY KEY (c1, c2)
);

 CREATE TABLE t1( # Set column 'c2' as a foreign key referencing 'c2' in table 't2'
c1 INT PRIMARY KEY, # and REFERENCES is a part of the FOREIGN KEY constraint in SQL
c2 INT,
FOREIGN KEY (c2) REFERENCES t2(c2)
);

 CREATE TABLE t( # Ensure the combination of values in columns 'c2' and 'c3' is unique
c1 INT, c2 INT,
UNIQUE(c2, c3)
);

 CREATE TABLE t( # Add a check constraint to ensure 'c1' is greater than 0 and 'c1' is greater than or equal to 'c2'
c1 INT, c2 INT,
CHECK(c1 > 0 AND c1 >= c2)
);

 CREATE TABLE t( # Ensure that values in column 'c2' cannot be NULL

c1 INT PRIMARY KEY,
c2 VARCHAR NOT NULL
);
Modifying Data
 INSERT INTO t(column_list)
VALUES(value_list); # Insert a single row with specified values into a table

 INSERT INTO t(column_list)

VALUES (value_list),
(value_list), …; # Insert multiple rows with specified values into a table

 INSERT INTO t1(column_list)

SELECT column_list
FROM t2; # Insert rows from table 't2' into table 't1' based on selected columns

 UPDATE t
SET c1 = new_value; # Update all rows in column 'c1' with a new value

 UPDATE t
SET c1 = new_value,
c2 = new_value
WHERE condition; # Update values in columns 'c1' and 'c2' for rows matching a condition

 DELETE FROM t; # Delete all data in the table 't'

 DELETE FROM t
WHERE condition; # Delete rows from table 't' that match a specific condition

Managing Views
 CREATE VIEW v(c1, c2)
AS
SELECT c1, c2
FROM t; # Create a new view 'v' consisting of columns 'c1' and 'c2' from table 't'

 CREATE VIEW v(c1, c2)

AS
SELECT c1, c2
FROM t
WITH [CASCADED | LOCAL] CHECK OPTION; # Create a view 'v' with a check option to enforce constraints on updates or inserts

 CREATE RECURSIVE VIEW v

AS
select-statement # Define the anchor part of the recursive query
UNION [ALL]
select-statement; # Define the recursive part of the query for the recursive view
 CREATE TEMPORARY VIEW v
AS
SELECT c1, c2
FROM t; # Create a temporary view 'v' consisting of columns 'c1' and 'c2'

 DROP VIEW view_name; # Delete a view named 'view_name' from the database

Managing Triggers
 CREATE OR MODIFY TRIGGER trigger_name # Create or modify a trigger with a specific name
WHEN EVENT # Specify when the trigger is executed (BEFORE or AFTER the event)
ON table_name TRIGGER_TYPE # Specify the table and trigger type (FOR EACH ROW or FOR EACH STATEMENT)
EXECUTE stored_procedure; # Define the action or procedure the trigger executes

WHEN
 BEFORE # Trigger is invoked before the event occurs
 AFTER # Trigger is invoked after the event occurs

EVENT
 INSERT # Trigger is invoked for INSERT operations
 UPDATE # Trigger is invoked for UPDATE operations
 DELETE # Trigger is invoked for DELETE operations

TRIGGER_TYPE
 FOR EACH ROW # Execute the trigger for each row affected by the event
 FOR EACH STATEMENT # Execute the trigger once for the entire SQL statement

Managing Indexes
 CREATE INDEX idx_name
ON t(c1, c2); # Create an index named 'idx_name' on columns 'c1' and 'c2' of the table 't'

 CREATE UNIQUE INDEX idx_name

ON t(c3, c4); # Create a unique index named 'idx_name' on columns 'c3' and 'c4' of the table 't'

 DROP INDEX idx_name; # Remove the index named 'idx_name' from the table
MySQL Data Types
Strings
 CHAR # Fixed-length string (0 - 255 characters)
 VARCHAR # Variable-length string (0 - 255 characters)
 TINYTEXT # Tiny text string (0 - 255 characters)
 TEXT # Text string (0 - 65,535 characters)
 BLOB # Binary large object, stores binary data (0 - 65,535 bytes)
 MEDIUMTEXT # Medium text string (0 - 16,777,215 characters)
 MEDIUMBLOB # Medium binary large object (0 - 16,777,215 bytes)
 LONGTEXT # Long text string (0 - 4,294,967,295 characters)
 LONGBLOB # Long binary large object (0 - 4,294,967,295 bytes)
 ENUM # String object with one value selected from a predefined list
 SET # String object with multiple values selected from a predefined list

Date & Time

 DATE # Stores date in 'yyyy-MM-dd' format
 TIME # Stores time in 'hh:mm:ss' format
 DATETIME # Stores date and time in 'yyyy-MM-dd hh:mm:ss' format
 TIMESTAMP # Stores date and time in 'yyyy-MM-dd hh:mm:ss' format (based on UTC)
 YEAR # Stores year in 'yyyy' format

Numeric
 TINYINT x # Integer with range (-128 to 127)
 SMALLINT x # Integer with range (-32,768 to 32,767)
 MEDIUMINT x # Integer with range (-8,388,608 to 8,388,607)
 INT x # Integer with range (-2,147,483,648 to 2,147,483,647)
 BIGINT x # Integer with range (-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807)
 FLOAT # Decimal number precise up to 23 digits
 DOUBLE # Decimal number precise between 24 to 53 digits
 DECIMAL # "DOUBLE" stored as a string for exact precision

MySQL Functions & Operators

Strings
 ASCII() # Returns the ASCII value of the leftmost character of a string
 BIN() # Converts a number to its binary representation
 HEX() # Converts a decimal or string to hexadecimal
 UNHEX() # Converts a hexadecimal string to a binary string
 OCT() # Converts a decimal number to octal
 FORMAT() # Formats a number to a specified number of decimal places
 CHAR() # Returns the character for each integer passed
 FROM_BASE64() # Decodes a Base64-encoded string
 TO_BASE64() # Encodes a string in Base64
 ELT() # Returns the nth element of a list of strings
 FIELD() # Returns the index of a value in a list of values
 INSERT() # Inserts a string into another string at a specified position
 LCASE() # Converts a string to lowercase
 UCASE() # Converts a string to uppercase
 LOWER() # Alias for LCASE()
 UPPER() # Alias for UCASE()
 LEFT() # Returns the leftmost characters of a string
 RIGHT() # Returns the rightmost characters of a string
 ORD() # Returns the code of the leftmost character of a string
 LIKE # Used to search for a pattern in a string
 RLIKE # Alias for REGEXP
 SOUNDS LIKE # Compares strings based on sound similarity
 REGEXP # Performs regular expression matching
 NOT LIKE # Negates the LIKE operator
 NOT REGEXP # Negates the REGEXP operator
 MATCH # Performs a full-text search
 LPAD() # Pads the left side of a string with a specified string
 RPAD() # Pads the right side of a string with a specified string
 RTRIM() # Removes trailing spaces from a string
 LTRIM() # Removes leading spaces from a string
 TRIM() # Removes leading and trailing spaces from a string
 EXPORT_SET() # Returns a string representation of a bitfield
 FIND_IN_SET() # Returns the position of a string within a list of strings
 MAKE_SET() # Returns a string made up of set values
 POSITION() # Returns the position of a substring in a string
 LOCATE() # Returns the position of the first occurrence of a substring
 QUOTE() # Quotes a string as a literal for use in SQL
 REGEXP_INSTR() # Returns the position of the first match of a regular expression
 REGEXP_LIKE() # Tests whether a string matches a regular expression
 REGEXP_REPLACE() # Replaces occurrences of a regular expression in a string
 REGEXP_SUBSTR() # Returns a substring that matches a regular expression
 SPACE() # Returns a string of spaces of a specified length
 INSTR() # Returns the position of the first occurrence of a substring
 MID() # Returns a substring starting at a specified position
 STRCMP() # Compares two strings
 SUBSTR() # Alias for SUBSTRING()
 SUBSTRING() # Returns a substring from a string
 CONCAT() # Concatenates strings
 CONCAT_WS() # Concatenates strings with a separator
 SUBSTRING_INDEX() # Returns a substring based on a delimiter
 BIT_LENGTH() # Returns the length of a string in bits
 CHAR_LENGTH() # Alias for CHARACTER_LENGTH()
 CHARACTER_LENGTH() # Returns the number of characters in a string
 LENGTH() # Returns the length of a string in bytes
 OCTET_LENGTH() # Returns the length of a string in bytes
 WEIGHT_STRING() # Returns the weight string of a string for sorting
 LOAD_FILE() # Reads a file and returns its contents
 REPEAT() # Repeats a string a specified number of times
 REPLACE() # Replaces occurrences of a substring in a string
 REVERSE() # Reverses a string
 SOUNDEX() # Returns the soundex value of a string

Date and Time Functions

 ADDDATE() # Adds a specified number of days to a date
 ADDTIME() # Adds a specified time interval to a time
 CONVERT_TZ() # Converts a date and time to a different time zone
 CURDATE() # Returns the current date
 CURRENT_DATE() # Alias for CURDATE(), returns the current date
 CURRENT_TIME() # Returns the current time
 CURRENT_TIMESTAMP() # Returns the current date and time
 CURTIME() # Returns the current time
 DATE() # Extracts the date part of a datetime expression
 DATE_ADD() # Adds a specified interval to a date
 DATE_FORMAT() # Formats a date according to a specified format
 DATE_SUB() # Subtracts a specified interval from a date
 DATEDIFF() # Calculates the difference in days between two dates
 DAY() # Returns the day of the month for a date
 DAYNAME() # Returns the name of the day for a date
 DAYOFMONTH() # Returns the day of the month for a date
 DAYOFWEEK() # Returns the weekday index (1 for Sunday) for a date
 DAYOFYEAR() # Returns the day of the year for a date
 EXTRACT() # Extracts a part of a date (e.g., year, month, day)
 FROM_DAYS() # Converts a day number to a date
 FROM_UNIXTIME() # Converts a UNIX timestamp to a date and time
 GET_FORMAT() # Returns the format for date and time based on a locale
 HOUR() # Extracts the hour from a time
 LAST_DAY() # Returns the last day of the month for a date
 LOCALTIME() # Alias for CURRENT_TIMESTAMP()
 LOCALTIMESTAMP() # Alias for CURRENT_TIMESTAMP()
 MAKEDATE() # Creates a date from a year and a day-of-year value
 MAKETIME() # Creates a time from hour, minute, and second values
 MICROSECOND() # Returns the microsecond part of a time value
 MINUTE() # Extracts the minute from a time
 MONTH() # Returns the month part of a date
 MONTHNAME() # Returns the name of the month for a date
 NOW() # Returns the current date and time
 PERIOD_ADD() # Adds a number of months to a period
 PERIOD_DIFF() # Calculates the difference between two periods in months
 QUARTER() # Returns the quarter of the year for a date
 SEC_TO_TIME() # Converts seconds to 'hh:mm:ss' format
 SECOND() # Extracts the second from a time
 STR_TO_DATE() # Converts a string to a date based on a format
 SUBDATE() # Subtracts a number of days or a time interval from a date
 SUBTIME() # Subtracts a time interval from a time
 SYSDATE() # Returns the current date and time (evaluated at the time of function call)
 TIME() # Extracts the time part of a datetime expression
 TIME_FORMAT() # Formats a time according to a specified format
 TIME_TO_SEC() # Converts a time value to seconds
 TIMEDIFF() # Calculates the difference between two time values
 TIMESTAMP() # Returns a datetime value from a date or datetime expression
 TIMESTAMPADD() # Adds an interval to a datetime value
 TIMESTAMPDIFF() # Calculates the difference between two datetime values
 TO_DAYS() # Converts a date to the number of days since year 0
 TO_SECONDS() # Converts a datetime to the number of seconds since year 0
 UNIX_TIMESTAMP() # Returns the UNIX timestamp for a date or the current date and time
 UTC_DATE() # Returns the current UTC date
 UTC_TIME() # Returns the current UTC time
 UTC_TIMESTAMP() # Returns the current UTC date and time
 WEEK() # Returns the week number for a date
 WEEKDAY() # Returns the weekday index (0 for Monday) for a date
 WEEKOFYEAR() # Returns the calendar week of the year for a date
 YEAR() # Returns the year part of a date
 YEARWEEK() # Returns the year and week number for a date
 GET_FORMAT() # Returns a format string for date and time based on locale settings

Numeric Functions and Operators

 %, MOD # Modulus operator, returns the remainder of a division
 * # Multiplication operator
 + # Addition operator
 - # Subtraction operator
 / # Division operator
 ABS() # Returns the absolute value of a number
 ACOS() # Returns the arccosine of a number
 ASIN() # Returns the arcsine of a number
 ATAN() # Returns the arctangent of a number
 ATAN2(), ATAN() # Returns the arctangent of two variables
 CEIL() # Rounds a number up to the nearest integer
 CEILING() # Alias for CEIL()
 CONV() # Converts a number from one base to another
 COS() # Returns the cosine of a number
 COT() # Returns the cotangent of a number
 CRC32() # Computes a cyclic redundancy check value for a string
 DEGREES() # Converts radians to degrees
 DIV # Integer division operator
 EXP() # Returns e raised to the power of a number
 FLOOR() # Rounds a number down to the nearest integer
 LN() # Returns the natural logarithm of a number
 LOG() # Returns the natural logarithm or logarithm to a specified base
 LOG10() # Returns the base-10 logarithm of a number
 LOG2() # Returns the base-2 logarithm of a number
 MOD() # Alias for %, returns the remainder of a division
 PI() # Returns the value of π (pi)
 POW() # Returns a number raised to the power of another number
 POWER() # Alias for POW()
 RADIANS() # Converts degrees to radians
 RAND() # Returns a random number between 0 and 1
 ROUND() # Rounds a number to a specified number of decimal places
 SIGN() # Returns the sign of a number (-1, 0, or 1)
 SIN() # Returns the sine of a number
 SQRT() # Returns the square root of a number
 TAN() # Returns the tangent of a number
 TRUNCATE() # Truncates a number to a specified number of decimal places

Aggregate Functions
 AVG() # Returns the average value of a numeric column
 BIT_AND() # Performs a bitwise AND operation across all values in a column
 BIT_OR() # Performs a bitwise OR operation across all values in a column
 BIT_XOR() # Performs a bitwise XOR operation across all values in a column
 COUNT() # Counts the number of rows in a result set
 COUNT(DISTINCT) # Counts the number of distinct values in a column
 GROUP_CONCAT() # Concatenates values from a group into a single string
 JSON_ARRAYAGG() # Aggregates values into a JSON array
 JSON_OBJECTAGG() # Aggregates key-value pairs into a JSON object
 MAX() # Returns the maximum value in a column
 MIN() # Returns the minimum value in a column
 STD() # Alias for STDDEV(), returns the standard deviation
 STDDEV() # Returns the standard deviation of values in a column
 STDDEV_POP() # Returns the population standard deviation
 STDDEV_SAMP() # Returns the sample standard deviation
 SUM() # Returns the sum of values in a column
 VAR_POP() # Returns the population variance
 VAR_SAMP() # Returns the sample variance
 VARIANCE() # Alias for VAR_POP(), returns the population variance

JSON Functions and Operators

 -> # Access a value in a JSON object using a key (returns as JSON)
 ->> # Access a value in a JSON object using a key (returns as plain text)
 JSON_ARRAY() # Creates a JSON array from a list of values
 JSON_ARRAY_APPEND() # Appends values to an existing JSON array
 JSON_ARRAY_INSERT() # Inserts values into a JSON array at a specified position
 JSON_CONTAINS() # Checks if a JSON document contains a specific value
 JSON_CONTAINS_PATH() # Checks if a specific path exists in a JSON document
 JSON_DEPTH() # Returns the maximum depth of a JSON document
 JSON_EXTRACT() # Extracts data from a JSON document using a path
 JSON_INSERT() # Inserts data into a JSON document, if the path does not exist
 JSON_KEYS() # Returns the keys in a JSON object
 JSON_LENGTH() # Returns the number of elements in a JSON document
 JSON_MERGE() # (Deprecated) Merges multiple JSON documents
 JSON_MERGE_PATCH() # Merges JSON documents by overwriting existing values
 JSON_MERGE_PRESERVE() # Merges JSON documents while preserving existing values
 JSON_OBJECT() # Creates a JSON object from key-value pairs
 JSON_OVERLAPS() # (Introduced 8.0.17) Checks if two JSON documents overlap
 JSON_PRETTY() # Returns a pretty-printed JSON document
 JSON_QUOTE() # Quotes a string as a JSON value
 JSON_REMOVE() # Removes data from a JSON document at specified paths
 JSON_REPLACE() # Replaces data in a JSON document at specified paths
 JSON_SCHEMA_VALID() # (Introduced 8.0.17) Validates a JSON document against a schema
 JSON_SCHEMA_VALIDATION_REPORT() # (Introduced 8.0.17) Returns a validation report for a JSON schema
 JSON_SEARCH() # Searches for a value in a JSON document and returns the path
 JSON_SET() # Updates or adds values to a JSON document
 JSON_STORAGE_FREE() # Returns the amount of free space for a JSON column
 JSON_STORAGE_SIZE() # Returns the size of the JSON document storage
 JSON_TABLE() # Creates a relational view of JSON data
 JSON_TYPE() # Returns the type of a JSON value
 JSON_UNQUOTE() # Removes quotes from a JSON string
 JSON_VALID() # Checks if a string is a valid JSON document
 JSON_VALUE() # (Introduced 8.0.21) Extracts a scalar value from a JSON document
 MEMBER OF() # (Introduced 8.0.17) Checks if a value is a member of a JSON array
Cast Functions and Operators
 BINARY # Converts a value to a binary string
 CAST() # Converts a value to a specified data type
 CONVERT() # Converts a value to a specified character set or data type

Flow Control Functions and Operators

 CASE # Evaluates conditions and returns a value based on the first condition met
 IF() # Returns a value if a condition is TRUE, otherwise returns a different value
 IFNULL() # Returns a specified value if the first value is NULL, otherwise returns the first value
 NULLIF() # Returns NULL if two values are equal, otherwise returns the first value

Information Functions
 BENCHMARK() # Executes an expression repeatedly for performance testing
 CHARSET() # Returns the character set of a string
 COERCIBILITY() # Returns the coercibility value of a string
 COLLATION() # Returns the collation of a string
 CONNECTION_ID() # Returns the unique connection ID for the current session
 CURRENT_ROLE() # Returns the active role for the current session
 CURRENT_USER() # Returns the authenticated user name and host name
 DATABASE() # Returns the name of the current database
 FOUND_ROWS() # Returns the number of rows found by the last SELECT query
 ICU_VERSION() # Returns the version of the ICU library used
 LAST_INSERT_ID() # Returns the value of the last AUTO_INCREMENT column inserted
 ROLES_GRAPHML() # Returns a GraphML representation of role relationships
 ROW_COUNT() # Returns the number of rows affected by the last statement
 SCHEMA() # Alias for DATABASE(), returns the name of the current database
 SESSION_USER() # Returns the current session user name and host name
 SYSTEM_USER() # Alias for USER(), returns the user name and host name of the connected user
 USER() # Returns the user name and host name of the connected user
 VERSION() # Returns the version of the MySQL server

Encryption and Compression Functions

 AES_DECRYPT() # Decrypts data encrypted using AES
 AES_ENCRYPT() # Encrypts data using AES
 COMPRESS() # Compresses a string using zlib
 MD5() # Calculates the MD5 hash of a string
 RANDOM_BYTES() # Generates a random binary string of a specified length
 SHA1(), SHA() # Calculates the SHA-1 hash of a string
 SHA2() # Calculates the SHA-2 hash of a string
 STATEMENT_DIGEST() # Computes a digest of an SQL statement
 STATEMENT_DIGEST_TEXT() # Returns a human-readable digest of an SQL statement
 UNCOMPRESS() # Decompresses a string compressed with COMPRESS()
 UNCOMPRESSED_LENGTH() # Returns the length of the original string before compression
 VALIDATE_PASSWORD_STRENGTH() # Validates the strength of a given password

Locking Functions
 GET_LOCK() # Acquires a named lock with a timeout
 IS_FREE_LOCK() # Checks if a named lock is available
 IS_USED_LOCK() # Checks which connection is using a specific named lock
 RELEASE_ALL_LOCKS() # Releases all locks held by the current connection
 RELEASE_LOCK() # Releases a specific named lock

Bit Functions and Operators

 & # Bitwise AND operator
 >> # Bitwise right shift operator
 << # Bitwise left shift operator
 ^ # Bitwise XOR (exclusive OR) operator
 BIT_COUNT() # Returns the number of bits set to 1 in a binary representation of a number
 | # Bitwise OR operator
 ~ # Bitwise NOT operator

Miscellaneous Functions
 ANY_VALUE() # Returns a value from a group, bypassing ONLY_FULL_GROUP_BY restrictions
 BIN_TO_UUID() # Converts a binary UUID to a string UUID
 DEFAULT() # Returns the default value for a column
 GROUPING() # Indicates whether a column is aggregated in a GROUP BY query
 INET_ATON() # Converts an IPv4 address from string to numeric format
 INET_NTOA() # Converts a numeric IPv4 address to string format
 INET6_ATON() # Converts an IPv6 address from string to binary format
 INET6_NTOA() # Converts a binary IPv6 address to string format
 IS_IPV4() # Checks if a string is a valid IPv4 address
 IS_IPV4_COMPAT() # Checks if an address is an IPv4-compatible IPv6 address
 IS_IPV4_MAPPED() # Checks if an address is an IPv4-mapped IPv6 address
 IS_IPV6() # Checks if a string is a valid IPv6 address
 IS_UUID() # Checks if a string is a valid UUID
 MASTER_POS_WAIT() # Waits until the slave has read up to a specific position in the master binary log
 NAME_CONST() # Creates a constant with a specified name and value
 SLEEP() # Causes the session to sleep for a specified number of seconds
 UUID() # Generates a Universal Unique Identifier (UUID)
 UUID_SHORT() # Generates a short, numeric UUID
 UUID_TO_BIN() # Converts a string UUID to binary format
 VALUES() # Returns values from the most recent INSERT or UPDATE statement

6. Adding Constraints to an Existing Table

Query:

-- Add a NOT NULL constraint

ALTER TABLE Products

MODIFY ProductName VARCHAR(100) NOT NULL;

-- Add a UNIQUE constraint

ALTER TABLE Users

ADD CONSTRAINT unique_username UNIQUE (Username);

-- Add a CHECK constraint

ALTER TABLE Salaries

ADD CONSTRAINT check_salary CHECK (Bonus <= BaseSalary);

-- Add a Foreign Key

ALTER TABLE Orders

ADD CONSTRAINT fk_customer FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);