Databasesyou cant modify structure and data of

This preview shows page 15 - 18 out of 54 pages.

databases.You can't modify structure and data of information_schema . You can only query the tables. Many information_schema tables provide the same data you can retrieve with a SHOW statement. While using SHOW commands is faster (the server responds much faster and you type less characters), the information_schema provides a more flexible way to obtain and organize the metadata. List databases The INFORMATION_SCHEMA table containing the databases information is SCHEMATA. The following SQL commands provide information about the databases located on the current server. Show all databases:
SHOW DATABASES; The SCHEMA keywords can be used in place of DATABASES. MySQL doesn't support standard SQL SCHEMAs, so SCHEMA is a synonym of database. It has been added for compatibility with other DBMSs. Add a filter on the databases names SHOW DATABASES LIKE 'pattern' ; The LIKE operator here works as in normal SELECTs or DML statements. So you can list all databases whose name starts with 'my': SHOW DATABASES LIKE 'my%' ; Add complex filters You can add more complex filters using the WHERE clause: SHOW DATABASES WHERE conditions; WHERE clause allows you to use regular expressions, '=', '<' and '>' operators, string functions or other useful expressions to filter the records returned by SHOW DATABASES. List tables and views The following SQL commands provide information about the tables and views contained in a database. The INFORMATION_SCHEMA tables containing this information are `TABLES` and `VIEWS`. Since the following statements provide very little information about views, if you need to get metadata about them you'll probably prefer to query the VIEWS table. The mysqlshow command line tool can be used instead. Show all tables USE ` database ` ; SHOW TABLES; SHOW TABLES FROM ` database ` ; The two forms shown above are equivalent.
Apply a filter You can apply a filter to the tables names, to show only tables whose name match a pattern. You can use the LIKE operators, as you do in SELECTs or in the DML statements: SHOW TABLES LIKE ` pattern ` Also, you can apply a more complex filter to any column returned by the SHOW TABLES command using the WHERE clause: SHOW TABLES WHERE condition; Extra info By default, SHOW TABLES returns only one column containing the name of the table. You can get extra information by using the FULL keyword: SHOW FULL TABLES; This will add a column called `Table_type`. This can have three values: 'BASE TABLE' for tables, 'VIEW' for views and 'SYSTEM VIEW' for special tables created by the server (normally used only INFORMATION_SCHEMA tables). So you can only list tables: SHOW FULL TABLES WHERE ` Table_type `= 'BASE TABLE' Or, you can only list views: SHOW FULL TABLES WHERE ` Table_type `= 'VIEW' Show only open tables You can get a list of the non-temporary tables (not views) which are open in the cache: SHOW OPEN TABLES; This command has the same parameters as SHOW TABLES, except for FULL (useless in this case). You can't get this information from the INFORMATION_SCHEMA. List fields ; ; ;

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture