The MATCH…AGAINST construct can be used on columns that have been given a FULL TEXT index. With it, you can make own-language searches as you would in an internet search engine.
Unlike the use of WHERE…= or WHERE…LIKE, MATCH…AGAINST lets you enter multiple words in a search query and checks them against all words in the FULLTEXT columns. FULLTEXT indexes are case-insensitive, so it makes no difference what case is used in your queries.

Assuming that you have added a FULLTEXT index to the author and title columns, enter the three queries. The first asks for any rows that contain
the word and to be returned. Because and is a stopword, MySQL will ignore it and the query will always produce an empty set—no matter what is stored in the columns. The second query asks for any rows that contain both of the words curiosity and shop anywhere in them, in any order, to be returned. And the last query applies the same kind of search for the words Aro and Markku.

Using MATCH…AGAINST on FULLTEXT indexes:

SELECT author,title FROM classics
WHERE MATCH(author,title) AGAINST(’30’);
SELECT author,title FROM classics
WHERE MATCH(author,title) AGAINST(‘kautta aikain’);
SELECT author,title FROM classics
WHERE MATCH(author,title) AGAINST(‘parhaat’);

The LIMIT qualifier enables you to choose how many rows to return in a query, and where in the table to start returning them. When passed a single parameter, it tells MySQL to start at the beginning of the results and just return the number of rows given in that parameter. If you pass it two parameters, the first indicates the offset from the start of the results where MySQL should start the display, and the second indicates how many to return. You can think of the first parameter as saying, “Skip this number of results at the start.”

Next example includes three commands. The first returns the first three rows from the table. The second returns two rows starting at position 1 (skipping the first row). The last command returns a single row starting at position 3 (skipping the first three rows)

Limiting the number of results returned:
SELECT author,title FROM classics LIMIT 3;
SELECT author,title FROM classics LIMIT 1,2;
SELECT author,title FROM classics LIMIT 3,1;

Be careful with the LIMIT keyword, because offsets start at 0, but
the number of rows to return starts at 1. So, LIMIT 1,3 means
return three rows starting from the second row. You could look at
the first argument as stating how many rows to skip, so that in
English the instruction would be “Return 3 rows, skipping the first
1.”

The WHERE keyword enables you to narrow down queries by returning only those where a certain expression is true. Returns only the rows where the column exactly matches the string 30 suosikkia, using the equality operator =.

Using the WHERE keyword:

SELECT author,title FROM classics WHERE author=”Markku Aro”;
SELECT author,title FROM classics WHERE isbn=”5051011157622″;

Given our current table, the two commands in display the same results.
But we could easily add more cd:s Markku Aro, in which case the first line would display all the titles he wrote and the second line would continue (because we know the ISBN is unique) to display 30 suosikkia. In other words, searches using a unique key are more predictable, and you’ll see further evidence later of the value of unique and primary keys.

You can also do pattern matching for your searches using the LIKE qualifier, which allows searches on parts of strings. This qualifier should be used with a % character before or after some text. When placed before a keyword, % means anything before. After a keyword, it means anything after 🙂

Using the LIKE qualifier:
SELECT author,title FROM classics WHERE author LIKE “Markku%”;
SELECT author,title FROM classics WHERE title LIKE “%suosikki”;
SELECT author,title FROM classics WHERE title LIKE “%30%”;

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;