1. Physical Data Model

图片 1

 

2.通过SSMS修改数据

题外话

在最新的 MySQL 8.0 版本中,有以下几个区别,追加在这里。

  1. 服务操作

# 使用 mysqld 进行服务操作
systemctl start mysqld   #启动mysql服务
systemctl stop mysqld    #停止mysql服务
systemctl restart mysqld #重启mysql服务
  1. 登陆

# 获取首次登陆密码
sudo grep 'temporary password' /var/log/mysqld.log
# 使用 root 用户登陆数据库
mysql -uroot -p
# 回车后,输入第一条语句中获得的密码,我的生成了两个密码,我使用第二个登陆成功的,不知道原因是什么…

# 登陆成功后重新设置密码
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';

注意这里非强密码不能通过,即必须同时包含至少一个大写、小写字母、特殊字符、数字,并且不少于8个字符

设置好密码,可以起飞啦。


 

1.前言

增删改查都是对数据的操作,其中“改”对应的SQL语句便是“update”,也就是“更新”的意思。
本篇主要介绍数据的修改,分别使用SSMS/SQL对数据进行修改操作。

参考文档

MySQL 5.6
官方文档
MySQL 8.0
官方文档
MySQL 卸载
MariaDB 安装

如有错误,还请在评论中告知,帮助我修改,感谢!
原文链接: –
转载请注明出处

2. SQL Statements

drop database MessageReceiver
go

/*==============================================================*/
/* Database: MessageReceiver                                    */
/*==============================================================*/
create database MessageReceiver
go

use MessageReceiver
go

/*==============================================================*/
/* Table: ReceiveMessage                                        */
/*==============================================================*/
create table ReceiveMessage (
   ID                   int                  identity,
   MessageType          nvarchar(200)        not null,
   Operation            smallint             not null,
   Content              nvarchar(max)        not null,
   IsCompleted          bit                  not null,
   TraceID              uniqueidentifier     not null default newid(),
   constraint PK_RECEIVEMESSAGE primary key (ID)
)
go

/*==============================================================*/
/* Table: ReceiveMessageLog                                     */
/*==============================================================*/
create table ReceiveMessageLog (
   ID                   int                  identity,
   ReceiveMessageID     int                  not null,
   LogTime              datetime             not null default getdate(),
   Remark               nvarchar(100)        null,
   constraint PK_RECEIVEMESSAGELOG primary key (ID)
)
go

/*==============================================================*/
/* Index: ix_ReceiveMessageLog_MsgID                            */
/*==============================================================*/
create index ix_ReceiveMessageLog_MsgID on ReceiveMessageLog (
ReceiveMessageID ASC
)
go

/*==============================================================*/
/* Table: SendMessage                                           */
/*==============================================================*/
create table SendMessage (
   ID                   int                  identity,
   MessageType          nvarchar(200)        not null,
   Operation            smallint             not null,
   Content              nvarchar(max)        not null,
   IsArrived            bit                  not null,
   TraceID              uniqueidentifier     not null default newid(),
   constraint PK_SENDMESSAGE primary key (ID)
)
go

/*==============================================================*/
/* Table: SendMessageLog                                        */
/*==============================================================*/
create table SendMessageLog (
   ID                   int                  identity,
   SendMessageID        int                  not null,
   LogTime              datetime             not null default getdate(),
   Remark               nvarchar(100)        null,
   constraint PK_SENDMESSAGELOG primary key (ID)
)
go

/*==============================================================*/
/* Index: ix_SendMessageLog_MsgID                               */
/*==============================================================*/
create index ix_SendMessageLog_MsgID on SendMessageLog (
SendMessageID ASC
)
go

alter table ReceiveMessageLog
   add constraint fk_ReceiveMessage_ReceiveMessageID foreign key (ReceiveMessageID)
      references ReceiveMessage (ID)
go

alter table SendMessageLog
   add constraint fk_SendMessageLog_SendMessageID foreign key (SendMessageID)
      references SendMessage (ID)
go


create procedure up_SendMessageToRemoteServer
as
declare @SendMessageID int,@MessageType nvarchar(200),@Operation smallint,@Content nvarchar(max),@TraceID uniqueidentifier
while(1=1)
begin
    set @SendMessageID=null
    select top(1)    @SendMessageID=ID,
                    @MessageType=MessageType,
                    @Operation=Operation,
                    @Content=Content,
                    @TraceID=TraceID
        from SendMessage a
        where a.IsArrived = 0
        order by a.ID
    if (@SendMessageID is null) break

    exec Server001.MessageReceiver.dbo.up_cReceiveMessageForRemoteServer 
            @MessageType =@MessageType, 
            @Operation = @Operation,
            @Content = @Content,
            @TraceID=@TraceID

    if (@@error <> 0) break
    exec up_cSendMessageLog 
        @SendMessageID = @SendMessageID,
        @Remark = N'发送',
        @IsArrived = 1
