JavaSE学习笔记 2023-12-28 --MySQL

MySQL

1.数据库介绍

数据库:数据仓库
DataBase:简称DB,用于长期存储有结构的,大量的,共享的数据

长期的:持久存储,永久存储
有结构:
	有类型,有内部的数据类型
	有关系,数据与数据之前是有关联的
大量的:
	大多数据库都是以文件系统存在的,可以将数据存储在磁盘中
共享的:
	多个应用之间可以共享一个或多个数据库资源

2.数据库的分类

关系型数据库
	关系型数据库采用关系表的形式进行数据的存储
	可以通过表与表之间的关系来维护数据的关系
		例如:用户 --- 购买 --- 商品
	用户信息 --- 用户类 --- 用户表
	常用的库:MySQL,Oracle,SQLServer...
非关系型数据库
	采用键值对的数据模型来存储数据的,只完成数据的记录,不会有任何的关联关系
	NoSQL:Not Only SQL
	常见的库:redis,MongoDB...

3.数据库中的常用术语

数据库(DataBase):存储数据的集合
数据(Data):被描述的事物符号记录
数据库管理系统(DBMS,DataBase Management System) 用于管理数据库软件系统
数据库管理员(DBA,DataBase Administrator) 负责创建数据库,使用以及维护数据库的专业人员
数据库系统(DBS,DataBase System) 数据库管理员,数据库管理系统以及数据库组成的整个单元结构

4.数据库安装和配置

MySQL环境准备
	下载,安装,配置,卸载
版本:
	企业中主流的版本5.x和8.x
	本次使用mysql8
安装:
	参考攻略
mysql服务的启动和停止:
	此电脑--->右键--->管理--->服务和应用程序--->服务--->mysql相关
	服务停止的原因:1.计算机改名 2.手动停止 3.外部的一些游戏加速器会停止该服务
软件卸载:
	官方:关闭服务--->控制面板点击程序卸载--->自带的卸载程序--->删除目录:mysql安装目录--->删除注册表:win+r输入regedit
	geek:直接卸载所有内容
管理工具:
	可视化工具,数据库的一个图形管理工具
		mysql自带clc(免费的)
		Navicat for MySql(需要破解的) mysql8配合navicat12+ mysql5配合navicat11-
		SQLyog(需要破解的)
		DBeaver(免费的)

5.MySQL的逻辑结构

MySQL在数据库存储时是按照一定的结构进行存储的,MySQL的常见结构是表
MySQL本质是一个服务器(数据库服务器)
navicat是一个客户端(进行数据库的访问操作)
数据库软件都可以管理很多的数据库,每个库中可以管理若干张表,表中用于存储数据

记录/元组:表中的一条(行)数据就是记录/元组 在JDBC中一条数据库记录就是一个java对象

学号	姓名	性别	年龄	身高	体重	出生时日期	入学日期	毕业日期	休学日期
1	张三	男	19	190	200	20000101	20070901	20130601

6.SQL语句

Structured Query Language
	结构化查询语句,关系型数据库的主要使用语言,用于数据的增删改查,数据库管理对象等操作.
	
SQL作为关系型数据库的通用语言,对不同的数据库的支持是不一样的,有7成左右的相似度
	例如:oracle中分页使用rownum,mysql中使用limit,SQLServer中使用top
SQL的扩展名为.sql

SQL语句的分类:
	根据不同的功能,可以将SQL划分为多类
		DML:数据操作语言,用于对数据的增删改查操作
			DQL:数据查询语言,用于对数据的查询
		DDL:数据定义语言,用于完成对数据库对象(表,视图,索引等)的操作(创建,删除,修改)
		TCL:事务控制语言
		DCL:数据控制语言,权限操作

7.DML

7.1DQL

数据查询语言
SQL可以在mysql中自带的工具中或者第三方工具(navicat)中使用
SQL语法:
	不区分大小写
	每天SQL以分号结尾,但不是强制的
	SQL中关键字之间使用空格进行分割
	SQL之间不限制换行,有空格的位置就可以使用换行
	SQL的注释:
		单行注释: --注释内容
		单行注释: # 注释内容
		多行注释: /*注释内容*/

7.1.1基本查询

语法
select 列名1,列名2,列名3... | *
from 表名;
* 表示的是所有字段,在测试环境使用,但数据库调优时会进行调整
	*是不会写的,使用具体的字段名称

