Oracle数据库SQL语句

Oracle数据库

//清屏命令
clear src

//处理空值的函数 如果bonus的值是null，则取0;两个参数可以是数字、字符或日期，但参数类型必须一致
select ename,salary,bonus,salary + nvl(bonus,0) month_sal from emp_xxx;

//distinct 去除重复查询,必须(只能)跟在select后边
select distinct job from emp_xxx;

//where 条件查询 SQL语句大小写不敏感,数据大小写敏感 where=WHERE
select * from emp_xxx where salary > 10000;

//lower() 将字符数据转换为小写
select * from emp_xxx where lower(job) = 'analyst';

//upper()将字符数据转换为大写
select * from emp_xxx where upper(job) = 'ANALYST';

//between ... and ... 在区间between 低值 and 高值 都是闭区间
select * from emp_xxx where salary>=5000 and salary<=10000;
== select * from emp_xxx where salary between 5000 and 10000;

//in(列表)
select * from emp_xxx where job = 'Manager' or job = 'Analyst';
== select * from emp_xxx where job in('Manager','Analyst')

//"%"表示0到多个字符，跟like配合使用 "_"下划线表示一个字符
//查询的数据中有特殊字符 做模糊查询时，需要加上\符号表示转义，并且用escape短语指明转义字符
select * from emp_xxx where lower(job) like '%sales%';含有sales字符的员工数据
select * from emp_xxx where job like '_a%';第二个字符是a的员工数据
select count(*) from user_tables where table_name like '%EMP%';数据库中有多少个名字包含'EMP'的表
select count(*) from user_tables where table_name like 'S\_%' escape '\';

//is null 数据库语言判断null值的方法
select * from emp_ning where bonus is null;

//is not null 数据库语言判断不是null值的方法
select * from emp_ning where bonus is not null;

//not between 低值 and 高值
select * from emp_xxx where salary not between 5000 and 8000;

//not in(list):不在列表中 不是部门20和30的员工
select * from emp_xxx where deptno not in(20,30);

SQL语言的分类

数据定义语言 DDL
create 数据库对象的创建
alter 修改数据库对象
drop 删除数据库对象
truncate清空表数据
数据操纵语言 DML
insert 插入操作
update 更新操作
delete 删除操作
数据查询语言 DQL
select 查询操作
事物控制语句 对DML操作进行确认的
commit 提交数据
rollback 数据回滚
savepoint

//round(数字,小数点后的位数)用于数字的四舍五入 s1原样显示 s2保留2位有效数字 s3默认0位有效数字
select ename,salary * 0.1234567 s1,round(salary * 0.1234567) s2,round(salary * 0.1234567) s3 from emp_xxx;

//trunc(数字，小数点后的位数)用于截取 如果没有第二个参数，默认是0
select ename,salar * 0.1234567 s1,round(salar * 0.1234567,2) s2,round(salar * 0.1234567) s3,trunc(salar * 0.1234567,2) s4 from emp_xxx;

//日期函数sysdate dual 为虚表
select sysdate from dual;

//日期相减

得到两个日期的天数差，不足一天用小数表示，可以用round函数处理
select ename,hiredate,(sysdate - hiredate) days from emp_xxx;

//日期函数 months_between() 可以用round函数处理
select ename,hiredate,months_between(sysdate,hiredate) months from emp_xxx;

//日期函数 add_months() 计算12个月之前的时间点
select add_months(sysdate,-12) from dual;

//日期函数 last_day() 计算本月的最后一天
select last_day(sysdate) from dual;

//转换函数 to_char(日期数据，格式):把日期数据转换为字符数据 把时间数据按指定格式输出
格式：
yyyy 四位数字年 2011
year 全拼的年 twenty eleven
month 全拼的月 november或11月(中文)
mm 两位数字月 11
mon 简拼的月 nov(中文没有简拼)
dd 两位数字日
day 全拼的星期 tuesday
dy 简拼的星期 tue
am 上午/下午 am/pm
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;2011-11-01 10:24:43
select to_char(sysdate,'year month dd day dy') from dual;twenty eleven 12月 05 星期一 星期一
select to_char(sysdate,'yyyy/mm/dd') from dual;2011/12/05

//转换函数 to_date() 把字符串数据 按指定格式 转换为 日期数据
insert to emp_xxx(empno,ename,hiredate) value(1024,'amy',to_date('2011-10-10','yyyy-mm-dd'));

