DDL: Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

    * CREATE - to create objects in the database
    * ALTER - alters the structure of the database
    * DROP - delete objects from the database
    * TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
    * COMMENT - add comments to the data dictionary
    * RENAME - rename an object
    * USE -  To use a database

DML: Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

    * SELECT - retrieve data from the a database
    * INSERT - insert data into a table
    * UPDATE - updates existing data within a table
    * DELETE - deletes all records from a table, the space for the records remain
    * MERGE - UPSERT operation (insert or update)
    * CALL - call a PL/SQL or Java subprogram
    * EXPLAIN PLAN - explain access path to data
    * LOCK TABLE - control concurrency

DCL: Data Control Language (DCL) statements. Some examples:

    * GRANT - gives user's access privileges to database
    * REVOKE - withdraw access privileges given with the GRANT comman

TCL: Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

    * COMMIT - save work done
    * SAVEPOINT - identify a point in a transaction to which you can later roll back
    * ROLLBACK - restore database to original since the last COMMIT
    * SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use



JOIN:
=======================================================================
SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables.

# INNER JOIN: Return rows when there is at least one match in both tables
# LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
# RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
# FULL JOIN: Return rows when there is a match in one of the tables
========================================================================
SQL INNER JOIN:
The INNER JOIN keyword return rows when there is at least one match in both tables

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

==========================================================================
SQL LEFT JOIN:
The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastNam

===========================================================================
SQL RIGHT JOIN:
The RIGHT JOIN keyword Return all rows from the right table (table_name2), even if there are no matches in the left table (table_name1).

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

========================================================================
SQL FULL JOIN:
The FULL JOIN keyword return rows when there is a match in one of the tables

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

=========================================================================
The SQL UNION Operator:
The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

SELECT E_Name FROM Employees_Norway
UNION
SELECT E_Name FROM Employees_USA

To get duplicate records use the follwing syntax;
SELECT E_Name FROM Employees_Norway
UNION ALL
SELECT E_Name FROM Employees_USA

=========================================================================
The SQL SELECT INTO:
The SELECT INTO statement selects data from one table and inserts it into a different table. 

SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_tablename

==========================================================================
Indexes:
Indexes allow the database application to find data fast; without reading the whole table.

An index can be created in a table to find data more quickly and efficiently.

The users cannot see the indexes, they are just used to speed up searches/queries.

Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on columns (and tables) that will be frequently searched against.

CREATE INDEX index_name
ON table_name (column_name)

SQL CREATE UNIQUE INDEX Syntax:
Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:

CREATE INDEX PIndex
ON Persons (LastName, FirstName)

CREATE INDEX part_of_name ON customer (name(10));

===============================================================================
Fulltext Search from multiple columns:
http://dev.mysql.com/doc/refman/5.0/en/fulltext-query-expansion.html
http://onlamp.com/pub/a/onlamp/2003/06/26/fulltext.html

alter table news add fulltext (title,short_desc,full_desc);

select news_id, title from news where match(title,short_desc,full_desc) against ('standards');

select news_id, title,short_desc,full_desc,match(title,short_desc,full_desc) against ('stand') as score from news where match(title,short_desc,full_desc) against ('korea')

Restrictions

A few restrictions affect MySQL FULLTEXT indices. Some of the default behaviors of these restrictions can be changed in your my.cnf or using the SET command.

    * FULLTEXT indices are NOT supported in InnoDB tables.
    * MySQL requires that you have at least three rows of data in your result set before it will return any results.
    * By default, if a search term appears in more than 50% of the rows then MySQL will not return any results.
    * By default, your search query must be at least four characters long and may not exceed 254 characters.
    * MySQL has a default stopwords file that has a list of common words (i.e., the, that, has) which are not returned in your search. In other words, searching for the will return zero rows.
    * According to MySQL's manual, the argument to AGAINST() must be a constant string. In other words, you cannot search for values returned within the query.

Altering FULLTEXT's Default Behavior

There are several ways to alter the default behavior of FULLTEXT. MySQL has some tips for fine tuning the FULLTEXT search, but the details are a little sparse. The most common problem is the four character minimum word length on queries. Before we go over that, let's review the variables associated with the FULLTEXT searching.

mysql> SHOW VARIABLES LIKE 'ft%';
+--------------------------+----------------+
| Variable_name            | Value          |
+--------------------------+----------------+
| ft_boolean_syntax        | + -><()~*:""&| |
| ft_min_word_len          | 2              |
| ft_max_word_len          | 254            |
| ft_max_word_len_for_sort | 20             |
| ft_stopword_file         | (built-in)     |
+--------------------------+----------------+
5 rows in set (0.00 sec)

