SQL知识记录

基础知识

查询语句(DQL)

常用函数

函数	含义
distinct	去重，只能出现在所有字段的最前面，并且表示后面的所有字段联合去重
count	求计数总和
desc	降序
asc	升序
decode	oracle中的自定义排序
NVL
LPAD(char1,n,char2)	字符 char1 按制定的位数 n 显示，不足的位数用 char2 字符串替换左边的空位
COALESCE(exp1,exp2)	多项替换函数，当exp1不为空时返回exp1，否则返回exp2，常用于将空值替换成0，sql中若查出的值为null，再与任何值运算都为空

聚合函数

聚合函数	含义
max()	会忽略null值
min()	会忽略null值
avg()	会忽略null值
sum()	会忽略null值
count()	count(*)对行的数目计数，不忽略null,count(var)会对var列计数并且忽略null值

过滤条件

过滤条件	含义
=
!=
>
<
<>
like
in
between…and	要先指定下限，再指定上限
not…in
left
is null	两个null值不相等
or

分组查询

分组查询主要涉及的是group by 和having ,当查询语句中有group by，那么select后面只能跟分组函数和参与分组的字段
多个字段可以联合起来分组
group by子句在where子句被评估后运行，因此无法对where子句增加过滤条件
group by

-- 找出每个岗位最高的工资
select max(sal),job 
from emp 
group by job;

-- 该语法错误，ename没有参与分组，无法查询
select ename,max(sal),job 
from emp 
group by job;

having
having只能和group by一起使用，用于对分组后的数据进行再次过滤，但是能用where过滤最好直接用where，效率更高，当出现过滤条件是聚合函数时，就只能用having进行过滤

-- 找出每个部门最高工资，要求显示工资大于2900
-- 此方法查询效率低
select deptno,max(sal)
from	emp
group by deptno
having	max(sal)>2900;

-- 直接用where先过滤，就先把不用的数据丢弃的，效率更高，能使用where就用where
select max(sal),deptno 
from emp 
where sal > 2900 
group by deptno;

-- 找出每个部门的平均薪资，要求薪资大于2000
-- where后面不能用分组函数，所以where不能计算平均工资
select deptno,avg(sal)
from emp 
group by deptno
having avg(sal) > 2000;

group_concat
待补充
可以实现同一分组的值进行连接显示

SELECT 
        ssav.attr_id,
        ssav.attr_name,
        ssav.attr_value,
        GROUP_CONCAT(distinct ssav.sku_id) sku_ids
from pms_sku_info info
LEFT JOIN pms_sku_sale_attr_value ssav on info.sku_id = ssav.sku_id
WHERE info.spu_id = #{spuId}
group by ssav.attr_id,ssav.attr_name,ssav.attr_value

一个分组过滤sql的执行顺序

-- 标准写法，顺序不能更改，数字代表执行顺序
select	xxx			5，执行选出数据
from	xxx			1，指定数据来源的表
where xxx			2，首先执行where语句过滤原始数据
group by xxx 		3，进行分组
having	xxx 		4，对分组数据进行操作
order by	xxx 	6，执行排序
	

连接查询

连接查询原理
两张表进行连接查询，如果没有任何限制，查询结果是两张表记录数的乘积(笛卡尔积)，即会出现很多冗余数据，通过添加过滤条件，可以避免笛卡尔积的冗余数据，但是并不会减少匹配次数，只是将过滤条件筛选的数据显示

-- 会根据表数据记录数乘积得到查询结果记录数
select ename,dname from emp,dept;

-- 通过where避免了冗余数据，但是查询效率和上面是一样的
select e.ename,d.dname from emp e,dept d where e.deptno = d.dpetno;

内连接
如果没有指定连接类型，默认会使用内连接，所以连接查询一定要指定连接类型
内连接查询会忽略显示null值，进而影响数据的展示
内连接查询出匹配表的所有匹配记录，没有主副之分
多表连接时，后面的表并非与前表相连，而是与前表相连的结果集连接

-- inner可以省略，它的目的就是可读性更好
select e.ename , d.dname 
from emp e 
inner join dept d 
on e.deptno = d.deptno;


-- 自连接，最大的特点是将一张表看做两张表
-- 找出每个员工的上级领导，要求显示员工名和对应领导名
select a.ename as '员工名',b.ename as '领导名'
from emp a
inner join emp b
on a.mgr = b.empno;

