标准通用SQL
简介：跨数据库通用查询语言，兼容MySQL、Oracle、SQL Server、PostgreSQL
小白入门案例1：基础分页查询
SELECT name,age FROM user LIMIT 10;
小白入门案例2：条件过滤数据
SELECT * FROM user WHERE age >= 24 AND city = '深圳';
基础实操案例3：完整CRUD增删改
INSERT INTO user(name,age) VALUES('罗秀萍',28);
UPDATE user SET age=29 WHERE name='罗秀萍';
DELETE FROM user WHERE name='罗秀萍';
基础实操案例4：分组聚合统计
SELECT city,COUNT(id) AS user_num,AVG(age) FROM user GROUP BY city HAVING COUNT(id)>1;
进阶项目案例5：LEFT JOIN多表关联
SELECT u.name,o.amount FROM user u LEFT JOIN orders o ON u.id=o.user_id WHERE o.amount>100;
进阶项目案例6：EXISTS子查询优化
SELECT name FROM user u WHERE EXISTS(SELECT 1 FROM orders o WHERE o.user_id=u.id);
进阶项目案例7：建表+索引DDL语句
CREATE TABLE user(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50),age TINYINT);
CREATE INDEX idx_user_city ON user(city);
企业精通案例8：转账事务ACID
BEGIN;
UPDATE account SET balance=balance-100 WHERE id=1;
UPDATE account SET balance=balance+100 WHERE id=2;
COMMIT;
-- ROLLBACK;
企业精通案例9：窗口函数排名统计
SELECT name,city,age,ROW_NUMBER() OVER(PARTITION BY city ORDER BY age DESC) AS rk FROM user;
企业精通案例10：EXPLAIN执行计划调优
EXPLAIN SELECT name FROM user WHERE city='深圳';
-- 禁止模糊前缀 %xxx 导致索引失效