Oracle SQL statements

1. Data Definition Language (DDL): these commands are mainly used by database administrators to create and delete database entities. For example: Create, alter, drop, and so on; 2. data manipulation language (DML): data manipulation language inserts records into the table, modifying records such as insert, update, delete, select .. fro update command and so on; 3. data Query Language (dql): Data Query Language, for example, basic SELECT query, order by, group by statements, etc. 4. transaction Control Language (TCL): transaction control language example: commit, sssacepoint, rollback5. Data Control Language (DCL): Date control language example: Grant, revoke

-- SYS: query the users created at a certain time Select * From all_users where CREATED-to_date ('1970-2-01 ', 'yyyy-mm-dd')> 0
-- Delete: deleting a large table is not released between time and space. It is very slow because it occupies a large amount of system resources and supports rollback. This table also occupies space. -- Truncate Table Name (tablespace is released when table records are deleted)
DML statement: Table-level shared lock: Row-level exclusive locks are not affected when different records in a table are operated: for a row of records, oracle only allows one user to modify the table at the same time. Wait () waits until the row-Level Lock is released to perform data operation drop on a table. It also locks the table and DDL exclusive locks, therefore, when deleting a table, if you still have a user who can operate the table, you cannot delete the alter table command to modify the table structure (these commands are not often used): Add constraints: Alter table table name Add constraint name primary key (field );Unconstraint: (delete constraint) alter table Table Name drop primary key (this method can be used directly for primary key constraints, because a table has only one primary key constraint name, note: If the primary key is referenced by another table, an error occurs when the primary key is deleted)
Alter tbale father drop primary key cascade;(If a sub-Table references a primary key, use this syntax to delete the primary key. In this case, the sub-table still has a foreign key constraint in the sub-table that is deleted and associated) Alter table Table Name drop constraint name;(How to Get a constraint name: 1. Manually obtain the violation according to the error message! 2. query the graph to obtain the constraint name !) Alter table table name disable from primary key;(Equivalent to disabling the primary key of a table) Alter table table name enable primary key;(Enable automatically checks whether the records meet the requirements,
If dirty data exists, you must first Delete the dirty data to enable it) **************************************** * *************************** add fields: Alter table table name Add (field type)Delete field: Alter table Table Name drop (field) Alter tbale Table Name drop column field;(Supported only after 8 I) renaming a column: 920 Alter table table name rename column old field name to new field name;Modify the field (in this case, you should pay attention to the problem. When changing the field type, it must be blank) Alter table table name modify (field, type)Modify fields in the table: Update table name set field = value where ConditionChange the rename old table name to the new table name. Delete the table: Trucate Table Name:(The table structure is still in progress, all data is deleted, the space occupied by the table is released, rollback is not supported, and large tables are often deleted) about the sequence generated in oralce (sequence ): create sequence name alter system flush shared_pool, in Oracle, in order to improve the generation sequence efficiency, 20 sequences are generated at a time and put into the sequence pool of the current session for backup to speed up the efficiency. The sequential rollback operation will not affect the sequence value)
Sequence parameters: Increment by n start value, start with N increment, maxvalue n maximum, minvalue n minimum,
Cycle | no Cycle cycle, cache n memory (the number of IDS retrieved at a time when the first time is retrieved) view the sequence diagram: DESC user_sequences; Select sequence_name, cache_size, last_number from user_sequences where Sequence_name like's _'; Select sequence name. currval from dual to view the current number of Sequences Select sequence name. nextval from dual to view the number of the next sequence. It will automatically add 1 to the current sequenceColumn: nextval currval (when another session is opened, the current value is not successfully obtained, the next value should be taken first, and then the current value) to clear the memory of the current session: Alter system flush shared_pool;(DBA permission is required to execute this command. Generally, an error occurs when the user executes the command.) modify the sequence: (this command is not commonly used. You only need to know it) Alter sequence name modification item;Delete sequence Sequence Drop sequence name;Create a graph: Creating views (knowledge) DESC user_views; select text from user_views where view_name = 'test1 _ V1 '; the graph is equivalent to a select statement, A graph is an SQL statement that does not occupy space.
It does not improve performance, but it can simplify SQL statements (extended knowledge: New Graph after Oracle 8i). The MV Materialized View (occupies the storage space and empty the select result)
And improves the query view and real-time performance. However, there is a refresh problem. It is mainly used in the data warehouse for aggregate tables.) The advantage of graph usage: control data access permissions. how to Create a graph: Create or replace views test_vi as select * From test1 where c1 = 1;When data is inserted into Table test1 (base table), the table does not change, and the data in the graph changes. when data is inserted in the graph, the corresponding table changes: when inserting data into the graph, it is directly inserted into the base table. when viewing the data in the graph, it is equivalent to executing the SELECT statement at the time of creation.
Sentence. Simple graph: DML operations can be performed. Complex Graph Display: It is from multiple tables and cannot perform DML operations. About rownum: rownum has a feature that is either equal to 1 or smaller than a value. It cannot be directly equal to or greater than a value. Rownum
It is usually used for pagination. Exercise: Query between 5th data records and 10th data records: Select first_name, rnum from (select rownum rnum, first_name from s_emp where
Rownum <= 10) Where rnum between 5 and 10; area display: Select * from (select. *, rownum R from s_emp A where r between 5 and 10); exercise: which employees have higher salaries than the average salaries of their respective departments? Select first_name, salary, avgsal from s_emp E, (select dept_id, AVG (salary
) Avgsal from s_emp group by dept_id) A where E. dept_id = A. dept_id and E. salary>
A. avgsal; adding a with check option on the graph is equivalent to adding the constraint create view test_v as select * from test where c = 1 with check option to the graph. Synonym: equivalent to the alias function (** only need to know **). The synonym used by the system is user_tables. Create synonym asd_s_emp for asd_0607.s_emp;The objective is to give the asd_0607_s_emp table another replacement name ASD. s_emp; note that this synonym can only be used by yourself; Create public synonym p_s_emp fro asd_0607.s_emp; create a public synonym, but require permissions. delete synonym: Drop synonym nameIndex creation: Creating indexes (the concept is very important to the system performance) index creation aims to speed up the query. The index is like the directory of a book. Index point system space, which belongs to the table's attachments. When deleting a table, the corresponding index is also
Will be deleted. The index structure is in the truncate table, but the data does not exist. Full table scan full table scan uses indexes to quickly locate data: (take the dictionary directory as an example) view the table's rowid: Select rowid, first_name from s_emp; the rowid defines the following information: Who creates the rowid index for each record of the object block table? user: After the index is created, the DML operation efficiency will be slow, however, the query efficiency is improved.
To create an index: Create Index name on table name (field name ); Create insex testindex on test (C1, C2 );Which fields should be indexed: The WHERE clause is often used, so the index should be used. The key is not to use the index.
The bigger the table is, the fewer records it queries, and the highest index efficiency. replace variable: Use the & symbol to define the replace variable. Interactive prompts are supported. For character numbers, set verify on set verify off between single quotes. This is equivalent to the switch variable, used to control whether to display new and old SQL statements Select ID, last_name, salary from s_emp where title = '& job_title ';Change the interaction prompt: accept p_dname prompt 'prompt information'; Define the variable: Define p_dname = 'abc'; implement the paging statement: (can run normally) Select * from (select rownum rnum, A. * from (select * From s_emp) a) Where rnum Between 5 and 10;