SQL injection attack hits hundreds of thousands of web page

An automated SQL injection attack has affected a large number of web sites - as in hundreds of thousands of pages - resulting in malware being downloaded to visitors' computers.

The attack appears to take advantage of features of Microsoft SQL Server, though according to security vendor F-Secure the problem lies in poor site coding, not in vulnerabilities in Microsoft's software. SQL injection is a well-known technique for attacking sites, and one that professional web developers should know they need to guard against.

Affected sites include some operated by the United Nations and the US, UK and Australian governments or their agencies.

According to F-Secure, the domains hosting the malicious content ultimately delivered by the hack are nmidahena.com, aspder.com and nihaorr1.com.

Some reports have suggested the problem is linked to an unpatched flaw in IIS that surfaced last week, but Microsoft has categorically denied that is the case. "We have also determined that these attacks are in no way related to Microsoft Security Advisory (951306)," wrote communications manager Bill Sisk in the Microsoft Security Response Center blog.

"The attacks are facilitated by SQL injection exploits and are not issues related to IIS 6.0, ASP, ASP.Net or Microsoft SQL technologies," he added.

Removing information

Keep the information in your database up to date by deleting obsolete information.
You can remove a row from a table with the DELETE query:

DELETE FROM tablename WHERE clause

Be extremely careful when using DELETE. If you use a DELETE query without a WHERE clause, it will delete all the data in the table. I mean all the data. I repeat, all the data. The data cannot be recovered. This function of the DELETE query is right at the top of my don’t-try-this-at-home list.
You can delete a column from a table by using the ALTER query:

ALTER TABLE tablename DROP columnname

Or you could remove the whole thing and start over again with

DROP TABLE tablename
or
DROP DATABASE databasename

Updating information

Changing information in an existing row is updating the information. For instance, you might need to change the address of a member because she has moved, or you might need to add a fax number that a member left blank when he originally entered his information.
The UPDATE query is straightforward:

UPDATE tablename SET column=value,column=value,...WHERE clause

In the SET clause, you list the columns to be updated and the new values to be inserted. List all the columns that you want to change in one query. Without a WHERE clause, the values of the column(s) would be changed in all rows. But with the WHERE clause, you can specify which rows to update.
For instance, to update an address in the Member table, use this query:

UPDATE Member SET street=”3333 Giant St”,
phone=”555-555-5555”
WHERE loginName=”bigguy”

So You Think You Know SQL?

I was reading an old post by a buddy of mine about bad SQL being the cause of a lot of application problems and decided to weigh in my two cents.

A lot of developers get forced into writing SQL as part of their jobs. Should they be doing it? I don't think so. It's not necessarily the best of ideas, and in MOST cases should probably be avoided at all costs.

Besides, developers cannot be experts in every language or technology right? Something has got to give somewhere. It's usually their SQL skills that suffer. Developers are, sometimes, forced into situations where they have no choice but to write their own SQL. There is either no DBA, a DBA who isn't interested in helping developers with their queries, or a DBA who isn't even in the development loop (never a good sign). In these cases, developers may have to write their own SQL.

Sometimes developers have to know their limitations when it comes to writing queries, especially complex queries. I don't think a lot of developers do truly know their skill limitations. Yet, these intrepid souls will trudge on thinking they can write SQL just fine. When, in reality, they really and truly do not know the little tricks and tweaks that can make the SQL perform better.

I've seen it a thousand times. It's not an indictment of the developer, just a limitation of their skills that needs to be recognized.

Don't lose hope though, there are ways to combat this and make your SQL as good as it should be, or as good as it can be. What are these answers, then? Personally, I opt for something like Hibernate (Java) or Transfer (ColdFusion) to abstract my SQL for me. What does this mean? It means that my queries will be optimized and I can spend my time focused on developing the business logic rather than spending that time mired in persistence.