-- 基本查询
select empno,ename,job
from emp;

select *
from emp;

select *
from dept;

7.1.2过滤查询

语法
select 列名1,列名2,列名3... | *
from 表名
where 条件;

进行数据的筛选,等到我们需要的数据,过滤掉不符合的数据

-- 过滤查询
-- 查询工资高于5000的人
select *
from emp
where sal > 5000;

-- 查询工资低于5000的人
select *
from emp
where sal <= 5000;

7.1.2.1条件运算符

=	等于
!=
<>	不等于
>
>=
<
<=

-- 条件运算符
-- 查询工资不为5000的人
select *
from emp
where sal <> 5000;
select *
from emp
where sal != 5000;

-- 查询名字为九元 单引号表示字符串
select *
from emp
where ename = '九元';

7.1.2.2逻辑运算符

not and or

-- 逻辑运算符
select *
from emp
where sal >= 5000
and ename = '九元';

select *
from emp
where sal >= 5000
or ename = '九元';

select *
from emp
where deptno is not null;

select *
from emp
where deptno is null;

7.1.2.3特殊的比较运算符

is null 判断数据是否为空;为空显示,不为空不显示
in(值的列表) 返回符合值列表中的数据
like 模糊查询*****
between ... and ... 在范围之内,包含范围的

上述的四个可以和not配合使用

-- 特殊的比较运算符
select *
from emp
where deptno is null;

select *
from emp
where deptno is not null;

-- 查询在10和20号部门的员工 属于or的关系
select *
from emp
where deptno in (10,20);

select *
from emp
where deptno = 10 or deptno = 20;

select *
from emp
where deptno not in (10,20);

select *
from emp
where deptno != 10 and deptno != 20;

-- 模糊查询
-- _一个字符的占位 %>=0符号的占位
-- 查询名字中有6的人
select *
from emp 
where ename like '%6%';

-- 表示6开始
select *
from emp 
where ename like '6%';

-- 表示6结尾
select *
from emp 
where ename like '%6';

-- 第三个位置为6的
select *
from emp 
where ename like '__6%';

-- 倒数第三个字符
select *
from emp 
where ename like '%6__';

-- 不像
select *
from emp 
where ename not like '%6%';

-- 查询工资在3000-5000范围内的
select *
from emp
where sal between 3000 and 5000;

-- 等价的
select *
from emp
where sal >= 3000 and sal <= 5000;

-- 可以执行的,但是可读性差 建议将字段写在前面,具体的数值在后面
select *
from emp
where 3000 <= sal and 5000 >= sal;

-- 查询工资不在3000-5000范围内的(不带边界)
select *
from emp
where sal not between 3000 and 5000;

select *
from emp
where sal < 3000 or sal > 5000;

7.1.3计算列

可以对查询的记录进行一定的计算之后进行显示
可以使用 + - * / 四个运算符

-- 计算列
select ename,sal + 500,comm - 100
from emp;

7.1.4列别名

使用 as 和 空格为某个列去添加别名

-- 列别名
select ename,sal + 500 as 工资,comm - 100 奖金
from emp;

7.1.5 distinct去除重复行

-- 去除重复行
select distinct deptno
from emp;

7.1.6 order by排序

必须是查询的最后一个字句
asc表示升序,默认不写就是
desc表示降序

-- 排序
select *
from emp
order by sal;

select *
from emp
order by sal asc;

select *
from emp
order by sal desc;

select *
from emp
order by hiredate;

7.1.7组合函数

组函数,聚合函数
类似于java中的方法

-- 组函数
-- 计数
select count(empno) from emp;
-- count(1)
select count(1) from emp;
select count(*) from emp;

-- 最值
select min(sal) from emp;
select max(sal) from emp;

-- 总和
select sum(sal) from emp;

-- 平均值
select avg(sal) from emp;

7.1.8日期函数

可以通过字符串进行赋值,格式为"yyyy-MM-dd hh:mm:ss"
表示当前的日期函数:now() sysdate()

-- 日期函数
select *
from emp
where hiredate < '2023-01-01';

select *
from emp
where hiredate < '2023-01-01 08:00:00';

select *
from emp
where hiredate between '2023-01-01' and now();

select *
from emp
where hiredate between '2023-01-01' and sysdate();

7.1.9字符函数

处理字符使用

-- 字符函数
select concat(ename,job)
from emp;

select concat(ename,'-',job)
from emp;

