SQL Server 和 MySQL 常用语法对比

目录

 1、增、删、改字段:

 （1）修改字段:

（2）删除字段:

（3) 增加字段

2、外键约束相关操作：

（1）删除约束：

（2) 查询约束：

（3）添加约束:

（4）添加唯一约束

（5）修改约束

3、触发器：

4、存储过程

---

 1、增、删、改字段:

 （1）修改字段:

sql_server ：注意sql server修改字段时，只能修改字段属性和是否为空，但是不能修改约束类型

alter table usermy_role_authority alter column id char(6) not null; #设置usermy_role_authority 的id字段类型为char(6) 且非空

        如果想要修改字段 约束，可以先根据约束名称删除约束 (如果不知道约束名，可以通过exec sp_helpconstraint 表名，来获取)，在使用alter table … add constraint进行引用

exec sp_helpconstraint horizontal; # 获取horizontal 表的所有约束的相关信息，这里的获得约束名是 id_test
alter table horizontal drop id_test; # 删除约束名id_test
alter table horizontal add constraint test_id check (id >=1) ; #添加check 约束检查 id>=1 条件是否被满足

        sqlServer如果想要修改字段名，推荐使用EXEC sp_RENAME 存储过程

EXEC sp_RENAME '表名.原字段名', '新字段名', 'COLUMN';

代码演示：

create table MOCK_DATA_COLD (
	id int ,
	first_name varchar(20),
	last_name varchar(20),
	email varchar(30),
	gender varchar(10),
	ip_address varchar(20),
	deteted tinyint 
);
EXEC sp_rename 'MOCK_DATA_COLD.deteted', 'deleted', 'COLUMN';

mysql: 修改字段

alter table 表名 change 旧字段名 新字段名 类型（长度）[comment  注释][约束]；

（2）删除字段:

sql server：与mysql 不同 drop 后面要加上cloumn(列)，才是删除字段，不然就是删除约束

#删除字段
alter table 表名 drop column 字段名
alter table usermy_role drop column id;
#删除约束
alter table usermy_role drop 约束名
alter table usermy_role drop liquor
#也可以这样删除约束
alter table 表名 drop constraint 外键名称

mysql:

alter table 表名 drop 字段名称

如果是要删除 primary key 如下表，没有约束名的话

create table emp(
    id int not null primary key AUTO_INCREMENT,
    name varchar(20)
);
#直接drop primary key 即可
alter table emp drop primary key ;

（3) 增加字段

sql server 和mysql一致

alter table horizontal add  str varchar(10)

2、外键约束相关操作：

（1）删除约束：

 sql server ：所有约束均可由下面的字段删除

alter table 表名 drop constraint 外键名称

 mysql： 和sql  server 不同的是删除外键和约束是不同的语句

#删除约束
alter table emp drop constraint 约束名称 ;
#删除外键
alter table 表名 drop foreign key 外键名称

如果是要删除 primary key 如下表：

create table emp(
    id int not null primary key AUTO_INCREMENT,
    name varchar(20)
);
#直接drop primary key 即可
alter table emp drop primary key ;

（2) 查询约束：

sql server:

EXEC sp_helpconstraint '表名';

mysql: 通过查询建表语句来查看表的相关约束

show create table 表名 ;

（3）添加约束:

sql server 和 mysql 一致

alter table 表名 add constraint 约束名 约束类型 ;
alter table horizontal add constraint test_id check (id >=1) ;

添加外键约束sqlserver 和mysql 也是一致的

alter table 表名 add constraint 外键名 foreign key (字段名) references 主表名(字段名);

（4）添加唯一约束

sql  server

alter table 表名 add constraint 约束名 unique nonclustered(字段名);
alter table horizontal add constraint id1 unique nonclustered(id);

 mysql

alter table 表名 add constraint 约束名 unique key(字段名);
alter table emp add constraint id1 unique key(name);

（5）修改约束

sqlserver:在修改字段中已经说过了，不懂的小伙伴，可以重新去看看

mysql:修改约束其实就是增加和删除约束，

3、触发器：

mysql: 在update后触发器可以通过old 和new直接获取更新前和更新后的字段，但是sql server不行

代码演示：

 create trigger student_update before update on sc for each row
      begin
          if old.Grade > new.Grade then
            insert into XXX value (XXX);
          end if ;
      end;

