MySQL和JDBC

1.行列转换

行列转换要实现可以将某一项的成绩单独拿出来，然后其他的里面全部为0，这样将几项全部加起来也还是原来的值，这样再把成绩给另一列就可以完成行列转换

可以使用case when then的方法，也可以使用if函数表达式

SELECT *,IF(SUBJECT='语文',fraction,0) FROM t_score

这个也可以实现将某一匹配的结果搜索出来

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.mysql数据类型

整数型： tinyint(一个字节)  smallint(两个字节)  mediumint(三个字节)int(四个字节) bigint(八个字节)

浮点型：double(总长度,小数位数) float(总长度,小数位数)  decimal(总长度,小数位数)

字符串类型：char  varchar  text

char的最大长度是255

varchar能存储的字节数是65535，
   1.结构问题，varchar类型的第一个字节不存储数据
   2.varchar前两个字节要存储长度
   3.有效位就剩下65532   编码格式决定了能够存储多少个字符
   如果是mb4就除以4，mb3就除以3
   行中列的总字节长度不能超过65535，如果要存储长文本使用text类型代替

 -- varchar和char的区别：1.varchar能记录的数据量更多
                        -- 2.char是定长，varchar是可以变化的
                              --  原因：char(20) 如果存储了abc，那就一定占20个字符位 

                                varchar(20),如果存储了abc，那就占3个字符位，abcd占四个（节省空间） 
                        -- 3.char的性能更好一点，varchar稍逊一些，因为要计算字符数

                      4.身份证号，学号，手机号，订单号，姓别（男，女）等一般用char，介绍信息，店名，姓名，地址一般用varchar

text    长文本   不需要设置长度

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

3.视图

视图view   是一个编译好的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;

创建一个视图之后，这个视图并不会存储数据，只是从数据库当中查询出来数据，试图是一个虚拟的表

但是通过视图存储数据之后可以加快执行效率（是一个编译好的sql语句）

视图的优点：1.定制用户数据，聚焦特定的数据，

2.简化数据操作

3.提高数据的安全性

4.共享所需数据

5.更改数据格式

4.触发器

触发器是再建立触发器之后，只要对表有了增删改的其中之一的操作就会自动触发，是被动执行的，相当于一个事件

触发器用来做日志比较多（但是要少用触发器，尽量不用触发器，会影响正常业务逻辑，可以让java逻辑完成触发器工作） 

触发器有两个触发时机：before，after

下面是创建一个视图，在完成插入对student表每一行中的任意一行操作之后，就更新现在student中一共的数据量，并且更新在log表中key是studentcount的上面

CREATE TRIGGER tgg_i_a_student AFTER INSERT on student for each ROW BEGIN 
UPDATE log set VALUE=(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 VALUE=concat(CONCAT('{',old.sid,',',old.sname,'}'),'->',CONCAT('{',new.sid,',',new.sname,'}'))
 WHERE `KEY`='lastupdate';
END;
DROP TRIGGER tgg_u_b_student

5.函数

1.自定义函数

现在已经学过的函数：now(),聚合函数（max,min,avg,count,sum),  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;


SELECT *,method(score) from sc;
select method(80);

最后两行都可以作为调用method方法的方式 

2.字符数

SELECT CHAR_LENGTH('你好');

3.字节数

SELECT LENGTH('你好');

4.截取字符串

select concat( LEFT('13954266219',3) ,'****',
RIGHT('13954266219',4)) 

5.取消字符串前后空格

SELECT LTRIM('   123   ')

6.替换

SELECT REPLACE('dsjkfsdjkf','d','1213')

7.substr截取字符串

不是从下标开始，是直接从第二个开始
 

SELECT SUBSTR('131312123'from 2 FOR 4)

SELECT SUBSTR('abcdefg' from 2)

8.颠倒字符串

SELECT REVERSE('dkjsfhsjkdf');

9.时间

