Skip to content

Latest commit

 

History

History
1916 lines (1746 loc) · 43.5 KB

MySQL-Learning-Materials.md

File metadata and controls

1916 lines (1746 loc) · 43.5 KB
Home | Database |

Database : MySQL Learning Materials

Syntax Legend

DataTypes

Summary : in this tutorial, you will learn about  **MySQL data types **and how to use them effectively in designing database in MySQL.

A database table contains multiple columns with specific data types such as numeric or string. MySQL provides more data types other than just numeric or string. Each data type in MySQL can be determined by the following characteristics:

  • The kind of values it represents.
  • The space that takes up and whether the values is a fixed-length or variable length.
  • The values of the data type can be indexed or not.
  • How MySQL compares the values of a specific data type.

https://sp.mysqltutorial.org/wp-content/uploads/0211/03/MySQL-Data-Types.jpg

MySQL numeric data types

In MySQL, you can find all SQL standard numeric types including exact number data type and approximate numeric data types including integer, fixed-point and floating point. In addition, MySQL also has BIT  data type for storing bit values. Numeric types can be signed or unsigned except for the BIT type.

The following table shows the summary of numeric types in MySQL:

Numeric Types Description
TINYINT A very small integer
SMALLINT A small integer
MEDIUMINT A medium-sized integer
INT A standard integer
BIGINT A large integer
DECIMAL A fixed-point number
FLOAT A single-precision floating point number
DOUBLE A double-precision floating point number
BIT A bit field

MySQL Boolean data type

MySQL does not have the built-in BOOLEAN or BOOL data type. To represent Boolean values, MySQL uses the smallest integer type which is TINYINT(1). In other words,  BOOLEAN and BOOL are synonyms for TINYINT(1).

MySQL String data types

In MySQL, a string can hold anything from plain text to binary data such as images or files. Strings can be compared and searched based on pattern matching by using the LIKE operator,  regular expression, and full-text search.

The following table shows the string data types in MySQL:

String Types Description
CHAR A fixed-length nonbinary (character) string
VARCHAR A variable-length non-binary string
BINARY A fixed-length binary string
VARBINARY A variable-length binary string
TINYBLOB A very small BLOB (binary large object)
BLOB A small BLOB
MEDIUMBLOB A medium-sized BLOB
LONGBLOB A large BLOB
TINYTEXT A very small non-binary string
TEXT A small non-binary string
MEDIUMTEXT A medium-sized non-binary string
LONGTEXT A large non-binary string
ENUM An enumeration; each column value may be assigned one enumeration member
SET A set; each column value may be assigned zero or more SET members

MySQL date and time data types

MySQL provides types for date and time as well as the combination of date and time. In addition, MySQL supports  timestamp data type for tracking the changes in a row of a table. If you just want to store the year without date and month, you can use the YEAR data type.

The following table illustrates the MySQL date and time data types:

Date and Time Types Description
DATE A date value in CCYY-MM-DD format
TIME A time value in hh:mm:ss format
DATETIME A date and time value in CCYY-MM-DD hh:mm:ssformat
TIMESTAMP A timestamp value in CCYY-MM-DD hh:mm:ss format
YEAR A year value in CCYY or YYformat

MySQL spatial data types

MySQL supports many spatial data types that contain various kinds of geometrical and geographical values as shown in the following table:

Spatial Data Types Description
GEOMETRY A spatial value of any type
POINT A point (a pair of X-Y coordinates)
LINESTRING A curve (one or more POINTvalues)
POLYGON A polygon
GEOMETRYCOLLECTION A collection of GEOMETRYvalues
MULTILINESTRING A collection of LINESTRINGvalues
MULTIPOINT A collection of POINTvalues
MULTIPOLYGON A collection of POLYGONvalues

JSON data type

MySQL supported a native JSON data type since version 5.7.8 that allows you to store and manage JSON documents more efficiently. The native JSON data type provides automatic validation of JSON documents and optimal storage format.

Query Statement

SELECT Clause

