Chapter 8 - Introduction to MySQL Flashcards
What is the purpose of the semicolon in My SQL queries?
The semicolon is used by MySQL to separate or end commands. If you forget to enter it, MySQL will issue a prompt and wait for you to enter it. (In the answers in this section, I’ve left off the semicolon, because it looks strange in the text. But it must terminate every statement.)
Which command would you use to view the available databases or tables?
To see the available databases, type SHOW databases. To see tables within a database that you are using, type SHOW tables. (These commands are case-insensitive.)
How would you create a new MySQL user on the local host called newuser with a password of newpass and with access to everything in the database newdatabase?
To create this new user, use the GRANT command like this: GRANT PRIVILEGES ON newdatabase.* TO ‘newuser’@’localhost’ IDENTIFIED BY ‘newpassword’;
How can you view the structure of a table?
To view the structure of a table, type DESCRIBE tablename.
What is the purpose of a MySQL index?
The purpose of a MySQL index is to substantially decrease database access times by maintaining indexes of one or more key columns, which can then be quickly searched to locate rows within a table.
What benefit does a FULLTEXT index provide?
A FULLTEXT index enables natural-language queries to find keywords, wherever they are in the FULLTEXT column(s), in much the same way as using a search engine.
What is a stopword?
A stopword is a word that is so common that it is considered not worth including in a FULLTEXT index or using in searches. However, it does participate in a search when it is part of a larger string bounded by double quotes.
Both SELECT DISTINCT and GROUP BY cause the display to show only one output row for each value in a column, even if multiple rows contain that value. What are the main differences between SELECT DISTINCT and GROUP BY?
SELECT DISTINCT essentially affects only the display, choosing a single row and eliminating all the duplicates. GROUP BY does not eliminate rows, but combines all the rows that have the same value in the column. Therefore, GROUP BY is useful for performing an operation such as COUNT on groups of rows. SELECT DISTINCT is not useful for that purpose.
Using the SELECT …WHERE construct, how would you return only rows containing the word Langhorne somewhere in the author column of the classics table used in this chapter?
To return only those rows containing the word Langhorne somewhere in the column author of the table classics, use a command such as: SELECT * FROM classics WHERE author LIKE “%Langhorne%”;
What needs to be defined in two tables to make it possible for you to join them together?
When you’re joining two tables together, they must share at least one common column such as an ID number or, as in the case of the classics and customers tables, the isbn column.
