Introduction to MySQL
About this tutorial
This is MySQL tutorial. It covers the MySQL database, various mysql command line tools and the SQL language covered by the database engine. It is an introductory tutorial for the beginners.
MySQL database
MySQL is a leading open source database management system. It is a multi-user, multithreaded database management system. MySQL is especially popular on the web. It is one of the parts of the very popular LAMP platform. Linux, Apache, MySQL and PHP. Currently MySQL is owned by Oracle. MySQL database is available on most important OS platforms. It runs under BSD Unix, Linux, Windows or Mac. Wikipedia, YouTube, Facebook use MySQL. These sites manage millions of queries each day. MySQL comes in two versions. MySQL server system and MySQL embedded system.
The development of MySQL begun in 1994 by a Swedish company MySQL AB. Sun Microsystems acquired MySQL AB in 2008. Sun was bought by Oracle in 2010. So today, Oracle corporation is the owner of the MySQL database.
MySQL, PostgreSQL, Firebird, SQLite, Derby and HSQLDB are the most well known open source database systems.
MySQL is developed in C/C++. Except of the C/C++, APIs exist for PHP, Python, Java, C#, Eiffel, Ruby, Tcl or Perl.
Definitions
A relational database is a collection of data organized in tables. There are relations among the tables. The tables are formally described. They consist of rows and columns. SQL (Structured Query Language) is a database computer language designed for managing data in relational database management systems. A table is a set of values that is organized using a model of vertical columns and horizontal rows. The columns are identified by their names. A schema of a database system is its structure described in a formal language. It defines the tables, the fields, relationships, views, indexes, procedures, functions, queues, triggers and other elements. A database row represents a single, implicitly structured data item in a table. It is also called a tuple or a record. A column is a set of data values of a particular simple type, one for each row of the table. The columns provide the structure according to which the rows are composed. A field is a single item that exists at the intersection between one row and one column. A primary key uniquely identifies each record in the table. A foreign key is a referential constraint between two tables. The foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table. A trigger is a procedural code that is automatically executed in response to certain events on a particular table in a database. A view is a specific look on data in from one or more tables. It can arrange data in some specific order, highlight or hide some data. A view consists of a stored query accessible as a virtual table composed of the result set of a query. Unlike ordinary tables a view does not form part of the physical schema. It is a dynamic, virtual table computed or collated from data in the database. A transaction is an atomic unit of database operations against the data in one or more databases. The effects of all the SQL statements in a transaction can be either all committed to the database or all rolled back. An SQL result set is a set of rows from a database, returned by the SELECT statement. It also contains meta-information about the query such as the column names, and the types and sizes of each column as well. An indexis a data structure that improves the speed of data retrieval operations on a database table.
Tables used
Here we will list all the tables, that are used throughout the tutorial.
-- SQL for the Cars table USE mydb; CREATE TABLE IF NOT EXISTS Cars(Id INTEGER PRIMARY KEY, Name VARCHAR(50), Cost INTEGER); INSERT INTO Cars VALUES(1,'Audi',52642); INSERT INTO Cars VALUES(2,'Mercedes',57127); INSERT INTO Cars VALUES(3,'Skoda',9000); INSERT INTO Cars VALUES(4,'Volvo',29000); INSERT INTO Cars VALUES(5,'Bentley',350000); INSERT INTO Cars VALUES(6,'Citroen',21000); INSERT INTO Cars VALUES(7,'Hummer',41400); INSERT INTO Cars VALUES(8,'Volkswagen',21600);
Cars table.
-- SQL for the Customers, Reservations tables USE mydb; CREATE TABLE IF NOT EXISTS Customers(CustomerId INTEGER AUTO_INCREMENT PRIMARY KEY, Name VARCHAR(55)); INSERT INTO Customers(Name) VALUES('Paul Novak'); INSERT INTO Customers(Name) VALUES('Terry Neils'); INSERT INTO Customers(Name) VALUES('Jack Fonda'); INSERT INTO Customers(Name) VALUES('Tom Willis'); CREATE TABLE IF NOT EXISTS Reservations(Id INTEGER AUTO_INCREMENT PRIMARY KEY, CustomerId INTEGER, Day DATE); INSERT INTO Reservations(CustomerId, Day) VALUES(1, '2009-11-22'); INSERT INTO Reservations(CustomerId, Day) VALUES(2, '2009-11-28'); INSERT INTO Reservations(CustomerId, Day) VALUES(2, '2009-11-29'); INSERT INTO Reservations(CustomerId, Day) VALUES(1, '2009-11-29'); INSERT INTO Reservations(CustomerId, Day) VALUES(3, '2009-12-2');
Customers and Reservations.
-- SQL for the Books table
USE mydb;
CREATE TABLE IF NOT EXISTS Books(Id INTEGER PRIMARY KEY,
Title VARCHAR(100), Author VARCHAR(60));
INSERT INTO Books VALUES(1,'War and Peace','Leo Tolstoy');
INSERT INTO Books VALUES(2,'The Brothers Karamazov','Fyodor Dostoyevsky');
INSERT INTO Books VALUES(3,'Paradise Lost','John Milton');
INSERT INTO Books VALUES(4,'Crime and Punishment','Fyodor Dostoyevsky');
INSERT INTO Books VALUES(5,'Cousin Bette','Honore de Balzac');
Books.