mysql命令自动补全 sql中自动补全命令快捷键

转载

温柔一刀 2023-05-30 17:33:12

文章标签 mysql命令自动补全 SQL Sql Prompt sql 锁表 文章分类 MySQL 数据库

SQL Prompt不仅根据数据库对象名称,语法,代码片段进行自动检索匹配唯一合适的代码,还提供了丰富的快捷键,只需要写上两三个字符,按下回车,就可以为我们自动填写预设好的代码片段,大大提高了编写sql的效率。

一、一些SqlPrompt预设的部分快捷键.

mysql命令自动补全 sql中自动补全命令快捷键_mysql命令自动补全

二、SQL Prompt中的自动补全及其设置

SQL Prompt代码段是可以插入查询的代码块。要插入代码段，请在查询中键入代码段名称（例如 ssf），然后按 Enter键或任何其他插入键：

mysql命令自动补全 sql中自动补全命令快捷键_sql_02

类似的自动补全,可以在Sql Prompt的配置中进行配置.

mysql命令自动补全 sql中自动补全命令快捷键_mysql命令自动补全_03

mysql命令自动补全 sql中自动补全命令快捷键_锁表_04

图片上有三个按钮对应新建、编辑、删除。根据需要可自助添加或修改很方便，下方附部分常用的sql

附：

常用sql 具体需根据实际情况调整

1. 计算百分比( C_Percentage )

select CONVERT(varchar, CONVERT(decimal(18,3), CONVERT(float,230000 *100/CONVERT(float,2100000))))+'%' RATE

2. casewhenthen条件（C_casewhen）

select case when State=1  then '' when State=2   then '2' else '3' end  as Result

3. 查看语句执行时间含CPU及其占用时间（C__execute_time）

--查看语句执行时间 含CPU及其占用时间
 SET STATISTICS TIME ON;
 
     SELECT *  FROM    TABLE1
      
 SET STATISTICS TIME OFF;

4.关于GetDate() 几种方式

select GETDATE() as '当前日期',
DateName(year,GetDate()) as '年',
DateName(month,GetDate()) as '月',
DateName(day,GetDate()) as '日',
DateName(dw,GetDate()) as '星期',
DateName(week,GetDate()) as '周数',
DateName(hour,GetDate()) as '时',
DateName(minute,GetDate()) as '分',
DateName(second,GetDate()) as '秒',
DATEPART(second,GETDATE()) as '秒',
convert(varchar,getdate(),121)
-- DATEPART 与 DateName 都可

5.查看被锁表及锁表语句、解锁

--查看被锁表：
SELECT
request_session_id spid,
OBJECT_NAME(
resource_associated_entity_id
) tableName
FROM
sys.dm_tran_locks
WHERE
resource_type ='OBJECT'
ORDER BY request_session_id ASC
--spid 锁表进程 
--tableName 被锁表名

--根据锁表进程查询相应进程互锁的SQL语句 以SPID=249为例
DBCC INPUTBUFFER (249)

-- 解锁：
KILL 249

--或着-- 解锁：
DECLARE
@spid INT
SET @spid = 52--锁表进程
DECLARE
@SQL VARCHAR (1000)
SET @SQL = 'kill ' +CAST(@spid AS VARCHAR) 
EXEC (@SQL)

 
--生成解锁SQL
SELECT
DISTINCT 'DECLARE @spid INT SET @spid = ',request_session_id,' DECLARE @SQL VARCHAR (1000) SET @SQL = ''kill '' + CAST (@spid AS VARCHAR) EXEC (@SQL);' AS s
FROM
sys.dm_tran_locks
WHERE
resource_type = 'OBJECT' --spid 锁表进程 
--tableName 被锁表名

6. 使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息（C_nocount ）

SET NOCOUNT ON;----使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息

SET NOCOUNT OFF;

7.打印实时时间可用于多语句调试（C_PrintTime）

PRINT CONVERT(VARCHAR,GETDATE(),121)

8.查找/筛选某数据库中所有存储过程中含某字符的 (C_Proc_Search)

