ORACLE数据库

DQL 数据库查询语句
select 查询所要显示的字段/列
from 表
where 过滤条件
条件表达式： 对比x和y的关系，返回一个布尔值（真,假,空）
多个条件使用and/or进行连接
not 输出不满足条件的数据
between … and … 在x和y之间
语句中字段对应多个值，使用in
模糊查询 like 通匹符_匹配1个字符，%匹配0或多个字符
where后面不允许使用分组函数
group by 分组查询
分组函数 sum()求和 max()最大值 min()最小值 avg()平均值 count()计数
分组函数不能与字段一起查询，分组函数只能和分组字段一起查询
having 分组过滤
having后面可以使用分组函数，但是不能使用非分组的字段
order by 排序输出
asc升序，desc降序
DQL 扩展部分
别名
select a from b 从b表中查询a字段
select a b from c 从c表中查询a字段，给a字段取别名为b
select a b from b c 给b表取别名为c，从c表中查询a字段，给a字段取别名为b
select a.b from c 从c表中查询a表下的b字段(错误)
select a,b from c 从c表查询a字段和b字段
select a.b,a.c from a 从a表查询a表下的b字段和a表下的c字段
select a.b,a.c from d.a 从d用户的a表中查询a表下的b字段和a表下字段
select a.b,a.c from d a 给d表取别名为a，从a表中查询a表下的b字段和c字段
select a.b c,a.d e from f.g a 给f用户的g表取别名为a，从a表中查询b字段并取别名为c，查询d字段并取别名为e
子查询
语句中嵌套查询语句；
函数
avg(),sum(),max(),min(),count()分组函数
to_char(a,‘yyyy-mm-dd 24hh:mi:ss’) 把日期类型的a，按照指定格式转化为字符串
to_date(a,‘格式’) 把字符串类型的a，按照指定格式转化为日期
sysdate 返回当前系统日期+时间
nvl(a,b) 如果a为null则输出b的值，如果a不为null则输出a的值
decode(a,b,c,d) 如果a的值等于b则输出c，否则输出d
sign(a) a为正返回1，a为负返回-1，a=0返回0
rownum 根据查询结果输出的顺序从1开始编号
多表查询
select
from 多张表用逗号隔开
where n张表至少需要n-1个关联关系
关联查询
一般用得较少，但是面试容易问到
select xxx from a (left/right/inner/full outer) join b on a.x=b.x
–左连接查询，查询左边表的所有的数据，和右边表满足关系的数据
select a.,b. from emp a left join dept b on a.deptno=b.deptno;
–右连接查询，查询右边表的所有的数据，和左边表满足关系的数据
select a.,b. from emp a right join dept b on a.deptno=b.deptno;
–全外连接查询，查询两边表的所有数据
select a.,b. from emp a full outer join dept b on a.deptno=b.deptno;
–内连接查询，只显示满足关系数据
select a.,b. from emp a inner join dept b on a.deptno=b.deptno;
--------------------------------------------------------------------掌握
DML 数据库操作语句
事务
所有的DML操作，都会有临时事务，必须要commit之后，服务器才会修改。
原子性：在一个会话窗口，所有的增/删/改操作，被视为一事务。
永久性：事务一旦提交，无法撤销。
隔离性：事务只在当前窗口可以查看，只有提交事务之后其它窗口才能查看。
一致性：事务结束后， 数据保持一致。
多个会话对同一张表数据进行操作，会产生冲突，所以事务一定要及时提交
新增数据
insert into 表名 values(值1,值2… …值n);
修改数据
update 表名 set 字段=‘新值’,字段=‘新值’ where 过滤条件
修改语句不接where，就会修改整张表的数据，后果非常严重！！！
删除数据
delete from 表名 where 过滤条件
手动修改数据
select rowid,表.* from 表
--------------------------------------------------------------------了解
DDL 数据库定义语句
新建表
create table 表名(
字段1 类型 [约束],
字段2 类型 [约束],
… …
字段3 类型 [约束]
);
表约束
限制表数据库必须遵循一定规则进行存储
·主键约束
规定字段的值不允许为Null，并且不允许重复
primary key
·非空约束
规定字段的值不允许为null
not null
·唯一约束
规定字段的值不允许重复
unique
·检查约束
规定字段的值必须满足指定的条件
check(sex in(‘男’,‘女’))
·默认值约束
规定字段的值如果不写，则取默认的值
default 值
·外键约束
规定字段的值只能从主表的关联字段中取值
foeign key(字段) references 主表(字段)
删除表
drop table 表名;
修改表
alter table 表名 add(字段 类型 [约束]);
alter table 表名 modify(字段 类型 [约束]);
alter table 表名 drop 字段;
alter table 表名 rename to 新表名;
alter table 表名 rename column 字段 to 新字段名;
对表结构进行操作的语句由开发进行编写，但是开发不一定会有测试环境的修改权限，
因此测试工程师也有可能会承担脚本的执行工作

