mysql笔记(一)：简单的过程/视图/表状态/导入导出数据到文本/导入导出数据到sql文件...

最新推荐文章于 2022-07-06 05:23:16 发布

njit_peiyuan

最新推荐文章于 2022-07-06 05:23:16 发布

阅读量166

点赞数

文章标签：数据库

原文链接：https://my.oschina.net/sallency/blog/509598

版权

为什么80%的码农都做不了架构师？>>>

过程/函数：

//显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程
show procedure/function status [like 'pattern']
//从系统表中查询某一存储过程的相关信息
select * from mysql.proc where name = 'procedure_name'
//创建过程
create procedure count_user() select count(*) as user_number from mysql.user;
//调用过程
call count_user();
//删除过程
drop procedure procedure_name
//指定的存储过程的创建信息
show create procedure procedure_name

视图：视图作为一张表的查询封装，可以很好的起到中间组件的作用，其实视图也是一张表，虚拟表

//创建视图
create view user_view as select host, user from mysql.user;
//使用视图
select * from user_view [where condition.....]
//删除视图
drop view user_view
//查看视图 
//show table status 会罗列出当前数据库所有的表的状态,视图作为一张特殊表也会被列出来且comment="view"
show table status where comment='view'
//查看视图创建信息
show create view view_name

查看存储过程/函数/触发器/事件/视图

show procedure status
show function status
show triggers
show events
show table status [from dbname] [like 'pattern'] where comment="view"

查看表状态：查看当前数据库或指定数据库的所有表的状态信息

show table status [from dbname] [like 'pattern']

查看数据库使用量

//每个数据库的信息图表
use information_schema;
//查看所有数据库的总大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data_size from tables;
//查看某一数据库的大小table_schema
select concat(round(sum(data_length/1024/1024),2),'MB') as data_size from tables where table_schema='dbname';
//查看某一表的大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='dbname' and table_name='table_name';

导出数据到文件 into outfile 'file_path_name'

select * FROM test.table into outfile '/tmp/data.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

导入mysql服务器端数据到数据库 load data infile 'file_path_name'

LOAD DATA INFILE '/tmp/data.txt' INTO TABLE test.table_same_create_info FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'

导入客户端上的数据到数据库 load data local infile 'file_path_name'

LOAD DATA LOCAL INFILE '/tmp/data.txt' INTO TABLE test.table_same_create_info FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'

FIELDS TERMINATED BY ',' 字段间分割符

OPTIONALLY ENCLOSED BY '"' 将字段包围对数值型无效

LINES TERMINATED BY '\n' 换行符

mysql

1、导出数据 mysql -e "command" [--skip-colunm-names] dbName

--e: 执行某命令并退出

--skip-colunm-names：导出数据时不携带列名，只有数据

mysql -u root -p -e "select * from tableName" --skip-column-names dbName > data.sql

如同登录mysql后的

select * from tableName into outfile 'data.sql'

2、导入数据 mysql -u root -p --default-character-set=utf8 -P 3306 < data.sql

mysqldump

-A 全库备份
--routines 备份存储过程和函数
--default-character-set=utf8 设置字符集
--lock-all-tables 全局一致性锁
--add-drop-database 在每次执行建表语句之前,先执行DROP TABLE IF EXIST语句
--no-create-db 不输出CREATE DATABASE语句
--no-create-info 不输出CREATE TABLE语句
--databases 将后面的参数都解析为库名
--tables 第一个参数为库名后续为表名

==========================================================================应用举例引用（http://blog.chinaunix.net/uid-16844903-id-3411118.html）

导出

导出全库备份到本地的目录

mysqldump -u$USER -p$PASSWD -h127.0.0.1 -P3306 --routines --default-character-set=utf8 --lock-all-tables --add-drop-database -A > db.all.sql

导出指定库到本地的目录(例如mysql库)

mysqldump -u$USER -p$PASSWD -h127.0.0.1 -P3306 --routines --default-character-set=utf8 --databases mysql > db.sql

导出某个库的表到本地的目录(例如mysql库的user表)

mysqldump -u$USER -p$PASSWD -h127.0.0.1 -P3306 --routines --default-character-set=utf8 --tables mysql user> db.table.sql

导出指定库的表(仅数据)到本地的目录(例如mysql库的user表,带过滤条件)

