MySQL基础语法与约束

一、DDL - 数据定义语言

DDL主要用于定义和管理数据库结构，包括创建、修改和删除数据库对象。

1.1 数据库操作

-- 创建数据库
CREATE DATABASE school_db;

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

-- 使用数据库
USE school_db;

-- 删除数据库
DROP DATABASE school_db;

1.2 表操作

-- 创建表
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 查看表结构
DESC students;
SHOW CREATE TABLE students;

-- 修改表结构
ALTER TABLE students ADD COLUMN phone VARCHAR(20);
ALTER TABLE students MODIFY COLUMN age INT NOT NULL;
ALTER TABLE students DROP COLUMN phone;

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

-- 删除表
DROP TABLE student_info;

1.3 索引操作

-- 创建索引
CREATE INDEX idx_name ON students(name);
CREATE UNIQUE INDEX idx_email ON students(email);

-- 查看索引
SHOW INDEX FROM students;

-- 删除索引
DROP INDEX idx_name ON students;

二、DML - 数据操作语言

DML用于对表中的数据进行增加、删除、修改操作。

2.1 插入数据（INSERT）

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

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

-- 从其他表插入数据
INSERT INTO students (name, age) 
SELECT name, age FROM temp_students WHERE age > 18;

2.2 更新数据（UPDATE）

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

-- 更新多个字段
UPDATE students 
SET age = 23, email = 'zhangsan_new@example.com' 
WHERE id = 1;

-- 条件更新
UPDATE students 
SET age = age + 1 
WHERE age < 20;

2.3 删除数据（DELETE）

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

-- 条件删除
DELETE FROM students WHERE age < 18;

-- 删除所有记录（保留表结构）
DELETE FROM students;

-- 清空表，重置自增字段
TRUNCATE TABLE students;

三、DQL - 数据查询语言

DQL主要用于查询数据库中的数据，是使用频率最高的SQL语句类型。

3.1 基本查询

-- 查询所有字段
SELECT * FROM students;

-- 查询指定字段
SELECT name, age FROM students;

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

-- 条件查询
SELECT * FROM students WHERE age > 20;
SELECT * FROM students WHERE name LIKE '张%';
SELECT * FROM students WHERE age BETWEEN 18 AND 25;
SELECT * FROM students WHERE age IN (19, 20, 21);

3.2 排序和分页

-- 排序查询
SELECT * FROM students ORDER BY age DESC;
SELECT * FROM students ORDER BY age ASC, name DESC;

-- 分页查询
SELECT * FROM students LIMIT 10;
SELECT * FROM students LIMIT 10, 5; -- 跳过10条，取5条

3.3 聚合函数

-- 统计函数
SELECT COUNT(*) FROM students;
SELECT COUNT(DISTINCT age) FROM students;
SELECT AVG(age) AS avg_age FROM students;
SELECT MAX(age), MIN(age) FROM students;
SELECT SUM(age) FROM students;

-- 分组查询
SELECT age, COUNT(*) FROM students GROUP BY age;
SELECT age, COUNT(*) FROM students 
GROUP BY age HAVING COUNT(*) > 1;

3.4 多表查询

-- 内连接
SELECT s.name, c.course_name 
FROM students s 
INNER JOIN courses c ON s.course_id = c.id;

-- 左连接
SELECT s.name, c.course_name 
FROM students s 
LEFT JOIN courses c ON s.course_id = c.id;

-- 右连接
SELECT s.name, c.course_name 
FROM students s 
RIGHT JOIN courses c ON s.course_id = c.id;

-- 子查询
SELECT * FROM students 
WHERE age > (SELECT AVG(age) FROM students);

四、DCL - 数据控制语言

DCL用于控制数据库的访问权限和安全性。

4.1 用户管理

-- 创建用户
CREATE USER 'app_user'@'%' IDENTIFIED BY 'app_password';

-- 修改用户密码
ALTER USER 'developer'@'localhost' IDENTIFIED BY 'new_password';

-- 删除用户
DROP USER 'developer'@'localhost';

4.2 权限管理

-- 授权
GRANT ALL PRIVILEGES ON school_db.students TO 'app_user'@'%';

-- 查看权限
SHOW GRANTS FOR 'developer'@'localhost';

-- 撤销权限
REVOKE INSERT, UPDATE ON school_db.* FROM 'developer'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

五、约束（Constraints）

约束用于确保数据库中数据的完整性和一致性。

5.1 主键约束（PRIMARY KEY）

-- 创建表时指定主键
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

-- 添加主键约束
ALTER TABLE students ADD PRIMARY KEY (id);

-- 删除主键约束
ALTER TABLE students DROP PRIMARY KEY;

5.2 外键约束（FOREIGN KEY）

-- 创建外键约束
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    order_date DATE,
    FOREIGN KEY (student_id) REFERENCES students(id)
);

-- 添加外键约束
ALTER TABLE orders 
ADD CONSTRAINT fk_student 
FOREIGN KEY (student_id) REFERENCES students(id);

-- 删除外键约束
ALTER TABLE orders DROP FOREIGN KEY fk_student;

5.3 唯一约束（UNIQUE）

-- 创建唯一约束
CREATE TABLE students (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20)
);

-- 添加唯一约束
ALTER TABLE students ADD UNIQUE (email);
ALTER TABLE students ADD CONSTRAINT uk_phone UNIQUE (phone);

-- 删除唯一约束
ALTER TABLE students DROP INDEX email;

5.4 非空约束（NOT NULL）

-- 创建非空约束
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT
);

-- 添加非空约束
ALTER TABLE students MODIFY COLUMN age INT NOT NULL;

-- 删除非空约束
ALTER TABLE students MODIFY COLUMN age INT;

5.5 检查约束（CHECK）

-- MySQL 8.0+ 支持CHECK约束
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT CHECK (age >= 0 AND age <= 150),
    email VARCHAR(100) CHECK (email LIKE '%@%')
);

-- 添加检查约束
ALTER TABLE students 
ADD CONSTRAINT chk_age CHECK (age BETWEEN 0 AND 150);