MySQL is very popular, used a lot, but people often forget its syntax, when you need to use it again, go to google. So this article will summarize the syntax, from basic to advanced, so that everyone can use it whenever needed.
Connect to MySQL Server
The first step in working with the MySQL database is to connect to it. In the terminal, type the command:
1 2 | mysql [-h machine] -u <user> -p [db_name] |
If the server and client are both running on the same machine, there’s no need to add -h
. db_name
also optional, because in the next section we will see that it is possible to create and change the current database with just one statement. To end the connection to MySQL, we can type q
, quit
or Ctrl + D
Create User and Database
MySQL works with the default user as root, but it should only be used for database management, not for data manipulation. That is why we should create users.
1 2 3 | CREATE USER 'mike' IDENTIFIED BY 'difficultpassword' CREATE USER 'mike'@'localhost' IDENTIFED BY 'difficultpassword' |
In the above code, we do not create 2 users, but 1 user that can be used even when the client is inside or outside the server. Now we will see how to create a database and authorize different users.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | -- Tạo database CREATE DATABASE University; -- Chọn database để sử dụng USE University; -- Phân quyền GRANT ALL ON University TO mike GRANT INSERT, UPDATE ON University.Students TO william GRANT SELECT(id, name) ON University.Teachers TO john -- Xóa database DROP DATABASE University; |
Create Table
Tables are the core concept of database management systems such as MySQL. We’ll learn how to create a simple table, and how to set primary keys, restrictions, foreign keys, and default values.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | CREATE TABLE Teachers (name VARCHAR(30), age INT); -- Primary key: CREATE TABLE Teachers (id INT PRIMARY KEY, name VARCHAR(30), age INT); # primary key: id, không thể lặp lại CREATE TABLE Students (id INT, name VARCHAR(30), age INT, PRIMARY KEY (code, name, age)); # primary key là tổ hợp CREATE TABLE Subjects (id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, name VARCHAR(30)); # primary key tăng tự động, bắt đầu từ 1, 2, 3... -- Restrictions: CREATE TABLE Teachers (id INT PRIMARY KEY NOT NULL, name VARCHAR(30) NOT NULL, age INT NOT NULL); # NOT NULL: giá trị cột này không thể rỗng CREATE TABLE Teachers (id INT PRIMARY KEY, name VARCHAR(30), age INT, CHECK (age>18 and age<70)); # CHECK: giá trị của cột phải tuân theo điều kiện CREATE TABLE Students (id INT, name VARCHAR(30), age INT, passport VARCHAR (20), UNIQUE(passport)); # UNIQUE: giá trị của cột này là duy nhất, không trùng lặp giữa các bản ghi. -- Foreign keys: CREATE TABLE Studies (student INT NOT NULL, subject INT NOT NULL, FOREIGN KEY (student) REFERENCES Students(id), FOREIGN KEY (subject) REFERENCES Subjects(id)); # foreign keys: mối quan hệ giữa các bảng -- Default values: CREATE TABLE Students (id INT, name VARCHAR(30), age INT, country VARCHAR(20) DEFAULT "Spain"); # nếu thuộc tính "country" không được chỉ định, nó sẽ lưu giá trị mặc định là "Spain" |
Add a record
After creating the table, the next step is to add data. The next example shows how to add 1 record and multiple records at the same time
1 2 3 4 5 6 7 8 9 | -- Thêm 1 bản ghi, cung cấp giá trị cho từng cột INSERT INTO Students VALUES (1, 'Peter', 25); -- Thêm 1 bản ghi, không cung cấp đủ giá trị cho tất cả các cột INSERT INTO Students(id, name) VALUES (1, 'Peter'); -- Thêm nhiều bản ghi cùng lúc INSERT INTO Students VALUES (1, 'Peter', 25), (2, 'Frank', 19), (3, 'William', 21); |
Execute the query
The query will be completed with just one SELECT
, which allows us to retrieve data from the database. MySQL allows us to execute complex queries, pull data from multiple tables, or create logical operators from the results of other queries.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | -- Truy vấn cơ bản SELECT * FROM Students; -- Lấy 1 vài cột và lọc kết quả SELECT name, country FROM Students WHERE age>28; -- Giới hạn số lượng kết quả trả về và sắp xếp theo trình tự alphabetically SELECT * FROM Students SORT BY name LIMIT 20; -- Lấy tổng số học sinh của mỗi quốc gia SELECT COUNT(age) FROM Students GROUP BY country; -- Lấy tuổi trung bình của các học sinh trong mỗi quốc gia, và chỉ lấy những kết quả nhỏ hơn 30 SELECT AVG(age) FROM Students GROUP BY country HAVING AVG(age)<30; -- Lấy tên và tuổi của tất cả học sinh và giá viên, nếu dùng UNION thì sẽ chỉ lấy 1 lần nếu giá trị bị duplicate, UNION ALL thì cho phép cả các giá trị duplicate lặp lại SELECT (name, age) FROM Students UNION ALL SELECT (name, age) FROM Teachers; -- Join 2 bảng Students và Grades, lấy dữ liệu từ cả 2 bảng SELECT Student.name, Grades.course, Grades.value FROM Students INNER JOIN Grades ON Students.id = Grades.student_id; -- Lấy tên của các học sinh lớp A SELECT name FROM Students WHERE id IN (SELECT student_id FROM Grades WHERE value=='A'); |
Create View
View allows us to wrap SELECT
into a view like a new table. We can then set permissions for users to that view. However, if the SELECT
is wrapped with calculating functions such as SUM
, MIN
, … or using GROUP BY
, DISTINCT
, …, it is not possible to perform INSERT
, UPDATE
, or DELETE
with that view. .
1 2 3 4 5 6 7 | -- Câu này SELECT (id, name, age) FROM Students UNION ALL SELECT (id, name, age) FROM Teachers; -- sẽ tương tự với CREATE VIEW People AS SELECT (id, name, age) FROM Students UNION ALL SELECT (id, name, age) FROM Teachers; SELECT * from People; |
Make Transactions
Transaction is a group of commands, if an instruction in the transaction fails, MySQL will undo the previous commands.
1 2 3 4 5 6 7 8 9 10 11 | -- Enabling và disabling transactions tự động SET AUTOCOMMIT = 1; -- mỗi câu SQL là 1 transaction; nó sẽ tự động commit vào DB (mặc định sẽ là cái nafy) SET AUTOCOMMIT = 0; -- câu SQL không được tự động commit vào DB, vì vậy phải thực hiện thủ công với lệnh COMMIT COMMIT; -- commits transaction -- Bắt đầu 1 transaction START TRANSACTION; -- ... các lệnh muốn thực hiện trong transaction ... COMMIT; |
Create Stored Procedure
A Stored Procedure is a sequence of SQL statements that can be invoked at any time from the console or from a .sql
file. It is similar to function in programming languages.
1 2 3 4 5 6 7 8 9 10 | DELIMITER // CREATE PROCEDURE get_age(IN user_id INT, OUT user_age INT) -- procedure có 1 tham số truyền vào và 1 biến lưu giá trị trả về BEGIN SELECT age INTO user_age FROM Students WHERE id = user_id; -- một vài lệnh gì đó khác -- END // DELIMITER ; CALL get_age(12, @age); -- gọi procedure, truyền 1 tham số và lưu trữ kết quả trong biến @age |
Play with variables
Variables are a useful way to temporarily store the results of the query or the value of a column in a record for later use. There are two main types of variables in MySQL: local and user-defined (also known as session variables). The first is declared prior to use, and their scope is limited to the stored procedure
where they are defined. Type 2 is not declared in advance, its value can be used at any time, but only in the session created by the client.
1 2 3 4 5 6 7 8 9 | -- Local variables (phải được khai báo ở trong STORED PROCEDURE) DECLARE john_age INT; -- khai báo biến SET john_age = 21; -- set giá trị cứng cho biến SELECT age INTO jonh_age FROM Students WHERE id = 10; -- lưu kết quả câu query vào trong biến -- Session (user-defined) variables SET @current_age = 21; -- không cần khai báo trước SELECT age INTO @current_age FROM Students WHERE id = 10; -- lưu kết quả câu query vào trong biến |
Play with Cursors
A Cursor is a loop-like tool that runs through each record in the result of a query. It is very useful when you want to convert each record into an object, for example. The way to declare and use is also very easy:
1 2 3 4 5 6 7 8 9 10 11 | -- Khai báo cursor DECLARE end_cursor BOOLEAN DEFAULT FALSE; -- trả về true khi cursor chạy đến bản ghi cuối cùng DECLARE my_cursor CURSOR FOR SELECT * from Students; DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_cursor = TRUE; -- chạy vòng lặp thông qua cursor WHILE NOT end_cursor DO FETCH cursor INTO @my_student; -- Làm cái gì bạn muốn với bản ghi cursor -- END WHILE; |
Flow control
Inside the stored procedure, we can manipulate the flow, just like in other programming languages
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | -- if-else IF boolean_condition THEN -- ... instructions ... [ELSEIF boolean_condition THEN] -- ... instructions ... ELSE -- ... instructions ... END IF -- normal loop LOOP -- ... instructions ... LEAVE; -- cái này vô cùng quan trọng để rời khỏi vòng lặp, nếu không, nó sẽ chạy mãi mãi END LOOP; -- while loop WHILE boolean_condition DO -- ... instructions ... END WHILE; -- do-while loop REPEAT -- ... instructions ... UNTIL boolean_condition; END REPEAT; |
Create Trigger
Trigger is a method that is run before or after an action, such as insert, update, or delete in a table or view. We need to be careful with triggers as it can consume quite a lot of server resources.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | -- Triggered chạy sau INSERT trong table "Students" CREATE TRIGGER my_trigger AFTER INSERT ON Students FOR EACH ROW BEGIN -- ... lệnh muốn thực hiện ... -- END; -- Triggered chạy trước DELETE trong bảng "Teachers" CREATE TRIGGER my_trigger BEFORE DELETE ON Teachers FOR EACH ROW BEGIN -- ... lệnh muốn thực hiện ... -- END; |
Create a Scheduled Event
Scheduled Event is a trigger that is executed at a specific time. There are events that happen once, scheduled on a specific date, time, or recurring events, run every minute, hour, day, … in a fixed time period.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | SET GLOBAL event_scheduler = ON; -- chắc chắn rằng MySQL event scheduler đã được set ON -- Sự kiện một lần CREATE EVENT IF NOT EXISTS my_event ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 12 HOUR -- event được chạy vào 12h và sau đó sẽ bị xóa DO DELETE * FROM Logs; CREATE EVENT IF NOT EXISTS my_event ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 24 MINUTE -- sự kiện sẽ được chạy sau 24 phút... ON COMPLETION PRESERVE -- ... sau đó sẽ bị xóa DO DELETE * FROM Logs; -- Sự kiện định kì CREATE EVENT IF NOT EXISTS my_event ON SCHEDULE EVERY 1 HOUR -- chạy mỗi giờ STARTS CURRENT_TIMESTAMP -- ... từ hiện tại ... ENDS CURRENT_TIMESTAMP + INTERVAL 1 DAY -- ... cho đến ngày hôm sau DO DELETE * FROM Logs; |
MySQL is very complicated and has so many things to say that it cannot be covered in this article. However the above should be quite enough for most general purposes, if you need more, you can consult this official document .
Source: https://medium.com/better-programming/the-mysql-cheatsheet-we-all-need-d1af0377bdc6