It also gives me the agility of being (most of the time) database agnostic. This lets us easily develop in one database locally, and port the same code to the server on a different database with minimal configuration changes. If ORMs are not an option for you and you have access to a good DBA, engage them. Talk to them and mine them for all the information they're worth. It's their job to know the database you're using and how to optimize queries. Make them earn their money by at least helping you write your queries, if not transferring the database functionality to them altogether (good luck).

None of these two options are viable? Well maybe you'll have to engage an outside consultancy to help you with your persistence layer optimizations, or pursue another path for optimizing your queries that has not been mentioned here. The bottom line is this: If you optimize your persistence today, then you'll have to spend less time dealing with it later when it causes you problems tomorrow. You may be able to write SQL, you may be able to do complex joins and create some wicked stored procedures, but are you a SQL expert? If so, then never mind this post. If not, then don't be afraid to swallow your pride and ask for help.

How to Use Join?

Combining tables side by side is a join. Tables are combined by matching data in a column — the column that they have in common. The combined results table produced by a join contains all the columns from both tables. For instance, if one table has two columns (memberID and height), and the second table has two columns (memberID and weight), a join results in a table with four columns: memberID (from the first table), height, memberID (from the second table), and weight.

The two common types of joins are an inner join and an outer join. The difference between an inner and outer join is in the number of rows included in the results table. The results table produced by an inner join contains only rows that existed in both tables. The combined table produced by an outer join contains all rows that existed in one table with blanks in the columns for the rows that did not exist in the second table. For instance, if table1 contains a row for Joe and a row for Sally, and table2 contains only a row for Sally, an inner join would contain only one row: the row for Sally. However, an outer join would contain two rows — a row for Joe and a row for Sally — even though the row for Joe would have a blank field for weight. The results table for the outer join contains all the rows for one table. If any of the rows for that table don’t exist in the second table, the columns for the second table are empty. Clearly, the contents of the results table are determined by which table contributes all its rows, requiring the second table to match it. Two kinds of outer joins control which table sets the rows and which match: a LEFT JOIN and a RIGHT JOIN. You use different SELECT queries for an inner join and the two types of outer joins. The following query is an inner join:

SELECT columnnamelist FROM table1,table2
WHERE table1.col2 = table2.col2
And these queries are outer joins:
SELECT columnnamelist FROM table1 LEFT JOIN table2
ON table1.col1=table2.col2
SELECT columnnamelist FROM table1 RIGHT JOIN table2
ON table1.col1=table2.col2

In all three queries, table1 and table2 are the tables to be joined. You can join more than two tables. In both queries, col1 and col2 are the names of the columns being matched to join the tables. The tables are matched based on the data in these columns. These two columns can have the same name or different names. The two columns must contain the same type of data.

How to Use UNION

UNION is used to combine the results from two or more select queries. The results from each query are added to the result set following the results of the previous query. The format of the UNION query is as follows:

SELECT query UNION ALL SELECT query ...

You can combine as many SELECT queries as you need. A SELECT query can include any valid SELECT format, including WHERE clauses, LIMIT clauses, and so on. The rules for the queries are

• All the select queries must select the same number of columns.
• The columns selected in the queries must contain the same type of data.

The result set will contain all the rows from the first query followed by all the rows from the second query and so on. The column names used in the result set are the column names from the first SELECT query. The series of SELECT queries can select different columns from the same table, but situations in which you want a new table with one column in a table followed by another column from the same table are unusual. It’s much more likely that you want to combine columns from different tables. For example, you might have a table of members who have resigned from the club and a separate table of current members. You can get a list of all members, both current and resigned, with the following query:

SELECT lastName,firstName FROM Member UNION ALL SELECT lastName,firstName FROM OldMember

The result of this query is the last and first names of all current members, followed by the last and first names of all the members who have resigned. Depending on how you organized your data, you might have duplicate names. For instance, perhaps a member resigned, and his name is in the OldMember table — but he joined again, so his name is added to the Member table. If you don’t want duplicates, don’t include the word ALL. If ALL is not included, duplicate lines are not added to the result.
You can use ORDER BY with each SELECT query, as I discuss in the previous section, or you can use ORDER BY with a UNION query to sort all the rows in the result set. If you want ORDER BY to apply to the entire result set, rather than just to the query that it follows, use parentheses as follows:

