JAVA-20-数据约束、数据库设计三大范式、多表查询、存储过程、触发器、数据库权限

来源：互联网发布：三国志9优化编辑：程序博客网时间：2024/05/21 06:33

一、数据约束
给表添加数据约束，从而约束用户操作表数据的行为。
1.１默认值约束(default)
注意：当前没有插入默认值字段的时候，默认值才会起作用

CREATE TABLE test(    NAME VARCHAR(20),    gender VARCHAR(2) DEFAULT '男')INSERT INTO test(NAME) VALUES('张三');INSERT INTO test(NAME,gender) VALUES('张三',NULL);SELECT * FROM test;

注意： 1）当没有插入gender字段的时候，分配一个默认值
　　　2）可以插入null，那么值就是null
1.２非空约束（not null）

CREATE TABLE test(    NAME VARCHAR(20) NOT NULL,    gender VARCHAR(2))

注意：1)非空约束，不能不插入值

INSERT INTO test(gender) VALUES('男'); -- ERROR 1364 (HY000): Field 'NAME' doesn't have a default value

　　2)非空约束，不能插入null

INSERT INTO test(NAME,gender) VALUES(NULL,'男'); -- ERROR 1048 (23000): Column 'NAME' cannot be null

1.３唯一约束

CREATE TABLE test(    id INT UNIQUE,    NAME VARCHAR(20))

注意： 1)不能插入重复的值

INSERT INTO test(id,NAME) VALUES(1,'李四'); -- Duplicate entry '1' for key 'id'INSERT INTO test(id,NAME) VALUES(2,'李四');

　　2)唯一约束，可以插入多个null。所以唯一约束不能约束null

INSERT INTO test(id,NAME) VALUES(NULL,'王五');INSERT INTO test(id,NAME) VALUES(NULL,'陈六');

1.４主键约束(primary key)（唯一+非空）
　　注意：
　　　　1）通常情况下，我们会给每张表都会设置一个主键字段，用来标记记录的唯一性
　　　　2）但是不建议把业务含义字段作为主键，因为随着业务的变化，业务字段可能会出现重复。
　　　　3）建议给每张张独立添加一个叫id的字段，把这个id字段设置成主键，用来作为记录的唯一性。

-- 创建表给id字段施加主键CREATE TABLE test(    id INT PRIMARY KEY,    NAME VARCHAR(20))-- 1)唯一性INSERT INTO test(id,NAME) VALUES(1,'张三');INSERT INTO test(id,NAME) VALUES(1,'张三'); -- Duplicate entry '1' for key 'PRIMARY'-- 2）非空性INSERT INTO test(id,NAME) VALUES(NULL,'张三'); -- ERROR 1048 (23000): Column 'id' cannot be null

1.5 自增长约束（auto_increment）
通常使用的是：主键自增长约束primary key auto_increment

-- 创建表给id字段添加主键自增长约束CREATE TABLE test(    id INT PRIMARY KEY AUTO_INCREMENT,    NAME VARCHAR(20))

　　注意：1）自增长约束：初始值为0，每次递增1

-- 需求：id的值不需要我们开发者管理，交给数据库维护，这时给id添加自增长约束。INSERT INTO test(NAME) VALUES('张三');INSERT INTO test(NAME) VALUES('李四');INSERT INTO test(NAME) VALUES('王五');

　　　　２）delete不能重置主键，truncate可以重置主键

-- 删除数据-- delete from test where id=2;-- 使用truncate table删除数据的时候，可以把自增长的初始值置为0TRUNCATE TABLE test;-- 查看表中数据SELECT * FROM test;

1.６外键约束
　　外键约束：约束两种表的情况
　　　　①问题：什么情况下会出现两种表？
　　　　　　答：员工表中出现部门名称的冗余字段！！！！
　　　　②如何解决部门名称冗余问题?
　　　　　　答：独立设计一张部门表，把部门名称放到部门表中，这是员工表只需要关联部门的id即可！！
　　　　③问题：当插入员工表的部门id的时候，可能会出现不存在的部门id！！这是非法数据！！！如何防止非法数据的插入？
　　　　　　答：这是可以把员工表的部门id设置为外键约束。

