nodejs之MAC终端操作mysql（三）批量操作数据、数据库导入导出、事务、函数、存储过程，触发器、流程控制

最新推荐文章于 2022-10-19 08:17:37 发布

liyubao160

最新推荐文章于 2022-10-19 08:17:37 发布

阅读量903

点赞数 1

分类专栏： nodejs

本文链接：https://blog.csdn.net/u011146511/article/details/79584481

版权

nodejs 专栏收录该内容

49 篇文章 3 订阅

订阅专栏

-------------数据库的导入和导出（数据备份和数据恢复）-------------

严格来讲，其实Navicat for MySQL导出的不是数据库，而是数据库中的所有表的数据以及结构。所以，如果你不建立与之对应的数据库，而是随便找一个数据导入，结果是这个数据库多了几张刚刚导入的表。

从navicat导出数据库.sql文件：选中要导出的数据库--》右键选中转储sql文件--》结构+数据--》设置存储在本地的名字和路径，--》存储按钮；

从navicat导入数据库.sql文件：新建一个数据库（数据库名字要根我们导入的数据库名字一样）--》选中刚才创建的数据库，点击右键选中运行SQL文件--》选择要导入的文件路径，开始按钮；

从navicat导出表(存储成各种样式如.xsl)：选中表或者具体某个表--》右键导出向导---》选择导出的格式--》选择存储的路径---》选择要导出的表--》确定；

从navicat导入表(吧各种样式的标导入，如.xsl)：选中表或者具体某个表--》右键导入向导--》选中格式--》选中要导入的数据表--》按提示操作；

navicat数据传输：使用数据传输可以导出表结构或数据，还可以分别选择每个表是否导出。"数据传输"这个名字起的可能使人误导，实际上它不只可以传输到另一个数据库，也可以保存为sql文件。

**********命令行下具体用法如下： mysqldump -u 用户名 -p 密码 -d 数据库名表名脚本名;

加-d只导出表结构，不加-d导出表结构和表数据

1、导出数据库dbname的表结构（其中用户名为root,密码为dbpasswd,生成的脚本名为db.sql）

/usr/local/mysql/bin/mysqldump -u root -p -d firstDB > /Users/wofu/Downloads/db2.sql 然后输如数据库密码

2、导出数据库为dbname某张表(test)结构

/usr/local/mysql/bin/mysqldump -u root -p -d firstDB test > /Users/wofu/Downloads/db2.sql 然后输如数据库密码

3、导出数据库为dbname所有表结构及表数据（不加-d）

/usr/local/mysql/bin/mysqldump -u root -p firstDB > /Users/wofu/Downloads/db2.sql 然后输如数据库密码

4、导出数据库为dbname某张表(test)结构及表数据（不加-d）

/usr/local/mysql/bin/mysqldump -u root -p firstDB test > /Users/wofu/Downloads/db2.sql 然后输如数据库密码

****导入数据库
方法一：
（1）选择数据库
use abc;
（2）设置数据库编码
set names utf8;
（3）导入数据（注意sql文件的路径）
source /Users/wofu/Downloads/db2.sql
方法二：

/usr/local/mysql/bin/mysql -u root -p密码 firstDB < db2.sql

*******SELECT INTO OUTFILE语句也是一种逻辑备份的方法，或者更准确地说是导出一张表中的数据。

select into outfile用法

语法：SELECT ... INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]

SELECT *FROM people
INTO OUTFILE '/Users/wofu/Downloads/a.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

[TERMINATED BY 'string']表示每个列的分隔符，

[[OPTIONALLY]ENCLOSED BY'char']表示对于字符串的包含符，

[ESCAPED BY'char']表示转义符，

[STARTING BY'string']表示每行的开始符号，

如果没有指定任何FIELDS和LINES的选项，默认使用以下的设置：

FIELDS TERMINATED BY '\t'

ENCLOSED BY ''

ESCAPED BY '\\'

LINES TERMINATED BY '\n'

STARTING BY ''

load data infile用法

语法：

LOAD DATA [LOW_PRIORITY|CONCURRENT] [LOCAL] INFILE 'file_name'

[REPLACE|IGNORE]

INTO TABLE tbl_name

[CHARACTER SET charset_name]

[{FIELDS|COLUMNS}

　　[TERMINATED BY'string']

　　[[OPTIONALLY]ENCLOSED BY'char']

　　[ESCAPED BY'char']

]

[LINES

　　[STARTING BY'string']

　　[TERMINATED BY'string']

]

[IGNORE number LINES]

[(col_name_or_user_var,……)]

[SET col_name=expr,……]

注意：如果导出时用到了FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'语句，那么LODA时也要加上同样的分隔限制语句。还要注意编码问题；

LOAD DATA INFILE '/Users/wofu/Downloads/a.txt'
INTO TABLE people
CHARACTER SET utf8

FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

-INTO TABLE ：导入到哪个表

--CHARACTER SET ：mysql字符集，一定要加上，免去乱码麻烦

--FIELDS TERMINATED BY ：以什么作为分割符

-- ENCLOSED BY ：被什么包围

load data infile语句从一个文本文件中以很高的速度读入一个表中。使用这个命令之前，mysqld进程（服务）必须已经在运行。为了安全原因，当读取位于服务器上的文本文件时，文件必须处于数据库目录或可被所有人读取。另外，为了对服务器上文件使用load data infile，在服务器主机上你必须有file的权限。

1 如果你指定关键词low_priority，那么MySQL将会等到没有其他人读这个表的时候，才把插入数据。可以使用如下的命令：
load data low_priority infile "/home/mark/data sql" into table Orders;

2 如果指定local关键词，则表明从客户主机读文件。如果local没指定，文件必须位于服务器上。

3 replace和ignore关键词控制对现有的唯一键记录的重复的处理。如果你指定replace，新行将代替有相同的唯一键值的现有行。如果你指定ignore，跳过有唯一键的现有行的重复行的输入。如果你不指定任何一个选项，当找到重复键时，出现一个错误，并且文本文件的余下部分被忽略。例如：

load data low_priority infile "/home/mark/data sql" replace into table Orders;

4 分隔符

（1） fields关键字指定了文件记段的分割格式，如果用到这个关键字，MySQL剖析器希望看到至少有下面的一个选项：
terminated by 分隔符：意思是以什么字符作为分隔符
enclosed by字段括起字符
escaped by转义字符

terminated by 描述字段的分隔符，默认情况下是tab字符（\t）
enclosed by描述的是字段的括起字符。
escaped by描述的转义字符。默认的是反斜杠（backslash：\ ）

例如：load data infile "/home/mark/Orders txt" replace into table Orders fields terminated by',' enclosed by '"';

（2）lines 关键字指定了每条记录的分隔符默认为'\n'即为换行符

如果两个字段都指定了那fields必须在lines之前。如果不指定fields关键字缺省值与如果你这样写的相同： fields terminated by'\t' enclosed by ’ '' ‘ escaped by'\\'

如果你不指定一个lines子句，缺省值与如果你这样写的相同： lines terminated by'\n'

例如：load data infile "/jiaoben/load.txt" replace into table test fields terminated by ',' lines terminated by '/n';

5 load data infile 可以按指定的列把文件导入到数据库中。当我们要把数据的一部分内容导入的时候，，需要加入一些栏目（列/字段/field）到MySQL数据库中，以适应一些额外的需要。比方说，我们要从Access数据库升级到MySQL数据库的时候

下面的例子显示了如何向指定的栏目(field)中导入数据：
load data infile "/home/Order txt" into table Orders(Order_Number, Order_Date, Customer_ID);

6 当在服务器主机上寻找文件时，服务器使用下列规则：
（1）如果给出一个绝对路径名，服务器使用该路径名。
（2）如果给出一个有一个或多个前置部件的相对路径名，服务器相对服务器的数据目录搜索文件。
（3）如果给出一个没有前置部件的一个文件名，服务器在当前数据库的数据库目录寻找文件。
例如： /myfile txt”给出的文件是从服务器的数据目录读取，而作为“myfile txt”给出的一个文件是从当前数据库的数据库目录下读取

