学习SQL查询语句

最新推荐文章于 2024-06-22 20:53:03 发布

iteye_16906

最新推荐文章于 2024-06-22 20:53:03 发布

阅读量74

点赞数

分类专栏：数据库文章标签： SQL Oracle 面试 MySQL 数据结构

数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏


--desc 表名 描述表的内容   
desc emp;

--加上数学表达式和列名  ""保持格式
select ename "name space", sal*12 year_sal from emp;    

select 2*3 from dual;

select sysdate from dual;

--空值的数学表达式 结果都是空值
select ename, sal*12 + comm from emp;   

--"||"字符串连接 单引号中的是字符串，字符串中的单引号，''表示
select ename||sal from emp;     

--distinct 修饰两个字段
select distinct deptno, job from emp;

select ename, sal from emp where sal between 800 and 1500;
select ename, sal from emp where sal >= 800 and sal <= 1500;

select ename, sal from emp where comm is null;


--一个事务开始于一条ddl语句，结束语rollback commit ddl数据定义语句 dcl数据控制语句语句
--正常断开连接 提交 非正常断开连接 回滚

select ename, sal from emp where sal not in (800 ,1500);

-- 模糊查询
select ename, sal from emp where ename like '_A%';
select ename, sal from emp where ename like '%\%%';

--转义字符
select ename, sal from emp where ename like '%$%%' escape '$';

--排序
select * from dept order by deptno desc;
select ename, sal from emp order by deptno asc, ename desc;

--函数
select lower(ename) from emp;
select substr(ename,1,3) from emp;
select round(23.652, 1) from dual;
select to_char(sal, '$99,999.999') from emp;
select to_char(sal, 'L000,000.000') from emp;
select to_char(hiredate, 'yyyy-mm-dd hh:mi:ss') from emp;
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;
select ename, hiredate from emp
    where hiredate > to_date('1982-09-11 12:44:44', 'YYYY-MM-DD HH24:MI:SS');
select sal from emp where sal > to_number('$1,250.000', '$9,999.000');
select ename,sal*12 + nvl(comm, 0) from emp;

--组函数  多行输入，只有一行输出 select ename, max(sal) from emp; 结果不能保证只有一行
select max(sal) from emp;
select sum(sal) from emp;
select count(distinct deptno) from emp;

--分组函数
select deptno,avg(sal) from emp group by deptno;
select deptno, job, max(sal) from emp group by deptno, job;


--rollup、cube配合group by命令使用，可以提供信息汇总功能（类似于"小计"）  
select job,deptno,sum(sal) total_sal from emp group by rollup(job,deptno);  


--出现在select后，不是组函数的，必须出现在group by里
--where语句对单挑记录进行过滤，分组后过滤用having

--子查询 把一次查询结果当成表
select ename from emp where sal = (select max(sal) from emp);
select ename, sal from emp
    join (select max(sal)max_sal, deptno from emp group) t
    on(t.max_sal = emp.sal and t.deptno = emp.deptno);

--自连接
select t1.ename, t2.ename from emp t1, emp t2 where t1.mgr = t2.empno;


select ename, dname, grade from emp e, dept d, salgrade s 
where e.deptno = d.deptno and e.sal between s.losal and s.hisal and job <> 'CLERK';

--SQL 1999  链接条件不写在where中，where只负责过滤数据
select ename, dname from emp cross join dept;
select ename, dname from emp join dept on (emp.deptno = dept.deptno);
select ename, dname from emp join dept using (deptno)；

select ename, grade from emp e join salgrade s on (e.sal between s.losal and s.hisal);
select e1.ename, e2.ename from emp e1 join emp e2 on (e1.mgr = e2.empno);

--左外连接
select e1.ename, e2.ename from emp e1 left join emp e2 on (e1.mgr = e2.empno);
--右外连接
select ename, dname from emp e right join dept d on (e.deptno = d.deptno);
select ename, dname from emp e full outer join dept d on (e.deptno = d.deptno);

--求部门中那些人薪水最高
select ename, sal  from emp 
join (select deptno, max(sal) max_sal from emp group by deptno) t 
on (emp.sal = t.max_sal and emp.deptno = t.deptno);

--求部门平均薪水的等级
select deptno, grade from salgrade s 
join (select avg(sal) avg_sal, deptno from emp group by deptno) t 
on (avg_sal between losal and hisal);

--求平均薪水最高的部门的部门标号
select deptno, avg_sal from
(select avg(sal) avg_sal, deptno from emp group by deptno)
where avg_sal =
(select max(avg(sal)) from emp group by deptno);

--平均薪水的等级最低的部门的部门名称
select dname, t1.deptno, grade, avg_sal from
(  select deptno, grade, avg_sal from
  (select deptno, avg(sal) avg_sal from emp group by deptno) t
  join salgrade s on (t.avg_sal between s.losal and s.hisal)
) t1
join dept on (t1.deptno = dept.deptno)
where t1.grade = 
(
  select min(grade) from
  (  select deptno, grade, avg_sal from
    (select deptno, avg(sal) avg_sal from emp group by deptno) t
    join salgrade s on (t.avg_sal between s.losal and s.hisal)
  )
)

--建立视图
create view v$_dept_avg_sal_info as
select deptno, grade, avg_sal from
  (select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal);

--变简单了
select dname, t1.deptno, grade, avg_sal from
v$_dept_avg_sal_info t1
join dept on (t1.deptno = dept.deptno)
where t1.grade = 
(
  select min(grade) from v$_dept_avg_sal_info
)

--求部门平均薪水的等级
select deptno, avg_sal, grade from
  (select avg(sal) avg_sal, deptno from emp group by deptno)
