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;
