1. 摘要

​ 对于很多先学习MySQL再学习SQLServer的初学者来说,很难受的一件事情莫过于在这俩者的语法中有一些细微的细节差异让人无法适应,例如SQLServer中并没有modify与change等关键字,又或者MySQL中的每条语句以;结尾,而SQLServer却是使用go这一关键字表示一段批处理语句的结束等等…

​ 本文正是在这种情况下出现,希望帮助同时学习这俩们SQL语言的人了解这俩种数据库的语法异别

​ 注:由于博主能力有限,无法做到对所有语法进行归纳总结,缺漏或错误之处还望指正,轻喷
最后,新的一年开始,祝福大家2022顺顺利利健健康康阖家幸福,都成为秃头大佬

2. 基础语法

1. 注释:在俩种数据库中注释的语法是一致的,示例:

# 单行注释
-- 单行注释(注意是杠杠空格)
/* 
多行注释
*/

2. 语句结尾:

   在MySQL中必须使用;分隔每条语句并作为语句结尾,当有多条语句一起执行时如果语句间没有使用分号分隔会报错

   示例:

use Student; -- 同时运行俩条语句时不用;分隔语句会报错
select * from SC;

而在SQLServer中分号是可选的,你可以选择加也可以选择不加,同时SQLServer中提供了go关键字,作为批处理语句的结尾,建议写SQLServer时使用go结束,这样子在下一段语句需要上一段语句执行完才能执行的情况下不会报批处理错误,示例:

use Student
-- 假设SC数据表在Student库下,此时如果不写go会报错
go
select * from SC

3. 存储引擎:

   在mysql中常用的有InnoDB | MyISAM | MEMORY | MERGE等存储引擎,其中使用最多的是InnoDB

   而在SQLServer中数据库的存储分为逻辑实现与物理实现,示意图:

其中主数据文件有且仅有一个,次数据文件可以有n个(0-n),日志文件可以有一到多个(至少有一个)

同时MySQL在创建表时可以指定表的存储引擎(默认InnoDB),而SQLServer只有一种存储引擎

4. MySQL可以使用单引号与双引号,而SQLServer只支持单引号

5. 都不严格区分大小写

6. 定位某张表

   mysql:库名.表名,示例:Student.SC

   SQLServer:库名.dbo.表名 或者 库名…表名

   其中dbo为数据库所有者(Database Owner),即有权限访问数据库的用户,是唯一的,拥有这个数据库的全部权限,并能给其他用户提供访问权限与功能

   示例:

Student.dbo.SC -- 或者:
Student..SC

6. SQLServer中的exec关键字:

-- 1. exec 存储过程名 参数1, 参数2....
-- 注意:执行存储过程时是不加括号的
-- 2. exec('sql语句'),表示执行该语句

7. SQLServer高级语法中提供了一系列的sp命令

8. SQLServer中的系统数据库:

   master:记录系统的所有系统级信息

   model:模板数据库

   msdb:存储计划信息,备份与恢复相关信息,SQLServer代理程序调度报警与作业调度等信息

   tempdb:临时数据库,他为所有的临时表,临时存储过程以及其他所有临时操作提供存储空间

   resource:隐藏的只读数据库,包含所有系统对象,但不含用户数据或用户原数据

9. mysql中的系统数据库:

   information_schema:提供了访问数据库元数据的方式。(元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。有时用于表述该信息的其他术语包括“数据词典”和“系统目录”) ,即保存着关于MySQL服务器所维护的所有其他数据库的信息,如数据库名，数据库的表，表栏的数据类型与访问权限等

   在INFORMATION_SCHEMA中，有几张只读表。它们实际上是视图，而不是基本表

   mysql:核心数据库(类似于SQLServer的master表),存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息,例如修改root用户密码就需要使用这个数据库

   performance_schema;

   sys;

10. 在负载压力相同时,MySQL消耗的内存和CPU更少

11. 在SQLServer中还提供了打印语句print,mysql中没有,示例:

-- print自带换行
print 'hello'

12. MySQL支持enum和set类型,SQLServer不支持

3. DDL&DML语句

3.1 建库

mysql:

-- 直接创建即可
CREATE DATABASE [IF NOT EXISTS] 数据库名 
[character set 字符集名];

SQLServer:

