MySql基础2-CSDN博客

本文链接：https://blog.csdn.net/m0_63367052/article/details/140830372

行列转换：

CASE 表达式：

在第一个查询中，使用了 CASE 表达式来根据不同的 subject 值计算不同的分数总和。语法是 CASE WHEN condition THEN result ELSE result END，它允许根据条件进行条件性的求和操作。在这里，根据 subject 的不同取值（'语文', '数学', '英语'），选择性地将 fraction 加入相应的总和中。
SUM 聚合函数：

在第一个查询中，每个 SUM 函数用于计算相应 subject 的分数总和。SUM 函数用于对指定列的值进行求和操作。通过 CASE 表达式控制哪些行的值会被包含在总和中（对应特定的 subject）。
GROUP BY 子句：

第一个查询中使用了 GROUP BY 子句，它根据 name 列对结果进行分组。GROUP BY 用于将行分组为汇总行，并对每个组应用聚合函数（在此处是 SUM 函数）。
IF 函数：

第二个查询中使用了 IF 函数来进行条件判断。IF 函数的语法是 IF(condition, value_if_true, value_if_false)，它根据条件 SUBJECT='语文' 来返回相应的 fraction 值或 0。这种方法类似于 CASE 表达式，但更适合于只有一个条件和两个结果的情况。

-- 行列转换

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 * ,IF(SUBJECT='语文',fraction,0) FROM t_score;

数据类型知识点：

-- 数据类型
-- 常用的数据类型
-- 整数类型 TINYINT（1） SMALLINT（2）MEDIUMINT（3） int（4）BIGINT（8）
-- 浮点型 DOUBLE（总长度，小数位数） FLOAT DECIMAL
-- 字符串 CHAR VARCHAR TEXT LONGTEXT
-- CHAR最大长度是255
-- VARCHAR能够存储的字节数是65535
   -- 1.结构varchar类型的第一个字节不存储数据
   -- 2.VARCHAR前两个字节存储数据长度
   -- 3.有效位就剩下65532
   -- 4.编码格式决定能够存储多少个字符（mb3，mb4...）
   -- 5.行中列的总字节长度不能超过65535
   -- 如果要存储长文本，使用text代替
-- CHAR和varchar的区别
   -- 1.CHAR是定长，VARCHAR是变长的（都不能超过最大个数）
       -- CHAR（20）存了abc，占20个字符位
       -- VARCHAR（20）存了abc，占3个字符位
   -- 2.CHAR性能更优，因为VARCHAR要计算字符数
   -- 3.运用场景：
       -- 身份证号，学号，手机号可以用char，位数确定。
       -- 介绍信息，姓名，地址可以用varchar。
-- TEXT 纯文本不需要设置长度
-- 日期
-- DATE 记录年月日 TIME 记录时分秒毫秒 DATETIME 记录年月日时分秒

视图：

视图（view）是一个已经编译好的SQL语句，创建时一般用v_加名称。

定义：

视图是一个虚拟的表，其内容由查询定义。它实际上是一个预编译的SQL查询结果集，当需要使用视图时，查询被执行并返回结果。
视图并不存储实际的数据，它只存储查询的定义。
用途：

简化复杂的查询：通过视图可以将复杂的多表联接、聚合操作封装成一个简单的视图，用户只需对视图进行查询而不必关心底层的复杂逻辑。
安全性控制：可以限制用户对数据库的访问权限，通过视图只允许用户访问特定的列或行，而不是整个表。
数据独立性：视图可以隐藏底层表结构的变化，只需调整视图的定义而不影响查询语句。
创建视图语法：

创建视图的语法类似于创建表，使用 CREATE VIEW 语句，并在后面指定视图的名称和定义查询。
使用视图：

查询视图：可以像查询表一样使用视图，例如 SELECT * FROM view_name;
更新视图：在某些数据库系统中，可以对视图进行更新操作，但这取决于视图的定义和数据库系统的支持。
更新视图：

视图可以是只读的，即不支持插入、更新和删除操作，这取决于视图的定义和数据库管理系统的设置。
性能影响：

视图并不会像表一样物理存储数据，因此对性能影响较小。然而，复杂的视图可能会导致查询性能下降，尤其是如果视图中涉及大量的联接和聚合操作。

-- 视图 VIEW 是一个已经编译好的SQL语句
-- 创建 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；
-- 编译  执行

编译器：

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

触发时机：执行增删改，before和after，new和old。

优点：

1.触发器的执行是自动的，当对触发器相关表的数据做出相应的修改后立即执行。

2.触发器可以实施比 FOREIGN KEY 约束、CHECK 约束更为复杂的检查和操作。

3.触发器可以实现表数据的级联更改，在一定程度上保证了数据的完整性。