(SELECT lastName FROM Member UNION ALL
SELECT lastName FROM OldMember) ORDER BY lastName

The UNION statement was introduced in MySQL 4.0. It is not available in MySQL 3.

Microsoft to Offer SQL Server 2005 SP3

Microsoft will release a third service pack for SQL Server 2005, just before the next version of the server software comes out.

Service Pack 3 is expected to come out after the release to manufacturing of SQL Server 2008, which is scheduled to happen in the third quarter this year.

Microsoft didn't reveal much about what the service pack would include, except to say in a Tuesday blog post that it will contain all cumulative updates to the software plus some additional fixes to bugs that customers have reported on MS Connect, a Microsoft Web site for customer feedback.

The development of a third service pack does not change the Incremental Servicing Model that Microsoft introduced last year. The model, unveiled last July, introduced a regular update process to SQL Server 2005. Since then, every two months Microsoft issues a cumulative update for SQL Server 2005 that includes all critical fixes that had been released during that time as well as updates for less urgent issues.

Customers like the predictability of the model, so Microsoft will continue using it, said Francois Ajenstat, director of SQL Server marketing for Microsoft, in a separate blog post. However, users also say that there's a need for a third service pack, so Microsoft plans to release one, he said. The company is announcing its plans now so that customers can plan for deployment, he said.

CodeGear delivers Delphi for PHP 2.0.

CodeGear, the developer tools arm of Borland Software, has upgraded its tool set for PHP developers.

The company announced Delphi for PHP 2.0 on April 14. Delphi for PHP is an IDE (integrated development environment) for rapidly building interactive Web applications using visual drag-and-drop design capabilities. The product also features a PHP component framework.

To read about the debugger CodeGear contributed to Eclipse, click here.

CodeGear officials said Delphi for PHP 2.0 features several enhancements, including HTML templates with embedded dynamic PHP; database support for MySQL, CodeGear InterBase, Microsoft SQL Server, Oracle, PostgreSQL, IBM Informix and DB2, and Sybase's SQL Anywhere, plus the ability to build rich data-driven Web applications without requiring database connectivity coding; an expanded and faster VCL (Visual Component Library) for PHP with support for popular PHP packages and libraries including the Zend Framework from Zend Technologies; productivity and performance enhancements including new error insight and source code formatting; and new PHP debugging features and integrated PHP performance profiling and tuning.

According to Michael Swindell, CodeGear's vice president of products, Version 2.0 of Delphi for PHP is a major advancement over the initial version, which introduced PHP developers to the productivity of a specialized IDE.

Yet, "perhaps the most significant advance is the new HTML template system, which enables PHP developers to seamlessly build rich PHP applications against HTML templates for design flexibility and easy customization with popular HTML tools," Swindell said.

Delphi for PHP 2.0 is available at an introductory price of $249 through the end of June 2008. Upgrades from Delphi for PHP 1.0 are currently $179.

Combining information from tables

In previous sections of this chapter, I assume that all the information you want is in a single table. However, you might want to combine information from different tables. You can do this easily in a single query. Two words can be used in a SELECT query to combine information from two or more tables:

• UNION: Rows are retrieved from one or more tables and stored together, one after the other, in a single result. For example, if your query selected 6 rows from one table and 5 rows from another table, the result would contain 11 rows.
• JOIN: The tables are combined side by side, and the information is retrieved from both tables.

Retrieving data from a specific source

Frequently, you don’t want all the information from a table. You want information from selected database objects, that is, rows. Three SQL words are frequently used to specify the source of the information:

