自己写的笔记,尽供参考,不适合当查询手册使用,有兴趣的可以慢慢读一下,总会有点收获的。
以下例子以下面三个表为基础:
雇员表:雇员号
雇员名
工种
雇员的经理人编号
雇员的入职日期
薪水
津贴
所属的部门编号
部门表: 部门编号
部门名称
部门位置
薪水等级表: 级别
最低钱
最高钱
1.在建表的最后写上 constraint c1 unique(name,number) 表示name和number的组合不能重复。
2.被参考对象必须是主键。
3.两个表的连接:
SELECT title, artist FROM album JOIN track
ON (album.asin=track.album)
WHERE song = 'Alison'
4.dual就一个字段,是用来做纯数字计算时用的表
如:select 2*3 from dual;
5.当前时间:
select sysdate from dual;
6.别名:
slecet name,薪水*12 年薪 from 雇员表; //在这里年薪就是别名
slecet name,薪水*12 “年薪 年薪” from 雇员表; //加了引号别名中就可以有空格或特殊字符了
7.任何含有空值的数学表达式都是空值。
8.字符串连接:
select 姓名||薪水 from 雇员表;
slecct 姓名||‘(中文名)’ from 雇员表;
如果想加的字符串中已经有一个单引号的话,用两个单引号代替一个单引号。
如:想添加中文‘名 :select 姓名|| ’中文’‘名‘ from 雇员表;
9.消除重复:
select distinct 薪水 from 雇员表;
select distinct 薪水,姓名 from 雇员表; //表示月薪和姓名的组合重复的去掉,
10.排序:
select * from 雇员表 where 薪水 >100 order by 薪水 desc; //降序排序,不加desc是升序
11.日期与字符串转换:
select 姓名 from 雇员表 where 入职日期 > to_date( '1981-2-20 12:34:56', 'YYYY-MM-DD HH24:MI:SS');
12.字符串转换成数字:
select 薪水 from 雇员表 where 薪水 > to_number('$1,250,00', '$9,999.99');
13.数字转换成字符:
slect to_char(avg(薪水),'999999.99') from 雇员表; //这样会保留小数点2位
14.如果想4舍5入:
select round(avg(薪水),2) from 雇员表;
15.处理空值:
selecet 薪水*12 + nvl(津贴,0) from 雇员表; //因为这里津贴有一部分是空值,所以如果不加nvl的话空值的
那一部分的结果将为空值。意思是如果津贴为空值的话用0来替代。
16.最大,最小,平均值,总和,个数:
select max(薪水) from 雇员表;//也可以用min(薪水),ave(薪水),sum(薪水),count(*)
******以上为分组函数,也就是多组输入,只有一组数据输出,如果有可能多行数据输出的话就不能用
分组函数了。
17.有分组函数时查询的A如果不在组函数中,就应在group by语句中。
select A,mmax(薪水) from 雇员表 group by A;
18.
select count(distinct 薪水) from 雇员表;
19.分组计算:
select avg(薪水) from 雇员表 group by 部门编号;
select avg(薪水) from 雇员表 group by 部门编号,经理人;
//表示按照部门编号和经理人的组合来进行分组。
20.子查询,解决上面*的问题:
select 姓名 from 雇员表 where 薪水 = (select max(薪水) from 雇员表);
21.对分组进行限制:
select avg(月薪) from 工资表 group by 工资表 having avg(月薪) > 2000;
22.求工资是按照部门分组后里面工资最高的人的姓名:
select 姓名 from 工资表 where 薪水 = (select max(薪水) from 雇员表 group by 部门编号);
//******这是错的,因为子select返回的不是一行,而是多行
//******也不能这样写:elect 姓名 from 雇员表 where
薪水 in (select max(薪水) from 雇员表
group by 部门编号);
//******因为在这里只要薪水一样就要了,而没说部门编号也一样
//******解决办法:select 姓名 from 雇员表
join (select max(薪水) max_薪水,部门编号 group by 部门编号) t
on (工资表.薪水 = t.max_薪水 and 雇员表.部门编号 = t.部门编号);
23.自连接:
求各职员的经理人:
select e1.姓名, e2.姓名 from 雇员表 e1,雇员表 e2 where e1.经理人编号 = e2.雇员号;
尽量写成:select e1.姓名, e2.姓名 from 雇员表 e1 join 雇员表 e2 on(e1.经理人编号 = e2.雇员号);
//KING是最高管理者,KING没有上司,所以KING是不显示的
24.左外连接:
让KING显示的方法:
select e1.姓名, e2.姓名 from 雇员表 e1 left join 雇员表 e2 on(e1.经理人编号 = e2.雇员号);
//左外连接会把左边那张表里不能连接的数据拿出来
25.全外连接:
select e1.姓名, e2.姓名 from 雇员表 e1 full join 雇员表 e2 on(e1.经理人编号 = e2.雇员号);
26.新规范:where里只写数据过滤条件。
slect 姓名,部门名 from 雇员表,部门表;
尽量写成 slect 姓名,部门名 from 雇员表 cross join 部门表;//cross join就是交叉连接,也就是笛卡尔乘机
slect 姓名,部门名 from 雇员表,部门表 where 姓名.部门号 = 部门表.部门号;
尽量写成 slect 姓名,部门名 from 雇员表 join 部门表 on (姓名.部门号 = 部门表.部门号);
上面的写法等同于 slect 姓名,部门名 from 雇员表 join 部门表 using (部门号);
//但不推荐,因为两个表都要有部门号,而且必须类型也相等,要求过多。
27.连接三个表:
select 姓名,部门名,级别 from
雇员表 e join 部门表 d on (e.部门号 = d.部门号)
join 薪水级别 s on (e.薪水 between s.最低钱 and s.最高钱)
where 姓名 not like '_A%';
28.
slect 部门号,平均工资,工资级别 from
(select 部门号,avg(薪水) 平均工资 from 雇员表 group by 部门表) t
join 薪水等级表 s on (t.平均工资 between s.最低钱 and s.最高钱);
29.不准用组函数,求薪水的最高值:
select distinct 薪水 from 雇员表 where 薪水 not in
(select distinct e1.薪水 from 雇员表 e1 join 雇员表 e2 on (e1.薪水 < e2.薪水 ));//也可以用差集
30.求平均薪水最高的部门的部门编号:
select 部门号,平均薪水 from
(select avg(薪水) 平均薪水, 部门号 from 雇员表 group by 部门号)
where 平均薪水 =
(select max(平均薪水) from
(select avg(薪水) 平均薪水,部门号 from 雇员表 group by 部门号)
);
以下例子以下面三个表为基础:
雇员表:雇员号
雇员名
工种
雇员的经理人编号
雇员的入职日期
薪水
津贴
所属的部门编号
部门表: 部门编号
部门名称
部门位置
薪水等级表: 级别
最低钱
最高钱
1.在建表的最后写上 constraint c1 unique(name,number) 表示name和number的组合不能重复。
2.被参考对象必须是主键。
3.两个表的连接:
SELECT title, artist FROM album JOIN track
ON (album.asin=track.album)
WHERE song = 'Alison'
4.dual就一个字段,是用来做纯数字计算时用的表
如:select 2*3 from dual;
5.当前时间:
select sysdate from dual;
6.别名:
slecet name,薪水*12 年薪 from 雇员表; //在这里年薪就是别名
slecet name,薪水*12 “年薪 年薪” from 雇员表; //加了引号别名中就可以有空格或特殊字符了
7.任何含有空值的数学表达式都是空值。
8.字符串连接:
select 姓名||薪水 from 雇员表;
slecct 姓名||‘(中文名)’ from 雇员表;
如果想加的字符串中已经有一个单引号的话,用两个单引号代替一个单引号。
如:想添加中文‘名 :select 姓名|| ’中文’‘名‘ from 雇员表;
9.消除重复:
select distinct 薪水 from 雇员表;
select distinct 薪水,姓名 from 雇员表; //表示月薪和姓名的组合重复的去掉,
10.排序:
select * from 雇员表 where 薪水 >100 order by 薪水 desc; //降序排序,不加desc是升序
11.日期与字符串转换:
select 姓名 from 雇员表 where 入职日期 > to_date( '1981-2-20 12:34:56', 'YYYY-MM-DD HH24:MI:SS');
12.字符串转换成数字:
select 薪水 from 雇员表 where 薪水 > to_number('$1,250,00', '$9,999.99');
13.数字转换成字符:
slect to_char(avg(薪水),'999999.99') from 雇员表; //这样会保留小数点2位
14.如果想4舍5入:
select round(avg(薪水),2) from 雇员表;
15.处理空值:
selecet 薪水*12 + nvl(津贴,0) from 雇员表; //因为这里津贴有一部分是空值,所以如果不加nvl的话空值的
那一部分的结果将为空值。意思是如果津贴为空值的话用0来替代。
16.最大,最小,平均值,总和,个数:
select max(薪水) from 雇员表;//也可以用min(薪水),ave(薪水),sum(薪水),count(*)
******以上为分组函数,也就是多组输入,只有一组数据输出,如果有可能多行数据输出的话就不能用
分组函数了。
17.有分组函数时查询的A如果不在组函数中,就应在group by语句中。
select A,mmax(薪水) from 雇员表 group by A;
18.
select count(distinct 薪水) from 雇员表;
19.分组计算:
select avg(薪水) from 雇员表 group by 部门编号;
select avg(薪水) from 雇员表 group by 部门编号,经理人;
//表示按照部门编号和经理人的组合来进行分组。
20.子查询,解决上面*的问题:
select 姓名 from 雇员表 where 薪水 = (select max(薪水) from 雇员表);
21.对分组进行限制:
select avg(月薪) from 工资表 group by 工资表 having avg(月薪) > 2000;
22.求工资是按照部门分组后里面工资最高的人的姓名:
select 姓名 from 工资表 where 薪水 = (select max(薪水) from 雇员表 group by 部门编号);
//******这是错的,因为子select返回的不是一行,而是多行
//******也不能这样写:elect 姓名 from 雇员表 where
薪水 in (select max(薪水) from 雇员表
group by 部门编号);
//******因为在这里只要薪水一样就要了,而没说部门编号也一样
//******解决办法:select 姓名 from 雇员表
join (select max(薪水) max_薪水,部门编号 group by 部门编号) t
on (工资表.薪水 = t.max_薪水 and 雇员表.部门编号 = t.部门编号);
23.自连接:
求各职员的经理人:
select e1.姓名, e2.姓名 from 雇员表 e1,雇员表 e2 where e1.经理人编号 = e2.雇员号;
尽量写成:select e1.姓名, e2.姓名 from 雇员表 e1 join 雇员表 e2 on(e1.经理人编号 = e2.雇员号);
//KING是最高管理者,KING没有上司,所以KING是不显示的
24.左外连接:
让KING显示的方法:
select e1.姓名, e2.姓名 from 雇员表 e1 left join 雇员表 e2 on(e1.经理人编号 = e2.雇员号);
//左外连接会把左边那张表里不能连接的数据拿出来
25.全外连接:
select e1.姓名, e2.姓名 from 雇员表 e1 full join 雇员表 e2 on(e1.经理人编号 = e2.雇员号);
26.新规范:where里只写数据过滤条件。
slect 姓名,部门名 from 雇员表,部门表;
尽量写成 slect 姓名,部门名 from 雇员表 cross join 部门表;//cross join就是交叉连接,也就是笛卡尔乘机
slect 姓名,部门名 from 雇员表,部门表 where 姓名.部门号 = 部门表.部门号;
尽量写成 slect 姓名,部门名 from 雇员表 join 部门表 on (姓名.部门号 = 部门表.部门号);
上面的写法等同于 slect 姓名,部门名 from 雇员表 join 部门表 using (部门号);
//但不推荐,因为两个表都要有部门号,而且必须类型也相等,要求过多。
27.连接三个表:
select 姓名,部门名,级别 from
雇员表 e join 部门表 d on (e.部门号 = d.部门号)
join 薪水级别 s on (e.薪水 between s.最低钱 and s.最高钱)
where 姓名 not like '_A%';
28.
slect 部门号,平均工资,工资级别 from
(select 部门号,avg(薪水) 平均工资 from 雇员表 group by 部门表) t
join 薪水等级表 s on (t.平均工资 between s.最低钱 and s.最高钱);
29.不准用组函数,求薪水的最高值:
select distinct 薪水 from 雇员表 where 薪水 not in
(select distinct e1.薪水 from 雇员表 e1 join 雇员表 e2 on (e1.薪水 < e2.薪水 ));//也可以用差集
30.求平均薪水最高的部门的部门编号:
select 部门号,平均薪水 from
(select avg(薪水) 平均薪水, 部门号 from 雇员表 group by 部门号)
where 平均薪水 =
(select max(平均薪水) from
(select avg(薪水) 平均薪水,部门号 from 雇员表 group by 部门号)
);