CREATE TABLE employee(    id INT PRIMARY KEY AUTO_INCREMENT,    NAME VARCHAR(20),    deptName VARCHAR(20))INSERT INTO employee(NAME,deptName) VALUES('张三','软件开发部');INSERT INTO employee(NAME,deptName) VALUES('李四','软件维护部');INSERT INTO employee(NAME,deptName) VALUES('王五','软件开发部');INSERT INTO employee(NAME,deptName) VALUES('陈六','软件开发部');-- 问题：每次插入员工数据时，部门名称数据会出现重复（冗余）,如果数据出现冗余，那么会浪费数据库存储空间。-- 如何解决部门名称数据冗余的问题? 这时就可以设计一张独立的 部门表，把部门名称放到部门表中。-- 员工表CREATE TABLE employee(    id INT PRIMARY KEY AUTO_INCREMENT,    NAME VARCHAR(20),    deptId INT)-- 部门表CREATE TABLE dept(    id INT PRIMARY KEY AUTO_INCREMENT,    NAME VARCHAR(20))SELECT * FROM employee;SELECT * FROM dept;INSERT INTO dept(NAME) VALUES('软件开发部');INSERT INTO dept(NAME) VALUES('软件维护部');INSERT INTO employee(NAME,deptId) VALUES('陈六',1);INSERT INTO employee(NAME,deptId) VALUES('王五',1);INSERT INTO employee(NAME,deptId) VALUES('张三',3); -- 问题：在插入员工表的部门id的时候，插入了不存在的部门id，如何防止这种非法数据的插入，这时就添加外键约束了。-- 添加外键约束（foreign key）-- 需求：deptId字段值来自于dept表的id字段的值，这时可以给deptId字段添加外键约束。-- 员工表（副表： 被别的表约束。外键设置在副表）CREATE TABLE employee(    id INT PRIMARY KEY AUTO_INCREMENT,    NAME VARCHAR(20),    deptId INT,    CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)    --          外键名称                     外键字段  参考          )-- 部门表（主表：约束别人的表）CREATE TABLE dept(    id INT PRIMARY KEY AUTO_INCREMENT,    NAME VARCHAR(20))SELECT * FROM employee;SELECT * FROM dept;INSERT INTO employee(NAME,deptId) VALUES('陈六',1);INSERT INTO employee(NAME,deptId) VALUES('王五',1);-- 插入数据INSERT INTO employee(NAME,deptId) VALUES('张三',3); -- 违反外键约束：a foreign key constraint fails -- 修改数据UPDATE employee SET deptId=5 WHERE id=2; -- a foreign key constraint fails-- 删除数据DELETE FROM dept WHERE id=1; -- a foreign key constraint fails

　　外键约束在什么情况下，会其作用？
　　　　①插入数据：当往副表插入了主表不存在的数据时，外键起作用
　　　　②修改数据：当往副表修改为主表不存在的数据时，外键起作用
　　　　③删除数据: 副表中有关联主表的数据的时候，当删除主表数据，外键其作用

　　当有了外键之后，应该如何管理数据呢？
　　　　插入数据：先插入主表的数据，再插入副表数据
　　　　修改数据：先修改主表数据，再修改副表数据

UPDATE employee SET deptId=3 WHERE id=5; UPDATE dept SET id=3 WHERE id=2;

　　　　删除数据：先删除副表数据，再删除主表数据

DELETE FROM dept WHERE id=3;DELETE FROM employee WHERE deptId=3;

1.７级联技术
　　级联：当有了外键的时候，我们希望修改或删除数据的时候，修改或删除了主表的数据，同时能够影响副表的数据，这时就可以使用级联。

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     --          外键名称                     外键字段  参考          )

二、数据库设计
　　2.1 引入
　　　　需求分析：原始需求 -> 业务需求
　　　　　　客户业务模型：客户（姓名登记时间邮箱微信号）
　　　　需求设计：
　　　　　　业务模型（客户业务模型）： -》抽取实体模型： class Customer{name email weixin}
　　　　　　业务模型（客户业务模型） -> 数据模型：设计数据库表（customer表：字段）
2.2 数据库设计的三大范式
　　第一范式：要求表的每个字段必须独立的不可分割的单元

                        学生表： student    name             --违反第一范式                                         张三|狗娃                                         王含|张小含                        查询： 现用名中包含‘张’的学生                            select * from student where name like '%张%';                        学生表： student  name   old_name             --符合第一范式                                         张三      狗娃                                         王含      张小含

　　第二范式：在第一范式的基础上，要求表的除主键以外的字段都和主键有依赖关系的。也就是说张表应该只表达一个意思！！！！

                    员工表：employee                    员工编号  员工姓名  部门名称   订单名称      --违反第二范式                    员工表：employee                    员工编号  员工姓名  部门名称        --符合第二范式                    订单表：                        订单编号  订单名称

