oracle中查询:子查询,连接查询
来源:互联网 发布:java逆序输出数组 编辑:程序博客网 时间:2024/06/08 12:25
--序列:sequence
--作自动增长的主键
--创建序列,初始值1,增长的步长默认是1
CREATESEQUENCE stu_seq;
CREATESEQUENCE test_seq
STARTWITH1 --初始值
INCREMENTBY5 --步长为5
MAXVALUE2000 --最大值2000
MINVALUE1 --最小值
NOCYCLE --不循环nocycle达到最大值不循环,报错,循环cycle,达到最大值是否循环,
CACHE20 --在内存的缓冲中存放序列的个数
--访问序列的下一个值和当前值,dual是oracle中自带的一张虚表
SELECT stu_seq.nextval,stu_seq.currvalFROM dual;
SELECT test_seq.nextvalFROM dual;
CREATETABLE usrinfo(
userid NUMBERPRIMARYKEY,
NAMEVARCHAR2(20)
);
--将userid作为自动增长的主键,使用序列的nextval进行访问
INSERTINTO usrinfovalues(stu_seq.nextval,'tom');//掌握
--删除序列
DROPSEQUENCE test_seq;
--数据库表中的几种约束
--主键约束;PRIMARY key,唯一性,并且不能为空
--唯一约束:Unique key,唯一性,允许有null值,只能有一个null值
--外键约束;foreign key,建立和另外一张主表之间的关联,外键字段的值必须要参照主表中主键的值
--非空约束:NOT NULL
--检查约束:check
CREATETABLE depts(
deptno NUMBER(2)PRIMARYKEY,
dname VARCHAR2(20),
loc VARCHAR2(20)
);
CREATETABLE emps(
empno NUMBER(5),
ename VARCHAR2(20)NOTNULL,
JOB VARCHAR2(20),
MGR NUMBER(5),
HIREDATE DATEDEFAULTSYSDATE,--default:设置字段的默认值,默认为系统日期
SAL NUMBER(10,2)CHECK(sal>100),--check约束
comm NUMBER(10,2),--表示数字位最大长度为7,其中小数位为2,整数位最大为5
deptno NUMBER(2),
idcard VARCHAR2(18)UNIQUECHECK(LENGTH(idcard)=18),--检查身份证号码长度是否为18位
CONSTRAINT fk_deptnosFOREIGNKEY(deptno)references depts(deptno),--外键约束
CONSTRAINT pk_empnosPRIMARYKEY(empno)--主键约束
);
DROPTABLE emps;//删除表,连带数据库中的表数据信息一块删除,delete只删除外在的表
INSERTINTO empsALUES(1234,'honey','clerk',NULL,SYSDATE,3000,500,NULL,'123456789456123045');
INSERTINTO empsVALUES(1250,'toney','sale',NULL,SYSDATE,3500,500,NULL,'189456234567123045');
INSERTINTO emps(empno,ename)VALUES(1236,'soney');
INSERTINTO deptsVALUES(1,'开发部','无锡');
INSERTINTO empsVALUES(1235,'honey','clerk',NULL,SYSDATE,3000,500,1,'456712389456123045');
SELECT *FROM emps;
SELECT *FROM depts;
--删除部门1的信息
--先删从表(外间所在的表),再删主表(关联的主键表)
DELETEFROM empsWHERE deptno=1;
DELETEFROM deptsWHERE deptno=1;
--Group by子句使用:对分组后的数据进行统计(结合聚合函数使用)
--在分组语句查询中,只有group by后面的字段才可以出现在select子句中,
--单行的目标不能和聚合函数放在一起使用
--查询各个部门的所有员工总工资、最高工资、最低工资、平均工资、部门人数
SELECT deptno,SUM(sal),MAX(sal),MIN(sal),AVG(sal),COUNT(*)
FROM emp
GROUPBY deptno;
--查询各工作岗位的所有员工总工资、最高工资、最低工资、平均工资、部门人数
SELECT job,SUM(sal),MAX(sal),MIN(sal),AVG(sal),COUNT(*)
FROM emp
GROUPBY job;
--查询各部门从事不同工作岗位的人数
SELECT deptno,job,COUNT(*)
FROM emp
GROUPBY deptno,job --按照多个字段分组,多个字段之间用逗号(‘,’)隔开
ORDERBY deptno; --按照部门编号升序排序
--对分组后的数据进行再次筛选,要使用Having子句
--不能使用where子句,而且在where子句中不能使用聚合函数
--查询各部门总工资高于10000的部门编号和总工资
SELECT deptno,SUM(sal)
FROM emp
GROUPBY deptno
HAVINGSUM(sal)>10000;
--order by子句:排序默认升序asc降序desc
--查询所有员工的信息,按照部门编号升序排列、工资降序排序
SELECT *
FROM emp
ORDERBY deptno,salDESC;
--连接查询
--查询员工编号、员工姓名、员工工资、员工所在部门名称
--等值连接(内连接);选择两张表中互相匹配的记录
--第一种,较常用,一般都是用这种方法
SELECT empno,ename,sal,dname,emp.deptno
FROM emp,dept
WHERE emp.deptno = dept.deptno;
--第二种方法,不常用
SELECT empno,ename,sal,dname,emp.deptno
FROM emp
INNERJOIN dept
ON emp.deptno = dept.deptno;
--左连接(left join):包含左边表中的所有记录,甚至是右边表中没有和它匹配的记录
--侧重点:查看的是所有员工的信息,即使员工没有信息
SELECT empno,ename,sal,dname,emp.deptno
FROM emp,dept
WHERE emp.deptno = dept.deptno(+);--该方法在oracle中可以使用,在SQLServer中不可以使用
SELECT empno,ename,sal,dname,emp.deptno
FROM emp,dept
LEFTJOIN dept
ON emp.deptno = dept.deptno;
--右连接(right join):包含右边表中的所有记录,甚至是左边表中没有和它匹配的记录
--侧重点:查看的是部门的信息,即使部门中没有员工
SELECT empno,ename,sal,dname,emp.deptno
FROM emp,dept
WHERE emp.deptno(+) = dept.deptno;
SELECT empno,ename,sal,dname,emp.deptno
FROM emp,dept
RIGHTJOIN dept
ON emp.deptno = dept.deptno;
--全连接(full join):左右表中的所有记录都会显示
SELECT empno,ename,sal,dname,emp.deptno
FROM emp,dept
FULLJOIN dept
ON emp.deptno = dept.deptno;
--自身连接:自己表和自己表作连接,通常会对表指定别名的方式来实现
--查询所有比SMITH员工工资高的员工工号、员工姓名、员工工资以及SMITH的工资
SELECT other.empno,other.ename,other.sal othersal,smith.sal smithSal
FROM emp other,emp smith
WHERE smith.ename ='SMITH'
AND other.sal > smith.sal;
--子查询
--查询比JAMES工资高的员工信息
SELECT empno,ename,job,sal
FROM emp
WHERE sal>(SELECT sal
FROM emp
WHERE ename='JAMES'
);
--查询工作岗位与7369相同,并且工资高于7876的所有员工信息
SELECT empno,ename,job,sal
FROM emp
WHERE job = (SELECT job
FROM emp
WHERE empno =7369)
AND sal >(SELECT sal
FROM emp
WHERE empno =7876);
--查询工作岗位与7369相同,并且同属一个领导的员工信息
SELECT empno,ename,job,mgr,sal
FROM emp
WHERE (job,mgr) = (SELECT job,mgr
FROM emp
WHERE empno =7499);--条件一致的情况下才可以用到这种写法,可以提高查询效率
--查询比部门30的最低工资还低的部门编号和部门最低工资
SELECT deptno,MIN(sal)
FROM emp
GROUPBY deptno
HAVINGMIN(sal) <(SELECTMIN(sal)
FROM emp
WHERE deptno =30);
--多行的子查询(使用多行运算符:in、ANY、ALL)IN表示在某一个集合范围之内
--查询每个部门中最低工资的员工信息
SELECT empno,ename,sal,deptno
FROM emp
WHERE salIN (SELECTMIN(sal)
FROM emp
GROUPBY deptno);
--显示工作岗位不是CLERK的雇员,并且检查这些雇员的薪水是否低于CLERK雇员中任意一个雇员的薪水
--< ANY:小于列表中的最大值
--> ANY:大于列表中的最小值
SELECT empno,ename,job,sal
FROM emp
WHERE job !='CLERK'
AND sal <ANY (SELECT sal
FROM emp
WHERE job ='CLERK');
SELECT empno,ename,job,sal
FROM emp
WHERE job !='CLERK'
AND sal < (SELECTMAX(sal)
FROM emp
WHERE job ='CLERK');
--显示工作岗位不是SALESMAN的雇员,并且检查这些雇员的薪水是否低于SALESMAN雇员中所有雇员的薪水
--< ALL:小于列表中的最小值
--> ALL:大于列表中的最大值
SELECT empno,ename,job,sal
FROM emp
WHERE job !='SALESMAN'
AND sal <ALL(SELECT sal
FROM emp
WHERE job ='SALESMAN');
SELECT empno,ename,job,sal
FROM emp
WHERE job !='SALESMAN'
AND sal < (SELECTMIN(sal)
FROM emp
WHERE job ='SALESMAN');
--查询没有下属的员工信息(注意NULL值运算)
SELECT empno,ename,job,mgr,sal
FROM emp
WHERE empnoNOTIN (SELECT mgr
FROM emp
WHERE mgrISNOTNULL);
--case表达式使用
SELECT empno,ename,job,
(CASE job
WHEN'CLERK'THEN'普通员工'
WHEN'SALESMAN'THEN'销售人员'
WHEN'MANAGER'THEN'经理'
WHEN'ANALYST'THEN'分析人员'
ELSE'老板'
END
)jobname
FROM emp;
--dual是一个虚表
SELECT *FROM dual;
--转换函数
--to_char(date,format):将日期格式化成字符串
SELECT to_char(SYSDATE,'YYYY"年"MM"月"DD"日" hh24:MI:SS')
FROM dual;
SELECT to_char(hiredate,'yyyy.mm.dd')FROM emp;
--to_date(char,format):将字符串转换为日期
SELECT to_date('2005-12-06','yyyy-mm-dd')FROM dual;
--to_number(char):将数字子串转换为数字
SELECT to_number('100')FROM dual;
--查询2月份入职的员工信息
SELECT *FROM empWHERE to_char(hiredate,'mm') ='02';
--日期函数使用
--add_months(date,count):在指定的日期上添加count个月,返回值是日期类型
SELECT add_months(SYSDATE,3)FROM dual;
--months_between(date1,date2):返回两个日期之间相差的月份
SELECT months_between(SYSDATE,to_date('2012-06-25','yyyy-mm-dd')) differ FROM dual;
--last_day(date):返回指定日期所在月的最后一天的日期
SELECT to_char(last_day(SYSDATE),'yyyy/mm/dd') lastday FROM dual;
--next_day(date,day):返回指定日期的下一个day所在的日期
SELECT next_day(SYSDATE,'星期六') nextday FROM dual;
--trunc(date,format):返回按指定格式截取后的日期
SELECTTRUNC(SYSDATE,'mi')FROM dual;
--greatest(date1,date2,...):返回日期列表中最晚的那个日期
SELECTGREATEST(SYSDATE,to_date('2012-08-01','yyyy-mm-dd')) great FROM dual;
--least(date1,date2,...):返回日期列表中最早的那个日期
SELECTLEAST(SYSDATE,to_date('2012-08-01','yyyy-mm-dd')) le FROM dual;
--两个日期之间相差的天数,round是取整
SELECT (SYSDATE - to_date('2012-08-01 10:02:00','yyyy-mm-dd hh:mi:ss')) daysFROM dual;
--日期和日期之间相加减得到的是数字
--日期和数字之间相加减得到的仍然是日期格式
--查询每个月倒数第二天入职的员工信息
SELECT *FROM empWHERE hiredate = last_day(hiredate) -1;
SELECT *FROM emp;
--查询在15年前入职的员工信息
SELECT *FROM emp
WHERE months_between(SYSDATE,hiredate) >15 *12;
--显示正好为5个字符的员工的姓名
SELECT *FROM emp
WHERELENGTH(ename) =5;
--显示不带有"R"的员工姓名
SELECT *FROM emp
WHEREINSTR(ename,'R') = 0;
--查询所有员工的年薪
SELECT empno,ename,sal,comm,(sal +NVL(comm,0)) *12 totalsal
FROM emp;
NVL(comm,0)--相当于
--decode和case表达式的用法很相似
SELECT empno,ename,deptno,
DECODE(deptno,
10,'第一部门',
20,'第二部门',
30,'第三部门',
'其他部门'
) deptname
FROM emp;
- oracle中查询:子查询,连接查询
- oracle 连接查询,子查询,分组函数
- Oracle: 四、Oracle连接查询,子查询(相关子查询,嵌套子查询)
- Oracle连接查询,子查询(相关子查询,嵌套子查询)
- Oracle中关联子查询
- 连接查询与子查询
- mysql 子查询,连接查询
- 连接查询与子查询
- 聚合函数查询、连接查询、子查询
- 连接查询 子查询 SQL 查询 小记
- 复杂查询(连接查询、子查询)
- oracle子查询嵌套查询
- oracle 分组查询 子查询
- 05-Oracle学习_子查询 和 连接查询
- 06-Oracle学习_练习-子查询 和 连接查询
- oracle之连接查询及子查询举例
- Oracle 多表连接子查询
- Oracle 多表连接子查询
- Android下ListView上下滑动背景变黑
- 垃圾回收的算法与方式
- 行列式_part1
- 数据库总结过程
- Checkbox的checked属性问题
- oracle中查询:子查询,连接查询
- 笔记一
- paip.取回密码功能总结
- Spring MVC环境搭建
- 笔记三
- paip.svn服务服务器安装配置
- 0403-027 The parameter list is too long 参数过长报错
- oracle循环语句
- 如何使用Apache的非默认端口部署wordpress