MySql tutorial

Features

MySQL is a popular choice of database for use in web applications.MySQL database is the world's most popular open source database because of its fast performance, high reliability, ease of use, and dramatic cost savings.The MySQL Database  improves performance and scalability on multi-processor hardware architectures.

The new replication monitoring and manageability features provide developers and DBAs with improved tools for building high performance, scalable applications. In addition, the MySQL Performance Schema provides low-level insight into MySQL database performance metrics.MySQL Database delivers enterprise features, including:

1.    Improved! Up to 540% faster performance on Windows

2.    Improved! Up to 370% faster performance on Linux

3.    Improved! Better scalabilty on modern, multi-core, multi-CPU hardware

4.    New! Performance Schema for monitoring MySQL server run-time performance

5.    New! Semi-synchronous replication to ensure data consistency and redundancy

6.    New! Replication Heartbeat to immediately uncover replication interruptions

7.    New! Partitioning options for faster lookups

8.    New! Easier development and debugging of stored procedures, functions, and triggers

9.    Reliability requiring little or no intervention to achieve continuous uptime

10.                       Ease of use with "15 minutes to success" installation and configuration

11.                       Low administration with very little database maintenance required

12.                       Replication providing flexible topologies for scale-out and high availability

13.                       Partitioning to improve performance and management of very large database environments

14.                       ACID Transactions to build reliable and secure business critical applications

15.                       Stored Procedures to improve developer productivity

16.                       Triggers to enforce complex business rules at the database level

17.                       Views to ensure sensitive information is not compromised

18.                       Information Schema to provide easy access to metadata

19.                       Pluggable Storage Engine Architecture for maximum flexibility

General RDBMS Architecture

1. Application Layer
2. Logical layer
3. Physical Layer

Application Layer

The application layer represents the interface for all the users of the system; it essentially provides the means by which the outside world can interact with the database server. In general, it has been found that users can be categorized into four main groups:

1.    Sophisticated users interact with the system without using any form of application; they form their requests directly with the use of a database query language.

2.    Specialized users are application programmers who write specialized database applications that do not fit into the traditional data-processing framework.

3.    Native users are unsophisticated users who interact with the system by invoking one of the permanent application programs that have been previously written.

4.    Database Administrators have complete control over the entire database system. They have a wide variety of responsibilities, including schema definition, the granting of access authorization, as well as the specification of integrity constraints.

Logical Layer

The core functionality of the RDBMS is represented in the logical layer of the architecture; it is in this portion of the system that there are a wide variety of vendor specific implementations. However, upon reading a number of general database management texts, it was found that general core logical functionality can indeed be abstracted.

Physical Layer

The RDBMS is responsible for the storage of a variety of information, which is kept in secondary storage and accessed via the storage manager. The main types of data kept in the system are:

1.    Data files: which store the user data in the database.

2.    Data dictionary: which stores metadata about the structure of the database.

3.    Indices which: provide fast access to data items that hold particular values.

4.    Statistical Data: which store statistical information about the data in the database; it is used by the query processor to select efficient ways to execute a query.

5.    Log Information: used to keep track of executed queries such that the recovery manager can use the information to successfully recover the database in the case of a system crash.

Application Layer

The MySQL application layer is where the clients and users interact with the MySQL RDBMS. There are three components in this layer as can be seen in the layered MySQL architecture diagram in Figure 3. These components illustrate the different kinds of users that can interact with the MySQL RDBMS, which are the administrators, clients and query users. The administrators use the administrative interface and utilities. In MySQL, some of these utilities are mysqladmin which performs tasks like shutting down the server and creating or dropping databases, isamchk a nd myisamchk which help to perform table analysis and optimization as well as crash recovery if the tables become damaged, and mysqldump for backing up the database or copying databases to another server. Clients communicate to the MySQL RDBMS through the interface or utilities.

Logical Layer

It was found that MySQL does indeed have a logical architecture that is virtually identical to the one depicted . The MySQL documentation gave an indication as to precisely how these modules could be further broken down into subsystems arranged in a layered hierarchy corresponding to the layered architecture in Garlan and Shaw. 

Query Processor

The vast majority of interactions in the system occur when a user wishes to view or manipulate the underlying data in storage. These queries, which are  specified using a data-manipulation language (ie SQL), are parsed and optimized by a query processor.

Embedded DML Precompiler

