7月31日MySQL学习笔记

今日内容: mysql: 行列转换   数据类型     函数  触发器  存储过程   事务  索引(还没讲)  三范式 JDBC连接数据库的6个步骤 三握四挥

---

行列转换

        第一步  新建要转换的列

select name, 
1 as '语文', 
1 as '数学', 
1 as '英语' 
from t_score GROUP BY name

        第二步  对每一列填入值，没有对应值填null/0

select *,
CASE subject 
when '语文' then fraction
else 0
end
,
CASE subject 
when '数学' then fraction
else 0
end,
CASE subject 
when '英语' then fraction
else 0
end
 from t_score			
				

        第三步  将多行数据分组合并

-- 求和或最大值			
select name, 
sum(CASE subject 
when '语文' then fraction
else 0
end) as '语文', 
sum(CASE subject 
when '数学' then fraction
else 0
end) as '数学', 
sum(CASE subject 
when '英语' then fraction
else 0
end) as '英语' 
from t_score GROUP BY name

        或者使用if函数

select name, 
avg(if(subject='语文',Fraction,null)) as '语文', 
sum(if(subject='数学',Fraction,null)) as '数学', 
sum(if(subject='英语',Fraction,null)) as '英语' 
from t_score GROUP BY name				
				

 if函数

        三元运算表达式 也可以实现行列转换

        面试常问：行列转换能不能使用avg函数：配合if函数可以实现

-- if函数  三元运算表达式 也可以实现行列转换
select * ,if(subject='语文',Fraction,0) from t_score

-- if返回null就可以用avg 
select * ,if(subject='语文',Fraction,null) from t_score	

数据类型

        

整数类型

        tinyint 1字节  smallint 2字节  mediumint 3字节  int 4字节  bigint 8字节

浮点型

        double(总长度,小数位数) float(总长度,小数位数) decimal(总长度,小数位数)

字符串

        char varchar() text longtext

-- char 最大长度是255字符长度；
-- varchar 能够存储的字节数是65535，varchar最大长度是计算出来的，不能说是65535
    -- 1.结构varchar类型的第一个字节不存储数据
    -- 2.varchar前两个字节存储数据长度 
    -- 3.有效位就(mb3:/3;mb4:/4;余数不是有效位)剩下65532  
    -- 4.编码格式决定能够存储多少个字符
    -- 5.每一行总占用的字节长度不能超过65535，所以要求varchar最大占用字节不能超过65535，如果要存储长文本要使用text类型
-- char和varchar区别（常见面试题）
    -- 1.总长度
    -- 2.char是定长 varchar是变长
        -- char(20)存abc 还是占20个字符位
        -- varchar(20)存abc 占3个字符位
    -- 3.char性能更优异，varchar要计算字符数，所以差一些
    -- 4.存储固定长度时使用char，例如：身份证号码、手机号、学号、订单号
        -- 可变长度使用varchar，例如：姓名、地址、店名
         
-- text 长文本 不需要记录长度 不记录在每行的data文件中，没有65535的限制

日期

        Date 年月日   time 时分秒毫秒   Datetime 年月日时分秒

视图

        视图 view 是一个已经编译好的sql语句，sql语句要先编译然后执行，视图就是编译完但没有执行。

-- 创建视图
create view v_student_score as 
select a.sname, c.cname, b.score from student as a 
join sc as b on a.sid=b.sid 
join course as c on b.cid=c.cid;

-- 使用  视图中不存储数据，使用视图就是使用编译完的子查询  可以加快减速效率
SELECT * from v_student_score

-- 删除
drop view if EXISTS v_student_score

使用视图的优点

简化和重用：简化复杂的查询并且可以实现sql语句重用。

安全性：视图可以限制用户对某些数据的访问，只展示他们需要看到的数据，从而保护敏感信息。

提高性能：视图是已经编译好的sql语句，使用视图可以加快检索效率，并且在某些情况下，如果视图被频繁使用，数据库可以对视图的查询进行优化，提高查询性能。

函数
 

自定义函数

        要先设置权限

set global log_bin_trust_function_creators=TRUE;

-- 判断score数值 >60合格 否则不及格
create function method(score int) returns varchar(20)
begin 
	-- 定义一个局部变量记录返回结果  default ''  默认值
	declare result varchar(20) default '';
	
	if score>=60 then 
	-- 对变量赋值  set 
		set result = '及格';
	else 
		set result = '不及格';
	end if;
	
	return result;
end;

-- 删除
drop function method;
-- 使用
select method(80);
select * ,method(score) as 水平 from sc 

内部函数

        包括数值函数和字符串函数

        注意点：char_length 字符长度        length  字节长度

        参考博主总结：http://t.csdnimg.cn/bqWLh

-- char_length 字符长度    length  字节长度
select char_length('大'), length('大')
-- 截取前/后 n个
select left('123456',3),right('123456',3)
select CONCAT(left('19555005777',3),'****',right('19555005777',4))

-- trim 使用方式特别多，查
select trim(' 123   ')

select replace('123123123','1','a')

select SUBSTR('1234',2,3);
select SUBSTR('1234' from 2 for 3);
select SUBSTR('1234' from 2)
select SUBSTRING('1234',2,3)

-- 日期函数
-- 差不多
select NOW(),SYSDATE()
log-- 格式化日期
select DATE_FORMAT(now(),'%Y-%m-%d %H:%i:%S')

