进阶-7.管理工具

1.系统数据库

2常用工具

2.1 mysql

C:\Users\Tracy>mysql -uroot -p123456 test -e "select * from student";
+----+---------+-------+
| id | name    | no    |
+----+---------+-------+
|  1 | 11      | 20001 |
|  2 | tracy   | 20002 |
|  3 | janms   | 20003 |
|  4 | qiaodan | 20004 |
|  7 | tom     | NULL  |
| 13 | tom     | NULL  |
| 14 | tom     | NULL  |
| 16 | tom     | NULL  |
| 17 | tom     | NULL  |
| 18 | tom     | NULL  |
+----+---------+-------+

2.2 mysqladmin

C:\Users\Tracy>mysqladmin -uroot -p123456 version
mysqladmin  Ver 8.42 Distrib 5.5.22, for Win32 on x86
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version          5.5.22
Protocol version        10
Connection              localhost via TCP/IP
TCP port                3306
Uptime:                 10 hours 28 min 25 sec

Threads: 3  Questions: 50  Slow queries: 0  Opens: 181  Flush tables: 1  Open tables: 62  Queries per second avg: 0.001

2.3 mysqlbinlog

2.4 mysqlshow

D:\>mysqlshow -uroot -p123456 --count
+--------------------+--------+--------------+
|     Databases      | Tables |  Total Rows  |
+--------------------+--------+--------------+
| information_schema |     37 |         3032 |
| demo               |      0 |            0 |
| mysql              |     24 |         2050 |
| performance_schema |     17 |           14 |
| test               |     27 |          139 |
| tracy              |      3 |            2 |
+--------------------+--------+--------------+
6 rows in set.

D:\>mysqlshow -uroot -p123456 test --count
Database: test
+--------------------+----------+------------+
|       Tables       | Columns  | Total Rows |
+--------------------+----------+------------+
| course             |        2 |          4 |
| department         |        4 |          0 |
| employer           |       10 |          0 |
| fan_news           |        3 |          2 |
| jobs               |        4 |          0 |
| location           |        6 |          0 |
| medcine_dept       |        5 |          3 |
| medcine_doctor     |        8 |          3 |
| medcine_drug       |        6 |          2 |
| medcine_login      |        4 |          5 |
| medcine_user       |        8 |         10 |
| stu                |        2 |          0 |
| stu_v_1            |        2 |         10 |
| stu_v_2            |        2 |          5 |
| stu_v_3            |        2 |          2 |
| stu_v_4            |        2 |          7 |
| stu_v_5            |        2 |          2 |
| stu_v_6            |        2 |          5 |
| student            |        3 |         10 |
| student_course     |        3 |          4 |
| stuinfo            |        2 |          3 |
| tb_stu_cource_view |        3 |          4 |
| tb_stu_course_view |        3 |          4 |
| tb_user            |        9 |         24 |
| tb_user_view       |        7 |         24 |
| user               |        4 |          3 |
| user_order         |        9 |          3 |
+--------------------+----------+------------+
27 rows in set.


D:\>mysqlshow -uroot -p123456 test course --count
Database: test  Table: course  Rows: 4
+-------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type        | Collation       | Null | Key | Default | Extra          | Privileges                      | Comment |
+-------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| id    | int(11)     |                 | NO   | PRI |         | auto_increment | select,insert,update,references | ID      |
| name  | varchar(10) | utf8_general_ci | YES  |     |         |                | select,insert,update,references | 姓名    |
+-------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+

D:\>mysqlshow -uroot -p123456 test course id --count
Database: test  Table: course  Rows: 4  Wildcard: id
+-------+---------+-----------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type    | Collation | Null | Key | Default | Extra          | Privileges                      | Comment |
+-------+---------+-----------+------+-----+---------+----------------+---------------------------------+---------+
| id    | int(11) |           | NO   | PRI |         | auto_increment | select,insert,update,references | ID      |
+-------+---------+-----------+------+-----+---------+----------------+---------------------------------+---------+

D:\>mysqlshow -uroot -p123456 test course -i
Database: test  Wildcard: course
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name   | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| course | InnoDB | 10      | Compact    | 4    | 4096           | 16384       | 0               | 0            | 6291456   | 5              | 2024-08-13 23:19:01 |             |            | utf8_general_ci |          |                | 课程表  |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

D:\>mysqlshow -uroot -p123456 test course id -i
Database: test  Table: course  Wildcard: id
+-------+---------+-----------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type    | Collation | Null | Key | Default | Extra          | Privileges                      | Comment |
+-------+---------+-----------+------+-----+---------+----------------+---------------------------------+---------+
| id    | int(11) |           | NO   | PRI |         | auto_increment | select,insert,update,references | ID      |
+-------+---------+-----------+------+-----+---------+----------------+---------------------------------+---------+

2.5 mysqldump

D:\>mysqldump -uroot -p123456 test >test.sql

D:\>mysqldump -uroot -p123456 test -t >test01.sql#不包含创表语句

D:\>mysqldump -uroot -p123456 test -d >test03.sql #不包含数据

D:\>mysqldump -uroot -p123456  -T D:\ test course #生成了course.sql创建表 couser.txt数据表
D:\>mysqldump -uroot -p123456 -T  D:\ test user

2.6mysqlimport /source

D:\>mysqlimport -uroot -p12345 test D:\user.txt
test.user: Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> source D:\user.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)