基础使用

1 DDL语句

1.1 授权语句

-- 授予了用户 'username'@'localhost' 对表 "employees" 执行 SELECT 和 INSERT 操作的权限
GRANT SELECT, INSERT ON employees TO 'username'@'localhost';

1.2 撤销授权

-- 撤销用户的权限
REVOKE SELECT ON employees FROM 'username'@'localhost';

1.3 创建表

CREATE TABLE employees (
    employee_id INT(5) UNSIGNED ZEROFILL AUTO_INCREMENT PRIMARY KEY ,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE
);
-- INT(3) 定义了整数的显示宽度为3位。
-- UNSIGNED 表明该整数是非负的。
-- ZEROFILL 会在数字前面填充零，以确保它总是3位数。
-- AUTO_INCREMENT 使得每次插入新记录时，id列的值都会自动递增。

1.4 修改表

1.4.1 修改字段类型

-- 修改字段类型为VARCHAR(50)
ALTER TABLE example_table
MODIFY COLUMN example_column VARCHAR(50);

1.4.2 删除字段

-- 删除字段
ALTER TABLE example_table
DROP COLUMN column_to_drop;

1.4.3 添加字段

-- 添加新字段以及注释
ALTER TABLE example_table
ADD COLUMN new_column VARCHAR(50) COMMENT 'This is a new column';

1.4.4 修改表名

-- 修改表名
RENAME TABLE old_table_name TO new_table_name;

1.5 删除表

1.5.1 DROP

用于删除数据库对象，如表、索引等

DROP TABLE employees;

1.5.2 TRUNCATE

用于删除表中的所有数据，但保留表结构

TRUNCATE TABLE table_name;

1.2 DML

1.2.1 SELECT

1.2.2 UPDATE

1.2.3 DELETE

1.2.4 INSERT

1.2.5 常用关键字

WITH使用示例

关键字 WITH 在 SQL 中用于创建临时结果集，通常称为公用表表达式（CTE）。它允许用户在查询中定义一个临时的结果集，以便在后续的查询中引用，从而提高查询的可读性和维护性。以下是 WITH 关键字的基本语法和示例：

WITH cte_name AS (
  SELECT column1, column2
  FROM table_name
  WHERE condition
)
SELECT * FROM cte_name;

假设我们有一个员工表 employees，我们想要查询所有部门的平均薪资，可以使用 WITH 关键字来简化查询：

WITH department_avg AS (
  SELECT department_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department_id
)
SELECT d.department_name, da.avg_salary
FROM departments d
JOIN department_avg da ON d.department_id = da.department_id;

优点：

1. 代码更清晰，易于维护
2. 可以重复使用子查询结果
3. 对于复杂查询特别有用
4. 可以创建递归查询（在处理层级数据时很有用）

注意点：

1. WITH子句必须在主查询之前
2. 可以定义多个CTE，用逗号分隔
3. CTE只在当前查询中有效

查询最新记录

row_number()、PARTITION 查询最新记录使用示例

SELECT a.*,  -- 选择表a的所有列
       row_number() OVER (
           PARTITION BY b  -- 按b字段分组
           ORDER BY CREATE_TIME DESC   -- 在每个分组内按CREATE_TIME降序排序
       ) as rn
FROM table_name a  -- 从table_name表中查询，并给它一个别名a

分析：

1. row_number() - 这是一个窗口函数，用于为结果集的每一行分配一个唯一的序号
2. PARTITION BY b - 按b字段分组
3. ORDER BY CREATE_TIME DESC - 在每个分组内按CREATE_TIME降序排序
4. as rn - 给这个序号列取名为'rn'

查询最新记录：

方法1：

select * from (
	SELECT a.*,  -- 选择表a的所有列
       row_number() OVER (
           PARTITION BY b  -- 按b字段分组
           ORDER BY CREATE_TIME DESC   -- 在每个分组内按CREATE_TIME降序排序
       ) as rn
	FROM table_name a  -- 从table_name表中查询，并给它一个别名a
)t
where t.rn=1
...

方法2：

使用with:

WITH temp_table AS (SELECT a,
                   b,
                   ROW_NUMBER() OVER (PARTITION BY b ORDER BY CREATE_TIME DESC) AS rn
                   FROM table_name
                   WHERE ...)
SELECT a,b
FROM temp_table
WHERE rn = 1