All Java developers not only must know “SQL”, but also a database is required for “JDBC tutorials”, “Hibernate Tutorials”, and so on. Any decent Java application will require a database. So, your self-taught Java projects must perform CRUD (i.e. Create Read, Update and Delete) operations on the database.
Step 1: Download “MySQL” Zip archive (i.e. Windows (x86, 64-bit), ZIP Archive) from
https://dev.mysql.com/downloads/mysql/
and select “No thanks, just start my download” when prompted to “login or signup”, and extract the zip file into “c:\tools” folder.
Step 2: Now you have “C:\tools\mysql-5.7.18-winx64”. Add the MySQL to the windows path with “C:\scripts\environment-setup.cmd“, and run it from a DOS console.
|
1 2 3 4 5 6 7 8 9 10 11 |
REM This is sample to set up your Java SET TOOLS_HOME=C:\tools SET JAVA_HOME=%TOOLS_HOME%\jdk-8u131-windows-x64 SET JRE_HOME=%TOOLS_HOME%\jdk-8u131-windows-x64\jre SET MYSQL_HOME=C:\tools\mysql-5.7.18-winx64\mysql-5.7.18-winx64 SET M3_HOME=%TOOLS_HOME%\apache-maven-3.5.0-bin\apache-maven-3.5.0 SET MAVEN_OPTS=-Xms1024m -Xmx1G SET PATH=%PATH%;%JAVA_HOME%\bin;%M3_HOME%\bin;%MYSQL_HOME%\bin |
Step 3: Initialize the database.
|
1 2 |
c:\scripts>mysqld --initialize --console |
Note down the temporary password: 2017-06-28T10:59:35.902725Z 1 [Note] A temporary password is generated for root@localhost: yw0+k4e-rjlI.
If you make a mistake somewhere, DELETE the entire “C:\tools\mysql-5.7.18-winx64” directory, and REPEAT the steps from unzipping into “c:\tools”.
Start the server – runs on port 3306
MySQL is a client/server application
Step 4: Start the server.
|
1 2 |
c:\scripts>mysqld --console |
Start the client, change the root password & create a new user
Step 5: Open another dos console, and run the “C:\scripts\environment-setup.cmd”, and start the client as a “root” user
|
1 2 |
c:\scripts>c:\scripts>mysql -u root -p |
You will be prompted for the password. Enter the one you previously noted down.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
c:\scripts>mysql -u root -p Enter password: ************ Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.18 Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. 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> |
Step 6: Change password for ‘root’@’localhost’ with something that you can remember.
|
1 2 |
mysql> alter user 'root'@'localhost' identified by 'password123'; |
Step 7: The superuser “root” is privileged, which means for database administration and is not
running DDLs and DMLs. So, let’s create a new user “user123” with a password “pwd123”
|
1 2 |
mysql> create user 'user123'@'localhost' identified by 'pwd123'; |
Step 8: Grant permissions to “user123”
|
1 2 |
mysql> grant all on *.* to 'user123'@'localhost'; |
Create a new database “learnjavadb” and a table “account”
Step 9: Create a new database named “learnjavadb“.
|
1 2 |
mysql> Create database if not exists learnjavadb; |
Step 10: Display the databases.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | learnjavadb | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) |
Step 11: Create a new table named “account“.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
mysql> use learnjavadb; Database changed mysql> mysql> drop table if exists account; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> create table account (id varchar(30), name varchar(50), balance float); Query OK, 0 rows affected (0.33 sec) mysql> show tables; +-----------------------+ | Tables_in_learnjavadb | +-----------------------+ | account | +-----------------------+ 1 row in set (0.00 sec) mysql> |
Step 12: Describe the table.
|
1 2 3 4 5 6 7 8 9 10 |
mysql> describe account; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id | varchar(30) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | balance | float | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) |
Step 13: Insert 3 rows.
|
1 2 3 4 |
mysql>insert into account values ('123', 'John R', 255.00); insert into account values ('456', 'Peter Smith', 1234.00); insert into account values ('789', 'Joanne R', 675.00); |
Step 14: Select all the rows from the table “Account”.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> select * from Account; +------+-------------+---------+ | id | name | balance | +------+-------------+---------+ | 123 | John R | 255 | | 456 | Peter Smith | 1234 | | 789 | Joanne R | 675 | +------+-------------+---------+ 3 rows in set (0.00 sec) mysql> |
Step 14: Quit the client.
|
1 2 3 4 5 |
mysql> quit Bye c:\scripts> |
Backup the database with the “mysqldump” utility program
Step 15: Backup the database.
|
1 2 3 |
c:\scripts>mysqldump -u user123 -p --databases learnjavadb > c:\projects\backup_learnjavadb.sql Enter password: ****** |
Step 16: Restore the database.
|
1 2 3 4 5 |
mysql -u user123 -p mysql> drop database if exists learnjavadb; mysql> source c:\projects\backup_learnjavadb.sql |
You can now practice your SQL commands.