PHP, MySQL, JavaScript & HTML5 All-in-One For Dummies (2013)

Book V: MySQL

Chapter 4: Using the Database

In This Chapter

Storing data in the database

Viewing and retrieving data from the database

Updating data

Deleting data

An empty database is like an empty cookie jar — you get nothing out of it. And searching an empty database is no more interesting or fruitful than searching an empty cookie jar. A database is useful only 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 CustomerOrderInformation database described in earlier chapters needs to be able to receive the customer and order information, and it needs to be able to deliver its stored information when you request it. If you want to know the address of a particular customer or the date a particular order was made, 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.

Retrieving information: Looking at the data. This request does not remove data from the database.

Updating information: Changing information in an existing row. This includes adding data to a blank field in an existing row.

Removing information: Deleting data from the database.

You interact with the database through SQL statements and queries, as discussed in Chapter 1 of this minibook. This chapter explains how to use SQL statements and queries to add, view, retrieve, update, and delete information in your database.

Adding Information to a Database

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. In either scenario, data is 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, in an SQL statement. 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 1,000 products that must be added to your database. Assuming that you’re greased lightning 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 5,000 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 might be faster to enter the data into a big text file than to enter each row in a separate SQL statement.

The SQL statement 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 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 text file and transfer it into MySQL as a second step.

Most text files can be read into MySQL, but some formats are easier to read than others. If you’re planning to enter the data into a 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.

Adding one row at a time

If you have a small amount of data, you can add one row at a time to the table. PHP scripts often need to add one row at a time. For instance, when a PHP script accepts the data from a customer in a form, it usually needs to enter the information for the customer into the database in a new row.

You use the INSERT statement to add a row to a database. This statement 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 statement is

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

VALUES (value, value,...,value)

The following rules apply to the INSERT statement:

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 column list, full or partial, is allowed. You don’t need to list all the columns. Columns that aren’t listed are given their default value or left blank if no default value is defined.

Remember, any columns that are defined as NOT NULL must be included, with values, or the statement will fail.

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 looks 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. You must provide a value for every column that you list or you'll get an error message like this: Column count doesn't match value count.

The following INSERT statement adds a row to the Customer table:

INSERT INTO Customer (lastName, street,city,state,zip,

email,phone,fax)

VALUES ("Contrary","1234 Garden St","Garden","NV","88888",

"maryc@hergarden.com","(555) 555-5555","")

Notice that firstName isn't 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. We describe these SQL queries in detail in the Retrieving Information from a Database section, later in this chapter. In brief, the following query retrieves all the data in the Customer table:

SELECT * FROM Customer

Adding a bunch of data

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.

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. Here’s how you create that table structure:

Columns: 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 that a different character than the tab separates the fields.

Rows: 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 an Inventory table might look like this:

Rock<TAB>Classic<TAB>Steely Dan<Tab>Aja<Tab>10.99

RockTAB>Pop<TAB>Semisonic<Tab>All About Chemistry<Tab>11.99

Rock<TAB>Classic<TAB>Beatles<TAB>Abbey Road<Tab>9.99

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 software’s 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 more experienced programmer.

You can leave a field blank in the data file by including the field separators with no data between them. If the field is not defined as NOT NULL, the field is blank. If the field is defined as NOT NULL, loading the data file fails and an error message is returned. If one of the fields is anAUTO_INCREMENT field, such as a SERIAL field, you can leave it blank and MySQL will insert the AUTO_INCREMENT value. For instance, the following data file contains data to be loaded into the Customer table.

,Smith,John,,Austin,TX,88888,,,

,Contrary,Mary,,Garden,ID,99999,,,

,Sprat,Jack,,Pumpkin,NY,11111,,,

This data file is comma delimited. Each row starts with a comma, leaving the first field blank for the customerID field, which is SERIAL. Other fields in the row are also blank and will be blank in the database after the data file is loaded.

The SQL statement that reads data from a text file is LOAD. The basic form of the LOAD statement is

LOAD DATA INFILE "path/datafilename" INTO TABLE tablename

The statement loads data from a text file located on your server. If the filename doesn't 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 CustomerOrderInformation. 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 statement might look for the file at C:\Program Files\MySQL\MySQL Server 5.0\data\CustomerOrderInformation\data.dat.

The basic form of the LOAD statement 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 Customer table, except that the fields are separated by a comma rather than a tab. The name of the data file is customer.dat, and it's located in the same directory as the database. The SQL statement to read the data into the table is

LOAD DATA INFILE "customer.dat" INTO TABLE Customer

FIELDS TERMINATED BY ','

To use the LOAD DATA INFILE statement, the MySQL account must have the FILE privilege on the server host. We discuss MySQL account privileges in Chapter 2 of this minibook.

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 statement, LOCAL might not be enabled. See http://dev.mysql.com/doc/refman/5.1/en/load-data.html for more information on the LOCAL keyword.

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. We 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 Customer

Looking at the Data in a Database

After data has been entered into a database, you might want to browse through the data to see whether the entered data looks correct or to get an idea of what type of data is in the database. You can also browse the data to determine simple information about the database, such as how many records it contains.

You can see all the data in a table with the following query:

SELECT * FROM tablename

This query gets all the data from a table. You can find out how many records are in the table and get a general idea of the data by browsing the output.

You can see exactly how many records are in a table with the following query:

SELECT COUNT(*) FROM tablename

This query outputs the number of records contained in the table.

Retrieving Information from a Database

The only purpose in storing information is to have it available when you need it. A database lives to answer questions. What products are for sale? Who are the customers? How many customers live in Indiana? What do the customers buy?

Many questions are answered by retrieving data from the database. For instance, to find out how many customers live in Indiana, you can retrieve all customer records where the field named state contains IN. Very often, you ask these kinds of questions in a PHP script and display the answer in a web page. In a PHP script, you might retrieve all the records for Indiana customers and display a list of their names and addresses on a web page.

To answer specific questions, you use the SELECT query. You can ask precise, complex, and detailed questions with a SELECT query. The simplest 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. Here are some tricks you can make the SELECT query perform:

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 customers.

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 customers whose addresses are in Florida.

We tell you how to use these types of queries in the text that follows.

In MySQL 4.1, MySQL added the capability 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 statements 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 http://dev.mysql.com/doc/refman/5.5/en/subqueries.html for detailed information on using subqueries.

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 from the lastName and firstName columns stored in the Customer table:

SELECT lastName,firstName FROM Customer

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 Inventory

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 Inventory

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 or highest value in the column. Table 4-1 lists some of the information that is available about a column.

Table 4-1 Information That Can Be Selected

For example, the query to find out the highest price in an Inventory table is

SELECT MAX(price) FROM Inventory

SQL words that look like MAX() and SUM(), with parentheses following the name, are functions. SQL provides many functions in addition to those in Table 4-1. Some functions, like those in Table 4-1, 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 http://dev.mysql.com/doc/refman/5.5/en/functions.html.

Retrieving data in a specific order

You might want to retrieve data in a particular order. For instance, in the Customer table, you might want customers organized in alphabetical order by last name. Or, in the Inventory table, you might want the various products grouped by category.

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, add this phrase to your SELECT query:

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 DESC before the column name. For example:

SELECT * FROM Customers 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 Category:

SELECT * FROM Inventory GROUP BY Category

You can use GROUP BY and ORDER BY in the same query.

Retrieving data from specific rows

Frequently, you don’t want all the information from a table. You want information only from selected 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 customers who live in California, or you can list only products that are a certain category of clothes.

LIMIT: Allows you to limit the number of rows from which information is retrieved. For instance, you can request the information from only the first three rows in the table.

DISTINCT: Allows you to request information from only one row of identical rows. For instance, in a 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 customer ever logged in?" rather than the question "How many times has the customer logged in?"

Using a WHERE clause

