【SQL语句分类】 DQL—数据查询语言（SELECT） DML—数据操作语言（INSERT/ UPDATE/ DELETE/ MERGE） DDL—数据定义语言(CREATE/ ALTER/ DROP/ TRUNCATE) DCL—数据控制语言（GRANT/ REVOKE） TCL—事务控制语句（COMMIT/ ROLLBACK/ SAVEPOINT） 【查询基
【SQL语句分类】
DQL—数据查询语言（SELECT）
DML—数据操作语言（INSERT/ UPDATE/ DELETE/ MERGE）
DDL—数据定义语言(CREATE/ ALTER/ DROP/ TRUNCATE)
DCL—数据控制语言（GRANT/ REVOKE）
TCL—事务控制语句（COMMIT/ ROLLBACK/ SAVEPOINT）
【查询基础】
1、使用连接符将属性值连起来：select ename || ' ''s job is '||job as job from emp;
【注：这里要注意s前面的两个单引号，如果使用一个会出现问题】
2、取消重复（distinct） ：select distinct job from emp;
3、取别名：select ename as "姓名" from emp;
4、查询表结构： desc emp;
5、条件查询where + 运算符
(1) between ...and...
(2) in(*, *, *, ....)
(3)like ，用于匹配字符串 （'%' //匹配任意多个字符； '_' //匹配一个字符）
(4) is null
(5) AND, OR, NOT IN/ between/ like/， is not null
6、条件显示查询结果
（1）ORDER ＢＹ： desc(降序)；asc (默认升序) [null升序排在最后]
【函数】
1、字符函数
（1） LOWER(column|expression); UPPER(); INITCAP()//首字母大写
（2） CONCAT,连接两个值，等同于||
CONCAT(column1|expression1,column2|expression2);
（3） SUBSTR(‘String’, 1, 3) = ‘Str’//取从第1个位置开始，len=3的子串
【注：oracle的字符串默认是从第1个位置开始】
（6） LPAD(sal, 10, ‘*’) =******50000; RPAD(); 【左对齐，右对齐】
（7） TRIM(‘S’ FROM ‘SSMITH’) =MITH //去除字符串头尾的字符‘S‘
使用参数：TRIM(LEADING | TAILING | BOTH 'S' FROM 'SSMITH')
LEADING：去除头部字符； TAILING:去除尾部字符； BOTH：头尾都去除
（8） REPLACE(‘abc’. ‘b’, ‘d’) = ‘adc’ //替换
（9） To_char(str, [fm]) 将属性转变成字符串，也可以自己添加转换的格式（具体格式需要看属性的具体类型）
Select ename,to_char(hiredate, ‘yyyy-mm-dd’) as d from emp;
2、数字函数
(1) ROUND(列名|表达式, n), 将值四舍五入到小数点后第n位
(2) TRUNC(列名|表达式, n), 截取到小数点后第n位（不四舍五入）
(3) MOD(列名|表达式, n), 表达式对n取余
3、日期函数
(1)SYSDATE //返回当前时间
select to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') as time from DUAL;
(2)MONTHS_BETWEEN //返回两个日期相隔的月数
select ename,hiredate,
round(months_between(to_date('01-10-3050','dd-mm-yyyy') , hiredate)) as months from emp;
(3)ADD_MONTHS(date, n) //在指定日期的基础上加上相应的月数
(4)NEXT_DAY(date,num)
返回某一个日期的下一个指定日期，求出date之后一周内某天num的日期，num可以是一个有效的表示星期几的数字，1为星期天
也可以直接输入如下所述的数据：NEXT_DAY(date, '星期一')
(5)LAST_DAY(date) //返回date所在月的最后一天
(6) ROUND(date, ['CC' | 'YY' | 'MM' | 'DD' | 'HH24' | 'MI' | 'SS' ])日期的四舍五入，默认格式是DD
(7)TRUNC(date[,’fmt’]) //截取, 默认格式是DD，将date截取为最近的天
(8)EXTRACT:返回日期类型中指定年、月、日
EXTRACT(YEAR | MONTH | DAY from hiredate)
4、转换函数
(1) TO_CHAR(date | number [, ‘fmt’]) 把日期类型、数字类型转换为字符类型
常用日期格式说明：
YYYY：4位数字表示年份
YY：2位数字表示年份，但无世纪转换
RR：2位数字表示年份，有世纪转换
当前年份0-49：指定年份0-49（当前世纪），指定年份50-99（上一个实际）
当前年份50-99：指定年份0-49（下一个世纪），指定年份59-99（当前世纪）
YEAR：年份的英文拼写
MM：2位数字表示月份
MONTH：月份的英文拼写
DY：星期的英文前三位字母
DAY：星期的英文拼写
DD：数字表示一月中的第几天
DDD：数字表示一年中的第几天
AM/ PM：上下午表示
HH/ HH12/ HH24：小时
MI：分钟
SS：秒、
TH：显示数字表示的英文序数词
SP：显示数字表示的拼写
SPTH：显示数字表示的序数词的拼写
数字的具体格式
9：一位数字
$：显示为美元符号
L：显示按照区域设置的本地货币符号
. ：小数点
，：千分割符
【注意：进行数字类型到字符类型转换时，格式中的宽度一定要超过实际列宽度，否则会显示###，小数点后如果小于实际宽度，进行四舍五入处理。】
select ename,to_char(sal,'$99,999.00') as salary from emp;
(2)TO_DATE（） 转换为时间格式
select to_date('2006-10-10','yyyy-mm-dd') + 15 from DUAL;
5、通用函数
【注意：数据格式可以是日期、字符、数字，数据类型必须匹配】
(2)NVL2(expression1, expression2, expression3),
(3)NULLIF(expression1, expression2), 如果两个参数不相等，返回第一个参数，如果相等，返回空
(4)COALESCE(expression1, expression2, …..,expression n), 返回第一个不为空的参数
(5) CASE的使用
selectename, deptno,
( case deptno
when 10 then '部门10'
when20 then '部门20'
when 30 then '部门30'
else '无'
end )dept
from emp;
也可以使用case判断取值范围
selectename, sal,
(case
when sal
when sal > 1000 then '多'
else '刚好'
from emp;
【多表查询】
1、 等值连接
select ename, job from dept, emp where dept.deptno = emp.deptno;
为表格取别名
select ename, job from dept D, emp E where D.deptno = E.deptno;
【注意：如果已经定义了表别名，就只能使用别名，不能使用原名】
2、 外连接
左外连接： select ename, dname from emp, dept where emp.deptno =dept.deptno(+);
右外连接： select ename, dname from emp, dept where emp.deptno(+) =dept.deptno;
【注意：没有+的表，会把内容显示完全】
3、 交叉连接CROSS JOIN（生成笛卡尔积）
select ename, dname from emp CROSS JOIN dept;
4、 自然连接 NATURAL JOIN
【连接条件：两个表中的值和数据类型都相同的同名列，如果列名相同数据类型不同则报错】
5、 USING子句
USING(column_name)
使用条件：两个表中存在两个以上相同的列（若只有1列，自然连接即可）
【注意：不要与自然连接NATURAL JOIN重复使用, 】
select ename, deptno from emp join dept using(deptno); --别忘记添加括号 和 关键字join
6、 ON子句
select ename,dname from emp join dept on emp.deptno = dept.deptno;
7、 左外连接：LEFT OUTER JOIN
select ename, dname from emp left outer join dept on emp.deptno = dept.deptno;
8、 右外连接：RIGHT OUTER JOIN
9、 全连接：FULL OUTER JOIN
select ename, dname from emp full outer join dept on emp.deptno = dept.deptno;
【集合运算】
1、 UNION联合运算
去掉重复行，默认排序
select ename,sal, deptno from emp
UNION
select ename, sal, deptno from emp
where deptno in(10, 20);
2、 UNION ALL
不去重复，默认情况下不排序
【分组函数】
1、 MIN/ MAX
可以对数字，也可以对字符
selectmin(sal), max(sal) from emp;
select min(ename), max(ename) from emp;
2、 SUM/ AVG
select sum(sal), avg(sal) from emp;
3、 COUNT
select count(*)from emp;
4、 DISTINCT取消重复
select count(distinct deptno) from emp;
5、 空值处理问题
select avg(comm)from emp; //空值不算在内
6、 GROUP BY
select deptno,sum(sal) from emp group by deptno;
select job, mgr, avg(sal) from emp group by job, mgr order by job;
【注意：group by字句可以不出现在select中；select子句中的出现的飞分组函数列必须在GROUP BY子句中出现】
7、 HAVING子句（完成组函数筛选判断）
select job, avg(sal) from emp where avg > 1000 group by avg;
(错误，此处不能使用where)
select job, avg(sal) from emp group by job having avg(sal) > 1000;
(改为使用having， having中不能使用别名)
【select的执行过程：FROM--WHERE--GROUP BY--HAVING --SELECT--ORDER BY】
【子查询】
1、 单行子查询：使用单行操作符，where可以出现在having中
2、 多行子查询，多行操作符：IN, ANY, ALL
3、 子查询中有空值
例如：select ename, sal from emp
where empno not in ( select mgr from emp where mgr is not null);
结果为：未选定行
原因：子查询中返回值中包含有空值（恰好NOT IN操作符对空值不忽略）
4、 EXISTS, NOT EXISTS
EXISTS子查询如果有记录找到，子查询语句不会继续进行，返回true
例如：select ename from emp e
where exists(select '1' from emp where manager_id = e.empno)
这里‘1’只是占位用，无实际意义【为表取别名 不能用as】
NOT EXISTS 操作符运算方法与NOT IN不同，只会返回true 或false，不会返回空值，所以不需要考虑子查询中的空值问题
【数据操作DML】
1、 INSERT插入
insert into dept values(60, 'qqq', 'xxx');
insert into dept(deptno, loc) values(70, 'sss');
insert into hhh select * from eee where XXX(条件)
2、UPDATE
update emp set deptno = 10 where ename ='SCOTT';
3、 DELETE 删除
delete from emp where empno = 7788;
delete 不能删除被其他表引用的数据（违反完整性约束条件的数据）
4、 MERGE 合并数据
根据指定条件执行插入或者更新操作。
如果条件满足执行更新操作，否则执行插入操作。
on(T1.a = T2.a)
when matched then
update set T1.b = T2.b
when not matched then
insert(a,b) values(T2.a, T2.b);
5、 ROWID
是表中虚拟的列,包含该行数据的物理位置信息
select rowid, dname from dept;
//AAAMgxAAEAAAAAMAAA qqq
进行快速定位
6、 事务的特征：ACID
原子性Atomicity：独立的工作单元，要么全都成功，要么全都失败
一致性Consistency：一旦事务完成，不管成功还是失败，整个系统处于操作规则的统一状态，即数据不会损坏
隔离性Isolation:不会被其他事务干扰，所以事务应被隔离起来
持久性Durability：一旦事务提交，对数据库中的数据改变就是永久的
7、 事务的控制
显示控制：显示提交（commit），显示回滚（rollback）
隐式控制：隐式提交（执行DDL或DCL语句，从SQL*PLUS使用EXIT或ＱＵＩＴ命令退出），隐式回滚（从SQL*PLUS强行退出，客户端到服务器端异常中断，系统崩溃）
8、 事务：
显示提交：commit(commit之后，rollback就没有意义了)
显示回滚：rollback
设立保存点：savepoint// (返回保存点 ：rollback to)
例如：
insert into dept values(60, 'xxx', 'xxx');
savepoint insert_a;
insert into dept values(80, 'xxx', 'xxx');
rollback to insert_a;
即取消insert intodept values(80, 'xxx', 'xxx')；操作
【表和约束】
1、 命名：
（1） 必须由字母开头，长度在1-30字符之间
（2） 名字中只能包含A-Z，a-z，0-9，_（下划线），$, #
（3） 不能使用保留字
（4） 名字大小写不敏感
2、 建表
create table dossier(
id number(4),
cname varchar2(20),
birthday date,
stature number(3),
weight number(5,2),
country_code char(2) default '01');
create table user_table(
user_id number(9) PRIMARY KEY,
user_name varchar2(50),
user_passwd varchar2(50) NOT NULL,
user_state varchar2(1) NOT NULL);
(用子查询的方式创建表)
as
select empno, ename, sal + 1000 as newsal
from emp
where deptno = 10;
3、添加列
不能指定添加位置，成为最后一列
alter table dossier add (sex char(1));
更改列属性
alter table dossier modify (sex char(2));
默认值
alter table dossier modify (sex default '男');
删除列
1、 alter table dossier drop column sex;
2、 alter table dossier drop (country_code);
3、删除表
drop table table_nale
重命名表
rename dossier to haha;
4、约束
NOT NULL非空
UNIQUE唯一值
PRIMARY KEY主码
FOREIGN KEY外表中列的引用
CHECK指定一个必须为真的条件
5、 相关数据字典
select table_name from user_tables;
查询数据字典结构
desc user_tables;
6、 数据说明补充，char，varchar，varchar2
(1)varchar和varchar2的区别在于后者把所有字符都占两字节，前者只对汉字和全角等字符占两字节。
（2）nvarchar和nvarchar2的区别和上面一样， 与上面区别在于是根据Unicode 标准所进行的定义的类型，通常用于支持多国语言类似系统的定义。
1.char char的长度是固定的，比如说，你定义了char(20),即使你你插入abc，不足二十个字节，数据库也会在abc后面自动加上17个空格，以补足二十个字节； char是区分中英文的，中文在char中占两个字节，而英文占一个，所以char(20)你只能存20个字母或10个汉字。 char适用于长度比较固定的，一般不含中文的情况
2.varchar/varchar2 varchar是长度不固定的，比如说，你定义了varchar(20),当你插入abc，则在数据库中只占3个字节。 varchar同样区分中英文，这点同char。 varchar2基本上等同于varchar，它是oracle自己定义的一个非工业标准varchar， 不同在于，varchar2用null代替varchar的空字符串 varchar/varchar2适用于长度不固定的，一般不含中文的情况
3.nvarchar/nvarchar2 nvarchar和nvarchar2是长度不固定的 nvarchar不区分中英文， 比如说：你定义了nvarchar(20),你可以存入20个英文字母/汉字或中英文组合，这个20定义的是字符数而不是字节数 nvarchar2基本上等同于nvarchar，不同在于nvarchar2中存的英文字母也占两个字节 nvarchar/nvarchar2适用于存放中文
查看更多关于Oracle数据库SQL重要语法总结的详细内容...