MySQL Tutorial
Introduction Installation of MySQL Getting MySQL server information RDBMS Concepts Data Definition Language Data types Managing Databases Managing Tables Managing Views Inserting records in a table Updating records in a table Deleting records from a table Retrieving Records Data Control Language Variables in MySQL Procedures in MySQL Built-In functions in MySQL User Defined functions in MySQL Triggers in MySQL Cursors in MySQL Transaction control Event scheduler Tuning MySQL Server Performance Managing users Importing and Exporting data Database Testing Workbench PHPMyAdminGetting server info in MySQL
Once you are connected to MySQL, you can execute help command to view all available commands in MySQL. To know what all options are supported by show command, you can use below command.
help show
There are many show commands that can give all important information of MySQL server. For example – You can view all databases as shown in below command.
show databases;
Here is the sample output of above command.
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys |
| world |
Information_schema is the database that stores meta data of the user tables, triggers, routines etc. If you can not see information_schema in workbench, you will have to edit the preferences to show the metadata and internal schemas. To view which database is currently selected, you can use below command.
select database();
Above command returns null if no database is selected. If you execute any command without selecting the database, you will get error saying no Database is selected. To select the database, you have to use below command.
use databaseName;
To view all tables in the database, you can use below command.
Show tables;
To view table schema, you can use below command.
Describe tableName;
You can use below commands to view procedures
SHOW PROCEDURE STATUS;
You can use below commands to view functions
SHOW FUNCTION STATUS;
MySQL supports many storage engines like InnoDB and MyISAM. To view which storage engine is being used for each table, you need to use below command.
SHOW TABLE STATUS;
Show table status where name like ‘%routines%’G
Note that \G (instead of ; semicolon ) is used when you want to display the records in key-value pairs.information_schema database in MySQL
MySQL stores metadata in the database called as information_schema. You can view all tables in information_schema using below command.
SHOW TABLES;
You can use SQL queries to get the information of all tables in MySQL Server. Below query will return all table and schema names in MySQL Server.
SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.tables
Below query will return all views and schema names in MySQL Server.
SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.tables
WHERE TABLE_TYPE LIKE ‘VIEW’;
Getting server date and time
Below commands can be used to get the current date and time of MySQL server instance.
SELECT CURDATE(), CURTIME(), NOW();
- CURDATE() function displays only date.
- CURTIME() function displays only time.
- NOW() function displays date as well as time.
SELECT USER();
Below command shows the statistics of the MySQL server like Threads connected, Threads created, read requests, write requests etc.
SHOW STATUS;
Web development and Automation testing
solutions delivered!!