sql server vs mysql

1.中文：
my.ini
[mysqld]
character-set-server=utf8
character-set-client=utf8

data	estdbdb.opt
default-character-set=utf8
default-collation=utf8_general_ci


2.拷贝数据库，除了data下面的数据库文件夹，还必须拷贝ibdata1
此外，如果需要存储过程，就拷贝mysql文件夹

3.不能更新数据
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
 To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
 SET SQL_SAFE_UPDATES = 0;
 
4.drop table wxingyao    Error Code: 1217. Cannot delete or update a parent row: a foreign key constraint fails    0.374 sec

use INFORMATION_SCHEMA;
select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from KEY_COLUMN_USAGE where
REFERENCED_TABLE_NAME = 'wxingyao';

alter table wmiaoxiangx drop foreign key FK_wMiaoXianGX_wXingYao1

5.Error Code: 1064. 
===============================================================================================
1. 标识符限定符

SqlServer [] 
MySql `` 

2. 字符串相加

SqlServer 直接用 + 
MySql concat() 

3. isnull()

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

4. getdate()

SqlServer getdate() 
MySql now() 

5. newid()

SqlServer newid() 
MySql uuid() 

6. @@ROWCOUNT

SqlServer @@ROWCOUNT 
MySql row_count()
注意：MySql的这个函数仅对于update, insert, delete有效  

7. SCOPE_IDENTITY()

SqlServer SCOPE_IDENTITY() 
MySql last_insert_id() 

8. if ... else ...

SqlServer IF Boolean_expression 
     { sql_statement | statement_block } 
[ ELSE 
     { sql_statement | statement_block } ] 
 
-- 若要定义语句块，请使用控制流关键字 BEGIN 和 END。

 
MySql IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

 

注意：对于MySql来说，then, end if是必须的。类似的还有其它的流程控制语句，这里就不一一列出。

9. declare

其实，SqlServer和MySql都有这个语句，用于定义变量，但差别在于：在MySql中，DECLARE仅被用在BEGIN ... END复合语句里，并且必须在复合语句的开头，在任何其它语句之前。
这个要求在写游标时，会感觉很BT.

10. 游标的写法

SqlServer declare @tempShoppingCart table (ProductId int, Quantity int)
insert into @tempShoppingCart (ProductId, Quantity)
    select ProductId, Quantity from ShoppingCart where UserGuid = @UserGuid


declare @productId int
declare @quantity int
declare tempCartCursor cursor for 
        select ProductId, Quantity from @tempShoppingCart

open tempCartCursor
fetch next from tempCartCursor into @productId, @quantity
while  @@FETCH_STATUS = 0
begin
    update Product set SellCount = SellCount + @quantity    where productId = @productId

    fetch next from tempCartCursor into @productId, @quantity
end

close tempCartCursor
deallocate tempCartCursor

 
MySql declare m_done int default 0;
declare m_sectionId int;
declare m_newsId int;

declare _cursor_SN cursor for select sectionid, newsid from _temp_SN;
declare continue handler for not found set m_done = 1;

create temporary table _temp_SN 
    select sectionid, newsid from SectionNews  group by sectionid, newsid having count(*) > 1;

open _cursor_SN;
while( m_done = 0 ) do
    fetch _cursor_SN into m_sectionId, m_newsId;
    
    if( m_done = 0 ) then 
        -- 具体的处理逻辑
    end if;
end while;
close _cursor_SN;
drop table _temp_SN;

 

注意：为了提高性能，通常在表变量上打开游标，不要直接在数据表上打开游标。

11. 分页的处理

SqlServer create procedure GetProductByCategoryId( 
    @CategoryID int, 
    @PageIndex int = 0, 
    @PageSize int = 20, 
    @TotalRecords int output
) 
as
begin
     
declare @ResultTable table
( 
    RowIndex int, 
    ProductID int, 
    ProductName nvarchar(50), 
    CategoryID int, 
    Unit nvarchar(10), 
    UnitPrice money, 
    Quantity int
); 
     
