What is a SQL database?
If you are asking yourself what makes the information on the websites so well organized and dynamically generated as we browse through the separate web pages, executing various click, select, download, etc. actions, the answer is simple - the SQL (Structured Query Language) databases. An SQL database is a structured collection of data. The content of the SQL database can be anything from a cooking recipe to a image gallery or the vast amounts of information in a corporate network. To add, access, and process data stored in a computer database, you need a database management system. Since computers are very good at handling large amounts of data, database management systems play a central role in computing, as standalone utilities, or as parts of other applications. To add speed and flexibility, relational database management systems (RDBMS), such as MySQL, store data in separate tables rather than putting all the data in one big storeroom. Websites which use databases have many advantages. They provide structured, easily accessible and editable content, available to multiple users. Also because most of the information is stored in the database the content can be easily transferred or exported to another web application, new web server or a newer version of the latter.
Why use MySQL?
The most widely used type of SQL database on the World Wide Web today is MySQL. As the most popular Open Source SQL database management system, MySQL is developed, distributed, and supported by MySQL AB. Because MySQL is lightweight, open source-based and free, it is a foundation for many amateur, non-professional, or open source projects like Joomla, Wordpress, 4images and PHPBB3. Unfortunately, MySQL does not provide the higher level of security typical of expensive enterprise class database solutions such as Oracle, MsSQL, Sybase, but it provides a secure, fast and reliable enough database service which is implemented in many professional web environments.
Cross-platform software - easy to use
MySQL databases are preferred over the proprietary database systems due to their reliability and speed of performance. They are most commonly used for both regular and embedded Web applications running on UNIX, Windows and Mac OS. MySQL is the perfect database software for both averagely loaded websites and high-traffic web portals. Depending on the purpose and scale of your website - you may need between 1 and 9999 MySQL databases.
Free open source database
MySQL is an open source database. It represents a free, reasonable alternative to the complex corporate database solutions, and allows today's web applications to store and display a large amount of articles, user posts, comments and lots of other different types of data. Some of the most popular open source PHP web applications, such as Joomla, Wordpress, phpBB and many more, use MySQL to store their data.
All that web content is contained in the database of the current web application and can be easily displayed, edited, or removed by executing 4 basic database statements: Insert, Select, Update or Delete (you can check the examples above, but note that they are not complete and are only for educational purposes).
A MySQL database table consists of rows and columns. The columns specify the type of data, whereas the rows contain the data itself. Below is shown how a MySQL table can possibly look like:
An example of how a MySQL table looks in PHPMyAdmin
All web hosting plans offered by NTC Hosting provide MySQL hosting too. The exact number of databases and storage quotas depends on the type of the packages you choose to take advantage of. With each of the plans you will be given access to a MySQL management interface from where you will be able to create, edit and delete your databases and have total control over their contents through a phpMyAdmin interface.
If, at any point, you decide that you require additional databases to install more scripts, or simply because your website has grown so much that the available ones are not enough, you can always purchase additional databases by clicking the Add Services/Upgrades link in your Web Hosting Control Panel, or upgrade to a more elaborate plan.
How to install MySQL?
MySQL works on many different operating systems, including Windows, Linux, BSD, Mac OSX and more. One of the things which make MySQL really popular is the simple integration with PHP (one of the most used web development languages).
MySQL - part of the XAMPP platform
As an open source database component of the XAMPP (formerly known as LAMP - Linux-Apache-MySQL-PHP) platform, MySQL has become a standard for running database-enabled websites and servers on an open-source basis. Also, the XAMPP platform provides an easy installation of MySQL, together with the Apache server, PHP5, and the popular MySQL database web management tool PHPMyAdmin, on Windows, MacOS, Solaris and other OSs.
Standalone MySQL Server installation
Also, there is an option for a standalone custom installation of the latest MySQL version on your web hosting server. To install MySQL you need to download the necessary packages from MySQL's official download page (http://dev.mysql.com/downloads/mysql/). You can use the provided installation binaries or compile the MySQL Server from the source.
Installing MySQL 5 server on a Linux host
Another way to get MySQL easily installed on a Linux host is to use the package management system and install the MySQL server and all other dependencies from your Linux distribution's repositories.
MySQL 5 server installation using the Ubuntu's Synaptic Package Manager
To install a MySQL 5 Server on an Ubuntu host you can use the inbuilt Synaptic Package Manager which is a GUI for the apt-get application. Also you can install the MySQL Server using the terminal. In Synaptic you need to choose the 'mysql-server' package (Hint: You can use the Quick search to find it faster).
The package manager will automatically match all the dependencies and will suggest installation of additional packages. To install all the needed packages please press the 'Mark' button.
Then click on the 'Apply' button and the package manager will start downloading all the necessary packages and install your MySQL 5 server on your Ubuntu host. Nearly at the end of the installation you will be prompted to configure the root password for you MySQL server.
After submitting and confirming the password for your MySQL Server's root account the installation will finish.
MySQL server installation using the Ubuntu's terminal
To install a MySQL 5 Server on an Ubuntu host using the terminal you need to open a terminal window and use the following command:
$sudo apt-get install mysql-serverMySQL comes with no root password. So you need to set the root password in order to make your MySQL Server more secure. To do so, type:
$mysqladmin -u root password your-new-password$sudo /etc/init.d/mysql restart
To create a new database, use the mysqladmin command again:
$mysqladmin create <yourdatabase>If you are running PHP you will also need to install the PHP module for MySQL 5:
$sudo apt-get install php5-mysqlMySQL's basic commands overview (with examples)
As a relational database management system, MySQL keeps all the data structured in tables. The tables and the data contained in them can be displayed, managed, or modified using simple or complex MySQL queries and commands. In order to perform any of the basic commands mentioned below, you need to have the necessary administration rights for the database.
How to Create User in MySQL
Because of security reasons it is not recommended to use your MySQL server's root account for common tasks. Also, when you work in a team, it is necessary to have more than one user for a database. To create user in MySQL you need to establish connection with your MySQL server and to use the following SQL query:
An example of How to Create User in MySQL
CREATE USER michael IDENTIFIED BY PASSWORD 'mypassword123';For more information about creating users in MySQL you can also check the MySQL create user article. This process has been facilitated to the maximum on the NTC Hosting servers and our system automatically creates users during the set-up of the MySQL databases.
How to manage a database table in MySQL
As we already mentioned, all the data in MySQL is structured in database tables. Once the database is created, it doesn't have any tables in it. So, the developers need to create the tables for the specific needs of their web applications. Actually, the creation of a MySQL table is not that complicated. You need to connect to the server through an account with appropriate rights and then run a command similar to the one in the example below:
An example of How to Create Table in MySQL
For our example we will create a table called 'phonebook' which will have fields for 'phone', 'firstname', 'lastname' and 'address'. For this we’ll use a query with a CREATE TABLE statement:
CREATE TABLE phonebook(phone VARCHAR(32), firstname VARCHAR(32), lastname VARCHAR(32), address VARCHAR(64));Because the most complex applications, such as CMSs and forums, use a large number of tables in a single database, the developers use a script which creates all the required tables automatically during the installation of the application.
Once your database table is created, you need to fill it with some data. To do so, you need to use INSERT INTO.
An example of how to insert data into a MySQL table
In order to add some data in our 'phonebook' table we'll use INSERT INTO. After the statement itself we’ll specify the database's table 'phonebook' and then all the table's fields (in the brackets). Then comes the VALUES statement which provides (again in brackets) the data for every field.
INSERT INTO phonebook(phone, firstname, lastname, address) VALUES('+1 123 456 7890', 'John', 'Doe', 'North America');Once the data is inserted in the database, you can select specific information using a SELECT SQL statement.
An example of how to select data from a MySQL table
For our example's purposes we'll use 'SELECT' to return all the data kept in the 'lastname' fields in the 'phonebook' table.
SELECT * FROM phonebook ORDER BY lastname;But if we need to list all the data for Doe as lastname, our query will look like this:
SELECT * FROM phonebook WHERE lastname = 'Doe';Let's make our query more complex and ask the database server to return all the data for the first and the last name of John Doe
SELECT * FROM phonebook WHERE firstname = 'John' AND lastname = 'Doe';When the information in a table needs to be updated you can use a query with an UPDATE statement in it and replace the data in the specified fields.
An example of how to update data in a MySQL database
In this example we will update the data for the phone and the address of all the persons named John Doe
UPDATE phonebook SET address = 'North America', phone = '+1 123 456 7890' WHERE firstname = 'John' AND lastname = 'Doe';When there is some data in our database which we don’t need anymore, we can delete it using a DELETE query.
An example of how to delete data from a MySQL database
In our example we will use the DELETE FROM command and we will drop all the information about John Doe kept in the 'phonebook' table
DELETE FROM phonebook WHERE WHERE firstname = 'John' AND lastname = 'Doe';If our application doesn't require the 'phonebook' table anymore, we can delete the table using the DROP TABLE query
An example of how to delete a MySQL table
DROP TABLE phonebook;Also, you can delete the table in order to empty its contents and then create it again, but this is the complicated way because you can achieve the same results using the TRUNCATE command.
An example of how to empty data from a MySQL table without deleting it
TRUNCATE TABLE phonebook;As you can see, the SQL language is not complicated, but, for the sake of convenience for both webmasters and developers, there are database management tools which allow you to manage tables and databases using a graphic user interface. Such tool is the popular, web-based, MySQL management application called PHPMyAdmin, which is built in our Control Panel by default.
If you need to read more information about the PHPMyAdmin tool or need detailed information on how to create user, create table, insert data, grant privileges or create a database backup using the DUMP function, please check the articles dedicated to those subjects.
Resources: