Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.