Home arrow MySQL arrow Page 4 - MySQL in SSH: Basic Guide

Creating a New MySQL Table and Configuration - MySQL

There are some hosting companies that do not offer an SSL (Secure Socket Layer) environment for MySQL. It is important to have an SSL environment for MySQL, because your sessions are protected with “encryption.” Fortunately, if you can't get SSL with your hosting company, there is an alternative: SSH.

TABLE OF CONTENTS:
  1. MySQL in SSH: Basic Guide
  2. Connecting to MySQL Server using the Putty SSH Client
  3. Basic MySQL Database and Table Commands in SSH
  4. Creating a New MySQL Table and Configuration
By: Codex-M
Rating: starstarstarstarstar / 5
May 03, 2010

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More MySQL Articles          >>> More By Codex-M
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: