MySQL in Ubuntu
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.