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:
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:
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:
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.
blog comments powered by Disqus