Mysql学习-day15

Mysql学习-day15

1. 行列转换

在MySQL中，行列转换可以通过使用CASE语句结合聚合函数来实现。

表t_score数据如图所示

我们想要以学科为列名，展示每个学生的科目成绩，可以先用CASE语句来选出每科的成绩，再进行求和。

选择科目时，如果是我们想要选择的科目，则成绩就是对应的fraction，如果是其他科目则设置为null

比如下面我们选择语文成绩

select * , 
case subject
when '语文'
then fraction
else null
end '语文'
from t_score;

将筛选出来的成绩以name进行分组，再求和，将求和的结果设置为新的列就完成了行列转换

SELECT NAME,
	sum( CASE SUBJECT WHEN '语文' THEN fraction ELSE null END ) '语文',
	sum( CASE SUBJECT WHEN '数学' THEN fraction ELSE null END ) '数学',
	sum( CASE SUBJECT WHEN '英语' THEN fraction ELSE null END ) '英语' 
FROM t_score 
GROUP BY NAME;

也可以使用if函数来实现行列转换

SELECT NAME,
	sum(IF( SUBJECT = '语文', fraction, NULL )) '语文',
	sum(IF( SUBJECT = '数学', fraction, NULL )) '数学',
	sum(IF( SUBJECT = '英语', fraction, NULL )) '英语' 
FROM t_score 
GROUP BY NAME;

2. 常有数据类型

MySQL数据库支持多种数据类型，以满足不同的数据存储需求。以下是MySQL中的主要数据类型分类及其特点：

1. 数值类型：
   • 整数类型：包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT等，用于存储不同范围的整数。
   • 浮点数类型：包括FLOAT和DOUBLE，用于存储小数。DOUBLE(总长度，小数位数)
   • 定点数类型：DECIMAL，用于存储精确的小数。
2. 字符串类型：
   • CHAR和VARCHAR：用于存储定长和变长的字符串。
   • TEXT，LONGTEXT和BLOB：用于存储大量的文本或二进制数据。
3. 日期和时间类型：
   • YEAR、DATE、TIME、DATETIME、TIMESTAMP等，用于存储日期和时间信息。
4. 二进制类型：
   • BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB等，用于存储二进制数据。
5. 特殊类型：
   • ENUM：用于存储预定义的值集合。
   • SET：用于存储一组可选的值。
   • JSON：用于存储JSON格式的数据。

整数类型

• TINYINT 1字节
• SMALLINT 2字节
• MEDIUMINT 3个字节
• INT 4字节
• BIGINT 8字节

字符串类型

CHAR最大长度是255字符

VARCHAR能够存储的字节数是65535

• 1.结构VARCHAR类型的第一个字节不存储数据
• 2.VARCHAR前两个字节存储数据长度
• 3.有效位就剩下65532,如果使用多字节字符集（如utf8mb4），实际存储的字符数量可能会少于65532个。因为每个字符可能占用多个字节。
• 4.行中列的总字节长度不能超过65535，如果要存储长文本，使用TEXT类型代替

CHAR和VARCHAR的区别

1.CHAR是定长的，VARCHAR是变长的

• CHAR(20) 存储abc 占20个字符位
• VARCHAR是变长的 存了abc 占了三个字符位 可以节省空间

2.CHAR的性能更好一些 VARCHAR性能不如CHAR，因为要计算字符数

3.身份证号 学号 手机号定长的数据都可以使用CHAR
介绍信息 店名 姓名 地址使用VARCHAR

日期时间类型

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

3. 视图

MySQL 视图（View）是一种虚拟存在的表，同真实表一样，视图也由列和行构成，但视图并不实际存在于数据库中。行和列的数据来自于定义视图的查询中所使用的表，并且还是在使用视图时动态生成的。

创建视图

CREATE VIEW <视图名> AS <SELECT语句>

删除视图

DROP VIEW <视图名>

student表

course表

sc表

创建视图，获取每个学生的各科成绩

-- 创建视图
create view v_student_score as
(select s.sname, c.cname, sc.score
from student s, course c, sc
where s.sid = sc.sid
and sc.cid = c.cid);

再次获取学生的各科成绩时就可以直接查询视图

-- 视图中不存储数据  数据还是存储在表当中
select * from v_student_score;

-- 等同于⬇
select * from 
(select s.sname, c.cname, sc.score
from student s, course c, sc
where s.sid = sc.sid
and sc.cid = c.cid) t;

视图的优点

• 1.定制用户数据，聚焦特定的数据
• 2.简化数据操作
• 3.提高数据的安全性
• 4.共享所需数据

4. 触发器

是嵌入到 MySQL 中的一段程序,通过对数据表的相关操作来触发、激活从而实现执行。比如当对 student 表进行操作（INSERT，DELETE 或 UPDATE）时就会激活它执行。