//coalesce()函数 返回参数列表中第一个非空参数，参数列表中最后一个值通常为常量
要求：
如果bonus不是null，发年终奖金额为bonus
如果bonus是null，发年终奖金额为salary * 0.5
如果bonus和salary都是null，发100元安慰一下
select ename,bonus,salary,coalesce(bonus,salary * 0.5,100) bonus from emp_xxx;

//case语句 是数据中的分支语句，相当于java中的switch-case语句
要求：
如果职位是Analyst 加薪10%
如果职位是Programmer 加薪5%
如果职位是clerk 加薪2%
其他职位 薪水不变
select ename,salary,job, case job when 'Analyst' then salary * 1.1
when 'Programmer' then salary * 1.05
when 'clerk' then salary * 1.02
else salary //相当于java中case语句的default
end new_salary //end是case语句的结束标识 new_salary 是从 case开始到end结束这部分的别名
from emp_xxx;

//decode函数 是Oracle中等价于case when 语句的函数，作用同case语句相同
//语法：decode(判断条件，匹配1，值1，匹配2，值2，... ，默认值)
要求：
如果职位是Analyst 加薪10%
如果职位是Programmer 加薪5%
如果职位是clerk 加薪2%
其他职位 薪水不变
select ename,salary,job,decode(job,'Analyst',salary*1.1,'Programmer',salary * 1.05,'clerk',salary * 1.02,salary) new_salary from emp_xxx;

//函数的嵌套 f3(f2(f1(p1,p2),p3),p4)

//查询结果排序 order by 排序语句放在查询语句的最后
select ename,salary from emp_xxx order by salary asc; //正序排列，asc可以省略
select ename,salary from emp_xxx order by salary desc; //desc(descend)

降序排列，不可省略

//组函数 count函数忽略空值
select count(*) from emp_xxx;

//组函数 count() avg() sum() max() min() 组函数是多行数据返回一行结果
select sum(salary) from emp_xxx;
要求：
计算员工的人数总和、薪水总和、平均薪水是多少 如果薪水为null 需要转换为0 否则avg只按有薪水的员工计算平均值
select count(*) num,sum(salary) sum_sal,avg(nvl(salary,0)) avg_sal from emp_xxx;

//分组查询 group by 表示按指定列分组查询
//select 后出现的列，凡是没有被数组函数包围的列，必须出现在group by短语中
select job,max(salary) max_s,min(salary) min_s,count(*) emp_num
from emp_xxx
group by job order by emp_num;
要求：
按部门计算每个部门的最高和最低薪水分别是多少
select deptno,max(salary) max_s,min(salary) min_s
from emp_xxx
group by deptno;

//having子句 用于对分组后的数据进行过滤 where是对表中数据的过滤；having是对分组得到的结果数据进一步过滤
要求：
平均薪水大于5000元的部门数据，没有部门的不算在内
select deptno,avg(nvl(salary,0)) avg_s
from emp_xxx
where deptno is not null
group by deptno
having avg(nvl(salary,0)) > 5000;

//子查询
要求：查询最高薪水的是谁
//分步
select max(salary) max_s from emp_xxx;
select ename from emp_xxx where salary = 15000;
//子查询
select ename from emp_xxx where salary = (select max(salary) from emp_xxx);

//查询语句的基本格式
select 字段1，字段2，字段3，表达式，函数，...
from 表名
where 条件
group by 列名
having 带组函数的条件
order by 列名

//字符函数

upper 转换为大写
lower 转换为小写
initcap 转换为首字母大写
length 取长度
lpad 左补丁
rpad 右补丁
replace 字符替换
trim 去除前后的空格
要求：
将ename字段设置为10个长度，如果不够左边用"*"号补齐
select lpad(ename,10,'*') from emp_xxx;

//数字函数 round/trunc/mod
要求：
求salary对5000取模
select salary,mod(salary,5000) from emp_xxx;

//子查询（二）单行比较运算符都只能和一个值比较
需求：
谁的薪水比张无忌高
分步:
select salary from emp_xxx where ename = '张无忌';
select ename from emp_xxx where salary > 10000;
子查询:
select ename from emp_xxx where salary > (select salary from emp_xxx where ename = '张无忌');

//All
需求：查询谁的薪水比所有叫张无忌的薪水都高
select ename,salary from emp_xxx where ename = '张无忌';
select ename from emp_xxx where salary >ALL(select salary from emp_xxx where ename = '张无忌');

//Any
需求：哪些人的薪水比任何一个叫张无忌的员工工资高 大于最小值
select ename from emp_xxx where salary >ANY(select salary from emp_xxx where ename = '张无忌');

//In
需求:谁和刘苍松同部门，列出除了刘苍松之