mysqldump -u$USER -p$PASSWD -h127.0.0.1 -P3306 --routines --default-character-set=utf8 --no-create-db --no-create-info --tables mysql user --where="host='localhost'"> db.table.sql

导出某个库的所有表结构

mysqldump -u$USER -p$PASSWD -h127.0.0.1 -P3306 --routines --default-character-set=utf8 --no-data --databases mysql > db.nodata.sql

导出某个查询sql的数据为txt格式文件到本地的目录(各数据值之间用"制表符"分隔)
例如sql为'select user,host,password from mysql.user;'

mysql -u$USER -p$PASSWD -h127.0.0.1 -P3306 --default-character-set=utf8 --skip-column-names -B -e 'select user,host,password from mysql.user;' > mysql_user.txt

导出某个查询sql的数据为txt格式文件到MySQL服务器.
登录MySQL,将默认的制表符换成逗号.(适应csv格式文件).
指定的路径,mysql要有写的权限.最好用tmp目录,文件用完之后,再删除!
```
SELECT user,host,password FROM mysql.user INTO OUTFILE '/tmp/mysql_user.csv' FIELDS TERMINATED BY ',';
```

导入

恢复全库数据到MySQL,因为包含mysql库的权限表,导入完成需要执行FLUSH PRIVILEGES;命令

第一种方法:
mysql -u$USER -p$PASSWD -h127.0.0.1 -P3306 --default-character-set=utf8 < db.all.sql

第二种方法:
登录MySQL,执行source命令,后面的文件名要用绝对路径.
......
mysql> source /tmp/db.all.sql;

恢复某个库的数据(mysql库的user表)

第一种方法:
mysql -u$USER -p$PASSWD -h127.0.0.1 -P3306 --default-character-set=utf8 mysql < db.table.sql

第二种方法:
登录MySQL,执行source命令,后面的文件名要用绝对路径.
mysql -u$USER -p$PASSWD -h127.0.0.1 -P3306 --default-character-set=utf8
......
mysql> use mysql;
mysql> source /tmp/db.table.sql;

恢复MySQL服务器上面的txt格式文件(需要FILE权限,各数据值之间用"制表符"分隔)

mysql -u$USER -p$PASSWD -h127.0.0.1 -P3306 --default-character-set=utf8
......
mysql> use mysql;
mysql> LOAD DATA INFILE '/tmp/mysql_user.txt' INTO TABLE user ;

恢复MySQL服务器上面的csv格式文件(需要FILE权限,各数据值之间用"逗号"分隔)

mysql -u$USER -p$PASSWD -h127.0.0.1 -P3306 --default-character-set=utf8
......
mysql> use mysql;
mysql> LOAD DATA INFILE '/tmp/mysql_user.csv' INTO TABLE user FIELDS TERMINATED BY ',';

恢复本地的txt或csv文件到MySQL

mysql -u$USER -p$PASSWD -h127.0.0.1 -P3306 --default-character-set=utf8
......
mysql> use mysql;
# txt
mysql> LOAD DATA LOCAL INFILE '/tmp/mysql_user.csv' INTO TABLE user;
# csv
mysql> LOAD DATA LOCAL INFILE '/tmp/mysql_user.csv' INTO TABLE user FIELDS TERMINATED BY ',';

注意事项

关于MySQL连接
-u$USER 用户名
-p$PASSWD 密码
-h127.0.0.1 如果连接远程服务器,请用对应的主机名或者IP地址替换
-P3306 端口
--default-character-set=utf8 指定字符集
关于mysql参数
--skip-column-names 不显示数据列的名字
-B 以批处理的方式运行mysql程序.查询结果将显示为制表符间隔格式.
-e 执行命令后,退出
关于mysqldump参数
-A 全库备份
--routines 备份存储过程和函数
--default-character-set=utf8 设置字符集
--lock-all-tables 全局一致性锁
--add-drop-database 在每次执行建表语句之前,先执行DROP TABLE IF EXIST语句
--no-create-db 不输出CREATE DATABASE语句
--no-create-info 不输出CREATE TABLE语句
--databases 将后面的参数都解析为库名
--tables 第一个参数为库名后续为表名
关于LOAD DATA语法
如果LOAD DATA语句不带LOCAL关键字,就在MySQL的服务器上直接读取文件,且要具有FILE权限.
如果带LOCAL关键字,就在客户端本地读取数据文件,通过网络传到MySQL.
LOAD DATA语句,同样被记录到binlog,不过是内部的机制.