Refs:

How To Install MySQL on Ubuntu 20.04

Installing MySQL

Always best before doing a new install is download and install any updates including a restart if that's convenient:

sudo apt-get update

sudo apt-get upgrade

sudo shutdown -r now

Install the MySQL DBMS and client (which helps manage access to the DB)

sudo apt install mysql-server mysql-client

checking afterward that MySQL is running with

systemctl status mysql.service

and pressing q to end the status report after it displays.

For completeness it helps to run 

sudo mysql_secure_installation

after installation to create a password for MySQL root and close some vulnerable default options; answering yes (y) to all suggestions is recommended for our needs. If setting the root password using this command results in the error SET PASSWORD has no significance for user ... then do the following before trying (again) the mysql_secure_installation command (using your desired password instead of mynewpassword):

sudo mysql

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'mynewpassword';

exit

MySQL First Steps

To get a first exposure to MySQL the MySQL client will be used to connect to MySQL, create a database, and manage a table. There are a variety of products, notably MySQL Workbench, that provide a GUI and tools for administering a MySQL server and its databases. Kind of like a command line for MySQL, the MySQL client is fundamental and enough for our purposes.

MySQL commands end with a semi-colon ; and if the semi-colon is left off the MySQL client will indicate that the command should be continued on another line -- so continue the command or just type the forgotten ; and press enter. Upper / lower case don't matter on SQL commands in MySQL but they are critical in database, table, and column names. If something seems to not be working check you have letter casing correct.

Despite having set a password for MySQL root you connect (from terminal or PowerShell) to the MySQL client as root in modern Ubuntu by using

sudo mysql

but if that results in something like

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

then use

sudo mysql -p

and at the password prompt enter the password created for root earlier.

MySQL has the capability for adding other users and assigning them passwords. For a user created in the database the syntax to connect via MySQL client is

mysql -u userid -p

and then providing the password for the userid connection to MySQL.

Once in MySQL the user you are connected as can be displayed using

select current_user();

and use

show databases;

to see what databases are in the server. For a fresh installation there should be a few system databases. To create a database named demoDB use

create database demoDB;

A MySQL connection can have one database in use at a time. The command

select database();

shows the database currently in use and the command to change this to the demoDB database is

use demoDB;

Databases contain tables ... well defined lists of data ... and

show tables;

will show the tables in the database currently in use. Tables are defined by their columns -- names, data types, other properties -- so to add a table named Member with a required integer Member ID called MbrID as a unique identifier for a member, a required nickname of up to 50 characters, and a Balance which will default to 0.00 for a new member use

create table Member (

MbrID int not null,

NickName varchar(50) not null,

Balance decimal(8,2) default 0.00,

primary key (MbrID)

);

In MySQL using

describe Member;

displays the table's structure.

There are no records in Member yet which can be verified using

select * from Member;

where the * means return all columns (and all rows since there is no WHERE clause on the SELECT statement).

To add a member nicknamed Frodo with ID 1001 and a balance of $25.00 use

insert into Member

(MbrID, NickName, Balance)

values (1001, 'Frodo', 25.00)

;

and run the select * from Member; statement to validate it was entered. There are many variations on an insert statement, beyond our scope, but two common ones are

insert into Member

(MbrID, NickName)

values (1002, 'Luke Skywalker'),

(1003, 'The Phantom')

;

which adds two new records with one command and the Balance will default to 0.00. The command

insert into Member

values (2001, 'Hal', 5.00)

;

inserts one new record but without a field list (the values are aligned with columns from left-to-right in the table). The results of select * from Member; should now show four records.

Existing records can be updated via SQL. As a 'for instance' suppose all members with ID's less than 2000 get 10% added to their balance. The command

update Member

set Balance = 1.1 * Balance

where MbrID < 2000

;

Run select * from Member; to see the effect.

Records can also be deleted. To remove those members with a balance of zero use

delete from Member

where Balance = 0.00

;

Again, run select * from Member; to see the effect.

Using exit or quit in the MySQL client exits the client. No worries about 'saving'; the data will be as you left them.