Creating a New MySQL Table and Configuration - MySQL

Aside from manipulating a MySQL database using the basic commands outlined earlier, you can also create new MySQL tables and their associated fields in an SSH environment. For example, suppose you are told to create the following MySQL database table in an existing database codex:

Database name: codex

Database table name: codexmovies

Database field names:

•  movieid ? type: int(10), Unsigned, NOT NULL, Auto-Increment
  
• movietitle ? type: varchar(100) NOT NULL default ' '
  
• moviegenre ? type: varchar(100) NOT NULL default ' '

It's important to note that some hosting companies do not allow the creation of a new database in an SSH environment; they let their customers create new databases using the hosting control panel. So you'd better check first with your hosting company.

Step 1: Connect to your MySQL server using the Putty SSH client (refer to the previously discussed section).

Step 2: At the MySQL prompt, select the database to work with (name of database is codex):

mysql> use codex;

Step 3: Create the table, its fields and configuration in one command line:

mysql> CREATE TABLE codexmovies (movieid int(10) UNSIGNED NOT NULL AUTO_INCREMENT,movietitle varchar(100) NOT NULL default '',moviegenre varchar(100) NOT NULL default '', PRIMARY KEY (movieid)) TYPE=MyISAM;

Below is the link to the screen shot of the actual MySQL query done in SSH:

http://www.php-developer.org/using_ssh_to_create_mysql_table.jpg

Discussion of the design elements of the MySQL database is beyond the scope of this article. It is suggested that you refer to the following materials if you need some references:

http://www.devshed.com/c/a/MySQL/Designing-a-MySQL-Database-Tips-and-Techniques/  

http://dev.mysql.com/doc/refman/5.0/en/data-types.html  

You have successfully created the MySQL database table codexmovies.

Likewise, you can view the structure of this table (showing the field types) using the following command:

mysql> describe codexmovies;

Inserting Records into a MySQL Table

Suppose you need to insert the following records into the table "codexmovies:"

movie title1: Iron Man 2

movie genre1: Action

movie title2: Sherlock Holmes

movie genre: detective

movie title3: Milk

movie genre: drama

mysql> INSERT INTO codexmovies (movietitle,moviegenre) VALUES ('Iron Man 2','Action');

mysql> INSERT INTO codexmovies (movietitle,moviegenre) VALUES ('Sherlock Holmes','detective');

mysql> INSERT INTO codexmovies (movietitle,moviegenre) VALUES ('Milk','drama');

Okay, now you need to view all of the codexmovies records in SSH:

mysql> select * from codexmovies;

You can even execute a more advanced MySQL queries like UPDATE, DELETE, etc. and other MySQL commands that are needed for complex database administration.