外连接
外连接有主副之分，最重要的特点是主表的数据无条件全部显示出来
查出主的全部，副的匹配，当副表中没有数据和主表数据匹配，则自动模拟null与之匹配
业务上主要用的是外连接查询，因为内连接查询会导致数据丢失，而外连接查询会保证主表的数据完成查询出来

-- 找出每个员工的上级领导,所有员工必须查询出来,
-- 左外连接，左表是主导地位
-- outer可以省略
select a.ename as '员工',b.ename as '领导'
from emp a
left join emp b
on a.mgr = b.empno;

-- 右外连接，右边表是主表
-- outer可以省略
select a.ename as '员工',b.ename as '领导'
from emp b
right join emp a
on a.mgr = b.empno;

-- 多张表连接查询
select e.ename as '员工',d.dname,s.grade,b.ename as '领导'		
from emp e
join dept d
on e.depton = d.depton
join salgrade s
on e.sal between s.losal and s.hisal
left join emp b
on e.mgr = b.deptno；

子查询
select 中嵌套select语句，被嵌套的select语句是子查询

-- 找出高于平均薪资员工的信息
select *
from emp
where sal > (select avg(sal) from emp);

//找出每个部门平均薪水的薪资等级
select t.*,s.grade
from (select deptno,avg(sal) as avgsal from emp group by deptno) t 
join salgrade s
on t.avgsal between s.losal and s.hisal;

-- 找出每个员工所在部门名称，要求显示员工名和部门名
select 
	e.ename,
	(select d.dname from dept d where e.deptno = d.deptno) as dname
from emp e;

union

union可以将查询结果集相加，可以解决or 和 in解决不了的查询，比如两张毫无相关的表的连接
使用union必须保证上下的列是相同的，否则报错

-- 找出工作岗位是SALESMEN和MANAGER的员工
-- 通过or连接
select ename,job from emp where job='SALESMEN' or job = 'MANAGER';
-- 通过in连接
select ename,job from emp where job in ('SALESMEN','MANAGER');


-- 通过union
select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMEN';

分页查询

mysql通过limit进行分页，并且它是mysql特有的，limit取结果集的部分数据，是sql语句最后的执行环节
oracle相同的机制是rownum

-- 取出工资前5名的员工
select ename,sal
from emp
order by sal desc
limit 0,5;

DDL语句

DDL语句修改表结构，不对表的数据进行修改，语句执行完没有提交回滚操作

常用数据类型

mysql常用数据类型

类型	描述
Char	定长字符串，存储空间大小固定，适合作为主键或外键，对应java的String， 当数据长度定长，例如性别，年龄，生日等采用这种
Varchar	变长字符串，存储空间等于实际数据空间，最多存255个字符， 当数据长度不确定，例如：简介，姓名等，采用这种，但效率没有Char高
double	数值型
Float	数值型
int	整型
bigint	长整型，对应Java中的long
Date	日期型 年月日，对应java.sql.Date类型
DateTime	日期型 年月日 时分秒 毫秒
time	日期型 时分秒
BLOB	二进制大对象(存图片，视频等流媒体信息)，对应java的Object
CLOB	字符串大对象，存大文本，例如4G的字符串，对应java的Object

常见语句

-- 创建表
create table 表名{
    字段名1，数据类型，[约束]，
    字段名2，数据类型，[约束]，
    		...
    字段名n，数据类型，[约束]
}


-- 删除表或库通用写法
drop table 表名
-- oracle不支持这种写法
drop table if exists 表名

-- 清除表里的所有记录，保留表结构
truncate

约束

约束用于给表中的数据加限制，保证其合法性，完整性，有效性

常见约束	用途
非空：not null	约束字段不能为null
唯一：unique	约束字段不能重复，但可以为Null
主键：primary key	约束字段不能为null，且不能重复
外键：foreign key	保证多表之间的关联性
检查：check	oracle数据库有check约束，mysql中没有该约束
自增长：auto_increment	oracle中提供的自增机制交sequence

DML语句

DML语句就是数据库操作语句

insert

-- 字段的数量和值的数量要相同，并且数据类型要对应
insert into 表名(字段1，字段2，...) 
values (值1，值2，...);

-- 字段可以省略不写，但后面的value对数量和顺序都有要求
insert into s_student 
values(1,'zhangsan','1','gaosao2ban');

-- 可以一次插入多行数据
insert into 表名(字段1，字段2，...) 
values (值1，值2，...)(值1，值2，...);

