oracle增删改查语句

时间: 2023-04-03 07:05:06 浏览: 132

Oracle数据库增删改查练习50例

### Oracle数据库增删改查练习知识点详解 #### 一、建表 1. **创建学生表** (`student`) - **表结构** ```sql CREATE TABLE student ( SNO VARCHAR2(10), SNAME VARCHAR2(10), SAGE DATE, SSEX VARCHAR2(10) ); ``` - **插入数据** ```sql INSERT INTO student (SNO, SNAME, SAGE, SSEX) VALUES ('01', '赵雷', TO_DATE('1990/1/1', 'yyyy/mm/dd'), '男'), ('02', '钱电', TO_DATE('1990/12/21', 'yyyy/mm/dd'), '男'), ('03', '孙风', TO_DATE('1990/5/20', 'yyyy/mm/dd'), '男'), ('04', '李云', TO_DATE('1990/8/6', 'yyyy/mm/dd'), '男'), ('05', '周梅', TO_DATE('1991/12/1', 'yyyy/mm/dd'), '女'), ('06', '吴兰', TO_DATE('1992/3/1', 'yyyy/mm/dd'), '女'), ('07', '郑竹', TO_DATE('1989/7/1', 'yyyy/mm/dd'), '女'), ('08', '王菊', TO_DATE('1990/1/20', 'yyyy/mm/dd'), '女'); ``` 2. **通过PL/SQL工具生成增删改查语句** - **创建包** (`eimhe_pkg`) ```sql CREATE OR REPLACE PACKAGE eimhe_pkg AS PROCEDURE add_student(p_sno VARCHAR2, p_sname VARCHAR2, p_sage DATE, p_ssex VARCHAR2); PROCEDURE delete_student(p_sno VARCHAR2); PROCEDURE update_student(p_sno VARCHAR2, p_sname VARCHAR2, p_sage DATE, p_ssex VARCHAR2); FUNCTION get_student(p_sno VARCHAR2) RETURN student%ROWTYPE; END eimhe_pkg; / CREATE OR REPLACE PACKAGE BODY eimhe_pkg AS PROCEDURE add_student(p_sno VARCHAR2, p_sname VARCHAR2, p_sage DATE, p_ssex VARCHAR2) IS BEGIN INSERT INTO student (SNO, SNAME, SAGE, SSEX) VALUES (p_sno, p_sname, p_sage, p_ssex); END add_student; PROCEDURE delete_student(p_sno VARCHAR2) IS BEGIN DELETE FROM student WHERE SNO = p_sno; END delete_student; PROCEDURE update_student(p_sno VARCHAR2, p_sname VARCHAR2, p_sage DATE, p_ssex VARCHAR2) IS BEGIN UPDATE student SET SNAME = p_sname, SAGE = p_sage, SSEX = p_ssex WHERE SNO = p_sno; END update_student; FUNCTION get_student(p_sno VARCHAR2) RETURN student%ROWTYPE IS l_student student%ROWTYPE; BEGIN SELECT * INTO l_student FROM student WHERE SNO = p_sno; RETURN l_student; END get_student; END eimhe_pkg; / ``` - **调用示例** ```sql EXECUTE eimhe_pkg.add_student('09', '陈龙', TO_DATE('1992/5/1', 'yyyy/mm/dd'), '男'); EXECUTE eimhe_pkg.delete_student('09'); EXECUTE eimhe_pkg.update_student('01', '赵磊', TO_DATE('1990/1/1', 'yyyy/mm/dd'), '男'); SELECT * FROM student WHERE SNO = '01'; ``` 3. **创建课程表** (`course`) - **表结构** ```sql CREATE TABLE course ( CNO VARCHAR2(10), CNAME VARCHAR2(10), TNO VARCHAR2(10) ); ``` - **插入数据** ```sql INSERT INTO course (CNO, CNAME, TNO) VALUES ('01', '语文', '02'), ('02', '数学', '01'), ('03', '英语', '03'); ``` 4. **创建教师表** (`teacher`) - **表结构** ```sql CREATE TABLE teacher ( TNO VARCHAR2(10), TNAME VARCHAR2(10) ); ``` - **插入数据** ```sql INSERT INTO teacher (TNO, TNAME) VALUES ('01', '张三'), ('02', '李四'), ('03', '王五'); ``` 5. **创建成绩表** (`sc`) - **表结构** ```sql CREATE TABLE sc ( SNO VARCHAR2(10), CNO VARCHAR2(10), SCORE NUMBER(18, 1) ); ``` - **插入数据** ```sql INSERT INTO sc (SNO, CNO, SCORE) VALUES ('01', '01', 80.0), ('01', '02', 90.0), ('01', '03', 99.0), ('02', '01', 70.0), ('02', '02', 60.0), ('02', '03', 80.0), ('03', '01', 80.0), ('03', '02', 80.0), ('03', '03', 80.0), ('04', '01', 50.0), ('04', '02', 30.0), ('04', '03', 20.0), ('05', '01', 76.0), ('05', '02', 87.0), ('06', '01', 31.0), ('06', '03', 34.0), ('07', '02', 89.0), ('07', '03', 98.0); ``` #### 二、查询 1. **查询'01'、'02'课程分数** - **查询同时存在"01"课程和"02"课程的情况** ```sql SELECT s.SNO, s.SNAME, sc1.SCORE AS "01_SCORE", sc2.SCORE AS "02_SCORE" FROM student s JOIN sc sc1 ON s.SNO = sc1.SNO AND sc1.CNO = '01' JOIN sc sc2 ON s.SNO = sc2.SNO AND sc2.CNO = '02'; ``` - **查询必须存在"01"课程，"02"课程可以没有的情况** ```sql SELECT s.SNO, s.SNAME, sc1.SCORE AS "01_SCORE", sc2.SCORE AS "02_SCORE" FROM student s LEFT JOIN sc sc1 ON s.SNO = sc1.SNO AND sc1.CNO = '01' LEFT JOIN sc sc2 ON s.SNO = sc2.SNO AND sc2.CNO = '02' WHERE sc1.CNO IS NOT NULL; ``` 2. **查询'01'课程比'02'课程成绩低的学生的信息及课程分数** - **查询同时'01'课程比'02'课程分数低的数据** ```sql SELECT s.SNO, s.SNAME, sc1.SCORE AS "01_SCORE", sc2.SCORE AS "02_SCORE" FROM student s JOIN sc sc1 ON s.SNO = sc1.SNO AND sc1.CNO = '01' JOIN sc sc2 ON s.SNO = sc2.SNO AND sc2.CNO = '02' WHERE sc1.SCORE < sc2.SCORE; ``` - **查询同时'01'课程比'02'课程分数低或'01'缺考的数据** ```sql SELECT s.SNO, s.SNAME, sc1.SCORE AS "01_SCORE", sc2.SCORE AS "02_SCORE" FROM student s LEFT JOIN sc sc1 ON s.SNO = sc1.SNO AND sc1.CNO = '01' LEFT JOIN sc sc2 ON s.SNO = sc2.SNO AND sc2.CNO = '02' WHERE (sc1.SCORE < sc2.SCORE OR sc1.SCORE IS NULL); ``` 3. **查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩** ```sql SELECT s.SNO, s.SNAME, AVG(sc.SCORE) AS AVG_SCORE FROM student s JOIN sc ON s.SNO = sc.SNO GROUP BY s.SNO, s.SNAME HAVING AVG(sc.SCORE) >= 60; ``` 4. **查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩** - **有考试成绩，且小于60分** ```sql SELECT s.SNO, s.SNAME, AVG(sc.SCORE) AS AVG_SCORE FROM student s JOIN sc ON s.SNO = sc.SNO GROUP BY s.SNO, s.SNAME HAVING AVG(sc.SCORE) < 60; ``` - **包括没有考试成绩的数据** ```sql SELECT s.SNO, s.SNAME, COALESCE(AVG(sc.SCORE), 0) AS AVG_SCORE FROM student s LEFT JOIN sc ON s.SNO = sc.SNO GROUP BY s.SNO, s.SNAME HAVING COALESCE(AVG(sc.SCORE), 0) < 60; ``` 5. **查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩** - **查询所有成绩的（不含缺考的）。** ```sql SELECT s.SNO, s.SNAME, COUNT(sc.CNO) AS COURSE_COUNT, SUM(sc.SCORE) AS TOTAL_SCORE FROM student s JOIN sc ON s.SNO = sc.SNO GROUP BY s.SNO, s.SNAME; ``` - **查询所有成绩的(包括缺考的)。** ```sql SELECT s.SNO, s.SNAME, COUNT(sc.CNO) AS COURSE_COUNT, SUM(COALESCE(sc.SCORE, 0)) AS TOTAL_SCORE FROM student s LEFT JOIN sc ON s.SNO = sc.SNO GROUP BY s.SNO, s.SNAME; ``` 6. **查询"李"姓老师的数量** ```sql SELECT COUNT(*) AS TEACHER_COUNT FROM teacher WHERE TNAME LIKE '李%'; ``` 7. **哪些学生上过张三（老师）的课** ```sql SELECT DISTINCT s.SNO, s.SNAME FROM student s JOIN sc ON s.SNO = sc.SNO JOIN course c ON sc.CNO = c.CNO JOIN teacher t ON c.TNO = t.TNO WHERE t.TNAME = '张三'; ``` 8. **哪些学生没上过张三（老师）的课** ```sql SELECT s.SNO, s.SNAME FROM student s WHERE s.SNO NOT IN ( SELECT DISTINCT s.SNO FROM student s JOIN sc ON s.SNO = sc.SNO JOIN course c ON sc.CNO = c.CNO JOIN teacher t ON c.TNO = t.TNO WHERE t.TNAME = '张三' ); ``` 9. **查询'01'、'02'都学过的同学的信息** ```sql SELECT s.SNO, s.SNAME FROM student s WHERE EXISTS ( SELECT 1 FROM sc sc1 WHERE sc1.SNO = s.SNO AND sc1.CNO = '01' ) AND EXISTS ( SELECT 1 FROM sc sc2 WHERE sc2.SNO = s.SNO AND sc2.CNO = '02' ); ``` 以上练习涵盖了Oracle数据库中的基本操作，包括表的创建、数据的增删改查以及复杂的多表连接查询。这些练习有助于深入理解关系型数据库的基本概念和操作技巧。