缺点：

1.使用触发器实现的业务逻辑在出现问题时很难进行定位，特别是涉及到多个触发器的情况下，会使后期维护变得困难。

2.大量使用触发器容易导致代码结构被打乱，增加了程序的复杂性。

3.如果需要变动的数据量较大时，触发器的执行效率会非常低。

-- 触发器 TRIGGER
-- 相当于一个事件，一旦表中发生了指定的事件，触发器就会自动运行
-- 只会对三种操作起作用，增删改。
-- 触发时机 BEFORE AFTER
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;

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逻辑代码完成触发器的工作

函数：

set @@global.log_bin_trust_function_creators=1-- 开启创建函数的权限

SQL 函数：包括数学函数（如 FLOOR）、字符串函数（如 CHAR_LENGTH, LEFT, RIGHT, CONCAT, TRIM, REPLACE, SUBSTR, REVERSE）、日期函数（如 NOW(), SYSDATE(), DATE_FORMAT）。

自定义函数：使用 CREATE FUNCTION 可以在数据库中定义自己的函数，增强 SQL 的功能和复用性。

自定义函数 method
创建函数语法：使用 CREATE FUNCTION 来定义一个自定义函数。
变量声明：使用 DECLARE 来声明变量，并初始化它们。
条件判断：使用 IF 条件语句来根据不同的分数返回不同的结果。
赋值语句：使用 SET 语句给变量赋值。
返回结果：使用 RETURN 关键字返回最终的结果。
全局设置 log_bin_trust_function_creators
通过 set global log_bin_trust_function_creators=TRUE; 设置，允许创建函数时跳过对创建者权限的检查，仅在开发环境或需要时使用。

SQL 函数示例
SELECT 语句：

使用 SELECT method(80); 调用自定义函数 method 并传入参数。
使用 SELECT *, method(score) FROM sc; 查询并返回表 sc 中每条记录及其分数对应的及格状态。
使用 SELECT FLOOR(score) FROM sc; 取整数部分。
使用 SELECT CHAR_LENGTH('你好'), LENGTH('你好'); 计算字符串长度。
使用 SELECT LEFT('123456',3); 返回字符串左侧的部分。
使用 SELECT RIGHT('123456',3); 返回字符串右侧的部分。
使用 SELECT CONCAT(LEFT('12345645678',3),'****',RIGHT('12345645678',4)); 拼接字符串。
使用 SELECT TRIM(' 123 '); 移除字符串两侧的空格。
使用 SELECT REPLACE('123123123','1','a'); 替换字符串中的部分内容。
使用 SELECT SUBSTR('1234567' FROM 2 FOR 4); 返回字符串的子串。
使用 SELECT SUBSTR('1234567' FROM 2); 从指定位置截取到字符串末尾。
使用 SELECT SUBSTR('1234567',4); 从指定位置截取字符串的子串。
使用 SELECT REVERSE('asdfgh'); 反转字符串。
使用 SELECT NOW(), SYSDATE(); 返回当前日期时间。

日期格式化：

使用 SELECT DATE_FORMAT(now(),'%Y-%m-%d %H-%i-%S'); 格式化日期时间。
INSERT 语句：

使用 INSERT INTO staff(id, ..., inserttime) VALUES(..., NOW()); 将当前时间插入到数据库表中作为时间戳。

-- 函数
-- now max if 
-- 自定义函数
CREATE FUNCTION method(score int )RETURNS VARCHAR(20)
BEGIN
-- 判断score的数值，60分以上及格，否则不及格
-- 定义一个变量，记录返回结果
DECLARE result VARCHAR(20);
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;
SELECT FLOOR(score) FROM sc;
SELECT CHAR_LENGTH('你好'),LENGTH('你好');
SELECT LEFT('123456',3);
SELECT right('123456',3);
SELECT CONCAT(LEFT('12345645678',3),'****',RIGHT('12345645678',4)) ;
SELECT TRIM('   123    ');
SELECT REPLACE('123123123','1','a');
SELECT SUBSTR('1234567' FROM 2 FOR 4);
SELECT SUBSTR('1234567' FROM 2);
SELECT SUBSTR('1234567',4);
SELECT REVERSE('asdfgh');
SELECT now(),SYSDATE();
SELECT DATE_FORMAT(now(),'%Y-%m-%d %H-%i-%S');
INSERT INTO staff(id....inserttime)VALUE(......now())

存储：

存储过程创建：

使用 CREATE PROCEDURE 命令定义存储过程，并指定输入参数 num，表示需要插入的日志记录数目。
循环语句：

