How to use SQL in terminal?

I want to create simple tables, insert values in it, and do queries. How to perform them in the terminal?

5 Answers

Here is the syntax to execute sql statement from terminal

I'm assuming that you are using MySQL.

Syntax:

mysql -u user_name -p password -e 'SQL Query' database 

Clearificance:

-u : Specify mysql database user name -p : Prompt for password -e : Execute sql query database : Specify database name 

Example:

• If you want to create a table person then:

  mysql -u root -p -e 'Create table person(PersonID int, LastName varchar(255), FirstName varchar(255))' mydb 

  where root is the username, mydb is the name of the database. Similary you can execute any query you want.

• If you want to insert values in person:

  mysql -u root -p -e 'Insert into person(PersonID,LastName,FirstName) Values(100,"Kumar","Saurav")' mydb 

• If you want to select all the information from person and want to save in a file:

  mysql -u root -p -e 'Select * from person' mydb > personinfo 

And of-course you can create a database using terminal itself

• To create database mydb execute following command in terminal:

  mysql -u root -p -e 'create database mydb' 

  it will silently create a database mydb without giving any message/output.

• To list all the databases execute this command in terminal:

  mysql -u root -p -e 'show databases' 

Hope it helps you.. Reply if you need further assistance..

You make a database by typing

 mysql 

In the prompt you enter, you then start by creating your database (as explained by onik in the comments):

 CREATE DATABASE dbname 

Once you have made that database, you can experiment with it. You can simply type mysql in a terminal and you can get do anything sql related you want. It is possible that you have create to a role in your database with your username.

As described in Sauruv's answer you can also connect to the database as follows (without the space between p and your password or better, just do not use the -p option and you will get a password prompt [credits go to onik]):

Syntax:

mysql -u user_name -ppassword dbname -u : Specify mysql database user name -p : Prompt for password dbname : Specify database name 

mycli

mysql default console is fine and pretty, but maybe prettier is mycli (and it Postgres alternative, pgcli).

Installation: On Ubuntu 16.04+ is on official repos: sudo apt install mycli

$ mycli --help Usage: mycli [OPTIONS] [DATABASE] A MySQL terminal client with auto-completion and syntax highlighting. Examples: - mycli my_database - mycli -u my_user -h my_host.com my_database - mycli mysql://my_user@my_host.com:3306/my_database 

Reference

• HomePage
• Documentation
• Github repo
• nixCraft post about mycli

mysql is a simple command-line tool. mysql is command line and it is very easy to use. Invoke it from the prompt of your command interpreter as follows:

$ mysql 

Output

mysql> 

You may need to provide mysql username, password and hostname, use:

$ mysql --user=your-user-name --password=your-password mysql> 

To list database type the following command

mysql> show databases; 

Output

+--------------------+ | Database | +--------------------+ | information_schema | | mysql | +--------------------+ 2 rows in set (0.00 sec) 

information_schema and mysql are name of databases. To use these database and to list available tables type the following two commands:

mysql> use mysql; 

Output:

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 

Now list tables:

mysql> show tables; 

Output:

+---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | func | | help_category | | help_keyword | | help_relation | | help_topic | | host | | proc | | procs_priv | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 17 rows in set (0.00 sec) mysql> 

There is sqlline which could connect to any rdbms (Oracle, mysql, postgres, h2 and others), also csv, cassandra, elastic-search, mongo (via apache-calcite).

There is a project demos page with short demo videos It provides multiline-editing, syntax highlighting, smart autocompletion, dialect support.

It could be embedded and no need to install it.