Oracle learning notes series ------ basic oracle operations and basic oracle operation statements

Oracle learning notes series ------ basic oracle operations and basic oracle operation statements

 

-- CREATE a table create table employee_souvc (id NUMBER (4), name VARCHAR2 (20), gender CHAR (1), birth DATE, salary NUMBER (6, 2 ), job VARCHAR2 (30), deptno NUMBER (2 ));

 

 

-- DESC table_name: view the table structure and view the name of the table's column, as well as the corresponding type and length. DESC employee_souvc;

 

 

-- Drop table table_name Delete the syntax of a table drop table employee_souvc;

 

 

The -- DEFAULT keyword is used to set the DEFAULT value for a given column (field). The DEFAULT value is NULL for all fields in the database. If the DEFAULT value is specified by DEFAULT, the specified value is used. -- Not null constraints: when creating a table, you can add non-NULL constraints to the column. The constrained column must be given a value when inserting data. This column cannot be blank. Create table employee_souvc (id NUMBER (4), name VARCHAR2 (20) not null, gender CHAR (1) DEFAULT 'M', birth DATE, salary NUMBER (6, 2 ), job VARCHAR2 (30), deptno NUMBER (2 ));

 

 

-- RENAME old_name TO new_name modify the table name -- Note: the new table name cannot be the current table rename employee_souvc TO emp_souvc in the database;

 

 

-- Modify Table: Add a new field (column) to the table, always append it to the last column of the table -- sysdate is a date value, indicating the current system time. Alter table emp_souvc ADD (hiredate date default sysdate );

 

 

-- Delete an alter table emp_souvc DROP (hiredate) column from the TABLE );

 

 

-- MODIFY the current alter table emp_souvc MODIFY (job VARCHAR2 (40) DEFAULT 'cler ');

 

Notes for modifying table fields:

1: do not modify the field type whenever possible.
2: do not reduce the field length.
3: The modified field only affects the newly inserted data. All data before the modified field is not affected.

 

-- DML operation: insert into emp_souvc (id, name, salary) VALUES (1, 'bos', 1500) INTO the table );

 

The INSERT statement inserts data into a table.
The values of the columns specified by the INSERT statement will be inserted into columns not listed in the table, and NULL will be inserted. However, if this column has a DEFAULT value (set by the DEFAULT keyword ), insert the default value.
If a column is not null and this column is NOT specified when an INSERT statement is executed, the INSERT operation throws an exception that violates the NULL constraint.

When executing the INSERT statement, if you do not specify to INSERT any columns, all columns are inserted. Note that the values must be in the same order as the columns in the table, and the values of any column cannot be ignored.

 

 

-- Query table data SELECT * FROM emp_souvc;

 

Insert into emp_souvc (id, name, salary) VALUES (2, 'Tom ', 2500); insert into emp_souvc (id, name, salary) VALUES (3, 'Jerry ', 3500); -- transaction control: -- used to commit transactions. COMMIT; -- used to roll back transactions. All add, delete, modify, and delete operations in this transaction are invalid. ROLLBACK;

 

 

-- TO_DATE () function -- TO_DATE ('1970-09-01 ', 'yyyy-MM-DD'); insert into emp_souvc (id, name, birth) VALUES (1, 'jack ', TO_DATE ('2014-09-01 ', 'yyyy-MM-DD'); SELECT * FROM emp_souvc;

 

 

-- Modify the data in the table UPDATE emp_souvcSET job = 'manager' WHERE salary = 3500;

 

Note: Generally, when you change a table, you must add a WHERE clause to specify the filter condition. If you do not specify a WHERE clause, the change is generally not done.

 

-- DELETE data FROM the table: delete from emp_souvcWHERE name = 'Tom '; SELECT * FROM emp_souvc;

When deleting data, you must note that adding WHERE. Otherwise, the whole table is deleted.

 

TRUNCATE  TABLE emp_souvc;SELECT   *   FROM   emp_souvc;