Shulou(Shulou.com)05/31 Report--

This article mainly explains "oracle table management method", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let Xiaobian take you to learn "oracle table management method"!

Oracle supported data types

character class

Char has a fixed length of up to 2000 characters.

Oracle Notes

5

Example: char(10) 'Xiao Han' put 'Xiao Han' in the first four characters, add 6 spaces to complete such as 'Xiao Han'

varchar2(20) Variable length up to 4000 characters.

Example: varchar2 (10)'Xiao Han' oracle assigns four characters. This saves space.

clob(character large object) Character large object Maximum 4G

Char query speed is very fast waste of space, query more data with.

varchar saves space

digital type

number range-10 to the 38th power to 10 to the 38th power

It can represent integers or decimals.

number(5,2)

5 significant digits for 1 decimal place, 2 decimal places

Range: -999.99 to 999.99

number(5)

Represents a five-digit integer

Range 99999 to-99999

date type

date contains month, day, hour, minute, second oracle default format 1-January-1999

timestamp This is an extension of Oracle9i to the Date data type. accurate to milliseconds.

Pictures

Blob binary data can store pictures/sounds 4G Generally speaking, in real projects, pictures and sounds will not be stored in the database, generally stored

The path of pictures and videos, if the security needs are relatively high, will be put into the database.

How to create tables

table creation

--Student table

create table student ( ---table name

xh number(4), --student number

xm varchar2(20), --Name

sex char(2), --Gender

birthday date, --date of birth

sal number(7,2) --Scholarship

);

--Class table

CREATE TABLE class(

classId NUMBER(2),

cName VARCHAR2(40)

);

modify the table

Add a field

SQL>ALTER TABLE student add (classId NUMBER(2));

Change the length of a field

SQL>ALTER TABLE student MODIFY (xm VARCHAR2(30));

Modify field type/or name (no data) Not recommended

SQL>ALTER TABLE student modify (xm CHAR(30));

Delete a field is not recommended (after deleting, the order will change. There is no problem with adding, it should be added later)

SQL>ALTER TABLE student DROP COLUMN sal;

Oracle Notes

6

Changing the name of a table is rarely required

SQL>RENAME student TO stu;

Delete table

SQL>DROP TABLE student;

add data

All fields insert data

INSERT INTO student VALUES ('A001 ', ' Zhang San','male','01-May-05', 10);

Default date format in oracle 'dd-mon-yy' dd day (day) mon month yy 2 digit year '09-June-99' June 9, 1999

Default format of modification date (temporary modification, default after database restart; modification of registry is required)

ALTER SESSION SET NLS_DATE_FORMAT ='yyyy-mm-dd';

After modification, we can add date types in a familiar format:

INSERT INTO student VALUES ('A002', 'MIKE', 'male','1905-05-06', 10);

insert field

INSERT INTO student(xh, xm, sex) VALUES ('A003', 'JOHN', 'female');

insert null value

INSERT INTO student(xh, xm, sex, birthday) VALUES ('A004', 'MARTIN', 'male', null);

If you want to query the record with null birthday in student table, how to write sql?

Select * from student where birthday = null;

Select * from student where birthday is null;

If you want to query birthday is not null, you should write:

select * from student where birthday is not null;

modify data

Modify a field

UPDATE student SET sex = 'female' WHERE xh = 'A001';

Modify multiple fields

UPDATE student SET sex = 'male', birthday = '1984-04-01' WHERE xh = 'A001';

Modify data with null values

Do not use = null but is null;

SELECT * FROM student WHERE birthday IS null;

Delete data

DELETE FROM student;

Delete all records, table structure is still in, write logs, can be restored, slow speed.

Deleted data can be recovered.

savepoint a; --Create savepoint

DELETE FROM student;

rollback to a; --revert to save point

An experienced DBA will periodically create restore points while ensuring that they are done correctly.

DROP TABLE student; --Delete table structure and data;

delete from student WHERE xh = 'A001';--Delete a record;

truncate TABLE student; --Delete all records in the table, the table structure is still there, do not write logs, can not retrieve deleted records, fast.

At this point, I believe everyone has a deeper understanding of the "oracle table management method," may wish to actually operate it! Here is the website, more related content can enter the relevant channels for inquiry, pay attention to us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.