SQLSERVER 脚本转MYSQL 脚本的方法总结 - Hamilton Tan

时间：2024-03-09 11:42:37

1、MYSQL（版本为5.6）中SQL脚本必须以分号(;)结尾，这点比SQLSERVER要严谨；关键字与函数名称全部大写；数据库名称、表名称、字段名称全部小写。

2、所有关键字都要加上``,比如 Status 替换成 `Status`,`按是有个“~的键”；mysql声明变量的定义不要和你的select的列的键同名，不然会报错的。如果想在begin .. end;里面在声明变量必须加begin ... end;语句块。

3、数据类型

  (1).整型 能用字节小的存储就尽量用字节小的存储。比如能用TINYINT，就不要用INT来存储，可以优化性能。推荐主键使用int(10) unsigned NOT NULL，unsigned 为非负数，从0开始，长度会大一倍。

   

 (2).浮点型 Float:单精度浮点  double:双精度浮点

  

  (3).日期时间类型  

    TIME：-8385959  到  8385959

    DATE：1000年1月1号 到 9999年12月31号

    DATETIME: 1000年1月1号0点 到 9999年12月31号23点59分59秒

    TIMESTAMP（时间戳）： 1970年1月1号0点 到 2037年

   

   (4).字符串类型 

   

  --  创建表，脚本如下

CREATE TABLE tb1
(
    username VARCHAR(20),
    age TINYINT UNSIGNED,-- 无符号类型，也就是正整数。年龄不可能为负数
    salary float(8,2) UNSIGNED,
    sex boolean, -- 还可以创建布尔类型
    createtime datetime -- 创建时间类型的字段
);

 

CREATE TABLE hy
(
   id SMALLINT UNSIGNED PRIMARY KEY auto_increment,
   username varchar(20) not NULL,
   `password` VARCHAR(32) NOT NULL,
    age TINYINT UNSIGNED NOT NULL
);
insert hy VALUES(DEFAULT,\'tan\',\'1233\',12); 1
insert hy VALUES(NULLIF,\'hua\',\'1233\',12);  2

-- 也可以这样插入
insert hy(username,`PASSWORD`,age) VALUES(\'test1\',\'1233\',12),(\'test2\',\'567\',10);
-- 也可以这样插入 
INSERT INTO hy SET username =\'hz\',`PASSWORD`=\'123\',age =12;

 

4、SQLSERVER的dbo.[,]在mysql中不支持,都要去掉。
5、rowNumber 不支持,可以用SELECT @rownum:=@rownum+1 AS pageRownum FROM (SELECT @rownum:=0) r代替。

   sqlserver : select *,row_number() over(partition by Grade order by Score desc) as Sequence from Student  脚本数据： http://www.cnblogs.com/sanlang/archive/2009/03/24/1420360.html

   转为MYSQL 写法：

-- 这个判断就是看你按照哪个字段编号，这里按照grade字段
-- 那么就先取一个空字段 赋值一个数  当grade 不变时 编号+1
-- 如果grade改变 说明分组变化 序号就从1开始 重新编号

select 
t1.id,t1.grade,t1.score,t1.rank as seq from (
select x.*,if(@pid = x.grade,
            @rank := @rank+ 1,
            @rank := 1) as rank,
            @pid := x.grade
 from (select * from student order by id asc,Grade asc,Score desc) x,(select @pid := null,@rank := 0) a )t1


6. CONCAT()代替+，也可以用+来拼接字符串，数字相加的除外。

7. GROUP_CONCAT代替for xml path(\'\')。 SHOW VARIABLES like \'%concat%\' 查看拼接字符串的长度

   GROUP_CONCAT(DISTINCT Guardian SEPARATOR \';\') Guardian 以分号显示“a;b;c” 

   mysql类型为time类型的时候，对应的C#定义为TimeSpan，赋值如下：

 

DateTime dt = DateTime.Parse("2016-6-29 19:20:30");
TimeSpan times = dt.TimeOfDay;//注意这里赋值

或
var tt = 490;
var ss = TimeSpan.FromMinutes(tt);

 

(1).sqlserver:

select 
id,Name
from systest1
for xml path(\'SkuSalesVolumesXML\') 


结果：
<SkuSalesVolumesXML>
  <id>1</id>
  <Name>测试1</Name>
</SkuSalesVolumesXML>
<SkuSalesVolumesXML>
  <id>2</id>
  <Name>测试12</Name>
</SkuSalesVolumesXML>
<SkuSalesVolumesXML>
  <id>3</id>
  <Name>测试3</Name>
</SkuSalesVolumesXML>

(2).mysql

select 
REPLACE(GROUP_CONCAT(CONCAT(\'<SkuSalesVolumesXML>\',\'<id>\',Id,\'</id>\',\'<Name>\',`Name`,\'</Name>\',\'</SkuSalesVolumesXML>\')),\',\',\'\')
from sysmenu;

如果结果字符串过长,会截断，而sqlserver里面就不会出现这个问题。经过排查，发现mysql的group_concat()函数默认只返回1024长度的字符串，于是修改my.ini加上 group_concat_max_len = 200000。具体参考http://blog.csdn.net/yyzhq/article/details/12848327
<SkuSalesVolumesXML><id>1</id><Name>测试1</Name></SkuSalesVolumesXML><SkuSalesVolumesXML><id>2</id><Name>测试12</Name></SkuSalesVolumesXML><SkuSalesVolumesXML><id>3</id><Name>测试3</Name></SkuSalesVolumesXML>

 

8.SQL Server中@@ROWCOUNT用MYSQL 的ROW_COUNT()替换。

9. TOP 要换成LIMIT的写法。

10.声明变量，从查询语句中给多个变量赋值的方法:=,具体如下：

/**声明变量，接受名为：@userName的数量,初始值为：0*/
declare  count_user int default 0;//默认值为0,注意默认值两边都没有括号。
declare  loginname varchar(20);
// set count_user=0;赋值为0 

/**查询名为：@userName的用户的数量*/
select @count_user:=count(1),@loginname := userName from login;

 将查询的结果集赋给变量的写法：

 SELECT COUNT(id) FROM test INTO useNums; //useNums为变量

11.时间格式：DATE_FORMAT(NOW(),\'%Y%m%d\')  -- 20150124  具体可以参考 http://blog.csdn.net/kangbrother/article/details/7030304 。

    SELECT DATE_FORMAT(NOW(),\'%Y年%m月%d日 %H点：%i分：%s秒\'); -- 2015年02月16日 12点：48分：35秒

   这个写成一个函数： SELECT f1(); 

CREATE FUNCTION f1()
RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(),\'%Y年%m月%d日 %H点：%i分：%s秒\');

SELECT CURDATE() : 2017-03-10
SELECT NOW() : 2017-03-10 09:44:16

12.类型转换要用cast 可以参考http://www.nowamagic.net/librarys/veda/detail/2044  。

13.条件判断，if  then ... end if; if then .. else .. end if; if then ... elseif() then ... elseif() then ... else ... end if; 注意IF EXISTS 或 IF NOT EXISTS 下不能嵌套 IF 判断条件,可以定义变量，然后判断变量的值是否大于0。

14.SQLSERVER 中的len() 对应MYSQL中的LENGTH(),CHARINDEX  对应 locate,REPLICATE 用mysql的REPEAT代替，ifnull 替换 isnull，mysql 中的存储过程 用SELECT替换return，return 只支持函数。

SQLserver:
IF CHARINDEX(\'Code\', @OrderBy) = 1
BEGIN
    SET @orderByStr = \'a.\' + @OrderBy
END

MYSQL:
IF  locate(\'Code\', @OrderBy) = 1
then
    SET @orderByStr = \'a.\' + @OrderBy;
end If;

15.多表更新数据

sql server 多表关联更新 update
update e
set e.SALARY = e.SALARY + (e.SALARY*0.1)
from EMP e 
inner join DEPT d on e.DNO = d.DNO
where e.SALARY < 600 and d.DNAME =\'销售部\'

 

update `order` a INNER JOIN orderproducts b on a.`Code` = b.orderCode
SET a.CycleSort = b.RID;//支持mysql 不支持sqlserver(要把`改成[或])

