SQL语言概述

SQL：结构化查询语言
分类：DQL（数据查询语言）；select
DML（数据操纵语言）；比如：insert，update,delete
TCL（事务控制语言），比如：commit ,rellback;savepoint
DDL(数据定义语言)，比如 create alter drop
DCL(数据控制语言)，比如：grant;revoke
检索数据：
（1）简单查询
（1）检索所有的列
select * from dms_cb_device
select a.name as cb_name ,b.name as tr_name from dms_cb_device a ,dms_tr_device b 可查询多表
（2）带有表达式的select语句，包括加减乘除
select sal*(1+0.1),sal from emp 此处做了一个对比，将价格提高百分之十
（3）为列指定别名
select name as te_name from dms_tr_device
其中as可以不添加
select numb “员工编号” from emp
（4）显示不重复记录
使用distinct去重，但不能全部字段去重，只能是一个字段，或者一些字段
select distinct name from dms_cb_device
二.筛选查询
（1）比较筛选，有=,！,<,>,>=,<=
select name ,sal from emp where sal >1500
（2）使用特殊关键字筛选
①like 模糊查询
通配负符：%代表任意数量的字符
– 代表任意一个字符
select * from dms_cb_device where name not like ‘-sq’ （可以加 not like ）
②in
select * from dms_cb_device where id in (3800475135547280856,3800475135547280858)
三，between … and
select * from emp where sal between 2000 and 3000
④is null
select * from dms_cb_device where code is null ，这是搜索出code为空的值
select isnull(code,2) from dms_cb_device where code is null
这时候isnull是函数，如果有空值，可以设置为自定义的值，比如1，23， 都可以
（3）逻辑筛选
指在where 字句中使用逻辑计算符 and，or ,not
select sal form emp where sal >=200 and sal <=300
select sal from emp where sal <200 or sal >300
(4)分组查询 group by 汇总数据
select name ,combined_id from dms_cb_device group by combined_id,name
配合聚集函数：注意费聚集函数的所有列都要展示在gropu by 分组内
select number as 部门编号 avg(sal) as 平均工资 group by number ,查询每个部分的平均工资
配合having 使用，功能和where类似，只不过hacving 可以包含聚合函数，min ,max count sum 等
select number as 部门编号 avg(sal) as 平均工资 group by number having avg(sal)>200
（5）排序查询orderby
desc是降序 asc是升序，可以根据一个列 或者多个列排序，
三.多表关联查询
(1)表别名
select b.name as 开关名称,t.name as 配变名称 from dms_cb_device b,dms_tr_device t
where b.feeder_id=t.feeder_id
（2）内连接inner join …on
内连接时候inner 可以省去
select b.name as 开关名称,t.name as 配变名称 from dms_cb_device b inner join dms_tr_device t
on b.feeder_id=t.feeder_id
(3)外连接：左连接 右脸接 完全连接
左连接 left join
select b.name as 开关名称,t.name as 配变名称 from dms_cb_device b left join dms_tr_device t
on b.feeder_id=t.feeder_id (69659)
select * from dms_cb_device （ 60912）
从这条案例上看 左关联之后的表比左边自己本身的表记录还要大，这是为什么呢
on后面的条件是一对多的话 也就是左边有一个 右边有两个对应 那么就会产生两条记录

右连接right join on
select b.name as 开关名称,t.name as 配变名称 from dms_cb_device b right join dms_tr_device t
on b.feeder_id=t.feeder_id (9415)
select count(*) from dms_tr_device 340
同理 on后面的条件是一对多的话，右边一个，左边有多个对应，则会产生多个记录

完全接连：full join …on
select b.name as 开关名称,t.name as 配变名称 from dms_cb_device b full join dms_tr_device t
on b.feeder_id=t.feeder_id (69691)
（4）自然连接:很少使用
将第一个表的列与第二个表的中具有相同名称的列进行自动连接，不需要用户指定，是系统自动执行 nature join
select name from dms_cb_device natural join dms_tr_device
（5）自连接 可不看
（6）交叉连接 笛卡儿积
不需要任何条件，cross join 非常冗余
select count(*) from dms_cb_device cross join dms_tr_device
笛卡儿积解释：
集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}

四。oracle常用系统函数
（1）字符类函数
ASCII（c）:返回一个字符的ASCii码
CHR(i)：返回给出ASCII码值所对应的字符
两者之间是互逆关系
select ascii(‘Z’)Z,ascii(‘H’)h,ascii(’’)space from dual ，求这几个字符的ASCII值
select chr(90),chr(32),chr(68) from dual

②concat(s1,s2):将s1连接到字符串s2的后面，如果s1空，返回s2,加入s2空，返回s1,都空，返回空
select concat(‘你好’,‘匹配’)information from dual #这里的information是别名

initcap(s):将每个单词的第一个字母大写，其他字母小写
select initcap(‘hellow my friend’) from dual
④instr（s1,s2[,i][,j]):返回s2在s1中第j次出现的位置，搜索从S1的第i个字符开始，当发现没有 返回0，如果i为负数，则从右到左进行，
查询在oracle 11g 中从第三个字符开始，查询1第二次出现的位置
select instr(‘oracle 11g’,‘1’,3,2) from dual

5.length(s):返回字符串长度
查询开关名称长度大于10 的开关
select name from dms_cb_device where length(name)>10

