Overview of Oracle

Overview of Oracle 1. multi-Table query: A Query of more than one table is called a multi-Table query. When querying multiple tables, you can specify an alias for the table to simplify the compilation of query columns, in a multi-Table query, a Cartesian product is generated, which is the result of multiplying the total number of two tables. To eliminate cartesian products, the join condition is used. Note the left and right connections. 2. grouping statistics and statistical functions-statistical functions: COUNT (), AVG (), SUM (), MAX (), MIN ()-statistical functions must appear in grouping statistics, the so-called GROUP is to specify repeated content in the column and use group by for grouping statistics. · If you want to add grouping conditions, write HAVING statements. All statistical functions can only appear in HAVING and cannot be used in WHERE. · When using grouping, you must note that grouping functions and grouping conditions can only appear during query. · The grouping function allows nesting, but other fields, including grouping fields, cannot be queried after nesting. 3. subquery contains another query, called a subquery. subquery can appear in any position: SELECT, FROM, WHERE. subquery can be associated with multiple tables, grouping statistics to complete complex query functions 4. database update operations · copy TABLE (Orcale) create table name AS (subquery); · add insert into table Name (field) VALUES (value) to TABLE data) · UPDATE table name SET field = value .... WHERE condition-data deletion delete from table name where delete condition 5. transaction Processing · all operations are either successful or all failed · In Orcale, each user connected to the database represents a session, and the modifications made by a session will not immediately modify the database, instead, they wait for the user to submit. The user uses the COMMIT or rollback to perform the rollback operation. In Orcale, a deadlock occurs and each other waits for the submission of the other party. 7. create a new table: specify the required fields and types. Copy a table: copy the structure or data of an existing table. If no data is required, you only need to write a condition that is never possible in the subquery. you can use drop table to delete a TABLE 9. truncated TABLE: truncate table. After a stage, the content of the TABLE cannot be rolled back and all resources will be released immediately. 10. constraints: · constraints on a single table: |-primary key constraint: primary key |-non-empty constraint: not null |-check constraints: CHECK |-UNIQUE constraint: UNIQUE-join Table constraint: |-foreign key constraint: foreign key, indicates that the values of the child table are associated with those of the parent table. |-delete the child table before deleting the parent table. |-if you want to delete the parent table directly, you can use Orcale's forcible means: |-drop table name cascade constraint |-CASCADE: on delete cascade when the parent TABLE is deleted Should be deleted. constraints are generally completed when the table is created. do not modify the table constraints as much as possible. · Note the naming rules for the constraint names 11. ROWNUM is a pseudo column that automatically exists, indicating the number of rows queried. You can partially retrieve the data in the data table (pagination) 12. union (union/union all) union (intersete) difference (minue) 13. TABLE modification: ALTER command-ADD column: alter table name ADD (field name, field type [, DEFAULT value])-MODIFY column: alter table name MODIFY (field name, field Type [, DEFAULT value]) · add constraint: alter table name add constraint name CONSTRAINT type (field); · Delete CONSTRAINT: alter table Name drop constraint name CONSTRAINT type (field );