参考：http://blog.csdn.net/helloxiaozhe/article/details/77680255

参考：https://www.cnblogs.com/wade-luffy/p/6307368.html

-----------------批量操作-------------

****** 一次性选择多个条件：

insert into people values('lambo',20),('lambo','21'),('lambo2',22);//一次性插入多条记录

select * from my_student where id in(1,3,5);//id是1，3，5的符合条件；

select * from my_student where height between 180 and 190 ;//180到190之间的符合条件；

select * from user where concat(username, '分隔符'，password) like '%admin%';// 实现的效果是可以输入多个关键字查询，多个关键字间以逗号分隔。concat的作用是连接字符串；

************批量插入记录**********

方法一：创建存储过程中用循环批量插入----调用call myproc()#

单循环：

create procedure myproc()
begin
  declare num int;
  declare st varchar(20);
  set num=1;
   while num <= 15 do
   set st=concat("la",num);
      insert into people(name,ages) values(st,num);
    set num=num+1;
  end while;
commit;
end#

循环嵌套：

DELIMITER ;; //更改界定符为；；
CREATE PROCEDURE test_insert()

BEGIN 
DECLARE a INT DEFAULT 1; 
DECLARE b TINYINT DEFAULT 1; 
WHILE (a <= 100) DO 
repeat 
SET a = a + 1; 
select a; 
WHILE (b <=20) DO 
insert into school_sp_mj(school_id,mojor_id,status) values(a,b,1); 
SET b = b + 1; 
select b; 
END WHILE; 

SET b = 1; 
select a; 
until a >= i_PlayerCount 
end repeat; 
END WHILE; 
commit; 
END;; //-----到此结束创建过程
CALL test_insert();//调用

方法二：命令行批量导入.sql文件到数据库中；

*****第一步：USE 数据库名

*****第二步：SOURCE 文件路径.sql;

--------------查看MySQL服务器配置信息 -------

参考：show variables like xxx 详解mysql运行时参数http://blog.csdn.net/mooncarp/article/details/51787694

show variables;

慢查询:show variables like '%slow%';

连接数：show variables like 'max_connections';

临时表：show global status like 'created_tmp%';

open table 的情况：show global status like 'open%tables%';

进程使用情况：show global status like 'Thread%';

查询缓存(query cache) ：show global status like 'qcache%';

Qcache_free_blocks：缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。
Qcache_free_memory：缓存中的空闲内存。
Qcache_hits：每次查询在缓存中命中时就增大
Qcache_inserts：每次插入一个查询时就增大。命中次数除以插入次数就是不中比率。
Qcache_lowmem_prunes：缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的 free_blocks和free_memory可以告诉您属于哪种情况）
Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。
Qcache_queries_in_cache：当前缓存的查询（和响应）的数量。
Qcache_total_blocks：缓存中块的数量。

--------查看MySQL服务器运行的各种状态值 -----

show global status;

------修改配置mysql的文件-------------
mac在安装这个版本的mysql的时候，我没有发现mysql 的配置文件。不管是
/etc 下还是 mysql 的support-files 下都没有；

-----------------------------存储函数-----------------------------------

存储函数不能有select语句；

begin-end用于定义一组语句块，在各大数据库中的客户端工具中可直接调用，但在mysql中不可用。begin-end、流程控制语句、局部变量只能用于函数、存储过程内部、游标、触发器的定义内部。

格式：

CREATE  function 函数名（参数一 数据类型，参数二 数据类型，。。。）

return 返回值类型

begin 

     这里写完整的语句

     return 返回值；（要和上面设定的返回值类型一致）

end

调用：SELECT 函数名（实参一，实参二）

******检查是否开启了创建函数的设置：

show variables like '%func%';

*********开启创建函数的设置：

set global log_bin_trust_function_creators = 1;

********创建一个简单的函数，没有参数

CREATE FUNCTION hello_world()------hello_world是函数名
RETURNS TEXT-------返回值类型
LANGUAGE SQL
BEGIN
RETURN 'Hello World';-----返回值
END;#-------------#是界定符号

调用函数：

SELECT hello_world()#

**********创建带参数的函数

DROP FUNCTION IF EXISTS hello_world#-------#是界定符，这句话意思：如果存在hello_world这个函数，就删掉；

DROP FUNCTION IF EXISTS hello_world#
CREATE FUNCTION hello_world(addressee TEXT)
RETURNS TEXT
LANGUAGE SQL
BEGIN
RETURN CONCAT('Hello ', addressee);

END;#

调用函数：SELECT hello_world('Earth')#

*********创建带参数的函数，并且在函数内创建局部变量；

DROP FUNCTION IF EXISTS hello_world#
CREATE FUNCTION hello_world(addressee TEXT)------addressee是参数，多个参数用,分割参数，text类型的，类型必须是mysql列存在的类型
RETURNS TEXT----返回值类型
BEGIN
DECLARE strlen INT;----strlen是定义的变量，int类型；
SET strlen = LENGTH(addressee);-----传入的参数的长度，LENGTH是MySQL内置的函数
RETURN CONCAT('Hello ', addressee, ' - your parameter has ', strlen, ' characters');----CONCAT用于连接字符串
END;#

调用：SELECT hello_world('Earth')#

***************函数中创建循环一：

DROP FUNCTION IF EXISTS hello_world#
CREATE FUNCTION looptest()
  RETURNS INT READS SQL DATA
BEGIN
  DECLARE v_total INT;
  SET v_total = 0;
  count_loop: LOOP
    SET v_total = v_total + 1;
    IF v_total = 10 THEN
      LEAVE count_loop;
    END IF; 
  END LOOP;
  RETURN v_total;
END;#

***************函数中创建循环二：

DROP FUNCTION IF EXISTS looptest#
CREATE FUNCTION looptest()
  RETURNS INT READS SQL DATA
BEGIN
  DECLARE v_total INT;
  DECLARE v_counter INT;
  DECLARE done INT DEFAULT FALSE;
  DECLARE csr CURSOR FOR 
    SELECT counter FROM items;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  SET v_total = 0;
  OPEN csr;
  read_loop: LOOP
    FETCH csr INTO v_counter;

    IF done THEN
      LEAVE read_loop;
    END IF;

    SET v_total = v_total + v_counter;
  END LOOP;
  CLOSE csr;

  RETURN v_total;
END;#

-----------------------存储过程----------------------

******存储过程相当于没有返回值的函数；可以是使用select语句；视图和存储过程数据哪一个数据库；

参数：https://www.cnblogs.com/zhucww/p/7085833.html

格式：

in|out|inout-----作为数据流向的修饰，in：外部流向存储过程内部，out存储过程流向外部；inout：双向；

CREATE procedure 存储过程名（【in|out|inout】参数一 数据类型，[in|out|inout]参数二 数据类型，。。。）

begin 

     这里写完整的语句：可以是流程控制，增删改查

end

调用：call 存储过程名（）

******创建存储过程myproc，包含循环和插入数据

DROP procedure IF EXISTS myproc#----删除已经存在的存储过程myproc

DROP procedure IF EXISTS myproc#
//  在sql语句进行字符串拼接时，不是我们习惯的使用“+”，而是使用mysql自带的字符拼接 函数concat(str1,str2,...)，将上面sql改为
create procedure myproc()
begin
  declare num int;
  set num=1;
   while num <= 5 do
      insert into people(name,ages) values('sdd',num);//
insert into people(name,ages) values(concat('sdd',num),num);//concat拼接字符串
    set num=num+1;
  end while;
commit;
end#

调用：CALL myproc()#

declare 声明变量：

