您的位置：首页 > 娱乐 > 八卦 > MySQL进阶

MySQL进阶

2024/12/23 5:50:12 来源：https://blog.csdn.net/qq_63161848/article/details/140814819  浏览:  次   关键词：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值，实现和前面类似的操作。

数据类型

常用数据类型

整数类型

类型TINYINTSMALLINTMEDIUMINTINTBIGINT
字节数12348

浮点型

        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 THENLEAVE 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();}}}

版权声明:

本网仅为发布的内容提供存储空间，不对发表、转载的内容提供任何形式的保证。凡本网注明“来源：XXX网络”的作品，均转载自其它媒体，著作权归作者所有，商业转载请联系作者获得授权，非商业转载请注明出处。

我们尊重并感谢每一位作者，均已注明文章来源和作者。如因作品内容、版权或其它问题，请及时与我们联系，联系邮箱：809451989@qq.com，投稿邮箱：809451989@qq.com

最新新闻

热搜词

密码应用建设方案通用模板 没有队名-第五次作业 @rushstack/eslint-patch 这个包的作用是什么 建设世界级物联网产业基地 影院订票系统/电影院售票系统/电影院购票系统的设计与实现/影院管理系统 什么是静态站点生成器，有哪些特点

声明：本站所有新闻及新闻图片来源于其他网站，如有侵权，请及时联系我们！

客户服务 | 关于我们 | 版权声明

版权所有：

Copyright 2024 尧图网 All Rights Reserved.QQ:809451989