MySQL Mini Course

SQL (or Sequel ) is still one of the most common databases. It has many meanings from a programming language to a data hosting system. Here we do not need to go in those technicalities, but you must be aware of many different ways one can use SQL — from command lines to Azure studio and from python codes and this makes things very very confusing for the beginners. Here I will try to simplify the process. Firstly you need to know the following.

• SQL as a software package is free and you can install it on any Windows/Linux or Mac Machine.
• SQL always has two ends — one server end and another client end and you can have both on your local machine for the learning purpose — as we will use here.
• Certain flavours of MySQL may come with their own terminals but using any normal terminal (such as ‘bash’ ) is the best solution.
• Once you have installed MySQL on your machine you will need to make sure that ‘mysqld’ process which is responsible for launching the server is up and running.
• You must create a ‘root’ user first (by setting a password) and once inside the MySQL terminal you can use any normal user, give permissions to the new user and logged into using that (after logging out from root) use anything as you wish, as I will show here.

0. Login as a root user:

[jayanti@JAYANTIs-MacBook-Pro ~]$mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.26 MySQL Community Server — GPL
Copyright © 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql>

1. Create a new MySQL User Account

mysql> CREATE USER ‘jayanti’@’localhost’ IDENTIFIED BY ‘jayanti1’;
One can use wildcard also for the hostname.
mysql> CREATE USER ‘newuser’@’%’ IDENTIFIED BY ‘user_password’;

2. Drop an existing user

DROP USER ‘user’@’localhost’

3. Get Current user’s info:

mysql> SELECT CURRENT_USER();

4. Show existing data bases

mysql> show databases;

5. Show existing tables

mysql> show tables;

6. Create a database

mysql> CREATE DATABASE music;

7. Delete a database

mysql> DROP DATABASE music;

8. Set Permissions

The most commonly used privileges are:

ALL PRIVILEGES — Grants all privileges to a user account.

CREATE — The user account is allowed to create databases and tables.
DROP — The user account is allowed to drop databases and tables.
DELETE — The user account is allowed to delete rows from a specific table.
INSERT — The user account is allowed to insert rows into a specific table.
SELECT — The user account is allowed to read a database.
UPDATE — The user account is allowed to update table rows.

• Give all the permissions:

mysql> GRANT permission1, permission2 ON database_name.table_name TO ‘database_user’@’localhost’;

• Grand all privileges to a user account on all databases:

mysql> GRANT ALL PRIVILEGES ON database_name.* TO ‘database_user’@’localhost’;

• Grand all privileges to a user account over a specific table from a database:

mysql> GRANT ALL PRIVILEGES ON database_name.table_name TO ‘database_user’@’localhost’;

• Grant multiple privileges to a user account over a specific database:

mysql> GRANT SELECT, INSERT, DELETE ON database_name.* TO database_user@’localhost’;

• Revoke Privileges from a MySQL User Account

mysql> REVOKE ALL PRIVILEGES ON database_name.* FROM ‘database_user’@’localhost’;

9. Creating a table

Firstly create a database

mysql> create database TEST;

Now go to that database

mysql> use TEST;

Now you can create a table in that database

mysql> CREATE TABLE DATE_OF_BIRT (name VARCHAR(20), DOB DATE, AGE VARCHAR(2));

You can create another table also:

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
 species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

You can check the table with the following command

mysql> SHOW TABLES;

It gives the list of the tables in your database. You can get more information about a table with the following command:

mysql> DESCRIBE DATE_OF_BIRTH;

10. Insert a row in a table

mysql> INSERT INTO DATE_OF_BIRTH(name, DOB, AGE) VALUES(‘JAYANTI PRASAD’,’1987–01–30',’43');

11. Display all the columns of a table

mysql> SELECT * from DATE_OF_BIRTH;

12. Selecting rows on the basis of some conditions on column:

mysql> select * from customers WHERE creditLimit > 200000;

In case you have found this short tutorial useful please like (clap), share & comment. I will add more commands here very soon.

13. Get a particular column from a table :

mysql> SELECT email from employees;

We can get more than one columns also :

14. Get the rows of conditions

The WHERE clause of the SELECT query enables you to make complicated selections. For instance, suppose your boss wants to know all the customers whose last names begin with B, who live in Indianapolis, and who have an 8 in either their phone or fax number. You can get this list for your boss in a SELECT query with a WHERE clause.

mysql> SELECT * FROM Customers WHERE country=”USA” LIMIT 1;

15. Load external mysql db

mysql> SOURCE /Users/jayanti/Codes/DB/mysqlsampledatabase.sql

16. Delete on the basis of condition

mysql> DELETE FROM weights WHERE name = “XXXX”;

17. Updating an existing row

mysql> UPDATE organizations SET org_end_date = DATE ‘1985–06–30’ WHERE org_id = ‘10001’;

18. Search

we can get a particular row with syntax like the following:

mysql> select * from HIST WHERE hist_id=’20004';

19. Count the number of rows in a table

mysql> select * from it_companies;
+ — — — — — — + — — — — — -+ — — — — — — — — — + — — — -+
| company_id | name | founder | start |
+ — — — — — — + — — — — — -+ — — — — — — — — — + — — — -+
| 1001 | Microsoft | Bill Gates | 1972 |
| 1002 | Google | Larry Page | 1998 |
| 1003 | Amazon | Jeff Bezos | 1994 |
| 1004 | Facebook | Mark Zuckerberg | 2004 |
+ — — — — — — + — — — — — -+ — — — — — — — — — + — — — -+
4 rows in set (0.00 sec)
mysql> select count(*) from it_companies;
+ — — — — — +
| count(*) |
+ — — — — — +
| 4 |
+ — — — — — +
1 row in set (0.00 sec)

mysql>

Please like, comment & share this article if you find it useful. In the next part of this tutorial I will explain some advanced concepts.

Useful Resources

• https://sqlbolt.com/