DCL
grant 权限 on 表/视图 to 用户;
create user xxx identified by"密码";
数据库系统中的常见对象
数据库 Database
可以理解为一个图书馆
表格 Table
可以理解解为书架
字段 Column
表格中的一列，就是数据一个属性
行 Row
表格中的一行就是一条数据
数据类型
char(5) 固定长度字符串
对数据存储没有长度运算，存储和查询速度快，对磁盘容量占用较多
适用于短字符串
varchar2(5) 可变长度字符串
对数据存储有长度运算，存储和查询速度慢，节约磁盘空间
适用于长字符串
number(x,y) 最大长度x位，保留到小数点后y位
date
账号
用于登录Oracle数据库的用户
新建账号 create user realme identified by “123456”;
视图 view
保护表数据
同义词 synonym
把scott.emp_realme使用单词emp替代
scott账号下的代码，可以直接让realme用户执行
索引 index
索引能让查询更加快速

例如：你从字典中查一下“钱”的意义
1、从第一页开始翻起，一直翻到钱，不走索引，慢
2、先找到索引Q,再一页一页往下翻，走索引，快

案例
案例一 创建账号并登录到orcl数据库
1、使用账号sys/123456 as sysdba登录到系统
2、创建账号create user realme identified by “123456”;
3、授权 grant create session to realme;
4、使用realme/123456登录
案例二 使用realme用户，查询scott用户中的表emp
1、使用账号sys/123456 as sysdba登录到系统
2、给realme用户授予scott.emp表的查询权限
grant select on scott.emp to realme;
3、使用realme登录
select * from scott.emp;
案例三 只想给realme用户查看empno,ename,job,deptno，其它字段不想给他看
1、使用账号sys/123456 as sysdba登录到系统
2、给scott用户授予创建视图的权限
grant create any view to scott;
3、使用账号scott登录，创建视图
create view emp_realme as select empno,ename,job,deptno from emp;
4、把视图的查看权限授予realme
grant select on emp_realme to realme;
5、使用账号realme登录，查看视图
select * from scott.emp_realme;
案例四 scott提供的查询语句select * from emp，realme执行不成功
1、使用账号sys/123456 as sysdba登录到系统
2、给realme用户授予创建同义词的权限
grant create any synonym to realme;
3、使用realme用户登录，创建同义词
create synonym emp for scott.emp_realme;
4、执行select * from emp;
案例五 对比走索引的查询和不走索引的查询
1、使用账号sys/123456 as sysdba登录到系统
2、给scott账号授予创建索引的权限
grant create any index to scott;
3、使用scott用户登录，给emp表的job列添加索引
create index emp_job on emp(job);
4、编写语句
select * from emp where job=‘MANAGER’;
select * from emp where ename=‘SMITH’;
5、选中语句，按F5查看资源消耗

