<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="creating-tables">
      </a>
      5.3.2 Creating a Table
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045325452832">
  </a>
  <a class="indexterm" name="idm46045325451344">
  </a>
  <p>
   Creating the database is the easy part, but at this point it is
        empty, as
   <a class="link" href="show-tables.html" title="15.7.7.39 SHOW TABLES Statement">
    <code class="literal">
     SHOW TABLES
    </code>
   </a>
   tells you:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa15811393"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">TABLES</span><span class="token punctuation">;</span>
<span class="token output">Empty set (0.00 sec)</span></code></pre>
  </div>
  <p>
   The harder part is deciding what the structure of your database
        should be: what tables you need and what columns should be in
        each of them.
  </p>
  <p>
   You want a table that contains a record for each of your pets.
        This can be called the
   <code class="literal">
    pet
   </code>
   table, and it
        should contain, as a bare minimum, each animal's name. Because
        the name by itself is not very interesting, the table should
        contain other information. For example, if more than one person
        in your family keeps pets, you might want to list each animal's
        owner. You might also want to record some basic descriptive
        information such as species and sex.
  </p>
  <p>
   How about age? That might be of interest, but it is not a good
        thing to store in a database. Age changes as time passes, which
        means you'd have to update your records often. Instead, it is
        better to store a fixed value such as date of birth. Then,
        whenever you need age, you can calculate it as the difference
        between the current date and the birth date. MySQL provides
        functions for doing date arithmetic, so this is not difficult.
        Storing birth date rather than age has other advantages, too:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      You can use the database for tasks such as generating
            reminders for upcoming pet birthdays. (If you think this
            type of query is somewhat silly, note that it is the same
            question you might ask in the context of a business database
            to identify clients to whom you need to send out birthday
            greetings in the current week or month, for that
            computer-assisted personal touch.)
     </p>
    </li>
    <li class="listitem">
     <p>
      You can calculate age in relation to dates other than the
            current date. For example, if you store death date in the
            database, you can easily calculate how old a pet was when it
            died.
     </p>
    </li>
   </ul>
  </div>
  <p>
   You can probably think of other types of information that would
        be useful in the
   <code class="literal">
    pet
   </code>
   table, but the ones
        identified so far are sufficient: name, owner, species, sex,
        birth, and death.
  </p>
  <p>
   Use a
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   statement to
        specify the layout of your table:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa83546169"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> pet <span class="token punctuation">(</span><span class="token keyword">name</span> <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">owner</span> <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       species <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span> sex <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">,</span> birth <span class="token datatype">DATE</span><span class="token punctuation">,</span> death <span class="token datatype">DATE</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
    <code class="literal">
     VARCHAR
    </code>
   </a>
   is a good choice for the
   <code class="literal">
    name
   </code>
   ,
   <code class="literal">
    owner
   </code>
   , and
   <code class="literal">
    species
   </code>
   columns because the column values
        vary in length. The lengths in those column definitions need not
        all be the same, and need not be
   <code class="literal">
    20
   </code>
   . You can
        normally pick any length from
   <code class="literal">
    1
   </code>
   to
   <code class="literal">
    65535
   </code>
   , whatever seems most reasonable to you.
        If you make a poor choice and it turns out later that you need a
        longer field, MySQL provides an
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER
        TABLE
    </code>
   </a>
   statement.
  </p>
  <p>
   Several types of values can be chosen to represent sex in animal
        records, such as
   <code class="literal">
    'm'
   </code>
   and
   <code class="literal">
    'f'
   </code>
   , or perhaps
   <code class="literal">
    'male'
   </code>
   and
   <code class="literal">
    'female'
   </code>
   . It is simplest to use the single
        characters
   <code class="literal">
    'm'
   </code>
   and
   <code class="literal">
    'f'
   </code>
   .
  </p>
  <p>
   The use of the
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATE
    </code>
   </a>
   data type for
        the
   <code class="literal">
    birth
   </code>
   and
   <code class="literal">
    death
   </code>
   columns is a fairly obvious choice.
  </p>
  <p>
   Once you have created a table,
   <a class="link" href="show-tables.html" title="15.7.7.39 SHOW TABLES Statement">
    <code class="literal">
     SHOW
        TABLES
    </code>
   </a>
   should produce some output:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa2650035"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">TABLES</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> Tables in menagerie <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> pet                 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   To verify that your table was created the way you expected, use
        a
   <a class="link" href="describe.html" title="15.8.1 DESCRIBE Statement">
    <code class="literal">
     DESCRIBE
    </code>
   </a>
   statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa61605911"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">DESCRIBE</span> pet<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> Field   <span class="token punctuation">|</span> Type        <span class="token punctuation">|</span> Null <span class="token punctuation">|</span> Key <span class="token punctuation">|</span> Default <span class="token punctuation">|</span> Extra <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> name    <span class="token punctuation">|</span> varchar(20) <span class="token punctuation">|</span> YES  <span class="token punctuation">|</span>     <span class="token punctuation">|</span> NULL    <span class="token punctuation">|</span>       <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> owner   <span class="token punctuation">|</span> varchar(20) <span class="token punctuation">|</span> YES  <span class="token punctuation">|</span>     <span class="token punctuation">|</span> NULL    <span class="token punctuation">|</span>       <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> species <span class="token punctuation">|</span> varchar(20) <span class="token punctuation">|</span> YES  <span class="token punctuation">|</span>     <span class="token punctuation">|</span> NULL    <span class="token punctuation">|</span>       <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> sex     <span class="token punctuation">|</span> char(1)     <span class="token punctuation">|</span> YES  <span class="token punctuation">|</span>     <span class="token punctuation">|</span> NULL    <span class="token punctuation">|</span>       <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> birth   <span class="token punctuation">|</span> date        <span class="token punctuation">|</span> YES  <span class="token punctuation">|</span>     <span class="token punctuation">|</span> NULL    <span class="token punctuation">|</span>       <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> death   <span class="token punctuation">|</span> date        <span class="token punctuation">|</span> YES  <span class="token punctuation">|</span>     <span class="token punctuation">|</span> NULL    <span class="token punctuation">|</span>       <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   You can use
   <a class="link" href="describe.html" title="15.8.1 DESCRIBE Statement">
    <code class="literal">
     DESCRIBE
    </code>
   </a>
   any time,
        for example, if you forget the names of the columns in your
        table or what types they have.
  </p>
  <p>
   For more information about MySQL data types, see
   <a class="xref" href="data-types.html" title="Chapter 13 Data Types">
    Chapter 13,
    <i>
     Data Types
    </i>
   </a>
   .
  </p>
 </div>
 <br/>
</div>
