Management of Oracle Tables

I. Naming rules for table and column names
1), must start with a letter
2), length cannot exceed 30 characters
3), cannot use Oracle's reserved words
4), can only use the following characters a-z,a-z,0-9,$, #等

Ii. Types of data
1), character class
Char has a fixed length of up to 2000 characters.
Example: char (10) ' Xiao Han ' first four characters descriptors ' Xiao Han ', after adding 6 spaces complement, such as ' Xiao Han '
VARCHAR2 (20) is variable in length and accommodates up to 4,000 characters.
Example: VARCHAR2 (10) ' Korean ' Oracle assigns four characters. This will save space.
CLOB (character large object) character large objects, accommodating up to 4g
Char queries are extremely fast and waste space, and are suitable for querying more frequent data fields.
varchar saves space
2), Digital type
Number range-10 of the 38-square to 10 38-square, can represent integers, can also represent decimals
Number (5,2) indicates that a decimal has 5 valid digits and 2 decimal places; Range: 999.99 to 999.99
Number (5) represents a 5-bit integer; Range 99999 to-99999
3), Date type
Date contains date and time of year Oracle default format January-January-1999
Timestamp this is an extension of the oracle9i to the date data type. Can be accurate to milliseconds.
4), picture
Blob binary data, can be stored picture/sound 4g; Generally speaking, in the real project is not to put the picture and sound really into the database, generally store pictures, video path, if security needs relatively high, then put into the database.

Third, how to create a table

--Create a table
--Student Table
CREATE TABLE Student (
XH Number (4),--Study No.
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 Table
--Add a field
Sql>alter Table Student Add (ClassID number (2));
--Modify the length of a field
Sql>alter Table Student Modify (XM VARCHAR2 (30));
--Change the type of field or name (cannot have data) do not recommend
Sql>alter Table Student Modify (XM char (30));
--Deleting a field is not recommended (after deletion, the order is changed.) Plus it's fine, it should be in the back.
Sql>alter table student Drop column Sal;
--Changing the name of a table rarely has this requirement
Sql>rename student to Stu;

--Delete Table
Sql>drop table student;

--Add data
--all fields are inserted into the data
INSERT into student values (' a001 ', ' Zhang San ', ' Male ', ' January-May-05 ', 10);
--oracle the default date format ' Dd-mon-yy ' DD day Mon month yy 2-bit year ' September-June-99 ' June 9, 1999
--Modify the default format of the date (temporary modification, the database is still the default after the restart, if you want to modify the registry)
Alter session Set Nls_date_format = ' Yyyy-mm-dd ';
--After modification, you can add the date type in our familiar format:
INSERT into student values (' a002 ', ' Mike ', ' Male ', ' 1905-05-06 ', 10);
--Insert Some fields
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);
--the question is, if you want to query the student table birthday null records, how to write SQL?
--Error notation: SELECT * FROM student where birthday = null;
--Correct notation: SELECT * from student where birthday is null;
--If you want to query birthday NOT NULL, you should write this:
SELECT * FROM student where birthday are 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 that contains null values
Do not use = null but use is null;
SELECT * FROM student where birthday is null;

--Delete data
Delete from student; --Delete all records, table structure is still in, write log, can recover, slow.
The--delete data can be recovered.
SavePoint A; --Create a save point
Delete from student;
Rollback to A; --Revert to the Save point
An experienced DBA that creates a restore point periodically when it is ensured that it is complete without error.

drop table student; --delete the structure and data of the table;
Delete from student where xh = ' a001 '; --delete a record;
TRUNCATE TABLE student; --Delete all the records in the table, the table structure is still in, do not write the log, can not retrieve deleted records, fast.

See also: http://www.cnblogs.com/linjiqin/archive/2012/02/01/2335035.html

Management of Oracle Tables