Oracle Common Command Daquan

View the role of the current user
SELECT * from User_role_privs;
View system and table-level permissions for the current user
SELECT * from User_sys_privs;
SELECT * from User_tab_privs;
View all the tables under the user
SELECT * from User_tables;
View all views under the user
Select View_name from User_views;
View constraints on a table
Select Constraint_name, Constraint_type,search_condition, R_constraint_name
From user_constraints WHERE table_name = upper (' table name ');

I. Data Control statement (DML) section

1.INSERT (insert a recorded statement into the data table)

INSERT into table name (field name 1, field Name 2, ...) Values (value 1, value 2, ...);
INSERT into table name (field name 1, field Name 2, ...) SELECT (field name 1, field Name 2, ...) from another table name;

field values for string types must be enclosed in single quotation marks, for example: ' Good day '
If the field value contains a single quotation mark ' required for string conversion, we replace it with two single quotes '.
A string type with a field value that exceeds the defined length can be an error, preferably a length check before inserting.

field values for date fields can be sysdate with the current database's system time, accurate to seconds
or convert a string into a date-type function to_date (' 2001-08-01 ', ' yyyy-mm-dd ')
To_date () also has a number of date formats, which can be found in Oracle DOC.
Year-month-day hour: minute: Format of seconds Yyyy-mm-dd HH24:MI:SS

The maximum operand length of an insert is less than or equal to 4,000 single bytes, and if you want to insert a longer string, consider the field with the Clob type,
method to borrow the Dbms_lob package that comes with Oracle.

If you want to use a serial number that automatically grows from 1 when you insert it, you should first create a serial number
Name of the CREATE SEQUENCE serial number (preferably table name + serial number tag) INCREMENT by 1 START with 1
MAXVALUE 99999 CYCLE NOCACHE;
Where the maximum value is determined by the length of the field, if the defined auto-grow serial number is no. (6), the maximum value is 999999
Insert statement inserts this field value as: The name of the serial number. Nextval

2.DELETE (Delete statements recorded in the data table)

DELETE from table name WHERE condition;

Note: Deleting a record does not release the block table space that is occupied in Oracle. It only marks those chunks of data that have been deleted as unused.

If you are sure you want to delete all the records from a large table, you can use the TRUNCATE command, which frees up the data block table space that is occupied
TRUNCATE table name;
This action is not fallback.

3.UPDATE (Modify the statement recorded in the data table)

Update table name SET field name 1= value 1, field name 2= value 2, ... WHERE condition;

If the modified value n is not assigned or defined, it will clear the original record content to null, preferably before the modification of non-null check;
A value of n exceeds the defined length error, preferably a length check before inserting:

Precautions:
A. The above SQL statement adds row-level locks to the table.
Once the confirmation is complete, you must add the command COMMIT to the end of the transaction to formally take effect.
Otherwise, changes are not necessarily written to the database.
If you want to recall these operations, you can restore them with the command ROLLBACK.

B. Before running insert, DELETE and UPDATE statements, it is best to estimate the range of records that might be manipulated.
It should be confined to a smaller (10,000 records) range. Otherwise, Oracle handles this thing with a large fallback segment.
The program responds slowly or even loses its response. If you record more than 100,000 of these operations, you can divide these SQL statements into sub-sections,
Plus commit to confirm things processing.
Two. Data definition (DDL) section

1.CREATE (Create tables, indexes, views, synonyms, procedures, functions, database links, etc.)

Oracle's Common field types are
CHAR fixed-length string
VARCHAR2 variable-length strings
Number (m,n) digit M is the total length of the digits, and N is the length of the decimal
Date type

When you create a table, you put smaller, non-empty fields in front of them, and possibly empty fields behind

You can use a Chinese field name when creating a table, but it's best to use the field name in English

You can add a default value to a field when you create a table, such as the defaults sysdate
In this way, each time you insert and modify the field, you do not have to use the program to get the action.

You can add constraints to a field when you create a table
For example, duplicate unique is not allowed, keyword PRIMARY key

2.ALTER (change tables, indexes, views, etc.)

Change the name of a table
ALTER table name 1 to table name 2;

Add a field to the back of the table
ALTER table name ADD field name description;

Modify the definition description of a table field
ALTER Table name modify field name description;

Add constraints to the fields in the table
ALTER table name ADD CONSTRAINT constraint name PRIMARY KEY (field name);
ALTER table name ADD CONSTRAINT constraint name UNIQUE (field name);

Place the table in or out of the memory area of the database
ALTER table name CACHE;
ALTER table name NOCACHE;

3.DROP (delete tables, indexes, views, synonyms, procedures, functions, database links, etc.)

Delete the table and all its constraints
DROP table name CASCADE CONSTRAINTS;

4.TRUNCATE (clears all records in the table, preserves the structure of the tables)

