Skip to main content

SQL

Basic​

USE sql_store;
-- Phần dưới được tính là một "Subquery"
SELECT DISTINCT * FROM customers
WHERE points BETWEEN 100 AND 200
AND WHERE points IN (150, 180)
AND WHERE points IS NOT NULL
GROUP BY country
ORDER BY first_name DESC
LIMIT 3;

Counting Records​

-- Hàm COUNT: return the number of records
SELECT COUNT(* | tên một column bất kỳ) FROM customers

like Operator​

-- Returns customers whose first name starts with "b"
SELECT * FROM customers
WHERE first_name LIKE ‘b%’
  • % : any number of characters
  • _ : exactly one character

regexp Operator​

SELECT * FROM customers
-- Returns customers whose first name starts with "a"
WHERE first_name REGEXP ‘^a’
-- Returns customers whose first letter NOT "a"; "c" or "f"
WHERE first_name LIKE ‘[!acf]%’
-- Returns customers whose first name starts with "my" or contains "se":
WHERE first_name REGEXP ‘^my|se’
  • ^: beginning of a string
  • $: end of a string
  • |: logical OR
  • [abc]: match any single characters
  • [a-d]: any characters from a to d

Joins​

Joins

Outer Join​

-- Return all customers whether they have any orders or not
SELECT * FROM customers AS c
LEFT JOIN orders AS o
-- Those 2 lines work the same
ON c.customer_id = o.customer_id
-- OR
USING (customer_id)

Unions​

-- Combine records from multiple result sets
SELECT name, address FROM customers
UNION
SELECT name, address FROM clients

Modify Data​

Insert Data​

(INSERT INTO | CREATE TABLE) customers(customer_id, name, phone, points)
VALUES (123, ‘Mosh’, NULL, DEFAULT), (LAST_INSERT_ID(), ‘Bob’, 0905112233, 10)
-- OR
(INSERT INTO | CREATE TABLE) customers AS (Subquery)

Update Data​

UPDATE invoices
SET value = value - 100
WHERE client_id IN
-- 1 in 2
(1, 9)
-- OR
(SELECT id FROM clients WHERE id IN (1, 9))

Delete Data​

DELETE FROM invoices WHERE …
-- ... tương tự như "WHERE" của Updating Data. Nếu ko có "WHERE" này thì là xóa hết cả Table "invoices"

Modify [Table|Database]​

(Create|Drop|Truncate)(Table|Database) customers

Insert/Delete columns​

ALTER TABLE customers
ADD Birthday DATE  -- DATE là `Datatype` của column `Birthday`
-- OR
DROP COLUMN Birthday