SQL DELETE statement

The DELETE statement is used to delete records in a table.

---

SQL DELETE statement

The DELETE statement is used to delete rows in a table.

SQL DELETE syntax

DELETE FROM table_name
WHERE some_column=some_value;

	Note the WHERE clause in the SQL DELETE statement!  The WHERE clause specifies which records or which records need to be deleted. If you omit the WHERE clause, all the records will be deleted!

---

Demo database

In this tutorial, we will use the RUNOOB sample database.

The following is the data selected from the "Websites" table:

+ ---- + -------------- + --------------------------- + - ------ + --------- + | id | name          | url                        | alexa | country | + ---- + -------------- + - -------------------------- + ------- + --------- + | 1 | Google | Https : //www.google.cm/ | 1 | USA | | 2 | Taobao | https : //www.taobao.com/ | 13 | CN | | 3 | Rookie Tutorial | http : //www.runoob.COM / | 4689 | CN | | 4 | Twitter | HTTP : //weibo.com/ | 20 | CN | | 5 | Facebook | HTTPS : //www.facebook.com/ | 3 | USA | | 6 | Baidu | Https : //www.baidu.com/ | 4 | CN | | 7 | stackoverflow | http : //stackoverflow.com/ | 0 | IND | + ---- + ---------- ----- + --------------------------- + ------- + -------- - +COM / | 20 is | the CN | | . 5 | Facebook | HTTPS : //www.facebook.com/ |. 3 | USA | | . 6 | Baidu | HTTPS : //www.baidu.com/ |. 4 | the CN | | . 7 | Stackoverflow | http : //stackoverflow.com/ | 0 | IND | + ---- + --------------- + ------------- -------------- + ------- + --------- +COM / | 20 is | the CN | | . 5 | Facebook | HTTPS : //www.facebook.com/ |. 3 | USA | | . 6 | Baidu | HTTPS : //www.baidu.com/ |. 4 | the CN | | . 7 | Stackoverflow | http : //stackoverflow.com/ | 0 | IND | + ---- + --------------- + ------------- -------------- + ------- + --------- +Com | | 4 | CN | | 7 | stackoverflow | http : //stackoverflow.com/ | 0 | IND | + ---- + --------------- + --- ------------------------ + ------- + --------- +Com | | 4 | CN | | 7 | stackoverflow | http : //stackoverflow.com/ | 0 | IND | + ---- + --------------- + --- ------------------------ + ------- + --------- +


           
              
          
              
         
             
   

---

SQL DELETE instance

Suppose we want to remove the site from the "Websites" table named "Baidu" and the country for the CN site.

We use the following SQL statement:

Examples

DELETE FROM Websites WHERE name = ' Baidu ' AND country = ' CN ' ;

Execute the above SQL, and then read the "Websites" table, the data is as follows:

---

Delete all data

You can delete all the rows in the table without deleting the table. This means that the table structure, attributes, and indexes will remain the same:

DELETE FROM table_name ;

or

DELETE * FROM table_name ;

Note: Be careful when deleting records! Because you can not come back!

SQL UPDATE statement

The UPDATE statement is used to update the records in the table.

---

SQL UPDATE statement

The UPDATE statement is used to update an existing record in the table.

SQL UPDATE syntax

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

	Note the WHERE clause in the SQL UPDATE statement! The WHERE clause specifies which records or which records need to be updated. If you omit the WHERE clause, all the records will be updated!

---

Demo database

In this tutorial, we will use the RUNOOB sample database.

The following is the data selected from the "Websites" table:

 + ---- + -------------- + --------------------------- + - ------ + --------- +
 | Id | name | url | alexa | country |
 + ---- + -------------- + --------------------------- + - ------ + --------- +
 | 1 | Google | https://www.google.cm/ | 1 | USA |
 | 2 | Taobao | https://www.taobao.com/ | 13 | CN |
 | 3 | rookie tutorial | http://www.runoob.com/ | 4689 | CN |
 | 4 | microblogging | http://weibo.com/ | 20 | CN |
 5 | Facebook | https://www.facebook.com/ | 3 | USA |
 + ---- + -------------- + --------------------------- + - ------ + --------- +

---

SQL UPDATE instance

Suppose we want to update the "rookie tutorial" alexa ranking to 5000, country changed to USA.

We use the following SQL statement:

Examples

UPDATE Websites SET Alexa = ' 5000 ' , country = ' USA ' WHERE Name = ' rookie tutorial ' ;

