实验五：Oracle中的SQL使用
一、实验目的
1.掌握SQL语言中常用系统函数;
2.掌握SQL语言的应用。
二、 实验内容
1. 查询SQL中如下常用函数的使用，并举例说明(完成格式参考Length)。
l字符< Ltrim、Replace、Rtrim、Substr、Trim>
l日期< Sysdate、Current_date、next_day>
l转换< To_char、to_date、to_number>
l统计函数
l其他< User、Decode、Nvl>
例如：Length
函数形式：Length(X)
函数说明：返回字符串X的长度
举例：selectlength(‘hello world’) from dual;
运行结果：
LENGTH(‘HELLOWORLD’)
一、字符
1)Ltrim
函数形式：Ltrim(X,trim_string)
函数说明：删除字符串X左边的字符。
举例：selectltrim(‘***Welcome***’,’*’) from dual;
运行结果：
LTRIM(‘***
———-
Welcome***
2)Replace
函数形式：Replace(X,search_string,replace_string)
函数说明：在字符串X中搜索search_string,如果找到则使用replace_string替换。
举例：select replace(‘HelloWelcome’,’Welcome’,’World’) from dual;
运行结果：
REPLACE(‘HE
———–
Hello World
3)Rtrim
函数形式：Rtrim(X,trim_string)
函数说明：去掉字符串X右边trim_string指定的字符
举例：select rtrim(‘***Welcome***’,’*’)from dual;
运行结果：
RTRIM(‘***
———-
***Welcome
4)Substr
函数形式：Substr(X,srart,length)
函数说明：返回字符串X的子串，开始位置是start，可选的子串长度为length
举例：selectsubstr(‘***Welcome***’,4,7) from dual;
运行结果：
SUBSTR(
——-
Welcome
5)Trim
函数形式：Trim(trim_char from X)
函数说明：删除字符串X中左右两端的一些字符，如果提供可选择的trim_string则删除，否则删除空格。
举例：select trim(‘*’ from’***Welcome***’) from dual;
运行结果：
TRIM(‘*
——-
Welcome
二、日期
1)Sysdate
函数形式：Sysdate()
函数说明：返回当前系统的日期
举例：select sysdate from dual;
运行结果：
SYSDATE
————–
28-3月 -18
2)Current_date
函数形式：Current_date()
函数说明：返回本地时区当前日期
举例：select current_date fromdual;
运行结果：
CURRENT_DATE
————–
28-3月 -18
3)Next_day
函数形式：Next_day(X,day)
函数说明：返回紧接着X的下一天，day是一字符串
举例：select next_day(sysdate,6)from dual;
运行结果：
NEXT_DAY(SYSDA
————–
06-4月 -18
三、转换
1)To_char
函数形式：Tochar(X,format)
函数说明：把指定的表达式转变成字符串,format用于指定X的表达式的格式。
举例：select To_char(sysdate,’HH12-MI-SS’)时间 from dual;
运行结果：
时间
——–
03-27-23
2)to_date
函数形式：to_date(c,fmt)
函数说明：将字符串转化为Oracle中的一个日期。如果参数fmt不为空时，则按照fmt指定的格式进行转换，如果fmt为J则表示按照公元制转换，c则必须为大于0小于5373484的正整数。
举例：select to_date(‘2018-3-3015:38:19′,’yyyy-mm-dd hh24:mi:ss’)
from dual;
运行结果：
TO_DATE(‘2018-
————–
30-3月 -18
3)to_number
函数形式：to_number(c,fmt)
函数说明：把某个表达式转变成数字。
举例：select to_number(’19f’,’xxx’),to_number(‘f’,’xxx’)from dual;
运行结果：
TO_NUMBER(’19F’,’XXX’)TO_NUMBER(‘F’,’XXX’)
——————————————
415 15
四、统计
1)Sum
函数形式：Sum(X)
函数说明：汇总值
举例：select sum(sal) from emp;
运行结果：
SUM(SAL)
———-
29025
2)Avg
函数形式：Avg(X)
函数说明：平均值
举例：select Avg(sal) from emp
group by empno
having empno=7369;
运行结果：
AVG(SAL)
———-
800
3)Max
函数形式：Max(X)
函数说明：求最大值
举例：select max(sal) from emp;
运行结果：
MAX(SAL)
———-
5000
4)Min
函数形式：min(X)
函数说明：求最小值
举例：select min(sal) from emp;
运行结果：
MIN(SAL)
———-
800
5)Count
函数形式：Count(X)
函数说明：统计数量
举例：select count(empno) from emp;
运行结果：
COUNT(EMPNO)
————
14
五、其他
1)User
函数形式：user()
函数说明：查找当前用户名
举例：select user from dual;
运行结果：
USER
——————————
SCOTT
2)Decode
函数形式：decode(columnname，值1,翻译值1,值2,翻译值2,…值n,翻译值n,缺省值)
函数说明：将查询结果翻译成其他值(即以其他形式表现出来)
举例：select sal,decode(sal,5000,’工资最高’)from emp;
运行结果：
SAL DECODE(S
———- ——–
800
1600
1250
2975
1250
2850
2450
3000
5000 工资最高
1500
1100
3)Nvl
函数形式：NVL(表达式1，表达式2)
函数说明：一个空值转换函数，如果表达式1为空值，NVL返回值为表达式2的值，否则返回表达式1的值。该函数的目的是把一个空值(null)转换成一个实际的值。其表达式的值可以是数字型、字符型和日期型。但是表达式1和表达式2的数据类型必须为同一个类型。
举例：select ename,NVL(TO_char(comm), ename||’ is not asalesperson!’) AS COMMISSION
from emp
运行结果：
ENAME COMMISSION
————————————————–
SMITH SMITH is not a salesperson!
ALLEN 300
WARD 500
JONES JONES is not a salesperson!
MARTIN 1400
BLAKE BLAKE is not a salesperson!
CLARK CLARK is not a salesperson!
SCOTT SCOTT is not a salesperson!
KING KING is not a salesperson!
TURNER 0
ADAMS ADAMS is not a salesperson!
ENAME COMMISSION
————————————————–
JAMES JAMES is not a salesperson!
FORD FORD is not a salesperson!
MILLER MILLER is not a salesperson!
2. 在使用SQL的内置函数时，经常用到关键词dual，查询dual的含义，并举例说明其使用方法。
答：mysql和oracle下的一个虚拟表，其实就是“空表”的意思
有时候想在查询中用到子查询做临时表，就要用到这个东西。
eg.
select * from(select 10 co1 from dual) tmp
就和
select * from(select 10 co1) tmp 一样。
3.练习SQL事务处理
(参见教材p112- p113中的示例内容，自己举例使用保存点进行事务回退操作，将练习所执行的SQL记录下来)
SQL> insertinto emp(empno,ename,job,sal) values(7901,’ZHY’,’IT’,8000);
已创建 1 行。
SQL>savepoint s1;
保存点已创建。
SQL> insertinto emp(empno,ename,job,sal) values(7911,’KXM’,’Teacher’,3000);
已创建 1 行。
SQL> selectempno,ename,job,sal from emp
2where empno>7900 and empno<7920;
EMPNO ENAME JOB SAL
——————– ——— ———-
7901 ZHY IT 8000
7902 FORD ANALYST 3000
7911 KXM Teacher 3000
SQL>rollback to savepoint s1;
回退已完成。
SQL> selectempno,ename,job,sal from emp
2where empno>7900 and empno<7920;
EMPNO ENAME JOB SAL
——————– ——— ———-
7901 ZHY IT 8000
7902 FORD ANALYST 3000
4. SQL语言的应用(根据题目的写出执行正确的SQL语句)：
(1)解锁HR模式，对HR模式下的表进行操作(此操作不用记录);
(2)以HR身份登录数据库后进行如下操作：
①查询HR.EMPLOYEES表的表结构;
SQL> desc employees
名称 是否为空 类型
————————————————- —————————-
EMPLOYEE_ID NOT NULLNUMBER(6)
FIRST_NAMEVARCHAR2(20)
LAST_NAME NOT NULLVARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBERVARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULLVARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCTNUMBER(2,2)
MANAGER_IDNUMBER(6)
DEPARTMENT_ID NUMBER(4)
②查询EMPLOYEES表中每个雇员的姓名(要求将first_name，last_name字段连接在一起显示)、职位、工薪、部门编号;
SQL> selectfirst_name|| ‘ ‘||last_name “姓名”,job_id “职位”,salary “工薪”,department_id”部门编号” from employees;
③查询HR.DEPARTMENTS表的表结构;
SQL> descdepartments
名称 是否为空 类型
————————————————- —————————-
DEPARTMENT_ID NOT NULL NUMBER(4)
DEPARTMENT_NAME NOT NULLVARCHAR2(30)
MANAGER_IDNUMBER(6)
LOCATION_IDNUMBER(4)
④使用子查询查询属于某一部门员工的姓名、职位、工薪、部门编号的信息(已知的信息为部门名称，部门名称由用户自己给出);
SQL>select first_name|| ‘ ‘||last_name “姓名”,job_id “职位”,salary “工薪”,
departments.department_id “部门编号”
from employees,departments
where employees.department_id=departments.department_id
and departments.department_name=’Sales’;
⑤统计某一部门的雇员的最高和最低工薪;
SQL> select max(salary),min(salary)
from employees,departments
where employees.department_id=departments.department_id
and departments.department_name=’Sales’;
MAX(SALARY) MIN(SALARY)
———– ———–
14000 6100
⑥向EMPLOYEES表添加一组数据：
values(600,’LISI’,’lisi@163.com’,sysdate,’SH_CLERK’,50)
通过SELECT查询语句，查看是否在EMPLOYEES表中添加了相应的记录;
SQL> insert into
employees(employee_id,first_name,last_name,email,hire_date,job_id,
department_id)
values(600,’LI’,’SI’,’lisi@163.com’,sysdate,’SH_CLERK’,50);
已创建 1 行。
SQL> selectemployee_id,first_name,last_name,email,hire_date,job_id,
department_id
fromemployees
whereemployee_id=600;
EMPLOYEE_ID FIRST_NAME LAST_NAME
———– ———————————————
EMAIL HIRE_DATE JOB_IDDEPARTMENT_ID
————————- ———————— ————-
600 LI SI
lisi@163.com 30-3月 -18 SH_CLERK 50
⑦ 使用UPDATE语句更新该记录的SALARY列，为部门编号80的员工上调工薪10%;
SQL> update employees
set salary=salary*1.1
where department_id=80;
已更新34行。
⑧删除操作⑥中新增加的那条记录。
SQL> delete from employees
where employee_id=600;
已删除 1 行。
作业提交
将本文档存盘(文件名保存为：本人学号姓名.doc)后关闭，再将其复制后提交(即粘贴)到服务器中：ftp://172.16.3.220/作业提交/本人班级/本次实验项目 对应的文件夹中!
实验五：Oracle中的SQL使用
一、实验目的
1.掌握SQL语言中常用系统函数;
2.掌握SQL语言的应用。
二、 实验内容
1. 查询SQL中如下常用函数的使用，并举例说明(完成格式参考Length)。
l字符< Ltrim、Replace、Rtrim、Substr、Trim>
l日期< Sysdate、Current_date、next_day>
l转换< To_char、to_date、to_number>
l统计函数
l其他< User、Decode、Nvl>
例如：Length
函数形式：Length(X)
函数说明：返回字符串X的长度
举例：selectlength(‘hello world’) from dual;
运行结果：
LENGTH(‘HELLOWORLD’)
一、字符
1)Ltrim
函数形式：Ltrim(X,trim_string)
函数说明：删除字符串X左边的字符。
举例：selectltrim(‘***Welcome***’,’*’) from dual;
运行结果：
LTRIM(‘***
———-
Welcome***
2)Replace
函数形式：Replace(X,search_string,replace_string)
函数说明：在字符串X中搜索search_string,如果找到则使用replace_string替换。
举例：select replace(‘HelloWelcome’,’Welcome’,’World’) from dual;
运行结果：
REPLACE(‘HE
———–
Hello World
3)Rtrim
函数形式：Rtrim(X,trim_string)
函数说明：去掉字符串X右边trim_string指定的字符
举例：select rtrim(‘***Welcome***’,’*’)from dual;
运行结果：
RTRIM(‘***
———-
***Welcome
4)Substr
函数形式：Substr(X,srart,length)
函数说明：返回字符串X的子串，开始位置是start，可选的子串长度为length
举例：selectsubstr(‘***Welcome***’,4,7) from dual;
运行结果：
SUBSTR(
——-
Welcome
5)Trim
函数形式：Trim(trim_char from X)
函数说明：删除字符串X中左右两端的一些字符，如果提供可选择的trim_string则删除，否则删除空格。
举例：select trim(‘*’ from’***Welcome***’) from dual;
运行结果：
TRIM(‘*
——-
Welcome
二、日期
1)Sysdate
函数形式：Sysdate()
函数说明：返回当前系统的日期
举例：select sysdate from dual;
运行结果：
SYSDATE
————–
28-3月 -18
2)Current_date
函数形式：Current_date()
函数说明：返回本地时区当前日期
举例：select current_date fromdual;
运行结果：
CURRENT_DATE
————–
28-3月 -18
3)Next_day
函数形式：Next_day(X,day)
函数说明：返回紧接着X的下一天，day是一字符串
举例：select next_day(sysdate,6)from dual;
运行结果：
NEXT_DAY(SYSDA
————–
06-4月 -18
三、转换
1)To_char
函数形式：Tochar(X,format)
函数说明：把指定的表达式转变成字符串,format用于指定X的表达式的格式。
举例：select To_char(sysdate,’HH12-MI-SS’)时间 from dual;
运行结果：
时间
——–
03-27-23
2)to_date
函数形式：to_date(c,fmt)
函数说明：将字符串转化为Oracle中的一个日期。如果参数fmt不为空时，则按照fmt指定的格式进行转换，如果fmt为J则表示按照公元制转换，c则必须为大于0小于5373484的正整数。
举例：select to_date(‘2018-3-3015:38:19′,’yyyy-mm-dd hh24:mi:ss’)
from dual;
运行结果：
TO_DATE(‘2018-
————–
30-3月 -18
3)to_number
函数形式：to_number(c,fmt)
函数说明：把某个表达式转变成数字。
举例：select to_number(’19f’,’xxx’),to_number(‘f’,’xxx’)from dual;
运行结果：
TO_NUMBER(’19F’,’XXX’)TO_NUMBER(‘F’,’XXX’)
——————————————
415 15
四、统计
1)Sum
函数形式：Sum(X)
函数说明：汇总值
举例：select sum(sal) from emp;
运行结果：
SUM(SAL)
———-
29025
2)Avg
函数形式：Avg(X)
函数说明：平均值
举例：select Avg(sal) from emp
group by empno
having empno=7369;
运行结果：
AVG(SAL)
———-
800
3)Max
函数形式：Max(X)
函数说明：求最大值
举例：select max(sal) from emp;
运行结果：
MAX(SAL)
———-
5000
4)Min
函数形式：min(X)
函数说明：求最小值
举例：select min(sal) from emp;
运行结果：
MIN(SAL)
———-
800
5)Count
函数形式：Count(X)
函数说明：统计数量
举例：select count(empno) from emp;
运行结果：
COUNT(EMPNO)
————
14
五、其他
1)User
函数形式：user()
函数说明：查找当前用户名
举例：select user from dual;
运行结果：
USER
——————————
SCOTT
2)Decode
函数形式：decode(columnname，值1,翻译值1,值2,翻译值2,…值n,翻译值n,缺省值)
函数说明：将查询结果翻译成其他值(即以其他形式表现出来)
举例：select sal,decode(sal,5000,’工资最高’)from emp;
运行结果：
SAL DECODE(S
———- ——–
800
1600
1250
2975
1250
2850
2450
3000
5000 工资最高
1500
1100
3)Nvl
函数形式：NVL(表达式1，表达式2)
函数说明：一个空值转换函数，如果表达式1为空值，NVL返回值为表达式2的值，否则返回表达式1的值。该函数的目的是把一个空值(null)转换成一个实际的值。其表达式的值可以是数字型、字符型和日期型。但是表达式1和表达式2的数据类型必须为同一个类型。
举例：select ename,NVL(TO_char(comm), ename||’ is not asalesperson!’) AS COMMISSION
from emp
运行结果：
ENAME COMMISSION
————————————————–
SMITH SMITH is not a salesperson!
ALLEN 300
WARD 500
JONES JONES is not a salesperson!
MARTIN 1400
BLAKE BLAKE is not a salesperson!
CLARK CLARK is not a salesperson!
SCOTT SCOTT is not a salesperson!
KING KING is not a salesperson!
TURNER 0
ADAMS ADAMS is not a salesperson!
ENAME COMMISSION
————————————————–
JAMES JAMES is not a salesperson!
FORD FORD is not a salesperson!
MILLER MILLER is not a salesperson!
2. 在使用SQL的内置函数时，经常用到关键词dual，查询dual的含义，并举例说明其使用方法。
答：mysql和oracle下的一个虚拟表，其实就是“空表”的意思
有时候想在查询中用到子查询做临时表，就要用到这个东西。
eg.
select * from(select 10 co1 from dual) tmp
就和
select * from(select 10 co1) tmp 一样。
3.练习SQL事务处理
(参见教材p112- p113中的示例内容，自己举例使用保存点进行事务回退操作，将练习所执行的SQL记录下来)
SQL> insertinto emp(empno,ename,job,sal) values(7901,’ZHY’,’IT’,8000);
已创建 1 行。
SQL>savepoint s1;
保存点已创建。
SQL> insertinto emp(empno,ename,job,sal) values(7911,’KXM’,’Teacher’,3000);
已创建 1 行。
SQL> selectempno,ename,job,sal from emp
2where empno>7900 and empno<7920;
EMPNO ENAME JOB SAL
——————– ——— ———-
7901 ZHY IT 8000
7902 FORD ANALYST 3000
7911 KXM Teacher 3000
SQL>rollback to savepoint s1;
回退已完成。
SQL> selectempno,ename,job,sal from emp
2where empno>7900 and empno<7920;
EMPNO ENAME JOB SAL
——————– ——— ———-
7901 ZHY IT 8000
7902 FORD ANALYST 3000
4. SQL语言的应用(根据题目的写出执行正确的SQL语句)：
(1)解锁HR模式，对HR模式下的表进行操作(此操作不用记录);
(2)以HR身份登录数据库后进行如下操作：
①查询HR.EMPLOYEES表的表结构;
SQL> desc employees
名称 是否为空 类型
————————————————- —————————-
EMPLOYEE_ID NOT NULLNUMBER(6)
FIRST_NAMEVARCHAR2(20)
LAST_NAME NOT NULLVARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBERVARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULLVARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCTNUMBER(2,2)
MANAGER_IDNUMBER(6)
DEPARTMENT_ID NUMBER(4)
②查询EMPLOYEES表中每个雇员的姓名(要求将first_name，last_name字段连接在一起显示)、职位、工薪、部门编号;
SQL> selectfirst_name|| ‘ ‘||last_name “姓名”,job_id “职位”,salary “工薪”,department_id”部门编号” from employees;
③查询HR.DEPARTMENTS表的表结构;
SQL> descdepartments
名称 是否为空 类型
————————————————- —————————-
DEPARTMENT_ID NOT NULL NUMBER(4)
DEPARTMENT_NAME NOT NULLVARCHAR2(30)
MANAGER_IDNUMBER(6)
LOCATION_IDNUMBER(4)
④使用子查询查询属于某一部门员工的姓名、职位、工薪、部门编号的信息(已知的信息为部门名称，部门名称由用户自己给出);
SQL>select first_name|| ‘ ‘||last_name “姓名”,job_id “职位”,salary “工薪”,
departments.department_id “部门编号”
from employees,departments
where employees.department_id=departments.department_id
and departments.department_name=’Sales’;
⑤统计某一部门的雇员的最高和最低工薪;
SQL> select max(salary),min(salary)
from employees,departments
where employees.department_id=departments.department_id
and departments.department_name=’Sales’;
MAX(SALARY) MIN(SALARY)
———– ———–
14000 6100
⑥向EMPLOYEES表添加一组数据：
values(600,’LISI’,’lisi@163.com’,sysdate,’SH_CLERK’,50)
通过SELECT查询语句，查看是否在EMPLOYEES表中添加了相应的记录;
SQL> insert into
employees(employee_id,first_name,last_name,email,hire_date,job_id,
department_id)
values(600,’LI’,’SI’,’lisi@163.com’,sysdate,’SH_CLERK’,50);
已创建 1 行。
SQL> selectemployee_id,first_name,last_name,email,hire_date,job_id,
department_id
fromemployees
whereemployee_id=600;
EMPLOYEE_ID FIRST_NAME LAST_NAME
———– ———————————————
EMAIL HIRE_DATE JOB_IDDEPARTMENT_ID
————————- ———————— ————-
600 LI SI
lisi@163.com 30-3月 -18 SH_CLERK 50
⑦ 使用UPDATE语句更新该记录的SALARY列，为部门编号80的员工上调工薪10%;
SQL> update employees
set salary=salary*1.1
where department_id=80;
已更新34行。
⑧删除操作⑥中新增加的那条记录。
SQL> delete from employees
where employee_id=600;
已删除 1 行。