Learning Mysql

components are separated by commas. There are other elements that you can add to a
CREATE TABLE
statement, and we’ll discuss some in a moment.
Let’s discuss the column specifications. The basic syntax is as follows
name type [NOT
NULL | NULL DEFAULT value]
. The
name
field is the column name, and it has the same limitations as database names, as discussed in the previous section. It can beat most characters in length, backward and forward slashes aren’t allowed, periods aren’t allowed, it can’t end in whitespace, and case sensitivity is dependent on the underlying operating system. The
type
defines how and what is stored in the column for example,
we’ve seen that it can beset to
CHAR
for strings,
SMALLINT
for numbers, or
TIMESTAMP
for a date and time.
If you specify NOT NULL, a row isn’t valid without a value for the column if you specify
NULL
or omit the clause, a row can exist without a value for the column. If you specify a
value
with the
DEFAULT
clause, it’ll be used to populate the column when you don’t otherwise provide data this is particularly useful when you frequently reuse a default value such as a country name. The
value
must be a constant (such as
0
, cat, or, except if the column is of the type TIMESTAMP. Types are discussed in detail later in this section.
The NOT NULL
and
DEFAULT
features can be used together. If you specify NOT NULL
and add a
DEFAULT
value, the default is used when you don’t provide a value for the column.
Sometimes, this works fine:
mysql> INSERT INTO artist SET artist_name = "Duran Duran";
Query OK, 1 row affected (0.05 sec)
And sometimes it doesn’t:
mysql> INSERT INTO artist SET artist_name = "Bob The Builder";
ERROR 1062 (23000): Duplicate entry '0' for key Whether it works or not is dependent on the underlying constraints and conditions of the database in this example, artist_id has a default value of
0
, but it’s also the primary key. Having two rows with the same primary-key value isn’t permitted, and so the second attempt to insert a row with no values (and a resulting primary-key value of fails. We discuss primary keys in detail later in this section.
Column names have fewer restrictions than database and table names. What’s more,
they’re not dependent on the operating system the names are case-insensitive and portable across all platforms. All characters are allowed in column names, though if you want terminate them with whitespace or include periods (or other special characters such as the semicolon, you’ll need to enclose the name with a backtick symbol) on either side. We recommend that you consistently choose lowercase names for developer-driven choices (such as database, alias, and table names) and avoid characters that require you to remember to use backticks. We also recommend being descriptive with your choices name doesn’t mean much outside of the context of the artist table, but artist_name has universal meaning across the music database. We like using the underscore character to separate words, but that’s just a matter of style and

Download 4.24 Mb.

Share with your friends: