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

`description` 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.

備份表

mysqldump -- user = ' russell ' - p \

rookery birds > / tmp / birds . sql

備份數據庫

mysqldump -- user = ' russell ' - p \

rookery > rookery . sql

恢復：

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 < rookery - ch3 - end . sql

The basic syntax for the ALTER TABLE is simple:

ALTER TABLE table_name changes;

ALTER TABLE bird_families ADD COLUMN order_id INT ;

To make a copy of the birds table, we ’ ll use the CREATE TABLE statement with the LIKE clause. This was covered in Chapter 4) In fact, let ’ s create the new table in the test database just to work separately on it (this isn ’ t necessary, but it ’ s a good practice to have a development database separate from the live one. To do this, enter the following in mysql on your server:

CREATE TABLE test.birds_new LIKE birds;

This DESCRIBE statement will show you the structure of the new table. Because we copied only the structure of the birds table when we created the new table, there is no data in this table. To do that, we could use an INSERT statement coupled with a SELECT like so:

INSERT INTO birds_new

SELECT * FROM rookery.birds;

This will work fine. However, there ’ s another method that creates a table based on another table and copies over the data in the process:

CREATE TABLE birds_new_alternative

SELECT * FROM rookery.birds;

This will create the table birds_new_alternative with the data stored in it. However,

if you execute a DESCRIBE statement for the table, you will see that it did not set the bird_id column to a PRIMARY KEY and did not set it to AUTO_INCREMENT . So in our

situation, the first method we used to create the table is preferred, followed by an INSERT INTO...SELECT statement. Enter the following to delete the alternative table:

DROP TABLE birds_new_alternative;

ALTER TABLE birds_new DROP COLUMN wing_id ;

There ’s no UNDO statement in MySQL

ALTER TABLE birds_new ADD COLUMN wing_id CHAR ( 2 ) AFTER family_id ;

This will put the wing_id column after the family_id in the table. Run the DESCRIBE

statement again to see for yourself. By the way, to add a column to the first position, you would use the keyword FIRST instead of AFTER . FIRST takes no column name.

ALTER TABLE birds_new

ADD COLUMN body_id CHAR ( 2 ) AFTER wing_id ,

ADD COLUMN bill_id CHAR ( 2 ) AFTER body_id ,

ADD COLUMN endangered BIT DEFAULT b '1' AFTER bill_id ,

CHANGE COLUMN common_name common_name VARCHAR ( 255 );

ALTER TABLE birds_new

ADD COLUMN body_id CHAR ( 2 ) AFTER wing_id ,

ADD COLUMN bill_id CHAR ( 2 ) AFTER body_id ,

ADD COLUMN endangered BIT DEFAULT b '1' AFTER bill_id ,

CHANGE COLUMN common_name common_name VARCHAR ( 255 );

In one of the columns added here, the endangered column, we ’re using a data type we haven ’t used yet in this book: BIT . This stores one bit, which takes a values of either set or unset —basically, 1 or 0.

UPDATE birds_new SET endangered = 0 WHERE bird_id IN ( 1 , 2 , 4 , 5 );

SELECT bird_id, scientific_name, common_name

FROM birds_new

WHERE endangered \G

*************************** 1. row ***************************

bird_id: 3

scientific_name: Aix sponsa

common_name: Wood Duck

Notice that in the WHERE clause of the SELECT statement we are selecting rows where the endangered column has a value. For the column data type of BIT , this is all that ’s needed , and it has the same effect as if we specified WHERE endangered = 1 . To filter on the reverse —to select rows in which the bit for the endangered column is not set —use the NOT operator like so:

SELECT * FROM birds_new WHERE NOT endangered \ G

如果希望endangered可以存0和1以為的值， 下面語句修改endangered 列成枚舉類型 。

ALTER TABLE birds_new MODIFY COLUMN endangered

ENUM ( 'Extinct' ,

'Extinct in Wild' ,

'Threatened - Critically Endangered' ,

'Threatened - Endangered' ,

'Threatened - Vulnerable' ,

'Lower Risk - Conservation Dependent' ,

'Lower Risk - Near Threatened' ,

'Lower Risk - Least Concern' )

AFTER family_id ;

Let ’s run the SHOW COLUMNS statement with the LIKE clause to see just the column settings for the endangered column:

SHOW COLUMNS FROM birds_new LIKE 'endangered' \G

*************************** 1. row ***************************

Field: endangered

Type: enum('Extinct','Extinct in Wild',

'Threatened - Critically Endangered',

'Threatened - Endangered',

'Threatened - Vulnerable',

'Lower Risk - Conservation Dependent',

'Lower Risk - Near Threatened',

'Lower Risk - Least Concern')

Null: YES

Key:

Default: NULL

Extra:

Dynamic Columns

This is something that is available only in MariaDB, as of version 5.3. It ’s similar

to an ENUM column

。。。

Renaming a Table

RENAME TABLE rookery . birds TO rookery . birds_old , test . birds_new TO rookery . birds ;

If there was a problem in doing any of these changes, an error message would be generated

and none of the changes would be made. If all of it went well, though, we should

have two tables in the rookery database that are designed to hold data on birds.

Let ’s run the SHOW TABLES statement to see the tables in the rookery database. We ’ll

request only tables starting with the word birds by using the LIKE clause with the wildcard,

% . Enter the following in mysql :

SHOW TABLES IN rookery LIKE 'birds%';

PART III

Basics of Handling Data

CHAPTER 6

Inserting Data

The INSERT statement adds rows of data into a table. It can add a single row or multiple rows at a time. The basic syntax of this SQL statement is:

INSERT INTO table [( column , …)]

VALUES ( value , …), (…), …;

Replacing Data

REPLACE INTO bird_families

( scientific_name , brief_description , order_id )

VALUES ( 'Viduidae' , 'Indigobirds & Whydahs' , 128 ),

( 'Estrildidae' , 'Waxbills, Weaver Finches, & Allies' , 128 ),

( 'Ploceidae' , 'Weavers, Malimbe, & Bishops' , 128 );

Query OK , 6 rows affected ( . 39 sec )

Records : 3 Duplicates : 3 Warnings :

Actually, when a row is replaced using the REPLACE statement, it ’s first deleted completely and the new row is then inserted.

CHAPTER 7

Selecting Data

Limiting Results

SELECT common_name, scientific_name, family_id

FROM birds

WHERE family_id IN(103, 160, 162, 164)

AND common_name != ''

ORDER BY common_name

LIMIT 3, 2;

+------------------------+-------------------------+-----------+

| common_name | scientific_name | family_id |

+------------------------+-------------------------+-----------+

| American Avocet | Recurvirostra americana | 162 |

| American Golden-Plover | Pluvialis dominica | 103 |

+------------------------+-------------------------+-----------+

This LIMIT clause has two values: the point where we want the results to begin, then the number of rows to display. The result is to show rows 3 and 4. Incidentally, LIMIT 3 used previously is the same as LIMIT 0, 3 : the 0 tells MySQL not to skip any rows.

SELECT orders.scientific_name AS 'Order',

families.scientific_name AS 'Family',

COUNT(*) AS 'Number of Birds'

FROM birds, bird_families AS families, bird_orders AS orders

WHERE birds.family_id = families.family_id

AND families.order_id = orders.order_id

AND orders.scientific_name = 'Pelecaniformes'

GROUP BY Family;

+----------------+-------------------+-----------------+

| Order | Family | Number of Birds |

+----------------+-------------------+-----------------+

| Pelecaniformes | Ardeidae | 157 |

| Pelecaniformes | Balaenicipitidae | 1 |

| Pelecaniformes | Pelecanidae | 10 |

| Pelecaniformes | Scopidae | 3 |

| Pelecaniformes | Threskiornithidae | 53 |

+----------------+-------------------+-----------------+

We gave the GROUP BY clause the Family alias, which is the scientific_name column

from the bird_families table. MySQL returns one results set for all five families, for one SELECT statement.

CHAPTER 8

Updating and Deleting Data

Updating Multiple Tables

UPDATE prize_winners , humans

SET winner_date = NULL ,

prize_chosen = NULL ,

prize_sent = NULL

WHERE country_id = 'uk'

AND prize_winners . human_id = humans . human_id ;

This SQL statement checks rows in one table, associates those rows to the related rows in another table, and changes those rows in that second table. Notice that we listed the two tables involved in a comma-separated list. We then used the SET clause to set the values of the columns related to winning a prize to NULL. In the WHERE clause, we give the condition that the country_id

Deleting Data

There is no UNDELETE or UNDO statement for restoring rows that you delete.

Deleting in Multiple Tables

There are many situations where data in one table is dependent on data in another table.If you use DELETE to delete a row in one table on which a row in another table is dependent,you ’ll have orphaned data. You could execute another DELETE to remove that other row, but it ’s usually better to delete rows in both tables in the same DELETE statement,especially when there may be many rows of data to delete.

The syntax for the DELETE that deletes rows in multiple tables is:

DELETE FROM table [, table ]

USING table [, . . . ]

[WHERE condition ];

DELETE FROM humans , prize_winners

USING humans JOIN prize_winners

WHERE name_first = 'Elena'

AND name_last = 'Bokova'

AND email_address LIKE '%yahoo.com'

AND humans . human_id = prize_winners . human_id ;

CHAPTER 9

Joining and Subquerying Data

Joining Tables

SELECT book_id , title , status_name

FROM books JOIN status_names

WHERE status = status_id ;

PART IV

Built-In Functions

PART V

Administration and Beyond

CHAPTER 13

User Accounts and Privileges

GRANT ALL ON rookery.*

TO 'lena_stankoska'@'localhost';

SHOW GRANTS FOR 'lena_stankoska'@'localhost';

+---------------------------------------------------------------------+

| Grants for lena_stankoska@localhost |

+---------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'lena_stankoska'@'localhost' |

| GRANT ALL PRIVILEGES ON `rookery`.* TO 'lena_stankoska'@'localhost' |

+---------------------------------------------------------------------+

When we executed

the CREATE USER statement and didn ’t specify a host, we created one user account—one with the wildcard for the host.

When we executed the GRANT statement to give privileges

to the same user, but with the host of localhost, a second user account was created.

SELECT User, Host

FROM mysql.user

WHERE User LIKE 'lena_stankoska';

+----------------+-----------+

| User | Host |

+----------------+-----------+

| lena_stankoska | % |

| lena_stankoska | localhost |

+----------------+-----------+

To eliminate both of the user accounts that we created for Lena, we will have to execute the DROP USER statement twice, like this:

DROP USER 'lena_stankoska' @ 'localhost' ;

DROP USER 'lena_stankoska' @ '%' ;