SELECT SYSDATE();
SELECT DATE_FORMAT(NOW(),'%Y,%m,%d,%H,%i,%S，%f')-- 时间

insert into staff(id....inserttime) VALUE(....NOW())

10.向下取整

SELECT FLOOR(4.99999)

6.存储过程

存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。当以后需要数据库提供与已定义好的存储过程的功能相同的服务时，只需调用“CALL存储过程名字”即可自动完成。

create PROCEDURE pro_insert_student_log(IN num int)
BEGIN
  -- 循环语句  三种 
  -- 1.loop死循环 需要使用leave跳出循环
  DECLARE i int DEFAULT 0;
  DECLARE stuname VARCHAR(20);
  DECLARE stuscore int ; 
  aa:LOOP
  -- 获取学生的名字，生成随即成绩插入数据库log表
  SELECT Sname INTO stuname FROM student LIMIT i,1;
  set stuscore=FLOOR(RAND()*100);
  INSERT INTO log value(stuname,stuscore);
  set i=i+1;
  IF i>=num THEN
  LEAVE aa;
  END IF;
  END LOOP aa;
  END;
  
  CALL pro_insert_student_log(8)

7.三范式

三范式
  -- 第一范式.每一列数据不可分割（不可以在分割）
  -- 第二范式.每一列数据完全依赖主键（不可以部份依赖）
  -- 第三范式.不可以传递依赖

8.事务

当作一件事情处理，要不然都成功，要不然都失败

事务有start transction/begin 开始事务，commit提交事务，rollback回撤事务

begin;
DELETE FROM student

END;
SELECT * from student
ROLLBACK

并发访问数据混乱
 

   脏读
        一个事务读取到了另一个事务修改未提交的记录
        例如:数据表中一条记录值为v1, 事务A执行, 将值改为v2, 但并没有提交, 此时事务B读取, 如果读取到的记录值为v2, 则为脏读
    幻读
        当事务不是独立执行时出现的一种现象
        例如:A事务读取或检索了多条数据,B事务添加或删除了一条新的数据 并且提交了B事务,A事务再去读取相同条件的内容,就会读取到B事务中新提交的数据,好像出现了幻觉一样 即为幻读 
    不可重复读
        一个事务两次读取的记录数据不一致
        例如: 事务A开启, 查找数据表记录r1, 并未提交, 此时事务B修改记录r1, 并提交, 事务A再次查找数据表记录r1, 如果两次得到的r1不一致, 即为不可重复读

ACID四大特性

    原子性（atomicity)
        事务内的操作是一个整体, 要么执行成功, 要么执行失败
    一致性（consistency)
        事务执行前后, 数据库状态保持一致
        以银行转账事务事务为例。在事务开始之前，所有账户余额的总额处于一致状态。在事务进行的过程中，一个账户余额减少了，而另一个账户余额尚未修改。因此，所有账户余额的总额处于不一致状态。事务完成以后，账户余额的总额再次恢复到一致状态。
    隔离性（isolation）
        多个事务并发时, 事务之间不能相互影响
    持久性（durability）
        事务一旦执行成功,数据库会保证事务处理一定会持久化到数据库中

9.JDBC

在这之前要有连接数据库的文件，先创建文件夹，将此文件放入文件夹，然后将此文件配置到构建路径

public class JDBC {
	public static void main(String[] args) {
		//六个步骤
		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 自动生成的 catch 块
			e.printStackTrace();
		}
		
		Connection con=null;
		Statement sta=null;
		try {//2>创建链接
			 con=DriverManager.getConnection(url,username,password);
			 //3>获取执行对象
		sta=con.createStatement();
		//4>执行sql语句
		int count =sta.executeUpdate("delete from student where sid=14");
		//5>处理结果集
		if(count>0) {System.out.println("成功");}
		else {
			System.out.println("失败");
		}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			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();
				}
			}
		}
	}

}