本文共 5875 字，大约阅读时间需要 19 分钟。

• SQL复习

1.常见的数据库对象有哪些？

表(table)  视图(view) 序列(sequence) 索引(index) 同义词(synonym)

存储过程(procedure) 存储函数(function) 触发器(trigger)

 

2.表：数据的主要存储方式，由行和列组成。后面重点说

 

 视图：存储起来的select语句。 

对视图中数据的DML操作，会导致创建视图使用的表中的数据的修改。

  create view emp_vu

  as

  select department_id,avg(salary) dept_avg_sal

  from employees

  group by department_id;

  --with read only

  select * from emp_vu;

 

  序列：提供了一系列有规律的数值，常用来作为表的主键的值

  create sequence emp_id_seq

  start with 1001

  increment by 1

  maxvalue 10000

  --minvalue

  --cycle/nocycle

  --cache/nocache

  1)nextval / currval

  select emp_id_seq.currval from dual;

 

  select emp_id_seq.nextval from dual;

 

  create table emp(

  id number(10),

  name varchar2(15)

  )

 

  insert into emp

  values(emp_id_seq.nextval,'BB');

 

  select * from emp;

 

  裂缝：①多个表共用一个序列②出现回滚③出现异常

 

  索引(index):当使用索引作用的列作为查询条件进行查询时，可以提高查询的效率。

  --如何创建索引：①自动创建（声明为主键或唯一性约束的列）   ②手动创建

  create index emp_sal

  on employees(salary);

 

3.重点：表

DDL:CREATE TABLE;ALTER TABLE;TRUNCATE TABLE;DROP TABLE;RENAME .. TO ..

不可回滚，即意味着：自动提交

--1.创建表

--1.1“白手起家”

create table dept(

dept_id number(10),

dept_name varchar2(15),

location_id varchar2(10),

birth Date

)

 

select * from dept;

 

--1.2基于现有的表，创建

create table emp1

as

select employee_id id,last_name name,hire_date,salary

from employees

--where department_id = 80;

where 1=2;

 

select * from emp1;

--1）对现有的表的复制/空表

create table emp_copy

as

select * from employees

--where 1=2;

 

select * from emp_copy;

 

--2.修改表

--2.1增加一个列

ALTER table emp

add(salary number(10,2) default 2000);

 

select * from emp;

--2.2修改现有的列

alter table emp

modify(salary number(15,2));

 

insert into emp(id,name)

values(1004,'CC');

 

--2.3重命名现有的列

alter table emp

rename column salary to sal;

 

--2.4删除现有的列

alter table emp

drop column sal;

 

--3.重命名现有的表

rename emp to employee;

 

select * from employee;

 

--4.清空表

truncate table employee;

 

rollback;

 

--5.删除表

drop table employee;

 

 

 

DML:增、删、改、查

--增insert into ...

--1.一条一条的添加

select * from dept;

 

insert into dept

values(,,,);

 

insert into dept(dept_id,location_id,dept_name)

values(,,);

--2.导入数据

insert into dept(dept_id,location_id,dept_name)

select department_id,location_id,department_name

from departments;

 

alter table dept

modify(dept_name varchar2(20));

 

--删

delete from dept

where dept_id < 40;

 

--改

update dept

set location_id = 1700

where dept_id = 20;

 

commit;

 

--查询（重中之重）

select ....   --分组函数（count / max / min / avg / sum）

from ...,....--多表连接

where ...--过滤条件和 多表的连接条件（若不写，会出现笛卡尔积的错误）

group by ...--凡是在select中出新了分组函数，则没有使用分组函数的列要作为group by的条件

having avg(..) ...--分组函数作为过滤条件，要使用having

order by ... asc/desc;

 

二、企业SQL考核真题

 

数据表：

dept: 

deptno(primary key), dname, loc

emp: 

empno(primary key), ename, job, mgr(references emp(empno)), sal,

deptno(references dept(deptno))

 

 

 

DEPT deptno(primary key) dname loc

EMP empno(primary key) ename job mgr(references emp(empno)) sal deptno(references dept(deptno))

 

 

 

 

 

 

 

 

1 列出emp表中各部门的部门号，最高工资，最低工资

select max(sal) as 最高工资,min(sal) as 最低工资,deptno from emp group by deptno;

2 列出emp表中各部门job 含’REP’的员工的部门号，最低工资，最高工资