select upper(ename) from emp;

select lower(ename) from emp;
-- 数据库中的下标从1开始的
select ename,substring(ename,2,1) from emp;

7.1.10分组查询

group by
配合组函数进行使用,分组后的过滤使用having
语法:
    select 字段
    from 表名
    where 条件
    group by 分组字段
    having 分组后的过滤
    order by 排序

-- 查询每个部门的人数
select deptno,count(*)
from emp
group by deptno;

-- 查询部门人数大于3的部门
select deptno,count(*)
from emp
group by deptno
having count(*) > 3;

7.1.11分页查询

一次性将所有数据都展示给用户,体验太差了,可以使用分页查询
limit关键字
limit param1,param2
parma1:从指定位置开始,不包含这个位置
param2:查几个

-- 查询1~3条数据
select *
from emp
limit 0,3;

-- 查询4~6条数据
select *
from emp
limit 3,3;

7.2连接查询

我们无法从一张表获取到所有的数据,需要将多张表关联在一起
n张表需要至少n-1个条件连接在一起

7.2.1笛卡尔积

笛卡尔积/集，叉积/集
多张表连接在一起,没有使用过滤条件,会将多张表的数据乘在一起
语法
	select *
	from a表,b表;

-- 笛卡尔积
select *
from emp,dept;

select *
from emp,dept,users;

-- 消除笛卡尔积,使用多张表的共有字段
-- 默认忽略空值的
select *
from emp,dept
where emp.deptno = dept.deptno;

-- 查询1号员工的个人信息和部门信息
select *
from emp,dept
where emp.deptno = dept.deptno
and empno = 1;

7.2.2内连接

查询出只符合条件的数据
消除笛卡尔积就是内连接

-- 查询1号员工的个人信息和部门信息
select *
from emp,dept
where emp.deptno = dept.deptno
and empno = 1;

-- 查询小白666 的所有信息
select *
from emp,dept
where emp.deptno = dept.deptno
and ename = '小白666';

7.2.3表别名

简化表名的操作

-- 表别名
-- 空格
select *
from emp e,dept d
where e.deptno = d.deptno
and ename = '小白666';

-- as (在oracle中不支持)
select *
from emp as e,dept as d
where e.deptno = d.deptno
and ename = '小白666';

7.2.4SQL语法分类

92语法
	上面写的内连接是92语法,在mysql中不支持其他的92语法
99语法
	使用join将多张表连接在一起

7.2.4.1 99语法-内连接

-- 99 内连接
select *
from emp e inner join dept d
on e.deptno = d.deptno;

select *
from emp e inner join dept d
on e.deptno = d.deptno
where empno = 1;

7.2.4.2 99语法-外连接

可以查询到不符合条件的数据
外连接分为左外连和右外连
outer left join ... on 条件
outer right join ... on 条件
inner/outer关键字可以省略

-- 99 外连接
-- 左外连 左侧表的数据是完全的
select *
from emp e left outer join dept d
on e.deptno = d.deptno;
-- 左右外连是可以互换的
select *
from dept d right outer join emp e
on e.deptno = d.deptno;

-- 右外连 右侧表的数据是完全的
select *
from emp e right outer join dept d
on e.deptno = d.deptno;

-- 满外连 左右两表的数据都可以查询不符合条件的(mysql中是不支持的)
select *
from emp e full outer join dept d
on e.deptno = d.deptno;

7.2.5子查询

又称查询嵌套,内部查询的数据可以被外部查询所使用

-- 子查询
-- 查询工资比九元高的
-- 1.查询九元的工资
select sal from emp where ename = '九元';

-- 2.查询比上面工资高的
select * from emp where sal > 50000;

select * from emp where sal > (select sal from emp where ename = '九元');

7.3DML

数据操作语言
主要用于数据的删除,修改,插入

7.3.1添加数据

语法
insert into 表名(列名1,列名2...)
values (值1,值2....)

列与值一一对应

-- 添加操作
-- 注意不要违反主键约束(主键的值不能重复)
-- 向dept表添加数据
insert into dept(deptno,dname,city)
values (50,'小卖部','昌图');

-- 若添加时的数据是全部数据,则列列表可以省略
insert into dept
values (60,'外联部','庄河');

-- 只添加一部分值 不是所有那么就需要列列表和值列表一一对应
insert into dept(deptno,dname)
values (70,'技术部');

