How to use SQL in terminal?

Question

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

Answer 1

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..

Answer 2

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

Answer 3

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

Answer 4

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.

Answer 5

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>