MySQL常见操作指令

什么是SQL&＃xff1f;

SQL&＃xff08;Structured Query Language&＃xff09;用于访问和操作数据库的结构化查询语言。

数据库包含一个或多个表&＃xff0c;每个表均有名称标识&＃xff0c;包含数据的记录&＃xff08;行&＃xff09;。

典型的SQL语句

1. SELEC语句

SELECT DISTINCT name
FROM accounts
WHERE mobile IS NOT NULLAND is_activated &＃61; TRUE
ORDER BY created_at;SELECT *
FROM roles
WHERE project_id &＃61; -1
LIMIT 4;-- 统计计算
SELECT count(id), sum(id), avg(id), max(project_id), min(project_id) project_id
FROM products
WHERE name LIKE &＃39;%o&＃39; AND name LIKE &＃39;n%&＃39;
group by project_id;SELECT *
FROM organizations_editions
WHERE edition_id BETWEEN 1 AND 7AND state in (2, 6);-- having与group by联用&＃xff0c;不能和where共存
SELECT project_id, string_agg(text(name), &＃39;, &＃39;)
FROM products
GROUP BY project_id
HAVING count(1) > 3;-- exists语句
SELECT *
FROM products
WHERE EXISTS(SELECT * FROM projects WHERE projects.disabled &＃61; TRUE AND products.disabled &＃61; FALSE);-- ALL && ANY(ANY任意匹配&＃xff0c;有返回值&＃xff1b;ALL需要全匹配&＃xff0c;无返回)
SELECT *
FROM editions_modules
WHERE module_id &＃61; ANY (SELECT id FROM modules WHERE key &＃61; &＃39;overview&＃39;);SELECT *
FROM editions_modules
WHERE module_id &＃61; ALL (SELECT id FROM modules WHERE key &＃61; &＃39;overview&＃39;);-- SELECT INTO复制一个表old_table&＃xff0c;到新数据库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 &＃61; 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