java学习【数据库入门2】

CREATE DATABASE day20 DEFAULT CHARACTER SET utf8;
– 外键约束：当一张表的数据来自于另一张表的时候，这种情况下就会出现一个外键约束
– 创建一张员工表(id,name,age,deptName)
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
deptName VARCHAR(20)
);
– 给表中添加几条数据
INSERT INTO employee(NAME,age,deptName) VALUES(‘华仔’,50,’软件开发部’);
INSERT INTO employee(NAME,age,deptName) VALUES(‘小月月’,30,’软件开发部’);
INSERT INTO employee(NAME,age,deptName) VALUES(‘孙胖子’,40,’软件开发部’);
INSERT INTO employee(NAME,age,deptName) VALUES(‘郭德纲’,50,’软件维护部’);
INSERT INTO employee(NAME,age,deptName) VALUES(‘谦哥’,50,’软件维护部’);
INSERT INTO employee(NAME,age,deptName) VALUES(‘张学友’,55,’秘书部’);

– 根据我们上面的数据插入，我们可以分析得知，当我们插入大量的重复的字段数据的时候
– 我们会发现，是非常的浪费我们的数据库的存储空间的，因为在这里出现了大量的重复的数据，这个俗称数据冗余

– 如何解决上面的数据库冗余呢，我们可以在这里先创建一张部门表
– 部门id 部门名称
– 1 软件开发部
– 2 软件维护部
– 3 秘书部

– 在我们的数据库中创建一张部门表
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,
deptName VARCHAR(20)
);
– 给部门表插入数据
INSERT INTO dept(deptName) VALUES(‘软件开发部’);
INSERT INTO dept(deptName) VALUES(‘软件维护部’);
INSERT INTO dept(deptName) VALUES(‘秘书部’);

– 给员工表加上部门id之后，改造员工表
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
deptId INT
);
– 给改造后的员工表中插入数据
INSERT INTO employee(NAME,age,deptId) VALUES(‘华仔’,50,1);
INSERT INTO employee(NAME,age,deptId) VALUES(‘小月月’,30,1);
INSERT INTO employee(NAME,age,deptId) VALUES(‘孙胖子’,40,1);
INSERT INTO employee(NAME,age,deptId) VALUES(‘郭德纲’,50,2);
INSERT INTO employee(NAME,age,deptId) VALUES(‘谦哥’,50,2);
INSERT INTO employee(NAME,age,deptId) VALUES(‘张学友’,55,4);

– 我们测试发现当我们给员工表中插入一个部门表中不存在的部门id的时候依然是可以插入的，
– 但是这种方式是完全不对的，因为员工表的deptId字段来自于部门表中的id字段，所以在此时我们就得施加外键约束了
– 继续对员工表进行改造，给deptID字段施加外键约束
– 附表：被约束的表
– 主表：约束副表的表
CREATE TABLE employee(– 附表
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
deptId INT,– 外键
CONSTRAINT emp_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)
– 声明 外键名称 外键 字段 依赖于 那张表的那个字段
);
– 当我们给表中插入的deptId的值在dept表中的id字段找不到的时候，外键就起作用了，插不进去
INSERT INTO employee(NAME,age,deptId) VALUES(‘刘亦菲’,30,4);– a foreign key constraint fails

– 需求，将华仔的部门id改为4
UPDATE employee SET deptId=4 WHERE id=1;– a foreign key constraint fails

– 需求：删除主表中的软件开发部
DELETE FROM dept WHERE id=1;– a foreign key constraint fails

– 什么情况下外键会起作用
– 1.给副表中插入主表中不存在的数据的时候，外键起作用
– 2.当将副表中的数据改为主表中不存在的数据的时候，外键起作用
– 3.当我们删除附表中还在使用的主表中的数据的时候，外键起作用

– 当我们有了外键之后，我们该怎么去操作数据库呢
– 1.插入数据的时候，先插入主表，再插入副表
– 需求：给副表中插入刘亦菲，deptId=4,
INSERT INTO dept(deptName) VALUES(‘后勤部’);
INSERT INTO employee(NAME,age,deptId) VALUES(‘刘亦菲’,30,4);
– 2.当我们修改副表中的数据的时候，先修改主表
– 需求：将华仔的部门改为4
UPDATE employee SET deptId=4 WHERE id=1;
– 3.删除主表数据的时候，先得将副表中的数据删除干净，再去删除主表中的数据
– 需求：删除主表中的id为4的部门
DELETE FROM employee WHERE id=1;
DELETE FROM employee WHERE id=8;
DELETE FROM dept WHERE id=4;

