数据库——简单SQL语句的应用

夏夏夏至

已于 2023-06-24 14:49:36 修改

阅读量297

点赞数 1

文章标签： sql 数据库

于 2023-06-23 19:53:04 首次发布

本文链接：https://blog.csdn.net/qq_45945307/article/details/131353666

版权

1 实验目的

\1. 熟悉 Oracle 的环境，学习使用SQL Developer 与Oracle 进行交互；

\2. 建立基本的数据库表，表结构的查看、修改与删除；

\3. 学习对表中数据进行插入，删除，修改及查询操作。

2 实验内容

SQL Developer 中使用实验一创建的学生学号用户连接Oracle 数据库，创建学生信息表（INFOS）和成绩表（SCORES），进行数据库表及数据的插入、查询、更新、删除等基本操作。

使用实验一导入的 EMP 表（员工表）、DEPT 表（部门表）和 SALGRADE表（工资等级表），进行简单查询以及高级查询操作。

3 实验软件

Oracle 数据库、SQL Developer

4 实验步骤及数据记录

4.1 数据库表和字段的基本操作

4.1.1 创建学生信息表（INFOS）和约束

查看 INFOS 表创建成功后的“列”和“约束条件”内容，并截图记录在实验报告中。

4.1.2 创建成绩表（SCORES）和约束

查看 SCORES 表创建成功后的“列”和“约束条件”内容，并记录：

4.1.3 插入数据

使用以下INSERT 命令，在学生信息表（INFS）中插入两个学生的信息：

查看插入数据成功后INFOS 表的“数据”内容，并截图记录在实验报告中。

4.1.4 简单查询数据

用SELECT 命令查询学生信息表（INFOS），获取所有性别（GENDER）为 “男”的学生姓名（STUNAME）、性别（GENDER）、年龄（AGE）和住址（STUADDRESS）信息，并按年龄排序。记录本次查询操作使用的相关命令和查询结果。

SELECT STUNAME,GENDER,AGE,STUADDRESS FROM INFOS
WHERE GENDER='男'
ORDER BY AGE

4.1.5 更新数据

用UPDATE 命令更新学生信息表（INFOS）中学生姓名（STUNAME）为“阮小二”的以下信息：

l 班号（CLASSNO）改为“1002”

l 住址（STUADDRESS）改为“山东莱芜”

记录本次更新操作使用的相关命令。

UPDATE INFOS SET CLASSNO='1002',STUADDRESS='山东莱芜'
WHERE STUNAME='阮小二';

QQ图片20230623195111

用 SELECT 命令查询学生信息表（INFOS），获取学生姓名（STUNAME）为“阮小二”的更新后的班号（CLASSNO）和住址（STUADDRESS）信息。记录命令和查询结果。

SELECT STUNAME,CLASSNO,STUADDRESS 
FROM INFOS
WHERE STUNAME='阮小二';

4.1.6 删除某条数据

用DELETE 命令删除学生信息表（INFOS）中学号（STUID）为“s100102” 的学生信息；然后用SELECT 命令查询学生信息表（INFOS）的所有信息。

记录本次删除和查询操作使用的相关命令，并截图记录相应的操作结果。

DELETE
FROM INFOS 
WHERE STUID='s100102';

SELECT * FROM INFOS;

4.2 简单查询

使用 Oracle 自带的三张表：EMP 表（员工表）、DEPT 表（部门表）和

SALGRADE 表（工资等级表）。

\1) 每名员工年终奖是 2000 元，请在 EMP 表中查询获取基本工资在 2000 元以上的员工姓名及其工资和不含奖金的年总工资信息。

记录本次查询操作使用的相关命令，并截图记录查询结果。

SELECT ENAME AS 姓名,SAL AS 工资,SAL*12+2000 AS 工资总数 
FROM EMP
WHERE SAL>2000;

\2) 请在EMP 表中查询获取工资在 2000 元以上的员工姓名及其工作信息。

SELECT (ENAME || 'is a ' || JOB) AS "Employee Details" ①
FROM EMP
WHERE SAL>2000;

4.3 高级查询

\1) 查询EMP 表中的员工所在部门编号