---- 查找/筛选 某数据库中 所有存储过程中 含 某 字符的
SELECT  obj.name 存储过程名 ,
        sc.text 存储过程内容
FROM    syscomments sc
        INNER JOIN sysobjects obj ON sc.id = obj.id
WHERE   sc.text LIKE '%INPEOCESS%';

9.数据库字典(C_DatabaseDictionary)

SELECT TOP 100 PERCENT --a.id,   
      CASE WHEN a.colorder = 1 THEN d.name ELSE '' END AS 表名,   
      CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END AS 表说明,   
      a.colorder AS 字段序号, a.name AS 字段名, CASE WHEN COLUMNPROPERTY(a.id,   
      a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 标识,   
      CASE WHEN EXISTS  
          (SELECT 1  
         FROM dbo.sysindexes si INNER JOIN  
               dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN  
               dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN  
               dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK'  
         WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END AS 主键,   
      b.name AS 类型, a.length AS 长度, COLUMNPROPERTY(a.id, a.name, 'PRECISION')   
      AS 精度, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数,   
      CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允许空, ISNULL(e.text, '')   
      AS 默认值, ISNULL(g.[value], '') AS 字段说明, d.crdate AS 创建时间,   
      CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END AS 更改时间  
FROM dbo.syscolumns a LEFT OUTER JOIN  
      dbo.systypes b ON a.xtype = b.xusertype INNER JOIN  
      dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND   
      d.status >= 0 LEFT OUTER JOIN  
      dbo.syscomments e ON a.cdefault = e.id LEFT OUTER JOIN  
      sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id AND   
      g.name = 'MS_Description' LEFT OUTER JOIN  
      sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0 AND   
      f.name = 'MS_Description'  
     -- where  d.name ='SN_Status'----具体哪张表
ORDER BY d.name, 字段序号

10.单列转行——举例说明

准备数据

create table tb([name] nvarchar(200))
insert into tb values('[)>0618VLELME1PKRF 901 43821PR1D22PFU 4449 B5+B12A12D20190911SDK45501590') 
insert into tb values('[)>0618VLELME1PKRF 901 43821PR1D22PFU 4449 B5+B12A12D20190911SDK45501555')
-----最后要删掉 drop table tb或者 用以下sql 可不删
SELECT  [NAME] INTO #TB FROM (SELECT '[)>0618VLELME1PKRF 901 43821PR1D22PFU 4449 B5+B12A12D20190911SDK45501590' [NAME] UNION SELECT '[)>0618VLELME1PKRF 901 43821PR1D22PFU 4449 B5+B12A12D20190911SDK45501555')TA

declare @sql varchar(1000)
set @sql = ''
select @sql = @sql + t.name+';' from (select name from tb) as t
PRINT  @sql
--set @sql='select result = ''' + @sql + ''''
--exec(@sql)
/*
result 
------ 
[)>0618VLELME1PKRF 901 43821PR1D22PFU 4449 B5+B12A12D20190911SDK45501590;[)>0618VLELME1PKRF 901 43821PR1D22PFU 4449 B5+B12A12D20190911SDK45501555;
*/

--coalesce函数 与isnull函数用法相同
declare @output varchar(8000)
select @output = coalesce(@output , '') + name+';' from tb
print @output
/*
[)>0618VLELME1PKRF 901 43821PR1D22PFU 4449 B5+B12A12D20190911SDK45501590;[)>0618VLELME1PKRF 901 43821PR1D22PFU 4449 B5+B12A12D20190911SDK45501555;

*/

declare @res varchar(8000)
    --不建议用，xml格式会将特殊字符转换 如>转义>
     SELECT  @res =STUFF((SELECT  ';'+A.name    FROM  tb A  for xml path('')),1,1,'')
     PRINT @res
  
/*
[)>0618VLELME1PKRF 901 43821PR1D22PFU 4449 B5+B12A12D20190911SDK45501590;[)>0618VLELME1PKRF 901 43821PR1D22PFU 4449 B5+B12A12D20190911SDK45501555
*/

11.不定列名行转列--例子

DECLARE @SQL_COL NVARCHAR(MAX)
DECLARE @SQL_STR NVARCHAR(MAX)
CREATE TABLE #T(
    [PARAMETER] [NVARCHAR](MAX),
    [TEST_RESULT] [FLOAT],
    [SERIAL_NUMBER] [NVARCHAR](MAX),
    [TEST_TIME] [DATETIME],
    [UPDATE_USER] [VARCHAR](50),
    [RESULT] [VARCHAR](50)
)

INSERT INTO #T
SELECT * FROM(
    SELECT CASE WHEN CHARINDEX(A.CPARAMETER,A.CREQUIREMENT)>0 
                    THEN A.CREQUIREMENT 
                WHEN LEN(A.CREQUIREMENT)=0 
                    THEN A.CPARAMETER+' '+A.CFREQUENCY 
                ELSE A.CREQUIREMENT+' '+A.CREQUIREMENT END AS PARAMETER,
            CONVERT(FLOAT,REPLACE(RIGHT(A.ITEST_RESULT, CHARINDEX('||',REVERSE(A.ITEST_RESULT)) - 1),'9.91E37','0')) AS TEST_RESULT,
            B.CSERIAL_NUMBER,B.DTEST_TIME,C.CUPDATE_USER,
            CASE C.CRESULT WHEN 'TRUE' THEN 'FAIL' ELSE 'PASS' END AS CRESULT 
    FROM G_TEST_DATA A,(
        SELECT CSERIAL_NUMBER,MAX(DTEST_TIME)DTEST_TIME 
        FROM DBO.G_TEST_SN GROUP BY CSERIAL_NUMBER
    )B,G_TEST_SN C
    WHERE A.ITEST_ID=C.ITEST_ID 
    AND A.CPARAMETER<>'' 
    AND C.CSERIAL_NUMBER='1' 
    AND C.CSERIAL_NUMBER=B.CSERIAL_NUMBER 
    AND C.DTEST_TIME=B.DTEST_TIME
) P --PIVOT (MAX([VALUE]) FOR [PARAM] IN ( '+ @SQL_COLV +') ) AS PVT

SELECT @SQL_COL = ISNULL(@SQL_COL + ',','') + QUOTENAME([PARAMETER]) FROM (SELECT * FROM #T ) AS A GROUP BY [PARAMETER]
SET @SQL_STR = '
SELECT ROW_NUMBER() OVER(PARTITION BY SERIAL_NUMBER,TEST_TIME ORDER BY TEST_TIME DESC) AS TESTSEQ,* FROM (
    SELECT [PARAMETER],[TEST_RESULT],[SERIAL_NUMBER],[TEST_TIME],[UPDATE_USER],[RESULT] FROM #T AS A) P PIVOT 
    (MAX([TEST_RESULT]) FOR [PARAMETER] IN ( '+ @SQL_COL +') ) AS PVT ';
EXEC (@SQL_STR)
DROP TABLE #T

12 SQL查询表中的有那些索引

----方法1. 使用系统表
SELECT  tablename = c.name ,
        indexname = a.name ,
        indexcolumns = d.name ,
        a.indid
FROM    sysindexes a
        JOIN sysindexkeys b ON a.id = b.id
                               AND a.indid = b.indid
        JOIN sysobjects c ON b.id = c.id
        JOIN syscolumns d ON b.id = d.id
                             AND b.colid = d.colid
WHERE   a.indid NOT IN ( 0, 255 )  
-- and   c.xtype='U'   and   c.status>0 -- 查所有用户表
        AND c.name = 'SN_Tracking' --查指定表
ORDER BY c.name ,
        a.name ,
        d.name;
        
        
------方法2. 使用系统存储过程        
----- sp_helpindex ：报告有关表或视图上索引的信息。

/*  sp_helpindex [ @objname = ] 'name'
参数 :[@objname =] 'name'
是当前数据库中表或视图的名称。name 的数据类型为 nvarchar(776)，没有默认值。 */
 
 exec sp_helpindex 'SN_Tracking'