When an amount of data is stored in an organized way, that is called a database.
In computers, a database is managed by a software called Database Management System.
What is a table?
A table is a set of data values. These values are organized using vertical columns and horizontal rows. Columns are identified by their names.
Pictorial representation of a database with tables
MySQL create database
In MySQL, CREATE DATABASE statement creates a database with the given name. To use this statement, you must have the CREATE privilege for the database. You will get an error if the database exists and you did not specify IF NOT EXISTS clause.
Limits on Number of Databases: MySQL has no limit on the number of databases. The underlying file system may have a limit on the number of directories.
CREATE SCHEMA is a synonym for CREATE DATABASE.
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] database_name
[create_specification] ...
create_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
Where
Example:
The following statement creates 'bookinfo' database.
-- Create a new database named bookinfo
CREATE DATABASE bookinfo;
Explanation:
The database names are case sensitive under Unix but this restriction does not apply in Windows. This is also true for table names. The best practice is to use same letter case for creating a database as well as a table.
Note: A database which has just been created is not current database. The user must have to instruct to make it the current database. A database needs to be created only once but a user must have to select it each time he intends to work with that database.
MySQL: Setting the Default Database
MySQL use statement is used to change the database from default to the given database.
Syntax:
use [database_name];
MySQL show database
SHOW statement displays a list of currently existing databases on the server.
Syntax:
SHOW [expression];
Example:
The following MySQL statement will show the current database.
-- Display a list of all databases in the MySQL server
SHOW DATABASES;
Explanation:
The list of databases shown bellow by the statement may be different to the other user's machine. SHOW DATABASES does not show databases for those you don't have SHOW DATABASES privilege.
Sample Output:
MySQL> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | bookinfo | | MySQL | | test | +--------------------+ 4 rows in set (0.01 sec)
MySQL select database
MySQL select database statement is used to see which database is currently selected.
Syntax:
SELECT [expression];
Example:
The following MySQL statement will show the current database.
-- Retrieve the name of the current database being used
SELECT DATABASE();
Explanation:
Sample Output:
MySQL> select database(); +------------+ | database() | +------------+ | bookinfo | +------------+ 1 row in set (0.03 sec)
MySQL show tables statement
MySQL 'show tables' statement displays a list of the tables in the database in use. If there is no table in the database, it returns empty rows.
Syntax:
SHOW [expression];
Example:
The following statement displays the list of tables in the database 'bookinfo'.
-- Display a list of all tables in the current database
SHOW TABLES;
Explanation:
Sample Output:
MySQL> show tables; +--------------------+ | Tables_in_bookinfo | +--------------------+ | author | | book_mast | | category | | despatch | | newpublisher | | order | | publisher | | purchase | | tempdate | | testtable | +--------------------+ 10 rows in set (0.03 sec)
MySQL SHOW CREATE DATABASE
Shows the CREATE DATABASE statement that creates the given database. If the SHOW statement includes an IF NOT EXISTS clause, the output to includes such a clause. SHOW CREATE SCHEMA is a synonym for SHOW CREATE DATABASE.
Syntax:
SHOW CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
Example:
The following statement shows the create database statement of 'world' database.
-- Display the SQL statement that would create the specified database 'world'
SHOW CREATE DATABASE world;
Explanation:
Sample Output:
MySQL> show create database world; +----------+------------------------------------------------------------------+ | Database | Create Database | +----------+------------------------------------------------------------------+ | world | CREATE DATABASE `world` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+------------------------------------------------------------------+ 1 row in set (0.00 sec)
Find MySQL database size
Following query show you the database size in MySQL.
-- Select the table_schema aliasing it as "Database" and calculate the total size of each database in MB
SELECT table_schema "Database",
-- Calculate the total size of data and index for each database, and convert it to MB
SUM(data_length + index_length)/1024/1024 "Size in MB"
-- Retrieve data from the information_schema.TABLES table
FROM information_schema.TABLES
-- Group the results by the table_schema (i.e., the database)
GROUP BY table_schema;
Explanation:
Sample Output:
+--------------------+-------------+ | Database | Size in MB | +--------------------+-------------+ | bupf | 20.09464169 | | hr | 0.28685379 | | information_schema | 0.00976563 | | mucemppf | 4.50534534 | | MySQL | 2.43705654 | | performance_schema | 0.00000000 | | sakila | 6.57598877 | | sample | 0.73437500 | | test | 0.06250000 | | tutorial | 0.02406311 | | world | 0.43582153 | +--------------------+-------------+ 11 rows in set (0.17 sec)
Find all the tables in a MySQL database with specific column names in them
The following statement shows all the tables in 'hr' database.
Sample Output:
MySQL> USE hr; Database changed MySQL> SHOW TABLES; +-----------------+ | Tables_in_hr | +-----------------+ | account | | alluser | | departments | | emp_details | | job_history | | jobs | | locations | | log_emp_details | | my_v1 | | my_v2 | | my_v3 | | my_view | | new_view | | regions | | user | +-----------------+ 22 rows in set (0.00 sec)
The following statement shows all the tables in 'hr' database with columns 'name' or 'department_id'.
-- Select distinct table names from the INFORMATION_SCHEMA.COLUMNS table
SELECT DISTINCT TABLE_NAME
-- Specify the source table as INFORMATION_SCHEMA.COLUMNS and select the column TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
-- Filter rows where the COLUMN_NAME is either 'department_id' or 'name' and the TABLE_SCHEMA is 'hr'
WHERE COLUMN_NAME IN ('department_id', 'name')
AND TABLE_SCHEMA='hr';
Explanation:
Sample Output:
+-------------+ | TABLE_NAME | +-------------+ | departments | | job_history | | my_v2 | | my_v3 | | my_view | | user | +-------------+ 7 rows in set (0.04 sec)
See also: MySQL DROP DATABASE
Previous: Connecting to and disconnecting from MySQL
Next: MySQL CREATE TABLE
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4