SqlServer 与 MySQL 基本操作语句对比

继上篇?MySQL 基本操作语句?后，个人测试和补充了?SqlServer 与 MySQL 的?基本操作语句对比，主要是对比SQL命令的一些差异，不作更多说明。

（由于mysql 没有系统学习过，欢迎大侠们拍砖~~O(∩_∩)O~）

?SqlServer版本：SqlServer 2008

MySQL版本： MySQL 5.5

执行符号：

MySQL ：分号';'

SqlServer ：字符'GO'

?

?

自增列：MySQL必须要索引，不能设置增量；

默认值：MySQL默认值不用加括号；SqlServer可加或不加；

--MySQL

CREATE TABLE?tabName(?

ID INTPRIMARY KEY AUTO_INCREMENT,

Value INTDEFAULT 12

) AUTO_INCREMENT= 100 ENGINE=MyISAM?DEFAULT CHARSET=utf8;?

?

--SqlServer

CREATE TABLE?tabName(

ID INTIDENTITY(100,1)PRIMARY KEY,

Value INTDEFAULT(12)

)

?

查看表结构定义：

--SqlServer?

EXEC sp_help'tabName'?

--MySQL

DESC tabName

?

更改表名：

--SqlServer?

EXEC sys.sp_rename@objname= N'tabName',@newname = 'newTabName'

--MySQL

ALTER TABLE tabName RENAME newTabName

?

更改字段类型：

--SqlServer

ALTER TABLE[dbo].[tabName]ALTER COLUMN [ID] BIGINT

?

--MySQL

ALTER TABLE tabName MODIFY IDBIGINT

?

更改字段名：

--SqlServer

EXEC sys.sp_rename@objname= N'tabName.ID',@newname= 'IewID',@objtype= 'column'

?

--MySQL

ALTER TABLE tabName CHANGE IDIewID BIGINT

?

添加字段：（一样）

--SqlServer

ALTER TABLE[dbo].[tabName]ADD NAME NVARCHAR(200)NULL

?

--MySQL

ALTER TABLE tabName ADD NAMENVARCHAR(200)NULL

?

删除字段：

--SqlServer

ALTER TABLE[dbo].[tabName]DROP COLUMN NAME

?

--MySQL

ALTER TABLE tabName DROP NAME

?

添加主键/外键或约束：

--SqlServer

ALTER TABLE[dbo].[tabName]ADD CONSTRAINT pk_tabNamePRIMARY KEY (id);?

?

--MySQL

ALTER TABLEtabName ADD CONSTRAINT pk_tabNamePRIMARYKEY tabName(id);??

ALTER TABLEsubTabName ADD CONSTRAINT fk_subTabName_tabNameFOREIGNKEY subTabName(fid)REFERENCES tabName(id);

?

删除主键/外键或约束：

--SqlServer（统一语法）

ALTER TABLE[dbo].[tabName]DROP CONSTRAINT pk_tabName

?

--MySQL

ALTER TABLEtabName DROP CONSTRAINT constaintName

ALTER TABLEtabName DROP PRIMARY KEY pk_tabName?

ALTER TABLE subTabName DROP FOREIGNKEY fk_subTabName_tabName?

?

删除表：

--SqlServer

DROP TABLEtabName,TabName2;

?

--MySQL

DROP TABLE IF EXISTS tabName,TabName2;

?

#######################################################################################

?

创建视图：

--MySQL

CREATE ORREPLACE VIEW VtabName

AS

SELECT *FROM tabName

WITH CHECK OPTION;

?

--SqlServer

CREATE VIEWVtabName

AS

SELECT *FROM tabName

WITH CHECK OPTION;

?

视图结构和定义：

--MySQL

DESC VtabName;?

SHOW CREATEVIEW VtabName;?

SHOW TABLE STATUS LIKE 'VtabName';

?

--SqlServer

sp_help VtabName

sp_helptext VtabName

?

删除视图：

--MySQL

DROP VIEWtabName;

DROP VIEW IF EXISTS tabName;

?

--SqlServer

DROP VIEWtabName;

?

#######################################################################################

?

创建索引：

--MySQL

CREATE TABLEtabName( id INT NOTNULL,KEY indName(id));

ALTER TABLEtabName ADD INDEX indName(Value);

CREATE UNIQUE INDEX indName ONtabName(Value);

?

--SqlServer

CREATE TABLE tabName(IDINT IDENTITY(100,1)CONSTRAINT PK_tabNamePRIMARYKEY)

