Informatics Practices for Class XI (CBSE)/Introducing MySQL

Note: Initiated based on the MySQL Introduction chapter of the MySQL book.data is stored in a relational database in one or more tables..

What is SQL?

Note: Section based on the SQL Wikibook.

SQL, which is an initialism for Structured Query Language, is a language to request data from a database, to add, update, or remove data from tables within a database, or to manipulate the metadata of the database.

SQL is generally pronounced as the three letters in the name, e.g. ess-cue-ell, or in some people's usage, as the word sequel.

SQL is a declarative language in which the expected result or operation is given without the specific details about how to accomplish the task. The steps required to execute SQL commands are handled transparently by the SQL database. Sometimes SQL is characterized as non-procedural because procedural languages generally require the details of the operations to be specified, such as opening and closing tables, loading and searching indexes, or flushing buffers and writing data to filesystems. Therefore, SQL is considered to be designed at a higher conceptual level of operation than procedural languages because the lower level logical and physical operations aren't specified and are determined by the SQL engine or server process that executes it.

Instructions are given in the form of commands, consisting of a specific SQL command and additional parameters and operands that apply to that command. The simplest example of how to retrieve all of the rows and columns of a table named Customers is:

SELECT * FROM Customers;

The asterisk (*) that follows the SELECT command is a wildcard character that refers to all of the columns of the table. SQL assumes that the whole set of records is to be retrieved unless you provide conditional logic to restrict the result to a subset, which is added using a WHERE clause. Following are a few typical SQL examples for a table named Customers with three columns: CustomerID, LastName, and FirstName.

SELECT LastName, FirstName FROM Customers WHERE CustomerID=99; Returns the last name and first name of the customers that have a CustomerID value of 99. Typically this will return a single row, if it exists, because values such as CustomerID would be defined as a primary key that uniquely identifies one customer and cannot have other rows identified with the same value

SELECT LastName, FirstName FROM Customers ORDER BY LastName, FirstName; Returns an alphabetized list of the last and first names of all customers

INSERT INTO Customers (CustomerID, LastName, FirstName) Values (1, "Doe", "John"); Creates a row with the CustomerID of 1 for John Doe.

UPDATE Customers SET FirstName="Johnny" WHERE CustomerID=1; Changes John Doe's first name to Johnny

DELETE Customers WHERE CustomerID=1; Removes the row where the CustomerID is the value 1

In general, SQL isn't case sensitive and ignores excess whitespace characters, except for the contents of an alphanumeric string. Upper case is frequently used for SQL commands as a matter of style, but this is not a universal convention.

Some databases support stored procedures in which SQL is embedded within another procedural language to manipulate data or tables on the server for more complex operations than allowed by SQL alone, and without necessarily returning data from the server. These procedural languages are usually proprietary to the vendor of the SQL server. For example, Oracle uses its own language PL/SQL while Microsoft SQL Server uses T-SQL. These procedural languages are similar in design and function, but use different syntax that are not interoperable.

SQL can also be embedded within other languages using SQL modules, libraries, or pre-compilers that offer functions to open a path of communication to the SQL server to send commands and retrieve results. Since the execution of an SQL command is a useful service that is performed externally to the host language by the SQL server, the overall processing is divided into two tiers: the client tier and the server tier. The modules and libraries used to provide the connection between the client and server are usually referred to as middleware, particularly when connections are allowed with one interface from the client to various servers from different vendors. ODBC, JDBC, OLEDB are common middleware libraries, among a large number of others specific to a wide range of client hosting operating systems and programming models. The use of SQL has become so prevalent that it would be unusual to find an application language that doesn't support it in some manner.

SQL commands and their modifiers are based upon official SQL standards and certain extensions to that each database provider implements. Commonly commands are grouped into the following categories:

Data Definition Language ( DDL )

• CREATE - Used to create a new table, a view of a table, or other objects in the database.
• ALTER - Used to modify an existing database object, such as a table.
• DROP - Used to delete an entire table, a view of a table or other objects in the database.

Data Control Language ( DCL )

• GRANT - Used to give a privilege to someone.
• REVOKE - Used to take back privileges granted to someone.

Data Manipulation Language ( DML )

• INSERT - Used to create a record.
• UPDATE - Used to change certain records.
• DELETE - Used to delete certain records.

Data Query Language ( DQL )

• SELECT - Used to retrieve certain records from one or more tables.

Why MySQL?

• Free as in Freedom - Released with GPL version 2 license (though a different license can be bought from Sun Microsystems, see below)
• Cost - Free!
• Support - Online tutorials, forums, mailing list (lists.mysql.com), paid support contracts.
• Speed - One of the fastest databases available.
• Functionality - support most of ANSI SQL commands.
• Ease of use - less need of training/retraining.
• Portability - easily import/export from Excel and other databases
• Scalable - Useful for both small as well as large databases containing billions of records and terabytes of data in hundreds of thousands of tables.
• Collaboration - selectively grant or revoke permissions to users.

The MySQL license

MySQL is available under a dual-licensing scheme:

• Under the GNU General Public License, version 2, ("or later" allowed in versions released before 2007): this is a Free (as in freedom), copyleft software license that allows you to use MySQL for commercial and non-commercial purposes in your application, as long as your application is released under the GNU GPL. There is also a "FLOSS Exception" which essentially allows non-GPL but Free applications (such as the PHP programming language, under the PHP license) to connect to a MySQL server. The exception lists a set of free and open-source software license that can be used in addition to the GNU GPL for your MySQL-dependent Free application.
• A so-called "commercial" , paid license, that is, a license where MySQL grants you the right to integrate MySQL with a non-FLOSS application that you are redistributing outside your own organization.

MySQL and its forks

MySQL is Free Software, so some forks and unofficial builds delivering contributions from the community exist.

MariaDB

In 2008 Sun Microsystems bought MySQL. After the acquisition, the development process has changed. The team has started to release new MySQL versions less frequently, so the new code is less tested. There were also fewer contributions from the community.

In 2009 Monty Widenius, the founder of MySQL, left the company and created a new one, called The Monty Program. He started a new fork called MariaDB. The scopes of MariaDB are:

• import all the new code that will be added to the main MySQL branch, but enhancing it to make it more stable;
• clean the MySQL code;
• add contributions from the community (new plugins, new features);
• develop the Maria storage engine;
• adding new features to the server.

There are no public releases of this fork, still. Its license will be the GNU GPLv2 (inherited from MySQL).

Drizzle

In 2008 Brian Aker, chief architect of MySQL, left the project to start a new fork called Drizzle. Sun Microsystems is funding the project. Its characteristics are:

• only a small part of the MySQL code has survived in this fork, the rest being removed: only essential features are implemented in the Drizzle server;
• the survived code has been cleaned;
• Drizzle is modular: many features are or can be implemented as plugins;
• the software is optimized for multi-CPU and multicore 64-bit machines;
• only GNU/Linux and UNIX systems are supported.

There are no public releases of this fork, still. Its main license will be the GNU GPLv2 (inherited from MySQL), but where possible the BSD license is applied.

OurDelta

OurDelta - Builds for MySQL is another fork. It's based on the MySQL main branch and it adds some patches contributed by the community. Some of these patches will be used by MariaDB, too.