---

– 级联技术：当我们修改或者删除主表的时候，同时能够影响到副表的数据
– 需要将部门表中的秘书部id改为4
– 创建一张员工表，添加外键约束，并且添加级联(修改的级联技术ON UPDATE CASCADE)
CREATE TABLE employee(– 附表
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
deptId INT,– 外键
CONSTRAINT emp_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE
– 声明 外键名称 外键 字段 依赖于 那张表的那个字段
);
– 修改部门表中的秘书部id该4
UPDATE dept SET id=4 WHERE id=3;

– 删除id为4的秘书部,ON DELETE CASCADE
DELETE FROM dept WHERE id=4;

---

– 数据库设计的三大范式
– 原始需求–>业务需求
– 登记时间 姓名 电话 微信 。。。需求分析师 —>原始需求
– 需求分析师将原始需求–>程序员–>业务需求
– 设计表(customer):id,name,number,weixin

– 而我们在设计数据库表的时候是需要遵循一些原则的，就是三大范式
– 第一大范式：要求表中的每一个字段都是一个独立的不可拆分的字段
– student表
– id 姓名(曾用名|现用名) 年龄
– 1 张翔|张含 30
– 2 王勇|张刚 40
– 需求：查询曾用名中姓张的学生
– select * from student where name like ‘张%’；
– 为了遵守第一大范式，我们可以将上面的student表进行修改
– id oldName nowName age
– 1 张翔 张晗 30
– 2 王勇 张刚 40

– 第二大范式：一张表只能表达一个意思
– student
– id name age 商品id(商品id)
– 1 郭德纲 40 1

– 为了满足第二大范式，我们在这里必须重新创建一个商品表
– product表
– 商品id 商品名称
– 1 纸尿裤
– 2 刮胡刀

– student表
– id name age

– 第三大范式：要求表中的每一个字段只能和主键有决定性的关系
– 员工表，这样做的话不满足第三大范式
– 员工id 姓名 部门id 部门名称
– 1 郭德纲 1 软件开发部
– 2 岳云鹏 2 软件维护部
– 3 刘德华 3 后勤部

– 上面的表结构不满足第三大范式，我们对其进行改造,拆分成两张表
– 员工表
– id name deptId

– 部门表
– id deptName

– 当我们降低了数据冗余之后，就会形成多张表，在我们进行查询数据的时候，我们是一张表查询数据快呢？
– 还是多张表查询数据快呢？
– 结论：存储空间和你的查询效率之间是一个矛盾的东西，当你降低了数据的冗余度的时候你的查询效率就会降低
– 当数据的存储空间占用的比较大的时候，我们不关心数据冗余这个问题的时候，但是查询效率比较高

---

– 多表查询
– 1.交叉连接查询（笛卡尔乘积 5*3=15，就是因为没有足够的连接条件）
– 需求：查询每一个员工的姓名以及对应的部门名称
– 预计结果：
– 姓名 部门名称
– 华仔 软件开发部
– 郭德纲 软件维护部
SELECT NAME,deptName
FROM employee,dept;

– 多表查询：
– 1.确定需要查询几张表
– 2.需要确定查询哪些字段
– 3.需要足够的连接条件(连接条件的数量：表数量-1)

– 2.内连接查询(用得最多)：只有满足连接条件的时候结果才会给予显示
– 需求：查询员工的姓名以及对应的部门名称

– 查询的是哪张表的哪个字段
SELECT e.name,d.deptName
– 需要查询的表
FROM employee e,dept d
– 连接条件
WHERE e.deptId=d.id;

– 内连接另一种写法
SELECT NAME,deptName
FROM employee e
INNER JOIN dept d
ON e.deptId=d.id;

– 3.左外连接查询
– 需求：查询部门以及部门对应的员工(要求部门全部显示)
– 预期结果：
– 部门名称 员工姓名
– 软件开发部 华仔
– 软件开发部 小月月
– 软件开发部 孙胖子
– 软件维护部 郭德纲
– 软件维护部 谦哥
– 秘书部 null

– 按照之前内连接的查询方式查询,
SELECT deptName,NAME
FROM dept,employee
WHERE dept.id=employee.deptId;

