SQL语法、函数和注意事项

统计一下这几年用过的函数和踩过的坑

数据库几种常见的driverClassName和url

##########
HSQLDB
##########
jdbc.driverClassName=org.hsqldb.jdbcDriver
jdbc.url=jdbc:hsqldb:hsql://localhost:9001/bookstore
jdbc.username=sa
jdbc.password=
###########


MySQL 5
###########
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=GBK
jdbc.username=root
jdbc.password=root
##############

PostgreSQL
##############
jdbc.driverClassName=org.postgresql.Driver
jdbc.url=jdbc:postgresql://localhost/bookstore
jdbc.username=
jdbc.password=
##########

Oracle
##########
jdbc.driverClassName=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:@192.168.1.250:1521:devdb
jdbc.username=HFOSPSP
jdbc.password=HFOSPSP
#############################

MS SQL Server 2000 (JTDS)
#############################
jdbc.driverClassName=net.sourceforge.jtds.jdbc.Driver
jdbc.url=jdbc:jtds:sqlserver://localhost:1433/bookstore
jdbc.username=
jdbc.password=
##################################

MS SQL Server 2000 (Microsoft)
##################################
jdbc.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc.url=jdbc:sqlserver://192.168.1.130:1433;database=ahos;user=sa;password=ahtec";
jdbc.username=sa
jdbc.password=ahtec
########

ODBC
########
jdbc.driverClassName=sun.jdbc.odbc.JdbcOdbcDriver
jdbc.url=jdbc:odbc:bookstore
jdbc.username=
jdbc.password= 

数据库连接：serverTimezone

serverTimezone=UTC 
UTC，简称世界统一时间，跟北京时间相比，比北京早8个小时，也就是说，北京2020年3月20日18点的时候，UTC时间为2020年3月20日10点
所以存储的时间不对了
调整
serverTimezone=Asia/Shanghai

解决sql Server连接jdbc的方法：

 使用jtds
 gradle引入：
 compile group: 'net.sourceforge.jtds', name: 'jtds', version: '1.2'
 连接信息：
 driver-class-name: net.sourceforge.jtds.jdbc.Driver
 url: jdbc:jtds:sqlserver://ip:1433;DatabaseName=数据库;

sql server连接问题

sql server的驱动

1、找到 jdk下面的jre/lib/secuity下的java.security 文件

2、打开文件找到 jdk.tls.disabledAlgorithms 属性

3、删掉 TLSv1, TLSv1.1,3DES_EDE_CBC 保存重新启动即可。

MySQL字符串去重 存储函数

 create function deleteManyChar(in_str varchar(600))
RETURNS VARCHAR(600) 
BEGIN
  DECLARE  v_result VARCHAR(600);
	DECLARE  v_strs VARCHAR(600);
	DECLARE  v_obj VARCHAR(64);
	IF (in_str is not null) THEN 
    SET v_strs = in_str;
		while INSTR(v_strs,',') > 0 do
			SET v_obj =  SUBSTR(v_strs, 1, INSTR(v_strs, ','));
			SET v_strs = SUBSTR(v_strs, INSTR(v_strs, ',') + 1);
			
			if(v_result is null)  THEN
					SET v_result = v_obj; 
			else 
					if(INSTR(v_result, v_obj) = 0) THEN
						SET v_result =  CONCAT(v_result,v_obj);
					end if;
			end if;
		end while; 
  ELSE 
    SET v_result = in_str;
  END IF ;
   
  RETURN v_result ;
END

ORACLE 查询字段只包含数字的数据

SELECT * FROM TABLE
 WHERE REGEXP_SUBSTR(CHECK, '^[0-9\.\-]\d*\.{0,1}\d*$') IS NOT NULL;

MySQL 查询字符串包含字段：

排名函数：

--ROW_NUMBER()  自增序号   1、2、3、4、5
SELECT ITEM_NAME,ITEM_PRICE, ROW_NUMBER() over(ORDER BY ITEM_PRICE DESC) FROM  SERVICE_ITEMS  

