Basic Mysql Commands

Creating a database

Now we are going to create our database.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

The SHOW DATABASES statement shows all available databases on our system. Note that SQL statements are terminated with a semicolon. There are four databases present. The information_schema, mysql and performance_schema are MySQL system databases. The test database is available as a workspace for users to try things out. It is empty; there are no tables.

mysql> CREATE DATABASE My_Database;

This statement creates a new database. Throughout this tutorial, we will use the My_Database database. To create a new database, we need to have certain privileges. Remember that we have connected to the server with the root user, which is a superuser and has all privileges.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| My_Database               |
| mysql              |
| performance_schema |
| test               |
+--------------------+

Showing all databases, the My_Database database is among them.

mysql> use My_Database;
Database changed

In order to work with a database, we must first select it. We select a specific database with a use command.

mysql> SHOW TABLES;
Empty set (0.00 sec)

The SHOW TABLES statement shows all available tables in a database. Since it is a newly created database, no tables are found.

mysql> source cars.sql
Database changed
Query OK, 0 rows affected (0.20 sec)

Query OK, 1 row affected (0.08 sec)

...

In the first chapter, we have provided some sql scripts to create some tables. We use the source command to execute the cars.sql script, which creates a Cars table for us.

mysql> SHOW TABLES;
+----------------+
| Tables_in_My_Database |
+----------------+
| Cars           |
+----------------+

Now the SHOW TABLES statement displays one table available.

mysql> SELECT * FROM Cars;
+----+------------+--------+
| Id | Name       | Cost   |
+----+------------+--------+
|  1 | Audi       |  52642 |
|  2 | Mercedes   |  57127 |
|  3 | Skoda      |   9000 |
|  4 | Volvo      |  29000 |
|  5 | Bentley    | 350000 |
|  6 | Citroen    |  21000 |
|  7 | Hummer     |  41400 |
|  8 | Volkswagen |  21600 |
+----+------------+--------+

And this is the data in the table.
Creating a new user

Similarly to Unix root account, it is advised not to use the MySQL superuser root account for our tasks. We should use the root account only when it is necessary. We rather create a new account that we will use for our tasks. This user will have limited privileges. When using the root user we could accidentally do a lot of harm to our data.

mysql> CREATE USER test_user@localhost IDENTIFIED BY '34klq*';

The above statement created a new user called test_user. The accout has password 34klq*. The user is created, but he has no privileges.

mysql> GRANT ALL ON My_Database.* to test_user@localhost;

This statement grants all privileges to test_user for all database objects on the My_Database database.