Oracle Study Notes: SQL updates data and some common functions

There are three types of data changes in the database: insert, update, and delete ). These operations are common to developers.

There are three types of data changes in the database: insert, update, and delete ). These operations are common to developers.

There are three main types of data changes in the database: insert data (

InsertOperations and precautions

UpdateOperations and precautions

DeleteOperations and precautions

Through the study in this chapter, readers can learnInsert/update/deleteThe basic operation syntax. You can also understand the precautions in actual development.

1

Insert

1.1Single insert

ForInsertFor operations, single insert is the most common method. Its syntax is as follows.

Insert

Values (

InInsertIn the operation, the columns in the column name list must be separated by commas. The Value List specifies the values of each column. The column names and values must correspond to each other.

1.2Batch insert

TableC_studentsThe data structure and content are as follows.

SQL> select * from c_students;

STUDENT_ID STUDENT_NAME

We can use the following

SQL> insert into c_students (student_id, student_name)

Select student_id, student_name

From students

Where student_id <= 10;

1.3Precautions and skills

Insert

1. Should develop the habit of using the column Name List

2. Quick retrieve column Name List

2

UpdateOperation is used to update existing data.

2.1 updateUpdate a single column

UpdateThe operation syntax is as follows.

Update

Where

In the table

SQL> update students set status = upper (status );

2.2 updateUpdate multiple columns

UseUpdateStatement. You can also update multiple columns. The syntax is as follows.

Update

InSetAfter the command, you can assign values to multiple columns at the same time, and these columns are separated by commas.

For tables

SQL> update students set student_age = student_age + 1, status = upper (status );

2.3Notes

For

Is

SQL> update students set student_age = student_age + 1

Where student_age> 20;

3

Delete

3.1 deleteOperation

DeleteThe operation is used to delete data in a table. Its syntax is as follows.

Delete fromTable Name

Delete fromSpecifies the table from which data is deleted. Because the target object of the delete action is at the record level, you do not need to specify the column name information.

We can use

SQL> delete from students where student_id> 10;

3.2 delete

Besides

SQL> rollback;

Lower function:
This function can change all uppercase letters to lowercase letters.
SELECT lower ('abcde') FROM dual;
Dual table is a virtual table.

Upper function: converts the input string to uppercase letters.
SELECT upper ('abcd') FROM dual;

For example, the query is case-sensitive. Therefore, if you enter lowercase letters, you can use the upper function to convert the data.
Query all employees whose names contain smith.
SELECT * FROM emp WHERE ename = UPPER ('Smith ');

Initcap function: capital the first letter of each string
For example, the first letter of all employee information in the employee table must be capitalized.
SELECT initcap (lower (ename) FROM emp;
Functions can be nested.

Concat function: String connection. Two strings can be connected.
SELECT concat ('hello', 'World !!! ') FROM dual;
You can also use "|" to connect two strings.

Substr function: String Truncation
When intercepting, you must note the length of the part starting from there.
SELECT substr ('hello', 1, 2) FROM dual;
Note: the start point of the substr function starts from 1.

Length: extract the length of a string. For example, extract the length of each employee's name:
SELECT ename | 'Name length:' | length (ename) FROM emp;

Instr function: checks whether a specified string exists in a string. If yes, the position of the specified string is returned.
SELECT instr ('hello', 'x') FROM dual;
If this string exists, the position is returned. If not, 0 is returned.

Replace function: replace the specified string in a string with other content:
SELECT replace ('hello', 'l', 'x') FROM dual;

Trim function: removes spaces between left and right.
SELECT trim ('hello') FROM dual;

Query:
1. Search for employees whose last letter is N
SELECT * FROM emp WHERE substr (ename,-1, 1) = 'n ';
2. Retrieve Information about all employees whose positions are "SALE"
SELECT * FROM emp WHERE substr (job, 1, 4) = 'sale ';

ROUND indicates rounding
Select round (34.56,-1) FROM dual;
TRUNC function: Intercept function
Select trunc (34.56,-2) FROM dual;
MOD function: returns the remainder.
Select mod (10, 3) FROM dual;

Get current date:
In Oracle, You can query sysdate to get the current date:
SELECT sysdate FROM dual;

For example, calculate the number of days for employees in 10 departments to enter the company:
Use the current date-employment date (hiredate) = days, and the number of days/7 is the number of days.
SELECT ename, round (sysdate-hiredate)/7) from emp;

For example:
Obtain the number of months of employment for all employees: months_between, and compare the current date with the employment date.
SELECT ename, round (MONTHS_BETWEEN (sysdate, hiredate) from emp;

ADD_MONTHS: indicates the date after a few months added Based on the date:
After three months, the date is the day:
SELECT ADD_MONTHS (sysdate, 3) FROM dual;

NEXT_DAY: indicates the next day of the week)
SELECT NEXT_DAY (sysdate, 'monday') FROM dual;

LAST_DAY: Find the last day of the month where the current date is located:
SELECT LAST_DAY (sysdate) FROM dual;