java学习（20） -- 数据库MySql（二）

电玩女神 2022-06-14 00:26 236阅读 0赞

**java学习（20） -- 数据库MySql（二）**

> **这篇接着来写关于MySql数据库的内容。**

1.数据约束

> 1.1 简介
> 
> 给表添加数据约束，从而约束用户操作表数据的行为。
> 
>   
> 
> 
> 1.2 默认值约束
> 
> 注意：当前没有插入默认值字段的时候，默认值才会起作用
> 
> 分析：这里给性别加默认值约束，如果插入数据的时候没有给性别插入数据，默认赋值，另外，如果插入数据为空串''或者NULL，就为空串''或者NULL，不会插入默认值了。
> 
>     -- 1.2 默认值约束
>     CREATE TABLE test(
>     NAME VARCHAR(20),
>     sex VARCHAR(2) DEFAULT '男'
>     );
>     INSERT INTO test(NAME) VALUES('s1');
>     
>     SELECT * FROM test;
> 
> ![Center][]  
>   
> 
> 
> 1.3 非空约束 
> 
> 分析：给某一个字段加入非空约束，则不能插入null的数据
> 
>     -- 1.3 非空约束
>     CREATE TABLE test(
>     NAME VARCHAR(20) NOT NULL,
>     sex VARCHAR(2)
>     );
>     INSERT INTO test VALUES(NULL,'男');
> 
> ![Center 1][]  
>   
> 
> 
> 1.4 唯一约束 
> 
>     -- 唯一约束(unique)
>     CREATE TABLE test(
>     -- 给stu表中的id字段添加唯一约束
>     id INT UNIQUE,
>     NAME VARCHAR(20),
>     sex VARCHAR(2)
>     );
>     -- 给表中插入两条id相同的数据
>     INSERT INTO test VALUES(1,'s2','男');
>     INSERT INTO test VALUES(1,'s2','男');
>     
>     
>     -- 给表中插入一条id为null的数据，当给id添加了唯一约束之后，
>     -- 依然可以给他插入多条null值，不会出现重复
>     INSERT INTO test VALUES(NULL,'s2','男');
>     INSERT INTO test VALUES(NULL,'s3','女');
> 
> ![Center 2][]  
>   
> 
> 
> 1.5 主键约束 
> 
> 分析：主键约束有着唯一并且非空的约束，不能有重复或者null
> 
>     -- 主键(primary key)
>     CREATE TABLE test(
>     id INT PRIMARY KEY,
>     NAME VARCHAR(20),
>     gender VARCHAR(2)
>     );
>     -- 给表中插入两条id为1的学生信息
>     INSERT INTO test VALUES(1,'s1','男');
>     INSERT INTO test VALUES(1,'s2','男');
>     
>     -- 给表中插入id为null的元素
>     INSERT INTO test VALUES(NULL,'s1','男');
> 
> ![Center 3][] ![Center 4][]  
> 
> 
>   
> 
> 
> 1.6 自增长约束
> 
> 分析：自增长约束一般用于主键，不用进行插入数据，每次插入一条数据，自动加一
> 
>     -- 自增长约束(auto_increment)
>     CREATE TABLE test(
>     id INT PRIMARY KEY AUTO_INCREMENT,
>     NAME VARCHAR(20),
>     gender VARCHAR(2)
>     );
>     
>     INSERT INTO test(NAME,gender) VALUES('s1','男');
>     INSERT INTO test(NAME,gender) VALUES('s2','男');
>     INSERT INTO test(NAME,gender) VALUES('s3','男');
>     INSERT INTO test(NAME,gender) VALUES('s4','男');
>     SELECT * FROM test;
> 
> ![Center 5][]
> 
>   
> 
> 
> 1.7 外键约束
> 
> 分析：当数据表有数据冗余或者一张表中数据依赖另一张表中数据时，会用到外键约束
> 
> 当有了外键约束之后，操作数据的顺序如下：
> 
> （1）插入数据：先插入主表的数据，再插入副表数据
> 
> （2）修改数据：先修改主表数据，再修改副表数据
> 
> （3）删除数据：先删除副表数据，再删除主表数据
> 
> 如下，创建部门表，员工表，在员工表中加入外键约束：
> 
>     -- 添加外键约束（foreign key）
>     -- 员工表
>     CREATE TABLE employee(
>     	id INT PRIMARY KEY AUTO_INCREMENT,
>     	NAME VARCHAR(20),
>     	deptId INT,
>     	CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE 
>     )
>     
>     -- 部门表
>     CREATE TABLE dept(
>     	id INT PRIMARY KEY AUTO_INCREMENT,
>     	NAME VARCHAR(20)
>     )
> 
>   
> 
> 
> 1.8 级联技术  
> 
> 级联：当有了外键的时候，在修改或删除数据的时候，修改或删除了主表的数据，同时能够影响副表的数据，这时就可以使用级联。
> 
>     -- 1.8 级联技术
>     CREATE TABLE employee(
>     	id INT PRIMARY KEY AUTO_INCREMENT,
>     	NAME VARCHAR(20),
>     	deptId INT,
>     	-- 添加级联修改： ON UPDATE CASCADE
>     	-- 添加级联删除： ON DELETE CASCADE 
>     	CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE 
>     )
>     -- 部门表（主表：约束别人的表）
>     CREATE TABLE dept(
>     	id INT PRIMARY KEY AUTO_INCREMENT,
>     	NAME VARCHAR(20)
>     )
> 
> 分析：添加了级联，在主表进行的修改，也会影响副表。  
>   
>

