SQL基础语法

SQL简介

SQL（Structured Query Language，结构化查询语言）是用于管理关系型数据库的标准语言。通过SQL，我们可以对数据库进行各种操作，包括创建表、插入数据、查询数据、更新数据和删除数据等。

SQL的特点

1. 标准化：ANSI和ISO标准
2. 声明式：告诉数据库"要什么"，而不是"怎么做"
3. 简单易学：语法接近自然语言
4. 功能强大：支持复杂的数据操作

SQL语言分类

1. 数据定义语言（DDL）

用于定义和管理数据库对象（表、索引、视图等）。

主要命令：

• CREATE：创建数据库对象
• ALTER：修改数据库对象
• DROP：删除数据库对象
• TRUNCATE：清空表数据

2. 数据操作语言（DML）

用于操作数据库中的数据。

主要命令：

• INSERT：插入数据
• UPDATE：更新数据
• DELETE：删除数据

3. 数据查询语言（DQL）

用于查询数据库中的数据。

主要命令：

• SELECT：查询数据

4. 数据控制语言（DCL）

用于控制数据库的访问权限。

主要命令：

• GRANT：授予权限
• REVOKE：撤销权限

5. 事务控制语言（TCL）

用于管理数据库事务。

主要命令：

• COMMIT：提交事务
• ROLLBACK：回滚事务
• SAVEPOINT：设置保存点

常用数据类型

数值类型

-- 整数类型
INT           -- 4字节整数
TINYINT       -- 1字节整数
SMALLINT      -- 2字节整数
MEDIUMINT     -- 3字节整数
BIGINT        -- 8字节整数

-- 浮点类型
FLOAT         -- 单精度浮点数
DOUBLE        -- 双精度浮点数
DECIMAL(10,2) -- 定点数，总长度10，小数位2

字符串类型

-- 定长字符串
CHAR(50)      -- 固定长度50字符

-- 变长字符串
VARCHAR(255)  -- 可变长度，最大255字符
TEXT          -- 长文本，最大65535字符
LONGTEXT      -- 超长文本，最大4GB

日期时间类型

DATE          -- 日期 'YYYY-MM-DD'
TIME          -- 时间 'HH:MM:SS'
DATETIME      -- 日期时间 'YYYY-MM-DD HH:MM:SS'
TIMESTAMP     -- 时间戳，自动更新
YEAR          -- 年份 'YYYY'

数据库操作

创建数据库

-- 创建数据库
CREATE DATABASE student_system;

-- 指定字符集
CREATE DATABASE student_system
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

-- 使用数据库
USE student_system;

-- 查看所有数据库
SHOW DATABASES;

-- 删除数据库
DROP DATABASE student_system;

表操作

创建表

-- 基本语法
CREATE TABLE table_name (
    column1 data_type [constraints],
    column2 data_type [constraints],
    ...
);

-- 完整示例
CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    gender ENUM('男', '女') NOT NULL,
    age INT CHECK (age BETWEEN 15 AND 30),
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    birthday DATE,
    enrollment_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

修改表结构

-- 添加列
ALTER TABLE students
ADD COLUMN address VARCHAR(200);

-- 修改列
ALTER TABLE students
MODIFY COLUMN phone VARCHAR(30);

-- 删除列
ALTER TABLE students
DROP COLUMN address;

-- 重命名列
ALTER TABLE students
CHANGE COLUMN phone mobile VARCHAR(20);

-- 重命名表
RENAME TABLE students TO student_info;

-- 添加主键
ALTER TABLE student_info
ADD PRIMARY KEY (student_id);

-- 添加外键
ALTER TABLE student_info
ADD FOREIGN KEY (class_id) REFERENCES classes(class_id);

删除表

-- 删除表结构和数据
DROP TABLE students;

-- 只删除数据，保留表结构
TRUNCATE TABLE students;

数据操作（DML）

插入数据

-- 插入单条记录
INSERT INTO students (name, gender, age, email)
VALUES ('张三', '男', 20, 'zhangsan@example.com');

-- 插入多条记录
INSERT INTO students (name, gender, age, email) VALUES
('李四', '女', 19, 'lisi@example.com'),
('王五', '男', 21, 'wangwu@example.com'),
('赵六', '女', 20, 'zhaoliu@example.com');

-- 插入时省略列名（需提供所有列的值）
INSERT INTO students
VALUES (0, '钱七', '男', 22, 'qianqi@example.com', NULL, NULL, NOW(), 1);

更新数据

-- 更新单个字段
UPDATE students
SET age = 21
WHERE name = '张三';

-- 更新多个字段
UPDATE students
SET age = 20, phone = '13800138000'
WHERE student_id = 1;

-- 基于条件更新
UPDATE students
SET age = age + 1
WHERE enrollment_date < '2024-01-01';

删除数据

-- 删除特定记录
DELETE FROM students
WHERE student_id = 1;

-- 基于条件删除
DELETE FROM students
WHERE age > 25;

-- 删除所有记录（谨慎使用）
DELETE FROM students;

数据查询（DQL）

基本查询

-- 查询所有列
SELECT * FROM students;

-- 查询指定列
SELECT name, age, email FROM students;

-- 使用别名
SELECT name AS '姓名', age AS '年龄' FROM students;

-- 去重查询
SELECT DISTINCT age FROM students;

条件查询

-- WHERE子句
SELECT * FROM students WHERE age = 20;

-- 比较运算符
SELECT * FROM students WHERE age > 18;
SELECT * FROM students WHERE age >= 18 AND age <= 22;
SELECT * FROM students WHERE age BETWEEN 18 AND 22;

