Oacle047 other learning materials

1. Regular Expression of question 3rd

Regular Expressions only search, replace, format, and other functions. Regular Expressions are usually used for back-reference formatting. length and concatenate are not calculated.

2. Delete columns with question 4th

When deleting a column, if the column has a contraint constraint, you must delete the column and cascade its constraint before deleting it.

3. Primary Key of question 5th

The primary key includes non-null and unique constraints. It will automatically create a unique index (note: the unique constraint will also automatically create a unique index)

4. 6th question synonym dynamic view dictionary table

Tables such as user_xx can only display their own tables.

Except the sys account, none of the other users can access the base table (v $) or other users.

5. 7th question Date Format

Note the usage of to_date and to_char.

Select extract (month FROM order_date) "Month" FROM gyj_order; // The method used to EXTRACT the month.

6. 9th question sequence

The default value of sequence is minvalue 1. After cycle is recycled, It is restored to 1.

10th question where multi-field Query

If you use the where statement to query multiple fields, the query fields on both sides must be in the same order, as shown below:

WHERE (manager_id, department_id) = (SELECT manager_id, department_id FROM

8. 11th sequential relationship between group by and where and having

SELECT column, group_function

FROM table

[WHERE condition]

[Group by group_by_expression]

[HAVING group_condition]

[Order by column];

9. 12th question connect by prior hierarchical Query

Top-down hierarchy in top-down and bottom-level queries. Hierarchical queries are tree-based queries. CONNECT BY PRIOR

10. Definition of subqueries related to question 13th

Oracle subqueries are classified into nested subqueries and non-nested subqueries. nested subqueries are non-correlated subqueries, that is, subqueries are a separate query, there is no correlation with the primary query of the outer layer. In this case, the entire query should first query the subquery. After the subquery finds the data, it will query the primary query and match it. A non-nested subquery is a related subquery. A subquery is not an independent query, and its query fields must correspond to the primary query, in this case, the entire query should first query the master query. After the subquery returns the data, it will query the subquery and match it.

Nested query is called: inner query (subquery, internal query), main query is called outer query (external query, main query)

11. 14th relationship between question permissions and Roles

Permissions can be granted to the role or to the user separately. when both parties are granted to the user, the permissions contained in the role can still be used by the user when the permissions are revoked.