Hello fams, I started learning a relational database (Sequel Query Language) and I decided to make a post to document my continuous progress. I stem from a NoSQL background (MongoDb). My choice software is Mysql and the Operating system used is windows. All commands should be the same. I have included examples in the Basic Queries Section
Let's get right to it. Click the link Download Link to navigate to MySQL and download the MSI
I use Windows, and to run my SQL commands from my command prompt we need to add the download path (containing the binary) to our Environment variable. (Highlighted in red)
Go to system properties π Environmental Variables π System variable π Click on path π click on edit π click new π paste the copied path highlighted in red earlier π Ok.
Now we have successfully added this path to our environment variable.
Check if MySQL is running
$ mysql --version
π You should get
C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe Ver 8.0.25 for Win64 on x86_64 (MySQL Community Server - GPL)
With that out of the way let's login to our Mysql with the commands below
login to mysql from terminal (command prompt)
mysql -u root -p
mysql -u joseph -p
π Create a specific user for a DataBase CREATE USER 'joseph'@'localhost' IDENTIFIED BY 'password';
π To see selected users SELECT user, host FROM mysql.user;
π Give privileges i.e full access
GRANT ALL PRIVILEGES ON * . * TO 'user'@'localhost'; GRANT ALL PRIVILEGES ON * . * TO 'joseph'@'localhost';
π Flush privileges after granting privileges FLUSH PRIVILEGES;
π Check privileges for certain users
SHOW GRANTS FOR 'user'@'localhost';
SHOW GRANTS FOR 'joseph'@'localhost';
π Exit mysql terminal to windows terminal by: exit;
Login into mysql terminal
mysql -u joseph -p
π Actions SELECT * FROM users;
Note: Actions are mostly written in Capitals.( not compulsory just a convention).
π Select values SELECT first_name, last_name FROM users;
π WHERE clause SELECT* FROM users WHERE location = 'sagamu';
π Multiple Conditions SELECT* FROM users WHERE location = 'sagamu' AND dept = "graphics";
π ALTER TABLE test ADD another_column VARCHAR(255);
π Bands table CREATE TABLE bands(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
);
π Albums table linked to bands CREATE TABLE albums(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
release_year INT,
band_id INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY(band_id) REFERENCES bands(id)
);
π Inserting multiple data INSERT INTO bands (name)
VALUES ('Abayomi Joseph'), ('the maxess'), ('blues');
π Selecting with limits SELECT * FROM bands LIMIT 2;
π Selecting specific property SELECT name FROM bands;
π Renaming using the AS alias SELECT id AS 'ID', name AS 'Band Name'
FROM bands;
π Ordering and sorting data SELECT * FROM bands ORDER BY name;
π Ordering and sorting data Descending order SELECT * FROM bands ORDER BY name DESC;
π Ordering and sorting data ascending order SELECT * FROM bands ORDER BY name ASC;
π *Ordering and sorting data Descending order II * SELECT * FROM bands ORDER BY name;
π Querying data SELECT * FROM albums;
SELECT name FROM albums;
π Selecting distinct data without duplicate SELECT DISTINCT name FROM albums;
π Updating data UPDATE albums
SET release_year= 1677
WHERE id = 1;
π** Filtering data using the WHERE ** SELECT * FROM albums
WHERE release_year < 2000;
or
SELECT * FROM albums*
WHERE release_year > 2000;
π** Selecting using the string filter** SELECT * FROM albums
WHERE name LIKE '%er%';
The above example is saying filter any string which has 'er' inside
π The OR operator SELECT * FROM albums
WHERE name LIKE '%er%' OR band_id=3;
π The AND clause SELECT * FROM albums
WHERE release_year=1986 AND band_id=1;
π Filtering between two different values SELECT *FROM albums
WHERE release_year BETWEEN 2000 AND 2009;
π ** Filtering NULL SELECT FROM albums* WHERE release_year IS NULL;
π Delete clause DELETE FROM albums
WHERE id = 4;
π Join tables together SELECT * FROM bands
JOIN albums ON bands.id = albums.band_id;
π** Inner join work as above** SELECT * FROM bands
INNER JOIN albums ON bands.id = albums.band_id;
π** LEFT JOIN** SELECT * FROM bands
LEFT JOIN albums ON bands.id = albums.band_id;
π RIGHT JOIN SELECT * FROM albums
RIGHT JOIN bands ON bands.id = albums.band_id;
AGGREGRATE FUNCTION
π AVERAGE SELECT AVG(release_year) FROM albums;
π SUM SELECT SUM(release_year) FROM albums;
π COUNT BY GROUP SELECT band_id, COUNT(band_id) FROM albums
GROUP BY band_id;
π Complex grouping SELECT b.name AS band_name, COUNT(a.id) AS num_albums
FROM bands AS b
LEFT JOIN albums AS a ON b.id = a.band_id
GROUP BY b.id;
π HAVING SELECT b.name AS band_name, COUNT(a.id) AS num_albums
FROM bands AS b
LEFT JOIN albums AS a ON b.id = a.band_id
WHERE b.name = 'blues'
GROUP BY b.id
HAVING num_albums = 1;
Conclusion:
This is a quick guide into using SQL if I miss any command or you have a better way of querying kindly drop it in the comment section. Thanks ππ½ for reading