sql 语句实现简单查询和复杂查询

数据分组

数据分组是通过Group by 字句，分组函数以及having 字句共同实现的，其中group by 用于指定分组列，分组函数用于显示统计结果（count,avg,sum）,having用于限制分组显示结果

 

一般情况下，分组函数与group by 字句结合使用

注意事项：

1 分组函数只能出现在选择列表、order by子句、having 子句中

2 当使用分组函数时，会忽略null行

3 如果在选择列表中既包括了分组函数，也包括了其他的列和表达式，那么这些列或表达式必须出现在group by 子句中

4 当使用分组函数时可以在函数中指定All和Distinct(没有重复值)

5 当限制分组限制结果时，必须使用having 子句而不能在where子句中使用分组函数

下面是一些常用的分组函数：

1. Min 和Max 适用任何数据类型

Select max(sal) 最高工资，min(sal) 最低工资from tableName;

2. avg 和sum avg用于取得列或者表达式的平均值，sum用于取得列或者表达式的总和

Select avg(sal) 平均工资，sum(sal) 总计工资 from tableName;

3. Count 用于取得总行数 select count(*) 雇员总数 from tableName;

Group by & having

Group by 用于对查询结果进行分组统计，Having 用于限制分组显示结果（如果选择列表同时包含有列，表达式和分组函数，那么这些列和表达式必须出现在group by 子句里）

Select column group_function from table

[where condition]

[group by group_by_expression]

[having group_condtion]

[order by expression]      其中where 为指定条件子句，group by 用于指定分组列 having 用于限制分组显示结果，order by 用于排序数据

1         select deptno 部门代码，avg(sal) from tableName

group by deptno;

2         显示每个部门每种岗位的平均工资和最高工资

Select deptno,job avg(sal),max(sal) from emp

Group by deptno, job;

3         显示平均工资低于2500的部门号，平均工资以及最高工资

select deptno, avg(sal), max(sal) from emp

group by deptno

having avg(sal) <2500; 

4         rollup 在保持原来的统计结果的同时还会生成横向小计和总计，cube在保持原来的统计结果的同时还会横向小计，纵向小计，和总计

 

 

连接查询

相等连接

不等连接

例如 salgrade 表存放着工资级别信息，下面显示的是所有雇员的名称，工资及其工资级别

Select a.enname,a.sal,b.grad from emp a, salgrade b

Where a.sal between b..losal and b.hisal;

 

内连接和外连接

 

内连接用于返回满足连接条件的记录，而外连接则是内连接的扩展，返回满足连接条件的所有记录，而且还返回不满足连接条件的记录

语法： select table1.column,table2.column

       From table1 [INNER | LEFT | RIGHT | FULL] JOIN table2

       ON table1.column1 = table2.column2;

 

内连接(oracle 9i 主键和外键关联 natural主动内连接)

Select a.dname,b.ename from dept a, emp b

Where a.deptno = b.deptno AND a.deptno = 10;

或者： select a.dname, b.ename from dept a INNER　JOIN emp b

       On a..deptno = b.deptno AND a.deptno = 10;

 

子查询

当在DDL语句中适用子查询时，可以带有order by 字句但是如果在where 子句，set 字句中使用子查询，不能带Order by 子句

子查询具有一下的作用：

通过在Insert 或Create table 语句中使用子查询，可以将源表数据追加到目标表

通过在create view 或Create materialized view 中使用子查询，可以定义视图或者实体化视图所对应的select 语句

通过在UPDATE 语句中使用子查询可以修改一列或多列的数据

通过在where,having,start with 子句中使用子查询，可以提供条件值

 

单行子查询：（=，>,<,>=,<=,<>）

显示和Scott 相同部门的雇员姓名，工资，部门号

Select ename, sal deptno from emp where deptno=

(select deptno from emp where ename=”scott”);

多行字查询：(IN,ALL,ANY 与单行比较符一起使用)

显示匹配于部门10岗位的雇员名，岗位，工资，部门号（in 会处理匹配于子查询任一个值的行） select ename, job, sal ,deptno from emp where job in

        (selelct distinct job from emp where deptno=10);

显示高于部门号30的所有雇员工资的雇员名，工资和部门号（all 操作符必须与单行操作符结合使用，并且返回行必须要匹配于所有子查询结果）

Select ename, sal deptno, from emp where sal>all

（select sal from emp where deptno=30）

多行子查询

查询日期：select ename,to_char(hiredate,’yyyy-mm-dd’)from emp;

处理空值问题：select ename,sal comm.,sal+nvl(coum,0)from emp;

连接字符串（||）如果要在字符串中要加入数字值，那么在||后可以直接指定数字;如果要在字符串中加入字符和日期值，则必须用单引号

例如连接雇员名及其岗位 select ename||’的岗位是’||job ‘employee’ From emp;

限制数据

1、 在where子句中使用数字值，即可以使用单引号也可以直接使用

显示工资高于2000的雇员 select ename ,sal , from emp where sal>2000;

2、 在where 子句中使用字符值，必须使用单引号，字符值区分大小写。显示岗位为MANAGER的所有雇员名和工资select ename,sal,from emp where job = “MANAGER”

3、 在where 子句中使用日期，必须使用单引号并且日期值必须符合默认日期显示格式和日期语言，如果日期不符合默认日期显示格式，必须需要to_date函数，select ename,sal,hiredate from emp where hiredate>’01-1月-82’;

4、 在where 中使用between….and,[like(%||_)]

5、 Where 子句中使用In 操作符 IN 操作符用于执行列表匹配操作。 显示岗位为CLERK和MANAGE的所有雇员为例 select ename,sal ,job, from emp where job in(‘CLERK’’ CLERK’);

6、 在where 子句中使用 IS NULL 操作符 IS NULL 操作符用于检测列或表达式的结果是否为NULL

7、 在where 中使用and

8、 在where 中使用or 操作符