[译] SQL Server 与 MariaDB SQL语句对比
SqlServer
2015-12-15
501
0
问题
我的工作需要同时使用SQL Server和MariaDB. 我想知道一些常用的代码怎么写. 你能提供 MariaDB 和 SQL Server 代码对比表么, 比如注释,创建函数,带参数的存储,怎么显示前几行的数据,怎么用循环等?
解决方案
下面是SQL Server 和 MariaDB命令代码区别的对比
安装需求
1. SQL Server 2014 , 当然95%的T-SQL 命令在老版本都可以运行的 2. 安装MariaDB 安装包可以从这里下载
如何在SQL Server中使用命令行
用管理员权限运行sqlcmd 命令就可以了
如何在MariaDB中使用命令行
1. 在安装目录里面打开 MySQL Client(MariaDB)
2.会提示你输入密码,改密码就是你安装时候提示你设置的那个.
SQL Server 与 MariaDB SQL代码对比
创建数据库
SQL Server:
CREATE DATABASE TestDB GO 详细信息请参考CREATE DATABASES
MariaDB:
CREATE DATABASE TestDB2; 详细信息请参考 CREATE DATABASES
切换数据库
SQL Server:
USE TestDB GO
MariaDB:
USE TestDB;
验证创建的数据库
SQL Server:
exec sp_databases GO
MariaDB:
show databases;
建立表格
SQL Server:
create table customer(id int,name varchar(30)) GO
相关信息可以参考以下链接
- Create tables
- SQL Server Data types MariaDB:
create table customer(id int,name varchar(30)); 相关信息可以参考以下链接
在表格中出入数据
SQL Server:
insert into customer values (1,'John') GO 更多信息: INSERT Tutorial
MariaDB:
insert into customer values (1,'John');
创建一个简单存储
SQL Server:
create procedure showcustomers as select * from customer GO 更多信息: Stored Procedure Tutorial
MariaDB:
create procedure showcustomers() select * from customer;
调用简单存储
SQL Server:
exec showcustomers GO
MariaDB:
call showcustomers;
创建一个带循环的存储
SQL Server:
create procedure dbo.repeatsample @l INT as declare @value int=0 while @l>@value-1 BEGIN SET @value = @value + 1 END SELECT @value GO
MariaDB:
delimiter // CREATE PROCEDURE repeatsample(l INT) BEGIN SET @value = 0; REPEAT SET @value = @value + 1; UNTIL @value > @l END REPEAT; END //
调用一个带循环的存储
SQL Server:
exec repeatsample 10
MariaDB:
CALL repeatsample(10)//
SELECT @value//
获取当前日期
SQL Server:
select CONVERT(date, GETDATE()) GO 更多信息:SQL Server Date Tips
MariaDB:
select current_date;
获取当前时间
SQL Server:
select CONVERT(time, GETDATE()) GO 更多信息:SQL Server Date Tips
MariaDB:
select current_time;
获取当前日期和时间
SQL Server:
select getdate()
更多信息 SQL Server Date Tips
MariaDB:
select current_date, current_time;
修改日期格式
SQL Server:
select FORMAT(getdate(),'MM-dd-yyyy') as date GO
更多信息:New SQL Server Function to Format Dates Date and Time Conversions Using SQL Server
MariaDB:
select date_format(current_date,'%m%d%Y');
更多信息:点此链接
设置变量值
SQL Server:
declare @var int=1 select @var GO
MariaDB:
set @var=1; select @var;
返回当前数据库中的表清单
SQL Server:
SELECT * FROM information_schema.tables GO
MariaDB:
SHOW TABLES;
返回当前数据库中的视图清单
SQL Server:
SELECT * FROM information_schema.views GO
MariaDB:
SHOW TABLES; #同时显示表和视图
单行备注
SQL Server:
--两个横杠是SQL Server 的单行备注
MariaDB:
#井号是MariaDB的单行备注
多行备注
SQL Server:
/多行注释写在 这里面/
MariaDB:
/MariaDB 多行注释和 SQL Server 一样/
取前五行记录
SQL Server:
SELECT TOP 5 * FROM dbo.customer; GO
MariaDB:
SELECT from customer LIMIT 5;
创建函数
SQL Server:
CREATE FUNCTION dbo.hello_mssqltips() RETURNS varchar(30)
AS BEGIN RETURN ('Hello mssqltips') END GO
MariaDB:
DELIMITER $$ CREATE FUNCTION hello_mssqltips() RETURNS varchar(30) LANGUAGE SQL BEGIN RETURN 'Hello mssqltips'; END; $$ DELIMITER ;
创建带参数的函数
SQL Server:
CREATE FUNCTION dbo.hello_withparameter(@name varchar(30)) RETURNS varchar(30)
AS BEGIN RETURN ('Hello '+@name) END GO
MariaDB:
DELIMITER $$ CREATE FUNCTION hello_withparameter(name varchar(30)) RETURNS TEXT LANGUAGE SQL BEGIN RETURN CONCAT('Hello ', name); END; $$ DELIMITER ;
调用带参数的函数
SQL Server:
select dbo.hello_withparameter('John') GO
MariaDB:
select hello_withparameter('John');
显示一个表的信息
SQL Server:
sp_help customer; GO
MariaDB:
describe customer;
获取创建代码
SQL Server:
sp_helptext object_name
只能返回存储,函数和视图的代码,表格不行.
MariaDB:
help create table help create procedure help create function
将查询结果存储在文本文件中
SQL Server:
Save this query in a file named customerquery.sql and save it at c:.
USE Test; GO SELECT * FROM dbo.customer GO
Now at the cmd prompt run this command:
sqlcmd -i c:\scripts\customerquery.sql -o c:\scripts\results.txt
MariaDB:
/First update the user privileges, in this case the root User to increase the privileges to write files./
UPDATE user SET File_priv = 'Y' WHERE User = 'root'; FLUSH PRIVILEGES;
/Now you can save the results of a query in the customer.csv file./
select * from customer into outfile 'c:\customer.csv'
IF, ELSEIF 和 ELSE 判断
SQL Server:
/This sample shows the water state according to the temperature/
CREATE FUNCTION dbo.WaterState(@temperature INT ) RETURNS varchar(10) as BEGIN declare @state varchar(10) IF @temperature = 100 SET @state = 'Boiled'
IF @temperature between 1 and 99 SET @state = 'Liquid'
ELSE SET @state = 'Frozen'
RETURN @state
END GO
MariaDB:
DELIMITER //
CREATE FUNCTION WaterState ( temperature INT ) RETURNS varchar(10)
BEGIN declare state varchar(10) ; IF temperature = 100 THEN SET state = 'Boiled';
ELSEIF temperature between 1 and 99 then SET state = 'Liquid';
ELSE SET state = 'Frozen';
END IF;
RETURN state;
END; //
DELIMITER ;
下一步
MariaDB commands T-SQL Reference T-SQL Tips All SQL Server Developer Tips All SQL Server Tutorials
原文地址
Code Comparison for SQL Server vs. MariaDB Compare MariaDB vs SQL Server SQL Commands