There are three main types of data changes in the database: insert data (Insert), Modify data (Update), Delete data (Delete). These operations are commonly used by developers. This chapter briefly introduces these operations. The main contents of this chapter include:
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.
1Insert data-- InsertOperation
InsertThe operation is used to insert new data into the table.InsertThe operation can be used in combination with single insert or subquery to implement batch insert. This chapter describesInsertOperation syntax and usage.
1.1Single insert
ForInsertFor operations, single insert is the most common method. Its syntax is as follows.
InsertTable Name(Column name1,Column name2,Column name3...Column nameN)
Values (Value1,Value2,Value3...ValueN)
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 followingSQLThe statement inserts new data into the table. The new data comes from the table.StudentsMediumStudent_idTop 10 student information.
SQL> insert into c_students (student_id, student_name)
Select student_id, student_name
From students
Where student_id <= 10;
1.3Precautions and skills
InsertThe operation syntax is simple, and it is also the most proficient by developers.SQLStatement. Pay attention to the following issues and skills during actual development.
1. Should develop the habit of using the column Name List
2. Quick retrieve column Name List
2Update Data-- UpdateOperation
UpdateOperation is used to update existing data.
2.1 updateUpdate a single column
UpdateThe operation syntax is as follows.
UpdateTable NameSetColumn=New Value
WhereUpdateCommand is used to update table data, followed by the table name;SetCommand to reset the column value, followed by the column name, and specify a new value with an equal sign.
In the tableStudentsColumnStatusThe values are in lowercase. We can useUpdateThe statement is converted to uppercase.
SQL> update students set status = upper (status );
2.2 updateUpdate multiple columns
UseUpdateStatement. You can also update multiple columns. The syntax is as follows.
UpdateTable NameSetColumn1 =New Value1,Column2 =New Value2 ,...
InSetAfter the command, you can assign values to multiple columns at the same time, and these columns are separated by commas.
For tablesStudentsStudent Information in, you can modify the columnStatusAt the same time, modify the columnStudent_age. For exampleStatusIn addition to the capitalized conversion, you also need to change the student ageStudent_ageAdd1. CorrespondingSQLThe statement is as follows.
SQL> update students set student_age = student_age + 1, status = upper (status );
2.3Notes
ForUpdateOperations, the most easily overlooked is to addWhereCondition. In the example5-3And examples5-4The operations performed are actually very dangerous. Because no restrictions are addedOracleAll data in the table will be updated. In practical applications, only part of the data needs to be updated. Therefore, for important dataUpdateOperation, first addWhereKeyword is a good habit, although sometimes you do not need filter conditions.
IsUpdateAdd operationWhereCondition.
SQL> update students set student_age = student_age + 1
Where student_age> 20;
3Delete data-- DeleteOperation
DeleteOperation is used to delete data in a table. BesidesDeleteStatement,TruncateThe command can also delete table data.
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 useDeleteCommand to delete a tableStudentsMedium and,Employee_idGreater10Records, correspondingSQLThe statement is as follows.
SQL> delete from students where student_id> 10;
3.2 deleteOperation andTruncate tableOperation
BesidesDeleteCommand,OracleYou can also useTruncate tableCommand to delete table data. HoweverTruncate tableStatement andDeleteStatements are essentially different.DeleteStatement andInsert,UpdateStatementDML --The category of the data operation language. After the data is modified, you can roll back to ignore the data modification. WhileTruncate tableThe statement isDDL --Category of a Data Definition Language. data cannot be rolled back after it is deleted.
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. The upper function will be introduced later: Convert 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 them.
Query all employees whose names contain smith.
SELECT * FROM emp WHERE ename = UPPER ('Smith '); initcap function: uppercase letters 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: query whether a specified string exists in a string. If yes, returns its location.
SELECT instr ('hello', 'x') FROM dual;
• If this string exists, the return position is returned. If no string exists, 0 is returned. Replace function: replace the specified string in a string with other content:
SELECT replace ('hello', 'l', 'x') FROM dual; trim function: Remove left and right Spaces
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,) = '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 () 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 that is added several months after the date:
After three months, the date is the day:
SELECT ADD_MONTHS (sysdate, 3) FROM dual; NEXT_DAY: indicates the next day (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; for example:
Displays the number of employees who have been employed for less than 320-month, employment date, number of employment months, review date for six months, first Friday after employment, and last day of employment for the current month
Obtain the information of an employee whose number is more than 320 in a month:
SELECT * FROM emp WHERE MONTHS_BETWEEN (sysdate, hiredate)> 320;
Overall:
SELECT empno employee number, hiredate employment date,
Round (MONTHS_BETWEEN (sysdate, hiredate) number of months of employment,
Add_months (hiredate, 6) review date, next_day (hiredate, 'Friday') after employment to the first Friday,
Last_day (hiredate) Last Day
FROM emp WHERE MONTHS_BETWEEN (sysdate, hiredate)> 320;
For example, you need to retrieve the year in which all employees are employed.
In fact, we need to separate the content in hiredate and retrieve the year.
SELECT empno, ename, TO_CHAR (hiredate, 'yyyy') FROM emp;
Take out the month and day of employment:
SELECT empno, ename, TO_CHAR (hiredate, 'yyyy,
TO_CHAR (hiredate, 'mm') month, TO_CHAR (hiredate, 'dd') month FROM emp; you can also use this function to modify the format of the displayed Date:
-Month-day:
SELECT empno, ename, TO_CHAR (hiredate, 'yyyy-MM-DD ') FROM emp; normal date, if it is month, it should be two, if it is February, it will display February. If you do not want to display the leading 0, you can add a fm
SELECT empno, ename, TO_CHAR (hiredate, 'fmyyyy-MM-DD ') FROM emp;
You can output a value in the specified format.
SELECT empno, sal FROM emp; at this time, the queried salary is not clearly known, and the number of wages is not standard.
To_char: SELECT empno, to_char (sal, 'l99, 999 ') FROM emp; to_date function:
You can change a string to a date type.
SELECT to_date ('1970-4-7 ', 'yyyy-MM-DD') FROM dual; requirements:
Query the annual salary (basic salary and Commission) of all employees ).
SELECT empno, (sal + comm) * 12 FROM emp;
If there is no bonus for an employee, the annual salary is also gone. Because it is null, the final calculation result is still null.
If the final value is null, it is expected to be calculated as 0. In this case, the NVL function is required. The Nvl function can change a null value to a specific value.
SELECT empno, sal, NVL (comm, 0) FROM emp;
Use this function to modify the preceding query:
SELECT empno, (sal + NVL (comm, 0) * 12 FROM emp;
Select empno, ename, job from emp;
Hope:
• If the job is CLERK: The CLERK is displayed
• For SALESMAN: sales personnel
• If the job is MANAGER, it is displayed as MANAGER
SELECT empno, ename, DECODE (job, 'cler', 'clerk ', 'salesman', 'salesperson ', 'manager', 'manager') FROM emp;