　　第三范式：在第二范式基础上，要求表的除主键外的字段都只能和主键有直接决定的依赖关系。

                员工表：employee                                                      员工编号  员工姓名  部门编号 部门名称　　　--不符合第三范式（出现数据冗余）                        1       张三       1    软件开发部                        2       李四       1    软件开发部                 员工表：employee                           --符合第三范式（降低数据冗余）                    员工编号  员工姓名  部门编号                         1       张三         1                            2       李四       1                    部门表： dept                    部门编号  部门名称                        1    软件开发部

　　注意：一般来说，数据库存储空间和数据查询效率是相悖的。存储空间越大，查询效率越低；存储空间越小，查询效率越高。
　　
三、多表查询
３.1 交叉连接查询（笛卡尔乘积: 4*3=12，产生笛卡尔积的原因是没有足够的连接条件）

-- 需求： 员工及其部门 SELECT employee.name,dept.name FROM employee,dept;-- 多表查询的步骤： 1）确定查询哪些表  2）确定查询哪些字段  3）确定连接条件(规则： 表数量-1)

３.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 左外连接查询
　　左外连接查询效果：左表（部门表）的数据全部显示，右表（员工）的数据当满足连接条件的时候，就显示满足条件的的数据，但是如果不满足连接条件，则显示null
　　需求：查询部门及其部门的员工（部门全部显示）

    -- 预期结果：    -- 软件开发部  张三    -- 软件开发部  王五    -- 软件维护部  李四    -- 软件维护部  陈六    -- 秘书部      nullSELECT d.name,e.name    FROM dept d    LEFT OUTER JOIN employee e    ON d.id=e.deptId;

３.4 右外连接查询
　　右外连接查询效果：右表（部门表）的数据全部显示，左表（员工）的数据当满足连接条件的时候，就显示满足条件的数据，但是如果不满足连接条件，则显示null

SELECT d.name,e.name    FROM employee e    RIGHT OUTER JOIN dept d    ON d.id=e.deptId;

３.5 自连接查询（虚拟一张表）
　　需求：查询员工姓名及其上司姓名(没有上司的员工也显示)

    -- 预期结果：    -- 员工    上司    -- 张三   null    -- 李四   张三    -- 王五   李四    -- 陈六   王五SELECT e.name AS '员工',b.name AS '上司'    FROM employee e    LEFT OUTER JOIN employee b    ON e.bossId=b.id;

四、mysql存储过程
4.1 引入：存储过程，其实就是带逻辑的（多个）sql语句。也是sql编程。
4.2 存储过程的特点
　　1）存储过程保存到数据库服务器端，通过数据库客户端工具调用存储过程
　　2）存储过程的效率会非常高！因为存储过程是在数据库服务器端执行。
　　3）存储过程的移植性非常差的！
4.3 存储过程语法
　　ａ．创建存储过程

-- 定义结束符号DELIMITER $CREATE PROCEDURE 存储过程名称 (形式参数列表) BEGIN　　多个sql语句END $

　　ｂ．调用存储过程

CALL 存储过程名称(实际参数列表);

　　ｃ．参数类型：
　　　　IN：输入参数，可以携带数据到存储过程中
　　　　OUT: 输出参数，可以携带数据到存储过程外面。
　　　　INOUT: 输入输出参数。
　　ｄ．mysql数据库三种变量：
　　　　1）全局变量。mysql内置的变量，mysql程序关闭的时候全局变量才会失效！！
　　　　　　show variables:产看全局变量
　　　　　　character_set_client: mysql接收的客户端的数据编码
　　　　　　character_set_results： mysql使用什么编码输出给客户端数据
　　　　　　查看某个全局变量： select @@变量名
　　　　　　修改某个全局变量： set @@变量名=值
　　　　2) 会话变量。变量只在某次登录的会话中有效！退出连接，会话变量数据失效！！
　　　　　　查看某个会话变量： select @变量名
　　　　　　修改/定义某个会话变量： set @变量名=值
　　　　3) 局部变量：在存储过程中定义的变量。存储过程结束局部变量失效！！
　　　　查看某个局部变量： select 变量名
　　　　修改某个局部变量： set 变量名=值
　　　　定义某个局部变量： declare 变量名数据类型;
4.4 练习：
　　①带有输入参数的存储过程

