A database is a systematic aggregation of informations. A Database Management System is required to hive away, entree, delete, or otherwise organize informations in a database.
MySQL is an unfastened beginning DBMS. You can freely download, modify, and utilize unfastened beginning package without holding to pay any fees or royalty to the original writer.
In this session, you will larn about MySQL, its characteristics, and its advantages over other Relational Database Management Systems ( RDBMS ) . An RDBMS is a database direction system that is based on relational theoretical account as specified by E.F. Codd. In add-on, you will larn about the advantages of MySQL as an unfastened beginning database and utilizing HyperText PreProcessor ( PHP ) with MySQL.
Overview of MySQL
See a library which lends books to its members. Traditionally, the inside informations of books, members, and loaning are maintained manually utilizing legers. As the figure of books additions, managing and seeking for books, members, and loaning inside informations becomes hard. This information can be stored in rows and columns in a tabular array. A database can be created to hive away these tabular arraies. In add-on, a DBMS can be used to pull off the databases.
A DBMS can be defined as a package plan that shops and manages databases. A database is a system used to hive away the informations in a structured format. In other words, database can be defined as an organized aggregation of informations. A DBMS is responsible for pull offing the assorted database operations such as adding, accessing, and processing of informations. A DBMS helps you to pull off informations in two ways:
It provides an interface to pull off informations
It supports connectivity to other applications that can be used to pull off informations
Both, DBMS and RDBMS, perform the same undertaking of hive awaying and pull offing informations. One of the cardinal differences between DBMS and RDBMS is that RDBMS splits big informations into smaller tabular arraies and establishes relationship between the tabular arraies. DBMS shops big sum of informations in a individual tabular array. Besides, the RDBMS is based on a relational theoretical account whereas DBMS is non.
MySQL is an unfastened beginning RDBMS. MySQL uses the standardised Structured Query Language ( SQL ) to pull off the database. MySQL is developed and distributed by MySQL AB, a company founded by the MySQL developers. In 2008, Sun Microsystems acquired MySQL AB. In 2010, Oracle acquired Sun Microsystems and therefore, MySQL is now owned by Oracle Corp.
Features of MySQL
MySQL was designed to accomplish velocity, hardiness, and easiness of usage. The characteristics of MySQL are as follows:
Is written in C and C++
Is tested with different compilers
Is compatible with multiple runing systems
Has support for multiple storage engines ; both transactional and non-transactional
Has Application Programming Interfaces ( APIs ) for accessing MySQL databases available in many linguistic communications, including C, C++ , Eiffel, Java, Perl, PHP, Python, Ruby, and Tcl
Is utilizing multiple meats togss or treating units, if available, for informations processing
Is utilizing multiple processors where available, hiking public presentation
Has ability to deviate memory resources from inactive togss to active togss for faster processing
Has bids and characteristics to recover, update, and delete informations from several tabular arraies
Has support for compatibility to be used as a separate application or as an embedded library
Includes multiple column or informations types including numeral, day of the month and clip, and threading
Includes function of informations types from other databases to MySQL informations types
Includes fixed- and variable-length strings
Commands and Functions:
Has support for all MySQL operators and maps in the SELECT statement and the WHERE clause
Has support for tabular arraies from different databases in one statement
Has support for tabular array and column assumed names
Has support for exposing information about databases, tabular arraies, and indexes utilizing the SHOW bid
Has support for exposing query declaration information utilizing the EXPLAIN bid
Has full support for SQL GROUP BY and ORDER BY clauses, group maps and left and right outer articulations
Has support for the usage of map names as tabular array or column name
Has support for in-built informations encoding and decoding
Has support for user history privileges
Has support for watchword encoding
Scalability and Limits:
Handles big databases that have up to 5 billion rows
Allows up to 64 indexes per tabular array
Allows up to 16 keys per tabular array
Supports connectivity on any platform to MySQL waiter utilizing Transmission Control Protocol/Internet Protocol ( TCP/IP ) sockets
Supports connectivity on Windows NT, 2000, XP, 2003, and Vista utilizing named pipes or shared-memory connexions
Supports connectivity on UNIX systems utilizing UNIX sphere socket files
Displaies mistake messages in linguistic communications, such as Czech, French, German, Japanese, Korean, Norwegian, Polish, and Russian
Supports Unicode and assorted character sets
Allows informations to be stored, sorted, and compared utilizing the chosen character set
Clients and tools:
Provides constitutional support to look into, optimise, and fix tabular arraies
Provides “ mysql ” tool to put to death single SQL commands or SQL bids stored in a file
Provides “ mysqlaccess ” tool to look into host, user, and database privileges
Provides “ mysqladmin ” tool to pull off the database waiter
Provides “ mysqldump ” tool to backup the contents of one ore more MySQL databases to a file
Provides “ mysqlhotcopy ” tool to backup a individual database or tabular array on to the same computing machine
Provides “ mysqlimport ” tool to import informations into a MySQL tabular array from a file
Provides “ mysqlshow ” tool to expose information about the databases, tabular arraies, and columns
Provides “ mysqld_safe ” tool that enables safe start up of the MySQL waiter
Advantages of MySQL Over Other RDBMS
There are many commercial DBMSes such as Oracle, Microsoft SQL Server, and Sybase available in the market. These DBMSes are robust, dependable, and support most of the characteristics that a user wants. However, it is impossible for these databases to vie with MySQL with respects to monetary value, as MySQL is available for free. In add-on, for commercial DBMSes, the initial apparatus cost is more expensive, resource intensive, and clip consuming, whereas with MySQL, this is non the instance. This is one of the cardinal advantages of MySQL.
Besides, as the beginning for MySQL is to the full available, you can custom-make MySQL as required. There are many trouble-shooting techniques, bid aid, and syntax aid that are available. This information is available in web logs, forums, and lists that do non necessitate paid subscriptions. However, trouble-shooting techniques, bid aid, and sentence structure aid for commercial databases may necessitate a paid subscription.
Typically, unfastened beginning package tends to be updated more often than commercial package because many users contribute to its development. As a consequence, new characteristics are available more frequently than for commercial databases.
MySQL provides different versions that work on the different versions of Linux, UNIX, Microsoft, Windows, and other runing systems. MySQL besides supports assorted constitutional and third-party GUI tools for faster and easier design, execution, and disposal.
Following are the other advantages that MySQL offers over other RDBMS:
Reliable: Supports tabular arraies that can hive away and manage big figure of records
Ease of Use: Provides a modular and flexible architecture that makes it easy to pull off and custom-make.
Cross Platform Support: Supports different runing systems, such as Linux, UNIX, and Microsoft Windows.
Positions: Supports positions where information is copied into impermanent or practical tabular arraies during processing. This characteristic ensures informations security.
Stored Procedures: Supports stored processs and maps. This allows you to implement concern logic at the database degree.
Gun triggers: Supports triggers. This characteristic besides enables you to implement concern logic during informations processing.
Comparing MySQL As An Open Source Database With Other RDBMSes
There are many unfastened beginning and commercial databases available today. Some of the popular unfastened beginning databases include mSQL, PostgreSQL, and InstantDB. As mentioned earlier, Oracle, MS SQL Server, and Sybase are some popular commercial databases.
The early editions of MySQL did non back up all the SQL characteristics. For illustration, dealing support and stored processs were non available in the older versions of MySQL. The latest versions of MySQL, nevertheless, provide full SQL and dealing support.
The commercial databases support about all the characteristics that are present in MySQL, but the public presentation of MySQL is better.
One drawback in MySQL is that it does non back up advanced SQL3 characteristics such as object oriented informations types. PostgresSQL supports advanced SQL3 characteristics and is a better pick as an open-source DBMS. However, PostgresSQL has a major disadvantage in its concealed bound of 8K of informations per row.
PostgreSQL is more powerful but MySQL is faster. MySQL does non necessitate a vacuum process as PostgreSQL. Vaccuum process refers to the procedure of optimising the information stored in the database. PostgreSQL withstands higher tonss. The latest versions of PostgreSQL and MySQL support characteristics, such as sub-selects, stored processs, triggers, brotherhoods, and positions. However, older versions of MySQL did non back up all these characteristics. In add-on, because these characteristics are new to MySQL, there are some public presentation issues.
However, MySQL provides more user-friendly bid interface so it is popular among Web developers. Besides, MySQL supports more informations types and maps as compared to mSQL.
InstantDB competes good with MySQL when you consider the different characteristics. The lone characteristic of MySQL that InstantDB is unable to vie with is public presentation. MySQL is faster as compared to InstantDB.
Advantages of PHP In MySQL Environment
A scripting tool enables you to command one or more applications when executed. PHP is a scripting tool designed for Web development. PHP supports implanting books into HTML codification. Developers can utilize PHP books to make HTML Web pages that can read and compose informations from a database.
PHP is a scripting linguistic communication that is executed at run-time. It enables interaction of the application with the database. You can utilize PHP and MySQL together to pull off informations on the Web. PHP is compatible with MySQL. You can besides hive away and pull off information from the database. Figure 1.1 displays the interaction between the client, waiter, and database:
Figure 1.1: Interaction between Client, Server, and the Database
See a database connected to a waiter as shown in figure 1.1. The waiter is connected to several clients. The database, waiter, and client maintain a bipartisan communicating. This system appears to be simple ; nevertheless, it has certain restrictions. For illustration, when a client petitions for informations, the browser sends a petition to the waiter. The waiter locates the information from the database, and returns it to the browser. If several clients make a petition to the waiter for the same information, so the waiter will return the informations to all the clients. This consequences in slow public presentation of the system.
The advantage of utilizing PHP is that the database can be accessed straight through a Web page. In this scenario, the client will bespeak for a PHP file. The PHP preprocessor will link to the database, retrieve the information, convert the information into HTML format, and direct it to the browser.
Following are some illustrations of real-world Websites where databases are used:
Online Ticket Reservation: In an on-line reserve system you can reserve a place utilizing the Internet. Your action updates the backend or the database of this engagement system. You can entree several parts of the database by altering the Uniform Resource Locator ( URL ) .
Message Boardss: On the Internet, message boards are widely present that run on MySQL and PHP. It is an on-line treatment site where messages can be posted. Message boards running on PHP and MySQL are more efficient because you need to update merely one page and the alterations are automatically reflected in others.
Marketing Web sites: See that a big Website is required to be updated. The Website can be updated utilizing few PHP books as the information related to these pages is stored in MySQL database. The PHP books accesses the MySQL database to obtain the information about the pages.
Ad Banners: See a Website where several advertizement streamers are present on the site. These streamers are stored in a database on the waiter. You can name a PHP book to expose each streamer. In order to infix, modify, or cancel a streamer you have to entree the database. A PHP book can be written to choose the database from which the streamer will be displayed. The PHP book would choose and expose the right streamers for the pages on the site.
To run a PHP book, you will necessitate to put in the undermentioned package:
PHP supports different runing systems such as Mac OS X, Linux, UNIX, and Windows.
Both PHP and MySQL are unfastened beginning. This characteristic makes them be effectual as compared to other package merchandises.
MySQL besides supports the bid line interface. This interface enables the PHP page to entree the database and expose the question consequences.
You can utilize PHP books to command the disposal activities of the database. However, it is better to put in a PHPMyAdmin on the waiter. PHPMyAdmin is an administrative interface for MySQL databases. It consists of a set of free books for disposal of the database.
Open Source Software Licensing
There are many types of package licences available. An open-source package licence permits users to read, entree, alteration, and recycle the beginning codification of a package merchandise. Open-source package does non needfully intend free package.
The advantage of utilizing open-source package is that the developer can custom-make as per demands. There is no bound on the customization. Besides, there are assorted trouble-shooting and public presentation tips freely available on the Internet for open-source package. However, open-source licence does non let you to sell customized package. In add-on, you will hold to supply the modified beginning codification when you distribute the package.
However, open-source package must follow with certain conditions and some of them are given below:
The beginning codification must be distributed along with the double star.
The package can be modified as required and redistributed under the same footings as the original package.
When the package is used as a portion of development of other applications, the package must be redistributed with the application without any royalty or fee.
Note: For a elaborate description of the open-source licence footings and conditions, visit: hypertext transfer protocol: //www.opensource.org/docs/osd
Licensing of MySQL
MySQL is available under two licensing strategies. They are:
General Public License ( GPL ) – Applies to developers who use, and/or distribute unfastened beginning package under the GPL.
Commercial License – Applies to developers who merely use MySQL to develop their ain executables and non the beginning codification.
A database shops informations in a structured format. A database direction system is responsible to hive away, entree, and delete informations from a database.
MySQL is an unfastened beginning RDBMS. It was developed and distributed by MySQL AB, which is now owned by Oracle.
There are many commercial RDBMSes available, such as Oracle, Microsoft SQL, and Sybase that support most of the information direction characteristics.
MySQL works on different runing systems, such as Mac OS X, Linux, UNIX, and Windows.
Unlike in traditional DBMSes that are proprietary, in unfastened beginning package, you can modify the beginning codification to custom-make the characteristics.
PHP is a scripting linguistic communication that enables interaction with a database. You can utilize PHP and MySQL to hive away and pull off informations on the Web.
In order to run a PHP book, you will necessitate to put in a Web waiter, PHP and MySQL.
Open-source package licenses allow you to read, entree, alteration, and recycle the beginning codification of a package merchandise.
MySQL is available under General Public License and Commercial License.
Check Your Advancement
The mysqlshow bid shows information about the__________ .
Databases and tabular arraies on the waiter
Tables and columns in a database
Columns and rows in a tabular array
Version of MySQL Server
_____________is an administrative interface for MySQL databases.
To put to death a PHP book, you will necessitate to put in
Web Server, PHP, MySQL
MySQL, Web Server
PHP, Web Server
MySQL allows up to _______ indexes per tabular array.
______________ tool manages users of MySQL.
This page has been deliberately left space