常用Sql语句学习总结

Sql语句学习总结用于以后复习查看

结构化查询语言(Structured Query Language)，简称SQL：
DQL：数据查询语言，用于对数据进行查询，如select
DML：数据操作语言，对数据进行增加、修改、删除，如insert、udpate、delete
DDL：数据定义语言，进行数据库、表的管理等，如create、drop、alter
DCL：数据控制语言，对数据访问权进行控制的指令，grant,revoke
TCL:事务控制语言，它的语句能确保被DML语句影响的表的所有行及时得以更新。包括commit（提交）、savepoint（保存点）、rollback（回滚）。
CCL:指针控制语言,它的语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。
(基于Oracle中的emp，dept,salgrade表)
emp:

dept:

salgrade:

一、基本语句

＝，！＝,<>，<,>,<=,>=,
any,some,all
–any取其中任意一个，
some跟any是同一个效果，只要大于其中某一个值都会成立，
all大于所有的值才会成立。

is null,is not null
在sql的语法中，null表示一个特殊的含义，需要使用is ,is not判断

between x and y
包含x和y的值

in（list），not in（list）
需要进行某些值的等值判断的时候可以使用in和not in
eg:

select * from emp where deptno in(10,20);

and ,or
and相当于是与操作，or相当于是或操作，and和or可能出现在同一个sql语句中,and 的优先级要高于or，所以一定要将or的相关操作用()括起来，提高优先级

exists（sub－query）
当exists中的子查询语句能查到对应结果的时候,意味着条件满足(相当于双层for循环)
eg:

查询部门编号为10和20的员工，要求使用exists实现
select *
  from emp e
 where exists (select deptno
          from dept d
         where (d.deptno = 10 or d.deptno = 20)
           and e.deptno = d.deptno);

like _ ,%,escape ‘\‘
在like的语句中，需要使用占位符或者通配符
_:某个字符或者数字仅出现一次
%:任意字符出现任意次数
escape:使用转义字符,可以自己规定转义字符
eg:

查询名字中带%的用户(此时a相当于转义字符a%看做成一个整体)
select * from emp where ename like('%a%%') escape('a')

order by排序操作
默认情况下完成的是升序的操作，
asc:是默认的排序方式，表示升序
desc：降序的排序方式
eg:

select * from emp order by sal;
select * from emp order by sal desc;

拼接字段(||, +)
首选|| (mysql中||表示or，一般用concat() )
eg:

select 'my name is '||ename name from emp;
select concat('my name is ',ename) from emp;

dual是oracle数据库中的一张虚拟表，没有实际的数据，可以用来做测试
eg:

select 100+null from dual;

全集:union all
并集:union
交集:intersect
差集:minus

dinstinct 去除重复数据
eg:

select distinct deptno from emp;

group by,按照某些相同的值去进行分组操作
group进行分组操作的时候，可以指定一个列或者多个列，但是当使用了group by 之后，选择列表中只能包含组函数的值或者group by 的普通字段
eg:

求平均薪水大于2000的部门
select avg(sal),deptno from emp where sal is not null group by deptno having avg(sal) >2000 order by avg(sal);

二、函数

(具体可查看Oracle函数大全)
组函数又称为聚合函数
输入多个值，最终只会返回一个值
组函数仅可用于选择列表或查询的having子句
单行函数
输入一个值，输出一个值

1.字符函数

concat：表示字符串的连接 等同于||

select concat('my name is ', ename) from emp;

将字符串的首字母大写

select initcap(ename) from emp;

将字符串全部转换为大写

select upper(ename) from emp;

将字符串全部转换为小写

select lower(ename) from emp;

填充字符串

select lpad(ename,10,'*') from emp;
select rpad(ename,10,'*') from emp;

去除空格

select trim(ename) from emp;
select ltrim(ename) from emp;
select rtrim(ename) from emp;

查找指定字符串的位置

select instr('ABABCDEF','A') from emp;

查看字符串的长度

select length(ename) from emp;

截取字符串的操作

select substr(ename,0,2) from emp;

替换操作

