If you fired a query, that takes more time to complete their execution.
but you want to stop this query.
To stop this query you must use Kill command to achieve it.
Follow two step to kill your query.
mysql> SHOW PROCESSLIST;
above command will show all running process on the server.
Here you will see thread ID return by above command.
mysql> KILL 22;
kill thread 22 to kill your query.
Category: MySQL
How to kill a MySQL query?
Differences between DROP and TRUNCATE a table?
DROP TABLE table_name – This will delete the table and its data.
TRUNCATE TABLE table_name – This will delete the data of the table, but not the table definition.
What is the default table in MYSQL? which type of table is generatedby default.
MyISAM is the default storage engine.
How many number of tables are present in MYSQL Data base?
Total 5 types of tables we can create
1. MyISAM
2. Heap
3. Merge
4. INNO DB
5. ISAM
what is LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN in mysql?
The best way to learn about JOINS is the example. So below is the example to demonstrate JOINS
Suppose that there are two tables
1- Person
2-Information
Person table has definition and data like that
Person:
| Number(Primary key) | name | phone |
| 1 | Mr Sana | 0120 234564 |
| 2 | Mr Urooj | 0120 234567 |
| 3 | Mr Imran | 0120 245678 |
Information:
| Entry_id | Number | Address |
| 1 | 1 | Chand Pura, Bahraich UP (INDIA) |
| 2 | 3 | Dadi hat, Bahraich UP (INDIA) |
| 3 | 3 | Qazi pura, Bahraich UP (INDIA) |
| 4 | 3 | New Ashok Nagar, Delhi UP (INDIA) |
| 5 | 4 | Faizadab UP (INDIA) |
mysql> select * from person;
out put:
| Number(Primary key) | name | phone |
| 1 | Mr Sana | 0120 234564 |
| 2 | Mr Urooj | 0120 234567 |
| 3 | Mr Imran | 0120 245678 |
3 rows in set (0.00 sec)
mysql> select * from information;
| Entry_id | Number | Address |
| 1 | 1 | Chand Pura, Bahraich UP (INDIA) |
| 2 | 3 | Dadi hat, Bahraich UP (INDIA) |
| 3 | 3 | Qazi pura, Bahraich UP (INDIA) |
| 4 | 3 | New Ashok Nagar, Delhi UP (INDIA) |
| 5 | 4 | Faizadab UP (INDIA) |
5 rows in set (0.00 sec)
Regular Join:
If apply a regular JOIN (with out use of these keywords INNER, OUTER, LEFT or RIGHT), then I get all records that match in the appropriate way in the two tables, and records in both incoming tables that do not match are not reported:
mysql> select name, phone, address
from person join information
on person.number = information.number;
OR
mysql> select name, phone, address
from person,information where
person.number = information.number;
Output:
| name | Phone | Address |
| Mr Sana | 0120 234564 | Chand Pura, Bahraich UP (INDIA) |
| Mr Imran | 0120 245678 | Dadi hat, Bahraich UP (INDIA) |
| Mr Imran | 0120 245678 | Qazi pura, Bahraich UP (INDIA) |
| Mr Imran | 0120 245678 | New Ashok Nagar, Delhi UP (INDIA) |
4 rows in set (0.01 sec)
LEFT JOIN:
If I apply left joint with using keyword LEFT JOIN
mysql> select name, phone, address
from person left join information on person.number = information.number;
Output:
| name | phone | address |
| Mr Sana | 0120 234564 | Chand Pura, Bahraich UP (INDIA) |
| Mr Urooj | 0120 234567 | NULL |
| Mr Imran | 0120 245678 | Dadi hat, Bahraich UP (INDIA) |
| Mr Imran | 0120 245678 | Qazi pura, Bahraich UP (INDIA) |
| Mr Imran | 0120 245678 | New Ashok Nagar, Delhi UP |
5 rows in set (0.00 sec)
RIGHT JOIN:
If I apply right joint with using keyword RIGHT JOIN
mysql> select name, phone, address
from person right join information on person.number = information.number;
| name | phone | address |
| Mr Sana | 0120 234564 | Chand Pura, Bahraich UP (INDIA) |
| Mr Imran | 0120 245678 | Dadi hat, Bahraich UP (INDIA) |
| Mr Imran | 0120 245678 | Qazi pura, Bahraich UP (INDIA) |
| Mr Imran | 0120 245678 | New Ashok Nagar, Delhi UP |
| NULL | NULL | Faizadab UP (INDIA) |
5 rows in set (0.00 sec)
OUTER JOIN: Outer join does not support RDBMS.
Delete Duplicate rows from the table?
A- Suppose that Student is the table
CREATE TABLE `student` (
`stid` int(11) NOT NULL auto_increment,
`name` varchar(50) NOT NULL,
`phone` varchar(15) NOT NULL,
PRIMARY KEY (`stid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
–
– Dumping data for table `student`
–
INSERT INTO `student` VALUES (1, ‘abc’, ‘9891215668′);
INSERT INTO `student` VALUES (2, ‘abc’, ‘9891215668′);
INSERT INTO `student` VALUES (3, ‘abc’, ‘9891215668′);
Student :
+————+————–+——+
stid | name | phone
+————+————–+——+
1 | abc | 9891215668
2 | abc |9891215668
3 | abc |9891215668
+————+————–+——+
Need to delete all duplicate rows.
MYSQl->
DELETE duplicate_rows. * FROM student AS duplicate_rows INNER JOIN (
SELECT name, MIN( stid ) AS st_id
FROM student
GROUP BY name
HAVING count( * ) >1
) AS exp_rows ON exp_rows.name = duplicate_rows.name
AND exp_rows.st_id <> duplicate _rows.stid;
Output:
+————+————–+——+
stid | name | phone
+————+————–+——+
1 | abc | 9891215668
+————+————–+——+
How to find the second highest salary from emp table?
There are number of examples to get second highest salary from EMP table.
example-select sal from(select sal from
(select distinct sal from emp order by sal desc)
where rownum<=2 order by sal asc)
where rownum=1;
example- SELECT MAX(SAL) FROM EMP WHERE SAL NOT IN (SELECT MAX(SAL) FROM EMP)
where EMP is table name,SAL is salary colum.
How to display nth highest record in a table for example How to display 4th highest (salary) record from customer table.
Query: SELECT sal FROM `emp` order by sal desc limit (n-1),
1If the question: “how to display 4th highest (salary) record from customer table.”The query will SELECT sal FROM `emp`
order by sal desc limit 3,1
What is the difference between inner join and outer join?
Inner join displays rows from table where the data is available in both the tables, where in outer join we can configure it to bring out rows from one table where the data is missing in other table for the corresponding rows.
Say you have one table of CUSTOMERS and one table of ORDERS. Each row in the ORDERS table has a reference (foreign key reference) to a customer id which represents what customer placed that order. If you want to run a query that lists the orders along with the names of the customers who ordered them (since a customer id number itself it pretty useless), you will want to execute a join query:
SELECT CUSTOMERS.NAME, ORDERS.NAME
FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
If for some reason, you wanted the query results to return all customer name regardless of whether they placed an order, you can use one of two types of OUTER JOINS, in this case, a LEFT JOIN:
SELECT CUSTOMERS.NAME, ORDERS.NAME
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
Polls
Loading ...
