Skip to main content

SQL Symphony: A Melodic Cheat Sheet for MySQL

 MySQL Complete Cheatsheet

 Comprehensive MySQL Cheatsheet

 

 Database Management

 

 Create and Use Database

SQL CODE:

CREATE DATABASE database_name;

USE database_name;

 

 Show Databases

SQL CODE:

SHOW DATABASES;

 

 Drop Database

SQL CODE:

DROP DATABASE database_name;



 

 Table Operations

 

 Create Table

SQL CODE:

CREATE TABLE table_name (

    column1 datatype,

    column2 datatype,

    column3 datatype,

   ....

);

 

 Show Tables

SQL CODE:

SHOW TABLES;

 

 Describe Table

SQL CODE:

DESCRIBE table_name;

 

 Drop Table

SQL CODE:

DROP TABLE table_name;

 

 Alter Table

SQL CODE:

ALTER TABLE table_name ADD column_name datatype;

ALTER TABLE table_name DROP COLUMN column_name;

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

 

 Data Manipulation

 

 Insert Data

SQL CODE:

INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);

 

 Select Data

SQL CODE:

SELECT * FROM table_name;

SELECT column1, column2 FROM table_name;

 

 Update Data

SQL CODE:

UPDATE table_name

SET column1 = value1, column2 = value2, ...

WHERE condition;

 

 Delete Data

SQL CODE:

DELETE FROM table_name WHERE condition;


 

 Querying Data

 

 WHERE Clause

SQL CODE:

SELECT * FROM table_name WHERE condition;

 

 AND, OR, NOT

SQL CODE:

SELECT * FROM table_name WHERE condition1 AND condition2;

SELECT * FROM table_name WHERE condition1 OR condition2;

SELECT * FROM table_name WHERE NOT condition;

 

 ORDER BY

SQL CODE:

SELECT * FROM table_name ORDER BY column1 ASC|DESC;

 

 LIMIT

SQL CODE:

SELECT * FROM table_name LIMIT number;

 

 LIKE

SQL CODE:

SELECT * FROM table_name WHERE column LIKE pattern;

 

Wildcards: % (any number of characters), _ (single character)

 

 IN

SQL CODE:

SELECT * FROM table_name WHERE column IN (value1, value2, ...);

 

 BETWEEN

SQL CODE:

SELECT * FROM table_name WHERE column BETWEEN value1 AND value2;

 

 Aliases

SQL CODE:

SELECT column AS alias_name FROM table_name;

 

 Joins

 

 INNER JOIN

SQL CODE:

SELECT columns

FROM table1

INNER JOIN table2

ON table1.column = table2.column;

 

 LEFT JOIN

SQL CODE:

SELECT columns

FROM table1

LEFT JOIN table2

ON table1.column = table2.column;

 

 RIGHT JOIN

SQL CODE:

SELECT columns

FROM table1

RIGHT JOIN table2

ON table1.column = table2.column;

 

 FULL JOIN (Not directly supported in MySQL, simulated with UNION)

SQL CODE:

SELECT columns

FROM table1

LEFT JOIN table2

ON table1.column = table2.column

UNION

SELECT columns

FROM table1

RIGHT JOIN table2

ON table1.column = table2.column;

 


 Aggregate Functions

 

 COUNT

SQL CODE:

SELECT COUNT(column) FROM table_name;

 

 SUM

SQL CODE:

SELECT SUM(column) FROM table_name;

 

 AVG

SQL CODE:

SELECT AVG(column) FROM table_name;

 

 MAX

SQL CODE:

SELECT MAX(column) FROM table_name;

 

 MIN

SQL CODE:

SELECT MIN(column) FROM table_name;

 

 GROUP BY

SQL CODE:

SELECT column, aggregate_function(column)

FROM table_name

GROUP BY column;

 

 HAVING

SQL CODE:

SELECT column, aggregate_function(column)

FROM table_name

GROUP BY column

HAVING condition;

 


 Subqueries

 

 Subquery in SELECT

SQL CODE:

SELECT column, (SELECT ...) AS alias

FROM table_name;

 

 Subquery in WHERE

SQL CODE:

SELECT column

FROM table_name

WHERE column operator (SELECT ... FROM ...);

 

 Indexes

 

 Create Index

SQL CODE:

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

 

 Drop Index

SQL CODE:

DROP INDEX index_name ON table_name;


 Views

 

 Create View

SQL CODE:

CREATE VIEW view_name AS

SELECT column1, column2, ...

FROM table_name

WHERE condition;

 

 Drop View

SQL CODE:

DROP VIEW view_name;

 

 Transactions

 

 Start Transaction

SQL CODE:

START TRANSACTION;

 

 Commit

SQL CODE:

COMMIT;

 

 Rollback

SQL CODE:

ROLLBACK;

 

 User Management

 

 Create User

SQL CODE:

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

 

 Grant Privileges

SQL CODE:

GRANT privilege ON database.table TO 'username'@'host';

 

 Revoke Privileges

SQL CODE:

REVOKE privilege ON database.table FROM 'username'@'host';

 

 Show Grants

SQL CODE:

SHOW GRANTS FOR 'username'@'host';

 

 Drop User

SQL CODE:

DROP USER 'username'@'host';

 

Remember to always end your SQL statements with a semicolon (;).

 

 

Comments

Post a Comment

Popular posts from this blog

The Git Life: Your Guide to Seamless Collaboration and Control

A Comprehensive Guide to Git: From Basics to Advanced   What is Git and GitHub?   Imagine you are organizing a wedding —a grand celebration with many family members, friends, and vendors involved. You need a foolproof way to manage tasks, keep track of who is doing what, and ensure that everyone stays on the same page. This is where Git and GitHub come in, though in the world of technology.   What is Git?   Git is like the wedding planner or the master ledger for managing all wedding-related activities. Think of it as a system that helps you:      1.   Keep track of every change made (like noting down who ordered the flowers or printed the invitation cards).       2.   Maintain a record of what changes happened and who made them (e.g., the uncle who updated the guest list).       3.   Go back to an earlier version if something goes wrong (...
Post a Comment
Read more

How to Open Jupyter Lab in your favourite browser other than system default browser in Mac OS: A Step-by-Step Guide

Are you tired of Jupyter Lab opening in your default browser? Would you prefer to use Google Chrome or another browser of your choice? This guide will walk you through the process of configuring Jupyter Lab to open in your preferred browser, with a focus on using Google Chrome. The Challenge   Many tutorials suggest using the command prompt to modify Jupyter's configuration. However, this method often results in zsh errors and permission issues, even when the necessary permissions seem to be in place. This guide offers a more reliable solution that has proven successful for many users.   Step-by-Step Solution   1. Locate the Configuration File - Open Finder and navigate to your user folder (typically named after your username). - Use the keyboard shortcut Command + Shift + . (full stop) to reveal hidden folders. - Look for a hidden folder named .jupyter . - Within this folder, you'll find the jupyter_notebook_config.py file.   2. Edit the Configuration File - Open ...
Post a Comment
Read more

Streamlit - An interactive app guide for Data Scientists and ML Engineers

Streamlit: A Guide to Create an Interactive App Introduction to Streamlit:   What is Streamlit? Streamlit  is an open-source Python library that allows you to build interactive and data-driven web applications with minimal effort. It is widely used in data science, machine learning, and analytics to create quick and interactive dashboards without requiring web development knowledge.   Why to use Streamlit? •                  Easy to use: No front-end knowledge required. •                  Quick development: Turn Python scripts into web apps instantly. •                  Interactive widgets: Built-in support for user interaction. •                  Ideal for ...
Post a Comment
Read more