select replace('ababefg','ab','hehe') from emp;

2.数值函数

给小数进行四舍五入操作，可以指定小数部分的位数

select round(123.123,2) from dual;
select round(123.128,2) from dual;
select round(-123.128,2) from dual;

截断数据,按照位数去进行截取，但是不会进行四舍五入的操作

select trunc(123.128,2) from dual;

取模操作

select mod(10,4) from dual;
select mod(-10,4) from dual;

向上取整

select ceil(12.12) from dual;

向下取整

select floor(13.99) from dual;

取绝对值

select abs(-100) from dual;

获取正负值

select sign(-100) from dual;

x的y次幂

select power(2,3) from dual;

3.日期函数

显示当前日期

select sysdate from dual;
select current_date from dual;

add_months,添加指定的月份

select add_months(hiredate,2),hiredate from emp;

返回输入日期所在月份的最后一天

select last_day(sysdate) from dual;

两个日期相间隔的月份

select months_between(sysdate,hiredate) from emp;

返回四舍五入的第一天

select sysdate 当时日期,round(sysdate) 最近0点日期,round(sysdate,'day') 最近星期日,round(sysdate,'month') 最近月初,round(sysdate,'q') 最近季初日期, round(sysdate,'year') 最近年初日期 from dual;

返回下周的星期几

select next_day(sysdate,'星期一') from dual;

提取日期中的时间

select 
extract(hour from timestamp '2001-2-16 2:38:40 ' ) 小时,
extract(minute from timestamp '2001-2-16 2:38:40 ' ) 分钟,
extract(second from timestamp '2001-2-16 2:38:40 ' ) 秒,
extract(DAY from timestamp '2001-2-16 2:38:40 ' ) 日,
extract(MONTH from timestamp '2001-2-16 2:38:40 ' ) 月,
extract(YEAR from timestamp '2001-2-16 2:38:40 ' ) 年
 from dual;

返回日期的时间戳

select localtimestamp from dual;
select current_date from dual;
select current_timestamp from dual;

给指定的时间单位增加数值

select
trunc(sysdate)+(interval '1' second), --加1秒(1/24/60/60)
trunc(sysdate)+(interval '1' minute), --加1分钟(1/24/60)
trunc(sysdate)+(interval '1' hour), --加1小时(1/24)
trunc(sysdate)+(INTERVAL '1' DAY),  --加1天(1)
trunc(sysdate)+(INTERVAL '1' MONTH), --加1月
trunc(sysdate)+(INTERVAL '1' YEAR), --加1年
trunc(sysdate)+(interval '01:02:03' hour to second), --加指定小时到秒
trunc(sysdate)+(interval '01:02' minute to second), --加指定分钟到秒
trunc(sysdate)+(interval '01:02' hour to minute), --加指定小时到分钟
trunc(sysdate)+(interval '2 01:02' day to minute) --加指定天数到分钟
from dual;

4.转换函数

在oracle中存在数值的隐式转换和显式转换
隐式转换(默认转换):当不同类型的数据进行直接的操作，没有显式的进行转换
显式转换：强制转换

eg：

select '999'+10 from dual;

date ：to_char：将日期以固定形式转换为字符串

select to_char(sysdate,'YYYY-MI-SS HH24:MI:SS') from dual;

number : to_char：将数字以固定形式装换为字符串

select to_char(123.456789,'9999') from dual;
select to_char(123.456789,'0000.00') from dual;
select to_char(123.456789,'$0000.00') from dual;
select to_char(123.456789,'L0000.00') from dual;
select to_char(123456789,'999,999,999,999') from dual;

to_date:将字符串以固定的格式转换为日期

select to_date('2019/10/10 10:10:10','YYYY-MM-DD HH24:MI:SS') from dual;

to_number:将字符串以固定格式转成数字

select to_number('123,456,789','999,999,999') from dual;

5.条件函数

**decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)**

**注意：**值1……n 不能为条件表达式,这种情况只能用case when then end解决

case when

case [<表达式>]
when <表达式条件值1> then <满足条件时返回值1> 
[when <表达式条件值2> then <满足条件时返回值2> 
……
[else  <不满足上述条件时返回值>]]
end

6.组函数

一般情况下，组函数都要和group by组合使用
组函数一般用于选择列表或者having条件判断
常用的组函数有5个
avg() 平均值,只用于数值类型的数据
sum() 求和，只适合数值类型的数据
min() 最小值，适用于任何类型
max() 最大值，适用于任何类型
count() 记录数,处理的时候会跳过空值而处理非空值。count一般用来获取表中的记录条数，获取条数的时候可以使用或者某一个具体的列甚至可以使用纯数字来代替，但是从运行效率的角度考虑，建议使用数字或者某一个具体的列而不要使用

7.通用函数

**nvl(arg1,arg2),如果arg1是空，那么返回arg2，如果不是空，则返回原来的值**

null是比较特殊的存在，null做任何运算都还是为null，因此要将空进行转换

三、关联查询

92语法：
等值连接，两个表中包含相同的列名
非等值连接，两个表中没有相同的列名，但是某一个列在另一张表的列的范围之中
左外连接（把左表的全部数据显示）和右外连接（把右表的全部数据显示）

select * from emp e,dept d where e.deptno = d.deptno；--等值连接
select * from emp e,dept d where e.deptno = d.deptno(+);--左外连接
select * from emp e,dept d where e.deptno(+) = d.deptno;--右外连接

自连接,将一张表当成不同的表来看待，自己关联自己
eg:

将雇员和他经理的名称查出来
select e.ename,m.ename from emp e,emp m where e.mgr = m.empno;

笛卡尔积,当关联多张表，但是不指定连接条件的时候，会进行笛卡尔积，关联后的总记录条数为M*n，一般不要使用
eg:

select * from emp e,dept d;

在92语法中，多张表的连接条件会方法where子句中，同时where需要对表进行条件过滤,因此，相当于将过滤条件和连接条件揉到一起，太乱了，因此出现了99语法

99语法：
cross join 等同于92语法中的笛卡儿积
eg:

select * from emp cross join dept;

natural join 相当于是等值连接，但是注意，不需要写连接条件，会从两张表中找到相同的列做连接当两张表中不具有相同的列名的时候，会进行笛卡儿积操作,自然连接跟92语法的自连接没有任何关系
eg:

select * from emp e natural join dept d ;

on子句，可以添加任意的连接条件,添加连接条件 相当于92语法中的等值连接

select * from emp e join dept d on e.deptno = d.deptno;

相当于92语法中的非等值连接
eg:

select * from emp e join salgrade sg on e.sal between sg.losal and sg.hisal;

left outer join ,会把左表中的全部数据正常显示，右表没有对应的数据直接显示空即可

select * from emp e left outer join dept d on e.deptno = d.deptno;
等同于select * from emp e,dept d where e.deptno = d.deptno(+);

right outer join ,会把右表中的全部数据正常显示，左表中没有对应的记录的话显示空即可

select * from emp e right outer join dept  d on e.deptno = d.deptno;
等同于select * from emp e,dept d where e.deptno(+) = d.deptno;

full outer join,相当于左外连接和右外连接的合集

select * from emp e full outer join dept d on e.deptno = d.deptno;

inner outer join，两张表的连接查询，只会查询出有匹配记录的数据

select * from emp e inner join dept d on e.deptno = d.deptno;

using,除了可以使用on表示连接条件之外，也可以使用using作为连接条件,此时连接条件的列不再归属于任何一张表

select deptno from emp e join dept d using(deptno);

四、子查询

子查询：
嵌套再其他sql语句中的完整sql语句，可以称之为子查询
分类：
单行子查询
多行子查询

限制输出，limit，mysql中用来做限制输出的，但是oracle中不是再oracle中，如果需要使用限制输出和分页的功能的话，必须要使用rownum，但是rownum不能直接使用，需要嵌套使用
eg:

select * from (select * from emp e order by e.sal desc) t1 where rownum <= 5

使用rownum的时候必须要再外层添加嵌套，此时才能将rownum作为其中的一个列，然后再进行限制输出
eg:

