Summary of Routine Commands
===========================

Start MySQL
Open a DATABASE
Open the TABLES in the DATABASE
View the FIELDS in a TABLE (line = 1 record)


Routine Commands in detail
==========================


Start MySQL
-----------

mysql [enter]

Note prompt changes to "mysql>" prompt:

mysql>



View existing databases
-----------------------

mysql> show databases;

+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+
2 rows in set (0.00 sec)

- 'mysql' and 'test' are standard databases included with
  a MySQL installation
- note the required ; at the end of the command, followed by
  pressing [enter]
- if you do forget it, you can type the semicolon at the
  prompt afterwards ;



Open the database called 'test'
-------------------------------

mysql> use test;

- use is the command to open a database
- 'test' is the name of the database to which all mysql users
  have access



With the 'test' database open, see the tables it contains
---------------------------------------------------------

mysql> show tables;

+----------------+
| Tables_in_test |
+----------------+
| andy           |
+----------------+
1 row in set (0.00 sec)



Create a Table
--------------

Where did the 'andy' table in the 'test' database come from?
With the database open, create a new table and the fields it will contain:

mysql> create table andy (fname varchar(20),sex varchar(1), dob date);

- the 'andy' table will be a personal table containing the names,
  gender and dates of   birth of family members

Once a table and its fields are created, you can check the table's structure..



View the structure of a table
-----------------------------

mysql> describe andy;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| fname | varchar(20) | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | NULL    |       |
| dob   | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


- the 'describe' command shows the structure of the defined table




Inserting New Data
------------------

MySQL is very verbose, you literally type in almost how you'd say it:

mysql> insert into andy (fname,sex,dob) values ('Debra','F','19690304');

- note you have to specify the field names receiving data and
  then the data itself
- date fields need to be entered as yyyymmdd



Viewing Data
------------

Once the fields are populated with some data, take a look at the whole table's
contents:

mysql> select * from andy;

+---------+------+------------+
| fname   | sex  | dob        |
+---------+------+------------+
| Debra   | F    | 1969-03-04 |
| Sarah   | F    | 1974-10-12 |
| Mum     | F    | 1939-05-16 |
| Dad     | M    | 1936-05-22 |
| Andy    | M    | 1963-12-04 |
+---------+------+------------+
6 rows in set (0.00 sec)

- 'select' displays a list of records
- * as usual, mean "everything"



To view specific field(s)
-------------------------

mysql> select sex from andy;

+------+
| sex  |
+------+
| F    |
| F    |
| F    |
| F    |
| M    |
| M    |
+------+
6 rows in set (0.00 sec)



View records containing specific data only
------------------------------------------

mysql> select * from andy where sex = 'F';

+---------+------+------------+
| fname   | sex  | dob        |
+---------+------+------------+
| Debra   | F    | 1969-03-04 |
| Sarah   | F    | 1974-10-12 |
| Mum     | F    | 1939-05-16 |
+---------+------+------------+
4 rows in set (0.00 sec)

and

mysql> select sex from andy where sex = 'f';

+------+
| sex  |
+------+
| F    |
| F    |
| F    |
| F    |
+------+
4 rows in set (0.00 sec)

- note results are NOT case sensitive



Delete a record from the andy table
-----------------------------------

mysql> delete from andy where fname ='Debra';

Query OK, 1 row affected (0.02 sec)

mysql> select * from andy;
+-------+------+------------+
| fname | sex  | dob        |
+-------+------+------------+
| Sarah | F    | 1974-10-12 |
| Mum   | F    | 1939-05-16 |
| Dad   | M    | 1936-05-22 |
| Andy  | M    | 1963-12-04 |
+-------+------+------------+
5 rows in set (0.00 sec)



Get help
--------

mysql> help;

MySQL commands:
Note that all text commands must be first on line and end with ';'
help    (\h)    Display this help.
?       (\?)    Synonym for `help'.
clear   (\c)    Clear command.
connect (\r)    Reconnect to the server. Optional arguments are db and host.
edit    (\e)    Edit command with $EDITOR.
ego     (\G)    Send command to mysql server, display result vertically.
exit    (\q)    Exit mysql. Same as quit.
go      (\g)    Send command to mysql server.
nopager (\n)    Disable pager, print to stdout.
notee   (\t)    Don't write into outfile.
pager   (\P)    Set PAGER [to_pager]. Print the query results via PAGER.
print   (\p)    Print current command.
quit    (\q)    Quit mysql.
rehash  (\#)    Rebuild completion hash.
source  (\.)    Execute a SQL script file. Takes a file name as an argument.
status  (\s)    Get status information from the server.
tee     (\T)    Set outfile [to_outfile]. Append everything into given outfile.
use     (\u)    Use another database. Takes database name as argument.

Connection id: 15  (Can be used with mysqladmin kill)



Quit MySQL
----------

mysql> \q