Chapter 8. Introduction to MySQL

mySQL prompt mysql>

Ready and waiting for a command

UNLOCK command

Unlock table(s)

SHOW command

lists tables, databases, and many other items. List details about an object

QUIT (\q) command

Same as EXIT

OR operator

- It displays a record it either the first condition OR the second condition is true.

TINYINT

1 byte Signed range: -128 to 127 Unsigned range: 0 to 255

SMALLINT

2 bytes, Signed range: -32,768 to 32,767, Unsigned range: 0 to 65,535

MEDIUMINT

3 bytes, Signed range: -8,388,608 to 8,388,607, Unsigned range: 0 to 16,777,215

BIGINT

8 bytes, Signed range: -263 to 263 -1, Unsigned range: 0 to 264 -1 when integer values might exceed the range that is supported by the int data type.

Row

A MySQL database contains one or more tables, each of which contains records or ____. Within these ___ are various columns or fields that contain the data itself. Table 8-1 shows the contents of an example database of five publications detailing the author, title, type, and year of publication. A single record within a table, which may contain several fields A ___ is a single group of related data within a table. Relational databases contain tables with rows and columns (also known as records and fields, respectively). Columns are vertical, and hold a list of values all from the same field. _____ are your horizontal elements in a table.

Table

A _____ is a collection of related data held in a table format within a database. It consists of columns and rows. A subcontainer within a database that stores the actual data In relational databases, and flat file databases, a _____ is a set of data elements (values) using a model of vertical columns (identifiable by name) and horizontal rows, the cell being the unit where a row and column intersect. A table has a specified number of columns, but can have any number of rows.

ALTER command

Alter a database or table _____ operates on an existing table and can add, change, or delete columns. Our example adds a column named id with the following characteristics: ALTER TABLE classics ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY; INT UNSIGNED Makes the column take an integer large enough for us to store more than 4 billion records in the table. NOT NULL Ensures that every column has a value. Many programmers use NULL in a field to indicate that it doesn't have any value. But that would allow duplicates, which would violate the whole reason for this column's existence, so we disallow NULL values. AUTO_INCREMENT Causes MySQL to set a unique value for this column in every row, as described earlier. We don't really have control over the value that this column will take in each row, but we don't care: all we care about is that we are guaranteed a unique value. KEY An auto-increment column is useful as a key, because you will tend to search for rows based on this column. This will be explained in the section "Indexes". Remove the column id EX: ALTER TABLE classics DROP id; Renaming a table, like any other change to the structure or meta-information about a table, is achieved via the ALTER command. So, for example, to change the name of the table classics to pre1900, you would use the following command: ALTER TABLE classics RENAME pre1900; Changing a column's data type also makes use of the ALTER command, this time in conjunction with the MODIFY keyword. To change the data type of the column year from CHAR(4) to SMALLINT (which requires only 2 bytes of storage and so will save disk space), enter the following: ALTER TABLE classics MODIFY year SMALLINT; Looking again at Figure 8-5, you may decide that having a column named type is confusing, because that is the name used by MySQL to identify data types. Again, no problem—let's change its name to category, like this: ALTER TABLE classics CHANGE type category VARCHAR(16); Note the addition of VARCHAR(16) on the end of this command. That's because the CHANGE keyword requires the data type to be specified, even if you don't intend to change it, and VARCHAR(16) was the data type specified when that column was initially created as type. Actuall

8 bytes

Although the most memory that a numeric field can use up is __ _____, 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.

CREATE INDEX

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 A SQL command that creates indexes on the basis of a selected attribute or attributes. CREATE INDEX author ON classics (author(20)); Creates an index for a table

COUNT

Another replacement for the something parameter is COUNT, which can be used in many ways. In Example 8-17, it 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. EX: SELECT COUNT(*) FROM classics;

BACKUP command

Back up a table

BLOB (BLOB(n) )

Binary Large Object Any complex binary data including images, documents, etc. Treated as binary data—no character set

VARBINARY (data type)

Binary data with variable length.

Example of creating an index when creating a table

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;

\c command

Cancel input

GRANT command

Change user privileges

CREATE command

Create a database or table Ex: Creating database: issuing the following command to create a new database called publications: CREATE DATABASE publications; Creating a table example: CREATE TABLE classics ( author VARCHAR(128), title VARCHAR(128), type VARCHAR(16), year CHAR(4)) ENGINE InnoDB;

DROP command

Delete a database or table

STATUS (\s) command

Display the current status

TRUNCATE command

Empty a table

SOURCE command

Execute a file

EXIT command

Exit

TINYBLOB(n)

For BLOBs (Binary Large OBjects). Max length: 255 bytes : A binary large object column with a maximum length of 255 (2^8 - 1) bytes. Each _________ value is stored using a one-byte length prefix that indicates the number of bytes in the value Treated as binary data—no character set

MEDIUMTEXT (Data Type)

Holds a string with a maximum length of 16,777,215 characters __________ can store up to 16,777,215 characters i.e 16,777,215 bytes or 64MB of data. It is suitable for larger text strings like books, research papers, and code backup. It takes 3-Bytes overhead. LONGTEXT can store the maximum characters among all four, up to 4,294,967,295 characters i,e 4,294,967,295 bytes or 4GB.

LONGTEXT (data type)

Holds a string with a maximum length of 4,294,967,295 characters LONGTEXT can store the maximum characters among all four, up to 4,294,967,295 characters i,e 4,294,967,295 bytes or 4GB. This is more than enough storage for any long-form text strings. For example, a book that MEDIUMTEXT can't hold can be stored using LONGTEXT. LONGTEXT takes 4-Bytes overhead

\c

If you are partway through entering a command and decide you don't wish to execute it after all, whatever you do, don't press Ctrl-C! That will close the program. Instead, you can enter __ and press Return. Example 8-1 shows how to use the command. prevents a new line following the command

INT

In MySQL, ____________ is a numeric value without a decimal. It defines whole numbers that can be stored in a field or column. 4 bytes, Signed range: -2147483648 to 2147483647, Unsigned range: 0 to 4294967295

UNSIGNED

Integer type that holds only values greater than or equal to zero. capable of storing only positive values To specify whether a data type is ________, use the ________ qualifier. The following example creates a table called tablename with a field in it called fieldname of the data type ________ INTEGER:

AND operator

It displays a record if both the first condition and the second condition is true Ex: SELECT author,title FROM classics WHERE author LIKE "Charles%" AND author NOT LIKE "%Darwin"; SELECT author,title FROM classics WHERE author LIKE "Charles%" AND author LIKE "%Darwin"

NATURAL JOIN

Links tables by selecting only the rows with common values in their common attributes Matches each row in a table against each row in another table based on common values found in columns sharing a common name and data type. A relational operation that yields a new table composed of only the rows with common values in their common attribute(s). EX: SELECT name,author,title FROM customers NATURAL JOIN classics;

LOCK command

Lock table(s)

ORDER BY

ORDER BY sorts returned results by one or more columns in ascending or descending order A SQL clause that is useful for ordering the output of a SELECT query (for example, in ascending or descending order). Ex: 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 publications by author in ascending alphabetical order (the default), and the second returns them by title in descending order.

RENAME command

Rename a table

SQL commands and keywords are case-____________ However for the sake of clarity, you may prefer to use uppercase.

SQL commands and keywords are case-insensitive. CREATE, create, and CrEaTe all mean the same thing. However, for the sake of clarity, you may prefer to use uppercase.

TIMESTAMP

Stores all the information that the DATE datatype stores, but also includes fractional seconds. The _________ function in SQL allows us to return and represent any DateTime expression from any date or any DateTime expression. A timestamp data type can be configured in some SQL databases so that the corresponding date and time are displayed and accepted when the database is used in a different time zone.

SQL

Structured Query Language a special-purpose programming language for accessing and manipulating data stored in a relational database

Table names are case-_________ on Linux and macOS, but case-__________ on Windows. So, for the sake of portability, you should always choose a case and stick to it. The recommended style is to use lowercase for table names.

Table names are case-sensitive on Linux and macOS, but case-insensitive on Windows. So, for the sake of portability, you should always choose a case and stick to it. The recommended style is to use lowercase for table names.

Match....against

The MATCH...AGAINST construct can be used on columns that have been given a FULLTEXT index (see the section "Creating a FULLTEXT index"). With it, you can make natural-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 shown in Example 8-24. The first asks for any rows that contain the word and to be returned. If you are using the MyISAM storage engine, then because and is a stopword in that engine, MySQL will ignore it and the query will always produce an empty set—no matter what is stored in the column. Otherwise, if you are using InnoDB and is an allowed word. 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 tom and sawyer. Figure 8-13 shows the results of these queries. SELECT author,title FROM classics WHERE MATCH(author,title) AGAINST('and'); SELECT author,title FROM classics WHERE MATCH(author,title) AGAINST('curiosity shop'); SELECT author,title FROM classics WHERE MATCH(author,title) AGAINST('tom sawyer');

YEAR

The ____ function takes a DATE or DATETIME argument and returns a four-digit integer that represents the year.

DATE

The ____ type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in ' YYYY-MM-DD ' format.

WHERE Keyword

The _____ keyword enables you to narrow down queries by returning only those where a certain expression is true. Example 8-20 returns only the rows where the column exactly matches the string Little Dorrit, using the equality operator =. Example 8-21 shows a couple more examples of using WHERE with =. Ex: SELECT author,title FROM classics WHERE author="Mark Twain"; SELECT author,title FROM classics WHERE isbn="9781598184891";

BINARY (data type)

The ______ data types (see Table 8-7) store strings of bytes that do not have an associated character set. For example, you might use the BINARY data type to store a GIF image. This is arbitrary data which can be an image, a program, or anything else

TINYTEXT (Data type)

The ________ data object is the smallest of the TEXT family and is built to efficiently store short information strings. This type can store up to 255 bytes (expressed as 2^8 -1) or 255 characters and requires a 1 byte overhead.

Semicolon (;)

The _________ is used by MySQL to separate or end commands. _________ is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.

FLOAT

The float data types are used to store positive and negative numbers with a decimal point, like 35.3, -2.34, or 3597.34987. The float data type has two keywords: Type. Size. Range. takes 4 bytes of storage.

character sets

The set of symbols that may be represented in a computer at a particular time. These symbols, called characters, can be letters, digits, spaces or punctuation marks, the set includes control characters. These assign particular binary values to particular characters. The character set you use for English is obviously different from the one you'd use for Russian. You can assign the character set to a character or text column when you create it.

FULLTEXT

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.

UPDATE command

Update an existing record

USE command

Use a database Ex: USE publications; tells the DBMS to use the publications database.

VARCHAR

Variable-length character data A _______ field does not pad the text; it lets the size of the field vary to fit the text that is inserted. But VARCHAR requires a small amount of overhead to keep track of the size of each value. _______ is more efficient if sizes can vary a lot and get large. In addition, the overhead causes access to _______ data to be slightly slower than to CHAR data. Just be aware that if you ever attempt to assign a string value longer than the length allowed, it will be truncated to the maximum length declared in the table definition. Character data type with variable length. _______ is a variable length string data type, so it holds only the characters you assign to it. VARCHAR takes up 1 byte per character, + 2 bytes to hold length information. For example, if you set a VARCHAR(100) data type = 'Jen', then it would take up 3 bytes (for J, E, and N) plus 2 bytes, or 5 bytes in all.

mySQL prompt ->

Waiting for the next line of a command

