什么是SQL?
SQL(Structured Query Language)用于访问和操作数据库的结构化查询语言。
数据库包含一个或多个表,每个表均有名称标识,包含数据的记录(行)。
典型的SQL语句
1. SELEC语句
SELECT DISTINCT name
FROM accounts
WHERE mobile IS NOT NULL
AND is_activated = TRUE
ORDER BY created_at;
SELECT *
FROM roles
WHERE project_id = -1
LIMIT 4;
-- 统计计算
SELECT count(id), sum(id), avg(id), max(project_id), min(project_id) project_id
FROM products
WHERE name LIKE '%o' AND name LIKE 'n%'
group by project_id;
SELECT *
FROM organizations_editions
WHERE edition_id BETWEEN 1 AND 7
AND state in (2, 6);
-- having与group by联用,不能和where共存
SELECT project_id, string_agg(text(name), ', ')
FROM products
GROUP BY project_id
HAVING count(1) > 3;
-- exists语句
SELECT *
FROM products
WHERE EXISTS(SELECT * FROM projects WHERE projects.disabled = TRUE AND products.disabled = FALSE);
-- ALL && ANY(ANY任意匹配,有返回值;ALL需要全匹配,无返回)
SELECT *
FROM editions_modules
WHERE module_id = ANY (SELECT id FROM modules WHERE key = 'overview');
SELECT *
FROM editions_modules
WHERE module_id = ALL (SELECT id FROM modules WHERE key = 'overview');
-- SELECT INTO复制一个表old_table,到新数据库new_db的新new_table表
SELECT *
INTO new_table IN new_db
FROM old_table
WHERE condition;
2. INSERT INTO语句
INSERT INTO users_roles(user_id, role_id, enable)
VALUE(1, 1, TRUE)
3. UPDATE语句
UPDATE users
SET disabled = TRUE
WHERE email is NULL AND mobile IS NOT NULL;
4. DELETE语句
DELETE FROM users
WHERE name IS NULL;
5. JOIN / INNER JOIN / LEFT JOIN / RIGHT JOIN / FULL JOIN / SELF JOIN