SELECT DISTINCT DEPTNO FROM EMP;

查询结果。

效果等同于 SELECT DEPTNO FROM EMP GROUP BY DEPTNO;

\2) 查询出薪酬少于 2000 且没有发奖金的员工

第一步：查询薪酬少于 2000 的员工。

SELECT ENAME,JOB,SAL,COMM FROM EMP WHERE SAL<2000;

查询结果。

第二步：查询薪酬少于 2000 且没有发奖金的员工

SELECT ENAME,JOB,SAL,COMM FROM EMP WHERE SAL<2000 AND COMM IS NULL;

在查询条件中NULL 值用IS NULL 作条件，非NULL 值用NOT IS NULL做条件。

查询结果。

\3) 查询工作职责是SALESMAN、PRESIDENT 或 ANALYST 的员工

SELECT ENAME,JOB,SAL FROM EMP
WHERE JOB IN ('SALESMAN', 'PRESIDENT', 'ANALYST');

查询结果。
QQ图片20230623195215

\4) 查询工资从 1000 到 2000 之间的员工

SELECT ENAME,JOB,SAL FROM EMP WHERE SAL BETWEEN 1000 AND 2000;

查询结果。

\5) 查询员工名称以J 开头以S 结尾的员工的姓名、工资和工资

SELECT ENAME,JOB,SAL FROM EMP WHERE ENAME LIKE 'J%S';

查询结果。

\6) 查询出DEPT 表中没有员工的部门编号

只需求出DEPT 表中部门编号和EMP 表中部门编号的补集即可。

SELECT DEPTNO FROM DEPT MINUS SELECT DEPTNO FROM EMP;

查询结果。

\7) 查询出月工资大于 2000 元的员工姓名、工作、工资，及其所在部门名称

SELECT e.ENAME,e.JOB,e.SAL,d.DNAME FROM EMP e,DEPT d
WHERE e.DEPTNO=d.DEPTNO
AND e.SAL>2000;

查询结果。

5 实验结论及思考题

\1) 查询出工作职责不是 SALESMAN、PRESIDENT 或 ANALYST 的员工姓名、工作、工资，及其所在部门名称。请写出 SELECT 命令，并给出查询结果截图。

SELECT E.ENAME,E.JOB,E.SAL,D.DNAME 
FROM EMP E,DEPT D
WHERE E.JOB NOT IN ('SALESMAN' ,'PRESIDENT' ,'ANALYST')
AND E.DEPTNO=D.DEPTNO;

\2) 换一种方法查询出工资大于 2000 元的员工姓名、工作、工资，及其所在部门名称。请写出SELECT 命令，并给出查询结果截图。

SELECT EMP.ENAME,EMP.JOB,EMP.SAL,DEPT.DNAME 
FROM EMP
INNER JOIN DEPT ON (EMP.DEPTNO=DEPT.DEPTNO)
WHERE EMP.SAL>2000;

\3) 查询出每个部门下的员工姓名和工资。请写出SELECT 命令，并给出查询结果截图。

SELECT DEPT.DNAME,EMP.ENAME,EMP.SAL
FROM EMP,DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO
ORDER BY DEPT.DNAME;

\4) 如果在步骤 1.4 中使用 sys 用户查询学生用户的 INFOS 表，能否查询到学生用户 INFOS 表的添加、删除、修改操作？为什么？如果不同，如何才能让 sys 用户查询到学生用户对INFOS 表的添加、删除、修改操作？

不能因为学生用户没有将表授权给sys用户，sys不能对表进行访问。

可以使用授权命令将INFOS表的所有权限给予SYS

GRANT ALL ON INFOS to SYS ;

在用户SYS中，可以使用STU2019010801003.INFOS来访问INFOS表，如下所示：

SELECT * FROM STU2019010801003.INFOS;

6 总结及心得体会

在本次实验中，我们利用搭建好的环境进行了简单SQL语句的使用练习。我们创建表定义约束并插入内容进行修改和查询，我们还使用Oracle自带的几张表进行了单表和多表查询，再次的熟悉了数据查询的命令，并且对于SQL Developer的使用有了更多的了解。我们也通过思考题的方式了解了表权限的授予方式。收获良多。