努力努力努力的第十四天（2024.7.31）

昨天日期写错了写成2020.7.30,应该是2024.7.31（手滑了哈哈哈）

1.行列转换

效果演示：

这是未经行列转换操作的t_score表：

这是经过行列转换后的t_score表：

第一步：确定初步的做法

使用分组查询(group by)能够将单个学生的成绩依次查询出来，再加上三列查询(分别定义成'语文' '数学' '英语' )

SELECT name,1 as '语文',2 as '数学',3 '英语' from t_score GROUP BY name

 第二步：对语数英三列中填入数据

case...when...then...

使用这种方式能特别查询出语文成绩（其他成绩格为空）、数学、英语。

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

 第三步：利用聚合函数（求和 / 取平均值）得到最终的形式

将第二步查询到的数据通过聚合函数进行单列求和，再放到第一步中写的1、2、3位置中

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 

2.if函数

if函数，本质是三元表达式 

SELECT name,avg(if(subject='语文',fraction,null))as '语文' ,
avg(if(subject='数学',fraction,null)) as '数学',
avg(if(subject='英语',fraction,null)) as '英语'
FROM t_score GROUP BY name

3.MySQL中的数据类型 

常用的数据类型：整数型，浮点型，字符串，日期型

1.整数型

tinyint(1)    smallint(2)    mediumint(3)    int(4)    bigint(8)

括号内是指的需要的字节数

2.浮点型

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

3.字符串

字符串类型有   char        varchar         text

1.char

char(最大长度是255)

2.varchar

varchar能够存储的字节数是65535
--    1.结构问题，varchar类型的第一个字节不存储数据
--    2.varchar前两个字节要存储数据长度
--    3.所以有效位数剩下65532，编码格式决定能够存储多少个字符
--    4.行中列的总字节长度不能超过65535

3.char 和 varcahr 的区别：

--  1.char是定长的  varchar是变长的 
--  char(20) 存了abc 占20个字符位，varchar(20) 存abc 占3个字符位
--  2.char的性能更优异  varchar要先经过计算，所以性能稍差
--  3.身份证号  学号  手机号  订单号    用char 
--    介绍信息  店名  姓名  地址            用varchar

4.text 

text负责记录长文本，不需要记录长度

4.日期型

date  年月日

time  时分秒

datetime  年月日时分秒

4.视图  view

视图是已经编译好的sql语句，可以理解为：

在查询中编写的SQL语句后，执行查询查出的表的格式

1.创建视图：

视图中是没有数据的，即这些数据都是sql语句执行完查询，显现在视图上的。

视图中不存储数据，数据还是存储在表当中

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

2.查询视图： 

 

5.触发器   trigger

触发器   trigger

相当于一个事件，一旦表中发生了指定的事件，该触发器就会自动运行
触发器只能对三种操作起作用：增 删 改
触发器的触发时机：before  after

1.创建触发器：

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表中插入一条数据，都会在log表中添加一条 val 数据，这里用聚合函数count来记录了student表中数据的个数

（注意：log表中只有两列，第一列是key数据类型是varchar(20)，第二列是val数据类型是varchar(20)，key用来限定where查询的数据，val是要记录的值）

2.实例：

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 ;

 每当更改student表中的数据时，都会记录更改前的值和更改后的值，利用concat函数拼接字符串，最后输出出来

3.删除触发器 

drop trigger tgg_u_b_student

4.注意：尽量不要使用触发器！！！

尽量不要用触发器，会影响正常的业务逻辑

6.函数

1.自定义函数

先将一个常数设定为true

set global log_bin_trust_function_creators=TRUE;

自定义一个函数，用来判断成绩score是否及格 

create function method(score int) returns varchar(20)
BEGIN
-- 判断score的数值 60分以上及格  否则不及格-- 定义一个局部变量来记录返回结果declare result varchar(20) ;IF score>=60 then -- 对变量赋值  有两种语法set result='及格';ELSEset result='不及格';END IF;return result;
END ;select method(80);
-- 函数返回80是及格的

将自定义的函数用于实际查询：

 2.数字函数

向下取整floor

select floor(12.99)

3.字符串函数

字符长度、字节长度  char_length()    length()

select CHAR_LENGTH('你好'),LENGTH('你好')
--         字符数           字节数

对字符串从左(右)进行截取     left('字符串',截取长度)    right('字符串',截取长度)

select left('12345678',3),right('12345678',4)

拼接字符串    concat( , , )

SELECT concat(left('13366668888',3),'****',RIGHT('13366668888',4))

替换字符串元素   replace('字符串','字符串中要替换的元素','要替换成什么')

SELECT REPLACE('12341234','1','a')

 截取字符串    substr('字符串' from 从第几项 for 截取长度)

-- 截取字符串，从第2项开始，共截取3个字符
select SUBSTR('abcdefg' from 2 for 3)
SELECT SUBSTR('abcdefg' FROM 2)

4.日期函数  

SELECT now(),SYSDATE()
SELECT DATE_FORMAT(NOW(),'%Y')
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H-%i-%s')

7.存储过程     procedure

loop循环，需要使用leave跳出

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 pro_insert_student_log(8)

8.三范式

三范式非常重要！！！！！

1.每一列的数据是不可分割的（不可以再分割）
2.每一列的数据完全依赖主键（不可以部分依赖）
3.不可以传递依赖

9.事务 

事务：一组不可分割的数据库操作，要么全执行，要么全不执行

BEGIN;
delete from student;
select*from student;
ROLLBACK;

在begin(开始)和rollback(回滚)之间是事务的内容（如查询表、删除表）

10.ACID四大特性：

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

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

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

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

11.JDBC 

JDBC链接数据库

先将jar文件放到IDEA的文件夹下（新建一个就可以），将文件add to library

然后新创建一个类，写如下代码：

public class JDBC {public static void main(String[] args) {//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) {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){e.printStackTrace();}finally {//6.关闭链接if(sta!=null) {try {sta.close();} catch (SQLException e) {e.printStackTrace();}}if(con!=null) {try {con.close();} catch (SQLException e) {e.printStackTrace();}}}}
}

于数据库连接共有6步操作;

1.加载驱动

2.创建连接

3.获取执行对象

4.执行SQL语句

5.处理结果集

6.关闭连接