-Create a student table, set the primary key for the table to be the number
CREATE TABLE Student (
Sno VARCHAR2 (Ten) PRIMARY KEY,--column-level constraint
Sno VARCHAR2 () CONSTRAINT sno_pk PRIMARY KEY,--column-level constraint with constraint name
Sname VARCHAR2 (Ten) DEFAULT ' Zhang San ',
Ssex VARCHAR2 (1),
Sbirthday DATE,
Saddress VARCHAR2 (50),
--CREATE TABLE-level constraints
CONSTRAINT sno_pk PRIMARY KEY (SNO)
)
--Inquiry Form Student
SELECT * FROM Student
--Modify the length of the Sno
ALTER TABLE student MODIFY Sno VARCHAR2 (20)
--Modify the length of the Ssex
ALTER TABLE student MODIFY ssex VARCHAR2 (2)
--What is the default value, and if no value is assigned when inserting the data, then the default value is taken.
INSERT into student (sno,ssex) VALUES (' 09 ', ' Male ')
INSERT into student (sno,ssex) VALUES (' 57507109 ', ' Male ')
--Modify the default value of Sname to John Doe
ALTER TABLE student MODIFY sname DEFAULT ' John Doe '
INSERT into student (sno,ssex) VALUES (' 57507112 ', ' female ')
--Table renaming
RENAME Student to Stu
--Delete Columns
ALTER TABLE Stu DROP COLUMN saddress;
--delete sno= ' 57507111 ' information
DELETE from Stu WHERE sno= ' 57507111 ';
--Create a constraint by modifying the table structure
ALTER TABLE Stu ADD CONSTRAINT sno_pk PRIMARY KEY (SNO);
--Delete Constraint
ALTER TABLE Stu DROP CONSTRAINT sno_pk;
--Insert Information
INSERT into Stu (sno,sbirthday) VALUES (' 57507105 ', to_date (' 2017-04-02 ', ' yyyy-mm-dd '))
--Modify the date default value of Stu
ALTER TABLE Stu MODIFY sbirthday DEFAULT sysdate
--Delete Column Sno
ALTER TABLE Stu DROP COLUMN Sno
--Add columns
ALTER TABLE Stu ADD Sno VARCHAR2 (20)
Oracle: Create, ALTER, INSERT, RENAME, delete operation exercises and primary foreign key constraints on tables