/* 除了数据库名字外还需要指定:
	主数据文件逻辑名(一般与数据库同名),主数据物理文件名称(.mdf)
	主数据文件初始大小(默认5MB),最大容量,增长速度
	日志文件逻辑名(一般命名为库名字_log),日志物理文件名(.ldf)
	日志文件初始大小(默认1MB),最大容量,增长速度
	是否加上次要数据文件(.ndf),是否在增加几个日志文件....
	并且逻辑文件命名需要与物理文件命名相对应
	主数据文件逻辑默认名为数据库名
*/
-- 示例:
CREATE DATABASE 数据库名
[ON  [PRIMARY]
(
    NAME = 'test', 
    FILENAME='D:\test.mdf', 
    [SIZE=10240KB/MB/GB/TB, ]
    [MAXSIZE = UNLIMITED/20480KB/MB/GB/TB,]
    [FILEGROWTH = 10%/1024KB/MB/GB/TB]
)]
[LOG ON 
(
    NAME='test_log',
    FILENAME='D:\test_log.ldf',
    [SIZE=1024KB/MB/GB/TB,]
    [MAXSIZE = 5120KB/MB/GB/TB/UNLIMITED,]
    [FILEGROWTH = 1024KB/MB/GB/TB/%]
)]
GO
/* 其中:
	ON表示后面定义的是数据文件
	ON PRIMARY表示定义主数据文件
	LOG ON表示定义日志文件
	NAME表示文件逻辑名
	FILENAME表示文件物理名
	SIZE表示初始大小,至少为模板数据库model的大小(主数据文件与日志文件分别是3M与1M)
	MAXSIZE表示文件最大大小,可以为UNLIMITED(无限制)
	FILEGROWTH表示文件大小增长速度,默认值10%,每次最少增加64kb
	默认单位都是MB
	注意:括号中最后一行无逗号,其他行都需要逗号
*/

3.2 查看库

3.2.1 打开指定库(一致)

俩者语法一致,都是use 库名

3.2.2 查看所有数据库

mysql:

-- 查看当前所有数据库:
show databases;
-- 查询某个数据库的字符集(查询数据库的创建语句即可实现):
show create database name;

SQLServer:

-- 查看当前所有数据库:
select name, database_id, create_date from sys.databases
go
-- SQLServer中的数据库信息存储在sys.databases中
-- 表示查询数据库名字,数据库id与创建时间,固定写法

-- 查看数据库信息
sp_helpdb 数据库名
go

3.3 修改库

注意:不管是哪种数据库,修改库的信息我们都是很少做的

mysql:

-- 对数据库重命名
RENAME DATABASE 数据库旧名 TO 数据库新名;
-- 修改数据库的字符集
ALTER DATABASE 数据库名 CHARACTER SET 字符集名;

SQLServer:

-- 对数据库重命名
sp_renamedb oldname, newname
go
-- 待补充

3.4 删除库(一致)

语法:

DROP DATABASE [IF EXISTS] 数据库名;

3.5 建表

3.5.1 最大容量

SQLServer每个表最多能有1024列,每行最多允许有8060个字节

MySQL一个表的总字段长度不能超过65535

3.5.2 建表语法(基本一致)

为什么说是基本一致呢,因为在SQLServer建表中,可以通过在表名前面加上db_name.dbo的形式来指定所属数据库与所有者,而在mysql中我暂时是没看到类似语法的

语法:

CRATE TABLE [IF NOT EXISTS] 表名(
	列名 列的类型[(长度) 约束],
	列名 列的类型[(长度)约束],
	列名 列的类型[(长度)约束],
	...
	列名 列的类型[(长度)约束]
);
-- 注：
-- 约束是可选项，不一定要填写
-- 最后一列的后面不需要添加逗号，其他每一列都需要添加逗号
-- SQLServer中不能通过这种IF NOT EXISTS的形式判断是否存在
-- SQLServer中的所有判断是否存在都只能通过IF EXISTS(查询语句)的方法实现
-- 检查表是否存在示例:
IF EXISTS(select count(*) 
          from dbo.sysobjects
         where name = 'table_name')
go
-- 检查字段是否存在示例:
IF EXISTS(select count(*) 
          from dbo.syscolumns
         where id = object_id('table_name')
          and name = 'column_name')
go
-- 或者:
if DB_ID('name') is not null -- 不存在
create TABLE....

3.6 查看表

mysql:

