MySQL Cheatsheet

by anupmaurya
763 views

MySQL is an open-source relational database management system. Its name is a combination of “My”, the name of co-founder Michael Widenius’s daughter, and “SQL”, the abbreviation for Structured Query Language. A relational database organizes data into one or more data tables in which data types may be related to each other; these relations help structure the data.

SQL is a language programmers use to create, modify and extract data from the relational database, as well as control user access to the database

Connecting to Database using command-line client

mysql -u [username] -p [database];
Code language: SQL (Structured Query Language) (sql)
  • To exit from mysql command-line clientexit;

To creen console window on Linux

system clear;

Database Commands

Creating a database

CREATE DATABASE [IF NOT EXISTS] db_name;
Code language: SQL (Structured Query Language) (sql)

Use a database

USE db_name;
Code language: PHP (php)

Drop a database

DROP DATABASE [IF EXISTS] db_name;
Code language: SQL (Structured Query Language) (sql)

Show available databases in the working database server

SHOW DATABASE;

Display all the tables present in the database

SHOW TABLES;

DDL Commands

1. CREATE

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

2. ALTER

ALTER TABLE Table_name ADD column_name datatype;

3. TRUNCATE

TRUNCATE table table_name;

4. DROP

DROP TABLE table_name;

5. RENAME

<strong>RENAME TABLE table_name1 to new_table_name1; </strong>
Code language: HTML, XML (xml)

6. COMMENT

Single-Line Comments:

--Line1;

Multi-Line comments:

/* Line1, Line2 */
Code language: JSON / JSON with Comments (json)

DML Commands

1. INSERT

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

Note: Column names are optional.

2. SELECT

SELECT column1, column2, ... FROM table_name [where condition];
Code language: CSS (css)

3. UPDATE

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

4. DELETE

DELETE FROM table_name where condition;

DCL Commands

1. GRANT

GRANT privileges ON object TO user;

2. REVOKE

REVOKE privileges ON object FROM user;

TCL commands

1. COMMIT

COMMIT;

2. ROLLBACK

ROLLBACK;

3. SAVEPOINT

SAVEPOINT savepoint_name; ROLLBACK TO savepoint_name;

How to delete a savepoint:

RELEASE SAVEPOINT savepoint_name;

Indexes

1. CREATE INDEX

CREATE INDEX index_name on table_name(column_name);
  • To Create Unique index:
CREATE UNIQUE INDEX index_name on table_name(column_name);

2. DROP INDEX

DROP INDEX index_name ON table_name;

Views

1. Create a View

Creating a View: CREATE VIEW View_name AS Query;
Code language: SQL (Structured Query Language) (sql)

2. How to call view

SELECT * FROM View_name;
Code language: SQL (Structured Query Language) (sql)

3. Altering a View

ALTER View View_name AS Query;
Code language: SQL (Structured Query Language) (sql)

4. Deleting a View

DROP VIEW View_name;
Code language: SQL (Structured Query Language) (sql)

Triggers

1. Create a Trigger

CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW [trigger_order] trigger_body /* where trigger_time: { BEFORE | AFTER } trigger_event: { INSERT | UPDATE | DELETE } trigger_order: { FOLLOWS | PRECEDES } */
Code language: SQL (Structured Query Language) (sql)

2. Drop a Trigger

DROP TRIGGER [IF EXISTS] trigger_name;
Code language: CSS (css)

Stored Procedures

1. Create a Stored Procedure

CREATE PROCEDURE sp_name(p1 datatype) BEGIN /*Stored procedure code*/ END;
Code language: SQL (Structured Query Language) (sql)

2. How to call Stored procedure

CALL sp_name;

3. How to delete stored procedure

DROP PROCEDURE sp_name;

Joins

1. INNER JOIN

SELECT * FROM TABLE1 INNER JOIN TABLE2 where condition;

2. LEFT JOIN

SELECT * FROM TABLE1 LEFT JOIN TABLE2 ON condition;

3. RIGHT JOIN

SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON condition;

4. CROSS JOIN

SELECT select_list from TABLE1 CROSS JOIN TABLE2;
Code language: JavaScript (javascript)

Hope this MySQL cheatsheet is useful to you. Share with your friends.

You may also like