Encyclopedia of SQL statement syntax

Some of the most commonly used SQL statement syntax for the triple compilation. Need a friend to come over to refer to the next

I. Data Control statement (DML) section

1.INSERT (Insert the statement into the data sheet)

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 quotes, such as: ' Good day '

If the field value contains a single quote ' that requires a string conversion, we replace it with two single quotes '.

The value of a field with a string type exceeds the length of the definition, and it is best to check the length before inserting it.

The field value of the Date field can be sysdate to the second with the current database's system time

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 you can refer to Oracle DOC.

Year-month-day hours: minutes: sec format Yyyy-mm-dd HH24:MI:SS

The maximum operable string length at insert is less than or equal to 4,000 single-byte, and if you want to insert a longer string, consider the field with the Clob type.

method to borrow the Dbms_lob package from Oracle.

Insert when you want to use the automatic growth of the serial number starting from 1, you should first create a serial number

The name of the CREATE SEQUENCE serial number (preferably the table name + serial number tag) INCREMENT by 1 START with 1

MAXVALUE 99999 CYCLE NoCache;

The maximum value is determined by the length of the field, and if the defined automatically-growing serial number (6), the maximum is 999999

Insert statement inserts this field value as: The name of the serial number. Nextval

2.DELETE (delete the statement recorded in the datasheet)

DELETE from table name WHERE condition;

Note: Deleting records does not release the data block tablespace that is occupied in Oracle. It only marks those deleted chunks of data as unused.

If you are sure you want to delete all records in a large table, you can use the TRUNCATE command, which frees up the data block tablespaces that occupy

TRUNCATE table name;

This operation cannot be rolled back.

3.UPDATE (Modify the statements 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, the original record content will be cleared to null, preferably a non-null checksum before modification;

The value n exceeds the length of the definition, and it is best to check the length before inserting.

Precautions:

A. The above SQL statement adds row-level locks to the table.

After the confirmation is complete, must add the matter processing end the order COMMIT to take effect formally,

Otherwise, the change is not necessarily written into the database.

If you want to recall these operations, you can use the command ROLLBACK to recover.

B. It is best to estimate the range of records that might be in operation before running the insert, DELETE, and UPDATE statements.

It should be limited to a smaller (10,000 records) range. Otherwise, Oracle handles this thing with a very 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 the SQL statements into sections.

With a commit to confirm things.

Two. Data definition (DDL) section

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

The types of fields commonly used by Oracle are

CHAR fixed-length string

VARCHAR2 variable-length strings

Number (M,n) M is the total number of digits, N is the length of the decimal

Date Date Type

To create a table, place a smaller, not-empty field in front of it, possibly with an empty field behind it

You can create a table with a field name in Chinese, but preferably a field name in English

You can add a default value to a field when you create a table, such as Sysdate

So every time you insert and modify this field, you can get the action time without using the program.

You can add constraints to a field when you create a table

For example, do not allow duplicate unique, keyword PRIMARY key

2.ALTER (change table, index, view, 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 First Name section name description;

Modify the definition description of a table or table field

ALTER Table name Modify field name paragraph 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 a table and all of its constraints

DROP Table table name CASCADE CONSTRAINTS;

4.TRUNCATE (Clear all the records in the table, keep 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 functions

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 in the set, and the number of digits replaces the left vacancy with the CHAR2 string.

Arithmetic operations can be performed between field names

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 results of two query statements can be set operations

For example: Union (remove duplicate records) and set union ALL (do not remove duplicate records), set minus, intersection intersect

Grouped queries

Select Field Name 1, field Name 2, ... From table name 1, [table Name 2, ...] GROUP by field name 1

[Having conditions];

Connection queries 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 Auto fill with (+) number

The sorting 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;

A method of fuzzy comparison of strings

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. Oracle Common Data Objects (SCHEMA)

1. indexes (Index)

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

ALTER index index name REBUILD;

A table should not have more than three indexes (except for special large tables), preferably with a single field index, combined with an analysis of SQL statements,

You can also set up multiple-field combined indexes and a function-based index

ORACLE8.1.7 string can be indexed with a maximum length of 1578 Single-byte

ORACLE8.0.6 string can be indexed with a maximum length of 758 single-byte

ORACLE Doc says the maximum index length of a string is approximately: the size of the data block (db_block_size) *40%

2. Views (view)

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

ALTER view 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. Databases link (database link)

CREATE Database link Database links name connect to username identified by password USING ' database connection string ';

Database connection strings can be defined in NET8 easy config or directly by modifying 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 identified 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) statement

1.GRANT Assignment to Permissions

The common set of system permissions has the following three:

Connect (basic connection), RESOURCE (program development), DBA (database management)

The most commonly used data object permissions have 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 username 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 username 1, user name 2;