Orace additions and deletions to search

Database additions and deletions to check the database table four kinds of operations:

• Add: Insert to increase data
• Deleting: Delete, deleting data
• Change: update, updating data
• Check: SELECT, find Data

First, insert

Basic syntax: INSERT into table name [(Column[,column]) VALUES (Value[,value])

Description: 1. The inserted data should be the same as the field data type

2. The size of the data should be within the specified range. For example, you cannot add a character with a length of 80 to a column of length 40.

3. The column listed in value must be in the same position as the column being joined

4. Character and date types must be enclosed in single quotes

5. Insert a null value. Do not specify or INSERT into table value (NULL)

6. To add a value to each column of a table, you can do so without a column name. such as insert into table values (column value)

INSERT into Test2 value (' Xiaoqiang ', 36)

Second, Update table

Basic syntax: Update table name set column name = expression [, expression]where condition

Description: 1.update syntax You can update old values in existing tables with new values, and set words indicate which columns to modify and which values to give

2.update to modify column values

　　　

3. Increase the scholarship of each person by 50%

Update test set scolarship =scolarship*1.5;

　　　

4. Change the scholarship to 750 for those who do not have a scholarship, and note the judgment of the null field

Update test set scolarship=750 where name is null;

　　　

Third, delete

Basic syntax: Delete table name where condition

Description

1. If you do not use the WHERE clause, all data in the table will be deleted

2.delete statement cannot delete a column's value

3. Use Delete to delete only table records and not delete the table itself. If you want to delete a table, use the DROP TABLE statement

4. As with update and insert, deleting records from one table will cause referential integrity issues with other tables, and in the mind, you can never forget this potential problem when modifying database data

Several ways to delete a table are compared:

1.delete from table name;

Delete all records, table structure is still in, write log, can recover, delete slow.

　　

2.drop Table Name

Delete the structure and data of a table

3.truncate Table Name

Delete all the records in the table, the table structure is still in, do not write the log, can not retrieve the deleted table records. Fast speed.

Truncate cannot add a WHERE clause when compared to delete.

Iv. Select

Basic syntax: SELECT [Distinct]*{colum1,column2 ....} from table name [where {condition}];

Description

Select: Specify which columns of data to check

Column: Specify the name

*: Represents all Columns

From: Specify which table to query

Distinct optional, indicates whether duplicate data is deleted when the result is displayed

1. Querying all columns and specified columns

SELECT * FROM table name

Select Column Name 1, column name 2 from table name

Note: Return as few columns as possible

2 How to cancel duplicate rows

Select DISTINCT Deptno job from EMP;

The returned data is exactly the same as the repeating row

Select Sal,job from emp where name= ' Smith '; The contents of the quotation marks are case-sensitive, and the other parts of the SQL statement are case insensitive.

3. Using an arithmetic expression in an Oracle query

Show each employee's salary

Select ename,sal*13 from EMP;

Select ename,sal*13+comm*2 from EMP; If Comlumn is empty, then sal*13+comm*2 is empty, empty arithmetic operation is empty, how to solve this problem?

Oracle provides the relevant functions:

Select Ename, SAL*13+NVL (comm,0) * * from EMP;

NVL (comm,0): returns 0 if comm is empty, otherwise returns COMM. An issue that can be used to handle null.

Note: aliases can be used , andOracle uses either double quotation marks or double quotes without using aliases.

Select ENAME,SAL*13+NVL (comm,0) [as] "annual salary" from EMP;

　　String connection (can be used when reporting) | |

Select Ename | | ' Is ' | |    Job from EMP; If you want to stitch multiple columns together and return as a column at query time, you can use | |

　　

　　

　　　

　　

Orace additions and deletions to search