笔记二

select * from emp;

select * from dept;

--- 1、全表查询 ------

/*

语法：

select 字段1,字段2,。。,字段n/*

from 表；

*/

select deptno,dname,loc

from dept;

select deptno,dname

from dept;

select empno,ename,job from emp;

--------- 2、条件查询 ----------

/*

语法：

select 字段1,字段2,。。,字段n/*

from 表

where 查询的过滤条件;

单值比较运算符：

>  >=  <  <=  =  <>或!=

*/

-- 例：查询20号部门中全体员工的姓名、工资、和工种的有关信息。

select ename,sal,job

from emp

where deptno = 20;

--- 练习：查找出奖金超过其工资的雇员的姓名、工资、奖金和工种的信息。

select ename,sal,comm,job

from emp

where comm > sal;

------------------

select deptno

from emp;

----- 去重：把重复的行，只返回一行：distinct 字段 ------

select distinct deptno

from emp;

select * from emp;

---多条件过滤之逻辑运算符:and、or

-- 例：查找出全部经理 和 第10号部门秘书的有关信息

select *

from emp

where job = 'MANAGER' or job = 'CLERK' and deptno = 10;

-- 练习：查找出不是30号部门中的所有经理的所有信息。

select *

from emp

--1、查找出工资高于1000元的职工的姓名、工种、工资和部门号，并按部门号由小到大排序显示

select e.ename,e.job,e.sal,e.deptno

from emp e

where e.sal > 1000

order by e.deptno;

--2、查找出奖金超过本人基本工资3%的职工的姓名，工资，奖金，奖金与工资的比例，并按其比例由高到低显示

select e.ename,e.sal,e.comm,e.comm/e.sal 奖金与工资的比例

from emp e

where e.comm > e.sal*0.03

order by e.comm/e.sal desc;

--3、按工种升序，而同工种按工资降序排列显示全部职工的姓名，工种，工 资。

select e.ename,e.job,e.sal

from emp e

order by e.job,e.sal desc;

----------------  华丽的分割线  -----------

-- 2、向数据库中插入数据 --

/*

commit:提交数据，把数据永久保存到数据库

rollback:回滚，撤销原来的操作

*/

/*

语法：

insert into 表(字段1,字段2,...,字段n) values(值1,值2,...,值n)

*/

select * from emp;

insert into t_student4(tid, tname, sex, birthday) values(1, '小王', '男', sysdate);

insert into t_student4 values(2, '老王', '男', sysdate);

insert into t_student4(tid,tname) values(3, '小红');

commit;

rollback;

------ 插入指定的时间，需要把时间格式的字符转换成时间类型，用到to_date()函数

-- 语法： to_date('字符串','格式')

insert into t_student4(tid, tname, sex, birthday)

                values(8, '小花', '女', to_date('1997-07-01','YYYY-MM-DD'));

 

-- 练习：在emp表中，查找1981-05-01之前入职的员工信息

select e.*

from emp e

where e.hiredate < to_date('1981-05-01','yyyy-mm-dd');

------------------------

select * from t_student4;

----------------

------ 3、修改数据库表中的数据 -----

/*

语法：

update 表名

set 字段 = 修改后的值

where 过滤条件;

*/

update t_student4

set tname = '大王'

where tid = 1;

commit;

rollback;

----  4、删除数据库表中的数据  ---------

/*

语法：

delete from 表名

where 过滤条件;

*/

delete from t_student4

where tname = '老王';

commit;

rollback;

select * from emp;

---------------- 三、单表复杂查询  ------------

-- 分组/聚合/统计函数：

--- count(*) 统计整个表的所有行数

select count(*) from emp;

--- count(字段) 统计整个表的非空行数

select count(comm) from emp;

--- 练习：统计emp表中，comm字段的空行的行数

select count(*)-count(comm) 空行行数 from emp;

select count(*) 空行行数 from emp where comm is null;

---- avg(字段) 统计平均数据

select avg(sal) from emp;

---- min(字段) 统计最小值

select min(sal) from emp;

--- max(字段) 统计最大值

select max(sal) from emp;

--- sum(字段) 求和

select sum(sal) from emp;

--计算emp表中公司职工的最低工资、最高工资、平均工资和总工资的和

select min(sal),max(sal),avg(sal),sum(sal)

from emp;

--计算emp表中公司职工的总人数及工种数

select count(e.empno) 总人数,count(distinct e.job) 工种数

from emp e

-------

-- group by 分组查询: 对多个组分别进行统计汇总

/*

语法：

select 字段/表达式

--- sum(字段) 求和

select sum(sal) from emp;

--计算emp表中公司职工的最低工资、最高工资、平均工资和总工资的和

select min(sal),max(sal),avg(sal),sum(sal)

from emp;

--计算emp表中公司职工的总人数及工种数

select count(e.empno) 总人数,count(distinct e.job) 工种数

from emp e

-------

-- group by 分组查询: 对多个组分别进行统计汇总

/*

语法：

select 字段/表达式

from 表

where 过滤条件

group by 字段

having 分组条件

order by 字段;

*/

--- 统计各个部门的人数，并显示人数大于或等于5人的部门号。

select deptno,count(*)

from emp

group by deptno

having count(*) >= 5;

-- 例：计算出公司支付给每个工种的总工资

select job,sum(sal)

from emp

group by job;

--- 练习：统计各部门的人数。

select deptno,count(*)

from emp

group by deptno;

-- 计算每个部门中每种工种各有多少职工数。

select e.deptno,job,count(*)

from emp e

group by e.deptno,job

order by e.deptno;

-- 练习1：查询出至少有两名秘书的所有部门的部门号，并按人数降序排序。

select e.deptno,count(*)

  from emp e

where e.job = 'CLERK'

group by e.deptno

having count(*) >= 2

order by count(*) desc;

-- 练习2：查询出所有经理和销售人员的年平均工资，并按年平均工资降序排序。

select e.job,avg(e.sal) * 12 年平均工资

  from emp e

where e.job in ('MANAGER', 'SALESMAN')

group by e.job

order by avg(e.sal) * 12 desc;

-- 1. 显示平均工资为>2000的职位

select e.job

from emp e

group by e.job

having avg(sal) > 2000;

-- 2. 计算工资在2000以上，各种职位的平均工资大于3000的职位及平均工 资

select e.job, avg(e.sal)

  from emp e

where e.sal > 2000

group by e.job

having avg(sal) > 3000;

3. 找每个部门的最高和最低的工资

4. 找每个部门中每种职位的最高和最低的工资

5. 显示出工作名称(job)中包含"MAN"的员工平均工资，最高工资，最低工资及工资的和

7. 显示出平均工资大于2000的部门名称及平均工资

select e.job, avg(e.sal)

  from emp e

where e.sal > 2000

group by e.job

having avg(sal) > 3000;

3. 找每个部门的最高和最低的工资

4. 找每个部门中每种职位的最高和最低的工资

5. 显示出工作名称(job)中包含"MAN"的员工平均工资，最高工资，最低工资及工资的和

7. 显示出平均工资大于2000的部门名称及平均工资