Oracle学习笔记（一）

我的Oracle用户及密码：
- sys：change_on_install
- system：manager
- Scott：tiger
- sh：sh

连接到数据库命令：sqlplus scott/tiger
连接成功后，切换用户:conn sys/change_on_install AS SYSDBA。其他用户不需要AS SYSDBA。

格式化命令：
- 设置每行的长度：SET LINESIZE 300
- 设置每页的长度：SET PAGESIZE 30

调用本机命令：如Host echo hello

sql简介

SQL指的是结构化查询语言。
- DML(数据操作语言)：主要指的是数据库的查询与更新操作。
- DDL(数据定义语言)：主要指的是数据对象的创建(表、用户)
- DCL(数据控制语言):主要是进行权限的管理操作

SCOTT用户表的结构

打开SQL PLUS ，登录到SCOTT用户。
查询一个用户下的所有表：select * from tab;
查询到SCOTT用户有四张表：BONUS、DEPT、EMP、SALGRADE。
下面要查询每张表的结构：
DESC dept;

No. 列名 类型 描述 1 DEPTNO NUMBER(2) 表示部门编号，最多由2位数字组成 2 DNAME VARCHAR2(14) 表示部门名称，最多由14个字符组成（三个字符表示一个中文） 3 LOC VARCHAR2(13) 部门位置

DESC EMP;

No. 列名 类型 描述 1 EMPNO NUMBER(4) 表示雇员编号，最多由2位数字组成 2 ENAME VARCHAR2(10) 表示雇员姓名 3 JOB VARCHAR2(9) 雇员职位 4 MGR NUMBER(4) 每个雇员的领导编号 5 HIREDATE DATE 雇佣日期，包含日期和时间 6 SAL NUMBER(7,2) 工资，小数点最多占两位，整数最多占5位 7 COMM NUMBER(7,2) 佣金，类似销售提成 8 DEPTNO NUMBER(2) 部门编号，指的是每个雇员对应的部门编号信息

DESC salgrade;

No. 列名 类型 描述 1 GRADE NUMBER 表示工资等级编号 2 LOSAL NUMBER 此等级的最低工资 3 HISAL NUMBER 此等级的最高工资

DESC bonus;

No. 列名 类型 描述 1 ENAME VARCHAR2(10) 雇员姓名 2 JOB VARCHAR2(9) 职位 3 SAL NUMBER 工资 4 COMM NUMBER 佣金

bonus表中没有数据。

SQL简单查询

查询emp表的全部记录

select * from emp;

查询每个雇员的编号、姓名、职位、工资

select empno,ename,job,sal from emp;

查询所有的职位信息，使用DISTINCE不会出现重复列。但是如果查询的是多个列，那么只有在多个列数据都相同时，才能消除。

select  DISTINCT job from emp;

查询每个雇员的编号、姓名、基本年薪。

select empno,ename,sal*12 income from emp;

查询每个雇员的编号、姓名、基本年薪(每年十五个月工资，每个月200元餐补、100元交通补贴、100元的电话补贴，每年5个月高温补贴200元)。

select empno,ename,（sal*15+（200+100+100）*12 + 200*5） incomefrom emp;

直接查询常量

select '雇员',empno,ename from emp;

查询效果：雇员编号：XXXX，姓名：XXX，收入：XXX。“||”负责进行输出内容的连接

select '雇员编号:'||empno,'姓名:'||ename,'收入:'||sal from emp;

SQL限定查询

查询基本公司高于1500的雇员

select *from empwhere sal>1500;

查询smith的完整信息，注意Oracle中的数据区分大小写

select *from empwhere ename='SMITH';

查询工资是5000的雇员信息

select *from empwhere sal=5000;

查询不是销售人员的雇员编号、姓名、职位。”!= 或者 <>”

select empno,ename,jobfrom empwhere job!='SALESMAN';

查询工资范围在1500到3000的雇员

select *from empwhere sal>=1500 and sal<=3000;

查询工资大于2000或者是办事员的雇员

select *from empwhere sal>2000 or job='CLERK';

查询所有工资小于2000。用NOT实现

select *from empwhere NOT sal>=2000;

查询工资范围在1500到3000的雇员，性能比AND高

select *from empwhere sal between 1500 and 3000;

查询所有在1981年雇佣的雇员

select *from empwhere hiredate between '01-1月 -1981' and '31-12月 -1981';

查询所有领取佣金的雇员

select *from empwhere comm is not null;select *from empwhere not comm is null;

查询雇员编号为7369、7566、7788、9999的雇员信息

select *from empwhere empno = 7369 or empno = 7566 or empno = 7788 or empno = 9999;

select *from empwhere empno in(7369,7566,7788,9999);

查询雇员编号不为7369、7566、7788、9999的雇员信息

select *from empwhere empno not in(7369,7566,7788,9999);

select *from empwhere not empno in(7369,7566,7788,9999);

注意：not in操作中不能有null值

select *from empwhere empno not in(7369,7566,7788,null);

结果：未选定行。
使用WHERE最大的用处在于控制显示的数据行，就是别显示全部数据行。使用 NOT IN 的目的是为了查询部分数据行，如果有了null，就变成了查询全部不为空的数据，而某些数据如主键，永远不能为空，就变成了查询全部数据。在大型数据库中，就宕机了。

模糊查询，在使用Like时，可以使用通配符：
- “_”，匹配任意的一位字符；
- “%”，匹配任意位字符；
查询姓名是以字母A开头的雇员信息

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

查询姓名是以第二个字母位A开头的雇员信息

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

查询姓名是包含A字母的雇员信息

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

查询排序

查询所有雇员信息，按照工资由高到低排序。

select * from emp order by sal desc;

查询所有销售人员信息，按照雇佣日期由早到晚排序。

select * from emp where job='SALESMAN' order by hiredate asc;

查询所有雇员信息，按照工资由高到低排序,如果相同，则按照雇佣日期由早到晚排序。

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

查询每个雇员的编号、姓名、年薪，按照年薪由低到高排序。

select empno,ename,sal*12 income from emp order by income asc;

排序查询总结

1. 使用 OREDER BY 查询的时候，排序是在最后完成的；
2. ORDER BY 子句是最后执行的子句；
3. 在ORDER BY 之中可以设置多个排序的字段；
4. ORDER BY 有两种模式：ASC、DESC；
5. ORDER BY 是唯一一个可以使用SELECT子句定义别名的子句。

练习题

选择部门30中所有员工。

select * from emp where deptno=30;

列出所有办事员(CLERK)姓名、编号和部门编号。

select ename,empno,deptno from emp where job='CLERK';

列出佣金高于薪资60%的员工。

select * from emp where comm > sal*0.6;

找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)。

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

找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)，以及既不是经理也不是办事员但是工资大于等于2000的所有员工的详细资料。

select * from empwhere (job='MANAGER' and deptno=10) or (job='CLERK' and deptno=20) or (job not in ('MANAGER','CLERK') and sal>=2000 );

找出 收取佣金的员工的不同工作

select distinct job from emp where comm is not null;

找出不收取佣金或收取佣金低于100的员工。

select * from emp where comm is null or comm <100;

显示不带有“R”的员工的姓名

select ename from emp where not ename like '%R%';select ename from emp where ename not like '%R%';

显示带有“A”的员工的姓名，显示的结果按照基本工资有高到低排序，如果基本工资相同，则按照雇佣年限由早到晚顺序，如果雇佣日期相同，则按照职位排序。
“`sql
select *
from emp
where ename like ‘%A%’
order by sal desc,hiredate asc,job;