行列转换
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
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
SELECT *, case subject when '语文' then fraction else 0
end ,
这条 SQL 语句的作用是:
- 选择
t_score
表中的所有列 (SELECT *
)。 - 添加一个新的列,该列的值如下:
- 如果
subject
列的值为 '语文',则该新列的值为fraction
列的值。 - 如果
subject
列的值不为 '语文',则该新列的值为 0。
- 如果
if 函数 三元
SELECT *, if(SUBJECT='语文',fraction,0) from t_score
和上面的语句有相同的效果
SELECT *, avg(if(SUBJECT='语文',fraction,null)) from t_score
可以加null值,而avg会忽略null,这样就可以计算某一学科的平均值
数据类型
常用的数据类型
-- 整数类型 tinyint(1字节) smallint(2) MEDIUMINT(3) int(4) bigint(8)
-- 浮点型 DOUBLE(总长度,小数位数) FLOAT DECIMAL
-- 字符串 char varchar text
-- 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 姓名 地址 等
-- text 长文本 不需要设置长度
-- 日期
-- 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=b.cid;
-- 视图中不存储数据 数据还是存储在表中
SELECT * from v_student_score;
-- 编译 执行
触发器 trigger
-- 相当于一个事件 一旦表中发生了指定的事件,该触发器就会自动运行
-- 触发器可以通过三种操作触发 增删改
-- 触发时机 before
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(CONCAT('{',old.sid,',',old.sname,'}'),'->',CONCAT('{',new.sid,',',new.sname,'}')) WHERE
`key`='lastupdate';
end;
drop trigger tgg_u_b_student;
drop trigger tgg_u_b_student;//删除触发器
-- 尽量不用触发器 会影响正常业务逻辑 Java逻辑代码完成触发器的工作
函数 now max聚合函数 if()
自定义函数
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;
set global log_bin_trust_function_creators=TRUE;
SELECT method(80);
SELECT *,method(score)from sc
一些SQL方法:
1. `SELECT FLOOR(12.99);`
- `FLOOR()` 函数用于返回小于或等于指定数值的最大整数。在这个例子中, `FLOOR(12.99)` 的结果为 12。
2. `SELECT CHAR_LENGTH('你好'), LENGTH('你好');`
- `CHAR_LENGTH()` 函数返回字符串中字符的数量。对于中文字符,它返回字符的个数。
- `LENGTH()` 函数返回字符串的字节长度。对于中文字符,它返回字符占用的字节数。在这个例子中, `'你好'` 占用 6 个字节。
3. `SELECT LEFT('123456', 3), RIGHT('123456', 3);`
- `LEFT()` 函数返回字符串左边指定长度的字符。在这个例子中, `LEFT('123456', 3)` 返回 `'123'`。
- `RIGHT()` 函数返回字符串右边指定长度的字符。在这个例子中, `RIGHT('123456', 3)` 返回 `'456'`。
4. `SELECT CONCAT(LEFT('13366669999', 3), '****', RIGHT('13366669999', 4));`
- `CONCAT()` 函数用于连接多个字符串。在这个例子中, 它连接了左边3位数字、4个星号、以及右边4位数字。
5. `SELECT TRIM(' 123 ');`
- `TRIM()` 函数用于删除字符串开头和结尾的空白字符。在这个例子中, `TRIM(' 123 ')` 返回 `'123'`。
6. `SELECT REPLACE('1se1rgb', '1', 'A');`
- `REPLACE()` 函数用于替换字符串中的某些字符。在这个例子中, `REPLACE('1se1rgb', '1', 'A')` 返回 `'Ase1rgb'`。
7. `SELECT SUBSTR('abcdefg', 2, 3);`
- `SUBSTR()` 函数用于从字符串中提取子串。在这个例子中, `SUBSTR('abcdefg', 2, 3)` 返回 `'bcd'`。第二个参数是起始位置, 第三个参数是长度。
8. `SELECT SUBSTR('abcdefg', 3);`
- 如果只指定了起始位置,不指定长度, `SUBSTR()` 函数会返回从起始位置开始直到字符串结尾的子串。在这个例子中, `SUBSTR('abcdefg', 3)` 返回 `'cdefg'`。
9. `SELECT REVERSE('cbewayfgji');`
- `REVERSE()` 函数用于反转字符串。在这个例子中, `REVERSE('cbewayfgji')` 返回 `'ijgfyawbec'`。
10. `SELECT NOW(), SYSDATE();`
- `NOW()` 函数返回当前日期和时间。
- `SYSDATE()` 函数也返回当前日期和时间,与 `NOW()` 功能相同。
11. `SELECT DATE_FORMAT(NOW(), '%Y-%m-%d-%H-%i-%s');`
- `DATE_FORMAT()` 函数用于格式化日期时间。在这个例子中, 它将 `NOW()` 返回的当前日期时间,格式化为 `'2023-07-31-15-30-00'` 的形式。
存储过程 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=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(7)
三范式的概念
1. 第一范式(1NF):
- 要求数据库表的每一列都是不可分割的原子性数据项,即列不可再分。
- 例如,不应该将"姓名"列设计为"名"和"姓"两个子列。
2. 第二范式(2NF):
- 在满足第一范式的前提下,要求数据库表中的每一列都完全依赖于主键。
- 也就是说,表中的所有列要么完全依赖于主键,要么完全不依赖于主键。
- 不能出现部分依赖的情况。
3. 第三范式(3NF):
- 在满足第二范式的前提下,要求数据库表中的每一列数据都不能依赖于其他非主键列。
- 也就是说,不能出现传递依赖的情况。
- 例如,在一个学生信息表中,如果学生的所在城市依赖于学生的所在省份,那么这就是传递依赖,不符合第三范式。
总结一下:
- 第一范式是关于列的原子性;
- 第二范式是关于列与主键的完全依赖关系;
- 第三范式是关于列与列之间的传递依赖关系。
数据库事务及其特性:
事务(Transaction)是指一组逻辑操作单元,它由一个或多个SQL语句组成。事务具有以下四个特性,也称为ACID特性:
1. 原子性(Atomicity):
- 事务中的所有操作要么全部完成,要么全部不完成,不会结果是部分完成部分失败。
- 即事务是一个不可分割的整体,要么全部执行成功,要么全部执行失败。
2. 一致性(Consistency):
- 事务的执行不会违反数据库的完整性约束。
- 事务开始前和结束后,数据库都必须处于一致性状态。
3. 隔离性(Isolation):
- 并发执行的事务之间不会互相影响,各事务内部的操作对其他事务是透明的。
- 事务之间的操作是相互独立的,不会产生外部影响。
4. 持久性(Durability):
- 事务一旦提交,它对数据库的影响是永久性的,即使系统崩溃也不会lost。
- 即使系统发生故障,已经提交的事务对数据的修改也不会丢失。
通过事务的这四个特性,可以确保数据库操作的正确性和完整性。同时,数据库管理系统还提供了事务的隔离级别,如读未提交、读已提交、可重复读、串行化等,用于控制事务之间的相互影响程度。