使用了 LOOP 和 LEAVE 实现循环操作，LOOP 开始一个循环标签为 aa 的无限循环，通过 LEAVE aa; 来跳出循环。
DECLARE i INT DEFAULT 0; 定义了一个整型变量 i，并初始化为0，用来追踪循环次数。
随机生成数据：

使用 SELECT ... INTO 查询语句从 student 表中获取学生名字 sname，并将结果存储在 stuname 变量中。
使用 RAND() 函数生成0到1之间的随机数，通过 FLOOR(RAND() * 100) 得到一个0到99的整数作为随机成绩 randomS。
插入数据：

使用 INSERT INTO log VALUES (stuname, randomS); 将随机生成的学生名字和成绩插入到 log 表中。
循环控制：

SET i = i + 1; 每次循环结束前递增 i，控制循环次数。
IF i >= num THEN LEAVE aa; END IF; 当 i 达到或超过 num 时，使用 LEAVE 语句退出循环。
存储过程结束：

使用 END LOOP aa; 结束循环体。
存储过程整体使用 END; 结束定义。
存储过程调用：

使用 CALL pro_insert_student_log(3); 调用存储过程，并传递参数 3，表示需要插入三条日志记录。

-- 存储过程
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;
DROP PROCEDURE pro_insert_student_log;

CALL pro_insert_student_log(3);

-- 三范式
-- 1.每一列的数据不可分割的（不可以再分割）
-- 2.每一列的数据完全依赖主键（不可以部份依赖)
-- 3.不可以传递依赖

事务：

四大特性：

1.原子性：事务内的操作是一个整体, 要么执行成功, 要么执行失败。

2.一致性：事务执行前后, 数据库状态保持一致。

3.隔离性：多个事务并发时, 事务之间不能相互影响。

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

事务并发访问数据混乱：

1.脏读：一个事务读取到了另一个事务修改未提交的记录。

2.幻读：当事务不是独立执行时出现的一种现象。

3.不可重复读：一个事务两次读取的记录数据不一致。

事务的隔离级别：

读未提交：SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED允许脏读, 幻读, 不可重复读。

读已提交：不允许脏读, 允许幻读, 不可重复读。方案: 给数据记录加行级读锁, 在更新记录前, 将记录锁定, 不允许其它事务操作。

可重复读：不允许脏读,不可重复读, 允许幻读。方案: 给数据记录加行级写锁, 当对记录操作时, 其它事务只能等待。

串行化：不允许脏读, 幻读, 不可重复读。方案: 给数据表加写锁, 一个事务对数据表操作时, 不允许其它事务访问。

-- 事务
BEGIN;
DELETE FROM student;
SELECT * FROM student;
ROLLBACK;

select @@global.transaction_isolation;

JDBC连接数据库的6个步骤

1.加载驱动程序：
Class.forName(driverClassName);
这一步是通过 Class.forName(driverClassName) 加载并注册MySQL数据库的JDBC驱动程序，确保可以与MySQL数据库建立连接。

2.创建连接：
con = DriverManager.getConnection(url, username, password);
使用 DriverManager.getConnection(url, username, password) 方法建立与MySQL数据库 easydata 的连接。url 包括数据库的地址和端口号，username 和 password 是数据库的登录名和密码。

3.获取执行对象：
sta = con.createStatement();
通过 con.createStatement() 创建一个 Statement 对象 sta，用于执行SQL语句并与数据库进行交互。

4.执行SQL语句：
int rowCount = sta.executeUpdate("delete from student where sid = 15");
使用 sta.executeUpdate(sql) 执行SQL语句，此处是执行一个删除操作，删除 student 表中 sid 为 15 的记录，并将影响的行数保存在 rowCount 变量中。

5.处理结果集：
根据 rowCount 的值判断SQL语句执行是否成功，并输出相应的结果信息。

6.关闭连接：
在 finally 块中关闭 Statement 和 Connection 对象，释放数据库资源。确保在使用完毕后关闭连接，以防止资源泄漏和提高数据库的性能。

package com;

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="123456";
        String driverClassName="com.mysql.cj.jdbc.Driver";
        //1加载驱动
        try {
            Class.forName(driverClassName);
        }catch (ClassNotFoundException e) {
            // TODO: handle exception
            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 =15");
            //5处理结果集
            if(rowCount>0) {
                System.out.println("删除成功");
            }else {
                System.out.println("删除失败");
            }
        }catch (SQLException e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally {
            //6关闭连接
            if(sta!=null) {
                try {
                    sta.close();
                }catch(SQLException e) {
                    // TODO: handle exception
                    e.printStackTrace();
                }
            }
            if(con!=null) {
                try {
                    con.close();
                }catch (SQLException e) {
                    // TODO: handle exception
                    e.printStackTrace();
                }
            }
        }
    }
}