【数据库】

部分架构图

练习：

--注释
/*
select查询语句
select 要查询的数据 from 数据来源

*:匹配一条数据的所有字段值

查询一张表中所有数据 select * from 表名；
*/
select * from emp;

select ename from emp;
--查询顺序代表字段顺序
select distinct job, ename,sal from emp;

select ename,sal*12+comm,job from emp;

select ename 员工姓名,sal as 工资,job as "JOB" from emp;
--将ename与a-->拼接 as别名为：员工姓名
select ename ||'a'||'-->' as 员工姓名 from emp;
--创建伪列（整数、字符串、表达式）
select 'my' from emp
--select*from dual虚表，用于计算表达式，显示单条记录的值
select 1+1 from dual
select 1+null from dual
--nvl 判断是否为null，如果为空，默认为0（第二个参数），不为null，则判定字段实际值
select ename,sal*12+nvl(comm,0) as "年薪"from emp;
--where 条件判断，先过滤再查询/
select ename,sal*12  from emp where sal*12>20000;
--先查询再过滤
select *from(select ename,sal*12 yearsal from emp) where yearsal>20000;
--查询不在20部门工作的员工
select * from emp where deptno <>20;
--查询工资比900，1000，1100都高的
select * from emp where sal>all(900,1000,2000);
select * from emp where sal>any(900,1000,1100);--查询工资比900，1000，1100任何一个高的
select * from emp where sal<some(900,1000,1100);
select * from emp where sal between 2000 and 3000;
select * from emp where job='SALESMAN';--查询工种为SALSEMAN的员工，字符串‘’，内容区分大小写
select ename,deptno,job from emp where job='CLERK' or deptno = 20;
select ename,deptno,job,sal from emp where job='CLERK' and  deptno != 20;
select ename,deptno,job,sal from emp where not job='CLERK' and not deptno =20;
select *from emp where comm is not null;--判断是否为null
select *from emp where not comm is null;
--集合函数：并集去重：union 并集不去重：union all 交集 intersect 差集 minus
select * from emp where comm is not null or sal>1500;
select * from emp where comm is not null
union all
select * from emp where sal>1500;
--查询显示不存在雇员的员工的所有部门号 
select deptno from dept
minus
select distinct deptno from emp;
--like （定值）模糊匹配查询，配合%任意个任意字符，_ 一个任意字符
--查询员工中名字包含A字符的员工
select *from emp where ename like '%A%';
select * from emp where ename like '_A%';
--数据中 员工姓名中 存在 _ % ,如何查找：
--1)、编写测试数据
insert into emp(empno,ename,sal) values(100,'t_%test',9999);
insert into emp(empno,ename,sal) values(200,'t_tes%t',10000);
select * from emp;
insert into emp(empno,ename,sal) values(1000,'t_%test1',9999);
insert into emp(empno,ename,sal) values(2000,'t_tes%t2',10000);
select * from emp;
--查询员工薪资等于（1000，1500）
select * from emp where sal in(1000,1500,2000);
--排序
select * from emp where deptno=30 order by sal desc,empno desc;
select * from emp order by sal asc;
select * from emp order by comm asc nulls first;

/*1. 使用基本查询语句.
(1)查询DEPT表显示所有部门名称.
(2)查询EMP表显示所有雇员名及其全年收入(月收入=工资+补助),处理NULL行,并指定列别名为"年
收入"。（NVL(comm,0) comm取空值时用0替代）
(3)查询显示不存在雇员的所有部门号。
2. 限制查询数据
(1)查询EMP表显示工资超过2850的雇员姓名和工资。
(2)查询EMP表显示工资不在1500～2850之间的所有雇员及工资。
(3)查询EMP表显示代码为7566的雇员姓名及所在部门代码。
(4)查询EMP表显示部门10和30中工资超过1500的雇员名及工资。
(5)查询EMP表显示第2个字符为"A"的所有雇员名其工资。
(6)查询EMP表显示补助非空的所有雇员名及其补助。
3. 排序数据
(1)查询EMP表显示所有雇员名、工资、雇佣日期，并以雇员名的升序进行排序
(2)查询EMP表显示在1981年2月1日到1981年5月1日之间雇佣的雇员名、岗位及雇佣日期，并以雇
佣日期进行排序。
(3)查询表显示获得补助的所有雇员名、工资及补助，并以工资升序和补助降序排序。*/

