MySQL Cheatsheet

381 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

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More

Enable Notifications    OK No thanks