# 实验一

> ## 如何建表 create Table

```sql
CREATE TABLE hhh(
    id int auto_increment primary key,
name varchar(30) not null,
    age int ,
    birthday date,
    is_actiove boolean default true
);

create table 表名(
字段名 字段类型 约束条件,
字段名 字段类型 约束条件,
字段名 字段类型 约束条件
);
```

> ## 如何新增字段 Add column

```sql
Alter table role add age int not null
ALTER TABLE <表名> ADD <新字段名><数据类型>[约束条件];
```

> ## 如何增加数据

```sql
INSERT INTO products (maker, model, `type`) VALUES('A', '1001', 'pc'),
INSERT INTO 表名 (字段，字段2，.....)values （`数值1`,`数值2`,`数值3`），
```



> ## **DDL DML**

DDL（Data Definition Language）语句： `数据定义语言`，主要是进行定义/改变表的结构、数据类型、表之间的链接等操作。常用的语句关键字有 CREATE、DROP、ALTER 等。

```sql
CREATE TABLE 表名(
列名1 数据类型,
列名2 数据类型,
列名3 数据类型,
...
)

ALTER TABLE 表名;
eg：ALTER TABLE 表名 ADD 列名 数据类型;（添加一个列）
    ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;（修改列名）
    ALTER TABLE 表名 DROP 列名;

DROP TABLE 表名;
DROP DATABASE 数据库名;
```



DML（Data Manipulation Language）语句: `数据操纵语言`，主要是对数据进行增加、删除、修改操作。常用的语句关键字有 INSERT、UPDATE、DELETE 等。

```sql
INSERT INTO 表名 (字段1,字段2,...) values (某值,某值,...),(某值,某值,...);

UPDATE 表名 SET 列名=新值 WHERE 限定条件;

DELETE FROM 表名 WHERE 限定条件;
```



> ## Like  

```sql
SELECT *
FROM customers
WHERE firstname LIKE '%a%';
```

- `%`：匹配任意字符（包括零个字符）。
- `_`：匹配单个字符。







# 实验二



> ## 四种连接 等值连接 自然连接 相关子查询 非相关子查询

> ## join(equal join: comma where， join on)  等值连接

```sql
SELECT 列1, 列2, ...
FROM 表1
[INNER] JOIN 表2  -- INNER可省略，默认就是内连接
  ON 表1.关联列 = 表2.关联列  -- 明确分离连接条件
WHERE 表1.过滤条件 = xxx  -- 仅存放过滤条件，可读性更强
  [AND 表2.过滤条件 = xxx];
  
select name ,age
from role  join user
on role.id=user.id
where role.name="郑笃实" and ....

-- 例子
-- 1. 老式逗号+WHERE写法
SELECT s.student_name, sc.subject, sc.score
FROM student s, score sc  -- 逗号分隔两张表，给表起别名简化书写
WHERE s.student_id = sc.student_id  -- 等值连接条件
  AND sc.score >= 60;  -- 过滤及格成绩

-- 2. 标准JOIN ON写法（推荐）
SELECT s.student_name, sc.subject, sc.score
FROM student s
JOIN score sc
  ON s.student_id = sc.student_id  -- 明确连接条件
WHERE sc.score >= 60;  -- 明确过滤条件
```

> ## natural join(natural join or join using()) 自然连接

```sql
-- 2. 标准JOIN ON写法（推荐）（必须两张表中均存在该列，且列名完全一致）（必须两张表中均存在该列，且列名完全一致）
SELECT s.student_name, sc.subject, sc.score
FROM student s
JOIN score sc
  USING (sc.student_id)  -- 明确连接条件  -- ON s.student_id = sc.student_id
WHERE sc.score >= 60;  -- 明确过滤条件
-- 其实就跟join on一样而已 只不过是 孔乙己的茴的四种写法罢了......
```



> ## corelated subquery 相关子查询

相关子查询（又称关联子查询）是**依赖外部查询的列值**才能执行的子查询，无法独立运行（子查询中会引用外部查询的表列）。

```sql
-- 需求：查询「个人成绩大于其所在班级平均成绩」的学生姓名和对应科目、成绩
SELECT s.student_name, sc.subject, sc.score
FROM student s
JOIN score sc ON s.student_id = sc.student_id
-- 外部查询的s.class_id传入子查询，作为平均成绩的计算条件
WHERE sc.score > (
  SELECT AVG(sc2.score)  -- 子查询：计算当前学生所在班级的平均成绩
  FROM score sc2
  JOIN student s2 ON sc2.student_id = s2.student_id
  WHERE s2.class_id = s.class_id  -- 引用外部查询的s.class_id，依赖外部查询
);

子查询 就是一个sql 嵌套在另一个sql里面
```

> ## noncorelated subquery  非相关子查询

是**不依赖外部查询**的子查询，可以独立执行并返回一个结果集，外部查询直接使用该结果集进行后续计算，是最常用的子查询类型。

```sql
-- 需求：查询“数学科目满分（100分）”的学生姓名
SELECT student_name
FROM student
WHERE student_id = (  -- 子查询返回单行结果时，用=匹配
  SELECT student_id
  FROM score
  WHERE subject = '数学' AND score = 100
  LIMIT 1  -- 确保子查询返回单行
);

-- 这个其实就是大多数时候 写的sql语句 （虽然工作中 一般都不这么手写原生的sql语句hhhh）
```

> ## UNION 大法

有思路但是不知道这么写？

有多张表但是不知道这么写在一个sql里面？？

**为什么要写在一个sql里面？？ UNION大法帮助你**

```sql
SELECT p.model, pc.price
FROM products p
JOIN pcs pc ON pc.model = p.model
WHERE p.maker = 'B'

UNION

SELECT p.model, l.price
FROM products p
JOIN laptops l ON l.model = p.model
WHERE p.maker = 'B'

UNION

SELECT p.model, pr.price
FROM products p
JOIN printers pr ON pr.model = p.model
WHERE p.maker = 'B';


-- left join
SELECT p.model, COALESCE(pc.price, l.price, pr.price) AS price
FROM products p
LEFT JOIN pcs pc      ON pc.model = p.model
LEFT JOIN laptops l   ON l.model  = p.model
LEFT JOIN printers pr ON pr.model = p.model
WHERE p.maker = 'B';


-- 谁最清晰一目了然
```







> ## 小tip

SELECT  分组列, 聚合函数(...)
FROM    表
WHERE   行条件
GROUP BY 分组列
HAVING  组条件(通常含聚合函数)
ORDER BY ... 排序顺序

- GROUP BY 不一定要配合having
- GROUP BY 是按照....分组  就是 类似去重了 
- HAVING 就是对于这个分组的限制 一般都是





Find those makers of at least two different computers (PCs or laptops) with speeds of at least 2.80.

```sql
SELECT maker
FROM (
    SELECT p.maker, p.model
    FROM products p
    JOIN pcs pc ON pc.model = p.model
    WHERE pc.speed >= 2.80

    UNION

    SELECT p.maker, p.model
    FROM products p
    JOIN laptops l ON l.model = p.model
    WHERE l.speed >= 2.80
) t
GROUP BY maker
HAVING COUNT(DISTINCT model) >= 2;
```

### 8) Find all customer's information purchased model 1001 and 1002.

（找出**同时**买过 1001 和 1002 的客户信息）

```sql
SELECT c.*
FROM customers c
JOIN sales s ON s.customer_id = c.customer_id
WHERE s.model IN ('1001', '1002')
GROUP BY c.customer_id, c.firstname, c.lastname, c.city, c.address, c.email
HAVING COUNT(DISTINCT s.model) = 2;
```

> 记忆点：`WHERE model IN (...)` 先筛两种型号，再用 `HAVING COUNT(DISTINCT model)=2` 保证“两种都买过”。

------

### 9) Find all laptops whose hard disk is greater than all PCs' hard disk.

（找出所有 laptop，使其 hd **大于所有** PC 的 hd）

**最稳写法（用 MAX）：**

```sql
SELECT *
FROM laptops
WHERE hd > (SELECT MAX(hd) FROM pcs);
```

（也可以写 ALL：）

```sql
SELECT *
FROM laptops
WHERE hd > ALL (SELECT hd FROM pcs);
```

------

### 10) Find all customer that not purchase any products.

（找出从未购买过任何产品的客户）

**方法1：LEFT JOIN**

```sql
SELECT c.*
FROM customers c
LEFT JOIN sales s ON s.customer_id = c.customer_id
WHERE s.customer_id IS NULL;
```

**方法2：NOT EXISTS**

```sql
SELECT c.*
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM sales s
    WHERE s.customer_id = c.customer_id
);
```





# 实验三



> ## 触发器 Trigger

```sql
//创建触发器
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name FOR EACH ROW
BEGIN
   -- 觸發器執行的SQL語句
   statement1;
   statement2;
   statement3;
END;
// 查看触发器
show trigger
//删除触发器
drop trigger schemaName.triggerName
```





```sql
（1）定义AFTER行级触发器，当电脑表PCS的价格发生变化后就自动在价格变化表pcprice_log中增加一条相应记录

create trigger abbc
after update on 电脑表 for each row
begin 
 insert into log values(old.values,new.values,,,)
end

```



```sql
（2）定义AFTER行级触发器，当删除顾客表Customer中的一个顾客信息后，同时删除购买信息表Sales中该顾客的所有购买记录

create trigger abbb
begin DELETE on customer for each row 
begin 
 delete from sales where contomerid=old.contomerid
end

```



