16

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

5 Answers 5

10

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

9

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
2
  • 3
    Actually, the syntax you specified only connects to the specified database, it doesn't create it. You'll have to first connect without specifying a database and run CREATE DATABASE [dbname]. Furthermore, if you specify the password on the command line (not recommended, the syntax is mysql -u user_name -ppassword database, without the space between p and your password. You can also omit the password here and MySQL will prompt you for it, which is safer. Oct 4, 2013 at 7:21
  • @onik you are absolutely right. Next time, feel free to either edit my answer or to copy it and elaborate it into your own answer. By way of thanks, I have found a good answer of you on another question and I have upvoted it.
    – don.joey
    Oct 5, 2013 at 10:58
2

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

screenshot

Reference

1

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

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .