mysql学习笔记
数据：对客观事务进行记录并可以鉴别的符号，可以是数字、文字、图像、声音等；
在数据库数据表示记录；比如，在学生管理数据库中，记录包含学号、性别、年龄等。
信息：有价值的数据
数据库：指长期存储在计算机类内的、有组织的、可共享的数据集合。通俗的讲就是
存储数据的仓库，本质就是一个文件系统。
数据库实际上就是一个文件集合，按照特定的格式把数据存储起来。
数据库设计的三大范式
1NF：字段不可分割
2NF：有主键，非主键字段依赖于主键
3NF：非主键字段不能循环依赖
第一范式：要求表中的字段是不可分割的原子数据项，下表家庭信息可以在分割不满足1NF
下表的产品和订单号(主键)没有依赖关系，不符合2NF
在满足第一和第二范式的前提下，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖），第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
一句话总结就是，表的每一个字段都应该直接与主键相关，间接相关是不行的
很明显，后面班主任相关的字段好像跟主键学号不是直接相关的，而像是间接相关的，所以这也是不符合第三范式的，应该通过外键，将班主任与学生关联起来，两张表相关联。
MySQL 是一个关系型数据库(以关系模型来组织数据的数据库，数据以二维表(行和列)形式存储)，使用 SQL 语言进行增删改查操作，目前属于 Oracle 旗下的产品。MySQL 数据库开源免费，能够跨平台，支持分布式，性能也不错，可以和 PHP、Java 等 Web 开发语言完美配合，非常适合中小型企业作为 Web 数据库（网站数据库）。
安装配置数据库
步骤 1)：将 MySQL 免安装压缩文件解压到一个文件夹
如果mysql路径下(E:\mysql\mysql-5.7.18-winx64)有Data文件夹(文件夹中有内容)，会导致初始化失败，删除后重新初始化即可。
如出现错误信息
failed to set datadir to E:\mysql\mysql-5.7.18-winx64\Data\，需要手动创建data文件夹
步骤 2)：配置环境变量,在系统变量 path 后面追加 G:\MySQL\mysql-5.7.31-winx64\bin
步骤 3)：在mysql文件创建配置文件，命名为 my.ini，编辑 my.ini 文件的内容
步骤 4)：用管理员权限在mysql bin目录下安装 MySQL 服务，执行命令为 mysqld–install
步骤 6)：启动 MySQL 服务，并使用用户 root 登录，同时设置登录密码
输入密码时直接回车
设置登录密码
> use mysql;
> update user set authentication_string=password('root') where user='root' and Host='localhost';
> flush privileges; (刷新权限)
mysql忘记密码，重置密码
主要是在my.ini文件中，配置skip-grant-tables,略过验证，然后再更新里面的密码设置。具体步骤：
1、先暂停mysql服务，修改my.ini配置文件，添加skip-grant-tables,
2、重启mysql服务，
3、登录mysql，并设定新的密码，
4、删除my.ini配置文件中的skip-grant-tables，
5、重启mysql服务并登录
net stop mysql
重启mysql服务: net start mysql
再次输入密码时直接回车即可：
最后将my.ini中skip-grant-tables注释
1 mysql数据库操作
1.1 创建数据库
create database [if not exists] database_name # if not exists 可以省略
如果不存在就创建数据库
数据库的字符集及校验规则
character set #指定数据库采用的字符集，如果不指定字符集默认utf8
collate #指定数据库字符集的校对规则(常用utf8_bin[区分大小写]，utf8_general_ci[不区分大小写])默认是utf8_bin
---------------------------------
# 创建一个使用utf8字符集并带校对规则的数据库
create database if not exists db_1 character set utf8 collate utf8_general_ci
1.2 使用/切换数据库
use db_name
1.3 查看数据库
# 1查看所有数据库
show databases
# 2查看是否有指定数据库
show databases like '%db_name%'
1.4 删除数据库
drop database [if exists] db_name
1.5 数据库的备份与恢复
假如需要将某个数据库移动到另一个DBMS上，或者这个数据库可能比较重要，里面有大量的表包含非常重要敏感的数据等等都需要备份数据库保存到文件系统上去，如果该数据库被攻击还可以用备份文件恢复数据。
备份数据库：
mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > 文件名.sql
恢复数据库：
mysql> source 文件路径\文件名.sql #要进入mysql命令行中执行
eg:
进入文件里面是一些sql操作命令
# 只备份指定表的数据
mysqldump -u root -p 数据库 表1 表2 > 文件名.sql
1.6 查看当前所用数据库
select database()
2 mysql数据类型(列类型)
数值
mysql中的整数默认是有符号整数, 比如tinyint (范围 -128~127) ，添加无符号整数需要加上 unsigned关键字(范围是0~255)
mysql中整数还有bit(m)类型(范围在你给定的位数n 0~2^n-1) m在1-64，按照二进制来显示; 如果一个值只有0、1可以考虑使用bit(1)，可以节省空间。
decimal(m,d) 表示任意精度的数据，可以存放很大的数，m表示位数(整数部分长度和小数部分长度总长，不含小数点),d表示小数点后面长度
字符串
char(size) 固定长度字符串，最大255字符；varchar(size)可变长度字符串 0~65535 utf8编码中一个字符占3个字节，此时varchar最大65535/3=21844个字符。char(4) 是固定4个字符长度，如果插入的是'aa'也会分配4个字符的空间，容易造成空间浪费；varchar(4)是变长，最大是4个字符空间，实际数据大小是根据插入数据来分配，varchar本身需要1~3个字节来记录存放内容长度。 如果数据是定长(md5加密，邮编，电话号码)，推荐使用char,如果数据不是定长(留言，文章 )，使用varchar；查询速度char>varchar。
当前是utf8不能存65535 个字符
int(n),char(n),varchar(n): int(n)中n表示显示数字的长度(显示多少个数字)，而不是数字长度；char(n)中n表示字符数(最大255),不是字节数，不管是中文还是字母都放n个；varchar(n)中n表示字符数，以定义好的编码来存放数据。(gbk:1个字符占2个字节，utf8：1个字符占3个字节)
时间日期
时间戳(timestamp)：是一个(能够表示一份数据在一个特定时间点已经存在的完整可验证的)时间凭证;它的提出主要是为用户提供一份电子证据， 以证明用户的某些数据的产生时间。就是一个时间记录，对数据产生的时间进行一个唯一认证，不可更改。计算方式:自1970年元旦开始到数据产生的时间经过的总毫秒数。
boolean类型
MySQL保存Boolean值时,用1代表TRUE,0代表FALSE，boolean类型在MySQL里的类型为tinyint(1)。
1.创建表
create table test(
id int PRIMARY key,
status boolean
)
这样是可以创建成功。查看建表后的语句会发现，mysql把它替换成tinyint(1)。
mysql>show create table test;
CREATE TABLE `test` (
`id` int NOT NULL,
`status` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
在pojo里直接定义该属性为布尔值即可：private Boolean status
3 表操作
3.1 创建表
create table t_name(
field1 datatype,
field2 datatype,
field3 datatype
)character set=字符集 collate=校验规则 engine=引擎
# 如果没有characterset默认是所在数据库字符集，collate也是。
eg:
3.2 修改表
# 添加列
alter table t_name add(field datatype,...);
# 修改列
alter table t_name modify 字段名 类型 约束 注释; 不可以重名字段，只能修改字段类型和约束
alter table t_name change 旧字段名 新字段名 类型 约束 注释 可以重命名字段及其类型约束等
# 删除列
alter table t_name drop (column...);
# 查看表结构
desc t_name; / describe t_name; /show create table t_name;
# 修改表名
rename table t_name to new_t_name;
# 修改字符集
alter table t_name character set=字符集
# 修改表的列名
alter table t_name change c_name new_c_name datatype
3.3 删除表
drop table t_name
3.4 查询表
查询指定数据库中的所有表：
select table_name from information_schema.tables where table_schema='数据库名';
------
查询指定数据库中是否有某个表：
select table_name from information_schema.tables where table_schema='数据库名'
and table_name='表名';
4 数据库表crud
4.1 insert插入数据
insert into t_name [column...] values (value...)
如果是添加所有列，则可以简写： insert into goods values(1,'华为mate10'，'2000'),当不给某个字段值时，如果该字段有默认值就会添加，否则就会报错
4.2 update修改数据
update t_name set c_name=value [c_name=value] [where expr]
注：如果不加where条件，则就是为所有数据的薪水修改为4000；插入字段和数据 位置和数据类型要对应
如果要修改多个字段可以通过 set field1=v1, field2=v2...
4.3 delete删除数据
delete from t_name [where expr]
注：如果不加where条件，则删除所有数据，等同于 truncate t_name 命令清空表
delete语句仅能删除表中数据，不能删除表，可以用drop table t_name命令来删除表。
4.4 select查询数据
select [distinct] * | [c1,c2,...] from t_name
# distinct可选，指显示结果时是否去掉重复数据，*表示查询所有列，也可以查询指定列
查询表中所有数据
查询表中所有学生以及对应的英语成绩
4.4.1 select使用表达式运算
select * | {c1 | expression,c2 | expression...} from t_name
另：在select语句中可以为某列取别名
select column as 别名 from t_name
对上面的student表统计每个学生的总分
嵌套查询select tmp.field1, tmp.field2 from (select field from t_name) tmp，对查询的结果作为临时表，在对临时表查询。
select查询结果自增列
将查询到的值赋给变量
set @db := ''
select database() into @db;
select '1' into @db;
set @num:=0; #定义一个局部变量
select * ,(@num:=@num+1) as id from t_name;
select *,(@num:=@num+1) as id from t_name ,(select @num:=0) as tmp;
create table t_name as (select语句) # 将查询结果保存到新的表中
eg: 查询e这位同学的总分名次：
4.4.2 select+where条件查询
比较运算符：>：大于 <：小于 <=: 小于等于 >=: 大于等于 =：等于 !=：不等于
between ...and ... 某一个区间
in(set) 在一个集合中 in(1,2,3)
like '%content%' 匹配content
is null 判断是否为空
逻辑运算符： and 同时成立 ， or 至少有一个成立， not 都不成立
统计下表总分超过240并且每科都不低于80的同学：
4.4.3 排序、count统计、sum统计、分组、过滤、限制条数、分页
排序： select * from t_name order by field [asc,desc];默认是 asc升序排序，desc表示降序排序
eg:上面成绩表按照成绩总分降序排序
count统计行数
select count(*) | count(列名) from t_name [where expr]
count(1),count(列名)，count(*)区别:count(1) and count(*)统计表中的所有记录的条数，包含null值；count(列名)统计指定列中不包含null的记录数。
分组统计
group by就是分组的意思，根据某个字段进行分组。基本语法：group by 字段名;
eg:按照姓名进行分组统计总分：
group_concat: 对每个分组的组内数据拼接：
eg: 统计每个部门有多少人
聚合函数
having过滤
having过滤：一般是对group by分组后的数据过滤，语法格式 ：having <查询条件>
一般情况下：where用于过滤表中数据行，having用于过滤查询后的分组；
where 查询条件中不可以使用聚合函数，而 having 查询条件中可以使用聚合函数。
where 在数据分组前进行过滤，而 having 在数据分组后进行过滤 。
where 针对数据库文件进行过滤，而 having 针对查询结果进行过滤。
where 中不可以使用字段别名，having可以使用。
where,groupby,having,orderby顺序：
Where, Group By, Having, Order by。
eg:查询总分>250的学生信息：
eg:对下表 写一条SQL语句: 要出现2门以及2门以上的学科不及格 ; 计算该考生所有学科的平均分，不单是，不及格的那几门
数据表里面的字段也叫属性，在java中称为变量，所以字段也可以调用函数
限制及分页
限制查询语句的条数
select * from t_name limit a 查询a条数据(从第一条开始)
select * from t_name limit a,b 查询b条数据(从第a+1条开始)
分页查询：
select * from t_name limit (pageNo-1)*pageSize , pageSize
4.4.4 合并select结果操作
如果我们需要将两个select语句查询的结果作为一个整体显示出来，我们就需要用到union或union all关键字
union和union all的区别是：
union会将合并的结果集中重复元素去重；
union all是将所有结果全部显示出来，不管是否有重复。
注：union操作时每条select语句查询的列顺序必须对应；需要匹配多个查询列，值必须对应。
查询语法：
select [field1,...] from t1_name
union
seelct [field1,...] from t2_name
select [field1,...] from t1_name
union all
seelct [field1,...] from t2_name
eg: 使用union合并以下表
5 字符串函数
返回str的字符编码：charset(str)
对str转换为大写： ucase(str) [uppercase大写]
对str转换小写： lcase(str) [lowercase小写]
连接字符串 ： concat(str1,str2 [,str3...]) [可以连接多个]
对str左边取n个字符： left(n)
对str右边取n个字符： right(n)
对str的position位置截取字串：substring(str,position [,length]) 通过length设置截取长度
比较两个字符串(ascii码比较)：strcmp(str1,str2) 小于返回-1，大于返回1
返回字符串的字节长度：length(str)
将str中的str1替换为str2: replace(str,str1,str2)
返回substring在str中出现的位置：instr(str,substring) 没有就返回0
ltrim、rtrim: 去除左、右空格
直接去除str左右空格：trim(str)
对数据加密：md5(str)
eg: 将下表名字首字母大写，其他小写
eg: 对数据加密：
可以通过md5 hash算法对用户隐秘数据加密，比如，可以对用户密码加密；用户注册时，对设定的密码采用md5算法生成32位字符串，数据库存储这一份字符串，即使数据库被恶意攻击，获取这一份32字符串也不会泄露密码信息，md5不可逆。当再次登录时把输入的密码使用MD5计算，看是否与数据库存储的值一致。
6 流程控制语句
6.1 if语句
语法格式：if(expr1,expr2,expr3) 如果expr1为true，返回expr2,否则返回expr3
类似于java的三目运算符: expr1?expr2:expr3
另：ifnull(s1,s2) 如果s1不为空，返回s1,否则返回s2
mysql判断空
field is null; //判断空
field is not null; //判断非空
6.2 case…when…语句
类似于java中的 switch..case
语法格式：case [column1] when expr1 then result1 ... end
eg: 将下表的部门1转换为part1, 部门2转换为part2, 部门3转换为part3
7 查询增强
7.1 日期比较
eg: 过滤出2000后出生的人的信息
mysql中日期(标准格式)可以直接比较大小
7.2 like匹配
%:表示匹配0~多个任意字符； _:表示匹配单个任意字符
eg: 对上面的stu表过滤出姓名以t开头的数据
eg: 对上面的stu表过滤出姓名第三个字符是b的数据
判断某一列是否为空不能用column=null, 必须用column is null
8 多表查询
8.1 内连接
内连接：获取两个表中字段匹配关系的记录：
select <字段名> from <表1> inner join <表2> on子句；其中inner可以省略
(相当于求两个表中匹配字段的交集)
8.2 外连接
外连接可以分为左外连接和右外连接，下面根据实例分别介绍左外连接和右外连接。
左外连接又称为左连接，使用 LEFT OUTER JOIN 关键字连接两个表，并使用 ON 子句来设置连接条件。
左连接的语法格式如下：
SELECT <字段名> FROM <表1> LEFT OUTER JOIN <表2> <ON子句>
上述语法中，“表1”为基表，“表2”为参考表。左连接查询时，可以查询出“表1”中的所有记录和“表2”中匹配连接条件的记录。如果“表1”的某行在“表2”中没有匹配行，那么在返回结果中，“表2”的字段值均为空值（NULL）。
右外连接又称为右连接，右连接是左连接的反向连接。使用 RIGHT OUTER JOIN 关键字连接两个表，并使用 ON 子句来设置连接条件。
右连接的语法格式如下：
SELECT <字段名> FROM <表1> RIGHT OUTER JOIN <表2> <ON子句>
右连接以“表2”为基表，“表1”为参考表。右连接查询时，可以查询出“表2”中的所有记录和“表1”中匹配连接条件的记录。如果“表2”的某行在“表1”中没有匹配行，那么在返回结果中，“表1”的字段值均为空值（NULL）。
练习：
t_dept:是每个帮派及对应的地址
t_emp:每个人物对应的信息
eg1: 查询所有门派的人员信息：
eg2.所有不入门派的人员
eg3 所有没人入的门派
9 mysql约束
约束：用于确保数据库中的数据满足特定的规则
mysql 约束包括：not null，unique, primary key, foreign key, check五种。
主键：primary key
一般情况下，为了更快的查找到表中的记录，都会在表中设置一个主键，主键必须非空且唯一。
创建表时用于指定表的某一个字段：字段名 字段类型 primary key
创建表以后在添加主键：alter table t_name modify column datatype primary key;
删除主键约束：alter table t_name drop primary key;
非空约束 not null：
非空约束指字段的值不能为空，如果再添加数据时没有指定值并且没有默认值，mysql就会报错。
创建表时在字段后加上 :字段 类型 not null
唯一约束：unique
唯一性约束是指所有记录中字段的值不能重复出现。例如，为id字段加上唯一性约束后，
每条记录的id值都是唯一的，不能出现重复的情况。唯一约束在一张表中可以设置多个
唯一性约束，并且设置的唯一性约束可以为空，但只能由一个空值。
创建表时添加唯一性约束： 字段 数据类型 unique
外键约束：foreign key
外键约束是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，
相关字段中主键所在的表就是主表，外键所在的表就是从表。外键用来建立主表与从表之间
的联系，约束两个表之间的数据的一致性和完整性。主表删除某条记录时从表中与之对应的
记录也必需有相应的改变。
一个表可以有多个外键，外键可以为空；若不为空，则每一个外键的值必须等于主表中主键
的某个值。
若一个表中的字段被另一个表关联则该字段不能被直接删除。
为主表添加数据：
一旦建立主外键之间的关系，主表就不能随意删除数据了，如果从表中的外键数据删除了那么主表对应的数据才可以删除
查看表是否有外键：show create table t_name
删除有外键约束的表数据
1.查看外键约束是否有效：select @@foreign_key_checks;
值为1就是有效
2.先让其无效：set @@foreign_key_checks=0;
3.使用删除语句
4.在然其生效：set @@foreign_key_checks=1;
10 索引
索引是对数据表中一列或多列的值生成排序树以便快速查询的一种数据结构，使用索引可以快速查询数据表的特定信息。简单的说，索引类似于文章目录，通过目录我们可以快速定位查询的信息在第几页,否则只能一页一页去翻，非常耗时。
实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件，建立索引会占用磁盘空间的索引文件；这就是常说的牺牲 空间换取时间。
# 为t_name表的t_column列创建名为index_name索引
create index index_name on t_name(t_column)
使用jdbc创建1000万条数据的表，比较加索引和不加索引的查询数据：
import com.mysql.jdbc.Driver;
import org.junit.Test;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;
/**
* @author brett
* @date 2022-09-23 19:44:40
*/
public class IndexTest {
@Test
public void indexTest() throws ClassNotFoundException, SQLException, IOException {
//1.加载driver
Class.forName("com.mysql.jdbc.Driver");
//2.创建驱动对象
Driver driver = new Driver();
//3.创建配置类
Properties properties = new Properties();
properties.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("jdbc.properties"));
//4.创建mysql连接
Connection conn = driver.connect(properties.getProperty("url"), properties);
//5.创建执行SQL对象
String sql="insert into t1 values(?,?)";
PreparedStatement preparedStatement = conn.prepareStatement(sql);
for (int i=0;i<10000000;i++){
preparedStatement.setInt(1,i);
preparedStatement.setString(2,"a"+i);
preparedStatement.executeUpdate();
}
preparedStatement.close();
conn.close();
}
}
读取resource下的资源配置文件
properties.load(Thread.currentThread().getContextClassLoader()
.getResourceAsStream("jdbc.properties"));
查询一条数据：
创建索引：
查询索引那一列数据：
速度差了几千倍，这就是索引的速度。
11 索引类型
1.主键索引：如果某个列是主键，那么它就是一个索引，称为主键索引。
2.唯一索引: 如果某一列添加了unique约束，那么它就是唯一索引。
3.普通索引：在一个列上专门指定的索引。
4.全文索引：fulltext；开发中考虑使用 solr/elasticsearch
在innodb存储引擎中根据索引存储形式，又分为：
聚集(聚簇)索引：将数据存储与索引放一块，索引的叶子节点保存了行数据，默认是主键 (必须有且只有一个)
二级索引：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 (可以有多个)
------
如果一张表中id设置主键(聚簇索引)，name设置索引(二级索引)，那么
select * from t_name where name='a';
该语句先定位到name索引的B+树结构，查找到name='a'的叶子节点，此时叶子节点上保存了
该行数据对应的主键id,再用查询到的id去走主键的索引查询相等的id在获取该节点上的行
数据。(这个过程称为回表查询)
12 添加、删除、查找索引
1.添加普通索引：create index 索引名 on 表名(列名)
alter table 表名 add index 索引名 (列名)
2.添加主键索引：创建表时指定主键就已经创建了主键索引
已经创建表之后，添加主键就创建了主键索引：alter table t_name add primary key (列名)
3.添加唯一索引：create unique index 索引名 on 表名(列名)
-------
什么时候用唯一索引，什么时候用普通索引？
当数据中不包含重复值时优先用唯一值索引，包含重复值时用普通索引。
删除索引：drop index index_name on t_name
删除主键索引：drop table t_name primary key
修改索引：先删除索引，在添加新的索引
查询索引：show index from t_name
show indedxes from t_name
show keys from t_name
13 设计索引原则
1.较频繁的查询字段，该字段应作为索引
2.某列数值具有唯一性的应该设计为索引
3.更新非常频繁的字段不适合创建索引
4.不会出现在where子句中的字段不该创建索引(因为创建也用不上)
14 B树、B+树
一种平衡的多叉树，它的一个节点可以有多个元素并且都是排序的，这样的树称为B树。
B树和二叉树、红黑树相比较，子树更多也就是路数越多，子树越多表示数的高度越低，搜索效率越高
一颗m阶的B树满足条件：
1.最多有m个孩子，意思就是最多有m-1个key
2.节点必须按序存储
举例：3阶B树，插入1，2，3，4，5过程：
B树每个节点存储了数据及数据所在的指针地址。
B+树：是B树的一个升级。B+树相比B树，本质上是一样的，区别就在B+树的所有根节点都不带有任何数据信息(数据指针地址)，只有索引信息，所有数据信息全部存储在叶子节点里。这样，整个树的每个节点所占的内存空间就变小了，相当于减少了磁盘IO次数。
mysql使用的索引是用B+树实现的，原因是：
0.B+树层级少，搜索效率高
1.B+树的磁盘读写代价更低
2.B+树的数据信息遍历更加方便(所有信息都可以在叶子节点查找)
3.B+树的查询效率更稳定
15 事务
事务是用户定义的一系列对数据库的操作，这些操作可以视为一个已经完成的逻辑处理单元，要么全部执行，要么全部不执行，是一个不可分割的操作，保证数据的一致性。
当执行事务操作时mysql会在表上加锁，防止其他用户修改表的数据。
事务的ACID
事务具有4个特征：
A:原子性，事务就是数据库的逻辑工作单元，事务中的操作要么都执行成功，要么都失败，不可分割。
C:一致性，事务中的执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态(不会有数据丢失)。
I:隔离性，一个事务的执行不能对其他事务形成干扰。
D:持久性，提交后的事务不可回滚，覆水难收。
mysql事务操作的几个重要命令
1. start transaction #开启一个事务
2. savepoint point_name #设置保存点
3. rollback to point_name #回滚事务到point_name保存点
4. rollback #回退全部事务，回到事务开始状态
5. commit #提交事务，所有操作生效，不能回退
eg: 将a薪水中的100元奖励给b；这种情况下就要使用事务保证数据的一致性。
16 事务隔离级别
事务隔离级别：多个连接开启各自事务操作数据库中的数据时，数据库要负责隔离操作，以保证各个连接在获取数据时的准确性，mysql隔离级别定义了事务与事务之间的隔离程度。
如果不考虑隔离性，可能会引发如下问题：
脏读 : 一个事务读取另一个事务未提交但改变的数据时产生脏读 。
不可重复读 ：同一个查询在同一个事务中多次进行，由于其他事务提交所做的修改或删除，每次返回不同的结果集，此时发生不可重复读。
幻读 ：同一查询在同一事务中多次进行，由于其他事务提交所做的插入操作，每次返回不同的结果集，此时发生幻读。
其他事务还未提交，可能会回滚，现在去查询肯可能会导致查询到的数据不一致。
4种隔离级别
| mysql隔离级别 | 脏读 | 不可重复读 | 幻读 | 加锁读 |
|---|---|---|---|---|
| 读未提交(read uncommitted) | v | v | v | 不加锁 |
| 读已提交(read committed) | x | v | v | 不加锁 |
| 可重复读(repeatable read) | x | x | v | 不加锁 |
| 可串行化(serializable) | x | x | x | 加锁 |
演示隔离级别
mysql默认的隔离级别是repeatable read
可以在my.ini里面设置默认隔离级别
[mysqld]
transaction-isolation=repeatable-read
------
查找当前事务隔离级别：
select @@tx_isolation;
------
mysql8以后：
select @@transaction_isolation;
------
查看系统的隔离级别：
select @@global.tx_isolation;
------
设置当前会话隔离级别：
set session transaction isolation level 级别；
------
设置系统隔离级别：
set global transaction isolation level 级别;
开启两个mysql客户端，其中一个设置隔离级别为读未提交(最弱)级别
创建account表：
现在有两个客户端，nvicat和mysql shell开启的
如下图，产生脏读
提交了事务，产生不可重复读、幻读
shell这边的事务从登录那一刻开始，我希望我访问的表是不变的(理想状态我们只能看到登录之前的状态)，以方便我操作表，但是这时其他事务却把这个表修改了；正常情况是两个事务是独立的，不应该相互影响。
将shell登录的事务关闭，在开启新的事务，隔离级别修改为read committed
如下图，所以读已提交级别不会出现脏读，但会出现不可重复读、幻读
再次将shell端事务提交，设置隔离级别为可重复读，开启新事务
再将shell端事务提交，开启新事务，设置隔离级别为可串行化
这里因为是Serializable隔离级别，加上了锁，所以必须要等左边事务执行完才能执行右边事务
17 mysql存储引擎
mysql 表类型由存储引擎决定的，主要包括MyISAM、InnoDB、Memory等。
简单说：存储引擎就是表的类型以及表在计算机上的存储方式。
查看存储引擎
show engines
主要的存储引擎特点：
1.myisam不支持事务，也不支持外键，但其访问速度快，对事务完整性没有要求
2.innodb存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起
myisam存储引擎，innodb写的处理效率差一些并且会占用更多的磁盘空间以保存
数据和索引。
3.memory存储引擎使用存在内存中的内容来创建表。每个memory表只实际对应一个
磁盘文件。memory类型的表访问非常快，因为它的数据都存储在内存中，对临时表有用，
并且默认使用hash索引。但是一旦关闭服务，表中的数据就会丢失，表的结构还在。
MyISAM索引与InnoDB索引的区别？
InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做
到覆盖索引会非常高效。
18 视图(view)
表的列信息很多，有些信息是个人非常重要的，如果我们希望某个用户只能查询表中不重要的信息，有什么办法？---视图view
MySQL 视图（View）是一种虚拟存在的表，同真实表(基表)一样，视图也由列和行构成，但视图
并不实际存在于数据库中。行和列的数据来自于定义视图的查询中所使用的表(基表)，并且还是在
使用视图时动态生成的。
视图可以从原有的表上选取对用户有用的信息，那些对用户没用，或者用户没有权限了解的信息，
都可以直接屏蔽掉，作用类似于筛选。这样做既使应用简单化，也保证了系统的安全。
视图的基本操作
1.create view 视图名 as select语句 创建视图
2.alter view 视图名 as select语句 修改视图
3.show create view 视图名 查看视图创建结构
4.drop view 视图名1,视图名2 删除视图
基表如下：
创建视图后，对应视图只有一个视图结构文件(视图名.frm),没有数据文件
eg: 针对emp、dept和salgrade三张表创建 一个视图，可以显示姓名，部门，薪水级别
19 存储过程
如果在实现用户某些需求时，需要编写一组复杂的SQL语句才能实现的时候，那么可以将这组SQL语句提前编写在数据库中，由jdbc来调用这组SQL语句，把编写在数据库中的SQL语句集称为存储过程。
存储过程(procedure)：是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储
过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于
提高数据处理效率很有好处。
就是对SQL语句代码封装与复用
存储过程就类似于Java中的方法，需要先定义，使用时需要调用。存储过程可以定义参数，参数分为IN、OUT、INOUT类型三种类型。
IN类型的参数表示接受调用者传入的数据；
OUT类型的参数表示向调用者返回数据；
INOUT类型的参数即可以接受调用者传入的参数，也可以向调用者返回数据。
设置变量
1.定义用户变量(在当前mysql连接中有效)
set @[变量名] = 值; 使用时用 @[变量名]
2.定义局部变量(在函数/存储过程中有效)
declare [变量名] [数据类型] [默认值];
局部变量可以用set赋值/使用select...into [变量名]
基本创建语法
delimiter $$
create
procedure `数据库名`.`存储过程名`(参数列表)
begin
sql语句集
end$$
delimiter;
基本调用语法call 存储过程名(参数列表)
DELIMITER $$
CREATE
PROCEDURE `demo`.`demo2`(IN s_sex CHAR(1),OUT s_count INT)
-- 存储过程体
BEGIN
-- 把SQL中查询的结果通过INTO赋给变量
SELECT COUNT(*) INTO s_count FROM student WHERE sex= s_sex;
SELECT s_count;
END$$
DELIMITER ;
@s_count表示测试出输出的参数
CALL demo2 ('男',@s_count);
20 mysql自定义函数
存储过程和函数都是经过事先编译并存储在数据库中的一段SQL语句集合，调用存储过程和函数可以简化应用开发工作，提高数据处理的效率。
函数创建：
delimiter $$
create function 函数名(形参列表) returns 返回类型
begin
函数体 -- 若干sql语句,但是不要直接写查询语句
return value;
end;
delimiter;
eg: 编写一个函数add，对下面books表中任意输入两本书名，求价格之和
delimiter $$
create function add1(name1 varchar(32),name2 varchar(32)) returns double
begin
declare price_1 double;
declare price_2 double;
select bookprice into price_1 from books where bookname=name1;
select bookprice into price_2 from books where bookname=name2;
return (price_1+price_2);
end$$
delimiter;
select add1('红楼梦','阿呆')
mysql用户管理
用户管理机制：当我们做项目开发时，可以根据不同的开发人员赋予其相应的mysql操作权限，不可能都赋予root权限
MySQL用户可以分为普通用户和root用户。root用户是超级管理员，拥有所有权限，包括创建用户、删除用户和修改用户的密码等管理权限;普通用户只拥有被授予的各种权限。
mysql中的用户都存储在系统数据库mysql的user表中。
其中user表的重要字段说明：host：允许登录的位置，localhost表示该用户只允许本机登录，也可以指定ip登录；user：用户名；authentication_string: 用户密码，是通过mysql的password()函数加密以后的密码。
创建普通用户：
create user '用户名' @ '允许登录的位置' identified by '密码'
---
在创建用户时如果没有指定host，则为%，%表示所有ip都有连接权限。(%表示匹配所有ip)
比如：create user 用户名 @'192.168.1.%' 表示时用户在192.168.1.*的ip主机都可以
登录。在删除用户时，如果host不是%，需要明确host的值。
删除用户：
drop user '用户名' @ '允许登录位置'
不同的数据库用户看到的数据库和表都不同，权限不同。
一个普通用户创建以后只有对information_schema库的权限，需要授权才能访问其他数据库。
修改用户密码：set password=password('密码'); 只对当前用户有效
修改其他用户密码(需要授予权限)：set password for '用户名'@'位置'=password('密码');
如果是root用户则不需要授权就可以修改任何用户密码。
mysql普通用户权限
给用户授权：
grant 权限列表 on 库.表名 to '用户名' @'登录位置' [identified by 密码]
权限列表说明：
grant select on... (给与用户查询权限)
grant select,delete,create,update on... (给与用户查询、删除、创建、修改权限)
grant all [privileges] on... (表示赋予该用户在该表/库上的所有权限)
---
grant 权限列表 on *.* to... (为用户赋予可以操作本系统所有数据库、表等对象)
grant 权限列表 on 库.* to... (为用户赋予可以操作指定库所有对象)
回收用户权限：
revoke 权限列表 on 库.表 from '用户名'@'登录位置'
权限生效：
flush privileges;
mysql练习题
0.行列互换
mysql> select * from stu;
| name | course | grade |
|---|---|---|
| 张三 | java | 68 |
| 李四 | java | 28 |
| 张三 | python | 81 |
| 李四 | python | 20 |
将上表转换为下表的格式(成绩由行转化为列)
| name | java | python |
|---|---|---|
| 张三 | 68 | 81 |
| 李四 | 28 | 20 |
answer：
------ 使用if语句
select name,
sum(if(course='java',grade,0)) as 'java', -- 如果查询的这一列数据是java，返回
--java的成绩，否则返回0，因为按照name分组了，所以同一个name有多个数据需要对这个
--分组做聚合操作；这里也可以使用max()聚合函数
sum(if(course='python',grade,0)) as 'python'
from stu
group by name;
------ 使用case...when...
select name,
max(case when course='java' then grade end) as 'java',
max(case when course='python' then grade end) as 'python'
from stu
group by name;
mysql> select * from stu2;
| name | java | python |
|---|---|---|
| 张三 | 68 | 81 |
| 李四 | 28 | 20 |
将该表转换为上面stu表
select name,'java' as course,java as grade from stu2
union
select name,'python' as course,python as grade from stu2;
数据库连接池
在使用基于数据库的web程序时，传统的模式基本是按照以下步骤：
1.在主程序中建立数据库连接
2.执行SQL语句
3.断开数据库连接
这种开发模式存在的问题：
1.普通的jdbc连接使用DriverManager来获取，每次向数据库建立连接的时候都要将Connection加载到内存中，再验证用户名和密码(得花费0.05~1s的时间)。需进行数据操作时就又连接数据库执行完以后在断开连接。这样的方式将会消耗大量的资源和时间，数据库的连接没有得到很好的重复利用。若果同时有几百人甚至上千人在线同时操作数据库会占用很多的系统资源，严重的甚至会照成服务器的崩溃。
2.对于每一次连接数据库，使用后都得断开连接，否则，如果程序出现异常而未能关闭，将会导致数据库系统中的内存泄露，最终将导致重启数据库。
为了解决上述出现的问题，提出来数据库连接池技术；核心思想是为数据库建立一个“缓冲池”，在缓冲池中放入一定量的数据库连接，当需要连接数据库时就从连接池中取出一个，使用完在放回去。
数据库连接池负责分配、管理和释放数据库连接，它允许用户重复使用一个现有的数据库连接而不是重复建一个。
当应用程序向连接池请求的连接数超过最大连接数据量时，这些请求将被放入等待队列中。
下面演示jdbc创建5000条mysql连接，非常耗时；但是5000个并发在项目中是非常常见的场景，很显然一般的jdbc不满足现实场景，并且有时还会直接报too many connections错误，直接导致服务器无法工作。
@Test
public void multiConnection() throws Exception {
//测试DriverManager 5000个mysql连接
Class.forName("com.mysql.jdbc.Driver");
Properties properties = new Properties();
properties.load(Thread.currentThread().getContextClassLoader()
.getResourceAsStream("jdbc.properties"));
for (int i=0;i<5000;i++){
Connection conn = DriverManager.
getConnection(properties.getProperty("url"), properties);
//todo 执行mysql
}
}
jdbc的数据库连接池使用javax.sql.DataSource来表示，DataSource只是一个接口，该接口通常由第三方提供实现，Druid(德鲁伊)是阿里提欧的数据库连接池，集c3p0,dbcp等连接池优点于一身，比较常用。
Druid连接池使用
1.在官网上下载Druidjar包在加入到项目中
2.设置配置文件
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mydb?serverTimeZone=Asia/Shanghai
username=root
password=986836
# 初始化的mysql连接数
initialSize=10
# 最小空闲连接数(原先创建了10个但是如果一直没有用，会释放直到留下5个)
minIdle=5
# 最大连接数
maxActive=50
# 最大等待时间5s(在等待队列里面等待5s如果没有获取连接则该次就放弃，准备下一次)
maxWait=5000
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;
import javax.sql.DataSource;
import java.sql.Connection;
import java.util.Properties;
/**
* @author brett
* @date 2022-10-09 21:25:32
*/
public class DruidTest {
@Test
public void testDruid() throws Exception {
//1加入Druid jar包
//2设置配置文件
//3创建properties配置对象
Properties properties = new Properties();
properties.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("druid.properties"));
//4创建一个指定参数的数据库连接池
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
//5获取连接
Connection connection = dataSource.getConnection();
System.out.println("创建数据库连接池成功");
connection.close();
}
}
373
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
