4. Add, delete and modify rows of Oracle database SQL development
Welcome to reprint, reprint please indicate the source: blog.csdn.net/notbaron/ar… \
Perform add, modify, and DELETE operations on database tables using the following SQL statements: INSERT,UPDATE, and DELETE. The COMMIT statement can be used to permanently save changes to rows, and the ROLLBACK statement can be used to undo changes to rows.
1. Add rows to the table
View the Customers table.
SQL> desc customers;
Name Null? Type
————————————————- —————————-
CUSTOMER_ID NOT NULL NUMBER(38)
FIRST_NAME NOT NULL VARCHAR2(10)
LAST_NAME NOT NULL VARCHAR2(10)
DOB DATE
PHONE VARCHAR2(12)
Find that the CUSTOMER_ID, FIRST_NAME, and LAST_NAME columns are NOT NULL and you must provide values for these columns. DOB and PHONE do not need to provide values.
Insert as follows:
SQL> insert into customers (
customer_id,first_name,last_name,dob,phone ) values (
6,’Fred’,’Brown’,’01-JAN-1970′,’800-555-1215′);
Then look at
SQL> select * from customers;
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
———– ———- ———- ———————
6 Fred Brown 01-JAN-70 800-555-1215
1 John Brown 01-JAN-65 800-555-1211
2 Cynthia Green 05-FEB-68 800-555-1212
3 Steve White 16-MAR-71 800-555-1213
4 Gail Black 800-555-1214
5 Doreen Blue 20-MAY-70
6 rows selected.
2. Modify existing rows in the table
Use the UPDATE statement to modify existing rows in the table.
Such as:
SQL>update customers set last_name=’Orange’ where customer_id=2;
1 row updated.
SQL>select * from customers where customer_id=2;
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
———– ———- ———- ———————
2 Cynthia Orange 05-FEB-68 800-555-1212
3. Delete rows from the table
The DELETE statement is used to DELETE rows from a table. Typically, a WHERE clause is used to restrict the rows that you want to delete.
SQL> delete from customers wherecustomer_id=6;
1 row deleted.
SQL> rollback;
Rollback complete.
Use COMMIT to permanently save changes made to rows.