轻松学SQL--基于Oracle Database 的学习笔记

SQL:结构化查询语言
C R U D: 增删改查

table : name age score

desc+表名 ---> 查询表结构
或者用 describe 命令 (desc是describe的简写)
查询语言：SELECT [DISTINCT] {*,column[alias],...} FROM table;
SELECT identifies what columns FROM identifies which table
******* SELECT 查什么 FROM 从哪里查 *******

SELECT dept_id,last_name,manager_id FROM s_emp;

遇到空值时可以这样操作：
SELECT last_name,title,salary*NVL(commission_pct,0)/100 COMM FROM s_emp;

排除查询时出现重名现象 ，用关键字 DISTINCT
eg: SELECT DISTINCT name FROM s_dept;
排除两个字段连合起来还有重复的现象：
eg: SELECT DISTINCT dept_id,title FROM s_emp;
对查询的结果进行排序用 ORDER BY;
eg: SELECT last_name "姓名",salary "工资" FROM s_emp ORDER BY salary;
(默认是升序排序)
eg: SELECT last_name "姓名",salary "工资" FROM s_emp ORDER BY salary asc;
(asc指定为升序排序)
eg: SELECT last_name "姓名",salary "工资" FROM s_emp ORDER BY salary desc;
(desc指定为降序排序)

总结：order by 排序
asc 升序
desc 降序

select last_name EMPLOYEE,start_date from s_emp order by EMPLOYEE desc;
(在排序的时候可以用别名当做字段[排序的根据]进行排序)--->这里是（EMPLOYEE）。

select last_name,salary*12 from s_emp order by 2;
(这里的2是用来指定字段的位置，这里指向 salary*12 ,即按员工的年薪排序)
select last_name,dept_id,salary from s_emp order by dept_id,salary desc;
(先按dept_id的升序进行排序，排完后再按salary的降序进行排序)
注：这里dept_id后没有直接指排序规则，按默认的升序排序。

WHERE关键字：
SELECT 姓名，部门ID FROM s_emp WHERE dept_id = 42;
(从s_emp表中查出部门ID=42的员工姓名和部门ID)

select last_name,dept_id from s_emp where last_name = ‘Biri’;
(查出员工姓名是Biri的员工的部门ID)
注：这里的Biri是用单[']号括起来的

select last_name,salary from s_emp where salary > 1000 order by salary;
(查出s_emp表中员工工资大于1000的员工的姓名并且按员工工资进行排序)
注：这里是WHERE和BODER BY结合的例子，ORDER BY放在语句的最后。

查两个范围之间的语句有两种：
1.select last_name,salary from s_emp where 1500<salary and salary<2000 order by salary;
2.select last_name,salary from s_emp where salary between 1500 and 2000 order by salary;
(两句都是用来查出工资在1500-2000之间的员工的姓名)
注：第二句用了between ..and..语句，在where之后一定要跟上标准；
between..and..查出的数据是包括限定范围在内的

in的用法：
select last_name,dept_id from s_emp where dept_id in (41,42,43);
(查出部门ID是 41，42，43 的所有员工)

select last_name,COMMISSION_PCT from s_emp where COMMISSION_PCT is null;
(查出s_emp表中COMMISSION_PCT为空的所有员工的姓名)
注：这里用了关键字 ----> is null。

select last_name,COMMISSION_PCT from s_emp where COMMISSION_PCT is not null;
(查出s_emp表中COMMISSION_PCT不为空的所有员工的姓名)

select * from s_emp where last_name like ‘B%’；
(查出s_emp表中姓名首字母为B的所有员工的信息)

补充：不等于的三种表示方式： != 、<>、^=
NOT BETWEEN
NOT IN
NOT LIKE
IS NOT NULL
[%]是通配符 [_]单配符

select last_name from s_emp where last_name like '_e%';
(查出员工姓名第二字符是‘e’的员工)

select table_name from user_Tables where table_name like 'S/_%' escape '/';
(查出数据库中以‘S_’开头的所有表，这里‘_’应用‘/’进行转义，如果不转义就会当做单配符使用)
注：escape关键字用来告诉系统以escape后面的字符做为转义字符。

注意下面两种语句：
select last_name,salary,dept_id,title from s_emp where dept_id=41 and title='Stock Clerk';
查出s_emp表中dept_id=41 且 title='Stock Clerk' 的所有员工
select last_name,salary,dept_id,title from s_emp where dept_id=41 or title='Stock Clerk';
查出s_emp表中dept_id=41 或 title='Stock Clerk' 的所有员工

注意：and 的优先级要比or高
select last_name,salary,dept_id from s_emp where salary >=1000 and dept_id =44 or dept_id=44;
(查出s_emp表中salary>=1000且dept_id=44的员工或dept_id=44的员工[优先级])
select last_name,salary,dept_id from s_emp where salary >=1000 and (dept_id =44 or dept_id=44);
(时间出s_emp表中dept_id=44或dept_id=42的salary>=1000的员工)

查询语句的总结：
SELECT[DISTINCT]{*,column[alias],...} FROM table [WHERE condition(s)] [ORDER BY{column,expralias}[ASC|DESC]];
注：大写部分为关键字，'[]'部分为可选值

函数部分：
function_name(column|expression,[arg1,arg2,...])

转化函数：
LOWER 转小写
UPPER 转大写
INITCAP 首字母大写
CONCAT 字符连接
SUBSTR 字符截取
LENGTH 取字符长度
NVL

select last_name from s_emp where lower(last_name) = 'biri';
(先把表中的数据转化为小写再和指定字段比较)
注：SQL语句不区分大小写，但字段区分大小写，这样转化可以避免大小写问题。

数字函数（number functions）:
ROUND 四舍五入
TRUNC 截取
ROUND(45.923,2)--------->45.92
ROUND(45.923,0)--------->46
ROUND(45.923,-1)-------->50
TRUNC(45.923,2)--------->45.92
TRUNC(45.923)----------->45
TRUNC(45.923,-1)-------->40

日期相关函数：
日期格式：默认格式： DD-MON-RR
SYSDATE 可以返回系统当前时间（oracle用）
select sysdate from dual;
获取oracle系统当前时间
select sysdate+1 from dual;
获取明天日期

MONTHS_BETWEEN
ADD_MONTHS
NEXT_DAY
LAST_DAY
ROUND
TRUNC

`MONTHS_BETWEEN('01-SEP-95','11-JAN-94')---------------------->19.774194
`ADD_MONTHS('11-JAN-94',6)------------------------------------>'11-JUL-94'
`NEXT_DAY('01-SEP-95','FRIDAY')------------------------------->'08-SEP-95'
`LAST_DAY('01-SEP-95')---------------------------------------->'30-SEP-95'
`ROUND('25-MAY-95','MONTH')----------------------------------->'01-JAN-95'
`ROUND('25-MAY-95','YEAR')------------------------------------>'01-JAN-95'
`TRUNC('25-MAY-95','MONTH')----------------------------------->'01-MAY-95'
`TRUNC('25-MAY-95','YEAR')------------------------------------>'01-JAN-95'

类型间的互相转化：
TO_CHAR
TO_NUMBER
TO_DATE
HH24:MI:SS AM

select start_date, last_name from s_emp where to_char(start_date,'mm')='07';
(查出s_emp表中入职月份是07的员工，只要是to_char的用法)
改进：select start_date,last_name from s_emp where to_number(to_char(start_date,'mm'))=7;

ceil 向上取整

等值连接
非等值连接查询
外连接
左外：以左表为基准，即左表中的数据必须全部出现 (+)在右边
右外：以右表为基准，即右表中的数据必须全部出现 (+)在左边
【凡是出现"+"的一边，就有可能出现空值】
自连接
内连接
内连接举例：
select e.last_name, d.name from s_emp e inner join s_dept d on
e.dept_id = d.id where e.last_name = 'Biri';

交叉连接

关联查询语法：
SELECT table.column,table.column FROM table1,table2 WHERE table1.column1=table2.column2;
eg: select last_name,name from s_emp,s_dept where dept_id=s_dept.id and last_name='Biri';
(查出Biri所在部门的名称，这里关联到两个表[s_emp表]和[s_dept表]，
关联条件是 s_emp.dept_id=s_dept.id)
注：这里关联到两个表时，字段表示和字段的顺序一定要弄清楚。

自连接：
select e.last_name 员工名, l.last_name 领导名 from s_emp e,s_emp l where
e.manager_id = l.id and e.last_name = 'Biri';
(查出s_emp表中员工名为Biri的员工的领导的名字)
注：领导也属于员工表的一员，所以本查询语句关联的是s_emp表本身，这里就要把它当作两个表
来使用，取两个别名来区分表，然后再对应表字段。
select e.last_name 员工名, l.last_name 领导名 from s_emp e,s_emp l where
e.manager_id = l.id;
(查出s_emp表中员工对应的领导名，这里有一个缺陷就是可能会少一条数据)
改进：select e.last_name 员工名, l.last_name 领导名 from s_emp e,s_emp l where
e.manager_id = l.id(+) ;
(对上面的语句进行了改进，即当总领导的领导为空时也打印出来，这里其实是左外连接)
标准的SQL语句应该为：
select e.last_name 员工名,l.last_name 领导名 from s_emp e left outer join s_emp l on
e.manager_id = l.id;
(这里使用了语句 "left outer join" 明确表示了左外连接 ，注意后面的where 变成了 on
相应的 右外连接就"right outer join")

GROUP BY语句的语法：
SELECT column,group_function FROM table [WHERE condition]
[GROUP BY group_by_expression] [HAVING group_condition]
[ORDER BY column];

GROUP BY 语句用来分组，HAVING 用来过滤。
GROUP BY 的组函数
AVG(DISTINCT|ALL|n)
COUNT(DISTINCT|ALL|expr|*)
MAX(DISTINCT|ALL|expr)
MIN(DISTINCT|ALL|expr)
SUM(DISTINCT|ALL|n)

select avg(salary) from s_emp;
(查出s_emp表中所有员工的平均工资，这里用到了AVG组函数)

select min(salary) , max(salary) , sum(salary)
from s_emp
group by dept_id;
(以dept_id为标准来查询s_emp表中所有员工的最小、最大和工资之和，此句显示的结果是
按dept_id的不同进行分组显示)

注意：观察下面两条语句
1、select dept_id min(salary) , max(salary) , sum(salary)
from s_emp
group by dept_id;
2、select last_name ,min(salary) from s_emp;
这两条语句中 第一条 可以执行 而第二条执行不了，原因是，第一条是组函数而第二条是单函数，
这是由SQL的语法规则而定的

select e.dept_id,min(e.salary),max(e.salary),sum(e.salary),d.name
from s_emp e,s_dept d
where e.dept_id = d.id
group by dept_id,d.name;
(以dept_id和s_dept表中的name为标准来查询s_emp表中所有员工的最小、最大、工资之和及部门名称)

select dept_id,count(*) "Number" from s_emp group by dept_id;
(按dept_id[部门ID]来统计s_emp表中每个部门有多少个员工[即统计相同的dept_id出现的次数])
注：dept_id可以和组函数count一起使用的原因是，这里是把dept_id做为了分组的条件

------求除41部门以外各个部门的最小，最高，工资之和，部门名
方法一：select e.dept_id,min(e.salary) ,max(e.salary),sum(e.salary),d.name
from s_emp e,s_dept d where e.dept_id=d.id and dept_id !=41
group by dept_id,d.name order by sum(salary) desc;
方法二：select e.dept_id,min(e.salary) ,max(e.salary),sum(e.salary),d.name
from s_emp e,s_dept d where e.dept_id=d.id
group by dept_id,d.name having dept_id!=41
order by sum(salary) desc;
(方法二用到关键字having ，having 必须和group by一起使用，having 的主要功能是过滤
即保留having后条件成立的内容)
注：having子句，是对分组之后的数据进行过滤

-----求增均工资高于1500的各个部门的最小，最高，工资之和，部门名
select e.dept_id,min(e.salary) ,max(e.salary),sum(e.salary),d.name
from s_emp e,s_dept d where e.dept_id=d.id
group by dept_id,d.name having avg(salary)>1500
order by avg(salary) desc;

子查询：
-------查出s_emp表中工资最低的员工
select last_name from s_emp where salary=(select min(salary) from s_emp);
(Oracle认可的一个子查询语句，可以简单的理解为查询语句的嵌套)
注：这里"="后的语句只能返回一个结果，如果返回多个结果就会出错
-------查出平均工资低于32部门的部门ID
select dept_id ,avg(salary) from s_emp group by dept_id
having avg(salary)<(select avg(salary) from s_emp where dept_id = 32);
注：这里用到了组函数、group by、having 子句 还有子查询

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/myron_mao/archive/2009/11/25/4870335.aspx