-- 向表中插入查询出来的数据
insert into emp1  
select * from emp;

update

没有条件整张表的数据会全部更新

-- 将部门10的LOC修改为SHANGHAI,部门名修改为RENSHIBU
update dept1 set loc = 'SHANGHAI' ,dname='RENSHIBU' where deptno=10;

delete

delete语句运行速度比较慢，原因是没有释放数据的物理存储空间（可以回滚），当表的数据庞大时，删除需要一定时间
通过truncate 可以截断表，只剩表头来达到删除大表的效果，非常危险，不能恢复

-- 删除指定数据
delete from emp where deptno = 10;
-- 删除表中所有数据
delete from emp;
-- 删除大表（重点）,表被截断，不能回滚，永久丢失
truncate table emp1;

DCL语句

--1.GRANT 赋于权限,常用的系统权限集合有以下三个
CONNECT(基本的连接)
RESOURCE(程序开发) 
DBA(数据库管理) 

--常用的数据对象权限有以下五个: 
ALL ON 数据对象名, SELECT ON 数据对象名, UPDATE ON 数据对象名, 

DELETE ON 数据对象名, INSERT ON 数据对象名, ALTER ON 数据对象名

GRANT CONNECT, RESOURCE TO 用户名; 
GRANT SELECT ON 表名 TO 用户名; 
GRANT SELECT, INSERT, DELETE ON 表名 TO 用户名 1, 用户名 2; 

--2.REVOKE 回收权限
REVOKE CONNECT, RESOURCE FROM 用户名; 
REVOKE SELECT ON 表名 FROM 用户名; 
REVOKE SELECT, INSERT, DELETE ON 表名 FROM 用户名 1, 用户名 2; 

--查询数据库中第 63 号错误：
select orgaddr,destaddr from sm_histable0116 where error_code='63'; 

--查询数据库中开户用户最大提交和最大下发数： 
select MSISDN,TCOS,OCOS from ms_usertable；

--查询数据库中各种错误代码的总和：
select error_code,count(*) 
from sm_histable0513 
group by error_code 
order by error_code; 

--查询报表数据库中话单统计种类查询。
select sum(Successcount) from tbl_MiddleMt0411 where ServiceType2=111 
select sum(successcount),servicetype from tbl_middlemt0411 group by servicetype 

--导文件 exp 和 imp 例子：
exp cjxt/cjxt tables=dcardres file=dcardres.dmp 
impgzcj/gzcj@oracletables=dcardres file=dcardres.dmp 

--查看有没有锁表
SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name, 
o.owner,o.object_name,o.object_type,s.sid,s.serial# 
FROM vlocked_object l,dba_objects o,vsession s 
WHERE l.object_id=o.object_id 
AND l.session_id=s.sid 

--怎么杀掉特定的数据库会话
Alter system kill session 'sid,serial#'; 
或者
alter system disconnect session 'sid,serial#' immediate; 

在 win 上，还可以采用 oracle 提供的 orakill 杀掉一个线程（其实就是一个 Oracle 进程）

在 Linux/Unix 上，可以直接利用 kill 杀掉数据库进程对应的 OS 进程


--怎么快速查找锁与锁等待
数据库的锁是比较耗费资源的，特别是发生锁等待的时候，我们必须找到发生等待的锁，有可能的话，杀掉该进程。
这个语句将查找到数据库中所有的 DML 语句产生的锁，还可以发现，任何 DML 语句其实产生了两个锁，一个是表锁，一个是行锁。可以通过 alter system kill session ‘sid,serial#’来杀掉会话

SELECT /*+ rule */ s.username, decode(l.type,'TM','TABLE LOCK', 'TX','ROW LOCK',  NULL)
LOCK_LEVEL, o.owner,o.object_name,o.object_type,s.sid,s.serial#,s.terminal,s.machine,
s.program,s.osuser 
FROM vsession s,vlock l,dba_objects o 
WHERE l.sid = s.sid 
AND l.id1 = o.object_id(+) AND s.username is NOT NULL 


--如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待,以下的语句可以查询到谁锁了表，而谁在等待。
SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name, 
o.owner,o.object_name,o.object_type,s.sid,s.serial# 
FROM vlocked_object l,dba_objects o,vsession s 
WHERE l.object_id=o.object_id 
AND l.session_id=s.sid 
ORDER BY o.object_id,xidusn DESC

DBA命令

--查看表结构
desc 表名
-- 查看mysql版本
select version()

--查看当前使用的数据库
select database();