insert into @ResultTable 
select row_number() over (order by ProductID asc) as RowIndex, 
       p.ProductID, p.ProductName, p.CategoryID, p.Unit, p.UnitPrice, p.Quantity 
from   Products as p 
where CategoryID = @CategoryID; 
       
select  @TotalRecords = count(*) from  @ResultTable; 
     
select * 
from   @ResultTable 
where  RowIndex > (@PageSize * @PageIndex) and RowIndex <= (@PageSize * (@PageIndex+1)); 
     
end;


当然，SqlServer中并不只有这一种写法，只是这种写法是比较常见而已。
 
MySql create procedure GetProductsByCategoryId(
   in _categoryId int,
   in _pageIndex int,
   in _pageSize int,
   out _totalRecCount int
)
begin
 
   set @categoryId = _categoryId;
   set @startRow = _pageIndex * _pageSize;
   set @pageSize = _pageSize;
 
   prepare PageSql from 
    'select sql_calc_found_rows * from product  where categoryId = ? order by ProductId desc limit ?, ?';
   execute PageSql using @categoryId, @startRow, @pageSize;
   deallocate prepare PageSql;
   set _totalRecCount = found_rows();
 
end

 

===============================================================================================
1.日期
sql server:
getdate()

日期的一部分
datepart(year,getdate())
比较日期
select datediff(day,getdate(),getdate()+1)
转成字符串
convert(nvarchar(8),getdate(),112) 
字符串转日期
select convert(datetime,'2011-01-01')
添加日期
dateadd(day,dayDiff,startDt)
dateadd(minute,minDiff,startDt)
当前周中第几天
datepart(dw,getdate()) 星期天 1星期一 2...星期五 6
当年第几周
select datepart(dw,getdate()),datepart(week,getdate())
当月第几周
datepart(week,getdate())-datepart(week,dateadd(day,1-11,getdate()))+1

mysql:
now()
curdate()
CURTIME()
日期的一部分
 year(curdate())
比较日期
select datediff(now(),now()+1)
year(now())-year('2017-01-01')
转成字符串
date_format(now(),'%Y%m%d')
字符串转日期
STR_TO_DATE('2012-10-11 16:42:30','%Y-%m-%d %H:%i:%s')
添加日期
select date_add(now(),INTERVAL 2 month);
select date_add(now(),INTERVAL 2 DAY);
select date_add(now(),INTERVAL 2 minute);
当前周第几天
SELECT WEEKDAY(now());返回的是数字：0为周一，6为周日
select date_format(curdate()-1,'%w');   %w 是以数字的形式来表示周中的天数（ 0 = Sunday, 1=Monday, . . ., 6=Saturday）
select date_format(solarDt,'%w')+1;
当月第几周
select  week(curdate())-week(curdate()-interval day(curdate())-1 day)+1;


PERIOD_ADD(P,N)　  
增加N个月到阶段P（以格式YYMM或YYYYMM)。以格式YYYYMM返回值。注意阶段参数P不是日期值。　  
mysql> select PERIOD_ADD(9801,2);　  
-> 199803　  
DATE_ADD(date,INTERVAL expr type)　  
  
DATE_SUB(date,INTERVAL expr type)　  
  
ADDDATE(date,INTERVAL expr type)　  
  
SUBDATE(date,INTERVAL expr type)　

select datediff(now(),now()+1),year(now())-year('2017-01-01'),month(now())-month('2017-07-01')
,timediff('2016-01-01 23:05:00','2016-01-01 00:09:00')
,time_format(timediff('2016-01-01 23:05:00','2016-01-01 00:09:00'),'%H')
,time_format(timediff('2016-01-01 23:05:00','2016-01-01 00:09:00'),'%i')
,timediff('2016-01-01 00:09:00','2016-01-01 23:05:00')
,time_format(timediff('2016-01-01 00:09:00','2016-01-01 23:05:00'),'%H')
,time_format(timediff('2016-01-01 00:09:00','2016-01-01 23:05:00'),'%i')


