August 26, 2019
Now its time to switch gears. Lets learn about MySQL. Its a relational database, or SQL-based database, which essentially means everythings related, as implied by the name via various ‘id’ columns. SQL stands for ‘Structured Query Language’. It’s simply a standardized way of accessing data in a database.
This is in contrast to a NoSQL-based database. The most obvious NoSQL-based database is MongoDB. NoSQL essentially follows a way of storing data nonrelationally. I won’t get into the advantages and disadvantages of both, just know that both exist.
Feel free to check out MySQL downloads page for your specific needs.
For me I used the following:
sudo apt updatesudo apt install -y mysql-server # installs mysqlsudo mysql_secure_installation # will provide various prompts
Ensure its working:
mysql # enters a prompt-> exitsudo service mysql statussudo service mysql stopsudo service mysql startsudo service mysql status
There are additional configs to add users, but thats a little too advanced for what were doing here.
Creating your first database:
mysql-> show databases;# Lists databases-> create database todo;# Query OK, 1 row affected-> show databases; # should show mytodo-> use mytodo; # This tells mysql what database to switch into and use-> show tables; # should be empty-> CREATE TABLE todos (description TEXT, completed BOOLEAN);# Query OK, 0 rows affected-> show tables; # Should show your new todos table-> describe todos; # Shows your fields and types-> drop tables; # Removes your todos tables-> CREATE TABLE todos (id INTEGER PRIMARY KEY AUTO_INCREMENT, description TEXT NOT NULL, completed BOOLEAN NOT NULL);-> show tables;-> describe todos;
I’m not going to get too in depth with the above commands, but I will touch on the following query.
Here’s the command:
CREATE TABLE todos (id integer PRIMARY KEY AUTO_INCREMENT, description text NOT NULL, completed boolean NOT NULL);
Here’s what it’s doing:
CREATE TABLE todos(); Create the table called ‘todos’
id integer PRIMARY KEY AUTO_INCREMENT: The first field will be the
this field will accept an
INTEGER. This field will also be the
This means that the
id will be the unique identifier for whatever data is stored.
AUTO_INCREMENT means the user does not have to supply the id integer. Instead, the
database will automatically set the id starting from 1.
mysql-> INSERT INTO todos (description, completed) VALUES('Go to the store', false);# Will auto-add the id, will add the description 'Go to store', and the boolean false-> SELECT * FROM todos;# Will show all columns in the todos table
mysql Start mysql
-> create database \<database\>; Create the database with \<database>.
-> use database <\database\>; Set the current database to query to \<database>.
-> CREATE TABLE \<table\>(<\fields\>); Create a table within the database with a name of \<table>
and has the following \<fields>.
-> show tables; List the tables in your database.
-> describe <\table\>; Shows the fields of the given \<table>.
-> INSERT INTO <\table\>(<\field1\>, \<field2\>) VALUES(<\value1\>, <\value2\>);
Insert the following
values into the specified \<field> for the given table name. Order is important.
-> SELECT * FROM \<table\>;
Allows you to view all columns and their data from the given \<table>.
-> SELECT * FROM \<table\>WHERE id = 1;
Select all columns from \<table> but only where the id is equal to 1.
Now using the command line for everything is not very fun. You can simply google:
‘mysql database GUI tools’
I decided to use the officially supported mysql-workbench.
sudo apt install mysql-workbenchmysql-workbench
Easy, simple, I like it.
sudo mysql -> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_passwordBY 'password'; -> FLUSH PRIVILEGES;
Yes, you can create a new user and then provide privileges that way. Again, beyond the scope of this, and this wont be used in production either.
I also had an issue running mysql-workbench without sudo. I solved this by chowning ~/.mysql
sudo chown $USER:$USER ~/.mysqlmysql-workbench
I’ll let you figure out other configurations with mysql-workbench. Play around with it. Don’t rush, find other commands. Have fun! Ill see you in the next part about classes.
Written by Konnor Rogers who currently works at VeueLive as a full time developer. You should follow him on Twitter