A database is a systematic aggregation of informations. A database direction system is needed to hive away, entree, delete, or otherwise organize informations in a database.
MySQL is an unfastened beginning database direction system.. You can freely download, modify, and utilize unfastened beginning package without holding to pay any fees/royalty to the original writer.
In this chapter, you will larn about MySQL and its advantages over other relational database direction systems ( RDMS ) . In add-on, you will larn about the advantages of MySQL as an unfastened beginning database and of utilizing PHP with MySQL.
What is 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. To keep and recover information fast, this information can be stored in an electronic database and a DBMS can be used to pull off these electronic databases.
A DBMS manages the database. A database is used to hive away the informations in a structured format. A DBMS is responsible for pull offing the operations such as adding, accessing, and treating informations. A DBMS plays a cardinal function in calculating ; as a standalone public-service corporation or as a portion of applications.
MySQL is a relational database direction system ( RDBMS ) , which manages a relational database. A relational database shops information in several tabular arraies alternatively of hive awaying it as a big tabular array. This construction imparts flexibleness to the database. MySQL uses the standardised Structured Query Language ( SQL ) to pull off the database.
MySQL is a popular Open Source database. It 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 so MySQL is now owned by Oracle Corp.
Main Features in MySQL
MySQL was designed to accomplish ends such as velocity, hardiness, and easiness of usage. The characteristics of MySQL are as follows:
Written in C and C++
Tested with different compilers
Cross-platform compatibility, that is, plants on multiple runing systems
Supports multiple storage engines ; both transactional and non-transactional
APIs for accessing MySQL databases available in many linguistic communications, including C, C++ , Eiffel, Java, Perl, PHP, Python, Ruby, and Tcl
Uses kernel togss to accomplish complete multi-threading
Can utilize multiple CPUs where available, hiking public presentation
Dynamic memory cache and usage of togss to apportion memory
Uses a single-sweep multi-join method to let fast declaration of articulations
Waiter can be used as a separate application or as an embedded library
Supports multiple column or informations types including Numeric, Date and Time, and Stringing
Provides for mapping informations types from other databases to MySQL informations types
Supports fixed- and variable-length strings
Commands and Functions:
Supports the usage of all MySQL operators and maps in the SELECT statement and the WHERE clause
Supports the usage of tabular arraies from different databases in one statement
Supports tabular array and column assumed names
Displaies information about databases, tabular arraies, and indexes utilizing the SHOW bid
Displaies query declaration information utilizing the EXPLAIN bid
Provides full support for SQL GROUP BY and ORDER BY clauses and group maps every bit good as left and right outer articulations
Supports the usage of map names as tabular array or column name
Support for in-built informations encoding and decoding
Privileges can be assigned to users
Scalability and Limits:
Handles big databases sizes
Allows up to 64 indexes per tabular array
Allows up to 16 keys per tabular array
On any platform, clients may link to the MySQL waiter utilizing TCP/IP sockets
On Windows NT, 2000, XP, 2003 and Vista, clients may link to the MySQL waiter utilizing named pipes or shared-memory connexions
On UNIX systems, clients may link utilizing UNIX sphere socket files
Displaies error message in 20 linguistic communications including Czech, French, German, Japanese, Korean, Norwegian, Polish, and Russians
Supports Unicode and assorted character sets
Allows informations to be stored, sorted and compared utilizing the chosen character set
Clients and tools:
Provides built in 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 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 utilizing MySQL over the other RDBMS
There are many commercial DBMSessuch as Oracle, Microsoft SQL Server, and Sybase available in the market. These DBMSes are robust and dependable, and support most of the characteristics a user wants.
It is impossible for these databases to vie MySQL on monetary value, as MySQL is available for free downloads. In add-on, for commercial DBMSes, the initial apparatus is more expensive, resource intensive, and clip consuming.
Another factor that adds to the cost is the preparation required on commercial databases. For MySQL, there are many free preparation classs available on the Internet. Besides, you can download MySQL for free and larn to utilize it.
As the beginning is available, you can custom-make MySQL as required. The huge support webs available ( as web logs, forums, and lists ) enables you to entree information and support easy as opposed to the paid support constructions of commercial DBMSes.
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 has different versions available that work on the assorted versions of Linux, UNIX, Microsoft Windows, and other runing systems. MySQL besides offers assorted in-built and third-party GUI tools for faster and easier design, execution, and disposal.
Following are the advantages that MySQL offers over other RDMS:
Reliable: Different users have found MySQL even under really heavy tonss and big database sizes.
Ease of Use: MySQL has a modular and flexible architecture that makes it easy to pull off and custom-make.
Cross Platform Support: MySQL is available on more than twenty different runing systems including Linux, UNIX, and Microsoft Windows.
Contrasting MySQL as an unfastened beginning database with other RDBMS
There are many unfastened beginning and commercial databases available. Some of the popular unfastened beginning databases include mSQL, PostgreSQL, and InstantDB. Similarly, 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 as 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 unfastened beginning DBMS with SQL3 characteristics. 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. PostgreSQL withstands higher tonss. The latest versions of PostgreSQL and MySQL support characteristics 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 with more user-friendly bid interface so it is it popular among web developers.
The databases such as mSQL can non vie with MySQL on many foreparts. For illustration, MySQL has a richer characteristic set of SQL than mSQL. MySQL besides supports more informations types and maps as compared to mSQL.
InstantDB competes good with MySQL when we consider the different characteristics. The lone characteristic of MySQL that InstantDB is unable to vie is public presentation. MySQL is faster as compared to InstantDB.
Advantages of PHP in MySQL environment
PHP is a simple tool designed for web development. It is easy to larn and allows a developer to implement assorted web-programming undertakings, including database-related modus operandis. It offers good flexibleness and public presentation.
PHP is a scripting linguistic communication that is executed at run-time. It enables a user to interact with databases. PHP and MySQL are used together to hive away informations on the Web. PHP is compatible with MySQL, which is one of the most popular database engines. MySQL is free and provides with high public presentation.
Using databases allows a shop of information that is accessed by users. In add-on to this, a user is able to hive away his information in the database. Following diagram will assist us to understand the interaction between the client-server-database.
Figure 1.1 Interaction between Client, Server, and the Database
In the above figure, a waiter is connected to several clients and a database connected with the waiter. These constituents have bipartisan interactions between them. When a client petitions for a HTML file, the browser sends a petition to the waiter. The waiter will happen the requested file and sends the HTML file to the browser. The working of the system above is simple. However, such a system has restrictions. If several users require the same file so the file is sent to all the clients who requested it as a inactive page and HMTL is non able to interact with the waiter. The chief advantage of utilizing PHP is that a Web page is able to entree a database.
Alternatively a client will bespeak for a PHP file. The PHP preprocessor will so change over the file to HTML, which is sent to the user.
Examples where databases are used on a web site are listed below:
Sites that work on database: We are rather familiar with the online reserve system in which the user is able to book a place utilizing the Internet. The backend database of this system gets updated depending on the action of the user. Different parts of the database can be accessed by altering the URL, which is the unvarying resource locater.
Message Boardss: Several message boards on the Internet are run utilizing MySQL and PHP. Such systems are more efficient than those utilizing other public-service corporations. Other systems create a page for each message and provide assorted options. While updating such system would necessitate to alter all the pages. A system utilizing MySQL and PHP would merely necessitate altering one page.
Updating Web sites: See that a big web site is to be updated. A user is able to modify the whole web site utilizing few PHP books. The information related to these pages is stored in MySQl database. The PHP books would entree a MySQL database to entree the information about the pages. For updating the web site, the user would necessitate to merely alter one page.
Banner Rotation: We have several streamers on a site. A PHP book is called for each streamer. A database of streamers is opened and a random streamer is displayed. In order to infix, modify or cancel a streamer we merely have to alter the database. The PHP book would pick the right streamers for the pages on the site.
To run a PHP book a user will necessitate the followers:
PHP needs to be installed
MySQL needs to be installed
PHP is compatible with about all the operating systems.
PHP and MySQL both are unfastened beginning. It is convenient to obtain these for free downloads from the Internet. This characteristic makes it be effectual as compared with others.
If we compare PHP and MySQL against ASP, the codification for seeking a database in ASP has a line at the top which states the figure of lucifers. In order to acquire this figure one needs to rhythm through the records one time to number them, as the RecordCount belongings supports returning -1. The tantamount MySQL map mysql_num_rows worked first clip as advertised, shortening the codification, and the clip taken to run the server-side page.
MySQL provides with command-line map, which allows entree to the database so that user is able to see the question consequences which the PHP page acquires.
PHP books can besides be used to command all the database administrative options but it is better to put in a PHPMyAdmin on the waiter. PHPMyAdmin is an administrative interface for MySQL databases. It consists of set of free books to supply administrative interface.
Licensing issues related to open beginning packages
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. Open beginning normally refers to package that is made available to everyone at no cost under a licence understanding holding a few limitations.
The distribution of open-source package must carry through with the undermentioned standards:
Free Distribution – The licence allows you to sell the package or administer it for free as a portion of your package distribution. You are non required to pay any fee or rayalty against such a distribution.
Beginning Code – The beginning codification of the merchandise is made available along the merchandise. If the beginning codification is non distributed with the package, so it must be made available free or at a sensible cost. In add-on, the beginning codification must be in a signifier that a user is able to modify it.
Derived Works – The licence must let alterations to the package. Besides after the alteration, it must let the redistribution under the same footings as the licence of the original package.
No Discrimination against Persons or Groups – The licence must be applied same for everybody
No Discrimination against Fields of Endeavor – It means that the licence should non curtail the package in a specific field of enterprise.
Distribution of License – The rights attached to the plan must use to all to whom the plan is redistributed without the demand for executing of an extra licence by those parties.
License must non be Specific to a merchandise -The rights attached to the plan must non depend on the plan ‘s being portion of a peculiar package distribution. If the plan is extracted from that distribution and used or distributed within the footings of the plan ‘s licence, all parties to whom the plan is redistributed should hold the same rights as those that are granted in concurrence with the original package distribution.
License must non pollute Other Software – The licence must non put limitations on other package that is distributed along with the accredited package
Integrity of the Author ‘s Source Code – The licence may curtail source-code from being distributed in modified signifier merely if the licence allows the distribution of “ spot files ” with the beginning codification for the intent of modifying the plan at build clip. The licence must explicitly allow distribution of package built from modified beginning codification. The licence may necessitate derived plants to transport a different name or version figure from the original package.
Open Source Software Benefits
The unfastened beginning package are freely available on the Internet for free downloads. Open-source package has become popular due to several grounds. Following are the benefits of Open-Source Software:
Entree to Source Code- Enables user to understand the package at a deep degree. It besides enables to debug the package.
Large Community- Creates an chance for widespread and rapid teamwork on development undertakings.
Low Cost- Available for free downloads from on the Internet.
Broad Rights- Enables to allow licences to utilize, modify and redistribute the unfastened beginning package, which is a major advantage. The commercial package merchandises are distributed merely in binary signifier and may non be modified.
Licensing of MySQL
MySQL package is released under the General Public License ( GPL ) . It may frequently be used for free, but users may necessitate purchasing commercial licences from MySQL AB for certain intent. Licensing options are as follows:
Commercial License- Suppose an organisation wants package to be accessed by the clients or the members in the organisation. In this instance the commercial licence is needed. This is utile when an organisation wants the beginning codification to be secret. Such organisations do non desire to move in conformity with the General Public License. The commercial licence is an understanding with MySQL AB for organisations that do non desire to let go of their application beginning codification. Commercial licensed users get a commercially supported merchandise from with confidences from MySQL. Commercially licensed users are besides free from the demand of doing their ain application unfastened beginning.
Open Source License- Enables users to offer the characteristics added to everyone. Anyone is able to utilize, modify or administer the package. The unfastened licence is free of cost. The status is that the user must do the complete beginning codification available.
MySQL is the most popular Open Source SQL database. It is developed and distributed by MySQL AB, which is a company, founded by the MySQL developers.
The MySQL is a database direction system which manages the database. A database is used to hive away the informations in a structured format. A database direction system is responsible to pull off the operations such as adding, accessing, and treating informations.
MySQL is a relational database direction system, which shops information in several files alternatively of hive awaying it as a big tabular array. This construction imparts flexibleness to the database.
MySQL is Open Source package that enables any one to utilize and modify the beginning codification.
We have many commercial databases available in the market such as Oracle, Microsoft, and Sybase. These commercial databases support most of the characteristics a user wants. These databases fail to vie with MySQL for read-heavy-database applications.
A user is able to modify the beginning codification due to which new characteristics become easy available as compared to the traditional databases.
An open-source database is inexpensive. It is free if you choose the unfastened beginning licence.
MySQL works on different runing systems such as Linux, UNIX, and Windows.
PHP is a scripting linguistic communication. It enables a user to interact with the databases. PHP and MySQL are used together to hive away informations on the Web.
In order to run a PHP we need a Web sever. Besides PHP and MySQL need to be installed.
An open-source package licence permits users to read, entree, alteration, and recycle the beginning codification of a package merchandise.
Check Your Advancement
The mysqlshow tool that displays information about the__________ .
MySQL waiter and the information about the databases and tabular arraies on the waiter
MySQL client and the information about the databases and tabular arraies of the client
All the databases of the client and the waiter
_____________is an administrative interface for MySQL databases.
MySQL package is released under the_________ .
Commercial beginning License
Open beginning License
General Public License
MySQL allows up to _______ indexes per tabular array.
______________ tool manages users of MySQL.
This page has been deliberately left space