---

Brief intro to MySQL

---

• The CS377 SQL Java GUI application
  • I made a Java based GUI application available for you to access the MySQL database server

    ---

  • You can run the GUI application using this command:

    /home/cs377001/bin/cs377-sql [SIZE] SIZE = ss, s, m, l, or ll ss = tiny, s = small, m = medium, l = large, ll = huge (font size)

  ---

  ---

  ---

  ---

• Databases available in CS377
  • The company database (with the content from the text book) is available:

    Enter companyDB in the Database field Click SELECT to select the companyDB database

    ---

    ---

  • The CS377 SQL projects will use the spjDB database:

    Enter spjDB in the Database field Click SELECT to select the spjDB database

  ---

  ---

  ---

  ---

• Getting Information about databases and relations (tables) (i.e.: meta-information)
  • Each database system maintains a special set of tables inside a special "system database"

    This special "system database" is called the system catelog

    ---

  • The system catalog that store information about the user defined databases and its relations

    In other words:

    The meta information is also stored as a relation

    (well, after all. meta information is also information --- so why not store it in tables ? :-))

    In MySQL, the System Catelog is stored inside the database information_schema

    ---

    Try this:

    mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | <-- This mySQL database contains the meta-data | LDI | | companyDB | | spjDB | +--------------------+ 4 rows in set (0.00 sec) mysql> use information_schema; mysql> select * from SCHEMATA ; +--------------+--------------------+----------------------------+------------------------+----------+ | CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | +--------------+--------------------+----------------------------+------------------------+----------+ | def | information_schema | utf8 | utf8_general_ci | NULL | | def | LDI | latin1 | latin1_swedish_ci | NULL | | def | companyDB | latin1 | latin1_swedish_ci | NULL | | def | spjDB | latin1 | latin1_swedish_ci | NULL | +--------------+--------------------+----------------------------+------------------------+----------+ mysql> select * from TABLES; /// Huge amount of meta-data mysql> select TABLE_SCHEMA, TABLE_NAME from TABLES; /// Show only 2 attributes... +--------------------+---------------------------------------+ | TABLE_SCHEMA | TABLE_NAME | +--------------------+---------------------------------------+ | information_schema | CHARACTER_SETS | | information_schema | COLLATIONS | | information_schema | COLLATION_CHARACTER_SET_APPLICABILITY | | information_schema | COLUMNS | | information_schema | COLUMN_PRIVILEGES | | information_schema | ENGINES | | information_schema | EVENTS | | information_schema | FILES | | information_schema | GLOBAL_STATUS | | information_schema | GLOBAL_VARIABLES | | information_schema | KEY_COLUMN_USAGE | | information_schema | PARAMETERS | | information_schema | PARTITIONS | | information_schema | PLUGINS | | information_schema | PROCESSLIST | | information_schema | PROFILING | | information_schema | REFERENTIAL_CONSTRAINTS | | information_schema | ROUTINES | | information_schema | SCHEMATA | | information_schema | SCHEMA_PRIVILEGES | | information_schema | SESSION_STATUS | | information_schema | SESSION_VARIABLES | | information_schema | STATISTICS | | information_schema | TABLES | | information_schema | TABLESPACES | | information_schema | TABLE_CONSTRAINTS | | information_schema | TABLE_PRIVILEGES | | information_schema | TRIGGERS | | information_schema | USER_PRIVILEGES | | information_schema | VIEWS | | information_schema | INNODB_CMP_RESET | | information_schema | INNODB_TRX | | information_schema | INNODB_CMPMEM_RESET | | information_schema | INNODB_LOCK_WAITS | | information_schema | INNODB_CMPMEM | | information_schema | INNODB_CMP | | information_schema | INNODB_LOCKS | | LDI | course | | LDI | grade_report | | LDI | my_report | | LDI | student | | companyDB | department | | companyDB | dependent | | companyDB | dept_loc | | companyDB | employee | Tables in the companyDB ! | companyDB | project | | companyDB | test | | companyDB | test1 | | companyDB | works_on | | spjDB | part | | spjDB | proj | | spjDB | spj | | spjDB | supplier | +--------------------+---------------------------------------+

    ---

  • Gist of the story:

    You could use an SQL select command on the meta-information database infromation_schema to obtain information on the databases and relations stored in MySQL However: it's a royal pain in the butt....

    ---

    ---

  • MySQL provide built-in commands to access the meta-data (for the convenience of the user)

    We saw a few of them before:

    show databases list all databases in mySQL use DBName Set current DB to DBName show tables List the tables in the current DB SELECT DATABASE() Return the name of the current DB describe TableName Display the structure of table TableName