创建触发器

CREATE TRIGGER <触发器名> < BEFORE | AFTER ><INSERT | UPDATE | DELETE > ON <表名> FOR EACH Row BEGIN  <触发器主体>  END

删除触发器

DROP TRIGGER [ IF EXISTS ] [数据库名] <触发器名>

创建一个触发器，如果student表执行插入操作就更新log中记录student表的行数

create trigger tgg_i_a_student after insert
on student for each row begin
update log set val=(select count(*) from student) where `key` = 'studentcount';
end;

当student表执行插入操作时，就会将student的新行数存入log表中

使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

触发器类型	NEW 和 OLD
INSERT	NEW 表示将要或者已经新增的数据
UPDATE	OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
DELETE	OLD 表示将要或者已经删除的数据

创建一个触发器，当对student表进行update操作时，将原来的数据和新数据添加到log表中

create trigger tgg_u_b_student before update
on student for each row
begin
-- old.列名  原数据
-- new.列名  新数据
-- mysql中字符串拼接使用concat
update log set val = concat(concat('{',old.sid, ',', old.sname,'}'),' -> ',CONCAT('{',new.sid, ',', new.sname,'}'))
where `key` = 'lastupdate';
end;

5. 函数

5.1 自定函数

在mysql8.0版本使用自定函数需要先执行以下语句，否则会报错

set global log_bin_trust_function_creators=TRUE;

创建函数

Create function function_name(参数列表) returns 返回值类型 BEGIN <具体实现> return 返回值 END

创建一个函数，获取一个int值，判断该int值是否大于等于60，如果大于等于60则返回及格，如果小于60则返回不及格

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;

5.2 常用函数

除了之前提过的max，min，sum，avg，count之外还有以下常用的的函数

函数名称	作用
ABS	求绝对值
SQRT	求二次方根
CEIL,CEILING	向上取整
FLOOR	向下取整
RAND	产生获取[0,1)之间的float型的数字
ROUND	四舍五入
LENGTH	返回字符串的字节长度
CHAR_LENGTH	返回字符串的字符个数
TRIM	删除字符串左右两侧的空格
LEFT	从左侧截取字符串，返回字符串左边的若干字符
RIGHT	从右侧截取字符串，返回字符串右边的若干字符
REPLACE	字符串替换函数，返回替换后的新字符串
SUBSTRING	截取字符串，返回从指定位置开始的指定长度的字符串
REVERSE	返回反转后的字符串
NOW,DATE	返回当前时间
DATE_FORMAT	设置时间格式

-- CHAR_LENGTH返回字符数    LENGTH返回字节数
select CHAR_LENGTH('你好'), LENGTH('你好');-- 2   6

select concat(LEFT('15145627495', 3),'****',RIGHT('15145627495', 4));-- 151****7495

select TRIM('      1  23    ');-- 1  23

select REPLACE('1231321321','1','a');-- a23a32a32a

select SUBSTRING('123456' from 2 for 5);-- 23456

select SUBSTRING('123456' from 3);-- 3456

select REVERSE('123456');-- 654321

select now(), SYSDATE();-- 2024-07-31 18:44:40   2024-07-31 18:44:40

select DATE_FORMAT(now(),'%Y-%m-%d-%H-%i-%S')-- 2024-07-31-18-44-40

6. 存储过程

存储过程是事先经过编译并存储在数据库中的一段sql语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程思想上很简单，就是数据库sql语言层面的代码封装与重用。

存储过程语法

DELIMITER //  
  
CREATE PROCEDURE CalculateSquare(IN num INT, OUT result INT)  
BEGIN  
    SET result = num * num;  
END //  
  
DELIMITER ;

DELIMITER 用于更改命令结束符，以便在存储过程中使用 BEGIN … END 语句。通常，我们使用 // 作为新的结束符，并在存储过程定义结束后将其改回 ;。

调用存储过程需要使用call语句

SET @input = 5;  
SET @output = 0;  
  
CALL CalculateSquare(@input, @output);  
  
SELECT @output;  -- 输出应该是 25

-- 存储过程 PROCEDURE
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
	-- 获取学生的名字，生成随机成绩 插入log表
	  select sname into stuname from student limit i,1;
	  set randomS = FLOOR((RAND()*100));
	  insert into log value(stuname,randomS);
	  set i = i + 1; 
	  IF i >= num THEN
		  LEAVE aa; 
	  END IF; 
  END LOOP aa;
END;

7. 三大范式

MySQL中的三大范式包括‌第一范式（1NF）、‌第二范式（2NF）和‌第三范式（3NF），它们是数据库设计的基本原则，用于确保数据库的结构具有良好的数据完整性和避免数据冗余。具体介绍如下：‌