0    -1    0    22:56:00    22    56    (null)    -22    -56

%f Microseconds (000000 to 999999)
 %f is available starting in MySQL 4.1.1 
%H Hour (00 to 23 generally, but can be higher) 
%h Hour (00 to 12) 
%I Hour (00 to 12) 
%i Minutes (00 to 59) 
%p AM or PM 
%r Time in 12 hour AM or PM format (hh:mm:ss AM/PM) 
%S Seconds (00 to 59) 
%s Seconds (00 to 59) 
%T Time in 24 hour format (hh:mm:ss) 


STR_TO_DATE(convert(1980,char(4))+'-'+convert(1,char(2))+'-'+convert(5,char(2))+' '+convert(8,char(2))+':'+convert(8,char(2))+':00','%Y-%m-%d %H:%i:%s')
,STR_TO_DATE('2012-10-11 16:42:30','%Y-%m-%d %H:%i:%s'); 

2.转换字符串
sql server:
convert(nvarchar(10),12345)
mysql:
convert(12345,char(10))

3.自增长ID
sql server:
SCOPE_IDENTITY()

create   table   type      
 (  
     Type_ID   int   identity(1,1)   primary   key       NOT   NULL   ,  
     TypeName   varchar(25)   NOT   NULL
 )
 
mysql:
create   table   type      
 (  
     Type_ID      int primary key auto_increment not null   ,  
     TypeName   varchar(25)   NOT   NULL
 )

 
LAST_INSERT_ID()
@@IDENTITY

insert into test.type(typename) values('bcd');
select LAST_INSERT_ID();

ALTER TABLE users AUTO_INCREMENT=1001;

3.存储过程里的临时表
sql server:
declare table tb(id int)
select * into tb2 from tb
mysql:
create temporary table tb(id int)
create temporary table tb as select * from tb;


drop temporary table if exists temptb ;
    create temporary table  temptb as  select * from tb;

4.isnull(id,0)
sql server: isnull(id,0)
mysql: ifnull(id,0)
5.错误处理
sql server: 
RAISERROR ('非法公历日期', 16, 1)
mysql: 
      SIGNAL SQLSTATE '01000';
      SET MESSAGE_TEXT = '非法时间';
6.执行存储过程
sql server: 
exec zConvertLunarSolar iyear,imon,iday,ihour,imin,IsleapM,ToLunar
mysql: 
call zConvertLunarSolar (iyear,imon,iday,ihour,imin,IsleapM,ToLunar);

7.It is wrong in mysql:
select typename,* from type

8.存储过程
sql server:
CREATE PROCEDURE [dbo].[hDelMingZhu]
    -- Add the parameters for the stored procedure here
    @MingZhuId int
AS
BEGIN
。。。
END


if ...
 begin
  ...
 end
else if ...
 ...
 
mysql:
DELIMITER $$
DROP PROCEDURE IF EXISTS hDelMingZhu$$  
CREATE PROCEDURE hDelMingZhu(
    IN MingZhuId int)
BEGIN
。。。
END$$
DELIMITER ;

if ... then 
 ...
elseif ... then 
  ...
else 
end if; 

9. update ... set ... from 根据某个表来更新
sql server:
Update tmptb  set GanId=ny.YueGanId from vNianToYue ny where tmptb.ZhiId = ny.YueZhiId 

mysql:
Update tmptb inner join vNianToYue ny on   tmptb.ZhiId = ny.YueZhiId  set GanId=ny.YueGanId 

