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  

sqlserver 建表语法：

if exists (select 1            from  sysobjects           where  id = object_id('QLC_TBENLIMIT ')            and   type = 'U')   drop table QLC_TBENLIMIT gocreate 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_TPAYAPPLYFYLIST2GOCREATE 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 = 200GO

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.. 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是必须的