Consulta com SELECT |
O comando SELECT extrai dados da base de dados, implementando os operadores da álgebra relacional.
Para ver todas as tabelas que o utilizador tem disponíveis podemos consultar DICTIONARY:
select * from dictionary; |
TABLE_NAME COMMENTS ------------------------------ ---------------------------------------------------------------------------- USER_RESOURCE_LIMITS Display resource limit of the user USER_PASSWORD_LIMITS Display password limits of the user USER_CATALOG Tables, Views, Synonyms and Sequences owned by the user ALL_CATALOG All tables, views, synonyms, sequences accessible to the user USER_CLUSTERS Descriptions of user's own clusters ALL_CLUSTERS Description of clusters accessible to the user USER_CLU_COLUMNS Mapping of table columns to cluster columns USER_COL_COMMENTS Comments on columns of user's tables and views ALL_COL_COMMENTS Comments on columns of accessible tables and views USER_COL_PRIVS Grants on columns for which the user is the owner, grantor or grantee ALL_COL_PRIVS Grants on columns for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee USER_COL_PRIVS_MADE All grants on columns of objects owned by the user ALL_COL_PRIVS_MADE Grants on columns for which the user is owner or grantor USER_COL_PRIVS_RECD Grants on columns for which the user is the grantee ALL_COL_PRIVS_RECD Grants on columns for which the user, PUBLIC or enabled role is the grantee ALL_ENCRYPTED_COLUMNS Encryption information on all accessible columns USER_ENCRYPTED_COLUMNS Encryption information on columns of tables owned by the user USER_DB_LINKS Database links owned by the user ALL_DB_LINKS Database links accessible to the user USER_INDEXES Description of the user's own indexes ALL_INDEXES Descriptions of indexes on tables accessible to the user USER_IND_COLUMNS COLUMNs comprising user's INDEXes and INDEXes on user's TABLES ALL_IND_COLUMNS COLUMNs comprising INDEXes on accessible TABLES USER_IND_EXPRESSIONS Functional index expressions in user's indexes and indexes on user's tables ALL_IND_EXPRESSIONS FUNCTIONAL INDEX EXPRESSIONs on accessible TABLES USER_JOIN_IND_COLUMNS Join Index columns comprising the join conditions ALL_JOIN_IND_COLUMNS Join Index columns comprising the join conditions USER_OBJECTS Objects owned by the user ALL_OBJECTS Objects accessible to the user USER_PROCEDURES Description of the users own procedures ALL_PROCEDURES Description of all procedures available to the user ALL_STORED_SETTINGS Parameter settings for objects accessible to the user USER_STORED_SETTINGS Parameter settings for objects owned by the user USER_PLSQL_OBJECT_SETTINGS Compiler settings of stored objects owned by the user ALL_PLSQL_OBJECT_SETTINGS Compiler settings of stored objects accessible to the user ALL_ARGUMENTS Arguments in object accessible to the user USER_ARGUMENTS Arguments in object accessible to the user USER_RESUMABLE Resumable session information for current user USER_ROLE_PRIVS Roles granted to current user USER_SYS_PRIVS System privileges granted to current user USER_SEQUENCES Description of the user's own SEQUENCEs ALL_SEQUENCES Description of SEQUENCEs accessible to the user USER_SYNONYMS The user's private synonyms ALL_SYNONYMS All synonyms for base objects accessible to the user and session USER_TABLES Description of the user's own relational tables USER_OBJECT_TABLES Description of the user's own object tables USER_ALL_TABLES Description of all object and relational tables owned by the user's ALL_TABLES Description of relational tables accessible to the user ALL_OBJECT_TABLES Description of all object tables accessible to the user ALL_ALL_TABLES Description of all object and relational tables accessible to the user USER_TAB_COLS Columns of user's tables, views and clusters ALL_TAB_COLS Columns of user's tables, views and clusters USER_TAB_COLUMNS Columns of user's tables, views and clusters ALL_TAB_COLUMNS Columns of user's tables, views and clusters USER_NESTED_TABLE_COLS Columns of nested tables ALL_NESTED_TABLE_COLS Columns of nested tables USER_TAB_COL_STATISTICS Columns of user's tables, views and clusters ALL_TAB_COL_STATISTICS Columns of user's tables, views and clusters USER_TAB_HISTOGRAMS Histograms on columns of user's tables ALL_TAB_HISTOGRAMS Histograms on columns of all tables visible to user USER_TAB_COMMENTS Comments on the tables and views owned by the user ALL_TAB_COMMENTS Comments on tables and views accessible to the user USER_TAB_PRIVS Grants on objects for which the user is the owner, grantor or grantee ALL_TAB_PRIVS Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee ... |
Para vermos quais as tabelas do utilizador consultamos a vista USER_TABLES. Para vermos todos os objectos do utilizador consultamos a vista USER_OBJECTS:
select object_name,object_type,created,last_ddl_time,status from user_objects; |
OBJECT_NAME OBJECT_TYPE CREATED LAST_DDL_ STATUS ----------------- ------------------ --------- --------- ------- BONUS TABLE 12-OCT-02 12-OCT-02 VALID DEPT TABLE 12-OCT-02 12-OCT-02 VALID DUMMY TABLE 12-OCT-02 12-OCT-02 VALID EMP TABLE 12-OCT-02 12-OCT-02 VALID SALGRADE TABLE 12-OCT-02 12-OCT-02 VALID |
Para listar todos os números de departamento, nomes de empregado e número do chefe da tabela de Empregados (EMP):
select deptno,ename,mgr from emp; |
DEPTNO ENAME MGR ---------- ---------- ---------- 20 SMITH 7902 30 ALLEN 7698 30 WARD 7698 20 JONES 7839 30 MARTIN 7698 30 BLAKE 7839 10 CLARK 7839 20 SCOTT 7566 10 KING 30 TURNER 7698 20 ADAMS 7788 30 JAMES 7698 20 FORD 7566 10 MILLER 7782 14 rows selected. |
Para listar todas as colunas da tabela emp:
SELECT * FROM emp; |
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --------- ---------- --------- --------- --------- --------- --------- --------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected. |
Realizado por Turtle Learning ®. Última alteração em 2011-02-26