The SELECT statement allows you to read data from one or more tables.

Syntax
SELECT
     [ALL | DISTINCT | DISTINCTROW ]
     [HIGH_PRIORITY]
     [STRAIGHT_JOIN]
     [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
     [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
     select_expr [, select_expr] ...
     [into_option]
     [FROM table_references
        [PARTITION partition_list]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

into_option: {
      INTO OUTFILE 'file_name'
      [CHARACTER SET charset_name]
      export_options
   | INTO DUMPFILE 'file_name'
   | INTO var_name [, var_name] ...
}

UPDATE Clause

The UPDATE statement modifies existing data in a table. You can also use the UPDATE statement change values in one or more columns of a single row or multiple rows.

Single-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET assignment_list
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

assignment:
   col_name = value

assignment_list:
   assignment [, assignment] ...

Multiple-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET assignment_list
[WHERE where_condition]

DELETE Clause

To delete data from a table, you use the MySQL DELETE statement. 

Syntax

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

INSERT Clause

The INSERT statement allows you to insert one or more rows into a table. 

Syntax

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
     [INTO] tbl_name
     [PARTITION (partition_name [, partition_name] ...)]
     [(col_name [, col_name] ...)]
     { {VALUES | VALUE} (value_list) [, (value_list)] ...
       |
       VALUES row_constructor_list
     }
     [AS row_alias[(col_alias [, col_alias] ...)]]
     [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
     [INTO] tbl_name
     [PARTITION (partition_name [, partition_name] ...)]
     [AS row_alias[(col_alias [, col_alias] ...)]]
     SET assignment_list
     [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    [AS row_alias[(col_alias [, col_alias] ...)]]
    {SELECT ... | TABLE table_name}
    [ON DUPLICATE KEY UPDATE assignment_list]

value:
     {expr | DEFAULT}

value_list:
     value [, value] ...

row_constructor_list:
     ROW(value_list)[, ROW(value_list)][, ...]

assignment:
     col_name = [row_alias.]value

assignment_list:
     assignment [, assignment] ...

WHERE Clause

The WHERE clause allows you to specify a search condition for the rows returned by a query.

Syntax

[WHERE where_condition]

GROUP BY Clause

The GROUP BY clause groups a set of rows into a set of summary rows by values of columns or expressions. The GROUP BY clause returns one row for each group. In other words, it reduces the number of rows in the result set.

Syntax

[GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]

HAVING Clause

The   HAVING clause is used in the SELECT statement to specify filter conditions for a group of rows or aggregates.

Syntax

[HAVING where_condition]

ORDER BY Clause

Syntax

[ORDER BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]

BETWEEN

The BETWEEN operator is a logical operator that allows you to specify whether a value in a range or not.

Syntax

expr [NOT] BETWEEN begin_expr AND end_expr;

Intermediate

Variables

User-Defined Variables

To create a user-defined variable, you use the format  @variable_name, where the  variable_name consists of alphanumeric characters. The maximum length of the user-defined variable is 64 characters.  The user-defined variables are not case-sensitive. It means that the  @id and  @ID are the same.  You can assign the user-defined variable to a certain data types such as  integer, floating point,  decimal, string or  NULL. A user-defined variable defined by one client is not visible by other clients. In other words, an user-defined variable is session-specific.

Note: that the user-defined variables are the MySQL-specific extension to SQL standard. They may not be available in other database systems.

Variable Assignment

There are two ways to assign a value to a user-defined variable. The first way is to use the  SET statement as follows:

SET @variable_name := value;

You can use either := or = as the assignment operator in the SET statement. For example, the statement assigns number 100 to the variable @counter.

SET @counter := 100;

The second way to assign a value to a variable is to use the  SELECT statement. In this case, you must use the := assignment operator because, within the SELECT statement, MySQL treats the = operator as the equal operator.

SELECT @variable_name := value;

Variable Examples

The following statement gets the most expensive product in the products table and assigns the price to the user-defined variable @msrp:

SELECT @msrp:=MAX(msrp)
FROM  products;

The following statement uses the @msrp variable to query the information of the most expensive product.

SELECT productCode, productName, productLine, msrp
FROM products
WHERE msrp = @msrp;

Sometimes, you want to insert a row into a table, get the last insert id, and use it for inserting data into another table. In this case, you can use the user-defined variable to store the most recent id generated by an  AUTO_INCREMENT column as follows.

SELECT @id:=LAST_INSERT_ID();

A user-defined variable can hold a single value only. If the SELECT statement returns multiple values, the variable will take the value of the last row in the result.

SELECT  @buyPrice:=buyprice
FROM    products
WHERE   buyprice > 95
ORDER BY buyprice;

Advanced

Functions

Create

CREATE 
    [DEFINER = user]
    FUNCTION function_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic:
    COMMENT 'string'
    | LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }

routine_body:
    Valid SQL routine statement>

Alter

ALTER FUNCTION func_name [characteristic ...]
    characteristic:
    COMMENT 'string'
    | LANGUAGE SQL
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }

Drop / Existing Check

DROP FUNCTION [IF EXISTS] function_name;

Stored Procedure

Create

CREATE 
    [DEFINER = user]
    PROCEDURE procedure_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

proc_parameter:
     [ IN | OUT | INOUT ] param_name type

func_parameter:
     param_name type

type:
     Any valid MySQL data type

characteristic:
     COMMENT 'string'
     | LANGUAGE SQL
     | [NOT] DETERMINISTIC
     | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
     | SQL SECURITY { DEFINER | INVOKER }
routine_body:
    Valid SQL routine statement

Alter

ALTER PROCEDURE proc_name [characteristic ...]
     characteristic:
     COMMENT 'string'
     | LANGUAGE SQL
     | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
     | SQL SECURITY { DEFINER | INVOKER }

Rename

Drop / Existing Check

DROP PROCEDURE [IF EXISTS] Procedure_name;

Views

Create

CREATE
     [OR REPLACE]
     [ALGORITHM = { | MERGE | TEMPTABLE}]
     [DEFINER = user]
     [SQL SECURITY { DEFINER | INVOKER }]
     VIEW view_name [(column_list)]
     AS select_statement
     [WITH [CASCADED | LOCAL] CHECK OPTION]

Alter

ALTER
     [ALGORITHM = { | MERGE | TEMPTABLE}]
     [DEFINER = user]
     [SQL SECURITY { DEFINER | INVOKER }]
     VIEW view_name [(column_list)]
     AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

Drop / Existing Check

DROP VIEW [IF EXISTS] 
     view_name [, view_name] ... 
     [RESTRICT | CASCADE]

Triggers

Create

CREATE
     [DEFINER = user]
     TRIGGER trigger_name
     trigger_time trigger_event
     ON tbl_name FOR EACH ROW
     [trigger_order]
trigger_body
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

Alter

Rename

Drop / Existing Check

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

Constraints

Primary Key

Foreign Key

Arithmetic Operators

Arithmetic

Name Description
%, MOD Modulo operator
* Multiplication operator
+ Addition operator
- Minus operator
- Change the sign of the argument
/ Division operator
DIV Integer division

Mathematical Functions

Name Description
ABS() Return the absolute value
ACOS() Return the arc cosine
ASIN() Return the arc sine
ATAN() Return the arc tangent
ATAN2(), ATAN() Return the arc tangent of the two arguments
CEIL() Return the smallest integer value not less than the argument
CEILING() Return the smallest integer value not less than the argument
CONV() Convert numbers between different number bases
COS() Return the cosine
COT() Return the cotangent
CRC32() Compute a cyclic redundancy check value
DEGREES() Convert radians to degrees
EXP() Raise to the power of
FLOOR() Return the largest integer value not greater than the argument
LN() Return the natural logarithm of the argument
LOG() Return the natural logarithm of the first argument
LOG10() Return the base-10 logarithm of the argument
LOG2() Return the base-2 logarithm of the argument
MOD() Return the remainder
PI() Return the value of pi
POW() Return the argument raised to the specified power
POWER() Return the argument raised to the specified power
RADIANS() Return argument converted to radians
RAND() Return a random floating-point value
ROUND() Round the argument
SIGN() Return the sign of the argument
SIN() Return the sine of the argument
SQRT() Return the square root of the argument
TAN() Return the tangent of the argument
TRUNCATE() Truncate to specified number of decimal places

Bitwise Operator

Name Description
& Bitwise AND
>> Right shift
<< Left shift
^ Bitwise XOR
BIT_COUNT() Return the number of bits that are set
\| Bitwise OR
~ Bitwise inversion

Comparision Operator

Name Description
> Greater than operator
>= Greater than or equal operator
< Less than operator
<>, != Not equal operator
<= Less than or equal operator
<=> NULL-safe equal to operator
= Equal operator
BETWEEN ... AND ... Whether a value is within a range of values
COALESCE() Return the first non-NULL argument
GREATEST() Return the largest argument
IN() Whether a value is within a set of values
INTERVAL() Return the index of the argument that is less than the first argument
IS Test a value against a boolean
IS NOT Test a value against a boolean
IS NOT NULL NOT NULL value test
IS NULL NULL value test
ISNULL() Test whether the argument is NULL
LEAST() Return the smallest argument
LIKE Simple pattern matching
NOT BETWEEN ... AND ... Whether a value is not within a range of values
NOT IN() Whether a value is not within a set of values
NOT LIKE Negation of simple pattern matching
STRCMP() Compare two strings

Compound Operator

JSON Function Reference

Name Description
-> Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT().
->> Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()).
JSON_ARRAY() Create JSON array
JSON_ARRAY_APPEND() Append data to JSON document
JSON_ARRAY_INSERT() Insert into JSON array
JSON_CONTAINS() Whether JSON document contains specific object at path
JSON_CONTAINS_PATH() Whether JSON document contains any data at path
JSON_DEPTH() Maximum depth of JSON document
JSON_EXTRACT() Return data from JSON document
JSON_INSERT() Insert data into JSON document
JSON_KEYS() Array of keys from JSON document
JSON_LENGTH() Number of elements in JSON document
JSON_MERGE() (deprecated) Merge JSON documents, preserving duplicate keys. Deprecated synonym for JSON_MERGE_PRESERVE()
JSON_MERGE_PATCH() Merge JSON documents, replacing values of duplicate keys
JSON_MERGE_PRESERVE() Merge JSON documents, preserving duplicate keys
JSON_OBJECT() Create JSON object
JSON_OVERLAPS() (introduced 8.0.17) Compares two JSON documents, returns TRUE (1) if these have any key-value pairs or array elements in common, otherwise FALSE (0)
JSON_PRETTY() Print a JSON document in human-readable format
JSON_QUOTE() Quote JSON document
JSON_REMOVE() Remove data from JSON document
JSON_REPLACE() Replace values in JSON document
JSON_SCHEMA_VALID() (introduced 8.0.17) Validate JSON document against JSON schema; returns TRUE/1 if document validates against schema, or FALSE/0 if it does not
JSON_SCHEMA_VALIDATION_REPORT() (introduced 8.0.17) Validate JSON document against JSON schema; returns report in JSON format on outcome on validation including success or failure and reasons for failure
JSON_SEARCH() Path to value within JSON document
JSON_SET() Insert data into JSON document
JSON_STORAGE_FREE() Freed space within binary representation of JSON column value following partial update
JSON_STORAGE_SIZE() Space used for storage of binary representation of a JSON document
JSON_TABLE() Return data from a JSON expression as a relational table
JSON_TYPE() Type of JSON value
JSON_UNQUOTE() Unquote JSON value
JSON_VALID() Whether JSON value is valid
MEMBER OF() (introduced 8.0.17) Returns true (1) if first operand matches any element of JSON array passed as second operand, otherwise returns false (0)

Logical Operator

Name Description
AND, && Logical AND
NOT, ! Negates value
OR, \|\| Logical OR
XOR Logical XOR

Expert

Condition Handling

DECLARE ... CONDITION Statement

DECLARE condition_name CONDITION FOR condition_value
     condition_value: {
     mysql_error_code
     | SQLSTATE [VALUE] sqlstate_value
}

DECLARE ... HANDLER Statement

DECLARE handler_action HANDLER
     FOR condition_value [, condition_value] ...
     statement

handler_action: {
     CONTINUE
     | EXIT
     | UNDO
}

condition_value: {
     mysql_error_code
     | SQLSTATE [VALUE] sqlstate_value
     | condition_name
     | SQLWARNING
     | NOT FOUND
     | SQLEXCEPTION
}

GET DIAGNOSTICS Statement

GET [CURRENT | STACKED] DIAGNOSTICS
{
     statement_information_item
     [, statement_information_item] ...
   | CONDITION condition_number
     condition_information_item
     [, condition_information_item] ...
}

statement_information_item:
     target = statement_information_item_name

condition_information_item:
     target = condition_information_item_name

statement_information_item_name:
     NUMBER
     | ROW_COUNT

condition_information_item_name: {
     CLASS_ORIGIN
     | SUBCLASS_ORIGIN
     | RETURNED_SQLSTATE
     | MESSAGE_TEXT
     | MYSQL_ERRNO
     | CONSTRAINT_CATALOG
     | CONSTRAINT_SCHEMA
     | CONSTRAINT_NAME
     | CATALOG_NAME
     | SCHEMA_NAME
     | TABLE_NAME
     | COLUMN_NAME
     | CURSOR_NAME
}

condition_number, target:

RESIGNAL Statement

RESIGNAL [condition_value]
     [SET signal_information_item
     [, signal_information_item] ...]

condition_value: {
     SQLSTATE [VALUE] sqlstate_value
     | condition_name
}

signal_information_item:
     condition_information_item_name = simple_value_specification

condition_information_item_name: {
     CLASS_ORIGIN
     | SUBCLASS_ORIGIN
     | MESSAGE_TEXT
     | MYSQL_ERRNO
     | CONSTRAINT_CATALOG
     | CONSTRAINT_SCHEMA
     | CONSTRAINT_NAME
     | CATALOG_NAME
     | SCHEMA_NAME
     | TABLE_NAME
     | COLUMN_NAME
    | CURSOR_NAME
}

condition_name, simple_value_specification:

SIGNAL Statement

SIGNAL condition_value
    [SET signal_information_item
    [, signal_information_item] ...]

condition_value: {
    SQLSTATE [VALUE] sqlstate_value
    | condition_name
}

signal_information_item:
    condition_information_item_name = simple_value_specification

condition_information_item_name: {
    CLASS_ORIGIN
    | SUBCLASS_ORIGIN
    | MESSAGE_TEXT
    | MYSQL_ERRNO
    | CONSTRAINT_CATALOG
    | CONSTRAINT_SCHEMA
    | CONSTRAINT_NAME
    | CATALOG_NAME
    | SCHEMA_NAME
    | TABLE_NAME
    | COLUMN_NAME
    | CURSOR_NAME
}

interval:
     quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND }

DROP EVENT [IF EXISTS] event_name

Privileges

The EVENT privilege governs the creation, modification, and deletion of events. This privilege can be bestowed using GRANT . For example, this GRANT statement confers the EVENT privilege for the schema named myschema on the user jon@ghidora:

GRANT EVENT ON myschema.* TO jon@ghidora;

(We assume that this user account already exists, and that we wish for it to remain unchanged otherwise.) To grant this same user the EVENT privilege on all schemas, use the following statement:

GRANT EVENT ON *.* TO jon@ghidora;

The EVENT privilege has global or schema-level scope. Therefore, trying to grant it on a single table results in an error as shown:

mysql> GRANT EVENT ON myschema.mytable TO jon@ghidora; ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the manual to see which privileges can be used

MySQL Shell

SQL Syntax

MySQLCheck

References