mysql> show databases; +--------------------+ | Database | +--------------------+ | c4333u | | clystms | | experiment | | information_schema | | mysql | | performance_schema | | sakila | | supply | | swim | | sys | | temp2 | | toyu | | universal | +--------------------+ 13 rows in set (0.01 sec) mysql> SELECT * -> FROM information_schema.tables -> LIMIt 5; +---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT | +---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------------+ | def | c4333u | class | BASE TABLE | InnoDB | 10 | Dynamic | 7 | 2340 | 16384 | 0 | 32768 | 0 | NULL | 2019-03-20 16:44:55 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | | | def | c4333u | department | BASE TABLE | InnoDB | 10 | Dynamic | 6 | 2730 | 16384 | 0 | 16384 | 0 | NULL | 2019-03-20 16:44:55 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | | | def | c4333u | dept_ex1 | VIEW | NULL | NULL | NULL | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2019-03-25 13:58:22 | NULL | NULL | NULL | NULL | NULL | VIEW | | def | c4333u | dept_sum | VIEW | NULL | NULL | NULL | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2019-03-25 17:15:07 | NULL | NULL | NULL | NULL | NULL | VIEW | | def | c4333u | enroll | BASE TABLE | InnoDB | 10 | Dynamic | 16 | 1024 | 16384 | 0 | 16384 | 0 | NULL | 2019-03-20 16:44:56 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | | +---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------------+ 5 rows in set (0.08 sec) mysql> SELECT t.* -> FROM information_schema.tables t -> WHERE t.TABLE_SCHEMA = 'sakila'; +---------------+--------------+----------------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+-------------------------------------------------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT | +---------------+--------------+----------------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+-------------------------------------------------------+ | def | sakila | actor | BASE TABLE | InnoDB | 10 | Dynamic | 200 | 81 | 16384 | 0 | 16384 | 0 | 200 | 2019-01-08 17:11:45 | NULL | NULL | utf8_general_ci | NULL | | | | def | sakila | actor_info | VIEW | NULL | NULL | NULL | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2019-01-08 17:11:48 | NULL | NULL | NULL | NULL | NULL | VIEW | | def | sakila | address | BASE TABLE | InnoDB | 10 | Dynamic | 603 | 163 | 98304 | 0 | 16384 | 0 | 605 | 2019-01-08 17:11:45 | NULL | NULL | utf8_general_ci | NULL | | 'sakila.actor_info' is not BASE TABLE | | def | sakila | category | BASE TABLE | InnoDB | 10 | Dynamic | 16 | 1024 | 16384 | 0 | 0 | 0 | 16 | 2019-01-08 17:11:45 | NULL | NULL | utf8_general_ci | NULL | | 'sakila.actor_info' is not BASE TABLE | | def | sakila | city | BASE TABLE | InnoDB | 10 | Dynamic | 600 | 81 | 49152 | 0 | 16384 | 0 | 600 | 2019-01-08 17:11:45 | NULL | NULL | utf8_general_ci | NULL | | 'sakila.actor_info' is not BASE TABLE | | def | sakila | country | BASE TABLE | InnoDB | 10 | Dynamic | 109 | 150 | 16384 | 0 | 0 | 0 | 109 | 2019-01-08 17:11:45 | NULL | NULL | utf8_general_ci | NULL | | 'sakila.actor_info' is not BASE TABLE | | def | sakila | customer | BASE TABLE | InnoDB | 10 | Dynamic | 599 | 136 | 81920 | 0 | 49152 | 0 | 599 | 2019-01-08 17:11:45 | NULL | NULL | utf8_general_ci | NULL | | 'sakila.actor_info' is not BASE TABLE | | def | sakila | customer_list | VIEW | NULL | NULL | NULL | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2019-01-08 17:11:48 | NULL | NULL | NULL | NULL | NULL | VIEW | | def | sakila | film | BASE TABLE | InnoDB | 10 | Dynamic | 1000 | 196 | 196608 | 0 | 81920 | 0 | 1001 | 2019-01-08 17:11:46 | NULL | NULL | utf8_general_ci | NULL | | 'sakila.customer_list' is not BASE TABLE | | def | sakila | film_actor | BASE TABLE | InnoDB | 10 | Dynamic | 5462 | 35 | 196608 | 0 | 81920 | 0 | NULL | 2019-01-08 17:11:46 | NULL | NULL | utf8_general_ci | NULL | | 'sakila.customer_list' is not BASE TABLE | | def | sakila | film_category | BASE TABLE | InnoDB | 10 | Dynamic | 1000 | 65 | 65536 | 0 | 16384 | 0 | NULL | 2019-01-08 17:11:46 | NULL | NULL | utf8_general_ci | NULL | | 'sakila.customer_list' is not BASE TABLE | | def | sakila | film_list | VIEW | NULL | NULL | NULL | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2019-01-08 17:11:48 | NULL | NULL | NULL | NULL | NULL | VIEW | | def | sakila | film_text | BASE TABLE | InnoDB | 10 | Dynamic | 1000 | 180 | 180224 | 0 | 16384 | 0 | NULL | 2019-01-08 17:11:46 | NULL | NULL | utf8_general_ci | NULL | | 'sakila.film_list' is not BASE TABLE | | def | sakila | inventory | BASE TABLE | InnoDB | 10 | Dynamic | 4581 | 39 | 180224 | 0 | 196608 | 0 | 4582 | 2019-01-08 17:11:47 | NULL | NULL | utf8_general_ci | NULL | | 'sakila.film_list' is not BASE TABLE | | def | sakila | language | BASE TABLE | InnoDB | 10 | Dynamic | 6 | 2730 | 16384 | 0 | 0 | 0 | 6 | 2019-01-08 17:11:47 | NULL | NULL | utf8_general_ci | NULL | | 'sakila.film_list' is not BASE TABLE | | def | sakila | nicer_but_slower_film_list | VIEW | NULL | NULL | NULL | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2019-01-08 17:11:48 | NULL | NULL | NULL | NULL | NULL | VIEW | | def | sakila | payment | BASE TABLE | InnoDB | 10 | Dynamic | 16086 | 98 | 1589248 | 0 | 638976 | 0 | 16049 | 2019-01-08 17:11:47 | NULL | NULL | utf8_general_ci | NULL | | 'sakila.nicer_but_slower_film_list' is not BASE TABLE | | def | sakila | rental | BASE TABLE | InnoDB | 10 | Dynamic | 16005 | 99 | 1589248 | 0 | 1196032 | 4194304 | 16050 | 2019-01-08 17:11:47 | NULL | NULL | utf8_general_ci | NULL | | 'sakila.nicer_but_slower_film_list' is not BASE TABLE | | def | sakila | s19t2v | VIEW | NULL | NULL | NULL | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2019-03-05 17:03:30 | NULL | NULL | NULL | NULL | NULL | VIEW | | def | sakila | s20v1 | VIEW | NULL | NULL | NULL | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2020-01-09 20:02:54 | NULL | NULL | NULL | NULL | NULL | VIEW | | def | sakila | sales_by_film_category | VIEW | NULL | NULL | NULL | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2019-01-08 17:11:48 | NULL | NULL | NULL | NULL | NULL | VIEW | | def | sakila | sales_by_store | VIEW | NULL | NULL | NULL | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2019-01-08 17:11:48 | NULL | NULL | NULL | NULL | NULL | VIEW | | def | sakila | staff | BASE TABLE | InnoDB | 10 | Dynamic | 2 | 32768 | 65536 | 0 | 32768 | 0 | 2 | 2019-01-08 17:11:48 | NULL | NULL | utf8_general_ci | NULL | | 'sakila.sales_by_store' is not BASE TABLE | | def | sakila | staff_list | VIEW | NULL | NULL | NULL | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2019-01-08 17:11:48 | NULL | NULL | NULL | NULL | NULL | VIEW | | def | sakila | store | BASE TABLE | InnoDB | 10 | Dynamic | 2 | 8192 | 16384 | 0 | 32768 | 0 | 2 | 2019-01-08 17:11:48 | NULL | NULL | utf8_general_ci | NULL | | 'sakila.staff_list' is not BASE TABLE | +---------------+--------------+----------------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+-------------------------------------------------------+ 25 rows in set (0.01 sec) mysql> mysql> SELECT Count(t.*) AS `Number of views in Sakila` -> FROM information_schema.tables t -> WHERE t.TABLE_SCHEMA = 'sakila' -> AND t.TABLE_TYPE = 'VIEW'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) AS `Number of views in Sakila` FROM information_schema.tables t WHERE t.TABLE' at line 1 mysql> mysql> SELECT Count(t.TABLE_NAME) AS `Number of views in Sakila` -> FROM information_schema.tables t -> WHERE t.TABLE_SCHEMA = 'sakila' -> AND t.TABLE_TYPE = 'VIEW'; +---------------------------+ | Number of views in Sakila | +---------------------------+ | 9 | +---------------------------+ 1 row in set (0.00 sec) mysql> -- Number of databases and their number of tables. mysql> SELECT t.TABLE_SCHEMA AS Database -> COUNT(t.TABLE_NAME) AS `Number of tables` -> FROM information_schema.tables t -> WHERE t.TABLE_TYPE = 'BASE TABLE' -> GROUP BY t.TABLE_SCHEMA -> ORDER BY `Number of tables` DESC; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Database COUNT(t.TABLE_NAME) AS `Number of tables` FROM information_schema.t' at line 1 mysql> -- Number of databases and their number of tables. mysql> SELECT t.TABLE_SCHEMA AS Database, -> COUNT(t.TABLE_NAME) AS `Number of tables` -> FROM information_schema.tables t -> WHERE t.TABLE_TYPE = 'BASE TABLE' -> GROUP BY t.TABLE_SCHEMA -> ORDER BY `Number of tables` DESC; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Database, COUNT(t.TABLE_NAME) AS `Number of tables` FROM information_schema.' at line 1 mysql> -- Number of databases and their number of tables. mysql> SELECT t.TABLE_SCHEMA AS `Database`, -> COUNT(t.TABLE_NAME) AS `Number of tables` -> FROM information_schema.tables t -> WHERE t.TABLE_TYPE = 'BASE TABLE' -> GROUP BY t.TABLE_SCHEMA -> ORDER BY `Number of tables` DESC; +--------------------+------------------+ | Database | Number of tables | +--------------------+------------------+ | performance_schema | 102 | | mysql | 33 | | sakila | 16 | | clystms | 14 | | swim | 13 | | toyu | 8 | | c4333u | 5 | | supply | 3 | | experiment | 3 | | universal | 2 | | sys | 1 | +--------------------+------------------+ 11 rows in set (0.01 sec) mysql> DROP VIEW customer_payment; ERROR 1051 (42S02): Unknown table 'sakila.customer_payment' mysql> mysql> CREATE DEFINER=CURRENT_USER SQL SECURITY INVOKER VIEW customer_payment -> AS -> SELECT -> p.customer_id AS customer, -> sum(p.amount) AS "Total Payment", -> sum(IF(p.rental_id IS NULL, 0, p.amount)) AS "Film Rental Payment" -> FROM payment as p -> GROUP BY p.customer_id; Query OK, 0 rows affected (0.10 sec) mysql> mysql> DROP VIEW customer_payment; Query OK, 0 rows affected (0.09 sec) mysql> mysql> CREATE VIEW customer_payment -> AS -> SELECT -> p.customer_id AS customer, -> sum(p.amount) AS "Total Payment", -> sum(IF(p.rental_id IS NULL, 0, p.amount)) AS "Film Rental Payment" -> FROM payment as p -> GROUP BY p.customer_id; Query OK, 0 rows affected (0.03 sec) mysql> SELECT * FROM customer_payment LIMIT 5; +----------+---------------+---------------------+ | customer | Total Payment | Film Rental Payment | +----------+---------------+---------------------+ | 1 | 118.68 | 118.68 | | 2 | 128.73 | 128.73 | | 3 | 135.74 | 135.74 | | 4 | 81.78 | 81.78 | | 5 | 144.62 | 144.62 | +----------+---------------+---------------------+ 5 rows in set (0.06 sec) mysql> SELECT -> cat.name AS category, -> count(DISTINCT fc.film_id) AS "Number of distinct films", -> count(i.inventory_id) AS "Total copies of films" -> FROM category AS cat -> LEFT JOIN film_category AS fc ON cat.category_id = fc.category_id -> JOIN inventory AS i ON fc.film_id = i.film_id -> GROUP BY category; +-------------+--------------------------+-----------------------+ | category | Number of distinct films | Total copies of films | +-------------+--------------------------+-----------------------+ | Action | 61 | 312 | | Animation | 64 | 335 | | Children | 58 | 269 | | Classics | 54 | 270 | | Comedy | 56 | 269 | | Documentary | 63 | 294 | | Drama | 61 | 300 | | Family | 67 | 310 | | Foreign | 67 | 300 | | Games | 58 | 276 | | Horror | 53 | 248 | | Music | 51 | 232 | | New | 60 | 275 | | Sci-Fi | 59 | 312 | | Sports | 73 | 344 | | Travel | 53 | 235 | +-------------+--------------------------+-----------------------+ 16 rows in set (0.03 sec) mysql> untee -> ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'untee' at line 1 mysql> ? For information about MySQL products and services, visit: http://www.mysql.com/ For developer information, including the MySQL Reference Manual, visit: http://dev.mysql.com/ To buy MySQL Enterprise support, training, or other products, visit: https://shop.mysql.com/ List of all MySQL commands: Note that all text commands must be first on line and end with ';' ? (\?) Synonym for `help'. clear (\c) Clear the current input statement. connect (\r) Reconnect to the server. Optional arguments are db and host. delimiter (\d) Set statement delimiter. ego (\G) Send command to mysql server, display result vertically. exit (\q) Exit mysql. Same as quit. go (\g) Send command to mysql server. help (\h) Display this help. notee (\t) Don't write into outfile. print (\p) Print current command. prompt (\R) Change your mysql prompt. quit (\q) Quit mysql. rehash (\#) Rebuild completion hash. source (\.) Execute an SQL script file. Takes a file name as an argument. status (\s) Get status information from the server. tee (\T) Set outfile [to_outfile]. Append everything into given outfile. use (\u) Use another database. Takes database name as argument. charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets. warnings (\W) Show warnings after every statement. nowarning (\w) Don't show warnings after every statement. resetconnection(\x) Clean session context. For server side help, type 'help contents' mysql> notee