MySQL学习记录（Day3）

最新推荐文章于 2024-09-08 22:38:01 发布

何容与

最新推荐文章于 2024-09-08 22:38:01 发布

阅读量808

点赞数 17

文章标签：学习数据库 mysql eclipse

本文链接：https://blog.csdn.net/qq_62499218/article/details/140830384

版权

一、行列转换

将t_score 表的数据格式惊醒修改

首先按照名字将成绩分组，然后建立三列分别为语文、数学、英语，只有对应的科目才会有分数，其余全为0，最后每组的每列成绩相加就是学生的一科成绩。

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 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;

二、数据类型

常用的数据类型

整数类型

tinyint(一个字节) smallint(两个字节) mediumint(3)int(4) bigint(8)

浮点型

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

字符串

char varchar text longtext
char 最大长度是255
varchar 能够存储的字节是65535

1.结构问题varchar类型的第一个字节不存储数据

2.varchar前两个字节存储数据长度

3.有效位就剩下65532 编码格式决定能够存储多少字符

4.行中列的总字节长度不能超过65535

char与varchar的不同

1.char是定长的，varchar是变长的 char（20）存了abc 占20个字符位

varchar(20) 变长的 abc 就是占三个字符位节省一些空间

2.char的性能更好一些 varchar稍逊因为要计算字符数

3.场景：身份证号，学号，手机号，订单号定长的用char

介绍信息、姓名、地址用varchar

text 长文本不需要设置长度

日期

date 年月日 time 时分秒毫秒 datetime 年月日时分秒

三、视图

view 是一个已经编译好的SQL语句

1.创建视图 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;

四、触发器 trigger

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

2.触发器可以通过三种操作触发增删改

3.触发时机 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;

当数据发生改变时，日志打印的内容是修改前的数据和修改后的数据

old.列名原来的数据
new.列名新的数据

删除触发器

drop trigger 触发器名字;

总结：

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

五、自定义函数

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;

删除自定义方法

drop FUNCTION 方法名;

常用函数方法

（1）向下取整函数

select floor(12.99);

（2）字符串中字符数和字符串所占的字节数

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

运行结果

（3）在字符串中取值

-- 从左边开始取值
select left ('123456',3);
-- 从右边开始取值
select RIGHT('123456',3);

示例：手机号的保密

-- 手机号
select concat(left('12345678901',3),'****',RIGHT('12345678901',4));

输出结果为

（4）去除空白格

select trim('     123     ');

（5）替换字符串中的某一元素

select replace('123123123','1','A');

（6）截取字符串片段

select SUBSTRING('abcdefg'from 2 for 4);

select SUBSTR('abcdefg' from 2);

第一个是从第2位开始截取一直到第四位

第二个是从第二位开始到最后

结果为

（7）字符串内容改变顺序

select reverse('qwertyuiop');

输出结果为

（8）获取当前日期

select now(),sysdate();

运行结果

（9）改变日期显示格式

select DATE_FORMAT(now(),'%y-%m-%d %H:%i:%S');

六、存储过程 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;Z
end loop aa;
end;

首先定义一个pro_insert_student_log名字的存储过程，定义一个int类型的参数，在内部定义三个变量，开始标签为aa的死循环，将student表中学生的sname写进log表中，并生成随机数。一直循环直到达到条件，用leave跳出循环。

下面是测试结果

call pro_insert_student_log(3);

取三个同学的名字

七、三范式

1.每一列的数据是不可分割的

2.每一列数据完全依赖主键（不可以部分依赖）

3.不可以传递依赖

八、事务

begin;-- 开始

delete from student;

select * from student;

rollback;-- 回滚

select @@global.transaction_isolation;

利用这个代码可以查看电脑的隔离级别

九、JDBC

JDBC链接数据库的六个步骤

package com.easy;
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链接数据库的六个步骤
	  String url="连接数据库的地址";
	  String username="用户名";
	  String password="密码";
	  String driverClassName="com.mysql.cj.jdbc.Driver";
	  //1.加载驱动
	 try {
		Class.forName(driverClassName);
	} catch (Exception 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) {
		e.printStackTrace();
	}finally {
		//6.关闭连接
		if(sta!=null) {
			try {
				sta.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(con!=null) {
			try {
				sta.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
  }
}