-- 逻辑运算符
SELECT * FROM students WHERE age = 20 OR age = 21;
SELECT * FROM students WHERE gender = '女' AND age > 19;

-- 模糊查询
SELECT * FROM students WHERE name LIKE '张%';  -- 以'张'开头
SELECT * FROM students WHERE name LIKE '%三';  -- 以'三'结尾
SELECT * FROM students WHERE name LIKE '%李%'; -- 包含'李'

-- 空值查询
SELECT * FROM students WHERE phone IS NULL;
SELECT * FROM students WHERE phone IS NOT NULL;

-- IN查询
SELECT * FROM students WHERE age IN (18, 19, 20);

排序查询

-- 升序排序（默认）
SELECT * FROM students ORDER BY age;

-- 降序排序
SELECT * FROM students ORDER BY age DESC;

-- 多字段排序
SELECT * FROM students ORDER BY age DESC, name ASC;

分页查询

-- LIMIT分页
SELECT * FROM students LIMIT 10;           -- 前10条记录
SELECT * FROM students LIMIT 10, 5;        -- 从第11条开始，取5条
SELECT * FROM students LIMIT 5 OFFSET 10;  -- 从第11条开始，取5条

聚合函数

-- 统计数量
SELECT COUNT(*) FROM students;
SELECT COUNT(DISTINCT age) FROM students;

-- 计算平均值
SELECT AVG(age) FROM students;

-- 计算最大值和最小值
SELECT MAX(age), MIN(age) FROM students;

-- 计算总和
SELECT SUM(age) FROM students;

分组查询

-- 按性别分组统计
SELECT gender, COUNT(*) as count
FROM students
GROUP BY gender;

-- 按年龄分组，并筛选
SELECT age, COUNT(*) as count
FROM students
GROUP BY age
HAVING COUNT(*) > 1;

连接查询

-- 内连接
SELECT s.name, c.class_name
FROM students s
INNER JOIN classes c ON s.class_id = c.class_id;

-- 左连接
SELECT s.name, c.class_name
FROM students s
LEFT JOIN classes c ON s.class_id = c.class_id;

-- 右连接
SELECT s.name, c.class_name
FROM students s
RIGHT JOIN classes c ON s.class_id = c.class_id;

-- 多表连接
SELECT s.name, c.class_name, co.course_name
FROM students s
LEFT JOIN classes c ON s.class_id = c.class_id
LEFT JOIN courses co ON s.student_id = co.student_id;

实践示例

创建完整的数据库结构

-- 创建班级表
CREATE TABLE classes (
    class_id INT PRIMARY KEY AUTO_INCREMENT,
    class_name VARCHAR(50) NOT NULL,
    grade VARCHAR(20) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建学生表
CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    gender ENUM('男', '女') NOT NULL,
    age INT CHECK (age BETWEEN 15 AND 30),
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    class_id INT,
    birthday DATE,
    enrollment_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (class_id) REFERENCES classes(class_id)
);

-- 插入测试数据
INSERT INTO classes (class_name, grade) VALUES
('计算机科学1班', '2023级'),
('计算机科学2班', '2023级'),
('软件工程1班', '2023级');

INSERT INTO students (name, gender, age, email, class_id, birthday) VALUES
('张三', '男', 20, 'zhangsan@example.com', 1, '2003-05-15'),
('李四', '女', 19, 'lisi@example.com', 1, '2004-03-22'),
('王五', '男', 21, 'wangwu@example.com', 2, '2002-11-08'),
('赵六', '女', 20, 'zhaoliu@example.com', 3, '2003-07-12'),
('钱七', '男', 19, 'qianqi@example.com', 2, '2004-01-25');

常用查询示例

-- 查询所有学生信息（包含班级名称）
SELECT s.student_id, s.name, s.gender, s.age, c.class_name
FROM students s
LEFT JOIN classes c ON s.class_id = c.class_id;

-- 按班级统计学生数量
SELECT c.class_name, COUNT(s.student_id) as student_count
FROM classes c
LEFT JOIN students s ON c.class_id = s.class_id
GROUP BY c.class_id, c.class_name;

-- 查询年龄大于等于20岁的学生
SELECT name, age, email
FROM students
WHERE age >= 20
ORDER BY age DESC;

-- 查询每个班级的平均年龄
SELECT c.class_name, AVG(s.age) as avg_age
FROM classes c
LEFT JOIN students s ON c.class_id = s.class_id
GROUP BY c.class_id, c.class_name;

SQL最佳实践

1. 使用有意义的主键：建议使用自增整数作为主键
2. 合理选择数据类型：根据实际需求选择合适的数据类型
3. 添加必要的约束：NOT NULL、UNIQUE、CHECK等
4. 使用索引优化查询：为经常查询的字段添加索引
5. **避免使用SELECT ***：明确指定需要的字段
6. 使用参数化查询：防止SQL注入攻击
7. 合理使用事务：保证数据的一致性
8. 定期备份数据：防止数据丢失

总结

SQL是与数据库交互的核心语言，掌握SQL基础语法对于PHP开发者来说至关重要。在本节中，我们学习了：

• SQL语言的分类和特点
• 常用的数据类型
• 数据库和表的创建、修改、删除
• 数据的增删改查操作
• 复杂查询和连接查询
• 实际的数据库操作示例

这些SQL知识将为后续学习PHP数据库操作打下坚实的基础。在实际开发中，建议多练习SQL语句的编写，提高查询效率和准确性。