MySQL Reference


    Basics

  1. Introduction

    Commands

  2. User Management
  3. User Privileges
  4. Selectable Items

    Operations

  5. Update

    Appendices

  6. Credits

SQL User Privileges

Functions

  • Select - table. EX: "SELECT VERSION(), CURRENT_DATE;" Syntax:

    SELECT what
    FROM tablename
    WHERE conditions

    The SELECT condition allows selection of columns such as "colname1, colname2". The WHERE condition allows the selection of specific rows (items).

    EX: SELECT * FROM tablename
  • Insert - Create tables
  • Update - table
  • Delete - table
  • Index - Used to create or remove indexes for tables.
  • Alter - Used for tables, allows the use of the "ALTER TABLE" command.
  • Create - Make new database, table, or index.
  • Drop - Remove database or table.
  • Grant - Allows a user to give other users the privilege that they have. Used to grant access to databases or tables.
  • References - Database or table.
  • Reload - Have the database engine re-read the grant tables.
  • Shutdown
  • Process
  • File - Can read and write files on the SQL server using "SELECT...INTO OUTFILE" or "LOAD DATA INFILE".

Specific functions

  • LOAD
  • DATA
  • INFILE

Additional Commands

  • OPTIMIZE TABLE - Used after many changes have been made to a table.
  • ANALYZE TABLE - Analyze and store the table key distribution.
  • FLUSH - Need RELOAD privilege to use this. This command flushes any unsaved buffers to permanent storage. Syntax:

    FLUSH flush_option [,flush_option]

    Options:

    • HOSTS - Empty host cache table.
    • LOGS - Close, then open all log files.
    • PRIVILEGES - Reload privileges from the grant tables.
    • TABLES - Close all tables
    • STATUS - Used for debugging queries, it will clear status variables.
  • KILL - Can kill a connection thread to the MySQL server. Requires the PROCESS privilege to kill processes that are not the user's processes. The command syntax is "KILL thread_id".
  • SHOW (4.5.5) - Presents information about databases, tables, or columns. Options:
    • SHOW DATABASES - List databases
    • SHOW [OPEN] TABLES [FROM] databasename - List the tables in a database.
    • SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE wild] - Show columns in a table.
    • SHOW FIELDS - A synonym for SHOW COLUMNS.
    • SHOW INDEX FROM tbl_name [FROM db_name]
    • SHOW KEYS - A synonym for SHOW INDEX.
    • SHOW TABLE STATUS [FROM db_name] [LIKE wild] - Like SHOW TABLE, but with additional information.
    • SHOW STATUS [LIKE wild] - Show server information describing data sent and received..
    • SHOW VARIABLES [LIKE wild] - Shows some system variable values.
    • SHOW LOGS - Shows information about some log files.
    • SHOW [FULL] PROCESSLIST - Shows threads that are running which the user has control over or access to.
    • SHOW GRANTS FOR user - Shows the permissions that the user currently has by showing it as grant commands required to duplicate the grants.
    • SHOW CREATE TABLE table_name - Show a statement that will create a specified table.
    • SHOW MASTER STATUS
    • SHOW MASTER LOGS
    • SHOW SLAVE STATUS