SQL语法快速参考手册

基础查询

SELECT 查询

-- 基本查询
SELECT column1, column2 FROM table_name WHERE condition;

-- 分页查询
-- Oracle
SELECT * FROM (
    SELECT a.*, ROWNUM rnum FROM (
        SELECT * FROM table_name ORDER BY column1
    ) a WHERE ROWNUM <= 20
) WHERE rnum > 10;

-- MySQL
SELECT * FROM table_name ORDER BY column1 LIMIT 10 OFFSET 10;

连接查询

-- 内连接
SELECT a.*, b.* FROM table1 a INNER JOIN table2 b ON a.id = b.id;

-- 左外连接
SELECT a.*, b.* FROM table1 a LEFT JOIN table2 b ON a.id = b.id;

-- 右外连接
SELECT a.*, b.* FROM table1 a RIGHT JOIN table2 b ON a.id = b.id;

-- 全外连接（MySQL不支持，需要UNION模拟）
-- Oracle
SELECT a.*, b.* FROM table1 a FULL OUTER JOIN table2 b ON a.id = b.id;

-- MySQL
SELECT a.*, b.* FROM table1 a LEFT JOIN table2 b ON a.id = b.id
UNION
SELECT a.*, b.* FROM table1 a RIGHT JOIN table2 b ON a.id = b.id WHERE a.id IS NULL;

数据操作

INSERT 插入

-- 单行插入
INSERT INTO table_name (col1, col2) VALUES (val1, val2);

-- 多行插入
-- Oracle
INSERT ALL
    INTO table_name VALUES (val1, val2)
    INTO table_name VALUES (val3, val4)
SELECT * FROM dual;

-- MySQL
INSERT INTO table_name (col1, col2) VALUES 
    (val1, val2),
    (val3, val4);

-- 从其他表插入
INSERT INTO table1 SELECT * FROM table2 WHERE condition;

UPDATE 更新

-- 基本更新
UPDATE table_name SET col1 = val1, col2 = val2 WHERE condition;

-- 使用子查询更新
UPDATE table1 t1 SET col1 = (
    SELECT col2 FROM table2 t2 WHERE t2.id = t1.id
) WHERE condition;

-- UPSERT操作
-- Oracle (MERGE)
MERGE INTO table1 t1
USING (SELECT id, val FROM table2) t2
ON (t1.id = t2.id)
WHEN MATCHED THEN UPDATE SET t1.col1 = t2.val
WHEN NOT MATCHED THEN INSERT (id, col1) VALUES (t2.id, t2.val);

-- MySQL (INSERT ... ON DUPLICATE KEY UPDATE)
INSERT INTO table1 (id, col1) VALUES (1, 'val')
ON DUPLICATE KEY UPDATE col1 = VALUES(col1);

DELETE 删除

-- 基本删除
DELETE FROM table_name WHERE condition;

-- 使用子查询删除
DELETE FROM table1 WHERE id IN (
    SELECT id FROM table2 WHERE condition
);

表结构操作

CREATE TABLE 创建表

-- Oracle
CREATE TABLE table_name (
    id NUMBER(10) PRIMARY KEY,
    name VARCHAR2(50) NOT NULL,
    salary NUMBER(8,2),
    hire_date DATE DEFAULT SYSDATE,
    CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(id)
);

