False. Rows cannot be added with an SQL statement that changes the table structure.
True. MySQL will convert existing data if necessary.
False. MySQL will tell you to use the DROP TABLE command for this action.
True. To do so, specify the keyword FIRST at the end of the ADD clause that provides the column definition.
True, although this will happen only as a side effect. You could truncate existing data by shortening the length of a CHAR or VARCHAR column, or you could convert data by changing the datatype.
You cannot do this. Column names in a table must be unique no matter what the lettercase is.
Either of the following statements renames the table:
ALTER TABLE tbl RENAME TO tbl_new; RENAME TABLE tbl TO tbl_new;
Indexes can speed up table scans, especially for large tables, and they can be used to place restrictions on columns to ensure that a column or a set of columns may contain only unique-valued entries.
The Key value in DESCRIBE output for a UNIQUE index will be UNI or PRI if the index does not allow NULL values. The Key value is MUL if the index does allow NULL values because NULL in a UNIQUE index is a special case: Multiple NULL values are allowed, unlike any other value. For mytable, the Key value is MUL, which indicates that the UNIQUE index on col allows multiple NULL values. Consequently, the INSERT statement will not fail, even though it inserts several NULL values.
No, it isn't possible. A PRIMARY KEY can only contain columns that are specified as NOT NULL.
Yes. Because the combination of col1 and col2 has unique and non-NULL values only, it's possible to create a PRIMARY KEY with this SQL statement:
ALTER TABLE mytable ADD PRIMARY KEY (col1, col2);
mysql> ALTER TABLE mytable -> ADD col0 INT FIRST, -> ADD col2 INT AFTER col1, -> ADD col4 INT -> ;
SHOW CREATE TABLE tbl will display all index information for the table, including composite indexes. SHOW INDEX FROM tbl also shows index information, although the output might not be as easy to interpret.
With ALTER TABLE ... ADD INDEX, if you don't explicitly provide a name for the index, MySQL creates an index name based on the name of the first indexed column. With CREATE INDEX, an error occurs if you don't provide a name for the index.
No. If you want to drop more than one index at the same time, you must use ALTER TABLE ... DROP INDEX.
mysql> CREATE TABLE tbl ( -> col1 INT NOT NULL, -> col2 INT NOT NULL, -> PRIMARY KEY (col1, col2) -> );
MEDIUMINT UNSIGNED can hold numbers up to 16,777,215. The UNSIGNED option ensures that you don't store negative numbers by accident. Without UNSIGNED, the maximum positive number would only be 8,388,607. The storage requirement is 3 bytes per row.
TEXT is the column datatype best suited to this situation. It can hold up to 65,535 characters. For 300 characters, the storage requirement is 302 bytes (300 bytes for a remark plus 2 bytes to store the actual length of the entry).
'1000-01-01 '(January 1, 1000) is the earliest date that can be stored in a DATE column. You might be able to store earlier dates, but doing so isn't recommended because unexpected results from date operations might result.
The value inserted is '2002-02-31 '. MySQL performs only elementary checking on the validity of a date.
The value inserted is '2010-00-00 '(this could differ in your MySQL version). The valid DATE range is from '1000-01-01 'to '9999-12-31 '. If you insert values outside of this range, you'll get unpredictable results.
If a date is entered with a two-digit year value, MySQL converts it to a date between '1970-01-01 'and '2069-12-31 '. For each of the three examples, then, the results are as follows:
The value inserted is '2012-00-00 '. MySQL interprets the inserted value as a date, '12-00-00 ', which is interpreted as the year '2012 '.
The value inserted is '0000-00-00 '. '12:00 'is considered to be an invalid date and so gets converted to the "zero" date value '0000-00-00 '.
The value inserted is '2002-02-08 '. '2002-02-08 21:39 'is a DATETIME value. When inserted into a DATE column, the time portion of a DATETIME value is truncated.
+--------+--------+------------+ | number | string | dates | +--------+--------+------------+ | 1 | 22 | 0000-00-00 | +--------+--------+------------+
22 is converted to the string value '22 '. The number 333 is interpreted as an invalid date, so the "zero" date is stored.
blog comments powered by Disqus