mysql学习(二)控制台执行sql脚本创建表修改表结构

mysql数据库控制套执行sql脚本实现如下三个功能：
1->创建表，并插入数据；
2->执行升级脚本，修改表字段；
3->执行会滚脚本，恢复表字段；

mysql数据库登陆

~$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.31-0ubuntu0.18.04.1 (Ubuntu)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| choose             |
| mysql              |
| performance_schema |
| roster             |
| sys                |
+--------------------+
6 rows in set (0.01 sec)

mysql> use choose;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> source /home/lwl/mysqlScript/createTable_ETHIO.sql

创建表

sql脚本内容：

-- 创建表
drop table if exists CREATE_TEST_TABLE;

create table CREATE_TEST_TABLE(
    `COLLECTION_CENTERID` varchar(256),
    `COLLECTION_CENTERNAME` varchar(1024),
    `UBS_FLAG` varchar(1)
    -- `ORG_LEVEL` varchar(8)
);

alter table CREATE_TEST_TABLE add constraint PK_CREATE_TEST_TABLE primary key (`COLLECTION_CENTERID`);

insert into CREATE_TEST_TABLE(`COLLECTION_CENTERID`, `COLLECTION_CENTERNAME`, `UBS_FLAG`) values ('CC20210228-1', 'CC20210228-CC20210228-1', 'N');
insert into CREATE_TEST_TABLE(`COLLECTION_CENTERID`, `COLLECTION_CENTERNAME`, `UBS_FLAG`) values ('CC20210228-2', 'CC20210228-CC20210228-2', 'N');
insert into CREATE_TEST_TABLE(`COLLECTION_CENTERID`, `COLLECTION_CENTERNAME`, `UBS_FLAG`) values ('CC20210228-3', 'CC20210228-CC20210228-3', 'N');
insert into CREATE_TEST_TABLE(`COLLECTION_CENTERID`, `COLLECTION_CENTERNAME`, `UBS_FLAG`) values ('CC20210228-4', 'CC20210228-CC20210228-4', 'N');

commit;

mysql> source /home/lwl/mysqlScript/createTable_ETHIO.sql
mysql> desc CREATE_TEST_TABLE;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| COLLECTION_CENTERID   | varchar(256)  | NO   | PRI | NULL    |       |
| COLLECTION_CENTERNAME | varchar(1024) | YES  |     | NULL    |       |
| UBS_FLAG              | varchar(1)    | YES  |     | NULL    |       |
+-----------------------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from CREATE_TEST_TABLE;
+---------------------+-------------------------+----------+
| COLLECTION_CENTERID | COLLECTION_CENTERNAME   | UBS_FLAG |
+---------------------+-------------------------+----------+
| CC20210228-1        | CC20210228-CC20210228-1 | N        |
| CC20210228-2        | CC20210228-CC20210228-2 | N        |
| CC20210228-3        | CC20210228-CC20210228-3 | N        |
| CC20210228-4        | CC20210228-CC20210228-4 | N        |
+---------------------+-------------------------+----------+
4 rows in set (0.00 sec)

升级脚本

升级脚本内容如下

-- 升级脚本
delimiter $$
drop procedure if exists UPDATE_TEST_TABLE$$
create procedure UPDATE_TEST_TABLE()
begin
    declare c_count int;
    select count(1) from information_schema.columns where table_name = 'CREATE_TEST_TABLE' and column_name = 'ORG_LEVEL' into c_count;
    if c_count = 0 then
        set @alterTableSql = concat( "alter table CREATE_TEST_TABLE add column ORG_LEVEL varchar(8)" );
        prepare stmt from @alterTableSql;
        execute stmt;
        deallocate prepare stmt;
    end if;

    select count(1) from information_schema.columns where table_name = 'CREATE_TEST_TABLE' and column_name = 'COLLECTION_CENTERID' into c_count;
    if c_count > 0 then
        set @alterTableSql = concat("alter table CREATE_TEST_TABLE modify COLLECTION_CENTERID VARCHAR(252)");
        prepare stmt from @alterTableSql;
        execute stmt;
        deallocate prepare stmt;
    end if;
end$$

call UPDATE_TEST_TABLE()$$
drop procedure UPDATE_TEST_TABLE$$
delimiter ;

mysql> source /home/lwl/mysqlScript/table_TEST_TABLE_update.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select * from CREATE_TEST_TABLE;
+---------------------+-------------------------+----------+-----------+
| COLLECTION_CENTERID | COLLECTION_CENTERNAME   | UBS_FLAG | ORG_LEVEL |
+---------------------+-------------------------+----------+-----------+
| CC20210228-1        | CC20210228-CC20210228-1 | N        | NULL      |
| CC20210228-2        | CC20210228-CC20210228-2 | N        | NULL      |
| CC20210228-3        | CC20210228-CC20210228-3 | N        | NULL      |
| CC20210228-4        | CC20210228-CC20210228-4 | N        | NULL      |
+---------------------+-------------------------+----------+-----------+
4 rows in set (0.01 sec)

mysql> desc CREATE_TEST_TABLE;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| COLLECTION_CENTERID   | varchar(252)  | NO   | PRI | NULL    |       |
| COLLECTION_CENTERNAME | varchar(1024) | YES  |     | NULL    |       |
| UBS_FLAG              | varchar(1)    | YES  |     | NULL    |       |
| ORG_LEVEL             | varchar(8)    | YES  |     | NULL    |       |
+-----------------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

回滚脚本

脚本内容如下：

-- 回滚脚本
delimiter $$
drop procedure if exists UPDATE_TEST_TABLE$$
create procedure UPDATE_TEST_TABLE()
begin
    declare c_count int;
    select count(1) from information_schema.columns where table_name = 'CREATE_TEST_TABLE' and column_name = 'ORG_LEVEL' into c_count;
    if c_count > 0 then
        set @alterTableSql = concat( "alter table CREATE_TEST_TABLE drop column ORG_LEVEL" );
        prepare stmt from @alterTableSql;
        execute stmt;
        deallocate prepare stmt;
    end if;

    select count(1) from information_schema.columns where table_name = 'CREATE_TEST_TABLE' and column_name = 'COLLECTION_CENTERID' into c_count;
    if c_count > 0 then
        set @alterTableSql = concat("alter table CREATE_TEST_TABLE modify COLLECTION_CENTERID VARCHAR(256)");
        prepare stmt from @alterTableSql;
        execute stmt;
        deallocate prepare stmt;
    end if;
end$$

call UPDATE_TEST_TABLE()$$
drop procedure UPDATE_TEST_TABLE$$
delimiter ;

mysql> source /home/lwl/mysqlScript/table_TEST_TABLE_rollback.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> desc CREATE_TEST_TABLE;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| COLLECTION_CENTERID   | varchar(256)  | NO   | PRI | NULL    |       |
| COLLECTION_CENTERNAME | varchar(1024) | YES  |     | NULL    |       |
| UBS_FLAG              | varchar(1)    | YES  |     | NULL    |       |
+-----------------------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from CREATE_TEST_TABLE;
+---------------------+-------------------------+----------+
| COLLECTION_CENTERID | COLLECTION_CENTERNAME   | UBS_FLAG |
+---------------------+-------------------------+----------+
| CC20210228-1        | CC20210228-CC20210228-1 | N        |
| CC20210228-2        | CC20210228-CC20210228-2 | N        |
| CC20210228-3        | CC20210228-CC20210228-3 | N        |
| CC20210228-4        | CC20210228-CC20210228-4 | N        |
+---------------------+-------------------------+----------+
4 rows in set (0.00 sec)