数据库查询练习

在oracle中的用户中scott的几个表进行查询

这里是oracle的建表语句

CONNECT SCOTT/tiger  
CREATE TABLE DEPT  
       (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,  
        DNAME VARCHAR2(14) ,  
        LOC VARCHAR2(13) ) ;  
  
CREATE TABLE EMP  
       (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,  
        ENAME VARCHAR2(10),  
        JOB VARCHAR2(9),  
        MGR NUMBER(4),  
        HIREDATE DATE,  
        SAL NUMBER(7,2),  
        COMM NUMBER(7,2),  
        DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);  
INSERT INTO DEPT VALUES  
        (10,'ACCOUNTING','NEW YORK');  
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');  
INSERT INTO DEPT VALUES  
        (30,'SALES','CHICAGO');  
INSERT INTO DEPT VALUES  
        (40,'OPERATIONS','BOSTON');  
INSERT INTO EMP VALUES  
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);  
INSERT INTO EMP VALUES  
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);  
INSERT INTO EMP VALUES  
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);  
INSERT INTO EMP VALUES  
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);  
INSERT INTO EMP VALUES  
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);  
INSERT INTO EMP VALUES  
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);  
INSERT INTO EMP VALUES  
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);  
INSERT INTO EMP VALUES  
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);  
INSERT INTO EMP VALUES  
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);  
INSERT INTO EMP VALUES  
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);  
INSERT INTO EMP VALUES  
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);  
INSERT INTO EMP VALUES  
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);  
INSERT INTO EMP VALUES  
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);  
INSERT INTO EMP VALUES  
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);  
  
CREATE TABLE BONUS  
        (  
        ENAME VARCHAR2(10)      ,  
        JOB VARCHAR2(9)  ,  
        SAL NUMBER,  
        COMM NUMBER  
        ) ;  
CREATE TABLE SALGRADE  
      ( GRADE NUMBER,  
        LOSAL NUMBER,  
        HISAL NUMBER );  
  
INSERT INTO SALGRADE VALUES (1,700,1200);  
INSERT INTO SALGRADE VALUES (2,1201,1400);  
INSERT INTO SALGRADE VALUES (3,1401,2000);  
INSERT INTO SALGRADE VALUES (4,2001,3000);  
INSERT INTO SALGRADE VALUES (5,3001,9999);  
COMMIT;  
EXIT  

与之对应的是mysql

MySQL 脚本:
[sql] view plaincopyprint?
use prod;  
CREATE TABLE dept  
       (deptNO int(2),   
        DNAME VARCHAR(14) ,  
        LOC VARCHAR(13),  
        primary key(deptNO) ) ;  
  
CREATE TABLE emp  
       (empNO int(4) ,  
        ENAME VARCHAR(10),  
        JOB VARCHAR(9),  
        MGR int(4),  
        HIREDATE DATE,  
        SAL int(7),  
        COMM int(7),  
        deptNO int(2),  
        primary key(empno),   
        foreign key(deptno) REFERENCES dept(deptno));  
  
INSERT INTO dept VALUES  
        (10,'ACCOUNTING','NEW YORK');  
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');  
INSERT INTO dept VALUES  
        (30,'SALES','CHICAGO');  
INSERT INTO dept VALUES  
        (40,'OPERATIONS','BOSTON');  

