个人简介
偶尔有空上来看看
文章分类
文章存档
最近访客
相关博文
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
分类： 数据库开发技术
2020-04-24 10:28:32
1) 切换到 sdbadmin 用户；
2）创建 mysql 实例 myinst 并创建 company 数据库以及 employee 数据表( id int, name varchar(128), age int )；
cd /opt/sequoiasql/mysql
/opt/sequoiasql/mysql/bin/sdb_sql_ctl addinst myinst -d database/3306/
mysql -h127.0.0.1 -uroot
create database company;
use company;
create table employee
(
id int ,
name varchar(128),
age int
);
3）在 employee 数据表创建普通索引 name_index，字段为 name ；
create index name_index on employee(name);
4）查看 sequoiadb 是否有 name_index 这个索引；
show index from employee;
5）在 employee 插入信息（id: 10001, name: 'georgi', age: 48）；
insert into employee (id,name, age) values (10001,"georgi", 48);
6）开始 mysql 事务,插入数据(id: 10002, name: 'bezalel', age: 21),然后查询 employee 表信息,最后提交事务;
begin;
insert into employee (id,name, age) values (10002,"bezalel", 21);
select * from employee;
commit;
7）开始 mysql 事务,插入数据(id: 10003, name: 'lazhu', age: 22),然后查询 employee 表信息,不提交然后回滚事务,在进行表信息查询；
begin;
insert into employee (id,name, age) values (10003,"lazhu", 22);
select * from employee;
rollback;
select * from employee;
8）修改实例下的配置文件（database/3306/auto.cnf），加入导入导出路径参数配置（secure_file_priv）；
cat >> /opt/sequoiasql/mysql/database/3306/auto.cnf << eof
secure_file_priv = "/opt/sequoiasql/tmp"
eof
mkdir /opt/sequoiasql/mysql/tmp
bin/sdb_sql_ctl restart myinst
9）导出 employee 表数据至 /opt/sequoiasql/tmp/employee_export.csv下；
use company;
select * from employee
into outfile '/opt/sequoiasql/tmp/employee_export.csv'
fields terminated by ','
optionally enclosed by '"'
escaped by '"'
lines terminated by '\r\n';
10）新建 employee_import_test 表( id int, name varchar(128), age int )并将刚刚导出的数据进行导入到 employee_import_test 表；
create table employee_import_test
(
id int ,
name varchar(128),
age int
);
load data infile '/opt/sequoiasql/mysql/tmp/employee_export.csv'
into table employee_import_test
fields terminated by ','
optionally enclosed by '"'
escaped by '"'
lines terminated by '\r\n';
11）对 employee 表进行备份,备份路径为 /opt/sequoiasql/tmp/employee.sql；
/opt/sequoiasql/mysql/bin/mysqldump -h 127.0.0.1 -u root company employee > /opt/sequoiasql/tmp/employee.sql
12）清空 employee 表数据并执行备份恢复操作；
truncate table employee;
source /opt/sequoiasql/mysql/tmp/employee_import_test.sql;
2）创建 mysql 实例 myinst 并创建 company 数据库以及 employee 数据表( id int, name varchar(128), age int )；
cd /opt/sequoiasql/mysql
/opt/sequoiasql/mysql/bin/sdb_sql_ctl addinst myinst -d database/3306/
mysql -h127.0.0.1 -uroot
create database company;
use company;
create table employee
(
id int ,
name varchar(128),
age int
);
3）在 employee 数据表创建普通索引 name_index，字段为 name ；
create index name_index on employee(name);
4）查看 sequoiadb 是否有 name_index 这个索引；
show index from employee;
5）在 employee 插入信息（id: 10001, name: 'georgi', age: 48）；
insert into employee (id,name, age) values (10001,"georgi", 48);
6）开始 mysql 事务,插入数据(id: 10002, name: 'bezalel', age: 21),然后查询 employee 表信息,最后提交事务;
begin;
insert into employee (id,name, age) values (10002,"bezalel", 21);
select * from employee;
commit;
7）开始 mysql 事务,插入数据(id: 10003, name: 'lazhu', age: 22),然后查询 employee 表信息,不提交然后回滚事务,在进行表信息查询；
begin;
insert into employee (id,name, age) values (10003,"lazhu", 22);
select * from employee;
rollback;
select * from employee;
8）修改实例下的配置文件（database/3306/auto.cnf），加入导入导出路径参数配置（secure_file_priv）；
cat >> /opt/sequoiasql/mysql/database/3306/auto.cnf << eof
secure_file_priv = "/opt/sequoiasql/tmp"
eof
mkdir /opt/sequoiasql/mysql/tmp
bin/sdb_sql_ctl restart myinst
9）导出 employee 表数据至 /opt/sequoiasql/tmp/employee_export.csv下；
use company;
select * from employee
into outfile '/opt/sequoiasql/tmp/employee_export.csv'
fields terminated by ','
optionally enclosed by '"'
escaped by '"'
lines terminated by '\r\n';
10）新建 employee_import_test 表( id int, name varchar(128), age int )并将刚刚导出的数据进行导入到 employee_import_test 表；
create table employee_import_test
(
id int ,
name varchar(128),
age int
);
load data infile '/opt/sequoiasql/mysql/tmp/employee_export.csv'
into table employee_import_test
fields terminated by ','
optionally enclosed by '"'
escaped by '"'
lines terminated by '\r\n';
11）对 employee 表进行备份,备份路径为 /opt/sequoiasql/tmp/employee.sql；
/opt/sequoiasql/mysql/bin/mysqldump -h 127.0.0.1 -u root company employee > /opt/sequoiasql/tmp/employee.sql
12）清空 employee 表数据并执行备份恢复操作；
truncate table employee;
source /opt/sequoiasql/mysql/tmp/employee_import_test.sql;
给主人留下些什么吧！~~