Term
| What is the "data directory"? |
|
Definition
| The directory on the MySQL server under which it stores its databases. |
|
|
Term
| What is the "database directory"? |
|
Definition
| A subdirectory of the "data directory" on the MySQL server which stores a database and has the same name as the database. |
|
|
Term
| What is the logical structure of a table? |
|
Definition
| Consists of rows and columns. |
|
|
Term
| What is the minimum number of rows in a table? |
|
Definition
|
|
Term
| What is the minimum number of columns in a table |
|
Definition
|
|
Term
| What is the table format file? |
|
Definition
| A file on the MySQL server located in the database directory that contains the table definition. |
|
|
Term
| What is the format filename? |
|
Definition
| Same as the table name, plus an .frm suffix. |
|
|
Term
| Can you use different storage engines for different tables in the same database? |
|
Definition
|
|
Term
| What additional files does the MyISAM engine create per table besides the format file? |
|
Definition
| A data file (table_name.MYD) and an index file (table_name.MYI). |
|
|
Term
| How does the InnoDB engine store data and indexes for tables? |
|
Definition
| By default, it uses a shared tablespace. |
|
|
Term
| How does the MEMORY engine store table contents? |
|
Definition
|
|
Term
| What is the maximum number of tables allowed in a database? |
|
Definition
| MySQL server places no limits, but storage engines may have limits (InnoDB has limit of 2 Billion tables within a shared tablespace) and operating systems may place a limit on the number of files in a directory. |
|
|
Term
| What factors limit the maximum size of individual tables? |
|
Definition
| Storage engines, maximum file size allowed by OS or filesystem |
|
|
Term
| What are some workarounds for file size limits for creating larger tables? |
|
Definition
* MyISAM tables can sometimes be distributed into smaller tables
* Convert the table for use with a storage engine that allows larger tables (InnoDB tablespace can be larger than a single file)
* Modify the operating system to support larger files |
|
|
Term
| What is the basic CREATE TABLE syntax? |
|
Definition
| CREATE TABLE table_name (column_definitions); |
|
|
Term
| What happens if you try to create a table that already exists? |
|
Definition
|
|
Term
| What clause can you add to a CREATE TABLE statement to specify to only create the table if it doesn't already exist? |
|
Definition
|
|
Term
| What happens if the IF NOT EXISTS clause is specified but the CREATE TABLE has a different definition than the current table? |
|
Definition
| Nothing; the table is not created and no warning is given. |
|
|
Term
| Can a table be created that does not reside in a database? |
|
Definition
|
|
Term
| What happens if the CREATE TABLE statement is not qualified with a database? |
|
Definition
| The default database is used. |
|
|
Term
| In the statement CREATE TABLE test.me (column_definitions); what do "test" and "me" indicate? |
|
Definition
| "test" is the database name and "me" is the table name |
|
|
Term
| How can you get a list of storage engines supported by your MySQL server? |
|
Definition
|
|
Term
| How can you get a list of storage engines supported by your MySQL server? |
|
Definition
|
|
Term
| How can you get a list of storage engines supported by your MySQL server? |
|
Definition
|
|
Term
| How can an engine be specified with the table creation? |
|
Definition
| CREATE TABLE t (column_definitions) ENGINE = engine; |
|
|
Term
| How can you convert a table from one storage engine to another? |
|
Definition
| ALTER TABLE t ENGINE = engine; |
|
|
Term
| How is the default storage engine determined? |
|
Definition
| The value of the storage_engine system variable; default value is MyISAM. |
|
|
Term
| What option can be used to specify the default storage engine at server startup? |
|
Definition
|
|
Term
| How can an administrator change the default storage engine globally for all clients? |
|
Definition
SET GLOBAL storage_engine = engine_name;
(clients that are already connected are unaffected) |
|
|
Term
| How can a client change its own default storage engine? |
|
Definition
SET SESSION storage_engine = engine_name;
or
SET storage_engine = engine_name; |
|
|
Term
| What happens if the ENGINE clause names a storage engine that is legal or unavailable? |
|
Definition
| The default storage engine is used and a warning is produced. |
|
|
Term
| How can a table based on another table be created? |
|
Definition
CREATE TABLE ... SELECT (populated table)
or
CREATE TABLE ... LIKE (empty table) |
|
|
Term
| What data is NOT copied by the CREATE TABLE ... SELECT statement? |
|
Definition
| PRIMARY KEY index information, AUTO_INCREMENT column attribute information, and storage engine |
|
|
Term
| What table attributes are NOT copied with CREATE TABLE ... LIKE? |
|
Definition
| The MyISAM DATA DIRECTORY or INDEX DIRECTORY and foreign key definitions. |
|
|
Term
| What are the characteristics of a TEMPORARY table? |
|
Definition
| Visible only for the client that created it, exists only for the duration of the connection in which it was created, may have the same name as a non-TEMPORARY table (in which case the non-TEMPORARY table becomes invisible), can be renamed only with ALTER TABLE (not RENAME TABLE) |
|
|
Term
| When are the contents of a MEMORY table lost? |
|
Definition
| When the server is restarted. |
|
|
Term
| Is a MEMORY table available to all client connections or just the client that created it? |
|
Definition
|
|
Term
| How can you change a table "in place"? |
|
Definition
|
|
Term
| When adding a column with ALTER TABLE, what is the default position of the new column? |
|
Definition
| After all existing columns. |
|
|
Term
| What keywords can change where a new column is inserted? |
|
Definition
| FIRST or AFTER column_name |
|
|
Term
| How can you drop a column from a table? |
|
Definition
| ALTER TABLE table_name DROP column_name; |
|
|
Term
| How can you add a column to an existing table? |
|
Definition
| ALTER table_name ADD column_definition; |
|
|
Term
| How can you change a column definition within a table? |
|
Definition
ALTER TABLE table_name MODIFY column_definition;
or (to change the name also)
ALTER TABLE table_name CHANGE column_name column_definition; |
|
|
Term
| How can you rename a table? |
|
Definition
ALTER t1 RENAME TO t2;
or
RENAME TABLE t1 TO t2; |
|
|
Term
| How can you swap the names of 2 tables? |
|
Definition
| RENAME TABLE t1 to tmp, t2 to t1, tmp TO t2; |
|
|
Term
| Can multiple alterations be specified with a single ALTER TABLE statement? |
|
Definition
| Yes, by separating the actions by commas. |
|
|
Term
| How can you remove a table when you no longer need it? |
|
Definition
|
|
Term
| Can a DROP statement specify multiple tables? |
|
Definition
| Yes, separated by commas. |
|
|
Term
| What happens if you attempt to drop a table that doesn't exist? |
|
Definition
|
|
Term
| What happens if you attempt to drop a table that doesn't exist? |
|
Definition
|
|
Term
| What clause can you add to DROP to prevent an error if the table does not exist? |
|
Definition
| IF EXISTS (a warning occurs) |
|
|
Term
| What happens if you drop a table by mistake? |
|
Definition
| You must recover from backups. |
|
|
Term
| How can you completely empty a table without dropping it? |
|
Definition
DELETE FROM t;
or
TRUNCATE TABLE t; |
|
|
Term
| What is the difference between DELETE and TRUNCATE? |
|
Definition
| DELETE can delete only a subset of records by adding a WHERE clause. |
|
|
Term
| What is the difference between a primary key index and a unique index? |
|
Definition
| A unique index can be allowed to contain NULL values and there can only be one primary key index per table. |
|
|
Term
| In what kind of index can any key value occur multiple times? |
|
Definition
|
|
Term
| What type of index is specially designed for text searching? |
|
Definition
|
|
Term
| What type of index applies only to columns with spacial data types? |
|
Definition
|
|
Term
| What keyword is a synonym with INDEX? |
|
Definition
|
|
Term
| What clause can be added to a CREATE TABLE statement to index a single column? |
|
Definition
|
|
Term
| What clause can be added to a CREATE TABLE statement to create a composite index for multiple columns? |
|
Definition
| INDEX (column_name_1, column_name_2) |
|
|
Term
| What clause can you add to name an index? |
|
Definition
| INDEX index_name (column_list), |
|
|
Term
| What is the default name for a multi-column index? |
|
Definition
| first column in the index |
|
|
Term
| What is the name for a PRIMARY KEY index? |
|
Definition
|
|
Term
| How can index names be displayed? |
|
Definition
| SHOW CREATE TABLE or SHOW INDEX |
|
|
Term
| How could you add a primary key to an existing table? |
|
Definition
| ALTER TABLE table_name ADD PRIMARY KEY (column_name); |
|
|
Term
| What are 3 differences between creating an index with ALTER TABLE, ADD INDEX and CREATE INDEX? |
|
Definition
1) CREATE INDEX requires an index name to be provided.
2) Only ALTER TABLE can create multiple indexes per statement.
3) Only ALTER TABLE supports the use of PRIMARY KEY. |
|
|
Term
| Which type of engine supports specifying an indexing algorithm? |
|
Definition
|
|
Term
| What is the default indexing algorithm for the MEMORY engine? |
|
Definition
|
|
Term
| What are hash indexes good for? |
|
Definition
| Fast lookup of unique items and equivalence comparisons. |
|
|
Term
| What is a BTREE index useful for? |
|
Definition
| Non-unique values or range comparisons. |
|
|
Term
| What clause can be used to specify a BTREE index for the MEMORY engine? |
|
Definition
| INDEX USING BTREE (column_name) |
|
|
Term
| How can an index be dropped from a table? |
|
Definition
ALTER TABLE table_name DROP INDEX index_name;
or
DROP INDEX index_name ON table_name; |
|
|
Term
| Is dropping an index a reversible action? |
|
Definition
|
|
Term
| What information resides in the TABLES table in the INFORMATION_SCHEMA database? |
|
Definition
|
|
Term
| Which table in the INFORMATION_SCHEMA table contains metadata about indexes? |
|
Definition
|
|
Term
| Which keywork specifies the database to use with SHOW TABLES? |
|
Definition
|
|
