| LevSelector.com |
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 |
+----+---------+