• WHERE: Allows you to request information from database objects with certain characteristics. For instance, you can request the names of members who live in California, or you can list only pets that are cats.
• LIMIT: Allows you to limit the number of rows from which information is retrieved. For instance, you can request all the information from the first three rows in the table.
• DISTINCT: Allows you to request information from only one row of identical rows. For instance, in the Login table, you can request loginName but specify no duplicate names, thus limiting the response to one record for each member. This would answer the question, “Has the member ever logged in?” rather than the question “How many times has the member logged in?”

The WHERE clause of the SELECT query enables you to make complicated selections. For instance, suppose your boss asks for a list of all the members whose last names begin with B, who live in Santa Barbara, and who have an 8 in either their phone or fax number. I’m sure there are many uses for such a list. You can get this list for your boss with a SELECT query by using a WHERE clause.
The basic format of the WHERE clause is

WHERE expression AND|OR expression AND|OR expression ...

expression specifies a value to compare with the values stored in the database. Only the rows containing a match for the expression are selected. You can use as many expressions as needed, each one separated by AND or OR. When you use AND, both of the expressions connected by the AND (that is, both the expression before the AND and the expression after the AND) must be true in order for the row to be selected. When you use OR, only one of the expressions connected by the OR must be true for the row to be selected. You can combine any of the expressions with ANDs and ORs. In some cases, you need to use parentheses to clarify the selection criteria. For instance, you can use the following query to answer your boss’s urgent need to find all people in the Member Directory whose names begin with B, who live in Santa Barbara, and who have an 8 in either their phone or fax number:

SELECT lastName,firstName FROM Member
WHERE lastName LIKE “B%”
AND city = “Santa Barbara”

AND (phone LIKE “%8%” OR fax LIKE “%8%”) Notice the parentheses in the last line. You would not get the results that your boss asked for without the parentheses. Without the parentheses, each connector would be processed in order from the first to the last, resulting in a list that includes all members whose names begin with B and who live in Santa Barbara and whose phone numbers have an 8 in them and all members whose fax numbers have an 8 in them, whether they live in Santa Barbara or not and whether their name begins with a B or not. When the last OR is processed, members are selected whose characteristics match the expression before the OR or the expression after the OR. The expression before the OR is connected to previous expressions by the previous ANDs and so does not stand alone, but the expression after the OR does stand alone, resulting in the selection of all members with an 8 in their fax number.

LIMIT specifies how many rows can be returned. The form for LIMIT is
LIMIT startnumber,numberofrows

The first row that you want to retrieve is startnumber, and the number of rows to retrieve is numberofrows. If startnumber is not specified, 1 is assumed. To select only the first three members who live in Texas, use this query:

SELECT * FROM Member WHERE state=”TX” LIMIT 3

Some SELECT queries will find identical records, but in this example you want to see only one — not all — of the identical records. To prevent the query from returning all identical records, add the word DISTINCT immediately after SELECT.

Retrieving data in a specific order

You might want to retrieve data in a particular order. For instance, in the Member table, you might want members organized in alphabetical order by last name. Or, in the Pet table, you might want the pets grouped by type of pet. In a SELECT query, ORDER BY and GROUP BY affect the order in which the data is delivered to you:

• ORDER BY: To sort information, use the phrase ORDER BY columnname The data is sorted by columnname in ascending order. For instance, if columnname is lastName, the data is delivered to you in alphabetical order by the last name. You can sort in descending order by adding the word DESC before the column name. For example: SELECT * FROM Member ORDER BY DESC lastName
  
• GROUP BY: To group information, use the following phrase: GROUP BY columnname The rows that have the same value of columnname are grouped together. For example, use this query to group the rows that have the same value as petType:
  
• SELECT * FROM Pet GROUP BY petType
  
• You can use GROUP BY and ORDER BY in the same query.

Retrieving specific information

To retrieve specific information, list the columns containing the information you want. For example:

SELECT columnname,columnname,columnname,... FROM tablename

This query retrieves the values from all the rows for the indicated column(s). For instance, the following query retrieves all the last names and first names stored in the Member table:

SELECT lastName,firstName FROM Member

You can perform mathematical operations on columns when you select them.
For example, you can use the following SELECT query to add two columns:

SELECT col1+col2 FROM tablename

Or you could use the following query:

SELECT price,price*1.08 FROM Pet

The result is the price and the price with the sales tax of 8 percent added.
You can change the name of a column when selecting it, as follows:

SELECT price,price*1.08 AS priceWithTax FROM Pet

The AS clause tells MySQL to give the name priceWithTax to the second column retrieved. Thus, the query retrieves two columns of data: price and priceWithTax.
In some cases, you don’t want to see the values in a column, but you want to know something about the column. For instance, you might want to know the lowest value in the column or the highest value in the column. For example, the query to find out the highest price in the Pet table is

SELECT MAX(price) FROM Pet

SQL words that look like MAX() and SUM(), with parentheses following the name, are functions. Some functions provide information about a column. Other functions change each value selected. For example, SQRT() returns the square root of each value in the column, and DAYNAME() returns the name of the day of the week for each value in a date column, rather than the actual date stored in the column. More than 100 functions are available for use in a SELECT query. For descriptions of all the functions, see the MySQL online manual at dev.mysql.com/doc/refman/5.0/en/functions.html.

Retrieving information from database

The only purpose in storing information is to have it available when you need it. A database lives to answer questions. What pets are for sale? Who are the members? How many members live in Arkansas? Do you have an alligator for sale? How much does a dragon cost? What is Goliath Smith’s phone number? And on and on. You use the SELECT query to ask the database questions.
The simplest, basic SELECT query is

SELECT * FROM tablename

This query retrieves all the information from the table. The asterisk (*) is a wildcard meaning all the columns.
The SELECT query can be much more selective. SQL words and phrases in the SELECT query can pinpoint the information needed to answer your question. You can specify what information you want, how you want it organized, and the source of the information:

• You can request only the information (the columns) that you need to answer your question. For instance, you can request only the first and last names to create a list of members.
• You can request information in a particular order. For instance, you can request that the information be sorted in alphabetical order.
• You can request information from selected objects (the rows) in your table. For instance, you can request the first and last names for only those members whose addresses are in Florida.

In MySQL 4.1, MySQL added the ability to nest a SELECT query inside another query. The nested query is called a subquery. You can use a subquery in SELECT, INSERT, UPDATE, or DELETE queries or in SET clauses. A subquery can return a single value, a single row or column, or a table, which is used in the outer query. All the features of SELECT queries can be used in subqueries. See the MySQL online manual at dev.mysql.com/doc/refman/5.0/en/ subqueries.html for detailed information on using subqueries.

How to adding a bunch of data into database?

If you have a large amount of data to enter and it’s already in a computer file, you can transfer the data from the existing computer file to your MySQL database. The SQL query that reads data from a text file is LOAD. The LOAD query requires you to specify a database.

Because data in a database is organized in rows and columns, the text file being read must indicate where the data for each column begins and ends and where the end of a row is. To indicate columns, a specific character separates the data for each column. By default, MySQL looks for a tab character to separate the fields. However, if a tab doesn’t work for your data file, you can choose a different character to separate the fields and tell MySQL in the query that a different character than the tab separates the fields. Also by default, the end of a line is expected to be the end of a row — although you can choose a character to indicate the end of a line if you need to. A data file for the Pet table might look like this:

UnicornhorseSpiral horn5000.00/pix/unicorn.jpg
PegasushorseWinged8000.00/pix/pegasus.jpg
LioncatLarge; Mane on neck2000.00/pix/lion.jpg

A data file with tabs between the fields is a tab-delimited file. Another common format is a comma-delimited file, where commas separate the fields. If your data is in another file format, you need to convert it into a delimited file. To convert data in another file format into a delimited file, check the manual for that software or talk to your local expert who understands the data’s current format. Many programs, such as Excel, Access, and Oracle, allow you to output the data into a delimited file. For a text file, you might be able to convert it to delimited format by using the search-and-replace function of an editor or word processor. For a truly troublesome file, you might need to seek the help of an expert or a programmer.
The basic form of the LOAD query is

LOAD DATA INFILE “path/datafilename” INTO TABLE tablename

The query loads data from a text file located on your server. If the filename does not include a path, MySQL looks for the data file in the directory where your table definition file, called tablename.frm, is located. By default, this file is located in a directory named for your database, such as a directory named PetDirectory. This directory is located in your data directory, which is located in the main directory where MySQL is installed. For example, if the file was named data.dat, the LOAD command might look for the file at C:\Program Files\MySQL\MySQL Server 5.0\data\PetDirectory\ data.dat.
This basic form can be followed by optional phrases if you want to change a
default delimiter. The options are

FIELDS TERMINATED BY ‘character’
FIELDS ENCLOSED BY ‘character’
LINES TERMINATED BY ‘character’

Suppose that you have the data file for the Pet table, shown previously in this section, except that the fields are separated by a comma rather than a tab. The name of the data file is pets.dat, and it’s located in the same directory as the database. The SQL query to read the data into the table is

LOAD DATA INFILE “pets.dat” INTO TABLE Pet FIELDS TERMINATED BY ‘,’

To use the LOAD DATA INFILE query, the MySQL account must have the FILE privilege on the server host.
You can also load data from a text file on your local computer by using the word LOCAL, as follows:

LOAD DATA LOCAL INFILE “path/datafilename” INTO TABLE tablename

You must include a path to the file. Use forward slashes for the path, even on a Windows computer, such as “C:/data/datafile1.txt”. If you get an error message when sending this query, LOCAL may not be enabled.

To look at the data that you loaded — to make sure that it’s correct — use an SQL query that retrieves data from the database. I describe these types of SQL queries in detail in the next section. In brief, use the following query to look at all the data in the table so that you can check it:

SELECT * FROM Pet

How to add one row at a time to a database?

You use the INSERT query to add a row to a database. This query tells MySQL which table to add the row to and what the values are for the fields in the row.
The general form of the query is

INSERT INTO tablename (columnname, columnname,...,columnname)
VALUES (value, value,...,value)

The following rules apply to the INSERT query:

• Values must be listed in the same order in which the column names are listed. The first value in the value list is inserted into the column that’s named first in the column list; the second value in the value list is inserted into the column that’s named second; and so on.
• A partial column list is allowed. You don’t need to list all the columns. Columns that are not listed are given their default value or left blank if no default value is defined.
• A column list is not required. If you’re entering values for all the columns, you don’t need to list the columns at all. If no columns are listed, MySQL will look for values for all the columns, in the order in which they appear in the table.
• The column list and value list must be the same length. If the list of columns is longer or shorter than the list of values, you get an error message like this: Column count doesn’t match value count.

The following INSERT query adds a row to the Member table:

INSERT INTO Member (loginName,createDate,password,lastName, street,city,state,zip,email,phone,fax)
VALUES (“bigguy”,”2001-Dec-2”,”secret”,”Smith”, “1234 Happy St”,”Las Vegas”,”NV”,”88888”, “gsmith@GSmithCompany.com”,”(555) 555-5555”,””)

Notice that firstName is not listed in the column name list. No value is entered into the firstName field. If firstName were defined as NOT NULL, MySQL would not allow this. Also, if the definition for firstName included a default, the default value would be entered, but because it doesn’t, the field is left empty. Notice that the value stored for fax is an empty string. To look at the data that you entered and ensure that you entered it correctly, use an SQL query that retrieves data from the database. I describe these SQL queries in detail in “Retrieving information,” later in this chapter. In brief, the following query retrieves all the data in the Member table:

SELECT * FROM Member

How to add information?

Every database needs data. For example, you might want to add data to your database so that your users can look at it. Or you might want to create an empty database for users to put data into, making the data available for your eyes only — an example of this is the Member Directory. In either scenario, data will be added to the database.
If your data is still on paper, you can enter it directly into a MySQL database, one row at a time, by using an SQL query. However, if you have a lot of data, this process could be tedious and involve a lot of typing. Suppose that you have information on 1000 products that must be added to your database. Assuming that you’re greased lightening on a keyboard and can enter a row per minute, that’s 16 hours of rapid typing — well, rapid editing, anyway. Doable, but not fun. On the other hand, suppose that you need to enter 5000 members of an organization into a database and that it takes five minutes to enter each member. Now you’re looking at more than 400 hours of typing — who has time for that?
If you have a large amount of data to enter, consider some alternatives. Sometimes scanning in the data is an option. Or perhaps you need to beg, borrow, or hire some help. In many cases, it could be faster to enter the data into a big text file than to enter each row in a separate SQL query. The SQL query LOAD can read data from a big text file (or even a small text file). So, if your data is already in a computer file, you can work with that file; you don’t need to type all the data again. Even if the data is in a format other than a text file (for example, in an Excel, Access, or Oracle file), you can usually convert the file to a big text file, which can then be read into your MySQL database. If the data isn’t yet in a computer file and there’s a lot of data, it might be faster to enter that data into the computer in a big text file and transfer it into MySQL as a second step.
Most text files can be read into MySQL, but some formats are easier than others. If you’re planning to enter the data into a big text file, read the section, “Adding a bunch of data,” to find the best format. Of course, if the data is already on the computer, you have to work with the file as it is.

How to Move Data Into and Out of the Database?

An empty database is like an empty cookie jar — it’s not much fun. And searching an empty database is no more interesting or fruitful than searching an empty cookie jar. A database is only useful with respect to the information that it holds.
A database needs to be able to receive information for storage and to deliver information on request. For instance, the MemberDirectory database needs to be able to receive the member information, and it also needs to be able to deliver its stored information when you request it. If you want to know the address of a particular member, for example, the database needs to deliver that information when you request it.
Your MySQL database responds to four types of requests:

• Adding information: Adding a row to a table.
• Updating information: Changing information in an existing row. This includes adding data to a blank field in an existing row.
  
• Retrieving information: Looking at the data. This request does not remove data from the database.
• Removing information: Deleting data from the database.

Sometimes your question requires information from more than one table. For instance, the question, “How much does a green dragon cost?” requires information from the Pet table and from the Color table. You can ask this question easily in a single SELECT query by combining the tables. In the following sections, I discuss how to receive and deliver information as well as how to combine tables.

Changing the database structure

Your database isn’t written in stone. By using the ALTER query, you can change the name of the table; add, drop, or rename a column; or change the data type or other attributes of the column.
The basic format for this query is ALTER TABLE tablename, followed by the specified changes.

• ADD columnname definition Adds a column; definition includes the data type and optional definitions.
• ALTER columnname SET DEFAULT value Changes the default value for a column
• ALTER columnname DROP DEFAULT Removes the default value for a column.
• CHANGE columnname newcolumnname definition Changes the definition of a column and renames the colum, definition includes the data type and optional definitions
  
• DROP columnname Deletes a column, including all the data in the column. The data cannot be recovered.
• MODIFY columnname definition Changes the definition of a column; definition includes the data type and optional definitions.
• RENAME newtablename Renames a table.

Changing a database is not a rare occurrence. You might want to change your database for many reasons. For example, suppose that you defined the column lastName with VARCHAR(20) in the Member table of the MemberDirectory database. At the time, 20 characters seemed sufficient for a last name. But now you just received a memo announcing the new CEO, John Schwartzheimer-Losertman. Oops. MySQL will truncate his name to the first 20 letters, a less-than-desirable new name for the boss. So you need to make the column wider — pronto. Send this query to change the column in a second:
ALTER TABLE Member MODIFY lastName VARCHAR(50)