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 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 the SELECT DISTINCT statement

The SELECT DISTINCT statement is used to return a unique different value.

---

SQL SELECT DISTINCT statement

In the table, a column may contain multiple duplicate values, and sometimes you may want to list only the distinct values.

The DISTINCT keyword is used to return a unique different value.

SQL SELECT DISTINCT syntax

SELECT DISTINCT column_name,column_name
FROM table_name;

---

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 | + --- - + -------------- + --------------------------- + ----- - + --------- +


           
              
          
              
         

---

SELECT DISTINCT instance

The following SQL statement only from the "Websites" table "country" column to select a unique different value, that is, remove the "country" column repeat value:

Examples

SELECT DISTINCT country FROM Websites ;

Output results:

SQL SELECT statement

The SELECT statement is used to select data from the database.

---

SQL SELECT statement

The SELECT statement is used to select data from the database.

The result is stored in a result table, called the result set.

SQL SELECT syntax

SELECT column_name , column_name
FROM table_name ;

versus

SELECT * FROM table_name ;

---

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 |
 + ---- + -------------- + --------------------------- + - ------ + --------- +

---

SELECT Column instance

The following SQL statement selects the "name" and "country" columns from the "Websites" table:

Examples

SELECT Name , country FROM Websites ;

The output is:

---

SELECT * instance

The following SQL statement selects all columns from the "Websites" table:

Examples

SELECT * FROM Websites ;

The output is:

---

Results of the centralized navigation

Most database software systems allow the use of programming functions to navigate in the result set, such as: Move-To-First-Record, Get-Record-Content, Move-To-Next-Record, and so on.