A database is a systematic collection of data. A database management system is needed to store, access, delete, or elsewhere set up data in a databases.
MySQL is an open source databases management system. . You may freely download, alter, and use wide open source software and never have to pay any fees/royalty to the initial author.
In this section, you will learn about MySQL and its own advantages over other relational data source management systems (RDMS). In addition, you will learn about the features of MySQL as an open up source databases and of using PHP with MySQL.
What is MySQL?
Consider a collection, which lends books to its people. Traditionally, the details of books, users, and lending are maintained physically using ledgers. as the number of books increases, taking care of and searching for books, members and lending details becomes difficult. To maintain and get information fast, this information can be stored within an electronic repository and a DBMS may be used to manage these digital databases.
A DBMS manages the databases. A database can be used to store the data in a structured format. A DBMS is responsible for managing the procedures such as adding, accessing, and digesting data. A DBMS performs a central role in computing; as a standalone utility or as part of applications.
MySQL is a relational repository management system (RDBMS), which manages a relational database. A relational data source stores information in several tables instead of storing it as a huge table. This composition imparts flexibility to the repository. MySQL uses the standardized Structured Query Dialect (SQL) to control the databases.
MySQL is a popular Open Source databases. It really is developed and written by MySQL AB, a business founded by the MySQL builders. In 2008, Sunshine Microsystems bought MySQL AB. In 2010 2010, Oracle attained Sun Microsystems therefore MySQL is currently held by Oracle Corp.
Main Features in MySQL
MySQL was designed to achieve goals such as acceleration, robustness, and ease of use. The top features of MySQL are the following:
Technical Features:
Written in C and C++
Tested with different compilers
Cross-platform compatibility, that is, works on multiple operating systems
Supports multiple storage area engines; both transactional and non-transactional
APIs for being able to access MySQL directories available in many languages, including C, C++, Eiffel, Java, Perl, PHP, Python, Ruby, and Tcl
Uses kernel threads to attain complete multi-threading
Can use multiple CPUs where available, maximizing performance
Dynamic storage cache and use of threads to allocate memory
Uses a single-sweep multi-join solution to allow fast quality of joins
Server can be used as another application or as an inserted library
Column Types:
Supports multiple column or data types including Numeric, Particular date and Time, and String
Provides for mapping data types from other databases to MySQL data types
Supports set- and variable-length strings
Commands and Functions:
Supports the use of most MySQL providers and functions in the SELECT declaration and the WHERE clause
Supports the use of dining tables from different directories in a single statement
Supports table and column aliases
Displays information about directories, dining tables, and indexes using the SHOW command
Displays query quality information using the EXPLAIN command
Provides full support for SQL GROUP BY and ORDER BY clauses and group functions as well as remaining and right outer joins
Supports the utilization of function labels as stand or column name
Security:
Support for in-built data encryption and decryption
Privileges can be given to users
Password encryption
Scalability and Limitations:
Handles large databases sizes
Allows up to 64 indexes per table
Allows up to 16 keys per table
Connectivity:
On any platform, clients may connect to the MySQL server using TCP/IP sockets
On Windows NT, 2000, XP, 2003 and Vista, clients may connect to the MySQL server using called pipes or shared-memory connections
On UNIX systems, clients may connect using UNIX domains socket files
Localization:
Displays error message in 20 dialects including Czech, French, German, Japanese, Korean, Norwegian, Polish, and Russians
Supports Unicode and various character sets
Allows data to be stored, sorted and compared using the chosen character set
Clients and tools:
Provides built-in support to check on, improve, and repair tables
Provides "mysql" tool to perform individual SQL directions or SQL directions stored in a file
Provides "mysqlaccess" tool to check host, individual, and databases privileges
Provides "mysqladmin" tool to control the databases server
Provides "mysqldump" tool to backup the material of one ore more MySQL databases to a file
Provides "mysqlhotcopy" tool to backup a single database or stand on to the same computer
Provides "mysqlimport" tool to import data into MySQL stand from a file
Provides "mysqlshow" tool to show information about the directories, desks, and columns
Provides "mysqld_safe" tool that enables safe set up of the MySQL server
Advantages of using MySQL above the other RDBMS
There are extensive commercial DBMSessuch as Oracle, Microsoft SQL Server, and Sybase available for sale. These DBMSes are powerful and reliable, and support most of the features a user wishes.
It is impossible for these directories to remain competitive MySQL on price, as MySQL is designed for free downloads. Furthermore, for commercial DBMSes, the initial setup is more costly, resource rigorous, and frustrating.
Another factor that increases the cost is the training required on commercial directories. For MySQL, there a wide range of free classes available on the Internet. Also, you can download MySQL free of charge and learn to use it.
As the source can be obtained, you can customize MySQL as required. The great support systems available (as sites, message boards, and lists) permits you to gain access to information and support easily as opposed to the paid support structures of commercial DBMSes.
Typically, open source software is commonly updated more often than commercial software because many users donate to its development. As a result, new features are available more regularly than for commercial directories.
MySQL has different versions available that work on the various versions of Linux, UNIX, Microsoft Glass windows, and other os's. MySQL also offers various in-built and third-party GUI tools for faster and easier design, implementation, and administration.
Following will be the advantages that MySQL offers over other RDMS:
Reliable: Different users have found MySQL even under very heavy loads and large repository sizes.
Ease of Use: MySQL has a modular and flexible architecture that means it is easy to manage and customize.
Cross Platform Support: MySQL is available on more than twenty different os's including Linux, UNIX, and Microsoft House windows.
Contrasting MySQL as an open source databases with other RDBMS
There are extensive open source and commercial directories available. A number of the popular open up source databases include mSQL, PostgreSQL, and InstantDB. In the same way, Oracle, MS SQL Server, and Sybase are some popular commercial directories.
The early editions of MySQL did not support all the SQL features. For example, deal support and stored procedures weren't available in the more aged types of MySQL. The latest versions of MySQL, however, provide full SQL and business deal support.
The commercial databases support almost all the features as MySQL, but the performance of MySQL is way better.
One drawback in MySQL is that it generally does not support advanced SQL3 features such as object focused data types. PostgresSQL supports advanced SQL3 features and it is a much better choice as an open source DBMS with SQL3 features. However, PostgresSQL has a major disadvantage in its concealed limit of 8k of data per row.
PostgreSQL is better but MySQL is faster. MySQL does not desire a vacuum treatment as PostgreSQL. PostgreSQL withstands higher tons. The latest variations of PostgreSQL and MySQL support features as sub-selects, stored techniques, sets off, unions, and views. However, old variants of MySQL did not support all these features. Furthermore, because these features are not used to MySQL, there are a few performance issues.
However, MySQL provides with an increase of user-friendly command interface so it could it be popular among web designers.
The databases such as mSQL cannot compete with MySQL on many fronts. For example, MySQL has a richer feature set of SQL than mSQL. MySQL also facilitates more data types and functions when compared with mSQL.
InstantDB competes well with MySQL when we consider the different features. The only real feature of MySQL that InstantDB struggles to contend is performance. MySQL is faster when compared with InstantDB.
Advantages of PHP in MySQL environment
PHP is a straightforward tool suitable for web development. It is easy to learn and allows a creator to put into action various web-programming tasks, including database-related regimens. It provides good flexibility and performance.
PHP is a scripting language that is performed at run-time. It permits a end user to interact with databases. PHP and MySQL are being used together to store data on the net. PHP is compatible with MySQL, which is one of the most popular repository engines. MySQL is free and with high performance.
Using directories allows a store of information that is utilized by users. Furthermore, a user is able to store his information in the database. Following diagram will help us to comprehend the interaction between your client-server-database.
Figure 1. 1 Interaction between Client, Server, and the Database
In the above mentioned physique, a server is connected to many clients and a data source connected with the server. These components have two-way interactions between them. When a client demands for a HTML file, the browser delivers a demand to the server. The server will see the requested file and delivers the HTML file to the web browser. The working of the system above is easy. However, such something has restrictions. If several users require the same record then the data file is delivered to all the customers who wanted it as a static webpage and HMTL is not able to interact with the server. The primary advantage of using PHP is a Web page can access a repository.
Alternatively a customer will request for a PHP data file. The PHP preprocessor will convert the file to HTML, which is sent to an individual.
Examples where directories are being used on an online site are listed below:
Sites that work on repository: We are quite familiar with the web reservation system where the user can book a seat utilising the web. The backend database of the system gets updated depending on action of an individual. Different parts of the repository can be utilized by changing the URL, which is the standard source of information locator.
Message Boards: Several message boards on the Internet are run using MySQL and PHP. Such systems are better than those using other resources. Other systems create a page for each message and offer various options. While updating such system would have to change all the web pages. Something using MySQL and PHP would just require changing one page.
Updating Websites: Consider that a big website is to be updated. A consumer can modify the whole website using few PHP scripts. The information related to these web pages is stored in MySQl data source. The PHP scripts would gain access to a MySQL database to access the information about the internet pages. For updating the web site, the user would have to just change one site.
Banner Rotation: We've several banners on a site. A PHP script is named for each and every banner. A database of banners is exposed and a random banner is shown. To be able to insert, change or erase a banner we have to change the database. The PHP script would select the correct banners for the internet pages on the site.
To operate a PHP script a end user will need the next:
Web server
PHP needs to be installed
MySQL needs to be installed
PHP works with with virtually all the operating systems.
PHP and MySQL both are available source. It really is convenient to obtain these for free downloads from the web. This feature helps it be cost effective as compared with others.
If we compare PHP and MySQL against ASP, the code for searching a database in ASP has a lines at the top which states the number of matches. To be able to get this number one needs to pattern through the details once to count number them, as the RecordCount property retains returning -1. The equivalent MySQL function mysql_num_rows performed first time as advertised, shortening the code, and the time taken to run the server-side site.
MySQL provides with command-line function, which allows usage of the repository so that individual can view the query results that your PHP page acquires.
PHP scripts can also be used to control all the databases administrative options but it is best to install a PHPMyAdmin on the server. PHPMyAdmin is an administrative program for MySQL directories. It includes group of free scripts to provide administrative software.
Licensing issues related to open source softwares
There are various kinds of software licenses available. An open-source software license permits users to learn, gain access to, change, and reuse the source code of your software product. Open-source software does not indicate free software. Start source commonly refers to software that is manufactured open to everyone free under a license agreement creating a few restrictions.
The distribution of open-source software must gratify with the following criteria:
Free Distribution - The certificate allows you to sell the program or spread it free of charge as a part of your software distribution. You aren't required to pay any charge or rayalty against such a circulation.
Source Code - The foundation code of the merchandise is manufactured available along the merchandise. If the foundation code is not allocated with the program, then it must be made available free or at an acceptable cost. In addition, the source code must maintain a form that a user can improve it.
Derived Works - The certificate must allow adjustments to the program. Also following the changes, it must allow the redistribution under the same conditions as the permit of the original software.
No Discrimination against Persons or Categories - The license must be applied same for everybody
No Discrimination against Domains of Effort - This means that the certificate should not limit the software in a specific field of endeavor.
Distribution of Certificate - The rights attached to the program must apply to all to whom this program is redistributed without the need for execution of an additional license by those people.
License must not be Specific to something -The rights attached to the program should never rely upon the program's being part of a particular software syndication. If the program is extracted from that syndication and used or distributed within the terms of the program's certificate, all functions to whom this program is redistributed must have the same privileges as those that are granted with the original software syndication.
License should never contaminate Other Software - The license should never place restrictions on other software that is allocated combined with the licensed software
Integrity of the Author's Source Code - The certificate may limit source-code from being distributed in revised form only if the permit allows the distribution of "patch data files" with the source code for the purpose of modifying this program at build time. The license must explicitly allow distribution of software built from changed source code. The certificate may require produced works to transport a new name or version amount from the initial software.
Open Source Software Benefits
The wide open source software are readily available on the net free of charge downloads. Open-source software has become popular due to many reasons. Following will be the advantages of Open-Source Software:
Access to Source Code- Enables end user to understand the software at a profound level. In addition, it enables to debug the software.
Large Community- Creates a chance for wide-spread and rapid teamwork on development tasks.
Low Cost- Designed for free downloads from on the web.
Broad Protection under the law- Enables to grant licenses to make use of, enhance and redistribute the available source software, which is a major advantages. The commercial software products are sent out only in binary form and might not be altered.
Licensing of MySQL
MySQL software is released under the General Public License (GPL). It may often be utilized free of charge, but users may require buying commercial licenses from MySQL Abdominal for certain goal. Licensing options are the following:
Commercial Certificate- Suppose an organization wishes software to be utilized by the customers or the customers in the business. In cases like this the commercial permit is needed. This is useful when a business wants the source code to be secret. Such organizations do not need to act in accordance with the General Public Permit. The commercial certificate is an agreement with MySQL Belly for organizations that do not want release a their program source code. Commercial licensed users get a commercially backed product from with assurances from MySQL. Commercially certified users are also clear of the requirement of making their own application wide open source.
Open Source License- Enables users to offer the features put into everyone. Anyone is able to use, improve or distribute the software. The open license is free of cost. The condition is that the user must make the complete source code available.
Summary
MySQL is typically the most popular Open up Source SQL repository. It really is developed and distributed by MySQL AB, which is a company, founded by the MySQL designers.
The MySQL is a databases management system which manages the data source. A database is employed to store the info in a organized format. A repository management system is in charge to control the procedures such as adding, accessing, and producing data.
MySQL is a relational repository management system, which stores information in several files instead of storing it as a big table. This structure imparts versatility to the data source.
MySQL is Start Source software that enables any one to make use of and modify the foundation code.
We have many commercial databases available in the market such as Oracle, Microsoft, and Sybase. These commercial databases support most of the includes a user wishes. These databases fail to contend with MySQL for read-heavy-database applications.
A user can modify the source code scheduled to which new features become common as compared to the traditional directories.
An open-source data source is cheap. It really is free if you select the open source certificate.
MySQL works on different os's such as Linux, UNIX, and Windows.
PHP is a scripting dialect. It permits a end user to interact with the databases. PHP and MySQL are used collectively to store data on the net.
In order to perform a PHP we need a Web sever. Also PHP and MySQL have to be installed.
An open-source software permit permits users to read, access, change, and reuse the source code of an software product.
Check Your Progress
The mysqlshow tool that displays information about the__________.
MySQL server and the info about the directories and dining tables on the server
MySQL customer and the info about the databases and dining tables of the client
All the databases of your client and the server
_____________is an administrative interface for MySQL databases.
mysqladmin
PHPMyAdmin
admin
MySQL software is released under the_________.
Commercial source License
Open source License
General Community License
MySQL allows up to _______ indexes per stand.
16
32
8
______________ tool manages users of MySQL.
mysqladmin
mysqlaccess
mysql
mysqlshow
This web page has been intentionally kept blank
