When you need to remove a row from a table, use the DELETE command. Its syntax is similar to the SELECT command and allows you to narrow down the exact row or rows to delete using qualifiers such as WHERE and LIMIT.

Now that you’ve seen the effects of the DISTINCT qualifier, you should remove 30 suosikkia by entering the commands in

you should remove 30 suosikkia by entering the commands in

DELETE FROM classics WHERE title=’30 suosikkia’;

This example issues a DELETE command for all rows whose title column contains the string 30 suosikkia.

The WHERE keyword is very powerful, and important to enter correctly; an error could lead a command to the wrong rows (or have no effect in cases where nothing matches the WHERE clause). So now we’ll spend some time on that clause, which is the heart and soul of SQL 🙂

The DISTINCT qualifier (and its synonym DISTINCTROW) allows you to weed out multiple entries when they contain the same data. For instance, suppose that you want a list of all authors in the table. If you select just the author column from a table containing multiple cd:s by the same author, you’ll normally see a long list with the same author names over and over. But by adding the DISTINCT keyword, you can show each author just once. So, let’s test that out by adding another row that repeats one of our existing authors.

Duplicating data:

INSERT INTO classics(author, title, category, year, isbn)
VALUES(‘Markku Aro’,’30 suosikkia’,’POP’,’1998′, ‘5051011157622’);

Now that Markku Aro appears twice in the table, we can compare the results of using SELECT with and without the DISTINCT qualifier.

show that the simple SELECT lists Aro twice, and the command with the DISTINCT qualifier shows him only once.

With and without the DISTINCT qualifier:
SELECT author FROM classics;
SELECT DISTINCT author FROM classics;

Another replacement for the something parameter is COUNT, which can be used in many ways. Command displays the number of rows in the table by passing * as a parameter, which means all rows. As you’d expect, the result returned is 5, as there are five publications in the table.

Counting rows:
SELECT COUNT(*) FROM classics;

So far, we’ve created a MySQL database and tables, populated them with data, and added indexes to make them fast to search. Now it’s time to look at how these searches are performed, and the various commands and qualifiers available.

SELECT command is used to extract data from a table. In
that section, I used its simplest form to select all data and display it—something you will never want to do on anything but the smallest tables, because all the data will scroll by at an unreadable pace. Alternatively, on Unix/Linux computers, you can tell MySQL to page output a screen at a time by issuing the command:
pager less;

This pipes output to the less program. To restore standard output and turn paging off, you can issue this command:
nopager;

Let’s now examine SELECT in more detail. The basic syntax is:
SELECT something FROM tablename;

Two different SELECT statements:

SELECT author,title FROM classics;
SELECT title,isbn FROM classics;

Unlike a regular index, MySQL’s FULLTEXT allows super-fast searches of entire columns of text. It stores every word in every data string in a special index that you can search using “natural language,” in a similar manner to using a search engine.

It’s not strictly true that MySQL stores all the words in a FULLTEXT
index, because it has a built-in list of more than 500 words that it
chooses to ignore because they are so common that they aren’t very
helpful for searching anyway—so-called stopwords. This list
includes the, as, is, of, and so on. The list helps MySQL run much
more quickly when performing a FULLTEXT search and keeps database
sizes down.

Here are some things that you should know about FULLTEXT indexes:

• Since MySQL 5.6, InnoDB tables can use FULLTEXT indexes, but prior to that FULLTEXT indexes could be used only with MyISAM tables. If you need to convert a table to MyISAM, you can usually use the MySQL command ALTER TABLE tablename ENGINE = MyISAM;.
• FULLTEXT indexes can be created for CHAR, VARCHAR, and TEXT columns only.
• A FULLTEXT index definition can be given in the CREATE TABLE statement when a table is created, or added later using ALTER TABLE (or CREATE INDEX).
• For large data sets, it is much faster to load your data into a table that has no FULLTEXT index and then create the index than to load data into a table that has an existing FULLTEXT index.

To create a FULLTEXT index, apply it to one or more records, which adds a FULLTEXT index to the pair of columns author and title in the classics
table (this index is in addition to the ones already created and does not affect them).

ALTER TABLE classics ADD FULLTEXT(author,title);

You can now perform FULLTEXT searches across this pair of columns.

Hint, if you find that MySQL is running slower than you think it should
be when accessing your database, the problem is usually related to
your indexes. Either you don’t have an index where you need one,
or the indexes are not optimally designed. Tweaking a table’s
indexes will often solve such a problem.

So far, you’ve created the table classics and ensured that MySQL can search it quickly by adding indexes, but there’s still something missing. All the datainfo in the table can be searched, but there is no single unique key for each publication to enable instant accessing of a row. The importance of having a key with a unique value for each row will come up when we start to combine data from different tables.

So let’s go ahead and create a new column for this key. Now, bearing in mind that ISBNs or standardID are 13 characters long, you might think that the following command would do the job:

ALTER TABLE classics ADD isbn CHAR(13) PRIMARY KEY;

But it doesn’t. If you try it, you’ll get the error Duplicate entry for key 1. The reason is that the table is already populated with some data and this command is trying to add a column with the value NULL to each row, which is not allowed, as all values must be unique in any column having a primary key index. However, if there were no data already in the table, this command would work just fine, as would adding the
primary key index upon table creation.

In our current situation, we have to be a bit sneaky and create the new column without an index, populate it with data, and then add the index.

Populating the isbn column with data and using a primary key:

ALTER TABLE classics ADD isbn CHAR(13);
UPDATE classics SET isbn=’5051011157622′ WHERE year=’ 1998′;
UPDATE classics SET isbn=’5051865309826′ WHERE year=’1811′;
UPDATE classics SET isbn=’cls0135099′ WHERE year=’1990′;
UPDATE classics SET isbn=’6417832081437′ WHERE year=’1975′;
UPDATE classics SET isbn=’5052498183227′ WHERE year=’2020′;
ALTER TABLE classics ADD PRIMARY KEY(isbn);
DESCRIBE classics;

Creating the table classics with a primary key:
CREATE TABLE classics (
author VARCHAR(128),
title VARCHAR(128),
category VARCHAR(16),
year SMALLINT,
isbn CHAR(13),
INDEX(author(20)),
INDEX(title(20)),
INDEX(category(4)),
INDEX(year),
PRIMARY KEY (isbn)) ENGINE InnoDB;

You don’t have to wait until after creating a table to add indexes. In fact, doing so can be timeconsuming, as adding an index to a large table can take a very long time. Therefore, let’s look at a command that creates the table classics with indexes already in place.

Creating the table classics with indexes:
CREATE TABLE classics (
author VARCHAR(128),
title VARCHAR(128),
category VARCHAR(16),
year SMALLINT,
INDEX(author(20)),
INDEX(title(20)),
INDEX(category(4)),
INDEX(year)) ENGINE InnoDB;

An alternative to using ALTER TABLE to add an index is to use the CREATE INDEX command.
They are equivalent, except that CREATE INDEX cannot be used for creating a PRIMARY KEY

These two commands are equivalent:
ALTER TABLE classics ADD INDEX(author(20));
CREATE INDEX author ON classics (author(20));

The way to achieve fast searches is to add an index, either when creating a table or at any time afterward. But the decision is not so simple. For example, there are different index types, such as a regular INDEX, a PRIMARY KEY, or a FULLTEXT index. Also, you must decide which columns require an index, a judgment that requires you to predict
whether you will be searching any of the data in each column. Indexes can get more complicated too, because you can combine multiple columns in one index. And even when you’ve decided that, you still have the option of reducing index size by limiting the amount of each column to be indexed. If we imagine the searches that may be made on the classics table, it becomes apparent that all of the columns may need to be searched.

ALTER TABLE classics ADD INDEX(author(20));
ALTER TABLE classics ADD INDEX(title(20));
ALTER TABLE classics ADD INDEX(category(4));
ALTER TABLE classics ADD INDEX(year);
DESCRIBE classics;

The first two commands create indexes on the author and title columns, limiting each index to only the first 20 characters. For instance, when MySQL indexes the following title:

Markku Aro
It will actually store in the index only the first 20 characters:
Markku Aro

This is done to minimize the size of the index, and to optimize database access speed. I chose 20 because it’s likely to be sufficient to ensure uniqueness for most strings in these columns. If MySQL finds two indexes with the same contents, it will have to waste time going to the table itself and checking the column that was indexed to find
out which rows really matched. With the category column, currently only the first character is required to identify a string as unique (P for POP, I for Iskelmä, and C for Classic), but I chose an index of four characters to allow for future categories that may share the first three
characters. You can also reindex this column later, when you have a more complete set of categories. And finally, I set no limit to the year column’s index, because it has a clearly defined length of four characters.

As things stand, the table classics works and can be searched without problem by MySQL—until it grows to more than a couple of hundred rows. At that point, database accesses will get slower and slower with every new row added, because MySQL has to search through every row whenever a query is issued. This is like searching through every book in a library whenever you need to look something up.

Of course, you don’t have to search libraries that way, because they have either a card index system or, most likely, a database of their own. And the same goes for MySQL, because at the expense of a slight overhead in memory and disk space, you can create a “card index” for a table that MySQL will use to conduct lightning-fast searches.