select max(sal) as 最高工资,min(sal) as 最低工资,deptno as 部门号 from emp where job like '%REP%' group by deptno;

3 对于emp中最低工资小于7000的部门中job为'SA_REP'的员工的部门号，最低工资，最高工资

select max(sal) as 最高工资,min(sal) as 最低工资,deptno as 部门号 from emp  b where job='SA_REP' and 7000>(select min(sal) from emp  a where a.deptno=b.deptno) group by b.deptno

 

4写出对上题的另一解决方法

（请补充）

select deptno,min(sal),max(sal)

from emp

where job = 'SA_REP' and deptno in (

select deptno

from emp

--group by deptno

having min(sal) < 7000

)

group by deptno

5根据部门号由高而低，工资由低而高列出每个员工的姓名，部门号，工资

select deptno as 部门号,ename as 姓名,sal as 工资 from emp order by deptno desc,sal asc

6 列出'Abel'所在部门中每个员工的姓名与部门号

select ename,deptno from emp where deptno = (select deptno from emp where ename = 'Abel')

 

(法二)

select ename,deptno

from emp e1

where exists (

             select 'x'

             from emp e2

             where e1.deptno = e2.deptno

             and e2.ename = 'Abel'  

               )

7 列出每个员工的姓名，工作，部门号，部门名

select ename,job,emp.deptno,dept.dname from emp,dept where emp.deptno=dept.deptno

8 列出emp中工作为'SH_CLERK'的员工的姓名，工作，部门号，部门名

select ename,job,dept.deptno,dname from emp,dept where dept.deptno=emp.deptno and job='SH_CLERK'

9 对于emp中有管理者的员工，列出姓名，管理者姓名（管理者外键为mgr）

select a.ename as 姓名,b.ename as 管理者 from emp  a,emp  b where a.mgr is not null and a.mgr=b.empno

10 对于dept表中，列出所有部门名，部门号，同时列出各部门工作为'SH_CLERK'的员工名与工作

select dname as 部门名,dept.deptno as 部门号,ename as 员工名,job as 工作 from dept,emp where dept.deptno = emp.deptno(+) and job = 'SH_CLERK'

11 对于工资高于本部门平均水平的员工，列出部门号，姓名，工资，按部门号排序

select a.deptno as 部门号,a.ename as 姓名,a.sal as 工资 from emp  a where a.sal>(select avg(sal) from emp  b where a.deptno=b.deptno) order by a.deptno

 

(法二)select e.deptno,ename,sal

from emp e,(select deptno,avg(sal) avg_sal from emp group by deptno) b

where e.sal > b.avg_sal and e.deptno = b.deptno

 

12 对于emp，列出各个部门中工资高于本部门平均水平的员工数和部门号，按部门号排序

select count(a.sal) as 员工数,a.deptno  部门号 from emp  a where a.sal>(select avg(sal) from emp  b where a.deptno=b.deptno) group by a.deptno order by a.deptno

13. 对于emp中工资高于本部门平均水平，人数多于1人的，列出部门号，高于部门平均工资的人数，按部门号排序

select *

from(

select deptno,count(*) count_num

from emp e

where sal > (

      select avg(sal)

      from emp e1

      where e.deptno = e1.deptno 

)

group by deptno

) e1

where e1.count_num > 1

order by e1.deptno

 

14 对于emp中工资高于本部门平均水平，且其人数多于3人的，列出部门号，部门人数，按部门号排序

select count(a.empno) as 员工数,a.deptno as 部门号,avg(sal) as 平均工资 from emp a where (select count(c.empno) from emp  c where c.deptno=a.deptno and c.sal>(select avg(sal) from emp b where c.deptno=b.deptno))>3 group by a.deptno order by a.deptno

 

(法二)

select m.deptno,count(ee1.empno)

from(

select e1.deptno,count(empno) count_num

from emp e1

where e1.sal >

(select avg(sal) from emp e2 where e1.deptno = e2.deptno)

group by e1.deptno

) m,emp ee1

where m.count_num > 3 and m.deptno = ee1.deptno

group by m.deptno

15 对于emp中低于自己工资至少5人的员工，列出其部门号，姓名，工资，以及工资少于自己的人数

select a.deptno,a.ename,a.sal,(select count(b.ename) from emp as b where b.sal<a.sal) as 人数 from emp as a where (select count(b.ename) from emp as b where b.sal<a.sal)>5

 

 

本教程由尚硅谷教育大数据研究院出品，如需转载请注明来源。