基础语法

SQL 分类

SQL 语句按照功能分为以下几类：

分类	说明	关键字
DDL	数据定义语言	CREATE, ALTER, DROP, TRUNCATE
DML	数据操作语言	INSERT, UPDATE, DELETE, SELECT
DCL	数据控制语言	GRANT, REVOKE
TCL	事务控制语言	COMMIT, ROLLBACK, SAVEPOINT

数据库操作

创建数据库

CREATE DATABASE db_name;
CREATE DATABASE db_name CHARACTER SET utf8mb4;
CREATE DATABASE IF NOT EXISTS db_name;

查看数据库

SHOW DATABASES;
SHOW CREATE DATABASE db_name;

修改数据库

ALTER DATABASE db_name CHARACTER SET utf8mb4;

删除数据库

DROP DATABASE db_name;
DROP DATABASE IF EXISTS db_name;

表操作

创建表

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

查看表结构

DESC users;
DESCRIBE users;
SHOW COLUMNS FROM users;
SHOW CREATE TABLE users;

修改表

ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users MODIFY COLUMN username VARCHAR(100);
ALTER TABLE users CHANGE COLUMN username user_name VARCHAR(100);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users RENAME TO user_info;

删除表

DROP TABLE users;
DROP TABLE IF EXISTS users;
TRUNCATE TABLE users;

数据操作 (DML)

INSERT 插入数据

INSERT INTO users (username, email, age) VALUES ('张三', 'zhangsan@example.com', 25);

INSERT INTO users (username, email) VALUES
    ('李四', 'lisi@example.com'),
    ('王五', 'wangwu@example.com');

UPDATE 更新数据

UPDATE users SET age = 26 WHERE username = '张三';
UPDATE users SET age = age + 1, email = 'new@example.com' WHERE id = 1;

DELETE 删除数据

DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE age < 18;
DELETE FROM users;

SELECT 查询

基本查询

SELECT * FROM users;
SELECT username, email FROM users;
SELECT DISTINCT age FROM users;

WHERE 条件

SELECT * FROM users WHERE age = 25;
SELECT * FROM users WHERE age > 20 AND age < 30;
SELECT * FROM users WHERE age IN (20, 25, 30);
SELECT * FROM users WHERE username LIKE '张%';
SELECT * FROM users WHERE email IS NOT NULL;

ORDER BY 排序

SELECT * FROM users ORDER BY age;
SELECT * FROM users ORDER BY age DESC;
SELECT * FROM users ORDER BY age DESC, username ASC;

LIMIT 分页

SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 0, 10;
SELECT * FROM users LIMIT 10 OFFSET 0;

聚合函数

SELECT COUNT(*) FROM users;
SELECT AVG(age) FROM users;
SELECT MAX(age), MIN(age) FROM users;
SELECT SUM(age) FROM users;

GROUP BY 分组

SELECT age, COUNT(*) FROM users GROUP BY age;
SELECT age, COUNT(*) as count FROM users GROUP BY age HAVING count > 5;

运算符

比较运算符

运算符	说明
=	等于
!=, <>	不等于
>, <	大于、小于
>=, <=	大于等于、小于等于
BETWEEN...AND	在范围内
IN	在列表中
LIKE	模糊匹配
IS NULL	为空

逻辑运算符

运算符	说明
AND	与
OR	或
NOT	非

模糊匹配

通配符	说明
%	任意多个字符
_	单个字符

SELECT * FROM users WHERE username LIKE '张%';
SELECT * FROM users WHERE username LIKE '_三';

下一步学习

• 数据类型 - 了解 MySQL 数据类型
• 表操作 - 掌握表管理