what is difference between TRUNCATE vs DELETE in SQL

The IN operator allows you to specify multiple values in a WHERE clause.

SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...);

SELECT * FROM Customers WHERE City IN ('Paris','London');

The BETWEEN operator is used to select values within a range.
The BETWEEN operator selects values within a range. The values can be numbers, text, or dates.

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;

SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;

SQL aliases are used to temporarily rename a table or a column heading.

SQL aliases are used to give a database table, or a column in a table, a temporary name. Basically aliases are created to make column names more readable.

SQL Alias Syntax for Columns

SELECT column_name AS alias_name FROM table_name;

SQL Alias Syntax for Tables

SELECT column_name(s) FROM table_name AS alias_name;

SQL aliases are used to better when two or more tabel column name same. in that case we use for change column name for other name.

Example :
SELECT CustomerName AS Customer, ContactName AS [Contact Person] FROM Customers;

SELECT CustomerName, Address+', '+City+', '+PostalCode+', '+Country AS Address FROM Customers;

MySQL database server is reliable, fast and very easy to use. This software can be downloaded as freeware and can be downloaded from the internet.

HEAP tables are present in memory and they are used for high speed storage on temporary basis.

The default port for MySQL server is 3306.

CHAR_LENGTH is character count whereas the LENGTH is byte count. The numbers are same for Latin characters but they are different for Unicode and other encodings.

ENUMs and SETs are used to represent powers of two because of storage optimizations.

ENUM is a string object used to specify set of predefined values and that can be used during table creation.
Create table size(name ENUM('Small', 'Medium','Large');

REGEXP is a pattern match in which matches pattern anywhere in the search value.

The string types are:

SELECT VERSION ();

Storage engines are called table types and data is stored in files using various techniques. Technique involves:

Following are the drivers available in MySQL:

Every row of a table is identified uniquely by primary key. There is only one primary key for a table. Primary Key is also a candidate key. By common convention, candidate key can be designated as primary and which can be used for any foreign key references.

% corresponds to 0 or more characters, _ is exactly one character in the LIKE statement.

mysql_fetch_array() -Returns a result row as an associated array or a regular array from database.
mysql_fetch_object – Returns a result row as object from database.

Total 5 types of tables are present:

SELECT * FROM LIMIT 0,50;

MySQL supports up to 4096 columns per table.

session_set_save_handler()
PHP provides a function that lets you override the default session mechanism by specifying the names of your own functions for taking care of the distinct tasks. I have organized this article according to these functions that you must write. For each function, I show you an example (using MySQL) and provide an explanation. The function is called session_set_save_handler(), and it takes six arguments, each of which is the name of a function that you write. These functions are responsible for the following tasks:

Following objects are created using CREATE statement:

SIX triggers are allowed in MySql table. They are as follows:

MySQL is written in C and C++

Tables that are present in memory is known as HEAP tables. When you create a heap table in MySQL, you should need to specify the TYPE as HEAP. These tables are commonly known as memory tables. They are used for high speed storage on temporary basis. They do not allow BLOB or TEXT fields.

A trigger is a set of codes that executes in response to some events.

Heap tables: Heap tables are found in memory. They are used for high speed storage on temporary basis. They do not allow BLOB or TEXT fields. Heap tables do not support AUTO_INCREMENT. Indexes should be NOT NULL.

Temporary tables: The temporary tables are used to keep the temporary data. Sometimes it is very useful in cases to keep temporary data. Temporary table is deleted after current client session terminates.

Mysql_connect:
It opens a new connection to the database. Every time you need to open and close database connection, depending on the request. Opens page every time when it loaded.

Mysql_pconnect:
In Mysql_pconnect, "p" stands for persistent connection so it opens the persistent connection. the database connection can not be closed. it is more useful if your site has more traffic because there is no need to open and close connection frequently and every time when page is loaded.

To find Nth highest salary is:
select distinct(salary) from employee order by salary desc limit n-1,1

if you want to find 3rd largest salary:
select distinct(salary) from employee order by salary desc limit 2,1

The =, <>,<=,>=,, AND, OR, or LIKE operator may be used in column comparison to the left of the from in select statement.

Select count(user_id) from users;

Select book_title from books limit 25, 100;

Select user_name from users where ISNULL(mobileno);

Select version();

Select user();

Alter table name_file ENGINE innodb;

CONCAT(string1, string2, string3);

Show databases;

Describe table_name;

Select last_insert_id() will return the last value assigned by the auto_increment funtion.
NOTE :- you don’t have to specify the table name.

Maximum size of Heap table can be controlled using MySQL config variable called max_heap_table_size.

BLOB stands for binary large object.- It that can hold a variable amount of data.There are four types of BLOB based on the maximum length of values they can hold:- TINYBLOB 
BLOB 
MEDIUMBLOB
LONGBLOB

TEXT is case-insensitive BLOB. The four types of TEXT are:- TINYTEXT- TEXT- MEDIUMTEXT- LONGTEXT

- In BLOB sorting and comparison is performed in case-sensitive for BLOB values.
- In TEXT types sorting and comparison is performed case-insensitive.

Mysql_connect() opens a new connection to the database while mysql_pconnect() opens a persistent connection to the database. This means that each time the page is loaded mysql_pconnect() does not open the database. Mysql_close() cannot be used to close the persistent connection. Though it can be used to close mysql_connect().

we can update 64 columns in single mysql query.

 column name can be upto 64 chars, table name can be upto 64 and database name can be upto 64 chars.

SELECT COUNT (user_id) FROM users  would only return the number of user_id’s.

SELECT user_name FROM users WHERE ISNULL(user_phonenumber);

SELECT team_name FROM teams WHERE team_won IN (2, 4, 6, 8)

SELECT LAST_INSERT_ID() will return the last value assigned by the auto_increment function. Note that you don’t have to specify the table name.

SHOW INDEX FROM questions

ALTER TABLE answers DROP answer_user_id.

ALTER TABLE questions ENGINE innodb;

SELECT SUBSTR(name, 1, 10) from questions;

SELECT (question USING utf8);

% corresponds to 0 or more characters, _ is exactly one character.

You can do this in two ways1) select salary from employee order by salary desc limit 1,1
2) select max(salary) from employee where max(salary)<(select max(salary) from employee)

SELECT MONTH(timestamp) from questions;

Following are the some techniques of query optimization1) Use Indexes2) User limit (Don't pull extra records)3) Use only required fields (Don't pull extra fields which you are not going to use)

SELECT COUNT (user_id) FROM users;

SELECT * FROM users order by salary desc limit 0,5

mysql_affected_rows mysql_query('DELETE FROM mytable WHERE id < 10');
printf("Records deleted: %d\n", mysql_affected_rows());