MySQL Database Cheatsheet

MySQL is one of the most popular relational database management systems, powering numerous web applications and services. Whether you’re a beginner or an experienced developer, having a cheatsheet handy can save time and make your interactions with the MySQL database more efficient. This cheatsheet provides a quick reference guide to common MySQL commands and operations.

Connecting to MySQL

Connect to MySQL Server

mysql -u username -p

Connect to a Specific Database

use database_name;

Show Databases

show databases;

Creating and Managing Databases

Create a Database

create database database_name;

Delete a Database

drop database database_name;

List Tables in a Database

show tables;

Managing Tables

Create a Table

create table table_name (
    column1 datatype,
    column2 datatype,
    ...
);

Delete a Table

drop table table_name;

Describe Table Structure

desc table_name;

Data Manipulation

Insert Data

insert into table_name (column1, column2, ...) values (value1, value2, ...);

Update Data

update table_name set column1 = value1 where condition;

Delete Data

delete from table_name where condition;

Select Data

select column1, column2, ... from table_name where condition;

Querying and Filtering

Simple SELECT Query

select * from table_name;

WHERE Clause

select * from table_name where column_name = value;

ORDER BY Clause

select * from table_name order by column_name;

LIMIT Clause

select * from table_name limit 10;

Advanced Features

Joins

select * from table1 join table2 on table1.column_name = table2.column_name;

Indexing

create index index_name on table_name (column_name);

Transactions

start transaction;
-- SQL statements
commit;

Security

Create User

create user 'username'@'localhost' identified by 'password';

Grant Privileges

grant all privileges on database_name.* to 'username'@'localhost';

Revoke Privileges

revoke all privileges on database_name.* from 'username'@'localhost';

This MySQL cheatsheet provides a quick and handy reference for common database operations. Whether you’re creating tables, querying data, or managing users, these commands will help you navigate and manipulate your MySQL databases efficiently. Bookmark this cheatsheet for quick access, and enhance your productivity when working with MySQL.

FAQ