Back to Basics – MySQL

So it’s been a few years now since I created a database in MySQL (version 6 in fact). I installed the latest version MySQL – version 8 for my new project. A little different from before as you now need need to add a preceding sql to the command. Looks like the shell can do other things!

sql CREATE DATABASE databasename
sql USE databasename
sql CREATE USER 'username'@'localhost' IDENTIFIED BY 'password'
sql GRANT ALL PRIVILEGES ON databasename.table TO 'username'@'localhost'
sql FLUSH PRIVILEGES

I don’t like using ‘ALL PRIVILEGES ON *.*’ so always define the database by name ‘ALL PRIVILEGES ON databasename.*’ It’s good to then login to the database in the MySQL Shell to check that all is well.

sql CONNECT username@localhost
use databasename
sql SHOW TABLES

Now, interestingly I had a problem with this. The program I was using was throwing an error when using the credentials I created. The error messages stated that I was not providing a password. I was. After a little digging around I found that I needed to use a MySQL Native Password. Here’s now.

sql ALTER USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'
sql FLUSH PRIVELEGES

I’m starting from scratch again to make sure there isn’t something I’ve missed along the way. I’ve made functional databases before, in fact my Medieval Realms database consists of no less than 33 tables. The largest table within consist of over 25,000 rows. So far here’s stuff I must have known before but somehow I must have forgotten, because these keywords play no role within my MR database.

The DEFAULT keyword

CREATE TABLE t_sometable
(
    c_some_data_column VARCHAR (5) NOT NULL,
    c_some_other_data_column VARCHAR (10) NOT NULL,
    c_one_more_column_with_a_preset_default DEC(10,2) NOT NULL DEFAULT 99.99
);

The main section here that is important is the DEFAULT 99.99 which sets up a default value for the tuple. One thing I’ve read recently which I kind of think is a good idea (so I’m giving it a try to see if I like it) is using a prefix t_ for tables and c_for columns.

Escape character alternative

SELECT * FROM t_sometable WHERE c_somecomlumn WHERE name = 'Mc'Lovin''s';

Yes sure I use ‘ as an escape for a single quote (‘) but I didn’t realise that a double – single – quote was also a means to achieve the same effect. Personally I will stick with the former but it’s good to know that the latter is also syntactically correct.

Not equal comparison

<>

Coming from a background in Java programming I had always expected the not equal sign in SQL to be != I was a little surprised to find it was actually <>. Goes to show I’ve never used it!

Textual Data Roping

Yeehaw! Let’s catch us some Strings! Again, something I wasn’t really aware of (and have not really had the need to use) is searching for textual data. I have almost exclusively worked with UIDs.

SELECT something
FROM t_sometable
WHERE
c_somecolumn >= 'A'
AND
c_somecolumn < 'C';

This will select all the data in c_somecolumn (inside t_sometable) where the columns string (a VARCHAR probably) that starts with A or B.

As always, cats can be skinned many ways. So the following SELECT statement also does the same. Personally I prefer the latter.

SELECT something FROM t_sometable WHERE c_somecolumn >= 'A' AND c_somecolumn <= 'B';

The LIKE keyword

A great keyword for searching for data when your input is incomplete.

SELECT * WHERE lastname LIKE '%son';

The % operator represents an unspecified length of unknown characters. The above command would find results like ‘Godson’, ‘Johnson’, ‘Jefferson’. Etc.