2025-03-28-数据库-葛瀛龙-笔记整理

20250328

主键约束 不可为空 唯一

唯一性约束 可为空 唯一

ADD 数据列名 数据类型 #添加新的列

DROP COLUMN 列名 #删除列

ALTER COLUMN 列名 新的数据类型 #改该列的数据类型，只能加长不能缩短

 

以下是关于 SQL 中 UPDATE、DELETE 和 TRUNCATE 语句的使用方法及注意事项的详细说明：

1. UPDATE 语句

用途：修改表中已有的数据。

基本语法：

UPDATE 表名
SET 列1 = 值1, 列2 = 值2, …
WHERE 条件;

示例：

— 将用户ID为1的用户的年龄更新为25
UPDATE users
SET age = 25
WHERE user_id = 1;

— 同时更新多个字段
UPDATE employees
SET salary = 50000, department = ‘HR’
WHERE employee_id = 101;

注意事项：

  • 务必使用 WHERE 子句：如果省略 WHERE，表中所有行的指定列都会被更新！
  • 事务安全：在生产环境中，建议在更新前开启事务（如 BEGIN TRANSACTION），以便在出错时回滚。
  • 备份数据：对关键数据操作前，建议先备份（如 CREATE TABLE users_backup AS SELECT * FROM users）。

2. DELETE 语句

用途：删除表中的数据行。

基本语法：

DELETE FROM 表名
WHERE 条件;

示例：

— 删除用户ID为1的记录
DELETE FROM users
WHERE user_id = 1;

— 删除所有年龄大于60的记录
DELETE FROM employees
WHERE age > 60;

注意事项：

  • WHERE 子句同样关键：省略 WHERE 会删除表中所有数据！
  • 性能问题：删除大量数据时，DELETE 会逐行操作并记录日志，可能较慢。
  • 触发器：DELETE 会触发 DELETE 类型的触发器（如果有）。
  • 事务与备份：同 UPDATE，建议操作前备份并使用事务。

3. TRUNCATE 语句

用途：快速删除表中所有数据（不可逆操作）。

基本语法：

TRUNCATE TABLE 表名;

示例：

TRUNCATE TABLE logs;  — 清空 logs 表的所有数据

注意事项：

  • 不可逆：TRUNCATE 会直接删除所有数据，且无法通过事务回滚（除非在支持事务的数据库中显式使用 ROLLBACK）。
  • 效率高：不逐行删除，而是释放数据页，日志记录少，速度远快于 DELETE。
  • 重置自增列：会将自增列（如 AUTO_INCREMENT 或 IDENTITY）重置为初始值。
  • 权限要求：通常需要更高权限（如 DROP 权限）。
  • 不触发触发器：不会触发 DELETE 触发器。

DELETE vs TRUNCATE 对比

特性 DELETE TRUNCATE
删除范围 可以有条件删除部分数据 只能删除全部数据
日志记录 逐行记录日志，较慢 整体释放数据页，速度快
事务回滚 可回滚（需在事务中） 通常不可回滚
自增列重置 不重置自增列 重置自增列到初始值
触发器 触发 DELETE 触发器 不触发
语法复杂度 支持复杂条件（如子查询） 仅能清空整个表

使用建议

  1. 谨慎操作：在生产环境执行前，先用 SELECT * FROM 表名 WHERE 条件 确认影响范围。
  2. 优先使用 TRUNCATE：如果需要清空表且无需条件，优先选择 TRUNCATE 以提高效率。
  3. 避免全表操作：对大表执行 DELETE 或 TRUNCATE 时，注意锁表和性能问题。
  4. 权限控制：严格限制对 TRUNCATE 的权限，防止误操作。

 

 

DISTINCT查询时去重

SELECT DISTINCT FROM WHERE GROUP BY HAVING ORDER BY

在 SQL 中，DISTINCT 关键字用于在查询结果中去除重复的行。当与其他子句（如 WHERE、GROUP BY、HAVING、ORDER BY）结合时，需注意它们的执行顺序和逻辑关系。以下是具体用法及示例：

1. 基本语法

SELECT DISTINCT column1, column2, …
FROM table_name
WHERE condition
GROUP BY column1, column2, …
HAVING group_condition
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

2. 各子句与 DISTINCT 的结合使用

(1) SELECT DISTINCT 基础去重

  • 作用：返回指定列的唯一组合值。
  • 示例：
  • — 查询不重复的部门名称
    SELECT DISTINCT department
    FROM employees;– 查询部门和职位的唯一组合
    SELECT DISTINCT department, position
    FROM employees;

(2) WHERE 子句过滤后去重

  • 执行顺序：先执行 WHERE 过滤数据，再对结果去重。
  • 示例：
  • — 查询工资高于 5000 的员工所在城市（去重）
    SELECT DISTINCT city
    FROM employees
    WHERE salary > 5000;

(3) GROUP BY 与 DISTINCT 的异同

  • 区别：
    • DISTINCT 直接去重，返回所有列的唯一值。
    • GROUP BY 用于分组聚合（如 COUNT, SUM）。
  • 示例：
  • — 使用 GROUP BY 统计每个部门的人数（无需 DISTINCT）
    SELECT department, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department;– 若同时需要去重和分组，需明确逻辑（如统计不同职位的数量）
    SELECT department, COUNT(DISTINCT position) AS unique_positions
    FROM employees
    GROUP BY department;