– 左外连接查询：左表数据满足连接条件就显示满足连接条件的数据，当不满足链接条件的时候显示null
– 左表和右表的区分：LEFT OUTER JOIN左边的就叫左表，右边的就叫右表
SELECT deptName,NAME
FROM dept d
LEFT OUTER JOIN employee e
ON e.deptId=d.id;

– 右外连接查询：优先显示右表，如果满足连接条件就显示满足连接条件的数据，如果不满足连接条件就显示null
– 需求：查询部门名称及对应的员工姓名(部门名称全部显示)– 使用右外连接查询
– 在右外连接中如何区分左表右表呢？
– RIGHT OUTER JOIN 右边的就叫右表，左边的就叫左表
SELECT deptName,NAME
FROM employee e
RIGHT OUTER JOIN dept d
ON e.deptId=d.id;

– 4.自连接查询
– 需求：查询员工及其对应的领导
– 给员工表中添加bossId这个字段
ALTER TABLE employee ADD COLUMN bossId INT;
– 预期结果
– 员工 上司姓名
– 华仔 null
– 小月月 华仔
– 孙胖子 小月月
– 郭德纲 孙胖子
– 谦哥 郭德纲

– 内连接查询：只有满足连接条件的时候才显示数据
SELECT e.name,b.name
FROM employee e,employee b
WHERE e.bossId=b.id;

– 使用左外连接查询改进上面的查询方式
SELECT e.name,b.name
FROM employee e
LEFT OUTER JOIN employee b
ON e.bossId=b.id;

SELECT * FROM employee;
SELECT * FROM dept;
DROP TABLE employee;

---

– 存储过程：多个sql语句组成的具有一定逻辑的语句，sql编程
– 存储过程特点：
– 1.保存在mysql数据库的服务器端
– 2.我们直接可以通过在客户端发送指令去调用我们的存储过程
– 3.执行效率会比较高,存储过程的移植性非常差

– 举例：
– 需求：需要查询id为2的员工的时候
SELECT * FROM employee WHERE id=2;
SELECT * FROM employee WHERE id=3;
SELECT * FROM employee WHERE id=4;

– 存储过程的语法
– delimiter –声明一个结束符–createprocedurepro T est(输入或者输出参数)–begin–带有逻辑的sql语句–end 

– 调用存储过程：
– call pro_Test(实参);

– 存储过程的输入输出参数如何表示呢？
– in 输入参数的变量名称 类型
– out 输出参数的名称 类型
– inout 输入输出参数名称 类型

– 需求：根据上面的语法叙述，创建一个不带参数的存储过程，并调用
DELIMITER CREATEPROCEDUREpro T est2()BEGIN–这里面写sql语句SELECT∗FROMemployeeWHEREid=2;END 

– 调用上面的存储过程
CALL pro_Test2();

– 创建一个带有输入参数的存储过程
– 需求：给存储过程传入员工id查询对应的员工信息
DELIMITER CREATEPROCEDUREpro Q ueryEmpById(INeIdINT)BEGINSELECT∗FROMemployeeWHEREid=eId;END 

– 调用上面的带有输入参数的存储过程
– 需求，查询id为4的员工信息
CALL pro_QueryEmpById(4);

– 思考一个问题？我们在调用带有输出参数的存储过程的时候，肯定是会返回一个值的，
– 那么我们该使用什么变量去接受存储过程给我返回来的这个值呢
– 在我们的数据库中有哪些变量可以充当这个变量使用呢？
– 1.全局变量(mysql数据库的系统变量)：随着mysql数据库的启动而存在，随着mysql数据库的关闭而消失
– 查看mysql数据库的全局变量
SHOW VARIABLES;
– 查看mysql数据库中全部变量和字符相关的
SHOW VARIABLES LIKE ‘character%’;
– 如何去查看mysql数据库的全局变量
– select @@+全局变量名
– 如何去改变数据库的全局变量
– set @@+全局变量=值
SELECT @@character_set_client;
SET @@character_set_client=’utf8’;

– 2.会话变量：存在于某一次会话中，随着会话的结束而消失
– 如何去查看一个会话变量
– select @+变量名
– 给会话变量设置值
– set @变量名=值
SET @n=’hello’;
SELECT @n;

– 3.局部变量：位于存储过程中，随着存储过程而存在，随着存储过程的调用完毕而消失
– 给局部变量设置值
– set 局部变量=值
– 查看局部变量
– select 局部变量

