A continuación se escriben algunos de los comandos básicos que usamos cuando estamos trabajando con una base de datos MySQL.
Como ver las bases de datos
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| soporte |
| test |
+--------------------+
4 rows in set (0.00 sec)
Cambiarse de base de datos
mysql> use soporte;
Database changed
Ver las tablas que tiene una base de datos
mysql> show tables from soporte;
+-------------------+
| Tables_in_soporte |
+-------------------+
| usuarios |
+-------------------+
1 row in set (0.00 sec)
Ver las columnas de una tabla
mysql> show columns from usuarios;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
| nombre | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
Ver los usuarios conectados
mysql> show processlist;
+----+------+----------------+---------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+----------------+---------+---------+------+-------+------------------+
| 7 | root | localhost:1025 | soporte | Query | 0 | NULL | show processlist |
+----+------+----------------+---------+---------+------+-------+------------------+
1 row in set (0.00 sec)
Ver los índices de una tabla
mysql> show index from usuarios;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| usuarios | 1 | id | 1 | id | A | 0 | NULL | NULL | YES | BTREE | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)
Ver los permisos de el usuario que estoy usando
Puede usarse cualquiera de los siguientes
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();
mysql> show grants for current_user;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*300D4364355A407C0713CDCC4B1C77402F2481DB' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Ver los permisos de un usuario en especifico
mysql> show grants for 'root'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*300D4364355A407C0713CDCC4B1C77402F2481DB' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)