SQL笔记

SQL优化

2022-12-31转换成20221231

CONVERT这个函数先将字符串转成时间，再讲时间转换成字符串
select CONVERT(varchar, CONVERT(date, ‘2023-10-19’,23), 112)

时间转年、月、日

SQL从时间字段值中获取年份使用DATENAME()函数。

DATENAME()函数语法：DATENAME(param,date)

date是时间字段名 或一个时间值

param是指定要返回日期部分的参数,包括下面几种:

–获取日期字段的年
select to_char(sysdate,‘yyyy’) as year from dual

或者：(指定日期)

select to_char(to_date(‘2013/08/08’,‘yyyy/mm/dd’),‘yyyy’) as year from dual

–获取日期字段的月
select to_char(sysdate,‘mm’) as month from dual

–获取日期字段的日
select to_char(sysdate,‘dd’) as day from dua

1.避免使用select *

2.union all 代替 union

union all 可以获取所有数据，包括重复数据
union 会排重，其过程需要遍历排序和比较 更耗时、更消耗CPU资源

3.小表驱动大表

4.批量操作尽量不要用for循环

SQL虚拟表

select * from dual

当前时间

sysdate

下划线转驼峰

# 控制台输出sql、下划线转驼峰
mybatis:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    map-underscore-to-camel-case: true

1. 设置传参、定义局部变量、局部变量赋值

ALTER PROCEDURE [dbo].[test]

@ID VARCHAR(100) --执行存储过程的传参

AS

BEGIN TRY

 

​    declare @COUNTA int; -- 定义全局存储过程参数

 

​    set @COUNTA = (select COUNT(*) from APP_BATCH_JOB_LOG); -- 给参数赋值

​    

​    if @ID>@COUNTA -- if判断

​       select COUNT(*) as id from APP_BATCH_JOB_LOG;

​    else

​       select * from APP_BATCH_JOB_LOG where id = @id;

​    

END

2.try catch

begin try

--SQL 

end try 

begin catch 

--sql (处理出错动作)

end catch

时间转换

https://www.cnblogs.com/damugua/p/14681300.html

取正数

SELECT ABS(-**123**); -- 123

 

# SQL拼接
```sql

可以传好几个参数

concat(cast(isnull(b.AVERAGE_RATE,0.0) as numeric(20,2)),'%')as AVERAGE_RATE

 

SELECT concat(code,'-',DESCRIPTION) FROM RP_D_REPORTLINE

取年

SELECT

datepart(yy,DATEADD(YEAR,-1,asof_date)) as lastYear,

datepart(yy,asof_date) as year

from RP_S_ASOFDATE

强转位小数

*Convert**(*decimal*(*18,2*)*,rd.AMOUNT*)* as AMOUNT,

删除表某个字段

   ALTER TABLE FMD_ADB DROP COLUMN ID;


   ALTER TABLE a表 DROP COLUMN  a表的某个字段;

# 添加某个字段
```sql

ALTER TABLE FMD_ADB add COLUMN  ID;

--- 对的

ALTER TABLE 表名 ADD字段名 nvarchar(200)

修改字段长度

alter table Table1 alter column Column1 varchar(10)

两表合并

select count*(***)* from T_FMDDATA_T24 union all
     select count*(***)* from FBNK_LIMIT_T24 union all
     select count*(***)* from T_TRIAL_BALANCE_T24

存储过程执行某个字段里的SQL语句

ALTER PROCEDURE [dbo].[aa]
AS
BEGIN
		create table #tmp(
		number int,
		code int,
		name NVARCHAR(200)
	)

	DECLARE @sqlMain NVARCHAR(4000),@balance_usd INT

       declare my_cursor cursor for
       SELECT code FROM RP_D_DEPARTMENT 
       open my_cursor
			 
			 DECLARE @dept NVARCHAR(50);
			 DECLARE @numb NVARCHAR(50);
 DECLARE @name NVARCHAR(200);
       fetch next from my_cursor into @dept
      
       while @@FETCH_STATUS=0
       begin
	-- routine body goes here, e.g.
	-- SELECT 'Navicat for SQL Server'
	SELECT @sqlMain = REPLACE(concat('set @balance_usd= (',db_dyn_sql,')'),'${department}',@dept )FROM jimu_report_db WHERE jimu_report_id = '707485132628443136' and db_ch_name = 'dca'
	 
	
	execute sp_executesql @sqlMain,N'@balance_usd NUMERIC(38,8) output',@balance_usd OUTPUT
	
	--select  @name = name from  RP_D_DEPARTMENT where cdoe = @dept
	
select @name= name from  RP_D_DEPARTMENT where CODE = @dept
	INSERT INTO #tmp VALUES (@balance_usd,@dept,@name)
	
	
	
	    fetch next from my_cursor into @dept --获取下一条数据并赋值给变量
       end--关闭释放游标
       close my_cursor;
       deallocate my_cursor;
			 
			 select * from  #tmp
END

# 创建索引

1、使用CREATE INDEX创建，语法如下：

CREATE INDEX indexName ON tableName (columnName(length));

2、使用ALTER语句创建，语法如下：

ALTER TABLE tableName ADD INDEX indexName(columnName);

ALTER语句创建索引，下面提供一个设置索引长度的例子：

ALTER TABLE t_user_action_log ADD INDEX ip_address_idx (ip_address(16));

SHOW INDEX FROM t_user_action_log;

3、建表的时候创建索引

CREATE TABLE tableName(  
  id INT NOT NULL,   
  columnName  columnType,
  INDEX [indexName] (columnName(length))  
);

示例：

X ip_address_idx (ip_address(16));

SHOW INDEX FROM t_user_action_log;

3、建表的时候创建索引


```sql 
CREATE TABLE tableName(  
  id INT NOT NULL,   
  columnName  columnType,
  INDEX [indexName] (columnName(length))  
);