Oracle_数据库设计开发简单过程.ppt

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,create 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) ); /*1．CHAR的长度是固定的，而VARCHAR2的长度是可以变化的。 2．CHAR的效率比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 students_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 FROM 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() 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 3000 GROUP BY department_id HAVING AVG(wage) = 2000 ORDER BY 2; 子查询 单行子查询： SELECT * FROM Students WHERE specialty = (SELECT specialty FROM Students WHERE name = 王天仪); 在HAVING子句中使用子查询： SELECT department_id, AVG(wage) AS 平均工资 FROM