一.行列转换
原表格样式:
进行行列转化为下面的格式:
首先,对每个人的名字进行分组查询:语文,数学,英语并各自生成新的一列,通过下面的命令:
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();
}
}
}
}
}