As compared to PostgreSQL, Microsoft SQL Server and Oracle Mysql have many strengths. This includes high performance, low cost, easy to configure and learn, portability, and the source code is available
Mysql is fast. From the developer’s benchmark, it is shown that Mysql to be orders of magnitude faster than competition.
Mysql is available at no cost, under an open source license. If required under a commercial licence, it at low cost for your application.
Most modern databases use SQL (Sequential Query Language) and if have used another RDBMS (Relational Database Management System), then you should have no problem adapting to Mysql.
Mysql can be used on many different UNIX systems as well as Microsoft Windows making it portable and platform independent.
Relational databases are, by far, the most commonly used type of database. They depend on sound theoretical basis in relational algebra.
Relational databases are made up of relations, commonly called tables. A table is a table of data just like in an electronic spreadsheet.
The table has columns and rows. Each column has a unique name and contains different data. Each column has an associated data type (more like the data types used in programming; integers, characters etc). These columns can sometimes be called fields or attributes. Each row represents a different entity. For example let’s say that we have a table for books in a library, then each row will represent a different book. These rows have the same attribute because of the tabular format. The rows are also called records of tuples. Each row consists of a set of individual values that correspond to columns. Each value must have the data type specified by its column. We need to have a way of identifying each book. Names aren’t usually a good way of doing this because two books may have the same name. Due to this, we use a key (or primary key) as the identifying column in a table.
A database may have two or more table. To relate these tables to one another we use the primary keys to form a communication between the tables, a communication within the database. A similar primary key in different tables is known as foreign key. This communication forms the relationship hence relational database.
Three basic kinds of relationships exist in a relational database, according to the number of things on each side of the relationship; One-to-one, one-to-many and many-to-many.
One-to-one relationship, there is one of each thing in the relationship.
One-to-many, one row in one table is linked to many rows in another table.
Many-to-many, many rows in one table are linked to many rows in another table.
Summary on creating a relational database
- Give each record in a table a unique identifier (primary key)
- Put each each group of associated data in a table of its own
- Cross reference related information by using the primary key from one table as the foreign key in other tables
- Store only one item of information in each field.
- Don’t repeat yourself.
MYSQL DATABASE SUPPORT.
The Mysql software consists of the Mysql server, several utility programms that assist in the administration of Mysql databases and some supporting software that the Mysql server needs. The heart of the system is the Mysql server.
The Mysql server is the manager of the database system. It handles all the database instructions e.g. creating a new database, adding, updating or data to that database. This can be done by sending messages to the Mysql server via SQL. The Mysql server continuously listens for messages that are directed to it.
All the interaction with the database is accomplished by passing messages to the Mysql server. The Mysql server must be able to understand the instructions that are sent to it, communicate using SQL (the standard language understood by most database management systems). Build an SQL query and send it to the Mysql server.
The only difficulty is when one has no or little understanding of the SQL. But if one has web server applications like XAMMP or WAMP, this can easen things. These application have inbuilt SQL functions already available in the user interface.
Mysql itself is a relational database management system. The Mysql server can manage different databases at the same time. Each database consists of a structure to hold data and the data itself.
A Mysql system can have many users. Each user who needs to use the system, you will need to set up an account and a password. It isn’t compulsory to set up passwords for users, but it is strongly recommended that one sets up passwords for all users that are created for security reasons. And still on security reasons the root user (the user that comes default with each Mysql installation) should generally be used for administration purposes only.
Mysql supports a sophisticated privilege system. A privilege is the right to perform a particular action on a particular object, and is associated with a particular user (similar to file permissions). When you create a user within Mysql, you grant her a set of privileges to specify what she can and cannot do within the system. GRANT and REVOKE commands to give and take away rights to and from Mysql users.
Mysql uses SQL . Structured Query Language is the most standard language for accessing relational database management systems (RDBMS). SQL is used to store and retrieve data to and from a database. It is used in other database systems such as Oracle, PostgreSQL, Sybase, and Microsoft SQL Server. Mysql offers enterprise level SQL features such as subqueries, views and storage procedures allowing the database to be adopted from enterprise level.
Mysql offers many type of mechanisms for managing data, known as storage engines. Mysql supports several engines; MyISAM (default on all operating systems except Windows), MEMORY ( the system memory. Attains the fastest response time possible), InnoDB (default on Windows, a robust transactional storage engine), FEDERATED (creates a local pointer to remote tables in a database network), ARCHIVE (used to compression of enormous amounts of data and uncompressing it only when neccessary), CSV (store data in comma-separated format ), EXAMPLE (a basic template for understanding how storage engines are created),BLACKHOLE (just like MyISAM except that it wont store any data) and MERGE (an aggregation of identical MyISAM tables). Each storage engine bears its own strengths and weaknesses and should be applied selectively to best fit the intended use of your data. Because a single could consist of several tables, Mysql affords one an opportunity to simultaneously use different storage engines in a single database.
Mysql version 3.23.23 added full-text indexing and searching which enhances the performance of mining data from text-based columns (CHAR, VARCHAR,TEXT). This feature also enables you to produce results in order of relevance in accordance with how closely the query matches the row’s indexed textual columns.
Mysql offers query caching. Query caching causes Mysql to store SELECT queries (SELECT ‘DATABASE-NAME’), along with their corresponding results, in memory. As subsequent queries are executed, Mysql compares them against the cached queries; if they match, Mysql forgoes the costly database retrieval and instead simply dumps the cached query result.
From version 3.23.15 to the current version Mysql the replication feature was added. Replication allows a database located within one Mysql server to be duplicated on another Mysql server. Using this feature, just having a single slave database in place can greately increase availability, because it could be immediately brought online if the master database experiences a problem. Also backup is efficient, one can the backup on a slave while the master is online, allowing your application to incur zero down time.
Mysql sports a vast array of security and configuration options, enabling one have total control over each and every aspect of its operation (Mysql’s).
With Mysql configuration options one can control things such as
- The default language, default port, location of Mysql’s data store.
- The amount of memory allocated to threads, the query cache, temporary tables, table joins and index key buffers.
- Various aspects of Mysql’s networking capabilities e.g. how long it will attempt to perform connection before aborting, the maximum allowable packet size and more.
Mysql’s security options allow one to manage things such as
- The total number of queries, updates and connections allowed on hourly basis
- Whether a user must present a valid SSL certificate to connect to the database
- Which actions are available to a user for a given database, table, and even column.
- A privilege system based authentication and authorization (...authorization cannot take place without successful authentication). The privilege verification information is stored in the Mysql database.
- Resource usage in a hosted environment such as an ISP. It is possible to limit the consumption of Mysql resources on per-user basis through max_connection, max_questions, max_updates, max_user_connections.
- Access privileges through GRANT and REVOKE commands. GRANT command is used to assign new privileges to a user or group of users. REVOKE command is responsible for deleting previously granted privileges from a user or group of users
- Mysql built in security features to encrypt (SQL and X509 encryption standard) a connection transferring data over unsecure channels.