oracle实训笔记整理（one Day)

2023-08-08 08:34:34

关于数据库的概念

数据：用来描述事物的符号称为数据
数据库：存放数据的仓库
数据库管理系统：用来管理数据的的系统软件
数据库系统：包括数据、数据库管理系统、DBA等。

SQL语言：数据控制语言DCL、数据操作语言DML、数据定义语言DDL

ORACLE

中文名为甲骨文，全球最大的数据服务公司。

TNS 监听服务器要打开服务先打开这个

scott用户第一次使用需要解锁

sqlplus / as sqldba
alter user scott account unlock;
commit;

管理员用户sys，超级管理员用户system.
这两个密码都是口令：123456

简单查询语句

select * from emp;

select e.empno 编号, e.ename 姓名 from emp e;

查询实发工资

select (e.sal + case when e.comm > 0 then e.comm else 0 end) as 实发工资 from emp e;

select  e.ename 姓名,e.job 职位 from emp e where (e.sal + case when e.comm > 0 then e.comm else 
0 end) >= 3000;

查询年薪和日薪

select e.empno 编号, e.ename 姓名,e.sal * 12 年薪,e.sal - 3.12 日薪 from emp e;

select e.empno 编号, e.ename 姓名,e.sal * 12 + 5000 增长后的年薪 from emp e;

字符串连接
select '编号' ||e.empno || ' 姓名' || e.ename as 基本信息 from emp e;

修改表结构
alter table emp modify sal decimal(7,5);

and和or查询

select * from emp where sal >=1500 and sal <=3000;

select * from emp where sal between 1500 and 3000;

select * from emp where sal > 1200 and job = 'SALESMAN';

select * from emp where (deptno = 10 and job = 'MANAGER') or (deptno = 20 and job = 'CLERK');

查询编号在和不在7369,7788,7566范围

select * from emp where empno in(7369,7788,7566);

select * from emp where empno=7369 or empno = 7788 or empno = 7566;

select * from emp where empno not in(7369,7788,7566);

select * from emp where not (empno=7369 or empno = 7788 or empno = 7566);

like模糊查询

%：表示零个或者多个任意字符。_：代表一个任意字符。

select * from emp where ename like 'S%';

select * from emp where ename like '%S';

select * from emp where ename like '_M%';

select * from emp where ename like '%L%';

习题：

查询工资大雨1200的员工姓名和基本工资

select ename 员工姓名, sal 基本工资

  from emp e

 where (e.sal + case

         when e.comm > 0 then

          e.comm

         else

          0

       end) > 1200;

查询员工号为7934的员工姓名和部门号

select ename 员工姓名,deptno 部门号 from emp;

选择工资不在5000到12000的员工姓名和工资

select e.ename 员工姓名,

       (e.sal + case

         when e.comm > 0 then

          e.comm

         else

          0

       end) 工资

  from emp e

 where sal not between 5000 and 12000;

选择雇用时间在1981-02-01到1981-05-01之间的员工姓名，职位(job)和雇用时间,按从早到晚排序

select ename 员工姓名, job 职位, hiredate 雇用时间

  from emp e

 where e.hiredate between to_date('1981-02-01', 'yyyy-mm-dd') and

       to_date('1981-05-01', 'yyyy-mm-dd')

 order by e.hiredate asc;

选择在20或10号部门工作的员工姓名和部门号

select ename 员工姓名,deptno 部门号 from emp where deptno in (10,20);

选择公司中没有管理者的员工姓名及job

select ename 员工姓名,job 职位 from emp where not job = 'MANAGER';

选择公司中有奖金 (COMM不为空,且不为0) 的员工姓名，工资和奖金比例,按工资逆排序,奖金比例逆排序

select ename 员工姓名,

       (e.sal + e.comm) as salary,

       e.comm / (e.sal + e.comm + 0.0) * 100 || '%' as 奖金比例

  from emp e

 where e.comm > 0

 order by (e.sal + e.comm) desc, e.comm / (e.sal + e.comm) desc;

in和like会影响查询效率

排序的语法

select [distinct]列名 as 别名 from 表名 where 查询条件(不能用别名) order by 排序的列(可以用别名) asc/desc

–排序例子

select * from emp order by sal desc;

–两个排序条件，如果第一个条件相同，就用第二个条件

select * from emp order by sal desc,hiredate asc;

单行函数

字符函数：接收数据返回具体的字符信息

upper（字符串）:把字符串内容转换成大写字母
lower(字符串)：把字符串内容转换成小写字母

select upper('naks') 大写 from dual;

replace(列|数据,替换数据,新数据)：把字符串中的旧数据换成新数据

select replace('南昌大学','南昌','南京') from dual;

