Table of contents :
What is a MySQL table?
Each database table
has a name which consists of two parts: a table prefix and the specific database name itself. The use of prefix allows several web applications to utilize one database. For example, a given database can store Joomla
CMS and phpBB forum data simultaneously by using tables of the jos_tablename and phppbb_tablename type, respectively.
Corporate applications are being built with the idea to store information in an orderly fashion. The usage of a database in such applications is an essential feature. In order to provide proper content for a given website, MySQL
stores all the data in tables. Regardless of the prefix, each MySQL database table consists of rows
. The columns specify the type of data, whereas the rows contain the actual data itself. Below is how you could imagine a MySQL table.
An example of how a MySQL table looks in PHPMyAdmin
How to manage a MySQL table using the SQL command line
The usage of MySQL databases makes your website's
content dynamic. To manage this dynamic data most websites have a backend control panel
which enables you to edit, delete and add data via a graphical user interface. To take full advantage of all the possibilities and features of your site's database and in order to keep your data optimized, it is necessary to manage your database and database tables.
The management of a database table involves more than CREATE
and DUMP actions. There is a list of operations which allow you to keep your data optimized and updated.
By using the ALTER TABLE
statement you can rename a database table, add a new column, insert new data in a column, drop a column, and more.
An example of how to add a column using the Alter Table in MySQL
ALTER TABLE phonebook ADD COLUMN email VARCHAR(128);
An example of how to rename a table using the Alter Table in MySQL
ALTER TABLE phonebook RENAME TO contacts
An example of how to rename a column in a table using the Alter Table in MySQL
ALTER TABLE contacts RENAME COLUMN last_name to first_name;
How to delete a column in a table using the Alter Table in MySQL
ALTER TABLE contacts DROP COLUMN first_name;
Using the MySQL INSERT
and UPDATE statements you can add or update data in your database tables. The MySQL insert query indicates what type of data will be inserted in which column and in which table.
An example of how to Insert data into a MySQL table
INSERT INTO recipes(ingrediant, quantaty, mtype, address) VALUES('chicken', '350', 'grams');
An example of how to Update data in a MySQL table
UPDATE recipes SET ingrediant = 'fish' WHERE quantaty = '350' AND mtype = 'grams';
A MySQL select query, on the other hand, is used for displaying the information in the database. It selects information about a given database field from the MySQL table in a way, similar to the MySQL insert query's.
An example of how to Select data from a MySQL table
SELECT * FROM phonebook ORDER BY lastname;
Also, it is important to create MySQL database dump
files, so that you will have a backup of your database data.
An example of how to create a MySQL database dump
mysqldump --tab=/path/to/some/dir --opt database_name
How to manage a MySQL table using the PHPMyAdmin
Managing databases using the SQL command line is not convenient for the end users and for the not-that-skilled webmasters. That's why we, at NTC Hosting, provide our clients with MySQL Database management tools. All our web hosting plans come with a built-in PHPMyAdmin
tool which enables our clients to easily interact with their databases in a convenient graphic user environment.
PHPMyAdmin is the most popular web based MySQL database management tool. It provides a complete set of tools and wizards and allows the customers to interact with their databases without the usage of a SQL command line. For a more detailed review of the basic PHPMyAdmin features, please check its separate article or watch our tutorial videos