join salgrade s
on (avg_sal between s.losal and s.hisal);

--求部门平均的薪水等级
select avg(grade), deptno from
(
  select deptno, ename, grade from emp
  join salgrade s
  on emp.sal between s.losal and s.hisal
) 
group by deptno

--雇员中有哪些人是经理人
select ename from emp where empno in
(select distinct mgr from emp);

--不使用组函数，求薪水的最高值
select distinct sal from emp where sal not in
(select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal));

--平均薪水最高的部门的部门名称
select dname from dept where deptno =
(
  select deptno from
    (select avg(sal) avg_sal, deptno from emp group by deptno)
  where avg_sal =
  (select max(avg_sal) from
    (select avg(sal) avg_sal, deptno from emp group by deptno)
  )
)

--求比普通员工的最高薪水还要高的经理人名称
select ename from emp 
where empno in (select distinct mgr from emp where mgr is not null) and sal >
(
  select max(sal) from emp where empno not in
  (select distinct mgr from emp where mgr is not null)
)

--rownum只能和小于等于号联合使用
--使用rownum 要关联表
select ename from
(select rownum r, ename from emp) where r > 10;

--薪水最高的5个人
select ename, sal from
(select ename, sal from emp order by sal desc)
where rownum <= 5;

--薪水最高的第六个到第十个雇员
select ename, sal from
  (
  select ename, sal, rownum r from
    (select ename, sal from emp order by sal desc)
  )
where r >= 6 and r <= 10


--http://penghao122.iteye.com/blog/54810
--SQL面试题
--找出没选过“黎明”老师的所有学生姓名。 
select sname from s 
join sc on (sc.sno = s.sno)
join c on (c.cno = s.cno) 
where c.cteacher <> 'liming';

--列出2门以上（含2门）不及格学生姓名及平均成绩。 
select sname where sno in 
  (
  select sno from sc where scgrade < 60
  group by sno having count(*) >= 2
  )

--即学过1号课程有学过2号课所有学生的姓名
select sno from sc where cno = 1 and sno in
(
  select sno from sc where cno = 2
);

--一个事务开始于一条ddl语句，结束语rollback commit ddl数据定义语句 dcl数据控制语句语句
--正常断开连接 提交 非正常断开连接 回滚

送上[b]mysql[/b]中的，oracle[b]默认表[/b]([b]emp[/b],[b]dept[/b], [b]salgrade[/b])的建表语句


CREATE TABLE EMP
       (EMPNO numeric(4) NOT NULL,
        ENAME VARCHAR(10),
        JOB VARCHAR(9),
        MGR numeric(4),
        HIREDATE DATE,
        SAL numeric(7, 2),
        COMM numeric(7, 2),
        DEPTNO numeric(2));

INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902,
        '1980-12-17',  800, NULL, 20);
INSERT INTO EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
        '1981-11-20', 1600,  300, 30);
INSERT INTO EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698,
        '1981-02-22', 1250,  500, 30);
INSERT INTO EMP VALUES
        (7566, 'JONES',  'MANAGER',   7839,
        '1981-04-02',  2975, NULL, 20);
INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN',  7698,
        '1981-09-28', 1250, 1400, 30);
INSERT INTO EMP VALUES
        (7698, 'BLAKE',  'MANAGER',   7839,
        '1981-05-01',  2850, NULL, 30);
INSERT INTO EMP VALUES
        (7782, 'CLARK',  'MANAGER',   7839,
        '1981-01-09',  2450, NULL, 10);
INSERT INTO EMP VALUES
        (7788, 'SCOTT',  'ANALYST',   7566,
        '1982-12-09', 3000, NULL, 20);
INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        '1981-11-07', 5000, NULL, 10);
INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN',  7698,
        '1981-09-08',  1500,    0, 30);
INSERT INTO EMP VALUES
        (7876, 'ADAMS',  'CLERK',     7788,
        '1983-01-12', 1100, NULL, 20);
INSERT INTO EMP VALUES
        (7900, 'JAMES',  'CLERK',     7698,
        '1981-11-03',   950, NULL, 30);
INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        '1981-12-03',  3000, NULL, 20);
INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        '1982-01-12', 1300, NULL, 10);


CREATE TABLE DEPT
       (DEPTNO numeric(2),
        DNAME VARCHAR(14),
        LOC VARCHAR(13) );

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');

CREATE TABLE BONUS
        (ENAME VARCHAR(10),
         JOB   VARCHAR(9),
         SAL   numeric,
         COMM  numeric);

CREATE TABLE SALGRADE
        (GRADE numeric,
         LOSAL numeric,
         HISAL numeric);

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);

CREATE TABLE DUMMY
        (DUMMY numeric);

INSERT INTO DUMMY VALUES (0);

[b]Oracle树状查询[/b]

Select  *  from tablename
  start with [condition1]
  Connect By Prior  [condition2]
  where [condition3]

condition1 通常是用于筛选某颗树或者多颗树的结点，这里所的树包括子树，

condtion2 通常是用于连接父结点和子结点的关系，prior 所表示的是上一条记录，通常用法：prior id = pid，（上一条的的ID号是本条记录的父类ID号）这样就把整个树状结构给建立起来了。

conditon3 就是我们通常的where语句，过滤某些记录。

iteye_16906

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
学习SQL查询语句

[code="sql"]--desc 表名描述表的内容 desc emp;--加上数学表达式和列名 ""保持格式select ename "name space", sal*12 year_sal from emp; select 2*3 from dual;select sysdate from dual;--空值的数学表达式结果都是空...
复制链接

扫一扫