Basic MySQL Database Setup and Manipulation

Logging into MySQL

at the shell prompt, type the following command

mysql - h hostname - u username -p

• hostname is the name of the MySQL server
• username is the MySQL admin name
• -p will cause the server to prompt for the password

Basic MySQL Commands

SHOW DATABASES;

this command will show all of the databases currently on the server that you have logged into. Be sure to include the semicolon (;) at the end of the command.

CREATE DATABASE dbname;

This command creates a new database with the name specified by dbname.

USE dbname;

Set the focus of the MySQL server to use the database specified by dbname.

DROP DATABASE dbname;

This command deletes or drops the database specified by dbname.

QUIT

This command will cause the server to log you out of and exit the MySQL server.

¥c

This is backslash-c which causes the command currently being typed to be ignored by the server. it is typed on the end of the line currently being typed and will return you to the command prompt when you press enter.

Table Creation

To set up a new table in the database you are currently using, you would use the following command

CREATE TABLE tableName (columnName1 columnType columnDetails, columnName2 columnType columnDetails,...);

where the tableName is the name you wish to assign to the table and the columnNames are the field names. The types specified in columnType can be any of the following:

• INT -- an integer number
• TINYTEXT -- Text limited to 255 characters

• TEXT -- pure text as entered
• DATE -- a date specified in standard format by you
• DATETIME -- A date and time combination
• TIMESTAMP -- a system timestamp in standard format

• BOOL, BOOLEAN -- a true or false indicator

• BIT -- A Bit field type 1-64
• TINYINT -- an integer from -128 to 127
• SMALLINT -- an integer from -32768 to 32767
• MEDIUMINT -- This is the same as INT
• BIGINT -- a really bit number (see manual for more)

while this is not a complete list of data types, it is a pretty good start. if you want to see all of the data type, check the SQL Manual.

The columnDetails can be specified by the following types:

• NOT NULL -- This means that the data can not be empty
• AUTO_INCREMENT -- this is generally associated with a counter
• PRIMARY KEY -- this means that this field will be the field which all searches are based on for this table.
• CURRENT_DATE -- assigns the current date as the data for the field

Viewing Databases

To view all of the tables in the particular database you are working with, you can use the folllowing command:

SHOW TABLES;

this will display a list of all tables and the number of records in the tables.

To view the physical structure of a particular table, you use the following command:

DESCRIBE tableName;

This will display the entire structure along with all of the data types specified to the fields of the table.

Deleting Tables

To delete a table froma database, we drop the table. This means that we drop it from the list of tables.

DROP TABLE tableName;

This will delete the table, the structure and any records in the table. Be careful with this as you can wind up erasing tons of hard work that you have done.

The same can be said for the DROP dbname; command which will delete the entire database.