数据和信息
Data数据
任何描述事物的文字或符号都可以称为数据
软件开发就是为了收集数据，从中筛选有用的信息
信息就是经过分析筛选后的数据
数据需要保存，保存的介质有内存或硬盘
内存中的数据都是临时的，随着系统的关闭数据都会消失
硬盘中的数据是永久的，就算系统关闭数据依然存在
如果需要大量数据保存，文件系统就不在方便了
使用一个系统化的数据仓库才能高效的管理数据
word等文件保存数据是一种保存到硬盘中的途径
数据库
DataBase称为数据库，简称DB
运行在操作系统上，按照一定的数据结构，保存数据的仓库，是一个电子化的文件柜
数据永久保存在硬盘中
数据库管理系统
DataBase Manager System，简称DBMS
通常所说的数据库，其实是指数据库管理系统，如MySQL SQLServer Oracle等
是一种操作和管理数据库的大型软件，用于建立、使用和维护数据库
总结
数据Data需要永久保存在数据库中
数据库DB是运行在操作系统上的一个软件
数据库管理系统DBMS是管理数据库的一个软件
学习数据库就是学习如何使用DBMS创建、使用数据仓库来管理软件
常见的数据库管理系统
关系型数据库
关系型数据库是主流的数据库类型。
数据是通过行（row）和列（column）的形式保存。
每行数据称为一条记录。
每列称为一个字段。
| 学号（字段） | 姓名（字段） | 专业（字段） |
| 1001 | 小王 | 计算机 |
| 1002 | 小凯 | 计算机 |
字段通常为Java中某个实体类的属性，通过这个类创建的对象，就是一条记录。
如calss Employee,有员工、部门、工资等属性。
对应数据库中有一张Eployee员工表，这张表中有员工编号、姓名、部门等字段。
关系型数据库中，数据表之间进行关联就能快速的查询想要的数据。
优点：
- 易于维护：数据都是以表的结构存储，格式一致
- 使用方便：SQL语句通用，可用于不同的关系型数据库
- 支持复杂查询：可以通过SQL语句在多个表之间查询出复杂数据
缺点：
- 读写能力差，在处理海量数据时，频繁读写效率略低
- 表结构不易改动，灵活性欠佳
- 高并发读写，硬盘I/O决定了读写速度
非关系型数据库
数据通过对象的形式保存，对象可以是一个键值对、文档、图片等。
非关系型数据库优缺点：
保存数据的格式多样
对于海量数据的读写性能高
不支持复杂查询
3.使用
可以之后使用图形化界面方便操作，但最好了解纯命令行的形式使用MySQL,因为最终项目运行在Linux服务器上，无法使用图形界面，在安装目录的bin文件夹中，输入cmd将入控制台
输入mysql -uroot -p后回车，输入安装时的密码
常用指令
show database; 查看所有数据库
use 数据库名； 切换到指定数据库
create database 数据库名; 创建指定数据库
drop database 数据库名; 删除指定数据库表
show tables; 在某个数据库中，查看其中的所有表
数据库管理系统图形化管理工具
如果只是使用控制台操作数据库管理系统很不方便，所以有很多
切换数据库
只需双击对应的数据库
创建数据库
在展开后的数据库中，在表的选项上右键新建表
保存时输入表名
- 每张表需要一个编号"id"列，通常设置为主键，目的是为了区分每条记录，主键列中的数据不能重复，通常还需将主键列设置为自增列
- 由于mysql中大小写不敏感，字段名使用全部小写字母，多个单词用_隔开
- 数据类型和所占长度根据时间情况选择，
- 如果某列数据必须要填写，将"不是null"勾选
- 如果某个字段有默认值，可以在设计表的时候设置，字符串用到引号引起来
- 最好加上注释
SQL
Structrued Query Language 结构化查询言语
用来操作关系型数据库的一门语言，可以用来创建、维护数据和数据
-- mysql中的注释操作数据库
创建数据库
create database 数据库名;切换数据库
use 数据库名;删除数据库
drop database 数据库名：操作数据表
创建数据表
create table 表名(
字段1 数据类型 [字段特征]
字段2 数据类型 [字段特征]
....
字段n 数据类型 [字段特征]
)修改数据表
- 对表重命名
alter table 旧表名 renaem to 新表名;- 添加新字段
alter table 表名 add column 字段名1 数据类型 [字段特征];- 修改字段
alter table 表名 change 旧字段名 新字段名 数据类型 [字段特征]；- 删除字段
alter table 表名 drop 字段名;添加约束
- 添加唯一约束
alter table 表名 add unique(字段名);- 添加主键约束
alter table 表名 add primary key(字段名);
- 添加默认值约束
alter table 表名 alter 字段名 set default '默认值';
- 添加外键约束
alert table 从表表名 add foreign key(从表外键字段) references 主表表名(主表主键字
段)
添加约束的操作通常是对已存在的表进行修改和维护时使用。如果是一张新表，最好在创建表的时候就 设计好约束。
建表同时添加约束
-- 创建游戏数据库gamedb
create database gamedb;
-- 切换到gamedb
use gamedb;
-- 创建游戏人物表hero
create table hero(
-- 编号id主键 非空 自增
id int not null primary key auto_increment comment '人物编号',
-- 姓名 唯一 非空
name varchar(20) unique not null,
-- 性别 默认男 非空
sex char(1) not null default '男',
-- 价格 非空
price int not null ,
-- 发布时间 可以为空
make_time date
)
-- 添加"定位"position字段
alter table hero add column position varchar(20)
-- 创建战斗表battle
create table battle(
-- id，主键字段
id int not null primary key auto_increment,
-- 出战人物编号hero_id,来自于hero表中的id字段 非空
hero_id int not null,
-- 昵称 可以为空
nick_name varchar(20),
-- 分路 不能为空
way varchar(20) not null,
-- 设置外键
foreign key(hero_id) REFERENCES hero (id)
)
删除数据表
drop table 表名
如果要删除有外键关联的表，要先删除从表，再删除主表
操作数据
数据完整性是指数据精确可靠，不能保存无意义或无效的数据。
如不合理的年龄性别、全为空的记录、重复记录等。
为了保证保存在数据库中的数据的完整性，就需要在设计表的时候添加一些约束或字段特征来保证数据的完整性
mysql中常见的数据类型
| 整形 | ||
| tinyint | 对应Java中的short | 短整形 |
| int | 对应Java中的int | 整形 |
| bigint | 对应Java中的long | 长整型 |
| 浮点型 | ||
| float | 对应Java中的float | 单精度浮点型 |
| double | 对应Java中的double | 双精度浮点型 |
| decimal（宽度 精度） | 指定保留的小数位数和整体宽度 | 如decimal（4,2）3.1415926 -->3.14 |
| 字符串 | ||
| char | 定长字符串 | 对应Java中的String，char(10)表示就算实际保存3个字符，也占10个字符 |
| varchar | 可变字符串 | 对应Java中的String，varchar(10)表示实际保存3个字符，占3个字符 |
| text | 文本 |
| 日期 | ||
| date | 日期 | yyyy-MM-dd |
| time | 时间 | HH：mm：ss |
| datetime | 日期时间 | yyyy-MM-dd HH：mm：ss |
| timestamp | 毫秒 | 保存日期的毫秒数 |
约束
| 约束/字段特征 | 关键字 | 概念 |
| 非空约束 | null表示运行为空，not null表示不能为空 | 是否允许该字段为null |
| 主键约束 | primary key | 主键(primary key)。也可以称为主关键字、主码，用来区分表中的每条记录的一个字段。如果有现成的字段可以区分每条记录时，将该字段设置为主键字段；如果没有现成的字段可以区分每条记录时，通常会额外添加一个id字段设置为主键。 |
| 唯一约束 | unique | 保证该字段值不能重复 |
| 默认值约束 | default | 添加一条记录时，可以不给有默认值约束的字段赋值，让其使用默认值 |
| 外键约束 | foreign key<br> | 在主从关系的表中，给从表中的某个字段添加外键约束后，该字段的值只 能来自于主表中的某个主键字段 |
删除数据表
drop table 表名;day02
操作数据
数据的操作，是指数据的增加create 修改opdate 查询read和删除delete,简称CURD,
数据添加insert
数据添加时都是整行添加。不能只给一个字段去添加数据。
如果只给某个字段去添加数据，实际是修改。
给所有字段赋值
insert into 表名 vlues('值1','值2'....)- 表名后无需添加字段名，添加时保证值的顺序和数量与表中字段的顺序和数量一致
- 遇到自增字段，不能省略不写，可以使用0、null或dfeault让其填充自增值
- 遇到有默认值的字段，不能省略不写，使用default让其填充默认值
- 遇到允许为空的字段，不能省略不写，使用null让其设置为空。
给指定字段赋值
insert into 表名(字段1，字段2....) values('值1'，'值2'...)至少需要将索引非空字段写出来
可以不用写出自增字段，有默认值的字段和允许为空的字段
表名后的字段顺序要和值的顺序一致
批量添加
可以用一个insert into语句添加多条记录
insert into 表名[(字段1,字段2...)] values
('值1','值2'...),
('值1','值2'...),
...
('值1','值2'...)修改数据（updata）
修改单个字段的所有值
update 表名 set 字段 = '值';修改多个字段的所有值
update 表名 set 字段 = '值1', 字段='值2'根据条件修改（where子句）
update 表名 set 字段 = '值1', 字段='值2'...where条件指定值
update 表名 set 字段 = '值1', 字段='值2'...where 字段='值';指定范围
使用>、<、>=、<=表示范围，使用and、or、||将多个条件关联
使用''字段between A and B''表示字段在[A,B]范围内
使用!=或<>表示不等于
指定集合
某个字段的值在某个集合中时，字段in('值1'，'值2'...)
某个字段的值在某个集合中时，字段in('值1'，'值2'...)
空值匹配
某个字段为空：字段 is null
某个字段不为空：字段 is not null
模糊查询
-- 字段 like '%宁%'
-- 带有'宁'字
-- 字段 like '%张%'
-- 带有'张'字
数据删除dalete
删除数据是删除一条或多条记录
删除所有
delete from 表名;
--或
truncate table 表名；
- delete会保留自增列删除前的值，删除后在添加时 自动从删除前的值开始自增
- truncate会重置自增列的值，删除所有数据时效率更高
- 如果要删除主从关系且设置了外键的表中的数据，从表中如果有数据，不能直接删除主表中相关联的数据，先删除从表数据在删除主表数据
条件删除
dalete from 表名 where 条件;
数据查询select
select * from 表名
查询指定字段
select 字段名1，字段名2，字段名3....from 表名
实际开发中很少使用*查询， 因为*查询效率远远低于查询指定字段
字段重命名
select 字段1 as '自定义名称',字段1 '自定义名称'... from 表名;查询指定条数
-- 查询N条记录
select * from 表名 limit N;
-- 查询从N索引开始的M条记录
select * from 表名 limit N,M;
-- 每页显示size条记录，第page页
select * from 表名 limit (page-1)*size,size;
条件查询
select * from 表名 where 条件;查询时的条件同修改时的条件语句
排序
select * from 表名 where 条件 order by 排序字段 排序规则,排序字段 排序规则...
排序规则不写或写asc表示升序
排序规则写desc表示降序排序时如果有条件，排序写在条件语句之后
多字段排序时，在order by 之后写多个字段及排序规则，用逗号隔开，按字段顺序排序
统计函数（聚合函数）
select 统计函数(字段) from 表名；
| 函数名 | 作用 |
| sum（字段） | 求和 |
| avg（字段） | 平均值 |
| count（字段） | 计数 |
| max（字段） | 最大值 |
| main（字段） | 最小值 |
数学相关函数
| 函数名 | 作用 |
| abs（值或字段） | 绝对值 |
| pow（值或字段） | 次幂 |
| qwrt（值或字段） | 开平方 |
| round（值或字段） | 四舍五入取整 |
| deil（值或字段） | 向上取整 |
| floor（值或字段） | 向下取整 |
字符串相关函数
| 函数名 | 作用 |
| length（字符串或字段） | 得到长度 |
| trim(字符串或字段)/ltrim(字符串或字段)/rtrim(字符串或 字段) | 去首尾/首/尾空格 |
| substr(字符串或字段,start) | 从start开始截取字符串到末尾 |
| substr(字符串或字段,start,len) | 从start开始截取len个字符串 |
| left(字符串或字段,len)/right(字符串或字段,len) | 从首/尾开始截取len个字符 |
| lcase(字符串或字段)/ucase(字符串或字段) | 转换为小写/大写 |
| reverse(字符串或字段) | 反转字符串 |
| concat(字符串1,字符串2...) | 将所有字符串拼接 |
| replace(字符串或字段,旧字符串,新字符串) | 将字符串中的旧字符串替换为新字符 串 |
时间相关函数
| 函数名 | 作用 |
| now() | 得到当前日期时间 |
| current_date()/curdate() | 得到当前日期 |
| current_time()/出入time（） | 得到当前日期 |
| year(日期)/month(日期)/day(日期) | 得到年月日部分 |
| datediff(时间1，时间2) | 得到时间1和时间2相隔的天数 |
| timediff(时间1，时间2) | 得到时间1与时间2相隔的时分秒 |
| timestampdiff(时间单位，时间1，时间2) |
分组
select 分组字段,统计函数 from 表名 group by 分组字段 having 统计函数条件
按照指定的字段进行分组，会将该字段值相同的记录归纳在同一组中
分组通常配合统计函数使用
如果统计函数作为条件，不能使用where 而需使用having子句，将整个having子句放在分组之后
group_concat()函数
将分组后的数据拼接成字符串
group_concat(字段或字符串,字段或字符串...)
-- 根据图书类型分组，查看每组下的图书名和作者
select GROUP_CONCAT(book_name,'--',book_author) from book_info group by type_id实际业务对应SQL操作
登录
登录通常是使用用户名和密码查询用户表，如果没有查询到数据，即为null，说明用户不存在或输入错误，如果能查到数据，返回查询到的内容
select * from 用户表 where 用户名 = ？ and 密码 = ？
注册
注册通常是使用用户名和密码添加一条记录
注册时一般还会验证要注册的用户是否存在
insert 用户表 values(注册信息1，注册信息2，...)
充值
充值或者是消费都是将数据进行更新
update 用户表 set 余额 = 余额+/-值 where 主键 = 值
create table userinfo（
userid int not null primary key auto_increment,
username varchar(20) not null
password varchar(20) not null
balance double not null
）
-- 注册
-- 检测要注册的用户是否存在
select * from
insert into userinfo values(null,'admin','123123',0);
-- 登录
select * from userinfo where username='naem',password='123123';
-- 充值
-- 获取注册时自动生成的id
update userinfo set balance = balance + 100 where userid = 1
删除
逻辑删除
不删除数据，只是不显示数据
| 用户名 | 电话 | 地址 | 是否删除 0：已删除 1：未删除 |
| 小张 | 122252 | xxx | 0 |
| 小王 | 524553 | xxx | 0 |
| 小李 | 254884 | xxx | 1 |
查询所有未删除的信息
物理删除
真实删除
delete from 用户表 where 用户编号=小王编号
day03
连接查询
交叉连接、笛卡尔积
将两张表中的数据两辆组合，得到的结果就是交叉连接的结果，也称笛卡尔积
集合A：{a，b}
集合B：{1,2,3}
集合A x 集合B = {a1,a2,a3,b1,b2,b3}
以上三种方式都能将两章表中的数据互相组合，其中有很多无效数据
内连接
交叉连接的基础上，筛选出关联的数据
select * from 表1，表2 where 表1.字段 = 表2.字段
select * from 表1 inner join 表2 on 表1.字段 = 表2.字段
-- 如查询所有图书详情和类型名
select * from 图书详情表 t1，图书类型表 t2 where t1.类型编号 = t2.类型编号
select * from 图书详情表 t1 inner join 图书类型表 t2 on t1.类型编号 = t2.类型编号;
通常是通过主表的主键字段关联从表的外键字段
如果两张表中关联的字段名一致，一定要通过"表名.字段名"进行区分，通常还会给表重命名
如果使用inner join，带条件时需要夸人where子句，如果使用逗号隔开多个表，带条件需要使用and拼接条件
内连接只会显示两张表中有关联的数据
左连接
-- 保证左表数据显示完整的情况下，关联右表中的数据，没有关联的用null表示
select * from 表1 left join 表2 on 表1.字段 = 表2.字段
-- 最终显示表1中的所有数据，关联表2中的数据右连接
-- 保证右表数据显示完整的情况下，关联左表中的数据，没有关联的用null表示
select * from 表1 right join 表2 on 表1.字段 = 表2.字段
-- 最终显示表2中的所有数据，关联表1中的数据嵌套查询
将查询出的结果继续使用在另一个查询语句中
-- 查询价格最低的图书信息
select * from book_info where book_price = (select min(book_price) from book_info)
-- 按类型分组，查询每组中图书价格大于平均价格的图书
select * from book_info bi inner join
(select type_id,avg(book_price) as avg from book_info group by type_id)temp
on bi.type_id = temp.type_id where book_price > avgJar
以.jar为后缀的文件，称为Java的归档文件，保存的是Java的字节码.calss文件
在Java程序中导入某个.jar文件后，就能使用其中的.class文件
在Java项目中使用.jar文件
1.创建一个Java项目，在其中最好新建一个文件夹，将.jar文件保存在里面
2.在.jar文件上右键，点击“add as library”
JDBC
Java DataBase Connectivity java数据库连接
用Java程序连接不同的数据库
实际是Java中定义的相关数据库连接时所需的接口的集合，不同的数据库对其进行实现
核心接口
Connection：用于设置连接的数据库的地址、账号、密码
PreparedStatement：用于预处理、执行SQL语句
**ResultSet：**用于保存查询后的数据
以上接口都来自于java.sql包中
单表查询
import java.sql.*;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Objects;
public class Main {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//连接mysql数据库实现单表查询
//1.加载连接mysql的驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.连接指定数据库,提供要连接的数据库的URL。用户名、密码
String url="jdbc:mysql://localhost:3306/gamedb?servertimezone=Asia/Shanghai";
Connection conn= DriverManager.getConnection(url,"root","root");
//3.构造查询的sql语句
String sql="select * from hero";
//4.处理SQL语句
PreparedStatement pst=conn.prepareStatement(sql);
//5.处理执行SQL语句后的结果
ResultSet rs = pst.executeQuery();
//6.循环遍历读取查询后的数据
//定义集合保存查询到的数据对象
ArrayList<Hero> list=new ArrayList<>();
while (rs.next()) {
//根据字段名获取
int id = rs.getInt("id");
//根据字段顺序获取
String name=rs.getString(2);
String sex = rs.getString(3);
int price=rs.getInt(4);
String makeTime = rs.getString(5);
String position = rs.getString(6);
//创建Hero对象
Hero hero = new Hero(id, name, sex, price, makeTime, position);
list.add(hero);
//System.out.println((id + "\t" + name + "\t" + sex + "\t" + price + "\t" + makeTime + "\t" + sex + "\t" + position));
}
//7.关闭连接
rs.close();
pst.close();
conn.close();
for (Hero hero : list) {
System.out.println(hero);
}
}
}SQL注入
在构造sql语句时，如果使用字符串拼接的方式构造可变的sql语句，可能会造成sql注入的风险，导致执行不是预期的sql语句
-- 如删除的sql String sql="delete from 表 where 主键="+参数;
-- 实际传递 '' or 1=1
delete from 表 where 主键= '' or 1=1
-- 导致条件永远成立，最终删除了所有数据
-- 查询的sql String sql="select * from 表 where name="+name +"and pwd="+pwd;
-- 实际第一个参数传递 '' or 1=1 --
select * from 表 where name = '' or 1=1 -- pwd=
-- 导致第二个参数被注释，第一个参数永远成立在构造sql语句时，使用?占位，通过preparestatement方法防止sql注入的风险
简化JDBC
package com.hqyj.test.util;
import java.sql.*;
/*
定义数据库工具类 简化jdbc操作
可以简化的操作
1.加载驱动 √
2.连接数据库 √
3.sql语句需要定义为参数
4.给sql中的？赋值
5.增删改executeUpdate() 查询executeQuery()
6.关闭 √
*/
public class DBUtil {
//用静态代码块用于加载驱动
static {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
System.out.println("驱动不存在"+e);
}
}
//定义静态常量保存连接数据库的字符串
private final static String URL="jdbc:mysql://localhost:3306/gamedb?serverTimezone=Asia/Shanghai";
private final static String USERNAME="root";
private final static String PASSWORD="root";
public static Connection getConn(){
Connection connection=null;
try {
connection= DriverManager.getConnection(URL,USERNAME,PASSWORD);
} catch (SQLException e) {
System.out.println("连接数据库信息异常"+e);
}
return connection;
}
/*
定义静态方法释放资源
*/
public static void release(Connection conn, PreparedStatement pst, ResultSet rs){
//使用那个接口就关闭那个接口
try {
if (rs!=null) {
rs.close();
}
if (pst!=null) {
pst.close();
}
if (conn!=null) {
conn.close();
}
}catch (SQLException e){
System.out.println("关闭异常"+e);
}
}
}
day04
数据设计
实体关系模型
实体Entity，一张表就是一个实体
关系Relationship，实体与实体之间的关系
实体关系模型也称为ER模型
用图形表示实体关系时，这个图叫做ER图
用矩形表示实体，用椭圆表示实体的属性，用菱形表示实体中间的关系，用直线连接各个图形
实体之间的关系
一对一
实体a与实体b之间唯一对应
ER图
在数据库中创建表的过程
可以使用一张表实现，但是后期维护拓展时较为不便
国家名称和领导人姓名需要添加唯一约束
| 国家名 | 人口数量 | 国土面积 | 领导人编号 | 领导人姓名（唯一约束） | 性别 | 生日 |
| 中国 | 15亿 | 950万 | 1 | xxx | xxx | xxx |
| 美国 | xxx | xxx | 2 | xxx | xxx | xxx |
最后使用两张表实现
国家表
create table country(
country_id int not null primary key auto_increment
country_name varchar(20) not null
country_population bigint not null
couontry_area int not null
)领导人表
cerate table leader(
leader_id int not null primary key auto_increment,
leader_name varchar(20) not null,
leader_sex varchar(2) not null,
leader_birthday date
)创建两张表示独立的，并不能体现一对一的关系，可以通过以下方式体现一对一
方式一（建议使用）
在国家表中添加领导人标号字段，将其设置为唯一，或在领导人表中添加国家编号将其设置为唯一
cerate table leader(
leader_id int not null primary key auto_increment,
leader_name varchar(20) not null,
leader_sex varchar(2) not null,
leader_birthday date
country_id int no null unique
foreigh key (country_id) references coumtry(country_id)
)方式二：
创建第三张关系表
create table relation(
relation_no int not null primary key auto_increment,
country_id int not null unique,
)一对多/多对一
一对多：一个实体a对应多个实体b，一个实体b不能对应多个实体a
如一个人可以由多辆车，一辆车不能对应多个人
多对一：多个实体b对应一个实体a，多个实体a不能对应一个实体b
如多个学员对应一个训练他们的教官，多个教官不能对应一个学员
在数据库中创建表的过程
创建主表（一）
create table country(
coach_id int not null primary key auto_increment,
coach_name varchar(20) not null,
coach_leavl varchar(20) not null
)创建从表（多）
create table student(
stu_id int not null primary key auto_increment,
stu_name varchar(20) not null,
stu_phone varchar(20) ,
coach_id int not null,
foreign key (coach_id) references coach(coach_id)
)多对多
一个实体a可以对应多个实体b，一个实体b也可以对应多个实体a
如：一个学生可以学习多门课程 一门课程也可以被多个学生学习
ER图
在数据库中创建表的过程
创建学生表
create table student(
stu_id int not null primary key auto_increment,
stu_name varchar(20) not null,
stu_phone varchar(20)
)
创建课程表
create table course(
course_id int not null primary key auto_increment,
course_name varchar(20) not null,
course_score int not null
)体现多对多关系的表：成绩表
create table score(
score_id not null primary key auto_increment,
stu_id int not null,
course_id int not null,
-- 可以选择添加外键约束
foreign key (stu_id) references student(stu_id),
foreign key (course_id) references course(course_id)
)总结
一对一：创建各自的实体表，在任意一张表中添加另一张表的主键字段，将其设置为唯一
一对多/多对一：先创建主表（一），在创建从表（多），在从表中添加主表的主键字段，外键可选
多对多：创建各自的实体表，在创建第三张：“”关系表“”，在关系表中添加两个实体表中的主键字段，外键可选
数据库设计规范
数据库设计的规范简称为范式
第一范式1NF、第二范式2NF、第三范式3NF、BC范式BCNF、第四范式3NF和第五范式5NF共六种
这六种范式的级别越高(第一范式到第五范式)，表示数据库设计的结构越规范。每一个高等级的范式都包 含了低等级的范式。
通常设计数据库时，只需满足3NF即可。
如有该原始表。 黄色背景称为联合主键。由学号和科目一起区分每一条记录。暂时这张表不满足任何范式。
当前表如果要做增删改查的操作，会涉及到的问题
- 如果要加入一个新的系别，就要添加学生、系主任
- 如果要删除"刘定宇"，他所在的系别也会被删除
- 如要将"王海"的系别改为会计，相应的系主任也要修改
- 当前表中有大量重复冗余数据
第一范式1NF
数据表中的每一项都是不可分割的原子项。
关系型数据库起码要满足1NF，才能创建表。
上表中的联系方式列，可以再分为手机和QQ两列，不满足原子性，不满足1NF。根据1NF，修改后
第二范式2NF
在满足1NF的基础上，消除部分依赖。
对于联合主键而言，每一个非主属性字段都需要完全依赖于主属性，而不是依赖其中的一部分。
在上图中，无法用学号当主键，需要将学号和科目组合为联合主键，通过联合主键才能得到分数。
除了学号和科目外，其他字段都是非主键字段，分数完全依赖于联合主键，其他字段不满足，所以进行拆分
d第三范式3NF
在满足2NF的基础上，消除传递依赖。
在上图中，系主任是通过学号-->系别-->系主任获取，系主任传递依赖于学号，消除这个传递依赖
最终根据实体关系模型进行优化，体现对应关系
名词解释
主码/主键/主属性
用于唯一区分每条记录的一个字段，称为主键字段，如学号、身份证号等。
通常选择能唯一确定且几乎不会更改的字段作为主键。如果没有，自定义一个id列作为主键。
联合主键
如果一个字段不能唯一区分每条记录，而是需要多个字段一起才能区分，这些字段组成了联合主键
完全依赖
如果能通过A和B得到C，A和B都不能单独得到C时，称为C完全依赖于A和B。
如(学号+科目)联合主键-->分数，分数完全依赖于联合主键。 其他字段完全依赖于学号。
部分依赖
通过A和B可以得到C，单独通过A或B也能得到C，称为C部分依赖于A和B。
如(学号+科目)联合主键-->姓名，其实只通过学号也能得到姓名，称为姓名部分依赖于联合主键。
传递依赖
如果通过A得到B，通过B得到C，称为C传递依赖于A。
如学号-->系-->系主任，其实只通过系也能得到系主任，称为系主任传递依赖于学号。
day05
视图view
视图可以当做数据库中的一个临时表 保存一些较为复杂的查询后的结果
之后可以直接通过该视图查询数据，不需要再次编写复杂的SQL语句
视图同时可以隐藏一些查询细节，定制查询数据
创建视图
create view 视图名 as
查询的SQL语句;
使用视图
select * from 视图名
修改视图中的数据，会直接修改原始表中的数据
删除视图
drop view 视图名;
事务transaction
事务是由一组sql语句组成的执行单元，这些SQL之间一般都互相依赖
图a给b转账
1.update 表 set money = money-1000 where id=1
2.update 表 set money = money+1000 where id=2
以上两句sql组成了一个转账的事务。一个事务要么全部执行，要么全部不执行。
事务的特性ACID
Atomicity原子性
事务是最小的执行单元，要么全部执行，要么全部不执行。
Consistency一致性
事务执行前后，必须让所有数据保持一致状态。(总体数据守恒)
Isolation隔离性
多个事务并发执行时应该互相隔离，互不影响。
Durability持久性
事务一旦提交，对数据的改变是永久的。
事务的使用
提交commit
回滚rollback
mysql中的事务默认是自动提交的。
如果关闭了事务自动提交，在执行某个事务途中，如果出错，可以使用rollback进行回滚，让数据回到 事务执行之前的状态。
- 查看事务自动提交开启状态：select @@autocommit 1表示开启了自动提交 0表示关闭了自动提交
- 设置事务不自动提交：set @@autocommit=0
如果不出错，通过commit提交事务后，无法进行回滚
手动提交/回滚事务
1.关闭事务自动提交:set @@autocommit=0
2.开启事务:start transaction
3.事务要执行的sql
4.没有提交之前，可以使用rollback回滚
5.如果要提交，使用commit，一旦提交成功，无法rollback
-- 查看事务自动提交状态
select @@autocommit
-- 关闭事务自动提交
set @@autocommit = 0
-- 开启事务
start TRANSACTION;
-- 模拟id=1转账给id=2 2000
-- 查询
select * from user where id=1 and money>=2000;
-- 扣钱
update user set money=money-2000 where id=1;
-- 模拟转账过程出错
sdfsdf
-- 转钱
update user set money=money+2000 where id=2;
-- 遇到问题,在没有commit之前，可以回滚
rollback
-- 确认无误后，使用commit提交,一旦提交后无法回滚
commit事务并发可能出现的问题
在同一时刻同时执行多个事务时，称为事务并发。
事务并发时会有可能出现以下问题
| 问题 | 描述 |
| 脏读 | 事务A读取到事务B未提交的数据 |
| 不可重复读 | 事务A中如果要读取两次数据，在这期间，事务B对数据进行了修改并提交，并导致事务A读取两次的情况不一致 |
| 幻读 | 事务A读取id为1-10之间的数据，假如只有id为2和5的数据，在读取期间，事务B添加了 一条id为3的数据，导致事务A多读取到了事务B中的数据。 |
事务隔离级别
| 隔离级别 | 能否出现脏读 | 能否出现不可重复读 | 能否出现幻读 |
| Read Uncommitted 未提交读RU | 会 | 会 | 会 |
| Read Committed 已提交读(Oracle默认) | 不会 | 会 | 会 |
| Repeatable Read 可重复读RR(MySQL默认) | 不会 | 不会 | 会 |
| Serializable 可序列化 | 不会 | 不会 | 不会 |
查看事务隔离级别
select @@transaction_isolation
设置事务隔离级别
set [session|global] transaction isolation level [Read Uncommitted|Read Committed|Repeatable Read|Serializable]
触发器trigg
创建触发器
-- 创建触发器
-- create trigger 触发器名
-- 除法时机（after/before） 除法操作（insert/update/delete）on 表 for each row
-- begin
-- 满足除法条件后执行的内容
-- end
create trigger mytrigger
after insert on user for each row
begin
insert into log values(null,'执行了添加操作',now());
end使用触发器
触发器创建成功后，无需手动调用，在执行忙住触发器的操作后，会自动执行触发器
i-- 在执行这句话后，就会自动在log表中添加一条记录
nsert into userinfo values（null,'admin','123123'）;
删除触发器
drop trigger 触发器名；
存储过程procedure
类似于Java中的方法，定义一组用于完成特定功能的sql语句
定义存储过程后没通过调用存储过程名，就可以执行定义的内容，存储过程还可以有参数
调用存储过程
-- 调用五参数的存储过程
call 存储过程名();
-- 调用输入型的存储过程
call 存储过程名('实参')；删除存储过程
drop PROCEDURE if EXISTS 存储过程名
定义存储过程
create procedure 存储过程名([参数类型 参数名 参数数据类型]) -- 参数类型分为输入型参数和输
出层参数
begin
sql语句;
end定义无参数的存储过程
create procedure 存储过程名()
begin
sql语句;
end -- 定义无参数的存储过程，查询每本图书的名称、作者、类型
CREATE PROCEDURE myproc1 ()
BEGIN
SELECT
*
FROM
book_info bi,
book_type bt
WHERE
bi.type_id = bt.type_id;
END-- 调用存储过程
call myproc1();定义输入型参数的存储过程
create procedure 存储过程名(in 参数名 数据类型)
begin
sql语句;
end -- 根据图书类型查询该类型下的所有图书
CREATE PROCEDURE myproc2 (IN lx VARCHAR ( 20 ))
BEGIN
SELECT
*
FROM
book_info bi
INNER JOIN book_type bt ON bi.type_id = bt.type_id
WHERE
type_name = lx ;
END
-- 调用输入型参数的存储过程
call myproc2('漫画');定义输出型参数的存储过程
输出型参数类似于有返回值的方法
create procedure 存储过程名(out 参数名 数据类型)
begin
sql语句;
-- 通常需要将查询后的结果保存到输出参数中
-- 通过 " select 值或字段 into 参数 " 赋值;
end
-- 根据作者查询其图书数量
CREATE PROCEDURE myproc3 (OUT book_count INT,IN zuozhe VARCHAR ( 20 ))
BEGIN-- 将查询到的结果使用Into赋值给输出参数book_count中
SELECT
count( book_id ) INTO book_count
FROM
book_info
WHERE
book_author = zuozhe;
END
-- 输出型参数，使用@变量 当做变量接收
call myproc3(@x,'古龙')
-- select @变量 查询变量中的数据
select @x定义输入输出型参数的存储过程
create procedure 存储过程名(inout 参数名 数据类型)
begin
sql语句;
-- 通常需要将查询后的结果保存到输出参数中
-- 通过 " select 值或字段 into 参数 " 赋值;
end
-- 根据书名的关键字查询书名、作者、类型
CREATE PROCEDURE myproc4 (INOUT keyword VARCHAR ( 20 ))
BEGIN
SELECT
book_name ,
book_author,
type_name
FROM
book_info bi,
book_type bt
WHERE
bi.type_id = bt.type_id
AND book_name LIKE concat( '%', keyword, '%' );
END
-- 创建变量
set @keyword='龙';
call myproc4(@keyword);
select @keywordmysql编程
在定义的存储过程中，可以定义变量、使用流程控制语句
定义变量
create procedure 存储过程名()
begin-- declare 变量名 数据类型;
declare num int;
declare name varchar(20);
end给变量赋值
create procedure 存储过程名()
begin
-- declare 变量名 数据类型;
declare num int;
declare name varchar(20);
-- 给num和name赋值
-- select 字段/值 into 变量 [from 表];
select 123 into num;
select 'hello' into name;
select book_author into name from book_info ;
end读取变量的值
create procedure 存储过程名()
begin
-- declare 变量名 数据类型;
declare num int;
declare name varchar(20);
-- 给num和name赋值
-- select 字段/值 into 变量 [from 表];
select 123 into num;
select 'hello' into name;
select book_author into name from book_info ;
-- select 变量名
select num;
select name;
end
-- 创建存储过程，查询所有图书库存总数，保存到变量中
CREATE PROCEDURE myproc5 ()
BEGIN
-- 定义变量，保存查询到的数据
DECLARE num INT;
-- 执行查询，将结果赋值给变量
SELECT
sum( book_num ) INTO num
FROM
book_info;
-- 输出变量的值
SELECT num;
END
-- 调用存储过程
call myproc5(条件语句
单分子if语句
if 条件
then
满足条件后执行的sql;
end if;
-- 根据作者查询图书库存，如果不足100，输出'库存不足100'
create PROCEDURE myproc6(in zz varchar(20))
begin
-- 定义变量保存该作者的图书库存
declare num int;
-- 根据参数查询该作者的图书库存
select sum(book_num) into num from book_info where book_author=zz;
-- 判断num
if num<100
then
select '不足100';
end if;
end
call myproc6('小刘')双分支if语句
if 条件
then
满足条件后执行的sql;
else
不满足条件时执行的sql；
end if;
-- 根据图书类型查询图书数量，如果不足6，输出"不足6种"，如果足够，输出详情
create PROCEDURE myproc7(in lx varchar(20))
begin
-- 定义变量保存图书数量
declare book_count int;
-- 根据条件查询
select count(book_id) into book_count
from book_info bi,book_type bt
where bi.type_id =bt.type_id and type_name = lx;
-- 双分支判断
if book_count<6
then
select '不足6种';
else
select *
from book_info bi,book_type bt
where bi.type_id =bt.type_id and type_name = lx;
end if;
end
call myproc7('漫画')
call myproc7('小说')case语句
case 变量
when 值 then
变量值为该值时执行的sql语句;
when 值 then
变量值为该值时执行的sql语句;
else
没有任何值满足时执行的sql语句;
end case;
-- 输入类型编号，输出对应类型名称，没有输出不存在
create PROCEDURE myproc8(in id int)
begin
declare type varchar(20);
select type_name into type from book_type where type_id=id;
case type
when '小说' then
select concat(id,'--','小说');
when '漫画' then
select concat(id,'--','漫画');
when '杂志' then
select concat(id,'--','杂志');
else
select concat(id,'不存在');
end case;
end
call myproc8(2）循环语句
while循环
while 循环条件 do
循环体;
end while;
-- 添加50本图书
create PROCEDURE myproc9()
begin
-- 定义循环变量
declare n int;
-- 循环变量初始1
select 1 into n;
-- 循环
while n<=50 do
-- 循环添加，同时更新循环变量的值
insert into book_info values(null,4,concat('测试',n),'测试作
者',50,null,null,100);
set n = n + 1;
end while;
end
call myproc9()repeat循环
repeat
循环体;
until 条件 end repeat;
-- 添加50本图书
create PROCEDURE myproc10()
begin
DECLARE n int ;
select 51 into n;
repeat
insert into book_info values(null,4,concat('测试',n),'测试作
者',50,null,null,100);
set n = n + 1;
until n=100 end repeat;
end
call myproc10loop循环
循环名:loop
循环体;
if 循环条件 then
leave 循环名;
end if;
end loop 循环名;
-- 添加50本图书
create PROCEDURE myproc11()
begin
DECLARE n int ;
select 100 into n;
myloop:loop
insert into book_info values(null,4,concat('测试',n),'测试作
者',50,null,null,100);
set n = n + 1;
if n=150 then
leave myloop;
end if;
end loop myloop;
end
call myproc11()
18万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