– 创建一个带有输出参数的存储过程
DELIMITER CREATEPROCEDUREpro T estOut(OUTstrVARCHAR(20))BEGIN–给输出参数赋值SETstr= ′ 我是输出参数 ′ ;END 

– 根据我们刚才的分析，我们，目前必须使用会话变量去接收一个带有输出参数的带出的值
SET @nn=’world’;
SELECT @nn;
CALL pro_TestOut(@nn)
SELECT @nn;

– 3.4 带有判断条件的存储过程
– 需求： 输入一个num整数，num=1 ,输出‘星期一’,num=2,输出‘星期二’，
– num=3,输出‘星期三’，否则，输出‘错误参数’
DELIMITER CREATEPROCEDUREpro T estDay(INnumINT,OUTdVARCHAR(20))BEGINIFnum=1THENSETd= ′ 星期一 ′ ;ELSEIFnum=2THENSETd=′ 星期二 ′ ;ELSEIFnum=3THENSETd= ′ 星期三 ′ ;ELSESETd= ′ 参数错误 ′ ;ENDIF;END 

– 调用上面的这个带有输入和输出参数的存储过程
CALL pro_TestDay(4,@d);
SELECT @d;

– 3.5 带有循环条件的存储过程
– 需求： 输入一个num，计算从1到num的总和。
DELIMITER CREATEPROCEDUREpro T estSum(INnumINT,OUTscoreINT)BEGINDECLAREiINTDEFAULT1;DECLAREresINTDEFAULT0;WHILEi<=numDOSETres=res+i;SETi=i+1;ENDWHILE;–将局部变量res的值赋值给scoreSETscore=res;END 

– 调用上面的存储过程
CALL pro_TestSum(200,@score);
SELECT @score;

– 3.6 携带数据库的数据给输出参数(INTO)
– 需求： 传入员工id，查询对应的员工，输出员工姓名
DELIMITER CREATEPROCEDUREpro Q ueryNameById(INeIdINT,OUTeNameVARCHAR(20))BEGINSELECTNAMEINTOeNameFROMemployeeWHEREid=eId;END 
– 调用这个存储过程
CALL pro_QueryNameById(2,@eName);
SELECT @eName;

– 删除存储过过程
DROP PROCEDURE pro_QueryEmpById;

---

– 触发器：当往员工表中插入，删除，修改一条数据的时候，我们可以将我们的操作记录在一张表上，这样就需要使用
– 触发器完成它

– 创建一个日志表
CREATE TABLE empLog(
id INT PRIMARY KEY AUTO_INCREMENT,
content VARCHAR(20)
);
– 创建一个触发器，当往员工表中插入一条数据的时候，自动往日志表中添加一条插入的记录
CREATE TRIGGER tri_empInsert AFTER INSERT ON employee FOR EACH ROW
INSERT INTO empLog(content) VALUES(‘员工表中被插入一条数据’);
– 给员工表中插入一条数据
INSERT INTO employee(NAME) VALUES(‘周华健’);

– 创建一个触发器，当往员工表中修改一条数据的时候，往日指标中添加一条记录
CREATE TRIGGER tri_empUpdate AFTER UPDATE ON employee FOR EACH ROW
INSERT INTO empLog(content) VALUES(‘员工表中被修改一条数据’);
– 修改员工表中的一条数据
UPDATE employee SET NAME=’成龙’ WHERE id=7;

– 创建一个触发器，当删除员工表的一条数据的时候，给日志表中添加一天删除的记录
CREATE TRIGGER tri_empDelete AFTER DELETE ON employee FOR EACH ROW
INSERT INTO empLog(content) VALUES(‘员工表中被删除一条数据’);
– 删除周华健
DELETE FROM employee WHERE id=7;

SELECT * FROM empLog;
SELECT * FROM employee;

---

– 查询mysql数据库的用户信息(user表)
SELECT * FROM USER;
– password(密码)这个函数会对密码进行加密
SELECT PASSWORD(‘root’);
– 更改root这个用户名的密码‘123456’
UPDATE USER SET PASSWORD=PASSWORD(‘root’) WHERE USER=’root’;

– 创建一个新的用户，并且给新的用户一些特有的权限呢？
GRANT SELECT ON day20.employee TO ‘eric’@’localhost’ IDENTIFIED BY ‘123456’;