1、show命令，这个命令用于提供有关数据库、表、字段或关于server状态的信息的，如，显示数据库
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | yuanqk | | yuanqk_gbk | | yuanqk_utf8 | +--------------------+ 7 rows in set (0.00 sec) mysql>
yuanqk、yuanqk_gbk\utf8是我刚才创建的数据库，到操作系统上看看都创建了哪些东西，我的数据存放目录在/data/3306/data下
[root@mysql data]# pwd
/data/3306/data
[root@mysql data]# ls -lrt
total 141336
drwx------. 2 mysql mysql 4096 May 17 23:32 test
drwx------. 2 mysql mysql 4096 May 17 23:32 performance_schema
drwx------. 2 mysql mysql 4096 May 17 23:32 mysql
-rw-rw----. 1 mysql mysql 5242880 May 17 23:35 ib_logfile1
-rw-rw----. 1 mysql mysql 134217728 May 18 19:38 ibdata1
-rw-rw----. 1 mysql mysql 5242880 May 19 10:40 ib_logfile0
drwx------. 2 mysql mysql 4096 May 19 10:53 yuanqk <===对应的三个目录
drwx------. 2 mysql mysql 4096 May 19 10:58 yuanqk_gbk <===对应的三个目录
drwx------. 2 mysql mysql 4096 May 19 11:03 yuanqk_utf8 <===对应的三个目录
[root@mysql data]# cd yuanqk
[root@mysql yuanqk]# ls -l
total 4
-rw-rw----. 1 mysql mysql 65 May 19 10:53 db.opt <===这个应该就是数据文件了
[root@mysql yuanqk]# cd ../yuanqk_gbk/
[root@mysql yuanqk_gbk]# ls -l
total 4
-rw-rw----. 1 mysql mysql 59 May 19 10:58 db.opt
[root@mysql yuanqk_gbk]#
----------------显示有哪些表------------
mysql> show tables;
Empty set (0.00 sec) <===显示是空的，因为还没连接到数据库中，不过可以使用from或in关键字来显示其他数据库的信息
mysql> show tables from mysql;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.00 sec)
mysql>
mysql>
mysql> show tables in mysql;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.00 sec)
mysql>
也可以使用use连接到数据库后，再使用show命令，show有很多命令，可以通过help show查看
2、连接数据库，这个简单，就是用use database，这个跟sqlserver是一样的
mysql> select database(); <===看一下当前在哪个数据库中，显示为NULL +------------+ | database() | +------------+ | NULL | +------------+ 1 row in set (0.00 sec)
--随便连接一个
mysql> use yuanqk
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| yuanqk |
+------------+
1 row in set (0.00 sec)
mysql> use yuanqk_gbk
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| yuanqk_gbk |
+------------+
1 row in set (0.00 sec)
mysql>
3、删除数据库，这个也很简单，drop database dbname
好，上面有个test数据库，看着很别扭，删掉 mysql> drop database test; Query OK, 0 rows affected (0.09 sec) mysql> show databases; <===通过show命令已经看不到test了 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | yuanqk | | yuanqk_gbk | | yuanqk_utf8 | +--------------------+ 6 rows in set (0.00 sec) mysql>
-----看看操作系统上有什么变化----------
[root@mysql yuanqk_gbk]# cd ../
[root@mysql data]# ls -lrt
total 141332
drwx------. 2 mysql mysql 4096 May 17 23:32 performance_schema <=====整个目录都没有了，如果没有备份，估计只能连夜跑路了
drwx------. 2 mysql mysql 4096 May 17 23:32 mysql
-rw-rw----. 1 mysql mysql 5242880 May 17 23:35 ib_logfile1
drwx------. 2 mysql mysql 4096 May 19 10:53 yuanqk
drwx------. 2 mysql mysql 4096 May 19 10:58 yuanqk_gbk
drwx------. 2 mysql mysql 4096 May 19 11:03 yuanqk_utf8
-rw-rw----. 1 mysql mysql 134217728 May 19 12:17 ibdata1
-rw-rw----. 1 mysql mysql 5242880 May 19 12:17 ib_logfile0
[root@mysql data]#
4、函数相关，database()就是个函数，还有以下这些，我仅知道这4个，还是刚刚学的。
mysql> select version(); +------------+ | version() | +------------+ | 5.5.60-log | +------------+ 1 row in set (0.00 sec) mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql> select now(); +---------------------+ | now() | +---------------------+ | 2018-05-19 12:20:30 | +---------------------+ 1 row in set (0.04 sec) mysql>
5、刚才在登录数据库时不小心多了个空格，却使我掌握了一种登录方法，赚到了。。。。
[root@mysql mysql]# mysql -uroot -p yuanqk -S /data/3306/mysql.sock <=== -p和yuanqk之间不小心多敲了个空格，原本-pyuanqk是要代表root的密码的 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.5.60-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, 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. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> <===经过一系列的操作后，在使用select database()时，意外发现，我什么时候连接到yuanqk这个库中了？于是，发现了上面多了个空格的问题，于是，我掌握了一套直接连接mysql数据库的方法。。。。。
mysql> select database();
+------------+
| database() |
+------------+
| yuanqk |
+------------+
1 row in set (0.00 sec)
mysql> exit
Bye
[root@mysql mysql]# mysql -uroot -pyuanqk yuanqk -S /data/3306/mysql.sock <===连接yuanqk数据库
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.60-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select database();
+------------+
| database() |
+------------+
| yuanqk |
+------------+
1 row in set (0.00 sec)
mysql> exit
Bye
[root@mysql mysql]# mysql -uroot -pyuanqk yuanqk_gbk -S /data/3306/mysql.sock <===连接yuanqk_gbk数据库
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.5.60-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select database();
+------------+
| database() |
+------------+
| yuanqk_gbk |
+------------+
1 row in set (0.00 sec)
mysql>