MySQL quick commands

It is a relational database management system, which is multiplataform and support several storage engines (MyISAM, InnoDB, MERGE, MEMORY). It also has triggers, functions, stored procedures, GIS, replication, clustering. It is licensed under GPL (and some other commercial licenses )

It was developed by Michael Widenius and David Axmark, and it was released as free by 2000 under the GPL, even though we know now by 2010 Oracle acquired Sun Microsystems so now it belongs to Oracle.

So this is a brief post in which I’m going to explain you the most basic commands you should know to start using mysql from the command line with no problem

so the first one you should know to connect to mysql is

Command:

mysql [<options>] database

Common options:
–user, -u: MySQL user name
–password, -p: user’s password
–host, -h: connect to the server on the given host

so typicaly when you want to connecto to the database you end up using something like this

$ mysql -u root -p

and here it’ll ask for the root pass. If you don’t type any database, you’ll just get in and then you can select the database that you wanna work with.

Then once we are in we want to start working with a database, so now we’ll use the show databases command in order to get the list of all the databases we have, just like this

mysql> show databases;

we could also use this command alongside the word like, so we could refine a bit our search if we know already a dbname we are looking for. It would look something like this

mysql> show databases like db%;

Now once we have found the database we want to work with, we select it with the use command. Quite simple as

mysql> use exampledb;
Database changed

Here in the database as before you are able to show now the tables or use the word like to show tables you may know the name. Another useful way to use show tables is when you want to see the tables from another database different than the one you are currently using. That would be something like

mysql> show tables from mysql;

If you would like to now the fields of any table you do this using the desc command

mysql> desc people

in this case I’m wanting to know the fields of a table name people. Also very useful could be the following

mysql> show create table people;

this is useful when you would like to replicate a table, since this command will show you the query you used the first time you created that table.

And finaly a very useful command is the use of mysqldump to back up and restore databases. To back up a single database yo use

mysqldump [<options>] <database> [<table> <table> …]

To back up multiple database:

mysqldump [<options>] –database [<db> <db> …]

and to restore from a backup_file:

mysql [<options>] <database> < <backup_file.sql>