• Mysql - shell脚本操作Mysql数据库

    目录

    知识点1：在linux系统里面使用shell来操作数据库

    一次多条语句查询

    知识点2：使用EOF方式操作数据库

    知识点3：在mysql里面使用Linux命令

    知识点4：mysql里面删除语句的区别

    delete 语句 和 truncate语句的区别

    面试题：将除了题目要求的三张表外的所有表的数据清空，编写一个shell脚本

     示例：建立测试环境：

     编写shell脚本

    知识点1：在linux系统里面使用shell来操作数据库

    1. [root@localhost ~]# mysql -u wangsh -p'123456' -e "show databases"
    2. mysql: [Warning] Using a password on the command line interface can be insecure.
    3. +--------------------+
    4. | Database           |
    5. +--------------------+
    6. | information_schema |
    7. | mysql              |
    8. | performance_schema |
    9. | sanchuang          |
    10. | student            |
    11. | sys                |
    12. | t1                 |
    13. | wangsh             |
    14. +--------------------+
    15. [root@localhost ~]# mysql -u wangsh -p'123456' -e "select host,user from mysql.user"
    16. mysql: [Warning] Using a password on the command line interface can be insecure.
    17. +---------------+---------------+
    18. | host          | user          |
    19. +---------------+---------------+
    20. | %             | liming        |
    21. | %             | shiyaling     |
    22. | %             | wangsh        |
    23. | %             | zhangj        |
    24. | 192.168.0.124 | liuhongjie    |
    25. | localhost     | mysql.session |
    26. | localhost     | mysql.sys     |
    27. | localhost     | root          |
    28. +---------------+---------------+
    29. [root@localhost ~]#

    一次多条语句查询

    1. [root@localhost ~]# mysql -u wangsh -p'123456' -e "show databases;select user,host from mysql.user"
    2. mysql: [Warning] Using a password on the command line interface can be insecure.
    3. +--------------------+
    4. | Database           |
    5. +--------------------+
    6. | information_schema |
    7. | mysql              |
    8. | performance_schema |
    9. | sanchuang          |
    10. | student            |
    11. | sys                |
    12. | t1                 |
    13. | wangsh             |
    14. +--------------------+
    15. +---------------+---------------+
    16. | user          | host          |
    17. +---------------+---------------+
    18. | liming        | %             |
    19. | shiyaling     | %             |
    20. | wangsh        | %             |
    21. | zhangj        | %             |
    22. | liuhongjie    | 192.168.0.124 |
    23. | mysql.session | localhost     |
    24. | mysql.sys     | localhost     |
    25. | root          | localhost     |
    26. +---------------+---------------+

     ############################################

    知识点2：使用EOF方式操作数据库

    一次进行多条sql语句的操作

    1. [root@localhost ~]# mysql -u wangsh -p'123456' <<EOF
    2. > show databases;
    3. > select host,user from mysql.user;
    4. > use mysql;
    5. > show tables;
    6. > EOF
    7. mysql: [Warning] Using a password on the command line interface can be insecure.
    8. Database
    9. information_schema
    10. mysql
    11. performance_schema
    12. sanchuang
    13. student
    14. sys
    15. t1
    16. wangsh
    17. host	user
    18. %	liming
    19. %	shiyaling
    20. %	wangsh
    21. %	zhangj
    22. 192.168.0.124	liuhongjie
    23. localhost	mysql.session
    24. localhost	mysql.sys
    25. localhost	root
    26. Tables_in_mysql
    27. columns_priv
    28. db
    29. engine_cost
    30. event
    31. func
    32. general_log
    33. gtid_executed
    34. help_category
    35. help_keyword
    36. help_relation
    37. help_topic
    38. innodb_index_stats
    39. innodb_table_stats
    40. ndb_binlog_index
    41. plugin
    42. proc
    43. procs_priv
    44. proxies_priv
    45. server_cost
    46. servers
    47. slave_master_info
    48. slave_relay_log_info
    49. slave_worker_info
    50. slow_log
    51. tables_priv
    52. time_zone
    53. time_zone_leap_second
    54. time_zone_name
    55. time_zone_transition
    56. time_zone_transition_type
    57. user

    ############################################ 

    知识点3：在mysql里面使用Linux命令

    在linux命令前面加上system

    1. wangsh@(none) 17:10  mysql>system ls;
    2. anaconda-ks.cfg  mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz  onekey_install_mysql_binary_v3.sh
    3. wangsh@(none) 17:10  mysql>system pwd;
    4. /root
    5. wangsh@(none) 17:11  mysql>

    ############################################ 

    知识点4：mysql里面删除语句的区别

    delete 语句 和 truncate语句的区别

    truncate适合删除大的表，速度非常快，可以理解为直接去清除表空间（表文件），但是不会产生日志二进制文件，不能通过日志去恢复，只能通过原来的备份去恢复

    delete语句删除数据非常慢，但是会产生二进制日志文件，可以恢复

    ############################################

    面试题：将除了题目要求的三张表外的所有表的数据清空，编写一个shell脚本

     示例：建立测试环境：

    1. wangsh@(none) 17:22  mysql>create database test;
    2. Query OK, 1 row affected (0.00 sec)
    3.  
    4. wangsh@(none) 17:22  mysql>use test;
    5. Database changed
    6. wangsh@test 17:22  mysql>create table tbllog_fan(id int);
    7. Query OK, 0 rows affected (0.01 sec)
    8.  
    9. wangsh@test 17:23  mysql>create table tbllog_liu(id int);
    10. Query OK, 0 rows affected (0.00 sec)
    11.  
    12. wangsh@test 17:23  mysql>create table tbllog_zhang(id int);
    13. Query OK, 0 rows affected (0.01 sec)
    14.  
    15. wangsh@test 17:23  mysql>create table tbllog_wang(id int);
    16. Query OK, 0 rows affected (0.00 sec)
    17.  
    18. wangsh@test 17:23  mysql>create table tbllog_pay(id int);
    19. Query OK, 0 rows affected (0.01 sec)
    20.  
    21. wangsh@test 17:24  mysql>create table tbllog_role(id int);
    22. Query OK, 0 rows affected (0.01 sec)
    23.  
    24. wangsh@test 17:24  mysql>create table tbllog_online(id int);
    25. Query OK, 0 rows affected (0.01 sec)
    26.  
    27. wangsh@test 17:24  mysql>show tables ;
    28. +----------------+
    29. | Tables_in_test |
    30. +----------------+
    31. | tbllog_fan     |
    32. | tbllog_liu     |
    33. | tbllog_online  |
    34. | tbllog_pay     |
    35. | tbllog_role    |
    36. | tbllog_wang    |
    37. | tbllog_zhang   |
    38. +----------------+
    39. 7 rows in set (0.00 sec)
    40.  
    41. wangsh@test 17:24  mysql>insert into tbllog_fan(id) values(1),(2),(3);
    42. Query OK, 3 rows affected (0.00 sec)
    43. Records: 3  Duplicates: 0  Warnings: 0
    44.  
    45. wangsh@test 17:25  mysql>insert into tbllog_liu(id) values(1),(2),(3);
    46. Query OK, 3 rows affected (0.01 sec)
    47. Records: 3  Duplicates: 0  Warnings: 0
    48.  
    49. wangsh@test 17:25  mysql>insert into tbllog_online(id) values(1),(2),(3);
    50. Query OK, 3 rows affected (0.00 sec)
    51. Records: 3  Duplicates: 0  Warnings: 0
    52.  
    53. wangsh@test 17:25  mysql>insert into tbllog_pay(id) values(1),(2),(3);
    54. Query OK, 3 rows affected (0.01 sec)
    55. Records: 3  Duplicates: 0  Warnings: 0
    56.  
    57. wangsh@test 17:25  mysql>insert into tbllog_role(id) values(1),(2),(3);
    58. Query OK, 3 rows affected (0.01 sec)
    59. Records: 3  Duplicates: 0  Warnings: 0
    60.  
    61. wangsh@test 17:26  mysql>insert into tbllog_wang(id) values(1),(2),(3);
    62. Query OK, 3 rows affected (0.00 sec)
    63. Records: 3  Duplicates: 0  Warnings: 0
    64.  
    65. wangsh@test 17:26  mysql>insert into tbllog_zhang(id) values(1),(2),(3);
    66. Query OK, 3 rows affected (0.01 sec)
    67. Records: 3  Duplicates: 0  Warnings: 0

    查看建立的环境：

    1. root@test 17:43  mysql>show tables;
    2. +----------------+
    3. | Tables_in_test |
    4. +----------------+
    5. | tbllog_fan     |
    6. | tbllog_liu     |
    7. | tbllog_online  |
    8. | tbllog_pay     |
    9. | tbllog_role    |
    10. | tbllog_wang    |
    11. | tbllog_zhang   |
    12. +----------------+
    13. 7 rows in set (0.00 sec)
    14.  
    15. root@test 17:43  mysql>select * from tbllog_fan;
    16. +------+
    17. | id   |
    18. +------+
    19. |    1 |
    20. |    2 |
    21. |    3 |
    22. +------+
    23. 3 rows in set (0.00 sec)

     编写shell脚本

    1. [root@localhost lianxi]# cat truncate_tables.sh 
    2. #! /bin/bash
    3.  
    4. for i in $(mysql -uwangsh -p'123456' -e "use test;show tables" 2> /dev/null|tail -n +2|egrep -v  "^tbllog_pay|^tbllog_online|^tbllog_role")
    5. do
    6. 	mysql -u wangsh -p'123456' -e "truncate table test.$i"
    7. 	echo "成功删除$i表"
    8. done
    9.

    执行结果

    1. [root@localhost lianxi]# bash truncate_tables.sh 
    2. mysql: [Warning] Using a password on the command line interface can be insecure.
    3. 成功删除tbllog_fan表
    4. mysql: [Warning] Using a password on the command line interface can be insecure.
    5. 成功删除tbllog_liu表
    6. mysql: [Warning] Using a password on the command line interface can be insecure.
    7. 成功删除tbllog_wang表
    8. mysql: [Warning] Using a password on the command line interface can be insecure.
    9. 成功删除tbllog_zhang表

    可以看到，除了规定的三张表，其他表的内容都被清空了。 

    1. root@test 17:43  mysql>select * from tbllog_fan;
    2. Empty set (0.00 sec)
    3.  
    4. root@test 17:45  mysql>select * from tbllog_liu;
    5. Empty set (0.00 sec)
    6.  
    7. root@test 17:45  mysql>select * from tbllog_role;
    8. +------+
    9. | id   |
    10. +------+
    11. |    1 |
    12. |    2 |
    13. |    3 |
    14. +------+
    15. 3 rows in set (0.00 sec)

  • 相关阅读:
    CTFHUB web-hate_php
    干掉Session？这个跨域认证解决方案真的优雅！
    计算机毕业设计之java+ssm基于web的校园短期闲置资源置换平台
    Spring5学习笔记08--Scope
    C. Crossword Validation(字典树)
    Linux手动更新时间Linux同步集群其他节点时间
    ES6 入门教程 6 正则的扩展 6.13 d 修饰符：正则匹配索引 & 6.14 String.prototype.matchAll()
    window下Vscode配置 git 为终端
    【开发者必看】【push kit】推送服务典型问题合集3
    phpstrom 快速注释：
  • 原文地址：https://blog.csdn.net/qq_48391148/article/details/126288517