The WHERE clause of the SELECT query enables you to make complicated selections. For instance, suppose your boss wants to know all the customers whose last names begin with B, who live in Indianapolis, and who have an 8 in either their phone or fax number. (We're sure there are many uses for such a list.) You can get this list for your boss in a SELECT query with 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.

Some common expressions are shown in Table 4-2.

Table 4-2 Expressions for the WHERE Clause

You can combine any of the expressions in Table 4-2 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 customers whose names begin with B, who live in Indianapolis, and who have an 8 in either their phone or fax number:

SELECT lastName,firstName FROM Customer

WHERE lastName LIKE "B%"

AND city = "Indianapolis"

AND (phone LIKE "%8%" OR fax LIKE "%8%")

Notice the parentheses in the last line. You wouldn't get the results that you 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 customers whose names begin with B and who live in Indianapolis and whose phone numbers have an 8 in them and all customers whose fax numbers have an 8 in them, whether or not they live in Indianapolis and whether or not their name begins with a B. When the last OR is processed, customers 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 it doesn't stand alone, but the expression after the OR does stand alone, resulting in the selection of all customers with an 8 in their fax number.

Using the LIMIT keyword

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 customers who live in Texas, use this query:

SELECT * FROM Customer WHERE state="TX" LIMIT 3

Using the DISTINCT keyword

Rows in the table can have identical values in one or more columns. However, in some cases, when you SELECT a column, you don't want to retrieve multiple rows with identical values. You want to retrieve the value only once. For example, suppose you have a table of products with one field called Category. The data undoubtedly contains many products in each category. Now suppose you want to display a list of all the categories available in the database. You want this list to contain each category listed only once. The keyword DISTINCT is provided for this purpose.

To prevent a SELECT query from returning all identical records, add the keyword DISTINCT immediately after SELECT, as follows:

SELECT DISTINCT Category FROM Product

Combining information from more than one table

In previous sections of this chapter, we 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.

Sometimes your question requires information from more than one table. For instance, the question, "How many orders did customer Joe Smith place during the months of April and December?" requires information from multiple tables. You can ask this question easily in a single SELECTquery by combining multiple tables.

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.

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 contains 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 (OldMember) and a separate table of current members (Member). 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 aren't added to the result.

You can use ORDER BY with each SELECT query, as we discuss in the Retrieving data in a specific order section, earlier in this chapter, 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

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 table1 has two columns (memberID and height), and table2 has two columns (memberID and weight), a join results in a table with four columns: memberID (from table1), height, memberID (from table2), 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.

Inner join: The results table produced by an inner join contains only rows that existed in both tables.

Outer join: 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 must 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, but they must contain the same type of data.

As an example of inner and outer joins, consider a Clothes catalog with two tables. One table is Product, with the two columns Name and Type holding the following data:

Name Type

T-shirt Shirt

Dress shirt Shirt

Jeans Pants

The second table is Color, with two columns Name and Color holding the following data:

Name Color

T-shirt white

T-shirt red

Loafer black

You need to ask a question that requires information from both tables. If you do an inner join with the following query:

SELECT * FROM Product,Color WHERE Product.Name = Color.Name

you get the following results table with four columns: Name (from Product), Type, Name (from Color), and Color.

Name Type Name Color

T-shirt Shirt T-shirt white

T-shirt Shirt T-shirt red

Notice that only T-shirt appears in the results table — because only T-shirt was in both of the original tables, before the join. On the other hand, suppose you do a left outer join with the following query:

SELECT * FROM Product LEFT JOIN Color

ON Product. Name=Color. Name

You get the following results table, with the same four columns — Name (from Product), Type, Name (from Color), and Color — but with different rows:

Name Type Name Color

T-shirt Shirt T-shirt white

T-shirt Shirt T-shirt red

Dress shirt Shirt <NULL> <NULL>

Jeans Pants <NULL> <NULL>

This table has four rows. It has the same first two rows as the inner join, but it has two additional rows — rows that are in the Product table on the left but not in the Color table. Notice that the columns from the table Color are blank for the last two rows.

And, on the third hand, suppose that you do a right outer join with the following query:

SELECT * FROM Product RIGHT JOIN Color

ON Product.petName=Color. Name

You get the following results table, with the same four columns, but with still different rows:

petName petType petName petColor

T-shirt Shirt T-shirt white

T-shirt Shirt T-shirt red

<NULL> <NULL> Loafers Black

Notice that these results contain all the rows for the Color table on the right but not for the Product table. Notice the blanks in the columns for the Product table, which doesn't have a row for Loafers.

The joins that we discuss so far find matching entries in tables. Sometimes it's useful to find out which rows in a table have no matching entries in another table. For example, suppose that you want to know who has never logged in to your Members Only section. Suppose you have one table with the member's login name (Member) and another table with the login dates (Login). You can ask this question by selecting from the two tables. You can find out which login names don't have an entry in the Login table with the following query:

SELECT loginName FROM Member LEFT JOIN Login

ON Member.loginName=Login.loginName

WHERE Login.loginName IS NULL

This query gives you a list of all the login names in the Member table that aren't in the Login table.

Updating Information in a Database

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

The UPDATE statement 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 statement. 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 Customer table, use this statement:

UPDATE Customer SET street="3423 RoseLawn",

phone="555-555-5555"

WHERE lastName="Contrary"

Removing Information from a Database

Keep the information in your database up to date by deleting obsolete information. However, be very careful when removing information. After you drop the data, it’s gone forever. It cannot be restored. You only get it back if you enter it all again.

You can remove a row or a column from a table, or you can remove the entire table or database and start over.

You can remove a row from a table with the DELETE statement:

DELETE FROM tablename WHERE clause

Be extremely careful when using DELETE. If you use a DELETE statement without a WHERE clause, it will delete all the data in the table. We mean all the data. We repeat, all the data. The data cannot be recovered. This function of the DELETE statement is right at the top of our don't-try-this-at-home list.

You can delete a column from a table by using the ALTER statement:

ALTER TABLE tablename DROP columnname

You can remove the entire table or database with

DROP TABLE tablename

or

DROP DATABASE databasename