Oracle 10g SQL操作.zip
简单的介绍了一个常用的SQL 语句 3.2 例3.2_1 执行SQL语句。 SELECT sysdate FROM dual; 例3.2_2 执行PL/SQL程序。 SET SERVEROUTPUT ON BEGIN DBMS_OUTPUT.PUT_LINE('这是第一个PL/SQL程序'); END; / 3.3.2 定义表的结构 例3.3_1 students表结构 CREATE TABLE students ( student_id NUMBER(5) CONSTRAINT student_pk PRIMARY KEY, monitor_id NUMBER(5), name VARCHAR2(10) NOT NULL, sex VARCHAR2(6) CONSTRAINT sex_chk CHECK(sex IN ('男','女')), dob DATE, specialty VARCHAR2(64) ); 例3.3_2 departments表结构 CREATE TABLE departments( department_id NUMBER(3) CONSTRAINT department_pk PRIMARY KEY, department_name VARCHAR2(8) NOT NULL, address VARCHAR2(40) ); 例3.3_3 teachers表结构 CREATE TABLE teachers ( teacher_id NUMBER(5) CONSTRAINT teacher_pk PRIMARY KEY, name VARCHAR2(8) NOT NULL, title VARCHAR2(6), hire_date DATE DEFAULT SYSDATE, bonus NUMBER(7,2), wage NUMBER(7,2), department_id NUMBER(3) CONSTRAINT teachers_fk_departments REFERENCES departments(department_id ) ); 例3.3_4 courses表结构 CREATE TABLE courses( course_id NUMBER(5) CONSTRAINT course_pk PRIMARY KEY, course_name VARCHAR2(30) NOT NULL, credit_hour NUMBER(2) ); 例3.3_5 students_grade表结构 CREATE TABLE students_grade( student_id NUMBER(5) CONSTRAINT students_grade_fk_students REFERENCES students(student_id), course_id NUMBER(5) CONSTRAINT students_grade_fk_courses REFERENCES courses(course_id), score NUMBER(4,1) ); 例3.3_6 grades表结构 CREATE TABLE grades ( grade_id NUMBER(1) CONSTRAINT grade_pk PRIMARY KEY, low_score NUMBER(4, 1), high_score NUMBER(4, 1), grade VARCHAR2(6) ); 3.3.3 查看表结构 例3.3_7 查看students表结构 DESCRIBE students; 例3.3_8 查看teachers表结构 DESCRIBE teachers; 例3.3_9 查看departments表结构 DESCRIBE departments; 例3.3_10 查看courses表结构 DESCRIBE courses; 例3.3_11 查看students_grade表结构 DESCRIBE students_grade; 例3.3_12 查看Grades表结构 DESCRIBE grades; 3.3.4 删除表 例3.3_13 删除teachers表。 DROP TABLE teachers; 例3.3_14 删除departments表。 DROP TABLE departments; 例3.3_15 删除students_grade表。 DROP TABLE students_grade; 例3.3_16 删除students表。 DROP TABLE students; 例3.3_17 删除courses表。 DROP TABLE courses; 例3.3_18 删除grades表。 DROP TABLE grades; 3.3.5 添加数据 同名,同生日(参加工作时间),时间数据为NULL. 例3.3_19 添加students记录 学生记录： INSERT INTO students VALUES(10101,NULL,'王晓芳', '女', '07-5月-1988','计算机'); INSERT INTO students VALUES(10205,NULL,'李秋枫', '男', '25-11月-1990','自动化'); INSERT INTO students VALUES(10102,10101,'刘春苹', '女', '12-8月-1991','计算机'); INSERT INTO students VALUES(10301,NULL,'高山', '男', '08-10月-1990','机电工程'); INSERT INTO students VALUES(10207,10205,'王刚', '男', '03-4月-1987','自动化'); INSERT INTO students VALUES(10112,10101,'张纯玉', '男', '21-7月-1989','计算机'); INSERT INTO students VALUES(10318,10301,'张冬云', '女', '26-12月-1989','机电工程'); INSERT INTO students VALUES(10103,10101,'王天仪', '男', '26-12月-1989','计算机'); INSERT INTO students VALUES(10201,10205,'赵风雨', '男', '25-10月-1990','自动化'); INSERT INTO students VALUES(10105,10101,'韩刘', '男', '3-8月-1991','计算机'); INSERT INTO students VALUES(10311,10301,'张杨', '男', '08-5月-1990','机电工程'); INSERT INTO students VALUES(10213,10205,'高淼', '男', '11-3月-1987','自动化'); INSERT INTO students VALUES(10212,10205,'欧阳春岚', '女', '12-3月-1989','自动化'); INSERT INTO students VALUES(10314,10301,'赵迪帆', '男', '22-9月-1989','机电工程'); INSERT INTO students VALUES(10312,10301,'白菲菲', '女', '07-5月-1988','机电工程'); INSERT INTO students VALUES(10328,10301,'曾程程', '男', NULL,'机电工程'); INSERT INTO students VALUES(10128,10101,'白昕', '男', NULL, '计算机'); INSERT INTO students VALUES(10228,10205,'林紫寒', '女', NULL, '自动化'); 例3.3_20 添加departments记录 系部记录： INSERT INTO departments VALUES(101,'信息工程','1号教学楼'); INSERT INTO departments VALUES(102,'电气工程','2号教学楼'); INSERT INTO departments VALUES(103,'机电工程','3号教学楼'); 连接查询时使用： INSERT INTO departments VALUES(104,'工商管理','4号教学楼'); 例3.3_21 添加teachers记录 教师记录： INSERT INTO teachers VALUES(10101,'王彤', '教授', '01-9月-1990',1000,3000,101); INSERT INTO teachers VALUES(10104,'孔世杰', '副教授', '06-7月-1994',800,2700,101); INSERT INTO teachers VALUES(10103,'邹人文', '讲师', '21-1月-1996',600,2400,101); INSERT INTO teachers VALUES(10106,'韩冬梅', '助教', '01-8月-2002',500,1800,101); INSERT INTO teachers VALUES(10210,'杨文化', '教授', '03-10月-1989',1000,3100, 102); INSERT INTO teachers VALUES(10206,'崔天', '助教', '05-9月-2000',500,1900, 102); INSERT INTO teachers VALUES(10209,'孙晴碧','讲师', '11-5月-1998',600,2500, 102); INSERT INTO teachers VALUES(10207,'张珂', '讲师', '16-8月-1997',700,2700, 102); INSERT INTO teachers VALUES(10308,'齐沈阳', '高工', '03-10月-1989',1000,3100, 103); INSERT INTO teachers VALUES(10306,'车东日', '助教', '05-9月-2001',500,1900, 103); INSERT INTO teachers VALUES(10309,'臧海涛','工程师', '29-6月-1999',600,2400, 103); INSERT INTO teachers VALUES(10307,'赵昆', '讲师', '18-2月-1996',800,2700, 103); 教师记录（NULL）： INSERT INTO teachers VALUES(10128,'王晓', NULL,'05-9月-2007',NULL,1000, 101); INSERT INTO teachers VALUES(10328,'张笑', NULL, '29-9月-2007',NULL,1000, 103); INSERT INTO teachers VALUES(10228,'赵天宇', NULL, '18-9月-2007',NULL,1000, 102); 连接查询时使用： INSERT INTO teachers VALUES(11111,'林飞', NULL, '11-10月-2007',NULL,1000, NULL); 例3.3_22 添加Courses记录 INSERT INTO courses VALUES(10101,'计算机组成原理',4); INSERT INTO courses VALUES(10201,'自动控制原理',4); INSERT INTO courses VALUES(10301,'工程制图',3); INSERT INTO Courses VALUES(10102,'C++语言程序设计',3); INSERT INTO courses VALUES(10202,'模拟电子技术',4); INSERT INTO courses VALUES(10302,'理论力学',3); INSERT INTO courses VALUES(10103,'离散数学',3); INSERT INTO courses VALUES(10203,'数字电子技术',4); INSERT INTO courses VALUES(10303,'材料力学',3); 例3.3_23 添加students_grade记录 INSERT INTO students_grade VALUES(10101,10101,87); INSERT INTO students_grade VALUES(10101,10201,100); INSERT INTO students_grade VALUES(10101,10301,79); 例3.3_24 添加grades记录 INSERT INTO grades VALUES(1,0,59,'不及格'); INSERT INTO grades VALUES(2,60,69,'及格'); INSERT INTO grades VALUES(3,70,79,'中等'); INSERT INTO grades VALUES(4,80,89,'良好'); INSERT INTO grades VALUES(5,90,100,'优秀'); 3.3.6 查看数据 例3.3_25 查看students数据 SELECT * FROM students; 例3.3_26 查看teachers数据 SELECT * FROM teachers; 例3.3_27 查看departments数据 SELECT * FROM departments; 例3.3_28 查看courses数据 SELECT * FROM courses; 例3.3_29 查看students_grade数据 SELECT * FROM students_grade; 例3.3_30 查看grades数据 SELECT * FROM grades; 3.3.7 删除数据 例3.3_31 删除students_grade数据 DELETE FROM students_grade; 例3.3_32 删除teachers数据 DELETE FROM teachers; 例3.3_33 删除departments数据 DELETE FROM departments; 例3.3_34 删除courses数据 DELETE FROM courses; 例3.3_35 删除students数据 DELETE FROM students; 例3.3_36 删除grades数据 DELETE FROM grades;