⑥lower(s )返回字符串的小写与upper(s)返回字符串大写
select upper(name) from dms_cb_device

⑦ltrim(s1,s2) :删除S1左边的字符串s2，rtrim(s1,s2)：删除s1右边的字符串s2， trim (s1,s2)：删除s1两边的字符串s2
select ltrim(’###east###’,’#’),rtrim(‘east ‘),trim(’#’ from ‘####east###’) from dual

⑧replace(s1,s2[,s3]):用s3替换出现在s1中的字符串s2，并返回替换后的新字符串
select replace(‘bad gril bad boy’,‘bad’,‘good’)information from dual
9.substr(s,i,[j])函数：从字符串第i个位置开始截取长度为J的子字符串，如果省去j，则直接截取到尾数
select substr(‘asdWEdfgcc’,3,5) information from dual

（2）数字类函数
ABS(n) :返回n 的绝对值
CEIL（n）:返回大于或等于n的最小整数
eg：select ceil(7.3),ceil(6),ceil(-2.9) from dual
ROUND（n1,n2）:返回舍入小数点右边n2位的n1值，n2默认为0，则返回小数点最接近的整数，n2为负数，舍入到小数点左边相应的位置
select round(‘23.124’,2) from dual
返回的值 是23.12
POWER（n1,n2）：返回n1的n2次方
select power(2,4) from dual
返回16 ，2的4次方

（3）日期时间类函数
SYSDATE（返回系统当前日期）
select sysdate() from dual
ADD_MONTHS（d,i）:日期d加上i个月之后的结果
select add_months(sysdate,6) from dual，在当前日期基础上加6个月

（4）转换类函数
TO_CHAR(X【,format】):将表达式转换为字符串
select sysdate as 默认格式日期,to_char(sysdate,‘YYYY-MM-DD’) as 转换后日期 from dual
TO_NUMBER（s[,format[lan]]：返回字符串s代表得数字，lan 表示语言
(5)聚合函数
avg平均
count 返回查询记录数
max
min
sum
variance统计方差
stddev标准方差

五，子查询的用法
注意事项：子查询必须用()括起来
子查询中不能有order by
子查询可以嵌套多层，但不能超过255 曾
（1）单行子查询：返回一行数据的子查询语句，是指子查询中返回一行，不是指最后全部查询得结果
在emp表中，查询出既不是最高工资也不是最低工资的员工信息
select empno,ename,sal from emp where sal<(select max(sal) from emp)
（2）多行子查询（In,any,all）
select name,id from dms_cb_device where feeder_id in (select feeder_id from dms_tr_device where name like ‘%配变%’)

any运算符必须与单行比较运算符结合使用
查询工资大于部门编号为10的任意一个员工工资即可的其他部门信息
select deptno,enname,sal from emp where sal >any(select sal from emp where deptno=10) and deptno <>10

all 也要结合比较运算符
查询工资大于部门编号为10 的所有员工工资的员工信息
sselect deptno,ename,sal from emp where sal >all(select sal from emp where deptno=10)

(3)关联子查询：内查询与外查询互相关联
查询工资大于同职位的平均工资的员工信息
select emptno,ename,sal,from emp f
where sal >(select avg(sal) from emp where job=f.job)
order by job

六，操作数据库：DML
包括：insert,update,dalete,truncate,call,locktable merge
(1)insert 单个
insert into dms_tr_warn_info (id,tr_id,tr_name,st_id,feeder_id)
values (‘11’,‘3801319560494187382’,‘hdd’,‘113997365584592903’,‘3799912185610633220’)
批量：注意select 语句返回得数据必须满足insert into表中列得约束
insert into jobs sleect * from jobs where jobs.max_salary >1000
(2）更新语句update
update dms_tr_warn_info set max_loadrate=12333 where tr_name=‘hdd’
(3)删除数据
delete:删除数据库所有的记录或者指定范围得记录
ddelete from job where name=‘df’
假如删除全部记录，会产生回滚记录，这种操作可以使用rollback 撤销
truncaate 删除全表记录要比delete快，并且不会产生回滚记录
truncate table job

七，事务处理
事务的属性：
（1）原子性：事务事一个整体的工作单元，事务对数据库的操作，要么全部执行，要么全部取消，如果一个失败，则所有语句全部回滚
（2）一致性：事务在完成时，必须使所有数据保持一致的状态
（3）隔离性：由事务所作的修改必须与其他事务所作的修改隔离
（4）持久性：当事务提交时候，对数据库所作的修改会永久保存下来

操作事务：
（1）commit语句提交事务
顺序：在回滚段内记录当前事务已提交，启动后台日志，释放事务处理所使用的系统资源，显示通知，告诉用户已经提交成功

（2）rollback回滚事务
顺序：使用回滚段中的数据撤销对数据库所作的修改
后台进程释放事务所使用的系统资源
显示通知，提醒用户回滚成功
允许回滚事务的一部分，建立保存点来实现
例如：向表插入俩条记录，然后使用保存点回滚最后添加的那条记录
select * from dms_tr_warn_info
insert into dms_tr_warn_info (id,tr_id,tr_name) values(12,1234595,‘d8d’)
savepoint sp
insert into dms_tr_warn_info (id,tr_id,tr_name) values(14,12345945,‘d855d’)
rollback to savepoint sp
commit