3.6 Managing Data

only for RuBoard - do not distribute or recompile

3.6 Managing Data

The first thing you will probably want to do with a newly created table is add data to it. Once the data is in place, you need to maintain it ”add to it, modify it, and perhaps even delete it.

3.6.1 Inserts

Adding a row to a table is one of the more straightforward concepts in SQL. You have already seen several examples of it in this book. MySQL supports the standard SQL INSERT syntax:

 INSERT INTO   table_name   (   column1   ,   column2   ,   ...   ,   columnN   ) VALUES (   value1   ,   value2   ,   ...   ,   valueN   ) 

Under this syntax, you specify the columns followed by the values to populate those columns for the new row. When inserting data into numeric fields, you can insert the value as is; for all other fields, you must wrap them in single quotes. For example, to insert a row of data into a table of addresses, you might issue the following command:

 INSERT INTO addresses (name, address, city, state, phone, age) VALUES('Irving Forbush', '123 Mockingbird Lane', 'Corbin', 'KY',        '(800) 555-1234', 26) 

In addition, the escape character ” \ , by default ”enables you to escape single quotes and other literal instances of the escape character:

 # Insert info for the directory Stacie's Directory which # is in c:\Personal\Stacie INSERT INTO files (description, location)  VALUES ('Stacie\'s Directory', 'C:\Personal\Stacie') 

MySQL allows you to leave out the column names as long as you specify a value for every column in the table in the order they were specified in the table's CREATE call. If you want to use the default values for a column, however, you must specify the names of the columns for which you intend to insert nondefault data. For example, if the earlier files table had contained a column called size , the default value would be used for Stacie's Directory . MySQL allows you to specify a custom default value in the table's CREATE call. If you do not have a default value set up for a column, and that column is NOT NULL , you must include that column in the INSERT statement with a non- NULL value.

Newer versions of MySQL support a nonstandard INSERT call for inserting multiple rows at once:

 INSERT INTO foods VALUES (NULL, 'Oranges', 133, 0, 2, 39),                          (NULL, 'Bananas', 122, 0, 4, 29),                          (NULL, 'Liver', 232, 3, 15, 10) 

While these nonstandard syntaxes supported by MySQL are useful for quick system administration tasks , you should not use them when writing database applications unless you really need the speed benefit they offer. As a general rule, you should stick as close to the ANSI SQL2 standard as MySQL will let you. By doing so, you make certain that your application can run against any other database in the future. Being flexible is especially critical for people with mid-range database needs because such users generally hope to become people with high-end database needs.

Another nonstandard syntax supported by MySQL enables you to specify the column name and value together:

 INSERT INTO book SET title='The Vampire Lestat', author='Anne Rice'; 

Finally, you can insert data by using the data from some other table (or group of tables) to populate your new table. For example:

 INSERT INTO foods (name, fat) SELECT food_name, fat_grams FROM recipes 

You should note that the number of columns in the INSERT call matches the number of columns in the SELECT call. In addition, the data types for the INSERT columns must match the data types for the corresponding SELECT columns. Finally, the SELECT clause in an INSERT statement cannot contain an ORDER BY modifier and cannot be selected from the same table where the INSERT occurs.

3.6.2 Sequence Generation

The best kind of primary key is one that has absolutely no meaning in the database except to act as a primary key. Primary keys are the tools used to identify rows uniquely in a relational database. When you use information such as a username or an email address as a primary key, you are in effect saying that the username or email address is somehow an intrinsic part of who that person is. If that person ever changes his username or email address, you will have to go to great lengths to ensure the integrity of the data in the database. Consequently, it is a better design principle to use meaningless numbers as primary keys.

To achieve this, simply make a numeric primary key that increments every time you insert a new row. Looking at the cities table shown earlier, the first city you insert would have an id of 1, the second 2, the third 3, and so on. To successfully manage this primary key sequencing, you need some way to guarantee that a number can be read and incremented by only one client at a time. You accomplish this task by making the primary key field AUTO_INCREMENT .

When you create a table in MySQL, you can specify at most one column as AUTO_INCREMENT . When you do this, you can have this column automatically insert the highest current value plus 1 for that column when you insert a row and specify NULL or 0 for that row's value. The AUTO_INCREMENT columns must be indexed. The following command creates the cities table with an AUTO_INCREMENT id field: [4]