--终止一条正在编写的语句
\c

--查看表的创建语句
show create table 表名

//新建用户
create user username user1 by 'password';
//将数据库中的数据导出
//在windows  dos命令窗口中执行
mysqldump test>D:\test.sql -uroot -proot;
mysqldump test emp>D:\test.sql -uroot -proot;//导出指定数据库的指定表

//导入数据
create database test;
use test;
source D:\test.sql	



--1、查看表空间的名称及大小
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size 
from dba_tablespaces t, dba_data_files d 
where t.tablespace_name = d.tablespace_name 
group by t.tablespace_name; 

--2、查看表空间物理文件的名称及大小
select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space 
from dba_data_files 
order by tablespace_name; 

--3、查看回滚段名称及大小
select segment_name, tablespace_name, r.status, 
(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent, 
max_extents, v.curext CurExtent 
From dba_rollback_segs r, v$rollstat v 
Where r.segment_id = v.usn(+) 
order by segment_name ; 

--4、查看控制文件
select name from v$controlfile; 

--5、查看日志文件
select member from v$logfile; 

--6、查看表空间的使用情况
select sum(bytes)/(1024*1024) as free_space,tablespace_name 
from dba_free_space 
group by tablespace_name;

SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE, 
(B.BYTES100)/A.BYTES "% USED",(C.BYTES100)/A.BYTES "% FREE" 
FROM SYS.SMTS_AVAIL A,SYS.SMTS_USED B,SYS.SM$TS_FREE C 
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME; 

--7、查看数据库库对象 
select owner, object_type, status, count(*) count# 
from all_objects group by owner, 
object_type, status; 

--8、查看数据库的版本
Select version 
FROM Product_component_version 
Where SUBSTR(PRODUCT,1,6)='Oracle'; 

--9、查看数据库的创建日期和归档方式
Select Created, Log_Mode, Log_Mode 
From V$Database; 

集合

集合	含义
union	取并集操作，会去掉重复数据
union all	取并集操作，不去掉重复数据
minus	差集
intersect	交集，如果不存在交集返回空，intersect去除了重复行

条件逻辑

case表达式

select c.cust_id,c.fed_id,
case 
	when c.cust_type_cd='I' then (select concat(i.fname,'',i.lname) from inded i where i.cust_id=c.sust_id)
	when c.cust_type_cd='B' then (select b.fname from hussds b where b.cust_id=c.sust_id)
else 'unknown' end name
from customer c;

索引

数据库中查询一张表有两种检索方式
1 全表扫描
2 根据索引检索（效率很高）

索引提高检索效率的根本原因是缩小的扫描范围，但是索引不能乱加，因为索引也是数据库中的对象，也需要维护成本。
经常被修改的数据就不适合添加索引，因为数据一旦修改，索引要重新排序，进行维护。

什么时候需要用索引

1.数据量很庞大
2.字段很少DML操作(字段修改索引也要维护)
3.该字段经常出现在where子句中
4.主键和具有unique约束的字段自动添加索引

模糊查询，第一个通配符使用%，这个时候索引是失效的

索引的分类

单一索引：给单个字段添加索引

复合索引：给多个字段联合起来添加一个索引

主键索引：主键上会自动添加索引

唯一索引：有unique约束的字段上会自动添加索引

索引实现原理

索引采用的数据结构是 B + Tree

当字段没有索引时会进行全局扫描，效率比较低

当给字段添加索引后

数据库中(根据存储引擎可能存储在硬盘也可能在内存)，会生成索引文件，索引会对字段进行排序，然后会分区，将数据拆分开存储到B + Tree数据结构中，并且每个字段都会携带该行记录的物理地址

当进行查询时，首先会查询对应字段是否有索引，有的话就找到了对应的索引对象

然后对根据字段数据，定位到区，缩短了扫描的数量，很对就对应到指定数据的物理地址

查询语句不再走表，直接定位到物理硬盘上的数据，where 后的条件就变成了 where 物理地址 = 索引中携带的物理地址；

索引Sql

-- 创建索引
create index 索引名称 on 表名(字段名);

-- 删除索引
drop index 索引名称 on 表名;

-- 可以通过explain查询sql语句的执行计划
explain select ename,sal from emp where sal = 5000;

视图

视图可以隐藏表的实现细节

站在不同角度去看数据（同一张表的数据，通过不同角度去看待）

只有DQL语句可以以视图对象的方式创建出来

对视图进行增删改查会影响到原表数据(通过视图影响原表数据，不是直接操作原表)

-- 创建视图
create view myview as select empno,ename from emp;
-- 删除视图
drop view myview;

事务

一个事务是一个完整的业务单元逻辑，和事务相关的语句只有DML语句(insert delete update)，因为这三个语句都是和数据库表当中的数据相关的
事务的存在是为了保证数据的完整性，安全性
开启事务后，任何操作都只是将执行记录到数据库的操作历史中【记录在硬盘缓存】，此时还并不会修改硬盘上的数据
直到提交事务(commit)或回滚事务(rollback)，才会真正修改数据，将数据持久化到硬盘中

事务的四大特性

四大特性	含义
原子性	事务是最小的工作单元，不可再分
一致性	事务必须保证多条DML语句同时成功或同时失败
隔离性	事务A和事务B之间具有隔离，隔离级别不一样对事务的影响效果不同
持久性	最终数据必须持久化到硬盘文件中，事务才算成功结束

事务的隔离级别

隔离级别	含义
读未提交	当前事务可以读取到其他事务未提交的数据，会存在脏读(读到了脏数据)
读已提交	解决了脏读现象，但存在不可重复读的问题（oracle默认)
可重复读	解决了不可重复读问题，存在幻读问题（对方把数据都删了，依然能读到）,mysql默认
序列化读/串行化读	解决了以上所有问题，但是效率低，事务需要排队

