V. Management of Oracle Tables

I. Naming rules for table and column names1), must start with a letter2), length cannot exceed 30 characters3), cannot use Oracle's reserved words4), use only the following characters a-Z,a-Z0-9, $, #等 ii. data type1), character classesCharfixed length with a maximum of 2000 characters. Example:Char(Ten"Xiao Han" the first four words descriptors ' Xiao Han ', after the addition of 6 space complement, such as ' Xiao Han 'varchar2( -) is variable in length and accommodates a maximum of 4,000 characters. Example:varchar2（Ten) ' Korean ' Oracle assigns four characters. This will save space. CLOB (characterlarge Object) character-type large objects, accommodating up to 4gCharqueries are extremely fast and waste space, and are suitable for querying more frequent data fields. varcharSpace Saving2), digital 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 number has 5 valid digits, 2 decimal places, or a range:-999.99To 999. About Number(5) represents a 5-bit integer; Range 99999 to-999993), date type dates include day of month and time of year Oracle default format 1-January-1999timestampThis is a oracle9i extension to the date data type. Can be accurate to milliseconds. 4), picture blob binary data, can be stored in pictures/sound 4g; Generally speaking, in the real project is not to put pictures 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 TableCreate TableStudent (XH Number(4),--School NumberXmvarchar2( -),--nameSexChar(2),--SexBirthday date,--Date of birthSal Number(7,2)--Scholarships);--Class TableCreate TableClass (ClassID Number(2), CNAMEvarchar2( +)); --Modify Table--Add a fieldSql>Alter TableStudentAdd(ClassID Number(2));--Modify the length of a fieldSql>Alter TableStudent Modify (XMvarchar2( -));--Modify the type of field or name (cannot have data) is not recommendedSql>Alter TableStudent Modify (XMChar( -));--Deleting a field is not recommended (after deletion, the order is changed.) Plus it's fine, it should be in the back.Sql>Alter TableStudentDrop columnSal;--changing the name of a table rarely has this requirementSql>Rename Student toStu; --Delete a tableSql>Drop Tablestudent; --Add Data--all fields are inserted into the dataInsert  intoStudentValues('a001','Zhang San','male','January-May on -05',Ten);--default date format in Oracle ' DD-MON-YY ' dd 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)AlterSessionSetNls_date_format='YYYY-MM-DD';--once modified, you can add the date type in our familiar format:Insert  intoStudentValues('a002','Mike','male','1905-05-06',Ten);--Insert a partial fieldInsert  intoStudent (XH, XM, Sex)Values('a003','John','female');--Insert Null valueInsert  intoStudent (XH, XM, sex, birthday)Values('a004','Martin','male',NULL);--The question comes, if you want to query the student table birthday is a null record, how to write SQL? --Error notation: SELECT * FROM student where birthday = null;--correct wording: SELECT * from student where birthday is null;--if you want to query birthday is not NULL, you should write this:Select *  fromStudentwhereBirthday is  not NULL; --Modifying Data--Modify a fieldUpdateStudentSetSex= 'female' whereXh= 'a001';--Modify multiple fieldsUpdateStudentSetSex= 'male', birthday= '1984-04-01' whereXh= 'a001';--modifying data that contains null valuesDon't use= NULLBut with is NULL;Select *  fromStudentwhereBirthday is NULL; --Delete DataDelete  fromStudent--Delete all records, the table structure is still in, write log, can be restored, the speed is slow. --the data for the delete can be restored. SavePoint A;--Create a save pointDelete  fromstudent;rollback  toA--revert to save pointan experienced DBA that creates a restore point periodically when it is ensured that it is complete without error. Drop TableStudent--Delete the structure and data of the table;Delete  fromStudentwhereXh= 'a001';--delete a record;truncate TableStudent--Delete all the records in the table, the table structure is still in, do not write logs, can not retrieve deleted records, fast.