Ii) Data Definition Language 1. Summary The data definition function of SQL is defined for various data objects in the database three-level mode structure. Standard SQL mainly includes tables, views, and indexes, and triggers, cursors, processes, and packages in Oracle. Standard SQL does not support index-defined operations or view modification operations. You can only delete the created method. You can use alter view to modify a view in Orale. 2. statement description 1) create a table, function, and view create table <Table Name> (<column Name> <data type> [column-level integrity constraints] [, <column Name> <data type> [column-level integrity constraints]... [, <Table-level integrity constraints>]); Create Table ABC (A varchar2 <10>, B char <10> ); create view <view Name> [(<column Name> [, <column Name>]...)] as <subquery> [with check option] Create [unique] [cluster] index <index Name> On <Table Name> (<column Name> [<order>] [, <column Name> [<order>]...) [tablespcae tablespace_name]; 2) alter table and function alter table <Table Name> [add <new column Name> <data type> [Integrity Constraint] [drop <Integrity Constraint Name>] [modify <column Name> <data Type>] 3) drop delete D Drop view; drop index. 4) create a table named it_employees, it consists of eight attributes: employee ID, first_name, last_name, email, phone_number, department ID job_id, salary, and department manager ID manager_id. Create Table it_employees (employee_id number (6) Not null unique, first_name varchar2 (20), last_name varchar2 (25) not null, email varchar2 (25), phone_number varchar (20 ), job_id varchar2 (10), salary number (8, 2), manager_id number (6); [Example 2] Create a programmer's view prog_employees (job_id = 'it _ prog '), the column name of the view is hidden. create view prog_employeesasselect employee_id, first_name, last_name, email, phone_number, salary, manager_idfrom it_employeeswhere job_id = 'it _ prog'; [Example 3] run the following create index statement to create an index: create index it_lastname on it_employees (last_name );
[Example 4] Delete the previously created view prog_employees. Drop view prog_employees;
[Example 5] add the "Employee birthday" column to the it_employees table. The data type is "alter table it_employees add birthdate; [Example 6] Change the manager_id field of the it_employees table to eight bits: alter table it_employees modify manager_id number (8); [Example 7] Delete the unique constraint of the employee ID field of the it_employees table: alter table it_employees drop unique (employee_id );
