1.中文：

my.ini

[mysqld]

character-set-server=utf8

character-set-client=utf8

data\testdb\db.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 '';

      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'+'', ''+'',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),''+'' ,concat('',1,9), ''+'',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')

'', '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:\Temp\CHUBB.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 = '\n',    --–> the field terminator is a comma (,), you may change it for your own needs

 ROWTERMINATOR = '\n'

 )

mysql

导入csv：

load data infile '/test.csv' into table table_name

fields terminated by ','  optionally

enclosed by '"' escaped by '"'

lines terminated by '\r\n'

ignore 1 lines;

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

SELECT * INTO OUTFILE '/test.csv'

FIELDS TERMINATED BY ',' OPTIONALLY

ENCLOSED BY '"'

LINES TERMINATED BY '\n'

FROM table_name;

32.数据库路径

mysql

select @@datadir;