sql server：sql server 想要获取更新前的字段可以通过select 从 deleted表中搜索，更新后的对应字段也可以从inserted表中搜索，inserted 和deleted 由sql server自动创建。

create trigger coursmax on sc
for insert
as 
  print '看看我执行了吗'
  declare @maxi int
  select @maxi= count(sno) from sc where sno=(select sno from inserted);
  if(@maxi>2)
  begin
    print '已选课程超过2门'
    rollback ;
  end;

sqlserver的inserted和deleted表是否生成

触发器类型	inserted	deleted
update	是	是
insert	是	否
delete	否	是

4、存储过程

mysql：

基本语法

create procedure 存储过程名称（[参数列表]）
           begin 
                   ...SQL语句
            end；

create procedure test_mu(it varchar(20)) #it varchar(20)表示varchar(20)类型的参数,参数名为it
begin
    select * from sc where sno=it;
end;

call test_mu('97002'); #调用存储过程

sqlserver：

基本语法：

CREATE PROCEDURE PR_Sum 
AS
BEGIN
    sql语句
END

create procedure avgmy  @myname varchar(10) #@myname varchar(10) 表示varchar(10)类型的参数@myname
as 
 select sc.sno,AVG(Grade) as avg,count(cno) as ant from sc,student where sname=@myname  group  by sc.sno;
 
  exec avgmy @myname='李勇';#sqlserve 调用过程

5、自增列

sql server: sql server 中无法像mysql 那样直接使用 Auto_INCREMENT设置自增，需要通过identity 设置第一个参数列的初始值，第二个参数新数据插入时列的增加值。

create table transit (
	id tinyint not null identity(1,1),
	name varchar(20)
);

insert into transit  values ('上海'); #上海id为1
insert into transit values ('博湖'); #博湖id为2

mysql:

create table empq(
    id int not null primary key AUTO_INCREMENT,
    name varchar(20)
);

6、查看、修改表属性

查看属性表结构

sql_server

sp_columns 表名;

mysql

desc 表名;

修改表名

sqlserver

exec sp_rename 旧表名,新表名;

mysql

exec sp_rename 旧表名,新表名;

7、默认值设置方法

SQL Server设置默认值时可有可没有括号

create table emp10(name int not null default(12));

Mysql 设置默认值语sql server唯一的区别就是不能加括号

create table emp10(name int not null default 12);

8、幂等操作

关于什么是幂等操作，其特点就是多次执行某个操作和执行一次操作的影响相同，sql的幂等操作推荐使用 MERGE 语句，

基础语法：

MERGE INTO 目标表 AS target
USING 源表或查询 AS source
ON (匹配条件)
WHEN MATCHED THEN
    -- 当源数据和目标数据匹配时的操作
    UPDATE SET 列 = 值, ...
WHEN NOT MATCHED [BY TARGET] THEN
    -- 当源数据在目标表中不存在时的操作
    INSERT (列列表) VALUES (值列表)
WHEN NOT MATCHED BY SOURCE THEN
    -- 当目标数据在源数据中不存在时的操作
    DELETE;

代码示例：如果对应的记入存在就执行更新操作，如果不存在就执行插入

 

MERGE INTO ProductInventory AS target
USING (SELECT 1001 AS ProductID, 50 AS Quantity) AS source
ON (target.ProductID = source.ProductID)
WHEN MATCHED THEN
    UPDATE SET target.Quantity = source.Quantity
WHEN NOT MATCHED THEN
    INSERT (ProductID, Quantity)
    VALUES (source.ProductID, source.Quantity);

###在.netCore里面的使用
  conon.Execute(
       @"merge into MOCK_DATA_COLD as target using(select @email as email ) as source
        on (target.email=source.email)
        when not matched then
        insert (first_name, last_name, email, gender, ip_address)
        values (@first_name, @last_name, @email, @gender, @ip_address);",
       new {
           first_name = mockDataCold.FirstName,
           last_name = mockDataCold.LastName,
           email = mockDataCold.Email,
           gender = mockDataCold.Gender,
           ip_address = mockDataCold.IpAddress
       }
  );

MySql的幂等操作 upsert就简单很多了

insert into table_name (column1,column2,....)
values (value1,value2,....)
on duplicate key update
column1=value1,column2=value2,...;