-- 不是所有列都添加时,是不能省略的
-- insert into dept
-- values (80,'技术部');

-- 批量添加(mysql支持,oracle中不支持)
insert into dept(deptno,dname,city)
values (80,'小卖2部','昌图'),(90,'小卖3部','昌图'),(100,'小卖4部','昌图');

7.3.2删除数据

语法
delete from 表名 where 条件;

oracle中from可以省略,但是mysql中不行
where 条件可以省略,省略了就是删除全部数据,在oracle中可以回滚的,在mysql中默认不可以回滚

-- 删除操作
delete from users;-- 别写

delete from dept where deptno = 80;

逻辑删除:
	本质修改,改变某个字段
	Goods(id,name,type,status[状态:0启动,1禁用])
物理删除:
	本质删除,从磁盘下将数据删除了

7.3.3修改数据

语法
	update 表名 set 列 = 值,列 = 值... where 条件;
	
where 可以省略,但是修改全部

-- 修改操作
update emp set ename = '张三';-- 别写

update emp set ename = '李四',sal = 1000000 where empno = 11;

8.DDL(表操作)

数据库对象操作

8.1创建数据库

数据库的创建类似于java中二维数组,外部的一维表名,内部就是字段(字段名,类型,默认值,约束等)

-- 建表
-- 表名/字段名:数字 字母 下划线 连接符 井字符 可以使用 长度最长30个字符
create table student(
	stu_no char(8), -- 字符串
	stu_name varchar(20), -- 字符串
	stu_gender char(2),
	stu_age int,
	stu_qq varchar(12)
)

insert into student 
values (1,'王成輝','未知',22,'1233456678'),(2,'王成輝2号','未知',22,'1233456678');

8.2修改表

8.2.1添加列

alter table 表名 add 列名 类型;

-- 添加列
alter table student add birthday date;

8.2.2修改列(列名和类型)

alter table 表名 change 旧列名 新列名 类型;

-- 修改列 名字和类型
alter table student change stu_name name varchar(30);

8.2.3修改列(类型)

alter table 表名 modify 列名 类型;

-- 修改列 类型
alter table student modify stu_no char(30);

8.2.4删除列

alter table 表名 drop 列名;

-- 删除列
alter table student drop stu_qq;

8.2.5表改名

alter table 表 rename to 新名;

-- 表改名
alter table student rename to stu;

8.3删除表

drop table 表名;
	若没有对应的表,则报错
drop table if exists 表名;
	若没有对应的表,则报错,也不删除;存在则删除

-- 删除表
drop table student;-- 不存在报错

drop table if exists student;-- 只删除存在的

drop table if exists stu;

9.MySQL数据类型

存储数据时,支持的类型

9.1数值型

int/integer 整数 4byte
double 双精度浮点型 8byte
其他:
tinyint 特小的整数型 1byte
smallint 小整数型 2byte
bigint 大整数型 8byte
float 单精度浮点型 4byte

9.2字符型

char 
	定长字符串,长度0~255 若存储的长度不足时,使用'\u0000'进行补位
	用于存储长度的固定的内容,例如:手机号,身份证号,性别...
varchar
	变长字符串,长度0~65535
	
其他:
blob 
	存储二进制字符串
longblob
	存储二进制字符串
longtext
	存储文本内容的,例如:小说,博客,base64的图片

9.3日期型

date
	日期型,存储的是年月日
datetime
	日期型,存储的是年月日 时分秒
	
其他
time
	时间,存储时分秒
year
	年,年份

10.约束

创建表时,为指定的字段添加限定条件
来保证数据的准确，完整和正确
约束的分类:
	非空约束 not null 被限定的字段不能为null
	唯一约束 unique 被限定的字段不能重复
	主键约束 primary key 非空且唯一，每张表中的唯一表示
	外键约束 foreign key 必须依赖主键才能使用(先有主键才能有外键)
	检查约束 check	对某个字段进行限制(mysql5中不支持 mysql8中支持)

10.1非空约束

-- 非空约束
create table goods (
	goods_id char(4),
	goods_name varchar(20) not null,
	goods_pirce double default 0 -- 默认值,若不添加对应的字段,则使用默认值
)

insert into goods 
values (1,'黑人牙膏',2);

-- name是不可以使用null添加的
insert into goods (goods_id) value (2);

-- 默认值
insert into goods (goods_id,goods_name) value (2,"白人药膏");

10.2唯一约束