-- MySQL
CREATE TABLE table_name (
    id INT(10) PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    salary DECIMAL(8,2),
    hire_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE 修改表

-- 添加列
-- Oracle
ALTER TABLE table_name ADD (col1 VARCHAR2(50), col2 NUMBER(10));

-- MySQL
ALTER TABLE table_name 
ADD COLUMN col1 VARCHAR(50),
ADD COLUMN col2 INT(10);

-- 修改列
-- Oracle
ALTER TABLE table_name MODIFY (col1 VARCHAR2(100));

-- MySQL
ALTER TABLE table_name MODIFY COLUMN col1 VARCHAR(100);

-- 删除列
ALTER TABLE table_name DROP COLUMN col1;

索引操作

创建索引

-- 唯一索引
CREATE UNIQUE INDEX idx_name ON table_name(column1);

-- 复合索引
CREATE INDEX idx_comp ON table_name(col1, col2, col3);

-- 函数索引（Oracle）
CREATE INDEX idx_func ON table_name(UPPER(column1));

-- 前缀索引（MySQL）
CREATE INDEX idx_prefix ON table_name(column1(10));

管理索引

-- 删除索引
-- Oracle
DROP INDEX idx_name;

-- MySQL
DROP INDEX idx_name ON table_name;

-- 查看索引
-- Oracle
SELECT index_name, table_name FROM user_indexes WHERE table_name = 'TABLE_NAME';

-- MySQL
SHOW INDEX FROM table_name;

视图操作

创建视图

-- 基本视图
CREATE VIEW view_name AS
SELECT col1, col2 FROM table_name WHERE condition;

-- 可更新视图（MySQL）
CREATE VIEW view_name AS
SELECT col1, col2 FROM table_name WHERE condition
WITH CHECK OPTION;

-- 只读视图（Oracle）
CREATE VIEW view_name AS
SELECT col1, col2 FROM table_name WHERE condition
WITH READ ONLY;

管理视图

-- 修改视图
CREATE OR REPLACE VIEW view_name AS
SELECT col1, col2, col3 FROM table_name WHERE condition;

-- 删除视图
DROP VIEW view_name;

存储过程和函数

存储过程

-- Oracle
CREATE OR REPLACE PROCEDURE proc_name(
    p_param1 IN NUMBER,
    p_param2 OUT VARCHAR2
)
IS
    v_var VARCHAR2(100);
BEGIN
    SELECT col1 INTO v_var FROM table_name WHERE id = p_param1;
    p_param2 := v_var;
EXCEPTION
    WHEN OTHERS THEN
        p_param2 := 'Error';
END;
/

-- MySQL
DELIMITER //
CREATE PROCEDURE proc_name(
    IN p_param1 INT,
    OUT p_param2 VARCHAR(100)
)
BEGIN
    DECLARE v_var VARCHAR(100);
    SELECT col1 INTO v_var FROM table_name WHERE id = p_param1;
    SET p_param2 = v_var;
END //
DELIMITER ;

函数

-- Oracle
CREATE OR REPLACE FUNCTION func_name(p_param NUMBER)
RETURN NUMBER
IS
    v_result NUMBER;
BEGIN
    SELECT col1 INTO v_result FROM table_name WHERE id = p_param;
    RETURN v_result;
END;
/

-- MySQL
DELIMITER //
CREATE FUNCTION func_name(p_param INT)
RETURNS DECIMAL(8,2)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE v_result DECIMAL(8,2);
    SELECT col1 INTO v_result FROM table_name WHERE id = p_param;
    RETURN v_result;
END //
DELIMITER ;

事务控制

事务管理

-- Oracle（隐式事务）
UPDATE table1 SET col1 = val1;
UPDATE table2 SET col2 = val2;
COMMIT;
-- 或
ROLLBACK;

-- MySQL（显式事务）
START TRANSACTION;
UPDATE table1 SET col1 = val1;
UPDATE table2 SET col2 = val2;
COMMIT;
-- 或
ROLLBACK;

-- 保存点
SAVEPOINT sp1;
-- 操作...
ROLLBACK TO SAVEPOINT sp1;

常用函数对比

字符串函数

功能	Oracle	MySQL
连接	`str1 \|\| str2`	`CONCAT(str1, str2)`
长度	`LENGTH(str)`	`LENGTH(str)`
子串	`SUBSTR(str, 1, 3)`	`SUBSTRING(str, 1, 3)`
替换	`REPLACE(str, old, new)`	`REPLACE(str, old, new)`
去除空格	`TRIM(' ' FROM str)`	`TRIM(str)`

数值函数

功能	Oracle	MySQL
四舍五入	`ROUND(num, 2)`	`ROUND(num, 2)`
截断	`TRUNC(num, 2)`	`TRUNCATE(num, 2)`
取模	`MOD(num, 10)`	`MOD(num, 10)`
幂运算	`POWER(num, 2)`	`POW(num, 2)`

日期函数

功能	Oracle	MySQL
当前日期	`SYSDATE`	`NOW()`
日期加减	`SYSDATE + 7`	`DATE_ADD(NOW(), INTERVAL 7 DAY)`
格式化	`TO_CHAR(date, 'YYYY-MM-DD')`	`DATE_FORMAT(date, '%Y-%m-%d')`
字符串转日期	`TO_DATE('2023-01-01', 'YYYY-MM-DD')`	`STR_TO_DATE('2023-01-01', '%Y-%m-%d')`

数据类型对比

数值类型

Oracle	MySQL	说明
`NUMBER(10)`	`INT(10)`	整数
`NUMBER(10,2)`	`DECIMAL(10,2)`	小数
`BINARY_FLOAT`	`FLOAT`	浮点数

字符串类型

Oracle	MySQL	说明
`VARCHAR2(255)`	`VARCHAR(255)`	可变长度字符串
`CHAR(10)`	`CHAR(10)`	固定长度字符串
`CLOB`	`TEXT`	大文本

日期时间类型

Oracle	MySQL	说明
`DATE`	`DATE`	日期
`TIMESTAMP`	`DATETIME`	日期时间
`TIMESTAMP`	`TIMESTAMP`	时间戳

性能优化技巧

查询优化

-- 使用EXISTS代替IN
SELECT * FROM table1 t1
WHERE EXISTS (
    SELECT 1 FROM table2 t2 WHERE t2.id = t1.id
);

-- 使用UNION ALL代替UNION（不需要去重时）
SELECT col1 FROM table1 WHERE condition1
UNION ALL
SELECT col1 FROM table2 WHERE condition2;

-- 限制结果集
-- Oracle
SELECT * FROM (
    SELECT a.*, ROWNUM rnum FROM (
        SELECT * FROM table_name ORDER BY col1
    ) a WHERE ROWNUM <= 10
) WHERE rnum > 0;

-- MySQL
SELECT * FROM table_name ORDER BY col1 LIMIT 10;

索引优化

-- 创建复合索引（最左前缀原则）
CREATE INDEX idx_comp ON table_name(col1, col2, col3);

-- 分析表统计信息
-- Oracle
ANALYZE TABLE table_name COMPUTE STATISTICS;

-- MySQL
ANALYZE TABLE table_name;

常用系统查询

查看表信息

-- Oracle
SELECT table_name, num_rows, blocks FROM user_tables WHERE table_name = 'TABLE_NAME';

-- MySQL
SHOW TABLE STATUS LIKE 'table_name';

查看列信息

-- Oracle
SELECT column_name, data_type, data_length, nullable 
FROM user_tab_columns 
WHERE table_name = 'TABLE_NAME';

-- MySQL
DESCRIBE table_name;
-- 或
SHOW COLUMNS FROM table_name;

查看约束信息

-- Oracle
SELECT constraint_name, constraint_type, column_name 
FROM user_cons_columns 
WHERE table_name = 'TABLE_NAME';

-- MySQL
SELECT 
    CONSTRAINT_NAME,
    CONSTRAINT_TYPE,
    COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE 
WHERE TABLE_NAME = 'table_name';

错误处理

常见错误及解决方案

ORA-00942: table or view does not exist
- 检查表名是否正确
- 检查用户权限
- 检查表是否在正确的schema中
ORA-00904: invalid identifier
- 检查列名是否正确
- 检查列名大小写
MySQL Error 1146: Table doesn't exist
- 检查表名是否正确
- 检查数据库是否选择正确
MySQL Error 1054: Unknown column
- 检查列名是否正确
- 检查表结构

最佳实践

命名规范
- 表名使用小写，单词间用下划线分隔
- 列名使用小写，单词间用下划线分隔
- 索引名使用 idx_表名_列名 格式
性能优化
- 合理使用索引
- 避免使用 SELECT *
- 使用 LIMIT 限制结果集
- 使用 EXISTS 代替 IN
数据安全
- 使用参数化查询防止SQL注入
- 定期备份数据
- 合理设置用户权限
代码维护
- 添加适当的注释
- 使用一致的命名规范
- 定期重构复杂查询

posted @ 2025-07-11 14:59 ceiloruz 阅读(52) 评论(0) 收藏举报

刷新页面返回顶部

SQL语法快速参考手册

SQL语法快速参考手册

基础查询

SELECT 查询

连接查询

数据操作

INSERT 插入

UPDATE 更新

DELETE 删除

表结构操作

CREATE TABLE 创建表

ALTER TABLE 修改表

索引操作

创建索引

管理索引

视图操作

创建视图

管理视图

存储过程和函数

存储过程

函数

事务控制

事务管理

常用函数对比

字符串函数

数值函数

日期函数

数据类型对比

数值类型

字符串类型

日期时间类型

性能优化技巧

查询优化

索引优化

常用系统查询

查看表信息

查看列信息

查看约束信息

错误处理

常见错误及解决方案

最佳实践

公告