728

MYSQL检查约束

check 检查约束,在数据添加或修改时保证数据的有效性。符合表达式的才会正确执行

//创建表添加
CREATE TABLE users (
... ,
age INT CHECK (age >= 18)
);

CREATE TABLE users (
... ,
CONSTRAINT chk_xxx CHECK(expr)
);//检查约束名

//alter添加
ALTER TABLE emp2 MODIFY sal DOUBLE CHECK(sal >= 2000);
ALTER TABLE tb_name ADD CONSTRAINT chk_xx check(id > 10);

-- 删除约束
ALTER TABLE tb_name DROP CONSTRAINT tb_chk_num;

检查约束会检查插入数据是否符合约束条件(该表约束为id>3)

image-20230728174129193

image-20230728173958182

用例表

image-20230728200511288

问题1:

  1. 在使用了 GROUP BY 的 SQL 中, ORDER BY 可以使用 聚合函数 吗? 可以使用未在 GROU P BY 中出现的列吗?

--可以在 ORDER BY 子句中使用聚合函数

--不能直接在 ORDER BY 子句中使用未在 GROUP BY 中出现的列

image-20230728192200845

**问题:**2. 在 DELETE / UPDATE 中可以使用子查询吗?有限制吗?限制是什么

1. 合法的子查询: 子查询在 DELETE 和 UPDATE 语句中必须是合法的 SELECT 查询语句。这意味着子查询本身必须返回有效的查询结果,否则会导致语法错误。

2. 子查询返回单一值: 在 DELETE 和 UPDATE 语句中,子查询必须返回单一值(一个列值或一行一列),否则会引发错误。因为 DELETE 和 UPDATE 操作是针对表中的单一行或单一列的。

3. 主键或唯一索引: 当使用子查询作为删除(DELETE)或更新(UPDATE)条件时,子查询通常会用于查找要删除或更新的行。为了确保准确性和避免操作多个目标行,子查询通常会基于主键或唯一索引来查找要操作的行。

4. 子查询中不涉及修改目标表: 在 DELETE 和 UPDATE 语句中,子查询不能修改目标表本身。子查询只能用于提供删除或更新操作的条件,而不应该对目标表进行修改。

例如

#错误示范
DELETE FROM employee WHERE dept_id in (select dept_id from employee where dept_id =2);
//ERROR 1093 (HY000): You can't specify target table 'employee' for update in FROM clause
#因为子查询出现了目标表employee

delete from dept where dept_id = (select dept_id from employee where dept_id =2);
//ERROR 1242 (21000): Subquery returns more than 1 row
#此处子查询返回值超过1行,故错误

#因此语正确句应该改为:
DELETE FROM employee WHERE dept_id in (select dept_id from dept where dept_id =2);
//或者
DELETE FROM employee WHERE employee.dept_id in (select dept_id from dept where dept_id =2);

image-20230728201427682

子查询练习:

  • 先尝试查看 SMITH 所从事的岗位?

  • 查询从事 clerk 工作的员工?

  • 查询 SMITH 所在的部门和从事的岗位 ?

  • 查询在 20 部门从事 clerk 岗位的员工信息?

  • 可以成对比较,也可以把子查询当成一张虚拟表使用?

image-20230728202735178

答案

1.先尝试查看 SMITH 所从事的岗位?
select loc from dept where DEPTNO =(select DEPTNO from emp where ENAME ='smith');

image-20230728205358660

2.查询从事 clerk 工作的员工?
select ENAME from emp where JOB in (select JOB from emp where JOB ='CLERK' group by JOB);

image-20230728205245699

3.查询 与 SMITH 在同一个部门且岗位相同的员工的信息

//使用where(a,b)成对比较
select* from emp where (DEPTNO,JOB) in (select deptno,JOB from emp where ename = 'smith');

image-20230728211705136

4.查询在 20 部门从事 clerk 岗位的员工信息

select* from emp where JOB in (select JOB from emp where JOB = 'clerk') and DEPTNO =20;

image-20230728212844439

多行子查询

  1. 查询emp表中与20部门员工岗位相同的员工信息
 select* from emp where JOB in (select JOB from emp where deptno =20);

image-20230728215642041

2.查询20部门的所有岗位

select distinct job from emp where job in (select job from emp where deptno = 20);

3.根据20部门的岗位来查询emp表中的员工

select* from emp where deptno in (select deptno from emp where deptno =20);

image-20230728220932055