Oracle与MySQL的区别
1.本质区别：Oracle收费，MySQL免费开源
2.数据库安全性：Oracle使用了许多安全功能，如用户名、密码、配置文件、本地身份验证、外部身份验证、高级安全增强功能等；MySQL使用三个参数来验证用户，即用户名、密码和位置
3.sql语法的区别：Oracle的sql语法与MySQL有很大不同，Oracle为称为PL/SQL的编程语言提供了更大的灵活性。Oracle的SQL Plus工具提供了比MySQL更多的命令，用于生成报表输出和变量定义
4.存储上的区别：与Oracle相比，MySQL没有表空间、角色管理、快照、同义词和包，以及自动存储管理
5.对象名称的区别：Oracle对所有对象名称都不区分大小写，而某些MySQL对象名称（如数据库和表）区分大小写（取决于底层操作系统）
6.运行程序和外部程序支持：Oracle数据库支持从数据库内部编写，编译和执行的几种编程语言。此外，为了传输数据，Oracle数据库使用XML。而MySQL不支持在系统内执行其他语言，也不支持XML
7.MySQL和Oracle的字符数据类型比较：两个数据库中支持的字符类型存在一些差异。对于字符类型，MySQL具有CHAR和VARCHAR，最大长度允许为65,535字节（CHAR最多可以为255字节，VARCHAR为65.535字节）。
而，Oracle支持四种字符类型，即CHAR，NCHAR，VARCHAR2和NVARCHAR2; 所有四种字符类型都需要至少1个字节长; CHAR和NCHAR最大可以是2000个字节，NVARCHAR2和VARCHAR2的最大限制是4000个字节。可能会在最新版本中进行扩展。
8.MySQL和Oracle的额外功能比较：MySQL数据库不支持其服务器上的任何功能，如Audit Vault。另一方面，Oracle支持其数据库服务器上的几个扩展和程序，例如Active Data Guard，Audit Vault，Partitioning和Data Mining等。
9.临时表的区别：Oracle和MySQL以不同方式处理临时表。
在MySQL中，临时表是仅对当前用户会话可见的数据库对象，并且一旦会话结束，这些表将自动删除。
Oracle中临时表的定义与MySQL略有不同，因为临时表一旦创建就会存在，直到它们被显式删除，并且对具有适当权限的所有会话都可见。但是，临时表中的数据仅对将数据插入表中的用户会话可见，并且数据可能在事务或用户会话期间持续存在。
10.MySQL和Oracle中的备份类型：Oracle提供不同类型的备份工具，如冷备份，热备份，导出，导入，数据泵。Oracle提供了最流行的称为Recovery Manager（RMAN）的备份实用程序。使用RMAN，我们可以使用极少的命令或存储脚本自动化我们的备份调度和恢复数据库。
MySQL有mysqldump和mysqlhotcopy备份工具。在MySQL中没有像RMAN这样的实用程序。
11.Oracle和MySQL的数据库管理：在数据库管理部分，Oracle DBA比MySQL DBA更有收益。与MySQL相比，Oracle DBA有很多可用的范围。
12.数据库的认证：MySQL认证比Oracle认证更容易。
与Oracle（设置为使用数据库身份验证时）和大多数仅使用用户名和密码对用户进行身份验证的其他数据库不同，MySQL在对用户进行身份验证location时会使用其他参数。此location参数通常是主机名，IP地址或通配符。
使用此附加参数，MySQL可以进一步将用户对数据库的访问限制为域中的特定主机或主机。此外，这还允许根据进行连接的主机为用户强制实施不同的密码和权限集。因此，从abc.com登录的用户scott可能与从xyz.com登录的用户scott相同或不同。

Oracle安装与服务启动相关（实际工作中不可能如此简单，仅作参考使用）：
虚拟机的准备:
(1)复制一个全新的win2003_qc92的压缩文件,解压到非中文的目录
(2)用vmware软件打开第一步中文件
(3)编辑虚拟机设置,将硬盘扩展到30g,然后打开共享文件目录,设置c盘为共享目录.
勾选"在windows客户机映射为网络驱动器"复选框.
(4)安装虚拟机的工具,安装完成后重启虚拟机
(5)进入虚拟机的桌面,计算机图标-右键-管理,找到磁盘管理,将刚才扩展的20g的硬盘进行分配磁盘空间,并格式化该硬盘.

oracle的安装:
(1)进入虚拟机,打开我的电脑-网络驱动器,这个网络驱动器指向的是主机的c盘
找到oracle11的安装文件
(2)按照oracle的安装文档进行安装
(3)最后需要解锁scott用户.
(4)安装pl/sql客户端软件.

oracle的服务:Win键+R --> services.msc -->回车
检查OracleDBConsoleorcl Oracle
数据库的管理控制台，提供一个Web系统用于可视化操作Oracle数据库，如创建表空间，创建表，创建用户，授权等。其命名规则为OracleDBConsole+数据库名称。
OracleOraDb11g_home1TNSListener
Oracle服务器端口侦听服务，用于侦听1521端口（Oracle默认端口，也可使用Net Configuration Assistant程序配置侦听其它端口），如果该服务不能正常启动，即使Oracle数据库正常启动，也无法提供服务给其它客户端。
OracleServiceORCL
Oracle的数据库服务，当创建好一个数据库后，系统中就会注册一个新的服务，该服务处于启动状态表明其对应的数据库已经启动，可以提供各类服务。其命名规则为OracleService+数据库名称。