外的员工名字
分步：
select deptno from emp_xxx where ename = '刘苍松';
select ename from emp_xxx where deptno = 10 and ename <> '刘苍松';
子查询：
select ename,salary,job from emp_xxx
where deptno = (select deptno from emp_xxx where ename = '刘苍松') and ename <> '刘苍松';
需求:谁和刘苍松同部门，列出除了刘苍松之外的员工名字 多个刘苍松
select ename,salary,job,deptno
from emp_xxx where deptno in(select deptno from emp_xxx where ename = '刘苍松')
and ename <> '刘苍松';

//关联子查询 子查询不再是独立的SQL语句 需要依赖主查询传来的参数，这种方式叫关联子查询
需求：那些员工的薪水比本部门的平均薪水低，不再和整个部门的平均薪水比较。
select ename,salary,deptno
from emp_xxx a
where salary < (select avg(nvl(salary,0))
from emp_xxx
where deptno = a.deptno );//子查询不再是独立的Sql语句，需要依赖主查询传来的参数a.deptno

//Exists 关键字
需求：哪些人是其他人的经理(查找有下属的员工)
select ename from emp_xxx a
where exists(select 1 from emp_xxx
where mgr = a.empno);
//exists关键字判断子查询有没有数据返回，有则为ture，没有则为false
//exists不关心子查询的结果，所以子查询中select后面写什么都可以，本例我们写常量'1'
//sql执行顺序从主查询开始，把主查询中的empno数据传入子查询，作为条件中的参数

//集合操作
//合集 union 去重，排序
select ename,salary from emp_xxx
where deptno = 10
union
select ename,salary from emp_xxx
where salary > 6000;

//union all 不去重，不排序
select ename,salary from emp_xxx
where deptno = 10
union all
select ename,salary from emp_xxx
where salary > 6000;

//交集 intersect
select ename,salary from emp_xxx
where deptno = 10
intersect
select ename,salary from emp_xxx
where salary > 6000;

//差集
select ename,salary from emp_xxx
where deptno = 10
minus
select ename,salary from emp_xxx
where salary > 6000;

//表间关联查询
//主键PK 外键FK

//内连接 表1 join 表2 on 条件
需求：列出员工的姓名和所在部门的名字和城市
select ename,dname,location
from emp_xxx e join dept_xxx d
on e.deptno = d.deptno;
//结论 子集中的外键值为null的数据不包含在结果集中

//驱动表和匹配表 表1 join 表2 on 条件
//表1叫做驱动表，表2叫做匹配表
//执行方式 不论谁做驱动表，都回遍历驱动表，在匹配表中查找匹配数据

//外连接
左外连接语法结构 表1 left outer join 表2 on 条件 左表为驱动表
右外连接语法结构 表1 right outer join 表2 on 条件 右表为驱动表
特征：
如果驱动表在匹配表中找不到匹配记录，则匹配一行空行
外连接的结果集 = 内连接的结果集 + 驱动表在匹配表中匹配不上的记录和空值
外

连接的本质是驱动表中的数据一个都不能少

//full outer join 全外连接
把两个表中的记录全部查出来
结果集 = 内连接的结果集 +
驱动表中的匹配表中找不到匹配记录的数据和null值 +
匹配表中在驱动表中找不到匹配记录的数据和null值
驱动表和匹配表可以互换

//SQL语句的种类
1) 数据查询语言 DQL ：select
2) 数据定义语言 DDL ：create/drop/alter/truncate
3) 数据操纵语言 DML ：insert/update/delete
4) 事务控制语言 TCL ：commit/rollback/savepoint
5) 数据控制语言 DCL ：grant/revoke(与用户权限相关)

//复制表 create table 表名 as 查询语句;
需求：只复制结构，不复制数据
create table salgrade_yyy
as
select * from salgrade_xxx
where 1<>1;

//drop删除结构和全部的表数据
drop table 表名;

//truncate保留表结构，删除表中所有数据 不需要提交commit，没有回退rollback的机会 与delete的区别 直接删除，不占用临时空间，不能回退
truncate table 表名;

//alter修改结构 add关键字
需求：增加列
create table mytemp_xxx(id number(4));
alter table mytemp_xxx add(name char(10));
alter table mytemp_xxx add(password char(4));
desc mytemp_xxx

//rename关键字
需求：修改列名password为pwd
alter table mytemp_xxx rename column password to pwd;
desc mytemp_xxx

//modify关键字
需求：修改列的数据类型pwd char(8)
alter table mytemp_xxx modify(pwd char(8));

//drop column
需求：删除列pwd
alter table mytemp_xxx drop column pwd;