一、练习的表结构
emp empno ename job mgr hiredate sal comm deptno
dept deptno dname loc
salgrade grade losal hisal
二、创建表
CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2));
INSERT INTO EMP VALUES
(7369, ‘SMITH‘, ‘CLERK‘, 7902,
TO_DATE(‘17-DEC-1980‘, ‘DD-MON-YYYY‘,‘NLS_DATE_LANGUAGE=American‘), 800, NULL, 20);
INSERT INTO EMP VALUES
(7499, ‘ALLEN‘, ‘SALESMAN‘, 7698,
TO_DATE(‘20-FEB-1981‘, ‘DD-MON-YYYY‘,‘NLS_DATE_LANGUAGE=American‘), 1600, 300, 30);
INSERT INTO EMP VALUES
(7521, ‘WARD‘, ‘SALESMAN‘, 7698,
TO_DATE(‘22-FEB-1981‘, ‘DD-MON-YYYY‘,‘NLS_DATE_LANGUAGE=American‘), 1250, 500, 30);
INSERT INTO EMP VALUES
(7566, ‘JONES‘, ‘MANAGER‘, 7839,
TO_DATE(‘2-APR-1981‘, ‘DD-MON-YYYY‘,‘NLS_DATE_LANGUAGE=American‘), 2975, NULL, 20);
INSERT INTO EMP VALUES
(7654, ‘MARTIN‘, ‘SALESMAN‘, 7698,
TO_DATE(‘28-SEP-1981‘, ‘DD-MON-YYYY‘,‘NLS_DATE_LANGUAGE=American‘), 1250, 1400, 30);
INSERT INTO EMP VALUES
(7698, ‘BLAKE‘, ‘MANAGER‘, 7839,
TO_DATE(‘1-MAY-1981‘, ‘DD-MON-YYYY‘,‘NLS_DATE_LANGUAGE=American‘), 2850, NULL, 30);
INSERT INTO EMP VALUES
(7782, ‘CLARK‘, ‘MANAGER‘, 7839,
TO_DATE(‘9-JUN-1981‘, ‘DD-MON-YYYY‘,‘NLS_DATE_LANGUAGE=American‘), 2450, NULL, 10);
INSERT INTO EMP VALUES
(7788, ‘SCOTT‘, ‘ANALYST‘, 7566,
TO_DATE(‘09-DEC-1982‘, ‘DD-MON-YYYY‘,‘NLS_DATE_LANGUAGE=American‘), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7839, ‘KING‘, ‘PRESIDENT‘, NULL,
TO_DATE(‘17-NOV-1981‘, ‘DD-MON-YYYY‘,‘NLS_DATE_LANGUAGE=American‘), 5000, NULL, 10);
INSERT INTO EMP VALUES
(7844, ‘TURNER‘, ‘SALESMAN‘, 7698,
TO_DATE(‘8-SEP-1981‘, ‘DD-MON-YYYY‘,‘NLS_DATE_LANGUAGE=American‘), 1500, NULL, 30);
INSERT INTO EMP VALUES
(7876, ‘ADAMS‘, ‘CLERK‘, 7788,
TO_DATE(‘12-JAN-1983‘, ‘DD-MON-YYYY‘,‘NLS_DATE_LANGUAGE=American‘), 1100, NULL, 20);
INSERT INTO EMP VALUES
(7900, ‘JAMES‘, ‘CLERK‘, 7698,
TO_DATE(‘3-DEC-1981‘, ‘DD-MON-YYYY‘,‘NLS_DATE_LANGUAGE=American‘), 950, NULL, 30);
INSERT INTO EMP VALUES
(7902, ‘FORD‘, ‘ANALYST‘, 7566,
TO_DATE(‘3-DEC-1981‘, ‘DD-MON-YYYY‘,‘NLS_DATE_LANGUAGE=American‘), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7934, ‘MILLER‘, ‘CLERK‘, 7782,
TO_DATE(‘23-JAN-1982‘, ‘DD-MON-YYYY‘,‘NLS_DATE_LANGUAGE=American‘), 1300, NULL, 10);
CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(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 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);
三、SQL练习
1、desc+表名 查看表结构
2、
SQL> desc dual
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
DUMMY VARCHAR2(1)
3、select ename,sal*12 sal_year from emp;//查询结果列名为大写
select ename,sal*12 "sal_year" from emp;//查询结果列名引号内“sal_yeal”小写，ename大写
4、任何含有空值的数学表达式计算结果都为空值
5、字符串连接符 select ename||sal from emp;//结果为ename和sal拼接成的字母串 如SMITH800；列名为ENAME||NAME
select ename||‘pinjiezifuchuan‘from emp;//结果为SMITHpinjiezifuchuan 列名为ENMAE||‘PINJIEZIFUCHUAN‘
select ename||‘pinjie‘‘zfuchuan’ from emp;//如果拼接的字符串中有单引号 则要进行类似转义 即要写两个单引号
结果为SMITHpinjie‘zifuchuan 列名为ENAME||PINJIE‘‘ZIFUCHUAN
6、去除相同记录select distinct deptno from emp;//去除deptno相同的记录
select distinct deptno,job from emp;//去除deptno job均相同的记录
7、是否为空 select ename from emp where comm is null;
8、日期的处理
select ename,hiredate from emp where hiredate>‘20-2月-81‘
9、通配符
select ename from emp where ename like ‘%\%%‘;//选中名字中含有%的人的名字 中间用了转移字符
select ename from emp where ename like ‘%$%%‘ escape ‘$‘//自己制定转义字符 $
10、字符截取
select substr(ename,1,3) from emp;//列名SUBSTR
11、
select count(distinct deptno) from dept 去除重复值之后所剩的记录数
12、薪水最高的员工的名字
select ename from emp where sal=(select max(sal) from emp);
13、每个部门薪水最高的员工的名字
出现在查询列表中的列名必须在组函数或者Group By中,因此这里需要用到连接表
select ename,deptno from emp join
(select max(sal) max_sal,deptno from emp group by deptno) t
on (emp.sal=t.max_sal and emp.deptno=t.deptno);
14、查询平均工资大于2000的部门的平均工资和部门编号
select avg(sal) deptno from emp group by deptno having avg(sal)>2000;
15、（自连接）求某位员工及其经理人的名字(mgr是经理人编号)
select e1.name ,e2.name from emp e1,emp e2 where e1.mgr=e2.empno;
16、
select ename,dname,grade from emp e,dept d, salgrade s
where e.deptno = d.deptno and e.sql between s.losal and s.hisal and
job<>‘CLERK‘;
有没有办法把过滤条件和连接条件分开来? 出于这样考虑,Sql1999标准推出来了.有许多人用的还是
旧的语法,所以得看懂这种语句.
select ename,dname from emp,dept;(旧标准).
交叉连接 笛卡尔乘积
select ename,dname from emp cross join dept;(1999标准)
新的语法，连接条件不会再放在where语句中，where语句中只写数据过滤条件
select ename,dname from emp,dept where emp.deptno=dept.deptno (旧)
select ename,dname from emp join dept on(emp.deptno = dept.deptno); 1999标准.没有Where语句.
select ename,dname from emp join dept using(deptno);等同上句,但不推荐使用.因为使用using有很多假设，假设两个表必须有deptno 而且类型必须相同
select ename,grade from emp e join salgrade s on(e.sal between s.losal and s.hisal);
join 连接语句, on过滤条件。连接，条件一眼分开。如果用Where语句较长时，连接语句和过滤语句混在一起。
三张表连接：
slect ename,dname, grade from
emp e join dept d on(e.deptno=d.deptno)
join salgrade s on(e.sal between s.losal and s.hisal)
where ename not like ‘_A%‘;
把每张表连接 条件不混在一起，然后数据过滤条件全部区分开来。读起来更清晰，更容易懂一点。
select e1.ename,e2.ename from emp e1 join emp e2 on(e1.mgr = e2.emptno);
左外连接：会把左边这张表多余数据显示出来，也就是不能与另外一张表产生连接的表连接起来。
select e1.ename,e2,ename from emp e1 left join emp e2 on(e1.mgr =e2.empno);left 后可加outer
右外连接：
select ename,dname from emp e right outer join dept d on(e.deptno =d.deptno); outer可以取掉。
即把左边多余数据，也把右边多余数据拿出来，全外连接。
select ename,dname from emp e full join dept d on(e.deptno =d.deptno);
17、求部门平均薪水的等级
select deptno,avg_sal,grade from (seclet deptno , avg(sal) avg_sal from emp group by deptno ) t join salgrade s on (t.avg_sal between s.losal and s.hisal )
18、求部门平均的薪水等级
select deptno,avg(grade) from (select deptno,grade from salgrade join emp on(emp.sal between losal and hisal)) group by deptno.
19、雇员中有哪些人是经理人
select ename from emp where empto in(select distinct mgr from emp);
20、不准用组函数，求薪水的最高值
select sal from emp where sal not in ( select distinct e1.sal from emp e1 join emp e2 on( e1.sal<e2.sal));
21、求平均薪水最高的部门的部门编号
select deptno,avg_sal from (select deptno, avg(sal) avg_sal from emp group by deptno ) where avg_sal=
(select max(avg_sal) from ( select deptno,avg(sal) avg_sal from emp group by deptno)) 蓝色部分求出的所有部门中平均薪水最高的的部门的平均薪水 红色部分是求出平均工资等于这个最高平均薪水的部门的编号和平均工资
22、求平均薪水最高的部门的部门的部门名称(利用上面求出的部门编号)
select dname from dept where deptno=(select deptno from (select deptno, avg(sal) avg_sal from emp group by deptno ) where avg_sal=
(select max(avg_sal) from ( select deptno,avg(sal) avg_sal from emp group by deptno)))
23、求平均薪水的等级最低的部门的部门名称
select dname, grade,t1.deptno,avg_sal from
(
select deptno, avg(sal) avg_sal ,grade from
(select deptno, avg(sal) avg_sal from emp group by deptno ) t//表t是每个部门的平均工资
join
salgrade s on(t.avg_sal between losal and hisal)//表t与表s连接求出部门平均工资所在的等级
)t1//该表t1现在求出每个部门的平均工资和平均工资等级 部门编号 但是没有部门名称因此需要在连接一个表
join dept on (t1.deptno=dept.deptno)//至此求出了每个部门的平均薪水 薪水等级 部门编号 部门名称 但是薪水等级不是最低
//因此需要再次求出薪水等级最低的那个等级号码
where t1.grade=
(
select min(grade) from//从上面的表t1便可以了,但是不能直接写t1,后面可以用创建视图的方法简化
(
select deptno ,avg(sal) avg_sal,grade from
(select deptno, avg(sal) avg_sal from emp group by deptno ) t
join
salgrade s on(t.avg_sal between losal and hisal)
)
)
24、创建视图, 虚表保存部门平均工资 部门编号，平均工资等级
create view v$_dept_avg_sal_info as
select deptno, avg(sal) avg_sal ,grade from
(select deptno, avg(sal) avg_sal from emp group by deptno ) t
join
salgrade s on(t.avg_sal between losal and hisal)
则上面23的查询语句可以改为：
select dname,grade, t1.deptno,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)
24、求部门经理人中平均薪水最低的部门名称
25、求比普通员工最高薪水还高的经理人名称
首先求出普通员工的最高薪水
select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null)
再求出经理人名称
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)
)
26、 创建新用户
backup scott 备份scott用户的所有表 数据资源
exp 导出
create user liuchao identified by liuchao default tablespace users quota 10M on users
//用户名密码均为liuchao 默认表空间users
grant create session，create table,create vision to liuchao//给予登录 创建表、创建视图权限
imp 导入
27、回滚
rollback
28、复制表
create table dept2 as select * from dept;
29、求薪水最高的前五名雇员
SQL> select empno,ename from emp;
EMPNO ENAME ROWNUM(这个字段是默认隐藏的给的是行号)
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
EMPNO ENAME
---------- ----------
7900 JAMES
7902 FORD
7934 MILLER
取前五行，但是只能<或者<= 不能用>或者>= 或者=设计原因不详
select empno,ename from emp where rownum<=5
取第十行
select empno,empname from
(
select rownum r,ename,empno from emp
)where r>=10;
将工资由高到低排序
select ename,sal from emp order by sal desc;
select ename,sal from
(
select ename,sal from emp order by sal desc
)where rownum<=5;
30、求薪水最高的第六个到第十个人
首先下面语句中R值还是原来的值，并不因为新的查询结果而更新R的值
SQL> select ename,sal,rownum r from emp order by sal desc;
ENAME SAL R
---------- ---------- ----------
KING 5000 9
FORD 3000 13
SCOTT 3000 8
JONES 2975 4
BLAKE 2850 6
CLARK 2450 7
ALLEN 1600 2
TURNER 1500 10
MILLER 1300 14
WARD 1250 3
MARTIN 1250 5
ENAME SAL R
---------- ---------- ----------
ADAMS 1100 11
JAMES 950 12
SMITH 800 1
只有这样才会重新更新行号 很坑爹啊
SQL> select ename,sal ,rownum r from(select ename,sal from emp order by sal desc);
ENAME SAL R
---------- ---------- ----------
KING 5000 1
FORD 3000 2
SCOTT 3000 3
JONES 2975 4
BLAKE 2850 5
CLARK 2450 6
ALLEN 1600 7
TURNER 1500 8
MILLER 1300 9
WARD 1250 10
MARTIN 1250 11
ENAME SAL R
---------- ---------- ----------
ADAMS 1100 12
JAMES 950 13
SMITH 800 14
因此这是可以求第六行到第十行
SQL> select ename,sal from
2 (
3 select ename,sal ,rownum r from(select ename,sal from emp order by sal desc)
4 )where r>=6 and r<=10;
ENAME SAL
---------- ----------
CLARK 2450
ALLEN 1600
TURNER 1500
MILLER 1300
WARD 1250
31、创建表
create table stu
(
sno number(6) not null,
sname varchar2(20),
sex number(1),
age number(3),
sdate date,
grade number(2) default 1
classno number(4),
email varchar2(50)
);
32、oracle数据字典表
- user_tables
- user_views
- user_constraints
存储数据字典表的表dictionary
SQL> select table_name from dictionary;查询字典表
SQL> select table_name from user_tables;查询表名
SQL> select view_name from user_views;查询视图名
SQL> select constraint_name from user_constraints;查询约束名。
SQL> select constraint_name,table_name from user_constraints;查询约束和约束加在那张表上
33、序列sequence
创建论坛的发帖纪录表：
SQL> create table article(
2 id number,
3 title varchar2(1024),
4 content long
5 );
SQL> create sequence S_ARTICLE_ID
2 minvalue 1
3 maxvalue 9999999999999999999999
4 start with 1
5 increment by 1
6 nocache;
SQL> insert into article values (S_ARTICLE_ID.nextVal,‘title‘,‘content‘);
SQL> select * from article;
ID TITLE CONTENT
--------------------------------------------------------------------------------
1 title content