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;