TRUNCATE table name;

Three. Query statement (SELECT) section

Select Field Name 1, field Name 2, ... From table name 1, [table Name 2, ...] WHERE condition;

Field names can be brought into the function
For example: COUNT (*), MIN (field name), MAX (field name), AVG (field name), DISTINCT (field name),
To_char (date field name, ' Yyyy-mm-dd HH24:MI:SS ')

NVL (EXPR1, EXPR2) function
Explain:
IF Expr1=null
RETURN EXPR2
ELSE
RETURN EXPR1

DECODE (Aa,v1,r1,v2,r2 ...) Function
Explain:
IF Aa=v1 then RETURN R1
IF Aa=v2 then RETURN R2
.. ...
ELSE
RETURN NULL

Lpad (CHAR1,N,CHAR2) function
Explain:
The character char1 is displayed by the number of digits n, and the insufficient digits replace the left empty with the CHAR2 string

Arithmetic operations can be performed between field names
For example: (Field name 1* field name 1)/3

Query statements can be nested
For example: SELECT ... From
(SELECT ... From table name 1, [table Name 2, ...] Where condition) where Condition 2;

The result of two query statements can be used as a collection operation
For example: Union (remove duplicate records), and set union ALL (do not remove duplicate records), difference set minus, intersection intersect

Group queries
Select Field Name 1, field Name 2, ... From table name 1, [table Name 2, ...] GROUP by field name 1
[Having conditions];

Connection query between more than two tables

Select Field Name 1, field Name 2, ... From table name 1, [table Name 2, ...] WHERE
Table name 1. field name = Table Name 2. field name [and ...];

Select Field Name 1, field Name 2, ... From table name 1, [table Name 2, ...] WHERE
Table name 1. field name = Table Name 2. Field name (+) [and ...];

field position with (+) number automatically fill empty value

The sort operation of the query result set, the default sort is ascending ASC, descending is desc

Select Field Name 1, field Name 2, ... From table name 1, [table Name 2, ...]
ORDER by Field name 1, field name 2 DESC;

Method of String Fuzzy comparison

INSTR (field name, ' string ') >0
Field name like ' String% ' ['% string% ']

Each table has an implied field rowid, which marks the uniqueness of the record.

Four. Data objects commonly used in Oracle (SCHEMA)

1. Indexing (Index)

CREATE index name on table name (field 1, [Field 2, ...]);
ALTER index index name REBUILD;

The index of a table should not be more than three (except for special large tables), preferably with a single field index, combined with the analysis of SQL statement execution,
You can also create multi-field combined indexes and function-based indexes

ORACLE8.1.7 string can be indexed up to a maximum length of 1578 single bytes
ORACLE8.0.6 string can be indexed up to a maximum length of 758 single bytes

2. Views (view)

CREATE view name as SELECT .... From ...;
ALTER View name COMPILE;

A view is just a SQL query statement that can make complex relationships between tables simple.

3. Synonyms (synonmy)
CREATE synonym synonym name for table name;
CREATE synonym synonym name for table name @ database link name;

4. Database link
CREATE database link name connect to user name identified by password USING ' database connection string ';

The database connection string can be defined using NET8 Easy config or directly modifying the Tnsnames.ora.

Database parameter global_name=true requires the database link name to be the same as the remote database name

The database global name can be isolated with the following command
SELECT * from Global_name;

Querying tables in the remote database
SELECT ... From table name @ database link name;

Five. Rights Management (DCL) statements

1.GRANT Assign Permissions
The common set of system permissions has the following three:
Connect (basic connection), RESOURCE (program development), DBA (database management)
The common data object permissions are the following five:
All on data object name, SELECT on data object name, UPDATE on data object name,
DELETE on data object name, INSERT on data object name, ALTER on data object name

GRANT CONNECT, RESOURCE to user name;
GRANT SELECT on table name to user name;
GRANT SELECT, INSERT, DELETE on table name to user name 1, user name 2;

2.REVOKE Reclaim Permissions

REVOKE CONNECT, RESOURCE from user name;
REVOKE SELECT on table name from user name;
REVOKE SELECT, INSERT, DELETE on table name from user name 1, user name 2;

Querying the database for number 63rd errors:
Select Orgaddr,destaddr from sm_histable0116 where error_code= ' 63 ';

Query database for the maximum number of users to submit and maximum issued: select Msisdn,tcos,ocos from Ms_usertable;

Query the sum of the various error codes in the database:
Select Error_code,count (*) from sm_histable0513 GROUP by Error_code Order
by Error_code;

Query the report database for the type of word statistics.
Select SUM (successcount) from tbl_middlemt0411 where servicetype2=111
Select SUM (successcount), servicetype from tbl_middlemt0411 Group by servicetype

Oracle Common Command Daquan