When a request is received from a client in the application layer, it is the responsibility of the embedded DML (Data Manipulation Language) precompiler to extract the relevant SQL statements embedded in the client API commands, or to translate the client commands into the corresponding SQL statements. This is the first step in the actual processing of a client application written in a programming language , before compiling the SQL query. The client request could come from commands executed from an application interface (API), or an application program. This is prevalent in all general RDBMS's. MySQL has this component in order to process the MySQL client application request into the format that MySQL understands.

DDL

DDL means Data Definition Language.Data Definition Language deals with database schemas and descriptions of how the data should reside in the database, .Some commands of DDL are:

1.    CREATE - to create table (objects) in the database

2.    ALTER - alters the structure of the database

3.    DROP - delete table from the database

4.    TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed

5.    COMMENT - add comments to the data dictionary

6.    RENAME - rename a table

CREATE TABLE 

Once you have selected the database, we can start creating tables. The CREATE statement is used to create a table in MySQL with constraint. A Constraint is restriction to the behavior of a variable. Sample syntax:

CREATE TABLE <tableName>
(
    fieldName1 dataType(size) [NULL | NOT NULL],
    fieldName2 dataType(size) [NULL | NOT NULL]
);

If 'NULL' is specified, the field is allowed to be left empty. If 'NOT NULL' is specified, the field must be given a value. In the absence of either a 'NULL' or 'NOT NULL', 'NULL' is assumed.

The below example query will help you in creating table:

CREATE TABLE student
(
    studID INT(5),
    name VARCHAR(30),
);

The above query will create the table student with fields ID and Name.

PRIMARY KEY: 

A 'PRIMARY KEY' is a field in a table that uniquely identifies a record. This attribute is used to define the field name to create a primary key. Example :  

fieldName INT UNSIGNED AUTO_INCREMENT PRIMARY KEY

 The 'PRIMARY KEY' is specified after defining the fields in the below example:

CREATE TABLE student
(
    studID INT UNSIGNED AUTO_INCREMENT,
    name VARCHAR(30),
    PRIMARY KEY(studID)
);

We can also create a compound primary key. A compound primary key is where more than one field is used to uniquely identify a record.

Lets create a table for holding student details in a class.

mysql> create table student(studid int(10), name varchar(20), address varchar(40), phone int(10));

Example for CREATE Table:

CREATE TABLE Ankur (
      Sno bigint(21) NOT NULL auto_increment,
      name varchar(32) NOT NULL default '',
      moblilenumber varchar(32) NOT NULL default '',
);

Description: Creating Table of name ankur,  sno, name, mobilenumber  are Different field, and bigint, varchar are Data Types. 21,32 are maximum value to be entered in these fields. 'NOT NULL' means that the field are mandetory. 'auto increment' 

ALTER TABLE 

ALTER TABLE is used to change the structure of an existing table. We can add or delete columns, change the type of existing columns, or rename columns or the table itself. We can also change the comment for the table and type of the table.The Syntax is
ALTER TABLE tbl_name alter_specification [, alter_specification] ...

Alter Specification	Description
Rename	Rename a Table name
Add	Add a new column, key, index
Add First	Add a column First
Add After	Add a column After
Drop	Drop a column, Index, key
Change	Change a column name
Change Type	Change a column type
Modify	Modify a column type

Renaming a Table :

mysql> ALTER TABLE student RENAME class;

 The above query will change the table name from 'student' to 'class'.

Adding a column to a table :

The ADD COLUMN modifier is used to add a column to a table. The following example query adds a field called marks to the student table.

mysql> ALTER TABLE student ADD COLUMN marks INT(10);

We can see the table schema using follwing command by issuing 'desc' or 'description' along with
table name.
mysql> desc student;
+---------+-------------+------+-----+---------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+
| studid  | int(10)     | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| address | varchar(40) | YES  |     | NULL    |       |
| phone   | int(10)     | YES  |     | NULL    |       |
| marks   | int(10)     | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+
mysql> ALTER TABLE student ADD COLUMN marks INT(10) FIRST;

Add a column After

We can also place the new field next to any of the field. The following example query will place the new field immediately after the field name.

mysql> ALTER TABLE student ADD COLUMN marks INT(10) AFTER names;

Delete a column :

The DROP COLUMN is used to delete a column from the table. The syntax is

ALTER TABLE tbl_name DROP col_name;

The following query drops the field marks.

mysql> ALTER TABLE student DROP COLUMN marks;

Change a column name :