# 2.数据库设计 #

> # 2.1 引入 #
> 
> # 需求分析：原始需求 \->业务需求 #
> 
> # 业务模型（客户业务模型） -> 抽取实体模型 #
> 
> # 业务模型（客户业务模型） \->  数据模型：设计数据库表 #
> 
> #     #
> 
> # 2.2 三大范式 #
> 
> # （1）第一范式：要求表的每个字段必须独立的不可分割的单元 #
> 
> # （2）第二范式：在第一范式的基础上，要求表的除主键以外的字段都和主键有依赖关系的，即一张表应该只表达一个意思 #
> 
> # （3）第三范式：在第二范式基础上，要求表的除主键外的字段都只能和主键有直接决定的依赖关系。 #
> 
>   
> 
> 
> # 2.3 数据库存储空间  和 数据查询效率 #
> 
> # 数据库存储空间和数据查询效率是比较矛盾的存在，一般来说，二者相反，即 #
> 
> # 减少存储空间的同时必将降低查询效率；增大存储空间的同时能提高查询效率 #
> 
> #     #

3.多表查询

> 3.1 交叉连接查询
> 
>     -- 交叉连接查询（笛卡尔乘积: 产生笛卡尔积的原因是没有足够的连接条件）
>     -- 需求： 员工及其部门 
>     SELECT employee.name,dept.name 
>     FROM employee,dept;
> 
>   
> 
> 
> 3.2 内连接查询
> 
>     -- 内连接查询（使用最多）
>     SELECT e.name,d.name
>     FROM employee e,dept d
>     WHERE e.deptId=d.id;
>     
>     -- 另一种语法
>     SELECT e.name,d.name
>     FROM employee e
>     INNER JOIN dept d
>     ON e.deptId=d.id;
> 
>   
> 
> 
>   
> 
> 
> 3.3左外链接查询
> 
>     -- 左外连接查询
>     -- 需求： 查询部门及其部门的员工（部门全部显示）
>     SELECT d.name,e.name
>     FROM dept d
>     LEFT OUTER JOIN employee e
>     ON d.id=e.deptId;
> 
> ![Center 6][]  
> 
> 
>   
> 
> 
> 3.4右外连接查询
> 
>     -- 右外连接查询
>     -- 右外连接查询效果： 右表（部门表）的数据全部显示，左表（员工）的数据当满足连接条件的时候，
>     -- 就显示满足条件的数据，但是如果不满足连接条件，则显示null
>     SELECT d.name,e.name
>     FROM   employee e
>     RIGHT OUTER JOIN dept d
>     ON d.id=e.deptId;
> 
>   
> 
> 
> 3.5自连接查询
> 
>     -- 自连接查询
>     -- 需求： 查询员工姓名及其上司姓名(没有上司的员工也显示)
>     ALTER TABLE employee ADD COLUMN bossId INT;
>     SELECT e.name AS '员工',b.name AS 'BOSS'
>     FROM employee e
>     LEFT OUTER JOIN employee b
>     ON e.bossId=b.id;
> 
> ![Center 7][]  
>   
>

4.mysql存储过程