Execute the above SQL, and then read the "Websites" table, the data is as follows:

---

Update Warning!

Be extra careful when updating your records! In the above example, if we omit the WHERE clause, as follows:

UPDATE Websites
SET alexa = '5000', country = 'USA' 

The above code will change all the data in the Websites table alexa to 5000, country to USA.

Execute UPDATE without WHERE clause to be careful and cautious.

SQL INSERT INTO statement

The INSERT INTO statement is used to insert a new record into the table.

---

SQL INSERT INTO statement

The INSERT INTO statement is used to insert a new record into the table.

SQL INSERT INTO syntax

INSERT INTO statements can have two forms of writing.

The first form does not need to specify the column name of the data to be inserted, just provide the inserted value:

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

The second form needs to specify the column name and the inserted value:

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

---

Demo database

In this tutorial, we will use the RUNOOB sample database.

The following is the data selected from the "Websites" table:

+ ---- + -------------- + --------------------------- + - ------ + --------- + | id | name          | url                        | alexa | country | + ---- + -------------- + - -------------------------- + ------- + --------- + | 1 | Google | Https : //www.google.cm/ | 1 | USA | | 2 | Taobao | https : //www.taobao.com/ | 13 | CN | | 3 | Rookie Tutorial | http : //www.runoob.COM / | 4689 | CN | | 4 | Twitter | HTTP : //weibo.com/ | 20 | CN | | 5 | Facebook | HTTPS : //www.facebook.com/ | 3 | USA | + --- - + -------------- + --------------------------- + ----- - + --------- +


           
              
          
              
         

---

INSERT INTO instance

Suppose we want to insert a new line into the "Websites" table.

We can use the following SQL statement:

Examples

INSERT INTO Websites ( name , url , alexa , country ) VALUES ( ' Baidu ' , ' https://www.baidu.com/ ' , ' 4 ' , ' CN ' ) ;

Execute the above SQL, and then read the "Websites" table, the data is as follows:

	Did you notice that we did not insert any numbers into the id field?  The id column is automatically updated, and each record in the table has a unique number.

---

Inserts data in the specified column

We can also insert data in the specified column.

The following SQL statement inserts a new line, but only the data is inserted in the "name", "url", and "country" columns (the id field is automatically updated):

Examples

The INSERT the INTO Websites ( name , URL , Country ) the VALUES ( ' StackOverflow ' , ' http://stackoverflow.com/ ' , ' IND ' ) ;

Execute the above SQL, and then read the "Websites" table, the data is as follows:

SQL ORDER BY keyword

The ORDER BY keyword is used to sort the result set.

---

SQL ORDER BY keyword

The ORDER BY keyword is used to sort the result set by a column or columns.

The ORDER BY keyword sorts the records by default. If you need to sort the records in descending order, you can use the DESC keyword.

SQL ORDER BY syntax

SELECT column_name , column_name
FROM table_name
ORDER BY column_name , column_name ASC|DESC;

---

Demo database

In this tutorial, we will use the RUNOOB sample database.

The following is the data selected from the "Websites" table:

 + ---- + -------------- + --------------------------- + - ------ + --------- +
 | Id | name | url | alexa | country |
 + ---- + -------------- + --------------------------- + - ------ + --------- +
 | 1 | Google | https://www.google.cm/ | 1 | USA |
 | 2 | Taobao | https://www.taobao.com/ | 13 | CN |
 | 3 | rookie tutorial | http://www.runoob.com/ | 4689 | CN |
 | 4 | microblogging | http://weibo.com/ | 20 | CN |
 5 | Facebook | https://www.facebook.com/ | 3 | USA |
 + ---- + -------------- + --------------------------- + - ------ + --------- +

---

ORDER BY instance

The following SQL statement selects all sites from the "Websites" table and sorts them according to the "alexa" column:

Examples

SELECT * FROM Websites ORDER BY Alexa

Execute the output:

---

ORDER BY DESC instance

The following SQL statement selects all sites from the "Websites" table and sorts them in descending order of "alexa"

Examples

SELECT * FROM Websites ORDER BY Alexa DESC ;

Execute the output:

---

ORDER BY multiple columns

The following SQL statement selects all sites from the "Websites" table and sorts them according to the "country" and "alexa" columns:

Examples

SELECT * FROM Websites ORDER BY Country , alexa

Execute the output: