Oracle中的SQL使用操作实例

实验五：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’,’[e-mail]’,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','[e-mail]',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

[e-mail] 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’,’[e-mail]’,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','[e-mail]',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

[e-mail] 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 行。