SQL 分类及常用操作总结

 

目录

1. SQL 分类

2. datetime 和 timestamp 的区别

3. 如何查看表结构

4. 如何复制表

5. 修改表

6. 删除数据库

7. DML 操作

8. DQL 查询

9. 示例：完整 SQL 使用方法

---

1. SQL 分类

SQL（结构化查询语言）主要分为以下几类：

• DDL（数据定义语言）：用于定义和管理数据库结构，如创建、修改和删除数据库或表。
  • 操作数据库：

sql

    -- 查看所有数据库
    SHOW DATABASES;
    
    -- 查看创建数据库的SQL语句
    SHOW CREATE DATABASE mysql;

• 操作表：

sql

    -- 创建表
    CREATE TABLE 表名 (列定义);
    
    -- 修改表名
    ALTER TABLE 表名 RENAME TO 新表名;
    
    -- 修改字符集
    ALTER TABLE 表名 CHARACTER SET utf8;
    
    -- 添加列
    ALTER TABLE 表名 ADD 列名 数据类型;
    
    -- 修改列名和数据类型
    ALTER TABLE 表名 CHANGE 旧列名 新列名 新数据类型;
    
    -- 删除列
    ALTER TABLE 表名 DROP 列名;
    
    -- 删除表
    DROP TABLE 表名;
    DROP TABLE IF EXISTS 表名;  -- 如果表存在则删除

• DML（数据操作语言）：用于对数据库中的数据进行增、删、改操作。
  • 添加数据：

sql

    INSERT INTO 表名 (列名1, 列名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...);

• 删除数据：

sql

    -- 删除表里全部记录
    DELETE FROM 表名;
    
    -- 按照条件删除记录
    DELETE FROM 表名 WHERE 条件;
    
    -- 删除表并创建一张一模一样的空表（效率高）
    TRUNCATE TABLE 表名;

- **DELETE 和 TRUNCATE 的区别**：
  - `DELETE` 是逐条删除数据，速度较慢，可以带条件。
  - `TRUNCATE` 是直接删除整个表并重建，速度较快，不能带条件。

• 修改数据：

sql

    UPDATE 表名 SET 列名1=值1, 列名2=值2, ... [WHERE 条件];

• DQL（数据查询语言）：用于查询数据库中的数据。
  • SELECT 查询：

sql

    SELECT 字段名
    FROM 表名
    WHERE 条件
    GROUP BY 分组字段
    HAVING 分组之后的条件
    ORDER BY 排序字段 [ASC|DESC]
    LIMIT [偏移量,] 记录数;

• DCL（数据控制语言）：用于管理数据库的权限。

sql

  -- 授予权限
  GRANT 权限 ON 数据库.表 TO '用户名'@'主机';
  
  -- 撤销权限
  REVOKE 权限 ON 数据库.表 FROM '用户名'@'主机';

2. datetime 和 timestamp 的区别

• datetime：

  • 格式：YYYY-MM-DD HH:MM:SS。
  • 范围：1000-01-01 00:00:00 到 9999-12-31 23:59:59。
  • 存储：占用 8 字节。
  • 特点：必须显式赋值，不会自动更新。

• timestamp：

  • 格式：YYYY-MM-DD HH:MM:SS。
  • 范围：1970-01-01 00:00:01 到 2038-01-19 03:14:07（32 位系统）。
  • 存储：占用 4 字节。
  • 特点：如果没有赋值，默认为当前时间；如果列定义为 ON UPDATE CURRENT_TIMESTAMP，则在更新记录时会自动更新为当前时间。

3. 如何查看表结构

sql

DESC 表名;

4. 如何复制表

sql

-- 复制表结构
CREATE TABLE 新表名 LIKE 被复制的表名;

-- 复制表结构和数据
CREATE TABLE 新表名 AS SELECT * FROM 被复制的表名;

5. 修改表

• 修改表名：

sql

  ALTER TABLE 表名 RENAME TO 新表名;

• 修改字符集：

sql

  ALTER TABLE 表名 CHARACTER SET utf8;

• 添加列：

sql

  ALTER TABLE 表名 ADD 列名 数据类型;

• 修改列名和数据类型：

sql

  ALTER TABLE 表名 CHANGE 旧列名 新列名 新数据类型;

• 删除列：

sql

  ALTER TABLE 表名 DROP 列名;

6. 删除数据库

sql

DROP DATABASE 数据库名;
DROP DATABASE IF EXISTS 数据库名;  -- 如果数据库存在则删除

7. DML 操作

• 添加数据：

sql

  INSERT INTO 表名 (列名1, 列名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...);

• 删除数据：

sql

  DELETE FROM 表名;
  DELETE FROM 表名 WHERE 条件;
  TRUNCATE TABLE 表名;  -- 快速清空表

• 修改数据：

sql

  UPDATE 表名 SET 列名1=值1, 列名2=值2, ... [WHERE 条件];

8. DQL 查询

• 基本查询：

sql

  SELECT 字段名
  FROM 表名
  WHERE 条件
  GROUP BY 分组字段
  HAVING 分组之后的条件
  ORDER BY 排序字段 [ASC|DESC]
  LIMIT [偏移量,] 记录数;

• 处理 NULL 值：

sql

  SELECT a, b, IFNULL(a, 0) + IFNULL(b, 0) AS sum_ab
  FROM 表名
  WHERE 条件;

• 运算符：
  • 比较运算符：>, <, >=, <=, =, <>。
  • 集合运算符：IN(集合)。
  • 模糊查询：LIKE（_ 表示单个字符，% 表示任意多个字符）。
  • 判断 NULL：IS NULL（注意不能用 = 判断 NULL）。
  • 逻辑运算符：AND 或 &&，OR 或 ||，NOT 或 !。
  • 范围查询：字段名 BETWEEN 值1 AND 值2。

9. 示例：完整 SQL 使用方法

sql

-- 创建数据库
CREATE DATABASE IF NOT EXISTS school_db CHARACTER SET utf8;

-- 使用数据库
USE school_db;

-- 创建表
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT,
    gender ENUM('男', '女'),
    birth_date DATE,
    last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 插入数据
INSERT INTO students (name, age, gender, birth_date) 
VALUES ('张三', 20, '男', '2000-01-01'), ('李四', 21, '女', '1999-05-15');

-- 查询数据
SELECT id, name, age, gender, birth_date, last_update
FROM students
WHERE age BETWEEN 20 AND 25
ORDER BY age DESC
LIMIT 10;

-- 更新数据
UPDATE students 
SET age = age + 1 
WHERE gender = '男';

-- 删除数据
DELETE FROM students 
WHERE age < 20;

-- 清空表
TRUNCATE TABLE students;