LDP
/
old-www
1
1
Fork 0
Code Issues Pull Requests Releases Wiki Activity
old-www/LDP/LG/issue67/tag/16.html

514 lines
18 KiB
HTML
Raw Permalink Blame History

<!--startcut ======================================================= -->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<html>
<head>
<META NAME="generator" CONTENT="lgazmail v1.3E.u">
<TITLE>The Answer Gang 67: MySQL tips and tricks</TITLE>
</HEAD><BODY BGCOLOR="#FFFFFF" TEXT="#000000"
LINK="#3366FF" VLINK="#A000A0">
<!-- ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: -->
<P> <hr>
<CENTER>
<!-- *** BEGIN navbar *** -->
<!-- *** END navbar *** -->
</CENTER>
</p>
<P> <hr> <P>
<!-- begin tagnav ::::::::::::::::::::::::::::::::::::::::::::::::::-->
<p align="center">
<table width="100%" border="0"><tr>
<td align="right" valign="center"
><IMG ALT="" SRC="../../gx/navbar/left.jpg"
WIDTH="14" HEIGHT="45" BORDER="0" ALIGN="middle" border="0"
><A HREF="..//"
><IMG SRC="../../gx/navbar/toc.jpg" align="middle"
ALT="[ Table Of Contents ]" border="0"></A
><A HREF="../lg_answer67.html"
><IMG SRC="../../gx/dennis/answertoc.jpg" align="middle"
ALT="[ Answer Guy Current Index ]" border="0"></A></td>
<td align="center" valign="center"><A HREF="../lg_answer67.html#greeting"><img align="middle"
src="../../gx/dennis/smily.gif" alt="greetings" border="0"></A> &nbsp;
<A HREF="bios.html">bios</A> &nbsp;
<A HREF="1.html">1</A> &nbsp;
<A HREF="2.html">2</A> &nbsp;
<A HREF="3.html">3</A> &nbsp;
<A HREF="4.html">4</A> &nbsp;
<A HREF="5.html">5</A> &nbsp;
<A HREF="6.html">6</A> &nbsp;
<A HREF="7.html">7</A> &nbsp;
<A HREF="8.html">8</A> &nbsp;
<A HREF="9.html">9</A> &nbsp;
<A HREF="10.html">10</A> &nbsp;
<A HREF="12.html">12</A> &nbsp;
<A HREF="13.html">13</A> &nbsp;
<A HREF="14.html">14</A> &nbsp;
<A HREF="15.html">15</A> &nbsp;
<A HREF="16.html">16</A> &nbsp;
<A HREF="17.html">17</A> &nbsp;
<A HREF="18.html">18</A> &nbsp;
<A HREF="19.html">19</A> &nbsp;
<A HREF="20.html">20</A>
</td>
<td align="left" valign="center"><A HREF="../../tag/kb.html"
><IMG SRC="../../gx/dennis/answerpast.jpg" align="middle"
ALT="[ Index of Past Answers ]" border="0"></A
><IMG ALT="" SRC="../../gx/navbar/right.jpg" align="middle"
WIDTH="14" HEIGHT="45" BORDER="0"></td></tr></table>
</p>
<!-- end tagnav ::::::::::::::::::::::::::::::::::::::::::::::::::::-->
<!-- ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: -->
<center>
<H1><A NAME="answer">
<img src="../../gx/dennis/qbubble.gif" alt="(?)"
border="0" align="middle">
<font color="#B03060">The Answer Gang</font>
<img src="../../gx/dennis/bbubble.gif" alt="(!)"
border="0" align="middle">
</A></H1>
<BR>
<H4>By Jim Dennis, Ben Okopnik, Dan Wilder, Breen, Chris, and the Gang,
the Editors of Linux Gazette...
and You!
<br>Send questions (or interesting answers) to
<a href="mailto:linux-questions-only@ssc.com">linux-questions-only@ssc.com</a>
</H4>
<p><em><font color="#990000">There is no guarantee that your questions
here will <b>ever</b> be answered. You can be published anonymously
- just let us know!
</font></em></p>
</center>
<p><hr><p>
<!-- endcut ======================================================= -->
<!-- begin 16 -->
<H3 align="left"><img src="../../gx/dennis/qbubble.gif"
height="50" width="60" alt="(?) " border="0"
>MySQL tips and tricks</H3>
<p><strong>From Travis Gerspacher
</strong></p>
<p align="right"><strong>Answered By Mike Orr, Karl-Heinz Herrmann
<br></strong></p>
<!-- sig -->
<!-- ::
MySQL tips and tricks
~~~~~~~~~~~~~~~~~~~~~
:: -->
<P><em>Yes, Gentle Readers, this is also in the Wanted area this month,
because expanding it into a more complete article would be very
tasty. Meanwhile we hope it's useful as it stands, and there's
some extra URLs at the end. -- Heather</em>
</P>
<P><STRONG><IMG SRC="../../gx/dennis/qbub.gif" ALT="(?)"
HEIGHT="28" WIDTH="50" BORDER="0"
>
I would love to see an article about making sense of MySQL.Perhaps
some basic commands, and how to do something useful with it.
</STRONG></P>
<BLOCKQUOTE><IMG SRC="../../gx/dennis/bbub.gif" ALT="(!)"
HEIGHT="28" WIDTH="50" BORDER="0"
> [Mike]
Here are some basic commands. As far as "something useful", what would
you consider useful?
</blockquote>
<P><STRONG><IMG SRC="../../gx/dennis/qbub.gif" ALT="(?)"
HEIGHT="28" WIDTH="50" BORDER="0"
>
I have found a
lot of articles either lack basic usage and administration or it it fails to
show how to put it all together and have somehing useful come out of it.
</STRONG></P>
<BLOCKQUOTE><IMG SRC="../../gx/dennis/bbub.gif" ALT="(!)"
HEIGHT="28" WIDTH="50" BORDER="0"
> [Mike]
The 'mysql' command is your friend. You can practice entering commands
with it, run ad-hoc queries, build and modify your tables, and test
your ideas before coding them into a program. Let's look at one of the
sample tables that come with MySQL in the 'test' database. First
we'll see the names of the tables, then look at the structure of the
TEAM table, then count how many records it contains, then display a few
fields.
</blockquote>
<blockquote><pre>$ mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1325 to server version: 3.23.35-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer
mysql&gt; show tables;
+------------------+
| Tables_in_test |
+------------------+
| COLORS |
| TEAM |
+------------------+
2 rows in set (0.00 sec)
mysql&gt; describe TEAM;
+------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+----------------+
| MEMBER_ID | int(11) | | PRI | NULL | auto_increment |
| FIRST_NAME | varchar(32) | | | | |
| LAST_NAME | varchar(32) | | | | |
| REMARK | varchar(64) | | | | |
| FAV_COLOR | varchar(32) | | MUL | | |
| LAST_DATE | timestamp(14) | YES | MUL | NULL | |
| OPEN_DATE | timestamp(14) | YES | MUL | NULL | |
+------------+---------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
mysql&gt; select count(*) from TEAM;
+----------+
| count(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
mysql&gt; select MEMBER_ID, REMARK, LAST_DATE from TEAM;
+-----------+-----------------+----------------+
| MEMBER_ID | REMARK | LAST_DATE |
+-----------+-----------------+----------------+
| 1 | Techno Needy | 20000508105403 |
| 2 | Meticulous Nick | 20000508105403 |
| 3 | The Data Diva | 20000508105403 |
| 4 | The Logic Bunny | 20000508105403 |
+-----------+-----------------+----------------+
4 rows in set (0.01 sec)
</blockquote></pre>
<blockquote>
Say we've forgotten the full name of that Diva person:
</blockquote>
<pre><blockquote>
mysql&gt; select MEMBER_ID, FIRST_NAME, LAST_NAME, REMARK
-&gt; from TEAM
-&gt; where REMARK LIKE "%Diva%";
+-----------+------------+-----------+---------------+
| MEMBER_ID | FIRST_NAME | LAST_NAME | REMARK |
+-----------+------------+-----------+---------------+
| 3 | Brittney | McChristy | The Data Diva |
+-----------+------------+-----------+---------------+
1 row in set (0.01 sec)
</blockquote></Pre>
<blockquote>
What if Brittney McChristy changes her last name to Spears?
</blockquote>
<pre><blockquote>
mysql&gt; update TEAM set LAST_NAME='Spears' WHERE MEMBER_ID=3;
Query OK, 1 row affected (0.01 sec)
mysql&gt; select MEMBER_ID, FIRST_NAME, LAST_NAME, LAST_DATE from TEAM
-&gt; where MEMBER_ID=3;
+-----------+------------+-----------+----------------+
| MEMBER_ID | FIRST_NAME | LAST_NAME | LAST_DATE |
+-----------+------------+-----------+----------------+
| 3 | Brittney | Spears | 20010515134528 |
+-----------+------------+-----------+----------------+
1 row in set (0.00 sec)
</blockquote></Pre>
<blockquote>
Since LAST_DATE is the first TIMESTAMP field in the table, it's
automatically reset to the current time whenever you make a change.
</blockquote>
<blockquote>
Now let's look at all the players whose favorite color is blue, listing
the most recently-changed one first.
</blockquote>
<pre><blockquote>
mysql&gt; select MEMBER_ID, FIRST_NAME, LAST_NAME, FAV_COLOR, LAST_DATE from TEAM
-&gt; where FAV_COLOR = 'blue'
-&gt; order by LAST_DATE desc;
+-----------+------------+-----------+-----------+----------------+
| MEMBER_ID | FIRST_NAME | LAST_NAME | FAV_COLOR | LAST_DATE |
+-----------+------------+-----------+-----------+----------------+
| 3 | Brittney | Spears | blue | 20010515134528 |
| 2 | Nick | Borders | blue | 20000508105403 |
+-----------+------------+-----------+-----------+----------------+
2 rows in set (0.00 sec)
</blockquote></Pre>
<blockquote>
Now let's create a table TEAM2 with a similar structure as TEAM.
</blockquote>
<pre><blockquote>
mysql&gt; create table TEAM2 (
-&gt; MEMBER_ID int(11) not null auto_increment primary key,
-&gt; FIRST_NAME varchar(32) not null,
-&gt; LAST_NAME varchar(32) not null,
-&gt; REMARK varchar(64) not null,
-&gt; FAV_COLOR varchar(32) not null,
-&gt; LAST_DATE timestamp,
-&gt; OPEN_DATE timestamp);
Query OK, 0 rows affected (0.01 sec)
mysql&gt; describe TEAM2;
+------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+----------------+
| MEMBER_ID | int(11) | | PRI | NULL | auto_increment |
| FIRST_NAME | varchar(32) | | | | |
| LAST_NAME | varchar(32) | | | | |
| REMARK | varchar(64) | | | | |
| FAV_COLOR | varchar(32) | | | | |
| LAST_DATE | timestamp(14) | YES | | NULL | |
| OPEN_DATE | timestamp(14) | YES | | NULL | |
+------------+---------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
</blockquote></Pre>
<blockquote>
Compare this with the TEAM decription above. They are identical (except
for the multiple index we didn't create because this is a "simple"
example).
</blockquote>
<blockquote>
Now, say you want to do a query in Python:
</blockquote>
<pre><blockquote>
$ python
Python 1.6 (#1, Sep 5 2000, 17:46:48) [GCC 2.7.2.3] on linux2
Copyright (c) 1995-2000 Corporation for National Research Initiatives.
All Rights Reserved.
Copyright (c) 1991-1995 Stichting Mathematisch Centrum, Amsterdam.
All Rights Reserved.
&gt;&gt;&gt; import MySQLdb
&gt;&gt;&gt; conn = MySQLdb.connect(host='localhost', user='me', passwd='mypw', db='test')
&gt;&gt;&gt; c = conn.cursor()
&gt;&gt;&gt; c.execute("select MEMBER_ID, FIRST_NAME, LAST_NAME from TEAM")
4L
&gt;&gt;&gt; records = c.fetchall()
&gt;&gt;&gt; import pprint
&gt;&gt;&gt; pprint.pprint(records)
((1L, 'Brad', 'Stec'),
(2L, 'Nick', 'Borders'),
(3L, 'Brittney', 'Spears'),
(4L, 'Fuzzy', 'Logic'))
</blockquote></Pre>
<blockquote>
Another approach is to have Python or a shell script write the SQL
commands to a file and then run 'mysql' with its standard input coming
from the file. Or in a shell script, pipe the command into mysql:
</blockquote>
<pre><blockquote>
$ echo "select REMARK from TEAM" | mysql -t test
+-----------------+
| REMARK |
+-----------------+
| Techno Needy |
| Meticulous Nick |
| The Data Diva |
| The Logic Bunny |
+-----------------+
</blockquote></Pre>
<blockquote>
(The -t option tells MySQL to draw the table decorations even though
it's running in batch mode. Add your MySQL username and password if requred.)
</blockquote>
<blockquote>
'mysqldump' prints a set of SQL commands which can recreate a table.
This provides a simple way to backup and restore:
</blockquote>
<pre><blockquote>
$ mysqldump --opt -u Username -pPassword test TEAM &gt;/backups/team.sql
$ mysql -u Username -pPassword test &lt;/backups/team.sql
</blockquote></Pre>
<blockquote>
This can be used for system backups, or for ad-hoc backups while you're
designing an application or doing complex edits. (And it saves your butt
if you accidentally forget the WHERE clause in an UPDATE statement and
end up changing all records instead of just one!)
</blockquote>
<blockquote>
You can also do system backups by rsyncing or tarring the /var/lib/mysql/
directory. However, you run the risk that a table may be in the middle
of an update. MySQL does have a command "LOCK TABLES the_table READ",
but interspersing it with backup commands in Python/Perl/whatever is less
convenient than mysqldump, and trying to do it in a shell script without
running mysql as a coprocess is pretty difficult.
</blockquote>
<blockquote>
The only other maintenance operation is creating users and assigning
access privileges. Study "GRANT and REVOKE syntax" (section 7.25) in
the MySQL reference manual. I always have to reread this whenever I
add a database. Generally you want a command like:
</blockquote>
<pre><blockquote>
mysql&gt; grant SELECT, INSERT, DELETE, UPDATE on test.TEAM to somebody
-&gt; identified by 'her_password';
Query OK, 0 rows affected (0.03 sec)
</blockquote></Pre>
<blockquote>
This will allow "somebody" to view and modify records but not to change
the table structure. (I always alter tables as the MySQL root user.)
To allow viewing and modifying of all current and future tables in
datbase 'test', use "on test.*". To allow certain users access without
a password, omit the "identified by 'her_password'" portion. To limit
access according to the client's hostname, use 'to somebody@"%.mysite.com"'.
</blockquote>
<blockquote>
Remember that MySQL usernames have no relationship to login usernames.
</blockquote>
<blockquote>
To join multiple tables (MySQL is a "relational" DBMS after all), see
"SELECT syntax" (section 7.11). Actually, all of chapter 7 is good to
have around for reference. The MySQL manual is at
http://www.mysql.com/doc/
</blockquote>
<!-- sig -->
<BLOCKQUOTE><IMG SRC="../../gx/dennis/bbub.gif" ALT="(!)"
HEIGHT="28" WIDTH="50" BORDER="0"
> [K.H.]
I think Linux Magazin did just that comparison in it's last issue.
Unfortunately
it's a German magazine. They also had an introduction to Data Bases and
SQL
--
all in the April number.
</BLOCKQUOTE>
<blockquote><dl><dt>They put their articles of past issues online:
<dd><a href="http://www.linux-magazin.de"
>http://www.linux-magazin.de</a>
<br><a href="http://www.linux-magazin.de/ausgabe/2001/04/index.html"
>http://www.linux-magazin.de/ausgabe/2001/04/index.html</a>
<br><a href="http://www.linux-magazin.de/ausgabe/2001/04/PostgresMySQL/postgres-mysql.html"
>http://www.linux-magazin.de/ausgabe/2001/04/PostgresMySQL/postgres-mysql.html</a>
</dl></blockquote>
<BLOCKQUOTE>
Maybe this is at least interesting for German speaking readers....
</BLOCKQUOTE>
<BLOCKQUOTE><IMG SRC="../../gx/dennis/bbub.gif" ALT="(!)"
HEIGHT="28" WIDTH="50" BORDER="0"
> [Mike]
I got a really funny translation of this.
<A HREF="http://www.freetranslation.com/">www.freetranslation.com</A> says:
</BLOCKQUOTE>
<BLOCKQUOTE>
Postgres' Foreign keys come out as "strange keys". In a sentance about
transactions it says, "With the MVCC-procedure, readers do not wait for
clerk".
</BLOCKQUOTE>
<BLOCKQUOTE>
The funniest quote is: "Like in almost all professional databank systems
Trigger and Stored Procedures are confessed. Implementiert is not
presently on the other hand the possibility, databank to replizieren."
</BLOCKQUOTE>
<BLOCKQUOTE>
Or maybe this is better, "To the Performance-increase, data models
denormalisiert become frequent."
</BLOCKQUOTE>
<BLOCKQUOTE>
MySQL's origin "lies loudly in the 1979 databank-Tool UNIREG".
Oh, and "the official pronunciation is Mei-it-kju-ell. People who say
'Mei Sequel' are pursued however not criminal."
</BLOCKQUOTE>
<!-- end 16 -->
<!--startcut ======================================================= -->
<P> <hr> </p>
<!-- *** BEGIN copyright *** -->
<H5 align="center">This page edited and maintained by the Editors
of <I>Linux Gazette</I>
<a href="http://www.linuxgazette.com/copying.html"
>Copyright &copy;</a> 2001
<BR>Published in issue 67 of <I>Linux Gazette</I> June 2001</H5>
<H6 ALIGN="center">HTML script maintained by
<A HREF="mailto:star@starshine.org">Heather Stern</a> of
Starshine Technical Services,
<A HREF="http://www.starshine.org/">http://www.starshine.org/</A>
</H6>
<!-- *** END copyright *** -->
<P> <hr>
<!-- begin tagnav ::::::::::::::::::::::::::::::::::::::::::::::::::-->
<p align="center">
<table width="100%" border="0"><tr>
<td align="right" valign="center"
><IMG ALT="" SRC="../../gx/navbar/left.jpg"
WIDTH="14" HEIGHT="45" BORDER="0" ALIGN="middle" border="0"
><A HREF="..//"
><IMG SRC="../../gx/navbar/toc.jpg" align="middle"
ALT="[ Table Of Contents ]" border="0"></A
><A HREF="../lg_answer67.html"
><IMG SRC="../../gx/dennis/answertoc.jpg" align="middle"
ALT="[ Answer Guy Current Index ]" border="0"></A></td>
<td align="center" valign="center"><A HREF="../lg_answer67.html#greeting"><img align="middle"
src="../../gx/dennis/smily.gif" alt="greetings" border="0"></A> &nbsp;
<A HREF="bios.html">bios</A> &nbsp;
<A HREF="1.html">1</A> &nbsp;
<A HREF="2.html">2</A> &nbsp;
<A HREF="3.html">3</A> &nbsp;
<A HREF="4.html">4</A> &nbsp;
<A HREF="5.html">5</A> &nbsp;
<A HREF="6.html">6</A> &nbsp;
<A HREF="7.html">7</A> &nbsp;
<A HREF="8.html">8</A> &nbsp;
<A HREF="9.html">9</A> &nbsp;
<A HREF="10.html">10</A> &nbsp;
<A HREF="12.html">12</A> &nbsp;
<A HREF="13.html">13</A> &nbsp;
<A HREF="14.html">14</A> &nbsp;
<A HREF="15.html">15</A> &nbsp;
<A HREF="16.html">16</A> &nbsp;
<A HREF="17.html">17</A> &nbsp;
<A HREF="18.html">18</A> &nbsp;
<A HREF="19.html">19</A> &nbsp;
<A HREF="20.html">20</A>
</td>
<td align="left" valign="center"><A HREF="../../tag/kb.html"
><IMG SRC="../../gx/dennis/answerpast.jpg" align="middle"
ALT="[ Index of Past Answers ]" border="0"></A
><IMG ALT="" SRC="../../gx/navbar/right.jpg" align="middle"
WIDTH="14" HEIGHT="45" BORDER="0"></td></tr></table>
</p>
<!-- end tagnav ::::::::::::::::::::::::::::::::::::::::::::::::::::-->
<P> <hr>
<CENTER>
<!-- *** BEGIN navbar *** -->
<!-- *** END navbar *** -->
</CENTER>
</p>
<!-- ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: -->
</BODY></HTML>
<!--endcut ========================================================= -->
Reference in New Issue View Git Blame Copy Permalink