时间：2021-07-01 10:21:17 帮助过：3人阅读
1 数据约束
1.1 简介
给表添加数据约束，从而约束用户操作表数据的行为。
1.2 默认值约束
注意： 当前没有插入默认值字段的时候，默认值才会起作用
-- 1.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; -- 需求：当不插入gender的时候，分配一个‘男’的默认值-- 注意： 1）当没有插入gender字段的时候，分配一个默认值
1.3 非空约束
-- 1.2 非空（not null）CREATE TABLE test( NAME VARCHAR(20) NOT NULL, gender VARCHAR(2))-- 需求： name字段一定要有值（不能不插入数据，不能是null），这是给name添加非空约束-- 1)非空约束，不能不插入值INSERT INTO test(gender) VALUES(‘男‘); -- ERROR 1364 (HY000): Field ‘NAME‘ doesn‘t have a default value -- 2)非空约束，不能插入nullINSERT INTO test(NAME,gender) VALUES(NULL,‘男‘); -- ERROR 1048 (23000): Column ‘NAME‘ cannot be null
1.4 唯一约束
CREATE TABLE test( id INT UNIQUE, NAME VARCHAR(20))-- 需求： id的值不能出现重复值。这时就要给id添加一个唯一约束。INSERT INTO test(id,NAME) VALUES(1,‘张三‘);-- 1)不能插入重复的值INSERT INTO test(id,NAME) VALUES(1,‘李四‘); -- Duplicate entry ‘1‘ for key ‘id‘INSERT INTO test(id,NAME) VALUES(2,‘李四‘);-- 2)唯一约束，可以插入多个null。所以唯一约束不能约束nullINSERT INTO test(id,NAME) VALUES(NULL,‘王五‘);INSERT INTO test(id,NAME) VALUES(NULL,‘陈六‘);
1.5 主键约束
-- 1.4 主键约束(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.6 自增长约束（auto_increment）
-- 1.5 自增长约束（auto_increment）-- 创建表给id字段添加主键自增长约束CREATE TABLE test( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20))-- 自增长约束：初始值为0，每次递增1-- 需求：id的值不需要我们开发者管理，交给数据库维护，这时给id添加自增长约束。INSERT INTO test(NAME) VALUES(‘张三‘);INSERT INTO test(NAME) VALUES(‘李四‘);INSERT INTO test(NAME) VALUES(‘王五‘); -- 删除数据-- delete from test where id=2;-- 使用truncate table删除数据的时候，可以把自增长的初始值置为0TRUNCATE TABLE test; SELECT * FROM test;
1.7 外键约束
外键约束：约束两种表的情况
问题：什么情况下会出现两种表？
答案：员工表中出现部门名称的冗余字段！！！！
如何解决部门名称冗余问题?
答案：独立设计一张部门表，把部门名称放到部门表中，这是员工表只需要关联部门的id即可！！
问题：当插入员工表的部门id的时候，可能会出现不存在的部门id！！这是非法数据！！！如何防止非法数据的插入？
答案：这是可以把员工表的部门id设置为外键约束。
当有了外键约束之后，操作数据的顺序如下：
插入数据：先插入主表的数据，再插入副表数据
修改数据：先修改主表数据，再修改副表数据
删除数据：先删除副表数据，再删除主表数据
-- 1.6 外键约束-- 外键作用： 约束两种表的数据-- 问题： 约束哪种情况下的两种表呢？-- 员工表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.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 -- 外键名称 外键字段 参考 )
2 数据库设计
2.1 引入
需求分析：原始需求 -> 业务需求
客户 业务模型：客户（姓名 登记时间邮箱 微信号）
需求设计：
业务模型（客户业务模型）： -》 抽取实体模型： classCustomer{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 软件开发部
数据库存储空间 和数据查询效率
3 多表查询
-- 1.1 交叉连接查询（笛卡尔乘积: 4*3=12，产生笛卡尔积的原因是没有足够的连接条件）-- 需求： 员工及其部门SELECT employee.name,dept.name FROM employee,dept; -- 多表查询的步骤： 1）确定查询哪些表 2）确定查询哪些字段 3）确定连接条件(规则： 表数量-1)-- 内连接效果：只有满足连接条件的数据才会显示出来-- 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; -- 1.3 左外连接查询（其次）-- 需求： 查询部门及其部门的员工（部门全部显示）-- 左外连接查询效果： 左表（部门表）的数据全部显示，右表（员工）的数据当满足连接条件的时候， -- 就显示满足条件的的数据，但是如果不满足连接条件，则显示null-- 预期结果： -- 软件开发部 张三 -- 软件开发部 王五 -- 软件维护部 李四 -- 软件维护部 陈六 -- 秘书部 nullSELECT d.name,e.name FROM dept d LEFT OUTER JOIN employee e ON d.id=e.deptId; -- 1.4 右外连接查询-- 右外连接查询效果： 右表（部门表）的数据全部显示，左表（员工）的数据当满足连接条件的时候， -- 就显示满足条件的数据，但是如果不满足连接条件，则显示nullSELECT d.name,e.name FROM employee e RIGHT OUTER JOIN dept d ON d.id=e.deptId; -- 1.5 自连接查询-- 需求： 查询员工姓名及其上司姓名(没有上司的员工也显示)-- 预期结果： 员工 上司 -- 张三 null -- 李四 张三 -- 王五 李四 -- 陈六 王五SELECT e.name AS ‘员工‘,b.name AS ‘上司‘ 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 存储过程名称 (形式参数列表)BEGIN 多个sql语句END 结束符号 -- 调用存储过程CALL 存储过程名称(实际参数列表); 参数类型：IN： 输入参数，可以携带数据到存储过程中OUT: 输出参数，可以携带数据到存储过程外面。INOUT: 输入输出参数。
-- 3.1 带有输入参数的存储过程-- 需求： 传入员工id查询对应的员工DELIMITER $CREATE PROCEDURE pro_testByIn(IN eid INT) -- 参数类型（IN） 参数名称 数据类型(int)BEGIN SELECT * FROM employee WHERE id=eid;END $ -- 调用CALL pro_testByIn(2); -- 3.2 带有输出参数的存储过程DELIMITER $CREATE PROCEDURE pro_testByOut(OUT n VARCHAR(20))BEGIN -- 修改变量n SET n = ‘输出参数‘;END $ -- 问题： 如何接收存储过程的输出参数？？？-- 定义变量去接收输出参数数据。 -- mysql数据库三种变量： -- 1）全局变量。mysql内置的变量，mysql程序关闭的时候全局变量才会失效！！ -- show variables:产看全局变量 -- character_set_client: mysql接收的客户端的数据编码 -- character_set_results： mysql使用什么编码输出给客户端数据 -- 查看某个全局变量： select @@变量名 -- 修改某个全局变量： set @@变量名=值 -- 2) 会话变量。变量只在某次登录的会话中有效！退出连接，会话变量数据失效！！ -- 查看某个会话变量： select @变量名 -- 修改/定义某个会话变量： set @变量名=值 -- 案例：演示查询和更改会话变量 -- 3) 局部变量：在存储过程中定义的变量。存储过程结束局部变量失效！！ -- 查看某个局部变量： select 变量名 -- 修改某个局部变量： set 变量名=值 -- 定义某个局部变量： declare 变量名 数据类型; -- 定义会话变量去接收输出参数数据 -- set @n=‘eric‘;CALL pro_testByOut(@n);-- 查看会话变量nSELECT @n; -- 3.3 带有输入输出参数的存储过程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; -- 3.4 带有判断条件的存储过程-- 需求： 输入一个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; -- 3.5 带有循环条件的存储过程-- 需求： 输入一个num，计算从1到num的总和。DELIMITER $CREATE PROCEDURE pro_testByWhile(IN num INT,OUT score INT)BEGIN -- int result =0; -- for(int i=1;i<=100;i++){ -- result += i; -- } -- 定义局部变量 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; -- 3.6 携带数据库的数据给输出参数(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 -- 练习存储过程练习在student表的数据基础上，设计一个存储过程： 1）计算出所有学生的英语平均分 2） 如果平均分，大于80分，且小于等于100分，输出‘优秀‘ 如果平均分，大于等于60分，且小于等于80分，输出‘良好‘ 如果平均分，小于60分，输出‘不及格‘USE day14; SELECT * FROM student2; DELIMITER $CREATE PROCEDURE pro_testByAvg(OUT str VARCHAR(20),OUT tavg FLOAT)BEGIN DECLARE savg FLOAT DEFAULT 0.0; SELECT AVG(english) INTO savg FROM student2; IF savg>80 AND savg<=100 THEN SET str = ‘优秀‘; ELSEIF savg>=60 AND savg<=80 THEN SET str = ‘良好‘; ELSE SET str = ‘不及格‘; END IF; SET tavg = savg; END $ -- 删除存储过程DROP PROCEDURE pro_testByAvg; CALL pro_testByAvg(@str,@tavg);SELECT @str;SELECT @tavg;
5 触发器
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 JDBC入门
1.1 回顾
之前操作mysql数据库：
1）使用mysql客户端工具
2）使用客户端连接mysql服务器
3）发送sql语句到mysql服务器，执行
1.2 什么是JDBC？
使用java程序发送sql语句到数据库服务器端执行，这叫用到了JDBC技术！！！！
jdbc是Oracle-Sun公司设计的一套专门用于java程序操作数据库的接口。
1.3 使用jdbc发送sql条件
连接mysql数据库：
数据库主机
端口
数据库用户名
数据库密码
连接的数据库
1.4JDBC的核心API
|-Driver接口：数据库驱动程序的接口，所有具体数据库厂商需要的驱动程序需要实现此接口。
Connectionconnect(String url, Properties info) 用于获取数据库连接
|-Connection接口：与具体的数据库的连接对象。
StatementcreateStatement() 创建一个静态sql语句对象
PreparedStatementprepareStatement(String sql) 创建预编译的sql语句对象
CallableStatementprepareCall(String sql) 创建存储过程的sql语句对象
|-Statement接口：用于执行静态 SQL 语句
intexecuteUpdate(String sql) 执行更新操作的sql语句 （create/alter/drop) DDL语句
(insert/update/delete）DML语句
ResultSetexecuteQuery(String sql) 执行查询操作的sql语句
(select)（DQL查询语句）
|-PreparedStatement接口：用于执行预编译的 SQL 语句（是Statement的子接口）
intexecuteUpdate() 执行更新操作的sql语句
ResultSetexecuteQuery() 执行查询操作的sql语句
|-CallableStatement接口：用于执行 SQL 存储过程的接口（是PreparedStatement的子 接口）
ResultSetexecuteQuery() 执行存储过程的sql语句
|- ResultSet接口：结果集对象。 存储所有数据库查询的结果，用该对象进行数据遍历。
booleannext() ： 把光标移动到下一行。如果下一行有数据，返回true，如果没有下一行数 据，返回false。
getXXX（列索引|列字段名称）： 获取字段的数据
2 Statement对象执行SQL操作
2.1 执行DDL操作
/** * 通过jdbc执行DDL语句 * @author APPle * */publicclass Demo1 { //数据库的连接的URL privatestatic String url = "jdbc:mysql://localhost:3306/day17"; //数据库用户名 privatestatic String user = "root"; //数据库密码 privatestatic String password = "root"; publicstaticvoid main(String[] args){ Connection conn = null; Statement stmt = null; try { //1.驱动驱动程序 Class.forName("com.mysql.jdbc.Driver"); //2.从驱动程序管理类获取连接 conn = DriverManager.getConnection(url, user, password); //3.通过Connection对象获取Statement对象 stmt = conn.createStatement(); //4.准备sql语句 String sql = "CREATE TABLE student(id INT PRIMARY&nbs