Curd in the Oracle View

1. Use the create or replace view command to create a view

Syntax format:

Create or replace view name

As select query clause

Example:

Create or replace view tb_user_view1 as select count (username) shuliang, username from tb_user group by username order by shuliang asc

2. Run the create or replace view command to modify the view.

Syntax format:

Create or replace view name

As select query clause

Example:

Create or replace view tb_user_view1 as select * from tb_user where username = 'A'

3. Use the drop view command to delete a view

Format:

Drop view name

Example:

Drop view tb_user_view1

4. operate basic tables using views

--- Add data rows (directly affecting basic tables)

Insert into tb_user_view1 values (8, 'insert', 5)

--- Modify data rows (affects basic tables)

Update tb_user_view1 set username = 'update' where bh = 3

--- Delete data rows (directly affecting basic tables)

Delete from tb_user_view1 where bh = 2

Create a view by joining multiple tables

Format: create or replace force view name (list name 1, list name 2 ,......, List name n) as select query clause

Example:

Create or replace force view tb_username_address (bh, username, address_id, address)
SELECT u. bh, u. username, a. bh, a. address FROM tb_user u, tb_address a WHERE u. address_id = a. bh

Modify View

Format: create or replace force view name (list name 1, list name 2 ,......, List name n) as select query clause

Example:
Create or replace force view tb_username_address (bh, username, address_id, address)
SELECT u. bh, u. username, a. bh, a. address FROM tb_user u, tb_address a WHERE u. address_id = a. bh and a. address = 'xinxiang'
Drop view command Delete view

Format:

Drop view name

Example: drop view tb_username_address

Delete data rows (affects basic tables ):

Example: delete tb_username_address where bh = 5

When a view comes from multiple basic tables, data cannot be added.

Modify data rows (affecting basic tables ):

Example: update tb_username_address set username = 'updaten' where bh = 6