PHP Tutorials
- What is MySqli
- mysql query
- mysql query example
- InnoDB
- mysql column Type
- CRUD Example
- Connection Using Function
- mysql keys
- SELECT
- WHERE
- UPDATE
- Count no of Rows
- ALIAS
- AND, AND & OR
- BETWEEN
- COMPARISON OPERATOR
- DELETE
- DELETE LIMIT
- DISTINCT
- EXISTS
- FROM
- GROUP BY
- HAVING
- IN
- INTERSECT
- IS NULL & IS NOT NULL
- LIKE
- NOT
- ORDER BY
- SELECT LIMIT
- SUBQUERY
- TRUNCATE
- UNION && UNION ALL
- Concat & Group_Concat
- mysql Function
- Mysql Insert Id
- MySql Aggregate Function
- Mysql Join
- JOIN in MySql
- Trigger
- Procedure
- Transaction
- views
- Index
- SQL Injection
- Normalization
- Query Bind
- Interview Questions
Important Link
Mysql Query :-
Create Table
create table student (id int(11) AUTO_INCREMENT PRIMARY key, name varchar(32) NOT NULL, email varchar(64) UNIQUE, address varchar(64) NOT NULL);
Create Foreign key
create table colledge (id int(11) AUTO_INCREMENT PRIMARY key, stud_id int(11), name varchar(32) NOT NULL, email varchar(64) UNIQUE, address varchar(64) NOT NULL, FOREIGN KEY (stud_id) REFERENCES student(id));
How to insert record into table.
you can write the INSERT statement in two ways:
INSERT INTO itechxpert (Name, Address, City, Pincode)
VALUES ('Sana', 'koilwar', 'ara', '802160');
INSERT INTO itechxpert
VALUES ('Sana', 'koilwar', 'ara', '802160');
Insert record into table.
INSERT INTO itechxpert set id=7, name="mahtab", address="delhi", city="delhi", pincode="110015";
Update record into table.
The mysql UPDATE statement is used to change the existing data in a table.
UPDATE itechxpert
SET Name = 'sana', City= 'patna'
WHERE ID = 1;
Select Distinct Rows from table.
The SELECT DISTINCT statement is used to return only distinct (different) values.
In a table, a column may contain many duplicate values; and sometimes you only want to list the different (distinct) values. The DISTINCT keyword can be used to return only distinct (different) values.
Distinct
File name : index.php
syntax: SELECT DISTINCT column_name,column_name
FROM table_name;
$qry = "select DISTINCT name from employee";
// it show distinct record on the basis of name.
$qry = "select DISTINCT name, phone from employee";
// it show distinct record on the basis of name and phone.
SELECT DISTINCT agent_code, ord_amount,cust_code
FROM orders WHERE agent_code='A002';
SELECT DISTINCT agent_code,ord_amount
FROM orders WHERE agent_code='A002' ORDER BY ord_amount;
COUNT() function and SELECT with DISTINCT on multiple columns
File name : index.php
SELECT COUNT(*)
FROM (
SELECT DISTINCT agent_code, ord_amount,cust_code
FROM orders
WHERE agent_code='A002');
// here first execute inner query and then outer query.
ORDER BY Keyword
The ORDER BY keyword is used to sort the result-set by one or more columns.
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword.
Example :-
File name : index.php
syntax:
SELECT column_name, column_name
FROM table_name
ORDER BY column_name ASC|DESC, column_name ASC|DESC;
SELECT * FROM Customers
ORDER BY Country;
AND or OR operator :-
The AND operator displays a record if both the first condition AND the second condition are true.
The OR operator displays a record if either the first condition OR the second condition is true.
Example :-
SELECT * FROM Customers WHERE Country='india' AND City='delhi';
/**************** Or ****************/
SELECT * FROM Customers WHERE City='Berlin' OR City='München';
How to find duplicate value in table.
SELECT name, COUNT(name) FROM users GROUP BY name HAVING ( COUNT(name) > 1 )
SELECT name, COUNT(*) FROM users GROUP BY name HAVING ( COUNT(*) > 1 )
SELECT name FROM users GROUP BY name HAVING ( COUNT(*) > 1 )
SELECT name, mobile, COUNT(*) FROM users GROUP BY name, mobile HAVING COUNT(*) > 1
SELECT name, mob FROM users WHERE mob in (SELECT mob FROM users GROUP BY mob HAVING COUNT(*)>1)
what is subquery.
A subquery is a SELECT statement within another statement.
All subquery forms and operations that the SQL standard requires are supported
A subquery is usually added within the WHERE Clause of another SQL SELECT statement.
You can use the comparison operators, such as >, <, or =. The comparison operator can also be a multiple-row operator, such as IN, ANY, SOME, or ALL.
A subquery can be treated as an inner query, which is a SQL query placed as a part of another query called as outer query.
The inner query executes first before its parent query so that the results of the inner query can be passed to the outer query.
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
In this example, SELECT * FROM t1 ... is the outer query (or outer statement), and (SELECT column1 FROM t2) is the subquery. We say that the subquery is nested within the outer query, and in fact it is possible to nest subqueries within other subqueries. A subquery must always appear within parentheses.
A MySQL subquery is a query that is nested inside another query such as SELECT, INSERT, UPDATE or DELETE. In addition, a MySQL subquery can be nested inside another subquery.
A MySQL subquery is also called an inner query while the query that contains the subquery is called an outer query.
The subquery returns all offices codes of the offices that locate in the India.
The outer query selects the last name and first name of employees whose office code is in the result set returned by the subquery.
select firstname, lastname from employee where officecode IN (select officecode from offices where country = 'india')
SELECT customerNumber,
checkNumber,
amount
FROM payments
WHERE amount = (
SELECT MAX(amount)
FROM payments
);
SELECT customerNumber,
checkNumber,
amount
FROM payments
WHERE amount > (
SELECT AVG(amount)
FROM payments
);
MySQL subquery with IN and NOT IN operators
If a subquery returns more than one value, you can use other operators such as IN or NOT IN operator in the WHERE clause.
SELECT customername
FROM customers
WHERE customerNumber NOT IN(
SELECT DISTINCT customernumber
FROM orders
);
SQL SELECT TOP Example :-
The SELECT TOP clause is used to specify the number of records to return.
Note: Not all database systems support the SELECT TOP clause.
Example :-
SELECT TOP 2 * FROM Customers;
SELECT TOP 50 PERCENT * FROM Customers;
SELECT * FROM Persons LIMIT 3;
SQL LIKE Operator :-
The LIKE operator is used to search for a specified pattern in a column.
Example :-
SELECT * FROM Customers WHERE City LIKE 's%';
SELECT * FROM Customers WHERE City LIKE 'ber%';
IN Operator Between :-
The IN operator allows you to specify multiple values in a WHERE clause.
Example :-
SELECT * FROM Customers WHERE City IN ('india','delhi'); /**************** *******************/
SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;
FOREIGN KEY Constraint on CREATE TABLE :-
Example :-
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)
) // here P_Id is the primary key of Persons table. and P_Id is the foregin key in Orders table.
FOREIGN KEY Constraint on ALTER TABLE
To create a FOREIGN KEY constraint on the "P_Id" column when the "Orders" table is already created, use the following SQL:
Example :-
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
/******************************* */
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
SQL FOREIGN KEY Constraint on ALTER TABLE :-
To create a FOREIGN KEY constraint on the "P_Id" column when the "Orders" table is already created, use the following SQL:
Example :-
ALTER TABLE Orders ADD FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
DROP a FOREIGN KEY Constraint
How to get third higest salary from table
File name : index.php
How do I find the third maximum salary from a salary table in MySQL?
Employee
Employee ID Salary
1 200
2 800
3 450
4 600
5 700
SELECT * FROM employee ORDER BY salary DESC LIMIT 2,1;
The LIMIT keyword in MySQL allows you to choose the "offset, count" of the number of rows you want.
In above query, you're getting the 2(th) row (offset starts from 0).
And the number of rows you want = 1.
How to get second highest salary from table.
File name : index.php
SELECT MAX(salary) FROM Employee WHERE Salary NOT IN ( SELECT Max(Salary) FROM Employee);
OR
SELECT MAX(Salary) From emp WHERE Salary < ( SELECT Max(Salary) FROM emp);
How to get random row from table.
File name : index.php
SELECT MAX(salary) FROM emp;
Second Maximum Salary in MySQL using LIMIT.
File name : index.php
SELECT Salary FROM (SELECT Salary FROM emp ORDER BY salary DESC LIMIT 2) AS Emp ORDER BY salary LIMIT 1
How to get random row from table.
File name : index.php
SELECT * FROM `table_name` ORDER BY RAND() LIMIT 1;
File name : index.php