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

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;

1.3 非空约束 

分析：给某一个字段加入非空约束，则不能插入null的数据

-- 1.3 非空约束
CREATE TABLE test(
NAME VARCHAR(20) NOT NULL,
sex VARCHAR(2)
);
INSERT INTO test VALUES(NULL,'男'); 

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','女');

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','男');

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;

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;

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;

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);

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

-- 带有输出参数的存储过程
DELIMITER $
CREATE PROCEDURE pro_testByOut(OUT n VARCHAR(20))
BEGIN
	SET n = 'hello';
END $

CALL pro_testByOut(@n);
SELECT @n;

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;

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';