The SQL DELETE Statement
The DELETE statement is used to delete existing records in a table.
DELETE Syntax
DELETE FROM table_name WHERE condition;
----------
Delete All Records
It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:
DELETE FROM table_name;
The following SQL statement deletes all rows in the "Customers" table, without deleting the table:
Example
------------
Setting environment for using XAMPP for Windows.
Mounika@MOUNIKA-PC c:\xampp
# mysql -h localhost -u root
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.4.18-MariaDB mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> USE vlrinst;
Database changed
MariaDB [vlrinst]> select * from emp;
+----+---------+-----------+------+-------+
| id | name | loc | age | sal |
+----+---------+-----------+------+-------+
| 1 | ramesh1 | jntu | 33 | 60840 |
| 2 | praveen | pune | 28 | 70980 |
| 3 | mounika | hyderabad | 22 | 79092 |
| 4 | revathi | jntu | 29 | 50560 |
| 5 | anji | gutta | 28 | 50560 |
| 6 | harika | lb nagar | 29 | 61043 |
| 7 | praveen | jntu | 33 | 10203 |
| 8 | pandu | munipeda | 35 | 61448 |
| 9 | pandu | pune | 23 | 79700 |
| 10 | venkat | kphb | 35 | 60840 |
| 11 | praveen | pune | 28 | 70980 |
| 12 | mounika | hyderabad | 22 | 79092 |
| 13 | revathi | jntu | 29 | 50560 |
+----+---------+-----------+------+-------+
13 rows in set (0.091 sec)
MariaDB [vlrinst]> delete from emp where id=11;
Query OK, 1 row affected (0.118 sec)
MariaDB [vlrinst]> select * from emp;
+----+---------+-----------+------+-------+
| id | name | loc | age | sal |
+----+---------+-----------+------+-------+
| 1 | ramesh1 | jntu | 33 | 60840 |
| 2 | praveen | pune | 28 | 70980 |
| 3 | mounika | hyderabad | 22 | 79092 |
| 4 | revathi | jntu | 29 | 50560 |
| 5 | anji | gutta | 28 | 50560 |
| 6 | harika | lb nagar | 29 | 61043 |
| 7 | praveen | jntu | 33 | 10203 |
| 8 | pandu | munipeda | 35 | 61448 |
| 9 | pandu | pune | 23 | 79700 |
| 10 | venkat | kphb | 35 | 60840 |
| 12 | mounika | hyderabad | 22 | 79092 |
| 13 | revathi | jntu | 29 | 50560 |
+----+---------+-----------+------+-------+
12 rows in set (0.001 sec)
MariaDB [vlrinst]> delete from emp where age > 28;
Query OK, 7 rows affected (0.092 sec)
MariaDB [vlrinst]> select * from emp;
+----+---------+-----------+------+-------+
| id | name | loc | age | sal |
+----+---------+-----------+------+-------+
| 2 | praveen | pune | 28 | 70980 |
| 3 | mounika | hyderabad | 22 | 79092 |
| 5 | anji | gutta | 28 | 50560 |
| 9 | pandu | pune | 23 | 79700 |
| 12 | mounika | hyderabad | 22 | 79092 |
+----+---------+-----------+------+-------+
5 rows in set (0.001 sec)
MariaDB [vlrinst]> delete from emp ;
Query OK, 5 rows affected (0.082 sec)
MariaDB [vlrinst]> select * from emp;
Empty set (0.001 sec)
MariaDB [vlrinst]> desc emp;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | YES | | NULL | |
| loc | varchar(100) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| sal | int(11) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
5 rows in set (0.057 sec)
MariaDB [vlrinst]> insert into emp (name,loc,age,sal) values('venkat',
-> 'hyd',35,23000);
Query OK, 1 row affected (0.108 sec)
MariaDB [vlrinst]> select * from emp;
+----+--------+------+------+-------+
| id | name | loc | age | sal |
+----+--------+------+------+-------+
| 14 | venkat | hyd | 35 | 23000 |
+----+--------+------+------+-------+
1 row in set (0.000 sec)
MariaDB [vlrinst]> drop table emp;
Query OK, 0 rows affected (0.273 sec)
MariaDB [vlrinst]> CREATE TABLE emp
-> (
-> id INT NOT NULL AUTO_INCREMENT,
-> name VARCHAR(100),
-> loc VARCHAR(100),
-> age INT,
-> sal INT,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.272 sec)
MariaDB [vlrinst]> INSERT INTO emp(name, loc, age,sal)
-> VALUES ('venkat', 'kphb', 35,30000),
-> ('praveen', 'pune', 28,35000),
-> ('mounika', 'hyderabad', 22,39000),
-> ('revathi', 'jntu', 26,20000),
-> ('anji', 'gutta', 28,20000),
-> ('harika', 'lb nagar', 26,30100),
-> ('praveen', 'jntu', 33,100),
-> ('pandu', 'munipeda', 35,30300),
-> ('pandu', 'pune', 23,39300);
Query OK, 9 rows affected (0.085 sec)
Records: 9 Duplicates: 0 Warnings: 0
MariaDB [vlrinst]> select * from emp;
+----+---------+-----------+------+-------+
| id | name | loc | age | sal |
+----+---------+-----------+------+-------+
| 1 | venkat | kphb | 35 | 30000 |
| 2 | praveen | pune | 28 | 35000 |
| 3 | mounika | hyderabad | 22 | 39000 |
| 4 | revathi | jntu | 26 | 20000 |
| 5 | anji | gutta | 28 | 20000 |
| 6 | harika | lb nagar | 26 | 30100 |
| 7 | praveen | jntu | 33 | 100 |
| 8 | pandu | munipeda | 35 | 30300 |
| 9 | pandu | pune | 23 | 39300 |
+----+---------+-----------+------+-------+
9 rows in set (0.001 sec)
MariaDB [vlrinst]> delete from emp where name='praveen';
Query OK, 2 rows affected (0.073 sec)
MariaDB [vlrinst]> select * from emp;
+----+---------+-----------+------+-------+
| id | name | loc | age | sal |
+----+---------+-----------+------+-------+
| 1 | venkat | kphb | 35 | 30000 |
| 3 | mounika | hyderabad | 22 | 39000 |
| 4 | revathi | jntu | 26 | 20000 |
| 5 | anji | gutta | 28 | 20000 |
| 6 | harika | lb nagar | 26 | 30100 |
| 8 | pandu | munipeda | 35 | 30300 |
| 9 | pandu | pune | 23 | 39300 |
+----+---------+-----------+------+-------+
7 rows in set (0.001 sec)
MariaDB [vlrinst]> delete from emp where sal <30000;
Query OK, 2 rows affected (0.094 sec)
MariaDB [vlrinst]> select * from emp;
+----+---------+-----------+------+-------+
| id | name | loc | age | sal |
+----+---------+-----------+------+-------+
| 1 | venkat | kphb | 35 | 30000 |
| 3 | mounika | hyderabad | 22 | 39000 |
| 6 | harika | lb nagar | 26 | 30100 |
| 8 | pandu | munipeda | 35 | 30300 |
| 9 | pandu | pune | 23 | 39300 |
+----+---------+-----------+------+-------+
5 rows in set (0.001 sec)
MariaDB [vlrinst]> delete from emp;
Query OK, 5 rows affected (0.055 sec)
MariaDB [vlrinst]> select * from emp;
Empty set (0.000 sec)
MariaDB [vlrinst]> INSERT INTO emp(name, loc, age,sal)
-> VALUES ('venkat', 'kphb', 35,30000),
-> ('praveen', 'pune', 28,35000),
-> ('mounika', 'hyderabad', 22,39000),
-> ('revathi', 'jntu', 26,20000),
-> ('anji', 'gutta', 28,20000),
-> ('harika', 'lb nagar', 26,30100),
-> ('praveen', 'jntu', 33,100),
-> ('pandu', 'munipeda', 35,30300),
-> ('pandu', 'pune', 23,39300);
Query OK, 9 rows affected (0.061 sec)
Records: 9 Duplicates: 0 Warnings: 0
MariaDB [vlrinst]> select * from emp;
+----+---------+-----------+------+-------+
| id | name | loc | age | sal |
+----+---------+-----------+------+-------+
| 10 | venkat | kphb | 35 | 30000 |
| 11 | praveen | pune | 28 | 35000 |
| 12 | mounika | hyderabad | 22 | 39000 |
| 13 | revathi | jntu | 26 | 20000 |
| 14 | anji | gutta | 28 | 20000 |
| 15 | harika | lb nagar | 26 | 30100 |
| 16 | praveen | jntu | 33 | 100 |
| 17 | pandu | munipeda | 35 | 30300 |
| 18 | pandu | pune | 23 | 39300 |
+----+---------+-----------+------+-------+
9 rows in set (0.001 sec)
MariaDB [vlrinst]>
No comments:
Post a Comment
Note: only a member of this blog may post a comment.