Sql常用语句

mysql

select cast((cast(sysdate()as Date) - interval 1 day) as datetime) as YesDay;     -- 获取当前时间的前一天凌晨
select * from [table] where DATE_FORMAT(ModifiedDate,'%Y-%m-%d') = '2019-01-10';  -- 根据时间查询  
-- 模糊统计表各行数
SELECT
concat( 'select "', TABLE_name, '", count(*) from ', TABLE_SCHEMA, '.', TABLE_name, ' union all' )
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = 'paas_data_center' and table_name like 'ent_datacenter_inspect_sale_%';
-- 动态执行sql
SET @tb=(select GROUP_CONCAT(table.filed) from table);
SET @sql = CONCAT('SELECT * FROM ', @tb);
PREPARE st FROM @sql;
EXECUTE st;
DEALLOCATE PREPARE st;
-- 批量删除外键
select GROUP_CONCAT(m.shell separator ';') from  (SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' DROP FOREIGN KEY ',CONSTRAINT_NAME,'')
FROM information_schema.TABLE_CONSTRAINTS c 
WHERE c.TABLE_SCHEMA='erp' AND c.CONSTRAINT_TYPE='FOREIGN KEY') m;
-- 查询父节点
with recursive cte(id,name,parent_id) as (
    select id,name,parent_id from [table] where id=#{id}
    union all
    select t1.id,t1.name,t1.parent_id from [table] t1 inner join cte t2 on t1.id = t2.parent_id
) select * from cte;
-- 查询子节点
with recursive cte(id,name,parent_id) as (
    select id,name,parent_id from [table] where id=#{id}
    union all
    select t1.id,t1.name,t1.parent_id from [table] t1 inner join cte t2 on t1.parent_id = t2.id
) select * from cte;

mssql

-- 字段值拆分 a/b/c -> province:a,city:b,district:c
SELECT
    ti.name,
    PARSENAME(REPLACE([region],'/','.'),3) as province,
    PARSENAME(REPLACE([region],'/','.'),2) as city,
    PARSENAME(REPLACE([region],'/','.'),1) as district
FROM
    tmp_institution ti;
select CONVERT(VARCHAR(10),GETDATE(),120)          -- 日期转换;结果: 2019-11-20
select CONVERT(VARCHAR(10),'2019-11-20T00:00',127) -- 日期转换;结果: 2019-11-20
-- 省市单列值拆分成列
with pre_where as (
    SELECT id,value,row_number() over(PARTITION by id order by id) as rank
    FROM (select '1' as id,'黑龙江省-哈尔滨市' as province_city
          union all
          select '2' as id,'山东省-临沂市' as province_city) t
    CROSS APPLY STRING_SPLIT(province_city, '-')
)
select 
ps.id,
ps.value as province,
cs.value as city
from pre_where ps
inner join pre_where cs on cs.id = ps.id
where ps.rank = 1 and cs.rank = 2;
--- 获取表字段信息
declare @structure table(
    TABLE_QUALIFIER VARCHAR(255),
    TABLE_OWNER VARCHAR(255),
    TABLE_NAME VARCHAR(255),
    COLUMN_NAME VARCHAR(255),
    DATA_TYPE VARCHAR(255),
    TYPE_NAME VARCHAR(255),
    "PRECISION" VARCHAR(255),
    "LENGTH" VARCHAR(255),
    SCALE VARCHAR(255),
    RADIX VARCHAR(255),
    NULLABLE VARCHAR(255),
    REMARKS VARCHAR(255),
    COLUMN_DEF VARCHAR(255),
    SQL_DATA_TYPE VARCHAR(255),
    SQL_DATETIME_SUB VARCHAR(255),
    CHAR_OCTET_LENGTH VARCHAR(255),
    ORDINAL_POSITION VARCHAR(255),
    IS_NULLABLE VARCHAR(255),
    SS_DATA_TYPE VARCHAR(255)
);
insert into @structure EXEC sp_columns @table_name = '%',@table_owner = 'dbo';
select
 s.column_name AS [column],
 replace(dbo.Proper(s.column_name),'_','') as property_key
from @structure s;
select
t.name as table_name,
c.name as column_name,
ec.value as column_comment
from sys.tables t
inner join sys.columns c on c.object_id = t.object_id
inner join sys.extended_properties ec on ec.major_id = t.object_id
and ec.minor_id = c.column_id
where t.object_id = object_id('MX_ContactRecords');
-- 驼峰方法
Create Function dbo.Proper(@Data VarChar(8000))
Returns VarChar(8000)
As
Begin
  Declare @Position Int

  Select @Data = Stuff(@Data, 1, 1, LOWER(Left(@Data, 1))),
         @Position = PatIndex('%[^a-zA-Z][a-z]%', @Data COLLATE Latin1_General_Bin)

  While @Position > 0
    Select @Data = Stuff(@Data, @Position, 2, Upper(SubString(@Data, @Position, 2))),
           @Position = PatIndex('%[^a-zA-Z][a-z]%', @Data COLLATE Latin1_General_Bin)

  Return @Data