-- 查询数据库中所有表(SQLServer没有):
show tables [from 数据库名;
-- 查看表结构(SQLServer没有)
desc 表名; # 查看指定表下的数据结构
-- 使用database()函数查看当前处于哪个数据库(SQLServer没有)
select database();

SQLServer:

-- 查询当前数据库内所有表,固定写法
select * from sysobjects where xtype = 'U'
-- 查看表结构
sp_help 表名; -- 或者:
sp_columns 表名;
-- 也可以在前面加上exec

sp_help查询结果示例:

sp_columns查询结果示例:

3.7 修改表

3.7.1 修改表名

mysql:

ALTER TABLE name rename [to] newName;

SQLServer:

exec sys.sp_rename 

3.7.2 修改语句

SQLServer中没有change与modify语句,因此SQLServer使用俩个alter

3.8 删除表

基本一致

3.9 分离与附加数据库:

SQLServer:

-- 分离数据库
sp_detach_db 数据库名
go
-- 附加数据库
exec sp_attach_db [@dbname = ]'数据库名',
[@filename1 = ]'包含路径的文件物理名'[...16]
go
-- 数据库文件最多可以指定16个

4. 约束/索引

1. 递增语句MySQL是AUTO_INCREMENT,SQLServer是identify(10.1),从10开始一次加1
2. mysql不支持检查索引(check),SQLServer支持

5. 数据类型

1. MySQL中没有nchar,nvarchar,ntext等类型

2. SQLServer使用datetime类型作为获取默认值为当前时间的数据类型

   而MySQL使用timestamp时间错类型实现这个效果

3. MySQL支持无符号的整数类型,而SQLServer不支持

6. DQL语句

6.1

查询前几条记录:

SQLServer提供了top关键字

而MySQL使用limit关键字

示例:

select * from Student limit 100;
select top 100 * from Student;

6.2 全外连接

mysql 不支持 直接写full outer join 或者 full join 来表示全外连接但是可以用union联合查询 代替

而SQLServer支持全外连接

其余查询语法基本一致

7. 常见函数

7.1 调用函数方法

MySQL与SQLServer调用函数都是使用select调用函数,示例:

SELECT 函数名(参数列表);

7.2 获取当前时间

MySQL可以使用current_date()函数获取当前日期,或者使用CURRENT_TIME()函数只获取当前时间,或者使用CURRENT_TIMESTAMP()函数与now()函数获取当前的完整时间,示例:

SELECT CURRENT_DATE(); -- 2021-12-27
SELECT CURRENT_TIME(); -- 01:42:23
SELECT CURRENT_TIMESTAMP(); -- 2021-12-27 01:42:23
SELECT NOW(); -- 2021-12-27 01:42:23

而SQLServer可以使用getdate()方法获取当前时间日期,示例:

SELECT getdate();
-- 返回值:2021-12-27 01:40:40.907

7.3 判空函数

mysql:

-- 1. ifnull(exp1,exp2);
-- 表示当exp1为空时值为exp2,不为空时值为exp1

-- 2. isnull(exp1);
-- 当exp1为空时返回1,不为空时返回0

-- 3. 同时在MySQL中还提供了if函数(与if结构语句不同),示例:
if (exp1,exp2,exp3)
-- 表示当条件表达式exp1成立时返回exp2.否则返回exp3
-- 类似于java中的三目表达式,SQLServer中没有这个函数

SQLServer:

-- 1. isnull(exp1,exp2);
-- 表示当exp1为空时值为exp2,不为空时值为exp1
-- 没有ifnull()函数
-- 相对来说mysql的ifnull和isnull函数容易理解一点

7.4 字符串连接函数

mysql:

-- 使用concat()函数,示例:
SELECT CONCAT('我','在','学习mysql');
-- 不能使用+连接字符串!

SQLServer:

-- 1. 使用加号+连接字符串
select 'hello'+'SQL'
-- 2. 使用concat()函数,示例:
SELECT CONCAT('我','在','学习mysql');

8. 流程控制结构

8.1 IF结构

mysql需要在if 条件后以及else后添加then再写语句

并且mysql中的IF结构只能写在begin end块中

语法:

-- 语法
IF 条件1 THEN 语句1;
ELSEIF 条件2 THEN 语句2;
...
ELSE 语句n;
END IF; -- 表示IF结构结束了
-- 注释：只能用于BEGIN END块中
-- 语句中只有一条时可以省略begin end

而在SQLServer中不需要写then

语法:

IF (条件1)
BEGIN
	语句1
END
else
BEGIN
	语句2
END
go
-- 示例:
IF (EXISTS (select Sno from Student where Sno = '200001'))
	select Sno from Student where Sno = '200001'
ELSE
	print '没有改学生'
go

8.2 case结构(一致)

都需要使用then

不需要写Begin,只需要写END,分为俩种形式:

1. case后可以带一个值,在when中通过判断这个值的取值来达到选择效果(switch-case形式)
2. 也可以不带值,在when语句中写条件判断式(多重IF形式)

语法:

-- 1:
case 要判断的字段或表达式
    when 常量1 then 要显示的值1或语句1
    when 常量2 then 要显示的值2或语句2
    ...
    else 要显示的值n或语句n
    end
-- 2:
case 
    when 条件1 then 要显示的值1或语句1
    when 条件2 then 要显示的值2或语句2
    ...
    else 要显示的值n或语句n
end

8.3 循环结构

基本一致

但是在MySQL中在while循环后面需要加上do关键字

同时在end后面需要写上循环类型与循环表示,例如:WHILE [标签];

SQLServer不用

9. 视图

mysql视图中的from子句不允许存在子查询,而SQLServer支持

10. 变量,函数存储过程与触发器

10.1 自定义变量

mysql定义局部变量不需要@,定义用户变量才需要加@

而SQLServer都需要增加自定义变量@

10.2 存储过程

1. mysql存储过程中的in,out,inout,out放在变量前面(该参数初始值为null)

   SQLServerOUTPUT放在变量后面

2. mysql存储过程没有return,

3. 对于变量使用select赋值的区别(待补充)

4. mysql即使存储过程没有参数也要写()

5. MySQL不需要写AS,但是在视图中俩者都需要AS关键字

6. 调用存储过程,SQLServer使用exec,execute,mysql使用call

11. DCL语句

12. TCL语句

13. 数据库的备份与还原

13.1 SQLServer中的备份还原

在SQLServer中数据库备份分为三种:

1. 完整备份:对数据库整体的备份
2. 差异备份:对前一个完整备份后更改的部分的备份
3. 事务日志备份
4. 文件或文件组备份

• 注意:只有进行了完整备份之后才能进行差异备份与事务日志备份
• 总结如图:

对于系统数据库,必须完整备份

对于用户数据库,先进行完整备份,后面可以进行差异备份

对于事务日志文件的备份(差异备份?),相对于数据库备份,日志文件备份所需时间与空间更小,但恢复时间更久

注意:

进行备份时需要有备份设备来作为存储数据库,事务日志,或文件和文件组备份的存储介质

备份设备共有三种:磁盘,磁带,逻辑备份设备,备份设备在磁盘中是以文件的方式存储的

其中备份设备的标识使用物理设备名称与逻辑设备名称

其中物理名称主要供操作系统对备份设备进行引用和管理

逻辑名称用来简化物理设备的名称,永久的存储在系统表中,可以多次使用

备份时间:

1. 对系统数据库进行修改之后
2. 创建数据库或索引之后
3. 执行了大容量数据操作之后
4. 消除了事务日志

发生意外后的处理方案(按以下步骤):

1. 如果事务日志文件没有损坏,优先备份事务日志文件(尾部日志文件)
2. 恢复最近的完整备份文件
3. 恢复最近的差异备份文件(如果进行了差异备份)
4. 依次按备份的先后顺序恢复自差异备份以来的所有事务日志文件备份

语法:

-- 创建备份设备:
sp_addumpdevice 'device_type','logical_name','physical_name'
go
/*
	参数说明:
	device_type:设备类型,可以为'DISK|TAPE',其中DISK表示磁盘,TAPE表示磁带
	logical_name:逻辑名称
	physical_name:物理名称
*/

-- 删除备份设备
EXEC master.dbo.sp_dropdevice  [@logicalname =] 备份设备名 
go

-- 对数据库进行完整备份:
BACKUP DATABASE db_name
TO device_name [with INIT|NOINIT]
/*
	参数说明:
	INIT:新备份的数据覆盖当前备份设备的每一项内容
	NOINIT:新备份的数据追加到备份设备已有的内容后面
*/

--