MySQL进阶

行列转换

将上面表中的内容以名字分组，然后将成绩按科目横向展示：

通过case... when... then...能够将数据筛选处理后横向展示，只需要再对每一列通过聚合函数将无关的行合并为一行，如max或者sum都可以只保留非零的有效数据。

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 ( ) 函数 ，逻辑同三元运算表达式

-- if 函数 三元
SELECT *,if(subject='语文',fraction,NULL) FROM t_score;

一般来说通过聚合函数行列转换时不可以使用AVG，但如果将无关行设置为null，在计算平均值时，AVG会自动忽略null值，实现和前面类似的操作。

---

数据类型

常用数据类型

整数类型

类型	TINYINT	SMALLINT	MEDIUMINT	INT	BIGINT
字节数	1	2	3	4	8

---

浮点型

        DOUBLE(总长度,小数位数) ，FLOAT ，DECIMAL    

        整数位长度不能超过总长减去小数位

在MySQL中，DECIMAL数据类型用于存储固定精度的小数值。DECIMAL数据类型在创建时需要指定精度和标度。精度指定了该数据类型的总位数，而标度指定了小数点后的位数。

如果想要DECIMAL数据类型不保存小数，可以通过将标度设置为0来实现。这样，将只保存整数部分，不保存小数部分。

---

字符串

        CHAR 字符，VARCHAR 可变字符，TEXT 长文本 还有一种不常用的 LONGTEXT

        char的最大字符长度255

注：varchar  能够存储的字节数是65535

        1.结构 varchar 类型的第一个字节不存储数据
        2.varchar前两个字节存储数据长度
        3.有效位就剩下65532 ， 编码格式决定能够存储多少个字符（如utf8mb4编码字符数16383）

        4.行中列的总字节长度不能超过65535，如果要存储长文本，使用text

        如上图中，int类型 4个字节 + name的16372 *4 + subject的10 *4 不超过 65535

char和varchar的区别？

        1.char定长的，    varcha变长的
                char(20)        存了abc，不超过20不管存储多少个字符，都占20个字符位
                varchar(20)   变长的，abc占3个字符位，存几个字符占几个字符位，可以节省空间
        2.char的性能更好，varchar稍逊，因为要计算字符数
        3.应用场合：身份证号、学号、手机号、订单号等固定长度的字符串适合用char；
                             介绍信息、店名、姓名、地址等可变长度的字符串适合用varchar。

TEXT  长文本，不需要设置长度

---

BLOB（Binary Large Object）数据类型用于存储二进制数据，如图像、音频、视频、文件等。

BLOB类型通常在以下场合使用：

1. 存储图像、音频、视频或其他二进制文件：BLOB类型常用于存储文件，如用户上传的图像、音频或视频文件等。通过将文件存储为BLOB类型，可以方便地将其与数据库记录关联起来，并在需要时进行检索。

2. 存储序列化的对象或数据结构：BLOB类型还可以用于存储序列化的对象或数据结构。通过将对象序列化为字节流，并将其存储为BLOB类型，可以将对象保存到数据库中，并在需要时进行反序列化。

3. 存储大文本或大数据块：如果需要存储非常大的文本内容或数据块，例如文章内容、日志记录等，BLOB类型也可以作为一种选择。它可以存储大量的文本数据，并允许高效地进行检索和处理。

另外，如果要在数据库中存储比较机密的公章，使用BLOB类型可以确保数据以二进制形式存储在数据库中，而不会对数据进行任何处理或转换。这可以保证公章数据的完整性和保密性。同时，BLOB类型也具有灵活性，可以根据实际需求存储不同大小的公章。

需要注意的是，在使用BLOB类型存储公章时，需要保证数据库的安全性和权限管理，以防止未经授权的访问和泄露机密数据的风险。

---

日期

DATE 年月日，TIME 时分秒毫秒，  DATETIME 年月日时分秒毫秒

---

视图 VIEW

视图（View）是一种虚拟表，由一个或多个基本表的数据按照特定的查询条件组合而成。视图在逻辑上表现为一个表，但实际上并不存储数据，它是通过查询语句动态生成的结果集。

创建视图

视图中不存储数据，数据本质上还是存储在表当中，视图可以当作表来查询

视图是已经编译好的，执行时省去了语句编译的过程，能够加快检索效率

-- 创建视图 v_ 
CREATE VIEW v_student_score AS 
SELECT a.sname,c.cname,b.score FROM student AS a
LEFT JOIN sc AS b ON a.sid=b.sid 
LEFT JOIN course AS c ON b.cid=c.cid;

-- 视图中不存储数据，数据还是存储在表当中，可以当作表来查询
SELECT * FROM v_student_score;

-- 删除视图
DROP VIEW v_student_score;

