Data Definition Language, Part 2 - Answers to Exercises, 91-114 (
Page 8 of 8 )
Answer 91:
CHAR(0) is not defined in ANSI SQL, but in MySQL it's possible to define a
column with this datatype specification. This is mainly useful when you have to
be compliant with older applications that depend on the existence of a column
but that do not actually use the value. It is also useful when you need a column
that can accept only two values. A CHAR(0) that isn't defined as NOT NULL will
occupy only one bit and can take only two values: NULL or ''(the empty
string).
Answer 92:
2,002 bytes; 2,000 bytes for the value and 2 bytes to store the length of the
value.
Answer 93:
String value: 'Africa '. Internal number: 1 because Africa
is the first member in the ENUM list.
Answer 94:
String value: ''(the empty string). Internal number: 0. Because
'Europa 'isn't a member of the ENUM list, the special error
value of ''(or 0 as the internal representation) is stored.
Answer 95:
String value: ''(the empty string). Internal number: 0. The empty
string (internally, 0) is the special error value that is stored if the inserted
value isn't a member of the ENUM list, or if the empty string (or 0) is
explicitly stored, as in this case.
Answer 96:
String value: ''(the empty string). Internal number: 0. The empty
string (internally, 0) is the special error value that is stored if the inserted
value isn't a member of the ENUM list, or if the empty string (or 0) is
explicitly stored, as in this case.
Answer 97:
String value: 'Africa '. Internal number: 1.
'Africa 'is the first member in the ENUM list. The value can
be inserted by giving the element number instead of the string value.
Answer 98:
String value: 'Africa '. Internal number: 1.
'Africa 'is the first member in the ENUM list. MySQL first
converts the string '1 'to a number before it's inserted.
Answer 99:
NULL can be inserted into the name column because the column definition
allows NULL values.
Answer 100:
These values will be inserted into the table's other INT columns (partial
listing only):
mysql>
SELECT * FROM defaults\G*********** 1. row
id: 1
col1: NULL
col2: 0
col3: 42
...
-
col1: Because this column has no defined DEFAULT value and can accept NULL
values, the value inserted is NULL.
-
col2: This column is declared NOT NULL and has no defined DEFAULT value.
Because the INSERT provides no explicit value for this column, MySQL assigns the
standard default value, in this case 0.
-
col3: This column was explicitly defined with a DEFAULT value (42), so this
value is inserted.
Answer 101:
These values will be inserted into the table's CHAR columns (partial listing
only):
mysql>
SELECT * FROM defaults\G*********** 1. row
id: 1
...
col4: NULL
col5:
col6: yoo
...
-
col4: Because this column has no defined DEFAULT value and can accept NULL
values, the value inserted is NULL.
-
col5: This column is declared NOT NULL and has no defined DEFAULT value.
Because the INSERT provides no explicit value for this column, MySQL assigns the
standard default value, in this case ''(the empty string).
-
col6: This column was explicitly declared with a DEFAULT value (
'yoo '), so this value is inserted.
Answer 102:
These values will be inserted into the table's TEXT columns (partial listing
only):
mysql>
SELECT * FROM defaults\G*********** 1. row
id: 1
...
col7: NULL
col8:
...
-
col7: Because this column can accept NULL values, the value inserted is
NULL.
-
col8: This column is declared NOT NULL. Because the INSERT provides no
explicit value for this column, MySQL assigns the standard default value, in
this case ''(the empty string). (You cannot declare a DEFAULT value for
a TEXT column.)
Answer 103:
This value will be inserted into the table's TIME column (partial listing
only):
mysql>
SELECT * FROM defaults\G*********** 1. row
id: 1
...
col9: 00:00:00
...
col9: This column is declared NOT NULL and has no defined DEFAULT value.
Because the INSERT provides no explicit value for this column, MySQL assigns the
standard default value, in this case '00:00:00 '.
Answer 104:
These values will be inserted into the table's DATE columns (partial listing
only):
mysql>
SELECT * FROM defaults\G*********** 1. row
id: 1
...
col10: NULL
col11: 0000-00-00
col12: 2002-02-08
...
-
col10: Because this column has no defined DEFAULT value and can accept NULL
values, the value inserted is NULL.
-
col11: This column is declared NOT NULL and has no defined DEFAULT value.
Because the INSERT provides no explicit value for this column, MySQL assigns the
standard default value, in this case '0000-00-00 '.
-
col12: This column was explicitly defined with a DEFAULT value (
'2002-02-08 '), so this value is inserted.
Answer 105:
These values will be inserted into the table's ENUM columns (partial listing
only):
mysql>
SELECT * FROM defaults\G*********** 1. row
id: 1
...
col13: NULL
col15: doo
...
-
col13: Because this column has no defined DEFAULT value and can accept NULL
values, the value inserted is NULL.
-
col15: The ENUM column is declared NOT NULL and has no defined DEFAULT value.
Because the INSERT provides no explicit value for this column, MySQL uses the
first list member as the standard default value.
Answer 106:
These values will be inserted into the table's SET columns (partial listing
only):
mysql>
SELECT * FROM defaults\G*********** 1. row
id: 1
...
col14: NULL
col16:
...
-
col14: Because this column has no defined DEFAULT value and can accept NULL
values, the value inserted is NULL.
-
col16: The SET column is declared NOT NULL and has no defined DEFAULT value.
Because the INSERT provides no explicit value for this column, MySQL uses the
empty string as the standard default value.
Answer 107:
You could use the SQL function LAST_INSERT_ID(). The inserted value is 1. If
you call LAST_INSERT_ID() repeatedly within the same connection, it will
continue to return the same value (1), even if other connections insert new rows
into the table.
Answer 108:
mysql>
SELECT * FROM cliptest;
+--------+--------+
| number | string |
+--------+--------+
| 255 | 0 |
| 0 | 0 |
+--------+--------+
1000000 is clipped to the maximum value of TINYINT UNSIGNED. The string is
converted to an integer number, and because it doesn't begin with an integer
part, the result of the conversion is the number 0. The NULL entries are
converted to values that match the new option NOT NULL, and because there are no
DEFAULT values specified in the ALTER TABLE statement, MySQL uses the standard
default values for integers, which is 0.
Answer 109:
The loop will run 127 times without error. With the first loop run, 1 is
inserted, with the second run, 2 is inserted, and so on. 127 is the maximum
value for a TINYINT column. An error will occur when the application tries to
insert id number 128. This number will be clipped to 127, and MySQL will try to
insert this value once again. Because of the PRIMARY KEY restriction that allows
for only unique values in the id column, this will result in a duplicate-key
error. With the mysql client, the error would be displayed as follows:
ERROR 1062: Duplicate entry '127' for key 1
Answer 110:
Either of the following statements provides the desired information:
SHOW TABLES LIKE '%test';
SHOW TABLE STATUS LIKE '%test%';
SHOW TABLES lists just the table names. SHOW TABLE STATUS displays the names
and additional table information.
Answer 111:
Either of the following statements provides the desired information:
SHOW TABLES FROM test;
SHOW TABLE STATUS FROM test;
Answer 112:
SHOW COLUMNS FROM mytest FROM test;
Answer 113:
SHOW COLUMNS FROM mytest FROM test LIKE 'id%';
Answer 114:
SHOW CREATE TABLE test.mytest;
 This chapter excerpt is from MySQL Certification Guide by Paul Dubois et al. (Sams, 2004, ISBN: 0672326329 ). Check it out at your favorite bookstore today. Buy this book now.
|
