Row Operations of SQL statements: rows of SQL statements

Row Operations of SQL statements: rows of SQL statements

 

SQL statements are explained in the following articles:

1. SQL statement Row Operations

2. SQL statement-based table operations

3. Database Operations for SQL statements

4. SQL statement user management

Storage Format of relational databases

　　

In relational databases, data is stored in a format similar to an Excel table (for example). We call a "column name" as a "field", and a piece of data refers to the information stored in a row, the main operations for this row of data are "add, delete, modify, and query ".

The above is a table in the database, with a total of five rows and two fields (nid and name). Next we will introduce how to add a new row of data, how to delete, modify, and query existing data.

 

Row operation-add

The statement used to add data is insert ...... Values ...... There are three insert methods:

　　

(1) Insert a row of data ("--" is followed by a comment, and the content after the program does not run)

Insert into table (field name, field name ......) Values (value, value ......); -- The SQL statement specifies that each command must end with a plus sign (;), for example, insert into test (name) values ("new insert 1 "); -- This statement inserts a "name" field as "new insert 1" in the test table. -- why is the field "nid" not inserted, but it is automatically filled with 6, which will be discussed later.

 

(2) Insert multiple rows of data (separated by commas)

Insert into table (field name, field name ......) Values (value, value ......), (Value, value ......), (Value, value ......), (Value, value ......), (Value, value ......)......; For example, use insert into test (name) values ("new insert 2"), ("new insert 3"), ("new insert 4 "), ("new insert 5"), ("new insert 6"), ("new insert 7"), ("new insert 8 ");

 

(3) Insert data from another table

Insert into Table 1 (Table 1 field name, Table 1 field name ......) Select Table 2 field name, Table 2 field name ...... From table 2

Select ...... From ...... The statement is a query statement, which means that the fields in table 2 that are queried later are inserted into Table 1. For example, I can use the following statement to re-insert all data in test into the test table:

insert into test(name) select name from test;

 

This statement may cause problems when you experiment on your computer. Most of the reasons may be that the field data type does not meet the requirements. For example, to insert the name column in table 2 to the age column in table 1, because name is of the varchar type and age is of the int type, the varchar type cannot be converted to the int type, so the program reports an error.

However, if the program inserts the age column into the name column, no error is reported because the int type can be converted to the varchar type.

Row operation -- Delete row operation -- modify row operation -- Query