-- 唯一约束
create table student (
	stu_no char(4),
	stu_name varchar(20) unique
);

insert into student (stu_no,stu_name) values (1,'张春啸');
insert into student (stu_no,stu_name) values (1,'张春啸');-- 重复名字不能添加

10.3主键约束

10.3.1主键的基本使用

一张表的唯一表示,主键一张表中只能有一个
唯一且非空的

唯一和非空在一张表中可以有多个

主键一般是自增的数值,所有的表中都会有xxx_id、xxx_no的字段

-- 主键
create table teacher (
	tea_id int primary key,
	tea_name char(3)
);

insert into teacher values (1,'殷亮');
insert into teacher values (1,'殷亮');-- 不能重复
insert into teacher values (null,'殷亮');-- 不能为空

10.3.2主键自增长

主键希望有一个自动增长的字段为其服务
mysql中有自增长功能
oracle中需要是sequence对象进行服务

定义int类型的主键,设置自动增长,auto_increment

-- 自增长的主键
create table pet (
	pet_id int primary key auto_increment,
	pet_name varchar(20)
)

insert into pet (pet_name) values ('咪咪');

10.3.3联合主键

一张表中的多个字段共同承担主键的任务,是一个主键由多个值组成
定义联合主键比较少

-- 联合主键
create table kecheng (
	stu_no int,
	cls_no int,
	score int,
	primary key (stu_no,cls_no)
)

insert into kecheng values (1,1,80);
-- 不可添加
insert into kecheng values (1,1,80);-- 两个字段不能同时重复
insert into kecheng values (1,null,80);-- 字段不能为空
insert into kecheng values (null,1,80);
insert into kecheng values (null,null,80);
-- 可添加
insert into kecheng values (1,2,80);
insert into kecheng values (2,1,80);

学生表

学号(主键)	姓名	性别	年龄
1	张三	男	19
2	李四	男	20

课程表

课程编号(主键)	课程名	学分
1	JavaSE	4
2	MySQL	3
3	WEB	3

选课(关联表:可以使用联合主键,不用也不会影响,关联表往往都没有主键)

学号	课程号	成绩	补考成绩
1	1	88	90
1	2	77
2	3	66

10.4外键约束

10.4.1表之间的关系

四种:
	一对一	人和身份证
	一对多 
	多对一
		 班级和学生
	多对多
		 讲师和学生
在sql中使用主外键的形式来体现这种关联关系
	主键所在表,称之为主表
	外键所在表,称之为从表
	
外键必须依赖主键,有主键了才能有外键
外键的值都是从主键中选的
外键可以重复,可以为空,必须依赖主键

10.4.2外键约束

语法
	constraint 外键的名字 foreign key(当前表的外键字段) references 主表名(主表的主键字段)
	名字:fk_主表名_从表名

-- 外联约束
-- 学生和班级为例
-- 主表
create table classes (
	c_id int primary key auto_increment,
	c_name varchar(20) not null,
	c_info varchar(200)
);

-- 从表
create table students (
	s_id int primary key auto_increment,
	s_name varchar(20) not null,
	s_gender char(1) default '0',-- 0是性别男 1是性别女
	s_age int not null,
	c_id int,
	constraint fk_stu_cls foreign key(c_id) references classes(c_id)
);

-- 添加数据
insert into students (s_name,s_age,c_id) values ('zs',19,null);-- 外键的值是从主键中选出来的
insert into students (s_name,s_age,c_id) values ('ls',19,null);

-- 想为外键添加值,先在主表添加数据
insert into classes (c_name,c_info) values ('4教室','班级可以容纳47人');
insert into classes (c_name,c_info) values ('3教室','班级可以容纳44人');

-- 添加学生
insert into students (s_name,s_age,c_id) values ('ww',19,1);

10.4.3外键的级联操作

级联删除:主键数据删除,对应的外键数据一起删除
级联置空:主键数据删除,对应者外键字段赋值为null
级联修改:主键的id修改,外键对应的字段会同步修改

10.4.3.1级联删除

-- 级联删除
-- 主表
create table classes (
	c_id int primary key auto_increment,
	c_name varchar(20) not null,
	c_info varchar(200)
);

-- 从表
create table students (
	s_id int primary key auto_increment,
	s_name varchar(20) not null,
	s_gender char(1) default '0',
	s_age int not null,
	c_id int,
	constraint fk_stu_cls foreign key(c_id) references classes(c_id) on delete cascade
);