--RANK()  自增序号 相同并列，跳跃排序  1、2、2、4、5
SELECT ITEM_NAME,ITEM_PRICE, RANK() over(ORDER BY ITEM_PRICE DESC) FROM  SERVICE_ITEMS  

--DENSE_RANK()  自增序号 相同并列，非跳跃排序  1、2、2、3、4、5
SELECT ITEM_NAME,ITEM_PRICE, DENSE_RANK() over(ORDER BY ITEM_PRICE DESC) FROM  SERVICE_ITEMS  


--  NTILE(5)分成五个分区，各自排序，具体用法目前占不明白
SELECT ITEM_NAME,ITEM_PRICE, NTILE(5) over(ORDER BY ITEM_PRICE DESC) FROM  SERVICE_ITEMS  


-- 分区排序（PARTITION BY ITEM_PRICE）通过ITEM_PRICE分区，相同的在一个区域，再更具ITEM_NAME排序
SELECT ITEM_NAME,ITEM_PRICE, ROW_NUMBER() over(PARTITION BY ITEM_PRICE  ORDER BY ITEM_NAME  DESC) FROM  SERVICE_ITEMS  


-- 统计讲前行 ITEM_PRICE 和前两行和后两行求和
select ITEM_NAME, ITEM_PRICE,sum(ITEM_PRICE)over(order by ITEM_PRICE rows between 2 preceding and 2 following) sum from SERVICE_ITEMS

如下：
血清白蛋白测定	4	12  
血清白蛋白测定	4	16
血清低密度脂蛋白胆固醇测定	4	20
血清总胆红素测定	4	16
钾测定	4	12

MySQL limit offset 分页查询（数据量大）

当limit offset rows中的offset很大时，会出现效率问题
#select * from test where val=4 limit 300000,5;

为了达到相同的目的，我们一般会改写成如下语句
select * from test a inner join
 (select id from test where val=4 limit 300000,5) b on a.id=b.id;

查找函数

instr()函数
格式一：instr( string1, string2 )    // instr(源字符串, 目标字符串)
格式二：instr( string1, string2 [, start_position [, nth_appearance ] ] )   // instr(源字符串, 目标字符串, 起始位置, 匹配序号)
解析：string2 的值要在string1中查找，是从start_position给出的数值（即：位置）开始在string1检索，检索第nth_appearance（几）次出现string2。

例：select * from tableName where instr(name,'helloworld')>0;
注：MySQL中的模糊查询 like 和 Oracle中的 instr() 函数有同样的查询效果

decode函数

 1、decode(expression,value,result1,result2)
 
 如果expression=value，则输出result1，否则输出result2


2、decode(expression,value1,result1,value2,result2,value3,result3......,default)

如果expression=value1，则输出result1，expression=value2，输出reslut2，expression=value3，输出result3，若expression不等于所列出的所有value，则输出为default

其余函数

#first_value 第一个
first_value(xx) over(partition by xx order by xx)

#last_value
last_value(xx) over(order by xx)

#忽略null
first_value(xx ignore nulls) over(xx)

#lag(n) over()不统计前N行
lag(expresstion,,)
with a as
(select 1 id,‘a’ name from dual
union
select 2 id,‘b’ name from dual
union
select 3 id,‘c’ name from dual
union
select 4 id,‘d’ name from dual
union
select 5 id,‘e’ name from dual
)
select id,name,lag(id,3,‘’)over(order by name) from a;

lead(n) over() 不统计后N行

lead(expresstion,<offset>,<default>)
with a as
(select 1 id,'a' name from dual
 union
 select 2 id,'b' name from dual
 union
 select 3 id,'c' name from dual
 union
 select 4 id,'d' name from dual
 union
 select 5 id,'e' name from dual
)
select id,name,lead(id,1,'')over(order by name) from a;

待更新