MySQL 数据库（三）：SQL 语法

一、SQL 语言分类分类全称作用关键字DDLData Definition Language定义数据结构CREATE、ALTER、DROPDMLData Manipulation Language操作数据INSERT、UPDATE、DELETEDQLData Query Language查询数据SELECTDCLData Control Language权限管理GRANT、REVOKETCLTransaction Control Language事务控制COMMIT、ROLLBACK、SAVEPOINT二、DDL数据库与表结构管理1. 数据库操作-- 创建数据库 CREATE DATABASE IF NOT EXISTS mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 查看所有数据库 SHOW DATABASES; -- 切换数据库 USE mydb; -- 查看当前数据库 SELECT DATABASE(); -- 删除数据库 DROP DATABASE IF EXISTS mydb;2. 创建表建表规范CREATE TABLE IF NOT EXISTS users ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 用户ID, username VARCHAR(50) NOT NULL UNIQUE COMMENT 用户名, email VARCHAR(100) NOT NULL UNIQUE COMMENT 邮箱, age INT DEFAULT 0 COMMENT 年龄, status TINYINT DEFAULT 1 COMMENT 状态0-禁用 1-正常, created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表;3. 修改表结构-- 添加列 ALTER TABLE users ADD COLUMN phone VARCHAR(20) COMMENT 手机号; -- 修改列类型 ALTER TABLE users MODIFY COLUMN phone VARCHAR(30); -- 修改列名 ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(30); -- 删除列 ALTER TABLE users DROP COLUMN mobile; -- 添加索引 CREATE INDEX idx_username ON users(username); CREATE UNIQUE INDEX idx_email ON users(email); -- 查看表结构 DESC users; SHOW CREATE TABLE users;4. 删除表DROP TABLE IF EXISTS users; TRUNCATE TABLE users; -- 清空数据保留结构三、DML数据操作1. INSERT插入数据-- 插入单条完整记录 INSERT INTO users (username, email, age) VALUES (张三, zhangsanexample.com, 25); -- 插入多条记录 INSERT INTO users (username, email, age) VALUES (李四, lisiexample.com, 30), (王五, wangwuexample.com, 28); -- 插入部分字段其他字段使用默认值 INSERT INTO users (username, email) VALUES (赵六, zhaoliuexample.com); -- 从另一张表插入 INSERT INTO users_backup SELECT * FROM users;2. UPDATE更新数据-- 更新单条记录必须带 WHERE UPDATE users SET age 26 WHERE username 张三; -- 更新多条记录 UPDATE users SET status 0 WHERE age 18; -- 多字段更新 UPDATE users SET age age 1, updated_at NOW() WHERE age 30; -- ⚠️ 不带 WHERE 会更新全表 UPDATE users SET status 1; -- 危险操作3. DELETE删除数据-- 删除单条记录 DELETE FROM users WHERE username 赵六; -- 删除多条记录 DELETE FROM users WHERE status 0; -- 删除全部数据但保留表结构 DELETE FROM users; -- 逐行删除速度慢 -- 更快的清空方式 TRUNCATE TABLE users; -- 重建表速度极快不能回滚4. REPLACE替换/插入-- 如果主键或唯一键冲突先删除再插入 REPLACE INTO users (id, username, email) VALUES (1, 张三, zhangsanexample.com);四、DQL数据查询核心1. 基础查询-- 查询所有字段 SELECT * FROM users; -- 查询指定字段 SELECT id, username, email, age FROM users; -- 查询并去重 SELECT DISTINCT age FROM users; -- 字段别名 SELECT id, username AS name, age FROM users; -- 限制返回行数 SELECT * FROM users LIMIT 10; SELECT * FROM users LIMIT 10 OFFSET 20; -- 第21-30条2. WHERE 条件过滤-- 比较运算 SELECT * FROM users WHERE age 18; SELECT * FROM users WHERE age BETWEEN 18 AND 30; SELECT * FROM users WHERE age IN (18, 25, 30); -- 字符串匹配 SELECT * FROM users WHERE username LIKE 张%; -- 以张开头 SELECT * FROM users WHERE username LIKE %三; -- 以三结尾 SELECT * FROM users WHERE username LIKE %小%; -- 包含小 -- 空值判断 SELECT * FROM users WHERE phone IS NULL; SELECT * FROM users WHERE phone IS NOT NULL; -- 逻辑组合 SELECT * FROM users WHERE age 18 AND status 1; SELECT * FROM users WHERE age 18 OR age 60; SELECT * FROM users WHERE NOT (status 0);3. 排序-- 单字段排序 SELECT * FROM users ORDER BY age DESC; -- 降序 SELECT * FROM users ORDER BY age ASC; -- 升序默认 -- 多字段排序 SELECT * FROM users ORDER BY age DESC, created_at ASC;4. 分组与聚合-- 常用聚合函数COUNT、SUM、AVG、MAX、MIN SELECT COUNT(*) AS total FROM users; SELECT AVG(age) AS avg_age FROM users; SELECT MAX(age) AS max_age, MIN(age) AS min_age FROM users; -- 分组统计 SELECT status, COUNT(*) AS count, AVG(age) AS avg_age FROM users GROUP BY status; -- 分组后过滤HAVING SELECT status, COUNT(*) AS count FROM users GROUP BY status HAVING count 5; -- 分组前过滤WHERE与分组后过滤HAVING的区别 SELECT status, COUNT(*) AS count FROM users WHERE age 18 -- 分组前过滤 GROUP BY status HAVING count 5; -- 分组后过滤5. 连接查询多表查询内连接INNER JOIN-- 查询用户及其订单 SELECT u.id, u.username, o.order_id, o.amount FROM users u INNER JOIN orders o ON u.id o.user_id;左连接LEFT JOIN-- 查询所有用户及其订单包括没有订单的用户 SELECT u.id, u.username, o.order_id, o.amount FROM users u LEFT JOIN orders o ON u.id o.user_id;右连接RIGHT JOIN-- 查询所有订单及其用户包括没有用户的订单极少使用 SELECT u.username, o.order_id, o.amount FROM users u RIGHT JOIN orders o ON u.id o.user_id;自连接-- 查询员工及其上级 SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id m.id;6. 子查询-- WHERE 中的子查询 SELECT * FROM users WHERE age (SELECT AVG(age) FROM users); -- FROM 中的子查询 SELECT avg_age FROM (SELECT AVG(age) AS avg_age FROM users) AS t; -- EXISTS 子查询 SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id u.id);五、常用函数字符串函数SELECT CONCAT(Hello, , World); -- Hello World SELECT LENGTH(Hello); -- 5 SELECT UPPER(hello); -- HELLO SELECT LOWER(HELLO); -- hello SELECT SUBSTRING(Hello World, 1, 5); -- Hello SELECT TRIM( Hello ); -- Hello SELECT REPLACE(Hello World, World, MySQL); -- Hello MySQL日期时间函数SELECT NOW(); -- 当前日期时间 SELECT CURDATE(); -- 当前日期 SELECT CURTIME(); -- 当前时间 SELECT DATE_ADD(NOW(), INTERVAL 1 DAY); -- 明天 SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH); -- 上个月 SELECT DATEDIFF(NOW(), 2024-01-01); -- 天数差 SELECT DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s); -- 格式化日期数值函数SELECT ROUND(3.14159, 2); -- 3.14 SELECT CEIL(3.14); -- 4 SELECT FLOOR(3.14); -- 3 SELECT ABS(-5); -- 5 SELECT RAND(); -- 随机数 (0-1)条件函数-- IF 函数 SELECT IF(age 18, 成年, 未成年) AS age_group FROM users; -- CASE WHEN类似 switch SELECT CASE WHEN age 18 THEN 未成年 WHEN age 30 THEN 青年 WHEN age 60 THEN 中年 ELSE 老年 END AS age_group FROM users;六、索引-- 创建索引 CREATE INDEX idx_age ON users(age); CREATE UNIQUE INDEX idx_email ON users(email); CREATE INDEX idx_age_status ON users(age, status); -- 联合索引 -- 查看索引 SHOW INDEX FROM users; -- 删除索引 DROP INDEX idx_age ON users;