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.
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 ,
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 );
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:
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
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 ;
