[Gu JIU Jian] Oracle knowledge point sorting (4) SQL statement DML and DDL, dmlddl

[Gu JIU Jian] Oracle knowledge point sorting (4) SQL statement DML and DDL, dmlddl
Link navigation for this series:

[Lone sword] Oracle knowledge point sorting (1) Table space and users

[Gu JIU Jian] Oracle knowledge point sorting (2) database connection

[Gu JIU Jian] Oracle knowledge point sorting (3) Import and Export

[Dan JIU Jian] Oracle knowledge point sorting (4) SQL statement DML and DDL

[Dan JIU Jian] Oracle knowledge point sorting (5) Table and View of common database objects

[Lone sword] Oracle knowledge point sorting (6) Procedure, function, and Sequence of common database objects

[Dan JIU Jian] Oracle knowledge point sorting (7) database Common Object Cursor

[Lone sword] Oracle knowledge point sorting (8) Common exceptions

[Lone sword] Oracle knowledge point sorting (9) package of common database objects

[Gu JIU Jian] Oracle knowledge point sorting (10) % type and % rowtype and common functions

4. SQL statements

Oracle divides SQL statements into the following types:

A) Data operation language statement [Data manipulation language, DML], such as select, insert, update, delete, merge, lock, fetch, etc.

B) Data definition language statement [Data definition language, DDL], such as create, alter, drop, rename, truncate, grant, revoke, audit, noaudit, comment, etc.

C) transaction control statement [transaction control statement], such as commit, rollback, savepoint, and set transaction

D) session control statement [session control statement]
D.1 perform a specific operation to modify the current SESSION, for example, enabling or disabling the SQL trace function [SQL trace facility] (ALTER SESSION );
D.2 enable or disable a role [ROLE] (a SET of permissions) for the current session (SET role)

E) system control statement [system control statement]. alter system is the only system control statement.

F) embed an SQL statement [embedded SQL statement]
F.1 declare cursor, open, close ),
F.2 select an oracle database and connect to declare database, connect
F.3 assign variable name declare statement
F.4 initialization descriptor [descriptor] (DESCRIBE)
F.5 set how to handle errors and warnings (WHENEVER)
F.6 parse and execute SQL statements (PREPARE, EXECUTE, EXECUTE IMMEDIATE)
F.7 FETCH data from the database (FETCH)

I operate on the most part of a, B, c, and f.

4.1. DML4.1.1. Use standard SQL statements to add, delete, modify, and query syntaxes:

1 select * from table1;2 insert into table1(f1,f2,f3) values(v1,v2,v3);3 update table1 t set t.f1=v1 where t.f2=v2;4 delete table1 t where t.f2=v2;

Rownum is a special keyword in oracle. When you select a table, oracle will mark the query result. rownum is of the number type and starts from 1 each time, if 10 rows of data are queried, rownum is marked from 1 to 10 in the order of query results.

Rownum cannot be jumped. You can use where rownum = 1, but you cannot use where rownum = 2. You can use rownum <5, but you cannot use rownum> 5, where rownum = 0 can get the table structure.

4.1.2 paging query:

Select tt. * from (select t. *, rownum rn from table1 t where rownum <= 10) tt where tt. rn> = 1; -- this is the best example of using rownum.

4.1.3 joint Query

• Where Method Association:

1 select t1. *, t2. * from table1 t1, table2 t2 where t1.f1 = t2.f2 -- same as inner join2 select t1. *, t2. * from table1 t1, table2 t2 where t1.f1 (+) = t2.f2 -- same as left join3 select t1. *, t2. * from table1 t1, table2 t2 where t1.f1 = t2.f2 (+) -- same as right join

• Join Method Association:

1 select t1.* from table1 t12 join table2 t23 on t1.f1=t2.f24 where t1.fx>10 and t2.fy>100

The left join, right join, and outer join modes are the same as those of standard SQL.

4.1.4 merge with merge

This command uses a statement to update and insert data to a table from one or more data sources.

1/* example of two table Associations */2 merge into toTable a -- the table to be updated 3 using fromTable B -- the joined table (data source table) 4 on (. id = B. id) -- join condition 5 when metched then -- match the join condition and perform update Processing 6 update set. f1 = B. f1,. f2 = B. f2 + 17 when not metched then -- does not match the association condition, and inserts 8 insert values (B. f1, B. f2, B. f3 ...);

 

1/* example involving Association of multiple tables. There are three tables, table1 is the target table, and table2 and table3 need to be associated as the data source of table1; only update operations */2 merge into table1 a3 using (select t2.id, t2.f1, t2.f2, t3.f1 from table t2 join table2 t3 on t2.id = t3.id) b4 on (. id = B. id) 5 when matched then6 update set. f1 = B. f1,. f2 = B. f2 + 1;

Note:

4.2 DDL

Create is used to create tables, views, procedure, functions, sequence, and so on.
Alter
Drop is used to delete tables, views, procedure, functions, sequence, and so on.
Grant and revoke are used to grant and revoke permissions to users.
Audit and noaudit for Auditing
Comment is used to add remarks.
Truncate is used to cut the table and cannot be rolled back.
Rename

Section 5th describes table, view, procedure, function, and sequence, all DDL statements are used.

4.3. Other types of SQL statements (￣ )"