- 第一课:客户端
- 1. Sql Plus(客户端),命令行直接输入:sqlplus,然后按提示输入用户名,密码。
- 2. 从开始程序运行:sqlplus,是图形版的sqlplus.
- 3. http://localhost:5560/isqlplus
- Toad:管理, PlSql Developer:
- 第二课:更改用户
- 1. sqlplus sys/bjsxt as sysdba
- 2. alter user scott account unlock;(解锁)
- 第三课:table structure
- 1. 描述某一张表:desc 表名
- 2. select * from 表名
- 第四课:select 语句:
- 1.计算数据可以用空表:比如:.select 2*3 from dual
- 2.select ename,sal*12 annual_sal from emp;与select ename,sal*12 "annual sal" from emp;区别,加双引号保持原大小写。不加全变大写。
- 3. select ename || "abcd" 如果连接字符串中含有单引号,用两个单引号代替一个单引号。
- 第五课:distinct
- select deptno from emp;
- select distinct deptno from emp;
- select distinct deptno from emp;
- select distinct deptno ,job from emp
- 去掉deptno,job两者组合的重复。更多的项,就是这么多项的组合的不重复组合。
- 第六课:Where
- select * from emp where deptno =10;
- select * from emp where deptno <>10;不等于10
- select * from emp where ename ='bike';
- select ename,sal from emp where sal between 800 and 1500 (>=800 and <=1500)
- 空值处理:
- select ename,sal,comm from emp where comm is (not) null;
- select ename,sal,comm from emp where ename ( not)in ('smith','king','abc');
- select ename from emp where ename like '_A%';_代表一个字母,%代表0个或多个字母. 如果查询%
- 可用转义字符./%. 还可以用escape '$'比如:select ename from emp where ename like '%$a%' escape '$';
- 第七课: orderby
- select * from dept;
- select * from dept order by dept desc;(默认:asc)
- select ename,sal,deptno from emp order by deptno asc,ename desc;
- 第八课: sql function1:
- select ename,sal*12 annual_sal from emp
- where ename not like '_A%' and sal>800
- order by sal desc;
- select lower(ename) from emp;
- select ename from emp
- where lower(ename) like '_a%';等同于
- select ename from emp where ename like '_a%' or ename like '_A%';
- select substr(ename,2,3) from emp;从第二字符截,一共截三个字符.
- select chr(65) from dual 结果为:A
- select ascii('a') from dual 结果为:65
- select round(23.652,1) from dual; 结果为: 23.7
- select round(23.652,-1) from dual; 20
- select to_char(sal,'$99_999_999') from emp;
- select to_char(sal,'L99_999_999') from emp;人民币符号,L:代表本地符号
- 这个需要掌握牢:
- select birthdate from emp;
- 显示为:
- BIRTHDATE
- ----------------
- 17-12月-80
- ----------------
- 改为:
- select to_char(birthdate,'YYYY-MM-DD HH:MI:SS') from emp;
- 显示:
- BIRTHDATE
- -------------------
- 1980-12-17 12:00:00
- -------------------
- select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual; //也可以改为:HH12
- TO_CHAR(SYSDATE,'YY
- -------------------
- 2007-02-25 14:46:14
- to_date函数:
- select ename,birthdate from emp where birthdate > to_date('1981-2-20 12:34:56','YYYY-MM-DD HH24:MI:SS');
- 如果直接写 birthdate>'1981-2-20 12:34:56'会出现格式不匹配,因为表中的格式为: DD-MM月-YY.
- select sal from emp where sal>888.88 无错.但
- select sal from emp where sal>$1,250,00;
- 会出现无效字符错误.
- 改为:
- select sal from emp where sal>to_number('$1.250.00','$9,999,99');
- 把空值改为0
- select ename,sal*12+nvl(comm,0) from emp;
- 这样可以防止comm为空时,sal*12相加也为空的情况.
- 第九课: Group function 组函数
- max,min,avg ,count,sum函数
- select to_char(avg(sal),'99999999,99') from emp;
- select round(avg(sal),2) from emp;
- 结果:2073.21
- select count(*) from emp where deptno=10;
- select count(ename) from emp where deptno=10; count某个字段,如果这个字段不为空就算一个.
- select count(distinct deptno) from emp;
- select sum(sal) from emp;
- 第十课: Group by语句
- 需求:现在想求,求每个部门的平均薪水.
- select avg(sal) from emp group by deptno;
- select deptno avg(sal) from emp group by deptno;
- select deptno,job,max(sal) from emp group by deptno,job;
- 求薪水值最高的人的名字.
- select ename,max(sal) from emp;出错,因为max只有一个值,但等于max值的人可能好几个,不能匹配.
- 应如下求:
- select ename from emp where sal=(select max(sal) from emp);
- Group by语句应注意,
- 出现在select中的字段,如果没出现在组函数中,必须出现在Group by语句中.
- 第十一课: Having 对分组结果筛选
- Where是对单条纪录进行筛选,Having是对分组结果进行筛选.
- select avg(sal),deptno from emp
- group by deptno
- having avg(sal)>2000;
- 查询工资大于1200雇员,按部门编号进行分组,分组后平均薪水大于1500,按工薪倒充排列.
- select * from emp
- where sal>1200
- group by deptno
- having avg(sal)>1500
- order by avg(sal) desc;
- 第十二课:字查询
- 谁挣的钱最多(谁:这个人的名字, 钱最多)
- select 语句中嵌套select 语句,可以在where,from后.
- 问那些人工资,在平均工资之上.
- select ename,sal from emp where sal>(select avg(sal) from emp);
- 查找每个部门挣钱最多的那个人的名字.
- select ename ,deptno from emp where sal in(select max(sal) from ename group by deptno) 查询会多值.
- 应该如下:
- select max(sal),deptno from emp group by deptno;当成一个表.语句如下:
- select ename, sal 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);
- 每个部门的平均薪水的等级.
- 分析:首先求平均薪水(当成表),把平均薪水和另外一张表连接.
- 第十四课:self_table_connection
- 把某个人的名字以及他的经理人的名字求出来(经理人及这个人在表中同处一行)
- 分析:首先求出这个人的名字,取他的编号,然后从另一张表与其相对应编号,然后找到经理的名字.
- select e1.ename ,e2.ename from emp e1,emp e2 where e1.mgr= e2.empno.
- empno编号和MGR都是编号.
- 第十15课: SQL1999_table_connections
- select ename,dname,grade from emp e,dept d, sqlgrade 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标准)
- 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);等同上句,但不推荐使用.
- 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);
- 16-23 课:求部门平均薪水的等级
- A.求部门平均薪水的等级。
- select deptno,avg_sal,grade 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)
- B.求部门平均的薪水等级
- select deptno,avg(grade) from
- (select deptno,ename, grade from emp join salgrade s on(emp.sal between s.losal and
- s.hisal)) t
- group by deptno
- C.那些人是经理
- select ename from emp where empno in(select mgr from emp);
- select ename from emp where empno in(select distinct mgr from emp);
- D.不准用组函数,求薪水的最高值(面试题)
- select distinct sal from emp where sal not in(
- select distinct e1.sal from emp e1 join emp e2 on (e1.sal<e2.sal));
- E.平均薪水最高的部门编号
- select deptno,avg_sal 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)
- )
- F.平均薪水最高的部门名称
- 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)
- )
- )
- G.求平均薪水的等级最低的部门的部门名称
- 组函数嵌套
- 如:平均薪水最高的部门编号,可以E.更简单的方法如下:
- 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)
- 组函数最多嵌套两层
- 分析:
- 首先求
- 1.平均薪水: select avg(sal) from group by deptno;
- 2.平均薪水等级: 把平均薪水当做一张表,需要和另外一张表连接salgrade
- 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)
- 上面结果又可当成一张表。
- DEPTNO GRADE AVG_SAL
- -------- ------- ----------
- 30 3 1566.66667
- 20 4 2175
- 10 4 2916.66667
- 3.求上表平均等级最低值
- 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.hisa)
- )
- 4.把最低值对应的2结果的那张表的对应那张表的deptno, 然后把2对应的表和另外一张表做连接。
- select dname ,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 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)
- )
- )
- 结果如下:
- DNAME DEPTNO GRADE AVG_SAL
- -------- ------- -------- --------
- SALES 30 3 1566.6667
- H: 视图(视图就是一张表,一个字查询)
- G中语句有重复,可以用视图来简化。
- conn sys/bjsxt as sysdba;
- grant create table,create view to scott;
- conn scott/tiger
- 创建视图:
- 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 9t.avg_sal between s.losal and s.hisal)
- 然后
- select * from v$_dept_avg-sal_info
- 结果如下:
- DEPTNO GRADE AVG_SAL
- -------- ------- ----------
- 30 3 1566.66667
- 20 4 2175
- 10 4 2916.66667
- 然后G中查询可以简化成:
- select dname,t1.deptno,grade,avg_sal from
- v$_dept_avg-sal_info t1
- join dept on9t1.deptno =dept.deptno)
- where t1.grade=
- (
- select min(grade) from v$_dept_avg-sal_info t1
- )
-
PLSQL语言(一)
最新推荐文章于 2021-04-08 08:53:15 发布