Category Archives: MySQL

MySQL Practical Interview questions and Syntax’s


<?php
 
//=====================================================================
//  SELECT
//=====================================================================

SELECT column_name,column_name
FROM table_name;

// If you want all column from database

SELECT * FROM table_name;

// Example

SELECT name, number FROM `tbl1`;

//=====================================================================
//  SELECT DISTINCT
//=====================================================================
 
// DISTINCT means Diffrent
// It can display only unique value, Not repit same value.

SELECT DISTINCT column_name,column_name
FROM table_name;

// Ex
SELECT DISTINCT name FROM tbl1;

//=====================================================================
// SELECT WHERE Clause 
//=====================================================================

SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;

// Ex
SELECT city FROM tbl1
WHERE city='junagadh';

//=====================================================================
// AND & OR Operators
//=====================================================================

// AND - Display result if both condition are true.

SELECT * FROM tbl1
WHERE city='junagadh'
AND name='ravi';

// OR - Display result if either one condition are true.

SELECT * FROM tbl1
WHERE city='junagadh'
OR city='keshod';

//=====================================================================
// ORDER BY Keyword
//=====================================================================

SELECT * FROM tbl1
ORDER BY name ASC, city DESC;

//=====================================================================
// INSERT INTO
//=====================================================================

// used to insert new records in a table.

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

//Ex

INSERT INTO `tbl1`(`name`, `number`, `city`) VALUES ('happy','9979745855','divrana');

//=====================================================================
// UPDATE Statement
//=====================================================================

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

// Ex

UPDATE `tbl1` SET `name`='mama',`number`='123456963',`city`='ahmedabad' WHERE `id` = '137';

//=====================================================================
// DELETE Statement
//=====================================================================

DELETE FROM table_name
WHERE some_column=some_value;

// Delete table

DELETE FROM tbl1

// Delete by value

DELETE FROM tbl1
WHERE name='ravi' AND city='junagadh';

//=====================================================================
// SELECT TOP
//=====================================================================

// Top N record list to return.

SELECT *
FROM tbl1
LIMIT 3;

//=====================================================================
// LIKE Operator
//=====================================================================

// Search by starting with the letter "s"
SELECT * FROM tbl1
WHERE city LIKE 's%';

// Search by ending with the letter "s"
SELECT * FROM tbl1
WHERE city LIKE '%s';

// Search by any single and multi latter "rav" 
SELECT * FROM tbl1
WHERE name LIKE '%rav%';

// Search every latter without "rav" 
SELECT * FROM tbl1
WHERE name NOT LIKE '%rav%';

//=====================================================================
// IN Operator
//=====================================================================

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

// Ex

SELECT * FROM tbl1
WHERE city IN ('divrana','ahmedabad');

//=====================================================================
// BETWEEN Operator
//=====================================================================

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

// Ex BETWEEN

SELECT * FROM tbl1
WHERE price BETWEEN 135 AND 137;

// Ex NOT BETWEEN 

SELECT * FROM tbl1
WHERE price NOT BETWEEN 135 AND 137;

//=====================================================================
// Aliases
//=====================================================================

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

SELECT name AS firstname, number as monumber
FROM tbl1;

//=====================================================================
// NULL Values
//=====================================================================

// NULL values represent missing unknown data.

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL

//=====================================================================
// JOIN
//=====================================================================

// INNER JOIN

// INNER JOIN = JOIN (You cal also use only "join" in syntex)
// Inner join also called simple join
// Inner join ma first table and second table banne ni value hase ej display tharse 

// Syntex ( fk = FOREIGN KEY , PK = PRIMARY KEY )
SELECT fk_table_name.column_name, table_name.column_name
FROM fk_table_name
INNER JOIN table_name
ON fk_table_name.fk_column_name=table_name.pk_column_name;

// EX

SELECT product.p_name, category.c_name
FROM product
INNER JOIN category
ON product.c_id=category.c_id;

// LEFT JOIN

// Left join ma first table ni badhi value display thase ane second table ma nai hoi to false display thase 

SELECT product.p_name, category.c_name
FROM product
LEFT JOIN category
ON product.c_id=category.c_id;

// RIGHT JOIN

// Right join ma second table ni badhi value display thase ane first table ma nai hoi to false display thase 

SELECT product.p_name, category.c_name
FROM product
LEFT JOIN category
ON product.c_id=category.c_id;

// RIGHT JOIN

// Full outer join ma first table and second table ma value nahi hoi e pan display tharse 

SELECT product.p_name, category.c_name
FROM product
FULL OUTER JOIN JOIN category
ON product.c_id=category.c_id;

//=====================================================================
// UNION Operator
//=====================================================================

// UNION operator combines the result of two or more SELECT statements.

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

// Display all duplicate value also
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;






// ============================================================ WITHOUT PHP MYADMIN ============================================================================

//=====================================================================
// CREATE TABLE 
//=====================================================================

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);

// Ex

CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

//=====================================================================
// Constraints
//=====================================================================

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

// NOT NULL

// "P_Id" column and the "LastName" column to not accept NULL values
CREATE TABLE PersonsNotNull
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

// UNIQUE 

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)

// PRIMARY KEY

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

// FOREIGN KEY

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

// Add Foreign key when table already created

ALTER TABLE Orders
ADD FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)

// Drop a FOREIGN KEY

ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

//=====================================================================
// DROP
//=====================================================================

// Delete table

DROP TABLE table_name

DROP DATABASE database_name

// Empty table

TRUNCATE TABLE table_name

//=====================================================================
// ALTER TABLE
//=====================================================================

// ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

// Add a column in a table
ALTER TABLE table_name
ADD column_name datatype

// Delete a column in a table
ALTER TABLE table_name
DROP COLUMN column_name

//=====================================================================
// AUTO INCREMENT
//=====================================================================

CREATE TABLE Persons
(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID)
)

Get Last Inserted ID in PHP MySQL


$query = "SELECT c_id FROM category ORDER BY c_id DESC LIMIT 1;";	  
$result = mysql_query($query);
while($row = mysql_fetch_array($result)) {  
	echo $c_id = $row['c_id'];
} 

%d bloggers like this: