Create a new database in MySQL, add a table and fields using the terminal

ID: 225

Category: MySQL

Date Posted: 22/04/2012

Page Impressions : 3,358

Create a new database in MySQL, add a table and fields using the terminal

To access MySQL database you first need to login. You will be prompted for your MySQL password. This is the root password you used when you set up MySQL or the password supplied by your webhosts.

mysql -u root -p

Once you have succesfully logged in to MySQL the mysql> prompt will appear

mysql>

The first thing we need to do is create a new database called test. To create a new database enter issue the following command

create database test;

Lets now show the databases assigned to this user

show databases;

In order to use the database issue the following command

use test;

Now we create the table with the three fields id, first_name, surname. We also set the table auto increment the id number so each record is unique.

CREATE TABLE users_tb (id INT( 3 ) NOT NULL AUTO_INCREMENT, first_name VARCHAR( 25 ) NOT NULL , surname VARCHAR( 50 ) NOT NULL , UNIQUE (id));

The database fields set, we need to enter some records. Copy and paste each below.

insert into users_tb (first_name,surname) values ('Joe','Bloggs');

insert into users_tb (first_name,surname) values ('Andy','Bloggs');

insert into users_tb (first_name,surname) values ('Mary','Bloggs');

Let's display the table and data

select * from users_tb;

Now we can manipulate the database using some MySQL queries.

Order the database by id in descending order

select * from users_tb order by id desc;

+----+------------+---------+
| id | first_name | surname |
+----+------------+---------+
| 3 | Mary | Bloggs |
| 2 | Andy | Bloggs |
| 1 | Joe | Bloggs |
+----+------------+---------+
3 rows in set (0.00 sec)

Order the database by id in ascending order

select * from users_tb order by id asc;

+----+------------+---------+
| id | first_name | surname |
+----+------------+---------+
| 1 | Joe | Bloggs |
| 2 | Andy | Bloggs |
| 3 | Mary | Bloggs |
+----+------------+---------+
3 rows in set (0.00 sec)

Order the database by first name

select * from users_tb order by first_name;

+----+------------+---------+
| id | first_name | surname |
+----+------------+---------+
| 2 | Andy | Bloggs |
| 1 | Joe | Bloggs |
| 3 | Mary | Bloggs |
+----+------------+---------+
3 rows in set (0.00 sec)

Count the number of users in the table

select count(id) as number_of_users from users_tb;

+-----------------+
| number_of_users |
+-----------------+
| 3 |
+-----------------+
1 row in set (0.00 sec)

Count the number of surname which = Bloggs

select count(surname) as number_of_surnames from users_tb where surname='bloggs';

+--------------------+
| number_of_surnames |
+--------------------+
| 3 |
+--------------------+
1 row in set (0.00 sec)

The next thing to do is logout of MySQL. To logout enter the following command.

exit;

MySQL will print the Bye!! and drop you back to the terminal. Now login with your new user details