mySQL prompt /*>

Waiting for the next line of a comment started with /*

mySQL prompt `>

Waiting for the next line of a string started with a backtick

mySQL prompt ">

Waiting for the next line of a string started with a double quote

mySQL prompt '>

Waiting for the next line of a string started with a single quote

Column

Within these rows are various ______ or fields that contain the data itself. Table 8-1 shows the contents of an example database of five publications detailing the author, title, type, and year of publication. The name of a field within a row In a relational database, a ______ is a set of data values of a particular type, one value for each row of the database. A column may contain text values, numbers, or even pointers to files in the operating system. ______ typically contain simple types, though some relational database systems allow columns to contain more complex data types, such as whole documents, images, or even video clips. A ______ can also be called an attribute. In the context of relational databases, a ______ is a set of data values, all of a single type, in a table. _______ define the data in a table, while rows populate data into the table.

DATETIME

YYYY-MM-DD HH:MM:SS The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ' YYYY-MM-DD hh:mm:ss ' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' . The TIMESTAMP data type is used for values that contain both date and time parts.

LIKE qualifier

You can also do pattern matching for your searches using the ____ 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. Example 8-22 performs three different queries, one for the start of a string, one for the end, and one for anywhere in a string. SELECT author,title FROM classics WHERE author LIKE "Charles%"; SELECT author,title F ROM classics WHERE title LIKE "%Species"; SELECT author,title FROM classics WHERE title LIKE "%and%";

TIME

____ function in MySQL is used to extracts the time part from a given time/datetime expression. If the expression is not a time or a datetime value, the TIME function will return '00:00:00′. If expression is NULL, the TIME function will return NULL.N

SELECT command

the SELECT command is used to extract data from a table. - Data retrieval in SQL is accomplished with the SELECT command. function that will pull specific data from the table being accessed in a relational database Retrieves zero or more rows from one or more database tables or database views. Ex: SELECT author,title FROM classics; SELECT title,isbn FROM classics;

Database

A ________ is a structured collection of records or data stored in a computer system and organized in such a way that it can be quickly searched and information can be rapidly retrieved. The overall container for a collection of MySQL data

Primary Key

A field (or group of fields) that uniquely identifies a given entity in a table a field that uniquely identifies a record in a table The field selected as a unique identifier for the database. Ex: 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;

LONGBLOB(n)

A longblob uses 4+n bytes of storage, where n is the actual size of the blob you're storing. If you're only ever storing (say) 10 bytes of blob data, you'd be using up 14 bytes of space. By comparison, a tinyblob uses 1+n bytes, so your 10 bytes would occupy 11 bytes of space, a 3 byte savings Description. A BLOB column with a maximum length of 4,294,967,295 bytes or 4GB (232 - 1). The effective maximum length of LONGBLOB columns depends on the configured maximum packet size in the client/server protocol and available memory

TEXT (Data Type)

A sequence of characters and punctuation that contains textual information (also called string data type) _____ is a variable-length data type that can store long character strings. _____can hold up to 2,147,483,647 bytes of data. The actual storage used depends on the length of the character string. Note: TEXT has been deprecated and will be removed in some future release of SQL Server. Use VARCHAR(Max) instead.

DELETE command

Delete a row from a table. 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, if you typed Example 8-18, you should remove Little Dorrit by entering the commands in Example 8-20. Ex: DELETE FROM classics WHERE title='Little Dorrit'; This example issues a DELETE command for all rows whose title column contains the string Little Dorrit.

DESCRIBE command

Describe a table's columns Ex: DESCRIBE classics; All being well, you will see the sequence of commands and responses shown in Example 8-4, where you should particularly note the table format displayed. The _________ command is an invaluable debugging aid when you need to ensure that you have correctly created a MySQL table. You can also use it to remind yourself about a table's field or column names and the types of data in each one. Let's look at each of the headings in detail Field: The name of each field or column within a table Type: The type of data being stored in the field Null: Whether the field is allowed to contain a value of NULL Key: What type of key, if any, has been applied (keys or indexes in MySQL are quick ways to look up and search for data) Default: The default value that will be assigned to the field if no value is specified when a new row is created Extra: Additional information, such as whether a field is set to auto-increment

MEDIUMBLOB(n)

Description. A BLOB column with a maximum length of 16,777,215 (224 - 1) bytes. Each MEDIUMBLOB value is stored using a three-byte length prefix that indicates the number of bytes in the value. Treated as binary data—no character set

HELP (\h, \?) command

Display help

InnoDB

From version 5.6 onwards ______ is the default storage engine for MySQL, and we use it here because it supports FULLTEXT searches. So long as you have a relatively up-to-date version of MySQL, you can omit the ENGINE InnoDB section of the command when creating a table, but I have kept it in for now to emphasize that this is the engine being used.

CHAR (data type)

Every string in a ____ field has the specified size. ____ field has the specified size. If you put in a smaller string, it is padded with spaces. So, ____ is slightly more efficient if the sizes are similar in all records The ____ data type stores character data in a fixed-length field. Data can be a string of single-byte or multibyte letters, numbers, and other characters that are supported by the code set of your database locale.

JOIN ON

If you wish to create an inner join, but the two tables do not have a commonly named attribute, you can use a(n) ____ clause. If a designer wishes to create an inner join, but the two tables do not have a commonly named attribute, he can use a(n) _____ clause. A way to express a join when the tables have no common attribute. The query will return only the rows that meet the indicated join condition. The join condition will typically include an equality comparison expression of two columns. Ex: SELECT name,author,title FROM customers JOIN classics ON customers.isbn=classics.isbn;

MATCH...AGAINST in Boolean mode

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." Example 8-25 illustrates Boolean mode through two queries. The first asks for all rows containing the word charles and not the word species to be returned. The second uses double quotes to request that all rows containing the exact phrase origin of be returned. Figure 8-14 shows the results of these queries. SELECT author,title FROM classics WHERE MATCH(author,title) AGAINST('+charles -species' IN BOOLEAN MODE); SELECT author,title FROM classics WHERE MATCH(author,title) AGAINST('"origin of"' IN BOOLEAN MODE); There is something of interest to note in the second query: the stopword of is part of the search string, but it is still used by the search because the double quotation marks override stopwords.

GroupBY

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 publications there are of each category in the classics table, you can issue the following query: SELECT category,COUNT(author) FROM classics GROUP BY category;

signed number

In case you are not acquainted with the terms, a _____ ________ is one with a possible range from a minus value, through 0, to a positive one; _______ _______ - Variables with signed numerals can store 0, positive, and negative numbers. Unsigned value - Variables with unsigned numerals can store only 0 and positive numbers.

INDEX

Indexes are special lookup tables that need to be used by the database search engine to speed up data retrieval. An index is simply a reference to data in a table. A database index is similar to the index in the back of a journal. It cannot be viewed by the users and just used to speed up the database access. An alphabetical listing of key words, phrases, or topics that includes the page numbers on which those items are found within a publication 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. However, if the pages column created in the section "Adding a new column" had not been deleted, it would probably not have needed an index, as most people would be unlikely to search for books by the number of pages they have. Anyway, go ahead and add an index to each of the columns, using the commands in Example 8-10. 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: The Adventures of Tom Sawyer It will actually store in the index only the first 20 characters: The Adventures of To 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 (F for Fiction, N for Non-fiction, and P for Play), 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.

INSERT command

Insert data Ex: To add data to a table, use the INSERT command. Let's see this in action by populating the table classics with the data from Table 8-1, using one form of the INSERT command repeatedly (Example 8-8). INSERT INTO classics(author, title, type, year) VALUES('Mark Twain','The Adventures of Tom Sawyer','Fiction','1876'); INSERT INTO classics(author, title, type, year) VALUES('Jane Austen','Pride and Prejudice','Fiction','1811'); INSERT INTO classics(author, title, type, year) VALUES('Charles Darwin','The Origin of Species','Non-Fiction','1856'); INSERT INTO classics(author, title, type, year) VALUES('Charles Dickens','The Old Curiosity Shop','Fiction','1841'); INSERT INTO classics(author, title, type, year) VALUES('William Shakespeare','Romeo and Juliet','Play','1594'); Don't worry about the SELECT command for now—we'll come to it in the section "Querying a MySQL Database". Suffice it to say that, as typed, it will display all the data you just entered. Let's go back and look at how we used the INSERT command. The first part, INSERT INTO classics, tells MySQL where to insert the following data. Then, within parentheses, the four column names are listed—author, title, type, and year—all separated by commas. This tells MySQL that these are the fields into which the data is to be inserted. The second line of each INSERT command contains the keyword VALUES followed by four strings within parentheses, separated by commas. This supplies MySQL with the four values to be inserted into the four columns previously specified. (As always, my choice of where to break the lines was arbitrary.) Each item of data will be inserted into the corresponding column, in a one-to-one correspondence. If you accidentally listed the columns in a different order from the data, the data would go into the wrong columns. Also, the number of columns must match the number of data items. There's also a shortcut for inserting multiple rows of data, as in Example 8-28, in which you can replace the three separate INSERT INTO queries with a single one listing the data to be inserted, separated by commas, like this: INSERT INTO customers(name,isbn) VALUES ('Joe Bloggs','9780099533474'), ('Mary Smith','9780582506206'), ('Jack W

ZEROFILL

__________ pads the displayed value of the field with zeros up to the display width specified in the column definition EX: 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.

DOUBLE / REAL

a _______ can represent much larger numbers than a float. Both data types represent numbers with decimals, but a float is 32 bits in size while a ______ is 64 bits. A double is twice the size of a float — thus the term double.

AUTO_INCREMENT

The ______________in SQL is a feature that is applied to a field so that it can automatically generate and provide a unique value to every record that you enter into an SQL table. This field is often used as the PRIMARY KEY column, where you need to provide a unique value for every record you add. As its name implies, a column given this data type will set the value of its contents to that of the column entry in the previously inserted row, plus 1. EX: ALTER TABLE classics ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY;

unsigned number

a number with a non-negative value of 0 or more. When we set the MySQL INT UNSIGNED column, it means that negative values cannot be inserted in this column. The maximum range value with the UNSIGNED data type is 4294967295, while the minimum one is 0.

LIMIT Qualifier

The _____ 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." Example 8-23 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). Figure 8-12 shows the results of issuing these three commands. 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."

DISTINCT qualifier keyword

The ________ 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 books by the same author, you'll normally see a long list with the same author names over and over. But by adding the ________ 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 (Example 8-18). SELECT DISTINCT author FROM classics;

UPDATE...SET

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 8-26 shows the use of _______________ in two different ways. You can see the results in Figure 8-15. UPDATE classics SET author='Mark Twain (Samuel Langhorne Clemens)' WHERE author='Mark Twain'; UPDATE classics SET category='Classic Fiction' WHERE category='Fiction'; In the first query, Mark Twain's real name of Samuel Langhorne Clemens 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 Fiction in the category column to the term Classic Fiction. 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.

stopwords

Words, such as a, the, and in, that are so commonly used that they can hinder accurate record retrieval in a FULLTEST search. 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 as in Example 8-15, 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). Ex: ALTER TABLE classics ADD FULLTEXT(author,title);

AS (Aliasing)

You can also save yourself some typing and improve query readability by creating aliases using the AS keyword. Simply follow a table name with AS and the alias to use. The following code, therefore, is also identical in action to Example 8-29: SELECT column_name AS column_alias FROM table_name or SELECT column_name FROM table_name AS table_alias Aliases can be particularly useful when you have long queries that reference the same table names many times. You can also use AS to rename a column (whether or not joining tables), like this: SELECT name AS customer FROM customers ORDER BY customer;