MySQL:First DataBase:Heights

From virtualMV2015wiki
Jump to: navigation, search
 Home  MySQL <

Introduction

Before we delve into the finer details of MySQL I will demonstrate the basic functions of MySQL using a very simple Flatfile database (single table).

At this point don't worry too much about the details, but try to get an overall impression of how a database is created, how tables are created, how we add data to the tables, and how to view the data in table view.


Icon Objectives.png

Objectives

  • This example covers the basic statements you will use to:
    • create a database,
    • create and populate a table, and
    • view the table's structure and contents.

Design: Planning and requirements analysis

C Height.png
  • When designing a database, a good place to start is to work out what we want out of it. In this case the database will be used to record the names and heights (in milli-metres) of people you know.
  • Reports required will give us a good indication of what data we need to store in the database.
  • Your contract is to design a database that will manage the heights of a list of people in an organisation.

Sample report

  • In this case we would like to produce a report listing all the people in height order (see diagram).
  • Before you build the database you should sit down an sketch out the tables and fields needed to complete the task. Failure to do this will usually result in unstable tables that need to be modified at a later stage.
PerID PerFullName PerHeight
1 C. Lyon 1232
3 L.E.Fant 1500
2 G. Raff 5232

The Logical database view

To build an electronic database we need to identify some basic structures. The logical view is how we see the database.

  • Enitities:
    • Entity instance - may be a person, place, object, event or concept (In a Relational database the entity instance as a row in the table)
    • Entity Type - the collection of entities (represented as a table) e.g. People, Cities, Timesheets, Enrolments
  • Relationships (This is how the entities link together) e.g. A Student can enrol in many courses.
  • Attributes
    • Properties of each entity (a column (field) in a table) e.g. FirstName, LastName, PhoneNumber

Height database

In this (Heights) database it is easy to identify the entities involved. In this case a single person is an entity instance, and a collection of people are described using the entity type (People). As there is only one entity you don't need to worry about any relationships !

  • Logical view (How we see the database). This is also a basic data dictionary.
    • Database: dbHeight
    • Table: ht_tblPeople
      • ID - A number that will uniquely identify each person
      • FullName - The full name of the person
      • Height - The height in mm of the person

Using MySQL to Create the Heights database

Exercise 1: Using the command line interface to create a basic database

Enter the following commands into MySQL. After each command write down what happened.

# Create database ------------------------------------
SELECT Database();
CREATE DATABASE dbHeight;
USE dbHeight;
SELECT DATABASE();
# Create Tables ------------------------------------
CREATE TABLE tblPeople
(
  PerID INT UNSIGNED NOT NULL AUTO_INCREMENT,
  PerFullName VARCHAR(40),
  PerHeightmm INT,  
    PRIMARY KEY (PerID)
);
SHOW TABLES;
DESCRIBE tblPeople;
INSERT INTO tblPeople (PerFullName, PerHeightmm )VALUES
  ('C. Lyon',1232),
  ('G. Raff',5232);
SELECT * FROM tblPeople;
INSERT INTO tblPeople VALUES
  (NULL,'L. E. Fant',1500);
SELECT * FROM tblPeople ORDER BY PerHeightmm; 
 
DELETE FROM tblPeople WHERE PerFullName = 'G. Raff';
SELECT * FROM tblPeople WHERE PerHeightmm > 1400;

Explain what each command does.

I have been using a Web provider that only allows me to create one database so I have had to add a prefix to all my tables to keep different database tables apart(e.g. here I may have used ht_tblPeople).

Exercise 2: Automating the database creation

When a developer is asked to create a database they expect to work with two versions of the database. The first is a development version (often called the dev version) and the second is the live version that the client will use. This means that a developer can create and test the database before it goes into production. So, if you had to type in each command at the prompt to create a database how could you be sure that the development and live versions were the same?

The solution is to create a script. Simply put a script is a file containing the commands you type next to the command prompt that you can run as many times as you like.

For a MySQL database you could create two main script files, plus an assortment of test, update and/or query files:

  1. One that creates the database,
  2. One that creates the table structures.
  3. Several that can add test data, update the data or query the database.


Icon Notes.png

Notes

You could combine all the files into a single one if you were only going to run the script once!

Exercise 2.1: Creating the database

For this example you will need to create a text file called Height00.sql in your MySQL working folder

Note the # allows us to place a comment line in the sql file.

# Create database
DROP DATABASE IF EXISTS dbHeight;
SELECT Database();
CREATE DATABASE dbHeight;
USE dbHeight;
SELECT DATABASE();

Now use the MySQL SOURCE command to run the script, using the pattern SOURCE [enter your path]/Height00.sql

  • For example: If you have a file saved on H drive in a folder Mysql ...
mySQL>SOURCE H:/Mysql/Height00.sql

Exercise 2.2: Creating the table

Create the following text file called Height01.sql in your MySQL working folder

#Open the database
USE dbHeight;
# Create table
DROP TABLE IF EXISTS tblPeople;
CREATE TABLE tblPeople
(
  PerID       INT UNSIGNED NOT NULL AUTO_INCREMENT,
  PerFullName VARCHAR(40),
  PerHeightmm INT,  
    PRIMARY KEY (PerID)
);
SHOW TABLES;
DESCRIBE tblPeople;

Now use the MySQL SOURCE command to run the script.

mySQL>SOURCE .. path../Height01.sql

Exercise 2.3: Adding some data and querying the database

Create the following text file called Height02.sql in your MySQL working folder

#Open the database
USE dbHeight;
# Insert some data
# Note Here we will identify the columns here because of the autoincrement
INSERT INTO tblPeople (PerFullName, PerHeightmm )VALUES
  ('C. Lyon',1232),
  ('G. Raff',5232);
SELECT * FROM tblPeople;
# Now I will use a \N to represent a null value for the ID (NULL is ok too)  
INSERT INTO tblPeople VALUES
  (\N,'L. E. Fant',1500);  
SELECT * FROM tblPeople ORDER BY PerHeightmm;

Now use the MySQL SOURCE command to run the script.

mySQL>SOURCE [enter your path]/Height02.sql

Note that you should only run this script once, otherwise you will add the same people to your database!!. If you want to empty the table first run Height01.sql to recreate the table.

Exercise 3: Automating Inserting data (HeightData.txt)

What happens if you want to import a lot of data from say a spreadsheet? MySQL has the capability to load data from a text file. By default the data is separated by tabs (I have shown this using [Tab] in the example.. so use the [Tab] button don't type in [Tab] !!!.

So create the following file (HeightData.txt) using notepad (Note some editors do not save the tab properly and the data is not added correctly to the table):

\N[Tab]U. Rangatang[Tab]1345
\N[Tab]G. Eko[Tab]345

Now in Mysql enter..(use the correct path)

mysql> LOAD DATA LOCAL INFILE 'C:/MVsql/HeightData.txt' INTO TABLE tblPeople;

Icon References.png References


MySQL:First DataBase:Heights. (2017). In virtualMV's ( Michael Verhaart ) wiki. Retrieved December 18, 2017, from http://www.virtualmv.com/wiki/index.php?title=MySQL%3AFirst_DataBase%3AHeights    (zotero)