sql语句2
行列转换
转换为
关键是将学科对应成绩单独列出
使用case-when-then
或使用if三元函数
再利用分组(group by)求和(sum())
数据类型
常用的数据类型
整数类型:tinyint(1个字节) smallint(2)
mediumint(3) int(4) biging(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个字符位
varachar(20) 存储了abc 占了3个字符位
2.char的性能更好 varchar稍逊一些 因为要计算字符数
3.场景:
身份证号 学号 手机号 订单号 char
介绍信息 店名 姓名 地址 varchar
text是长文本不需要设置长度
日期:Date(年月日) time(时分秒) Datetime(年月日时分秒)
视图
视图view是已经编译好的SQL语句
创建视图
使用视图
视图中不存储数据,数据还是存储在表中
视图只是提前编译,方便执行
触发器
触发器trigger相当于一个事件,一旦表中发生了指定的事件,该触发器就会自动运行
触发器可以通过三种操作出发:增、删、改
触发器的触发时机:before、after
该触发器意为:
当student的表中添加了任意的数据,就会更新log表,将key为studentcount的那一行的val值改为student的总数量
该触发器意为:
当student表被修改时,就会更新log表,将key为lastupdate的那一行的val值改为{原来的数据->新的数据}的形式
其中old.列名表示原来的数据
new.列名表示新的数据
concat()表示字符串拼接
尽量不用触发器 会影响正常业务逻辑
java逻辑代码完成触发器的工作
函数
自定义函数
create function 方法名(参数名 参数类型) returns 返回值类型
begin
代码块
end
定义变量:
declare 变量名 变量类型
--------------------------------------------
if-else格式:
if 条件 then 条件成立方法体
else 条件不成立方法体
end if
--------------------------------------------
调用方法
mysql内置函数
mysql数值常用函数
abs(x) | 函数用于求绝对值。 |
floor(x) | 函数返回小于或等于x的最大整数。 |
rand() | 函数是返回0~1的随机数 |
PI() | 函数用于返回圆周率 |
truncate(x,y) | 函数返回x保留到小数点后y位的值。 |
round(x) | 函数对x四舍五入,round(x,y)返回x保留到y位,截断时进行四舍五入处理。 |
sqrt(x) | 函数用于求平方根。 |
mysql字符串常用函数
insert(s1,x,len,s2) | 函数将字符串s1中x位置开始长度为len的字符串替换为s2 |
upper(x)和ucase(x) | 函数将字符串x的所有字母变成大写。 |
left(x,n) | 函数返回字符串x的前n个字符。 |
concat(x1,x2,x3…) | 函数返回结果为连接参数产生的字符串。如果有任何一个参数为null,则返回值为null。 |
rtrim(x) | 函数将去掉字符串x结尾处的空格。 |
substring(x,n,len) | 函数从字符串x的第n个位置开始获取长度为len的字符串。 |
reverse(x) | 函数将字符串x的顺序反过来。 |
field(x,x1,x2,x3…) | 函数返回第一个与字符串x匹配的字符串的位置。 |
position(x1 in x) | 函数表示子字符串x1在字符串x中的开始位置。 |
instr(x,x1) | 函数表示子字符串x1在字符串x中的开始位置。 |
mysql日期类型函数
curdate()和 current_date() | 函数都可以获取当前日期。 |
curtime()和 current_time() | 函数用于获取当前时间。 |
now() | 函数可以获取当前日期和时间。 |
datediff(t1,t2) | 函数用于计算日期t1与t2之间相隔的天数。 |
adddate(t,n) | 函数用于返回t日期加上n天后的日期。 |
subdate(t,n) | 函数返回t日期减去n天的日期。 |
存储过程
create procedure 存储过程名(in 参数名 参数类型)
begin
代码块
end
调用存储过程
循环 loop是死循环
循环名:loop
循环体
leave 循环名
end loop
三范式
1.每一列的数据是不可分割的(不可以再分割)
2.每一列的数据要完全依赖主键(不可以部分依赖)
3.不可以传递依赖
事务
一组不可分割的数据库操作,要么全执行,要么全不执行
事务开启:
之后的操作都是事务内的操作,比如删除表
事务回退
事务回退后,事务操作都将会撤销
事务特性ACID
原子性(atomicity) | 事务内的操作是一个整体,要么执行成功,要么执行失败。 |
一致性(consistency) | 事务执行前后,数据库状态保持一致 以银行转账事务事务为例。在事务开始之前,所有账户余额的总额处于一致状态。在事务进行的过程中,一个账户余额减少了,而另一个账户余额尚未修改。因此,所有账户余额的总额处于不一致状态。事务完成以后,账户余额的总额再次恢复到一致状态。 |
隔离性(isolation) | 多个事务并发时,事物之间不能相互影响 |
持久性(durability) | 事务一旦执行成功,数据库会保证事务处理一定会持久化到数据库中 |
并发访问数据混乱
脏读 | 一个事务读取到了另一个事务修改未提交的记录 例如:数据表中一条记录值为v1, 事务A执行, 将值改为v2, 但并没有提交, 此时事务B读取, 如果读取到的记录值为v2, 则为脏读 |
幻读 | 当事务不是独立执行时出现的一种现象 例如:A事务读取或检索了多条数据,B事务添加或删除了一条新的数据 并且提交了B事务,A事务再去读取相同条件的内容,就会读取到B事务中新提交的数据,好像出现了幻觉一样 即为幻读 |
不可重复读 | 一个事务两次读取的记录数据不一致 例如: 事务A开启, 查找数据表记录r1, 并未提交, 此时事务B修改记录r1, 并提交, 事务A再次查找数据表记录r1, 如果两次得到的r1不一致, 即为不可重复读 |
事务的隔离级别
读未提交 | SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 允许脏读, 幻读, 不可重复读 |
读已提交 | 不允许脏读, 允许幻读, 不可重复读 方案: 给数据记录加行级读锁, 在更新记录前, 将记录锁定, 不允许其它事务操作 |
可重复读(确保同一个事务中读取的数据是一样的) | 不允许脏读,不可重复读, 允许幻读 方案: 给数据记录加行级写锁, 当对记录操作时, 其它事务只能等待 |
串行化/序列化 | 不允许脏读, 幻读, 不可重复读 方案: 给数据表加写锁, 一个事务对数据表操作时, 不允许其它事务访问 |
JDBC链接数据库的6个步骤
准备工作
数据库所在路径
用户名
密码
jdbc驱动类
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
1.加载驱动
2.创建连接
3.获取执行对象
4.执行SQL语句
5.处理结果集
6.处理异常,关闭JDBC资源
原数据库student表中内容
结果