[4] You can seed AUTO_INCREMENT to start at any arbitrary number by specifying the seed value at the end of the CREATE statement. To start incrementing at 1025, for example, you would add AUTO_INCREMENT = 1025 after the closing parentheses of the CREATE TABLE statement.

 CREATE TABLE cities (id      INT  NOT NULL PRIMARY KEY AUTO_INCREMENT,                      name    VARCHAR(100),                      pop     INT,                      founded DATE) 

The first time you insert a row, the id field for your first row will be 1 as long as you use NULL or 0 for that field in the INSERT statement. For example, this command takes advantage of the AUTO_INCREMENT feature:

 INSERT INTO cities (id, name, pop) VALUES (NULL, 'Houston', 3000000) 

If no other values are in that table when you issue this command, MySQL will set this field to 1, not NULL (remember, it cannot be NULL ). If other values are present in the table, the value inserted will be one greater than the largest current value for id .

You can also implement sequences by referring to the value returned by the LAST_INSERT_ID( ) function and doing your own incrementing:

 UPDATE table_name SET id=LAST_INSERT_ID( id+1 ); 

The AUTO_INCREMENT attribute may be supplied for at most one column of an integer type in a table. In addition to being an integer type, the column must be either a primary key or the sole column in a unique index. When you attempt an insert into a table with such an integer field and fail to specify a value for that field (or specify a NULL value), a value of one greater than the column's current maximum value will be automatically inserted.

Chapter 17 contains reference material on the LAST_INSERT_ID( ) function.

3.6.3 Updates

The insertion of new rows into a database is just the start of data management. Unless your database is read-only, you will probably also need to make periodic changes to the data. The standard SQL modification statement looks like this:

 UPDATE   table_name   SET   column1   =   value1   ,   column2   =   value2   ,   ...   ,   columnN   =   valueN   [WHERE   clause   ] 

You specifically name the table you want to update and the values you want to assign in the SET clause, and then identify the rows to be affected in the WHERE clause. If you fail to specify a WHERE clause, MySQL will update every row in the table.

In addition to assigning literal values to a column, you can also calculate the values. You can even calculate the value based on a value in another column:

 UPDATE years SET end_year = begin_year+5 

This command sets the value in the end_year column equal to the value in the begin_year column plus 5 for each row in that table.

3.6.4 The WHERE Clause

The previous section introduced one of the most important SQL concepts, the WHERE clause. In SQL, a WHERE clause enables you to pick out specific rows in a table by specifying a value that must be matched by the column in question. For example:

 UPDATE bands SET lead_singer = 'Ian Anderson' WHERE band_name = 'Jethro Tull' 

This UPDATE specifies that you should change only the lead_singer column for the row where band_name is identical to Jethro Tull . If the band_name column is not a unique index, that WHERE clause may match multiple rows. Many SQL commands employ WHERE clauses to help pick out the rows on which you wish to operate . Because the columns in the WHERE clause are columns on which you search, you should generally have indexes created around whatever combinations you commonly use. We discuss the kinds of comparisons you can perform in the WHERE clause later in the chapter.

3.6.5 Deletes

Deleting data is a straightforward operation. You simply specify the table followed by a WHERE clause that identifies the rows you want to delete:

 DELETE FROM   table_name   [WHERE   clause   ] 

As with other commands that accept a WHERE clause, the WHERE clause is optional. If you omit it, you will delete all of the records in the table! Of all the destructive commands in SQL, this is the easiest one to issue by mistake.

MySQL 4.0 has introduced a new, dangerous form of DELETE that supports the ability to delete from multiple tables with a single command:

 DELETE  table1, table2, ..., tablen  FROM  table1, table2, ... tablen  [WHERE  clause  ] 

The FROM clause in this syntax does not mean the same thing as it does in the simpler form. In other words, it does not list the tables from which rows are deleted ”it lists the tables referenced in the WHERE clause. If you are familiar with the SELECT statement, it works exactly the same as the FROM clause in SELECT statements. The tables you are deleting from are listed directly after the DELETE statement:

 DELETE Author, Address FROM Author, Book, Address WHERE Author.author_id = Addess.address_id AND Author.author_id = Book.author_id AND Book.publish_date < 1980; 

This statement deletes all the authors and any address information you have for those authors in the Address table for every author with books published before 1980. The old books will remain in the Book table, because Book was not named after the DELETE keyword. We further cover the complexities of the WHERE clause later in the chapter.

only for RuBoard - do not distribute or recompile


Managing and Using MySQL
Managing and Using MySQL (2nd Edition)
ISBN: 0596002114
EAN: 2147483647
Year: 2002
Pages: 137

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net