MySQL Cheat Sheet

Structured Query Language (SQL) is a standard query language. It is commonly used with all relational databases for data definition and manipulation. All the relational systems support SQL, thus allowing migration of database from one DBMS to another. 

According to Wiki, 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 My, and "SQL", the acronym for Structured Query Language.

Connect to MySQL

To Start Working with MySQL, first you will need to establish a connection:

****@ts:~$ mysql -u root -p

Enter password:                       

If you didn’t set a password for your MySQL root user, you omit the -p switch or just hit enter:

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Create a New User Account

To Create a new user, run the following command:

mysql> CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Change the username and password according to yours.

Delete the User's Account

If you need to delete a user, use the following command:

mysql> DROP USER 'username'@'localhost';

Change the username and password according to yours.

Grant Permission to User Account

To check the user privileges, use the following command:

mysql> SHOW GRANTS FOR 'username'@'localhost';

+-----------------------------------------------------------------------------------------------------------------+

| Grants for username@localhost                                                                                   |

+-----------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'username'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' | 

+-----------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql>

The user "username" has only usage permissions. To grant all privileges use the following command:

mysql> GRANT ALL on *.* to 'username'@'localhost';

mysql> SHOW GRANTS FOR 'username'@'localhost';

+--------------------------------------------------------------------------------------------------------------------------+

| Grants for username@localhost                                                                                            |

+--------------------------------------------------------------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' | 

+--------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql>

Now the user 'username' have all privileges.

Create a New Database 

To create a new database, use the following command:

mysql> CREATE DATABASE db_name;

Change the db_name according to yours.

You can list all your databases with this command: 

mysql> SHOW DATABASES;

Delete a MySQL Database

To remove a database use the following command:

mysql> DROP DATABASE db_name;

Import Data From A Sample MySQL database

After a lot of search I got a Sample MySQL Database: 

(GitHub Link)

Download and extract the zip file:

****@ts:~$ cd test_db

****@ts:~/test_db$ ls

Changelog                      load_departments.dump   load_salaries2.dump  sakila                  test_versions.sh

employees_partitioned_5.1.sql  load_dept_emp.dump      load_salaries3.dump  show_elapsed.sql

employees_partitioned.sql      load_dept_manager.dump  load_titles.dump     sql_test.sh

employees.sql                  load_employees.dump     objects.sql          test_employees_md5.sql

images                         load_salaries1.dump     README.md            test_employees_sha.sql

****@ts:~/test_db$ 

Now use following command to import SQL database:

mysql -u username -p < employees.sql 

If you want to install with two large partitioned tables, then use following command:

mysql -u username -p < employees_partitioned.sql 

Once the database is created You can perform essential MySQL commands:

• SELECT                : Used to choose specific data from your database
• INSERT  INTO     :  Inserts new data into a database
• UPDATE               : Update data in your database
• DELETE               : Deletes data from your database
• CREATE TABLE  : Create a new table in a database
• DROP TABLE       : Remove a table
• INDEX
• CREATE INDEX : create an index (search key for all the info stored) 
• DROP INDEX      : delete an index
• ALTER DATABASE : Modify an existing database

Show Databases

Run the following command after importing the test_db database:

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema | 

| challenges         | 

| employees          | 

| mysql              | 

+--------------------+

10 rows in set (0.00 sec)

mysql>

The newly created database is employees. Now, use this database using following command:

mysql> use employees;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql>

To show all Tables inside this Database Use the following command:

mysql> SHOW TABLES;

+----------------------+

| Tables_in_employees  |

+----------------------+

| current_dept_emp     | 

| departments          | 

| dept_emp             | 

| dept_emp_latest_date | 

| dept_manager         | 

| employees            | 

| salaries             | 

| titles               | 

+----------------------+

8 rows in set (0.00 sec)

mysql> 

There are 8 rows in a Table, use the following command to select all data from a table:

SELECT * FROM <table_name>;

******This cheat sheet is not yet completed. Please comment below to improve this cheat sheet.