Technique

Inserting data into a table:
mysql> INSERT Into Weight VALUES (20090101, 240);

(Note: It appears that you must enter the data in the order in which the columns were first entered. (Also please note that in MySQL 5.1 the only date format that gets ingested properly is in the format yyyymmdd, with no dashes, periods, etc.)

This will show everything in the database:
mysql> SELECT * FROM Weight;

–Joab Jackson

To find an average of numbers in a database:

mysql>SELECT AVG ([column]) from [Table];

i.e.

mysql>SELECT AVG(HWeight) FROM Weight;

To sum of numbers in a database:

mysql>SELECT SUM ([column]) from [Table];

i.e.

mysql>select SUM(Weight)from Weight;

From here

–Joab Jackson

To delete an entry, you use the DELETE FROM command:

mysql> DELETE FROM [table_name] WHERE [records to delete]

(WHERE is optional)

To modify an existing record, use UPDATE which has this form:

mysql>UPDATE [Table] SET [Column Name]=[New Value] WHERE [Column name]=[Values in this column that is in the same row as the data you want to change].

i.e.
mysql>update Run set HDate=2009010 where RTime=97;

Taken from here

–Joab Jackson

After being installed, MySQL requires an additional step, to establish the user directories. Ruin this program:

mysql_install_db

start MySql this way:

mysql -u [user name] -p

it will then ask you for a password….

(Info taken from here)

–Joab Jackson

To drop a table:

mysql>drop table if exists (The name of the table)

(You don’t have to use the “if exists”)

from this page

–Joab Jackson

Here is how you create a table in a MySQL database

mysql> CREATE TABLE [name of table ([name of column 1] [data type of column 1], [name of column 2] [data type of column 2], … and so on…

i.e. (from a weight/exercise database I’m creating:

mysql> create table Weight (HDate DATE, Weight FLOAT(4,1));

(Note: in the FLOAT data type, the parenthesized numbers following the declaration [(x,y)], the first number is the total number of digits you want, the second number is how many digits do you want right of the decimal place)

Show the tables:
mysql> SHOW TABLES;

Show how the table is constructed:
mysql> DESCRIBE Bike;

(Taken from the Mysql.com’s tutorial: Creating a table. MySQL 5.1 datatypes are explained here. Table queries are explained yonder.

Also, an explanation of data types is here and here, and numeric data types are explained more here).

–Joab Jackson

You work with MySQL through a command line (mysql>). It is not case-sensitive (though database names will be, if you are working through them in Unix). commands can span multiple lines. You end them with a “;” QUIT gets you out.

This command:

mysql> SELECT VERSION(), CURRENT_DATE;

gives you info about the current version of MySQL and the current date.

Logging into a database:

USE [Name of database here]

Create a database:

mysql> CREATE DATABASE [Name of database];

Show table command:

mysql> SHOW TABLES;

(Taken from the MySQL tutorial on MySQL.com: Entering Queries, Database use, Database creation)

–Joab Jackson