DBA Hub

📋Steps in this guide1/6

Create MySQL Database

Create MySQL Database

mysql configurationintermediate
by MYSQL
16 views
1

Overview

Create MySQL Database 1. Login to MySQL 2. Create Database

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[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

Section 2

create database dell; 3. Show create database syntax | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
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>

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>
3

Section 3

4. Switch database 5. How to clear the mysql screen

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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>

mysql>
system 'clear';
mysql>
4

Section 4

6. Create Table create table test ( -> trans_id smallint, -> trans varchar(5), -> primary key (trans_id)) -> ENGINE=InnoDB; 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 7. Direct connect to Database instead of switch

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
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>
5

Section 5

7. Direct connect to Database instead of switch 8. Select version of Database mysql -u root -p dell

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
[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>

[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]#
6

Section 6

Enter password: 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. 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.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!