INSERT INTO emp VALUES  
(7369,'SMITH','CLERK',7902,date_format('1980-12-17','%Y-%m-%d'),800,NULL,20);  
INSERT INTO emp VALUES  
(7499,'ALLEN','SALESMAN',7698,date_format('1981-2-20','%Y-%m-%d'),1600,300,30);  
INSERT INTO emp VALUES  
(7521,'WARD','SALESMAN',7698,date_format('1981-2-22','%Y-%m-%d'),1250,500,30);  
INSERT INTO emp VALUES  
(7566,'JONES','MANAGER',7839,date_format('1981-4-2','%Y-%m-%d'),2975,NULL,20);  
INSERT INTO emp VALUES  
(7654,'MARTIN','SALESMAN',7698,date_format('1981-9-28','%Y-%m-%d'),1250,1400,30);  
INSERT INTO emp VALUES  
(7698,'BLAKE','MANAGER',7839,date_format('1981-5-1','%Y-%m-%d'),2850,NULL,30);  
INSERT INTO emp VALUES  
(7782,'CLARK','MANAGER',7839,date_format('1981-6-9','%Y-%m-%d'),2450,NULL,10);  
INSERT INTO emp VALUES  
(7788,'SCOTT','ANALYST',7566,date_format('1987-7-13','%Y-%m-%d'),3000,NULL,20);  
INSERT INTO emp VALUES  
(7839,'KING','PRESIDENT',NULL,date_format('1981-11-17','%Y-%m-%d'),5000,NULL,10);  
INSERT INTO emp VALUES  
(7844,'TURNER','SALESMAN',7698,date_format('1981-9-8','%Y-%m-%d'),1500,0,30);  
INSERT INTO emp VALUES  
(7876,'ADAMS','CLERK',7788,date_format('1987-6-13', '%Y-%m-%d'),1100,NULL,20);  
INSERT INTO emp VALUES  
(7900,'JAMES','CLERK',7698,date_format('1981-12-3','%Y-%m-%d'),950,NULL,30);  
INSERT INTO emp VALUES  
(7902,'FORD','ANALYST',7566,date_format('1981-12-3','%Y-%m-%d'),3000,NULL,20);  
INSERT INTO emp VALUES  
(7934,'MILLER','CLERK',7782,date_format('1982-1-23','%Y-%m-%d'),1300,NULL,10);  

CREATE TABLE BONUS  
        (  
        ENAME VARCHAR(10)      ,  
        JOB VARCHAR(9)  ,  
        SAL int,  
        COMM int  
        ) ;  
  
CREATE TABLE SALGRADE  
      ( GRADE int,  
        LOSAL int,  
        HISAL int );  
  
INSERT INTO SALGRADE VALUES (1,700,1200);  
INSERT INTO SALGRADE VALUES (2,1201,1400);  
INSERT INTO SALGRADE VALUES (3,1401,2000);  
INSERT INTO SALGRADE VALUES (4,2001,3000);  
INSERT INTO SALGRADE VALUES (5,3001,9999);  
COMMIT;  

SELECT deptno,ename,sal
  FROM emp outer
  WHERE sal>(SELECT avg(sal)
                FROM emp )
  ORDER BY deptno;

表的属性

在这里用的是mysql环境,mysql和oracle在进行sql查询时基本都是相同的

emp表

dept表

SALGRADE表

相关的查询语句

单表查--------- 单表查询

1.查找职位是职员（Clerk）或分析员（Analyst）的雇员信息

select *from emp where job like 'CLERK' or job like 'ANALYST';
2.查找以“S”开头的雇员姓名和所在部门 
select ename, deptno from emp where ename like 'S%';
3.查询每个雇员的年工资
select ename, 12*(sal+ifnull(comm, 0)) from emp;    -- ifnul 对应nvl 
4.按工资升序排列20号部门的雇员 
select * from emp where deptno=20 order by sal;

5.查询工资的最高值
select max(sal) from emp;

6.查询工资最高的员工的信息
select * from emp where sal = (select max(sal) from emp);

7.查询每个部门的人数
select deptno ,count(*) from emp group by deptno;

8.查询雇员人数在4人以上的部门的部门号
select deptno from emp group by deptno having count(deptno)>4;

9.查找部门工资总和超过9000的部门,并按工资总和升序排列。
select deptno ,sum(sal) from emp group by deptno having sum(sal)>9000 order by sum(sal);

10.查找平均工资低于2000的部门号和平均工资。
select deptno, avg(sal) from emp group by deptno having  avg(sal)<2000;

11.统计有奖金的员工人数。
select ename, dname,comm from emp, dept where comm >0;

12.按照部门号升序，部门号相同的再按雇员的工资降序排列，显示雇员的信息。
select * from emp order by deptno , sal desc;

13.查询没有奖金的员工的信息。（comm字段为奖金字段）
select * from emp where comm is null;   --  查询字段为null用is不能用=

14.显示在2000到3000的员工姓名和工资
select ename, sal from emp where sal >= 2000 and sal <= 3000;

15.查询每个部门的编号以及工种（job字段）信息
select distinct deptno,job from emp ;

 

嵌套查询

1.查询获得奖金的雇员的姓名和所在部门名称。 
select ename, deptno from emp,dept where comm > 0 ;

2.查询财务部门的员工的姓名、入职日期 、工资。
select ename,hiredate,sal from emp,dept where emp.deptno=dept.deptno and dname='ACCOUNTING';

3.查询1981年以后入职的雇员的姓名及所在的部门名称，并按入职时间升序排序。
select ename,dname
from emp,dept
where emp.deptno=dept.deptno and hiredate >= '1981-01-01' order by hiredate;

4.查询每个部门的员工人数及其部门名称。 
select dname, count(emp.empno)
from emp, dept 
where emp.deptno=dept.deptno group by dname;

5.查询每个部门的雇员姓名、员工工资、工资级别和部门名称。
select ename, sal, GRADE, dname from emp, SALGRADE, dept  
where (emp.deptno=dept.deptno) and (emp.sal between SALGRADE.losal and SALGRADE.hisal);
-- 这两个一样
select ename, sal, GRADE, dname from emp e, SALGRADE s, dept d  
where (e.deptno=d.deptno) and (e.sal between s.losal and s.hisal);

6.查询部门人数超过5人的部门名称。   -- 并且统计部门的个数如何写
select dname  from emp, dept
where emp.deptno=dept.deptno 
group by dname having count(emp.empno)>3;

7.1 查询SMITH的上级领导的名字;
select e.ename emp_ename, b.ename mgr_ename from emp e, emp b where e.empno=b.mgr; 
7.查询SMITH的上级领导的姓名及其职位和部门名称。 
select ename, job, dname from emp e, dept d
where  empno = (select mgr from emp where ename='SMITH') and e.deptno=d.deptno;


8.查询大于员工平均工资的员工的姓名和工资
select ename, sal from emp where sal > (select avg(sal) from emp);

9.查询大于本工种的平均工资的员工的姓名和工资
-- 不一定对

select ename,sal, job, (select avg(sal) from emp e2 where e2.deptno=e1.deptno) 
from emp e1 where sal>(select avg(sal) from emp e2 where e2.deptno=e1.deptno);

10.查询与smith在同一部门，且工作岗位相同的员工的信息（多列子查询）
-- 先查询同一部门的,然后查询同一个岗位
select * from emp where deptno = (select deptno from emp where ename='SMITH')
and job = (select job from emp where ename='SMITH');

select *from emp where (deptno, job)=(select deptno,job from emp where ename='SMITH');

 

 

综合查询

前面的有几个是oracle中的操作,但是后面的查询语句都是一样的.

1.启动服务的方法
管理,打开服务
sqlplus system/sias

2.使用scott用户登录

sqlplus system/sias
alter user scott account  unlock;
alter user scott identified by sias;
conn scott/sias

3.设置环境变量 linesize为150
set linesize  150
4.显示当前连接用户名
show user;

5.查看系统拥有哪些用户
select * from db_users;

6.用@ 或start命令将指定命令文件调入缓冲区并执行。
@d:\文件名.sql

7.显示当前用户下某个表的表结构
desc 表名

8.创建student表（sno char（4），sanme varchar（20），sex char(2),birthday date,sal number(6,2)）

drop table student cascade constraints;  -- 删除约束
create table student(
sno char(4),
sname varchar(20),
sex char(2),
birthday date,
sal double(6,2)  ---- 在 oracle中用number
)

9.创建class表（classid number（2），classname varchar(20)）
drop table class cascade constraints;
create table class(
class int(2),  -- 在oracle中用number
classname varchar(20)  
)

