Oracle Common Commands __oracle

1. List all tables for the current user:

SELECT * from tab;

2.sqlplus can also execute commands on the operating system itself. The command format is: HOST operating system command

For example, open the Windows registry, use: Host regedit; Open the word this program uses: Host Notepad

3. Exit Sqlplus

Exit or quit

4.help command

Format: Help command name

such as: Help DESC

5. Clear Screen Command

Clear screen or clear src

The host CLS can also be used in sqlplus; to clear the screen

6. Show Current User

Show user

7.set Linesize Set line width

8.set PageSize Set the number of rows per page

9.distinct keyword to eliminate duplication

10.in and not in

11.and,or,not,between...and,order By,like

12.SQL (Structured Query language) structure query language

DML (manipulation Language) data manipulation language---used to retrieve or modify data

The DDL (data definition Language) Data definition language---the structure used to define data, such as creating, modifying, or deleting database objects

DCL Language Data Control Language---to define permissions for database users

13. In the database system, the biggest difference between each database is the function support, the USE function can complete a series of operation function.

14. Using Ed Buf.sql (name of the file), a new Buf.sql text file will be created, and our SQL script can be written on buf.sql. Save, and then run @buf.sql (for *.sql form of text, run directly @buf.sql also can).

You can execute the SQL script that you just saved on the buf.sql.

15. For 14, using the start filename is also possible. For example, the start Buf.sql

16. A view is a special kind of table, a virtual table built on a base table. It can consist of some rows and columns selected in a base table, or a data row that satisfies a certain condition in several tables. The view reflects the local of one or more base tables

data, but it does not hold the data itself, and its data is still stored in the base table. Therefore, a view is a data window for a base table, but it can manipulate the data in a table through a view. A view is a means of achieving confidentiality and data security for data,

It also simplifies the access to the database. To refer to a view you must create a view, and after the view is created, you can query, add, delete, and so on the data in the database table through the view. For a view that has been created, if you no longer use the

You can also delete it.

The general format for creating views is:

Create view view name (View list name)

As [SELECT statement]

For example, create a view that contains information about the employee name, project, monthly salary, and total payroll of employees in Sector 10th. You can enter the following command:

Create index Emp10_view (person,title,salary,annual_sal)

As

Select ename,job,sal,sal*12 from emp where deptno = 10;

Use of views

A view is a data window of a base table that allows you to query, add, delete, and modify data in the base table, which, for the user, can be manipulated as if it were a table.

Request through the View Emp10_view inquires the 10th Department to pay below 5000 yuan staff's information. and displayed in order of wages from high to low.

SELECT * from Emp10_view where salary<5000 order by salary desc;

Check the base table EMP query for information about workers in sector 10th who pay less than 5000 yuan. and displayed in order of wages from high to low.

SELECT * from emp where sal<5000 and deptno=10 the order by Sal Desc;

Executing a query through a view is the same result as executing a query through the corresponding base table.

The view is queried by the fact that the user queries the data in the database base table through the views. However, using the view simplifies the user query command, because the view-defined subquery can have certain conditions that make the table

Some of the conditions for complex queries can be placed in the view definition, so users can get the same information through the view without complex conditions.

The condition used by the view query "salary<5000" is equivalent to the portion of the query condition "deptno=10 and sal<5000" used in the base table.

By the view of the table to add to the data in the table, delete, change, and other update operations, as on the base table operation, but need to note the following points:

Delete action (delete)

① can only operate on views created by Ico

② defines a subquery for a view that does not contain a GROUP BY clause, DISTINCT clauses, and individual grouping functions.

Update action (updated)

① can only operate on views created by Ico

② defines a subquery for a view that does not contain a GROUP BY clause, DISTINCT clauses, and individual grouping functions.

No update column with an expression is defined in the ③ subquery

Insert Action (Inserts)

① can only operate on views created by Ico

② defines a subquery for a view that does not contain a GROUP BY clause, DISTINCT clauses, and individual grouping functions.

No update column with an expression is defined in the ③ subquery

④ defines all the "not NULL" columns in the base table of the view to appear in the view.

Delete View

Drop View View Name

17. Subqueries cannot contain an ORDER BY clause because the results of a subquery are not displayed and are only criteria for the main query.

18.in and not in indicate whether or not the semantics of any representation is "at least more than ...". All says that the doctrine is "more than all".

19. Indexes (Index)

① Index Creation

Create [unique] index name on table name (field ...)

For example: Establish a Uniqueness index for the EMP table with empno as indexed columns

Create unique index index_no on EMP (EMPNO);

② Delete Index

Drop INDEX Index name

20.oracle Time function

① Current Date

Select Sysdate from dual; or select current_date from dual;

②to_char function

Date and year of acquisition

Select Hiredate,to_char (hiredate, ' YYYY ') Year,to_char (hiredate, ' MM ') Month,to_char (hiredate, ' DD ') day from EMP;

21. Commonly used focus function

Count (): Find all the record seeds

Max (): Find the maximum value in a group

Min (): Find the minimum value in a group

AVG (): Find the average

SUM (): Sum

22. Grouped statistics

Example: Find out the number of employees in each department

Select Deptno,count (empno) from the EMP group by DEPTNO;

The following SQL script is wrong: select Deptno,max (SAL) from EMP; prompts: Not a single group of grouped functions. The following should be followed:

① If you use a grouping function in your program, there are two things you can use.

A group by IS present in the program and a grouping condition is specified so that the grouping criteria can be queried together.

If you do not use grouping, you can only use grouping functions separately

② when working with grouping functions, fields other than grouping functions and grouping criteria cannot appear

Select Deptno,empno,count (empno) from the EMP group by DEPTNO;

There will be an error, suggesting that Empno is not a group by expression. This violates the ②.

③ grouping functions can only be used in groups and are not allowed to appear in the WHERE clause. You can only pass the HAVING clause.

④ all grouping functions ignore null values (NULL), except for COUNT (*) (Note that only * is not ignored).

23.union operator

The UNION operator derives a result table by combining the other two result tables (such as TABLE1 and TABLE2) and eliminating any duplicate rows in the table. (You need to remember that it removes duplicate rows) when all is used with union (that is, union ALL), duplicate rows are not eliminated. In both cases, each row of the derived table is either from TABLE1 or from TABLE2. Using union you must ensure that two intermediate results have the same number of columns, and that the data types of the corresponding columns are the same.

24. Table Copy

Create Talbe table name as SELECT * from Talbe_name