触发器 trigger

        相对于一个事件，一旦表中发生了指定的事件，该触发器就会自动运行

        只对增删改起作用

        触发时机 before after 

        尽量不用触发器  会影响正常业务逻辑  由java逻辑去完成触发器工作

-- for each row 每有一行变动就触发一次
create trigger if not EXISTS tgg_i_a_student after insert on student for each row 
begin 
    -- 普通增删改sql语句
	update log set val=(select count(*) from student) where `key`='studentcount';
end ;


create TRIGGER tgg_u_b_student before update on student for each row 
begin 
-- old.列名  获取原数据
-- new.列名  新的数据
    update log set val=concat(concat('{',old.sid,',',old.sname,'}'), '->',concat('{',new.sid,',',new.sname,'}')) where `key`='lastupdate';
end; 

存储过程PROCEDUR

        要使用循环语句loop，loop是个死循环，要用标签起别名aa:loop,使用 eave aa跳出 

create procedure pro_insert_student_log(in num int )
BEGIN
	-- 循环语句 loop 死循环 leave跳出 
	DECLARE i int DEFAULT 0;
	DECLARE stuname varchar(20);
	DECLARE randomS int ;
	aa:loop 
		-- 获取学生的名称  生成随机成绩
		-- into和set差不多
		select sname into stuname from student limit i,1;
		set randomS=FLOOR(RAND()*100);
		-- 插入log表
		insert into log value(stuname,randomS);
		set i=i+1;
		if i>=num THEN
			LEAVE aa;
		end if;
	end loop aa;
END

-- 使用
call pro_insert_student_log(8)

事务

        事务是数据库管理系统中执行的逻辑单位，它是一系列操作，这些操作要么全部完成，要么全部不完成，以保证数据库状态的一致性。事务是数据库操作的原子性保证。

事务的四大特性（ACID）

1. 原子性：事务中的所有操作要么全部完成，要么全部不执行，不会结束在中间某个点。
2. 一致性：事务必须保证数据库从一个一致的状态转移到另一个一致的状态。
3. 隔离性：并发执行的事务之间不会互相影响。
4. 持久性：一旦事务提交，它对数据库的改变就是永久性的，即使系统发生故障也不会丢失。

并发问题

1. 脏读：一个事务读取了另一个事务未提交的数据。
2. 不可重复读：在一个事务中，由于其他事务的更新，导致多次读取同一数据集合时结果不一致。
3. 幻读：一个事务在读取某个范围内的记录时，由于其他事务的插入，导致再次读取该范围时出现之前不存在的记录。
4. 丢失更新：两个或多个事务更新同一数据项，但只有一个事务的更新被提交，导致其他事务的更新丢失。

事务隔离级别

1. 读未提交：最低级别的隔离，允许读取未提交的数据，可能会导致脏读。
2. 读已提交/不可重复度：只允许读取已提交的数据，可以避免脏读，但可能会出现不可重复读。
3. 可重复读(MySQL默认)：使用行锁，保证在同一个事务中多次读取同一数据的结果是一致的，可以避免不可重复读，但不保证整个查询范围，可能会出现幻读。
4. 串行化：最高级别的隔离，事务将依次顺序执行，可以避免脏读、不可重复读和幻读，但性能较低。

三范式

        第一范式：每一列的数据是不可分割的（不可以再分割）
        第二范式：每一列的数据要完全依赖主键（不可以部分依赖）
        第三范式：不可以传递依赖

JDBC

        jdbc连接数据库的六个步骤：1.加载驱动；2.创建连接；3.获取执行对象；4.执行sql语句；5.处理结果集；6.关闭连接；

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBC {
	public static void main(String[] args) {
		//1.jdbc连接数据库的六个步骤
		String url="jdbc:mysql://localhost:3306/easydata";
		String username="root";
		String password="tcwjy2021";
		//从Java 6和JDBC 4.0开始，JDBC驱动可以自动被加载，无需调用 Class.forName()。
		String driverClassName="com.mysql.cj.jdbc.Driver";
		//1.加载驱动
		try {
			Class.forName(driverClassName);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		Connection con=null;
		Statement sta=null;
		try {
			//2.创建链接
			con=DriverManager.getConnection(url,username,password);
			//3.获取执行对象
			sta=con.createStatement();
			//4.执行sql语句  写  executeUpdate  读
			int rowCount=sta.executeUpdate("delete from student where sid=14");
			//5.处理结果集
			if(rowCount>0) {
				System.out.println("成功删除了"+rowCount+"条数据");
			}else {
				System.out.println("删除失败");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			//关闭连接
			if(sta!=null) {
				try {
					sta.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(con!=null) {
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		} 
		
	}
}

TCP三次握手和四次挥手

TCP三次握手建立连接：
1. 客户端发送SYN：请求建立连接。
2. 服务器回送SYN-ACK：同意建立连接。
3. 客户端发送ACK：确认连接建立。

TCP四次挥手断开连接：
1. 主动方发送FIN：请求关闭连接。
2. 被动方回送ACK：确认请求。
3. 被动方发送FIN：请求关闭另一端连接。
4. 主动方回送ACK：确认关闭连接。

 重点：
        三次握手：确保连接建立，使用SYN和ACK标志位。
        四次挥手：确保连接完全关闭，使用FIN和ACK标志位。
        状态转换是关键，确保每一步骤都得到确认。