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;
show tables
mysql> show tables;
run a sql script from inside sql
mysql> @lt;script name>.sql
search a table
mysql> select * from <database>.<table> e
where e.email like 'pass%';
order the search results
mysql> select * from <database>.<table> order by <database>.<table>.<field>;

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.
mysql> update subscribers e
    set e.email = 'fred@yahoo.com'
    where id = 5419
Conditional update
update orders x
  set x.number = 0
  where x.id <= 3000
  and x.number != 0

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

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

Backing up and restoring a database

Backing up a database: move to the mysql bin directory.
eg # pushd /usr/local/mysql/bin
# ./mysqldump <database name> > <database name>.sql
# ./mysqldump --all-databases | gzip > databasebackup.sql.gz


Restoring:
this should be straight forward, but when you run into problems it is time to directly start editing you sql. For example I manually created the database, cut the comments out of the .sql file, cut specific stuff from the sql -eg that is not part of the language, removed locks to simplify the sql...

Again I move to the sql directory and copied the .sql file there.
# ./mysql < <database name>.sql


Using MySQL Administrator to back up.
Backup > + > select database in left pane and move it to the right pane with arrow > expand database and select tables > click Start Backup and give destination.

Scripting with Bash OS

A script to display a table on the command line
#!/bin/sh
# Filename: s01.sh
/usr/local/mysql/bin/mysql -uroot -e"use mydatabase; select id from deliveries"

# bash s01.sh

Putting in a user variable
# Filename: s02.sh
/usr/local/mysql/bin/mysql -uroot -e"use mydatabase; update deliveries x set x.number = 0 where x.id <= '$1' and x.number != 0;"

#bash s02.sh 2946