Oracle's basic operations on data tables

The principle of selecting a primary key:

• Minimum sex
• Choose to use a single key as the primary key
• Stability
• Try selecting a column with fewer values to update as the primary key

1. Creating a data table (CREATE TABLE)

--Creating a data table studentCreate TableStudent (SID Number(2)constraintPk_sidPrimary Key,--Specify the column as the primary key and specify the primary key named Pk_sidSNamevarchar2( -) not NULL)--Create a data table classCreate TableClass (CID Number(2)constraintPk_cidPrimary Key,--Specify the column as the primary key and specify the primary key named Pk_cidCNamevarchar2( -) not NULL)

2. Renaming and deleting data tables

-- Rename data table student to Stu Alter Table  to Stu;
--Delete Data sheet student
drop table Student;

3. Add, rename, delete fields, modify field data types

--For data sheet studentadd fields Sgender and SCIDAlter TableStudentAdd(SgenderChar(2));Alter TableStudentAdd(SCID Number(2));--Delete a field from the datasheet student SgenderAlter TableStudentDrop columnSgender;--Rename the field SID in data table student to StuidAlter TableStudent RenamecolumnSid toStuid;--Modifying the data type of a field SID in a data table studentAlter TableStudent Modify SID Number(2);

4. Add and remove field constraints

--Add a constraint to the field Sgender in datasheet student and specify that the constraint name is Ch_gender, specifying that the column's value can only be ' male ' or ' female 'Alter TableStudentAdd constraintCh_genderCheck(Sgender='male' orSgender='female');--to delete a constraint named Ch_gender in a data table studentAlter TableStudentDrop constraintCh_gender;

5. View, add, rename, delete, disable, enable primary key

--to view a defined primary key in a data table studentSelect *  fromUser_cons_columnswheretable_name='STUDENT';--set the field sname in the data table student as the primary key column, and specify that the name of the primary key is Pk_nameAlter TableStudentAdd constraintPk_namePrimary Key(SName);--Remove primary key with primary key named Pk_nameAlter TableStudentDrop constraintPk_name;--Pk_stuid The primary key nameRename to Pk_sidAlter TableStudent RenameconstraintPk_stuid toPk_sid;--Disable primary keyAlter TableStudent DisablePrimary key;--Enable primary keyAlter TableStudent EnablePrimary key;

6. View, add, rename, delete, disable, enable foreign keys

--to view a foreign key that already exists in the datasheetSelectOwner,constraint_name fromUser_constraintswhereConstraint_type='R'--P PRIMARY key R foreign key--Add foreign KeyAlter TableStudentAdd constraintFk_scidForeign Key(SCID)ReferencesClass (CID)--Delete foreign keyAlter TableStudentDrop constraintFk_scid--Renaming foreign keysAlter TableStudent RenameconstraintFk_sclassid toFk_scid--Disable foreign keysAlter TableStudent DisableconstraintFk_scid--enable foreign keysAlter TableStudent EnableconstraintFk_scid

Oracle's basic operations on data tables