-- 主表数据
insert into classes (c_name,c_info) values ('4教室','班级可以容纳47人');
insert into classes (c_name,c_info) values ('3教室','班级可以容纳44人');

-- 从表数据
insert into students (s_name,s_age,c_id) values ('zs',19,1);
insert into students (s_name,s_age,c_id) values ('ls',19,2);

-- 删除
delete from classes where c_id = 1;

10.4.3.2级联置空

-- 级联置空(项目中常用的)
-- 主表
create table classes (
	c_id int primary key auto_increment,
	c_name varchar(20) not null,
	c_info varchar(200)
);

-- 从表
create table students (
	s_id int primary key auto_increment,
	s_name varchar(20) not null,
	s_gender char(1) default '0',
	s_age int not null,
	c_id int,
	constraint fk_stu_cls foreign key(c_id) references classes(c_id) on delete set null
);

-- 主表数据
insert into classes (c_name,c_info) values ('4教室','班级可以容纳47人');
insert into classes (c_name,c_info) values ('3教室','班级可以容纳44人');

-- 从表数据
insert into students (s_name,s_age,c_id) values ('zs',19,1);
insert into students (s_name,s_age,c_id) values ('ls',19,2);

-- 删除
delete from classes where c_id = 1;

10.4.3.3级联更新

-- 级联更新
-- 主表
create table classes (
	c_id int primary key auto_increment,
	c_name varchar(20) not null,
	c_info varchar(200)
);

-- 从表
create table students (
	s_id int primary key auto_increment,
	s_name varchar(20) not null,
	s_gender char(1) default '0',
	s_age int not null,
	c_id int,
	constraint fk_stu_cls foreign key(c_id) references classes(c_id) on update cascade
);

-- 主表数据
insert into classes (c_name,c_info) values ('4教室','班级可以容纳47人');
insert into classes (c_name,c_info) values ('3教室','班级可以容纳44人');

-- 从表数据
insert into students (s_name,s_age,c_id) values ('zs',19,1);
insert into students (s_name,s_age,c_id) values ('ls',19,2);

-- 更新主表
update classes set c_id = 3 where c_name = '4教室';

10.5检查约束

mysql5.x没有该约束
mysql8.x中添加该约束
oracle中任何版本都可以使用

-- 检查约束 (mysql中length是判断字节数)
create table person (
	p_id int primary key,
	p_name varchar(20) check(length(p_name) >= 3)
);


insert into person values (1,'王成輝6');

insert into person values (2,'王成');

insert into person values (3,'ab');

11.DDL(库操作)

11.1 MySQL Command Line Cilent

mysql自带的管理工具,纯命令行的
打开:
	开始菜单--->MySQL--->CLC工具
连接MySQL:
	在CLC中输入密码即可,若闪退说明密码输出错误
关闭CLC:
	输入exit指令即可

11.2DDL(库操作)

11.2.1查询库

show databases;查询本地所有数据库的列表
show create database mysql;显示指定名称的数据库的创建SQL指令

11.2.2创建库

create database 库名;创建指定数据库名字的库
create database if not exists 库名;若库名不存在时则创建
create database 库名 character set 字符集;创建库时采用何种编码集

11.2.3删除库

drop database 库名;
drop database if exists 库名;

11.2.4使用库

use 库名;

12.视图

命名化查询,由数据库中的一张或者多张表组成,构成的虚拟表
视图是基于表存在的,简化查询,安全性高,可以只公开表的部分数据

12.1视图的创建

语法
create view 视图名
as
子查询;

视图只能通过子查询创建
视图的分类:简单视图,复杂视图

12.1.1简单视图

由一张表组成

-- 简单视图
-- 通过emp表创建myemp视图
create view myemp
as
select empno,ename,sal from emp;

-- 查询视图,将表中的其他数据就隐藏起来了
select * from myemp;

12.1.2简单视图和表中的数据关系

简单视图和表中的数据添加是会同步的

-- 向emp表中添加数据,myemp视图的数据是同步的
insert into emp values (23,'王成輝','总经理助理','2022-12-12',1200,500,40);

-- 向myemp视图中添加数据,同步到emp表中
insert into myemp values (24,'李昊阳睡着了',1200);

简单视图和表中的数据删除是会同步的

-- 删除
-- 删除表中数据,视图同步
delete from emp where empno = 24;
-- 删除视图中数据,表中数据同步
delete from myemp where empno = 23;

简单视图和表中的数据修改是会同步的

-- 修改
-- 改表,视图同步
update emp set ename = '王成輝' where empno = 22;
-- 改视图,表同步
update myemp set ename = '李昊阳' where empno = 21;

12.1.3复杂视图

-- 复杂视图
create view mydept_emp
as
select empno,ename,d.deptno,dname
from emp e,dept d
where e.deptno = d.deptno;

12.1.4复杂视图和表中的数据关系

添加
	向表中添加数据,可以同步到视图中
	向视图中添加数据
		只能向对应的某一张表的视图去添加数据
		添加的数据不能是连接字段(外键)
		可以添加到复杂视图中的数据是单表

-- 添加
-- 添加数据到表中,视图同步
insert into emp values (23,'王成輝','总经理助理','2022-12-12',1200,500,40);
-- 添加数据到视图中,
-- 向视图中添加数据,不能每个字段都添加
insert into mydept_emp values (24,'毕明辉',40,'行政部');
-- 只添加到emp表中对应视图部分,可以添加
insert into mydept_emp (empno,ename) values (24,'毕明辉');
-- 只添加到dept表中对应视图部分,可以添加非共有字段(deptno是两张表中都有字段)
insert into mydept_emp (dname) values ('毕明辉部');

删除
	删除表中数据,复杂视图同步
	无法删除复杂视图中的数据

-- 删除
-- 删除表,视图同步
delete from emp where empno = 23;
-- 删除视图,是无法完成
delete from mydept_emp where empno = 21;
delete from mydept_emp where ename = '李昊阳';
delete from mydept_emp where deptno = '101';
delete from mydept_emp where dname = '毕明辉部';

修改
	修改表,视图中数据同步
	修改视图,表中数据同步

-- 修改
-- 修改表,视图同步
update emp set ename = '王成輝' where empno = 18;
-- 修改视图,表中同步
update mydept_emp set ename = '王成輝66' where empno = 18;
-- 修改视图,通过dname,修改ename字段 可以改
update mydept_emp set ename = '王成輝66' where dname = '总部';
-- 通过empno,修改depname
update mydept_emp set dname = '王成輝66部' where empno = 18;

13.索引

提供查询效率,增强用户的体验

13.1索引的分类

主键索引:被primary key修饰,就自动的添加了主键索引,每张表中只有一个主键索引
唯一索引:添加唯一键时就添加唯一索引
	主键索引和唯一索引都自动的添加的

13.2索引的优缺点

优点:
	提高查询效率,减少磁盘IO的过程
缺点:
	占用磁盘资源,做DML时慢

14.数据库事务

事务就同时完成一组DML操作,或者一个DDL操作
事务就是一个功能,也是业务中的某个功能,按照指定的步骤去完成DML操作

14.1事务的特性

ACID
原子性:一个事务中多个DML操作,要么同时执行成功,要么同时执行失败
一致性:事务执行前后,数据库中的数据要保持一致,完整的不能被破坏的
隔离性:多个事务之间执行操作是互不干扰的
持久性:事务完成之后,数据库中的数据是永久保存的

14.2MySQL中的事务管理

MySQL中的事务默认是自动提交的
	只要执行了一个DML语句,那么自动的进行数据同步
事务管理
	1.开启事务管理,关闭自动提交
	2.在执行DML操作前,执行strat transcation(事务管理开启)
	3.以此的执行DML操作
	4.若执行成功,选择使用commit(提交)事务语句
	5.若执行失败,选择使用rollback(回滚)事务语句
		commit提交,将数据保存
		rollback回滚,数据恢复原样
			commit和rollback只能需要一个

-- 使用默认的事务,自动提交的,每条DML都需要使用事务语句进行提交或者回滚
-- 开启MySQL的事务管理,关闭自动提交,每次都需要手动的开启
start TRANSACTION;
-- 转账功能
update t_user set u_balance = u_balance - 200 where u_id = 1;

update t_user set u_balance = u_balance + 200 where u_id = 2;

-- 手动提交
commit;

-- 手动回滚
rollback;

-- navicat一个窗口就是一个新的事务开始
-- 每个事务之间是相互独立的,默认是看不到别人事务的操作(隔离性)
select * from t_user;

14.3事务的隔离级别

