SQL相关知识

存储过程中的循环的用法

     BEGIN TRY

    BEGIN TRANSACTION  --开始事务

    DECLARE CUR_01 CURSOR FOR   --定义游标
        SELECT OC_ID, OBJ_CODE, KEY_VALUE, PRO_CODE, OLD_VALUE, OLD_NAME, NEW_VALUE, NEW_NAME
            FROM QLC_TOBJCHANGE
                WHERE SP_STATE = '117002' AND PROBLEM_ID = @IN_PROBLEM_ID
    OPEN CUR_01   --打开游标
    FETCH NEXT FROM CUR_01 INTO @V_OC_ID, @V_OBJ_CODE, @V_KEY_VALUE, @V_PRO_CODE, @V_OLD_VALUE, @V_OLD_NAME, @V_NEW_VALUE, @V_NEW_NAME  --挤压游标
    WHILE @@FETCH_STATUS = 0   --开始状态
    BEGIN
       .................................

       FETCH NEXT FROM CUR_01 INTO @V_OC_ID, @V_OBJ_CODE, @V_KEY_VALUE, @V_PRO_@V_OLD_VALUE, @V_OLD_NAME, @V_NEW_VALUE, @V_NEW_NAME
    END
    CLOSE CUR_01  --关闭游标
    DEALLOCATE CUR_01  --销毁游标

    COMMIT TRANSACTION  --提交事务
    END TRY  

–有些情况下要去掉begin try和end try,否则会报错

sqlserver 建表语法：

if exists (select 1
            from  sysobjects
           where  id = object_id('QLC_TBENLIMIT ')
            and   type = 'U')
   drop table QLC_TBENLIMIT 
go
create table QLC_TBENLIMIT (
   LIMIT_UUID           nvarchar(64)         not null default newid() /*限制UUID*/,
   LIMIT_ID             integer              identity /*限制ID*/,
   START_DATE           integer              null /*开始日期*/,
   INPUT_MAN_NAME       nvarchar(20)         not null /*操作员名称*/,
   INPUT_TIME           datetime             not null default getdate() /*操作时间*/,
   constraint PK_QLC_TBENLIMIT primary key (LIMIT_ID)
)
go

mysql 建表语法：

 #员工表
 create table tb_emp(
 id int primary key auto_increment,#auto_increment只是MySQL特有的
 Name varchar(18),
 sex varchar(2),
 age int,
 datetime datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' ;
 );

sqlsever 语法 ’ ’ ’ ’ 想当于python中的 “‘ ” 用于给变量加上引号

一个外键下有多条数据
查询某外键下的 b 同时有b=1,b=2两种条件的情况

 select A.外键 from (select * from  表
  where 字段B=1) as A,
 (select * from 表
  where 字段B=2) as B where A.外键=B.外键

sql的视图技巧

--用union来连接两张不相关的表，可制造主键
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'V_QLC_TPAYAPPLYFYLIST2')
    DROP VIEW V_QLC_TPAYAPPLYFYLIST2
GO
CREATE VIEW V_QLC_TPAYAPPLYFYLIST2 AS
    SELECT '2_' + CONVERT(NVARCHAR(20),A.FY_ID) AS ZF_ID,A.FY_ID,2 AS MARK, A.REGITEM_ID, 
        FROM QLC_TPAYAPPLYFYLIST A
            WHERE  A.SP_STATE = '117003' 
    UNION
    SELECT '3_' + CONVERT(NVARCHAR(20),A.PRODUCT_ID) AS ZF_ID,0 AS FY_ID,3 AS MARK, A.REGITEM_ID
        FROM  QLC_TITEMREGINFO 
            WHERE A.REGITEM_ID = 200
GO

sqlserver和mysql的一些语法差别
1. 字符串相加
SqlServer 直接用 +
MySql concat()

2.isnull()
SqlServer isnull()
MySql ifnull()
注意：MySql也有isnull()函数，但意义不一样

3.getdate()

SqlServer getdate()
MySql now()

4.newid()
SqlServer newid()
MySql uuid()

5.查看表结构
sqlserver sp_help 表名
mysql desc 表名

6.. if … else …
sqlserver:
IF Boolean_expression BEGIN
END
ELSE BEGIN
END

mysql
IF search_condition THEN statement_list THEN
END IF
对于MySql来说，then, end if是必须的