(4) HAVING 过滤分组后的结果

  • 作用：对 GROUP BY 分组后的结果进行条件过滤。
  • 示例：
  • — 查询员工数超过 5 人的部门（需 GROUP BY）
    SELECT department
    FROM employees
    GROUP BY department
    HAVING COUNT(*) > 5;– 结合 DISTINCT 和 HAVING（较少见）
    SELECT department, COUNT(DISTINCT city) AS cities
    FROM employees
    GROUP BY department
    HAVING cities > 2;

(5) ORDER BY 排序去重结果

  • 作用：对最终结果按指定列排序。
  • 示例：
  • — 按注册时间降序排列不重复的用户名
    SELECT DISTINCT username
    FROM users
    ORDER BY registration_date DESC;

3. 注意事项

  1. 多列去重：DISTINCT 作用于所有列的组合，而非单列。例如：
  • SELECTDISTINCT department, position FROM employees;
    — 结果中 department 和 position 的组合是唯一的。
  1. 性能问题：DISTINCT 可能导致全表扫描和排序，大数据量时需谨慎使用。
  2. 避免混淆：
    • DISTINCT 不适用于聚合函数内部（但 COUNT(DISTINCT column) 是合法的）。
    • 不能同时使用 DISTINCT 和 GROUP BY 对同一列去重（逻辑冲突）。
  1. 替代方案：若仅需去重，优先用 DISTINCT；若需分组统计，使用 GROUP BY。

4. 完整示例

— 查询工资高于 5000 的员工所在城市（去重），并按城市名排序
SELECT DISTINCT city
FROM employees
WHERE salary > 5000
ORDER BY city;

— 统计每个部门的不同职位数量，并筛选职位数超过 2 的部门
SELECT department, COUNT(DISTINCT position) AS unique_positions
FROM employees
GROUP BY department
HAVING unique_positions > 2;

通过合理组合这些子句，可以高效实现复杂的去重查询需求。

SELECT * from STUDENT t where ROWNUM<0; #最近六条记录
SELECT * FROM STUDENT WHERE SNAME LIKE '%张%';
SELECT * FROM STUDENT WHERE SNAME LIKE '%张__'; #下划线代表一个字符 百分号代表任意字符
    select* from STU1 t
    oDBCImporter Command Window
    where t.birthday between to_date(2000-01-01',yyyy-mm-d') and to_date(2003/12/31','yyy/mm/dd')

SELECT count(*) from STUDENT;
ASC为升序，DESC为降序
‘=’是精确匹配，不可用过量通配符
‘LIKE’是模糊匹配
11-12题
11
(1)ALTER TABLE STUDENT ADD NATION VARCHAR2(20);
(2)ALTER TABLE STUDENT DROP COLUMN NATION;
(3)INSERT INTO GRADE VALUES (‘2021110′,’3′,’80’);
(4)UPDATE GRADE SET GMARK=’70’ WHERE SNO=’2021110′;
(5)DELETE FROM GRADE WHERE SNO=’2021110′;
(6)CREATE INDEX IX_CLASS ON GRADE(CNO ASC);
(7)DROP INDEX IX_CLASS;/*这是ORACLE的写法*/
12
(1)SELECT DISTINCT CNO FROM GRADE;
(2)SELECT * FROM STUDENT T WHERE T.SsEX=’女’ AND T.CLNO=’20311′;
(3)SELECT T.SNAME,T.SSEX,T.SBIRTH FROM STUDENT T WHERE T.CLNO=’20311′ OR T.CLNO=’20312′;
(4)SELECT * FROM STUDENT T WHERE T.SNAME LIKE ‘李%’;
(5)SELECT *
FROM STUDENT
WHERE CLNO = (SELECT CLNO FROM STUDENT WHERE SNAME = ‘李勇’);
(6)SELECT
MAX(GMARK) AS 最高分,
MIN(GMARK) AS 最低分,
AVG(GMARK) AS 平均分
FROM GRADE
WHERE CNO = (SELECT CNO FROM COURSE WHERE CNAME = ‘操作系统’);
(7)SELECT COUNT(DISTINCT SNO)
FROM GRADE;
(8)SELECT COUNT(DISTINCT SNO)
FROM GRADE WHERE CNO=(SELECT CNO FROM COURSE WHERE CNAME=’操作系统’);
(9)SELECT SNAME
FROM STUDENT
WHERE CLNO IN (
SELECT CLNO
FROM CLASS
WHERE SPECIAL = ‘软件工程’
AND TRIM(INYEAR) = ‘2020’ — 处理 INYEAR 是 CHAR(8) 的空格填充问题
)
AND SUBSTR(SNO, 1, 7) NOT IN ( — 匹配 GRADE.SNO 的 CHAR(7) 长度
SELECT SNO FROM GRADE
);
/*步入迷惑*/
(10)
SELECT
C.CNO,
C.CNAME,
C.CPNO AS 直接先修课号,
(SELECT CPNO FROM COURSE WHERE CNO = C.CPNO) AS 间接先修课号,
(SELECT CNAME FROM COURSE
WHERE CNO = (SELECT CPNO FROM COURSE WHERE CNO = C.CPNO)) AS 间接先修课名
FROM COURSE C;

Leave a Reply

Your email address will not be published. Required fields are marked *