What is a MySQL table and how can you use it

Content:

What is a MySQL table?
How to manage a MySQL table via the SQL command line
How to manage a MySQL table using the PHPMyAdmin
Resources

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 and columns. 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
tu

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.

Resources:

MySQL Table