<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="database-use">
      </a>
      5.3 Creating and Using a Database
     </h2>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="creating-database.html">
       5.3.1 Creating and Selecting a Database
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="creating-tables.html">
       5.3.2 Creating a Table
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="loading-tables.html">
       5.3.3 Loading Data into a Table
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="retrieving-data.html">
       5.3.4 Retrieving Information from a Table
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <a class="indexterm" name="idm46045325519600">
  </a>
  <a class="indexterm" name="idm46045325518144">
  </a>
  <a class="indexterm" name="idm46045325516656">
  </a>
  <p>
   Once you know how to enter SQL statements, you are ready to access
      a database.
  </p>
  <p>
   Suppose that you have several pets in your home (your menagerie)
      and you would like to keep track of various types of information
      about them. You can do so by creating tables to hold your data and
      loading them with the desired information. Then you can answer
      different sorts of questions about your animals by retrieving data
      from the tables. This section shows you how to perform the
      following operations:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Create a database
     </p>
    </li>
    <li class="listitem">
     <p>
      Create a table
     </p>
    </li>
    <li class="listitem">
     <p>
      Load data into the table
     </p>
    </li>
    <li class="listitem">
     <p>
      Retrieve data from the table in various ways
     </p>
    </li>
    <li class="listitem">
     <p>
      Use multiple tables
     </p>
    </li>
   </ul>
  </div>
  <p>
   The menagerie database is simple (deliberately), but it is not
      difficult to think of real-world situations in which a similar
      type of database might be used. For example, a database like this
      could be used by a farmer to keep track of livestock, or by a
      veterinarian to keep track of patient records. A menagerie
      distribution containing some of the queries and sample data used
      in the following sections can be obtained from the MySQL website.
      It is available in both compressed
   <span class="command">
    <strong>
     tar
    </strong>
   </span>
   file and
      Zip formats at
   <a class="ulink" href="/doc/" target="_top">
    https://dev.mysql.com/doc/
   </a>
   .
  </p>
  <p>
   Use the
   <a class="link" href="show.html" title="15.7.7 SHOW Statements">
    <code class="literal">
     SHOW
    </code>
   </a>
   statement to find out
      what databases currently exist on the server:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa8976653"><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">DATABASES</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>
<span class="token output"><span class="token punctuation">|</span> Database <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>
<span class="token output"><span class="token punctuation">|</span> mysql    <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> test     <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> tmp      <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></code></pre>
  </div>
  <p>
   The
   <code class="literal">
    mysql
   </code>
   database describes user access
      privileges. The
   <code class="literal">
    test
   </code>
   database often is
      available as a workspace for users to try things out.
  </p>
  <p>
   The list of databases displayed by the statement may be different
      on your machine;
   <a class="link" href="show-databases.html" title="15.7.7.15 SHOW DATABASES Statement">
    <code class="literal">
     SHOW DATABASES
    </code>
   </a>
   does not show databases that you have no privileges for if you do
      not have the
   <a class="link" href="show-databases.html" title="15.7.7.15 SHOW DATABASES Statement">
    <code class="literal">
     SHOW DATABASES
    </code>
   </a>
   privilege. See
   <a class="xref" href="show-databases.html" title="15.7.7.15 SHOW DATABASES Statement">
    Section 15.7.7.15, “SHOW DATABASES Statement”
   </a>
   .
  </p>
  <p>
   If the
   <code class="literal">
    test
   </code>
   database exists, try to access it:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa22670523"><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">USE</span> test
<span class="token keyword">Database</span> <span class="token keyword">changed</span></code></pre>
  </div>
  <p>
   <a class="link" href="use.html" title="15.8.4 USE Statement">
    <code class="literal">
     USE
    </code>
   </a>
   , like
   <code class="literal">
    QUIT
   </code>
   ,
      does not require a semicolon. (You can terminate such statements
      with a semicolon if you like; it does no harm.) The
   <a class="link" href="use.html" title="15.8.4 USE Statement">
    <code class="literal">
     USE
    </code>
   </a>
   statement is special in another
      way, too: it must be given on a single line.
  </p>
  <p>
   You can use the
   <code class="literal">
    test
   </code>
   database (if you have
      access to it) for the examples that follow, but anything you
      create in that database can be removed by anyone else with access
      to it. For this reason, you should probably ask your MySQL
      administrator for permission to use a database of your own.
      Suppose that you want to call yours
   <code class="literal">
    menagerie
   </code>
   .
      The administrator needs to execute a statement like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa38846998"><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">GRANT</span> <span class="token keyword">ALL</span> <span class="token keyword">ON</span> menagerie<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> <span class="token string">'your_mysql_name'</span>@<span class="token string">'your_client_host'</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   where
   <code class="literal">
    your_mysql_name
   </code>
   is the MySQL user name
      assigned to you and
   <code class="literal">
    your_client_host
   </code>
   is the
      host from which you connect to the server.
  </p>
 </div>
 <br/>
</div>
