Oracle table management-crud introduction, oracle table crud

Oracle table management-crud introduction, oracle table crud

Insert Statement (add data)

Update Statement (Update data)

Delete Statement (Delete data)

Select Statement (query data)

1. Manage oracle tables-add data

Use the INSERT statement to INSERT data to the table.

Insert into table [(column [, column...])]

VALUES (value [, value...]);

The inserted data should be of the same type as the field data.

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

The data positions listed in values must correspond to the arranged positions of the columns to be added.

Character and date data should be included in single quotes.

Insert null value, not specified or insert into table value (null)

2. Manage oracle tables-modify data

Use the update statement to modify table data.

UPDATE tbl_name

SET col_name1 = expr1 [, col_name2 = expr2...]

[WHERE where_definition]

The UPDATE syntax uses the new value to UPDATE columns in the original table rows.

The SET clause indicates the columns to be modified and the values to be given.

The WHERE clause specifies the rows to be updated. If no WHERE clause exists, all rows are updated.

3. Manage oracle tables-delete data

Delete data

Delete from table name;

Delete all records, the table structure is still in progress, and logs are written, which can be recovered and the speed is slow.

Drop table name; Delete table structure and data

Delete from student where xh = 'a001'; delete a record

Truncate table name;

Delete all records in the table. The table structure is still in progress. If you do not write logs, you cannot retrieve deleted records, which is fast.

4. Basic oracle Table query-Introduction

Basic select statement

SELECT [DISTINCT] * | {column1, column2. column3 ..}

FROM table;

Select specifies the columns to query.

Column specifies the column name.

* Indicates to query all columns.

From specifies the table to be queried.

DISTINCT (optional) indicates whether to remove duplicate data when the result is displayed.

View table structure

SQL> desc table name;

■ Query all columns

Select * from table name;

■ Query a specified Column

Select column 1, column 2... From table name;

■ How to cancel duplicate rows

Select distinct deptno, job from emp;

Query SMITH's salary, job, and department