###########################################################################
# MySQL Notes                                                             #
# Anuradha Weeraman, 08 November 2000                                     #
# $Id: mysql.txt,v 1.1 2004/06/02 21:17:53 anuradha Exp $                 #
###########################################################################

Small primer on MySQL

*	after installing MySQL for the first time, make sure u run
	'mysql_install_db' to create the preliminary tables.
	
*	do a quick test by issuing 'mysql -uroot test'.

*	after installing mysql, the root account doesn't get assigned
	a password by default so its very important the root user
	quickly does so by typing "mysqladmin -uroot password 'newpass'".
	
*	test out ur password by typing 'mysql -uroot -pnewpass mysql',
	'select * from user;' etc.
	
*	to shutdown the server, 'mysqladmin -uroot -pnewpass shutdown'.

*	edit 'safe_mysqld' to customize ur configuration.

*	if u are locked out of ur system with a problem in authentication
	then by issuing this command u can guess full access to the grant
	tables : '/usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql
		  --datadir=/usr/local/mysql/data -Sg'

*	the following is an example of adding new users to the grant tables :
		mysql> insert into user values('localhost','mysql',
		       password('xyzzy'),'Y','Y','Y','Y','Y','Y','Y','Y',
		       'Y','Y');
		mysql> insert into user values('localhost','john',
		       password('blah0x1'),'Y','Y','Y','Y','Y','Y','Y',
		       'Y','Y','Y');
                         
	the following will add create a table and assign privileges.
		mysql> create database johns_DB;
		mysql> insert into db values('localhost','johns_DB','john',
		       'Y','Y','Y','Y','Y','Y');

	after editing grant tables, the mysql daemon has to be restarted
	before changes can take place 'mysqladmin -uroot -pnewpass reload'.

*	to add new users without messing around with grant tables directly
	u can simply issue a GRANT command in the MySQL prompt and have it
	do the dirty stuff for u.
		mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
		    -> ON johns_DB.*
		    -> TO anuradha@localhost
		    -> IDENTIFIED BY 'anupass';
		Query OK, 0 rows affected (0.00 sec)

*	to see the databases on the system issue :
	mysql> show databases;

*	if u want to open a particular database, just issue :
		mysql> connect johns_DB;
		Connection id: 163
		Current Database: johns_DB

*	u can create table definition files and use them to automatically
	create tables. the following are three examples:

		create table personinfo (
			userid CHAR(20),
			firstname CHAR(20),
			lastname CHAR(20),
			address1 CHAR(40),
			address2 CHAR(40),
			city CHAR(40),
			state CHAR(40),
			zip CHAR(10),
			phone CHAR(14)
		);

		create table projectinfo (
			supervisorfirstname CHAR(20),
			supervisorlastname CHAR(20),
			projectname CHAR(40) NOT NULL PRIMARY KEY,
			termination DATE,
			description TEXT
		);

		create table projectmembers (
			projectname CHAR(40),
			userid CHAR(20)
		);

	the DATE type takes values in the YEAR-MO-DY format. TEXT fields
	can store 32KB of data.

*	u can create these tables by :
		# mysql -uanuradha -panupass johns_DB < personinfo.def
		# mysql -uanuradha -panupass johns_DB < projectinfo.def
		# mysql -uanuradha -panupass johns_DB < projectmembers.def

*	u can see the tables by :
		# mysql -uanuradha -panupass johns_DB
		mysql> show tables;

*	here are a few examples in entering data :
		mysql> INSERT INTO projectinfo
		    -> SET supervisorfirstname='Henry',
		    -> supervisorlastname='James',
		    -> projectname='Wine Tasting 101',
		    -> description='A Very Important Experiment',
		    -> termination='2000-01-01';

		mysql> INSERT INTO projectinfo
		    -> (supervisorfirstname,supervisorlastname,
		    -> projectname,description,termination)
		    -> VALUES ('Martin','Dooley','Hydroponics',
		    -> 'Heat dissipation in hydroponics systems',2020-0401);

*	to see what u have typed :
		mysql> SELECT projectname,supervisorfirstname,
		       supervisorlastname FROM projectinfo;

*	you can also do complex relational searches like this :
		mysql> SELECT personifo.userid, personinfo.firstname,
		    -> personifo.lastname, personinfo.phone,
		    -> projectinfo.projectname
		    -> FROM personifo, projectinfo, projectmembers
		    -> WHERE projectinfo.supervisorfirstname LIKE '%enry%'
		    -> AND projectmembers.projectname = projectinfo.projectname
		    -> AND personinfo.userid = projectmembers.userid;