SQL应用案例

SQL案例一

⭐ 考点：

  • 如何在关系表中指定外键约束、以及check 约束
  • 如何创建视图
  • 创建触发器
  • Having 子句 和 group by 分组
  • 子查询
image-20220420213848246

使用sql创建works表。

create table works (
    Eno char(10) references Emp(Eno),
    Cno char(4) references Company(Cno),
    Salary int Check(salary>=1500),
    Primary key (Eno,Cno)
)
-- (Eno,Cno) 为共同主键

references 关键字

references 关键字用于定义在外键关系中使用的表和列，即外键来自哪里?

-- 外键约束完整语法
[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…]
REFERENCES <主表名> 主键列1 [，主键列2，…]

primary key 指定主键约束

primary key 指定主键约束

-- 主键约束
<字段名> <数据类型> PRIMARY KEY [默认值]

CHECK 约束

CHECK 约束用于限制列中的值的范围或有效性。

CHECK <表达式>

可以在创建表或修改表时使用。

例如，我们需要修改工资不低于1600，则sql为下：

-- ALTER TABLE tableName ADD CONSTRAINT <检查约束名> CHECK(<检查约束>)
ALTER TABLE works ADD CONSTRAINT salary_check Check(salary>=1600)

删除约束

-- ALTER TABLE <数据表名> DROP CONSTRAINT <检查约束名>;
ALTER TABLE works DROP CONSTRAINT salary_check

创建视图

CREATE VIEW <视图名> AS <SELECT语句>

创建触发器 create trigger

语法：

CREATE TRIGGER [IF NOT EXISTS] trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body
--trigger_time:
{ BEFORE | AFTER }
--trigger_event: 
{ INSERT | UPDATE | DELETE }
--trigger_order:
{ FOLLOWS | PRECEDES } other_trigger_name
-- trigger_body 
BEGIN
  [statement_list]
END
-------------- 即
CREATE TRIGGER <触发器名> < BEFORE | AFTER >
<INSERT | UPDATE | DELETE >
ON <表名> 
FOR EACH Row
BEGIN
  <触发器主体>
END

教程中使用了 referencing new row as nrow

但是：referencing 在mysql中是不存在的。 直接使用 NEW.Eno即可

having

HAVING子句来指定一组行或聚合的过滤条件。

HAVING子句通常与GROUP BY子句一起使用，以根据指定的条件过滤分组。

如果省略GROUP BY子句，则HAVING子句的行为与WHERE子句类似。

GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]  [HAVING where_condition]
-- GROUP BY可以和HAVING一起限定显示记录所需满足的条件，只有满足条件的分组才会被显示。
--问题？ 我们我们要查询供应商提供的水果名称大于2的，如果查询呢，可以使用HAVING进行过滤
select s_id,count(*) as total,group_concat(name) as names from fruit  group by s_id having count(name) >2;