Below you’ll find some basic MySQL commands in case you are new to it.

First of all I recommend using the MySQL terminal to connect to your database server, because it forces you to learn the commands by hand. Using a GUI tool like PHPMyAdmin makes the job easier but removes the learning curve.

I am assuming your MySQL server is on your remote server (i.e., on the hosting account you rent from a hosting company). In that case you can login to your server via cPanel and create a database and a user with all privileges for that database (make sure to write down the names and password). After that make sure to add your IP address to the list of IPs allowed to access the MySQL server remotely.

If you don’t have access to cPanel or prefer to use the command line, here’s the process. First of all install the MySQL client on your machine:

sudo apt-get install mysql-client

Then login to the MySQL server using the root user:

mysql -h HOSTNAME -u root -p

Once logged in, create the database:

create database db_name;

Create the user:

create user db_user;

And finally add all the privileges, while creating a password to the user:

grant all on db_name.* to 'db_user'@'HOSTNAME' identified by 'db_password';

Notice that if you are logged in locally, the hostname will be ‘localhost’.

Now you should log out as root and log in as the normal user you just created:

mysql -h HOSTNAME -u USERNAME -p

You’ll be asked to type the password of the user. If the connection is successful you’ll see a welcome message and the mysql prompt.

Now here’s a list of commands you can use to play arround:

1. Creating a database
create database DBNAME;

2. Displaying all available databases on the server
show databases;

3. Selection a database for usage
use DBNAME;

4. Creating a table inside the selected database

create table users(
name varchar(30),
password int,
email varchar(30)
);

5. Displaying all tables inside a database
show tables;

6. Getting information about the table (columns, key, NULL values, etc)
describe TABLENAME;

7. Inserting an entry into a table
insert into users(name, password, email) values('daniel',12345,'daniel@test.com');

8. Deleting an entry from a table
delete from users where name='daniel';

9. Adding a new column
alter table users add facebook varchar(30);

10. Adding a new id column to work as the primary key
alter table users add id int not null auto_increment first, add primary key(id);

11. Changing the default value of a column
alter table users modify column email varchar(30) default 'test@test.com';

12. Updating the value of a column
update users set email='test@test.com' where id=5;

13. Displaying the last 5 rows of a table
select * from users order by id desc limit 5;

14. Creating a table with a foreign key

CREATE TABLE child (
    id INT, 
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id) 
        REFERENCES parent(id)
        ON DELETE CASCADE
);

15. Performing a join

SELECT a.id, a.name FROM users a, payments b WHERE b.status=1 AND a.id = b.id;

16. Replacing information on columns
UPDATE table_name SET column_name = REPLACE(column_name, 'search', 'replace');

17. Selecting and deleting from one table based on data from another

SELECT * FROM t_salary WHERE employee_id NOT IN (select id FROM t_employee);

DELETE FROM t_salary WHERE employee_id NOT IN (select id FROM t_employee);

18. Select entries from a specific month or year

SELECT * FROM t_table WHERE month(date_column) = 10 AND year(date_column) = 2014;

Notice that the above query is very slow though, because it does not use indexes, so the DB will need to parse all the rows. If you have a lot of entries it will be much better to use this:

SELECT * FROM t_table WHERE date_column > '2014-10-00' AND date_column < '2014-10-40';

19. Disconnecting
quit