End
declare @structure table(
    TABLE_QUALIFIER VARCHAR(255),
    TABLE_OWNER VARCHAR(255),
    TABLE_NAME VARCHAR(255),
    COLUMN_NAME VARCHAR(255),
    DATA_TYPE VARCHAR(255),
    TYPE_NAME VARCHAR(255),
    "PRECISION" VARCHAR(255),
    "LENGTH" VARCHAR(255),
    SCALE VARCHAR(255),
    RADIX VARCHAR(255),
    NULLABLE VARCHAR(255),
    REMARKS VARCHAR(255),
    COLUMN_DEF VARCHAR(255),
    SQL_DATA_TYPE VARCHAR(255),
    SQL_DATETIME_SUB VARCHAR(255),
    CHAR_OCTET_LENGTH VARCHAR(255),
    ORDINAL_POSITION VARCHAR(255),
    IS_NULLABLE VARCHAR(255),
    SS_DATA_TYPE VARCHAR(255)
);
insert into @structure EXEC sp_columns @table_name = '%',@table_owner = 'dbo';
select
 s.column_name AS [column],
 replace(dbo.Proper(s.column_name),'_','') as property_key
from @structure s
where s.table_name = 'table_name';
-- 创建测试数据
if OBJECT_ID(N'[test_person]',N'U') is not null
    drop table [test_person];
CREATE TABLE [dbo].[test_person] (
  [id] varchar(255) NOT NULL,
  [code] varchar(255) NULL,
  [name] varchar(255) NULL,
  [age] int NULL,
  [created_time] datetime2 NULL,
  PRIMARY KEY ([id])
);
-- 取指定范围内的随机时间
declare @Date_start datetime 
declare @Date_end datetime 
set @Date_start= '2018-06-01' 
set @Date_end=getdate();
-- 取指定范围内的随机数
DECLARE @range_number INT
DECLARE @left INT
DECLARE @right INT
-- 取随机名字
DECLARE @fName TABLE(Id INT IDENTITY(1,1) PRIMARY KEY, NAME NVARCHAR(20)) -- 姓氏
DECLARE @lName TABLE(Id INT IDENTITY(1,1) PRIMARY KEY, NAME NVARCHAR(20)) -- 名字
INSERT @fName VALUES
('赵'),('钱'),('孙'),('李'),('周'),('吴'),('郑'),('王'),('冯'),('陈'),('楮'),('卫'),('蒋'),('沈'),('韩'),('杨'),
('朱'),('秦'),('尤'),('许'),('何'),('吕'),('施'),('张'),('孔'),('曹'),('严'),('华'),('金'),('魏'),('陶'),('姜'),
('戚'),('谢'),('邹'),('喻'),('柏'),('水'),('窦'),('章'),('云'),('苏'),('潘'),('葛'),('奚'),('范'),('彭'),('郎'),
('鲁'),('韦'),('昌'),('马'),('苗'),('凤'),('花'),('方'),('俞'),('任'),('袁'),('柳'),('酆'),('鲍'),('史'),('唐'),
('费'),('廉'),('岑'),('薛'),('雷'),('贺'),('倪'),('汤'),('滕'),('殷'),('罗'),('毕'),('郝'),('邬'),('安'),('常'),
('乐'),('于'),('时'),('傅'),('皮'),('卞'),('齐'),('康'),('伍'),('余'),('元'),('卜'),('顾'),('孟'),('平'),('黄'),
('和'),('穆'),('萧'),('尹')
INSERT @lName VALUES 
('爱'),('安'),('百'),('邦'),('宝'),('保'),('抱'),('贝'),('倍'),('蓓'),('本'),('砥'),('典'),('佃'),('必'),('碧'),
('璧'),('斌'),('冰'),('兵'),('炳'),('步'),('彩'),('曹'),('昌'),('长'),('常'),('超'),('朝'),('陈'),('晨'),('成'),
('呈'),('承'),('诚'),('崇'),('楚'),('传'),('春'),('纯'),('翠'),('村'),('殿'),('丁'),('定'),('东'),('冬'),('二'),
('凡'),('方'),('芳'),('昉'),('飞'),('菲'),('纷'),('芬'),('奋'),('风'),('峰'),('锋'),('凤'),('芙'),('福'),('付'),
('复'),('富'),('改'),('刚'),('高'),('阁'),('铬'),('根'),('庚'),('耕'),('公'),('功'),('冠'),('光'),('广'),('归'),
('桂'),('国'),('海'),('寒'),('翰'),('昊'),('浩'),('荷'),('红'),('宏'),('洪'),('鸿'),('厚'),('华'),('存'),('大'),
('丹'),('道'),('德'),('登');
SET @left = 20
SET @right = 50
DECLARE @n INT
SET @n=1
WHILE @n <= 10000
  BEGIN
      INSERT [test_person]([id],[code],[name],[age],[created_time])
      VALUES ( Newid(),
               '48'+ RIGHT(Cast(@n AS NVARCHAR(10)), 8),
               (SELECT RTRIM((SELECT NAME FROM @fName WHERE Id = Round(Rand()*(100-1)+1,0)))+RTRIM(LTRIM((SELECT NAME FROM @lName WHERE Id = Round(Rand()*(100-1)+1,0))))+RTRIM(LTRIM((SELECT NAME FROM @lName WHERE Id = Round(Rand()*(100-1)+1,0))))),
               (select ROUND(((@right - @left -1) * RAND() + @left), 0)),
               (select dateadd(minute,abs(checksum(newid()))%(datediff(minute,@Date_start,@Date_end)+1),@Date_start)));
      SET @n=@n + 1
  END
select
*
from test_person