MySQL学习笔记3

今日内容:

mysql:  数据类型   整数 5种   字符型 char varchar text   时间  

视图  函数  常用函数   触发器  存储过程   事务(特性   事务隔离级别  并发问题)    三范式  JDBC连接数据库的6个步骤

行列转换

行列转换  group by 根据一个或多个列对结果集进行分组

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

原表格

变化后

原理：先以人名为筛选条件得出一个人的所有成绩，将指定科目外的科目成绩记为0，然后求和，最后结果只会是想要的科目的成绩。然后将其放于以该科目为别名的那一列下，有几个科目重复几次该操作最后得到行列转换。

等同于if三元函数

select * , sum(if (subject ='英语',fraction,0))from t_score;

如果想用avg则

select * ,avg( if (subject ='英语',fraction,null))from t_score;

数据类型

常用的数据类型

整数类型 tinint(1字节) smallint(2字节) 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

如果要存储长文本 使用text类型代替

char 和 varchar 的区别

1.char定长的 varchar变长的

  char(20)存了abc 占20个字符位

varchar(20)变长的 abc占3个自负位

2.char的性能更加优异  varchar有两个字节记录长度使用是有一便运算要计算字符数

3.应用场景不同 在需要存储字符串的长度固定时用char列如身份证号 学号 手机号等    存储介绍信息，店名，地址，姓名等时 使用varchar

test 长文本 不需要记录长度

日期  

date 只能记录年月日  

time 记录时分秒

datetime 年月日时分秒

视图

视图 view 是一个已经编写好的sql语句

创建视图  起名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

编译  执行指令

删除使用drop view<视图名>as <select语句>

定制数据用户，聚焦特定数据，提高数据的安全性。更改数据格式

函数  now  max聚合函数  if()

自定义函数

传入一个数值作为成绩，60以上及格以下不及格

判断score的数值  定义一个变量记录返回结果 DECLARE(局部变量)

create function method(score int) returns varchar(20) begin

DECLARE result varchar(20);

if score>=60 then #if...then语法

 set result = '及格';

else

 set result = '不及格';

end if ;

 return result;#具体要返回的值return  返回什么类型returns

end;

set global log_bin_trust_function_creators=TRUE;

设置好后调用

SELECT method(80);

会得到

select *,method(score) from sc

会得到指定表格中数据的回馈结果

对变量赋值 两种语法

常用的函数：

触发器

触发器  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;#删除数据



create trigger tgg_u_b_student before update on student for each row begin

#old.列名  原来数据

#new.列名  新的数据

Update log set val=

CONCAT(str1CONCAT('{',old.sid,',',old.sname,'}'),'->',CONCAT('{',new.sid,',',new.sname,'}')) WHERE

`key`='lastupdate';

end;



drop trigger tgg_u_b_student;

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

存储过程

存储过程  procedure

CREATE procedure pro_insert_student_log(in num int)

BEGIN

事务

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

特性

事务的隔离级别

三范式

1.每一列的数据不可分割的（不可以在分割）

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

3.不可以传递依赖

JDBC连接数据库的6个步骤

首先要导入所需要的jar包

String url = "jdbc:mysql://localhost:3306/easydata";//定义链接地址，最后一个/后面是数据库的名字

String username = "root";//用户名

String password = "123456";//密码

String driverClassName = "com.mysql.cj.jdbc.Driver";//在包上可以直接找到驱动包

第一步加载驱动 创建链接：

try {

  Class.forName(driverClassName);

} catch (ClassNotFoundException e) {

 // TODO Auto-generated catch block

   e.printStackTrace();

}



        Connection con = null;

        Statement sta = null;

        try {

            //2.创建链接，会有IO流要关

            con = DriverManager.getConnection(url, username, password);

            //3.获取执行对象，也有IO流

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

                }

            }

        }