昨天日期写错了写成2020.7.30,应该是2024.7.31(手滑了哈哈哈)
1.行列转换
效果演示:
这是未经行列转换操作的t_score表:
这是经过行列转换后的t_score表:
第一步:确定初步的做法
使用分组查询(group by)能够将单个学生的成绩依次查询出来,再加上三列查询(分别定义成'语文' '数学' '英语' )
SELECT name,1 as '语文',2 as '数学',3 '英语' from t_score GROUP BY name
第二步:对语数英三列中填入数据
case...when...then...
使用这种方式能特别查询出语文成绩(其他成绩格为空)、数学、英语。
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
第三步:利用聚合函数(求和 / 取平均值)得到最终的形式
将第二步查询到的数据通过聚合函数进行单列求和,再放到第一步中写的1、2、3位置中
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.if函数
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
3.MySQL中的数据类型
常用的数据类型:整数型,浮点型,字符串,日期型
1.整数型
tinyint(1) smallint(2) mediumint(3) int(4) bigint(8)
括号内是指的需要的字节数
2.浮点型
double(总长度,小数位数) float(总长度,小数位数) decimal(总长度,小数位数)
3.字符串
字符串类型有 char varchar text
1.char
char(最大长度是255)
2.varchar
varchar能够存储的字节数是65535
-- 1.结构问题,varchar类型的第一个字节不存储数据
-- 2.varchar前两个字节要存储数据长度
-- 3.所以有效位数剩下65532,编码格式决定能够存储多少个字符
-- 4.行中列的总字节长度不能超过65535
3.char 和 varcahr 的区别:
-- 1.char是定长的 varchar是变长的
-- char(20) 存了abc 占20个字符位,varchar(20) 存abc 占3个字符位
-- 2.char的性能更优异 varchar要先经过计算,所以性能稍差
-- 3.身份证号 学号 手机号 订单号 用char
-- 介绍信息 店名 姓名 地址 用varchar
4.text
text负责记录长文本,不需要记录长度
4.日期型
date 年月日
time 时分秒
datetime 年月日时分秒
4.视图 view
视图是已经编译好的sql语句,可以理解为:
在查询中编写的SQL语句后,执行查询查出的表的格式
1.创建视图:
视图中是没有数据的,即这些数据都是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
2.查询视图:
5.触发器 trigger
触发器 trigger
相当于一个事件,一旦表中发生了指定的事件,该触发器就会自动运行
触发器只能对三种操作起作用:增 删 改
触发器的触发时机:before after
1.创建触发器:
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;
每当向student表中插入一条数据,都会在log表中添加一条 val 数据,这里用聚合函数count来记录了student表中数据的个数
(注意:log表中只有两列,第一列是key数据类型是varchar(20),第二列是val数据类型是varchar(20),key用来限定where查询的数据,val是要记录的值)
2.实例:
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 ;
每当更改student表中的数据时,都会记录更改前的值和更改后的值,利用concat函数拼接字符串,最后输出出来
3.删除触发器
drop trigger tgg_u_b_student
4.注意:尽量不要使用触发器!!!
尽量不要用触发器,会影响正常的业务逻辑
6.函数
1.自定义函数
先将一个常数设定为true
set global log_bin_trust_function_creators=TRUE;
自定义一个函数,用来判断成绩score是否及格
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);
-- 函数返回80是及格的
将自定义的函数用于实际查询:
2.数字函数
向下取整floor
select floor(12.99)
3.字符串函数
字符长度、字节长度 char_length() length()
select CHAR_LENGTH('你好'),LENGTH('你好')
-- 字符数 字节数
对字符串从左(右)进行截取 left('字符串',截取长度) right('字符串',截取长度)
select left('12345678',3),right('12345678',4)
拼接字符串 concat( , , )
SELECT concat(left('13366668888',3),'****',RIGHT('13366668888',4))
替换字符串元素 replace('字符串','字符串中要替换的元素','要替换成什么')
SELECT REPLACE('12341234','1','a')
截取字符串 substr('字符串' from 从第几项 for 截取长度)
-- 截取字符串,从第2项开始,共截取3个字符
select SUBSTR('abcdefg' from 2 for 3)
SELECT SUBSTR('abcdefg' FROM 2)
4.日期函数
SELECT now(),SYSDATE()
SELECT DATE_FORMAT(NOW(),'%Y')
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H-%i-%s')
7.存储过程 procedure
loop循环,需要使用leave跳出
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=FLOOR(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(8)
8.三范式
三范式非常重要!!!!!
1.每一列的数据是不可分割的(不可以再分割)
2.每一列的数据完全依赖主键(不可以部分依赖)
3.不可以传递依赖
9.事务
事务:一组不可分割的数据库操作,要么全执行,要么全不执行
BEGIN;
delete from student;
select*from student;
ROLLBACK;
在begin(开始)和rollback(回滚)之间是事务的内容(如查询表、删除表)
10.ACID四大特性:
1.原子性:事务内的操作是一个整体,要么执行成功,要么执行失败
2.一致性:事务执行前后,数据库状态保持一致
3.隔离性:多个事务并发时,事务之间不能互相影响
4.持久性:事务一旦执行成功,数据库会保证事务处理一定会持久化到数据库中
11.JDBC
JDBC链接数据库
先将jar文件放到IDEA的文件夹下(新建一个就可以),将文件add to library
然后新创建一个类,写如下代码:
public class JDBC {
public static void main(String[] args) {
//JDBC链接数据库的六个步骤
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) {
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) {
e.printStackTrace();
}
}
if(con!=null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
于数据库连接共有6步操作;
1.加载驱动
2.创建连接
3.获取执行对象
4.执行SQL语句
5.处理结果集
6.关闭连接
标签:varchar,努力,--,31,第十四天,score,student,字符串,SELECT From: https://blog.csdn.net/tuowenjian/article/details/140820498