《Oracle_数据库设计开发简单过程课件》由会员分享，可在线阅读，更多相关《Oracle_数据库设计开发简单过程课件（19页珍藏版）》请在金锄头文库上搜索。
1、Oracle数据库系统实训,数据库实现简单过程,计算机学院,表空间创建、用户创建、授权,CREATE TABLESPACE test DATAFILE test.dbf size 1000m; CREATE USER test IDENTIFIED BY test DEFUALT TABLESPACE test TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON test; -quota：配额 GRANT DBA TO test; GRANT connect,create table,create procedure,create sequence,cre
2、ate trigger,create database link TO test; commit;,建表,CREATE TABLE students ( student_id NUMBER(5) CONSTRAINT student_pk PRIMARY KEY, monitor_id NUMBER(5), sname VARCHAR2(10) NOT NULL, ssex VARCHAR2(6) CONSTRAINT sex_chk CHECK(ssex IN (男,女), birthdate DATE, specialty VARCHAR2(10) ); /*1CHAR的长度是固定的，而V
3、ARCHAR2的长度是可以变化的。 2CHAR的效率比VARCHAR2的效率稍高。 3标准的VARCHAR类型可以存储空字符串。Oracle自己开发了一个数据类型VARCHAR2，它将VARCHAR可以存储空字符串的特性改为存储NULL值。Oracle建议使用VARCHAR2而*/ CREATE TABLE students_grade( student_id NUMBER(5) CONSTRAINT students_grade_fk_students REFERENCES students(student_id), course_id NUMBER(5) CONSTRAINT studen
4、ts_grade_fk_courses REFERENCES courses(course_id), score NUMBER(4,1) ); 查看students表结构 DESCRIBE students;,SELECT的使用,查询指定列：SELECT name, title, wage, hire_date FROM teachers; 查询时，指定日期格式：SELECT name, title, wage, TO_CHAR(hire_date,YYYY-MM-DD) FROM teachers; 单一条件查询：SELECT name, hire_date, title, bonus FR
5、OM teachers WHERE bonus IN(500,600); 单一条件查询： SELECT student_id, name, specialty, birthdate FROM students WHERE name LIKE 王%; IS NULL的使用：SELECT name, hire_date, title, bonus FROM teachers WHERE bonus IS NULL; 使用别名：SELECT name AS 姓名, birthdate AS 出生日期“ FROM students ORDER BY 出生日期 DESC; GROUPBY子句，带有SUM
6、() AVG(): SELECT department_id, SUM(wage), AVG(wage) FROM teachers GROUP BY department_id; 使用ORDER BY子句改变分组查询输出结果的顺序: SELECT department_id, AVG(wage) FROM teachers WHERE wage = 2000 ORDER BY 2;,子查询,单行子查询： SELECT * FROM Students WHERE specialty = (SELECT specialty FROM Students WHERE name = 王天仪); 在HA
7、VING子句中使用子查询： SELECT department_id, AVG(wage) AS 平均工资 FROM Teachers GROUP BY department_id HAVING AVG(wage) (SELECT MIN(AVG(wage) FROM Teachers GROUP BY department_id);,连接查询,SELECT s.student_id, s.name, c.course_name, AVG(sg.score) AS 平均成绩 FROM Students s, Courses c, Students_grade sg WHERE s.studen
8、t_id = sg.student_id AND c.course_id = sg.course_id GROUP BY s.student_id, s.name, c.course_name;,修改与事务,DELETE FROM Students WHERE specialty = 计算机应用; 1事务提交 INSERT INTO departments VALUES(111,地球物理,X号教学楼); COMMIT; SELECT * FROM departments; 2事务回滚 UPDATE departments SET address = 5号教学楼 WHERE department
9、_id = 104; SELECT * FROM departments; ROLLBACK; SELECT * FROM departments;,求LENGTH(My name is yZM)的值： SELECT LENGTH(My name is yZM) FROM dual; 求LTRIM(student, tu)、LTRIM(student)的值：SELECT LTRIM(student, stu), LTRIM(student) FROM dual; 求CURRENT_DATE的值：SELECT CURRENT_DATE FROM dual; 求SYSDATE的值：SELECT S
10、YSDATE FROM dual; 求TO_CHAR(sysdate, YYYY-MM-DD), TO_CHAR(2008.0808, 9.9EEEE)的值。 SELECT TO_CHAR(sysdate, YYYY-MM-DD), TO_CHAR(2008.0808, 9.9EEEE) FROM dual;,DDL,由表Teachers复制生成表Teachers2。 CREATE TABLE Teachers2 AS SELECT * FROM Teachers; 由表Teachers和表Departments 生成表Teachers4。 CREATE TABLE Teachers4 AS
11、SELECT t.teacher_id, t.name, department_name FROM Teachers t, Departments d WHERE t.department_id=d.department_id; 修改表的列 修改Teachers1表wage列的数字精度，由原来的NUMBER(5)修改为NUMBER(7,2)。 DESCRIBE Teachers1 ALTER TABLE Teachers1 MODIFY wage NUMBER(7,2); DESCRIBE Teachers1,PL/SQL 程序块,顺序结构 DECLARE v_student students
12、%ROWTYPE; BEGIN SELECT * INTO v_student FROM students WHERE student_id = 10213; DBMS_OUTPUT.PUT_LINE (姓名：|v_student.name); DBMS_OUTPUT.PUT_LINE (性别：|v_student.sex); DBMS_OUTPUT.PUT_LINE (出生日期：|v_student.birthdate); DBMS_OUTPUT.PUT_LINE (专业：|v_student.specialty); END; / 分支结构 DECLARE v_id teachers.tea
13、cher_id%TYPE; v_title teachers.title%TYPE; BEGIN v_id := ,带异常处理的PL/SQL 程序块1-2,SET SERVEROUTPUT ON /*命令设置环境变量SERVEROUTPUT为打开状态, 能够在SQL*plus中输出结果 使用函数dbms_output.put_line()可以输出参数的值。 */ DECLARE v_id Students.student_id%TYPE; v_sname Students.name%TYPE; BEGIN v_id := ,带异常处理的PL/SQL 程序块2-2,SET SERVEROUTPU
14、T ON DECLARE v_specialty Students.specialty%TYPE; v_sname Students.name%TYPE; BEGIN v_specialty := ,PL/SQL 程序块游标FOR循环,SET SERVEROUTPUT ON DECLARE v_specialty Students.specialty%TYPE; CURSOR Students_cur IS SELECT name, birthdate FROM Students WHERE specialty = v_specialty; BEGIN v_specialty := ,复合数据
15、类型,SET SERVEROUTPUT ON DECLARE v_student Students%ROWTYPE; -行数据类型 BEGIN SELECT * INTO v_student FROM Students WHERE student_id = 10201; DBMS_OUTPUT.PUT_LINE (姓名 性别 专业); DBMS_OUTPUT.PUT_LINE (v_student.name| |v_student.sex| |v_student.specialty); END;,存储过程1-2,CREATE OR REPLACE PROCEDURE app_student(
16、v_no IN students.student_id%TYPE, v_monitor_id IN students.monitor_id%TYPE, v_name IN students.name%TYPE, v_sex IN students.sex%TYPE, v_dob IN students.dob%TYPE, v_specialty IN students.specialty%TYPE) AS BEGIN INSERT INTO students VALUES( v_no, v_monitor_id, v_name, v_sex, v_dob, v_specialty); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN -唯一索引上有重复值 DBMS_OUTPUT.PUT_LINE(插入学生信息时，学生号不能重复。); END app_student; SET SER
