Basic MySQL Bash Commands
https://www.zyxware.com/sites/default/files/styles/user_image/public/default_images/index.png?itok=2YmREnrP
BY fazeela.ma
5 years ago
Technical-Solution
0 comments comment

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 

      Query would become,

      mysql> mysqldump -u root -p train 

      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 ARTICLE

/themes/custom/zyxpro_light/images/placeholder.png
close

on 04th April 2007 / by webmaster
Linux is the epitome of the FLOSS model. Technical Solution Linux installation Leave a reply Your email address will not be published. Required fields are marked * Dewey J. Corl (not verified) access_time 25 May 2020 - 08:47 Linux is not ready for the general public! (I am a Ubuntu user). For proof, 1. try to simply print labels on a Dymo label printer. It takes a LOT of work to get to the ease of label printing already available in Windows and Mac. 2. try syncing a modern Palm based PDA. Yes, jpilot and others will sync the main data, but pictures and midi files do not get transferred without a lot of extra setup. These are examples of applications that are not ready for the general public. Since an operating system only supports applications, Linux is not ready to be a common desktop for the general public. While we are waiting for the applications to catch up, keep up the good work!! Anoop John (not verified) access_time 25 May 2020 - 08:47 Dewey Yes you may be right. But these are not applications that a common man would use. A common man would use one of these applications - Internet browser, Chat Client, Email Client, Document Writer, Presentation Creator, Spreadsheet Editor, Movie Viewer, Music Player, CD/DVD Writer. Both examples you cited are more specific applications that only a small percentage of the whole population uses. It will take time before those hardware vendors identify the need from their perspective to address the Linux community. Krishnadas (not verified) access_time 25 May 2020 - 08:47 Dear Mr Anoop, Thank you very much for the free installation of LINUX done in my PC. I am getting immersed in the LINUX and seeing the versatility. To my surprise, I am able to read one of my old backup CD(wherein lot of my valuable file exist) done in DIRECT CD wizard (a custom cd writing software of Easy Cd creator in WIN98) which could not be read in XP. Installation of old version of easycd creator/direct cd program was not possible in XP. I thought that possible i lost all data. Very pleasant start! Srikanth N. S. (not verified) access_time 25 May 2020 - 08:47 Hi Anoop, My friend is thrilled with Ubuntu. One of his grouses with windows was that after OS installation, everything else is to be installed separately which is a real headache. WHereas if u install Linux, everything is installed as a package and he is thrilled to bits! Let linux installation spread in Trivandrum and your service in this direction is highly appreciated. Kepp up the good work Regards. PDA Freak (not verified) access_time 25 May 2020 - 08:47 Is it possible to install linux on a packardbell notebook ? Pagination Current page 1 Page 2 Next page Next › Last page Last » Add new comment
Leave a reply
Your email address will not be published. Required fields are marked *

Filtered HTML

  • Web page addresses and email addresses turn into links automatically.
  • Allowed HTML tags: <a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type='1 A I'> <li> <dl> <dt> <dd> <h2 id='jump-*'> <h3 id> <h4 id> <h5 id> <h6 id>
  • Lines and paragraphs break automatically.
The content of this field is kept private and will not be shown publicly.
CAPTCHA This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.