Learning MySQL and MariaDB
PART II
Database Structures
To start, let's create a database that will contain information about birds and call it rookery. To do this, enter the following from within the mysql client:
CREATE DATABASE rookery
DROP DATABASE rookery
CREATE DATABASE rookery
CHARACTER SET latin1
COLLATE latin1_bin
Now that we've created a database, let's confirm that it's there, on the MySQL server. To get a list of databases, enter the following SQL statement:
SHOW DATABASES
+-+
| | Database |
+-+
| | information_schema |
| | rookery |
| | mysql |
| | test |
+-+
Three other databases that were created when MySQL was installed on the server.
Before beginning to add tables to the rookery database, enter the following command
Into the mysql client:
USE rookery
CREATE TABLE birds (
Bird_id INT AUTO_INCREMENT PRIMARY KEY
Scientific_name VARCHAR (255) UNIQUE
Common_name VARCHAR (50)
Family_id INT
Description TEXT)
The AUTO_INCREMENT option tells MySQL to automatically increment the value of this
Field. It will start with the number 1, unless we specify a different number.
DESCRIBE birds
+-+ +
| | Field | Type | Null | Key | Default | Extra | |
+-+ +
| | bird_id | int (11) | NO | PRI | NULL | auto_increment |
| | scientific_name | varchar (255) | YES | UNI | NULL |
| | common_name | varchar (50) | YES | | NULL |
| | family_id | int (11) | YES | | NULL |
| | description | text | YES | | NULL |
+-+ +
Inserting Data
INSERT INTO birds (scientific_name, common_name)
VALUES ('Charadrius vociferus',' Killdeer')
('Gavia immer',' Great Northern Loon')
('Aix sponsa',' Wood Duck')
('Chordeiles minor',' Common Nighthawk')
('Sitta carolinensis',' White-breasted Nuthatch')
('Apteryx mantelli',' North Island Brown Kiwi')
SELECT * FROM birds
+-- +
| | bird_id | scientific_name | common_name | family_id | description | |
+-- +
| | 1 | Charadrius vociferus | Killdeer | NULL | NULL | |
| | 2 | Gavia immer | Great Northern... | | | NULL | NULL |
| | 3 | Aix sponsa | Wood Duck | NULL | NULL | |
| | 4 | Chordeiles minor | Common Nighthawk | NULL | NULL | |
| | 5 | Sitta carolinensis | White-breasted... | | | NULL | NULL |
| | 6 | Apteryx mantelli | North Island... | | | NULL | NULL |
+-- +
More Perspectives on Tables
SHOW CREATE TABLE birds\ G
Table: birds
Create Table: CREATE TABLE `birds` (
`bird_ id` int (11) NOT NULL AUTO_INCREMENT
`scientific_ name` varchar (255) COLLATE latin1_bin DEFAULT NULL
`common_ name` varchar (50) COLLATE latin1_bin DEFAULT NULL
`family_ id` int (11) DEFAULT NULL
52 | Chapter 4: Creating Databases and Tables
`accountion` text COLLATE latin1_bin
PRIMARY KEY (`bird_ id`)
UNIQUE KEY `scientific_ name` (`scientific_ name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin
CHAPTER 5
Altering Tables
Use the mysqldump utility to make a backup of the tables you're altering or the whole database.
Backup tabl
Mysqldump-- user = 'russell'-p\
Rookery birds > / tmp / birds. Sql
Backup database
Mysqldump-- user = 'russell'-p\
Rookery > rookery. Sql
Restore:
Later on, if you have a problem and need to restore the database back to where you were at the end of a chapter, you would enter something like the following from the command line:
Mysql-- user = 'russell'-p\
Rookery