mysql事务默认自动提交，只要执行任意一条DML语句提交一次，通过start transaction 关闭

-- 设置全局隔离级别为 read uncommitted
set global transaction isolation level read uncommitted
-- 查看全局隔离级别
select @@global.tx_isolation;
-- 开启事务
start transaction

数据库三范式

第一范式：任何一张表都因该有主键，并且每一个字段原子行不可再分
第二范式：建立在第一范式的基础上，所有非主键字段完全依赖主键，不产生部分依赖
第三范式：建立在第二范式基础之上，所有非主键字段直接依赖主键，不能产生传递依赖
实际开发中，以满足客户需求为主，有时候会拿冗余换执行速度

数据库优化

Oracle相关知识

有效利用数据字典

ORACLE 的数据字典是数据库的重要组成部分之一，它随着数据库的产生而产生, 随着数据库的变化而变化,

体现为 sys 用户下的一些表和视图。

数据字典名称是大写的英文字符。

数据字典里存有用户信息、用户的权限信息、所有数据对象信息、表的约束条件、统计分析数据库的视图等。

我们不能手工修改数据字典里的信息。

很多时候，一般的 ORACLE 用户不知道如何有效地利用它。

dictionary 全部数据字典表的名称和解释，它有一个同义词 dict

dict_column 全部数据字典表里字段名称和解释

如果我们想查询跟索引有关的数据字典时，可以用下面这条 SQL 语句:

SQL>select * from dictionary where instr(comments,‘index’)>0;

如果我们想知道 user_indexes 表各字段名称的详细含义，可以用下面这条 SQL 语句:

SQL>select column_name,comments from dict_columns where

table_name=‘USER_INDEXES’;

依此类推，就可以轻松知道数据字典的详细名称和解释，不用查看 ORACLE 的其它文档资料了。

--一些 ORACLE 用户常用数据字典的查询使用方法。
--1、用户
--查看当前用户的缺省表空间
SQL>select username,default_tablespace from user_users; 
--查看当前用户的角色
SQL>select * from user_role_privs; 
--查看当前用户的系统权限和表级权限
SQL>select * from user_sys_privs; 
SQL>select * from user_tab_privs; 

--2、表
--查看用户下所有的表
SQL>select * from user_tables; 
--查看名称包含 log 字符的表
SQL>select object_name,object_id from user_objects where instr(object_name,'LOG')>0; 
--查看某表的创建时间
SQL>select object_name,created from user_objects where object_name=upper('&table_name'); 
--查看某表的大小
SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&table_name');
--查看放在 ORACLE 的内存区里的表
SQL>select table_name,cache from user_tables where instr(cache,'Y')>0; 

--3、索引
--查看索引个数和类别
SQL>select index_name,index_type,table_name from user_indexes order by table_name; 
--查看索引被索引的字段
SQL>select * from user_ind_columns where index_name=upper('&index_name'); 
--查看索引的大小
SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&index_name'); 

--4、序列号
--查看序列号，last_number 是当前值
SQL>select * from user_sequences; 我们的论坛 http://forum.meslog.cn 

