The term BLOB stands for Binar Large OBject, and therefore, as you would think, the BLOB data type is most useful for binary data in excess of 65,536 bytes in size. The main other difference between the BLOB and BINARY data types is that BLOBs cannot have default values.

• TINYBLOB(n) Up to n (<= 255) Treated as binary data—no character set
• BLOB(n) Up to n (<= 65535) Treated as binary data—no character set
• MEDIUMBLOB(n) Up to n (<= 1.67e+7) Treated as binary data—no character set
• LONGBLOB(n) Up to n (<= 4.29e+9) Treated as binary data—no character set

MySQL supports various numeric data types, from a single byte up to doubleprecision floating-point numbers. Although the most memory that a numeric field can use up is 8 bytes, you are well advised to choose the smallest data type that will adequately handle the largest value you expect. This will help keep your databases small and quickly accessible.

Next lists the numeric data types supported by MySQL and the ranges of values they can contain. In case you are not acquainted with the terms, a signed number is one with a possible range from a minus value, through 0, to a positive one; and anunsigned number has a value ranging from 0 to a positive one. They can both hold the same number of values; just picture a signed number as being shifted halfway to the left so that half its values are negative and half are positive. Note that floating-point
values (of any precision) may only be signed.

Data type / Bytes used / Minimum value / Maximum value / Unsigned min / Unsigned max
TINYINT 1 –128 127 0 255
SMALLINT 2 –32768 32767 0 65535
MEDIUMINT 3 –8.38e+6 8.38e+6 0 1.67e+7
INT / INTEGER 4 –2.15e+9 2.15e+9 0 4.29e+9
BIGINT 8 –9.22e+18 9.22e+18 0 1.84e+19
FLOAT 4 –3.40e+38 3.4e+38 – –
DOUBLE / REAL 8 –1.80e+308 1.80e+308 – –

To specify whether a data type is unsigned, use the UNSIGNED qualifier. The following example creates a table called tablename with a field in it called fieldname of the data type UNSIGNED INTEGER:

CREATE TABLE tablename (fieldname INT UNSIGNED);

When creating a numeric field, you can also pass an optional number as a parameter, like this:

CREATE TABLE tablename (fieldname INT(4));

But you must remember that, unlike with the BINARY and CHAR data types, this parameter does not indicate the number of bytes of storage to use. It may seem counterintuitive, but what the number actually represents is the display width of the data in the field when it is retrieved. It is commonly used with the ZEROFILL qualifier, like
this:

CREATE TABLE tablename (fieldname INT(4) ZEROFILL);

What this does is cause any numbers with a width of less than four characters to be padded with one or more zeros, sufficient to make the display width of the field four characters long. When a field is already of the specified width or greater, no padding takes place.

Character data can also be stored in one of the set of TEXT fields. The differences between these fields and VARCHAR fields are small:

• Prior to version 5.0.3, MySQL would remove leading and trailing spaces from VARCHAR fields.
• TEXT fields cannot have default values.

• MySQL indexes only the first n characters of a TEXT column (you specify n when you create the index).

What this means is that VARCHAR is the better and faster data type to use if you need to search the entire contents of a field. If you will never search more than a certain number of leading characters in a field, you should probably use a TEXT data type.

• TINYTEXT(n) Up to n (<= 255) Treated as a string with a character set
• TEXT(n) Up to n (<= 65535) Treated as a string with a character set
• MEDIUMTEXT(n) Up to n (<= 1.67e+7) Treated as a string with a character set
• LONGTEXT(n) Up to n (<= 4.29e+9) Treated as a string with a character set

The data types that have smaller maximums are also more efficient; therefore, you should use the one with the smallest maximum that you know is enough for any string you will be storing in the field.

In a similar fashion to ORDER BY, you can group results returned from queries using GROUP BY, which is good for retrieving information about a group of data. For example, if you want to know how many artist there are of each category in the classics table, you can issue the following query:

SELECT category,COUNT(author) FROM classics GROUP BY category;

ORDER BY sorts returned results by one or more columns in ascending or descending order.

SELECT author,title FROM classics ORDER BY author;
SELECT author,title FROM classics ORDER BY title DESC;

As you can see, the first query returns the artist by author in ascending alphabetical order (the default), and the second returns them by title in descending order.

If you wanted to sort all the rows by author and then by descending year of publication (to view the most recent first), you would issue the following query:

SELECT author,title,year FROM classics ORDER BY author,year DESC;

This shows that each ascending and descending qualifier applies to a single column. The DESC keyword applies only to the preceding column, year. Because you allow author to use the default sort order, it is sorted in ascending order. You could also have explicitly specified ascending order for that column, with the same results:

SELECT author,title,year FROM classics ORDER BY author ASC,year DESC;

This construct allows you to update the contents of a field. If you wish to change the contents of one or more fields, you need to first narrow in on just the field or fields to be changed, in much the same way you use the SELECT command.

Example:
UPDATE classics SET author=’Markku Aro (Markku Puputti)’
WHERE author=’Markku Aro’;
UPDATE classics SET type=’Suomi POP’
WHERE type=’POP’;

In the first query, Markku Aros’s real name of Markku Puputti was appended to his pen name in parentheses, which affected only one row. The second query, however, affected three rows, because it changed all occurrences of the word POP in the category column to the term Suomi POP. When performing an update, you can also make use of the qualifiers you have already seen, such as LIMIT, and the following ORDER BY and GROUP BY keywords.

If you wish to give your MATCH…AGAINST queries even more power, use Boolean mode. This changes the effect of the standard FULLTEXT query so that it searches for any combination of search words, instead of requiring all search words to be in the text. The presence of a single word in a column causes the search to return the row. Boolean mode also allows you to preface search words with a + or – sign to indicate
whether they must be included or excluded. If normal Boolean mode says, “Any of these words will do,” a plus sign means, “This word must be present; otherwise, don’t return the row.” A minus sign means, “This word must not be present; its presence disqualifies the row from being returned.”

The first asks for all rows containing the word suosikkia and not the word Aro to be returned. The second uses double quotes to request that all rows containing the exact phrase origin of be returned.

SELECT author,title FROM classics
WHERE MATCH(author,title)
AGAINST(‘+suosikkia -Aro’ IN BOOLEAN MODE);
SELECT author,title FROM classics
WHERE MATCH(author,title)
AGAINST(‘kaikki parhaat’ IN BOOLEAN MODE);

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%”;