how to create mysql database Archives

Create MySQL Database

Table of Contents
___________________________________________________________________________________________________

1. Login to MySQL
2. Create Database
3. Find create database syntax
4. Switch database
5. How to clear the mysql screen
6. Create Table
7. Direct connect to Database instead of switch
8. Select version of Database
___________________________________________________________________________________________________

1. Login to MySQL

[root@rac1 mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

2. Create Database

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>
mysql>
mysql> create database dell;
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| dell               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql>

3. Show create database syntax

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql>


mysql> show create database dell;
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| dell     | CREATE DATABASE `dell` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

4. Switch database

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql>
mysql> use dell;
Database changed
mysql>
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| dell       |
+------------+
1 row in set (0.00 sec)

mysql>

5. How to clear the mysql screen

mysql> system 'clear';
mysql>

6. Create Table

mysql> SELECT USER();
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| dell       |
+------------+
1 row in set (0.00 sec)

mysql>
mysql> show tables;
Empty set (0.00 sec)

mysql>

mysql> create table test (
    -> trans_id smallint,
    -> trans varchar(5),
    -> primary key (trans_id))
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql>

mysql> show tables;
+----------------+
| Tables_in_dell |
+----------------+
| test           |
+----------------+
1 row in set (0.00 sec)

mysql>

mysql> show create table test \G;
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `trans_id` smallint NOT NULL,
  `trans` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`trans_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>


mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

mysql>

mysql> INSERT INTO test values(1,'SUGI');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test values(2,'TEJU');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test values(3,'RAJ');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test values(4,'SOMU');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test values(5,'JANA');
Query OK, 1 row affected (0.01 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.06 sec)

mysql>
mysql> select * from test;
+----------+-------+
| trans_id | trans |
+----------+-------+
|        1 | SUGI  |
|        2 | TEJU  |
|        3 | RAJ   |
|        4 | SOMU  |
|        5 | JANA  |
+----------+-------+
5 rows in set (0.00 sec)

mysql>

7. Direct connect to Database instead of switch

[root@rac1 mysql]# mysql -u root -p dell
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| dell       |
+------------+
1 row in set (0.00 sec)

mysql>

8. Select version of Database

[root@rac1 mysql]# mysql -u root -p dell
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| dell       |
+------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.23    |
+-----------+
1 row in set (0.00 sec)

mysql>

OR

[root@rac1 mysql]#  mysql -u root -p -e 'select version();'
Enter password:
+-----------+
| version() |
+-----------+
| 8.0.23    |
+-----------+
[root@rac1 mysql]#

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Bright DBA

ORACLE DBA – Tips and Techniques

Tag Archives: how to create mysql database

Create MySQL Database

Create MySQL Database