Creating A MySQL Database

Background

If you know SQL, then MySQL is easy to use. This exercise demonstrates how to easy it is to work with a MySQL database.

Objectives

• Grant privileges;
• Create a database;
• Create table;
• Load data into tables;
• Query the database using various SQL commands.

Steps

1. Create a Database
• Grant privileges to a database called 'pet'.

mysql>GRANT ALL ON pet.* TO 'mysql_name'@'client_host';

• Create the database.

mysql>CREATE DATABASE pet;

• Select the database.

mysql>USE pet;

2. Create a Table
• Create a table.

mysql>CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
->species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

3. View the Database Contents
• List existing tables.

mysql>SHOW TABLES;

• Verify that the table was created as expected.

mysql>DESCRIBE pet;

4. Create Data
• Use vi to create a text file called pet.txt containing one record per line, with values separated by tabs.
• To represent blank, or unknown values, in the text file, use \N to represent a NULL value.

name	owner	species	sex	birth	death
Fluffy	Harold	cat	f	1993-02-04
Claws	Gwen	cat	m	1997-03-17
Buffy	Harold	dog	f	1989-05-13
Fido	Ben	dog	m	1990-08-27
Rover	Dave	dog	m	1989-08-31	2005-07-21
Chirpy	Gwen	bird	f	1998-09-11
Whistler	Gwen	bird		1999-12-19
Peter	Ben	rabbit	m	1997-04-21



5. Load Data Into the Table
• Load the file into the table.

mysql>LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

• Add a new record.

mysql>INSERT INTO pet VALUES
->('Puffball','Bob','hamster','f','2001-03-31',NULL);

6. Retrieve Data From the Table
• Obtain a listing of the table.

mysql>SELECT * FROM pet;

• Obtain a listing of a particular record.

mysql>SELECT * FROM pet WHERE name = 'Rover';

• Find the older pets.

mysql>SELECT * FROM pet WHERE birth <= '1998-1-1';

• Find all cats and dogs.

mysql>SELECT * FROM pet WHERE species = 'cat' OR species = 'dog';

• Find all cats and dogs, but only list their names and owners.

mysql>SELECT name, owner FROM pet WHERE species = 'cat' OR species = 'dog';

• List the owners.

mysql>SELECT DISTINCT owner FROM pet;

7. Date Calculations
• List pet names alphabetically and their ages.

mysql>SELECT name, birth, CURDATE(),
-> (YEAR(CURDATE())-YEAR(birth)) - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
-> AS age FROM pet ORDER BY name;

• Use IS NOT NULL in listing pet names alphabetically and their ages.

mysql>SELECT name, birth, death,
->(YEAR(death)-YEAR(birth)) - (RIGHT(death,5)<RIGHT(birth,5))
->AS age FROM pet WHERE death IS NOT NULL ORDER BY age;

8. Pattern Matching
• Find all pets whose names start with 'f'.

mysql>SELECT * FROM pet WHERE name LIKE 'f%';

• Find all pets whose names ending in 'fy'.

mysql>SELECT * FROM pet WHERE name LIKE '%fy';

9. Counting Rows
• Obtain a count of how many pets there are by species.

mysql>SELECT sex, COUNT(*) FROM pet GROUP BY species;

10. Multiple Tables
• Use vi to create a text file called event.txt containing one record per line, with values separated by tabs.
• To represent blank, or unknown values, in the text file, use \N to represent a NULL value.

name	date	type	remark
Fluffy	1995-05-15	litter	4 kittens, 3 female, 1 male
Buffy	1993-06-23	litter	5 puppies, 2 female, 3 male
Buffy	1994-06-19	litter	3 puppies, 3 female
Chirpy	1999-03-21	vet	needed beak straightened
Fido	2005-08-03	vet	broken rib
Rover	1991-10-12	kennel
Fido	1991-10-12	kennel
Fido	1998-08-27	birthday	Gave him a new chew toy
Claws	2001-03-17	birthday	Gave him a new flea collar
Whistler	2000-12-09	birthday	First birthday



11. Create a Second Table
• Create a table.

mysql>CREATE TABLE event (name VARCHAR(20), date DATE,
->type VARCHAR(15), remark VARCHAR(255));

12. Load Data Into the Table
• Load the file into the table.

mysql>LOAD DATA LOCAL INFILE '/path/event.txt' INTO TABLE event;

13. Use INNER JOIN
• Use INNER JOIN to build a complex query.

  mysql>SELECT pet.name,
  ->(YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5))
  ->AS age, remark FROM pet INNER JOIN event
  ->ON pet.name = event.name WHERE event.type = 'litter';

14. Exit
• Use QUIT to exit out of the server

  mysql>QUIT

Congratulations! You have successfully created and used a MySQL database.