--5、视图
--查看视图的名称
SQL>select view_name from user_views; 
--查看创建视图的 select 语句
SQL>set view_name,text_length from user_views; 
SQL>set long 2000; 说明：可以根据视图的 text_length 值设定 set long 的大小
SQL>select text from user_views where view_name=upper('&view_name'); 

--6、同义词
--查看同义词的名称
SQL>select * from user_synonyms; 

--7、约束条件
--查看某表的约束条件
SQL>select constraint_name, constraint_type,search_condition, r_constraint_name 
from user_constraints where table_name = upper('&table_name'); 

SQL>select c.constraint_name,c.constraint_type,cc.column_name 
from user_constraints c,user_cons_columns cc 
where c.owner = upper('&table_owner') and c.table_name = upper('&table_name') 
and c.owner = cc.owner and c.constraint_name = cc.constraint_name 
order by cc.position; 

--8、存储函数和过程
--查看函数和过程的状态
SQL>select object_name,status from user_objects where object_type='FUNCTION'; 
SQL>select object_name,status from user_objects where object_type='PROCEDURE'; 
--查看函数和过程的源代码
SQL>select text from all_source where wner=user and 
name=upper('&plsql_name'); 

Oracle用户连接的管理

--用系统管理员，查看当前数据库有几个用户连接：
select username,sid,serial# from v$session; 

--如果要停某个连接用
alter system kill session 'sid,serial#'; 

--如果这命令不行,找它 UNIX 的进程数
-- 说明：21 是某个连接的 sid 数,然后用 kill 命令杀此进程号。
select pro.spid from vsession ses,vprocess pro where ses.sid=21 and ses.paddr=pro.addr; 

实际案例

数据转int

order by cast(序号 as UNSIGNED INTEGER)

表快速备份

-- 将后面的查询结果当作一张表创建
create table emp1 as select * from emp;
-- 复制部分数据
create table emp2 as select name ,job from emp;

查询筛选出带特殊字符的数据如%

当字段带特殊字符，如%时，需要进行转义才可以查询到

select * from table where 字段 like '%\%%' ESCAPE '\'

行转列

基于oracle

--创建视图，将指标数据列转行
CREATE OR REPLACE VIEW LY_VIEW_TEMP_XSFZGH AS 
select EJXY, 
       BJ, 
       XM, 
       XH,
       MBZ,
       replace(replace(LM,'LJ_',''),'_','.') AS LM,
       dxid 
from (select a.*,
             b.ID as dxid 
      from TEMP_XSFZGH a 
      left join LY_JX_FW_ZGDX b on a.xh=b.DXID and b.YYZBMC='20-21-2学期学生个人诊改'
     )
unpivot (MBZ for LM in (lj_1_1,lj_1_2,lj_1_3,lj_1_4,
                        lj_2_1,lj_2_2,lj_2_3,lj_2_4,lj_2_5,lj_2_6,
                        lj_3_1,lj_3_2,lj_3_3,lj_3_4,lj_3_5,lj_3_6,
                        lj_4_1,lj_4_2,lj_4_3,lj_4_4,lj_4_5,
                        lj_5_1_1,lj_5_1_2,lj_5_2,lj_5_3,lj_5_4
                        )
         ) t
;

查询一列数据中的某值所占人数，以及所占比例

Ratio_to_report() 括号中就是分子，over() 括号中就是分母，分母缺省就是整个占比

select GQGWDJDM,count(GQGWDJDM) as 人数,round((ratio_to_report(count(GQGWDJDM)) over())*100)||'%' as ZB
 from LY_ABI_RS_JZG_JBXX
group by GQGWDJDM
having GQGWDJDM is not null
order by GQGWDJDM

转换函数cast

字符类型数据排序时如果1-50，会先将所有开头1的排完序，再排2…
只有数值型的会从1排到50

-- cast(),用于转换数据类型的专用函数
select cast('2312'  as signed integer);

-- cast将字符转int
select gwmc,count(gwmc ) as rs
from LY_ABI_RS_JZG_JBXX 
group by gwmc 
order by cast(GWMC as INT)  asc;

分组查询年龄并约束年龄信息

获取CJDPRQ距今年份，CJDPRQ格式为：1990-11-12
通过获取当前年份-CJDPRQ前四位，再分组查询