10.循环和游标
sql server:
    declare @MingZhuId int
        begin 
           declare mzs cursor for select MingZhuId  from dMingZhu where Disabled = 0 
           open mzs --开启游标
           while @@FETCH_STATUS=0--取值
             begin
             fetch next FROM mzs into @MingZhuId--这样就将游标指向下一行，得到的第一行值就传给变量了
             -------------------------------------------
                  exec [dbo].[wZiWeiPaiPan] @MingZhuId
             -------------------------------------------
              end
           close mzs--关闭游标

          deallocate  mzs--释放游标
         end
         
mysql:        

while count < 10 do 
  set count = count +1;
end while;  

  DECLARE a CHAR(16);
  -- 游标
  DECLARE cur CURSOR FOR SELECT i FROM test.t;
  -- 遍历数据结束标志
  DECLARE done INT DEFAULT FALSE;
  -- 将结束标志绑定到游标
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  -- 打开游标
  OPEN cur;
  
  -- 开始循环
  read_loop: LOOP
    -- 提取游标里的数据，这里只有一个，多个的话也一样；
    FETCH cur INTO a;
    -- 声明结束的时候
    IF done THEN
      LEAVE read_loop;
    END IF;
    -- 这里做你想做的循环的事件

    INSERT INTO test.t VALUES (a);

  END LOOP;
  -- 关闭游标
  CLOSE cur;

11.mysql不能用+=
  set count = count +1;
  
  12.动态SQL
  
  SET @rangee = plimitRange * 10; 
SET @uid    = puserid;

PREPARE STMT FROM
'select @max_postid := MAX(postid), @min_postid := MIN(postid) from
(
select wall.postid from wall,posts where  
wall.postid = posts.postid and posts.userid=?
order by wall.postid desc LIMIT 10 OFFSET ?
)m;
';

 EXECUTE STMT USING @uid,@rangee;
 DEALLOCATE PREPARE STMT;
 
 13.在Mysql WorkBench不能update表
    Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.    0.312 sec
这是因为MySql运行在safe-updates模式下，该模式会导致非主键条件下无法执行update或者delete命令，执行命令SET SQL_SAFE_UPDATES = 0;修改下数据库模式
1.Go to Edit --> Preferences
2.Click "SQL Queries" tab and uncheck "Safe Updates" check box
3.Query --> Reconnect to Server // logout and then login
4.Now execute your sql query
P.s No need to restart mysql daemon!

14.存储过程中报错不能重新打开临时表
mysql> SELECT * FROM temp_table, temp_table AS t2; 
  　　ERROR 1137: Can't reopen table: 'temp_table'  

temporary table can't reopen table 
下面几点是临时表的限制：

1、临时表只能用在 memory,myisam,merge,或者innodb 
2、临时表不支持mysql cluster(簇)   
3、在同一个query语句中，你只能查找一次临时表。

15.mysql 的查询语句里面可不可以用if else 之类的，我知道可以用case end
correct:
select if(true, 1, 2);
wrong:
if (1==1) then
  select 1 from test.type;
else 
  select 2 from test.type;
end if;

16.字符串拼接用CONCAT不要用+
select 'abc'+'0', '123'+'67',convert(1980,char(4))+'-'+convert(1,char(2))+'-'+convert(5,char(2))+' '+convert(8,char(2))+':'+convert(8,char(2))+':00',STR_TO_DATE(convert(1980,char(4))+'-'+convert(1,char(2))+'-'+convert(5,char(2))+' '+convert(8,char(2))+':'+convert(8,char(2))+':00','%Y-%m-%d %H:%i:%s')
,STR_TO_DATE('2012-10-11 16:42:30','%Y-%m-%d %H:%i:%s'); 

select concat(4,'-',1,9),'1'+'9' ,concat('',1,9), '123'+'67',convert(1980,char(4))+'-'+convert(1,char(2))+'-'+convert(5,char(2))+' '+convert(8,char(2))+':'+convert(8,char(2))+':00',STR_TO_DATE(convert(1980,char(4))+'-'+convert(1,char(2))+'-'+convert(5,char(2))+' '+convert(8,char(2))+':'+convert(8,char(2))+':00','%Y-%m-%d %H:%i:%s')
,STR_TO_DATE('2012-10-11 16:42:30','%Y-%m-%d %H:%i:%s'); 
4-19   10.0     19    190.0    2002.0    2001-11-30 00:00:00    2012-10-11 16:42:30