• 第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项。这意味着列中存储的数据应该是最小的、不可再分的单位，不能是集合、数组或其他复杂的数据结构。
• 第二范式（2NF）：在满足第一范式的基础上，要求非主键列完全依赖于主键。这意味着表中的所有列都必须完全通过主键来定义，不能有仅依赖于主键一部分的列。
• 第三范式（3NF）：在第二范式的基础上，进一步要求非主键列不依赖于其他非主键列。这意味着表中的每一列都应该是直接依赖于主键的，不应该存在数据冗余和不必要的数据依赖。

这些范式是逐步递进的，第二范式在第一范式的基础上建立，第三范式在第二范式的基础上建立。遵循这些范式可以帮助设计出结构清晰、易于维护的数据库结构。‌

数据库三大范式只是数据库表设计时的理论依据，最终的目的是为了满足客户的需求，有时候会选择用数据库表的冗余来换取执行速度，因为在查询过程中，多张表的连接会降低查询的效率。

8. 事务

事务: 一组不可分割的数据库操作, 要么全执行, 要么全不执行;例如: 银行卡转帐

8.1 ACID四大特性

ACID四大特性是指数据库事务的四个关键属性，包括‌原子性（Atomicity）、‌一致性（Consistency）、‌隔离性（Isolation）和‌持久性（Durability）。‌

• 原子性（Atomicity）：原子性指事务是一个不可分割的工作单位，事务中的操作要么全部执行，要么全部不执行。这确保了事务的完整性，防止了部分操作被执行而导致的数据不一致情况。

• 一致性（Consistency）：一致性指事务必须使数据库从一个一致性状态变换到另一个一致性状态。也就是说，一个事务执行前后，数据库都必须保持一致性的状态。这保证了数据的正确性和可靠性。

• 隔离性（Isolation）：隔离性指多个事务并发执行时，一个事务的执行不应影响其他事务的执行，各个事务之间互不干扰，相互独立。这防止了多个事务并发执行时产生的数据冲突和不一致问题。

• 持久性（Durability）：持久性指一旦事务提交，则其结果就是永久性的，即使发生系统崩溃或宕机等故障，数据库也能将数据恢复到提交后的状态，从而保证了数据的稳定性和安全性。

8.2 事务并发访问可能造成的数据混乱

1. 脏读

   脏读是指在一个事务处理过程里读取了另一个未提交事务中的数据。

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

2. 不可重复读

   不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。

   例如事务T1在读取某一数据，而事务T2立马修改了这个数据并且提交事务给数据库，事务T1再次读取该数据就得到了不同的结果，发送了不可重复读。

   不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。

   在某些情况下，不可重复读并不是问题，比如我们多次查询某个数据当然以最后查询得到的结果为主。但在另一些情况下就有可能发生问题，例如对于同一个数据A和B依次查询就可能不同，A和B就可能打起来了……

3. 幻读(虚读)

   事务非独立执行时发生的一种现象。

   幻读是事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。

   幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

   参考: https://blog.csdn.net/weixin_48053866/article/details/134123102

8.3 事务隔离级别

隔离级别	脏读	不可重复读	幻读
读未提交(Read uncommitted)	是	是	是
读已提交(Read committed)	否	是	是
可重复读(Repeatable read)	否	否	是
串行化(Serializable)	否	否	否

mysql默认的事务隔离级别是可重复读

9. JDBC连接数据库

Java数据库连接，（Java Database Connectivity，简称JDBC）是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。

java连接mysql

在项目目录新建lib文件夹，将对应的版本的mysql-connector-java-8.0.26.jar包放在lib中，将jar添加到Project Structure的Libraries中

JDBC连接数据库的六个步骤

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) {
        //JDBC连接数据库的六个步骤
        //url: jdbc:mysql://地址:端口号/数据库名
        String url = "jdbc:mysql://localhost:3306/easydata";
        String username = "root";
        String password = "123456";
        //驱动类
        String driverClassName = "com.mysql.cj.jdbc.Driver";
        //1.加载驱动类
        try {
            Class.forName(driverClassName);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        try(
                //2.创建连接
                Connection connection = DriverManager.getConnection(url, username,password);
                //3.获取执行对象
                Statement statement = connection.createStatement();
        ){
            //4.执行sql语句
            int rowCount = statement.executeUpdate("delete from student where sid = 15");
            //5.处理结果集
            if(rowCount > 0) {
                System.out.println("删除成功");
            }else {
                System.out.println("删除失败");
            }
        }catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            //6.关闭连接
            //使用try-with-resource可省略此步
            /*
             * if(statement != null) { statement.close(); }
             * if(connection != null) { connection.close(); }
             */
        }
    }
}