MySQL8.0与SQL server 2017的技术区别（三）

BinaryPark

已于 2023-03-20 18:02:59 修改

阅读量114

点赞数

文章标签： mysql 数据库 sql sqlserver 正则表达式

于 2023-03-02 14:10:12 首次发布

本文链接：https://blog.csdn.net/is_integer/article/details/129297473

版权

MySQL8.0与SQL server 2017都是关系型数据库，两者在原理与技术方面有极高的相似度。本文主要简述笔者在学习过程中发现的两者有区别的部分，如果读者知道还有其他区别的点，欢迎进行评论补充，大家一起交流学习，共同成长！

1.严谨性不高的MySQL：

/* MySQL 要求当星号与其它列名同时出现时，星号必须放在其它列名之前。*/
SELECT *, ProductID, Productname, Englishname, Unitprice FROM Products;
SELECT ProductID,
/* 特别地，在MySQL中，通过设置表别名，星号又可以被放在其它列名之后。*/
Productname, Englishname, Unitprice, a.* FROM Products as a;

标准的 SQL 语句要求非聚合函数只有在GROUP BY 之后出现过的关键字才可以在输出列中出现。但 MySQL 在分组汇总时，可以把分组前每一组的第一行记录显示在输出列中，这在逻辑概念上是不正确的。因为当输出列不是完全依赖于 Group By 之后的关键字时，这些输出列的值是错误的、没有意义的或者在语义是错误的。

/* 根据订单明细表汇总计算每个商品的平均销售单价。通常，只有在GROUP BY中出现关键字才可以在SELECT 语句的列表中出现。此外，JOIN要放在GROUP BY之前。
下列语句在group by之后带多个具有相同分组含义的关键字，在rollup时会产生多个毫无意义的汇总行。*/
SELECT b.ProductID, b.Productname, b.Unitprice as CostPrice, Sum(Amount)/Sum(Quantity) as SalesPrice 
FROM OrderItems a JOIN Products b on a.ProductID=b.ProductID
Group by b.ProductID, b.Productname, b.Unitprice;
/* MySQL允许输出在GROUP BY关键字中没有出现的列，这些列的值取自分组前每一组的第一行记录。下列语句group by之后的ProductID是主键，而b.Productname, 
b.Unitprice完全依赖于ProductID值，其输出结果与上一条查询语句相同。 但这种写法在分组汇总的逻辑概念上是错误的，因此不建议使用这种书写方式。*/
SELECT b.ProductID, b.Productname, b.Unitprice as CostPrice, Sum(Amount)/Sum(Quantity) as SalesPrice 
FROM OrderItems a JOIN Products b on a.ProductID=b.ProductID
Group by b.ProductID;

2.在学习过程中，又遇到一些MySQL特有的知识点：

①在 MySQL 中，字符串相加与拼接必须通过 CONCAT()函数来实现，直接用加号将字符串相加是错误的。

②使用正则表达式（ regular expression ）可以检索或替换符合某个模式的文本内容，根据指定的匹配模式匹配文本中符合要求的特殊字符串。在 MySQL 中，使用 REGEXP 关键字指定正则表达式的字符匹配模式。

/* 中间匹配：查找商品名称中包含“哈”字的所有商品。*/
SELECT * FROM Products Where Productname regexp '哈';
SELECT * FROM Products Where Productname LIKE '%哈%'; /*等同于上一句 */
/* 前端匹配：用^符号查找商品名称中以“乐天”开头的所有商品。*/
SELECT * FROM Products Where Productname regexp '^乐天';
SELECT * FROM Products Where Productname like '乐天%'; /*等同于上一句 */
/* 后端匹配：用$符号查找商品名称中以“巧克力”结尾的所有商品。*/
SELECT * FROM Products Where Productname regexp '巧克力$';
SELECT * FROM Products Where Productname LIKE '%巧克力'; /*等同于上一句 */
/* 检索哪些员工编号与下面模式相匹配：第一个字符为字母，第二个字符为减号(-)，第三个字符为字母，之后的 3 个字符为数字，最后一个字符为 F 或 M 中的某个字母。*/
SELECT * FROM Employees Where EmployeeID regexp '^[a-z]-[a-z][0-9][0-9][0-9][FM]';
/* regexp默认也是不区分大小写，可以使用BINARY关键词来强制区分字母匹配的大小写。*/
SELECT * FROM Employees Where EmployeeID regexp BINARY '^[A-Z][-][A-Z][0-9][0-9][0-9][FM]';
/* 检索哪些产品的英文名称是没有连续出现2次及以上字母o的，注意NOT的位置。*/
SELECT * FROM Products Where NOT Englishname regexp 'o{2}';

