MySQL Review

week6 and exercise 7

Creating your own table

You all have been given your own databases, login_db for this part. The following is an example of how to log into your own database:

mysql -u ssmallen -h dictator ssmallen_db

To create your own table use the CREATE TABLE command while at the mysql prompt. Here is an example which creates a table called hiking with 4 columns or fields.

    CREATE TABLE hiking( trail CHAR(50), area CHAR(50), 
                         distance FLOAT, est_time FLOAT );

The following commands are useful in seeing what you just did.

show tables:
show columns from hiking

Select Statement

Some other column datatypes you can use are

CHAR(length) - a stream of alphabet characters of maximum length
VARCHAR(length) - a stream of alphanumeric characters of maximum length
INT - an integer
FLOAT - a floating point number
DATE - form of 'yyyy-mm-dd'
TIME - form of 'hh:mm:ss'

Other useful commands

DROP TABLE table_name; - removes a table from the database
DELETE FROM table_name WHERE where_clause; - deletes entries from the table which satisfy the where clause
INSERT INTO table ...

mstrout@cs.ucsd.edu .... May 18, 2000