<<

. 14
( 132 .)



>>

Chapter 3

The Structured Query
Language for Inserting,
Editing, and Selecting
Data
IN THIS CHAPTER

— Using the insert statement

— Using the update statement

— Using the replace statement

— Using the delete statement
— Using the basic select statement

— Joining tables



NOW how to make tables, you need to learn how to put data into
THAT YOU KNOW
them and get data out of them. You need to familiarize yourself with only a few
simple SQL statements in order to get data into tables, and you need only another
couple to edit data once it™s in your tables. Following that, you need to learn the
select statement, which retrieves your data in about as many ways as you can
imagine, either from a single table, or by joining two or more tables together.



The insert Statement
You will use the insert statement to place rows of data into your tables. The basic
form of the SQL insert statement is as follows:

Insert into tablename ( column1 [, column2 [, column3 [, ...] ] ] )
values ( value1 [, value2 [, value3 [, ...] ] ] )

53
54 Part I: Working with MySQL

If a column in your table allows null values, you can leave that column out of
the insert statement.
Text strings must be surrounded by single quote marks (˜), or double-quote
marks (˜™) if you™re not running in ANSI mode. For example:

insert into table_name (text_col, int_col) values (˜hello world™, 1)

This can cause a problem because undoubtedly someone is going to want to
insert a contraction into a table and that would confuse your database because it
would interpret the first single quote it sees (after the start of the string) as the end
of the string, and it then wouldn™t know what to do with the remainder of the
string. Therefore you™ll need a way of escaping, or working around, the single quote
character, by preceding it with a backslash (\). The same applies to the backslash
character itself:

insert into mytable ( mycolumn ) values (˜This is\™nt going to
fail.™);
insert into mytable ( mycolumn ) values (˜this \\ stores a
backslash™);

It™s worth noting that % and _ need to be escaped only in contexts where wild-
card matching is allowed. You can also escape single quotes by using two consecu-
tive single quote marks (˜™), and double quotes within a double-quoted string by
using two consecutive double quotes (“”).
The following characters are identified in MySQL by their typical escape
sequences:

— \n (newline)

— \t (tab)

— \r (carriage return)

— \b (back space)



For the most part, you won™t have to worry about escaping all of these char-
acters while doing your PHP programming. As you™ll see, functions and set-
tings built into PHP handle this automatically. The addslashes() function
and the magic quotes settings in the php.ini (covered in the MySQL docu-
mentation at http://www.mysql.com) are particularly helpful.
Chapter 3: Inserting, Editing, and Selecting Data 55

In MySQL you can also use the insert statement to add more than one row of
data at a time. All you need to do is include additional sets of values. For example:

insert into table_name (text_col, int_col)
values
(˜hello world™, 1),
(˜hello mars™, 2)
;

This approach has a few significant benefits, including that the database has less
parsing to do and that less data has to be sent to the database server over a net-
work. It™s a matter of reducing overhead.



The update Statement
The SQL update statement is slightly different from the others you have seen so far
in that it makes use of a where clause. A where clause enables you to pick out par-
ticular rows from your table ” the rows where these conditions are true. Most often,
the conditions have to do with matching the values of fields in the row to the par-
ticular values you™re looking for. The general syntax is as follows:

update table_name set col_1=value1, col_2=value_2 where col=value

Once again, if you™re inserting a string you™ll need to surround it with single
quotes and escape special characters properly. Keep in mind that the comparisons
in the where portion of the update statement can use any comparison operator (for
example, ˜col = value™, ˜col > value™, and so on).
Often the where clause will be used to identify a single row by its primary key.
In Table 3-1, id is the primary key. (The where clause is discussed in more detail
later in the chapter.)


TABLE 3-1 THE FOLKS TABLE

id Fname lname Salary

Don Ho 25,000
1

Don Corleone 800,000
2

Don Juan 32,000
3

Don Johnson 44,500
4
56 Part I: Working with MySQL

The following statement would affect only Don Corleone:

update folks set fname=™Vito™ where id=2;

As you can see, it would be risky to run an update statement based on the fname
column, as you could accidentally update every column in this table.

update folks set fname=™Vito™ where fname=™Don™;

You can also use update to give your underpaid employees a raise:

update folks set salary=50000 where salary<50,000;

As of MySQL 4.0, you can also update a table based on data in other tables. This
is an extremely helpful feature, since it enables you to make changes using only
SQL statements that previously would have required a program or script (or some
very dodgy workarounds).
To demonstrate, we add another table (Table 3-2) to the example set, recording
the income brought in by the people in folks:


TABLE 3-2 THE INCOME TABLE

id Income

500,000
1

1,500,000
2

250
3

1,250,000
4




We can use a multi-table update to give the top performers a raise:

update folks, income
set folks.salary = folks.salary * 1.1
where folks.id = income.id and income.income >= 1000000
;

As you might guess from the syntax, you can update multiple tables with a sin-
gle update statement. You might have good reasons to do that, but be careful ” the
results might not be what you expect. The reason is that the order in which
Chapter 3: Inserting, Editing, and Selecting Data 57

you update columns in the query makes a difference. To illustrate, we add a salary
column to the income table, not something you™d want to do if this were a real
database, by the way:

alter table income add salary numeric(10,2);

Then we update the records in income to fill in the salary with the values from
the folks table:

update income, folks set
income.salary = folks.salary
where income.id = folks.id
;

Now the income table looks like Table 3-3:


TABLE 3-3 THE INCOME TABLE

id Income Salary

500,000 50,000
1

1,500,000 880,000
2

250 50,000
3

1,250,000 55,000
4




Next, we redo the previous query, giving a raise to people who have brought in
an income of at least $1,000,000. This time, we update the salary field in both
tables at the same time:

update folks, income set
folks.salary = folks.salary * 1.1
, income.salary = income.salary * 1.1
where folks.id = income.id and income.income >= 1000000
;

If we run a select on the two tables now, the results (Table 3-4) look reasonable:

select f.id, f.fname, f.lname, i.income, f.salary as folks_salary,
i.salary as income_salary from folks f, income i where f.id = i.id;
58 Part I: Working with MySQL


TABLE 3-4 RESULTS OF THE UPDATE

id fname lname Income folks_salary income_salary

Don Ho 500,000 50,000 50,000
1

Vito Corleone 1,500,000 968,000 968,000
2

Don Juan 250 50,000 50,000
3

Don Johnson 1,250,000 60,500 60,500
4




However, if we change the query to use the value from the salary column in the
folks table to update both tables, as in the following code, the results are a bit odd
(Table 3-5).

update folks, income set
folks.salary = folks.salary * 1.1
, income.salary = folks.salary * 1.1
where folks.id = income.id and income.income >= 1000000
;


<<

. 14
( 132 .)



>>