📄 mysql-client.html
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0//EN" "http://www.w3.org/TR/REC-html40/strict.dtd"><html><head><title>Using SQL (Structured Query Language) with the Command-Line MySQL Client</title>
<link rel="stylesheet" href="mysql-client_files/home.css" type="text/css">
<link rel="stylesheet" href="mysql-client_files/webpub.css" type="text/css"></head>
<body bgcolor="#ffffff">
<a href="http://www.washington.edu/"><img id="toplogo" src="mysql-client_files/UWlogo150p.gif" alt="University of Washington" border="0"></a>
<div id="toolbar">
<span class="l1text"><a href="http://www.washington.edu/home/search.html">Search</a> |
<a href="http://www.washington.edu/home/directories.html">Directories</a> |
<a href="http://www.lib.washington.edu/research/">Reference
Tools</a></span></div>
<!--Section Banner Table -->
<div class="bannerwrapper">
<div id="topbanner"><img src="mysql-client_files/rib0.gif" alt="" height="16" width="6"></div>
</div>
<div id="crumbs"><span class="l1text"><a href="http://www.washington.edu/">UW Home</a> > <a href="http://www.washington.edu/uwin/">UWIN</a> > <a href="http://www.washington.edu/computing/">Computing and Networking</a> > <a href="http://www.washington.edu/computing/web/">Web</a> > <a href="http://www.washington.edu/computing/web/publishing/">Web Publishing</a> > <a href="http://www.washington.edu/computing/web/publishing/mysql.html">Using MySQL</a></span> </div><div class="forceclear"></div>
<table style="table-layout: fixed;" align="right" bgcolor="#ffffcc" border="1" cellpadding="5" cellspacing="0">
<tbody><tr>
<td width="120">
<p>This article applies to the following UW Web servers:</p>
<center>
<a href="http://faculty.washington.edu/">faculty</a><br>
<a href="http://staff.washington.edu/">staff</a><br>
<a href="http://students.washington.edu/">students</a><br>
<a href="http://courses.washington.edu/">courses</a><br>
<a href="http://depts.washington.edu/">depts</a><br>
</center>
</td>
</tr>
</tbody></table>
<h1>Using SQL (Structured Query Language) with the Command-Line MySQL Client</h1>
<h4>Summary</h4>
<p> Once you know the <a href="http://www.washington.edu/computing/web/publishing/mysql-admin.html">basics of administering your MySQL server</a>,
you can begin the important part: using MySQL to store your data. For
this you will use SQL (Structured Query Language). SQL is a special
language that allows programmers to issue instructions or 'queries' to
databases. This article will show you how to issue basic queries to
create, modify, or retrieve information in MySQL databases using the
command-line
MySQL client.</p>
<p><b>Note:</b> This article assumes that you have followed the instructions for
<a href="http://www.washington.edu/computing/web/publishing/mysql-install.html">installing and starting MySQL on the UW servers</a>.
</p><p><i>Included on this page:</i></p>
<ul>
<li><a href="#1">Invoking the MySQL Client</a>
</li><li><a href="#2">Creating and Opening a Database</a>
</li><li><a href="#3">Creating and Listing a Table; Listing the Fields in a Table</a>
</li><li><a href="#4">Putting Data into a Table</a>
</li><li><a href="#5">References</a>
</li></ul>
<h4><a name="1">Invoking the MySQL Client</a></h4>
<p><a name="1">You can issue SQL queries to your database using the command-line MySQL client, which you used during the installation of MySQL
when you set user/host grant permissions in the "mysql" database. To invoke the client again, follow these intructions:</a></p>
<ol>
<li><a name="1"> </a><p><a name="1">Log in to your Homer or Dante account with </a><a href="http://www.washington.edu/computing/software/uwick/teraterm">Tera Term</a>
or another terminal emulator.</p>
</li><li> <p>Press the O key for Other.</p>
</li><li> Press the W key to drop into the <a href="http://www.washington.edu/computing/web/publishing/environs.html">Web development environment</a>, and <tt>cd</tt> into your <tt>mysql</tt> directory:
<p><span class="code">cd mysql</span></p>
</li><li> Start the MySQL client and log in as user root:
<p><span class="code">./bin/mysql -u root -p</span></p>
</li></ol>
<p>You will be prompted for the root password, and then you should see something like the following:</p>
<p></p><table class="code">
<tbody><tr><td>
Welcome to the MySQL monitor. Commands end with ; or \g.<br>
Your MySQL connection id is 51 to server version: 3.22.32<br><br>
Type 'help' for help.<br><br>
mysql>
</td></tr>
</tbody></table>
<p>You are now ready to issue SQL commands.</p>
<h4><a name="2">Creating and Using a Database</a></h4>
<p><a name="2">The first query we'll issue will create a database. After entering the
following SQL command, you should see an acknowledgement from the MySQL client similar to the reply in italics printed below:</a></p>
<p></p><table class="code">
<tbody><tr><td>
mysql> CREATE DATABASE foo;<br>
<i>Query OK, 1 row affected (0.00 sec)</i>
</td></tr>
</tbody></table>
<p><a name="2"><b>Tip:</b> Names of databases, tables, and fields in
MySQL are case sensitive. They can include the underscore character,
but not spaces or hyphens.</a></p>
<p><a name="2">We can verify that the database has been created by asking:</a></p>
<table class="code">
<tbody><tr><td><pre>mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| foo |
| mysql |
| test |
+----------+
<i>3 rows in set (0.01 sec)</i>
</pre></td></tr>
</tbody></table>
<p><a name="2">Indeed, the new database is there, along with the mysql
database that stores users and hosts, and an empty test database that
was created during installation.</a></p>
<p><a name="2"> To work with your new database, you need to tell MySQL you want to use it:</a></p>
<p></p><table class="code">
<tbody><tr><td>
mysql> USE foo;<br>
<i>Database changed</i>
</td></tr>
</tbody></table>
<h4><a name="3">Creating and Listing a Table; Listing the Fields in a Table</a></h4>
<p> You can now ask to see the tables in your database:</p>
<p></p><table class="code">
<tbody><tr><td>
mysql> SHOW TABLES;<br>
<i>Empty set (0.00 sec)
</i></td></tr>
</tbody></table>
<p>As expected, there aren't any, because you haven't created any yet. You can do so now:</p>
<p></p><table class="code">
<tbody><tr><td>
mysql> CREATE TABLE Friends (name char(20), age integer);<br>
<i>Query OK, 0 rows affected (0.05 sec)</i>
</td></tr>
</tbody></table>
<p>This creates a table with two fields: a "name" field, which is an
array of twenty characters, and an "age" field, which is an integer.
Now when you ask to see your tables, you should see something like:</p>
<p></p><table class="code">
<tbody><tr><td>
<pre>mysql> SHOW TABLES;
+--------------------+
| Tables in foo |
+--------------------+
| Friends |
+--------------------+
<i>1 row in set (0.01 sec)</i>
</pre>
</td></tr>
</tbody></table>
<p>You can also view the columns in your new table:</p>
<p></p><table class="code">
<tbody><tr><td>
<pre>mysql> SHOW COLUMNS FROM Friends;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
<i>2 rows in set (0.07 sec)</i>
</pre>
</td></tr>
</tbody></table>
<h4><a name="4">Putting Data into a Table</a></h4>
<p>To insert a record into your table, type the following:</p>
<p></p><table class="code">
<tbody><tr><td>
mysql> INSERT INTO Friends (name, age) values ('Tom', 20);<br>
<i>Query OK, 1 row affected (0.03 sec)</i>
</td></tr>
</tbody></table>
<p>Now you can view your record:</p>
<table class="code">
<tbody><tr><td><pre>mysql> SELECT * FROM Friends;
+------+------+
| name | age |
+------+------+
| Tom | 20 |
+------+------+
<i>1 row in set (0.01 sec)</i>
</pre></td></tr>
</tbody></table>
<h4><a name="5">References</a></h4>
<p> This article covers only the most basic of SQL commands. MySQL's <a href="http://dev.mysql.com/doc/mysql/en/Tutorial.html">Tutorial Introduction</a>
provides a good starting point for using the command-line MySQL client.
The following specific links may be of most use for getting started:</p>
<p><a href="http://dev.mysql.com/doc/mysql/en/Entering_queries.html">Entering Queries</a><br>
<a href="http://dev.mysql.com/doc/mysql/en/Database_use.html">Creating and Using a Database</a><br>
<a href="http://dev.mysql.com/doc/mysql/en/Getting_information.html">Getting Information about Databases and Tables</a><br>
<a href="http://dev.mysql.com/doc/mysql/en/Examples.html">Examples of Common Queries</a>.</p>
<p> <b>Tip:</b> The command-line MySQL client is just one of many tools
that can be used to manipulate MySQL databases. Visit the following
links to learn more about the alternatives:</p>
<p>
The convenient web-based PHP utility <a href="http://www.washington.edu/computing/web/publishing/phpmyadmin.html">phpMyAdmin</a> can be used to create databases and manipulate data without the need for SQL.<br>
The popular database software <a href="http://www.washington.edu/computing/web/publishing/mysql-access.html">Microsoft Acess</a> can be used to enter data into tables in an intuitive spreadsheet-like environment.<br>
The scripting languages <a href="http://www.washington.edu/computing/web/publishing/mysql-script.html">PHP and Perl</a> can be used to write dynamic Web pages that modify MySQL databases.<br>
</p>
<div id="footer"><div id="footerseal">
<a href="http://www.washington.edu/"><img src="mysql-client_files/footersealW.gif" alt="UW Seal"></a>
</div>
<div id="addressright"><address>
A service provided by<br>
<a href="http://depts.washington.edu/cac/"><img src="mysql-client_files/CnC_full_UW_Purple-1.gif" alt="Computing & Communications" border="0" height="14" width="202"></a>
</address>
</div>
<div id="address"><address>
<a href="http://depts.washington.edu/cac/">Computing
& Communications</a><br>
help@cac.washington.edu<br>
Modified: May 16, 2005
</address>
</div>
</div>
<!--Created by chtml on Sep 15, 2006 11:45am--></body></html>⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -