Một số câu lệnh cơ bản và nâng cao MySQL

Cơ bản

Login MySQL.

mysql -u root -p

Tạo user và Database.

create database dbcuaban;
create user 'usercuaban'@'localhost' identified by 'passcuaban';
grant all on dbcuaban.* to usercuaban@localhost;
FLUSH PRIVILEGES;

Tạo table.

CREATE TABLE ten_table(id INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT, cot1 VARCHAR(20) NOT NULL Unique, cot2 VARCHAR(50) NOT NULL, cot3 DATETIME NOT NULL) ENGINE=InnoDB;

Chèn thêm 1 cột.

ALTER TABLE ten_table ADD COLUMN cot4 VARCHAR(300) NOT NULL;

Ghi dòng.

INSERT INTO ten_table(cot1,cot2,cot3,cot4) VALUES ('$value1','$value','$value3','$value4');

Update dòng 1

UPDATE ten_table SET cot1='$value1', cot2='$value2', cot3='$value3', cot4='$value4' where id = 1 limit 1;

Xóa dòng 1

Delete From ten_table where id = 1 limit 1;

Đếm số dòng

Select count(id) as total from ten_table;

Thêm chỉ mục Index.

ALTER TABLE ten_table ADD INDEX cot1 (cot1);

Drop Index

ALTER TABLE `Ten_Bang` DROP INDEX `Ten_cot_index`

Dump all database

mysqldump -u [uname] -p[pass] --all-databases | gzip > db_backup.sql.gz

Nâng cao.

Full text search

ALTER TABLE ten_table ADD FULLTEXT INDEX cot2 (cot2);

SELECT * FROM ten_table WHERE MATCH(cot2) AGAINST ('keyword' IN NATURAL LANGUAGE MODE);

Hoặc

Tạo Index FULLTEXT cho các cột muốn truy vấn

ALTER TABLE `TenBang` ADD FULLTEXT INDEX `CotFullText` (`Cot1`, `Cot2`);

Truy vấn

SELECT * FROM `TenBang` WHERE MATCH(`Cot1`, `Cot2`) AGAINST ('Tim cai gi do');

Xóa dữ liệu trùng nhau của dòng.

Đơn giản là add index là unique và bỏ qua lỗi, các dòng trùng nhau sẽ tự động xóa.

ALTER IGNORE TABLE ten_table ADD UNIQUE INDEX cot3 (cot3);

Replace trong MySQL

UPDATE `ten_table`
SET cot = REPLACE (cot, 'string_search', 'string_replace')
WHERE `cot` LIKE '%dieu_kien_gi_do%'

Update String Cột 1 sang Cột 2

UPDATE `ten_table` SET `Cot2`=LEFT(`Cot1`, 10);

Update nếu trùng nhau.

INSERT INTO ten_table(cot1,cot2,cot3,cot4) VALUES ('$value1','$value2','$value3','$value4') ON DUPLICATE KEY UPDATE cot1=VALUES(cot1), cot2=VALUES(cot2), cot3=VALUES(cot3), cot4=VALUES(cot4);

Lấy danh sách hàng có giá trị trùng nhau

SELECT name, COUNT(*) c FROM table GROUP BY name HAVING c > 1;

Tối ưu Select với Table hàng triệu dòng.

Thường các truy vấn có order và limit mà số dòng trong table lên đến hàng triệu thì truy vấn rất chậm, mẹo sau đây giúp tăng tốc độ truy vấn của bạn lên gấp nhiều lần.

SELECT  t.* FROM (SELECT `id` FROM `ten_table` FORCE INDEX (ten_table) WHERE `cot1` like '$value1' ORDER BY `cot2` DESC LIMIT 0, 50) q JOIN `ten_table` t ON t.id = q.id;

Tối ưu truy vấn với Timestamp của MySQL

Bình thường khi muốn tìm những dòng có timestamp là hôm nay chúng ta thường có truy vấn như sau:

SELECT * FROM Table WHERE DATE(`timestamp`) = CURDATE();

Nếu table chỉ với vài ngàn dòng thì không sao, nhưng khi đến cả triệu dòng thì truy vấn trên hơi chậm.

Chúng ta có thể đối thành truy vấn như sau:

SELECT * FROM Table WHERE timestamp BETWEEN '2018-12-07 00:00:01' AND '2018-12-07 23:59:59';

Hoặc

SELECT * FROM Table WHERE `timestamp` >= NOW() - INTERVAL 1 DAY

Tạo cache cho MySQL Query

Tối ưu cho VPS có ít ram

mysql -u root -p

Nhập mật khẩu SQL và kiểm tra tình trạng cache hiện tại bằng lệnh

show variables like 'query_cache_%';

Kết quả trả về tương tự như sau:

+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+

Chỉnh sửa trong file /etc/my.cnf (Red Hat) hoặc /etc/mysql/my.cnf (Debian)

nano /etc/my.cnf

Điều chỉnh lại theo ý bạn

query_cache_limit = 3M
query_cache_size = 128M

Sau khi thêm thì nội dung sẽ như sau:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
query_cache_limit = 3M
query_cache_size = 128M
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Khởi động lại mysql

service mysql restart

nếu bị lỗi mysql: unrecognized service thì sửa thành:

service mysqld restart

Kiểm tra lại bằng lệnh

show variables like 'query_cache_%';

Kết quả

+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| query_cache_limit            | 3145728   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 134217728 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+

Dòng lệnh xem và xóa user + database

Đôi khi có những website không còn hoạt động trên máy chủ và bạn cần xóa nó đi cho nhẹ nhàng thì đây là một số lệnh cơ bản.

mysql -u root -p

trong Centminmod nếu quên pass thì gõ

cat ~/.my.cnf

nhập mật khẩu của MySQL -> nếu đăng nhập thành công thì dấu nhắc sẽ giống thế này: mysql>

Xem danh sách Database:

SHOW DATABASES;

Xóa Database

DROP DATABASE ten_database_muon_xoa;

Xem danh sách User

SELECT User FROM mysql.user;

Xóa User

DROP USER 'testuser'@'localhost';

Reset mật khẩu MySQL trong CentOS

service mysql stop

sudo mysqld_safe --skip-grant-tables &

mysql -u root

use mysql;

update user set password=PASSWORD("PUT_A_NEW_PASSWD_HERE") where User='root';

flush privileges;

exit

service mysql stop

service mysql start

Kết hợp nhiều Column thành 1 Column

Tạo 1 cột mới

ALTER TABLE TenBang ADD COLUMN TenCot VARCHAR(50);

Update dữ liệu hiện tại sang TenCot

UPDATE TenBang SET TenCot = CONCAT(Cot1, Cot2, Cot3);

Update dữ liệu trong tương lai khi có hành động Insert hoặc Update

CREATE TRIGGER insert_trigger

BEFORE INSERT ON TenBang

FOR EACH ROW

SET new.TenCot = CONCAT(new.Cot1, new.Cot2, new.Cot3);

CREATE TRIGGER update_trigger

BEFORE UPDATE ON TenBang

FOR EACH ROW

SET new.TenCot = CONCAT(new.Cot1, new.Cot2, new.Cot3);