可以回答这个问题。Oracle 增删改查语句分别是：INSERT、DELETE、UPDATE 和 SELECT。INSERT 用于插入新的数据行，DELETE 用于删除数据行，UPDATE 用于修改数据行，SELECT 用于查询数据行。

阅读全文

oracle增删改查语句

相关推荐

ORACLE常用增删改查语句

oracle增删改查

oracle 增删改查

oracle增删改查语法

SQL Server连接Oracle详细步骤和增删改查语句

sql2005连接oracle过程全解和增删改查语句

ssh结合oracle增删改查

JDBC封装oracle增删改查类

ibatis + oracle 增删改查例子

asp写的简单oracle增删改查

Oracle增删改查常用操作.docx

SSH+jquery oracle增删改查以及分页

dbutils + oracle 增删改查批量插入示例

oracle增删改查及入门及注意事项总结

jsp+jdbc_oracle增删改查过滤分页排序

ORACLE增删改查，游标，包，过程，触发器

oracle的增删改查语句

增删改查语句介绍

最新推荐

Django数据库操作的实例(增删改查）

Java中SSM框架实现增删改查功能代码详解

数据库基础测验20241113.doc

微信小程序下拉选择组件

黑板风格计算机毕业答辩PPT模板下载

管理建模和仿真的文件

提升点阵式液晶显示屏效率技术

在SoC芯片的射频测试中，ATE设备通常如何执行系统级测试以保证芯片量产的质量和性能一致？

CodeSandbox实现ListView快速创建指南

"互动学习：行动中的多样性与论文攻读经历"