在数据库中是允许多个事务并行的操作,多个事务之间是互不干扰的,相互独立的;若多个事务之间没有隔离操作,可能会导致多个事务之间同时操作同一条数据,可能会破坏数据的一致性
读未提交:
	read uncommitted,两个事务同时执行,T2可以读取到T1未提交的数据,可能造成脏读。脏读:一个事务读取到另一个事务未提交的数据。
读已提交:
	read committed,T2只能读取到T1以提交的数据,避免了脏读,但可能出现虚读。虚读(重复读取数据):在一个事务中的两次查询结果不一致。
可重复读:(mysql中事务隔离级别的默认值)
	repeatable read:T2执行查询时,无论T1进行了任何操作,都不会影响到T2,避免了虚读。可能会出现幻读:T1在做操作的同时T2也在做操作,T1和T2是互不干扰的,但是T1执行完成之后,T2也执行完成了，T1看到了T2数据。
串行化:
	serializable,同时只能允许一个事务对一张表进行操作(单线程的),效率非常低的。可以避免脏读,幻读,虚读。

隔离级别	脏读	虚读	幻读
read uncommitted	√	√	√
read committed	×	√	√
repeatable read	×	×	√
serializable	×	×	×

14.4MySQL中设置事务的隔离级别

MySQL默认的隔离级别为可重复读

-- 查看事务的隔离级别
select @@transaction_isolation; -- 默认为可重复读

-- 设置隔离级别
set session transaction isolation level serializable;

15.数据库设计

MySQL数据库作为数据存储的介质,需要为应用程序提供数据存储功能,那么就要设计出合理的数据和数据表

15.1数据库设计流程

1.根据应用系统的功能,分析数据实体(数据对象)
	学生管理系统:学生,课程,成绩...
	外卖管理系统:商家,客户,骑手...
2.提取实体的数据项(实体属性)
	学生:学号,姓名,性别,爱好,生日...
3.根据数据库设计的三大范式进行数据表的设计
	数据设计有自己的规则,遵循规则会使得我们的数据库更加的合理,使用起来更方便
	若不满足三大范式,会导致数据冗余,维护困难
4.绘制ER图
	展现出实体与实体之间的关联关系,直观的展现出每个实体之间的联系
5.进行数据库建模
	三线图进行数据库设计
	...
6.创建数据库
	编写sql指令完成库和表的创建
7.CRUD的SQL功能测试

15.2数据库设计的三大范式

在数据设计时不止三大范式,但是三大范式是我们必须遵守的

第一范式:要求数据表中的字段不可分割的
下面的联系方式可以被分割

ID	姓名	性别	联系方式(手机号,QQ,微信…)
1	张三	男

联系方式字段可以被分割为若干字段,不合理,至少可以被分为3个字段
遵循第一范式进行设计,如下

ID	姓名	性别	手机号	QQ	微信	…
1	zs	男	134xxxxxxxxx	12xxxxxxx	xxxxxx

第二范式:在满足第一范式的基础上,不存在非关键字段对关机字段段的部分依赖
	下面例子中有部分的依赖
		学号和课程号,是这张表的联合主键,我们就认定学号和课程号是关键字段,除了关键字段以外都是非关键字段
	姓名和性别只依赖于学号,课程名只依赖于课程号,就存在非关键字段对关键字段的部分依赖

学号	课程号	姓名	性别	课程名称	成绩
1001	1	张三	男	java	88
1002	1	李四	男	mysql	76

遵循第二范式,设计上述表结构如下:
	非关键字段只依赖于关键字段

学号	姓名	性别
1	zs	男

课程编号	课程名
1	java

成绩编号(可以没有的,只是关联表)	学号	课程号	成绩
	1	1	88

第三范式:在满足第二范式的基础上,不存在非关键字段之间的传递依赖
	如下就不符合
		姓名,性别,年龄,院系编号是依赖学号;但是存在院系名称和院系描述依赖于院系编号

学号	姓名	性别	年龄	院系编号	院系名称	院系描述	…

遵循第三范式设计如下:

学号	姓名	性别	年龄	院系编号
1	zs	男	19	1
2	ls	男	20	1

院系编号	院系名称	院系描述
1	计算机科学与计算	挺好

15.3ER图

数据库建模
可以直观的体现出实体与实体之间的联系
图形:
	矩形:实体
	菱形:关系
	直线:连接
	椭圆:属性

15.4三线表

每个实体都会对应着一个三线表