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输入regeditgeek:直接卸载所有内容
管理工具:可视化工具,数据库的一个图形管理工具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的扩展名为.sqlSQL语句的分类:根据不同的功能,可以将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三线表

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