select * from emp;
select * from dept;
select ename, sal*12+nvl(comm,0) as "年薪" from emp;
select empno from emp where comm is null;
select ename,sal from emp where sal>2850;
select ename,sal from emp where sal not between 1500 and 2850;
select ename,empno,mgr from emp where mgr = 7566;
--查询EMP表显示部门10和30中工资超过1500的雇员名及工资。
select * from emp where sal>1500 and deptno in(10,30);
select * from emp where ename like '%_A%'
select * from emp where comm is not null
select * from emp order by ename asc
--日期函数sysdate/current_date 返回当前日期
select sysdate from dual;
--2天后时间
select sysdate+2 from dual;
--5个月后时间
select add_months(sysdate,5) from dual;--返回加上2个月后的日期hiredate的值 ，并添加给伪列after
select ename,hiredate,add_months(hiredate,2) after from emp;
--月份只差
months_between（sysdate,hiredate）from emp;
--转换函数 to_date(c,m)字符串以指定格式转换为日期 to_char(d,m)日期以指定格式转换为字符串
select * from emp where hiredate between to_date('1981-02-01','yyyy-mm-dd') and to_date('1981-05-01','yyyy-mm-dd') order by hiredate;
select to_char(sysdate,'yyyy"年"mm"月"dd"日"') from dual；
select * from emp where to_char(hiredate,'yyyy')='1982';

--一
--1)找出每个月倒数第三天受雇的员工（如： 2009-5-29）
select * from emp where hiredate=last_day(hiredate)-2
--2)所有员工名字前加上 Dear ,并且名字首字母大写
select concat('dear',upper(substr(ename,1,1)),lower(substr(ename,2,length(ename-1))) from emp
--3)分组统计各部门下工资>500的员工的平均工资、
select avg(sal),ename from emp where sal>500 group by sal,ename
--4)算出每个职位的员工数和最低工资
select min(sal),count(ename),job from emp group by job
--5)列出员工表中每个部门的员工数，和部门no
select count(ename),job from emp group by job
--6)查询所有部门的编号，员工数量和工资平均值,并按平均工资降序
select deptno,count(ename),avg(sal) from emp group by deptno order by avg(sal) desc
--7)查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
select min(sal) from emp group by mgr having min(sal)>=6000
--8)查询员工  最高工资和  最低工资的差距使用别名DIFFERENCE
select max(sal),min(sal),max(sal)-min(sal) as DIFFERENCE from emp 

--9)查询员工姓名中包含字符a，每个部门的最低工资高于3000的部门编号,按照最低工资降序排序
select ename from emp where ename like '%A%' 
union
select min(sal),deptno,ename from emp where ename like '%A%' group by deptno,ename having min(sal)>700 order by min(sal) desc

select * from nobel;
--1)给出诺贝尔获奖总人数
select count(winner) from nobel;
--2)给出诺贝尔奖的获奖总次数
select count(subject) from nobel where subject = '诺贝尔'
--3)显示每个奖项的获奖总次数
select subject,count(subject) from nobel group by subject
--4)显示每个奖项在2005年获奖人数
select count(winner) from nobel where yr='2005'
--5)显示每个奖项不同获奖者的人数
select subject,count(winner) from nobel group by subject
--6)显示当年     有    三个诺贝尔奖的      年份
select yr,subject from nobel where subject ='诺贝尔' --找出含有诺贝尔奖的年份,再从含有诺贝尔年份的表里按年份分组找出数量等于3的年份
select count(subject),yr from(select yr, subject from nobel where subject='诺贝尔')group by yr having count(subject)=3
select count(subject),yr from nobel where subject='诺贝尔'group by yr having count(subject)=3 
select winner from nobel 
select count(subject),winner from nobel group by winner having count(subject)>1
--8)显示2005年及以后，有三个人获 得同一奖项的 年份 以及 奖项
select yr,count(winner),subject from nobel group by subject,yr having count(winner)=3 and yr<2008--同时按年份和奖项分组，

select * from wcemploy
--1.请用一个SQL语句查询每个部门的总人数
select department_name,count(name) from wcemploy group by department_name;
--2.请用一个SQL语句查询出    不同部门   的担任     “钳工”的   职工平均工资
select department_name,avg(salary)as"平均工资" from (select * from wcemploy where type ='钳工' ) group by department_name
--3.请用一个SQL语句查询出  不同部门  的担任  “钳工”的  职工平均工资 高于2000的部门
select department_name,avg(salary) from wcemploy where type='钳工' group by department_name having avg(salary)>2000

--4.请用一个SQL语句查询   每个部门   低于    平均工资 的员工信息
select * from wcemploy w,(select department_name,avg(salary) as "平均工资" from wcemploy group by department_name) a where w.department_name = a.department_name and w.salary<"平均工资"
/*

--9)显示每个地区的总人口数和总面积,以总人口来排序，仅显示那些面积超过1000000的地区

select region,sum(population),sum(area) from bbc group by region having sum(area)>1000000 order by sum(population)