end
go


create procedure up_cReceiveMessage
(
    @MessageType nvarchar(200),
    @Operation smallint,
    @Content nvarchar(max)
)
as
begin try
    begin transaction
        declare @ReceiveMessageID int  

        insert into ReceiveMessage ( MessageType, Operation, Content,IsCompleted)
            values(@MessageType,@Operation,@Content,0)

        set @ReceiveMessageID=scope_identity()

        insert into ReceiveMessageLog ( ReceiveMessageID, Remark )
            values(@ReceiveMessageID,N'接收.')

    commit transaction  
end try
begin catch 
    declare @error nvarchar(2048)=error_message()
    ;throw 50001 ,@error,1
    if (@@trancount >0) rollback transaction
end catch
go


create procedure up_cReceiveMessageForRemoteServer
(
    @MessageType nvarchar(200),
    @Operation smallint,
    @Content nvarchar(max),
    @TraceID uniqueidentifier
)
as
begin try
    begin transaction
        declare @ReceiveMessageID int  

        insert into ReceiveMessage ( MessageType, Operation, Content,IsCompleted,TraceID)
            values(@MessageType,@Operation,@Content,0,@TraceID)

        set @ReceiveMessageID=scope_identity()

        insert into ReceiveMessageLog ( ReceiveMessageID, Remark )
            values(@ReceiveMessageID,N'接收.')

    commit transaction  
end try
begin catch 
    declare @error nvarchar(2048)=error_message()
    ;throw 50001 ,@error,1
    if (@@trancount >0) rollback transaction
end catch
go


create procedure up_cReceiveMessageLog
(
    @ReceiveMessageID int,
    @Remark nvarchar(100),
    @IsCompleted bit
)
as
begin try
    begin transaction        

        insert into ReceiveMessageLog ( ReceiveMessageID, Remark )
            values(@ReceiveMessageID,@Remark)

        update ReceiveMessage set IsCompleted=@IsCompleted where ID=@ReceiveMessageID

    commit transaction  
end try
begin catch 
    declare @error nvarchar(2048)=error_message()
    ;throw 50001 ,@error,1
    if (@@trancount >0) rollback transaction
end catch
go


create procedure up_cSendMessage
(
    @MessageType nvarchar(200),
    @Operation smallint,
    @Content nvarchar(max)
)
as
begin try
    begin transaction
        declare @SendMessageID int  

        insert into SendMessage ( MessageType, Operation, Content,IsArrived)
            values(@MessageType,@Operation,@Content,0)

        set @SendMessageID=scope_identity()

        insert into SendMessageLog ( SendMessageID, Remark )
            values(@SendMessageID,N'接收.')

    commit transaction  
end try
begin catch 
    declare @error nvarchar(2048)=error_message()
    ;throw 50001 ,@error,1
    if (@@trancount >0) rollback transaction
end catch
go


create procedure up_cSendMessageLog
(
    @SendMessageID int,
    @Remark nvarchar(100),
    @IsArrived bit
)
as
begin try
    begin transaction        

        insert into SendMessageLog ( SendMessageID, Remark )
            values(@SendMessageID,@Remark)

        update SendMessage set IsArrived=@IsArrived where ID=@SendMessageID

    commit transaction  
end try
begin catch 
    declare @error nvarchar(2048)=error_message()
    ;throw 50001 ,@error,1
    if (@@trancount >0) rollback transaction
end catch
go


create procedure up_dReceiveMessageWithCompleted
as
set nocount on
begin try
    begin transaction
        declare @tb_del table(ID int)  
        insert into @tb_del(ID) select ID from ReceiveMessage where IsCompleted=1

        delete a from ReceiveMessageLog a where exists(select 1 from @tb_del x where x.ID=a.ReceiveMessageID)

        delete a from ReceiveMessage a where exists(select 1 from @tb_del x where x.ID=a.ID)

    commit transaction  
end try
begin catch 
    declare @error nvarchar(2048)=error_message()
    ;throw 50001 ,@error,1
    if (@@trancount >0) rollback transaction
end catch
go


create procedure up_dSendMessageWithArrived
as
begin try
    begin transaction

        declare @tb_del table(ID int)  
        insert into @tb_del(ID) select ID from SendMessage where IsArrived=1

        delete a from SendMessageLog a where exists(select 1 from @tb_del x where x.ID=a.SendMessageID)

        delete a from SendMessage a where exists(select 1 from @tb_del x where x.ID=a.ID)

    commit transaction  
end try
begin catch 
    declare @error nvarchar(2048)=error_message()
    ;throw 50001 ,@error,1
    if (@@trancount >0) rollback transaction
end catch
go

 

1.前言

CentOS 下 MySQL 5.6 基于 RPM 的下载、安装、配置

系统: CentOS 7 x86_64
MySQL 版本: 5.6.40
安装方式: RPM


发表评论

电子邮件地址不会被公开。 必填项已用*标注