My SQL Cheatsheet

3657

3 years ago

Connecting to Database using command-line client

mysql -u [username] -p [database];

To exit from mysql command-line client
```
exit;
```

To creen console window on Linux

system clear;

Database Commands

Creating a database

CREATE DATABASE [IF NOT EXISTS] db_name;

Use a database

USE db_name;

Drop a database

DROP DATABASE [IF EXISTS] db_name;

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

RENAME TABLE table_name1 to new_table_name1;

6. COMMENT

Single-Line Comments:

 --Line1;

Multi-Line comments:

   /* Line1,
   Line2 */

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];

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;

2. How to call view

SELECT * FROM View_name;

3. Altering a View

ALTER View View_name AS 
Query;

4. Deleting a View

DROP VIEW View_name;

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 } */

2. Drop a Trigger

DROP TRIGGER [IF EXISTS] trigger_name;

Stored Procedures

1. Create a Stored Procedure

CREATE PROCEDURE sp_name(p1 datatype)
BEGIN
/*Stored procedure code*/
END;

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;