站长之家html模板,点播视频服务器,wordpress用户中心商城,网站建设需求发布Oracle系列《一》#xff1a;简单SQL与单行函数 使用scott/tiger用户下的emp表和dept表完成下列练习#xff0c;表的结构说明如下 emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号) dept部门表(deptno部门编号…Oracle系列《一》简单SQL与单行函数 使用scott/tiger用户下的emp表和dept表完成下列练习表的结构说明如下 emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号) dept部门表(deptno部门编号/dname部门名称/loc地点) 工资 薪金 佣金 登录Oracle数据库 1、sqlplus scott/tiger 2、sqlplus /nolog SQLconn scott/tiger若是使用SYS的账号进行登录的话则使用以下语句 SQLconn / as sysdba 【1】EMP表内容查询 SQL SELECT * FROM emp; 出错原因是没有找到该表因为该表时SCOTT用户的表所以查询时应该加上scott.emp就可以了 【2】显示当前用户 SQL show user 【3】查看当前用户的所有表 SQL SELECT * FROM tab; 【4】若想重复执行上一条SQL语句则在sqlplus命令行下输入/即可 【5】查询一张表的结构例如dept表 SQL desc dept 【6】在雇员表中查询雇员的编号、姓名、工作 SQL SELECT empno,ename,job FROM emp; 【7】可以为列名取别名在Linux下Oracle如果英文别名不加上双引号则会变成大写 SQL SELECT empno 编号,ename 姓名,job 工作 FROM emp; 【8】查询所有的工作 SQL SELECT DISTINCT job FROM emp; 工作可能会重复加上DISTINCT关键字 去重复值 【9】若要求按照以下的格式进行结果输出如 NO:7469,Name:SMITH,Job:CLERK SQL SELECT NO:||empno||,Name:||ename||,Job:||job FROM emp; 【10】要求列出每个雇员的姓名及年薪 SQL SELECT ename,sal*12 income FROM emp; 这里年薪最好用别名进行标识可以一眼就能明白 【11】查看每月可以得到奖金的雇员信息 SQL SELECT * FROM emp WHERE comm is NOT NULL; 【12】要求基本工资大于1500同时可以领取奖金的雇员信息 SQL SELECT * FROM emp WHERE sal1500 AND comm is NOT NULL; 如果是或的是关系则使用 OR 【13】查询基本工资不大于1500同时不可以领取奖金的雇员信息 SQL SELECT * FROM emp WHERE NOT(sal1500 AND comm is NOT NULL); 【14】查询在1981年雇佣的全部雇员信息BETWEEN .. AND 包含等于的情况 SQL SELECT * FROM emp WHERE hiredate BETWEEN 01-JAN-81 AND 31-DEC-81; 【15】Oracle对大小敏感所以查询时名字要区分大小写 【16】要求查询出雇员编号不是 7369、7499的雇员信息 SQL SELECT * FROM emp WHERE empno NOT IN(7369,7499); 【17】SQL中LIKE语句要注意通配符 % 和 _ SQL SELECT * FROM emp WHERE hiredate LIKE %81%; 【18】查看雇员编号不是7369的雇员信息使用或! SQL SELECT * FROM emp WHERE empno7369; 【19】要求对雇员的工资由低到高进行排序升序为默认(ASC)降序(DESC) SQL SELECT * FROM emp GROUP BY sal desc; 【20】查看出部门号为10的雇员信息查询的信息按照工资从高到低若工资相等则按雇用日期从早到晚排列 SQL SELECT * FROM emp WHERE deptno10 ORDER BY sal DESC,hiredate ASC; 数据库系统中每个数据库之间区别最大的就是在函数的支持上单行函数是最简单的函数单行函数分为 1、字符函数接受字符输入并且返回字符或数值 2、数值函数接受数值输入并返回数值 3、日期函数对日期型数据进行操作 4、转换函数将一种数据类型转换为另一种数据类型 5、通用函数NVL、DECODE 函数 字符函数 【1】大小写转换 UPPER 和 LOWER SQL SELECT UPPER(smith) FROM dual; 【2】将雇员姓名变为开头字母大写INITCAP SQL SELECT INITCAP(ename) FROM emp; 字符函数中有连接函数CONCAT但不如 || 好用还有字符串处理的一些函数 字符串截取substr() 字符串长度length() 内容替换replace() SQL SELECT substr(hello,1,3),length(hello),replace(hello,l,x) FROM dual; 这里注意的是Oracle中字符串截取从0和从1开始都是一样的谨防面试提问 【3】要求显示所有雇员的姓名及姓名的后3个字符 SQL SELECT ename,SUBSTR(ename,LENGTH(ename)-2) FROM emp; 以上操作显得较为麻烦substr()函数是可以倒着截取 SQL SELECT ename,SUBSTR(ename,-3,3) FROM emp; 数值函数 1、四舍五入ROUND() 2、截断小数位TRUNC() 3、取余(取模)MOD SQL SELECT ROUND(789.536) FROM dual; 【1】保留2位小数,(如果是-2则对整数进行四舍五入变为800了) SQL SELECT ROUND(783.56,2) FROM dual; 【2】使用MOD()函数进行取余操作 SQL SELECT MOD(10,3) FROM dual; 日期函数 1、日期 - 数字 日期 2、日期 数字 日期 3、日期 - 日期 数字(天数) 【1】求出当前日期 SQL SELECT SYSDATE FROM dual; Oracle提供【2】求出从雇用日期到今天所有雇员的雇员编号、姓名和月数 SQL SELECT 了以下的日期函数支持 MONTHS_BETWEEN():求出给定日期范围的月数 ADD_MONTHS():在指定日期上加上指定的月数求出之后的日期 NEXT_DAY():下一个的今天的日期 LAST_DAY():求出给定日期的最后一天日期 empno,ename,ROUND(MONTHS_BETWEEN(SYSDATE,hiredate)) FROM emp; 【3】验证 ADD_MONTHS()、NEXT_DAY()、LAST_DAY() SQL SELECT ADD_MONTHS(SYSDATE,4) FROM DUAL; SQL SELECT NEXT_DAY(SYSDATE,MON) FROM DUAL; SQL SELECT LAST_DAY(SYSDATE) FROM DUAL; 转换函数 1、TO_CHAR() 将日期或数值转换成字符串 2、TO_NUMBER()将字符串转换成数字 3、TO_DATE() 将字符串转换成日期 【1】将年月日进行分开要指定拆分的通配符,yyyy-mm-dd SQL SELECT empno,ename,TO_CHAR(hiredate,yyyy) datetime FROM emp; 【2】将薪水的数字进行格式化$99,999表示美元L99,999表示当地货币 SQL SELECT empno,ename,TO_CHAR(sal,99,999) salary FROM emp; 【3】TO_NUMBER()验证 SQL SELECT TO_NUMBER(123)TO_NUMBER(123) FROM DUAL; 【4】TO_DATE()验证如下例子执行后显示为 11-JUL-11 SQL SELECT TO_DATE(2011-7-11,yyyy-mm-dd) FROM DUAL; 通用函数 【1】求出每个雇员的年薪(应算上奖金) SQL SELECT empno,ename,(salcomm)*12 FROM emp; 由于comm中有NULL,NULL值计算后还是NULL,正确如下 SQL SELECT empno,ename,NVL(comm,0),(salNVL(comm,0))*12 income FROM emp; NVL可以理解为将NULL值转换为具体的内容这里是0 【2】DECODE()函数该函数类似于 IF ... ELSEIF...ELSE 语法如下 DECODE(col/expression,选择1,结果1[选择2结果2...,默认]) 验证DECODE()函数 SQL SELECT empno,ename,hiredate, DECODE(job,CLERK,业务员,SALESMAN,销售人员,’MANAGER,经理,ANALYST,分析员,PRESIDENT,总裁) 职位 FROM emp; SQL简单语句练习 【1】找出佣金高于薪金的60%的员工 SQL SELECT * FROM emp WHERE commsal*0.6 【2】找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料 SQL SELECT * FROM emp WHERE (deptno20 AND jobMANAGER) OR (deptno10 AND jobCLERK); 【3】找出既不是经理又不是办事员但其薪金大于或等于2000的所有员工的资料 SQL SELECT * FROM emp WHERE job NOT IN(MANAGER,CLERK) AND sal 2000; 【4】找出有奖金的员工的不同国祚 SQL SELECT DISTINCT job FROM emp WHERE comm IS NOT NULL; 【5】找出各月倒数第3天受雇的所有员工 SQL SELECT * FROM emp WHERE LAST_DAY(hiredate)-2hiredate; 【6】找出早于12年前受雇的员工 SQL SELECT * FROM emp WHERE MONTHS_BETWEEN(sysdate,hiredate)/12 12; 【7】显示刚好为5个字符的员工的姓名 SQL SELECT ename FROM emp WHERE length(ename)5; 【8】显示不带有R的员工的姓名 SQL SELECT ename FROM emp WHERE ename NOT LIKE %R%; 【9】显示员工的姓名和受雇日期将最老的员工排在最前 SQL SELECT * FROM emp Order BY hiredate; 【10】显示所有员工的姓名加入公司的年份和月份按受雇日期所在月排序若月份相同则按年份排序 SQL SELECT ename,TO_CHAR(hiredate,yyyy) year,TO_CHAR(hiredate,mm) month FROM emp ORDER BY month,year; 【11】找出在2月受聘的员工 SQL SELECT * FROM emp WHERE TO_CHAR(hiredate,mm)2; 【12】以年月日方式显示所有员工服务年限 SQL SELECT ename,TRUNC(MONTHS_BETWEEN(sysdate,hiredate)/12) year, TRUNC(MOD(MONTHS_BETWEEN(sysdate,hiredate),12)) month, TRUNC(MOD(sysdate-hiredate,30)) day FROM emp; Oracle系列《二》多表复杂查询和事务处理 多表查询应该注意去除笛卡尔积一般多个表时会为表起个别名 【1】要求查询雇员的编号、姓名、部门编号、部门名称及部门位置 SQL SELECT e.empno,e.ename,d.deptno,d.dname,d.loc FROM emp e,dept d WHERE e.deptno d.deptno; 【2】要求查询每个雇员的姓名、工作、雇员的直接上级领导的姓名(表自关联) SQL SELECT e.ename,e.job,m.ename FROM emp e,emp m WHERE e.mgr m.empno; 【3】对【2】进行扩充将雇员所在部门名称同时列出 SQL SELECT e.ename,e.job,m.ename,d.dname FROM emp e,emp m,dept d WHERE e.mgr m.empno AND e.deptnod.deptno; 【4】查询每个雇员的姓名、工资、部门名称工资在公司的等级(salgrade)及其领导的姓名所在公司的等级 1先确定工资等级表的内容 SQL SELECT * FROM salgrade; 2查询每个雇员的姓名、工资、部门名称和工资在公司的等级 SQL SELECT e.ename,e.sal,d.dname,s.grade FROM emp e,dept d,salgrade s WHERE e.deptnod.deptno AND e.sal BETWEEN s.losal AND s.hisal; 3查询其领导姓名及工资所在公司的等级 SQL SELECT e.ename,e.sal,d.dname,s.grade,m.ename,m.sal,ms.grade FROM emp e,dept d,salgrade s,emp m,salgrade ms WHERE e.deptno d.deptno AND e.sal BETWEEN s.losal AND s.hisal AND e.mgr m.empno AND m.sal BETWEEN ms.losal AND ms.hisal; 【5】左连接与右连接的概念在等号左边表示右连接反之左连接 查询雇员的编号、姓名及其领导的编号、姓名 SQL SELECT e.empno,e.ename,m.empno,m.ename FROM emp e,emp m WHERE e.mgr m.empno(); 就发现将KING的那条记录也连过来了 SQL1999语法中有如下几种连接(了解) 1、交叉连接CROSS JOIN产生笛卡尔积 SQL SELECT * FROM emp CROSS JOIN dept; 2、自然连接NATURAL JOIN,自动进行关联字段的匹配 SQL SELECT * FROM emp NATURAL JOIN dept; 3、使用USING子句直接关联操作列 SQL SELECT * FROM emp JOIN dept USING(deptno) WHERE deptno30; 4、使用ON子句用户自己编写的条件 SQL SELECT * FORM emp JOIN dept ON(emp.deptno dept.deptno) WHERE deptno30; 5、左连接(左外连接、LEFT (OUTER) JOIN)、右连接(右外连接、RIGHT (OUTER) JOIN) 组函数及分组统计 1、COUNT():求出全部记录数 2、MAX()求出一组中最大值 3、MIN():求出最小值 4、AVG()求出平均值 5、SUM()求和 【1】求出每个部门的雇员数量 SQL SELECT deptno,count(empno) FROM emp GROUP BY deptno; 【2】按部门分组并显示部门的名称及每个部门的员工数 SQL SELECT d.dname,COUNT(e.empno) FROM emp e,dept d WHERE e.deptnod.deptno GROUP BY d.dname; 【3】要求显示平均工资大于2000的部门编号和平均工资 SQL SELECT deptno,AVG(sal) FROM emp WHERE AVG(sal)2000 GROUP BY deptno; 出错WHERE子句中不能出现分组函数的条件要使用HAVING子句 上述语句应该改为如下 SQL SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)2000 【4】显示非销售人员工作名称以及从事同一工作雇员的月工资总和并且要求从事同一工作的雇员月工资合计大于$5000, 输出结果按月工资的合计升序排序 1按工作分组求出非销售人员的月工资总和 SQL SELECT job,SUM(sal) FROM emp WHERE jobSALESMAN GROUP BY job; 2对分组条件进行限制然后进行排序,HAVING子句不能使用别名 SQL SELECT job,SUM(sal) totalSal FROM emp WHERE jobSALESMAN GROUP BY job HAVING SUM(sal) 5000 ORDER BY totalSal; 【3】分组函数可以嵌套使用但是在SELECT列中就不能再出现该分组条件的列名了 SQL SELECT deptno,MAX(AVG(sal)) FROM emp GROUP BY deptno; 出错修改如下 SQL SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno; 【4】查询出比7654工资要高的全部雇员的信息 1首先要查询雇员编号7654的工资 SQL SELECT sal FROM emp WHERE empno7654; 2以上述条件的结果最后后续查询的依据 SQL SELECT * FROM emp WHERE sal(SELECT sal FROM emp WHERE empno7654); 子查询在操作中分为以下三类 1、单列子查询返回的结果是一列的内容 2、单行子查询返回多个列也可能是一条记录 3、多行子查询返回多个记录 【1】要求查询工资比7654高同时与7788从事相同工作的全部雇员 SQL SELECT * FROM emp WHERE sal(SELECT sal FROM emp WHERE empno7654) AND job(SELECT job FROM emp WHERE empno7788); 【2】要求查询 部门名称、部门员工数、部门平均工资部门的最低收入雇员的姓名 1查询部门员工数、部门平均工资 SQL SELECT deptno,COUNT(empno),AVG(sal) FROM emp GROUP BY deptno; 2查询部门的名称及最低收入雇员姓名要进行表关联(子查询) SQL SELECT d.dname,ed.c,ed.a,e.ename FROM dept d,( SELECT deptno,COUNT(empno) c,AVG(sal) a,MIN(sal) min FROM emp GROUP BY deptno) ed, emp e WHERE d.deptnoed.deptno AND e.sal ed.min; 若上述存在两个最低工资的情况则会出错在子查询中存在以下3种查询的操作符号 IN指定一个查询范围例如查询每个部门的最低工资(返回值有多个) SQL SELECT * FROM emp WHERE sal IN(SELECT MIN(sal) FROM emp GROUP BY deptno); ANYANY(与IN操作一样)、ANY(比最小大)、ANY(比最大小) SQL SELECT * FROM emp WHERE sal ANY(SELECT MIN(sal) FROM emp GROUP BY deptno); ALL: ALL(比最大要大)、ALL(比最小的小),SQL语句类似上面 SQL多列子查询示例如下 SQL SELECT * FROM emp WHERE (sal,NVL(comm,-1)) IN (SELECT sal,NVL(comm,-1) FROM emp WHERE deptno20); 数据库更新操作INSERT、UPDATE、DELETE 【1】复制一张表例如复制EMP表为MYEMP SQL CREATE TABLE MYTEMP AS SELECT * FROM emp; 【2】将编号为7899的雇员的领导取消 SQL UPDATE myemp SET mgrnull WHERE empno7899; 【3】更新时一定要注意不能批量更新(加上WHERE子句)多列更新例子如下 SQL UPDATE myemp SET mgrnull,commnull WHERE empno IN(7369,8899); 【4】删除掉全部领取奖金的雇员 SQL DELECT FROM emp WHERE comm is NOT NULL; 事务处理 ACID AAtomicity 原子性事务中的操作或者都完成或者都取消 CConsistency 一致性事务中的操作保证数据库中的数据不会出现逻辑上不一致的情况 IIsolation 隔离性当前的事务与其他未完成的事务是隔离的 DDurability 持久性在COMMIT之后数据永久保存在数据库中在此之前事务的操作都可以回滚 验证事务过程 1创建一张临时表只包含部门10 SQL CREATE TABLE emp10 AS SELECT * FROM emp WHERE empno10; 2删除emp10中的7782雇员 SQL DELETE FROM emp10 WHERE empno7782; 再打开另一个窗口发现数据还存在此时如果可以使用以下的两种命令进行事务处理 COMMIT 和 ROLLBACK 提交事务和回滚事务 SQL查询练习 【1】列出至少一个员工的所有部门 SQL SELECT d.*,ed.cou FROM dept d,( SELECT deptno,COUNT(empno) cou FROM emp GROUP BY deptno HAVING COUNT(empno) 1) ed WHERE d.deptnoed.deptno; 【2】列出部门名称和这些部门的员工信息同时列出那些没有员工的部门 SQL SELECT d.deptno,d.dname,e.empno,e.ename FROM dept d,emp e WHERE d.deptno e.deptno(); 【3】列出所有CLERK(办事员)的姓名及其部门名称部门的人数 1关联dept表 SQL SELECT e.ename,d.dname FROM emp e,dept d WHERE e.deptnod.deptno and e.jobCLERK; 2使用GROUP BY 完成部门分组人数 SQL SELECT e.ename,d.dname,ed.cou FROM emp e,dept d,( SELECT deptno,COUNT(empno) cou FROM emp GROUP BY deptno) ed WHERE jobCLERK AND e.deptnod.deptno AND ed.deptnoe.deptno; 转载于:https://www.cnblogs.com/hxv-3670/p/7297498.html
