LevSelector.com New York
home > MySQL

MySQL
 
On this page: Other pages:
* links
* books
* hints
* commands examples
- sample_mysql.cgi


 
links home - top of the page -

* www.mysql.com - MySQL's official homepage by TCX DataKonsult AB. MySQL is good, fast, support regular expression search, doesn't support transactions, - and FREE (or almost free). Can work on unix-s (mostly used on Solaris and Linux) and Windows.
* directory.google.com/Top/Computers/Software/Databases/MySQL/ - MySQL on google
* www.nusphere.com/products - NuSphere MySQL Advantage - MySQL, Apache, Perl, and PHP + manuals + graphical tools + support.

* www.mysql.com/downloads/gui-clients.html - MySQLGUI is the graphical sql client for MySQL developed by MySQL AB. This is still in alpha (as of 2000).
* www.pccs-linux.com/phplinux/mysql_clients.html - screenshots of MySQL client (available for Linux and Windows)
     ( www.pccs-linux.com/phplinux/- php development using linux & mysql )

* http://www.urbanresearch.com/software/utils/urbsql/ - UrbanResearch SQL Client for Windows - GUI to work with databases (supports MySQL, Paradox, Oracle 8i and MS Access). Has customizable synatx highlighting, self-documenting code generator, result-set grid, table structure, access to other objects. Multiple query windows. Export results into txt, html, etc. Database Administration.
* http://www.scibit.com/products/software/utils/mascon.asp - Mascon - A powerful MySQL administration interface for 32-bit Windows. Free version and $50 version.

* www.gjt.org/servlets/JCVSlet/log/ice/com/ice/sqlclient/MySQLClient.java/0 - MySQL client in Java
* http://www.worldserver.com/mm.mysql/ - JDBC driver (free download with source).

* rpmfind.net/linux/rpm2html/search.php?query=mysqlclient - mysqlclient for RedHat - 2 RPMs.
* http://www.phpwizard.net/phpMyAdmin/ - phpMyAdmin - work with mysql remotely using php (sql, administraion).

* http://www.lilback.com/macsql/ - on Macintosh - mysql & mSQL.
* http://www.dbwww.com/ - ForwardSQL ($300 .. $600 for tools integrating MySQL databases into Web sites).

* http://my.anse.de/ - MySQL-Front (Windows, Delphi) - a free admin. tool for MySQL.
* http://www.webeifer.de/alwin/programs/kmysqladmin/ - KMySQLAdmin - An admin tool for MySQL for Linux ( QT and KDE ).

* http://www.tek-tips.com/gthreadminder.cfm/lev2/4/lev3/27/pid/436/ - MySQL Forum at Tek-Tips.
* http://phpclub.unet.ru/index.php3?m=dbases&g=MySQL - Club of the Developers PHP - A russian PHP Club's collection of links to MySQL related resources.

 
Books home - top of the page -

Books:
* MySQL and mSQL (1999) by Randy Jay Yarger, et al
* MySQL (OTHER NEW RIDERS) (1999) by Paul DuBois
* MySQL and Perl for the Web (OTHER NEW RIDERS) (2001) by Paul DuBois
   This is excellent new book: Perl, Apache mod_perl, CGI.pm - and MySQL.

 
Hints home - top of the page -

Setting up mysql is very simple.
You download it from mysql.com (I prefer source version), unzip and untar it - then do usual installation as described in the text instructions.
Then you also install DBI and DBD (Perl).
After you started the mysql server - you should change the password for root:
    mysql> SET PASSWORD FOR root=PASSWORD('new_passwd_here');
Then you can create databases for different users:
   mysql>create database user1_db;
   mysql>grant all on user1_db to user1;
Read more on security in the manual, for example here:
* www.mysql.com/documentation/mysql/bychapter/
      6.12 Setting Up the Initial MySQL Privileges
      6.13 Adding New User Privileges to MySQL


 
Commands_examples home - top of the page -

MySQL keeps each individual table as 3 files in the var/ directory.  If you create a directory and then move the tables (set of 3 files) there then you have a new table in your database.  (If you care, the database command of "SHOW DATABASES" actually just gives you a directory listing of var/ ).  It seems that the platform move from *nix to Win32 is feasible.

Also, to get an idea of what the dump looks like without a lot of junk on the screen, use this command
"mysqldump --no-data DATABASE_NAME TABLE_NAME -u root -p"
This will show you what it does when it dumps.....

When working with mysql tool on the prompt, you can enter several commands separated by semicolon:

UPDATE vars SET block_id='32' WHERE  block='test1'; SELECT * FROM vars; UPDATE vars SET block_id='33' WHERE block='test1'; SELECT * FROM vars;

You can keep a list of your commands in a separate file and execute them as a batch from the prompt like this:

      cat filename | mysql -u someuser -password=somepassword  somedatabase
or   cat file | mysql > result

It is convenient to define these 3 aliases:

alias con="mysql -h myhost.com -u myuser --password=mypasswd  mydatabase"
alias vv="vi test.sql"
alias rr="cat test.sql | con"

con is a conection string command.

Here are examples of commands:
  con
  vv
  rr
  rr  > out.txt
  con < test.sql
  con < test.sql > out.txt

using 'limit' to limit the output:
select * from ABC limit 5,10;     # Retrieve rows 6-15
select * from ABC limit 5;     # retrieve first 5 rows, equivalent to: limit 0,5
 

autoincrement:

www.mysql.com/doc/e/x/example-AUTO_INCREMENT.html

CREATE TABLE animals (
  id MEDIUMINT NOT NULL AUTO_INCREMENT,
  name CHAR(30) NOT NULL,
  PRIMARY KEY (id)
);
INSERT INTO animals (name) VALUES ("dog"),("cat"),("penguin"),("lax"),("whale");
SELECT * FROM animals;

Which returns:

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
+----+---------+