视图的优点：

1. 定制用户数据，聚焦特定的数据：不同的用户可能对不同的数据有不同的要求.

2. 简化数据操作：在使用查询时，很多时候要使用聚合函数，同时还要显示其他字段的信息，可能还需要关联到其他表，语句可能会很长，如果这个动作频繁发生的话，可以创建视图来简化操作。

3. 提高数据的安全性：视图是虚拟的，物理上是不存在的。可以只授予用户视图的权限，而不具体指定使用表的权限，来保护基础数据的安全。

4. 共享所需数据：通过使用视图，每个用户不必都定义和存储自己所需的数据，可以共享数据库中的数据，同样的数据只需要存储一次。（主要针对企业用数据库）

5. 更改数据格式：通过使用视图，可以重新格式化检索出的数据

总之，视图在数据库中起到了简化查询、数据安全性、数据虚拟化和逻辑数据独立性等方面的作用，提供了灵活性和便利性，提高了数据库的管理和应用的效率。

---

触发器 TRIGGER

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

触发条件：

        触发器可以通过三种操作触发：增、删、改，一个触发器只能写其中一个

触发的时机：

        before 之前，after 之后

创建触发器

-- 在student表中插入数据后自动触发更新log表的val值
create trigger tgg_i_a_student after insert 
on student for each row BEGIN	-- for each row 针对每一行数据都执行一次
update log set val=(SELECT count(*) from student) 
where `key`='studentcount';
END;
-- 查看触发器
SHOW TRIGGERS;
-- 删除触发器
drop trigger tgg_i_a_student;

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;

触发器常用于生成日志，如上述两个触发器，在插入之后更新人数，在修改之前留存原始数据。

注：尽量不要用触发器，会影响正常的业务逻辑，要用java逻辑代码去完成触发器的工作。

---

函数 FUNCTION

自定义函数

IF语句：

IF search_condition THEN statement_list

[ELSEIF search_condition THEN statement_list]...

[ELSE statement_list]

END IF

CREATE function method(score int) returns varchar(20)
BEGIN
-- 传入一个数作为成绩，判断score的数值：60分以上及格，否则不及格
	-- 定义一个局部变量记录返回结果
	DECLARE result varchar(20) default '';	-- 可以指定默认值
	if score>=60 then 
		-- 对变量赋值  两种语法
		SET result='及格';
	else 
		set result='不及格';
	END if;
	return result;
END;

如果创建失败，可能需要设置一个数据库的常量：

set global log_bin_trust_function_creators=TRUE;

调用和删除自定义函数

SELECT method(80);

SELECT *,method(score) FROM sc;
-- 删除函数
drop FUNCTION method;

---

内置函数

-- 内置函数
SELECT FLOOR(12.99);	-- 向下取整

MySQL数值型函数

函数名称	作用
ABS	求绝对值
SQRT	求二次方根
MOD	求余数
CEIL和CEILING	两个函数功能相同，都是返同不小于参数的最小整致，即向上取整
FLOOR	向下取整，返回值转化为一个BIGINT
RAND	生成一个0-1之间的随机数，传入整数参数是用来产生重复序列
ROUND	对所传参数进行四舍五入
SIGN	返回参数的符号
POW和POWER	两个函数的功能相同，都是所传参数的次方的结果值
SIN	求正弦值
ASIN	求反正弦值，与函数 SIN 互为反函数
COS	求余弦值
ACOS	求反余弦值，与函数 COS 互为反函数
TAN	求正切值
ATAN	求反正切值，与函数 TAN 互为反函数
COT	求余切值

---

字符串函数

MySQL字符串函数

函数名称	作用
LENGTH	计算字符串长度函数，返回字符串的字节长度
CONCAT	合并字符串函数，返回结果为连接参数产生的字符串，参数可以是一个或多个
INSERT	替换字符串函数
LOWER	将字符串中的字母转换为小写
UPPER	将字符串中的字母转换为大写
LEFT	从左侧字截取符串，返回字符串左边的若干个字符
RIGHT	从右侧字截取符串，返回字符串右边的若干个字符
TRIM	删除字符串左右两侧的空格
REPLACE	字符串替换函数，返回替换后的新字符串
SUBSTRING	截取字符串，返回从指定位置开始的指定长度的字符串
REVERSE	字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串

1.返回字符数CHAR_LENGTH(str)，返回字节数LENGTH(str)

SELECT CHAR_LENGTH('你好'),LENGTH('你好'); 

2.从左保留几位字符 LEFT，从右 RIGHT，拼接字符串CONCAT

SELECT LEFT('123456',3),RIGHT('123456',3);
-- 隐藏手机号
SELECT CONCAT( LEFT('18088886666',3),'****',RIGHT('18088886666',4));

