MYSQL Cheat Sheet

MYSQL Cheat Sheet

 

MySQL is a popular relational database management system (RDBMS) used for managing and manipulating databases. Below is a cheat sheet that covers essential MySQL commands and operations.

Connecting to MySQL

bash
 
mysql -u [username] -p

• Connect to MySQL with a specified username. You will be prompted for the password.

bash
 
mysql -u [username] -p [database_name]

• Connect to a specific database.

Basic Commands

sql
 
SHOW DATABASES;

• List all databases.

sql
 
USE [database_name];

• Select a database to use.

sql
 
SHOW TABLES;

• List all tables in the selected database.

sql
 
DESCRIBE [table_name];

• Show the structure of a table.

sql
 
SELECT VERSION();

• Display the MySQL version.

Creating Databases and Tables

sql
 
CREATE DATABASE [database_name];

• Create a new database.

sql
 
DROP DATABASE [database_name];

• Delete a database.

sql
 
CREATE TABLE [table_name] (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ...
);

• Create a new table with specified columns and datatypes.

sql
 
DROP TABLE [table_name];

• Delete a table.

Basic CRUD Operations

Insert Data

sql
 
INSERT INTO [table_name] (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

• Insert a new record into a table.

Select Data

sql
 
SELECT column1, column2, column3, ...
FROM [table_name]
WHERE condition;

• Retrieve specific columns from a table based on a condition.

sql
 
SELECT * FROM [table_name];

• Retrieve all columns from a table.

Update Data

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

• Update existing records in a table.

Delete Data

sql
 
DELETE FROM [table_name]
WHERE condition;

• Delete records from a table based on a condition.

Advanced Queries

Join

sql
 
SELECT columns
FROM table1
JOIN table2
ON table1.common_column = table2.common_column;

• Combine rows from two or more tables based on a related column.

Inner Join

sql
 
SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

• Select records that have matching values in both tables.

Left Join

sql
 
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;

• Select all records from the left table and the matched records from the right table.

Right Join

sql
 
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;

• Select all records from the right table and the matched records from the left table.

Full Join (MySQL doesn't directly support FULL JOIN, use UNION instead)

sql
 
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column
UNION
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;

• Combine results of both left and right joins.

Group By

sql
 
SELECT column, COUNT(*)
FROM [table_name]
GROUP BY column;

• Group rows that have the same values in specified columns into summary rows.

Order By

sql
 
SELECT column1, column2
FROM [table_name]
ORDER BY column1 ASC, column2 DESC;

• Sort the result set in ascending or descending order.

Indexes

Create Index

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

• Create an index on a table for faster retrieval.

Drop Index

sql
 
DROP INDEX [index_name] ON [table_name];

• Remove an index from a table.

Constraints

Primary Key

sql
 
ALTER TABLE [table_name]
ADD PRIMARY KEY (column);

• Set a column as the primary key.

Foreign Key

sql
 
ALTER TABLE [table_name]
ADD CONSTRAINT [constraint_name] FOREIGN KEY (column)
REFERENCES [other_table] (other_column);

• Set a column as a foreign key.

Unique Key

sql
 
ALTER TABLE [table_name]
ADD UNIQUE (column);

• Ensure all values in a column are unique.

Transactions

Start Transaction

sql
 
START TRANSACTION;

• Begin a new transaction.

Commit

sql
 
COMMIT;

• Save the changes made in the current transaction.

Rollback

sql
 
ROLLBACK;

• Undo changes made in the current transaction.

User Management

Create User

sql
 
CREATE USER '[username]'@'localhost' IDENTIFIED BY '[password]';

• Create a new MySQL user.

Grant Privileges

sql
 
GRANT ALL PRIVILEGES ON [database_name].* TO '[username]'@'localhost';

• Grant privileges to a user.

Revoke Privileges

sql
 
REVOKE ALL PRIVILEGES ON [database_name].* FROM '[username]'@'localhost';

• Revoke privileges from a user.

Delete User

sql
 
DROP USER '[username]'@'localhost';

• Delete a MySQL user.

Conclusion

This cheat sheet provides a quick reference to essential MySQL commands and operations. Whether you are managing databases, performing CRUD operations, or optimizing queries, these commands will help you work more efficiently with MySQL.

from
https://intitute.blogspot.com/2018/10/mysql-cheat-sheet.html

MySQL Data Types

CHAR String       (0 - 255)

VARCHAR String    (0 - 255)

TINYTEXT String   (0 - 255)

TEXT String       (0 - 65535) 

BLOB String       (0 - 65535)

MEDIUMTEXT String (0 - 16777215)

MEDIUMBLOB String (0 - 16777215)

LONGTEXT String   (0 - 429496­7295)

LONGBLOB String   (0 - 429496­7295)

TINYINT x Integer (-128 to 127)

SMALLINT x Integer(-32768 to 32767)

MEDIUMINT x Integer (-8388608 to 8388607)

INT x Integer     (-2147­483648 to 214748­3647)

BIGINT x Integer  (-9223­372­036­854­775808 to 922337­203­685­477­5807)

FLOAT Decimal     (precise to 23 digits)

DOUBLE Decimal    (24 to 53 digits)

DECIMAL "­DOU­BLE­" stored as string

DATE              YYYY-MM-DD

DATETIME          YYYY-MM-DD HH:MM:SS

TIMESTAMP         YYYYMM­DDH­HMMSS

TIME              HH:MM:SS

ENUM              One of preset options

SET               Selection of preset options

MySQL Mathem­atical Functions

ABS     COS

SIGN    SIN

MOD     TAN

FLOOR   ACOS

CEILING ASIN

ROUND   ATAN, ATAN2

DIV     COT

EXP     RAND

LN      LEAST 

LOG, LOG2, LOG10 GREATEST 

POW     DEGREES 

POWER   RADIANS 

SQRT    TRUNCATE

PI

MySQL Date and Time Functions

DAYOFWEEK DATE_SUB

WEEKDAY ADDDATE

DAYOFMONTH SUBDATE

DAYOFYEAR EXTRACT 

MONTH TO_DAYS

DAYNAME FROM_DAYS

MONTHNAME DATE_F­ORMAT

QUARTER TIME_F­ORMAT

WEEK CURREN­T_DATE

YEAR CURREN­T_TIME

YEARWEEK NOW

HOUR SYSDATE

MINUTE UNIX_T­IME­STAMP

SECOND FROM_U­NIXTIME

PERIOD_ADD SEC_TO­_TIME

PERIOD­_DIFF TIME_T­O_SEC

DATE_ADD

MySQL Miscel­laneous Functions

BIT_COUNT    DES_EN­CRYPT

DATABASE     DES_DE­CRYPT

USER LAST  _ I­NSE­RT_ID

SYSTEM­_USE   FORMAT

SESSIO­N_USER VERSION

CURREN­T_USER CONNEC­TION_ID

PASSWORD     GET_LOCK

OLD_PA­SSWORD RELEAS­E_LOCK

ENCRYPT      IS_FRE­E_LOCK

DECODE       BENCHMARK

MD5          INET_NTOA

SHA1         INET_ATON

AES_EN­CRYPT  FOUND_ROWS

AES_DE­CRYPT  STRCMP

MySQL String Functions

ASCII       SUBSTRING

ORD         MID

CONV        SUBSTRING_INDEX

BIN         LTRIM

OCT         RTRIM

HEX         TRIM

CHAR        SOUNDEX

CONCAT      SPACE

CONCAT_WS   REPLACE

LENGTH      REPEAT

CHAR_LENGTH REVERSE

BIT_LENGTH  INSERT

LOCATE      ELT

INSTR       FIELD

LPAD        LCASE

RPAD        UCASE

LEFT LOAD  _FILE

RIGHT       QUOTE

MySQL Grouping Functions

AVG           MAX

BIT_AND       STD

BIT_OR        STDDEV

COUNT         SUM

GROUP_­CONCAT  VARIANCE

MIN