[译] SQL Server 与 MariaDB SQL语句对比

问题

我的工作需要同时使用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

在表格中出入数据

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

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