MySQL基础语法总结

MySQL基础语法总结

---

一、数据库操作

1. 创建数据库

CREATE DATABASE dbname;
CREATE DATABASE IF NOT EXISTS dbname; -- 避免重复创建

2. 选择数据库

USE dbname;

3. 删除数据库

DROP DATABASE dbname;
DROP DATABASE IF EXISTS dbname; -- 安全删除

4. 显示数据库

SHOW DATABASES; -- 显示所有数据库
SHOW CREATE DATABASE dbname; -- 显示创建语句

---

二、数据表操作

1. 创建表

CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY,username VARCHAR(50) NOT NULL,email VARCHAR(100) UNIQUE,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,age TINYINT UNSIGNED CHECK (age >= 18)
);

2. 修改表结构

ALTER TABLE users 
ADD COLUMN phone VARCHAR(15) AFTER email; -- 添加列ALTER TABLE users 
MODIFY COLUMN username VARCHAR(30); -- 修改列类型ALTER TABLE users 
DROP COLUMN phone; -- 删除列

3. 删除表

DROP TABLE users;
DROP TABLE IF EXISTS users; -- 安全删除

4. 显示表信息

SHOW TABLES; -- 显示所有表
DESCRIBE users; -- 显示表结构
SHOW CREATE TABLE users; -- 显示创建语句

---

三、数据操作（CRUD）

1. 插入数据

INSERT INTO users (username, email, age)
VALUES ('john_doe', 'john@example.com', 25);-- 批量插入
INSERT INTO users (username, email, age)
VALUES ('jane_smith', 'jane@example.com', 30),('bob_jones', 'bob@example.com', 28);

2. 查询数据

SELECT * FROM users; -- 查询所有列
SELECT username, email FROM users; -- 查询指定列
SELECT DISTINCT age FROM users; -- 去重查询

3. 更新数据

UPDATE users 
SET age = 26, email = 'john_new@example.com' 
WHERE id = 1;

4. 删除数据

DELETE FROM users WHERE id = 3;
TRUNCATE TABLE users; -- 清空表（更快）

---

四、查询进阶

1. WHERE 条件筛选

SELECT * FROM users
WHERE age > 25 AND email LIKE '%@example.com';-- 常用运算符
=, !=, >, <, >=, <=
BETWEEN, IN, NOT IN
LIKE, NOT LIKE (% 通配符)
IS NULL, IS NOT NULL

2. 排序与限制

SELECT * FROM users
ORDER BY age DESC, username ASC; -- 多列排序SELECT * FROM users
LIMIT 10 OFFSET 20; -- 分页查询（第3页，每页10条）

3. 聚合函数

SELECT COUNT(*) AS total_users,AVG(age) AS average_age,MAX(age) AS max_age,MIN(age) AS min_age,SUM(age) AS age_sum
FROM users;

4. 分组统计

SELECT age, COUNT(*) AS user_count
FROM users
GROUP BY age
HAVING COUNT(*) > 5; -- HAVING 筛选分组结果

---

五、表关系操作

1. 连接查询

-- 内连接（默认）
SELECT u.username, o.order_date
FROM users u
JOIN orders o ON u.id = o.user_id;-- 左外连接
SELECT u.username, o.order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;-- 右外连接
SELECT u.username, o.order_date
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

2. 子查询

SELECT * FROM users
WHERE age > (SELECT AVG(age) FROM users);-- IN 子查询
SELECT * FROM products
WHERE id IN (SELECT product_id FROM orders WHERE status = 'completed');

3. 联合查询

SELECT username FROM active_users
UNION
SELECT username FROM inactive_users; -- 自动去重SELECT username FROM active_users
UNION ALL
SELECT username FROM inactive_users; -- 保留重复

---

六、约束与索引

1. 常用约束

PRIMARY KEY     -- 主键
FOREIGN KEY     -- 外键
UNIQUE          -- 唯一约束
NOT NULL        -- 非空约束
DEFAULT         -- 默认值
CHECK           -- 检查约束
AUTO_INCREMENT  -- 自增

2. 索引管理

-- 创建索引
CREATE INDEX idx_email ON users(email);-- 唯一索引
CREATE UNIQUE INDEX idx_username ON users(username);-- 删除索引
DROP INDEX idx_email ON users;

---

七、常用函数

1. 字符串函数

CONCAT('Hello', ' ', 'World')  -- 字符串连接
SUBSTRING('MySQL', 2, 3)       -- 子串：ySQ
LENGTH('text')                 -- 字符串长度
UPPER('mysql'), LOWER('MySQL') -- 大小写转换
TRIM('  text  ')               -- 去除空格

2. 数值函数

ROUND(3.14159, 2)  -- 四舍五入：3.14
CEIL(2.3), FLOOR(2.7) -- 向上/向下取整
ABS(-10)            -- 绝对值
RAND()              -- 随机数(0-1)

3. 日期函数

NOW()               -- 当前日期时间
CURDATE()           -- 当前日期
CURTIME()           -- 当前时间
DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') -- 日期格式化
DATEDIFF('2023-12-31', '2023-01-01') -- 日期差(364)

4. 条件函数

-- IF 函数
SELECT username, IF(age >= 18, 'Adult', 'Minor') AS age_group
FROM users;-- CASE 语句
SELECT username,CASE WHEN age < 20 THEN 'Teen'WHEN age BETWEEN 20 AND 29 THEN '20s'ELSE '30+'END AS age_group
FROM users;

---

八、视图与存储过程

1. 视图

-- 创建视图
CREATE VIEW adult_users AS
SELECT * FROM users WHERE age >= 18;-- 使用视图
SELECT * FROM adult_users;-- 删除视图
DROP VIEW IF EXISTS adult_users;

2. 存储过程

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE GetUserCount(IN min_age INT, OUT user_count INT)
BEGINSELECT COUNT(*) INTO user_count FROM users WHERE age >= min_age;
END //
DELIMITER ;-- 调用存储过程
CALL GetUserCount(20, @count);
SELECT @count;

---

九、事务管理

START TRANSACTION;UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;-- 提交或回滚
COMMIT; -- 确认执行
ROLLBACK; -- 取消操作

ACID 特性：

• Atomicity（原子性）
• Consistency（一致性）
• Isolation（隔离性）
• Durability（持久性）

---

十、用户与权限

1. 创建用户

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

2. 授予权限

GRANT SELECT, INSERT ON dbname.* TO 'user'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;

3. 撤销权限

REVOKE INSERT ON dbname.* FROM 'user'@'localhost';

4. 删除用户

DROP USER 'olduser'@'localhost';

---

最佳实践提示

1. 命名规范：使用蛇形命名法（snake_case）
2. 主键设计：使用无业务意义的自增ID
3. 避免SELECT*：明确指定需要的列
4. 索引优化：为WHERE、JOIN、ORDER BY的列建索引
5. 事务控制：保持事务短小精悍
6. SQL注入：使用参数化查询防止注入攻击

以上语法基于MySQL 8.0版本，部分语法在旧版本中可能不适用。