查询案例1
–查询所有信息
select * from emp;
–1、找出奖金高于薪水60%的员工信息。
select * from emp where comm>0.6;
–2、找出部门10中所有经理（MANAGER）和部门20中所有办事员（CLERK）的详细资料。
select * from emp where deptno=10 and job=‘MANAGER’ or deptno=20 and job=‘CLERK’;
–3、统计各部门的薪水总和。
select deptno,sum(sal) from emp group by deptno;
–4、找出部门10中所有经理理（MANAGER），和部门20中所有办事员（CLERK），和既不是经理又不是办事员但其薪水大于或等2000的所有员工的详细资料。
select * from emp where deptno=10 and job=‘MANAGER’ or deptno=20 and job=‘CLERK’ or JOB not in(‘MANAGER’,‘CLERK’) and sal>=2000;
–5、列出各种工作的最低工资。
select job,min(sal) from emp group by job;
–6、列出各个部门的MANAGER（经理）的最低薪水。
select deptno,min(sal),job from emp where job=‘MANAGER’ group by deptno,job;
–7、找出收取奖金的员工的不同工作。
select distinct job from emp where comm is not null;
select * from emp where comm is not null;
–8、找出不收取奖金或收取的奖金小于等于300的员工信息。
select * from emp where comm<=300 or comm is null;
–9、找出在1981年入职的所有员工。
select * from emp where to_char(HIREDATE,‘YYYY’)=1981;
–10、查询不在’NEW YORK’工作，各种岗位员工里名字不带有C的人数。
select job,count() from emp where ename not like ‘%C%’ and deptno!=(select deptno from dept where loc=‘NEW YORK’) group by job;
select job,count() from emp where ename not like ‘%C%’ and deptno in(select deptno from dept where not loc=‘NEW YORK’) group by job;
–11、显示正好为5个字符的员工的姓名。
select ename from emp where ename like ‘’;
–12、显示不带有“R”的员工姓名。
select ename from emp where ename not like ‘%R%’;
–13、查询Smith、Allen、ward、Martin、Turner这些人中，哪位的岗位不是salesman；
select * from emp where ename in(‘SMITH’,‘ALLEN’,‘WARD’,‘MARTIN’,‘TUENER’) and job!=‘SALESMAN’;
–14、查询工资和公司平均薪资，差距在500之内的员工信息；
select * from emp where sal-(select avg(sal) from emp) between -500 and 500;
–15、查询A开头的员工人数；
select count() from emp where ename like ‘A%’;
–16、查询名字中不包含‘O’，并且岗位平均工资小于2000的岗位、平均薪资、最低薪资，按最低薪资升序排列；
select job,avg(sal),min(sal) from emp where ename not like ‘%O%’ having avg(sal)<2000 group by job order by min(sal) desc;
–17、查询在’CHICAGO’工作的员工中，哪些岗位人数大于1人
select job,count() from emp where deptno=(select deptno from dept where loc=‘CHICAGO’) having count(*)>1 group by job;
–18、查询工资在1000-3000的员工中，各部门平均薪资大于2000的部门编号、平均薪资、最大薪资，按最小薪资降序排列;
select deptno,avg(sal),max(sal) from emp where sal between 1000 and 3000 having avg(sal)>2000 group by deptno order by min(sal) desc;
–19、查询工资和奖金差距在1000以内的员工
select * from emp where sal-comm between -1000 and 1000 and comm is not null;
–20、查询工号比领导工号大50以上的员工
select * from emp where empno-mgr>50 ;
–21、查询10部门名字中有A的员工和20部门不是’MANAGER’的员工和30部门工资在1000-2000的员工
select * from emp where deptno=10 and ename like ‘%A%’ or deptno=20 and job!=‘MANAGER’ or deptno=30 and sal between 1000 and 2000;
–22、查询名字由5个字组成并且不包含’E’的员工
select * from emp where ename like '’ and ename not like ‘%E%’;
–23、查询BLAKE，CLARK，SCOTT，KING，TURNER，ADAMS，哪些人不是10部门的
select * from emp where ename in (‘BLAKE’,‘CLAKE’,‘SCOTT’,‘KING’,‘TURNER’,‘ADAMS’) and deptno!=10;
–24、查询每个部门工号比领导工号大的员工人数
select deptno,count() from emp where empno>mgr group by deptno;
–25、查询哪些年入职人数超过2人，按年降序排列
select to_char(hiredate,‘YYYY’),count() from emp having count()>2 group by to_char(hiredate,‘YYYY’) order by to_char(hiredate,‘YYYY’) desc;
–26、查询除掉工作为’CLERK’的员工和名字由6个字组成的员工后，部门人数小于等于3人的部门。
select deptno,count() from emp where job!=‘CLERK’ and ename like ‘%_____%’ having count(*)<=3 group by deptno;
–27、查询名字是Smith、Allen、ward、Martin、Turner和工号是7654，7782，7839，7521，7902，7756的员工中，哪些人的名字里倒数第二个字符是’E’
select ename from emp where (ename in(‘SMITH’,‘ALLEN’,‘WARD’,‘MARTIN’,‘TURNER’) or empno in(7654,7782,7839,7521,7902,7756)) and ename like '%E’;
–28、查询工号为7839的员工所在的部门，各岗位的平均薪资
select job,avg(sal) from emp where deptno=(select deptno from emp where empno=7839) group by job;
–29、查询在’NEW YORK’或者‘DALLAS’工作的员工里，平均薪资大于2000的岗位
select job,avg(sal) from emp where deptno in (select deptno from dept where loc=‘NEW YORK’ or loc=‘DALLAS’) having avg(sal)>2000 group by job;
select job,avg(sal) from emp,dept where emp.deptno=dept.deptno and (loc=‘NEW YORK’ OR loc=‘DALLAS’) group by job having avg(sal)>2000;
–30、查询各部门没有奖金的员工人数，按人数升序排列。
select deptno,count() from emp where comm is null or comm=0 group by deptno order by count() asc;
–31、查询工资比他的部门平均薪资高的员工信息
select * from emp t where sal>(select avg(sal) from emp where deptno=t.deptno);
–32、查询公司谁的员工姓名有重复（不区分大小写）
select lower(ename),count() from emp having count()>1 group by lower(ename);
–33、查询SMITH的领导姓名
select ename from emp where empno=(select mgr from emp where ename=‘SMITH’);
–34、查询工号在7000-7700之间的员工，哪些人名字中没有‘A’
select ename,empno from emp where (empno between 7000 and 7700) and ename not like ‘%A%’;

