MYSQLday2

行列转换
 

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 语句的作用是:

1. 选择 t_score 表中的所有列 (SELECT *)。
2. 添加一个新的列,该列的值如下:
   • 如果 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。
   - 即使系统发生故障,已经提交的事务对数据的修改也不会丢失。

通过事务的这四个特性,可以确保数据库操作的正确性和完整性。同时,数据库管理系统还提供了事务的隔离级别,如读未提交、读已提交、可重复读、串行化等,用于控制事务之间的相互影响程度。

脏读，幻读，不可重复读

锁的分类：