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 inperson
: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
-
3Actually, 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 ismysql -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.joeyOct 5, 2013 at 10:58
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.