查询案例2
–案例说明
写出下面查询SQL语句
学生表（学生id，姓名，性别，分数）student
s_id name sex score
1 小明 女 80
2 小强 男 90
3 小刚 男 75
4 亮亮 男 100
5 小华 女 35
6 莉莉 女 84
7 周润发 男 100
班级表（班级id，班级名称） class
c_id c_name
1001 一班
1002 二班
1003 三班
学生班级表（学生id，班级id）student_class
s_id c_id
1 1001
2 1001
3 1001
4 1002
5 1002
6 1002
7 1003
1、查询"一班"得分在80分以上的学生姓名
2、查询"二班"的女生人数
3、查询"一班"和"二班"的学生分数在40-90之间的学生信息
选做4、查询全校考试成绩排名2-4的学生信息
选做5、查询每个班级的学生人数
选做6、查询哪个班级没有不及格的学生

–建立表环境
create table student(
s_id char(1) primary key,
name varchar2(6) not null,
sex char(2) check(sex in(‘男’，‘女’)),
score number(3) not null
);

insert into student values(1,‘小明’,‘女’,80);
insert into student values(2,‘小强’,‘男’,90);
insert into student values(3,‘小刚’,‘男’,75);
insert into student values(4,‘亮亮’,‘男’,100);
insert into student values(5,‘小华’,‘女’,35);
insert into student values(6,‘莉莉’,‘女’,84）;
insert into student values(7,‘周润发’,‘男’,100);

create table class(
c_id char(4) primary key,
c_name char(4) not null
);

insert into class values(1001,‘一班’);
insert into class values(1002,‘二班’);
insert into class values(1003,‘三班’);

create table student_class(
s_id char(1) not null,
c_id char(4) not null
);

insert into student_class values(1,1001);
insert into student_class values(2,1001);
insert into student_class values(3,1001);
insert into student_class values(4,1002);
insert into student_class values(5,1002);
insert into student_class values(6,1002);
insert into student_class values(7,1003);

select * from student;
select * from class;
select * from student_class;

–案例解析
–1、查询"一班"得分在80分以上的学生姓名
select name
from student
where score >= 80
and s_id in
(select s_id
from student_class
where c_id in (select c_id from class where c_name = ‘一班’));

select s.name
from student s,class c,student_class sc
where s.s_id=sc.s_id and c.c_id=sc.c_id
and s.score>=80 and c.c_name=‘一班’

–2、查询"二班"的女生人数
select count(*)
from student
where sex=‘女’
and s_id in
(select s_id
from student_class
where c_id in (select c_id from class where c_name = ‘二班’));

select count(s.*)
from student s,class c,student_class sc
where s.s_id=sc.s_id and c.c_id=sc.c_id
and c.c_name=‘二班’ and s.sex=‘女’

–3、查询"一班"和"二班"的学生分数在40-90之间的学生信息
select *
from student
where score between 40 and 90
and s_id in
(select s_id
from student_class
where c_id in (select c_id from class where c_name in(‘一班’, ‘二班’)));

–选做4、查询全校考试成绩排名2-4的学生信息
select * from (select rownum r,t.* from(select student.* from student order by score desc) t)
where r between 2 and 4;
select * from（select t.,rank()over(order by t.score desc) r from student t) where r between 2 and 4;
select * from（select t.,dense_rank()over(order by t.score desc) r from student t)where r between 2 and 4;