select * from (select t1.*, rownum rn from (select * from emp e order by e.sal desc) t1 where rownum <= 10) t where t.rn >  5 and t.rn <= 10;

五、表设计语句

1.视图view

*如果普通用户第一次创建视图，提示没有权限，要使用管理员去修改权限(scott为数据库名)*使用grant,revoke修改权限

grant create view to scott;

撤销权限:

revoke create view from scott

1).视图的使用
eg:

select * from v_emp;

2).向视图中添加数据,执行成功之后，需要提交事务
eg:insert into v_emp(empno,ename) values(1111,‘zhangsan’);
3).如果定义的视图是非只读视图的话，可以通过视图向表中插入数据，如果是只读视图，则不可以插入数据，只读视图只提供查询的需求，无法进行增删改操作
eg:先创建视图

create view v_emp2 as select * from emp with read only;

当输入下面这行代码时提示无法对只读视图执行DML操作

insert into v_emp2(empno,ename) values(1234,'lisi');

4).删除视图
当删除视图中的数据的时候，如果数据来源于多个基表，则此时不能全部进行删除，只能删除一个表中的数据

drop view v_emp2;

2.序列sequence

在oracle中如果需要完成一个列的自增操作，必须要使用序列

创建序列

create sequence seq_name
  increment by n  每次增长几
  start with n    从哪个值开始增长
  maxvalue n|nomaxvalue 10^27 or -1  最大值
  minvalue n|no minvalue  最小值
	cycle|nocycle           是否有循环
	cache n|nocache          是否有缓存

eg:

create sequence my_sequence increment by 2 start with 1

如果创建好序列之后，没有经过任何的使用，那么不能获取当前的值，必须要先执行nextval之后才能获取当前值
查看当前序列的值

select my_sequence.currval from dual;

获取序列的下一个值

select my_sequence.nextval from dual;

六、数据操作DML语句

1.增
插入操作：
元组值的插入(一组里的多个值)
查询结果的插入
元组值的插入：

insert into tablename values(val1,val2,....) 如果表名之后没有列，那么只能将所有的列都插入
insert into tablename(col1,col2,...) values(val1,val2,...) 可以指定向哪些列中插入数据

向部分列插入数据的时候，不是想向哪个列插入就插入的，要遵循创建表的时候定义的规范
eg:

insert into emp(empno,ename) values(3333,'wangwu');

查询结果的插入：
eg:复制表同时复制表数据，不会复制约束

create table emp2 as select * from emp;

复制表结构但是不复制表数据，不会复制约束

create table emp3 as select * from emp where 1=2;

2.删
删除操作：

 *delete from tablename where condition*

eg:删除满足条件的数据

delete from emp2 where deptno = 10;

truncate ：跟delete有所不同，delete在进行删除的时候经过事务，而truncate不经过事务，一旦删除就是永久删除，不具备回滚的操作，效率比较高，但是容易发生误操作，所以不建议使用
eg：truncate table emp2；

3.改
修改操作
可以更新或者修改满足条件的一个列或者多个列

*update tablename set col = val1,col2 = val2 where condition;*

更新单列
eg：

`update emp set ename = 'heihei' where ename = 'hehe';`

更新多个列的值
eg：

`update emp set job='teacher',mgr=7902 where empno = 15;`

七、事务控制语言TCL

commit,rollback,savepoint
commit:对数据库的所有更新都以交付实施。
rollback:数据库应恢复该事务到初始状态。
一旦执行了commit语句，将目前对数据库的操作提交给数据库（实际写入DB）,以后就不能用rollback进行撤销。
执行一个 DDL ，dcl语句或从 SQL*Plus正常退出，都会自动执行commit命令。

savepoint 保存点
当一个操作集合中包含多条SQL语句，但是只想让其中某部分成功，某部分失败，此时可以使用保存点,此时如果需要回滚到某一个状态的话使用 rollback to sp1;

delete from emp where empno = 1111;
delete from emp where empno = 2222;
savepoint sp1;
delete from emp where empno = 1234;
rollback to sp1;
commit;