17.与操作,binary数据类型
注意，select 0x004BD8 & 0xF是可行的，但直接bitdata & 0xF不行，必须CONV(HEX(bitdata),16,10) & 0xF
select 0x004BD8 & 0xF;
select 19416 & 0xF;
select *,bitdata & 0xF, CONV(HEX(bitdata),16,10) & 0xF  from tLunarYear y 

binary转换成int
select CONV(HEX(0x004BD8),16,10)

correct:
    insert into tYear(yearNo,bitdt)
    select * from tLunarYear;
wrong: 
    insert into tYear(yearNo,bitdt,bitData)
    select id+1899,bitdata,CONV(HEX(bitdata),16,10) from tLunarYear;

位右移 
mysql> select 100>>3; 
位左移 
mysql> select 100<<3; 

18.给变量赋值
错误 
declare a int;
select a=1 from test.type;
select a;
错误 
select a：=1 from test.type;
错误 
select 1,2 into a,b from test.type;
正确
select @a：=1 from test.type;
正确
select 1 into a from test.type;


!!!注意
@变量名 是用户变量，下次调用时不会自动初始化
The difference between a procedure variable and a session-specific user-defined variable is that procedure variable is reinitialized to NULL each time the procedure is called, while the session-specific variable is not:


使用into的方法(单个赋值)
select id into @id from tbl_currentWeather where cityid = _cityid;
 多个赋值
select  @id:=id,@cityid:=cityid  from tbl_currentWeather where cityid = _cityid;

mysql中变量不用事前申明，在用的时候直接用“@变量名”使用就可以了。
第一种用法：set @num=1; 或set @num:=1; //这里要使用变量来保存数据，直接使用@num变量
第二种用法：select @num:=1; 或 select @num:=字段名 from 表名 where ……
注意上面两种赋值符号，使用set时可以用“=”或“：=”，但是使用select时必须用“：=赋值”


18.自动四舍五入
select truncate(1995/1000,0),format(1995 /1000,0), 1995 /1000
1    2    1.9950

19.不能在 MySQL 存储过程中使用 “return” 关键字

区块定义，常用
begin
 ......
 end;
也可以给区块起别名，如：
lable:begin
 ...........
 end lable;
可以用leave lable;跳出区块，执行区块以后的代码

20.length
sql server: len('abvc')
mysql:length('abvc')

rtrim()和ltrim()两个都可以用

21.表字段设置默认值
sql server:
ALTER TABLE [dbo].[dMingZhu] ADD  CONSTRAINT [DF_dMingZhu_CreateBy]  DEFAULT (suser_sname()) FOR [CreateBy]
ALTER TABLE [dbo].[dMingZhu] ADD  CONSTRAINT [DF_dMingZhu_CreateDateTime]  DEFAULT (getdate()) FOR [CreateDateTime]
mysql: MySQL 中，默认值无法使用函数.假如需要 某列的默认值为 当前数据库时间，那么可以使用 TIMESTAMP 数据类型。
wrong:
alter table dMingZhu alter column CreateBy set default current_user();
alter table dMingZhu alter column CreateDateTime set default now();
dt TIMESTAMP 等价于dt TIMESTAMP  default CURRENT_TIMESTAMP  ON UPDATE CURRENT_TIMESTAMP


create table foo_audit (
    foo_audit_id not null auto_increment primary key,
    foo_id int,
    foo_data varchar(100),
    change_type char(1),
    change_timestamp timestamp default current_timestamp,
    change_login varchar(100)
    );
    
    create trigger trg_foo_insert
    after insert on foo
    for each row 
    insert into foo_audit (
        foo_id,
        foo_data,
        change_type,
        change_login
        )
    values (
        new.foo_id,
        new.foo_data,
        'I',
        current_user
        );
        
