Sqlserver sql写法与mysql 不同之处收集

系统管理员 2024-04-17 06:23 60阅读 0赞

# Sqlserver sql写法与mysql 不同之处收集 #

# #

## 一. 常用sql语句及常见问题 ##

### 对关键字，保留字的写法不同 ###

sqlserver:  select \* from tbName where \[key\]='test';  关键字一般用\[ \]括起来即可！

mysql: select \* from tbName where \`before\`='test';  关键字一般用 \`\`括起来，即~这个键的下面那个字符

### 当前时间的写法,取n条记录 ###

sqlserver:   select top 10 \* from tbName where CreateTime<getdate();

mysql: select \* from tbName where CreateTime<Now() limit 10;

### 从数据库定位到表 ###

Sqlserver写法：库名.dbo.表名 ；或者：库名..表名   
 （注：中间使用两个点）  
select password from Info.dbo.users where userName='boss'  
或者  
select password from Info..users where userName='boss'  
mysql写法：库名.表名  
select password from Info.users where userName='boss'

###    判断是否存在某个数据库，若存在，则删除 ###

Sqlserver写法：  
IF DB\_ID('users') IS NOT NULL  
DROP DATABASE users  
Mysql写法：  
Drop DATABASE if exists users

拓展：若sqlserver数据库正在使用中，删除之前，先要把数据库变成“单一用户”，再删除  
ALTER DATABASE users SET SINGLE\_USER with ROLLBACK IMMEDIATE IF DB\_ID('users') IS NOT NULL DROP DATABASE users

另附：判断某数据库中是否存在某张表，若存在，则删除  
Sqlserver写法：  
if exists(select \* from sysobjects where name ='Users\_test')  
drop table Users\_test  
Mysql写法：  
DROP TABLE IF EXISTS Users\_test

###    主键存在，则更新，不存在，则插入 ###

Mysql写法:      
INSERT into users (userID,userName,password) VALUES (1,’jmj’,’123’) ON DUPLICATE KEY UPDATE  userName='jmj', password=123

Sqlserver没有mysql这样的关键字，只能组合sql语句来实现操作:  
if not exists (select userID from users where userID= 1)insert into users (userID,userName,password) values(1,’jmj’,’123’) else update users set userName= ’jmj’, password=’123’ where userID = 1

###    符号的使用 ###

mysql对参数可以使用单引号，也可以使用双引号，对字段名和表明可以使用反引号。  
sqlserver只能使用单引号，且不能使用反引号。  
Mysql写法：  
Select \`password\` from Users where userName='boss' or username=”jmj”;  
Sqlserver写法：  
Select password from Users where userName='boss' or username=’jmj’;

###    查询指定库中的所有表 ###

mysql 语句  
\-- 查看系统内所有数据库  
show databases；  
或  
select \* from information\_schema.SCHEMATA;  
\-- 查询数据库内所有表  
show tables;  
或  
select \* from information\_schema.\`TABLES\` where TABLE\_SCHEMA='dbname'  
\-- 显示表结构  
desc 表名;

sql server语句  
\-- 查看系统内所有数据库  
SELECT name, database\_id, create\_date  FROM sys.databases  ;  
\-- 查询数据库内所有表  
select \* from sysobjects where xtype= 'U'  ;  
\-- 显示表结构  
sp\_help/sp\_columns 表名;

###    字符串的处理 ###

截取字符串  
SQLServer只能使用SUBSTRING关键词来截取字符串。  
MySQL可以使用SUBSTRING和SUBSTR截取字符串  
取得字符串的长度

SQLServer只能使用Len关键词取得字符串的长度。  
MySQL可以使用Length取得字符串的长度。

### ISNULL()函数 ###

SqlServer:  
select \* from test where isnull(no,0)=0;  
MySQL  
MySQL 可以使用 ISNULL() 函数。不过它的工作方式与微软的 ISNULL() 函数有点不同。  
在 MySQL 中，我们可以使用 IFNULL() 函数，就像这样：  
mysql> select \* from test where ifnull(no,0)=0;

### 注释符区别 ###

SqlServer的注释符为--和/\*\*/  
MySql的注释符为/\*\*/和\#

### 语法及兼容方面的不同点 ###

mysql支持enum,和set类型，sql server不支持  
mysql不支持nchar,nvarchar,ntext类型  
mysql的递增语句是AUTO\_INCREMENT，而mssql是identity(1,1)  
msms默认到处表创建语句的默认值表示是((0)),而在mysql里面是不允许带两括号的  
mysql需要为表指定存储类型  
mssql识别符是\[\],\[type\]表示他区别于关键字，但是mysql却是 \`，也就是按键1左边的那个符号  
mssql支持getdate()方法获取当前时间日期，但是mysql里面可以分日期类型和时间类型，获取当前日期是cur\_date()，当前完整时间是 now()函数  
mssql不支持replace into 语句，但是在最新的sql20008里面，也支持merge语法  
mysql支持insert into table1 set t1 = ‘’, t2 = ‘’ ,但是mssql不支持这样写  
mysql支持insert into tabl1 values (1,1), (1,1), (1,1), (1,1), (1,1), (1,1), (1,1)  
mssql不支持limit语句，是非常遗憾的，只能用top 取代limt 0,N，row\_number() over()函数取代limit N,M  
mysql在创建表时要为每个表指定一个存储引擎类型，而mssql只支持一种存储引擎  
mysql不支持默认值为当前时间的datetime类型（mssql很容易做到），在mysql里面是用timestamp类型  
mysql支持无符号型的整数，那么比不支持无符号型的mssql就能多出一倍的最大数存储  
mysql不支持在mssql里面使用非常方便的varchar(max)类型，这个类型在mssql里面既可做一般数据存储，也可以做blob数据存储  
mysql创建非聚集索引只需要在创建表的时候指定为key就行，比如：KEY displayorder (fid,displayorder) 在mssql里面必须要：create unique nonclustered index index\_uc\_protectedmembers\_username\_appid on dbo.uc\_protectedmembers(username asc,appid asc)  
mysql text字段类型不允许有默认值  
mysql的一个表的总共字段长度不超过65XXX。  
一个很表面的区别是mysql的安装特简单，且文件大小才110M（非安装版），相比微软这个庞然大物，安装进度来说简直就是.....  
mysql的管理工具有几个比较好的，mysql\_front,和官方那个套件，不过都没有SSMS的使用方便，这是mysql很大的一个缺点。  
mysql的存储过程只是出现在最新的版本中，稳定性和性能可能不如mssql。  
同样的负载压力，mysql要消耗更少的CPU和内存，mssql的确是很耗资源。  
mysql支持date,time,year类型，mssql到2008才支持date和time。

## 二. 数据定义部分 ##

**数据库操作基本命令**  
Mysql:  
create database name; 创建数据库   
use databasename; 选择数据库   
drop database name 直接删除数据库，不提醒 –  
CREATE TABLE --创建一个数据库表  
2.1    PRIMARY KEY 约束（主键）区别解析：  
2.1.1 创建primary key  
 Mysql:      
CREATE TABLE Persons  
(  
Id\_P  int  NOT NULL,  
LastName varchar(255) NOT NULL,  
FirstName varchar(255),  
Address varchar(255),  
City varchar(255),  
PRIMARY KEY (Id\_P)               //声明主健写在最后  
)

SqlServer：  
CREATE TABLE Persons  
(  
Id\_P int NOT NULL PRIMARY KEY,     //声明主健 紧跟列后  
LastName varchar(255) NOT NULL,  
FirstName varchar(255),  
Address varchar(255),  
City varchar(255)  
)  
但是如果表存在，之后给表加主健时：  
Mysql 和SqlServer  
ALTER TABLE Persons ADD PRIMARY KEY (Id\_P)  
  
2.1.2撤销 PRIMARY KEY 约束  
MySQL:  
ALTER TABLE Persons DROP PRIMARY KEY  
SQL Server  
ALTER TABLE Persons DROP CONSTRAINT pk\_PersonID  
  
2.1.3 创建外健约束  
MySQL:  
CREATE TABLE Orders  
(  
O\_Id  int NOT NULL,  
OrderNo int NOT NULL,  
Id\_P int,  
PRIMARY KEY (O\_Id),  
FOREIGN  KEY (Id\_P)  REFERENCES  Persons(Id\_P)    //写在最后  
)

SQL Server   
CREATE TABLE Orders  
(  
O\_Id  int  NOT NULL  PRIMARY KEY,  
OrderNo  int NOT NULL,  
Id\_P  int  FOREIGN KEY REFERENCES Persons(Id\_P)    //顺序不同  
)  
如果在 "Orders" 表已存在的情况下为 "Id\_P" 列创建 FOREIGN KEY 约束，请使用下面的 SQL：  
MySQL / SQL Server   
ALTER  TABLE  Orders ADD  FOREIGN KEY  (Id\_P) REFERENCES  Persons(Id\_P)  
  
2.1.4 撤销外健约束  
MySQL:  
ALTER TABLE Orders DROP  FOREIGN KEY f k\_PerOrders  
SQL Server   
ALTER TABLE Orders DROP CONSTRAINT  fk\_PerOrders

**2.2     UNIQUE 约束（唯一的，独一无二的）区别解析**  
UNIQUE 约束唯一标识数据库表中的每条记录。  
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。  
PRIMARY KEY 拥有自动定义的 UNIQUE 约束。  
请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

2.2.1 创建UNIQUE约束  
MySQL:  
CREATE TABLE Persons  
(  
Id\_P int NOT NULL,  
LastName varchar(255) NOT NULL,  
FirstName varchar(255),  
Address varchar(255),  
City varchar(255),  
UNIQUE (Id\_P)                 //写在最后  
)  
SQL Server  
CREATE TABLE Persons  
(  
Id\_P int NOT NULL UNIQUE,        //紧跟列后  
LastName varchar(255) NOT NULL,  
FirstName varchar(255),  
Address varchar(255),  
City varchar(255)  
)  
  
2.2.2 撤销 UNIQUE 约束  
MySQL:  
ALTER TABLE Persons DROP INDEX  uc\_PersonID  
SQL Server  
ALTER TABLE Persons DROP CONSTRAINT uc\_PersonID  
  
**2.3    CHECK 约束**  
CHECK 约束用于限制列中的值的范围。  
如果对单个列定义 CHECK 约束，那么该列只允许特定的值。  
如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。  
2.3.1 创建 CHECK约束  
下面的 SQL 在 "Persons" 表创建时为 "Id\_P" 列创建 CHECK 约束。CHECK 约束规定 "Id\_P" 列必须只包含大于 0 的整数。  
My SQL:  
CREATE TABLE Persons  
(  
Id\_P int NOT NULL,  
LastName varchar(255) NOT NULL,  
FirstName varchar(255),  
Address varchar(255),  
City varchar(255),  
CHECK (Id\_P>0)                       //写在最后  
)

SQL Server   
CREATE TABLE Persons  
(  
Id\_P int NOT NULL CHECK (Id\_P>0),    //紧跟列后  
LastName varchar(255) NOT NULL,  
FirstName varchar(255),  
Address varchar(255),  
City varchar(255)  
)  
如果需要命名 CHECK 约束，以及为多个列定义 CHECK 约束，请使用下面的 SQL 语法：  
MySQL / SQL Server:  
CREATE TABLE Persons  
(  
Id\_P int NOT NULL,  
LastName varchar(255) NOT NULL,  
FirstName varchar(255),  
Address varchar(255),  
City varchar(255),  
CONSTRAINT chk\_Person CHECK (Id\_P>0 AND City='Sandnes') //多个条件  
)  
如果在表已存在的情况下为 "Id\_P" 列创建 CHECK 约束，请使用下面的 SQL：  
MySQL / SQL Server:  
ALTER TABLE Persons ADD CHECK (Id\_P>0)  
2.3.2 撤销 CHECK约束  
Sqlserver:  
ALTER  TABLE  Persons  DROP CONSTRAINT chk\_Person  
Mysql我没有找到怎么删除。  
  
**2.4    DEFAULT 约束（系统默认值）**  
DEFAULT 约束用于向列中插入默认值。  
如果没有规定其他的值，那么会将默认值添加到所有的新纪录。  
2.4.1 创建DEFAULT约束  
下面的 SQL 在 "Persons" 表创建时为 "City" 列创建 DEFAULT 约束：  
My SQL / SQL Server:  
CREATE TABLE Persons  
(  
Id\_P int NOT NULL,  
LastName varchar(255) NOT NULL,  
FirstName varchar(255),  
Address varchar(255),  
City varchar(255) DEFAULT 'Sandnes'     //紧跟列后，默认值字符串Sandnes  
)

通过使用类似 GETDATE() 这样的函数，DEFAULT 约束也可以用于插入系统值：  
CREATE TABLE Orders  
(  
Id\_O int NOT NULL,  
OrderNo int NOT NULL,  
Id\_P int,  
OrderDate date DEFAULT GETDATE()   //紧跟列后，函数  
)

如果在表已存在的情况下为 "City" 列创建 DEFAULT 约束，请使用下面的 SQL：

MySQL:  
ALTER  TABLE  Persons  ALTER  City  SET  DEFAULT  'SANDNES'

SQL Server:  
ALTER  TABLE  Persons  ALTER  COLUMN  City  SET  DEFAULT  'SANDNES'  
2.4 .2 撤消DEFAULT约束  
MySQL:  
ALTER  TABLE  Persons  ALTER  City  DROP  DEFAULT

SQL Server:  
ALTER  TABLE  Persons  ALTER  COLUMN  City  DROP  DEFAULT  
  
**2.5    索引区别**  
CREATE INDEX 语句  
CREATE INDEX 语句用于在表中创建索引。  
在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据。  
在表上创建一个简单的索引。允许使用重复的值：  
CREATE INDEX index\_name ON table\_name (column\_name) //"column\_name" 规定需要索引的列。  
在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值。  
CREATE UNIQUE INDEX index\_name ON table\_name (column\_name)  
Mysql和SqlServer的创建索引都是一致的，但是在删除索引方面却有区别:  
SqlServer: DROP INDEX table\_name.index\_name  
Mysql: ALTER TABLE table\_name DROP INDEX index\_name

**2.6    主键自动增加的区别**  
mySql的主键自动增加是用auto\_increment字段，sqlServer的自动增加则是identity字段.  
Auto-increment 会在新纪录插入表中时生成一个唯一的数字。  
我们通常希望在每次插入新纪录时，自动地创建主键字段的值。  
我们可以在表中创建一个 auto-increment 字段。  
用于 MySQL 的语法  
下列 SQL 语句把 "Persons" 表中的 "P\_Id" 列定义为 auto-increment 主键：  
CREATE TABLE Persons  
(  
P\_Id int NOT NULL AUTO\_INCREMENT,  
LastName varchar(255) NOT NULL,  
FirstName varchar(255),  
Address varchar(255),  
City varchar(255),  
PRIMARY KEY (P\_Id)  
)  
MySQL 使用 AUTO\_INCREMENT 关键字来执行 auto-increment 任务。  
默认地，AUTO\_INCREMENT 的开始值是 1，每条新纪录递增 1。  
要让 AUTO\_INCREMENT 序列以其他的值起始，请使用下列 SQL 语法：  
ALTER TABLE Persons AUTO\_INCREMENT=100

用于 SQL Server 的语法  
下列 SQL 语句把 "Persons" 表中的 "P\_Id" 列定义为 auto-increment 主键：  
CREATE TABLE Persons  
(  
P\_Id int PRIMARY KEY IDENTITY,或则是写成P\_id int primary key identity (1,1),  
LastName varchar(255) NOT NULL,  
FirstName varchar(255),  
Address varchar(255),  
City varchar(255)  
)  
MS SQL 使用 IDENTITY 关键字来执行 auto-increment 任务。  
默认地，IDENTITY 的开始值是 1，每条新纪录递增 1。  
要规定 "P\_Id" 列以 20 起始且递增 10，请把 identity 改为 IDENTITY(20,10)  
  
**2.7    MySQL支持enum,和set类型，SQL Server不支持**  
2.7.1    枚举enum  
ENUM是一个字符串对象，其值来自表创建时在列规定中显式枚举的一列值.  
枚举最多可以有65,535个元素。  
枚举的简单用法举例:  
mysql> create table meijut (f1 enum('1','2','3','4','5','6'));  
mysql> desc meijut;  
\+-------+-------------------------------+------+-----+---------+-------+  
| Field | Type                          | Null | Key | Default | Extra |  
\+-------+-------------------------------+------+-----+---------+-------+  
| f1    | enum('1','2','3','4','5','6') | YES  |     | NULL    |       |  
\+-------+-------------------------------+------+-----+---------+-------+  
mysql> insert into meijut values(8);  
mysql> select \* from meijut  
    -> ;  
\+------+  
| f1   |  
\+------+  
|      |  
\+------+  
这个情况说明如果你将一个非法值插入ENUM(也就是说，允许的值列之外的字符串)，将插入空字符串以作为特殊错误值。该字符串与“普通”空字符串不同，该字符串有数值值0。  
mysql> insert into meijut values('3');  
mysql> insert into meijut values(3);  
mysql> insert into meijut values('4');  
mysql> insert into meijut values('5');  
mysql> select \* from meijut;  
\+------+  
| f1   |  
\+------+  
|      |  
| 3    |  
| 3    |  
| 4    |  
| 5    |  
\+------+    
改表

mysql> alter table meiju modify f1 enum("a","b","c","d","e","f");  
可以自动将记录当序号匹配成新的字段值  
mysql> select \* from meijut;  
\+------+  
| f1   |  
\+------+  
|      |  
| c    |  
| c    |  
| d    |  
| e    |  
\+------+    
2.7.2    集合set  
mysql> create table jihe(f1 set('f','m'));  
mysql> insert into jihe values('f');  
可以按照序号输入 注意序号为 1 2 4 8 16 32 ....  
mysql> insert into jihe values('3');  
mysql> select \* from jihe;  
\+------+  
| f1   |  
\+------+  
| f    |  
| f,m  |  
\+------+   
其他字母不能插入  
mysql> insert into jihe values("q");  
ERROR 1265 (01000): Data truncated for column 'f1' at row 1  
插入空  
mysql> insert into jihe values("0");  
Query OK, 1 row affected (0.11 sec)  
超出序号之和不能插入  
mysql> insert into jihe values("4");  
ERROR 1265 (01000): Data truncated for column 'f1' at row 1  
集合 和 枚举的区别  
集合可以有64个值 枚举有65535个  
集合的序号是 1 2 4 8 16 枚举是 1 2 3 4 5 6  
集合一个字段值可以有好几个值  
\+-------+  
| f1    |  
\+-------+  
| f,m   |  
\+-------+

**2.8    MySQL不支持nchar,nvarchar,ntext类型**  
   DROP TABLE –删除一个数据库表  
Mysql判断一个数据库表是否存在并删除的语句是：  
drop table if exists jihe;  
SqlServer判断一个数据库表是否存在并删除的语句是：  
    if exists (select \* from sysobjects where name='Sheet1$' and xtype='U')  
   drop table Sheet1$  
  其中jihe和Sheet1$指的均是数据库表名  
   显示库表  
 Mysql:   
        Show tables;//显示一个库中的所有表  
        Desc table;/显示一个表的表结构  
    mysql> desc meijut;  
\+-------+-------------------------------+------+-----+---------+-------+  
| Field | Type                          | Null | Key | Default | Extra |  
\+-------+-------------------------------+------+-----+---------+-------+  
| f1    | enum('a','b','c','d','e','f') | YES  |     | NULL    |       |  
| f2    | int(11)                       | YES  |     | 0       |       |  
| f3    | text                          | YES  |     | NULL    |       |  
\+-------+-------------------------------+------+-----+---------+-------+   
        Show create table tablename;//显示一个表的详细创建信息  
mysql> show create table meijut;  
\+--------+-------------------------------------------  
\-----------------------------------------------------  
\---+  
| Table  | Create Table

|  
\+--------+-------------------------------------------  
\-----------------------------------------------------  
\---+  
| meijut | CREATE TABLE \`meijut\` (  
  \`f1\` enum('a','b','c','d','e','f') default NULL,  
  \`f2\` int(11) default '0',  
  \`f3\` text  
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |  
   alter 修改库表

重命名表: -  
mysql > alter table t1 rename t2;   
添加一列  
mysql> alter table meijut add column f2 int default 0 ;  
 修改一列  
mysql> alter table meijut modify f2 text;

持续更新中

\--- end ---