mysql与sqlserver之间的关系转换

  1. sqlserver中的数据类型与mysql之间的对应
    --sqlserver
    select * into xx from xx where 1=0  只复制表结构
    select * into xx from xx where 1=1   复制表结构和表数据
    
    --mysql
    create table xx like xx;  只复制表结构
    create table xx as select * from xx; 复制表结构和表数据

    mysql复制表结构/数据的时候,并不会复制主键,索引,自增列等任何属性,仅仅是简单拷数据,而sqlserver会拷贝自增列
    sqlserver mysql类型
    bit布尔型(true/false) boolean转换后变成tinyint(1/0)
    smalldatetime timestamp长度设为空
     varchar(max)  text
     nvarchar(max)  ntext
     varbinary(max)  image
    DATEDIFF(datepart,startdate,enddate)函数
    timestampdiff(unit,datetime_expr1,datetime_expr2)函数
    datediff(date1,date2):两个日期相减 date1 - date2,返回天数。
     CONVERT() 用不同的格式显示日期/时间  DATE_FORMAT() 用不同的格式显示日期/时间
  2. mysql存储过程实例
    例1、一个简单存储过程游标实例 
    DELIMITER $$  
    DROP PROCEDURE IF EXISTS getUserInfo $$
     CREATE PROCEDURE getUserInfo(in date_day datetime) -- 实例 -- 存储过程名为:getUserInfo 
    -- 参数为:date_day
    -- 日期格式:2008-03-08   
      BEGIN declare _userName varchar(12); -- 用户名 
    declare _chinese int ; -- 语文 
    declare _math int ;    -- 数学 
    declare done int; -- 定义游标
     DECLARE rs_cursor CURSOR FOR SELECT username,chinese,math from userInfo where datediff(createDate, date_day)=0;
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; -- 获取昨天的日期
     if date_day is null then  
      set date_day = date_add(now(),interval -1 day);  -- 日期格式
     end if;
     open rs_cursor; 
     cursor_loop:loop   
     FETCH rs_cursor into _userName, _chinese, _math; -- 取数据     
    if done=1 then     leave cursor_loop;    end if;    -- 更新表    update infoSum set total=_chinese+_math where UserName=_userName; end loop cursor_loop; close rs_cursor;    
    END$$ DELIMITER ;
  3. Mysql存储过程的使用:
    1.定义变量
    
    MySQL中可以使用DECLARE关键字来定义变量。
    (MySQL中还可以使用DECLARE关键字来定义条件或者-
    MySQL中可以使用DECLARE关键字来定义处理程序)
    定义变量的基本语法如下:
    
    DECLARE  var_name[,...]  type  [DEFAULT value] 
    2.为变量赋值
    
    MySQL中可以使用SET关键字来为变量赋值。SET语句的基本语法如下:                                        
    
    SET  var_name = expr [, var_name = expr] ...
    
    MySQL中还可以使用SELECT…INTO语句为变量赋值。其基本语法如下:
    
    SELECT  col_name[,…]  INTO  var_name[,…]  
        FROM  table_name  WEHRE  condition
    计算两个数之和
    create procedure pr_add (a int,b int)
    begin
    declare c int;
    if a is null then
    set a = 0;
    end if;
    if b is null then
    set b = 0;
    end if;
    set c = a + b;
    select c as sum;
    end
  4. mysql变量的定义
    MySQL存储过程中,定义变量有两种方式:
    1.使用set或select直接赋值,变量名以 @ 开头.
    例如:set @var=1;
    可以在一个会话的任何地方声明,作用域是整个会话,称为会话变量。
    
    2.以 DECLARE 关键字声明的变量,只能在存储过程中使用,称为存储过程变量,例如:
    DECLARE var1  INT DEFAULT 0;  
    主要用在存储过程中,或者是给存储传参数中。
    
    两者的区别是:
    在调用存储过程时,以DECLARE声明的变量都会被初始化为 NULL。而会话变量(即@开头的变量)则不会被再初始化,在一个会话内,只须初始化一次,之后在会话内都是对上一次计算的结果,就相当于在是这个会话内的全局变量。
    
    在存储过程中,使用动态语句,预处理时,动态内容必须赋给一个会话变量。
    例:
    set @v_sql= sqltext;
    PREPARE stmt FROM @v_sql;  
    EXECUTE stmt;     
    DEALLOCATE PREPARE stmt;
  5. mysql存储过程定义区别sqlserver
    三 语法定义
    3.1 注释符区别
    SqlServer的注释符为--和
    MySql的注释符为--和和#
    
    3.2 识别符的区别
    MS SQL识别符是[],[type]表示他区别于关键字,但是MySQL却是 `,也就是按键1左边的那个符号
    
    3.3存储过程的区别(未经验证,从网上找的)
    mysql的存储过程中变量的定义去掉@;
    
    SQLServer存储过程的AS在MySql中需要用begin .....end替换
    
    Mysql的Execute对应SqlServer的exec;
    
        (注意:必须想下面这样调用)
        Set @cnt=’select * from 表名’;
        Prepare str from @cnt;
        Execute str;
    
    MySql存储过程调用其他存储过程用call
    
    Call 函数名(即SQLServer的存储过程名)(’参数1’,’参数2’,……)
    
    select @a=count(*) from VW_Action 在mySql中修改为:select count(*) from VW_Action into @a;

    MySQL视图的FROM子句不允许存在子查询,因此对于SQL Server中FROM子句带有子查询的视图,需要手工进行迁移。可通过消除FROM子句中的子查询,或将FROM子句中的子查询重构为一个新的视图来进行迁移。
  6. MySql的out对应SQLServer的output,且mysql 的out要放在变量的前面,SQLServer的output放在变量后面:
  7. MySQL视图的FROM子句不允许存在子查询,因此对于SQL Server中FROM子句带有子查询的视图,需要手工进行迁移。可通过消除FROM子句中的子查询,或将FROM子句中的子查询重构为一个新的视图来进行迁移。
  8. MySql out,in,inout的区别——
    MySQL 存储过程 “in” 参数:跟 C 语言的函数参数的值传递类似, MySQL 存储过程内部可能会修改此参数,但对 in 类型参数的修改,对调用者(caller)来说是不可见的(not visible)。
    MySQL 存储过程 “out” 参数:从存储过程内部传值给调用者。在存储过程内部,该参数初始值为 null,无论调用者是否给存储过程参数设置值。
    MySQL 存储过程 inout 参数跟 out 类似,都可以从存储过程内部传值给调用者。不同的是:调用者还可以通过 inout 参数传递值给存储过程。

    3.4字符串连接

    SQLServer: Temp=’select * from ’+’tablename’+…+…
    
        MySql:Temp=concat(’select * from’, ’tablecname’,…,…)
  9.  函数和数据类型的区别
    Date 函数
    MySQL Date 函数
    NOW() 返回当前的日期和时间
    CURDATE() 返回当前的日期
    CURTIME() 返回当前的时间
    DATE() 提取日期或日期/时间表达式的日期部分
    EXTRACT() 返回日期/时间按的单独部分
    DATE_ADD() 给日期添加指定的时间间隔
    DATE_SUB() 从日期减去指定的时间间隔
    DATEDIFF() 返回两个日期之间的天数
    DATE_FORMAT() 用不同的格式显示日期/时间
    
    SQL Server Date 函数
    GETDATE() 返回当前日期和时间
    DATEPART() 返回日期/时间的单独部分
    DATEADD() 在日期中添加或减去指定的时间间隔
    DATEDIFF() 返回两个日期之间的时间
    CONVERT() 用不同的格式显示日期/时间
    
    SQL Date 数据类型
    MySQL 使用下列数据类型在数据库中存储日期或日期/时间值:
    DATE - 格式 YYYY-MM-DD
    DATETIME - 格式: YYYY-MM-DD HH:MM:SS
    TIMESTAMP - 格式: YYYY-MM-DD HH:MM:SS
    YEAR - 格式 YYYY 或 YY
    
    SQL Server 使用下列数据类型在数据库中存储日期或日期/时间值:
    DATE - 格式 YYYY-MM-DD
    DATETIME - 格式: YYYY-MM-DD HH:MM:SS
    SMALLDATETIME - 格式: YYYY-MM-DD HH:MM:SS
    TIMESTAMP - 格式: 唯一的数字
  10. 日期类型 (201611)上个月和下个月的表达
    1.1上一个月的表达方式
    _lastmomth=YEAR(DATE_SUB(CAST(CAST(_Curr_OrderDate*100+10 AS CHAR) AS DATETIME),INTERVAL 1 MONTH))*100 + MONTH(DATE_SUB(CAST(CAST( _Curr_OrderDate*100+10 AS CHAR) AS DATETIME),INTERVAL 1 MONTH)); 1.2下一个月的表达方式 _nextMonth=YEAR(DATE_ADD(CAST(CAST(_Curr_OrderDate*100+10 AS CHAR) AS DATETIME),INTERVAL 1 MONTH))*100 + MONTH(DATE_ADD(CAST(CAST( _Curr_OrderDate*100+10 AS CHAR) AS DATETIME),INTERVAL 1 MONTH)); 或者: SELECT EXTRACT(YEAR_MONTH FROM DATE_ADD(OperationTime,INTERVAL 1 MONTH)) FROM b_log WHERE id=1 --变量:_Curr_OrderDate格式:201607--
    对比sqlserver表达方式如下:
     select  @lastmomth=year(DATEADD(m,-1,convert(datetime, cast( @Curr_OrderDate*100+10 as char), 112)))*100 + month(DATEADD(m,-1,convert(datetime, cast( @Curr_OrderDate*100+10 as char), 112)))
     
    select  @nextMonth=year(DATEADD(m,1,convert(datetime, cast( @Curr_OrderDate*100+10 as char), 112)))*100 + month(DATEADD(m,1,convert(datetime, cast( @Curr_OrderDate*100+10 as char), 112)))

    取出返回日期单独的部分
    SELECT DATEPART(yyyy,OrderDate) AS OrderYear,
    DATEPART(mm,OrderDate) AS OrderMonth,
    DATEPART(dd,OrderDate) AS OrderDay
    FROM Orders
    WHERE OrderId=1
     

    如果日期格式为2016-06-07表示下个月,可以使用SELECT DATE_ADD('2016-07-06',INTERVAL 1 MONTH);

  11. 删除重复数据

    如果你想删除数据表中的重复数据,你可以使用以下的SQL语句:
    
    mysql> CREATE TABLE tmp SELECT last_name, first_name, sex
        ->                  FROM person_tbl;
        ->                  GROUP BY (last_name, first_name);
    mysql> DROP TABLE person_tbl;
    mysql> ALTER TABLE tmp RENAME TO person_tbl;
  12. mysql语句的导入导出
    1.1
    使用 SELECT ... INTO OUTFILE 语句导出数据
    在下面的例子中,生成一个文件,各值用逗号隔开。这种格式可以被许多程序使用。
    
    SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '
    '
    FROM test_table;
    1.2
    使用 LOAD DATA 导入数据
    以下实例中将从当前目录中读取文件 dump.txt ,将该文件中的数据插入到当前数据库的 mytbl 表中。
    
    mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;
  13. mysql查询今天、昨天、7天、近30天、本月、上一月 数据
    今天
    
    select * from 表名 where to_days(时间字段名) = to_days(now());
    
    昨天
    
    SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1
    
    7天
    
    SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)
    
    近30天
    
    SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)
    
    本月
    
    SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) 
    
       = DATE_FORMAT( CURDATE( ) , '%Y%m' )
    
    上一月
    
    SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , 
    
       date_format( 时间字段名, '%Y%m' ) ) =1
    
    
    
    #查询本季度数据
    select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());
    
    
    #查询上季度数据
    select * from `ht_invoice_information` where QUARTER(create_date)
    
      =QUARTER(DATE_SUB(now(),interval 1 QUARTER));
    
    
    #查询本年数据
    select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());
    
    
    #查询上年数据
    select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));
    
    
    
    查询当前这周的数据 
    SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d'))
    
       = YEARWEEK(now());
    
    查询上周的数据
    SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) 
    
      = YEARWEEK(now())-1;
    
    查询当前月份的数据
    select name,submittime from enterprise   where date_format(submittime,'%Y-%m')
    
      =date_format(now(),'%Y-%m')
    
    查询距离当前现在6个月的数据
    select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();
    
    查询上个月的数据
    select name,submittime from enterprise   where date_format(submittime,'%Y-%m')
    
       =date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')
    
    
    
    select * from ` user ` where DATE_FORMAT(pudate, ' %Y%m ' ) 
    
      = DATE_FORMAT(CURDATE(), ' %Y%m ' ) ;
    
    
    
    select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) 
    
       = WEEKOFYEAR(now())
    
    
    
    select * from user where MONTH (FROM_UNIXTIME(pudate, ' %y-%m-%d ' ))
    
       = MONTH (now())
    
    
    
    select * from [ user ] where YEAR (FROM_UNIXTIME(pudate, ' %y-%m-%d ' )) 
    
      = YEAR (now()) and MONTH (FROM_UNIXTIME(pudate, ' %y-%m-%d ' )) = MONTH (now())
    
    
    
    select * from [ user ] where pudate between 上月最后一天 
    
      and 下月第一天 where   date(regdate)   =   curdate();
    
    select   *   from   test   where   year(regdate)=year(now())   and   month(regdate)=month(now())   
    
       and  day(regdate)=day(now())
    
    
    
    SELECT date( c_instime ) ,curdate( ) FROM `t_score` WHERE 1 LIMIT 0 , 30
  14. 备份的不同
    1.0 SQLSERVER逻辑备份:
    
    BACKUP DATABASE test TO DISK='c:	est.bak' 
    BACKUP LOG test  TO DISK='c:	est_log.bak' 
    
    --第一个SQL是完整备份test库,如果加上WITH DIFFERENTIAL就是差异备份
    --第二个SQL是备份test库的日志
    
    2.0 生成脚本的数据库
    
    要选择架构和数据,并且要选择索引,这样就会生成表的数据、定义、索引

    还原

    用mysql命令将school_2014-7-10.sql文件中的备份导入到数据库中
    
    mysql -u root -h 127.0.0.1 -p school<c:school_2014-7-10.sql

原文地址:https://www.cnblogs.com/step-city/p/6023787.html