> 4.1 引入
> 
> 存储过程，其实就是带逻辑的（多个）sql语句。也是sql编程
> 
>   
> 
> 
> 4.2 存储过程的特点
> 
> 1）存储过程保存到数据库服务器端，通过数据库客户端工具调用存储过程
> 
> 2）存储过程的效率会非常高！因为存储过程是在数据库服务器端执行。
> 
> 3）存储过程的移植性非常差的
> 
>   
> 
> 
> 4.3 存储过程语法
> 
>     -- 存储过程
>     DELIMITER $
>     CREATE PROCEDURE pro_test(参数列表)
>     BEGIN
>          SQL语句
>     END$
>     -- 调用存储过程
>     CALL pro_test(参数);
> 
>   
> 
> 
> 4.3.1带有输入参数的存储过程
> 
>     -- 带有输入参数的存储过程
>     -- 需求： 传入员工id查询对应的员工
>     DELIMITER $
>     CREATE PROCEDURE pro_testIn(IN nId INT)
>     BEGIN
>     	SELECT * FROM employee WHERE id=nId;
>     END$
>     CALL pro_testIn(3);
> 
> ![Center 8][]  
>   
> 
> 
>   
> 
> 
> 4.3.2带有输出参数的存储过程
> 
>     -- 带有输出参数的存储过程
>     DELIMITER $
>     CREATE PROCEDURE pro_testByOut(OUT n VARCHAR(20))
>     BEGIN
>     	SET n = 'hello';
>     END $
>     
>     CALL pro_testByOut(@n);
>     SELECT @n;
> 
> ![Center 9][]  
>   
> 
> 
> 4.3.3带有输入输出参数的存储过程
> 
>     -- 带有输入输出参数的存储过程
>     DELIMITER $
>     CREATE PROCEDURE pro_testByInOut(INOUT n VARCHAR(20))
>     BEGIN
>     	SELECT n;
>     	SET n = '200';
>     END $
>     
>     -- 调用存储过程
>     SET @n='50';
>     CALL pro_testByInOut(@n);
>     SELECT @n;
> 
> ![Center 10][] ![Center 11][]  
>   
>

5.触发器

> 5.1 简介
> 
> 当往员工表插入/修改/删除一条数据的时候，同时往日志表记录下来，这时就要使用触发器完成。
> 
>   
> 
> 
> 5.2 触发器语法
> 
> 分析：这里简单的写了几个触发器，当对员工表进行修改，删除，插入的时候，会在日志表里有记录：

>     -- 触发器
>     -- 员工日志表
>     CREATE TABLE employ_log(
>     id INT PRIMARY KEY AUTO_INCREMENT,
>     txt VARCHAR(50)
>     );
>     CREATE TRIGGER tri_empolyAdd AFTER INSERT ON employee FOR EACH ROW
>     	INSERT INTO employ_log(txt) VALUES('员工被插入了一条记录');
>     CREATE TRIGGER tri_empolyUpdate AFTER UPDATE ON employee FOR EACH ROW
>     	INSERT INTO employ_log(txt) VALUES('员工被更改了一条记录');
>     CREATE TRIGGER tri_empolyDelete AFTER DELETE ON employee FOR EACH ROW
>     	INSERT INTO employ_log(txt) VALUES('员工被删除了一条记录');
> 
>   
>

6.数据库

> 6.1 root
> 
> 超级管理员，拥有对数据库的所有权限，并且能分配权限

>   
> 
> 
> 6.2权限分配：
> 
> 分配数据库test的employee表的查询权限给用户eric，密码123456，在分配的同时，创建该用户，用户信息在mysq数据库的user表中存储：
> 
>     -- 分配权限给不同的用户
>     GRANT SELECT ON test.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';
> 
>   
>

[Center]: /images/20220614/d7406d081f4e4882977423ad415fd63a.png
[Center 1]: /images/20220614/5bfa2ca67f314c4ab72ad58698cc28f1.png
[Center 2]: /images/20220614/dfddd345723c4a1486d70d76c5829ebf.png
[Center 3]: https://img-blog.csdn.net/20170605150602246?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvQWdfbmV2ZXJnaXZldXA=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 4]: /images/20220614/dbcfb2528b504352b3ff1c28e907bd9f.png
[Center 5]: /images/20220614/0f34d02e0a52410f846caff8c417d6bb.png
[Center 6]: /images/20220614/b1cddbd301f84a3d922a5375bbf18638.png
[Center 7]: /images/20220614/a5a3d07f5e094f5d8fbb33cc69daa854.png
[Center 8]: /images/20220614/4fe4ce27f04c4c75b848c18893866c52.png
[Center 9]: /images/20220614/52b2a1dded854d4db66a48588fedfb43.png
[Center 10]: https://img-blog.csdn.net/20170605155051288?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvQWdfbmV2ZXJnaXZldXA=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 11]: /images/20220614/d70e5f16732e4cb5a9b3cc530a3d2286.png