-- 需求： 传入员工id查询对应的员工DELIMITER $CREATE PROCEDURE pro_testByIn(IN eid INT)  -- 参数类型（IN） 参数名称 数据类型(int)BEGIN    SELECT * FROM employee WHERE id=eid;END $-- 调用CALL pro_testByIn(2);

　　②带有输出参数的存储过程

DELIMITER $CREATE PROCEDURE pro_testByOut(OUT n VARCHAR(20))BEGIN    -- 修改变量n    SET n = '输出参数';END $-- 定义会话变量去接收输出参数数据          -- set @n='eric';CALL pro_testByOut(@n);-- 查看会话变量nSELECT @n;

　　③带有输入输出参数的存储过程

DELIMITER $CREATE PROCEDURE pro_testByInOut(INOUT n VARCHAR(20))BEGIN    -- 查看n变量    SELECT n;    -- 修改n变量    SET n = '500';END $-- 定义会话变量调用存储过程SET @n='100';CALL pro_testByInOut(@n);-- 查看nSELECT @n;

　　④带有判断条件的存储过程

-- 需求： 输入一个num整数，num=1 ,输出‘星期一’,num=2,输出‘星期二’，num=3,输出‘星期三’，否则，输出‘错误参数’DELIMITER $CREATE PROCEDURE pro_testByIf(IN num INT,OUT str VARCHAR(20))BEGIN    IF num=1 THEN        SET str = '星期一';    ELSEIF num= 2 THEN        SET str ='星期二';    ELSEIF num=3 THEN        SET str = '星期三';    ELSE        SET str = '错误参数';    END IF;END $CALL pro_testByIf(5,@str);SELECT @str;

　　⑤带有循环条件的存储过程
– 需求：输入一个num，计算从1到num的总和。

DELIMITER $CREATE PROCEDURE pro_testByWhile(IN num INT,OUT score INT)BEGIN    -- 定义局部变量    DECLARE i INT DEFAULT 1;    DECLARE result INT DEFAULT 0;    WHILE i<=num DO        SET result = result + i;        SET i = i+1;    END WHILE;    SET score = result;END $CALL pro_testByWhile(200,@score);SELECT @score;

⑥ 携带数据库的数据给输出参数(INTO)

-- 需求： 传入员工id，查询对应的员工，输出员工姓名DELIMITER $CREATE PROCEDURE pro_testByData(IN eid INT,OUT sname VARCHAR(20))BEGIN    SELECT NAME INTO sname FROM employee WHERE id=eid;END $CALL pro_testByData(2,@sname);SELECT @sname;ss

五、触发器
5.1 简介
　　当往员工表插入/修改/删除一条数据的时候，同时往日志表记录下来，这时就要使用触发器完成。
5.2 触发器语法

-- 员工日志表CREATE TABLE emp_log(    id INT PRIMARY KEY AUTO_INCREMENT,    content VARCHAR(20))

-- 创建增加触发器CREATE TRIGGER tri_empAdd AFTER INSERT ON employee FOR EACH ROW    INSERT INTO emp_log(content) VALUES('员工被插入了一条记录');-- 创建修改触发器CREATE TRIGGER tri_empUpd AFTER UPDATE ON employee FOR EACH ROW    INSERT INTO emp_log(content) VALUES('员工被修改了一条记录');-- 创建删除触发器CREATE TRIGGER tri_empDel AFTER DELETE ON employee FOR EACH ROW    INSERT INTO emp_log(content) VALUES('员工被删除了一条记录');DROP TRIGGER tri_empAdd;SELECT * FROM employee;SELECT * FROM emp_log;INSERT INTO employee(NAME,deptId,bossId) VALUES('李七',5,1);UPDATE employee SET NAME='张三' WHERE id=10;DELETE FROM employee WHERE id=10;

5.3 数据库的权限问题——–mysql权限问题
　　①root用户：超级管理员。权限：增删改查（数据库，表，数据）
　　②给mysql数据库分配不同权限的不同用户
　　③mysql数据库：存放mysql配置信息，包括用户信息

USE mysql;-- 用户表SELECT * FROM USER;

加密函数(md5算法–单向加密、不可解密)

SELECT PASSWORD('root'); -- *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B

修改用户密码

UPDATE USER SET PASSWORD=PASSWORD('123456') WHERE USER='root';

分配权限给不同的用户

-- 需求： 分配查询day15数据库的employee表的权限给eric用户，密码为‘123456’GRANT SELECT ON day15.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';GRANT DELETE ON day15.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';SHOW TABLES;

阅读全文

1 0