③MySQL 提供了 IF()函数，可以根据表达式满足条件的情况，来确定 SQL 语句的输出结果，其基本语法是：IF(expr, v1, v2)。这里，逻辑表达式 expr 可以是文字、变量、运算符或函数的组合，IF()函数返回 TRUE、FALSE 或 NULL 值。当表达式 expr 为真时返回 v1 的值，否则返回 v2 的值。

/*根据系统日期来判断今天是否为周末，输出不同的提示符。*/
SELECT IF(Weekday(now())=5 or Weekday(now())=6, 'Today is holiday', 'Today is
work day')
/* 在 UPDATE 中使用 IF()函数可以一次搜索表中记录完成 4 种不同情况的价格更新。*/
UPDATE myProducts SET Unitprice=Unitprice*IF(Unitprice<=10, 1.25, IF(Unitprice<=30, 1.15,
IF(Unitprice<=80, 1.0, 0.95)));

④MySQL的多表连接中，如果ON子句连接的两个表的列名是相同的，而且连接条件是等值连接，那么可以将USING子句替代ON子句以简化语法，其格式为：USING(column_name)，其功能相当于ON t1.column_name=t2.column_name。使用USING子句有时候可以不指定表的别名。

⑤在MySQL中，在创建视图的时候，如果不用drop view if exists view_name;删除可能已存在的同名视图，也可以直接写CREATE or REPLACE VIEW myOrderView，这样也判断视图是否存在，如果存在，则替换它的定义。

3.ORDER BY 子句可以按列名、列的别名或表达式对检索结果进行排序，也可以为排序指定多个关键字。排序方向有升序（ ASC ， Ascending ）或降序（ DESC ， Descending ）两种方式，默认是升序。在SQL server中，汉字按拼音字母进行排序。而在 MySQL 中，汉字默认以笔画排序，如果需要按拼音排序，则借助 Convert()函数使用 gbk 字符集。

MySQL 例子：
/*按商品名称排序，默认情况下汉字按 UTF8 笔画升序排列。*/
SELECT * FROM Products Order By Productname;
/* 转换成 gbk 字符集后，商品名称中的汉字按拼音字母从 A 到 Z 升序排列。*/
SELECT * FROM Products Order By Convert(Productname using gbk) ;

/* 对汉字使用Between…And操作符，拼音排序需要Convert(Productname using gbk)。*/
SELECT ProductID, Productname, Englishname, Unitprice FROM Products 
WHERE Convert(Productname using gbk) Between '山' and '赵' 
Order By Convert(Productname using gbk);

4.在MySQL中，Convert( Productname using gbk )和Convert(Productname using utf8)叫做不同字符集之间转换数据。而数据类型之间的转换，也没有像SQL server那样自由和通用，如要转成字符型数据必须写成 Convert(Unitprice,char ) 或者Convert(Unitprice,nchar ) ,而且不能写字符型数据的长度。在SQL server中，其写法是Convert(nvarchar(10)，Unitprice)，另外，CONVERT 函数有一个 Style 选项，在转换日期型数据时，可以借此指定日期格式。例如， CONVERT( varchar(10), BirthDate,120)中的 Style 值为 120 ，那么按 yyyy-mm-dd 输出日期（中国格式）；Style 值为 103 时按 yyyy.mm.dd格式输出日期（英国格式）。

5.MySQL的数值随机函数为RAND()，可以产生0~1之间的一个随机数。ORDER BY RAND()可以将查询结果按照随机次序进行输出。有实验发现，当记录很多时，MySQL 目前版本中ORDER BY RAND()的运行效率较低。

/* 示例 1：直接用 Order By Rand()按随机次序输出查询结果。*/
SELECT * FROM Products Order By Rand();
/* 示例 2：先按单价排序，相同单价的商品再随机排序，这在语法上是允许的。*/
SELECT * FROM Products Order By Unitprice, Rand();

而在 SQL Server 中，随机函数主要有两种： NewID() 和 Rand() 。 NewID() 的返回值是
Uniqueidentifier 类型数据，而 Rand() 返回一个 0~1 之间的数值。在执行查询语句时，NewID() 在扫描每条记录都生成一个值，而由于生成的值是随机的，因此按这个值进行排序的结果也是无序的。 Rand() 每个查询语句只调用一次，所有的行会得到由 Rand() 函数产生的同一个随机值。因此，在 SQL Server 中使用 Rand() 函数其实是无法实现随机排序的。由于 NewID() 每一行都会单独计算 NewID() 的值，当记录比较多时（大于 30万条）， ORDER BY NewID() 语句会占用较多的系统资源，运行速度比较慢。

6.在MySQL中，使用 LIMIT 子句可以只返回一个查询结果中的一部分行数据，即从中间位置提取一个查询结果集中的某几行记录。

/* 示例 1：取商品表中单价最低的前 10 个商品。LIMIT 通常与 Order By 联合使用。*/
SELECT ProductID, Productname, Unitprice FROM Products 
Order By Unitprice 
LIMIT 10;
/* 示例 2：从商品表中第 20 个之后取 10 个商品。LIMIT 之前没有 Order By 子句。*/
SELECT ProductID, Productname, Unitprice FROM Products 
LIMIT 20, 10;
SELECT ProductID, Productname, Unitprice FROM Products 
LIMIT 10 OFFSET 20;   --OFFSET 20 表示查询时需要跳过前面的20行

由于 LIMIT 子句中不允许用表达式或子查询，只能是一个变量或常量，因此 LIMIT 语句的使用范围和功能受较大的限制。MySQL 的预处理语句可以帮助解决实现这一功能，具体方法步骤如下：1）使用 PREPARE 语句预备一条 SQL 语句，在这条语句中用问号（?）表示参数；2）对 PREPARE 语句中的参数进行赋值；3）使用 EXECUTE…USING 执行预处理语句；4）使用 DEALLOCATE 或 DROP 解除分配预处理语句。

/* 使用 Prepare 预制一条查询语句，在 LIMIT 右边填加一个空格，之后指定一个参数，用问号?表示。*/
PREPARE stmt FROM    --stmt是用户自定义的PREPARE语句的名称
'SELECT * FROM Products Order By Unitprice LIMIT ?';
/* 使用 Count(*)函数求得当前商品表的记录总数，用全局变量@n 记录 30%的商品个数。*/
SET @n=0.3*(SELECT Count(*) FROM Products);
/* 使用 EXECUTE 执行这条预制语句，同时将商品个数@n 值传递给预制语句中的参数。*/
EXECUTE stmt USING @n;     --EXECUTE不能简写成EXEC
/* 使用 DEALLOCATE 或 DROP 语句解除分配这条预制语句。*/
DEALLOCATE PREPARE stmt;

在SQL server 2017中，增加了OFFSET...FETCH...语句，可以提取表中间一部分行。

select * from products
order by productid    --这里必须要有order by字句，不能缺
offset 20 rows   --表示跳过前面的20行记录
fetch next 10 rows only   --取第20行之后的10行

7. 在MySQL中，条件处理 IF... THEN... ELSEIF …THEN... ELSE ... END IF 是在程序代码中做出判断的一种方法，在执行 MySQL 语句时强加条件。如果条件满足（布尔表达式返回 TRUE时），则执行和 IF 关键字相匹配的THEN语句。可选的ELSEIF和ELSE 关键字引入备用的 MySQL 语句，当不满足 IF 条件时（布尔表达式返回FALSE），就执行这些语句。

drop procedure if exists sp1;
delimiter $$
create procedure sp1()
begin
    declare $a,$b int;
    set $a=1,$b=2;
    IF $a<$b THEN select  1111;
    ELSEIF $a>$b THEN select 2222;   --ELSEIF...THEN...是可选语句，可以根据问题确定是否需要
    ELSE select 3333;    --ELSE是可选语句，可以根据问题确定是否需要
    END IF;
end$$
delimiter ;
call sp1();

而在SQL Server中，条件处理结构的语法是 IF... ELSE IF... ELSE ...如果条件满足（布尔表达式返回 TRUE 时），则在 IF 关键字及其条件之后执行 T-SQL 语句。可选的ELSE IF和 ELSE 关键字引入备用的 T-SQL 语句，当不满足 IF 条件时（布尔表达式返回 FALSE ），就执行这些语句。

declare  @a int , @b int;
select  @a=2, @b=2
IF (@a<@b)  select  1111;
ELSE IF @a>@b  begin select 2222 end   --ELSE IF... 是可选语句，可以根据问题确定是否需要
ELSE select 3333;   --ELSE... 是可选语句，可以根据问题确定是否需要