When we modify a column, we have to specify the attribute of the column again. The following example renames the name field to stud_name in the student table.

mysql> ALTER TABLE student CHANGE name stud_name VARCHAR(20);

Change a column name :

If we want to change the attribute alone, we can use the same column as in the following example.

mysql> alter table student change name name varchar(40);

Modify a column type :

The modify statement is also used to change the column type in a table, as the previous example. The
below query will modify the column type.

mysql> alter table student modify name varchar(40);

DROP TABLE

The DROP statement is used to delete one or more tables completely from a database.   The syntax is

DROP TABLE tbl_name

  The following example deletes the student table.

mysql> drop table student;

This query will permanently remove or delete the table student. DROP TABLE query drops all fields in the table and deletes the table. Once the DROP TABLE statement is used, we cannot use that table. So, we should be careful with this statement.

RENAME TABLE

The RENAME statement is used to rename one or more tables in a database. The syntax is

RENAME TABLE tbl_name TO new_tbl_name
    [, tbl_name2 TO new_tbl_name2] ...

The following example query renames the student table as class table.

mysql> rename table student to class;
Now we can view the table whether the name is changed by the following query.

mysql> show tables;
+--------------------+
| Tables_in_sample   |
+--------------------+
| class              |
+--------------------+
1 row in set (0.00 sec)

If the query renames more than one table, renaming operations are done from left to right. We can also swap two table names. Let us assume tmp table which does not exists.

Example :

RENAME TABLE emp1 TO tmp,
             emp2 TO emp1,
             tmp TO emp2;

We can also use RENAME TABLE to move a table from one database to another.

Example :

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;

Truncate Table

'TRUNCATE TABLE' is better than a delete statement as it drops then recreates the table.'TRUNCATE TABLE' is faster on large tables and more importantly can be used as part of a transaction.Remove the data from the original table using a TRUNCATE statement.Syntax:

TRUNCATE TABLE <table_name>

Example:

mysql> TRUNCATE TABLE employee;

DML

Data Manipulation Language (DML) statements are used for managing data within tables. Some commands of DML are:

1.    SELECT - retrieve data from the a database

2.    INSERT - insert data into a table

3.    UPDATE - updates existing data within a table

4.    DELETE - deletes all records from a table, the space for the records remain

5.    MERGE - UPSERT (insert or update)

6.    CALL - call a PL/SQL subprogram

7.    LOCK TABLE - control concurrency

SELECT 

SELECT statement is used to form queries for extracting information out of the Database

SELECT <attribute>, …..,
<attribute n> FROM <table name>;

Example:

SELECT StudID, Name FROM STUDENT;

INSERT

INSERT query is used for inserting new rows or data into an existing table. The Insert syntax is

INSERT INTO <table name>
VALUES (<value 1>, ... <value n>);

Example:

INSERT INTO STUDENT VALUES (1001, ‘Ram’);

The inserted values must match the table structure exactly in the number of attributes and the data type of each attribute. Character type values are always enclosed in single quotes; number values are never in quotes; date values are often (but not always) in the format ‘yyyy-mm-dd’ (for example, ‘2006-11- 30’).

If we only want to insert selected fileds in table...

UPDATE 

 The UPDATE query is used to change or modify the existing values in a table. The Update Syntax is

UPDATE tbl_name SET
col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_condition];

The UPDATE query updates the columns of existing rows in a table with the new values. The SET clause
is used to indicate which columns to be modified. The WHERE clause is used to specify the conditions
that identify which rows to be updated.

The following example will set the address of the student to a new address.

mysql> update student set address='welling street' where address='victoria street';

But this will set all the address of the students who ever lives in victoria street will be changed to
welling street.Suppose if we want to set the address of a single student to a new address then we can
choose the below option.

mysql> update student set address='welling street' where name='jack';

If we want to change a students mark we can use the below statement.

mysql> update student set marks=100 where name='david';

This can also be rewritten as the following.

mysql> update student set marks=marks+2 where name='david';

In UPDATE statement we can also use the arithmetic operations.

DELETE 

The deleting query is used to delete the values from a table. The syntax is

DELETE FROM tbl_name
[WHERE where_condition];

The DELETE statement deletes the rows from tbl_name and returns the number of rows deleted. The
WHERE clause is used to specify the conditions that identify which rows to be deleted. If the DELETE
statement contains without WHERE clause, all rows will be deleted.

Now lets see an example for DELETE statement.

mysql> delete from student where name='michael';

