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)
Comments