10.显示表结构，给student表增加一个字段classid;
desc student;
alter table student add classid char(10);

11.为表dept增加一列All_emp 用来存放部门人数 
alter table dept all_emp char(10);

12.向dept表中表中插入一条记录，每个字段都有具体值
insert into dept values (1,'a','b');
13.向student表中插入记录，记录来自一个查询结果
insert into student(sno, sname) select empno,ename from emp where empno='7369'; 

14.新建一个表dept1，结构与dept相同，并将dept表中插入到dept1中
create table dept1 select *from dept;

15.将 Martin 提升为经理，工资加1000 。
update emp set job='MANAGER', sal=sal+1000 where ename='Martin';

16.查找职位是职员（Clerk）或分析员（Analyst）的雇员姓名和工种
select ename, job from emp where job='CLERK' or job='ANALYST';

17.查找以“S”开头的雇员姓名和所在部门 
select ename, deptno from emp where ename like 'S%';

18.查所工资大于所有20部门的雇员的信息 
select * from emp  where  sal > (select max(sal) from emp where deptno=20);

19.查询1982年入职的员工的信息
select *from emp where hiredate like '%82';  -- 在Oracle中
select *from emp where hiredate like '1982%'; -- 在mysql中, 主要是数据不同,如果数据相同都一样

20.查询每个雇员的年工资
select ename, 12*(sal+ifnull(comm, 0)) from emp;    -- ifnul 对应nvl 

21.按工资升序排列20号部门的雇员
select *from emp where deptno='20' order by sal ;
----------------------------- 重新对比较
22.查询工资的最高值
select max(sal) from emp;
23.查询工资最高的员工的信息
select * from emp where sal=(select max(sal) from emp);
24.查询员工的平均工资
select avg(sal) from emp;
25.查询工资低于平均工资的员工的信息
select *from emp where sal < (select avg(sal) from emp);

26.将工资低于平均工资的员工工资增加10%
update emp set sal=sal*1.1 where sal < (select avg(sal) from emp);

27.查询每个部门的人数  ---第二次还没写出来
select deptno, count(*) from emp group by deptno;
28.查询雇员人数在4人以上的部门的部门号  -- 不会
select deptno from emp group by deptno having count(*)>4;

29.部门工资总和超过9000的部门,并按工资总和升序排列。
select deptno, sum(sal) from emp 
group by deptno having  sum(sal) > 9000 order by deptno desc;

30.查询工资高于7698号雇员，并且工种与他相同的雇员情况。
select *from emp where 
sal >(select sal from emp where empno='7698')
and job=(select job from emp where empno='7698');

31.查询工资高于或等于20号部门工资额最高的雇员情况 。
select *from emp where sal >= (select max(sal) from emp where deptno='20');

32.查询20号部门的雇员工资、奖金情况 。
select sal, comm from emp where deptno='20';

33.查询与smith部门及岗位相同的员工的信息。
select *from emp where (deptno, job)=
(select deptno, job from emp where ename='SMITH'); 

34.查看工资高于3000的雇员及所在部门情况 。
select * from emp, dept where emp.deptno=dept.deptno and sal>3000;

35.查询部门号为10的员工的名字，工资和部门号并按工资降序排
select ename, sal, deptno from emp where deptno='10' order by sal desc;
36.查询每位员工的员工号和工资级别。
select empno, GRADE from emp, SALGRADE where sal >= losal AND sal <= hisal;


37.查询与smith在同一部门，且工作岗位相同的员工的信息（多列子查询）
select * from emp where deptno=(select deptno from emp where ename='SMITH')
and job = (select job from emp where ename='SMITH');

--- 另一种写法
select *from emp where 
(deptno, job) = (select deptno, job from emp where ename='SMITH');

38.查询工资低于本部门的平均工资的员工的信息   --- 不会写 
select *from emp a
where sal < (select avg(sal) from emp where emp.deptno=a.deptno group by deptno);

 

 

其他的查询语句

http://www.voidcn.com/article/p-qnzwvljd-hx.html