–选做5、查询每个班级的学生人数
select c.c_name,count(s.s_id)
from student s,class c,student_class sc
where s.s_id=sc.s_id and c.c_id=sc.c_id
group by c.c_name;

–选做6、查询哪个班级没有不及格的学生
select c.c_name
from student s,class c,student_class sc
where s.s_id=sc.s_id and c.c_id=sc.c_id
group by c.c_name
having min(s.score)>=60

select * from class where c_id not in
(select c.c_id
from student s,class c,student_class sc
where s.s_id=sc.s_id and c.c_id=sc.c_id
and s.score<60);

案例查询3（嵌套查询使用）
–案例说明
S（SNO,SNAME）学生关系。
sno学号 sname姓名
1 小明
2 小强
3 小红
4 小冬
C（CNO,CNAME,CTEACHER）课程关系。
cno课程号 cname课程名 cteacher任课教师
1 语文 李白
2 数学 体育老师
3 英语 李明
SC（SNO,CNO,SCGRADE）选课关系。
SNO CNO scgrade成绩
1 2 98
1 3 9
2 1 70
2 2 68
3 1 89
3 2 59
4 1 59
4 2 58
4 3 98
1、找出没有选修过“李明”老师讲授课程的所有学生姓名
2、计算所有同学的各科成绩之和，并按照总成绩进行降序排序
3、列出有二门以上（含两门）不及格(<60)课程的学生姓名及其平均成绩
4、列出既学过“1”号课程，又学过“2”课程的所有学生姓名
5、列出“1”号课成绩比“2”号课成绩高的所有学生的学号

–创建表数据
create table students(
sno number(1) primary key,
sname varchar(6)
);
insert into students values(1,‘小明’);
insert into students values(2,‘小强’);
insert into students values(3,‘小红’);
insert into students values(4,‘小冬’);

create table course(
cno number(1) primary key,
cname char(4) not null,
cteacher varchar(8)
);
insert into course values(1,‘语文’,‘李白’);
insert into course values(2,‘数学’,‘体育老师’);
insert into course values(3,‘英语’,‘李明’);

create table students_course(
sno number(1),
cno number(1),
scgrade number(2),
foreign key(sno) references students(sno),
foreign key(cno) references course(cno)
);
insert into students_course values(1,2,98);
insert into students_course values(1,3,9);
insert into students_course values(2,1,70);
insert into students_course values(2,2,68);
insert into students_course values(3,1,89);
insert into students_course values(3,2,59);
insert into students_course values(4,1,59);
insert into students_course values(4,2,58);
insert into students_course values(4,3,98);

–案例解析
–1、找出没有选修过“李明”老师讲授课程的所有学生姓名
select sname from students where sno not in (select sno from students_course where cno in (select cno from course where cteacher=‘李明’));

select s.sname from students s where s.sno not in (select sc.sno from course c,students_course sc where c.cno=sc.cno and c.cteacher=‘李明’);

