Creating Databases and Tables

To see the current databases on the system: SHOW DATABASES ;

mysql> show databases ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| phpmyadmin         |
+--------------------+
4 rows in set (0.00 sec)

 

 

To create a database called Academy:  CREATE DATABASE Academy ;

mysql> CREATE DATABASE Academy ;
Query OK, 1 row affected (0.00 sec)

 

 

In order to work with a database it  must be selected with the “use” command:  use Academy ;

mysql> use Academy ;

 

 

Now the database has been defined and is in use, we can create a table to store the data:  CREATE TABLE

mysql> CREATE TABLE IF NOT EXISTS Students ( 

id int(5) NOT NULL auto_increment,

First varchar(20) NOT NULL,

Surname varchar(20) NOT NULL,

Age int(5) NOT NULL,

Town varchar(20) NOT NULL,

PIN int(5) NOT NULL,

Study varchar(20) NOT NULL,

Score int(5) NOT NULL,

PRIMARY KEY (id)

) ;

Which has created an empty table with the fields ready to populated:

 

 

 

To see the current tables for the database in use: SHOW TABLES ;

mysql> show tables ;
+--------------------+
| Tables in Academy  |
+--------------------+
| Students           |
+--------------------+
1 rows in set (0.00 sec)

 

 

We can then use the DESCRIBE  (aka DESC) command to check the table schema: DESCRIBE Students ;

mysql> desc Students;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(5)      | NO   | PRI | NULL    | auto_increment |
| First   | varchar(20) | NO   |     | NULL    |                |
| Surname | varchar(20) | NO   |     | NULL    |                |
| Age     | int(5)      | NO   |     | NULL    |                |
| Town    | varchar(20) | NO   |     | NULL    |                |
| PIN     | int(5)      | NO   |     | NULL    |                |
| Study   | varchar(20) | NO   |     | NULL    |                |
| Score   | int(5)      | NO   |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

 

 

We could also make a copy of an existing table, using CREATE TABLE pupils LIKE students;

mysql> CREATE TABLE pupils LIKE Students ;
Query OK, 0 rows affected (0.14 sec)

 

SHOW TABLES ;

mysql> show tables ;
+--------------------+
| Tables in Academy  |
+--------------------+
| pupils             |
| Students           |
+--------------------+
1 rows in set (0.00 sec)

Leave a Reply