02. Làm quen với MySQL.

Hôm nay ta sẽ học cách tạo database, tạo table, chỉnh sủa table, xoá table, xoá database. Bắt đầu nhé

1. Tạo Database tên là "testing_db"

CREATE DATABASE testing_db;
mysql> CREATE DATABASE testing_db;
Query OK, 1 row affected (0.00 sec)

2. Kiểm tra Database

SHOW DATABASES;
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testing_db         |
+--------------------+
5 rows in set (0.00 sec)

3. Dùng Database "testing_db"

USE testing_db;
mysql> USE testing_db;
Database changed
mysql> 

4. Kiểm tra hiện nay đang dùng database nào

SELECT DATABASE();
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| testing_db |
+------------+
1 row in set (0.00 sec)

mysql> 

5. Tạo Table tên là animals

CREATE TABLE animals(name VARCHAR(100), age INT);
mysql> CREATE TABLE animals(name VARCHAR(100), age INT);
Query OK, 0 rows affected (0.01 sec)

mysql> 

Table này có 2 field name và age. name có kiểu dữ liệu VARCHAR(100) tối đa 100 ký tự. age có kiểu INT.

6.Xem cấu trúc của table

DESC animals;
mysql> DESC animals;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(100) | YES  |     | NULL    |       |
| age   | int(11)      | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> 

Null Yes có nghĩa là khi thêm dữ liệu vào có quyền để trống, Default NUll nghĩa là không có giá trị mặc định.

7. Xem table

SHOW TABLES;
mysql> SHOW TABLES;
+----------------------+
| Tables_in_testing_db |
+----------------------+
| animals              |
+----------------------+
1 row in set (0.00 sec)

mysql> 

8. Xoá table

DROP TABLE animals;
mysql> DROP TABLE animals;
Query OK, 0 rows affected (0.00 sec)

mysql> 

9. Đổi tên table

// Tạo table
CREATE TABLE people (name VARCHAR(100), age INT);

// đổi tên table từ people sang dog
ALTER TABLE people RENAME TO dog;

DESC dog;
SHOW TABLES;

mysql> CREATE TABLE people (name VARCHAR(100), age INT);
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE people RENAME TO dog;
Query OK, 0 rows affected (0.00 sec)

mysql> DESC dog;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(100) | YES  |     | NULL    |       |
| age   | int(11)      | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> SHOW TABLES;
+----------------------+
| Tables_in_testing_db |
+----------------------+
| dog                  |
+----------------------+
1 row in set (0.00 sec)

mysql> 

10. Xoá field name

ALTER TABLE dog DROP name;
DESC dog;
mysql> ALTER TABLE dog DROP name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC dog;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| age   | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> 

11. Thêm field name

ALTER TABLE dog ADD name VARCHAR(120);
DESC dog;
mysql> ALTER TABLE dog ADD name VARCHAR(120);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC dog;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| age   | int(11)      | YES  |     | NULL    |       |
| name  | varchar(120) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> 

12. Thêm field phone sau field name

ALTER TABLE dog ADD phone VARCHAR(100) AFTER name;
DESC dog;
mysql> ALTER TABLE dog ADD phone VARCHAR(100) AFTER name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC dog;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| age   | int(11)      | YES  |     | NULL    |       |
| name  | varchar(120) | YES  |     | NULL    |       |
| phone | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> 

13. Thêm field email ở thứ tự đầu tiên

ALTER TABLE dog ADD email VARCHAR(100) FIRST;
DESC dog;
mysql> ALTER TABLE dog ADD email VARCHAR(100) FIRST;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC dog;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| email | varchar(100) | YES  |     | NULL    |       |
| age   | int(11)      | YES  |     | NULL    |       |
| name  | varchar(120) | YES  |     | NULL    |       |
| phone | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> 

14. Chỉnh sửa field name có tối đa 150 ký tự

ALTER TABLE dog MODIFY name VARCHAR(150);
DESC dog;
mysql> ALTER TABLE dog MODIFY name VARCHAR(150);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC dog;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| email | varchar(100) | YES  |     | NULL    |       |
| age   | int(11)      | YES  |     | NULL    |       |
| name  | varchar(150) | YES  |     | NULL    |       |
| phone | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> 

15. Chỉnh sửa field age sang dạng BIGINT

ALTER TABLE dog MODIFY age BIGINT;
DESC dog;
mysql> ALTER TABLE dog MODIFY age BIGINT;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC dog;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| email | varchar(100) | YES  |     | NULL    |       |
| age   | bigint(20)   | YES  |     | NULL    |       |
| name  | varchar(150) | YES  |     | NULL    |       |
| phone | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> 

16. Thay đổi tên field và kiểu dữ liệu

ALTER TABLE dog CHANGE name newName VARCHAR(130);
DESC dog;
mysql> ALTER TABLE dog CHANGE name newName VARCHAR(130);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC dog;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| email   | varchar(100) | YES  |     | NULL    |       |
| age     | bigint(20)   | YES  |     | NULL    |       |
| newName | varchar(130) | YES  |     | NULL    |       |
| phone   | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> 

17. Thay đổi chiều dài kiểu dữ liệu

ALTER TABLE dog CHANGE newName newName VARCHAR(110);
DESC dog;
mysql> ALTER TABLE dog CHANGE newName newName VARCHAR(110);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC dog;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| email   | varchar(100) | YES  |     | NULL    |       |
| age     | bigint(20)   | YES  |     | NULL    |       |
| newName | varchar(110) | YES  |     | NULL    |       |
| phone   | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> 

18. Set giá trị mặc định

ALTER TABLE dog ALTER age SET DEFAULT 100;
DESC dog;
mysql> ALTER TABLE dog ALTER age SET DEFAULT 100;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC dog;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| email   | varchar(100) | YES  |     | NULL    |       |
| age     | bigint(20)   | YES  |     | 100     |       |
| newName | varchar(110) | YES  |     | NULL    |       |
| phone   | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> 

19.Drop giá trị mặc định

ALTER TABLE dog ALTER age DROP DEFAULT;
DESC dog;
mysql> ALTER TABLE dog ALTER age DROP DEFAULT;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC dog;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| email   | varchar(100) | YES  |     | NULL    |       |
| age     | bigint(20)   | YES  |     | NULL    |       |
| newName | varchar(110) | YES  |     | NULL    |       |
| phone   | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql>

20. Chỉnh sửa field newName

ALTER TABLE dog MODIFY newName VARCHAR(50) NOT NULL DEFAULT "DEFAUL VALUE";
DESC dog;
mysql> ALTER TABLE dog MODIFY newName VARCHAR(50) NOT NULL DEFAULT "DEFAUL VALUE";
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC dog;
+---------+--------------+------+-----+--------------+-------+
| Field   | Type         | Null | Key | Default      | Extra |
+---------+--------------+------+-----+--------------+-------+
| email   | varchar(100) | YES  |     | NULL         |       |
| age     | bigint(20)   | YES  |     | NULL         |       |
| newName | varchar(50)  | NO   |     | DEFAUL VALUE |       |
| phone   | varchar(100) | YES  |     | NULL         |       |
+---------+--------------+------+-----+--------------+-------+
4 rows in set (0.00 sec)

mysql> 

21. Drop table, Drop Database

DROP TABLE dog;
SHOW TABLES;

DROP DATABASE testing_db;
SHOW DATABASES;
mysql> DROP TABLE dog;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW TABLES;
Empty set (0.00 sec)

mysql> 
mysql> DROP DATABASE testing_db;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> 

Vậy là ta biết các tạo database, tạo table, chỉnh sửa NOT NULL, DEFAULT VALUE. Cũng đơn giản phải không nào ?

U