2022年Oracle数据库基础 .pdf

Oracle 数据字段的类型和Oracle 中运算符1.oracle 数据字段的主要数据类型数据类型说明char(n)存放定长字符串，最长为2000 个字符nchar(n)最长由数据库使用的字符集决定varchar2(n)存放变长字符串，最长为4000 个字符nVarchar2(n)最长由数据库使用的字符集决定number(l,p)存放数值类型数据。L 长度p 小数位数,最长为38 位.可以用来保存任何数值类型的数据blob 二进制大对象，长度2G Date 存放日期时间,默认格式：DD-MONTH-YY long 存放可变长字符数据。长度2G clob 存放可变长字符数据。长度2G bFile 存放在数据库外部的大型二进制文件。长度=ALL(Select salary from emp where deptno=30)3Exists 用来检查子查询的结果，如果至少有一行，则返回true，否则，返回false 4双目运算符-可以作用于两个日期相减，返回它们之间相差的天数，可用于计算年龄等例如：select 姓名,ceil(Sysdate-birthday)/365)年龄from men 一、准备数据：例 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(10);例 2 departments表结构CREATE TABLE departments(department_id NUMBER(3)CONSTRAINT department_pk PRIMARY KEY,department_name VARCHAR2(8)NOT NULL,address VARCHAR2(40);例 3 teachers表结构CREATE TABLE teachers(teacher_id NUMBER(5)CONSTRAINT teacher_pk PRIMARY KEY,name VARCHAR2(8)NOT NULL,名师资料总结-精品资料欢迎下载-名师精心整理-第 2 页，共 15 页 -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);例 4 courses表结构CREATE TABLE courses(course_id NUMBER(5)CONSTRAINT course_pk PRIMARY KEY,course_name VARCHAR2(30)NOT NULL,credit_hour NUMBER(2);例 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);例 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);二查看表结构DESCRIBE;例 1 查看 students表结构DESCRIBE students;例 2 查看 teachers表结构DESCRIBE teachers;名师资料总结-精品资料欢迎下载-名师精心整理-第 3 页，共 15 页 -例 3 查看 departments 表结构DESCRIBE departments;例 4 查看 courses 表结构DESCRIBE courses;例 5 查看 students_grade表结构DESCRIBE students_grade;例 6 查看 Grades 表结构DESCRIBE grades;三删除表及其结构DROP TABLE;例 1 删除 teachers表。DROP TABLE teachers;例 2 删除 departments 表。DROP TABLE departments;例 3 删除 students_grade表。DROP TABLE students_grade;例 4 删除 students表。DROP TABLE students;例 5 删除 courses 表。DROP TABLE courses;例 6 删除 grades表。DROP TABLE grades;四向表中添加数据INSERT INTO VALUES(字段值);要求字段值必须与字段名顺序，类型相同INSERT INTO(字段名)VALUES(字段值);例 1 添加 students记录学生记录：INSERT INTO students VALUES(10101,NULL,王晓芳,女,07-5 月-1988,计算机);名师资料总结-精品资料欢迎下载-名师精心整理-第 4 页，共 15 页 -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,机电工程);学生记录（NULL）：INSERT INTO students VALUES(10328,10301,曾程程,男,NULL,机电工程);INSERT INTO students VALUES(10128,10101,白昕,男,NULL,计算机);INSERT INTO students VALUES(10228,10205,林紫寒,女,NULL,自动化);例 2 添加 departments 记录系部记录：INSERT INTO departments V ALUES(101,信息工程,1 号教学楼);INSERT INTO departments V ALUES(102,电气工程,2 号教学楼);INSERT INTO departments V ALUES(103,机电工程,3 号教学楼);连接查询时使用：名师资料总结-精品资料欢迎下载-名师精心整理-第 5 页，共 15 页 -INSERT INTO departments V ALUES(104,工商管理,4 号教学楼);例 3 添加 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);名师资料总结-精品资料欢迎下载-名师精心整理-第 6 页，共 15 页 -例 4 添加 Courses 记录INSERT INTO courses V ALUES(10101,计算机组成原理,4);INSERT INTO courses V ALUES(10201,自动控制原理,4);INSERT INTO courses V ALUES(10301,工程制图,3);INSERT INTO Courses V ALUES(10102,C+语言程序设计,3);INSERT INTO courses V ALUES(10202,模拟电子技术,4);INSERT INTO courses V ALUES(10302,理论力学,3);INSERT INTO courses V ALUES(10103,离散数学,3);INSERT INTO courses V ALUES(10203,数字电子技术,4);INSERT INTO courses V ALUES(10303,材料力学,3);例 5 添加 students_grade记录INSERT INTO students_grade V ALUES(10101,10101,87);INSERT INTO students_grade V ALUES(10101,10201,100);INSERT INTO students_grade V ALUES(10101,10301,79);例 6 添加 grades记录INSERT INTO grades V ALUES(1,0,59,不及格);INSERT INTO grades V ALUES(2,60,69,及格);INSERT INTO grades V ALUES(3,70,79,中等);INSERT INTO grades V ALUES(4,80,89,良好);INSERT INTO grades V ALUES(5,90,100,优秀);六查看数据例 1 查看 students数据SELECT*FROM students;例 2 查看 teachers数据SELECT*FROM teachers;例 3 查看 departments 数据SELECT*FROM departments;例 4 查看 courses 数据SELECT*FROM courses;例 5 查看 students_grade数据SELECT*FROM students_grade;例 6 查看 grades数据SELECT*FROM grades;名师资料总结-精品资料欢迎下载-名师精心整理-第 7 页，共 15 页 -七删除数据DELETE FROM 只删除表记录，不删除表结构及其关系例 1 删除 students_grade数据DELETE FROM students_grade;例 2 删除 teachers数据DELETE FROM teachers;例 3 删除 departments 数据DELETE FROM departments;例 4 删除 courses 数据DELETE FROM courses;例 5 删除 students数据DELETE FROM students;例 6 删除 grades数据DELETE FROM grades;简单查询查询是指从数据库中找到满足条件的数据。数据查询(select)可以从一个表或多个表、视图或快照中检索数据。用户要对表或快照进行查询操作，该表或者快照必须在用户自己的模式中，或者用户在这些对象上具有select权限。用户要查询视图的基表的行，在该表上也必须有 select 权限。主要内容：使用 select子句和 from 子句的简单查询使用 where 子句的条件查询使用 order by 子句对记录进行排序使用 group by 子句和 having 子句对记录进行分组查询Select 语句功能强大，语法也比较复杂，完整的select语句由 6 个子句构成，分别为：Select 子句,指定要获取表中哪些列数据from 子句，指定数据来自哪些表where 子句，指定获得哪些行数据group by 子句，用于对表中数据排序having 子句，在组中指定条件order by 子句，指定排序的列和排序方式名师资料总结-精品资料欢迎下载-名师精心整理-第 8 页，共 15 页 -查询语句的完整语法如下：select Bounds600=Bounds=600 1-10-1949=Bounds=600 ,!=Bounds300 包含测试in 在指定集合中Deptno in(10,20)Not in 不在指定集合中Deptno not in(10,20,30)范围测试Between and 在指定范围中Wage between 1000 and 2000 Not Between and 不在指定范围中Wage not between 600 and 800 匹配测试like 与指定模式匹配Name like 王%表示 0 个或多个字符，_ 表示单个字符Not like 不 与 指 定 模 式 匹配Name not like 王%NULL 测试Is Null 是 NULL 值Bounds is null Is not NUll 不是 NULL 值Bounds not is null 逻辑运算符And 逻辑与Bounds600 and name like 张%Or 逻辑或Bounds600 or name like 张%Not 逻辑非Not bounds=600 4.1 单一条件查询例 1：数值型SELECT name,hire_date,title,wage FROM teachers WHERE wage=2000;名师资料总结-精品资料欢迎下载-名师精心整理-第 10 页，共 15 页 -例 2：字符型SELECT student_id,name,specialty,dob FROM students WHERE specialty=计算机;例 3：日期型SELECT student_id,name,specialty,dob FROM students WHERE dob 1-1月-1990;例 4：数值型SELECT name,hire_date,title,bonus FROM teachers WHERE bonus IN(500,600);例 5：日期型SELECT student_id,name,specialty,dob FROM students WHERE dob IN(08-10月-1990,26-12 月-1989);例 6：数值型SELECT name,hire_date,title,bonus FROM teachers WHERE bonus BETWEEN 500 AND 600;例 7：字符型SELECT student_id,name,specialty,dob FROM students WHERE name LIKE 王%;例 8：数值型SELECT name,hire_date,title,bonus FROM teachers WHERE bonus IS NULL;例 9：字符型SELECT name,hire_date,title,bonus FROM teachers WHERE title IS NULL;例 10：日期型SELECT student_id,name,specialty,dob FROM students WHERE dob IS NULL;4.2 复合条件查询例 1：AND SELECT student_id,name,sex,specialty FROM students WHERE specialty=计算机 AND sex=男;例 2：OR SELECT student_id,name,sex,specialty FROM students WHERE specialty=计算机 OR specialty=自动化;名师资料总结-精品资料欢迎下载-名师精心整理-第 11 页，共 15 页 -例 3：NOT SELECT student_id,name,sex,specialty FROM students WHERE NOT specialty=计算机;例 4：not in SELECT student_id,name,specialty,dob FROM students WHERE name NOT IN(欧阳春岚,高山);例 5：not between and SELECT student_id,name,specialty,dob FROM students WHERE dob BETWEEN 1-1月-1989 AND 1-1 月-1990;例 6：NOT LIKE SELECT student_id,name,specialty,dob FROM students WHERE name NOT LIKE 张%;例 7：IS NOT NULL SELECT name,hire_date,title,bonus FROM teachers WHERE bonus IS NOT NULL;例 8 SELECT student_id,name,sex,specialty FROM students WHERE specialty=计算机 AND sex=女 OR specialty=机电工程 AND sex=男;例 9 SELECT name,hire_date,title,bonus,wage FROM teachers WHERE NOT title=工程师 AND hire_date 1-1 月-2002 AND wage 1000 GROUP BY department_id;4.4.3 HAVING子句Group by 子句用于指定分组的依据，而 having 子句则指定条件，用于限制分组显示结果。Having 子句中的条件用于指定限制分组结果的条件。Having 子句必须和group by 子句一起使用，而group by 子句可以单独使用例 1：同时使用GROUP BY 子句以及HAVING 子句SELECT department_id,A VG(wage)FROM teachers GROUP BY department_id HA VING A VG(wage)2200;例 2：同时使用WHERE 子句、GROUP BY 子句以及HAVING 子句SELECT department_id,A VG(wage)FROM teachers WHERE wage 2000;例 3：使用 ORDER BY 子句改变分组查询输出结果的顺序。SELECT department_id,A VG(wage)FROM teachers WHERE wage=2000 ORDER BY 2;名师资料总结-精品资料欢迎下载-名师精心整理-第 14 页，共 15 页 -名师资料总结-精品资料欢迎下载-名师精心整理-第 15 页，共 15 页 -