SQL笔记

自己写的笔记，尽供参考，不适合当查询手册使用，有兴趣的可以慢慢读一下，总会有点收获的。


以下例子以下面三个表为基础：
雇员表：雇员号
                雇员名
                工种
                雇员的经理人编号
                雇员的入职日期
                薪水
                津贴
                所属的部门编号
部门表： 部门编号
                  部门名称
                  部门位置
薪水等级表： 级别
                          最低钱
                          最高钱

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 部门号)
                                  );