update `order`,orderordertrans SET `order`.CycleSort = orderordertrans.RID where `order`.`code` = orderordertrans.`code`;//支持mysql 和sqlserver 

update a set a.DefaultCycleDeliveDate=null from [Order] a where a.CycleSort>@CurrentCycleSort and a.Cycle_group=@cycleGroup //支持server,不支持mysql              

 mysql 多表更新：

注意：表的别名，一定要统一，要么小写，要么大写。

1).
UPDATE sys_account accountIn 
  INNER JOIN sys_testData sysData 
    ON accountIn.accountid = sysData.accountid SET sysData.TestDate = DATE_ADD(sysData.TestDate,INTERVAL 1 DAY) 
WHERE sysData.IsDed = 0

2).
UPDATE sys_account accountIn,sys_testData sysData 
  SET sysData.TestDate = DATE_ADD(sysData.TestDate,INTERVAL 1 DAY) 
WHERE accountIn.accountid = sysData.accountid
AND sysData.IsDed = 0 

3).
UPDATE sys_account accountIn 
  INNER JOIN sys_testData sysData 
   SET sysData.TestDate = DATE_ADD(sysData.TestDate,INTERVAL 1 DAY) 
WHERE accountIn.accountid = sysData.accountid
AND sysData.IsDed = 0

4).mysql 处理根据重复的数据

    1.先根据PID,OID,DID分组,having count(*) >1, 查询最大的主键ID

SELECT MAX(TestAcountID) FROM TestAcount WHERE Deleted = 0 
GROUP BY PID,OID,DID
HAVING COUNT(*) > 1
2.然后删除删除包含最大的主键ID的记录(可能需要执行多次)
UPDATE TestAcount a,(SELECT MAX(TestAcountID) AS TestAcountID FROM TestAcount WHERE IsDeleted = 0 
GROUP BY PID,OID,DID
HAVING COUNT(*) > 1) b 
SET a.IsDeleted = 1 
WHERE a.TestAcountID = b.TestAcountID;

 

 

 

 

16.Mysql没有xml类型，用text代替。

 17. like 的对比：

   (1).sqlserver:

SELECT TOP 1 @serialNo = [SerialNo] FROM [OrderSerial] 
        WHERE [Prefixion]= @prefixion and [StrDate] LIKE \'%\'+@strDate+\'%\'  ORDER BY [SerialNo] DESC

  (2).mysql:

SELECT SerialNo INTO serialStrNo  FROM OrderSerial
        WHERE Prefixion= prefixion and StrDate  LIKE concat(\'%\',strDate,\'%\')  ORDER BY SerialNo DESC LIMIT 1;

18.mysql使用临时表：

CREATE TEMPORARY TABLE IF NOT EXISTS tb_OrderTo   
 (    
      TrackNumber varchar(50) NULL,
      SettlementAmount decimal(18, 2) NULL,
      FreightAmount decimal(18, 2) NULL,
      SettlementDate datetime NULL,
      Remark varchar(100) NULL                
);

19.MYSQL 操作xml,获取xml节点的值：

set @xmls = \'<Query>
    <Code></Code>
    <OrderCode></OrderCode>
    <SwappedOutCode></SwappedOutCode>
    <Status></Status>
    <Lock></Lock>
    <BuyerNick></BuyerNick>
    <AfterSaleTypeId></AfterSaleTypeId>
    <Shipper></Shipper>
    <ShopId></ShopId>
    <ShipperId></ShipperId>
    <PtStatus></PtStatus>
    <DvStatus></DvStatus>
    <IsComplete></IsComplete>
    <Type></Type>
    <Type1></Type1>
    <RefundStatus></RefundStatus>
    <IsTssk></IsTssk>
    <NeedPost></NeedPost>
    <TsskCode></TsskCode>
    <CompanyLoss OpType="=&gt;"></CompanyLoss>
    <Created From="2014-05-01" To="2014-05-31" />
    <Feedback></Feedback>
    <Action></Action>
    <AvailableShippers>1,2,3</AvailableShippers>
    <AvailableShops>1,2,3</AvailableShops>
</Query>\';

select ExtractValue(@xmls,\'/Query/Created/@To\'); -- 2014-05-31

select ExtractValue(@xmls,\'/Query/AvailableShippers\'); --1,2,3

 20.MYSQL没有分割字符串的函数，需要自定义。具体参考了 http://www.2cto.com/database/201209/156549.html  如下。

  如果报错，得执行：

show variables like \'%func%\';

set GLOBAL log_bin_trust_function_creators=on;

  (1).函数func_splitStringTotal：将字符串按指定方式分割，并计算单元总数。

DROP function IF EXISTS `func_get_splitStringTotal`;   
CREATE FUNCTION `func_get_splitStringTotal`(    
f_string varchar(10000),f_delimiter varchar(50)    
) RETURNS int(11)    
BEGIN    
  return 1+(length(f_string) - length(replace(f_string,f_delimiter,\'\')));
end;

(2).函数func_splitString：将字符串按指定方式分割，获取指定位置的数。

DROP function IF EXISTS `func_splitString`;   
CREATE FUNCTION `func_splitString`    
(f_string varchar(1000),f_delimiter varchar(5),f_order int)   
  RETURNS varchar(255) CHARSET utf8   
BEGIN    
 
    declare result varchar(255) default \'\';    
    set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1));    
    return result;    
END;

(3).存储过程splitString 将字符串分割，并放到临时表tmp_split 里面

DROP PROCEDURE IF EXISTS `splitString`;  
CREATE PROCEDURE `splitString`    
(IN f_string varchar(1000),IN f_delimiter varchar(5))    
BEGIN  
    declare cnt int default 0; 
    declare i int default 0; 
    set cnt = func_get_splitStringTotal(f_string,f_delimiter); 
    DROP TABLE IF EXISTS tmp_split; 
    create temporary table  tmp_split (val_ text not null) DEFAULT CHARSET=utf8; 
    while i < cnt 
    do 
    set i = i + 1; 
    insert into tmp_split(val_) values (func_splitString(f_string,f_delimiter,i)); 
    end while;    
END;

 (4).调用的代码：

set @xmls = \'<field name="client_id"  Id="001">2</field>,<field name="client_id2"  Id="002">3</field>\';
call splitString(@xmls,\',\');  
SELECT  ExtractValue(val_,\'/field/@name\') from tmp_split;

 查询的结果集如下：

 

(5)处理xml,MYSQL不支持xml类型，用text来替换。

DROP PROCEDURE IF EXISTS AutoDownload;
CREATE PROCEDURE AutoDownload(AccXML text)
BEGIN
  IF AccXML is not NULL
  THEN
    -- 新建临时表 
    CREATE TEMPORARY TABLE if not EXISTS tb_Accounts    
    (    
         CreateTime datetime NULL,
         MType varchar(100) NULL,
         BusinessType varchar(100) NULL,
         Balance decimal(18,2) NULL,
         InAmount decimal(18,2) NULL,
         OutAmount decimal(18,2) NULL,
         AlipayOrderNo varchar(200) NULL,
         MerchantOrderNo varchar(200) NULL,
         SelfUserId varchar(100) NULL,
         OptUser varchar(100) NULL,
         Memo varchar(800) NULL,
         Tid varchar(100) null        
    );
        
    -- 调用存储过程，分割xml
    CALL splitString(AccXML,\',\'); 

    -- 往临时表里面插入数据
    INSERT into tb_Accounts
    SELECT ExtractValue(val_,\'/AccountsXML/@CreateTime\')  as \'CreateTime\'
           ,ExtractValue(val_,\'/AccountsXML/@MType\') as MType
           ,ExtractValue(val_,\'/AccountsXML/@BusinessType\') as BusinessType
     FROM tmp_split; 
     
     drop table tb_Accounts;
end;

(6)函数循环获取字符串：

DELIMITER $$

USE `plat_test_ch`$$

DROP FUNCTION IF EXISTS `splitStringStr`$$

CREATE DEFINER=`root`@`%` FUNCTION `splitStringStr`(f_string VARCHAR(1000),f_delimiter VARCHAR(5)) RETURNS varchar(255) CHARSET utf8
BEGIN
       DECLARE str VARCHAR(255) DEFAULT \'\';    
       DECLARE cnt INT DEFAULT 0; 
       DECLARE i INT DEFAULT 0; 
       DECLARE qid VARCHAR(255) DEFAULT \'\';    
       DECLARE icontent VARCHAR(255) DEFAULT \'\';    
       SET cnt = func_get_splitStringTotal(f_string,f_delimiter); 
       WHILE i < cnt  
       DO 
        SET i = i + 1; 
        SET qid = func_splitString(f_string,f_delimiter,i);
        if qid is not null and qid <> \'\' then 
     SET icontent =(SELECT Content FROM test_question WHERE QuestionID = qid); 
     IF icontent IS NOT NULL THEN
        if i = 1 then 
           SET str = CONCAT(str,icontent); 
        else 
           SET str = CONCAT(str, \',\', icontent); 
        end if; 
      END IF; 
    end if;
       END WHILE; 
    RETURN str;    
    END$$

DELIMITER ;

 

21.开始事物，用START TRANSACTION; 不用begin TRANSACTION。

22.嵌套删除数据：

  mysql ： 

delete a from DeliverToLogistics as a,deliverorder as b
where a.DeliverCode = b.`Code`;
DELETE DeliverToLogistics where DeliverCode in(select `Code` from deliverorder); 支持Sqlserver，不支持mysql

DELETE from Inventory WHERE AdjustCode=valAdjustCode; 支持mysql和sqlserver

DELETE Inventory WHERE AdjustCode=valAdjustCode;不支持mysql,支持sqlserver

 mysql多表删除

delete a from DeliverToLogistics as a INNER JOIN deliverorder as b
on a.DeliverCode = b.`Code`;

 

23.MySQL 取得两个时间相差的分钟数

-- 取得相隔秒数
SELECT UNIX_TIMESTAMP(\'2012-06-09 00:10:11\')-UNIX_TIMESTAMP(\'2012-06-09 00:09:12\')

-- 取得相隔分钟数
SELECT round((UNIX_TIMESTAMP(\'2012-06-09 00:10:11\')-UNIX_TIMESTAMP(\'2012-06-09 00:09:12\'))/60)

 24.拼接字符串和执行SQL的结果解插入到临时表

set @whereStr = \' and Id = 7957\';
set @orderByStr = \'Id asc\'; 

CREATE TEMPORARY TABLE IF NOT EXISTS t(
            RowId int PRIMARY KEY,
            Id int,
            Remark text,
            Sort int,
            `Status` int, 
            CreateDateTime datetime
    );
    
-- 拼接SQL
set @sqlStr = concat(\'INSERT INTO t SELECT @rownum := @rownum + 1 as RowId
    ,Id,Remark,Sort,`Status`,CreateDateTime
    FROM 
    (SELECT Id,Remark,Sort,`Status`,CreateDateTime from sysoperation
    where 1= 1\' ,@whereStr,\' ORDER BY \',@orderByStr,\') tb,
  (SELECT @rownum := 0) r;\');

prepare selectList from @sqlStr;
-- 执行拼接的SQL
EXECUTE selectList;
DEALLOCATE prepare selectList;

SELECT * from t;

 25.查看当前使用的数据库：SELECT DATABASE();

 26.sqlserver的NEWID() 对应 mysql的 uuid();

 27.查看数据表：SHOW TABLES; 查看mysql中所有的数据表：SHOW TABLES FROM mysql; 查看表结构：SHOW COLUMNS from 表名;

28.AUTO_INCREMENT 自动编号，且必须与主键组合使用；但是主键不一定和AUTO_INCREMENT 使用；默认情况下，起始值为1，每次的增量为1；

create table tb1
(
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(30) not null
);

show COLUMNS from tb1;

 29.唯一约束 UNIQUE key

create table tb2
(
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(30) not null UNIQUE key
);

 30.默认约束 default 

create table tb3
(
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(30) not null UNIQUE key,
  sex enum(\'1\',\'2\',\'3\') DEFAULT \'3\' -- 1：男 2：女 3：保密
);

 31.外键约束的要求：

    a.父表和子表必须使用相同的存储引擎，而且禁止使用临时表。

    b.数据表的存储引擎只能为InnoDB。MySQL配置文件（my.ini）：default-storage-engine = INNODB

    c.外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同。

    d.外键列和参照列必须创建索引。如果外键列不存在索引的话，MYSQL将自动创建索引。

    比如：

     

-- 创建脚本
create table provinces
(
   id SMALLINT UNSIGNED PRIMARY KEY auto_increment,
   pname varchar(20) not null
);
-- select * from provinces;

-- 显示表结构 
SHOW CREATE TABLE provinces;

Table        CREATE Table 
provinces    CREATE TABLE `provinces` (
            `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
            `pname` varchar(20) NOT NULL,
             PRIMARY KEY (`id`)
           ) ENGINE=InnoDB DEFAULT CHARSET=latin1

 -- 外键

CREATE TABLE users
(
   Id SMALLINT UNSIGNED PRIMARY KEY auto_increment,
   username VARCHAR(10) not null,
   pid SMALLINT UNSIGNED,-- 数字类型和无符号位和provinces的ID一致
FOREIGN key(pid) REFERENCES provinces (id) );

 -- 显示索引：SHOW INDEX FROM provinces;

32.

外键约束的参照操作：

a.CASCADE :从父表删除或更新且自动删除或更新子表中匹配的行。
b.SET NULL:从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL
c.RESTRICT:拒绝对父表的删除或更新操作。
d.NO ACTION :标准的SQL的关键字，在MySQL中与RESTRICT相同。

CREATE TABLE `provinces1` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `pname` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;


CREATE TABLE users1 (
  `Id` smallint(5) unsigned NOT NULL AUTO_INCREMENT  PRIMARY KEY,
  `username` varchar(10) NOT NULL,
  `pid` smallint(5) unsigned,
  FOREIGN KEY (pid) REFERENCES provinces1 (id) ON DELETE CASCADE
);

INSERT provinces1(pname) VALUES(\'A\'),(\'B\'),(\'C\');

 33.添加单列：

语法：ALTER TABLE tbl_name ADD [COLUMN] col_name
         column_definition [FIRST|AFTER col_name]

   -- 添加的列位于到所有列的最后面
   ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT null DEFAULT 10;
   -- 添加的列位于username列的后一列
   ALTER TABLE users1 ADD pwd VARCHAR(30) NOT NULL AFTER username; 
   -- 添加的列位于到所有列的最前面
   ALTER TABLE users1 ADD truename VARCHAR(30) NOT NULL FIRST;

 SqlServer: alter table 表名 add 列名 字段类型 -> alter table shopinfo add ShopUrl nvarchar(100) ;

  34.添加多列 

    语法：ALTER TABLE tbl_name ADD [COLUMN] 

             (col_name column_definition,...)

    例子: ALTER TABLE users1 ADD (sex int,address VARCHAR(100));

   35.删除列

     语法： ALTER TABLE tbl_name DROP [COLUMN] col_name;

     例子： ALTER TABLE users1 DROP age; -- 删除一列

               ALTER TABLE users1 DROP sex,DROP address; -- 删除多列

    新增列，同时删除列，用逗号隔开。 ->  ALTER TABLE users1 DROP pwd,ADD address VARCHAR(100);

  36.添加约束

CREATE TABLE users3 (
  `Id` smallint(5) unsigned NOT NULL,
  `username` varchar(10) NOT NULL,
  `pid` smallint(5) unsigned DEFAULT NULL
);

-- 添加主键约束
ALTER TABLE users3
ADD CONSTRAINT PK_users2_id PRIMARY key(id);
-- 删除主键约束
 ALTER TABLE users3 DROP PRIMARY KEY;

-- 添加唯一约束
ALTER TABLE users3
ADD CONSTRAINT UNIQUE(username);

-- 删除唯一约束
ALTER TABLE users3 DROP INDEX username;

 -- 添加外键约束
 ALTER TABLE users3 ADD FOREIGN KEY(pid) REFERENCES provinces(id);
 -- 删除外键约束
 ALTER TABLE users3 DROP FOREIGN KEY user_2;

-- 添加/删除默认约束
ALTER TABLE users3 ADD age TINYINT UNSIGNED NOT NULL;

ALTER TABLE users3 ALTER age SET DEFAULT 15;

SHOW COLUMNS FROM users3;

ALTER TABLE users3 ALTER age DROP DEFAULT; --  删除约束

 

  37.修改列定义，列名称，数据表更名。不建议修改，会影响视图和存储过程

     

 

 

 

   37.游标的使用：参考：http://www.cnblogs.com/trying/p/3296793.html

CREATE PROCEDURE `test`.`new_procedure` ()
BEGIN
-- 需要定义接收游标数据的变量 
  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;

END

 38.like   模式匹配

    SELECT * from `order` where `code` like \'%%%\'; //mysql将三个%都认为为通配符

   可以这样来处理： SELECT * from `order` where `code` like \'%1%%\' ESCAPE \'1\';

   百分号(%):任意个字符

  下划线_:任意一个字符

  SELECT TRIM(LEADING \'?\' FROM \'??MySQL???\'); // MySQL???  LEADING :前导   

  SELECT TRIM(TRAILING \'?\' FROM \'??MySQL???\');  //  ??MySQL   TRAILING :后续

 39.查看表的结构：desc 表名;

 40.查询对比：

SELECT HealthHutType,COUNT(0),OrgID FROM sys_healthhut GROUP BY HealthHutType 
支持mysql,不支持sqlserver

41.修改mysql的注释：

ALTER TABLE sys_Test MODIFY IResult TINYINT(4) DEFAULT \'0\' COMMENT \'邀约结果：0 同意 1 不需要 2 时间待定\';

 42.mysql可以连续增加多个字段：

ALTER TABLE  Test ADD(TestWay TINYINT(4) DEFAULT \'0\' COMMENT \'邀约方式 1：呼叫中心、2：自行邀约\',DrId INT(11) DEFAULT \'0\' COMMENT \'推广大使Id\');

 43.查询数据库编码格式：SHOW VARIABLES LIKE \'%char%\'; 

 45. mysql FIND_IN_SET的使用 like是广泛的模糊匹配，字符串中没有分隔符，Find_IN_SET 是精确匹配，字段值以英文”,”分隔，Find_IN_SET查询的结果要小于like查询的结果。

 46.mysql 查询的结果集可以导出sql出来，然后进行修改相关值。

 47.mysql 显示表字段的属性：show full fields from tableA

 48.mysql修改注释和字段名称 

ALTER TABLE sys_Test MODIFY Status TINYINT NOT NULL COMMENT \'服务状态 0 正常 1 异常\';
ALTER TABLE sys_Test CHANGE Status srv TINYINT NOT NULL COMMENT \'服务状态 0 正常 1 异常\';

mysql 修改表字段的类型

alter table Test modify column Id varchar(20);

mysql 修改表的字段为自增长

ALTER TABLE `tableUSer` MODIFY COLUMN `userID`  int(11) NOT NULL AUTO_INCREMENT FIRST ;

49.mysql 索引

1)新增和删除索引

RESET QUERY CACHE;
-- 清除缓存在
RESET QUERY CACHE;
FLUSH TABLES;
RESET MASTER;

新增索引：
CREATE INDEX `idx_testpersonalscheme_CreateID_MSTypeID` ON `testpersonalscheme`(`CreateID`, `MSTypeID`) USING BTREE ;
删除索引:
ALTER TABLE `testpersonalscheme` DROP INDEX `idx_testpersonalscheme_CreateID_MSTypeID`;

2)查看索引

 SHOW INDEX FROM table1

3).55.mysql 优化 explain ：参考 http://blog.sina.com.cn/s/blog_4586764e0100o9s1.html

50.查看数据库物理路径：SHOW VARIABLES LIKE \'%data%\' 下面的数据和ibdata1,ib_logfile1和ibdata1 可以直接拷贝出来对应新的环境上。

 问题，拷贝完的时候，发现打开表出现表不存在，怎么解决？ 参考：http://www.aichengxu.com/view/6440103

除了复制.frm的源数据库文件夹外，还需停止mysql服务(无论是拷贝源还是拷贝目的地，mysql服务最好都停掉)，然后再复制与源数据库文件夹同目录下的以下3个文件粘贴或覆盖到目的文件中，然后再启动Mysql服务即可。


拷贝以上3个文件需以停止Mysql服务为前提是因为:Mysql采用的是缓冲方式来将数据写入ibdata1中，所以在拷贝时若不停止Mysql服务有可能导致ibdata1文件不完整，同时目的端因Mysql服务处于运行状态，会造成该文件程序占用，从而不能拷贝。

51.备份数据库

USE test_ch;
DROP TABLE IF EXISTS sys_testuser_bak;
CREATE TABLE sys_testuser_bak SELECT * FROM remp_sys_ch.sys_testuser;

52.Toad for MySQL Freeware 可以对mysql断点调试

53.mysql 根据当前日期分组，group by DATE_FORMAT(NOW(),\'%Y-%m-%d\'),  2017-12-06 

    排序 DATE_FORMAT(createtime,\'%Y%m%d%h%i%s\') DESC  2017/11/7 14:10:04对应20171107021004

54.mysql 字段为bit类型，可以这样查。WHERE IsDeled = 0 或 WHERE IsDeled !=TRUE 或 where !IsDeleted;  时间类型createdtime IS NOT NULL 或!ISNULL(createdtime)

ALTER TABLE `tableUSer` MODIFY COLUMN `userID`  int(11) NOT NULL AUTO_INCREMENT FIRST ;

55.根据明细表和主表分组去掉重复的明细项

SELECT phyItem.TestCode,phyItem.CheckValue,phyItem.TestID
,(SELECT TestTime FROM CM_Test WHERE TestID = phyItem.TestID AND IsDel = 0) AS CheckTime
FROM CM_TestIndexItem phyItem
JOIN 
(
SELECT TestCode,CheckValue,MAX(TestID) TestID
FROM CM_TestIndexItem 
WHERE  IsDel = 0 AND CheckValue IS NOT NULL AND CheckValue <> \'\'  
AND  TestID = 1199085  GROUP BY TestCode ) tb 
ON phyItem.TestID=tb.TestID AND phyItem.TestCode=tb.TestCode 
WHERE phyItem.IsDel = 0

 56.mysql从身份证号中提取生日、性别

SELECT
    p.ID,
    p.IDCardNo,
IF (
    LENGTH(p.IDCardNo) = 18,
    CAST(
        SUBSTRING(p.IDCardNo, 7, 8) AS DATETIME
    ),
    CAST(
        CONCAT(
            \'19\',
            SUBSTRING(p.IDCardNo, 7, 6)
        ) AS DATETIME
    )
) AS Birthday,
IF (
    SUBSTR(
        p.IDCardNo,
        LENGTH(p.IDCardNo) - 1,
        1
    ) % 2 = 1,
    \'男\',
        \'女\'
) AS sex
FROM
    users p
WHERE
    p.IsDelete = 2
AND p.IDCardNo REGEXP \'[1-8][0-8][0-9]{16}|[1-8][0-8][0-9]{15}X|[1-8][0-8][0-9]{13}\'
AND (
    length(p.IDCardNo) = 18
    OR LENGTH(p.idcardno) = 15
)

 

    

相关文章