```sql
（3）需要对在表上进行DML操作的用户进行安全检查，看是否具有合适的特权


```





```sql
（5）如果一个特定客户支付的总金额高于10000，该客户的折扣小于10%然后将该客户的折扣设置为10%。在每一个插入到销售表之后，该触发器应该被激活。

create trigger asdgfsd
after insert on 表 for each row 
begin 
  DECLARE total_paid DECIMAL(12,2);

  SELECT SUM(paid)
  INTO total_paid
  FROM Sales
  WHERE customer_id = NEW.customer_id;

  IF total_paid > 10000 THEN
    UPDATE Customer
    SET discount = 0.1000
    WHERE customer_id = NEW.customer_id
      AND discount < 0.1000;
  END IF;
end
 
```



```sql
（6）在客户中添加一个新的"AllPaid"栏，如果插入或更新或删除一个销售元组，那么修改该客户的"AllPaid"的值
CREATE TRIGGER trg_sales_allpaid_ins
AFTER INSERT ON Sales
FOR EACH ROW
BEGIN
  UPDATE Customer
  SET AllPaid = AllPaid + NEW.paid
  WHERE customer_id = NEW.customer_id;
END

CREATE TRIGGER trg_sales_allpaid_ins
AFTER update ON Sales
FOR EACH ROW
BEGIN
  UPDATE Customer
  SET AllPaid = AllPaid + NEW.paid
  WHERE customer_id = NEW.customer_id;
END$$

CREATE TRIGGER trg_sales_allpaid_ins
AFTER delete ON Sales
FOR EACH ROW
BEGIN
  UPDATE Customer
  SET AllPaid = AllPaid + NEW.paid
  WHERE customer_id = NEW.customer_id;
END$$
```

**关于触发器的new虚拟表格和old虚拟表格**

当进行`insert`的时候，new表格可以获取插入的数据。

当进行`delete`的时候，old表格可以获取被删除行的数据。

当进行`update`的时候，new表格中是修改后的数据，old表格中是被修改行的数据。



# 实验四

> ## 存储过程

**我们之前所学习的 MySQL 语句都是针对一个表或几个表的单条 SQL 语句，但是在数据库的实际操作中，经常会有需要多条 SQL 语句处理多个表才能完成的操作。**

**例如：** `张三工资是否大于 所属部门的平均工资` 先知道张三部门，计算平均工资，是否大于

**存储过程**是一组为了完成特定功能的 **SQL 语句集合**

-  减少网络传输量（客户端不需要把所有的 SQL 语句通过网络发给服务器） 
-  减少了 SQL 语句暴露在 网上的风险，也提高了数据查询的安全性 
-  简化操作，提高了sql语句的重用性，减少了开发程序员的压力 
-  减少操作过程中的失误，提高效率

```sql
# 因为存储过程中,为了区分多条SQL每个SQL需要使用 ;分号作为结束符号 
# 而 Mysql ;分号是几乎所有sql语言的结束语 BEGIN --- END 中的分号会导致声明存储过程的语法结束,报错;  
# 所以：需要使用 DELTMITER 来改变MYSQL的结束符号 (这里的// 并不是固定,而是一个不会造成其它影响的一个特殊符号 可以随意更改Mysql的结束符号 
DELIMITER //

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
	DECLARE 
	#存储过程体
END //
# 因为 DELIMITER 更改了Mysql的结束符所以执行:  END // 结束存储过程声明


DELIMITER ;
# 为了存储过程中的多SQL ; 分号结束符不会导致存储过程声明的中断.
# DELTMITER 改变了Mysql的结束符, 当存储过程声明结束,为了不影响正常使用,建议将Mysql默认结束符 ; 更改回去,避免造成其它影响 

# 类似于Java 的函数定义语法： 修饰符 返回类型 方法名(参数类型 参数名,....){ 方法体; } 学过编程语言的这里应该都不是难事.
```



| 参数类型 | 作用                               | 传递方向               | 特点说明                                       |
| :------- | :--------------------------------- | :--------------------- | :--------------------------------------------- |
| IN       | 输入参数，给存储过程传入值         | 调用时传入，过程内只读 | 过程内不能修改传入值（改变不影响调用者）       |
| OUT      | 输出参数，存储过程把值返回给调用者 | 过程内写，调用后拿结果 | 调用时不传值，过程内部给它赋值，调用后取出值   |
| INOUT    | 输入输出参数，既传入又返回结果     | 调用时传入，过程内读写 | 过程内可以读取初始值并修改，调用结束后返回新值 |

> ## 例子

```sql
	CREATE PROCEDURE selId(IN ID INt, NNAME DOUBLE) # 如果只有一个输入参数 关键字in 可以省略
    BEGIN
        -- 	SELECT *  FROM employees WHERE employee_id = 100;
        SELECT * FROM employees WHERE employee_id = ID;
    END
    #调用: 方式一
	CALL selId(100);	
	#调用: 方式二
	CALL selId('100');
    #调用: 方式三,通过创建变量方式进行入参赋值...
    SET @eid = 100	
	CALL selId(@eid);
```



> ## 存储函数

```sql
-- 存储函数 和 存储过程声明语法大致也相同,
CREATE FUNCTION 函数名([IN] 参数名 参数类型)
RETURNS 返回值类型
[characteristics...]
BEGIN 
	-- 函数体
	-- 函数体中存在 RETURN 语句
END
```

- **参数列表：** 存储函数 声明比较严格，参数只能是 `IN入参` **默认也是IN 所以可以不用声明参数 IN 更加符合编程语言的规范.** 
-  **RETURNS** 表示存储函数的 返回类型, **存储函数只能有一个返回值. 且`必须有一个返回值.`** 
-  **[characteristics…]** 和存储过程一样，声明对函数内部的声明约束. 
-  **BEGIN…END** 和存储过程一样包含方法体，如果只有一条语句，也可以省略.



```sql
# 根据员工的id 查询员工的姓名
DELIMITER $$

CREATE FUNCTION find(empid INT)
RETURNS VARCHAR(60)
BEGIN
  DECLARE name VARCHAR(50);
  SELECT CONCAT(firstname, lastname) INTO name
  FROM employees
  WHERE id = empid;
  RETURN name;
END$$

DELIMITER ;


# 调用函数
select find(123);
select find(3123);
 
```

> **存储函数（FUNCTION）不支持 `OUT / INOUT` 参数**，只能有 **`IN` 参数**（输入参数）。



```sql
#编写一个存储过程 RateModel(cid, m, r)：
#将客户 cid 对型号 m 的新评分 r 插入到表 Ratings 中；
#字段 rating_time 应设置为当前日期与时间；
#该过程还需要更新表 Products 中型号 m 的：
#number_of_ratings（评分次数）
#total_rating（评分总和）

CREATE PROCEDURE RateModel(IN cid VARCHAR(20), IN m VARCHAR(20), IN r INT)
BEGIN
  DECLARE v_cnt INT;
  DECLARE v_sum INT;

  -- 1) 插入一条新评分（历史保留）
  INSERT INTO Ratings(customer_id, model, rating_time, rating)
  VALUES (cid, m, NOW(), r);

  -- 2) 重新统计该型号 m 的评分次数和总分（只统计非 NULL）
  SELECT COUNT(rating),SUM(rating)
  INTO v_cnt, v_sum
  FROM Ratings
  WHERE model = m AND rating IS NOT NULL;

  -- 3) 更新 Products
  UPDATE Products
  SET number_of_ratings = v_cnt,
      total_rating = IF(v_cnt = 0, NULL, v_sum)
  WHERE model = m;
END


```





```sql
#编写一个存储过程 DeleteRating(cid, m)：
#通过插入一条新记录的方式来“删除/取消”客户 cid 对型号 m 的评分；
#实现方式：在 Ratings 表中插入一条记录，其中：
#customer_id = cid
#model = m
#rating = NULL
#字段 rating_time 仍应设置为当前日期与时间。

create procedure DeleteRating(in cid int, in m varchar(50))
begin

```











# Functional Dependency and Normal Form



>请给出 **函数依赖 (Functional Dependency)** 的定义，并解释 **平凡函数依赖 (Trivial FD)** 和 **非平凡函数依赖 (Non-trivial FD)**。



> 求候选码

```sql
Let relation (i.e. table) R(ABCDEFGH) have the following functional dependencies:
A → B
CH → A
B → E
BD → C
EG → H
DE → F
Find all keys of relation R(ABCDEFG) with functional dependencies.
#这个FD的所有的候选码
```

1. **先算出所有的RHS**
   1. RHS就是在等号右边的 RHS={B A E C H F }
2. **所以我们要以不在RHS的字母开始 也就是C D**
3. **算{C D}+ 就是看 C D  能推出来什么 看起来什么都不能推出来 只有自己**
4. **那就算三个的 {ACD} 能推出全集 那就是 acd是一个key**
   1. 然后发现BCD也是
5. **发现剩下的三位的不够了**
6. 看四位的CDGH CDEG 是
7. 最终总结u所有的candidate key 是  {ADG} {BDG} {CDGH} {CDEG}
8. 判断下面是否有冗余的



1. 邪修法门
   1. L 只在左边出现过的  （包的）
   2. R 只在右边出现过的 （不可能）
   3. LR 两边都出现过的 （有可能成为候选码）





> 判断是否符合BCNF

```
The relation R has the attributes R(ABCD) and the functional dependencies F={AB→C, C→D, D→A}, Is the relation R in BCNF? Briefly explain your answer.
```



> 求最小依赖集