oracle简单入门搭配plsql

/*创建学生表*/
CREATE TABLE student(
stu_id NUMBER NOT NULL COMMENT '学生ID',
stu_name VARCHAR2(10) NOT NULL COMMENT '学生姓名',
gender VARCHAR2(10) NOT NULL COMMENT '性别' ,
age NUMBER(2) NOT NULL COMMENT '年龄' ,
join_date DATE NULL COMMENT '入学时间',
class_id NUMBER NOT NULL COMMENT '班级ID',
address VARCHAR2(50) NULL COMMENT '家庭住址'
);
/*创建学生表*/
CREATE TABLE student(
  stu_id NUMBER NOT NULL ,
  stu_name VARCHAR2(10) NOT NULL ,
  gender VARCHAR2(10) NOT NULL ,
  age NUMBER(2) NOT NULL ,
  join_date DATE NULL ,
  class_id NUMBER NOT NULL ,
  address VARCHAR2(50) NULL 
);

/*给学生表和表字段添加注释*/
/*给表添加注释*/
COMMENT ON TABLE student IS '学生信息' ;

/*给字段添加注释*/
COMMENT ON COLUMN student.stu_id  IS '学生ID' ;
COMMENT ON COLUMN student.stu_name  IS '学生姓名' ;
COMMENT ON COLUMN student.gender  IS '性别' ;
COMMENT ON COLUMN student.age  IS '年龄' ;
COMMENT ON COLUMN student.join_date  IS '入学时间' ;
COMMENT ON COLUMN student.class_id  IS '班级ID' ;
COMMENT ON COLUMN student.address  IS '家庭住址' ;

/*查询表结构:需要命令窗口desc命令查看表结构*/
 Name      Type         Nullable Default Comments 
--------- ------------ -------- ------- -------- 
STU_ID    NUMBER                        学生ID   
STU_NAME  VARCHAR2(10)                  学生姓名 
GENDER    VARCHAR2(10)                  性别     
AGE       NUMBER(2)                     年龄     
JOIN_DATE DATE         Y                入学时间 
CLASS_ID  NUMBER                        班级ID   
ADDRESS   VARCHAR2(50) Y                家庭住址 

/*创建班级表*/
CREATE TABLE stu_class(
class_id NUMBER NOT NULL ,
class_name VARCHAR2(20) NOT NULL ,
notes VARCHAR2(50) NULL 
);

/*给表添加注释和默认值*/
COMMENT ON COLUMN stu_class.class_id IS '班级ID' ;
COMMENT ON COLUMN stu_class.class_name IS '班级名称' ;
COMMENT ON COLUMN stu_class.notes IS '班级信息' ;
/*字段添加默认值*/
ALTER TABLE stu_class MODIFY notes DEFAULT '班级信息';

/*查看表结构*/
Name       Type         Nullable Default Comments 
---------- ------------ -------- ------- -------- 
CLASS_ID   NUMBER                        班级ID   
CLASS_NAME VARCHAR2(20)                  班级名称 
NOTES      VARCHAR2(50) Y        '班级信息' 班级信息 

/*添加数据库表约束*/
ALTER TABLE student ADD CONSTRAINT pk_student_stu_id PRIMARY KEY(stu_id);
ALTER TABLE stu_class ADD CONSTRAINT pk_stu_class_stu_id PRIMARY KEY(class_id);

/*添加检查约束*/
ALTER TABLE student ADD CONSTRAINT ck_student_gender CHECK(gender='男' OR gender = '女') ;
ALTER TABLE student ADD CONSTRAINT ck_student_age CHECK(age >= 0 AND age <=100) ;

/*添加唯一约束*/
ALTER TABLE student ADD CONSTRAINT uq_student_stu_name UNIQUE(stu_name) ;

/*添加默认约束*/
ALTER TABLE student MODIFY address VARCHAR(50) DEFAULT '地址不想' ;
ALTER TABLE student MODIFY join_date DATE DEFAULT SYSDATE ;

/*添加外键约束*/
ALTER TABLE student ADD CONSTRAINT fk_student_stu_class_class_id FOREIGN KEY(class_id) REFERENCES
stu_class(class_id) ;

SELECT * FROM stu_class;
/*添加数据*/
/*给学生表添加数据*/
/*删除表中所有数据*/
TRUNCATE TABLE   student;
INSERT INTO student
  (stu_id, stu_name, gender, age, join_date, class_id, address)
  SELECT 1, '贾探春', '女', 18, SYSDATE, '001', '贾府' FROM dual
  UNION ALL
  SELECT 2, ' 史太君 ', '女', 67, SYSDATE, ' 001 ', ' 贾府 '
    FROM dual
  UNION ALL
  SELECT 3, ' 薛宝钗 ', '女', 88, SYSDATE, ' 002 ', ' 薛府 '
    FROM dual;
/*查询学生表数据*/
SELECT * FROM student ;
/*删除学号为1学生信息*/
DELETE FROM student WHERE stu_id = 1 ;

/*设置所有的address为 王府*/
UPDATE student SET address = '王府'; 
/*设置班级是1的学生性别变为男性*/
UPDATE student SET gender = '男' WHERE class_id = 1 ;

/*两种方案添加数据*/
/*第一种通过 UNION ALL 拼接查询*/
INSERT INTO stu_class
  (class_id, class_name, notes)
  SELECT 3, 'dsf', 'ljldsfj'
    FROM dual
  UNION ALL
  SELECT 4, '4dsfs', '4dsljf'
    FROM dual;    

/*查询验证数据*/
SELECT * FROM stu_class;

/*第二种是 INSERT ALL 方式*/
INSERT ALL INTO stu_class
  (class_id, class_name, notes)
VALUES
  (5, '5klsd', '5lsdjf') INTO stu_class
  (class_id, class_name, notes)
VALUES
  (6, '5klsd', '5lsdjf')
  SELECT * FROM dual;

/*查询验证数据*/
SELECT * FROM stu_class;

/*验证数据表约束*/
/*验证student表stu_name是否唯一*/
INSERT INTO student
  (stu_id, stu_name, gender, age, join_date, class_id, address)
  SELECT 8, ' 史太君 ', '女', 67, SYSDATE, ' 001 ', ' 贾府 ' FROM dual ;
/*警告提示：ORA-00001:违反唯一约束条件(SCOTT.UQ_student_stu_name);*/