3.翻转字符串 REVERSE

SELECT REVERSE('123456789');	-- 翻转字符串

4.替换 REPLACE

SELECT REPLACE('123123123','1','A')	-- 替换

5.截取 SUBSTR

SELECT SUBSTR('abcdefg' FROM 2 for 3);	-- 从第二个位置开始截取，不是下标
SELECT SUBSTR('abcdefg' FROM 2);  		-- 从哪个位置截取到最后

6.去除首尾空格 TRIM

MySQL的字符串函数trim是用于去除字符串两端的空格或指定字符的函数。

SELECT TRIM('  123    '); -- 去空格

语法： TRIM([BOTH | LEADING | TRAILING] [指定字符] FROM 字符串)

参数说明：

• BOTH：表示从字符串两端都去除指定字符，默认为两端。
• LEADING：表示只从字符串起始位置去除指定字符。
• TRAILING：表示只从字符串结束位置去除指定字符。
• 指定字符：要去除的字符或字符集合，默认为去除空格。

示例：

        1.去除字符串两端的空格： SELECT TRIM(' abc ');

        结果为："abc"

        2.去除字符串起始位置的指定字符： SELECT TRIM(LEADING '0' FROM '000123');

        结果为："123"

        3.去除字符串结束位置的指定字符： SELECT TRIM(TRAILING '!' FROM 'hello!!!');

        结果为："hello"

        4.去除字符串两端的指定字符： SELECT TRIM(BOTH '.' FROM '.example.com.');

        结果为："example.com"

注意事项：

• 如果没有指定字符，则默认去除空格。
• TRIM函数对于中间的字符不会进行处理，只处理字符串两端的字符。
• TRIM函数是区分大小写的，如果指定字符的大小写与字符串中的字符不一致，将不会被去除。

---

时间函数

MySQL的日期和时间函数

函数名称	作用
CURDATE 和 CURRENT_DATE	两个函数作用相同，返回当前系统的日期值
CURTIME 和 CURRENT_TIME	两个函数作用相同，返回当期系统的时间值
NOW 和 SYSDATE	两个函数作用相同，返回当前系统的时间和日期值
UNIX_TIMESTAMP	获取UNIX时间戳函数，返回一个以UNIX时间戳为基础的无符号整数
FROM_UNIXTIME	将UNIX时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数
MONTH	获取指定日期中的月份
MONTHNAME	获取指定日期中的月份英文名称
DAYNAME	获取指定日期对应的星期几的英文名称
DAYOFWEEK	获取指定日期对应的一周的索引位置值
WEEK	获取指定日期是一年中的第几周，返回值的范围是1~53
DAYOFYEAR	获取指定日期是一年中的第几天，返回值范围是1~366
DAYOFMONTH	获取指定日期是一个月中的第几天，返回值范围是1~31
YEAR	获取年份，返回值是1970~2069
TIME_TO_SEC	将时间参数转换为秒数
SEC_TO_TIME	将秒数转换为时间，与TIME_TO_SEC互为反函数
DATE_ADD 和 ADDDATE	两个函数功能相同，都是为日期添加指定的时间间隔
DATE_SUB 和 SUBDATE	两个函数功能相同，都是为日期减去指定的时间间隔
ADDTIME	时间加法运算，在原始时间上添加指定的时间
SUBTIME	时间减法运算，在原始时间上减去指定的时间
DATEDIFF	获取两个日期之间的间隔，返回参数 1 减去参数 2 的值
DATE_FORMAT	格式化指定的日期，根据参数返回指定格式的值
WEEKDAY	获取指定日期在一周内对应的工作日索引

获取当前时间

如果需要在插入数据时存储插入的时间只需要传入now()即可

-- 时间函数
SELECT NOW(),SYSDATE();

格式化时间

-- 格式化时间
select DATE_FORMAT(NOW(),'%Y-%m-%d %H-%i-%s');

更多详情参考 MySQL教程：MySQL DATE_FORMAT() 函数

---

存储过程 PROCEDURE

存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。当以后需要数据库提供与已定义好的存储过程的功能相同的服务时，只需调用“CALL存储过程名字”即可自动完成。

创建存储过程

-- 存储过程
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
	-- 获取学生的名字，生成随机成绩
	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

call pro_insert_student_log(8); -- 调用存储过程

---

范式

三种范式是关系型数据库设计中的规范化原则，用于减少数据冗余并提高数据结构的有效性。

1. 第一范式（1NF）：确保每个列的值都是原子性的，不可再分。每个列都应该只包含一个值，不允许重复组或多值字段。（不可以分割）

2. 第二范式（2NF）：在1NF的基础上，确保表中的每个非主键列完全依赖于所有主键的组合，而不是仅依赖于某一部分主键。即要求每个非主键列与全部主键直接相关，而不是与只部分主键相关。（不可以部分依赖）

