#Written by Jen Chen
#On 03/07/05

Lesson ?
   Running mysql command(s) from the Unix shell and as a Unix shell script.

	- First of all, some of you ask me how to save the result of an mysql
	query to a file.
	There are two ways of doing this:
	  (1) Inside mysql:

	 mysql> select * INTO OUTFILE 'absolute_path_to_your_output_file'
	 	from some_table

	CAUTION: Make sure that you have read and write permission on the
	folder that you attempt to save the file to.

	 ex: To generate a csv file, named "salespeople.csv", under the /tmp directory
	     based on all records from the SalesPeople table.

		select salesID,',',firstName,',',lastname,',',initial,',',base,',',
		       supervisor into OUTFILE '/tmp/salespeople.csv' from SalesPeople;


	  (2) Outside of mysql, ie from the unix shell: Look at (2) below. 	

(1)	- Note: We can run Mysql queries from the Unix prompt by applying
	the mysql command with the -e option, follow by the query within a 
	pair of double quotes as follow:

		mysql -u jchen13 - p -e "select * from Data.Tmp"

	you have to supply the option -p, because this is security set up
	on our server.

	- To redirect the query result to a file, user the redirection in 
	Unix as follow:

		mysql -u jchen13 -p -e "select * from Data.Tmp" > allTmp.txt

(2)	- Note: We can also run mysql queries by writing all these queries
	in a file (let's say the file's name is query.sql).
	At the moment let's say I have a Tmp table under my Data database,
	and I would like to query all records from that table. To do that I
	open the VI-editor then add this query into my editor:

		select * from Data.Tmp

	Now save your query.sql file, then quit your VI-editor.
	To execute the contents in this file (of course it should ONLY contain
	mysql queries), I would type the following at the Unix prompt:

		mysql < query.sql -p
	
	We can execute more than one query in the query.sql file. FOr example
	we'd like to see the structure of the Tmp table, then query all of its
	records, we would add one more line on top of the existing "select..."
	statement as follow:

		desc Data.Tmp;
		select * from Data.Tmp

	Note that we add a semi-colon at the end of the 1st SQL-statement; 
	since this is required for executing more than one SQL-statements 
	either from a command line or from the mysql prompt.
	Save your query.sql file, then exit VI and type

		mysql < query.sql -p

	You should see the results of your queries displayed ion the screen.
	
	- Note: To direct the result of your queries to a file, add a 
	redirection as follow:

		mysql < query.sql -p > result.txt

(3)	Now if you want to process multiple queries from the command prompt,
	then supply the option -p (for password, follow by the option e).
	Remember to put -p BEFORE -e, follow a double quotes, then by any
	msyql queries, seperate each query by a semi-colon, then ends your
	queries with a pair of double quotes as follow:

		mysql -p -e "desc Data.tmp;
			     select * from Data.Tmp"	
	You don't have to enter your queries as one long query, you can press
	the "ENTER" key after each query (or part of your query) then go
	to the next line for your next query.

(4)	Or if you are as lazy as I am, then use redirection as follow to 
	processs multiple Mysql queries at the command line as follow:

		mysql -p << _EOF

	After you pressed "ENTER" you'll see a prompt waiting for your input.
	Type all your queries, and remember to seperate each query by a semi-
	colon.
	End your input by provinding the "_EOF" word ALL ON ONE LINE.
	After pressing the "ENTER" key, all queries that you just typed will be
	processed.
	
(5)	Another way to process Mysql queries is as follow:

		mysql -u userName <<-EOF
	
	then the shell will prompt for input with a question mark (?).
	First we need to enter the name of the database that we want to use
	by entering:
	   
	    \u test
	
	where "test" is the name of the database that we use.
	- The rest of the input should be SQL statement; for example
	
	    select *
	    from
	    Student
	
	- and it must end with "-EOF".
	
(6)	To run mysql as a script:

	- Create one script to store this mysql command:

    ======================================
    #!/usr/bin/csh
     
    mysql -u userName -D databaseName < '$argv[1]'

    ======================================
   
    - where $argv[1] is the name's of the file that contains your

     SQL statements. For example the contents of the $argv[1] 

     file would look like:

    ======================================
    select *
    from
    Student
    limit 5
    ======================================