At a glance......
MySQL is the most popular Open Source Relational SQL Database Management System. MySQL is one of the best RDBMS being used for developing various web-based software applications. MySQL is developed, marketed and supported by MySQL AB, which is a Swedish company. This tutorial will give you a quick start to MySQL and make you comfortable with MySQL programming.
MySQL is free and open-source software under the terms of the GNU General Public License and is also available under a variety of proprietary licenses. MySQL was owned and sponsored by the Swedish company MySQL AB, which was bought by Sun Microsystems (now Oracle Corporation).[rx] In 2010, when Oracle acquired Sun, Widenius forked the open-source MySQL project to create MariaDB.
MySQL is the world’s most popular open-source database. With its proven performance, reliability and ease-of-use, MySQL has become the leading database choice for web-based applications, covering the entire range from personal projects and websites, via e-commerce and information services, all the way to high profile web properties including Facebook, Twitter, YouTube, Yahoo! and many more.
MySQL is a fast, easy-to-use RDBMS being used for many small and big businesses. MySQL is developed, marketed and supported by MySQL AB, which is a Swedish company. MySQL is becoming so popular because of many good reasons −
- MySQL is released under an open-source license. So you have nothing to pay to use it.
- MySQL is a very powerful program in its own right. It handles a large subset of the functionality of the most expensive and powerful database packages.
- MySQL uses a standard form of the well-known SQL data language.
- MySQL works on many operating systems and with many languages including PHP, PERL, C, C++, JAVA, etc.
- MySQL works very quickly and works well even with large data sets.
- MySQL is very friendly to PHP, the most appreciated language for web development.
- MySQL supports large databases, up to 50 million rows or more in a table. The default file size limit for a table is 4GB, but you can increase this (if your operating system can handle it) to a theoretical limit of 8 million terabytes (TB).
- MySQL is customizable. The open-source GPL license allows programmers to modify the MySQL software to fit their own specific environments.
Many hosting companies offering MySQL hosting with various hosting plans. When choosing a MySQL hosting provider, you do need to consider several additional options beyond the standard features such as MySQL version, MySQL server uptime, backup, bandwidth, and storage:
- “Unlimited” MySQL databases with unlimited storage – This feature is very important if you want to scale up your operations and create additional MySQL databases without being charged. You need to do serious research to compare the services of a good number of hosting providers before going forward with the most suitable one.
- Database client management tools – To have a good database client management tool is so important because you’ll use it to interact with the MySQL database server on a regular basis. The MySQL client management tools provided by hosting provider must be intuitive and easy to use for DBA and developers who are working often with MySQL. Most hosting companies offer PHPMyAdmin software as a default tool for working with MySQL database server. The PHPMyAdmin tool is free web-based software that allows you to perform various database tasks including executing the query, backing up data, uploading data, etc., so when you choose a hosting provider, make sure that PHPMyAdmin is available.
- Secure connection to MySQL server – If your data is sensitive, you need a secure connection to your MySQL server. Ask the hosting provider to provide you with a secure shell (SSH) tunnel.
Regardless of MySQL hosting providers, if you sign up to HostGator, you can be sure that you’ll get very good hosting quality. We have done a lot of serious researches to find the best hosting services based on price, performance, features and support.
Features of MySQL
MySQL is offered under two different editions: the open-source MySQL Community Server and the proprietary Enterprise Server.[rx] MySQL Enterprise Server is differentiated by a series of proprietary extensions which install as server plugins, but otherwise shares the version numbering system and is built from the same code base.
Major features as available in MySQL 5.6
- A broad subset of ANSI SQL 99, as well as extensions
- Cross-platform support
- Stored procedures, using a procedural language that closely adheres to SQL/PSM
- Updatable views
- Online Data Definition Language (DDL) when using the InnoDB Storage Engine.
- Information schema
- Performance Schema that collects and aggregates statistics about server execution and query performance for monitoring purposes.[rx]
- A set of SQL Mode options to control runtime behavior, including a strict mode to better adhere to SQL standards.
- X/Open XA distributed transaction processing (DTP) support; two phases commit as part of this, using the default InnoDB storage engine
- Transactions with savepoints when using the default InnoDB Storage Engine. The NDB Cluster Storage Engine also supports transactions.
- ACID compliance when using InnoDB and NDB Cluster Storage Engines[rx]
- SSL support
- Query caching
- Sub-SELECTs (i.e. nested SELECTs)
Built-in replication support
- Asynchronous replication – master-slave from one master to many slaves[rx][rx] or many masters to one slave[rx]
- Semi synchronous replication – Master to slave replication where the master waits on replication[rx] [rx]
- Synchronous replication – Multi-master replication is provided in MySQL Cluster [rx].
- Virtual Synchronous – Self-managed groups of MySQL servers with multi-master support can be done using: Galera Cluster [rx] or the built-in Group Replication plugin[rx]
- Full-text indexing and searching
- Embedded database library
- Unicode support
- Partitioned tables with the pruning of partitions in the optimizer
- Shared-nothing clustering through MySQL Cluster
- Multiple storage engines, allowing one to choose the one that is most effective for each table in the application.
- Native storage engines InnoDB, MyISAM, Merge, Memory (heap), Federated, Archive, CSV, Blackhole, NDB Cluster.
- Commit grouping, gathering multiple transactions from multiple connections together to increase the number of commits per second.
The developers release minor updates of the MySQL Server approximately every two months. The sources can be obtained from MySQL’s website or from MySQL’s GitHub repository, both under the GPL license.
Functions of MySQL
- WordPress requires MySQL – to store and retrieve all of its data including post content, user profiles, and custom post types. Most web hosting providers already have MySQL installed on their web servers as it is widely used in many open source web applications such as WordPress.
- WordPress uses the PHP programming language – to store and retrieve data from the MySQL database. To retrieve data from the database, WordPress runs SQL queries to dynamically generate content. SQL stands for Structured Query Language and is the programming language typically used to query databases.
- For users that are not comfortable writing their own PHP and SQL scripts – most web hosting providers offer easy to use web applications to manage databases. One such web application is phpMyAdmin which allows users to manage their database using a web-based graphical interface. You can manipulate your tables visually while phpMyAdmin runs the SQL queries for you.
Here is the list of all important MySQL functions. Each function has been explained along with a suitable example.
- MySQL Group By Clause − The MySQL GROUP BY statement is used along with the SQL aggregate functions like SUM to provide means of grouping the result dataset by certain database table column(s).
- MySQL IN Clause − This is a clause, which can be used along with any MySQL query to specify a condition.
- MySQL BETWEEN Clause − This is a clause, which can be used along with any MySQL query to specify a condition.
- MySQL UNION Keyword − Use a UNION operation to combine multiple result sets into one.
- MySQL COUNT Function − The MySQL COUNT aggregate function is used to count the number of rows in a database table.
- MySQL MAX Function − The MySQL MAX aggregate function allows us to select the highest (maximum) value for a certain column.
- MySQL MIN Function − The MySQL MIN aggregate function allows us to select the lowest (minimum) value for a certain column.
- MySQL AVG Function − The MySQL AVG aggregate function selects the average value for a certain table column.
- MySQL SUM Function − The MySQL SUM aggregate function allows selecting the total for a numeric column.
- MySQL SQRT Functions − This is used to generate a square root of a given number.
- MySQL RAND Function − This is used to generate a random number using MySQL command.
- MySQL CONCAT Function − This is used to concatenate any string inside any MySQL command.
- MySQL DATE and Time Functions − Complete list of MySQL Date and Time-related functions.
- MySQL Numeric Functions − Complete list of MySQL functions required to manipulate numbers in MySQL.
- MySQL String Functions − Complete list of MySQL functions required to manipulate strings in MySQL.