CREATE UNIQUE INDEX indName ONtabName(Value);

?

重建索引：

--MySQL

REPAIR TABLE tabName QUICK;

?

--SqlServer

DBCC DBREINDEX('dbo.tabName','',100)

ALTER INDEXindName ON tabName REBUILD;

ALTER INDEX ALL ON tabNameREORGANIZE;

?

删除索引：

--MySQL

ALTER TABLEtabName DROP INDEX indName;

ALTER TABLEtabName DROP PRIMARY KEY pk_tabName;

ALTER TABLEsubTabName DROP FOREIGN KEY fk_subTabName_tabName;

DROP INDEX indName ON tabName;

?

--SqlServer

DROP INDEX indName ON tabName;

ALTER TABLE tabName DROP CONSTRAINTPK_tabName

?

查看索引：

--MySQL

SHOW INDEX FROM tabName;

?

--SqlServer

SELECT *FROM sys.indexesWHERE object_id= OBJECT_ID('tabName')

SELECT * FROM sys.sysindexesWHERE id = OBJECT_ID('tabName')

?

#######################################################################################

查看存储过程/函数定义：

--MySQL

SHOW CREATE{ PROCEDURE | FUNCTION }name ;??

SHOW {PROCEDURE | FUNCTION } STATUS[ LIKE 'pattern'];?

SELECT * FROM information_schema.RoutinesWHERE ROUTINE_NAME='name';?

?

--SqlServer

exec sp_helpf_getdate

exec sp_helptextf_getdate

SELECT * FROM information_schema.RoutinesWHERE ROUTINE_NAME='name';?

?

存储过程：

--MySQL

DELIMITER //?

CREATE PROCEDUREsp_name(IN param1 INT,OUTparam2 INT)??

BEGIN?

? {sql_statement}?

END//?

DELIMITER ;?

?

CALL sp_name();??

DROP PROCEDUREIF EXISTS sp_name;

?

--SqlServer

CREATE PROCEDUREsp_name(@param1INT,@param2INT OUTPUT)

AS

BEGIN?

? {sql_statement}

END

GO

?

EXEC sp_name();??

DROP PROCEDUREsp_name;

?

函数：

--MySQL

DELIMITER //?

CREATE FUNCTIONfn_name()?

RETURNS Decimal(10,2)?

RETURN 3.14;?

//?

DELIMITER ;?

?

SELECT fn_name();?

DROP PROCEDUREIF EXISTS fn_name;?

?

--SqlServer

CREATE FUNCTIONdbo.fn_name()?

RETURNS Decimal(10,2)

AS

BEGIN

RETURN 3.14

END

GO

?

SELECT dbo.fn_name();?

DROP FUNCTIONdbo.fn_name;

?

触发器：

--MySQL

DELIMITER //?

CREATE TRIGGERtr_name?

{ AFTER| BEFORE } { INSERT| UPDATE | DELETE }?

ON tabName?

FOR EACHROW??

BEGIN?

? {sql_statement;}

END?

DELIMITER ;?

?

DROP TRIGGER IF EXISTS tr_name;?

?

--SqlServer

CREATE TRIGGERdbo.tr_name

ON [dbo].[tabName]

{ FOR| AFTER | INSTEAD OF} { INSERT | UPDATE| DELETE }

AS

BEGIN

? {sql_statement;}

END

GO

?

DROP TRIGGER dbo.tr_name

?

#######################################################################################

?

循环语句：

--MySQL（1至100之和）

WHILE 循环：

DELIMITER //

CREATE PROCEDURETESTPRO()

BEGIN

DECLARE I,K INTDEFAULT 0 ;

WHILE I<= 100 DO

SET K= I + K;

SET I= I + 1;

END WHILE;

SELECT K;

END;//

DELIMITER ;

?

REPEAT UNTIL 循环：

DELIMITER //

CREATE PROCEDURETESTPRO()

BEGIN

DECLARE I,K INTDEFAULT 0 ;

REPEAT

SET K= I + K;

SET I= I + 1;

UNTIL I>100

END REPEAT;

SELECT K;

END;//

DELIMITER ;

?

LOOP 循环：

DELIMITER //

CREATE PROCEDURETESTPRO()

BEGIN

DECLARE I,K INTDEFAULT 0 ;

LABEL: LOOP

SET K= I + K;

SET I= I + 1;

IF I>100THEN

LEAVE LABEL;

