SQL statement Operations Summary in Java

1. Add Record ( INSERT )

Use the Insert command of the SQL statement to insert a record into the database, with the following basic form of the Insert command:

INSERT into table name [(Field name 1, field Name 2 ...)] Values (value 1, value 2,...)

If you enter a record, each field has content, and you can save the field name after the token name.

The SQL statement is used to value 1, value 2 ... The value n is assigned To field 1, Field 2, ... Field N,

and add a record to the table. There are two rules to be aware of when using this command:

The inserted value must correspond to column one by one.

The data type of the inserted value must match the data type of the corresponding column.

For example, use the following command to add a record to the table Stu:

Insert into Stu values (1000,′ li ′,′ male ′,99)

Note: The input data is enclosed in parentheses, and the data items are separated by commas.

string and date values must be enclosed in single quotation marks.

Numeric data is not enclosed.

The data order must correspond to the field order.

All punctuation marks are entered in the English half-width state.

2. Delete Records ( DELETE )

Deleting a database uses the delete command, which has two DELETE statements, a conditional DELETE statement, and a DELETE statement without a condition. The DELETE statement is formatted as follows:

DELETE from < table name > [WHERE condition]

For example, if you want to delete all records in the Stu table that have less than 60 of the score field, you can write:

DELETE from Stu WHERE score<60

n The above sake if no condition is specified and there is no where score<60 then all records in the database will be deleted such as:

DELETE from Stu

n This time the Stu table will become a blank table.

3. Update record (updated)

Use the update command to modify the eligible records in the database. The update command has the following format:

UPDATE < table name > SET field 1= value 1 [, field 2= value 2 ...] WHERE < conditions >

Use this command to modify all records that meet the Where condition, assigning the value 1 To field 1 ....

The update command needs to indicate the table name and the value of the field to be changed, and the value of the field is always guided by the reserved word set, which tells the SQL which field to change and how to change the value.

For example, the following command adds 5 to the score value of all records in the Stu table that have IDs greater than 900:

UPDATE Stu SET score=score+5 WHERE id>900

Note: If you do not add a condition, it will be a modification to all records in the table.

4. Filter queries

You can use the WHERE clause of select to filter the query results of a table. The format is:

Select < Field name 1> [field Name 2,...] from < table name > where < condition >

For example, to query the data in the Stu table for score values greater than 60, you can write:

SELECT * from Stu where score>60

If you want to query data that has a score value greater than 60 in the Stu table and sex is "male," the statement should read:

SELECT * from Stu where score>60 and sex= "male"

in the JAVA uses SQL statements to manipulate practical examples:

1. Input data

Add a row of data to the UserInfo table

String sql = "INSERT into userinfo values ('li', 15487);";

2. Delete Data

Delete a record of a user as Li in the UserInfo table

String sql = "Delete from userinfo where Username=li";

Username the field name for the user

3. Query Data

query The records for the user Li in the UserInfo table

String sql = "SELECT * from Stu where Userinfo=li";

4. Modify the Data

Change the pwd field value of the record that has the Username field value of num in the UserInfo table to Wang

String sql= "Update userinfo set pwd= '" +wang+ "' where username =" +num+ "";

SQL statement Operations Summary in Java