select sum(case when (to_char(sysdate, 'yyyy' )-substr(CJDPRQ,1,4))<12 then 1 else 0 end) as NL011,
sum(case when (to_char(sysdate, 'yyyy' )-substr(CJDPRQ,1,4))<22 and (to_char(sysdate, 'yyyy' )-substr(CJDPRQ,1,4))>11 then 1 else 0 end) as NL1221,
sum(case when (to_char(sysdate, 'yyyy' )-substr(CJDPRQ,1,4))<32 and (to_char(sysdate, 'yyyy' )-substr(CJDPRQ,1,4))>21 then 1 else 0 end) as NL2231,
sum(case when (to_char(sysdate, 'yyyy' )-substr(CJDPRQ,1,4))<42 and (to_char(sysdate, 'yyyy' )-substr(CJDPRQ,1,4))>31 then 1 else 0 end) as NL3241
from LY_ABI_RS_JZG_JBXX  
where CJDPRQ is not null

case-when-when用法

select count(ZGH) as rs, 
case by05
when '1' then '工勤1级'
when '2' then '工勤2级'
when  '3' then '工勤3级'
when '4' then '工勤4级'
when '5' then '工勤5级'
when '6' then '普工'
else ' '  end  as jb
from LY_ABI_RS_JZG_JBXX where by05 is not null 
group by by05

通过转义处理字符中的单引号等

--字符中还有单引号需要处理，否则报错
update tb1 set text='test didn''t work';
update tb1 set text='test didn\'t work';

-- mysql中提取字符串时希望提取转义字符
select quote(text) from tb1;

操作字符

/*寻找字符串中子字符串的位置,mysql可以使用position(),返回0代表找不到*/
select position('chartsf' in vchar_fld) from tb1;

/*locate()用于在字符串的任意位置搜索,如在第五个字符后搜索*/
select locate('is',vchar_fld,5) from tb1;

/*strcmp()用于比较排序，对大小写不敏感,只有mysql中可以使用
返回-1代表第一个字符串排序在第二个之前，0代表相同，1代表之后*/
select strcmp('12345','12345') 12345_12345
	strcmp('125','1235') 12345_12345
	strcmp('abc','ABC') 12345_12345
/*mysql中使用like可以得到类似效果
例如，获取所有部门名称以及一个表达式，当名称为ns结尾返回1，否则返回0
*/
select name,name like '%ns' ends_in_ns from dept;

/*更复杂的匹配模式可以使用regexp
例如如果fed_id的值格式为xxx-xx-xxxx，is_ss_no_format列的结果就会返回1
*/
select cust_id,cust_cd,fed_id,fed_id regexp '.{3}-.{2}-.{4}' is_ss_no_format
from customer;


/*使用concat在末尾添加字符*/
update tb1 set text=concat(text,'aaa');

/*concat函数还可以根据独立的数据片段构建字符串,结果为
helen fleming has been a head teller since 2009
...
...
*/
select concat(fname,'',lname,'has been a',title,'since',start_date) emp_narrative from emp;
/*oracle中的concat只能接受两个字符串的参数，上查询不能出现，但是有替代方法*/
select fname||''||lname||'has been a'||title||'since'||start_date emp_narrative from emp;


/*在字符串中间增加或替换部分字符*/
/*1.mysql中，insert()接受4个参数：原始字符串，操作的开始位置，需要替换的字符数，替换字符串
根据第三个值，可以选择插入或替换原始字符串，如果该参数为0替换字符串就会插入，剩余字符串向右排列
*/
select insert('goodbye world',9,0,'cruel') string; [goodbye cruel world]
select insert('goodbye world'1,7,'hello') string;  [hello world]
/*2.oracle中可以使用replace()替换*/
select replace('goodbye world','goodbye','hello') from dual;

/*substring提取子字符串,oracle中为substr()
如下，提取第九个位置开始的5个字符
*/
select substring('goodbye cruel world',9,5);

操作数值

/*mod()用于求余，mysql和oracle通用*/
select mod(10,4);
/*pow()用于幂计算，oracle中为power()*/
select pow(2,8);   [256]


/*ceil()和floor()用于向上或向下截取整型数字*/
select ceil(72.445),floor(72.445)

/*round()四舍五入，可以选择第二个参数指定保留几位小数,第二参数可以选择负数，表示小数点左侧取整多少位*/
select round(23.44343,2);

/*round内可以用查询语句的结果运算*/
select round(((select sum(班级总数) from ztsj_2018_a7_1_1开设专业 where 采集时点=${参数:周期}+1)/(select count(1) from ztsj_2018_a8_3专职学生管理人员基本情况 where 采集时点=${参数:周期}+1)),2)

