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’,’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 行。

 

(0)
上一篇 2022年3月22日
下一篇 2022年3月22日

相关推荐