SQL的类型
- DML(Data Manipulation Language 数据操作语言): insert update delete select
- DDL(Data Definition Language 数据定义语言): create/alter/drop/truncate table
create/drop view,sequence,index,synonym(同义词) - DCL(Data Control Language 数据控制语言）： grant(授权) revoke（撤销权限）
DDL
- 创建表:
create table tname(c1 t1 default v,c2 t2,,);
- 子查询:
create table ttemp as select * from emp where 1=2;(不要数据)
create table ttemp as select * from emp;(要数据)
- 修改表:
alter table tname add col type;--追加列
alter table tname modify col newtype;--修改类的类型
alter table tname drop column col; --删除列
alter table tname rename column cname to cnewname;--修改列名
rename tname to tnewname--修改表名
- 删除表:
drop table tname; 把表放入回收站
truncate table tname;把表删除重建
- 表的重构（去掉因删除造成的碎片）:
alter table emp move
- 回收站:
select * from user_recyclebin; 查看回收站
purge recyclebin; 清空回收站
select * from "回收站的表名";可以看到数据
flashback table tname to before drop;--恢复表（闪回）
DML
- insert语句
insert into dept(deptno,dname) values(70,'oracle');
- 通过子查询插入数据
insert into tname select .. from tname2;
- update语句
update dept set dname='c++' where deptno=80;
- delete语句
delete from dept where deptno=80;
delete 和truncate区别
- delete 逐条删除，truncate先删除表，再重建
- delete是DML(可以回滚) truncate是DDL（不可以回滚）
- delete 不会释放空间；truncate会
- delete会产生碎片 truncate不会
- delete可以闪回(flashback) truncate不可以
基本查询
- 当前用户
- show user;
- 用户下所有的表
- select * from tab;
- 列的别名
- col as "别名" （col as 别名）
- col "别名" （原名显示）
- cal 别名 （自动转大写，不能和oracle关键字冲突）
- 列的别名最好用双引号括起来
- 数据字典
- select * from v$nls_parameters;
- 修改日期格式
- alter session set NLS_DATE_FORMAT='yyyy-mm-dd';
- between and
- 1.含边界 2. 小值在前 大值在后
- 查询薪资大于等于1000并且小于等于2000的员工信息
- select * from emp where sal between 1000 and 2000;
- 去掉重复行
- select distinct deptno from emp;
- 连接字符串 concat() or ||
- select concat('your name:',ename) from emp;
- select 'your name:'||ename from emp;
- 模糊查询like 和转义字符 escape (%零或多个，_一个)
- 查询名字A打头的员工信息
- select * from emp where ename like 'A%';
- 查询名字第二个字母是L的员工信息
- select * from emp where ename like '_L%';
- 查询出名字中带_字符的信息
- select * from emp where name like '%{_%' ESCAPE '{'
- null条件查询 is null 或 is not null
- select * from emp where COMM is null; 没有奖金的员工
- null值最大,所以desc的时候,null排在最前面
- select * from emp order by comm desc nulls last
- desc 查看对象的定义信息
- desc dept; 查看表
Oracle中的函数查询
基本格式：
- select 函数名 from dual;
数字函数
round(number,小数位数正负都行) 四舍五入
- round(125.315) would return 125
trunc(number,小数位数) 截断
- trunc(-125.815, 2) would return -125.81
字符函数
- substr(a,b) 从a中，第b为开始
- substr(a,b,c) 从a中，第b开始，取c个
- length(a) 字符长度
- lengthb(a) 字节长度
- instr(a,b) 在a中查询b的位置
- lpad(a,n,c) 左填充
- rpad(a,n,c) 右填充
- trim('H' from 'HsssH') 去掉前后指定的字符
- replace('hello','l','*') 替换
日期函数
- sysdate:系统时间（格式），格式化显示时间
- select to_char(sysdate,'yyyy-mm-dd') from dual;
- systimestamp:系统时间戳 ：精度更高，格式化显示时间
- select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss:ff') from dual;
日期的运算:计算员工工龄
- months_between (sysdate,hiredate)月
- sysdate-hiredate 天
- 加月 add_months(sysdate,n) n可正负
- 月最后一天 last_day(sysdate)
- 下一个星期: next_day(sysdate,'星期一')下个星期一的日期
- 四舍五入 针对日期函数:round(to_date ('22-AUG-03'),'YEAR') would return '01-JAN-04'
- 截断 针对日期函数:trunc(to_date('22-AUG-03'), 'YEAR') would return '01-JAN-03'
转换函数
- number--> character to_char(sal,'L9,999.99') 人民币格式字符串
- select to_char(sal,'L9,999.99') from emp;
- character--->number to_number('99.99')
- select to_number('99.99') from dual;
- date---->character to_char(sysdate,'yyyy-mm-dd hh24:mi:ss "今天"day')
- select to_char(sysdate,'yyyy-mm-dd') from dual;
- character-->dateto_date('2016-11-23','yyyy-mm-dd')
- select to_date('2016-11-13','yyyy-mm-dd') from dual;
其他函数
nvl(e1,e2) e1为null时返回e2
nvl2(e1,e2,e3) e1为null 返回e3 否则返回e2
nullif(a,b) 当a=b的时候 返回null 否则返回a
coalesce(e1,e2,e3,,,) 从左到右 找第一个不为null的值
-
decode函数
语法：decode(exp,ser1,res1,ser2,res2,,,,defualt);
案例：decode(job,'manager',sal+1000,,,,,sal+100);
decode函数的原理是case语句：
语法： case exp when com_epr then return_exp when com_epr then return_exp when com_epr then return_exp else return_exp end; 案例： select sal 涨前, case job when 'manager' then sal + 100 when .... else sal+300 end 涨后
-
行转列wm_concat()
- select deptno,wm_concat(ename) from emp group by deptno;
-
exists函数,是否有记录的函数
- select * from dept where exists(select 1 from emp where emp.deptno=dept.deptno)
分组查询
按列或者函数分组，分组后select后面只能跟分组之后的列，或聚合函数，或子查询
--统计每年入职的员工人数
select to_char(hiredate,'yyyy') y, count(1) from emp group by to_char(hiredate,'yyyy');
--每个部门的平均工资
select avg(sal),deptno from emp group by deptno;
多表查询
--等值连接
select * from emp,dept where emp.deptno = dept.deptno;
--不等值连接
select * from emp,dept where emp.deptno <> dept.deptno;
--内连接
select * from emp e inner join dept d on e.deptno = d.deptno;
--外连接
--左外连接
select * from emp e left join dept d on e.deptno = d.deptno;
--左外连接
select * from emp e , dept d where e.deptno = d.deptno(+);
--右外连接
select * from emp e right join dept d on e.deptno = d.deptno;
--右外连接
select * from emp e , dept d where e.deptno(+) = d.deptno;
--全外连接
select * from emp e full join dept d on e.deptno = d.deptno;
自连接不适合操作大表
Oracle中的事务
- 起始标志：事务中第一题DML语句
- 结束标志： 提交： 显式 commit
隐式 正常退出exit，DDL，DCL
回滚 显式 rollback
隐式 非正常退出，掉电，宕机
--定义保存点
savepoint a;
—回滚到a;
rollback to savepoint a;
oracle分页
select *
from (select rownum r,e1.*
from (select * from emp order by sal) e1
where rownum <=8
)
where r >=5;
子查询
注意的问题
- 括号
- 合理的书写风格
- 可以在主查询的where select having from后面放置子查询
- 不可以在group by后面放置子查询
- 强调from后面的子查询
- 主查询和子查询可以不是同一张表；只要子查询返回的结果主查询可以使用即可
- 一般不在子查询中排序；但在Top-N分析问题中，必须对子查询排序
- 一般先执行子查询，再执行主查询；但相关子查询例外
- 单行子查询只能使用单行操作符；多行子查询只能使用多行操作符
- 子查询中的null
可以在主查询的where select having from后面放置子查询
select empno,ename,sal,(select job from emp where empno=7839) 第四列
2 from emp;
EMPNO ENAME SAL 第四列
---------- ---------- ---------- ---------
7369 SMITH 800 PRESIDENT
7499 ALLEN 1600 PRESIDENT
7521 WARD 1250 PRESIDENT
7566 JONES 2975 PRESIDENT
7654 MARTIN 1250 PRESIDENT
7698 BLAKE 2850 PRESIDENT
7782 CLARK 2450 PRESIDENT
7788 SCOTT 3000 PRESIDENT
7839 KING 5000 PRESIDENT
7844 TURNER 1500 PRESIDENT
7876 ADAMS 1100 PRESIDENT
强调from后面的子查询
查询员工信息：员工号 姓名 月薪
select * 2 from (select empno,ename,sal from emp);
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500
7876 ADAMS 1100
EMPNO ENAME SAL
---------- ---------- ----------
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300
in 在集合中
查询部门名称是SALES和ACCOUNTING的员工
select *
from emp
where deptno in (select deptno from dept where dname='SALES' or dname='ACCOUNTING');
any 和集合中任意一个值比较
查询工资比30号部门任意一个员工高的员工信息
select *
from emp
* where sal > any (select sal from emp where deptno=30)
===
select *
from emp
* where sal > (select min(sal) from emp where deptno=30)
all: 和集合中的所有值比较
查询工资比30号部门所有员工高的员工信息
select *
from emp
where sal > all (select sal from emp where deptno=30);
===
select *
from emp
* where sal > (select max(sal) from emp where deptno=30)
集合运算
union 并集：两个集合取并集，自动排序，去掉重复行
select ename,sal,deptno from emp where sal > 1000
union
select ename,sal,deptno from emp where deptno = 30;
union all 并集：两个结合取并集，不排序，不去掉重复行
select ename,sal,deptno from emp where sal > 1000
union all
select ename,sal,deptno from emp where deptno = 30;
intersect 交集：第一个集合减第二个集合
select ename,sal,deptno from emp where sal > 1000
intersect
select ename,sal,deptno from emp where deptno = 30;
minus 差集：第一个集合减第二个集合
select ename,sal,deptno from emp where sal > 1000
minus
select ename,sal,deptno from emp where deptno = 30;
SQL> 注意的问题:
SQL> 1. 参与运算的各个集合必须列数相同 且类型一致
SQL> 2. 采用第一个集合表头作为最后表头
SQL> 3. order by 永远在最后
SQL> 4. 括号
SQL> select deptno,job,sum(sal) from emp group by deptno,job
2 union
3 select deptno,sum(sal) from emp group by deptno
4 union
5 select sum(sal) from emp;
select deptno,sum(sal) from emp group by deptno
*
第 3 行出现错误:
ORA-01789: 查询块具有不正确的结果列数
SQL> select deptno,job,sum(sal) from emp group by deptno,job
2 union
3 select deptno,to_char(null),sum(sal) from emp group by deptno
4 union
5 select to_number(null),to_char(null),sum(sal) from emp;
order by 后面 + 列，表达式，别名，序号
select empno,ename,sal,sal*12
from emp
order by sal*12 desc;
select empno,ename,sal,sal*12 年薪
from emp
* order by 年薪 desc
select empno,ename,sal,sal*12 年薪
from emp
* order by 4 desc
select empno,ename,sal,sal*12 年薪
from emp
* order by 5 desc
SQL> /
order by 5 desc
*
第 3 行出现错误:
ORA-01785: ORDER BY 项必须是 SELECT-list 表达式的数目
多个列的排序
order by 作用于后面所有的列，先按照第一个列排序，如果相同，再按照第二列排序；以此类推
select *
from emp
order by deptno,sal;
desc 只作用于离他最近的一列
select *
from emp
* order by deptno desc,sal desc
用desc的时候
如果有null会被排在上面,因为null最大
select *
from emp
order by comm desc
* nulls last
表的高级管理
create table t_student(t_id integer,t_sex char(2),t_age integer);
create table t_teacher(tc_id integer primary key,tc_name varchar2(10));
--给学生表添加外键列
alter table t_student add tc_id integer;
--主键约束
alter table t_student add constraint pk_t_student_t_id primary key(t_id);
--唯一性约束
alter table t_student add constraint unique_stu_name unique(t_name);
--非空约束
alter table t_student modify t_name not null;
--外键约束
alter table t_student add constraint fk_stu_tc_id_tea foreign key(tc_id) references t_teacher(tc_id);
--检查约束
alter table t_student add constraint ck_age check(t_age between 1 and 200);
alter table t_student add constraint ck_sex check(t_sex in ('男','女'));
SQL优化原则
- 理论上，尽量使用多表查询
- 尽量不要使用集合运算
- 尽量使用列名代替*
- where顺序： 右--》 左
SQL中的null
- 包含null的表达式都为null
- SQL> 2. null永远!=null
- 如果集合中，含有null，不能使用not in；但是可以使用in
- 如果要查询的话用exists函数代替in
- select * from dept where exists(select 1 from emp where emp.deptno=dept.deptno)
表的三大范式
- 所有的列都是不可分割的最小单元
- 所有的非主键列都完全依赖主键列
- 非主键列之间不存在传递依赖
数据库设计原则
- 分析系统中所有的数据（对象，表）
- 确定数据与数据之间的关系（一对一，一对多，多对多）