create procedure myproc()
begin
  declare num int;
  declare st varchar(20);
  set num=1;
   while num <= 5 do
   set st=concat("la",num);//拼接字符串
      insert into people(name,ages) values(st,num);
    set num=num+1;
  end while;
commit;
end#

******************************流程控制********************************

流程控制语句，用于将多个SQL语句，划分或组成成符合业务逻辑的代码块

流程控制语句包括，IF语句、CASE语句、LOOP语句、WHILE语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句

每个流程中，可能包含一个单独语句，也可以是使用BEGIN……END构造的复合语句，可以嵌套

参考详解：https://www.cnblogs.com/tongxiaoda/p/8022858.html

参考详解二：http://blog.csdn.net/nangeali/article/details/76285327

**************************触发器**********************************

预先定义好一段代码，在某种情形下触发；触发器和存储函数、存储过程一样，内部属于编程环境；触发器不能有select语句；触发器属于哪一张表；

在触发器内部不要用变量；

new在触发器中代表刚刚插入的数据行，在insert中有效，old代表旧的一条数据，在update和delete中有效，new.id代表刚插入的一行的id字段的值；触发器属于具体的哪一张表；视图，存储过程属于哪个数据库；

一张表最多创建6个触发器；

参考详解：https://www.cnblogs.com/duodushu/p/5446384.html

创建触发器
在MySQL中，创建触发器语法如下：

EATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt

其中：

trigger_name：标识触发器名称，用户自行指定；
trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；
trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；
tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；
trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句。

由此可见，可以建立6种触发器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。

另外有一个限制是不能同时在一个表上建立2个相同类型的触发器，因此在一个表上最多建立6个触发器。

MySQL 除了对 INSERT、UPDATE、DELETE 基本操作进行定义外，还定义了 LOAD DATA 和 REPLACE 语句，这两种语句也能引起上述6中类型的触发器的触发。

LOAD DATA 语句用于将一个文件装入到一个数据表中，相当与一系列的 INSERT 操作。

REPLACE 语句一般来说和 INSERT 语句很像，只是在表中有 primary key 或 unique 索引时，如果插入的数据和原来 primary key 或 unique 索引一致时，会先删除原来的数据，然后增加一条新数据，也就是说，一条 REPLACE 语句有时候等价于一条。

********查看触发器:SHOW TRIGGERS [FROM 数据库名];如果加上方括号中的代码，就不用先use 数据库名；

********删除触发器：DROP TRIGGER [IF EXISTS] [数据库名.]触发器名；

*************************事务********************************

事务是保证多条语句要么全部执行成功，要么一条都不执行的机制；

查看模式：（是不是自动执行提交commit）show variables like ”%autocommit%“；

autocommit为0的话（set autocommit=0），就关闭了该模式；所有语句必须commit之后就关闭了该模式；

********事务的基本流程：1.声明开始事务：start transaction;

2.设定多条要执行的语句；---这时只是在内存状态下执行，而不是物理状态的执行（就是数据库还没改变）；

3.判断是否需要执行这些语句：

if(正确){

commit；//提交，变成物理状态执行；

}else{

rollback;//回滚，不执行任何语句；

}

事务的特点：

原子性-----不可分割，要么语句全部执行，要么都不执行；

一致性------

隔离性-------多个事务同时发生，但每个事务独立执行；

持久性----一个事务执行成功，数据会在硬盘上永久改变；

liyubao160

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
nodejs之MAC终端操作mysql（三）批量操作数据、数据库导入导出、事务、函数、存储过程，触发器、流程控制

-------------数据库的导入和导出（数据备份和数据恢复）-------------严格来讲，其实Navicat for MySQL导出的不是数据库，而是数据库中的所有表的数据以及结构。所以，如果你不建立与之对应的数据库，而是随便找一个数据导入，结果是这个数据库多了几张刚刚导入的表。从navicat导出数据库.sql文件：选中要导出的数据库--》右键选中转储sql文件--》结构+数据--》...
复制链接

扫一扫