山西农业大学20240904

一. 外键约束(续)

[CONSTRAINT symbol] FOREIGN KEY(外键字段)
REFERENCES tbl_name(主表的主键)
[ON DELETE {RESTRICT | CASCADE | SET NULL}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL}]

1. 级联操作

1.1 RESTRICT(默认)

• 当主表删除记录时, 如果从表中有相关联的记录, 则不允许主表删除
• 当主表更新记录时, 如果从表中有相关联的记录, 则不允许主表更新

ALTER TABLE emp ADD CONSTRAINT dept_fk 
FOREIGN KEY (dept_id) REFERENCES dept(id)
# 默认值,写与不写效果一样
ON DELETE RESTRICT ON UPDATE RESTRICT;

代码演示

# 删除id为4的部门
# 删除失败, 因为在从表emp有数据引用了id为4的部门
DELETE FROM dept WHERE id=4;
# 正确做法: 先将员工删除 再删除部门
DELETE FROM emp WHERE id=4;
DELETE FROM dept WHERE id=4;
# 不允许修改: 因为在从表emp有数据引用了id为1的部门
UPDATE dept SET id=4 WHERE id=1;

1.2 CASCADE

• 当主表删除记录或更新记录,从表会进行级联删除或更新

ALTER TABLE emp ADD CONSTRAINT dept_fk 
FOREIGN KEY (dept_id) REFERENCES dept(id)

ON DELETE CASCADE ON UPDATE CASCADE;

代码演示

# 级联操作 CASCADE
# 删除外键
ALTER TABLE emp DROP FOREIGN KEY dept_fk;
# 增加外键
ALTER TABLE emp ADD CONSTRAINT dept_fk
FOREIGN KEY (dept_id) REFERENCES dept(id)
ON DELETE CASCADE ON UPDATE CASCADE ;
DESC emp;

# 级联删除: 删除主表数据时,从表中的数据一并删除
DELETE FROM dept WHERE id=3;
# 级联更新: 更新主表数据时,从表中的数据一并更新
UPDATE dept SET id=7 WHERE id=1;

1.3 SET NULL

• 当主表记录删除时,从表的外键字段变为null
• 当主表记录更新时,从表的外面字段变为null

ALTER TABLE emp ADD CONSTRAINT dept_fk 
FOREIGN KEY (dept_id) REFERENCES dept(id)

ON DELETE SET NULL ON UPDATE SET NULL;

代码演示

# 级联更新 - SET NULL
ALTER TABLE emp ADD CONSTRAINT dept_fk
FOREIGN KEY (dept_id) REFERENCES dept(id)
ON DELETE SET NULL ON UPDATE SET NULL ;

# 级联删除: 删除主表数据时, 从表数据会改为null
DELETE FROM  dept WHERE id=2;
# 级联更新: 更新主表数据时,从表数据会更新为null
UPDATE dept SET id=7 WHERE id=1;

二. 查询语句前期准备

1. idea中导入sql(tedu.sql)脚本

2. 数据表说明

三. DQL基本查询

DQL用来检索表中数据的语言, 涉及到的关键词SELECT

语法

---------------------------------执行顺序
SELECT 子句------------------- 6
FROM 子句 ------------------- 1
JOIN … ON… 子句 ------------------- 2
WHERE 子句 ------------------- 3
GROUP BY 子句 ------------------- 4
HAVING 子句 ------------------- 5
ORDER BY 子句 ------------------- 7
LIMIT 子句 ------------------- 8

1 基础查询

语法

SELECT 字段1,字段2,… FROM 表名;

• SELECT 子句用于指定 查询表中的字段
• SELECT 子句用于指定 数据来源于哪张表

示例:查询表中所有字段, 以及指定字段

# 检索教师表中所有(*)的字段
SELECT * FROM teacher;
# 检索所有教师的id,姓名,年龄
SELECT id,name,age FROM teacher;

注意:

实际开发中, JAVA代码若执行DQL语句, 尽量不要使用 *
因为当使用 SELECT * 时,数据库首先要查询数据字段,了解数据表的结构, 了解完成所有的字段之后才进行查阅, 因此会给数据库带来额外的开销,不换算
因为JAVA代码仅需要编写一次, 所以我么应该在SELECT后将所有的字段列举出来

2 WHERE子句

在DQL语句中, WHERE同样是用于指定过滤条件, 此时进件刚满足该过滤条件的记录查询出来

# 查看'一级讲师'的信息, 列出:名字,职位,工资, 年龄
SELECT name,title,salary,age
FROM teacher
WHERE title='一级讲师';
# 查看除了'刘苍松'以外的所有老师的名字, 工资, 奖金,职位
SELECT name,salary,comm,title
FROM teacher
WHERE name!='刘苍松';
# 查看职位是'大队长'的学生的名字, 年龄, 性别, 职位
SELECT name,age,gender,job
FROM student
WHERE job='大队长';

# 查看年龄在30岁以上(含)的老师的名字,职称,工资, 奖金
SELECT name,title,salary,comm,age
FROM teacher
WHERE age>=30;

2.1 算数运算符

# 算数运算符
# 查询年龄为偶数(age%2=0)的老师的信息
SELECT name,age,salary,title
FROM teacher
WHERE age%2=0;
# 查询年薪高于60000老师都有谁?
SELECT name,age,title,salary*12
FROM teacher
WHERE salary*12>60000;