SQLize | PHPize | SQLtest

-- 以下SQL代码刻意聚合了大量常用关键字用于演示，实际场景中不会如此混杂使用 -- /* 1. DDL语句 (数据定义) */ -- CREATE DATABASE company; -- CREATE DATABASE 创建数据库 -- USE company; -- USE 切换数据库 CREATE TABLE employees ( -- CREATE TABLE 创建表 id INT PRIMARY KEY AUTO_INCREMENT, -- PRIMARY KEY主键 AUTO_INCREMENT自增 name VARCHAR(100) NOT NULL, dept_id INT CHECK(dept_id > 0), -- CHECK约束 salary DECIMAL(10,2) DEFAULT 0.00, -- DEFAULT默认值 hire_date DATE, CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(id) -- FOREIGN KEY外键 ) ENGINE=InnoDB; ALTER TABLE employees ADD COLUMN email VARCHAR(255); -- ALTER TABLE...ADD COLUMN 添加列 CREATE INDEX idx_name ON employees(name); -- CREATE INDEX 创建索引 DROP INDEX idx_name ON employees; -- DROP INDEX 删除索引 /* 2. DML语句 (数据操作) */ INSERT INTO employees (name, dept_id, salary, hire_date) -- INSERT INTO 插入数据 VALUES ('张三', 1, 7500.00, CURDATE()); UPDATE employees -- UPDATE 更新数据 SET salary = salary * 1.1 WHERE dept_id = 2; -- SET修改值 WHERE条件过滤 DELETE FROM employees -- DELETE 删除数据 WHERE hire_date < '2020-01-01' AND salary < 5000; -- AND逻辑与 TRUNCATE TABLE departments; -- TRUNCATE 清空表 /* 3. 查询语句 */ SELECT DISTINCT dept_id FROM employees; -- SELECT DISTINCT 去重查询 SELECT e.name, d.dept_name -- SELECT 选择列 FROM employees AS e -- FROM 指定表 + AS别名 INNER JOIN departments d ON e.dept_id = d.id -- INNER JOIN 内连接 WHERE salary BETWEEN 5000 AND 10000 -- BETWEEN范围查询 AND name LIKE '张%' -- LIKE模糊查询 ORDER BY hire_date DESC; -- ORDER BY排序 + DESC降序 SELECT dept_id, AVG(salary) AS avg_sal -- AVG聚合函数 FROM employees WHERE salary > ANY (SELECT salary FROM employees WHERE dept_id = 3) -- ANY子查询 GROUP BY dept_id -- GROUP BY分组 HAVING avg_sal > 8000 -- HAVING分组后过滤 LIMIT 5 OFFSET 2; -- LIMIT限制结果 OFFSET偏移 /* 4. 事务控制 */ START TRANSACTION; -- 开始事务 SAVEPOINT sp1; -- 创建保存点 -- [...] 执行操作 ROLLBACK TO sp1; -- 回滚到保存点 COMMIT; -- 提交事务 /* 5. 高级功能 */ WITH cte AS ( -- WITH公用表表达式(CTE) SELECT dept_id, COUNT(*) AS cnt FROM employees GROUP BY dept_id ) SELECT * FROM cte WHERE cnt > 10; SELECT name, CASE WHEN salary > 10000 THEN '高薪' -- CASE WHEN条件判断 WHEN salary > 5000 THEN '中薪' ELSE '低薪' END AS salary_level, COALESCE(email, '未设置') AS email -- COALESCE处理NULL FROM employees; /* 6. 权限控制 */ GRANT SELECT, INSERT ON employees TO user1; -- GRANT授权 REVOKE DELETE ON employees FROM user1; -- REVOKE撤销权限 /* 7. 其他关键字示例 */ EXPLAIN SELECT * FROM employees; -- EXPLAIN执行计划 SELECT * FROM employees WHERE dept_id IN (1,3,5) -- IN成员查询 AND EXISTS (SELECT 1 FROM departments WHERE id = dept_id); -- EXISTS存在性查询 SELECT * FROM employees FOR UPDATE; -- 行级锁 -- 窗口函数示例 SELECT name, salary, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dept_rank -- OVER窗口函数 FROM employees;