mysql

Platform is a Fedora linux box.

Needed to start,stop mysql
# /etc/rc.d/init.d/mysqld start
# /etc/rc.d/init.d/mysqld stop
To verify # ps -Al | grep mysql
# which mysql
/usr/bin/mysql
To start a command line
# mysql --password='***'
To see the databases
# mysqlshow --password='***'

MySQL gui prgrams, login with user + password
MySQL Query Browser - gui to execute sql queries
MySQL Administrator - gui
eg created libraryuser with restricted privilleges: User Administrator

Select the database
mysql> USE menagerie;
List all databases
mysql> SHOW DATABASES;
Create a database
mysql> CREATE DATABASE menagerie;
Delete a database
mysql> drop database;
Delete a table
mysql> drop table;
Look at a tables data types and constraints
mysql> describe table;


Example Database

mysql> CREATE TABLE Branch( bid INT NOT NULL PRIMARY KEY, suburb VARCHAR(100) );
mysql> insert into Branch (bid,suburb) values(7,'Richmond');
mysql> create table Property( pid varchar(10) NOT NULL PRIMARY KEY, bid int NOT NULL, blocksize int, address varchar(50), dateadv varchar(10), priceask int);
mysql> insert into Property (pid,bid,blocksize,address,dateadv,priceask) values('B11',18,620,'Diana St Reservoir','2005-03-15',470000);
or more simply
insert into Property values( 'B11',18,620,'Diana St Reservoir','2005-03-15',470000);


Read in data from a file into a table, delimitered with tabs.
mysql> load data local infile '/usr/local/mysql/bin/t5.txt' into table natio.stockists fields terminated by '\t' lines terminated by '\n';

Editing sql record.
>   update subscribers e
    set e.email = 'fred@yahoo.com'
    where id = 5419

Deleting a record
>   delete from subscribers
    where id = 6342;

Starting a mysql session
Move to mysql directory
# cd /usr/local/mysql/bin
# ./mysql
>   use database