Institute It. Training | Kursus Komputer Jakarta Timur | WA. +628978298280 |

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 - 4294967295)

LONGBLOB String   (0 - 4294967295)

TINYINT x Integer (-128 to 127)

SMALLINT x Integer(-32768 to 32767)

MEDIUMINT x Integer (-8388608 to 8388607)

INT x Integer     (-2147483648 to 2147483647)

BIGINT x Integer  (-9223372036854775808 to 9223372036854775807)

FLOAT Decimal     (precise to 23 digits)

DOUBLE Decimal    (24 to 53 digits)

DECIMAL "DOUBLE" stored as string

DATE              YYYY-MM-DD

DATETIME          YYYY-MM-DD HH:MM:SS

TIMESTAMP         YYYYMMDDHHMMSS

TIME              HH:MM:SS

ENUM              One of preset options

SET               Selection of preset options

MySQL Mathematical 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_FORMAT

QUARTER TIME_FORMAT

WEEK CURRENT_DATE

YEAR CURRENT_TIME

YEARWEEK NOW

HOUR SYSDATE

MINUTE UNIX_TIMESTAMP

SECOND FROM_UNIXTIME

PERIOD_ADD SEC_TO_TIME

PERIOD_DIFF TIME_TO_SEC

DATE_ADD

MySQL Miscellaneous Functions

BIT_COUNT    DES_ENCRYPT

DATABASE     DES_DECRYPT

USER LAST  _ INSERT_ID

SYSTEM_USE   FORMAT

SESSION_USER VERSION

CURRENT_USER CONNECTION_ID

PASSWORD     GET_LOCK

OLD_PASSWORD RELEASE_LOCK

ENCRYPT      IS_FREE_LOCK

DECODE       BENCHMARK

MD5          INET_NTOA

SHA1         INET_ATON

AES_ENCRYPT  FOUND_ROWS

AES_DECRYPT  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

MYSQL Cheat Sheet