MySQL query stored procedure view trigger function index table building statement database version current login user current database name

Time:2021-4-16

MySQL query stored procedure view trigger function index table building statement database version current login user current database name

 

INFORMATION_SCHEMA.TABLES

INFORMATION_SCHEMA.TABLESMySQL is a built-in (regarded as) information database, which stores the information of other databases maintained by MySQL server.INFORMATION_SCHEMA.TABLESThere are several read-only tables, which are actually views, not basic tables, so you can’t see any related files. INFORMATION_ SCHEMA.TABLES See here for various field names in

  • Query (basic) table method 1

    SELECT TABLE_ NAME FROM INFORMATION_ SCHEMA.TABLES  WHERE TABLE_ Schema ='Database name 'and table_ TYPE = 'BASE TABLE';

    Return examples:

    mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_TYPE = 'BASE TABLE';
    +------------+
    | TABLE_NAME |
    +------------+
    | t          |
    +------------+
    1 row in set (0.00 sec)

    Query (basic) table method 2

    USE TABLE xxx
    SHOW TABLES;

    Return examples:

    mysql> show tables;
    +----------------+
    | Tables_in_test |
    +----------------+
    | t              |
    +----------------+
    1 row in set (0.00 sec)
  • Query view

    SELECT TABLE_ NAME FROM INFORMATION_ SCHEMA.TABLES  WHERE TABLE_ Schema ='Database name 'and table_ TYPE = 'VIEW';
  • Query all triggers

    SELECT * FROM information_schema.`triggers`;

    Test results show that information_ Schema also follows the principle of “Linux MySQL is not case sensitive”

     

mysql.proc

  • Query all functions

    SELECT name FROM  mysql.proc  Where DB ='Database name 'and type ='fund';
  • Query all stored procedures

    SELECT name FROM  mysql.proc  Where DB ='Database name 'and type ='procedure';

    testing

    mysql> SELECT name FROM mysql.proc WHERE db='test' and type='procedure';
    +-------------------+
    | name              |
    +-------------------+
    | add_sum           |
    | add_sum_procedure |
    | findByNums        |
    +-------------------+
    3 rows in set (0.00 sec)

     

Mysql.user

  • Query database users

 

Current version, user, database

mysql>SELECT VERSION(),USER(),DATABASE();

#Linux capital V
mysql -V

 

Query table

  • Query all indexes in the table

    SELECT INDEX FROM table_name;
  • Query the statement of creating table

    SELECT CREATE TABLE table_name;

    (0701)

  • View all user permissions (0716)

    select * from mysql.user where user = 'user_name';
    perhaps
    show grants for user_name;

 

Recommended Today

Review of SQL Sever basic command

catalogue preface Installation of virtual machine Commands and operations Basic command syntax Case sensitive SQL keyword and function name Column and Index Names alias Too long to see? Space Database connection Connection of SSMS Connection of command line Database operation establish delete constraint integrity constraint Common constraints NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY DEFAULT […]