— 数据插入
INSERT INTO table_name (column1, column2, …)
VALUES (value1, value2, …);

— 数据更新
UPDATE table_name
SET column1 = value1, column2 = value2, …，更多学习教程公众号风哥教程itpux_com
WHERE condition;

— 数据删除
DELETE FROM table_name
WHERE condition;

— 创建表
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
…
);

— 修改表
ALTER TABLE table_name
ADD column datatype constraint;

— 删除表
DROP TABLE table_name;

— 创建索引
CREATE INDEX index_name
ON table_name (column1, column2, …);

— 删除索引
DROP INDEX index_name;

3.2 高级查询技巧

高级查询技巧包括：

from DB视频:www.itpux.com

— 左连接
SELECT *
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

— 右连接
SELECT *
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

— 全连接
SELECT *
FROM table1
FULL JOIN table2
ON table1.column = table2.column;

— 子查询
SELECT *
FROM table1
WHERE column IN (SELECT column FROM table2 WHERE condition);

— 聚合查询
SELECT COUNT(*), AVG(column), SUM(column), MAX(column), MIN(column)
FROM table1
GROUP BY column;

— 窗口函数
SELECT column,
ROW_NUMBER() OVER (ORDER BY column) AS row_num,
RANK() OVER (PARTITION BY column2 ORDER BY column3) AS rank_num
FROM table1;

— 递归查询
WITH RECURSIVE cte AS (
SELECT column FROM table1 WHERE condition
UNION ALL
SELECT t1.column FROM table1 t1
JOIN cte t2 ON t1.parent_column = t2.column
)
SELECT * FROM cte;

— 调用存储过程
CALL procedure_name(value1, @value2);
SELECT @value2;

— 创建函数
CREATE FUNCTION function_name (parameter1 datatype, parameter2 datatype)
RETURNS datatype
BEGIN
— 函数逻辑
DECLARE result datatype;
SET result = parameter1 + parameter2;
RETURN result;
END;

— 使用函数
SELECT function_name(value1, value2);

— 创建触发器
CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
— 触发器逻辑
SET NEW.create_time = CURRENT_TIMESTAMP;
END;