mysql笔记 以及jdbc连接

一.行列转换

原表格样式:

进行行列转化为下面的格式:

 

 首先,对每个人的名字进行分组查询:语文,数学,英语并各自生成新的一列,通过下面的命令:

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

这里也可以用if函数  三元运算表达式

例: select *,if(subject='语文',fraction,0) from t_score

会得到这样的格式:

然后对他们分别起别名 :语文 数学 英语  并对每个的成绩进行求和

根据每个人的姓名(name)分组查询

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

二.数据类型

1.常用的数据类型

-- 浮点型（后面跟着（总长度，小数位数））      
      -- double（总长度，小数位数） float   decimal
-- 字符串(字符数，不是字节数)  char  varchar text longtext 
--  char（最大长度255字符）
-- varchar(最大长度不能确定，有很多因素造成）
   -- 能够存储的字节数是65535，
        -- 1.结构问题:varchar类型的第一个字节不存储数据 
-- 2.varchar 的前两个字节存储数据长度，有效位就剩下65532字节位数--16383
-- 3.编码格式决定能够存储多少个字符（若为int就是65532/4个字符）
-- 4.行中列的总字节长度 不能超过65535个字节，如果要存储长文本，使用text类型代替

-- char 和vachar的区别
-- 1.char定长的 varchar 变长的
-- char(20) 存了abc 占20个字符位
-- vachar(20)变长的 abc占3个字符位 abcd占4个字符位
-- 2.char的性能更好一些 vachar 稍差 因为要计算字符数
-- 3.身份证号 学号 手机号 订单号 定长的都用char
-- 介绍信息 店名 姓名  地址 用 varchar

2.日期

-- 1.data 记录年月日  
-- 2.time 时分秒
-- 3.datetime 年月日时分秒

三.视图 view 是一个已经编译好的SQL语句

1.创建视图

reate 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
-- 相当于一个事件  ,一旦表中发生了指定的事件,该触发器就会自动运行
-- 触发器可以通过三种操作触发  增删改
-- 触发的时机 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;
drop trigger tgg_i_a_student

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

删除触发器

drop trigger tgg_u_b_student

五.函数   now  max聚合函数  if()

1.自定义函数

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(80);

select *,method(score) from sc 

结果:

删除函数

drop function method;

 2.floor向下取整

select floor(12.99);

3.char_length 返回字符       length 返回字节

select char_length('你好'),length('你好');

4. 截取   left从左边开始   right从右边开始

select left('123456',3),right('123456',3)

select concat(left('133666669999',3),'****',right('133666669999',4))

 

5.去掉空格  trim

select trim('    123    ')

6.替换   replace

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

7.截取字符串  

1>从第2个开始截取3个 ,这里的2指的就是第2个数不是指下标

select substr('abcdefg' from 2 for 3)

2>从第2个开始截取到最后一个

select substr('abcdefg' from 2)

8.逆序   reverse

select reverse('abcdefg')

9.获取当前日期时间

select now(),sysdate();

10.时间   年月日   时分秒

select DATE_FORMAT(NOW(),'%Y-%m-%d %H-%i-%s')

11.存储过程   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
-- 获取学生的名字  生成随机成绩 
select sname into stuname from student limit i,1;
set randoms=(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(3)

查看log表，可以发现，有三行数据被插入到该表中

六.三范式

-- 1.每一列的数据是不可分割的(不可以分割)
-- 2.每一列的数据要完全依赖于主键(不可以部分依赖)
-- 3.不可以传递依赖 

七.事务

定义:一组不可分割的数据库操作, 要么全执行, 要么全不执行;例如: 银行卡转帐

ACID四大特性:

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

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

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

4.持久性:多个事务并发时, 事务之间不能相互影响

事务并发访问数据混乱:

1.脏读

2.幻读

3.不可重复读

事务隔离级别

begin ;

delete from student;

select * from student;

rollback;-- 撤销

select @@global.transaction_isolation;

七.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) {
		//1.JDBC连接数据库的6个步骤
		String url="jdbc:mysql://localhost:3306/easydata";
		String username="root";
		String password="123456";
		String diverClassName="com.mysql.cj.jdbc.Driver";
		//1>加载驱动
		try {
			Class.forName(diverClassName);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//2>创建连接
		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 Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(con!=null) {
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}
}