注：DUAL是Oracle与数据字典一起自动创建的一个表，它只有一列：DUMMY，其数据类型为：VARCHAR2(1)。DUAL中只有一行数据：‘X’。DUAL属于SYS模式，但所有用户都可以使用DUAL名称访问它。用SELECT计算常量表达式、伪列等值时常用该表，因为它只返回一行数据，而使用其它表时可能返回多个数据行。

substr(列|数据,开始位置,长度)：截取字符串，从1开始查找

select * from emp where substr(ename,0,3) ='JAM';

select substr(ename,4) from emp where deptno = 10;

select ename 姓名,substr(ename,length(ename)-2) from emp;

trim(字符串)：去除左右两边空格
rtrim(字符串）：去除右边空格
ltrim(字符串）：去除左边空格

select trim( ' 南昌大学 ') from dual;

数值函数：对数据进行操作的函数

round(列|数值 [保留小数位]);不写保留小数位则不保留如果小数位为负数,则从小数点左边计算。
trunc(列|数值 [保留小数位]) :直接截取小数位，(不指定小数位)默认不保留小数位如果是负数直接整数位为0

select round(2.5),trunc(2.644,2) from dual;

日期函数：

sysdate是系统时间（当前时间）

select e.hiredate,sysdate - e.hiredate as 天数 from emp e
select trunc(sysdate - hiredate) as 雇佣天数 from emp; //去除小数位

ADD_MONTHS(日期，月数);在指定的日期上增加月数
select hiredate 雇佣日期, add_months(hiredate,3) 雇佣后三个月的日期 from emp
NEXT_DAY(日期，周几);当前日期的下一个星期几
select sysdate 今天，next_day(sysdate,'星期二') from dual
LAST_DAY(日期)指定日期在当前月的最后一天
select * from emp where hiredate = last_day(hiredate) - 2
MONTHS_BETWEEN(日期1，日期2) 两个日期间的相隔的月份

select e.empno as 编号,
       e.ename as 姓名,
       hiredate as 日期,
       trunc(months_between(sysdate, hiredate)) as 月数,
       trunc(trunc(months_between(sysdate, hiredate)) / 12) as 年数
  from emp e

上面的第二种写法：

select e.empno as 编号,

      e.ename as 姓名,

      hiredate as 日期,

      trunc(months_between(sysdate, hiredate)) as 月数,

      extract(year from sysdate) - extract(year from hiredate) as 年数

 from emp e

extract()从一个日期时间中截取一个特定的部分。
函数结构如下

extract (    

        { year | month | day | hour | minute | second }    

        | { timezone_hour | timezone_minute }    

        | { timezone_region | timezone_abbr }    

from { date_value | interval_value } )

取出当前日期的年、月、日：

select extract(year from sysdate) 年,

       extract(month from sysdate) 月,

       extract(day from sysdate) 日

  from dual;

转换函数

TO_CHAR(列|日期|数字，转换格式)：日期转换成字符串年(yyyy)月(mm)日(dd)时(hh24)分(mi)秒(ss);
SELECT TO_CHAR(SYSDATE, 'yyyy mm dd') FROM DUAL;
TO_CHAR数字转换成字符串任意一位数字(9)、货币(L 本地货币)
SELECT TO_CHAR(1234567876,'999,999,999,999') FROM DUAL;
TO_DATE(字符串,‘时间格式’);字符串转换为日期格式
将’2018-07-04’日期类型的属性插入到EMP表

INSERT INTO EMP

 (EMPNO, ENAME, HIREDATE, SAL)

VALUES

 (8888, '李四', TO_DATE('2018-07-04', 'yyyy-mm-dd'), 3000);

TO_NUMBER(列|数值);
可以将字符串中全是数字的类型转换为数字类型，oracle中如果是全是数字的字符串类型可以直接加减乘除，系统会默认转换成CHAR类型。如果该字符串中不仅仅只有数字的话该TO_NUMBER 类型没有任何作用。

其他函数

nvl(表达式1，表达式2)：如果表达式1非空，返回表达式1，否则返回表达式2。
select nvl(e.comm,0),sal + nvl(e.comm,0) from emp e
nvl2(表达式，表达式1，表达式2)：如果表达式非空，返回表达式1，否则返回表达式2
select nvl2(comm,sal + comm,sal) * 12 年薪 from emp;
decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)：
select decode(4-2,1,'结果是1',2,'结果是2') from dual;

将职位替换为中文：

select ename 姓名,

      decode(job,

             'CLERK','业务员',

             'SALESMAN','销售人员',

             'MANAGER','经理',

             'ANALYST','分析员',

             'PRESIDENT','总裁') 职位,

      sal 基本工资

 from emp;

码农公寓