/*truncate()，截取小数位，第二参数可以是负数，表示小数点左侧截取多少位*/
selecet truncate(32.2332,2);   [32.23]

/*sing(),当数值为负数时返回-1，为0时返回0，为正数时返回1*/

/*abs(),返回账户余额的绝对值*/

操作时间数据

/*current_date(),返回当前日期
current_time(),返回当前时间
current_timestamp()，返回当前日期与时间*/

/*date_add(),指定日期增加任意一段时间间隔，如当前日期增加5天*/
select date_add(current_date(),interval 5 day);

/*oracle 中可以使用add_months()实现上函数*/
update employee set date=add_months(b_date,110) where emp_id=342;

/*last_day(),获取当月最后一天，返回一个date值*/

/*datename(),确定日期为星期几,mysql中使用*/

/*extract(),提取时间中的信息，如年份*/
select extract(year from '2008-09-08 22:12:34');

/*datediff()，返回日期之间的天数*/

给查询结果添加后缀

--mysql通过concat函数将两个字符串连接起来,形成单一字符串,如下，为查询结果添加百分号
select concat(round(((select COUNT(1) from aec5b6d69d_2021_用人单位满意度信息 where XXPJZK NOT LIKE '%不%')/(select count(1) 
FROM aec5b6d69d_2021_用人单位满意度信息))*100,2),'%') 

oracle中可以通过||为查询结果添加后缀，但是在mysql中会导致结果总为1
select abc||'%' from test 

账户设置密码不过期

/*查看dba_users字典，explay_date显示过期时间*/
select  username, user_id, account_status, expiry_date, profile  from  dba_users  where  username = 'ETL_TEST';

/*看一下，profile参数为default，所指定的密码有效期为多长时间*/
SELECT  *  FROM  dba_profiles  s  WHERE  s.profile = 'DEFAULT'  AND  resource_name = 'PASSWORD_LIFE_TIME';

/*创建名为passwd_unlimit的profile文件，设置其profile下密码限定为永不过期*/
create profile  passwd_unlimit limit  PASSWORD_LIFE_TIME unlimited;

/*把passwd_unlimit的初始化参数设置到ETL_TEST用户下*/
alter user   ETL_TEST   profile   passwd_unlimit;

/*再次查看，看explay_date是否改过来了*/
select  username, user_id, account_status, expiry_date, profile  from  dba_users  where  username = 'ETL_TEST';

/*解锁用户*/
/*首先在公司配置文件中查看被锁住账号密码,然后在oracle中执行解锁*/
alter user 用户名 identified by <原来的密码> account unlock; ----不用换新密码

将一列的值替换成另一张表列的值

1.通过子查询实现
该方法要注意tab2中的数据要完全等于tab1
如果tab1中有id为1，val为xx的列，而tab2中没有该条数据
更新后，tab1中的id为1的val将更新值为null

update tab1 set val=(select val from tab2 where tab1.id=tab2.id);

2.子查询加以限制
改进了对于 table1 中有，但是table2中不存在的idd，不做修改
存在问题：
如果table2中继续插入数据后，一个idd对应多个val，并且在table1中有对应idd时
执行后会报错：ORA-01427:单行子查询返回多个行

update table1 set val = (select val from table2 where table1.idd = table2.idd)
where exists (select 1 from table2 where table1.idd = table2.idd)

3.使用merge
此时若table2 中有一个idd对应多个val，并且在table1中有对应idd时，还是会报错。
ORA-30926: 无法在源表中获得一组稳定的行

merge into table1
using table2
on (table1.idd = table2.idd)
when matched then
update set table1.val = table2.val

通过增加限制条件，可以解决问题
下述方法在using后面构造了一个新的table2，一定要对val做出处理
如果是varchar类型，可以选择 max，min等函数
如果number类型，可以使用sum，avg等函数
总之，要对val做出筛选，新的table2是一个idd对应一个val

merge into table1
using (select t.idd ,max(t.val) m from table2 t group by t.idd)table2
on (table1.idd = table2.idd)
when matched then
update set table1.val = table2.m

空值处理

sql中若查出的值为null，再与任何值运算都为空，要将空值替换成0
COALESCE(exp1,exp2)：多项替换函数，当exp1不为空时返回exp1，否则返回exp2

COALESCE(exp1,exp2)