top of page

[MySQL 101] Get started

Updated: Sep 20, 2021



MySQL overview

MySQL is a RDBMS (Relational Database Management System) which is used to store and manage a large amount of data. MySQL is called RDMBS as it uses table structure to store the data, and there are many relationship between tables via key.


MySQL is quite easy for newbie to get started, MySQL is usually used for many kind of actions/tasks, and it is the most popular RDBMS in the world, such as:

  • MySQL is free open-source project.

  • MySQL is a mature engine for RDBMS.

  • MySQL uses standard statement: SQL - Structured Query Language.

  • MySQL can work on several popular OSes and languagues: PHP, Perl, C++, Java...

  • MySQL is stronger in managing big data.

  • MySQL is strongly compatible with PHP - one of the most popular language for web development.



Create Databases & Users

One MySQL instance can manage mulitiple databases. Usually, we use specific database for 1 application, it also separate management actions between databases, applications and users as well.


One database can be granted permission for multiple users, one users will have a set of permission on databases.


Create databases

create database <name>;

with <name>: name of database you want to create.


Notes: when execute any command on MySQL CLI, they always end with semicolon ";". This is the signal for CLI to know that this is finished command and need to execute.


Example:

mysql> create database test;

Query OK, 1 row affected (0.00 sec)

with above example, we have created successfully database named test.


About the users & permissions

You should set the password for users all the time, right after having created users.

Furthermore, you should read the instruction of permission carefully due to granting suitable and enough permissions for a user.


To create a user:

CREATE USER 'username'@'localhost' identified by 'password';

the command above create a user with username, can work on localhost, and have a password associated.

If you want to create a user, can login and work anywhere, you should go with:

CREATE USER 'username'@'%' identified by 'password';

However, the command CREATE USER just creates a user with zero permission, even login by MySQL CLI. So, we should do one more action with GRANT statement.


Indeed, if you grant the permissions for in-existed user, that user will be created automatically alongside with permission set.



Some popular permissions:

Permission

Explanation

ALL PRIVILEGES

Full permission on database, or some table on database

CREATE

User can create new databases or tables

DROP

User can delete tables or databases

DELETE

User can delete data from tables

INSERT

User can insert data to table

UPDATE

User can update existing data on table

SELECT

User can use SELECT statement for querying data

GRANT OPTION

User can grant or revoke permission of other users

Permission CLI commands


To grant:

GRANT [permission_type]

ON [dbname].[table_name]

TO ‘username’@’localhost’ IDENTIFIED BY ‘password’;

Example:

mysql> GRANT ALL PRIVILEGES ON test.* TO 'testuser'@'localhost' identified by 'abcdefgh';

with above command, we have granted full permission for testuser on database 'test' and all table on the database.


Once we finished GRANT statement, we should reload the privileges with the command:

mysql> FLUSH PRIVILEGES;

To revoke:

REVOKE [permission_type]

ON [dbname].[table_name]

from 'username'@'localhost';

Example:

mysql> REVOKE CREATE,DROP on test.* from 'testuser'@'localhost';

with this example, we revoke 2 permission CREATE and DROP on database 'test' all tables from user 'testuser'.


Change user's password


Using ALTER statement:

mysql> alter user 'testuser'@'localhost' identified by 'newpassword';

Other CLI command

STATEMENT

Explanation

SHOW DATABASES;

Show all databases on MySQL instance.

USE <dbname>;

Change to database <dbname>.

SHOW TABLES;

Show all tables in database.

DESCRIBE <table_name>;

Show all detail of table <table_name>.

SHOW GRANTS FOR <username>;

Show the privileges of user <username>.



Data type

There are some popular data type in MySQL:


Numberic data type

DATA TYPE

EXPLANATION

INT

Integer upto 11 number character.

TINYINT

Integer upto 4 number character.

FLOAT (M,D)

Values can store upto M digits in total, of which D digits may be after the decimal point.

Date & time data type

DATA TYPE

EXPLANATION

DATE

YYYY-MM-DD

DATE TIME

YYYY-MM-DD HH:MM:SS

TIME

HH:MM:SS

String data type

DATA TYPE

EXPLANATION

VARCHAR(M)

String data from 1 to 255 characters

Create Table in database

Use this statement:

CREATE TABLE <table_name> (column_name1 data_type, column_name2 data_type, ...)

Example:

mysql> CREATE TABLE student (student_id varchar(20), name varchar(20), dob date)

Query OK, 0 rows affected (0.02 sec)


107 views0 comments

Comments

Rated 0 out of 5 stars.
No ratings yet

Add a rating
Stationary photo

Be the first to know

Subscribe to our newsletter to receive news and updates.

Thanks for submitting!

Follow us
bottom of page