3. 第三范式（3NF）：在2NF的基础上，确保表中的每个非主键列不存在传递依赖关系。即不允许非主键列依赖于其他非主键列。（不可以传递依赖）

通过遵守这三种范式，可以减少数据冗余，并确保数据的一致性和完整性。范式化的数据库设计通常能够提高数据的查询效率和维护性。

---

事务

概念: 一组不可分割的数据库操作, 要么全执行, 要么全不执行

ACID四大特性

1.原子性（atomicity)

        事务内的操作是一个整体, 要么执行成功, 要么执行失败

2.一致性（consistency)

        事务执行前后, 数据库状态保持一致

        以银行转账事务事务为例。在事务开始之前，所有账户余额的总额处于一致状态。在事务进行的过程中，一个账户余额减少了，而另一个账户余额尚未修改。因此，所有账户余额的总额处于不一致状态。事务完成以后，账户余额的总额再次恢复到一致状态。

3.隔离性（isolation）

        多个事务并发时, 事务之间不能相互影响

4.持久性（durability）

        事务一旦执行成功,数据库会保证事务处理一定会持久化到数据库中

---

执行事务的语法和流程

开始事务 BEGIN;或者START TRANSACTION;

回滚（撤销）事务 ROLLBACK;

提交事务 COMMIT;        事务提交之后不可回滚

-- 开启事务
BEGIN;	-- START TRANSACTION
delete from student;
SELECT * from student;

-- 回滚（撤销）事务
ROLLBACK;

-- 提交事务 COMMIT
COMMIT;

select @@global.transaction_isolation; -- 默认事务隔离级别，可重复读
END;

---

并发访问数据混乱

1.脏读

        一个事务读取到别的事务修改但没有提交的记录

例如:数据表中一条记录值为v1, 事务A执行, 将值改为v2, 但并没有提交, 此时事务B读取, 如果读取到的记录值为v2, 则为脏读

2.幻读

        当事务不是独立执行时出现的一种现象，读取了别的事务已经增删了的数据

        前后读取个数不一致（没在表上加锁）

例如:A事务读取或检索了多条数据,B事务添加或删除了一条新的数据 并且提交了B事务,A事务再去读取相同条件的内容,就会读取到B事务中新提交的数据,好像出现了幻觉一样 即为幻读

3.不可重复读

        第二次读取时数据已经被另一个数据修改并提交，一个事务两次读取的记录数据不一致

        （没在数据上加锁）

例如: 事务A开启, 查找数据表记录r1, 并未提交, 此时事务B修改记录r1, 并提交, 事务A再次查找数据表记录r1, 如果两次得到的r1不一致, 即为不可重复读

---

事务的隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交(read-uncommitted)	是	是	是
读已提交(read-committed)	否	是	是
可重复读(repeatable-read)	否	否	是
串行化(serializable)	否	否	否

读未提交：允许脏读, 幻读, 不可重复读

读已提交：给数据记录加行级读锁, 在更新记录前, 将记录锁定, 不允许其它事务操作

可重复读：给数据记录加行级写锁, 当对记录操作时, 其它事务只能等待

串行化：给数据表加写锁, 一个事务对数据表操作时, 不允许其它事务访问

---

锁的类型：

事务并发处理类似于线程并发的同步处理, 都是通过锁来实现的,MySQL中的锁是在服务器层或者存储引擎层实现的，保证了数据访问的一致性与有效性。

乐观锁：乐观地认为一个事务在访问数据库时, 不会与其它事务发生冲突, 一旦了生冲突, 提交给用户, 由用户处理

悲观锁：悲观地认为一个事务的处理过程, 总是受到其它事务的影响

        共享锁（读锁）：一个事务在访问数据库时, 允许其它事务的只读操作, 不允许写操作

        排他锁（写锁）：一个事务在访问数据库时, 不允许其它事务的任何操作

---

索引

...

数据库优化

...

---

JDBC

JDBC（Java Database Connectivity）是Java语言与关系型数据库进行通信的标准接口。JDBC API提供了一组用于连接、查询和操作数据库的方法和类。

需要先导入链接MySQL所需的jar库

定义地址和驱动类等：

        String url="jdbc:mysql://localhost:3306/easydata";//定义地址
        String username="root";
        String password="123123";
        String driverClassName="com.mysql.cj.jdbc.Driver";//驱动类

JDBC链接数据库的六个步骤：

1>加载驱动

2>创建链接

3>获取执行对象

4>执行SQL语句

5>处理结果集

6>关闭链接

        //1>加载驱动
        try {
            Class.forName(driverClassName);
        } catch (ClassNotFoundException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }

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