–2、计算所有同学的各科成绩之和，并按照总成绩进行降序排序
select sum(scgrade) from students_course group by sno order by sum(scgrade) desc;

select s.sno,s.sname,sum(sc.scgrade)
from students s,students_course sc
where s.sno=sc.sno
group by s.sno,s.sname
order by sum(sc.scgrade) desc;

–3、列出有二门以上（含两门）不及格(<60)课程的学生姓名及其平均成绩
select s.sno,s.sname,avg(sc.scgrade)
from students s,students_course sc
where s.sno=sc.sno
and s.sno in (select sno from students_course where scgrade<60 group by sno having count(*)>=2)
group by s.sno,s.sname

–4、列出既学过“1”号课程，又学过“2”课程的所有学生姓名
select sname from students where sno in (select sno from students_course where sno in (select sno from students_course where cno=1) and cno=2);
select s.sname
from students s,students_course sc
where s.sno=sc.sno
and sc.cno=1 and sc.sno in (select sno from students_course where cno=2);
–5、列出“1”号课成绩比“2”号课成绩高的所有学生的学号
select a.sno
from students_course a,students_course b
where a.sno=b.sno
and a.cno=1 and b.cno=2
and a.scgrade>b.scgrade;

–嵌套查询方法
–1、查询不在’NEW YORK’工作，各种岗位员工里名字不带有C的人数;
select count() from emp where deptno in (select deptno from dept where loc!=‘NEW YORK’) and ename not like ‘%C%’ group by job;
–2、查询工资和公司平均薪资，差距在500之内的员工信息;
select * from emp where sal-(select avg(sal) from emp) between -500 and 500;
–3、查询在’CHICAGO’工作的员工中，哪些岗位人数大于1人;
select job,count() from emp where deptno in (select deptno from dept where loc=‘CHICAGO’) group by job having count()>1;
–4、查询工号为7839的员工所在的部门，各岗位的平均薪资
select job,avg(sal) from emp where deptno in (select deptno from emp where empno=7839) group by job;
–5、查询在’NEW YORK’或者‘DALLAS’工作的员工里，平均薪资大于2000的岗位
select job,avg(sal) from emp where deptno in (select deptno from dept where loc in (‘NEW YORK’,‘DALLAS’)) group by job having avg(sal)>2000;
–6、查询工资比他的部门平均薪资高的员工信息
select emp.
from emp,(select deptno,avg(sal) asal from emp group by deptno) a
where emp.deptno=a.deptno
and emp.sal>a.asal;
–7、显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，并将最早年份的项目排在最前面。
select ename,to_char(hiredate,‘YYYY’),to_char(hiredate,‘MM’)
from emp
group by to_char(hiredate,‘YYYY’),to_char(hiredate,‘MM’),ename
order by to_char(hiredate,‘YYYY’),to_char(hiredate,‘MM’);
–8、显示在一个月为30天的情况下所有雇员的日薪金，取整。
select ename,trunc(sal/30) from emp;
–9、找出在（任何年份的）2月受聘的所有雇员。
select * from emp where to_char(hiredate,‘MM’)=‘02’ or to_char(hiredate,‘MM’)=‘2’;
–10、对于每个雇员，显示其加入公司的天数。
select ename,to_date(‘2018-12-24’,‘YYYY-MM-DD’)-hiredate from emp;
–11、找出所有的普通员工.
select * from emp where empno not in (select nvl(mgr,0) from emp);
–12、求部门中哪些人的薪水最高.
select emp.*
from emp,(select deptno,max(sal) msal from emp group by deptno) a
where emp.deptno=a.deptno
and emp.sal=a.msal;
–13、雇员中哪些人是领导.
select * from emp where empno in (select nvl(mgr,0) from emp);
–14、不准用组函数，求薪水的最高值
select rownum,a.* from (select * from emp order by sal desc) a where rownum=1;
–15、求平均薪水最高的部门的部门编号
select b.n from (select deptno n,avg(sal) from emp group by deptno order by avg(sal) desc) b where rownum=1;
–16、求平均薪水最高的部门的部门名字
select dname from dept where deptno in (select b.n from (select deptno n,avg(sal) from emp group by deptno order by avg(sal) desc) b where rownum=1);
–17、求比普通员工的最高薪水还要高的领导名称
select ename from emp where sal>(select max(sal) from emp where empno not in (select nvl(mgr,0) from emp)) and empno in (select nvl(mgr,0) from emp);