The variable we wish to change is ft_min_word_len. According to the manual we should be able to change this via the SET VARIABLE command, but, in reality, this does not work. After asking the mailing list about this problem I was told this had to be specified as a startup option. To change the minimum query string to three characters, start the MySQL server as follows.

$ cd /path/to/mysql
$ ./bin/safe_mysqld --user=mysql -O ft_min_word_len=3 &

After you have restarted your MySQL server (don't forget to change your startup scripts) you have to rebuild the FULLTEXT indices. The manual suggests the following command:

-- Replace tbl_name with the name of your table
mysql> REPAIR TABLE tbl_name QUICK;

After you have rebuilt your indices, you should be able to search with query strings of three or more characters instead of the default four character limit. 

=========== Stored procedure ======================

A MySQL stored procedure:

    * is a block of code stored on the server
    * will normally carry out a series of SQL statements

This is particularly useful because:

    * client applications need to know nothing about the structure or the content of a database - they just need to know how to run any MySQL stored procedures
    * any changes in procedures can be made via the stored procedures - those changes will automatically be used by client applications without the need to modify the applications.

Example:
===================================================
delimiter //

mysql> create procedure getallnews()
    -> begin
    -> set @q = "select news_id,title from news";
    -> prepare stmt from @q;
    -> execute stmt;
    -> end;
    -> //
call getallnews();
===============
delimiter //
create procedure getnewsdetails(in newsid int)
begin
set @result = "select news_id,title from news where news_id = @newsid";
prepare stmt from @result;
execute stmt;
end;

call getnewsdetails(1);

// didn't get any results from above procedure call, empty set shown.

create procedure getnewsdetails(in newsid int)
begin
select news_id,title from news where news_id = @newsid;
end;

// didn't get any results from above procedure call, empty set shown.

create procedure getnewsdetails()
begin
declare getnewsid int;
set getnewsid = newsid;
set @query = "select news_id,title from news where news_id = 1";
prepare stmt from @query;
execute stmt;
end;

call getnewsdetails();

// got results for nes id = 1 from above procedure call

========= single conditions =============

create procedure getnewsdetails(IN newsid int)
begin
declare getnewsid int;
set getnewsid = newsid;
set @query = concat("select news_id,title from news where news_id = ", getnewsid);
prepare stmt from @query;
execute stmt;
end;

call nd(2);

========= multiple conditions =============

create procedure nd2(IN newsid int, in nstatus int)
begin
declare query text;
set query = "select news_id,title from news where ";
set @queryres = concat(query, "news_id = ",newsid," AND status = ",nstatus);
prepare stmt from @queryres;
execute stmt;
end;

call nd2(2,1);

// got news results for nes id = 2 and status = 1 from above procedure call

By default only one procedure can be called from php code
=================================
create function emicalc(totaldue double, totalmonth int, rateofinterest double)
returns double
deterministic
begin
declare emi double;
declare interest double;
declare totalemi double;
set emi = totaldue/totalmonth;
set interest = (rateofinterest/100)*emi;
set totalemi = emi+interest;
return totalemi;
end;
================================
alter function emicalc(totaldue double, totalmonth int, rateofinterest double)
returns double(10,2)
deterministic
begin
declare emi double;
declare interest double;
declare totalemi double;
set emi = totaldue/totalmonth;
set interest = (rateofinterest/100)*emi;
set totalemi = emi+interest;
return totalemi;
end;
==================================
CREATE TABLE parent (id INT NOT NULL,
                     PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT,
                    INDEX par_ind (parent_id),
                    FOREIGN KEY (parent_id) REFERENCES parent(id)
                      ON DELETE CASCADE
) ENGINE=INNODB;
===================================
CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
                      price DECIMAL,
                      PRIMARY KEY(category, id)) ENGINE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
                       PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
                            product_category INT NOT NULL,
                            product_id INT NOT NULL,
                            customer_id INT NOT NULL,
                            PRIMARY KEY(no),
                            INDEX (product_category, product_id),
                            FOREIGN KEY (product_category, product_id)
                              REFERENCES product(category, id)
                              ON UPDATE CASCADE ON DELETE RESTRICT,
                            INDEX (customer_id),
                            FOREIGN KEY (customer_id)
                              REFERENCES customer(id)) ENGINE=INNODB;
======================================