The above example will delete the record of the student Michael from the table.

We can also delete all the values in the table as the following query.

mysql> delete from student;

The above example will delete all the records from the student table.

Truncate VS Delete

MERGE TABLES

mysql> CREATE TABLE a1(i int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);
mysql> CREATE TABLE a2 LIKE a1;
mysql> INSERT INTO a1 VALUES(2);

mysql> INSERT INTO a2 VALUES(1);
mysql> CREATE TABLE am(i int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY) type=merge union(a1,a2) insert_method=last;
mysql> INSERT INTO am VALUES(NULL);

mysql> SELECT * FROM am;

+---+

| i |

+---+

| 1 |

| 2 |

| 3 |

+---+

3 rows IN SET (0.00 sec)

example:

CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,message CHAR(20)) ENGINE=MyISAM;
CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,message CHAR(20));
INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');

CREATE TABLE total(a INT NOT NULL AUTO_INCREMENT,message CHAR(20),INDEX(a))ENGINE=MERGE UNION=(t1,t2) ;

DCL

Data Control Language includes commands such as GRANT, and mostly concerns with rights, permissions and other controls of the
database system. The DCL statements are:

1.    GRANT :Use to grant privileges to other users or roles.

2.    REVOKE :Use to take back privileges granted to other users and roles.

3.     

Grant

Grant is use to grant privileges on tables, view, procedure to other users or roles.Data Control Language includes commands such as GRANT, and mostly concerns with rights, permissions and other controls of the database system.
Syntax:

GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY 'password']
[, user_name [IDENTIFIED BY 'password'] ...]
[WITH GRANT OPTION]

Suppose you own emp table. Now you want to grant select,update,insert privilege on this table to other user “SAMI”.

grant select, update, insert on emp to sami;

Suppose you want to grant all privileges on emp table to sami. Then

grant all on emp to sami;

Suppose you want to grant select privilege on emp to all other users of the database. Then

grant select on emp to public;

Suppose you want to grant update and insert privilege on only certain columns not on all the columns then include the column names in grant statement. For example you want to grant update privilege on ename column only and insert privilege on empno and ename columns only. Then give the following statement

grant update (ename),insert (empno, ename)  on emp to sami;

 To grant select statement on emp table to sami and to make sami be able further pass on this privilege you have to give WITH GRANT OPTION clause in GRANT statement like this.

grant select on emp to sami with grant option;

 
GRANT is used to add a new user to the database. This user will be used to access the ankur database:

GRANT usage ON *.* TO ankur@localhost
IDENTIFIED BY 'ankur';

his will create a new user named ankur, capable of connecting to the MySQL database server via the host localhost using the password
Keep in mind that this only grants connection privileges. It will not allow the user to do anything with the MySQL server! Go ahead and switch to the mysql database and execute the following query:

mysql>SELECT * FROM user;

assume that the administrator wanted to grant user ankur with SELECT, INSERT, UPDATE and DELETE privileges for the widget database. This is accomplished using the following GRANT command:

GRANT SELECT, INSERT, UPDATE, DELETE ON widgets.* TO ankur@localhost;

REVOKE

Use to revoke privileges already granted to other users.This statement is used to revoke the permission (INSERT,SELECT,UPDATE,DELETE) on a specific table from different user accounts.

Syntax:

REVOKE {ALL/SPECIFIC PERMISSIONS} ON TABLENAME FROM USER ACCOUNT (S) [CASCADE]

CASCADE: Using this option we can destroy the communication link between user account more over from the main user it self we can revoke the permission from all sub users.

For example to revoke select, update, insert privilege you have granted to Sami then give the following statement.

revoke select, update, insert on emp from sami;

To revoke select statement on emp granted to public give the following command.

revoke select on emp from public;

To revoke update privilege on ename column and insert privilege on empno and ename columns give the following revoke statement.

 revoke update, insert on emp from sami;

Note :You cannot take back column level privileges. Suppose you just want to take back insert privilege on ename column then you have to
first take back the whole insert privilege and then grant privilege on empno column.

One point to keep in mind is that while REVOKE can remove all privileges (including connection privileges) from a user, it does not explicitly remove that user from the privilege tables. To illustrate this, consider the following command:

REVOKE ALL PRIVILEGES ON widgets.* FROM ankur@localhost;

While this would result in all privileges being revoked from the user ankur, it would not delete the relevant rows from the privilege tables! If completely removing the user from the database is the intention, the rows would have to be removed using the delete command, as follows:

