What are basic and important MySQL commands?

Introduction:

Structured Query Language i.e. SQL is used to create database tables, insert data into these tables, read data from tables and also providing data access control. For execution of these command we need to have some tool which will act as interface for it. The MySQL is acting as interface for its user in order to execute the SQL commands. The commands which are executed in the MySQL open source are called as MySQL commands. In this article, a handy document for MySQL commands is created, so that it can be easily used.

Note:
1. Here please not that, whenever we see # sign in command, it means that the command need to be executed from unix shell.
2. We have another notation that is mysql>. Whenever you see this sign it means that, the command need to be fired from SQL promt.

Login to Unix Shell:

To login Unix shell below command is used. In this command ISChostname indicates the hostname to which the shell needs to be connected. Go to the bin directory of MySQL installation and then to mysql directory in order to execute the command.

# [mysql dir]/bin/mysql -h ISChostname -u root -p

Creating Database and executing different commands at Database Level:

1) To create database fire the create command as mentioned below. Here ISCDB is the name of database.

mysql> create database ISCDB;

2) To list the all the databases in the MySQL database, use following command. No need to mention any specific database here. All the databases which are present in DB are shown on the database console.

mysql> show databases;

3) If you are using any database and in between wants to switch the database, here is the command which will achieve it. Here ISCDB is the name of database to which you want to switch.

mysql> use ISCDB;

4) How to see all the tables in the ISCDB? Is this your query, then below command help you out to see all the tables from the database.

mysql> show tables;

5) To see the structure of columns and what type of content each column contains as well as to see which are the constraint on each column, use the below command. Here ISCDB is the name to database of which structure is required to be displayed.

mysql> describe ISCDB;

6) In any case, if given database does not satisfy the requirement. In that case, it is required to delete that database. So to delete the database use below given command. Here ISCDB is the name of database which will get deleted from the database.

mysql> drop database ISCDB;

7) Any table from database is needed to be deleted then below command help to delete the table. ISCDBTable is the name of table from ISCDB database which will get deleted from database.

mysql> drop table ISCDBTable;

Using SQL commands:

1) For selecting the data from table, use the below select query command. ISCDBTable is table from which data will be get fetched.

mysql> SELECT * FROM ISCDBTable;

2) You want to know what are the columns exist in the database table. Below command will list the column names from the table – ISCDBTable.

mysql> show columns from ISCDBTable;

3) If you want to fetch unique records from the table on particular column then use the below distinct command in order to get unique data content. Here, ISC_COLUMN is the name of column on which distinct command have to be executed and ISCDBTable is the table.

mysql> SELECT DISTINCT ISC_COLUMN FROM ISCDBTable;

4) What if we get data is sorted as well in ordered form. Yes there is command to get data in sorted and in ordered form. In the below command data from ISCDBTable will be in order form based on the column ISC_COLUMN content and it will be descending order i.e. DESC. In case, if want data in ascending order then use ASC keyword.

mysql> SELECT * FROM ISCDBTable ORDER BY ISC_COLUMN DESC;

5) What to know, how many rows exist in the table, once you fire the below command, number of rows available in the table will get displayed on the console. No of rows available in the table ISCDBTable will be displayed as output.

mysql> SELECT COUNT(*) FROM ISCDBTable;

6) To delete specific row from table use below delete command. Here we have assumed the content of column ISC_COLUMN is "test" from table ISCDBTable.

mysql> DELETE from ISCDBTable where ISC_COLUMN = 'test';

7) The most important command is deleting column from database. So in case, if you want to delete the column from table use the below command. The column ISC_COLUMN will be get deleted from ISCDBTable with below command.

mysql> alter table ISCDBTable drop column ISC_COLUMN;

8) Another important command is adding new column to table. So this task easily resolved by Alter command. Use below alter command to add new column. The new column ISC_COLUMN will be get added to table ISCDBTable with below command.

mysql> alter table ISCDBTable add column ISC_COLUMN varchar (20);

9) The interesting command to change the column name is given below. If you want to change the column name from OLD_ISC_COLUMN to NEW_ ISC_COLUMN column from the table ISCDBTable then use the below command

mysql> alter table ISCDBTable change OLD_ISC_COLUMN NEW_ISC_COLUMN varchar (50);

Using Database commands:

1) Creating database dumps from the backup, use below command. This command is required to be fired from Unix shell. Below command will create the database dump for ISCDB database.

# [mysql dir]/bin/mysqldump -u username -ppassword --databases ISCDB >/tmp/ISCDB.sql

2) In order to create dump for specific table from databse, use the below Unix shell command. In this command, ISCDB is the database name and ISCDBTable is the table name for which dump is needed to be created.

# [mysql dir]/bin/mysqldump -c -u username -ppassword ISCDB ISCDBTable > /tmp/ISCDB.ISCDBTable.sql

3) Are you looking for how to restore the database, use below important command to restore the database. Here ISCDB is the database name which will get restored.

# [mysql dir]/bin/mysql -u username -ppassword ISCDB < /tmp/ ISCDB.sql

Loading CSV File in database:

The interesting command from MySQL is loading the content from CVS file to database. Generally the data in bulk format is created suing CSV (Comma Separated Values). It is command form of saving the data. CSV is popular form of saving different content in flat file format. Now, if you have data in CSV format, and you need to have same data in database as well then you can use below command to transfer the data from CSV file to database.

mysql> LOAD DATA INFILE '/tmp/MYCSVFILE.csv' replace INTO TABLE ISCDBTable FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (ISC_COLUMN1, ISC_COLUMN2, ISC_COLUMN3);

In above command, ISCDBTable it the table to which data from MYCSVFILE.csv CSV file is transferred to columns such as ISC_COLUMN1, ISC_COLUMN2 and ISC_COLUMN3.