22.top n records
select  * from test.type limit 10

22.重命名
RENAME DATABASE db_name TO new_db_name

23.行号
SQL server:
rownum()
mysql:
SELECT @rownum:=@rownum+1 rownum, t.* From
(SELECT @rownum:=0,bz.* FROM dbazi bz where mingzhuid=5 and ganzhitypeid=7 and  bazirefid is null ) t

24.自动四舍五入
sql server:
select 5/2
2
Mysql:
select 5/2,floor(5/2),round(123.5),floor(123.5),ceil(123.5);
3 2 124    123    124

25.合并字符串
select 'aa'+'bbb',concat('aaa','bbb')
# 'aa'+'bbb', concat('aaa','bbb')
'0', 'aaabbb'


以id分组，把name字段的值打印在一行，逗号分隔(默认)

mysql> select id,group_concat(name) from aa group by id;
+------+--------------------+
| id| group_concat(name) |
+------+--------------------+
|1 | 10,20,20|
|2 | 20 |
|3 | 200,500|
以id分组，把name字段的值打印在一行，分号分隔

mysql> select id,group_concat(name separator ';') from aa group by id;

26.回车符
wrong:
group_concat(Remark separator char(13))
correct:
group_concat(text SEPARATOR 0x3)


27.找出所有相关的外键
sql server:
select  

  oSub.name  AS  [子表名称], 

  fk.name AS  [外键名称], 

  SubCol.name AS [子表列名], 

  oMain.name  AS  [主表名称], 

  MainCol.name AS [主表列名] 

from  

  sys.foreign_keys fk   

    JOIN sys.all_objects oSub   

        ON (fk.parent_object_id = oSub.object_id) 

    JOIN sys.all_objects oMain  

        ON (fk.referenced_object_id = oMain.object_id) 

    JOIN sys.foreign_key_columns fkCols  

        ON (fk.object_id = fkCols.constraint_object_id) 

    JOIN sys.columns SubCol  

        ON (oSub.object_id = SubCol.object_id   

            AND fkCols.parent_column_id = SubCol.column_id) 

    JOIN sys.columns MainCol  

        ON (oMain.object_id = MainCol.object_id   

            AND fkCols.referenced_column_id = MainCol.column_id)

mysql:
use INFORMATION_SCHEMA;
select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from KEY_COLUMN_USAGE where
REFERENCED_TABLE_NAME = 'wxingyao';

28.生成数据的sql
sql server:
Task->Generate scripts->data only
mysql:
MySql Workbench->Server->Data Export
Database->Reverse Engineer , Database->Forward Engineer 
29.重命名
RENAME TABLE  `oldTableName` TO  `newTableName`

30.中文字符串长度
select substr('aaab',4,1),substr('海中金',3,1),char_length('海中金'),length('海中金'),char_length('aaa')
b    金    3    9    3

31.导入导出CSV
sql server
BULK INSERT ReqOutDated
 FROM 'C:TempCHUBB.CSV'         --  –> change the file path
 WITH
 (
 FIRSTROW = 2,                       -- –> An indicator where the data starts. Usually its 2 because row 1 is the column names.
 FIELDTERMINATOR = '
',    --–> the field terminator is a comma (,), you may change it for your own needs
 ROWTERMINATOR = '
'
 )

mysql
导入csv：

load data infile '/test.csv' into table table_name 
fields terminated by ','  optionally 
enclosed by '"' escaped by '"' 
lines terminated by '
' 
ignore 1 lines;

导出csv(如果有中文必须为utf-8)：

SELECT * INTO OUTFILE '/test.csv'  
FIELDS TERMINATED BY ',' OPTIONALLY 
ENCLOSED BY '"' 
LINES TERMINATED BY '
' 
FROM table_name;

32.数据库路径

mysql
select @@datadir;