MySQL cheat sheet | Brief Summery of MySQL

Here is a MySQL cheat sheet that includes almost everything you would want to know about using MySQL:

MySQL Commands

• SHOW DATABASES: Shows a list of all databases on the MySQL server.
• USE database_name: Selects a database to use.
• SHOW TABLES: Shows a list of all tables in the selected database.
• DESCRIBE table_name: Shows the structure of a table (column names and data types).

MySQL Data Types

• INT: Integer value
• FLOAT: Floating point value
• DOUBLE: Double precision floating point value
• DECIMAL: Exact decimal value
• DATE: Date (format: YYYY-MM-DD)
• DATETIME: Date and time (format: YYYY-MM-DD HH:MM:SS)
• TIMESTAMP: Date and time (format: YYYY-MM-DD HH:MM:SS)
• TIME: Time (format: HH:MM:SS)
• YEAR: Year (format: YYYY or YY)
• CHAR(n): Fixed-length character string (n characters)
• VARCHAR(n): Variable-length character string (up to n characters)
• BINARY(n): Fixed-length binary string (n bytes)
• VARBINARY(n): Variable-length binary string (up to n bytes)
• TINYBLOB: Very small binary string (max 255 bytes)
• BLOB: Small binary string (max 65535 bytes)
• MEDIUMBLOB: Medium-sized binary string (max 16777215 bytes)
• LONGBLOB: Large binary string (max 4294967295 bytes)
• TINYTEXT: Very small character string (max 255 characters)
• TEXT: Small character string (max 65535 characters)
• MEDIUMTEXT: Medium-sized character string (max 16777215 characters)
• LONGTEXT: Large character string (max 4294967295 characters)

MySQL Statements

• SELECT: Retrieves data from a table
• INSERT INTO: Inserts new data into a table
• UPDATE: Modifies existing data in a table
• DELETE FROM: Deletes data from a table
• CREATE TABLE: Creates a new table
• ALTER TABLE: Modifies an existing table
• DROP TABLE: Deletes a table
• TRUNCATE TABLE: Empties a table (deletes all rows)
• CREATE INDEX: Creates an index (search key) on a table
• DROP INDEX: Deletes an index from a table

MySQL Operators

• =: Equal to
• <>: Not equal to
• >: Greater than
• <: Less than
• >=: Greater than or equal to
• <=: Less than or equal to
• BETWEEN: Between a certain range (inclusive)
• LIKE: Search for a pattern
• IN: Match any of the values in a list
• IS NULL: Check

MySQL Queries with examples

SELECT

SELECT column1, column2, ...
FROM table_name;

This statement retrieves data from the specified table. The * wildcard can be used to select all columns.

For example:

SELECT *
FROM employees;

INSERT INTO

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

This statement inserts new data into the specified table.

For example:

INSERT INTO employees (name, age, salary)
VALUES ('John', 35, 50000);

UPDATE

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

This statement modifies existing data in the specified table. The WHERE clause specifies which rows to update.

For example:

UPDATE employees
SET salary = 55000
WHERE name = 'John';

DELETE FROM

DELETE FROM table_name
WHERE condition;

This statement deletes data from the specified table. The WHERE clause specifies which rows to delete.

For example:

DELETE FROM employees
WHERE age < 30;

CREATE TABLE

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  ...
);

This statement creates a new table.

For example:

CREATE TABLE employees (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255),
  age INT,
  salary DECIMAL(10,2),
  PRIMARY KEY (id)
);

ALTER TABLE

ALTER TABLE table_name
ADD column datatype;

ALTER TABLE table_name
DROP COLUMN column;

These statements modify an existing table. The first statement adds a new column, and the second statement removes a column.

For example:

ALTER TABLE employees
ADD department VARCHAR(255);

ALTER TABLE employees
DROP COLUMN age;

DROP TABLE

DROP TABLE table_name;

This statement deletes a table.

For example:

DROP TABLE employees;

TRUNCATE TABLE

TRUNCATE TABLE table_name;

This statement empties a table (deletes all rows).

For example:

TRUNCATE TABLE employees;

CREATE INDEX

CREATE INDEX index_name ON table_name (column1, column2, ...);

This statement creates an index (search key) on a table.

For example:

CREATE INDEX idx_name ON employees (name);

DROP INDEX

DROP INDEX index_name ON table_name;

This statement deletes an index from a table

MySQL Database storage engines

MySQL supports several different storage engines that can be used to store and manage data in a database. Here are a few of the most commonly used storage engines in MySQL:

• InnoDB: This is the default storage engine in MySQL. It supports transactions, foreign keys, and row-level locking. It is well-suited for applications that require high reliability and performance.
• MyISAM: This storage engine is optimized for fast reads and is often used for data warehousing and web applications. It does not support transactions or foreign keys, but it does support full-text search indexing.
• Memory: This storage engine stores data in memory, making it extremely fast for read and write operations. However, all data is lost when the MySQL server is restarted, so it is not suitable for permanent data storage.
• CSV: This storage engine stores data in CSV (comma-separated values) format, which can be useful for importing and exporting data to and from other applications.
• ARCHIVE: This storage engine is optimized for storing large amounts of historical data that is not frequently accessed. It provides very fast insert performance, but it does not support indexing or transactions.

You can specify which storage engine to use when creating a table in MySQL using the ENGINE clause.

For example:

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  ...
) ENGINE = InnoDB;

MySQL latest 3 changelogs

MySQL maintains a changelog that lists all the changes made to the MySQL server in each version. You can find the changelog for each version of MySQL in the documentation on the MySQL website: https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html

Here is a summary of some of the main changes made in recent versions of MySQL:

• In MySQL 8.0, the following changes were made:
  • The default storage engine was changed to InnoDB.
  • The JSON data type was added.
  • The GROUPING SETS, ROLLUP, and CUBE clauses were added to the GROUP BY clause.
  • The WITHOUT SIDE EFFECTS clause was added to the SELECT statement.
  • The IGNORE and RESIGNAL clauses were added to the SIGNAL statement.
  • The CUME_DIST and PERCENT_RANK functions were added.
• In MySQL 5.7, the following changes were made:
  • The JSON data type and functions were added.
  • The EXPLAIN FORMAT=JSON and EXPLAIN ANALYZE clauses were added to the EXPLAIN statement.
  • The ON UPDATE RESTRICT and ON DELETE RESTRICT foreign key actions were added.
  • The ON UPDATE CASCADE and ON DELETE CASCADE foreign key actions were changed to be more strict.
  • The ROW_NUMBER window function was added.
  • The BIT data type was improved.
• In MySQL 5.6, the following changes were made:
  • The EXPLAIN PARTITIONS clause was added to the EXPLAIN statement.
  • The OFFSET clause was added to the SELECT statement.
  • The IGNORE INDEX and FORCE INDEX clauses were added to the SELECT, UPDATE, and DELETE statements.
  • The INSERT ... ON DUPLICATE KEY UPDATE statement was improved.
  • The STATS_PERSISTENT and STATS_AUTO_RECALC table options were added.
  • The PERFORMANCE_SCHEMA system database was added.
  • The COUNT(DISTINCT) and GROUP_CONCAT(DISTINCT) functions were improved.