Technical Solution

Basic MySQL Bash Commands

Fazeela.ma | | 3 min read

Command line provides a platform to that takes input from keyboard to interact with the operating system. In Linux Operating System's, the Command line is often referred to bash.

MySQL in Command Line

About 90% of us would have created database, and its table using phpmyadmin user interface. How would it be if we can create database, tables etc. through bash ? Sure to add as an asset to our knowledge base. So lets get started.

  • Follow up the instructions below before you explore sql commands in bash. Now lets assume we are good to go with our pre requesites installed.
apt-get install mysql-client;
apt-get install mysql-server;
  • After installing, lets get started with the basics,

    We need to switch on to mysql mode, open your terminal. Either open using keyboard shortcut 
    Ctl+Alt+t

    or Click on dash home, type terminal and select.

    • Login 
      mysql -u username -p;

      Enter the password on prompting. Replace username with yours.

      mysql>

      Everything went right for you, if you get the above to the left corner of bash. Or else something went wrong in enabling mysql, go back to make corrections or ask for technical support.

    • Create database 
       mysql> CREATE DATABASE database_name;

      Lets assume the name of our database as train. Query would become

      Enter the password on prompting. Replace username with yours.

       mysql> CREATE DATABASE train;
        Query OK, 1 row affected (0.03 sec)
    • Create a user
      • Create a user without password. 
         mysql> CREATE USER 'username'@'hostname';

        Lets assume the name of the new user as me. Query would become

         mysql> CREATE USER 'me'@'localhost';
        Query OK, 0 rows affected (0.10 sec)
      • Create a user with password
         mysql> CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';

        Lets assume the name of the new user and password as me. Query would become

         mysql> CREATE USER 'me'@'localhost' IDENTIFIED BY 'me';
        Query OK, 0 rows affected (0.10 sec)
    • Create a table 
      mysql> USE database_name;
      mysql> CREATE TABLE table_name (column_name1 column_type1, column_name2 column_type2, );

      Query would become,

        USE train;
  Database changed 
      
  mysql> create table train_details (Id int(11), Name char(20), StartingStation VARCHAR(20));
  Query OK, 0 rows affected (0.29 sec)
    • Insert value into table 
      mysql> INSERT INTO TABLE_NAME (column_name1, column_name2, column_name3) VALUES (coloumn_value1, coloumn_value2, coloumn_value3);

      Query would become,

      mysql> INSERT INTO  train_details(Id, Name, StartingStation) VALUES (1, 'Shadabdi Express', 'Aluva' );
       Query OK, 1 row affected (0.08 sec)
    • Update a table 
      mysql> UPDATE table SET coloumn_name = coloumn_value, WHERE conditions;

      Query would become,

       UPDATE train_details SET StartingStation = 'Ernakulam'  WHERE Id =1;
 Query OK, 1 row affected (0.08 sec)
 Rows matched: 1  Changed: 1  Warnings: 0
    • Delete a value from table 
      mysql> DELETE FROM table_name WHERE column_name = coloumn_value;

      Query would become,

      mysql> DELETE FROM train_details WHERE Id = 1;
       Query OK, 1 row affected (0.04 sec)
    • Table Structure 
      mysql> DESC table_name;

      Query would become,

      mysql> DESC train_details;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| Id              | int(11)     | YES  |     | NULL    |       |
| Name            | char(20)    | YES  |     | NULL    |       |
| StartingStation | varchar(20) | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
    • Drop a table
      mysql> DROP table table_name;

      Query would become,

      mysql> DROP table train_details;
       Query OK, 1 row affected (0.08 sec)
    • Delete a database
      mysql> DROP DATABASE train;

      Query would become,

      mysql> DROP DATABASE train;
       Query OK, 1 row affected (0.08 sec)
    • Export a database
      mysql> mysqldump -u username -p password database_name > sqlfile_name.sql;

      Query would become,

      mysql> mysqldump -u root -p train > train.sql;
       Query OK, 1 row affected (0.08 sec)
    • Import a database
      mysql> mysqldump -u username -p password database_name  sqlfile_name.sql;

      Query would become,

      mysql> mysqldump -u root -p train  train.sql;
Query OK, 1 row affected (0.08 sec)

      There is another method to do this, the one i prefer most

      mysql> USE DATABASE database_name;
mysql> SOURCE pathtosqldfile.sql

      The query will become,

      mysql> USE DATABASE train;
mysql> SOURCE /home/zyx/Projects/train.sql
Query OK, 1 row affected (0.08 sec)

For me it was more like a nightmare to work on command prompt, once we learn the way to work on commad prompt, i bet we insist ourselves to do things on bash. The commands we used are all mysql queries. Bash can perform a task much faster than using Graphical Interface. It also allow access to more commands and scripts.

 

 

Related Topics