樹上的芽

 

	-- 字元相加
	sqlserver
		SELECT null + 'A' 
		=> 'A'
		SELECT 'A' + 'ABC' 
		=> 'AABC'
	MySql
		SELECT null + 'A' 
		=> null
		SELECT CONCAT('A','ABC');
		=>'AABC'
	
	-- 如果為空則輸出'xxx'
	sqlserver
		SELECT ISNULL(NULL, '0')
		=> '0'
		SELECT ISNULL('AA', '0')
		=> 'AA'

	MySql
		SELECT IFNULL(NULL, '0')
		=> '0'
		SELECT IFNULL('AA', '0')
		=> 'AA'

	-- 字串取中間字元
	sqlserver
		SELECT SUBSTRING('1234測試5678', 0, DATALENGTH('1234測試5678')) 
		=> '1234測試5678'
	MySql
		SELECT SUBSTRING('1234測試5678', 1, LENGTH('1234測試5678')) 
		=> '1234測試5678'
	
	-- 擷取字串
	sqlserver
		SELECT CONVERT(CHAR(9), 'ABC測試0123456')
		=> ABC測試01
	MySql
		SELECT CONVERT('ABC測試0123456', CHAR(9))
		=> ABC測試01
	
	-- 清空資料表
	sqlserver
		DELETE FROM mail_server WHERE 1=1
	MySql
		DELETE FROM mail_server WHERE mail_server_key > ''
	
	--日期
	sqlserver
		SELECT CONVERT(varchar, GETDATE(), 120)
		=>2023-05-10 10:15:18
	MySql
		SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %T');
		=>2023-05-10 10:18:04
		
	--日期減一天
	sqlserver
		SELECT GETDATE() ,GETDATE() - 1
		=>2023-05-10 10:54:37.797 ,2023-05-09 10:54:37.797
	MySql
		SELECT NOW() ,NOW() - INTERVAL 1 DAY
		=>2023-05-10 10:55:42 ,2023-05-09 10:55:42
		
	-- 只要兩筆資料
	sqlserver
		SELECT TOP 2 * FROM users;
	MySql
		SELECT * FROM users LIMIT 2;
	
	-- 數字
	sqlserver
		SELECT CONVERT(INT, 1234.9876);
		=> '1234'
		SELECT CAST('123.987' AS DECIMAL);
		=> '1234'
	MySql
		SELECT CONVERT(' 0123A456', DECIMAL);
		=> '123'
		SELECT CONVERT('123.987', DECIMAL);
		=> '124'
		SELECT CAST('123.987' AS DECIMAL);
		=> '1234'
		SELECT CAST('12X9.987' AS DECIMAL);
		=> '12'
		SELECT CAST('X123.987' AS DECIMAL);
		=> '0'
	
	-- 判斷後移除資料表
	sqlserver
		IF OBJECT_ID('tempdb..[#abcAddDb1]') IS NOT NULL
		DROP TABLE #abcAddDb1	
	MySql
		DROP TABLE if exists abcAddDb1;

	--長度null
	sqlserver
		SELECT DATALENGTH(null)
		=> NULL
	MySql
		SELECT LENGTH(null)
		=> NULL
		SELECT LENGTH(IFNULL(NULL, '0'))
		=> 0
	
	--變數 參數
	sqlserver
		DECLARE @Data0 varchar(255)
		SELECT @Data0 = '123456'
		SELECT * FROM Ddd WHERE 1 = 1 AND D1 = @Data0
	MySql
		Set @Data0 = '123456'; 
		SELECT * FROM Ddd WHERE 1 = 1 AND D1 = @Data0
	
	--變數 取資料
	sqlserver
		DECLARE @Data0 int
		SELECT @Data0=count(first_name) FROM users 
	MySql
		SELECT @Data0:=count(first_name) FROM users

 

我只是一棵樹