Common Operations for creating and managing tables in oracle and creating a table in oracle

Common Operations for creating and managing tables in oracle and creating a table in oracle

In oracle, tables are divided into user-defined tables and data dictionaries (a group of tables automatically created by Oracle Server, including database information ).

1. view the table created by the user

SELECT * FROM user_tables;

Select table_name from user_tables;

2. view User-Defined database objects

Select distinct object_type FROM user_objects;

3. view User-defined tables, views, synonyms, and Sequences

SELECT * FROM user_catalog

4. oracle creates a table, which is basically the same as mysql but does not like to create a table.

1. custom TABLE creation, specifying the column name, data type, length: create table dept (deptno NUMBER (2), dname VARCHAR2 (14), loc VARCHAR2 (13); 2. create a table using a subquery, but the index will be lost. Create table emp1 as select * from employees; (including employees data) create table emp2 as select * from employees where 1 = 2; -- the created emp2 table is empty. 3. create table emp1 like employees. This method is not available in oracle. oracle does not support the like clause. Mysql and hive.

5. alter table modify the table structure and modify table records without updating.

1. add a column to the new TABLE, addALTER TABLE dept80ADD (job_id VARCHAR2 (9); 2. modify the data type, size, and default value of a column, modifyALTER TABLEdept80MODIFY (last_name VARCHAR2 (30); 3. delete a COLUMN, dropALTER TABLE dept80DROP COLUMN job_id; 4. recommand COLUMN renameALTER TABLE dept80RENAME COLUMN job_id TO id;

6. Delete the table. The drop. Data and structure are all deleted, the transaction is committed, the index is deleted, and cannot be rolled back.

Drop table dept80;

7. Clear the table. The truncate and delete statements DELETE all data in the table and retain the table structure. The difference is that the TRUNCATE statement cannot be rolled back. However, the delete statement can DELETE data and roll back.

Delete from employees; select * from employees; rollback; select * from employees; -- the data is still ------------------ truncate table employees; select * from employees;

8. The RENAME statement changes the name of a table, view, sequence, or synonym.

RENAME dept TO detail_dept;

9. CRUD of data in the table, that is, adding, deleting, modifying, and querying data, is basically the same as mysql.

1. insert data into the table (the inserted data can be sysdate) I. insert into employees (employee_id, email, phone_number, hire_date) VALUES (113, 'lpopp @ qq.com ', '192. 124.4567 ', SYSDATE); ii. insert INSERTINTO emp2 SELECT * FROM employees WHEREdepartment_id = 90; 2. update several records in the update table: update employees set dempartment_id = 70 where employee_id = 140; ------------------------------------------- update records based on subqueries: Get job_id of employee 114, salary and j of employee 205 Ob_id, UPDATE employees SET job_id = (SELECT job_id FROM employees WHERE employee_id = 205), salary = (SELECT salary FROM employees WHERE employee_id = 205) WHERE employee_id = 114; 3. delete data from the table. delete from orders ments WHERE department_name = 'finance '; -- if the where clause is deleted, data in the entire table will be deleted, but rollback is supported (difference between the preceding clearing expression and truncate) -- Similarly, delete supports deletion of basic subquery conditions: delete from emp1 WHERE department_id = (SELECT department_id FROM dept1 WHERE department_name LIKE '% Public %'); 4. Same select query, support for each seed query. The focus is basically the same as that of mysql.