bios   1   2   3   4   5   6   7   8   9   10   12   13   14   15   16   17   18   19   20

MySQL tips and tricks

From Travis Gerspacher

Answered By Mike Orr, Karl-Heinz Herrmann

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

I would love to see an article about making sense of MySQL.Perhaps some basic commands, and how to do something useful with it.

[Mike] Here are some basic commands. As far as "something useful", what would you consider useful?

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.

[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.

$ 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> show tables;
+------------------+
| Tables_in_test   |
+------------------+
| COLORS           |
| TEAM             |
+------------------+
2 rows in set (0.00 sec)

mysql> 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> select count(*) from TEAM;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

mysql> 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)

Say we've forgotten the full name of that Diva person:

mysql> select MEMBER_ID, FIRST_NAME, LAST_NAME, REMARK

-> from TEAM
-> where REMARK LIKE "%Diva%";

+-----------+------------+-----------+---------------+
| MEMBER_ID | FIRST_NAME | LAST_NAME | REMARK        |
+-----------+------------+-----------+---------------+
|         3 | Brittney   | McChristy | The Data Diva |
+-----------+------------+-----------+---------------+
1 row in set (0.01 sec)

What if Brittney McChristy changes her last name to Spears?

mysql> update TEAM set LAST_NAME='Spears' WHERE MEMBER_ID=3;
Query OK, 1 row affected (0.01 sec)

mysql> select MEMBER_ID, FIRST_NAME, LAST_NAME, LAST_DATE from TEAM
-> where MEMBER_ID=3;

+-----------+------------+-----------+----------------+
| MEMBER_ID | FIRST_NAME | LAST_NAME | LAST_DATE      |
+-----------+------------+-----------+----------------+
|         3 | Brittney   | Spears    | 20010515134528 |
+-----------+------------+-----------+----------------+
1 row in set (0.00 sec)

Since LAST_DATE is the first TIMESTAMP field in the table, it's automatically reset to the current time whenever you make a change.

Now let's look at all the players whose favorite color is blue, listing the most recently-changed one first.

mysql> select MEMBER_ID, FIRST_NAME, LAST_NAME, FAV_COLOR, LAST_DATE from TEAM

-> where FAV_COLOR = 'blue'
-> 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)

Now let's create a table TEAM2 with a similar structure as TEAM.

mysql> create table TEAM2 (

-> MEMBER_ID int(11) not null auto_increment primary key,
-> FIRST_NAME varchar(32) not null,
-> LAST_NAME varchar(32) not null,
-> REMARK varchar(64) not null,
-> FAV_COLOR varchar(32) not null,
-> LAST_DATE timestamp,
-> OPEN_DATE timestamp);
Query OK, 0 rows affected (0.01 sec)

mysql> 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)

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).

Now, say you want to do a query in Python:

$ 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.
>>> import MySQLdb
>>> conn = MySQLdb.connect(host='localhost', user='me', passwd='mypw', db='test')
>>> c = conn.cursor()
>>> c.execute("select MEMBER_ID, FIRST_NAME, LAST_NAME from TEAM")
4L
>>> records = c.fetchall()
>>> import pprint
>>> pprint.pprint(records)
((1L, 'Brad', 'Stec'),
 (2L, 'Nick', 'Borders'),
  (3L, 'Brittney', 'Spears'),
   (4L, 'Fuzzy', 'Logic'))

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:

$ echo "select REMARK from TEAM" | mysql -t test
+-----------------+
| REMARK          |
+-----------------+
| Techno Needy    |
| Meticulous Nick |
| The Data Diva   |
| The Logic Bunny |
+-----------------+

(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.)

'mysqldump' prints a set of SQL commands which can recreate a table. This provides a simple way to backup and restore:

$ mysqldump --opt -u Username -pPassword test TEAM >/backups/team.sql
$ mysql -u Username -pPassword test </backups/team.sql

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!)

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.

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:

mysql> grant SELECT, INSERT, DELETE, UPDATE on test.TEAM to somebody

-> identified by 'her_password';
Query OK, 0 rows affected (0.03 sec)

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"'.

Remember that MySQL usernames have no relationship to login usernames.

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/

[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.

They put their articles of past issues online:

http://www.linux-magazin.de
http://www.linux-magazin.de/ausgabe/2001/04/index.html
http://www.linux-magazin.de/ausgabe/2001/04/PostgresMySQL/postgres-mysql.html

Maybe this is at least interesting for German speaking readers....

[Mike] I got a really funny translation of this. www.freetranslation.com says:

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".

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."

Or maybe this is better, "To the Performance-increase, data models denormalisiert become frequent."

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."

This page edited and maintained by the Editors of Linux Gazette Copyright © 2001
Published in issue 67 of Linux Gazette June 2001

HTML script maintained by Heather Stern of Starshine Technical Services, http://www.starshine.org/

bios   1   2   3   4   5   6   7   8   9   10   12   13   14   15   16   17   18   19   20