DELETE FROM user WHERE user = 'ankur';

SHOW GRANTS FOR 'ankur'@'localhost';

GRANT RELOAD, PROCESS ON *.* TO 'ankur'@'localhost'

ROLES

A role is a group of Privileges. A role is very handy in managing privileges, Particularly in such situation when number of users should have the same set of privileges.

Syntax:

For example you have four users :Sami, Scott, Ashi, Tanya in the database. To these users you want to grant select ,update privilege on emp table, select,delete privilege on dept table.

To do this first create a role by giving the following statement

create role clerks

Then grant privileges to this role.

grant select,update on emp to clerks;

grant select,delete on dept to clerks;

Now grant this clerks role to users like this

grant clerks to sami, scott, ashi, tanya ;

Now Sami, Scott, Ashi and Tanya have all the privileges granted on clerks role.

Suppose after one month you want grant delete on privilege on emp table all these users then just grant this privilege to clerks role and automatically all the users will have the privilege.

grant delete on emp to clerks;

If you want to take back update privilege on emp table from these users just take it back from clerks role.

revoke update on emp from clerks;

 

To Drop a role

Drop role clerks;

LISTING INFORMATION ABOUT PRIVILEGES

To see which table privileges are granted by you to other users.

SELECT * FROM USER_TAB_PRIVS_MADE

To see which table privileges are granted to you by other users

SELECT * FROM USER_TAB_PRIVS_RECD;

To see which column level privileges are granted by you to other users.

SELECT * FROM USER_COL_PRIVS_MADE

To see which column level privileges are granted to you by other users

SELECT * FROM USER_COL_PRIVS_RECD;

To see which privileges are granted to roles

SELECT * FROM USER_ROLE_PRIVS;

Hierarchy of Conditions

     WHERE
       |
    GROUP BY
    |     |
HAVING    ORDER BY
    |     |
     LIMIT

GROUP BY cannot go before the WHERE condition.
LIMIT cannot go before HAVING.
HAVING and ORDER BY are at the same level.

Contacation

Example:

+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id   | first_name | last_name | start_date | end_date   | salary  | city      | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
|    1 | Jason      | Martin    | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto   | Programmer  |
|    2 | Alison     | Mathews   | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester      | |   
+------+------------+-----------+------------+------------+---------+-----------+-------------+

SELECT concat(First_Name, " ",Last_Name) AS Name FROM Employee;

+----------------+
| Name           |
+----------------+
| Jason Martin   |
| Alison Mathews |
+----------------+
8 rows in set (0.00 sec)

DISTINCT

Retrieve each unique output record just once by adding the keyword DISTINCT

CREATE PROCEDURE

Syntax:
The general syntax of Creating a Stored Procedure is :

CREATE PROCEDURE proc_name ([proc_parameter[......]]) routine_body

proc_name : procedure name
proc_parameter : [ IN | OUT | INOUT ] param_name type
routine_body : Valid SQL procedure statement

The parameter list is available with in the parentheses. Parameter can be declared to use any valid data type, except that the COLLATE attribute cannot be used. By default each parameter is an IN parameter. For specifying other type of parameter used the OUT or INOUT keyword before the parameter name.
An IN parameter is used to pass the value into a procedure. The procedure can be change the value but when the procedure return the value then modification is not visible to the caller. An OUT parameter is used to pass the value from the procedure to the caller but its visible to the caller. An INOUT parameter is initialized by the caller and it can be modified by the procedure, and any change made by the procedure is visible to the caller.
For each OUT or INOUT parameter you have to pass a user –defined variable because then the procedure returns the value then only you can obtain it values. But if you invoking the procedure from the other procedure then you can also pass a routine parameter or variable as an IN or INOUT parameter.
The routine_body contains the valid SQL procedure statement that can be a simple statement like SELECT or INSERT or they can be a compound statement written using BEGIN and END. Compound statement can consists declarations, loops or other control structure. Now we are describing you a example of a simple stored procedure which uses an OUT parameter. It uses the mysql client delimiter command for changing the statement delimiter from ; to // till the procedure is being defined.Example :

CREATE PROCEDURE Sproc(OUT p1 INT)SELECT COUNT(*) INTO p1 FROM ankur;

calling that procedure:

CALL ankur(@a);

display result of that procedure:

select @a;

Output

+------+
| @a   |
+------+
| 5    |
+------+