Learning Mysql
Download 4.24 Mb. View original pdf
|
- Navigate this page:
- SHOW COLUMNS FROM artist;
- SELECT MAX(artist_id) FROM artist;
- INSERT INTO artist VALUES (7, "Barry Adamson");
| INSERT Basics Inserting data typically occurs in two situations when you bulk-load in a large batch as you create your database, and when you add data on an ad hoc basis as you use the database. In MySQL, there are different optimizations built into the server for each situation and, importantly, different SQL syntaxes available to make it easy for you to work with the server in both cases. We explain a basic INSERT syntax in this section, and show you examples of how to use it for bulk and single record insertion. Let’s start with the basic task of inserting one new row into the artist table. To do this, you need to understand the table’s structure. As we explained in Chapter 4 in “The Music Database you can discover this with the SHOW COLUMNS statement: mysql> SHOW COLUMNS FROM artist; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | artist_id | smallint(5) | NO | PRI | 0 | | | artist_name | char) | NO | | | | +-------------+-------------+------+-----+---------+-------+ 2 rows inset sec) This tells you that the two columns occur in the order artist_id and then artist_name , and you need to know this for the basic syntax we’re about to use. Our new row is fora new artist, Barry Adamson.” But what artist_id value do we give him You might recall that we already have six artists, so we should probably use. You can check this with: mysql> SELECT MAX(artist_id) FROM artist; +----------------+ | MAX(artist_id) | +----------------+ | 6 | +----------------+ 1 row inset sec) The MAX function is an aggregate function, and it tells you the maximum value for the column supplied as a parameter. This is a little cleaner than SELECT artist_id FROM artist , which prints out all rows and requires you to inspect the rows to find the maximum value adding an ORDER BY makes it easier. Using MAX is also much simpler than SELECT artist_id FROM artist ORDER BY artist_id DESC LIMIT 1 , which also returns the correct answer. You’ll learn more about the AUTO_INCREMENT shortcut to automatically assign the next available identifier in Chapter 6, and about aggregate functions in Chapter Were now ready to insert the row. Here’s what you type: mysql> INSERT INTO artist VALUES (7, "Barry Adamson"); Query OK, 1 row affected (0.00 sec) Download 4.24 Mb. Share with your friends:
|