END IF;

END LOOP;

SELECT K;

END;//

DELIMITER ;

?

?

CALL TESTPRO();

DROP PROCEDUREIF EXISTS TESTPRO;

?

--SqlServer

DECLARE @I INT = 1

DECLARE @K INT = 0

WHILE @I<= 100

BEGIN

??? SET @K = @K + @I

??? SET @I = @I + 1

END

SELECT @K

?

游标：

--MySQL(参数名称不能与列明一样)

DELIMITER //

CREATE PROCEDURETESTPRO()

BEGIN

DECLARE FName varchar(50);

DECLARE LName varchar(50);

DECLARE IsDone BOOLEAN DEFAULTFALSE;

DECLARE cursor_name CURSOR FOR SELECT FirstName,LastNameFROM Person;

DECLARE CONTINUE HANDLER FOR NOT FOUNDSET IsDone = TRUE;

OPEN cursor_name;

LABEL: LOOP

??? FETCH cursor_name INTO FName,LName;

??? IF IsDoneTHEN

??? LEAVE LABEL;

??? END IF;

??? SELECT CONCAT('Contact Name:',FName,LName)AS Name;

END LOOP;

CLOSE cursor_name;

END;//

DELIMITER ;

?

--SqlServer

DECLARE @LastName varchar(50),@FirstName varchar(50);

DECLARE contact_cursor CURSOR FOR SELECT LastName,FirstName FROM Person

OPEN contact_cursor;

FETCH NEXTFROM contact_cursorINTO @LastName,@FirstName;

WHILE @@FETCH_STATUS = 0

BEGIN

??? SELECT 'Contact Name: ' + @FirstName + ' '+ @LastName

??? FETCH NEXT FROM contact_cursorINTO @LastName,@FirstName;

END

CLOSE contact_cursor;

DEALLOCATE contact_cursor;

GO

?

#######################################################################################

查看账户信息：

--MySQL

select Host,User,Passwordfrom mysql.user;

show grantsfor 'username'@'localhost';

?

--SqlServer

select *from sys.syslogins

select * from sys.sysuserswhere issqluser = 1

?

添加账户：

--MySQL(insertinto mysql.user 默认禁止,去掉my.initsql-modeSTRICT_TRANS_TABLES)

CREATE USER 'username'@'localhost'IDENTIFIEDBY 'password';?

INSERT INTOmysql.user(Host,Password)?

VALUES ('localhost','username',PASSWORD('password'));

?

--SqlServer

USE [master]

GO

CREATE LOGIN[username] WITH PASSWORD=N'password',CHECK_POLICY=OFF

GO

USE [YourDatabase]

GO

CREATE USER [username] FORLOGIN [username]

GO

?

更改密码：

--MySQL

mysqladmin -uusername -p123456?password 654321

set password=password("kk");

set passwordfor 'username'@'localhost'= password('123456');

update mysql.userset Password = PASSWORD('123456')WHERE user='username';

flush privileges;

?

--SqlServer

ALTER LOGIN[username] WITH PASSWORD=N'123456'

sp_password @new = '123456',@loginame= 'username'--,@old = 'old_password'

?

授权和回收权限：(SqlServer服务器角色和数据库角色相关操作)

--MySQL

GRANT SELECTon *.* to 'username'@'localhost'identified by 'password';

REVOKE allprivileges, grant option FROM'username'@'localhost';

?

--SqlServer

USE [master]

GRANT CONNECTSQL TO [username]

REVOKE CONNECTSQL TO [username]

EXEC master..sp_addsrvrolemember@loginame= N'username',@rolename = N'sysadmin'

EXEC master..sp_dropsrvrolemember@loginame= N'username' ,@rolename =N'sysadmin'?

GO

USE [YourDatabase]

GRANT INSERT,UPDATE,DELETE,SELECTON [dbo].[TestTab]TO [username]

REVOKE INSERT,SELECTON [dbo].[TestTab]TO [username]

EXEC sp_addrolememberN'db_owner',N'username'

EXEC sp_droprolememberN'db_owner',N'username'

GO

?

删除账户：

--MySQL

DROP user 'username'@'localhost';?

DELETE FROMmysql.userWHERE user='username';

?

--SqlServer

USE [master]

GO

DROP LOGIN[TUser]

sp_droplogin @loginame = 'username'

GO

USE [YourDatabase]

GO

DROP USER [TUser]

sp_dropuser @name_in_db = 'username'

GO

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!